MySQL数据库-SQL基础应用

1.1 什么是SQL

结构化查询语言。MySQL内置的命令。数据库管理、对象操作。
是关系型数据库通用的语言。
SQL规范: SQL89 SQL92 SQL99 SQL03

1.2 SQL 常用种类

1.2.1 DDL ： 数据定义语言

库 ：增、删、改
表 ：增、删、改
主要是针对以上对象的 结构、属性的定义。

1.2.2 DCL ： 数据控制语言

对于权限相关管理。
grant revoke

1.2.3 DML ： 数据操作语言

数据行：增、删、改、查

1.2.4 DQL ： 数据查询语言

select
show

1.3 对象属性

1.3.1 SQL_MODE

用来规范SQL使用的模式。在操作SQL语句是能够符合现实常识和科学的逻辑。
严格模式参数 ：
mysql> select @@sql_mode;
ONLY_FULL_GROUP_BY ： 重点关注的。
STRICT_TRANS_TABLES,
NO_ZERO_IN_DATE,
NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,
NO_ENGINE_SUBSTITUTION

1.3.2 对象属性–字符集和校对规则

作用对象： 库 ，表
字符集是存储数据的编码格式。
UTF8 : 不是传统意义的UTF8,只支持最大3个字节的字符。不支持emoji。
UTF8MB4 ：传统意义的UTF8,支持最大4个字节长度的字符。例如emoji。

校对规则：影响到数据的排序
Acd
abc
Bcd
ABB

敏感 ：ASCII
ABB
Acd
Bcd
abc
不敏感 ：ASCII
ABB
ABC
ACD
BCD

1.3.3 对象属性–数据类型

作用对象：
表。
常用数据类型：
数字类型 ：
tinyint : 1字节 ： 8位二进制数字 --》10进制 00000000-11111111 2^8-1 0-255,-2^7-27-1,3位数
int ：4个字节： 32位二进制数字–》10进制 0-2^32-1 , -2^31~231-1，10位数
bigint ：8个字节： 64位二进制数字–》10进制 0-2^64-1 , -2^63~263-1，20位数
字符串：
char(20) : 最多存储20个字符。最多支持255个字符。 定长。
varchar(20)：最多存储20个字符。最多支持65535个字符。变长。额外占用1-2字节长度存储字符长度。
abcdef—》6个字节 + 1字节
虽然理论上varchar(65535) 但是我们建议，还是不要超过255，会自动转换成TEXT，导致无法创建
BTREE索引，只能使用全文索引。我们建议使用ES数据库存储大字段。

enum(‘bj’,‘sh’) 枚举类型

id name telnum addr enum(‘bj’,‘sh’)
1 zs 1110 0 0 1
1 zs 1110 1
1 zs 1110 2
…

时间 ：
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会受到时区的影响

1.3.4 对象属性–约束和其他属性

作用对象： 表。
（1）Primary KEY： 主键约束 PK
特点：非空、唯一
一般建议是自增长的数字列。
AUTO_INCREMENT
每张表只能一个主键。
（2）NOT NULL 非空
（3）Unique KEY 唯一约束
（4）unsigned 无符号约束，只是针对数字类型。
（5）default 默认值
（6）comment 注释
（7）表字符集，引擎，校对规则，表的注释。

1.4

4.1 mysql连接工具内置功能

4.1.1 专用参数

-u  -p  -h -P  -S -e <
mysql -uroot -p123 -S /tmp/mysql.sock 
mysql -uoldguo -p123 -h10.0.0.51 -P3306
mysql -uroot -p123 -S /tmp/mysql.sock  -e "select @@port"
mysql -uroot -p123 -S /tmp/mysql.sock  </root/world.sql

4.1.2 mysql 命令窗口自带命令

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

日常功能：
help 打印帮助
ctrl+c 结束上个命令运行
ctrl+d 退出mysql会话窗口
\G 数据行以键值对方式，垂直显示。
tee 记录操作日志
system 调用OS命令
use 进入到某个库

2 DDL 数据定义语言

2.1 操作对象

库 、表

