第五章SQL 常识

1. SQL 常识

1.1 SQL的介绍

属于关系型数据库产品中专用的语句。结构化查询语句。
MySQL 

1.2  SQL 标准
SQL-89  \ SQL-92  \  SQL-99   \SQL03 .....

1.3 sql_mode 

1.3.1 作用

影响到了SQL语句的执行行为。为了让数据库在存、取能够满足生活的常识、科学的逻辑，让这些数据有意义。
例子： 
    现实生活中常识：
        日期： 1000-9999 年 1-12 月 1-31日 
    科学逻辑：         
        除法：除数不能为0
1.3.2 查看sql_mode 
mysql> select @@sql_mode;
ONLY_FULL_GROUP_BY          :  针对group by 语句执行时的规范
STRICT_TRANS_TABLES         :  针对事务表启动严格模式
NO_ZERO_IN_DATE             ： 2010-00-10
NO_ZERO_DATE                ： 0000-00-00
ERROR_FOR_DIVISION_BY_ZERO  ： 除数不能为0
NO_AUTO_CREATE_USER         ： 是否自动创建用户
NO_ENGINE_SUBSTITUTION      :  建表是使用了一个不支持的存储引擎报错。        

2. 数据库对象属性介绍

2.1 数据库对象

库
    库名
    库属性：字符集、校对规则

表 
    表名 
    列 ：
        列名
        列属性：数据类型、约束、其他属性
    行
    表属性：引擎、字符集、校对规则、其他。

2.2 字符集

2.2 字符集  
2.2.1 作用 
MySQL 中的 “密码本”
2.2.2 种类
utf8    not  UTF-8  
utf8mb4 is   UTF-8（万国码）

特点 : 
1. utf8   , 最大支持3字节的字符。不支持emoji字符
2. utf8mb4, 最大支持4字节的字符。支持emoji字符。
8.0之前，默认字符集latin1,8.0之后是utf8mb4。
我们建议使用utf8mb4.

2.2.3 设置方法

默认字符集参数
建库
建表

2.3 校对规则（排序规则）  
2.3.1 作用 
影响到数据的排序方式。
    ASCII码
a    97
b    98
c    99
d    100

A    65
B    66
C    67
D    68

2.3.2 查看属性

mysql> show collation;

2.4 列属性

2.4.1 数据类型

#  作用 
尽可能保证数据是有意义的。
#  种类 
## 数字类型
整数：
    tinyint   1字节  =  8位 = 00000000 - 11111111  = 2^8个  = 0  - 255     , -127-128    
    int       4字节  =  32位                       = 2^32个 = 0  - 2^32-1  ,-2^31-2^31-1 ,10位数
    bigint    8字节                                           0  - 2^64-1  ,-2^63-2^63-1 ,20位数
浮点数：
    decimal(m,n)

## 字符串类型
char(N)    : 
    N字符个数，最大不超过255
    定长的字符串类型。
    例如： char(10) ,最多存10个字符,只要10个字符以内，都10个字符长度的存储空间。剩余用空格填充。
varchar(M) ：
    M字符个数，最大不超过65535
    变长的字符串类型。会额外占用1-2字节存储字符长度。255字符之内，额外1字节，255以上，额外2字节
    例如： varchar(10),最多存10个字符,按需分配存储空间。
    abc 3             =4
    asdasdadasd 1000  =1002

enum('bj','sh','tj','heibei','henan',.....)
district enum('bj','sh','tj','heibei','henan',.....)

2.5时间类型

datetime    8字节 
timestamp   4字节 

DATETIME 
范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。

TIMESTAMP 
1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。
timestamp会受到时区的影响

2.6二进制类型

## 二进制类型
## json 数据类型


数据类型选型： 合适的、足够的、简短的

2.7约束

# 主键 primary key （PK）  ： 
1. 一张表只能有一个，可以有多个列构成
2. 特点： 非空+唯一
3. 建议每张表都设置主键，有利于索引的应用，通常是使用自增的数字列更佳。


# 非空 not null :
1. 特点： 必须录入值。
2. 建议： 每个列最好设置为，有利于索引的应用。


# 唯一 unique key ：
1. 特点： 不能有重复值。


# 无符号 unsigned
1. 特点： 针对数字列，无符号设定。

2.8其他

