Mysql数据查询
Mysql查询
数据多次过滤
条件：from、where、group by、having、distinct、order by、limit => 层层筛选后的结果
查：
select [distinct] 字段1 [[as] 别名1],...,字段n [[as] 别名n] from [数据库名.]表名 [条件];
注：一条查询语句，可以拥有多种筛选条件，条件的顺序必须按照上方顺序进行逐步筛选，distinct稍有特殊(书写位置)，条件的种类可以不全
可以缺失，但不能乱序
单表查询
distinct 去重
数据为：
+------+------+
| x | y |
+------+------+
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 1 | 2 |
+------+------+
#执行
select distinct * from t1;
+------+------+
| x | y |
+------+------+
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
+------+------+
# 总结：distinct对参与查询的所有字段，整体去重(所查的全部字段的值都相同，才认为是重复数据)
常用函数
拼接：concat() | concat_ws()
大小写：upper() | lower()
浮点型操作：ceil() | floor() | round()
整型：可以直接运算
concat()和concat_ws()
mysql>: select name as 姓名, concat(area,'-',port) 地址 from emp; # 上海-浦东
mysql>: select name as 姓名, concat_ws('-',area,port,dep) 信息 from emp; # 上海-浦东-教职部
#concat_ws(),可以在每个字段之间加上第一个字符
upper()和lower()
select upper(name) 姓名大写,lower(name) 姓名小写 from emp;
ceil()，floor()，round()
#元数据
+------+
| x |
+------+
| 1.5 |
| 1.2 |
| 1.51 |
+------+
#ceil() 向上取整
mysql> select ceil(x) from t2;
+---------+
| ceil(x) |
+---------+
| 2 |
| 2 |
| 2 |
+---------+
#floor()向下取整
mysql> select floor(x) from t2;
+----------+
| floor(x) |
+----------+
| 1 |
| 1 |
| 1 |
+----------+
#round() 特殊版本的四舍五入 5的时候舍
mysql> select round(x) from t2;
+----------+
| round(x) |
+----------+
| 2 |
| 1 |
| 2 |
+----------+
整数
select x+1 from t1;
where
常用判断
比较符合：> | < | >= | <= | = | !=
区间符合：between 开始 and 结束 | in(自定义容器) | not in
逻辑符合：and | or | not
相似符合：like _|%
正则符合：regexp 正则语法
自定义区间：id in (2,3) | id not in (2,3) | id < all(2,3) | id < any(2,3)
案例
mysql>: select * from emp where salary>5;
mysql>: select * from emp where id%2=0;
mysql>: select * from emp where salary between 6 and 9;
mysql>: select * from emp where id in(1, 3, 7, 20);
# _o 某o | __o 某某o | _o% 某o* (*是0~n个任意字符) | %o% *o*
mysql>: select * from emp where name like '%o%';
mysql>: select * from emp where name like '_o%';
mysql>: select * from emp where name like '___o%';
# sql只支持部分正则语法
mysql>: select * from emp where name regexp '.*\d'; # 不支持\d代表数字，认为\d就是普通字符串
mysql>: select * from emp where name regexp '.*[0-9]'; # 支持[]语法
all与any：区间修饰条件
# 语法规则
# where id in (1, 2, 3) => id是1或2或3
# where id not in (1, 2, 3) => id不是1，2，3
# where salary < all(3, 6, 9) => salary必须小于所有情况（小于最小）
# where salary > all(3, 6, 9) => salary必须大于所有情况（大于最大）
# where salary < any(3, 6, 9) => salary只要小于一种情况（小于最大）
# where salary > any(3, 6, 9) => salary只要大于一种情况（大于最小）
in < > ()
# 案例
select * from emp where salary < all(select salary from emp where id>11);
分组group by
分组与筛选：group by|having
where和having区别
1.where在分组之前限定，如果不满足条件，则不参与分组。
having在分组之后进行限定，如果不满足结果，则不会被查询出来
2.where后不可以跟聚合函数，having可以进行聚合函数的判断
聚合函数
max()：最大值
min()：最小值
avg()：平均值
sum()：和
count()：记数
group_concat()：组内字段拼接，用来查看组内其他字段
分组查询group by
配置
# 修改my.ini配置重启mysql服务
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
注意
分组查询的字段只有：分组字段、聚合函数
having不能使用聚合函数别名
完整体
select sex,AVG(math),COUNT(id) from student where math>70 group by sex having count(id) > 2
实验
#元数据
+------+------+
| z1 | z2 |
+------+------+
| 1 | zx |
| 2 | zy |
| 9 | zx |
| 5 | zy |
| 6 | zx |
| 7 | zy |
+------+------+
#执行
select z2,avg(z1) from t3 group by z2;
+------+---------+
| z2 | avg(z1) |
+------+---------+
| zx | 5.3333 |
| zy | 4.6667 |
+------+---------+
联合分组
简单来说就是对多个字段分组
#执行
select z1,z2 from t3 group by z1,z2;
+------+------+
| z1 | z2 |
+------+------+
| 1 | zx |
| 2 | zy |
| 5 | zy |
| 6 | zx |
| 7 | zy |
| 9 | zx |
+------+------+
排序
排序规则
# order by 主排序字段 [asc|desc], 次排序字段1 [asc|desc], ...次排序字段n [asc|desc]
未分组状态下
mysql>: select * from emp;
# 按年龄升序
mysql>: select * from emp order by age asc;
# 按薪资降序
mysql>: select * from emp order by salary desc;
# 按薪资降序，如果相同，再按年龄降序
mysql>: select * from emp order by salary desc, age desc;
# 按龄降序，如果相同，再按薪资降序
mysql>: select * from emp order by age desc, salary desc;
分组状态下
mysql>:
select
dep 部门,
group_concat(name) 成员,
max(salary) 最高薪资,
min(salary) 最低薪资,
avg(salary) 平均薪资,
sum(salary) 总薪资,
count(gender) 人数
from emp group by dep;
# 最高薪资降序
mysql:
select
dep 部门,
group_concat(name) 成员,
max(salary) 最高薪资,
min(salary) 最低薪资,
avg(salary) 平均薪资,
sum(salary) 总薪资,
count(gender) 人数
from emp group by dep
order by 最高薪资 desc;
分页limit
# 语法：limit 条数 | limit 偏移量,条数
#元数据
+------+------+
| z1 | z2 |
+------+------+
| 1 | zx |
| 2 | zy |
| 9 | zx |
| 5 | zy |
| 6 | zx |
| 7 | zy |
+------+------+
#执行
select * from t3 limit 1;
+------+------+
| z1 | z2 |
+------+------+
| 1 | zx |
+------+------+
#执行
select * from t3 limit 2,2;
+------+------+
| z1 | z2 |
+------+------+
| 9 | zx |
| 5 | zy |
+------+------+
多表查询
分类
1.内连接
2.左连接
3.右连接
4.全连接
from 左表 inner|left|right join 右表 on 两个表之间的关联条件
inner：都是有用的
left：左边全有
right：右边全有
全连：left union right 有用没用都有
笛卡尔积
笛卡尔积是数据库多表连接的基础，它会列出所有的组合，多表查询其实就是对笛卡尔积进行过滤留下有用的数据
1 2 3和z x y的笛卡尔积
1 z 1 x 1 y
2 z 2 x 2 y
3 z 3 x 3 y
select * from z1,z2
一对一和一对多
元数据
+------+----------+
| id | name |
+------+----------+
| 1 | 教室_1 |
| 2 | 教室_2 |
| 3 | 教室_3 |
| 4 | NULL |
| NULL | NULL |
+------+----------+
+------+------+
| id | name |
+------+------+
| 1 | zx |
| 2 | wl |
| 3 | zy |
| 1 | zxy |
| 2 | fd |
| 3 | hj |
| 4 | ds |
| 1 | NULL |
| NULL | NULL |
+------+------+
内连接
找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果
# 关键字：inner join on
# 语法：from A表 inner join B表 on A表.关联字段=B表.关联字段
可以简写为join
#执行
select * from class join student on class.id=student.id;
+------+----------+------+------+
| id | name | id | name |
+------+----------+------+------+
| 1 | 教室_1 | 1 | zx |
| 2 | 教室_2 | 2 | wl |
| 3 | 教室_3 | 3 | zy |
| 1 | 教室_1 | 1 | zxy |
| 2 | 教室_2 | 2 | fd |
| 3 | 教室_3 | 3 | hj |
| 4 | NULL | 4 | ds |
| 1 | 教室_1 | 1 | NULL |
+------+----------+------+------+
# 总结：只保留两个表有关联的数据
左连接
优先显示左表全部记录
# 关键字：left join on
# 语法：from 左表 left join 右表 on 左表.关联字段=右表.关联字段
#执行
select class.id,class.name,student.name from class left join student on class.id=student.id;
+------+----------+------+
| id | name | name |
+------+----------+------+
| 1 | 教室_1 | zx |
| 2 | 教室_2 | wl |
| 3 | 教室_3 | zy |
| 1 | 教室_1 | zxy |
| 2 | 教室_2 | fd |
| 3 | 教室_3 | hj |
| 4 | NULL | ds |
| 1 | 教室_1 | NULL |
| NULL | NULL | NULL |
+------+----------+------+
# 总结：保留左表的全部数据，右表有对应数据直接连表显示，没有对应关系空填充
右连接
优先显示右表全部记录
# 关键字：right join on
# 语法：from A表 right join B表 on A表.关联字段=B表关联字段
#执行
select class.id,student.name,class.name from class right join student on class.id=student.id;
+------+------+----------+
| id | name | name |
+------+------+----------+
| 1 | zx | 教室_1 |
| 1 | zxy | 教室_1 |
| 1 | NULL | 教室_1 |
| 2 | wl | 教室_2 |
| 2 | fd | 教室_2 |
| 3 | zy | 教室_3 |
| 3 | hj | 教室_3 |
| 4 | ds | NULL |
| NULL | NULL | NULL |
+------+------+----------+
# 总结：保留右表的全部数据，左表有对应数据直接连表显示，没有对应关系空填充
左右可以相互转化
更换一下左右表的位置，相对应更换左右连接关键字，结果相同
全连接
全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
#执行
select class.id,student.name,class.name from class left join student on class.id=student.id
UNION
select class.id,student.name,class.name from class right join student on class.id=student.id
order by id;
+------+------+----------+
| id | name | name |
+------+------+----------+
| NULL | NULL | NULL |
| 1 | zxy | 教室_1 |
| 1 | NULL | 教室_1 |
| 1 | zx | 教室_1 |
| 2 | fd | 教室_2 |
| 2 | wl | 教室_2 |
| 3 | hj | 教室_3 |
| 3 | zy | 教室_3 |
| 4 | ds | NULL |
+------+------+----------+
# 总结：左表右表数据都被保留，彼此有对应关系正常显示，彼此没有对应关系均空填充对方
# 注意 union与union all的区别：union会去掉相同的纪录
多对多
相当于在一对多的基础上，多建立一次连接
##源数据
#人物表
zx
+----+------+
| id | name |
+----+------+
| 1 | zx |
| 2 | wl |
| 3 | zy |
| 4 | zxy |
+----+------+
#关系表
zx_zy
+----+-------+-------+
| id | zx_id | zy_id |
+----+-------+-------+
| 1 | 1 | 1 |
| 2 | 1 | 3 |
| 3 | 2 | 2 |
| 4 | 2 | 4 |
| 5 | 3 | 1 |
| 6 | 3 | 3 |
| 7 | 4 | 5 |
| 8 | 4 | 2 |
| 9 | 4 | 3 |
+----+-------+-------+
#技能表
zy
+----+-----------+
| id | name |
+----+-----------+
| 1 | 吃饭 |
| 2 | 睡觉 |
| 3 | 打代码 |
| 4 | 写作业 |
| 5 | 上厕所 |
+----+-----------+
查询1
#执行
以关系表为中心，进行两次左连接
select zx_zy.id,zx.name,zy.name from zx_zy LEFT JOIN zx on zx.id=zx_zy.zx_id LEFT JOIN zy on zy.id=zx_zy.zy_id
+----+------+-----------+
| id | name | name |
+----+------+-----------+
| 1 | zx | 吃饭 |
| 2 | zx | 打代码 |
| 3 | wl | 睡觉 |
| 4 | wl | 写作业 |
| 5 | zy | 吃饭 |
| 6 | zy | 打代码 |
| 7 | zxy | 上厕所 |
| 8 | zxy | 睡觉 |
| 9 | zxy | 打代码 |
+----+------+-----------+
查询2
#执行
任意表，进行两次内连接
select zx_zy.id,zx.name,zy.name from zx join zx_zy on zx_zy.zx_id=zx.id join zy on zx_zy.zy_id=zy.id
+----+------+-----------+
| id | name | name |
+----+------+-----------+
| 1 | zx | 吃饭 |
| 2 | zx | 打代码 |
| 3 | wl | 睡觉 |
| 4 | wl | 写作业 |
| 5 | zy | 吃饭 |
| 6 | zy | 打代码 |
| 7 | zxy | 上厕所 |
| 8 | zxy | 睡觉 |
| 9 | zxy | 打代码 |
+----+------+-----------+
子查询
简单来说就是一个查询的数据当做另一个查询的查询条件
要注意的是子查询的数据一般有好多，一般用in
# 增：insert into 表 select子查询
# 删：delete from 表 条件是select子查询(表不能与delete表相同)
# 查：select 字段 from 表 条件是select子查询
# 改：update 表 set 字段=值 条件是select子查询(表不能与update表相同)
简单案例-子查询
##元数据
t1
+------+------+
| x | y |
+------+------+
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 1 | 2 |
+------+------+
t3
+------+------+
| z1 | z2 |
+------+------+
| 1 | zx |
| 2 | zy |
| 9 | zx |
| 5 | zy |
| 6 | zx |
| 7 | zy |
+------+------+
##需求根据t1的x，删除t3，与t1相同的z1数据
#执行
select * from t3;
+------+------+
| z1 | z2 |
+------+------+
| 9 | zx |
| 5 | zy |
| 6 | zx |
| 7 | zy |
+------+------+
