数据库-＞增删改查

2024-10-26 07:24:38

一、CRUD简介

二、Create新增

1.单行数据插入

2.查询

3. 多行数据插入

4.执行本机的SQL脚本插入

三、Retrieve检索

1.全列查询

2.指定列查询

3.查询字段为表达式

3.1 常量表达式

3.2列的值与常量运算

3.3列与列之间的运算

3.4为列指定别名

4.结果查询去重

5.Where条件查询

5.1比较运算符

5.2 逻辑运算符

5.3示例

1.查询英语成绩不及格的同学(

2.查询语文成绩高于英语成绩的同学

3.总分在200分以下的同学

4.查询语文成绩大于80且英语成绩大于80的同学

5.查询语文成绩大于80或英语成绩大于80的同学

6.观察and和or的优先级

7.语⽂成绩在[80,90]分的同学

8.数学成绩是78或者79或者98或者99分的同学

9.查询所有姓孙的同学

10.查询姓孙且姓名共有两个字同学

11.查询英语成绩为NULL的记录

12.查询英语成绩不为NULL的记录

13.NULL与其他值进⾏运算结果为NULL

14.注意

6.Order by(排序)

6.1语法

6.2示例

1.按数学成绩从低到⾼排序(升序)

2.按语⽂成绩从⾼到低排序(降序)

3.按英语成绩从⾼到低排序

4.查询同学各⻔成绩，依次按数学降序，英语升序，语⽂升序的⽅式显⽰

5.查询同学及总分，由⾼到低排序

6.可以使⽤列的别名进⾏排序

7. 所有英语成绩不为NULL的同学，按语⽂成绩从⾼到低排序

8.注意

7.分页查询

7.1语法

1.语法1

2.语法2

3.语法3

四、Update修改

1.语法

2.示例

1.将孙悟空同学的数学成绩变更为 80 分

2.将曹孟德同学的数学成绩变更为60分，语⽂成绩变更为70分

3.将总成绩倒数前三的3位同学的数学成绩加上30分

3.Update注意事项

五、Delete删除

1.语法

2.示例

1.删除学生姓为孙权的记录

2.删除整张表数据

六、截断表

1.语法

2.示例

3.注意事项

七、插入查询

1.语法

2.示例

1.删除表中的重复记录，重复的数据只能有⼀份

2.重命名表

八、聚合函数

1.常用函数

2.示例

2.1Count

1.统计exam表中有多少记录

2.统计有多少学⽣参加数学考试

3.统计语⽂成绩⼩于60分的学⽣个数

2.2Sum

1.统计所有学⽣数学成绩总分

2.统计所有学⽣英语成绩总分

3.不能统计⾮数值的列

2.3Avg

1.统计英语成绩的平均值

2.格式化输出小数

3.统计平均总分

2.4Max && Min

1.查询英语最高分

2.查询 >70 的数学最低分

3.查询数学成绩的最⾼分与英语成绩的最低分

九、Group by分组查询

1.概念

2.语法

3.示例

1.统计每个⻆⾊的⼈数

2.统计每个⻆⾊的平均⼯资，最⾼⼯资，最低⼯资

4.having语句

1.示例

1.显⽰平均⼯资低于1500的⻆⾊和它的平均⼯资

2.Having 与 Where 的区别

十、内置函数

1.日期函数

2.字符串处理函数

2.1示例

1.显⽰学⽣的考试成绩，格式为 "XXX的语⽂成绩：XXX分，数学成绩：XXX分，英语成绩：XXX分"

3.数学函数

4.其它常用函数

一、CRUD简介

CURD是对数据库中的记录进⾏基本的增删改查操作：
• Create(创建)
• Retrieve(读取)
• Update(更新)
• Delete(删除)

二、Create新增

INSERT [INTO] table_name
    [(column [, column] ...)]
VALUES
    (value_list) [, (value_list)] ...

value_list: value, [, value] ...

1.单行数据插入

推荐使用指定字段列表，只要值与字段对应即可，和表定义时字段的顺序无关

没有自定id这一列时 id这一列会用默认值去填充默认值是NULL

2.查询

3. 多行数据插入

4.执行本机的SQL脚本插入

或者

执行本机的SQL脚本

三、Retrieve检索

SELECT
    [DISTINCT]
    select_expr [, select_expr] ...
    [FROM table_references]
    [WHERE where_condition]
    [GROUP BY {col_name | expr}, ...]
    [HAVING where_condition]
    [ORDER BY {col_name | expr } [ASC | DESC], ... ]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

1.全列查询

2.指定列查询

select 列名[,列名] ... from 表名;

3.查询字段为表达式

3.1 常量表达式

表达式在查询列表中，但是在表中不存在

返回的结果集是根据查询列表中的字段和表达式生成的一个临时表，专门用来保存查询结果的当结果集返回给客户端时，结果集就销毁了

3.2列的值与常量运算

把所有学⽣的语⽂成绩加10分

当使用表达式计算新成绩后表达式中的成绩没有被修改

3.3列与列之间的运算

计算所有学⽣语⽂、数学和英语成绩的总分

3.4为列指定别名

若中间有空格必须用 ' ' 引起来

as可以省略不写

每一个查询的列都可以起别名

4.结果查询去重

select distinct 列名[,列名] ... from 表名;

要查询的结果集中发现重复数据只保留一条不会删除原来的内容

在结果集中，每一列都时相同的MYSQL才判定为重复

5.Where条件查询

根据指定的一些条件，过滤掉不符合条件的记录，把符合条件的记录返回给用户

SELECT
    select_expr [, select_expr] ... [FROM table_references]
    WHERE where_condition

5.1比较运算符

运算符	说明
>,>=,<,<=	⼤于，⼤于等于，⼩于，⼩于等于
=	等于，对于NULL的⽐较不安全，⽐如NULL=NULL结果还是NULL
<=>	等于，对于NULL的⽐较j是安全的，⽐如NULL<=>NULL结果是TRUE(1)
!=,<>	不等于
value BETWEEN a0 AND a1	范围匹配，[a0,a1]，如果a0<=value<=a1，返回TRUE或1，NOT BETWEEN则取反
value IN(option,...)	如果value在optoin列表中，则返回TRUE(1)，NOT IN则取反
IS NULL	是NULL
IS NOT NULL	不是NULL
LIKE	模糊匹配，%表⽰任意多个(包括0个)字符；_表⽰任意⼀个字符，NOT LIKE则取反

5.2 逻辑运算符

运算符	说明
AND	多个条件必须都为TRUE(1)，结果才是TRUE(1)
OR	任意⼀个条件为TRUE(1),结果为TRUE(1)
NOT	条件为TRUE(1)，结果为FALSE(0)

5.3示例

1.查询英语成绩不及格的同学(<60)

插入一条英语成绩为NULL的记录，结果集中没有NULL的记录

2.查询语文成绩高于英语成绩的同学

3.总分在200分以下的同学

where子句不能以别名当作过滤条件

1.先找到这个表 -- from

2.定位符合条件的数据行 -- where

3.在过滤后的结果集中抽取要显示的列、表达式 -- select

所以在执行where子句时，select后边指定的别名还没有定义

4.查询语文成绩大于80且英语成绩大于80的同学

5.查询语文成绩大于80或英语成绩大于80的同学

6.观察and和or的优先级

not > and > or

推荐使用小括号给扩起来

7.语⽂成绩在[80,90]分的同学

8.数学成绩是78或者79或者98或者99分的同学

9.查询所有姓孙的同学

10.查询姓孙且姓名共有两个字同学

11.查询英语成绩为NULL的记录

12.查询英语成绩不为NULL的记录

13.NULL与其他值进⾏运算结果为NULL

14.注意

• WHERE条件中可以使⽤表达式，但不能使⽤别名
• AND的优先级⾼于OR，在同时使⽤时，建议使⽤⼩括号()包裹优先执⾏的部分
• 过滤NULL时不要使⽤等于号(=)与不等于号(!=,<>)
• NULL与任何值运算结果都为NULL

6.Order by(排序)

-- ASC 为升序（从⼩到⼤）
-- DESC 为降序（从⼤到⼩）
-- 默认为 ASC
SELECT ... FROM table_name [WHERE ...] ORDER BY {col_name | expr } [ASC |
DESC], ... ;

6.1语法

select 列名 from 表名 order by 列名 [ASC | DESC]

指定排序的列返回的结果集就是针对这个列进行排序后的结果集实在临时表中显示的

6.2示例

1.按数学成绩从低到⾼排序(升序)

2.按语⽂成绩从⾼到低排序(降序)

3.按英语成绩从⾼到低排序

NULL 在排序时 NULL值被认定为比任何值都小

4.查询同学各⻔成绩，依次按数学降序，英语升序，语⽂升序的⽅式显⽰

分别为每个列指定排序规则在结果集中按从左到右的顺序进行排序

5.查询同学及总分，由⾼到低排序

6.可以使⽤列的别名进⾏排序

有了查询结果之后才可以进行排序

7. 所有英语成绩不为NULL的同学，按语⽂成绩从⾼到低排序

8.注意

• 查询中没有ORDER BY⼦句，返回的顺序是未定义的，永远不要依赖这个顺序

• ORDER BY⼦句中可以使⽤列的别名进⾏排序

• NULL进⾏排序时，视为⽐任何值都⼩，升序出现在最上⾯，降序出现在最下⾯

7.分页查询

-- 起始下标为 0
-- 从 0 开始，筛选 num 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT num;
-- 从 start 开始，筛选 num 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT start, num;
-- 从 start 开始，筛选 num 条结果，⽐第⼆种⽤法更明确，建议使⽤
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT num OFFSET start;

7.1语法

1.语法1

从第零条开始，只取前面两条作为返回的结果值

limit执行顺序在 order by 之后

2.语法2

表示从第1条开始读取两条注意起始位置从0开始计数

若起始位置超过了表中记录范围执行没有问题但是会返回一个空结果集

3.语法3

从位置为1的记录开始向后读取2条记录

四、Update修改

1.语法

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment [, assignment] ... 
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

2.示例

1.将孙悟空同学的数学成绩变更为 80 分

2.将曹孟德同学的数学成绩变更为60分，语⽂成绩变更为70分

3.将总成绩倒数前三的3位同学的数学成绩加上30分

3.Update注意事项

• 以原值的基础上做变更时，不能使⽤math += 30这样的语法
• 不加where条件时，会导致全表数据被列新，谨慎操作

五、Delete删除

1.语法

DELETE FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]

