- 多表查询
1.0 准备学习
student ：学生表
sno： 学号
sname：学生姓名
sage： 学生年龄
ssex： 学生性别
teacher ：教师表
tno： 教师编号
tname： 教师名字
course ：课程表
cno： 课程编号
cname：课程名字
tno： 教师编号
sc ：成绩表
sno： 学号
cno： 课程编号
score：成绩
1.1 作用
需要获取的数据在多张表中存在.
1.2 类型
1.2.1 笛卡尔乘积
mysql> create table a (id int ,name varchar(20));
mysql> create table b ( id int , addr varchar(20),telnum char(11) ,aid int);
mysql> insert into a values(1,‘zs’),(2,‘ls’),(3,‘w5’);
mysql> insert into a values(1,‘zs’),(2,‘ls’),(3,‘w5’);
mysql> insert into a values(11,‘a’),(22,‘b’),(33,‘c’);
mysql> insert into b values(1001,‘bj’,‘110’,1),(1002,‘sh’,‘119’,2),(1003,‘sz’,‘112’,‘3’),(1004,‘gz’,‘120’,10);
select * from a,b;
select * from a join b;
拿着a表的每行数据和b表每行进行组合.
原理上就是两层for循环.
1.2.2 内连接
select * from a,b where a.id=b.aid;
select * from a join b on a.id=b.aid
for each_row in a ---->驱动表
for each_row in b ---->非驱动表
if a.id=b.aid
marge
1.2.3 外连接
select * from a left join b on a.id=b.aid
select * from a right join b on a.id=b.aid
1.2.4 多表连接套路
– 查询oldguo老师教的课程名
a. 通过需求找到所有需要的表
teacher ：教师表
tno： 教师编号
tname： 教师名字
course ：课程表
cno： 课程编号
cname：课程名字
tno： 教师编号
b. 找直接和间接关联条件
teacher.tno = course.tno
c. 组合到一起()
from teacher
join course
on
teacher.tno = course.tno
d. 罗列其他查询条件
mysql> select teacher.tname ,course.cname
from teacher
join course
on teacher.tno = course.tno
where teacher.tname=‘oldguo’;
– 练习 :
– 1. 每位学生学习的课程门数
student ：学生表
sno： 学号
sname：学生姓名
sage： 学生年龄
ssex： 学生性别
course ：课程表
cno： 课程编号
cname：课程名字
tno： 教师编号
sc ：成绩表
sno： 学号
cno： 课程编号
score：成绩
select student.sname,count(*)
from student
join sc
on student.sno=sc.sno
group by student.sno;
– 2. 每位老师所教的课程门数
– teacher ：教师表
— tno： 教师编号
— tname： 教师名字
– course ：课程表
— cno： 课程编号
— cname：课程名字
— tno： 教师编号
SELECT *
FROM teacher
JOIN course
ON teacher.tno=course.tno
GROUP BY teacher.tno
– 3. 每位老师所教的课程门数和名称
SELECT teacher.tname,COUNT(*) ,GROUP_CONCAT(course.cname)
FROM teacher
JOIN course
ON teacher.tno=course.tno
GROUP BY teacher.tno
– 4. 每位学生学习的课程门数和名称
student ：学生表
sno： 学号
sname：学生姓名
sage： 学生年龄
ssex： 学生性别
sc ：成绩表
sno： 学号
cno： 课程编号
score：成绩
course ：课程表
cno： 课程编号
cname：课程名字
tno： 教师编号
SELECT student.sname,COUNT(*) ,GROUP_CONCAT(course.cname)
FROM student
JOIN sc
ON student.sno=sc.sno
JOIN course
ON sc.cno=course.cno
GROUP BY student.sno;
– 5. 查询zhang3,学习的课程名称有哪些?
student ：学生表
sno： 学号
sname：学生姓名
sage： 学生年龄
ssex： 学生性别
sc ：成绩表
sno： 学号
cno： 课程编号
score：成绩
course ：课程表
cno： 课程编号
cname：课程名字
tno： 教师编号
select *
from student
join sc
on student.sno=sc.sno
join course
on sc.cno=course.cno
– 6. 查询oldguo老师教的学生名.
teacher course sc student
SELECT teacher.tname,GROUP_CONCAT(student.sname) FROM
teacher
JOIN course
ON teacher.tno=course.tno
JOIN sc
ON course.cno=sc.cno
JOIN student
ON sc.sno=student.sno
WHERE teacher.tname=‘oldguo’
GROUP BY teacher.tno ;
– 7. 查询oldguo所教课程的平均分数
SELECT teacher.tname , AVG(sc.score) FROM
teacher
JOIN course
ON teacher.tno=course.tno
JOIN sc
ON course.cno=sc.cno
WHERE teacher.tname=‘oldguo’
GROUP BY teacher.tno;
– 8. 每位老师所教课程的平均分,并按平均分排序
SELECT teacher.tname , AVG(sc.score) FROM
teacher
JOIN course
ON teacher.tno=course.tno
JOIN sc
ON course.cno=sc.cno
GROUP BY teacher.tno;
– 9. 查询oldguo所教的不及格的学生姓名
SELECT teacher.tname,GROUP_CONCAT(student.sname)
FROM teacher
JOIN course
ON teacher.tno=course.tno
JOIN sc
ON course.cno=sc.cno
JOIN student
ON sc.sno=student.sno
WHERE teacher.tname=‘oldguo’ AND sc.score<60
GROUP BY teacher.tno;
– 10. 查询所有老师所教学生不及格的信息
SELECT teacher.tname,GROUP_CONCAT(student.sname)
FROM teacher
JOIN course
ON teacher.tno=course.tno
JOIN sc
ON course.cno=sc.cno
JOIN student
ON sc.sno=student.sno
WHERE sc.score<60
GROUP BY teacher.tno;
– 11. 查询平均成绩大于60分的同学的学号和平均成绩；
SELECT student.sno,student.sname,AVG(sc.score)
FROM student
JOIN sc
ON student.sno=sc.sno
GROUP BY student.sno
HAVING AVG(sc.score)>60
– 12. 查询所有同学的学号、姓名、选课数、总成绩；
SELECT student.sno ,student.sname,COUNT(*),SUM(sc.score) ,AVG(sc.score)
FROM student
JOIN sc
ON student.sno=sc.sno
GROUP BY student.sno
– 13.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
SELECT sc.cno,MAX(sc.score),MIN(sc.score)
FROM sc
GROUP BY sc.cno
– 14.查询出只选修了一门课程的全部学生的学号和姓名
SELECT student.sno ,student.sname,COUNT()
FROM student
JOIN sc
ON student.sno=sc.sno
GROUP BY student.sno
HAVING COUNT() =1;
– 15.查询选修课程门数超过1门的学生的学号和姓名
SELECT student.sno ,student.sname,COUNT()
FROM student
JOIN sc
ON student.sno=sc.sno
GROUP BY student.sno
HAVING COUNT() >1;
– 16.查询平均成绩大于85的所有学生的学号、姓名和平均成绩
SELECT student.sno,student.sname,AVG(sc.score) FROM
student
JOIN sc
ON student.sno=sc.sno
GROUP BY student.sno
HAVING AVG(sc.score)>85;
CASE WHEN THEN END
– 17.统计各位老师,所教课程的及格率 (及格人数/总人数)
SELECT teacher.tname, CONCAT(COUNT(CASE WHEN sc.score>60 THEN 1 END)/COUNT(*)*100,"%") AS a
FROM teacher
JOIN course
ON teacher.tno=course.tno
JOIN sc
ON course.cno=sc.cno
GROUP BY teacher.tno
– 18.统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表
SELECT course.cname,
GROUP_CONCAT(CASE WHEN sc.score>=85 THEN student.sname END) AS ‘优秀’,
GROUP_CONCAT(CASE WHEN sc.score>=70 AND sc.score<85 THEN student.sname END) AS ‘良好’,
GROUP_CONCAT(CASE WHEN sc.score>=60 AND sc.score<70 THEN student.sname END) AS ‘一般’,
GROUP_CONCAT(CASE WHEN sc.score<60 THEN student.sname END) AS ‘不及格’
FROM student
JOIN sc
ON student.sno=sc.sno
JOIN course
ON sc.cno=course.cno
GROUP BY course.cno
- 元数据获取
2.1 show
show databases; —> 查看所有库名
use;show tables; /show tables from world; —> 查看当前库中的表名
show [full] processlist ; —> 查看当前连接信息
show create database world; —> 查看建库语句
show create table world.city; —> 查看建表语句
show variables like ‘%trx%’; —> 查看参数信息
show grants for root@‘localhost’ —> 查看用户权限
show privileges; —> 所有可授权权限
show charset; —> 查看所有支持的字符
show collation; —> 查看所有支持的校对规则
show engines; —> 查看所有支持的引擎
show engine innodb status; —> 查看InnoDB状态信息
show status like ; —> 查看所有数据库状态信息
show binary logs ;
show binlog events in ‘’
show master status ;
show slave status ;
show slave hosts;
show index from
2.2 information_schema库
2.2.1 information_schema结构
保存了查询元数据的方法.以视图的方式来使用.
视图view?
use school;
CREATE VIEW v_select AS
SELECT course.cname,
GROUP_CONCAT(CASE WHEN sc.score>=85 THEN student.sname END) AS ‘优秀’,
GROUP_CONCAT(CASE WHEN sc.score>=70 AND sc.score<85 THEN student.sname END) AS ‘良好’,
GROUP_CONCAT(CASE WHEN sc.score>=60 AND sc.score<70 THEN student.sname END) AS ‘一般’,
GROUP_CONCAT(CASE WHEN sc.score<60 THEN student.sname END) AS ‘不及格’
FROM student
JOIN sc
ON student.sno=sc.sno
JOIN course
ON sc.cno=course.cno
GROUP BY course.cno;
SELECT * FROM v_select;
2.2.2 常用视图----> tables
a. 介绍
存储数据库层面所有表的元数据信息.
b. 应用
– 统计整个数据库里,所有业务相关库下表名
mysql> use information_schema;
mysql> desc tables;
TABLE_SCHEMA —> 表所在的库
TABLE_NAME —> 表名
ENGINE —> 引擎
TABLE_ROWS —> 数据行
AVG_ROW_LENGTH —> 平均行长度
INDEX_LENGTH —> 索引长度
DATA_FREE —> 碎片量
CREATE_TIME —> 创建时间
UPDATE_TIME —> 修改时间
TABLE_COMMENT —> 注释
mysql> select table_schema,group_concat(table_name) ,count() from information_schema.tables where table_schema not in (‘sys’,‘mysql’,‘iinformation_schema’,‘performance_schema’) group by table_schema;
±-------------±---------------------------------------±---------+
| TABLE_SCHEMA | group_concat(table_name) | count() |
±-------------±---------------------------------------±---------+
| school | a,b,course,sc,student,teacher,v_select | 7 |
| test | a,stu,student | 3 |
| world | city,country,countrylanguage | 3 |
±-------------±---------------------------------------±---------+
– 统计每个业务库数据量大小
select table_schema,sum(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)
from information_schema.tables where table_schema not in (‘sys’,‘mysql’,‘iinformation_schema’,‘performance_schema’) group by table_schema;
– 统计业务数据库下,不是InnoDB引擎的表名
select table_schema,table_name
from
information_schema.tables where table_schema not in (‘sys’,‘mysql’,‘information_schema’,‘performance_schema’) ;
6772
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
