问题：s_id是学号,c_id是学科, s_score是学分,我想查询学科1比学科2学分低的人都有谁？

CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

答案：

mysql> select  *  from  Score;
+------+------+---------+
| s_id | c_id | s_score |
+------+------+---------+
| 01   | 01   |      80 |
| 01   | 02   |      90 |
| 01   | 03   |      99 |
| 02   | 01   |      70 |
| 02   | 02   |      60 |
| 02   | 03   |      80 |
| 03   | 01   |      80 |
| 03   | 02   |      80 |
| 03   | 03   |      80 |
| 04   | 01   |      50 |
| 04   | 02   |      30 |
| 04   | 03   |      20 |
| 05   | 01   |      76 |
| 05   | 02   |      87 |
| 06   | 01   |      31 |
| 06   | 03   |      34 |
| 07   | 02   |      89 |
| 07   | 03   |      98 |
+------+------+---------+
18 rows in set (0.00 sec)mysql>
mysql>
mysql>
mysql> select a.s_id-> from-> (select s_id , s_score from Score where c_id='01') as a-> join-> (select s_id , s_score from Score where c_id='02') as b-> on a.s_id=b.s_id-> where a.s_score< b.s_score-> ;
+------+
| s_id |
+------+
| 01   |
| 05   |
+------+
2 rows in set (0.00 sec)mysql>

课程回顾：思维导图

导出某个库文件

[root@db01 ~]# mysqldump    -B  school >/tmp/sch.sql

多表查询的类型（内连接、外连接）
项目构建
source /root/school.sql
多表查询类型
笛卡尔乘积，线上业务不使用，因为会出现一些无意义的数据
这里的意义是了解多表查询的执行顺序原理

mysql> select  * from  teacher,course;

或者：

mysql> select  * from  teacher join course;

拿着teacher每行数据和course逐行进行组合显示
两层for循环的实现逻辑 simple-nextloop （嵌套循环方式）
得出的结果，会有部分数据是无意义的
内连接（取交集）
先找两个表中都有的信息，用这种方法取出有意义的数据

mysql> select  * from  teacher join course on teacher.tno=course.tno   ;

必须要有on的条件，也就是说要有交集的部分
等价于where条件语句

mysql> select  * from  teacher join course where teacher.tno=course.tno   ;

外连接
（左外连接 右外链接）

left join   right join

原理：左连接，全部A的数据以及A和B的交集；右连接的原理差不多

导出连接查询的数据表

[root@db01 tmp]# mysql -e   ' use school; select  * from  teacher  right  join course on  teacher.tno=course.tno ;'> /tmp/a.txt

导出数据方式二（在mysql中使用命令）

mysql> select * from  teacher join  course on teacher.tno=course.tno  into  outfile '/tmp/aa.txt'

实现此方式的前提是在 /etc/my.cnf配置文件中设置导出的路径信息

[root@db01 tmp]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
secure-file-priv=/tmp  #也就是这一行
[mysql]
socket=/tmp/mysql.sock

多表连接语法格式：
多表的方式解决的问题：单表的数据无法提供我们要查询的数据问题时，涉及多张表的数据进行组合查询时使用
1、a表和b表有直接关联关系
a是表，a.x是a表中的x列

select  a.x,b.y from   a join b on a.z=b.z where group by having  order by limit ;

2、a和b没有直接关系，
假如：a和b没有关系 但是a和c有关系 b和c有关系，那么可以使用一下方式让a和c先关联。

a join c  on a.i=c.j    join   b  on  c.x=b.y

套路：
1、根据题意将所有涉及到的表找出来 A B
2、找到A和B直接或者间接的关联条件
3、用join on 语句把所有表连接到一起。
4、罗列其他查询条件
建议：不要超过4个表的关联查询，太多的话会严重影响mysql的性能。
大量练习：

练习前了解多个表的关系
1、student(学生信息)-----sc（分数）------course（课程）------teacher（老师信息）
2、ER图的方式
例题1：查询每位老师教授的课程名称

mysql> select  teacher.tname,course.cname   from  teacher join  course on teacher.tno=course.tno;
+--------+--------+
| tname  | cname  |
+--------+--------+
| oldboy | linux  |
| hesw   | python |
| oldguo | mysql  |
+--------+--------+
3 rows in set (0.00 sec)

mysql> select teacher.tname,course.cname from teacher join course on teacher.tno=course.tno;
±-------±-------+
| tname | cname |
±-------±-------+
| oldboy | linux |
| hesw | python |
| oldguo | mysql |
±-------±-------+
3 rows in set (0.00 sec)

