004 MySQL高级查询
目录
sql注入的根源
开发：
1、永远不要相信前端。不是说不要相信前端工程师，而是说不要相信前端提交的数据
后端接口对前端提交的数据没有做任何的校验 ，出现注入
注入的存在就是开发没有做任何处理，直接把前端的值拼到select里面去了
2、把数据库的错误返回给前端，或者返回空数据给前端
错误日志中判断后台数据库的名称 版本 库 表 引擎信息
数据库不同职业的关注点
1、开发人员 对数据库的查询和插入的效率，索引，并发优化，包括库，表的创建，在MVC架构中提供数据接口，返回给前端数据。
2、运维/DBA 支持数据库运行稳定，数据备份。主要是主从备份，高可用，分库分表。监控慢查询等等。
备份工具脚本信息，数据库高可用的配置信息，数据库的备份数据，文件存储系统，从库信息，ansible saltstack剧本之类的
3、安全人员 开发人员和DBA和运维人员都会暴露出数据库的信息，针对这些信息加以利用。
主要从 sql注入点 ，数据库主机，配置，账户密码 等方面入手。
样式： 解决的对象的原子性，把一个表拆到不能再拆为止，最大程度的保持简单。
高级查询
关键字
示例表
mysql> SELECT * FROM school.student;
+-----+--------+--------+-----+----------+
| sid | sname | gender | age | class_id |
+-----+--------+--------+-----+----------+
| 1 | 钢弹 | 男 | 10 | 1 |
| 2 | 铁蛋 | 男 | 11 | 2 |
| 3 | 小丽 | 女 | 23 | 3 |
| 4 | 小红 | 女 | 15 | 1 |
| 5 | 明明 | 男 | 15 | 2 |
| 6 | 张笑话 | 男 | 11 | 3 |
| 7 | 张笑1 | 男 | 10 | 3 |
| 8 | 张笑话 | 男 | 9 | 3 |
| 9 | 张笑话 | 男 | 8 | 3 |
| 10 | 钢弹 | 男 | 19 | 2 |
| 11 | 刚弹 | 男 | 22 | 3 |
+-----+--------+--------+-----+----------+
11 rows in set (0.09 sec)
1 distinct 去重的功能 单列去重
只能适用于select 语句 和count组合使用 count可以过滤掉null
mysql> SELECT DISTINCT class_id FROM school.student;
+----------+
| class_id |
+----------+
| 1 |
| 2 |
| 3 |
+----------+
3 rows in set (0.07 sec)
--》有空值 ，计算有几个班级的时候，null也会被计算
mysql> SELECT DISTINCT NAME FROM school.123;
+------+
| NAME |
+------+
| 张三 |
| 李四 |
| |
+------+
3 rows in set (0.08 sec)
# 使用count 过滤掉null
mysql> SELECT COUNT(DISTINCT NAME) FROM school.123;
+----------------------+
| COUNT(DISTINCT NAME) |
+----------------------+
| 3 |
+----------------------+
1 row in set (0.06 sec)
1、group by 分组
group by
作用1： 可以对一个字段的数据进行去重，只显示唯一值。
1.在示例表中： 显示每个班级的编号，不允许重复
mysql> SELECT class_id FROM student GROUP BY class_id ;
+----------+
| class_id |
+----------+
| 1 |
| 2 |
| 3 |
+----------+
3 rows in set (0.07 sec)
注意 分组的字段，在显示的时候分组字段显示的才是真实的，其他的字段不一定是正确的。
2、数据的最大值max 最小值 min 平均值 avg 之和sum，行数之和 count
count 统计行数
统计每个年级的学生的人数
（思路就是找每个年级的class_id, 把相同的class_id的年级的行作为一个分组。把相同年级的用户的的行数相加就可以了）
# 查找每个部门的人数
SELECT departid,COUNT(departid) FROM school.info GROUP BY departid;
mysql> SELECT departid,COUNT(departid) FROM school.info GROUP BY departid;
+----------+-----------------+
| departid | COUNT(departid) |
+----------+-----------------+
| 1 | 4 |
| 2 | 3 |
| 3 | 1 |
+----------+-----------------+
3 ROWS IN SET (0.06 sec)
max 最大值
以班级为划分 每个班级最大的年龄的学生
（思路 把每个年级的人数是全部找出来，然后比较所有人的年龄，显示年龄）
SELECT class_id,max(age) FROM student WHERE class_id=1
SELECT class_id,max(age) FROM student WHERE class_id=2
SELECT class_id,max(age) FROM student WHERE class_id=3
整合了where class_id 的全部条件
SELECT class_id,max(age) FROM school.student GROUP BY class_id
以班级为划分 找到 每个班级最小的年龄的学生
min 最小值
mysql> SELECT class_id,min(age) FROM school.student GROUP BY class_id;
+----------+----------+
| class_id | min(age) |
+----------+----------+
| 1 | 10 |
| 2 | 11 |
| 3 | 8 |
+----------+----------+
3 rows in set (0.10 sec)
sum 求和
以班级为划分 找到每个班级年龄总和
SELECT SUM(age) FROM student GROUP BY class_id
# 对相同class_id的其他字段进行 数据相加
avg 平均值
SELECT AVG(age) FROM student GROUP BY class_id
# 对相同class_id的其他字段进行 数据相加然后除以行数获取平均值
SELECT age,COUNT(age) FROM school.info GROUP BY age;
# 在聚合函数中用count 统计相同组的个数
mysql> SELECT age,COUNT(age) FROM school.info GROUP BY age;
+-----+------------+
| age | COUNT(age) |
+-----+------------+
| 30 | 1 |
| 40 | 1 |
| 50 | 4 |
| 80 | 1 |
| 90 | 1 |
+-----+------------+
5 ROWS IN SET (0.07 sec)
3、having 对数据 整合后的排序
# 查找每个部门的人数，大于2个的显示
SELECT departid,COUNT(departid) FROM school.info GROUP BY departid HAVING COUNT(departid) >2;
HAVING 子句
在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。
语法：SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value；
例如:SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000;
5、TOP 规定返回的数据条数
TOP 子句
TOP 子句用于规定要返回的记录的数目。
对于拥有数千条记录的大型表来说，TOP 子句是非常有用的。
6、数据的精度取值
round 小数精度的取值
mysql> SELECT ROUND(1.298, 1);
-> 1.3
mysql> SELECT ROUND(1.298, 0);
-> 1
7 null 和‘’ 的判断
空判断
NULL和 '' 是不相同的
判断空使用 is NULL
select * from stu where card='' #找出card为'' 的数据
select * from stu where not card='' #找出card有效的数据
select * from stu where card is NUll # card为NULL
执行的顺序join on > where > group by > having > order by >limit
连表 条件 分组 组内条件 排序 分页显示
1 跨表查询1
了解
内链接 传统链接 集合的差集 满足条件的
select t1.name ,t2.choose from t1,t2
where t1.id = t2.userid
and ti.id >50;
# 查什么，写什么 ，在那个表，写那个表，
# 写两个表的相同的字段，进行关联，
# 写判断任意表的任意字段的的判断条件。
了解
两个表的中必须有相同的字段才能自动关联
select t1.name ,t2.student from t1 natural join t2
where and ti.id >50;
连表查询 规范样式
理解了笛卡尔乘积 交叉链接表的意思名。第一个表的全部行和第二张表的 每一行进行关联，但是总有一条以上是关联正确的。
2.full join 全外连接，在mysql中不支持全外连接，可以使用 union all 进行关联，如果想要去重，去掉all 即可。
mysql> SELECT * FROM student,class ;
+-----+--------+--------+-----+----------+-----+----------+
| sid | sname | gender | age | class_id | cid | caption |
+-----+--------+--------+-----+----------+-----+----------+
| 1 | 钢弹 | 男 | 10 | 1 | 1 | 三年二班 |
| 1 | 钢弹 | 男 | 10 | 1 | 2 | 一年三班 |
| 1 | 钢弹 | 男 | 10 | 1 | 3 | 三年一班 |
| 2 | 铁蛋 | 男 | 11 | 2 | 1 | 三年二班 |
| 2 | 铁蛋 | 男 | 11 | 2 | 2 | 一年三班 |
| 2 | 铁蛋 | 男 | 11 | 2 | 3 | 三年一班 |
| 3 | 小丽 | 女 | 23 | 3 | 1 | 三年二班 |
| 3 | 小丽 | 女 | 23 | 3 | 2 | 一年三班 |
| 3 | 小丽 | 女 | 23 | 3 | 3 | 三年一班 |
1、物理表
物理表join 物理表join 物理表，表足够的多的话，可以无限join下去。
2、虚拟表
物理表和物理表关联后 as 一个虚拟表名，这个虚拟表可以和物理表进行关联，也可以和另外的虚拟表进行关联。
3、子查询
关联表内的子查询，获取一个班级中得分最高的同学的姓名和班级，分数这个字段的最高分的具体值，是不知道的，
select t1.name,t1.class_name t1.zuigaofen from student t1 where t1.zuigaofen = ( selecet man(fenshu) from csroce );
1、这样判断字段的条件在不知道具体值的情况下，可以使用子查询的语句进行 判断，
2、使用子查询进行 in 或者 not in 一个字段的值， 进行逻辑的判断。
2 跨表查询2
企业级的跨表查询
select t1.user_name ,t2.student_name t4.school from t1
join t2 # 关联一张表
on t2.userid=t1.id # 写两张表之间相同的字段，基于的条件
# 再关联一张表 也就说多张表关联 书写方便了
join t4
on t4.sid = t1.id
where ti.id >50; # 判断条件
3跨表查询 left right
外链接 左链接 右链接
select t1.name ,t2.choose from t1
left join t2 # 只显示左侧表满足条件的
on t1.id = t2.userid
and ti.id >50;
select t1.name ,t2.choose from t1
right join t2 # 只显示右 侧表满足条件的
on t1.id = t2.userid
and ti.id >50;
4跨表查询 union
union 改写 in 和 or 单表中不同条件的查询
union查询 SQL UNION 操作符合并两个或多个 SELECT 语句的结果
UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。
UNION 自动去重
UNION ALL 显示源数据
SELECT info.`name` FROM info UNION ALL SELECT depart.`title` FROM depart
5 跨表查询 样式
-- inner join 内连接 可以简写join
显示集合交集的部分
-- 显示该集合的全部，左右连接就是外连接，必须指定主表， outer JOIN 也可以简写成 join
SELECT info.`name`,info.`departid`,depart.title FROM
info
LEFT JOIN depart
ON depart.`id` = info.`departid`
-- outer join
SELECT info.`name`,info.`departid`,depart.title FROM
info
RIGHT OUTER JOIN depart
ON depart.`id` = info.`departid`
-- 交叉链接 (笛卡尔积)
行数的乘积，囊括所有的链接
mysql> SELECT * FROM sco;
+-------+---------+-------+
| stuid | sconame | score |
+-------+---------+-------+
| 1 | linux | 59 |
| 2 | python | 60 |
| 3 | bigdata | 50 |
| 4 | java | 40 |
| 5 | go | 0 |
| 2 | linux | 70 |
| 3 | linux | 70 |
| 5 | python | 50 |
+-------+---------+-------+
8 rows in set (0.04 sec)
mysql> SELECT * FROM student;
+----+-------+
| id | name |
+----+-------+
| 1 | 张3 |
| 2 | 李四 |
| 3 | wang5 |
| 4 | 赵7 |
| 5 | dfgf |
+----+-------+
5 rows in set (0.04 sec)
1 跨表查询 人员 学科 成绩
SELECT student.`name` ,sco.`sconame` ,sco.`score` FROM
student
JOIN sco
ON student.`id` = sco.`stuid`
WHERE sco.`sconame`='linux';
# 统计每个学员报名课程的总数
SELECT student.`name` ,COUNT(sco.sconame) FROM
student
LEFT JOIN sco
ON student.`id` = sco.`stuid`
GROUP BY sco.stuid;
-- 统计一共多少个学科
SELECT COUNT( DISTINCT sco.`sconame`) FROM sco;
-- 统计不及格的学生信息和成绩
SELECT student.`name` ,sco.`sconame`,sco.`score` FROM student
JOIN sco
ON sco.`stuid` = student.`id`
WHERE sco.sconame = 'linux'
AND sco.`score`<60;
6 跨表查询 -子查询
开发 - 内链接
select info.name ,info.age ,(select title from depart where depart.id = info.departid) as title from info;
+--------+-----+--------+
| name | age | title |
+--------+-----+--------+
| 张三 | 22 | 开发 |
| 李四 | 30 | 运营 |
| 王五 | 40 | 销售 |
| 张7 | 50 | 运营 |
+--------+-----+--------+
4 rows in set (0.07 sec)
7 跨表查询 -别名
# 别名设置 规范
SELECT student.`name` ,sco.`sconame`,sco.`score` FROM student as b
JOIN sco as a
ON a.`stuid` = b.`id`
WHERE a.sconame = 'linux'
AND a.`score`<60;
8 跨表查询 -子查询+ 逻辑判断
mysql> SELECT * FROM school.depart;
+----+-------+
| id | title |
+----+-------+
| 1 | 开发 |
| 2 | 运营 |
| 3 | 销售 |
| 4 | NULL |
+----+-------+
4 rows in set (0.03 sec)
mysql> SELECT * FROM school.info;
+----+------+-------+-----+----------+
| id | name | email | age | departid |
+----+------+-------+-----+----------+
| 1 | 张三 | 123 | 22 | 1 |
| 2 | 李四 | 22 | 30 | 2 |
| 3 | 王五 | dfd | 40 | 3 |
| 4 | 张7 | dfs | 50 | 2 |
+----+------+-------+-----+----------+
4 rows in set (0.04 sec)
=======================================
# as别名的效果 为结果加一个新的字段 666是值，num是字段名
mysql> select id , 666 as num from info;
+----+-----+
| id | num |
+----+-----+
| 1 | 666 |
| 2 | 666 |
| 3 | 666 |
| 4 | 666 |
+----+-----+
4 rows in set (0.05 sec)
# 对某一列的数据进行别样的显示
mysql> select info.name ,info.age,case when age= 50 then "big_Y" end "small_Y" from info;
+--------+-----+--------+
| name | age | 年龄 |
+--------+-----+--------+
| 张三 | 22 | NULL |
| 李四 | 30 | NULL |
| 王五 | 40 | NULL |
| 张7 | 50 | 大龄 |
+--------+-----+--------+
4 rows in set (0.07 sec)
mysql> select info.name ,info.age,case when age>49 then "big_Y" else "small_Y" end "age_info" from info;
+--------+-----+-----------+
| name | age | 年龄 |
+--------+-----+-----------+
| 张三 | 22 | 年轻人 |
| 李四 | 30 | 年轻人 |
| 王五 | 40 | 年轻人 |
| 张7 | 50 | 大龄 |
+--------+-----+-----------+
4 rows in set (0.12 sec)
# 对于年龄这个字段名本身是没有的，只是对age字段的数据，进行了数据的加工
case 关键字
when 相当于 if
then 相玉于 if 缩进内执行的代码
else 否则 显示什么
end 设置字段的名称
浙公网安备 33010602011771号