2.示例

1.删除学生姓为孙权的记录

2.删除整张表数据

如果不加任何条件执行delete语句会把表中的数据全部删除（非常危险）

六、截断表

1.语法

TRUNCATE [TABLE] tbl_name

2.示例

表截断：

表回到了最初创建表时候的最初位置自增ID也会从零开始

3.注意事项

• 只能对整表操作，不能像 DELETE ⼀样针对部分数据
• 不对数据操作所以⽐DELETE更快，TRUNCATE在删除数据的时候，不经过真正的事物，所以⽆法回滚
• 会重置 AUTO_INCREMENT 项

七、插入查询

1.语法

INSERT INTO table_name [(column [, column ...])] SELECT ...

2.示例

1.删除表中的重复记录，重复的数据只能有⼀份

2.重命名表

八、聚合函数

1.常用函数

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的数量统计结果集的记录数
SUM([DISTINCT] expr)	返回查询到的数据的总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数据的平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数据的最⼤值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数据的最⼩值，不是数字没有意义

2.示例

2.1Count

1.统计exam表中有多少记录

推荐使用count(*)

如果统计的列中有null值的行则改行不参与统计

2.统计有多少学⽣参加数学考试

3.统计语⽂成绩⼩于60分的学⽣个数

2.2Sum

1.统计所有学⽣数学成绩总分