例题2统计每个学员学了几门课程
student
course
统计： count()

SELECT  CONCAT(student.sname,"_",COUNT(*))  FROM  sc JOIN  student  ON sc.sno=student.sno  GROUP BY  student.sno;

连接所有的表

select  *  from  teacher join  course on teacher.tno=course.tno join   sc  on sc.cno=course.cno join  student on student.sno=sc.sno;
连接所有的表select  *  fro

练习1 ：统计每个学员，学习课程的门数和课程名列表

mysql> select student.sname,course.cname  from   student   join  sc  on student.sno=sc.sno  join  course on  sc.cno=course.cno;

答案：group_concat

练习2 ：每位老师教的学生数量和学生名列表

select teacher.tname,COUNT(student.sno),GROUP_CONCAT(student.sname)  from  teacher join  course on teacher.tno=course.tno join   sc  on sc.cno=course.cno join  student on student.sno=sc.sno group by teacher.tname;

练习3 ：每位老师教所教课程的平均分

select  avg(sc.score),teacher.tname  from  teacher join  course on teacher.tno=course.tno join   sc  on sc.cno=course.cno join  student on student.sno=sc.sno group by teacher.tname ;
+---------------+--------+
| avg(sc.score) | tname  |
+---------------+--------+
|       70.0000 | hesw   |
|       80.6667 | oldboy |
|       76.7500 | oldguo |
+---------------+--------+
3 rows in set (0.00 sec)

练习4 ：查找学习了hesw但没学习oldguo课程的学生名。

 select  group_concat(teacher.tname)  from  teacher join  course on teacher.tno=course.tno join   sc  on sc.cno=course.cno join  student on student.sno=sc.sno     group by student.sname ;

练习5 ：查询出只选修了一门课程的全部学生的学号和姓名

select  group_concat(student.sno),group_concat(student.sname)  from  teacher join  course on teacher.tno=course.tno join   sc  on sc.cno=course.cno join  student on student.sno=sc.sno     group by course.cno;

练习1 ：统计每个学员，学习课程的门数和课程名列表

-- 关系图： student ----> sc  ----> course ---> teacher
select CONCAT(student.sname,"_",student.sno),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

练习2 ：每位老师教的学生数量和学生名列表

-- 关系图： student ----> sc  ----> course ---> teacher
select CONCAT(teacher.tname,"_",teacher.tno),COUNT(*),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
group by teacher.tno

4题答案
练习4 ：查找学习了hesw但没学习oldguo课程的学生名。
差集的方式