表属性 ： 
        engine  ： 存储引擎设置，默认是innodb，也是我们建议的。
        charset ： utf8mb4 
        comment :  注释。
列属性 ： 
        default       :  默认值。一般是在not null 配合使用
        auto_increment： 数字列自增长。一般是在主键列配合使用
        comment       ： 列的注释，建议每个列都有

3. SQL 种类

3.1  DDL  ： 数据定义语言
对于： 库 、表（元数据）的 增、删、改
建库、删库、修改库、建表、删表、修改表
3.2 DCL  ： 数据控制语言
grant 
revoke 
3.3 DML  ： 数据操作语言
表的数据行进行的增、删、改、查
3.4 DQL  ： 数据查询语言
对于表数据行查看
对于元数据查看

1. DDL语句

4.1 DDL-库定义 

# 建库  
CREATE DATABASE oldguo CHARSET utf8mb4;
CREATE DATABASE school CHARSET utf8mb4;

# 字符集设置： 
vim /etc/my.cnf
[mysqld]
character_set_server=utf8mb4
init_connect = "SET NAMES 'utf8mb4'"


# 改库  
CREATE DATABASE oldli ;
SHOW CREATE DATABASE oldboy;
SHOW CREATE DATABASE oldli;
ALTER DATABASE oldli CHARSET utf8mb4;
SHOW CREATE DATABASE oldli;

# 删库 
DROP DATABASE oldli;


# 查库 
show databases;
SHOW CREATE DATABASE oldboy;


# 库定义的 开发规范 
开发  ----》  测试   ----》 预发布  ----》生产
Windows                                   linux
test=TEST                               test ≠ TEST

1. 库名不能数字开头
2. 库名要和业务有关
3. 库名不要有大写字符
   原因：为了多平台兼容。
4. 建库需要显示指定字符集。建议是utf8mb4。    
5. 生产中禁用普通用户的drop database权限。

2.DDL - 表定义

# 建表 
Create table `oldboy`.`student`
( 
`id` int NOT NULL AUTO_INCREMENT COMMENT '学号',
 `name` varchar(64) NOT NULL COMMENT '学生姓名',
 `age` tinyint UNSIGNED NOT NULL DEFAULT 0 COMMENT '学生年龄',
 `gender` CHAR(1) NOT NULL DEFAULT 'n' COMMENT '学生性别',
 `address` enum('北京','天津','上海','重庆','河北','河南','山东','未知') NOT NULL DEFAULT '未知' COMMENT '省份', 
 `intime` datetime NOT NULL COMMENT '入学时间',
 `shenfen` CHAR(18) NOT NULL COMMENT '身份证',
 `num` int(10) UNSIGNED ZEROFILL NOT NULL COMMENT '测试', 
 primary key (`id`) 
) ENGINE=InnoDB charset=utf8mb4 collate=utf8mb4_bin; 


补充：  
1. `` 反引号， 还原字符串原有的含义。
2. 克隆一张和student表结构一致的stu表。
create table stu like student;
3. 建表规范
    a. 表名： 
        不能数字开头
        业务有关
        不要大写字母
        不要超过18字符
        不能是关键字
    b. 存储引擎使用InnoDB
    c. 5.7版本以后，字符集使用utf8mb4
    d. 列名要和业务有关，不要超过18个字符
    e. 选择合适、足够、简短数据类型
    f. 建议每个列设置not null
    g. 每个列要有注释
    h. 每个表要有主键
    i. 针对not null 列，可以设定默认值。
    j. 表注释

推荐软件： 
yearing   开源SQL审核工具。
inception SQL审核工具。


# 查表
mysql> use oldboy;
mysql> show tables;
mysql> show create table student;  # 命令行
mysql> desc student;


# 改表（表定义=元数据）  

-- 修改表定义  
--- 添加列: 在student表中添加telnum列 *****
use oldboy;
desc student;
alter table oldboy.student 
add column telnum CHAR(11) not null unique key default '0' comment '手机号' ;
desc student;

--- 不建议的方式
alter table oldboy.student 
add column a CHAR(11) not null unique key default '0' comment '手机号' after gender ;
desc student;

alter table oldboy.student 
add column b CHAR(11) not null unique key default '0' comment '手机号' first ;
desc student;

--- 删除列 （不代表生产操作，危险！！！！）
alter table student drop  a;
alter table student drop  b;
alter table student drop  telnum;

