MySQL数据库-第五章：MySQL查询语句

上集回顾:

1.DSQL - SELECT

1.1 不用子句

select @@port;
select database();
select 1+1  from dual;

1.2 配合子句

FROM  -->  WHERE  GROUP BY   HAVING   ORDER BY LIMIT
select a,count(b) from t group by a;

面试题: 执行过程?

排序
去重复
函数聚合

面试题:

SQL_MODE(5.7+)=ONLY_FULL_GROUP_BY
mysql> select name,district,count(name) from world.city where countrycode='CHN' group by district;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'world.city.Name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql>

select后的列,不在group by子句后
也不在聚合函数中出现的.

mysql> select group_concat(name),district,count(name) from world.city where countrycode='CHN' group by district;

经典的案例(性能低下的例子)

limit 500w 100    ---->   >  and  <

====================================

1. 多表连接查询 ****

1.1 介绍

内连接 inner join
外连接 left join right join
笛卡尔

1.2 作用

聚合多张表数据,实现查询需求
查询人口数小于100人城市名,国家名,国土面积?

1.3 多表连接的语法

1.3.1 内连接(交集)

FROM A 
INNER JOIN B 
ON  A.x=B.y

1.3.2 外连接

FROM A 
LEFT JOIN B 
ON  A.x=B.y

FROM A 
RIGHT JOIN B 
ON  A.x=B.y

1.3.3 笛卡尔乘积

FROM A 
JOIN B

1.4 多表连接例子

-- 1. 查询人口数小于100人城市名,国家名,国土面积?

-- 套路 : 1. 找关联表 2. 找关系列

USE world
SHOW TABLES;
DESC city;
DESC country;

SELECT 
city.name,
country.name, 
country.SurfaceArea,
city.`Population`
FROM city
JOIN country
ON city.`CountryCode`=country.`Code`
WHERE city.`Population`<100

-- 2. 统计查询每位学员的平均分

SELECT student.xid ,
student.xname,
AVG(score.score)   
FROM  student
JOIN score 
ON student.xid=score.xid
GROUP BY student.xid,student.xname

-- 3. 统计每位学员学习了几门课

SELECT student.xid ,
student.xname,
COUNT(score.score)   
FROM  student
JOIN score 
ON student.xid=score.xid
GROUP BY student.xid,student.xname

-- 4. 查询每位老师教的课程名和价格

SELECT teacher.tname,course.`cname`,course.`cprice`
FROM teacher 
JOIN course
ON  teacher.`tid`=course.`tid`;

-- 5. 每位老师教的学生的个数和姓名列表

SELECT 
teacher.tname,
COUNT(student.xid),
GROUP_CONCAT(student.xname)
FROM student 
JOIN score 
ON student.xid=score.xid 
JOIN course
ON score.cid=course.cid
JOIN teacher 
ON course.tid=teacher.tid
GROUP BY teacher.tid,teacher.tname;

综合练习题:

查询oldguo老师教的学生名
查询oldguo所教课程的平均分数
每位老师所教课程的平均分,并按平均分排序
查询白龙马,学习的课程名称有哪些?
统计每位老师赚了多少钱?
统计每门课程学习的人数.
查询oldboy老师教的学生不及格的学生名单
统计每位老师不及格学生名单
每位老师所教课程的平均分,并按平均分排序
查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
查询平均成绩大于60分的同学的学号和平均成绩；

扩展:

统计各位老师,所教课程的及格率
统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表

-- 1. 查询人口数小于100人城市名,国家名,国土面积?

-- 套路 : 1. 找关联表 2. 找关系列

USE world
SHOW TABLES;
DESC city;
DESC country;

SELECT 
city.name,
country.name, 
country.SurfaceArea,
city.`Population`
FROM city
JOIN country
ON city.`CountryCode`=country.`Code`
WHERE city.`Population`<100

-- 2. 统计查询每位学员的平均分

SELECT student.xid ,
student.xname,
AVG(score.score)   
FROM  student
JOIN score 
ON student.xid=score.xid
GROUP BY student.xid,student.xname

-- 3. 统计每位学员学习了几门课

SELECT student.xid ,
student.xname,
COUNT(score.score)   
FROM  student
JOIN score 
ON student.xid=score.xid
GROUP BY student.xid,student.xname

-- 4. 查询每位老师教的课程名和价格

SELECT teacher.tname,course.`cname`,course.`cprice`
FROM teacher 
JOIN course
ON  teacher.`tid`=course.`tid`;

-- 5. 每位老师教的学生的个数和姓名列表

SELECT 
teacher.tname,
COUNT(student.xid),
GROUP_CONCAT(student.xname)
FROM student 
JOIN score 
ON student.xid=score.xid 
JOIN course
ON score.cid=course.cid
JOIN teacher 
ON course.tid=teacher.tid
GROUP BY teacher.tid,teacher.tname;