2.统计所有学⽣英语成绩总分

在进行求和运算时会忽略无效的null值

3.不能统计⾮数值的列

2.3Avg

1.统计英语成绩的平均值

2.格式化输出小数

3.统计平均总分

2.4Max && Min

1.查询英语最高分

2.查询 >70 的数学最低分

3.查询数学成绩的最⾼分与英语成绩的最低分

九、Group by分组查询

1.概念

GROUP BY ⼦句的作⽤是通过⼀定的规则将⼀个数据集划分成若⼲个⼩的分组，然后针对若⼲个
分组进⾏数据处理，⽐如使⽤聚合函数对分组进⾏统计

2.语法

SELECT {col_name | expr} ,... ,aggregate_function (aggregate_expr)
    FROM table_references
    GROUP BY {col_name | expr}, ...
    [HAVING where_condition]

• col_name | expr：要查询的列或表达式，可以有多个，必须在 GROUP BY ⼦句中作为分组的依
据
• aggregate_function：聚合函数，⽐如COUNT(), SUM(), AVG(), MAX(), MIN()
• aggregate_expr：聚合函数传⼊的列或表达式，如果列或表达式不在 GOURP BY ⼦句中，必须
包含中聚合函数中

3.示例

1.统计每个⻆⾊的⼈数

