MySQL 语句随笔（一）

最新推荐文章于 2022-05-02 11:05:41 发布

devp_yang

最新推荐文章于 2022-05-02 11:05:41 发布

阅读量203

点赞数

分类专栏： MySQL 文章标签： mysql sql

本文链接：https://blog.csdn.net/weixin_43365369/article/details/104572137

版权

MySQL 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

select 完整语法：select 去重选项 字段列表 [as 字段别名] 
					from 数据源 
			[where子句] [group by 子句] [having子句] [order by 子句] [limit子句]

1. SQL逻辑查询语句执行顺序(重点)

# 伪代码 序号代表执行顺序
7. SELECT 
8. DISTINCT <select_list>
1. FROM <left_table>
3. <join_type> JOIN <right_table>
2. ON <join_condition>
4. WHERE <where_condition>
5. GROUP BY <group_by_list>
6. HAVING <having_condition>
9. ORDER BY <order_by_condition>
10.LIMIT <limit_number>

2. 查询 sid 为 1 的学生数据

select * from student s where s.sid = 1

3. 查询 sid 为 1 的学生姓名

select sname from student s where s.sid = 1

4. 对学生表中的性别去重

select distinct t.gender from student t

5. 查询学生表中的姓名和性别并取别名

select t.sname as '姓名', t.gender as '性别' from student t

6. 多表不加条件联合查询之笛卡尔乘积：A表中数据条数 * B表中数据条数 = 笛卡尔乘积

select * from student s, pet p

7. 多表加条件联合查询

select * from teacher t, course c where t.tid = c.teacher_id

8. 多表加条件联合查询

# 多表连接查询语法(重点)
SELECT 字段列表
    FROM 表1  INNER|LEFT|RIGHT JOIN  表2
ON 表1.字段 = 表2.字段;

8.1 内连接查询（只显示符合条件的数据，相当于取交集）：与多表联合查询的效果是一样的

select * from teacher t inner join course c on t.tid = c.teacher_id

8.2 左外连接查询 (左边表中的数据优先全部显示，右表的数据符合条件的显示，不符合条件的用 null 进行填充)

select * from teacher t left join course c on t.tid = c.teacher_id

8.3 右外连接查询 (右边表中的数据优先全部显示，左表的数据符合条件的显示，不符合条件的用 null 进行填充)

select * from teacher t right join course c on t.tid = c.teacher_id

8.4 全连接查询 (显示左右表中全部数据，相当于并集，注意：oracle 支持 full join，mysql 是不支持的)

# 使用 UNION 可以间接实现 full JOIN 功能
select * from teacher t left join course c on t.tid = c.teacher_id
union
select * from teacher t right join course c on t.tid = c.teacher_id

9. 对分数表的分数进行排序，可以有多个字段，不同的排序方式

# 升序，不写默认升序
select * from score s order by s.num asc 
# 降序
select * from score s order by s.num desc 
# 先根据分数降序，再根据id升序
select * from score s order by s.num desc, s.sid asc

10. 对分数表的分数进行分组并排倒序，默认升序

# group by 的作用主要是统计，一般会配合一些统计函数来使用
select s.num as '分数', count(*) as '合计' from score s group by s.num desc
# group by 字段可以有多个，实际是二次分组，先按分数分组，再按课程id分组
select s.num as '分数',s.course_id, count(num) as '合计' from score s group by s.num,s.course_id

11. count（）函数：统计记录数（null 不统计在内）

select count(cname) from teacher t left join course c on t.tid = c.teacher_id

12. max（）函数：统计最大值

# 找出最大的分数
select max(num) from score s

13. min（）函数：统计最小值

# 找出最小的分数
select min(num) from score s

14. sum（）：求和

# 对分数进行求和
select sum(num) from score s

15. avg（）：求平均数

# 对分数进行求平均值
select avg(num) from score s

16. having 子句：having 功能与 where 类似，不过 having 的条件判断发生在数据在内存中时，所以可以使用在内存中才发生的数据，如“分组”，“字段别名”等

select * from student s having s.sname = '钢蛋'
# 先分组，在内存中过滤
select s.num, count(num) from score s group by s.num having count(num) > 2;

17. limit 子句：用来限制结果数量的，与 where\having 等配合使用时，可以限制匹配出的结果

语法: select 字段列表 from 表名 limit [offset,] count;   offset 从 0 开始
# 查询出分数大于等于60的数据的第一条
select * from score s where s.num >= 60 limit 1
# 查询出分数大于等于60的数据的第三条开始的5条数据
select * from score s where s.num >= 60 limit 2,5

18. where 子句：用于筛选符合条件的结果，where 是从磁盘中获取数据的时候就进行筛选的。所以某些在内存时才有的东西 where 无法使用。(字段别名什么的本来不是“磁盘中的数据”（是在内存中运行时才定义的），所以 where 无法使用，一般都依靠having 来筛选)

# 等于
select * from score s where s.num = 100
# 不等于
select * from score s where s.num != 100
# 不等于
select * from score s where s.num <> 100
# 大于
select * from score s where s.num > 66
# 大于等于
select * from score s where s.num >= 66
# 小于
select * from score s where s.num < 66
# 小于等于
select * from score s where s.num <= 66
# 模糊匹配
# like
select * from student s where s.sname like '%三%'
# INSTR(str, substr)
select * from student s where instr(s.sname,'三')
# LOCATE(substr, str)
select * from student s where locate('三', s.sname)
# POSITION(substr IN str)
select * from student s where position('三' in s.sname)
# 基于值的范围
select * from score s where s.num in (100,66)
select * from score s where s.num not in (100,66)
# 是闭区间[66,100]
select * from score s where s.num between 66 and 100
# 条件复合
# or
select * from score s where s.num = 66 or s.num = 100
# and 
select * from score s where s.num = 66 and s.course_id = 4
# not
select * from score s where not s.num = 100
# && = and
select * from score s where s.num = 66 && s.course_id = 4
# || = or
select * from score s where s.num = 66 || s.course_id = 4
# ! = not
select * from score s where !(s.num = 100)