-- 综合练习题:

-- 1. 查询oldguo老师教的学生名.

SELECT 
CONCAT(teacher.tname,"_",teacher.tid) AS "教师名",
GROUP_CONCAT(student.`xname`) AS "学生列表"
FROM teacher
JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`  
JOIN student 
ON score.`xid`=student.`xid`
WHERE teacher.tname='oldguo'
GROUP BY teacher.tid,teacher.tname;

-- 2. 查询oldguo所教课程的平均分数.

SELECT 
CONCAT(teacher.tname,"_",teacher.tid) AS "教师名",
course.cname AS "课程名",
AVG(score.score) AS "平均分"
FROM teacher
JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`  
WHERE teacher.tname='oldguo'
GROUP BY teacher.tid,teacher.tname,course.cname;

-- 3. 每位老师所教课程的平均分,并按平均分排序.

SELECT 
CONCAT(teacher.tname,"_",teacher.tid) AS "教师名",
course.cname AS "课程名",
AVG(score.score) AS "平均分"
FROM teacher
JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`  
GROUP BY teacher.tid,teacher.tname,course.cname;

-- 4. 查询白龙马,学习的课程名称有哪些.

SELECT 
CONCAT(student.xname,"_",student.`xid`) AS "学生姓名",
GROUP_CONCAT(course.`cname`) AS "课程列表"
FROM student 
JOIN score
ON student.xid = score.xid 
JOIN course 
ON score.`cid`=course.`cid`
WHERE student.xname='白龙马'
GROUP BY student.xid,student.xname;

-- 5. 统计每位老师总收入

SELECT teacher.`tname`,COUNT(score.`xid`),SUM(course.`cprice`)
FROM teacher
JOIN course
ON teacher.`tid` = course.`tid`
JOIN score
ON course.`cid` = score.`cid`
GROUP BY teacher.`tid`,teacher.`tname`;

-- 5.1 统计每位老师,每门课程,分别的收入情况

SELECT 
CONCAT(teacher.`tname`,"_",teacher.`tid`) AS "讲师名",
course.`cprice`*COUNT(score.xid) AS "课程收入"
FROM teacher
JOIN course
ON teacher.`tid` = course.`tid`
JOIN score
ON course.`cid` = score.`cid`
GROUP BY teacher.`tid`,teacher.`tname` , course.`cid`;

-- 6. 统计每门课程学习的人数.

SELECT course.`cname`,COUNT(score.`xid`)
FROM course 
JOIN score
ON course.`cid`=score.`cid`
GROUP BY course.`cname`;

-- 7. 查询oldboy老师教的学生不及格的学生名单.

SELECT 
CONCAT(teacher.tname,"_",teacher.tid) AS "教师名"
,GROUP_CONCAT(CONCAT(student.xname,":",score.score))
FROM teacher
JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`  
JOIN student 
ON score.`xid`=student.`xid`
WHERE teacher.`tname`='oldboy' AND score.`score`<60
GROUP BY teacher.tid,teacher.tname;

-- 8. 统计每位老师不及格学生名单.

SELECT 
CONCAT(teacher.tname,"_",teacher.tid) AS "教师名"
,GROUP_CONCAT(CONCAT(student.xname,":",score.score))
FROM teacher
JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`  
JOIN student 
ON score.`xid`=student.`xid`
WHERE score.`score`<60
GROUP BY teacher.tid,teacher.tname;

-- 9. 每位老师所教课程的平均分,并按平均分排序.

SELECT 
CONCAT(teacher.tname,"_",teacher.tid) AS "教师名",
course.`cname`,
AVG(score.`score`)
FROM teacher
JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`  
GROUP BY teacher.tid,teacher.tname,course.`cid`;

-- 10. 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分.

SELECT course.`cid`,MAX(score.`score`),MIN(score.`score`)
FROM course
JOIN score
ON course.`cid`=score.`cid` 
GROUP BY course.`cid`;

-- 11. 查询平均成绩大于60分的同学的学号和平均成绩.

SELECT 
CONCAT(student.`xname`,"_",student.`xid`),
AVG(score.`score`)
FROM student
JOIN score
ON student.`xid`=score.`xid`
GROUP BY student.`xname`,student.`xid`
HAVING AVG(score.`score`)>60
ORDER BY AVG(score.`score`) DESC ;

-- 扩展:

-- 12. 统计各位老师,所教课程的及格率