2.统计每个⻆⾊的平均⼯资，最⾼⼯资，最低⼯资

4.having语句

使⽤GROUP BY 对结果进⾏分组处理之后，对分组的结果进⾏过滤时，不能使⽤ WHERE ⼦句，⽽要使⽤ HAVING ⼦句

1.示例

1.显⽰平均⼯资低于1500的⻆⾊和它的平均⼯资

2.Having 与 Where 的区别

• Having ⽤于对分组结果的条件过滤
• Where ⽤于对表中真实数据的条件过滤

十、内置函数

1.日期函数

函数	说明
CURDATE()	返回当前⽇期，同义词 CURRENT_DATE ， CURRENT_DATE()
CURTIME()	返回当前时间，同义词 CURRENT_TIME ， CURRENT_TIME([fsp])
NOW()	返回当前⽇期和时间，同义语 CURRENT_TIMESTAMP ,CURRENT_TIMESTAMP
DATE(data)	提取date或datetime表达式的⽇期部分
ADDDATE(date,INTERVAL expr unit)	向⽇期值添加时间值(间隔)，同义词 DATE_ADD()
SUBDATE(date,INTERVAL expr unit)	向⽇期值减去时间值(间隔)，同义词 DATE_SUB()
DATEDIFF(expr1,expr2)	两个⽇期的差，以天为单位，expr1 - expr2

在计算时只使用日期部分表达式1日期早于表达式2日期时返回负数

一般对数据的处理在应用程序中完成，不要放在数据库中

2.字符串处理函数