19. 子查询（嵌套查询）：查多次，多个 select，第一次的查询结果可以作为第二次的查询的条件或者表名使用，子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS 等关键字。还可以包含比较运算符：= 、 !=、> 、< 等

# 作为表名使用，as 后面的表名称不能加引号('')
select * from (select * from score) as account;
# any 关键字 假设返回结果有三个 相当于 select ...from ... where a > result1 or a > result2 or a > result3;
select * from score s where s.num > any(select num from score where sid in(8,9,10));
# all 关键字 假设返回结果有三个 相当于 select ...from ... where a > result1 and a > result2 and a > result3;
select * from score s where s.num > all(select num from score where sid in(8,9,10));
# some 关键字 和 any 关键字类似
select * from score s where s.num > some(select num from score where sid in(8,9,10));
# EXISTS 和 NOT EXISTS 子查询语法
# EXISTS (subquery) 只返回 TRUE 或 FALSE,因此子查询中的 SELECT * 也可以是 SELECT 1 或其他,官方说法是实际执行时会忽略 SELECT 清单,因此没有区别。
# 主查询(外部查询)会根据子查询验证结果（TRUE 或 FALSE）来决定主查询是否得以执行
# 此处如果子查询有满足条件的结果就会返回true,就会执行主查询（外部查询）
select * from score where exists (select * from score where sid = 8);
# not exists 刚好相反 子查询没有满足条件的结果时执行主查询
select * from score where not exists (select * from score where sid = 133);

20. 判断查询 case when … else… end

# 语法一
select case when num between 60 and 80 then '良好' when num < 60 then '不合格'
else '优秀' end from score
# 语法二
select case num when 100 then '满分' else '优秀' end from score

21. 外键约束

约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。
foreign key 定义：就是表与表之间的某种约定的关系，由于这种关系的存在，能够让表与表之间的数据，更加的完整，关连性更强。

# 添加外键约束
ALTER table person add constraint fk_did FOREIGN key(dept_id) REFERENCES dept(did);
# 删除外键约束
ALTER TABLE person drop FOREIGN key fk_did;

定义外键的条件
（1）外键对应的字段数据类型保持一致，且被关联的字段(即references指定的另外一个表的字段)，必须保证唯一
（2）所有 tables 的存储引擎必须是InnoDB类型
（3）外键的约束4种类型: 1.RESTRICT 2. NO ACTION 3.CASCADE 4.SET NULL

RESTRICT：同no action，都是立即检查外键约束
NO ACTION：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作
CASCADE：在父表上update/delete记录时，同步update/delete掉子表的匹配记录
SET NULL：在父表上update/delete记录时，将子表上匹配记录的列设为null (要注意子表的外键列不能为not null)

（4）建议:1.如果需要外键约束，最好创建表同时创建外键约束
　　　　 2.如果需要设置级联关系，删除时最好设置为 SET NULL
　　　　
注：插入数据时，先插入主表中的数据，再插入从表中的数据。
删除数据时，先删除从表中的数据，再删除主表中的数据。

22. 其他约束

not null：非空约束，表示不可空，用来约束表中的字段列
primary key：主键约束，用于约束表中的一行，作为这一行的标识符，在一张表中通过主键就能准确定位到一行，因此主键十分重要。注意：主键这一行的数据不能重复且不能为空。还有一种特殊的主键——复合主键。主键不仅可以是表中的一列，也可以由表中的两列或多列来共同标识。
unique：唯一约束，它规定一张表中指定的一列的值必须不能有重复值，即这一列每个值都是唯一的。
default：默认值约束，INSERT语句执行时.，如果被DEFAULT约束的位置没有值，那么这个位置将会被DEFAULT的值填充

23. if() 函数

IF(condition, value_if_true, value_if_false)
SELECT IF(500 < 1000, 5, 10);

24. ifnull() 函数

IFNULL(expr1,expr2): 如果expr1的值为null，则返回expr2的值，如果expr1的值不为null，则返回expr1的值
SELECT IFNULL(NULL,'B');    -- 输出结果：B
SELECT IFNULL('HELLO','B'); -- 输出结果：HELLO

25. nullif() 函数

NULLIF(expr1,expr2)，如果expr1 = expr2成立，那么返回值为null，否则返回值为expr1的值
SELECT NULLIF('A','A');     -- 输出结果：null
SELECT NULLIF('A','B');     -- 输出结果：A

26. isnull() 函数

ISNULL(expr)，如果expr的值为null，则返回1，如果expr1的值不为null，则返回0。
SELECT ISNULL(NULL);        -- 输出结果：1
SELECT ISNULL('HELLO');     -- 输出结果：0

devp_yang

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL 语句随笔（一）

select 完整语法：select 去重选项字段列表 [as 字段别名] from 数据源 [where子句] [group by 子句] [having子句] [order by 子句] [limit子句]1. SQL逻辑查询语句执行顺序(重点)# 伪代码序号代表执行顺序7. SELECT 8. DISTINCT <select_list>1. ...
复制链接

扫一扫