2.2 库的定义

--- 1. 创建库
CREATE DATABASE wordpress CHARSET utf8mb4;
--- 2. 查询库定义
SHOW DATABASES; -- 查看库名
SHOW CREATE DATABASE wordpress; -- 查看建库的具体信息
--- 3. 修改库定义 
SHOW CREATE DATABASE oldboy;
ALTER DATABASE oldboy CHARSET utf8mb4;
--- 4. 删库
DROP DATABASE oldboy;

开发规范

1. 库名： 
业务有关、
不要有数字开头、
不能有大写字母、
不要超过18个字符、
不要用系统关键字。
2. 必须设置字符集。建议使用UTF8MB4。
3. 修改字符集需要严格超集。
4. 生产业务中禁用DROP操作。 

2.3 表的定义

--- 1. 创建表 
use school;
CREATE TABLE `sch_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(64) NOT NULL COMMENT '学生姓名',
  `age` tinyint(4) NOT NULL DEFAULT '0' COMMENT '学生年龄',
  `gender` char(1) NOT NULL DEFAULT 'N' COMMENT '学生性别',
  `addr` enum('北京市','上海市','天津市','重庆市') NOT NULL DEFAULT '北京市' COMMENT '学员地址',
  `intime` datetime DEFAULT NULL COMMENT '入学时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 comment '学生表';

建表规范

1. 表名： 
业务有关、
不要有数字开头、
不能有大写字母、
不要超过18个字符、
不要用系统关键字。
2. 列名： 和业务功能有关，不要超过18字符
3. 选择合适的、简短的、足够的数据类型。
4. 必须要有主键
5. 每个列进行非空约束
6. 设置默认值。
7. 每个列要加注释。
8. 表属性：引擎、字符集、表注释

 --- 克隆表（表结构）
CREATE TABLE t1 LIKE sch_student;


--- 2. 查询表定义
DESC sch_student;
SHOW CREATE TABLE sch_student;
SHOW TABLES;

--- 表状态查询
SHOW TABLE STATUS LIKE 't%';            --  表状态
SELECT * FROM mysql.innodb_table_stats; --  统计信息

--- 3. 修改表定义 

--- 添加列
ALTER TABLE t1 ADD telnum CHAR(11) NOT NULL UNIQUE KEY COMMENT '手机号';
DESC t1;

--- 修改列的数据类型
ALTER TABLE t1 MODIFY NAME VARCHAR(32) NOT NULL COMMENT '学员姓名';
--- 修改列名name为sname
ALTER TABLE t1 CHANGE NAME sname VARCHAR(64) NOT NULL COMMENT '学员姓名';
--- 删除列 
ALTER TABLE t1 DROP telnum;

--- 4. 删除表
DROP TABLE t1;
SHOW TABLES;

注意：  Online-DDL处理细节
	修改表定义，即对表的结构修改： 添加、删除列、修改列属性、修改列名，索引管理等。
	在操作时，是需要进行表的加锁。在业务繁忙期间是不建议做的。
	如果必须要做的话，建议ONLINE DDL操作。我们更推荐使用第三方的工具，例如：PT-OSC,GH-OST工具进行
	Online DDL的操作，减少对线上业务的影响。

3 DML

3.1 作用

针对 表 的数据行 ，进行的增、删、改、查操作。

3.2 简单数据查询

mysql> select user ,host from mysql.user;
mysql> select *  from mysql.user;

3.3 插入数据

--- 标准的方式
USE school;
DESC sch_student;
INSERT INTO 
sch_student(id,NAME,age,gender,addr,intime)
VALUES(1,'zhang3',18,'M','北京市','2020-03-17 9:00:00');
SELECT * FROM sch_student;
--- 简化的插入方法
INSERT INTO 
sch_student
VALUES(2,'zhang4',18,'F','北京市','2020-03-16 9:00:00');

--- 按列插入数据
INSERT INTO 
sch_student(NAME,intime)
VALUES('li4',NOW())

SELECT * FROM sch_student;

--- 批量插入数据
INSERT INTO 
sch_student(NAME,intime)
VALUES('a',NOW()),('b',NOW())
,('c',NOW());

3.4 更新 update

UPDATE sch_student 
SET NAME='wang5' 
WHERE id=4 ;
SELECT * FROM sch_student;
注意： update语句必须加where条件。

3.5 删除 delete

DELETE FROM sch_student WHERE id=6;
SELECT * FROM sch_student;
注意： 必须要加where条件。

伪删除

删除操作，有没有替代方案？
SELECT * FROM sch_student ;
--- 1. 原来业务语句
DELETE FROM sch_student WHERE id=5;
SELECT * FROM sch_student ;
--- 2. 添加一个状态列
ALTER TABLE sch_student 
ADD state TINYINT NOT NULL DEFAULT 1 
COMMENT '1代表存在,0代表删除！';
--- 3. 替换原来业务语句
UPDATE sch_student SET state=0 WHERE id=5;
SELECT * FROM sch_student WHERE state=1;

以下语句的区别
DROP TABLE t1;  : 删除的是表定义+所有数据行。物理。立即释放磁盘空间
DELETE FROM t1; ：逐行删除表中的数据。逻辑。仅仅是打上了删除的标签，不会立即释放空间而且会产生大量日志。
trucate TABLE t1; 清空表的数据页。物理。立即释放磁盘空间

4 DQL 数据查询语言

4.1 作用

获取 表 中数据行。

4.2 类型

select
show

4.3 select 语句应用

1. 单独使用

1.1查询数据库参数

SELECT @@port;
SELECT @@basedir;
SELECT @@datadir;
SELECT @@innodb_flush_log_at_trx_commit;
SHOW VARIABLES;
SHOW VARIABLES LIKE '%trx%';

1.2 调用函数

USE mysql
SELECT DATABASE();
SELECT NOW();
SELECT CONCAT("hello !!")
SELECT CONCAT(USER,"@",HOST) FROM mysql.user;

2. SELECT 单表标准语法

2.1 SELECT 语句的多子句执行逻辑

SELECT 
--> FROM     表1,表2,...
--> WHERE 
--> GROUP BY 
--> HAVING 
--> ORDER BY 
--> LIMIT

如何了解业务？
--- 最好的办法是有注释。
--- world.city
USE world;
SHOW TABLES;
--- city  城市信息表
DESC city;
---- id          : 主键，城市编号。
---- NAME        : 城市名
---- countrycode : 国家编码？CHN、USA
---- district    : 区域 省
---- population  : 人口

SELECT * FROM city WHERE NAME='shenyang';
SELECT * FROM city WHERE NAME='jinan';

2.2 SELECT 配合 FROM 使用

例子1： 查询city表中所有数据(不代表生产操作！！！)
SELECT * FROM world.city;  
或者：
USE world;
SELECT * FROM city;
--- 注意：大表不要使用全表查询。

--- 例子2： 查询city表中每个城市的人口数。
SELECT NAME,population FROM world.city;

2.3 SELECT + FROM + WHERE 应用

**WHERE 等值条件查询** 
--- 例子3：查询中国所有城市信息 
SELECT  *  FROM world.city 
WHERE countrycode='CHN';

 **WHERE 条件判断符( > < >= <= != <> )**
--- 例子4： 查询城市人口数小于100人的城市
SELECT  *  FROM world.city 
WHERE population<100;

**WHERE 逻辑连接符( AND OR ,... )**
--- 例子5: 查询中国，山东省的城市信息。
SELECT * FROM city 
WHERE countrycode='CHN' 
AND district='shandong';

--- 例子6：查询中国或美国的城市信息
SELECT * FROM city 
WHERE countrycode='CHN'  OR countrycode='USA';

2.4 where 模糊查询(like)

--- 例子7：查询 城市名为 qing开头的城市信息
select * from city where name like 'qing%';
SELECT * FROM city WHERE NAME LIKE '%qing%'; ---> 不走索引，生产中禁用

2.5 WHERE 其他 (IN ,NOT IN, BETWEEN AND )

--- 例子8：查询中国或美国的城市信息
SELECT * FROM city 
WHERE countrycode IN ('CHN','USA');


SELECT * FROM city WHERE countrycode='CHN' 
UNION ALL
SELECT * FROM city WHERE countrycode='USA'

聚合查询： 把结果集合并到一起显示。
union all ： 只合并不去重复 
union     ： 合并并去重复
性能会有提升或下降趋势。



--- 例子9：查询中国或美国之外的城市信息
SELECT * FROM city 
WHERE countrycode NOT IN ('CHN','USA');

--- 例子10：查询人口数在1000w到2000w的城市信息
SELECT * FROM city
WHERE population > 10000000 AND population<20000000;

SELECT * FROM city 
WHERE population BETWEEN 10000000 AND 20000000;
--- 说明： BETWEEN AND 是包含头尾，一般针对数字列。

3.GROUP BY + 聚合函数应用

3.1 聚合函数(统计函数)

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

3.2 group by

select addr,count(name) from t1 group by addr;
工作逻辑： 
1. 取出相关列 addr和name列
2. 按照addr进行排序
3. 对于addr进行去重复
4. 对name列进行聚合操作

--- 例子1: 统计每个国家的城市个数。 
SELECT countrycode,COUNT(NAME)
FROM city 
GROUP BY countrycode;

--- 例子2：统计中国每个省的城市个数。
select  district , count(name)
from city 
where countrycode ='CHN'
group by  district 

--- 例子3： 统计每个国家的人口总数

SELECT countrycode,SUM(population)
FROM city 
GROUP BY countrycode 

--- 例子4： 统计中国每个省的总人口数
SELECT  district , SUM(population)
FROM city 
WHERE countrycode ='CHN'
GROUP BY  district 

--- 例子5： 统计中国每个省的城市个数，和每个省的城市名列表。
SELECT  district , COUNT(NAME),group_concat(NAME)
FROM city 
WHERE countrycode ='CHN'
GROUP BY  district 

4. having 子句

--- 例子6： 统计中国每个省的城市个数，和每个省的城市名列表，只显示城市个数大于10个的。
SELECT  district , COUNT(NAME),group_concat(NAME)
FROM city 
WHERE countrycode ='CHN'
GROUP BY  district 
having count(name)>10;

5. order by 子句

--- 例子7： 统计中国每个省的城市个数，和每个省的城市名列表，按照城市数量排序现实结果
SELECT  district , COUNT(NAME),GROUP_CONCAT(NAME)
FROM city 
WHERE countrycode ='CHN'
GROUP BY  district 
ORDER BY COUNT(NAME) DESC;

6. limit 子句

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

7. 多表连接查询

7.1 作用

我们要查询的数据，存在于多个表中。

7.2 类型

笛卡尔乘积
内连接
外连接

7.3 多表连接环境

-- 项目构建
drop database school;
CREATE DATABASE school CHARSET utf8mb4;
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'),
(2,'zhang4',18,'m'),
(3,'li4',18,'m'),
(4,'wang5',19,'f'),
(5,'zh4',18,'m'),
(6,'zhao4',18,'m'),
(7,'ma6',19,'f'),
(8,'oldboy',20,'m'),
(9,'oldgirl',20,'f'),
(10,'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;

7.4 多表连接方式介绍

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

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  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 | bgx    | NULL | NULL   | NULL |
+-----+--------+------+--------+------+
4 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 | go     | 106 |
+------+--------+------+--------+-----+
4 rows in set (0.00 sec)

7.5 多表连接例子

套路： 
	1. 找到所有相关表
		a  join  b  on a.xx=b.yy 
		a  join   c on a.x=c.y   join   b  on c.e=b.f
	2. 找关联条件
	3. 列举其他查询条件

--- 例子1： 统计每位老师所讲的课程数量
select teacher.tname,count(course.cno) from teacher join course on teacher.tno = course.tno group by teacher.tname;

select concat(teacher.tname,"_",teacher.tno),count(course.cno) from teacher join course on teacher.tno = course.tno group by teacher.tno,teacher.tname;

--- 例子2：
world数据库：
查询一下世界上人口数量小于100人的城市名和国家名

1. city   coutry
2. city.countrycode country.code

select  city.name,country.name ,city.population
from city join country 
on  city.countrycode = country.code
where city.population<100;


--- 例子3：
world数据库：
查询城市shenyang，城市人口，所在国家名（name）及国土面积（SurfaceArea）

select  city.name,country.name ,city.population ,country.SurfaceArea
from city join country 
on  city.countrycode = country.code
where city.name='shenyang'

--- 例子4：
school数据库：
统计zhang3,学习了几门课
select 
concat(student.sname,"_",student.sno),
count(sc.cno)  
from sc 
join student 
on   sc.sno=student.sno 
group by student.sno,student.sname;;


--- 例子5：
查询zhang3,学习的课程名称有哪些?
select 
concat(student.sname,"_",student.sno),
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,student.sname;



--- 例子6：
查询oldguo老师教的学生名.

select 
concat(teacher.tname,"_",teacher.tno) ,
count(student.sname)  
from  teacher   
join course  
on teacher.tno=course.tno 
join sc  
on couurse.cno=sc.cno 
join student  
on sc.sno=student.sno 
where teacher.tname='oldguo' 
group by teacher.tno,teacher.tname;


--- 例子7：
查询oldguo所教课程的平均分数
SELECT 
CONCAT(teacher.tname,"_",teacher.tno,"_",course.cno,"_",course.cname),
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,teacher.tname,course.cno,course.cname;

--- 例子8：
每位老师所教课程的平均分,并按平均分排序
SELECT 
CONCAT(teacher.tname,"_",teacher.tno,"_",course.cno,"_",course.cname),
AVG(sc.score) 
FROM  teacher    
JOIN course   
ON teacher.tno=course.tno  
JOIN sc   
ON course.cno=sc.cno  
GROUP BY teacher.tno,teacher.tname,course.cno,course.cname
order by AVG(sc.score) ;

--- 例子9：
查询oldguo所教的不及格的学生姓名
select 
concat(teacher.tname,"_",teacher.tno) ,group_concat(concat(student.sname,"_",student.sno))
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,teacher.tname;

--- 例子10：
查询所有老师所教学生不及格的信息
select 
concat(teacher.tname,"_",teacher.tno) ,group_concat(concat(student.sname,"_",student.sno,"_",sc.score))
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,teacher.tname;

7.6 别名的应用

select 
concat(teacher.tname,"_",teacher.tno) ,group_concat(concat(student.sname,"_",student.sno,"_",sc.score))
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,teacher.tname;

表别名

SELECT 
CONCAT(a.tname,"_",a.tno) ,
GROUP_CONCAT(CONCAT(d.sname,"_",d.sno,"_",c.score))
FROM  teacher AS  a   
JOIN course AS b
ON a.tno=b.tno 
JOIN sc  AS c
ON b.cno=c.cno 
JOIN student  AS d 
ON c.sno=d.sno 
WHERE c.score<60
GROUP BY a.tno,a.tname;

列别名

SELECT 
CONCAT(teacher.tname,"_",teacher.tno,"_",course.cno,"_",course.cname) AS 老师信息,
AVG(sc.score) AS 平均分
FROM  teacher    
JOIN course   
ON teacher.tno=course.tno  
JOIN sc   
ON course.cno=sc.cno  
GROUP BY teacher.tno,teacher.tname,course.cno,course.cname
ORDER BY 平均分 DESC ;

5.元数据获取

5.1 什么是元数据

用来描述数据的数据。相当于Linux系统中的Inode。
存储在数据库系统表和文件中的。又被称之为“基表”
大部分的元数据，都被存储在 mysql库 、IS、 PS、sys库中，还有一部分存储在ibdata1、frm文件中。
元数据包含：权限，大小，状态，参数，属性，时间，时区等信息。
MySQL不建议，用户直接对于元数据表进行修改。所以提供了多种对于元数据访问的渠道。
如果需要修改元数据： DDL 、 DCL进行
如果需要查询元数据： show、 IS、PS、SYS进行查询

5.2 show 语句的介绍

show databases;             	#查询所有数据库
show tables;                	#查询当前库中的表 
show tables from world;      	#查询指定库中的表
show create database world; 	#查看建库语句
show create table world.city;   #查看建表语句
show table status like 'ci%'    #查看表的具体状态
show full processlist;          #查询所有会话信息
show variables like '%trx%'     #查看数据库参数配置
show status like '%com%'        #查看系统状态信息
show index from world.city      #查询表的索引信息
show engines;                   #查看支持的引擎
show engine innodb stauts \G    #查看InnoDB存储引擎状态
show charset;                   #查看支持的字符集
show collation;                 #查看校对规则
show binary logs                #查询二进制日志信息
show binlog events in ''        #查看二进制日志事件
show master status              #查看当前使用的二进制日志
show slave status               #查看从库状态 
show slave hosts                #查看从库主机列表
show relaylog events in ''      #查看中继日志事件

5.3 information_schema.tables 基础应用

5.3.1 tables的结构

mysql> use information_schema;
mysql> desc tables;
TABLE_SCHEMA    # 表所在的库 
TABLE_NAME      # 表名
ENGINE          # 表的存储引擎
TABLE_ROWS      # 表的数据行(有可能不准)
AVG_ROW_LENGTH  # 平均行长度
DATA_LENGTH     # 数据的存储大小（有可能不准确）
INDEX_LENGTH    # 索引的存储大小
DATA_FREE       # 碎片的数量
CREATE_TIME     # 创建时间
UPDATE_TIME     # 更新时间
TABLE_COMMENT   # 表注释

5.3.2 使用tables视图

--- 1. 统计每个库的表个数和表名
select table_schema,count(table_name),group_concat(table_name)
from information_schema.tables 
group by table_schema;

--- 2. 资产统计：统计全库数据量
SELECT SUM(table_rows*AVG_ROW_LENGTH+index_length)/1024/1024  AS total_mb
FROM information_schema.tables;

--- 3. 统计每个业务库的数据量，表个数，表名列表
SELECT table_schema,
COUNT(table_name),
GROUP_CONCAT(table_name),
SUM(table_rows*AVG_ROW_LENGTH+index_length)/1024/1024  AS total_mb
FROM information_schema.tables 
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql')
GROUP BY table_schema;

--- 4. 查询业务库中，非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';

--- 4.1 查询业务库中，非InnoDB的表批量替换为InnoDB
修改配置文件
vim /etc/my.cnf 
#添加配置并重启
secure-file-priv=/tmp

SELECT CONCAT("ALTER TABLE ",table_schema,".",table_name," ENGINE=INNODB;")
FROM information_schema.tables 
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql')
AND ENGINE!='INNODB' INTO OUTFILE '/tmp/alter.sql';

数据库巡检-schema部分

1. 资产巡检
2. 规范性巡检
   2.1 引擎 InnoDB
   2.2 表名大小写（正则，自己扩展一下），长度（字符长度函数，自己扩展一下）
   2.3 表注释
   2.4 表中的列情况巡检: desc columns

多表连接优化

驱动表？ —— 默认优化器帮助我们选择。主要看哪个表有索引。
强制左表为驱动表：
B left join A on A.x=B.y
多表连接选择驱动表，一般是将结果集小的表作为驱动表。

统计各位老师,所教课程的及格率
及格的人数/总人数
CASE WHEN sc.score>60 THEN 1 END
CASE WHEN 1=1  THEN "hello" END

SELECT course.`cname`,
teacher.`tname`,
COUNT(CASE WHEN sc.score>60 THEN 1 END)/COUNT(sc.`score`)
FROM sc 
JOIN course
ON sc.cno=course.cno
JOIN teacher
ON teacher.tno=course.tno
GROUP BY course.`cname`,teacher.`tname`;


 统计每门课程:优秀(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 sc 
JOIN course
ON sc.cno=course.cno
JOIN student
ON student.sno=sc.sno
GROUP BY course.cname;