--- 修改表属性
--- 修改表名 
alter table student rename to st;
--- 修改引擎  *****
create table t1(id int)engine=myisam;
show create table t1;
alter table t1 engine=innodb;
--- 修改字符集
create table t2(id int)charset=utf8;
show create table t2;
alter table t2 charset=utf8mb4;


--- 修改列属性
desc st;
--- 修改列名
alter table st change shenfen cardnum CHAR(18) not null default '0' comment '身份证';

--- 修改默认值
alter table st change cardnum cardnum CHAR(18) not null default '1' comment '身份证';

--- 修改数据类型 *****
alter table st modify cardnum CHAR(20) not null default '1' comment '身份证';


#  删表(危险操作！！ 普通用户禁用 drop 操作)
drop table stu;


# 表定义规范
1. 建表  
    a. 表名： 
        不能数字开头
        业务有关
        不要大写字母
        不要超过18字符
        不能是关键字
    b. 存储引擎使用InnoDB
    c. 5.7版本以后，字符集使用utf8mb4
    d. 列名要和业务有关，不要超过18个字符
    e. 选择合适、足够、简短数据类型
    f. 建议每个列设置not null
    g. 每个列要有注释
    h. 每个表要有主键
    i. 针对not null 列，可以设定默认值。
    j. 表注释    
2. 修改表 
    a. 添加列，使用追加式添加列 
    b. 修改列属性，尽量使用modify语句
    c. 修改表定义，建议在业务不繁忙期间进行。尽量采用pt-osc或者gh-ost工具减少业务影响。

上作业： 
    1. 什么是online-ddl? 各个版本的不同？
    参考 ： https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html
    2. pt-osc（percona-toolkit : pt-online-schema-change） 或者 gh-ost工具的使用
    参考： https://www.jianshu.com/p/c97228b6f60c 或者 百度。

4-sql语句

1-DML

1.1 作用
针对 表 中的 数据行 进行的增、删、改、查
1.2 增 insert into ...
desc st;

2-增 insert into …

-- 标准 
必须 ''
insert into 
    st(id,name,age,gender,address,intime,cardnum,num)
values(1,'张三',18,'m','北京','2020-04-27','666666',10);

select * from st;

--- 部分列录入
insert into 
    st(name,intime,num)
values('李四',NOW(),1111);
select * from st;

alter table st modify intime datetime not null default NOW() comment '入学时间';
insert into 
    st(name,num)
values('王五',11112);
select * from st;

--- 省略写法
desc st;
insert into 
    st
values(5,'张三',18,'m','北京','2020-04-27','666666',10);
select * from st;

3 update 更新数据行的值

--- 更新数据行
update st set name='张六'  where id=4;
update st set name='错' , age=44 ,  address='北京' where id=10


update st set name='张qi' , age=21  where id=4;
select * from st;

4 delete 删除数据行

-- delete 
delete from st  where id=5;
select * from st;

5 伪删除：

1.5 伪删除： update 替代 delete ，添加状态列，1带表存在，0代表删除
改变 ： 
a. 添加状态列 
alter table st add column state tinyint not null default 1 comment '状态列,0是删除,1是存在';
alter table st add column sster tinyint not null  default 2  comment '状态列,0是删除,1是存在'
b. 使用update 替换 delete 
原： 
delete from st where id=4
修改后： 
update st set state=0 where id=4;
c. 替换原来查询业务语句
原：　
select * from st;
改变后：　
select * from st where state=1;

彩蛋：

面试题 drop　table t1 ,truncate table t1 , delete from t1 区别 ？ 
# drop　table t1;    
    作用： 1. 删除所有表数据，删除整个表段（rm ibd  ）,属于物理性质，会释放磁盘空间。
           2. 删除表定义 (rm  frm , 元数据也会被删除)
# truncate table t1 ;
    作用： 保留表结构，清空表段中的数据页。属于物理删除，会释放磁盘空间。    

# delete from t1; 
    作用: 删除数据行。逐行删除。保留表结构，属于逻辑性质删除。只是标记删除，不会立即释放磁盘空间。
    所以delete 操作会产生碎片。

5-DQL 数据查询语句

1 业务数据查询 - select

2.1 业务数据查询 - select 
2.1.1 select 独立使用（MySQL 独有）
--- 查询数据库服务器配置参数
select @@port;
select @@server_id;
select @@basedir;
select @@datadir;
select @@socket;
select @@innodb_flush_log_at_trx_commit;

