多表连接
1.1 作用
需要获取的数据在多张表中存的话.
1.2 类型
1.2.1 笛卡尔乘积(一种算法)
## 通过交叉匹配的进行两张或多张表的合并。(相当于两层for循环)
## 测试环境
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 99 版本之后,使用join 使得两张表格联系。
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
## left 左外连接: 取左边表中所有的数据,去匹配右边与之有关系的数据。左边没有匹配到的就已空的形式显示。
## right 右外连接: 取右边表中所有的数据,去匹配左边与之有关系的数据。右边没有匹配到的就已空的形式显示。
## 驱动表:外层循环。
## 非驱动表: 内循环。
## 驱动表的行数肯定是越少越好的。行数越多,只会消耗越大。
1.2.4 取差集
select * from a left join b on a.id=b.aid where b.id is ot;
多表连接的抒写方法:
1.通过问题,先找到与之对应的表。先将表的数据提出来
2.去找寻他们之间已经的关联联系(直接换着间接),通过join 将两张或者多张表联系成一张彼此有关系的表
3.然后进行单表的方式进行取查询的条件
select 查询命令
teacher.tname,course.cname 想最后输出的列的信息
from teacher join course on 将两张表连接起来
teacher.tno=course.tno 通过他们共有(相同的列)进行关联
where teacher.tname='oldguo' 得到最后的值之后,可以在进行整体的判断(这个时候就是一张大表,可以当一张表处理)
多表连接的案例题目
– 1. 每位学生学习的课程门数
# student :学生表
sno: 学号
sname:学生姓名
sage: 学生年龄
ssex: 学生性别
# 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 teacher.`tname`,COUNT(course.`cname`)
FROM teacher JOIN course ON
teacher.`tno`=course.`tno`
GROUP BY teacher.`tno`
– 3. 每位老师所教的课程门数和名称
# teacher :教师表
tno: 教师编号
tname:教师名字
# course :课程表
cno: 课程编号
cname:课程名字
tno: 教师编号
## 解答:
SELECT teacher.`tname`,COUNT(*),GROUP_CONCAT(course.`cname`) AS a
FROM teacher JOIN course ON
teacher.`tno`=course.`tno`
GROUP BY teacher.`tno`
– 4. 每位学生学习的课程门数和名称
# student :学生表
sno: 学号
sname:学生姓名
sage: 学生年龄
ssex: 学生性别
# course :课程表
cno: 课程编号
cname:课程名字
tno: 教师编号
# sc :成绩表
sno: 学号
cno: 课程编号
score:成绩
## 解答:
SELECT student.`sname`,COUNT(course.`cname`), GROUP_CONCAT(course.`cname`) AS a
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: 学生性别
# course :课程表
cno: 课程编号
cname:课程名字
tno: 教师编号
# sc :成绩表
sno: 学号
cno: 课程编号
score:成绩
## 解答:
SELECT student.`sname`,GROUP_CONCAT(course.`cname`)
FROM student JOIN sc ON
student.`sno`=sc.`sno`
JOIN course ON
sc.`cno`=course.`cno`
WHERE student.`sname`='zhang3'
GROUP BY student.`sno`
– 6. 查询oldguo老师教的学生名.
# student :学生表
sno: 学号
sname:学生姓名
sage: 学生年龄
ssex: 学生性别
# teacher :教师
tno: 教师编号
tname:教师名字
# course :课程表
cno: 课程编号
cname:课程名字
tno: 教师编号
# sc :成绩表
sno: 学号
cno: 课程编号
score:成绩
## 解答:
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所教课程的平均分数
# teacher :教师
tno: 教师编号
tname:教师名字
# course :课程
cno: 课程编号
cname:课程名字
tno: 教师编号
# sc :成绩表
sno: 学号
cno: 课程编号
score:成绩
## 解答:
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. 每位老师所教课程的平均分,并按平均分排序
# teacher :教师表
tno: 教师编号
tname:教师名字
# course :课程表
cno: 课程编号
cname:课程名字
tno: 教师编号
# sc :成绩表
sno: 学号
cno: 课程编号
score:成绩
## 解答:
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`
ORDER BY AVG(sc.`score`)
– 9. 查询oldguo所教的不及格的学生姓名
# student :学生表
sno: 学号
sname:学生姓名
sage: 学生年龄
ssex: 学生性别
# teacher :教师表
tno: 教师编号
tname:教师名字
# course :课程表
cno: 课程编号
cname:课程名字
tno: 教师编号
# sc :成绩表
sno: 学号
cno: 课程编号
score:成绩
## 解答:
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. 查询所有老师所教学生不及格的信息
# student :学生表
sno: 学号
sname:学生姓名
sage: 学生年龄
ssex: 学生性别
# teacher :教师表
tno: 教师编号
tname:教师名字
# course :课程表
cno: 课程编号
cname:课程名字
tno: 教师编号
# sc :成绩表
sno: 学号
cno: 课程编号
score:成绩
## 解答:
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分的同学的学号和平均成绩;
# student :学生表
sno: 学号
sname:学生姓名
sage: 学生年龄
ssex: 学生性别
# sc :成绩表
sno: 学号
cno: 课程编号
score:成绩
## 解答:
SELECT student.`sname`,student.`sno`,AVG(sc.`score`)
FROM student JOIN sc ON
student.`sno`=sc.`sno`
GROUP BY student.`sno`
HAVING AVG(sc.`score`)>60
– 12. 查询所有同学的学号、姓名、选课数、总成绩;
# student :学生表
sno: 学号
sname:学生姓名
sage: 学生年龄
ssex: 学生性别
# course :课程表
cno: 课程编号
cname:课程名字
tno: 教师编号
# sc :成绩表
sno: 学号
cno: 课程编号
score:成绩
## 解答:
SELECT student.`sname`,student.`sno`,COUNT(course.`cname`),SUM(sc.`score`)
FROM student JOIN sc ON
student.`sno`=sc.`sno`
JOIN course ON
sc.`cno`=course.`cno`
GROUP BY student.`sno`
– 13.查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
# sc :成绩表
sno: 学号
cno: 课程编号
score:成绩
## 解答:
SELECT sc.`cno`,MAX(sc.`score`),MIN(sc.`score`)
FROM sc
GROUP BY sc.`cno`
– 14.查询出只选修了一门课程的全部学生的学号和姓名
# student :学生表
sno: 学号
sname:学生姓名
sage: 学生年龄
ssex: 学生性别
# course :课程表
cno: 课程编号
cname:课程名字
tno: 教师编号
# sc :成绩表
sno: 学号
cno: 课程编号
score:成绩
## 解答:
SELECT student.`sname`,student.`sno`,COUNT(course.`cname`)
FROM student JOIN sc ON
student.`sno`=sc.`sno`
JOIN course ON
sc.`cno`=course.`cno`
GROUP BY student.`sno`
HAVING COUNT(course.`cname`)=1
– 15.查询选修课程门数超过1门的学生信息
# student :学生表
sno: 学号
sname:学生姓名
sage: 学生年龄
ssex: 学生性别
# course :课程表
cno: 课程编号
cname:课程名字
tno: 教师编号
# sc :成绩表
sno: 学号
cno: 课程编号
score:成绩
## 解答:
SELECT student.*,COUNT(course.`cname`)
FROM student JOIN sc ON
student.`sno`=sc.`sno`
JOIN course ON
sc.`cno`=course.`cno`
GROUP BY student.`sno`
HAVING COUNT(course.`cname`)>1
– 16.查询平均成绩大于85的所有学生的学号、姓名和平均成绩
# student :学生表
sno: 学号
sname:学生姓名
sage: 学生年龄
ssex: 学生性别
# sc :成绩表
sno: 学号
cno: 课程编号
score:成绩
## 解答:
SELECT student.`sname`,student.`sno`,AVG(sc.`score`)
FROM student JOIN sc ON
student.`sno`=sc.`sno`
GROUP BY student.`sno`
HAVING AVG(sc.`score`)>85
– 17.统计各位老师,所教课程的及格率
# student :学生表
sno: 学号
sname:学生姓名
sage: 学生年龄
ssex: 学生性别
# teacher :教师表
tno: 教师编号
tname:教师名字
# course :课程表
cno: 课程编号
cname:课程名字
tno: 教师编号
# sc :成绩表
sno: 学号
cno: 课程编号
score:成绩
## 解答:
SELECT teacher.`tname`,CONCAT(COUNT(CASE WHEN sc.`score`>60 THEN 1 END)/COUNT(*)*100,"%")
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)的学生列表
# student :学生表
sno: 学号
sname:学生姓名
sage: 学生年龄
ssex: 学生性别
# course :课程表
cno: 课程编号
cname:课程名字
tno: 教师编号
# sc :成绩表
sno: 学号
cno: 课程编号
score:成绩
## 解答:
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
元数据获取
关于show
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
information_schema库
2.2.1 information_schema结构
## 在information_schema中存放的是更加详细的关于元数据获取的方法了
而这个库里的所有表,在磁盘中是找不到的,所以他们都是存放在内存中(他们是每个数据启动的时候,就会生成类似与表的东西)。
## 所以它叫做虚拟表,也叫做视图。
## 所以,information_schema库下存放的所有表都是虚拟表,也就是视图
# 视图?viem
举例:有一个非常长的命令,就像例18一样。每次调取的时候,都很不方便。
那这个时候,就想给它做一个别名。
## 定义:create viem oldboy as (长串命令)
viem 就是你要创建一个视图(虚拟表)
oldbooy 是你虚拟表的名称,随便取,只要不重复就行。
as 是别名,后边是长命令。相当于给后边的长命令赋予了一个值是oldboy,以后调取oldboy就可以得结果。
## 其实就相当于一个封装的函数相似。
## 调用:SELECT * FROM oldboy; 直接查这个表名就可以得结果
注意:
1) 视图中,是不存储表里边的数据,只是存储查询的方法。(相当于软链接,shell里的函数一样)
2) 保存的是查询元数据的方法.以视图的方式来使用.
---------------------------------------------------------------------------
---------------------------------------------------------------------------
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 ---> 注释
-- 统计整个数据库里,所有业务相关库下表名
select
table_schema,group_concat(table_name) ,count(*)
from information_schema.tables
where
table_schema not in ('sys','mysql','information_schema','performance_schema')
group by table_schema;
-- 统计每个业务库数据量大小
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,COUNT(*)
FROM
information_schema.tables
WHERE
table_schema NOT IN ('sys','mysql','information_schema','performance_schema')
GROUP BY table_schema