函数	说明
CHAR_LENGTH(str)	返回给定字符串的⻓度，同义词 CHARACTER_LENGTH()
LENGTH(str)	返回给定字符串的字节数，与当前使⽤的字符编码集有关
CONCAT(str1,str2,...)	返回拼接后的字符串
CONCAT_WS(separator,str1,str2,...)	返回拼接后带分隔符的字符串
LCASE(str)	将给定字符串转换成⼩写，同义词 LOWER()
UCASE(str)	将给定字符串转换成⼤写，同义词 UPPER()
HEX(str), HEX(N)	对于字符串参数str, HEX()返回str的⼗六进制字符串表⽰形式，对于数字参数N, HEX()返回⼀个⼗六进制字符串表⽰形式
INSTR(str,substr)	返回substring第⼀次出现的索引
INSERT(str,pos,len,newstr)	在指定位置插⼊⼦字符串，最多不超过指定的字符数
SUBSTR(str,pos) SUBSTR(str FROM pos FOR len)	返回指定的⼦字符串，同义词 SUBSTRING(str,pos) SUBSTRING(str FROM pos FOR len)
REPLACE(str,from_str,to_str)	把字符串str中所有的from_str替换为to_str，区分⼤⼩写
STRCMP(expr1,expr2)	逐个字符⽐较两个字符串，返回-1，0，1
LEFT(str,len) ，RIGHT(str,len)	返回字符串str中最左/最右边的len个字符
LTRIM(str) RTRIM(str) TRIM(str)	删除给定字符串的前导、末尾、前导和末尾的空格
TRIM([{LEADING \| TRAILING \| BOTH } [remstr] FROM]	删除给定符串的前导、末尾或前导和末尾的指定字符串

2.1示例

1.显⽰学⽣的考试成绩，格式为 "XXX的语⽂成绩：XXX分，数学成绩：XXX分，英语成绩：XXX分"

3.数学函数

函数	说明
ABS(X)	返回X的绝对值
CEIL(X)	返回不⼩于X的最⼩整数值，同义词是 CEILING(X)
FLOOR(X)	返回不⼤于X的最⼤整数值
CONV(N,from_base,to_base)	不同进制之间的转换
FORMAT(X,D)	将数字X格式化为“#，###，###”的格式。##'，四舍五⼊到⼩数点后D 位，并以字符串形式返回
RAND([N])	返回⼀个随机浮点值，取值范围 [0.0, 1.0)
ROUND(X), ROUND(X,D)	将参数X舍⼊到⼩数点后D位
CRC32(expr)	计算指定字符串的循环冗余校验值并返回⼀个32位⽆符号整数

4.其它常用函数

函数	说明
version()	显⽰当前数据库版本
database()	显⽰当前正在使⽤的数据库
user()	显⽰当前⽤⼾
md5(str)	对⼀个字符串进⾏md5摘要，摘要后得到⼀个32位字符串
ifnull(val1, val2)	如果val1为NULL，返回val2，否则返回 val1

码农公寓

一、CRUD简介

二、Create新增

1.单行数据插入

2.查询

3. 多行数据插入

4.执行本机的SQL脚本插入

三、Retrieve检索

1.全列查询

2.指定列查询

3.查询字段为表达式

3.1 常量表达式

3.2列的值与常量运算

3.3列与列之间的运算

3.4为列指定别名

4.结果查询去重

5.Where条件查询

5.1比较运算符

5.2 逻辑运算符

5.3示例

1.查询英语成绩不及格的同学(<60)

2.查询语文成绩高于英语成绩的同学

3.总分在200分以下的同学

4.查询语文成绩大于80且英语成绩大于80的同学

5.查询语文成绩大于80或英语成绩大于80的同学

6.观察and和or的优先级

7.语⽂成绩在[80,90]分的同学

8.数学成绩是78或者79或者98或者99分的同学

9.查询所有姓孙的同学

10.查询姓孙且姓名共有两个字同学

11.查询英语成绩为NULL的记录

12.查询英语成绩不为NULL的记录

13.NULL与其他值进⾏运算结果为NULL

14.注意

6.Order by(排序)

6.1语法

6.2示例

1.按数学成绩从低到⾼排序(升序)

2.按语⽂成绩从⾼到低排序(降序)

3.按英语成绩从⾼到低排序

4.查询同学各⻔成绩，依次按数学降序，英语升序，语⽂升序的⽅式显⽰

5.查询同学及总分，由⾼到低排序

6.可以使⽤列的别名进⾏排序

7. 所有英语成绩不为NULL的同学，按语⽂成绩从⾼到低排序

8.注意

7.分页查询

7.1语法

1.语法1

2.语法2

3.语法3

四、Update修改

1.语法

2.示例

1.将孙悟空同学的数学成绩变更为 80 分

2.将曹孟德同学的数学成绩变更为60分，语⽂成绩变更为70分

3.将总成绩倒数前三的3位同学的数学成绩加上30分

3.Update注意事项

五、Delete删除

1.语法

2.示例

1.删除学生姓为孙权的记录

2.删除整张表数据

六、截断表

1.语法

2.示例

3.注意事项

七、插入查询

1.语法

2.示例

1.删除表中的重复记录，重复的数据只能有⼀份

2.重命名表

八、聚合函数

1.常用函数

2.示例

2.1Count

1.统计exam表中有多少记录

2.统计有多少学⽣参加数学考试

3.统计语⽂成绩⼩于60分的学⽣个数

2.2Sum

1.统计所有学⽣数学成绩总分