select a.sname from 
(select 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 = 'hesw') as a
left join 
(select 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') as b
on a.sname=b.sname
where b.sname is null

不建议使用这两个方式，因为不走索引

having
!=
like  '%%'

练习答案

---

练习1 ：统计每个学员，学习课程的门数和课程名列表
-- 关系图： student ----> sc  ----> course ---> teacher
select CONCAT(student.sname,"_",student.sno),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

--- 练习2 ：每位老师教的学生数量和学生名列表
-- 关系图： student ----> sc  ----> course ---> teacher
select CONCAT(teacher.tname,"_",teacher.tno),COUNT(*),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
group by teacher.tno

--- 练习3 ：每位老师教所教课程的平均分
select CONCAT(teacher.tname,"_",teacher.tno,"_",course.cno),AVG(sc.score)
from teacher 
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno 
group by teacher.tno , course.cno

--- 练习4 ：查找学习了hesw但没学习oldguo课程的学生名。select a.sname from 
(select 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 = 'hesw') as a
left join 
(select 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') as b
on a.sname=b.sname
where b.sname is null  SELECT student.`sname`,GROUP_CONCAT(teacher.tname)
FROM student
JOIN sc
ON sc.`sno`=student.`sno`
JOIN course
ON course.cno=sc.cno
JOIN teacher
ON teacher.tno=course.`tno`
GROUP BY student.snoHAVING GROUP_CONCAT(teacher.tname) LIKE '%hesw%' AND GROUP_CONCAT(teacher.tname) NOT LIKE '%oldguo%'

--- 练习5 ：查询出只选修了一门课程的全部学生的学号和姓名
select student.sname,student.sno ,COUNT(*)
from  course 
join sc
on course.cno=sc.cno  
join student
on sc.sno=student.sno 
group by student.sno 
having COUNT(*) = 1

--- 练习6 ：查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分select course.cname ,MAX(sc.score),MIN(sc.score)
from course
join sc 
on course.cno=sc.cno 
group by course.cno;

--- 练习7 ：查询平均成绩大于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;

--- 练习8 ：统计各位老师,所教课程的及格率
case 
when   条件1    then 输出 1      
end

--- 练习9 ：统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表
select 
course.cname , 
GROUP_CONCAT(case when sc.score>=85 then  student.sname end),
GROUP_CONCAT(case when sc.score>=70 and sc.score<85 then  student.sname end),
GROUP_CONCAT(case when sc.score>=60 and sc.score<70 then  student.sname end),
GROUP_CONCAT(case when sc.score<60 then  student.sname end)
from course
join sc 
on course.cno=sc.cno
join student
on sc.sno=student.sno 
group by course.cno 

别名使用

1. 表别名

select 
a.cname , 
GROUP_CONCAT(case when b.score>=85 then  c.sname end),
GROUP_CONCAT(case when b.score>=70 and b.score<85 then  c.sname end),
GROUP_CONCAT(case when b.score>=60 and b.score<70 then  c.sname end),
GROUP_CONCAT(case when b.score<60 then  c.sname end)
from course as a
join sc as b 
on a.cno=b.cno
join student as c
on b.sno=c.sno 
group by a.cno 

功能： 表别名设置之后，覆盖到整个查询。

2. 列别名

select 
a.cname as "课程名称" , 
GROUP_CONCAT(case when b.score>=85 then  c.sname end) as "优秀学员",
GROUP_CONCAT(case when b.score>=70 and b.score<85 then  c.sname end) as "良好学员",
GROUP_CONCAT(case when b.score>=60 and b.score<70 then  c.sname end) as "一般学员",
GROUP_CONCAT(case when b.score<60 then  c.sname end) as "不及格学员"
from course as a
join sc as b 
on a.cno=b.cno
join student as c
on b.sno=c.sno 
group by a.cno 

功能：
1.显示好看
2. 列别名只能在 having 、order by 子句中调用

元数据获取

1、show  databases   查询所有数据库名
2、show  tables;   查询当前库的所有表名 show  tables  from  world; 查询指定数据库所有表名
3、show  create  database world   查看world建库语句
4、show  create  table;  当前库下的city表
5、show  create  world.city  查询指定库下的city语句
show  grants  for  root@'localhost' 查询某个用户的权限
show variables;   查询数据参数
show  privileges;查询数据库中所有权限
show  engines  数据库中支持的存储引擎
show  charset  查询数据库字符集支持
show  collation  查询所有校对规则的支持
show  variables  like  '%trx%'  结合模糊查询的方式   、查询数据库参数
show   status  like  '%com_%'  查询数据库的状态
show  processlist;    查询所有会话信息
show  engine  innodb statuss 查询innodb引擎相关的状态
show  binary logs   查询二进制日志文件信息
show binlog  events  in  'xxx'  查看二进制日志事件
show  master  status  ；当前正在使用的二进制日志信息
show   slave status\G    查看主从状态相关信息
show  slave  hosts ；  查看从库主机信息

help  show  查看show语句的帮助

通过系统表查询元数据
mysql库下的系统表
权限表 user表、db

统计信息表：
innodb_index_stats
innodb_table_stats

IS (information_schema)
对象属性：tables columns 自行查询

PS（performance_schema）
和性能有关
sys库
性能有关

performance_schema中table和columns的应用
1、数据库的资产统计
2、命令拼接，完成批量工作

desc  tables；（这个是表名 就叫tables）
TABLE_SCHEMA 表所在的库
table_name  表名
engine  存储引擎
table_rows  表的行数
AVG_ROW_LENGTH  平均行长度
INDEX_LENGTH  索引长度
DATA_FREE  碎片的大小

例子：每个业务库表的个数和名称

select  table_schema,count(*),group_concat(table_name)
from information_schema.tables
where table_schema not in ('mysql','information_schema','performance_schema','sys')
group by table_schema;

例子2： 统计每个库的数据量

select  table_schema,SUM(table_rows*avg_row_length+index_length) as total_B
from information_schema.tables
where table_schema not in ('mysql','information_schema','performance_schema','sys')
group by table_schema;

例子3： 查询业务表中，所有不是InnoDB引擎的表

select  table_schema,table_name
from information_schema.tables
where 
table_schema not in ('mysql','information_schema','performance_schema','sys')
and engine!='innodb';

二、 命令拼接，完成批量工作

alter table oldboy.t1 engine=innodb;
select  concat("alter table ",table_schema,".",table_name," engine=innodb;")
from information_schema.tables
where 
table_schema not in ('mysql','information_schema','performance_schema','sys')
and engine!='innodb' into outfile '/tmp/alter.sql';