SELECT 
CONCAT(teacher.tname,"_",teacher.tid) AS "教师名",
course.`cname`,
CONCAT(COUNT(CASE WHEN score.score>60 THEN 1  END)/COUNT(score.xid)*100,"%")
FROM teacher
JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`  
GROUP BY teacher.tid,teacher.tname,course.`cid`;

-- 13. 统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表

SELECT 
course.`cname` AS 课程名称,
GROUP_CONCAT(CASE WHEN score.`score` >= 85 THEN student.xname END )  AS "优秀",
GROUP_CONCAT(CASE WHEN score.`score` >=70 AND score.`score` < 85  THEN student.xname  END) AS "良好",
GROUP_CONCAT(CASE WHEN score.`score` >=60  AND score.`score` <70 THEN  student.xname  END  )AS "一般",
GROUP_CONCAT(CASE WHEN score.`score` <60  THEN  student.xname END  ) AS "不及格"
FROM student 
JOIN score
ON student.xid = score.xid 
JOIN course 
ON score.`cid`=course.`cid`
GROUP BY course.`cid`;

1.5 left/right join 外连接应用

一般应用在强制驱动表时,强制小结果集驱动大表
-- 生产中可以使用left join 强制驱动表.尽量减少next loop的出现.
-- 为什么要强制? inner join 优化器自动选择, 按照索引选择的几率较大

select a.name,b.telnum from a left join b on a.id=b.id where a.age>=18

1.6 补充别名的应用 .

1.6.1 列别名

SELECT 
course.`cname` AS 课程名称,
GROUP_CONCAT(CASE WHEN score.`score` >= 85 THEN student.xname END )  AS "优秀",
GROUP_CONCAT(CASE WHEN score.`score` >=70 AND score.`score` < 85  THEN student.xname  END) AS "良好",
GROUP_CONCAT(CASE WHEN score.`score` >=60  AND score.`score` <70 THEN  student.xname  END  )AS "一般",
GROUP_CONCAT(CASE WHEN score.`score` <60  THEN  student.xname END  ) AS "不及格"
FROM student 
JOIN score
ON student.xid = score.xid 
JOIN course 
ON score.`cid`=course.`cid`
GROUP BY course.`cid`;

说明: 1. 为了显示的好看. 2. 可以在 having 或 order by 子句中调用

1.6.2 表别名

SELECT 
CONCAT(te.tname,"_",te.tid) AS "教师名"
,GROUP_CONCAT(CONCAT(st.xname,":",sc.score))
FROM teacher as te 
JOIN course as  co 
ON te.`tid`=co.`tid`
JOIN score as sc 
ON co.`cid`=sc.`cid`  
JOIN student  as st
ON sc.`xid`=st.`xid`
WHERE sc.`score`<60
GROUP BY te.tid,te.tname;

SELECT 
CONCAT(te.tname,"_",te.tid) AS "教师名"
,GROUP_CONCAT(CONCAT(st.xname,":",sc.score))
FROM teacher AS te 
JOIN course AS  co 
ON te.`tid`=co.`tid`
JOIN score AS sc 
ON co.`cid`=sc.`cid`  
JOIN student  AS st
ON sc.`xid`=st.`xid`
WHERE sc.`score`<60
GROUP BY te.tid,te.tname;

2. show 语句的列表介绍 ***

-- 查看所有的库
show databases;

-- 查看当前库下的所有表
show tables;
show tables from world;

-- 查看当前并发会话信息
show processlist;
show  full processlist;

-- 查看数据库支持的权限
show privileges;

-- 查看数据库参数信息
show variables 
show variables like '%trx%';

-- 查看字符集&校对规则 
show  charset;
show  collation;

-- 查看建库&建表语句
show create database world;
show create table world.city;

-- 查看用户权限 
show grants for root@'localhost';

-- 查看支持的存储引擎
show engines;

-- 查询表中索引信息
show index from world.city;

-- 查看数据库当前状态信息
show status;
show status like '%lock%';

-- 查看InnoDB引擎相关的状态信息(内存,事务,锁,线程...)
show engine innodb status\G

-- 查看二进制日志相关信息
show binary logs ;
show master status; 
show binlog events in 'xxxx';

-- 查看主从复制相关信息
show relaylog events in 'xxxx';
show slave status \G

mysql> help show ;

3. Information_schema 统计信息库

3.1 介绍:

视图 ?

1. 安全 : 只允许查询,不知道操作的是什么对象.

2. 方便 : 只需要简单的select语句即可使用.

3.2 作用:

1. 方便做数据库资产统计

库\表 :
个数
数据量(大小,行数)
每张表的数据字典信息

2. 获取到Server层状态信息

3. 获取到InnoDB引擎层的状态信息

3.3 应用举例:

TABLES : 
TABLE_SCHEMA  : 表所在的库 
TABLE_NAME    : 表名
ENGINE        : 表的引擎      
TABLE_ROWS    : 表的行数
AVG_ROW_LENGTH: 平均行长度(字节) 
INDEX_LENGTH  : 索引占用长度(字节) 
TABLE_COMMENT : 表注释

-- 例子:

-- 1. 简单查询体验TABLES信息

SELECT * FROM TABLES;

-- 2. 所有业务库和表的名字.

SELECT table_schema , table_name 
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql');

-- 3. 统计每个业务库,表的个数和列表

SELECT table_schema , COUNT(table_name),GROUP_CONCAT(table_name) 
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql')
GROUP BY table_schema;

-- 4. 统计业务数据库的总数据量

SELECT SUM(table_rows * AVG_ROW_LENGTH+index_length)/1024 AS total_KB
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql');

-- 5. 每个业务库分别统计数据量

SELECT table_schema,SUM(table_rows * AVG_ROW_LENGTH+index_length)/1024 AS total_KB
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql')
GROUP BY table_schema
ORDER  BY total_KB DESC ;

-- 6. top 3 数据量大的表

SELECT table_schema,table_name,(table_rows * AVG_ROW_LENGTH+index_length)/1024 AS table_kb
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql')
ORDER BY  table_kb DESC 
LIMIT 3;

-- 7. 查询所有非INNODB的表

SELECT table_schema,table_name ,ENGINE FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql')
AND ENGINE <> 'innodb';

-- 8. 查询所有非INNODB的表 , 并且提出修改建议

SELECT 
table_schema,
table_name ,
ENGINE ,
CONCAT("alter table ",table_schema,".",table_name," engine=innodb;") AS "修改建议"
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql')
AND ENGINE <> 'innodb';

-- 9. 所有业务库和表的名字,并且生成备份语句

SELECT 
table_schema , 
table_name ,
CONCAT("mysqldump ",table_schema," ",table_name," > /bak/",table_schema,"_",table_name,".sql") AS "备份"
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql');

MySQL数据库-第五章：MySQL查询语句

上集回顾:

1.DSQL - SELECT

1.1 不用子句

1.2 配合子句

面试题: 执行过程?

面试题:

经典的案例(性能低下的例子)

====================================

1. 多表连接查询 ****

1.1 介绍

1.2 作用

1.3 多表连接的语法

1.3.1 内连接(交集)

1.3.2 外连接

1.3.3 笛卡尔乘积

1.4 多表连接例子

-- 1. 查询人口数小于100人城市名,国家名,国土面积?

-- 2. 统计查询每位学员的平均分

-- 3. 统计每位学员学习了几门课

-- 4. 查询每位老师教的课程名和价格

-- 5. 每位老师教的学生的个数和姓名列表

综合练习题:

扩展:

-- 1. 查询人口数小于100人城市名,国家名,国土面积?

-- 2. 统计查询每位学员的平均分

-- 3. 统计每位学员学习了几门课

-- 4. 查询每位老师教的课程名和价格

-- 5. 每位老师教的学生的个数和姓名列表

-- 综合练习题:

-- 1. 查询oldguo老师教的学生名.

-- 2. 查询oldguo所教课程的平均分数.

-- 3. 每位老师所教课程的平均分,并按平均分排序.

-- 4. 查询白龙马,学习的课程名称有哪些.

-- 5. 统计每位老师总收入

-- 5.1 统计每位老师,每门课程,分别的收入情况

-- 6. 统计每门课程学习的人数.

-- 7. 查询oldboy老师教的学生不及格的学生名单.

-- 8. 统计每位老师不及格学生名单.

-- 9. 每位老师所教课程的平均分,并按平均分排序.

-- 10. 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分.

-- 11. 查询平均成绩大于60分的同学的学号和平均成绩.

-- 扩展:

-- 12. 统计各位老师,所教课程的及格率

-- 13. 统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表

1.5 left/right join 外连接应用

1.6 补充 别名的应用 .

1.6.1 列别名

说明: 1. 为了显示的好看. 2. 可以在 having 或 order by 子句中调用

1.6.2 表别名

2. show 语句的列表介绍 ***

3. Information_schema 统计信息库

3.1 介绍:

1. 安全 : 只允许查询,不知道操作的是什么对象.

2. 方便 : 只需要简单的select语句即可使用.

3.2 作用:

1. 方便做数据库资产统计

2. 获取到Server层状态信息

3. 获取到InnoDB引擎层的状态信息

3.3 应用举例:

-- 1. 简单查询体验TABLES信息

-- 2. 所有业务库和表的名字.

-- 3. 统计每个业务库,表的个数和列表

-- 4. 统计业务数据库的总数据量

-- 5. 每个业务库分别统计数据量

-- 6. top 3 数据量大的表

-- 7. 查询所有非INNODB的表

-- 8. 查询所有非INNODB的表 , 并且提出修改建议

-- 9. 所有业务库和表的名字,并且生成备份语句

1.6 补充别名的应用 .