2-==替代方法：==

show variables;
show variables like '%trx%';

2.3-查询内置函数

select DATABASE();
select NOW();
select USER();
select CONCAT("hello world");
select user,host from mysql.user;
select CONCAT("数据库用户:",user,"@",host,plugin,";") from mysql.user;

2.4-其他数据库

select NOW() from dual;

3-select 多子句执行顺序（单表）

select     列   
from       表  
where      条件  
group by   列 
having     条件 
order by   列 
limit      条件

--- 学习业务？
--- 查看表的定义
desc city; 
---- id           : 主键 1-N数字
---- name         ：城市名
---- countrycode  ：国家编码(三个字母？CHN,USA)
select * from city where id<10;
---- district     : 区域 （省、州、县）
---- population   : 城市人口

---- 论 comment 的重要性

生产中熟悉业务： 
1. comment 
2. desc ，简单查询表中数据，猜
3. E-R关系图

1-select 配合 from 子句使用

---  查询表中所有数据（小表）
use world;
select id,name,countrycode ,district,population 
from city;
# 或者
select id,name,countrycode ,district,population 
from world.city;

# 或者 
select * from city;

--- 查询部分列数据 
--- 例子： 查询所有城市名及人口信息。
select name,population from city;

2-select + from + where 子句使用

--- where 配合 比较 操作符(= != > < >= <=)
--- 例子：查询city表中，所有中国的城市信息。 
select *  from city where countrycode = 'CHN';

--- 例子：查询人口数小于100人城市信息
select * from city where population<100;

--- where 配合逻辑连接符（and, or , between and），实现多条件过滤

--- 例子：查询中国,人口数超过500w的所有城市信息
select * from city where countrycode='CHN' and  population>5000000;

--- 例子：查询中国或美国的城市信息。
 select * from city where countrycode='CHN'  or countrycode='USA' ;
# 或者
 select * from city where countrycode in ('CHN','USA');

--- 例子：查询人口数为100w-200w（包括两头）城市信息



select * from city where population >= 1000000 and population <= 2000000 
# 或者： 
select * from city where population between (介于)  1000000 and  2000000 ;
between (介于)
--- 例子 ：查询中国或美国，人口数大于500w的城市


SELECT * FROM city WHERE (countrycode='CNH' OR countrycode='USA' ) AND population>5000000;
select * from world.city where countrycode in ('CHN','USA') and population >= 5000000;

3-where 配合 like 语句实现模糊查询

--- 查询城市名为qing开头的城市信息
select * from city where name like 'qing%';
select * from city where name like '%qing%';  
# 一般线上业务慎用%qing%，不走索引，如果业务中很多需求，ES可以很好解决。

6-se==lect +== from + group by + ==聚合函数==

聚合函数：

count()            统计数量
sum()              求和
avg()              平均数
max()              最大值
min()              最小值
group_concat()     列转行

1-group by 分组功能原理

1. 按照分组条件进行排序
2. 进行分组列的去重复
3. 聚合函数将其他列的结果进行聚合。
--- SQL_MODE=only_full_group 
保证group by 语句 准确性。

2-select + from + group by + 聚合函数

例子： 统计city表的行数
select COUNT(*) from city;

--- 例子： 统计中国城市的个数


select COUNT(*) from city where countrycode='CHN';

--- 例子： 统计中国的总人口数。


select SUM(population) from city where countrycode='CHN';

--- 例子：统计每个国家的城市个数。


select countrycode,COUNT(name) from city group by countrycode;

--- 练习1： 统计每个国家的总人口数。
select countrycode,sum(population) from city   group by countrycode ;

--- 练习2： 中国每个省的城市个数，和总人口数。


mysql> select district, COUNT(name),name
    -> from city 
    -> where countrycode='CHN'  group by district 
    -> ;
ERROR 1055 (42000): Expression #3 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> 

报错说明： 5.7 之后的SQL对于group by语句的限制。
1. 没有在group by 后
2. 同时没有在函数中聚合操作
违反了SQL_MODE=only_full_group_by

--- 练习3：  统计中国每个省的城市个数及城市名列表。


select district, COUNT(name),GROUP_CONCAT(name)
from city 
where countrycode='CHN'  group by district 

