目录
一、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.统计所有学⽣数学成绩总分
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 |