3-having 子句应用

后判断，主要应用在group by之后需要判断。
--- 统计每个国家的总人口数，只显示总人口超过1亿人的信息

select countrycode,SUM(population)  
from city 
group by countrycode 
having  SUM(population)>100000000;

4-order by 语句应用

--- 例子： 查询所有城市信息，并按照人口数排序输出。
select * from city order by population;

--- 例子：查询中国所有的城市信息，并按照人口数从大到小排序输出

select * from city where countrycode = 'CHN' order by population desc ;

--- 例子： 每个国家的总人口数，总人口超过5000w的信息,并按总人口数从大到小排序输出


select countrycode,SUM(population)  
from city 
group by countrycode 
having  SUM(population)>50000000
order by SUM(population) desc ;

5-limit 分页查询

- 一般配合order by 使用
--- 例子： 查询中国所有的城市信息，并按照人口数从大到小排序输出，只显示前十名。
select * 
from city 
where countrycode = 'CHN' 
order by population desc 
limit 10 ;

--- 6-10 名 

select * 
from city 
where countrycode = 'CHN' 
order by population desc 
limit 5,5


select * 
from city 
where countrycode = 'CHN' 
order by population desc 
limit 5 offset 5;

-- limit M,N : 跳过M行，显示N行
-- limit N offset M : 跳过M行，显示N行


彩蛋： 
    limit 有可能对性能的影响。    
    limit 1000

大表中： 
limit 5000000,10
明确查询范围，使用精确的where去做数据获取。

7. 多表查询笛卡尔乘积

use school
student ：学生表
sno：    学号
sname：学生姓名
sage： 学生年龄
ssex： 学生性别

teacher ：教师表
tno：     教师编号
tname：教师名字

course ：课程表
cno：  课程编号
cname：课程名字
tno：  教师编号

score  ：成绩表
sno：  学号
cno：  课程编号
score：成绩

-- 项目构建
drop database school;
CREATE DATABASE school CHARSET utf8;
USE school

CREATE TABLE student(
sno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname VARCHAR(20) NOT NULL COMMENT '姓名',
sage TINYINT UNSIGNED  NOT NULL COMMENT '年龄',
ssex  ENUM('f','m') NOT NULL DEFAULT 'm' COMMENT '性别'
)ENGINE=INNODB CHARSET=utf8;

CREATE TABLE course(
cno INT NOT NULL PRIMARY KEY COMMENT '课程编号',
cname VARCHAR(20) NOT NULL COMMENT '课程名字',
tno INT NOT NULL  COMMENT '教师编号'
)ENGINE=INNODB CHARSET utf8;

CREATE TABLE sc (
sno INT NOT NULL COMMENT '学号',
cno INT NOT NULL COMMENT '课程编号',
score INT  NOT NULL DEFAULT 0 COMMENT '成绩'
)ENGINE=INNODB CHARSET=utf8;

CREATE TABLE teacher(
tno INT NOT NULL PRIMARY KEY COMMENT '教师编号',
tname VARCHAR(20) NOT NULL COMMENT '教师名字'
)ENGINE=INNODB CHARSET utf8;

INSERT INTO student(sno,sname,sage,ssex)
VALUES (1,'zhang3',18,'m');

INSERT INTO student(sno,sname,sage,ssex)
VALUES
(2,'zhang4',18,'m'),
(3,'li4',18,'m'),
(4,'wang5',19,'f');

INSERT INTO student
VALUES
(5,'zh4',18,'m'),
(6,'zhao4',18,'m'),
(7,'ma6',19,'f');

INSERT INTO student(sname,sage,ssex)
VALUES
('oldboy',20,'m'),
('oldgirl',20,'f'),
('oldp',25,'m');


INSERT INTO teacher(tno,tname) VALUES
(101,'oldboy'),
(102,'hesw'),
(103,'oldguo');

DESC course;
INSERT INTO course(cno,cname,tno)
VALUES
(1001,'linux',101),
(1002,'python',102),
(1003,'mysql',103);

DESC sc;
INSERT INTO sc(sno,cno,score)
VALUES
(1,1001,80),
(1,1002,59),
(2,1002,90),
(2,1003,100),
(3,1001,99),
(3,1003,40),
(4,1001,79),
(4,1002,61),
(4,1003,99),
(5,1003,40),
(6,1001,89),
(6,1003,77),
(7,1001,67),
(7,1003,82),
(8,1001,70),
(9,1003,80),
(10,1003,96);

SELECT * FROM student;
SELECT * FROM teacher;
SELECT * FROM course;
SELECT * FROM sc;

-- 项目构建
source /root/school.sql
1.1 多表查询类型
1.1.1 笛卡尔乘积 
select  *   from  teacher,course;
或者： 
select * from teacher join course;

拿着 teacher每行数据和course逐行进行组合，显示
两层for循环的实现逻辑。Simple-Nextloop （嵌套循环方式）
得出的结果，会有部分数据是无意义的。

1-内连接 （取交集）

mysql> select * from teacher  join course on teacher.tno=course.tno ;
+-----+--------+------+--------+-----+
| tno | tname  | cno  | cname  | tno |
+-----+--------+------+--------+-----+
| 101 | oldboy | 1001 | linux  | 101 |
| 102 | hesw   | 1002 | python | 102 |
| 103 | oldguo | 1003 | mysql  | 103 |
+-----+--------+------+--------+-----+
3 rows in set (0.00 sec)
mysql> select * from teacher,course where teacher.tno=course.tno;
+-----+--------+------+--------+-----+
| tno | tname  | cno  | cname  | tno |
+-----+--------+------+--------+-----+
| 101 | oldboy | 1001 | linux  | 101 |
| 102 | hesw   | 1002 | python | 102 |
| 103 | oldguo | 1003 | mysql  | 103 |
+-----+--------+------+--------+-----+

2-外连接 （left join , right join ）

mysql> select * from teacher left join course on teacher.tno=course.tno ;
+-----+--------+------+--------+------+
| tno | tname  | cno  | cname  | tno  |
+-----+--------+------+--------+------+
| 101 | oldboy | 1001 | linux  |  101 |
| 102 | hesw   | 1002 | python |  102 |
| 103 | oldguo | 1003 | mysql  |  103 |
| 104 | oldx   | NULL | NULL   | NULL |
| 105 | oldw   | NULL | NULL   | NULL |
+-----+--------+------+--------+------+
5 rows in set (0.00 sec)

mysql> select * from teacher right  join course on teacher.tno=course.tno;
+------+--------+------+--------+-----+
| tno  | tname  | cno  | cname  | tno |
+------+--------+------+--------+-----+
|  101 | oldboy | 1001 | linux  | 101 |
|  102 | hesw   | 1002 | python | 102 |
|  103 | oldguo | 1003 | mysql  | 103 |
| NULL | NULL   | 1004 | k8s    | 108 |
+------+--------+------+--------+-----+
4 rows in set (0.00 sec)

3-多表连接语

a 和 b 有直接的关联关系

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

• a 和 b 没有直接的关联关系
• 假如： a 和 c 有关， b和c 有关
  a join c on a.i = c.j join b on c.x=b.y

3.-1-方案 套路

    1. 根据题意将所有涉及到的表找出来 a  b 
    2. 找到a和b直接或者间接的关联条件 
    3. 用join on 语句把所有表连接到一起
    4. 罗列其他查询条件

4-大量练习

1： 每位老师所教课程名称。

select 
  teacher.tname,course.cname
  from teacher 
  join course
  on teacher.tno=course.tno;

2：统计每个学员，学习课程的门数

select CONCAT(student.sname,"_",student.sno) ,COUNT(*)
from student
join sc
on student.sno=sc.sno 
group  by student.sno;

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.sno

HAVING 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 子句中调用

8. 元数据获取

1-常用show 语句

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

2 通过系统表查询元数据

2.2.1 mysql
# 权限表： 
    user
    db
# 统计信息表 :
    innodb_index_stats
    innodb_table_stats

2.2.2 IS(information_schema)
对象属性：
tables  
columns

2.2.3 PS 
性能有关

2.2.4 SYS
性能有关

3-IS中的 tables 和 columns 的应用

1-一、 数据库资产统计

use information_schema;
desc tables;
-- table_schema   表所在的库
-- table_name     表名
-- engine         存储引擎
-- table_rows     表行数
-- avg_row_length 平均行长度
-- index_length   索引长度
-- data_free      碎片的大小

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

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;

5： 统计每个库的数据量

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;

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

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

7-命令拼接，完成批量工作

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';