一、SQL应用

1.client端

help（打印出client端的内置命令）
\c     ：结束上条命令运行
\G     ：格式化输出
exit   ：退出MySQL会话
\q     ：退出MySQL会话
ctrl+D ：退出MySQL会话
source ：导入SQL脚本，类似于 <
mysql> source /root/word.sql

system：调用linux命令
mysql> system ls;
anaconda-ks.cfg

mysql> system cd /tmp && ls;
vmware-root_885-4021784556  yum_save_tx.2021-02-25.14-53.TZNfy7.yumtx
vmware-root_910-2697139510

2.server端

linux中一切皆命令，linux中一切皆文件
MySQL中一切皆SQL，MySQL中一切皆库，表

二、DDL：数据定义语言

1.库定义：库名和库属性

字符集是必须要设置的（这里我借用的是sqlyog工具）

1）创建库：
CREATE DATABASE hsy CHARSET utf8mb4；
规范：
	1.库名：小写，与业务有关，不要数字开头，库名不要太长，不能使用保留字符串
	2.必须制定字符集
2）查询库：
mysql> show databases;
#查看建库语句：
mysql> show create database hsy;

3）修改库：（只能修改库属性，不能修改库名）
mysql> alter database ll  charset utf8mb4;
A ---> B
B是A字符集的严格超集

4）删除库：（生产环境中一般不建议使用）
注意：生产数据库中，除了管理员，任何人没有删库权限
mysql> drop database wordpress；

2.表定义

1>创建表
2>查询表
3>修改表
4>删除表

1）创建表

#开发人员工作
CREATE TABLE `wordpress`.`wp—user`(  
  `id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '用户序号',
  `name` VARCHAR(64) NOT NULL COMMENT '用户名',
  `age` TINYINT(3) UNSIGNED NOT NULL DEFAULT 18 COMMENT '年龄',
  `gender` CHAR(1) NOT NULL DEFAULT 'F'  COMMENT '性别',
  `cometime` DATETIME NOT NULL COMMENT '注册时间',
  `shengfen` ENUM('北京市','上海市','天津市','深圳市') NOT NULL DEFAULT '北京市'  COMMENT '省份',
  PRIMARY KEY (`id`)
) ENGINE=INNODB CHARSET=utf8mb4;

#建表规范：
1.表名：
	小写字母
	不能数字开头
	表名和业务相关
	名字不能过长
	不能使用关键字
2.必须设置存储引擎和字符集
3.数据类型，合适，简短，足够
4.必须要有主键
5.每个列都要设置not null ，在不知道填什么内容的时候，就设定默认值
6.每个列都要有注释
7.列名不要过长

#优化建议：
id太大，int（10）就足够
warehouse_id 如果是数字应该换数据类型为数字
station_region_id  同上
replenish_type 用enum可以节省空间
c_t 应该用时间类型
u_t 同上
is_deleted  tinyint（1）
字符集 utf8mb4 比较好

栗子：建表规范—>图表形式

2）查询表

mysql> show tables;
mysql> desc wp_users;
mysql> show create table wp_users;

3）修改表

#栗子：
1.添加手机号列：
mysql> alter table wp-users add column shouji bigint not null unique key comment '手机号';

2.将shouji列数据类型修改为char：
mysql> alter table wp-users modify shouji char（11）；（not null 的属性不复存在）

mysql> alter table wp-users modify not null unique key comment '手机号';（只修改数据类型）

3.删除手机号列：--->较危险
mysql> alter table wp-users drop shouji；

4)删除表

mysql> drop table wp-user;

三、DDL扩展：

1.手动创建一张表
USE hsy
CREATE TABLE hsy.student (
id             INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT'学号',
name           VARCHAR(64) NOT NULL COMMENT '学生名',
age            TINYINT UNSIGED NOT NULL DEFAULT 0 COMMENT '年龄',
xingbie        TINYINT NOT NULL DEFAULT 2 COMMENT '0 代表女，1代表男，2代表不详',
addr           ENUM('北京市','上海市','深圳市') NOT NULL DEFAULT '地址',
cometime       DATABASE NOT NULL DEFAULT NOW() COMMENT '入学时间',
telnum         BIGINT NOT NULL DEFAULT UNIQUE KEY COMMENT '手机号'，
)ENGINE=INNDB CHARSET=utf8mb4;

2.线上DDL（alter）操作对于生产的影响
SQL审核平台：yearing，inception
说明：
元数据是什么？ =------>类似于linux Inode信息
在MySQL中，DDL语句在表进行操作时，是要锁“元数据表”的，此时所有修改类的命令无法正常运行

所以：
	在对于大表，业务繁忙的表，进行线上DDL操作时，要谨慎
	尽量避开业务繁忙期间，进行DDL

面试题回答要点：
	1.SQL语句的意思是什么？
	以上4条语句是对2张核心业务表，进行DDL加列操作
	2.以上操作带来的影响？
	在MySQL中，DDL语句在表进行操作时，是要锁“元数据表”的，此时所有修改类的命令无法正常运行
	在对于大表，业务繁忙的表，进行线上DDL操作时，要谨慎
	3.我们的建议？
	（1）尽量避开业务繁忙期间，进行DDL，走流程
	（2）建议使用：pt—online-schema-change，gh-ost 工具进行DDL操作，减少锁表的影响
	（3）如果是8.0版本可以用pt工具，8.0以前需要借助于以上工具

四、DCL：数据控制语言

grant：授权
revoke：取消授权

五、DML：数据操作语言

insert
update
delete

1）作用

表中数据进行操作

2）insert应用

#规范用法：
DESC student；
INSERT INTO 
student（id，name，age，xingbie，addr，cometime，tel）
VALUES（1，'张三',18,1,'北京市','2020-02-17 11:50:58',110）;
SELECT * FROM student;

#简约的方法
INSERT INTO
student
VALUES(2,'hsy',18,1'张掖市','2020-02-18 11:58:02',119);

#部分录入数据
INSERT INTO
student(sname,telnum)
VALUES ('hsy',120);
SELECT * FROM student;

#批量录入方式
INSERT INTO
student(sname,telnum)
VALUES('hsy',111),(hsy1,112),(hsy2,113);
SELECT * FROM student;

3）update应用：修改指定数据行的值，可修改多行

#前提：必须要明确要修改哪一行，一般update的语句都有where的条件
UPDATE student SET sname='hhh' WHERE id =6;
SELECT * FROM student;

4）delete应用：删除指定行的值

前提：必须要明确要删哪些行，一般delete语句都有where的条件
DELETE FROM student WHERE id =6；

#扩展
1.伪删除
--需求：删除id为1的数据行
##原操作：DELETE FROM student WHERE id =1；
--查询数据：SELECT * FROM student;

##伪删除：
--修改表结构，添加状态列：
ALTER TABLE student ADD COLUMN state TINYINT NOT NULL DEFAULT 1；
--删除数据改为update：
UPDATE student SET state=0 WHERE id =1；
--查询语句修改为：
SELECT * FROM student WHERE state=1；

2.delete from student，drop table student，truncate table student区别？
说明：
	1.以上3条命令都可以删除全表数据
	2.区别：
	delete from student       
	逻辑上，逐行删除
	数据行多，操作就会很慢
	并没有真正从磁盘上删除,只是在存储层面打标记，磁盘空间不立即释放，HWM高水位线不会降低
	
	drop table student
	将表结构(元数据)和数据行物理层次删除，删的很彻底。
	
	truncate table student
	清空表段中的所有数据页，物理层次删除全表数据，磁盘空间立即释放，HWM高水位线会降低

六、DQL：数据查询语言

select
show
#上传world.sql的表：
[root@db01 ~]# mysql -u root -p < /root/world.sql 

1.select

1.1功能：获取表中的数据行（单表查询）

1.2select单独使用

1）select配合内置函数使用

#查看当前时间
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-03-02 09:12:50 |

#查看库
mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+


#配合concat查询mysql用户
mysql> select concat(user,"@",host)from mysql.user;
+-------------------------+
| concat(user,"@",host)   |
+-------------------------+
| hhh@10.0.0.%            |
| hsy@10.0.0.%            |
| hhh@localhost           |
| hsy@localhost           |
| mysql.session@localhost |
| mysql.sys@localhost     |
| root@localhost          |
+-------------------------+

#查看mysql的版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.33    |
+-----------+

#查看mysql是用哪个用户登录的
mysql> select user();
mysql> select user() from dual;
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+

#查看所有的函数
mysql> help contents;
mysql> help functions;

#查看端口
mysql> select @@port;
+--------+
| @@port |
+--------+
|   3306 |
+--------+

#查看数据路径
mysql> select @@datadir;
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+

#查看文件路径
mysql> select @@socket;
+---------------------------+
| @@socket                  |
+---------------------------+
| /var/lib/mysql/mysql.sock |
+---------------------------+

#又臭又长
mysql> select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
|                                1 |
+----------------------------------+

2）计算类

mysql> select 20*33;
+-------+
| 20*33 |
+-------+
|   660 |
+-------+

*用show替代

#显示全部参数（查看mysql设定的526个参数都可查看）
mysql> show variables\G
mysql> show variables like '%innodb%';

1.3select标准用法（配合其他句子使用）

--单表
前提：
select
1.from   表1，表2.....
2.where  过滤条件1，过滤出条件2......
3.group by 条件列1，条件列2......
4.having   过滤条件1，过滤条件2......
5.order by 条件列1，条件列2.......
6.limit     限制

1）select配合from语句的使用

环境准备
city                  #城市表
id                    #主键id
name                  #城市名
countrycode           #城市所在国家代号
district              #区域：省，州
population            #人口数

--语法：
SELECT 列 FROM 表；   cat/etc/passwd

#栗子：
SELECT id，name，countrycode，district，population FROM world.city;
SELECT * FROM world.city;
--查询部分列值，类似于awk取列
#查询城市名和对应的人口数
SELECT NAME,population FROM world.city;

2）select + from + where 配合使用 -----> 相当于grep命令，where 配合比较判断符号 =，>,<,>=,<=,!=

#查询city中，中国所有城市的信息
SELECT * FROM world.city WHERE countrycode='CHN';

#查询city中，人口数量小雨1000人的城市
SELECT * FROM world.city WHERE population<1000;

3）where 配合like语句模糊查询

#查询city中，国家代号是CH开头的城市信息
SELECT * FROM world.city WHERE countrycode LIKE 'CH%';
**注意：like语句在使用时，切记不要出现前面带%的模糊查询（只能用于字符串列）
#问题栗子：
SELECT * FROM world.city WHERE countrycode LIKE '%CH%';

4）where配合逻辑连接符 and or

#查询中国人口大于500万的
SELECT * FROM world.city WHERE countrycode='CHN' AND population > 5000000;
#查询中国或者美国的城市信息
SELECT * FROM world.city WHERE countrycode= 'CHN' OR countrycode = 'USA';
#查询中国或美国人口大于500万的
SELECT * FROM world.city WHERE countrycode IN ('CHN','USA') AND population >5000000;

5）where 配合 between and

#查询城市人口数在100万-200万中间
SELECT * FROM world.city WHERE population BETWEEN 1000000 AND 2000000; 

6）select + from + where + group by

group by 配合聚合函数使用（max()，min(),avg(),count(),sum(),group_contat()）
max()                #求最大值
min()                #最小值
avg()                #平均值
count()              #统计个数
sum()                #求和
group_contat()       #列转行
说明：碰到group_by必然会有聚合函数
#栗子：
--统计各个省份，学生的数量
SELECT addr COUNT(id) FROM t1 GROUP  BY addr;
--统计男生和女生分别有多少人
SELECT gender,COUNT(id) FROM t1 GROUP BY gender;

栗子：
1.统计city中，每个国家的城市个数
SELECT countrycode,COUNT(id) FROM world.city GROUP BY countrycode;
2.统计中国每个省的城市个数
SELECT district,COUNT(id) FROM world.city WHERE countrycode='CHN' GROUP  BY district;
3.统计每个国家的总人口
SELECT Countrycode,SUM(Population) FROM world.city GROUP BY Countrycode;
4.统计中国，每个省的总人口
SELECT district ,SUM(population) FROM world.city WHERE countrycode='CHN' GROUP BY district;
5.统计总共，每个省总人口，城市个数，城市名列表
SELECT district,SUM(population),COUNT(id),GROUP_CONCAT(NAME) FROM world.city WHERE countrycode='CHN' GROUP BY district\G

#错误语法
SELECT district,SUM(population),COUNT(id),NAME FROM world.city WHERE countrycode='CHN' GROUP BY district;
#报错：ERROR 1055 (42000): Expression #4 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
***5.6版本隐藏
***5.7版本会报错
***5.7版本SQL_mode的区别
sql_mode = only_full_group

说明：select list中的列，要么是group by的条件，要么在聚合函数中出现
原理：MySQL不支持，结果集是1行对多行的显示方式

#扩展：delete，drop。truncate如果不小心删除了，他们可以恢复吗？
常规方法：
以上三种问题都可以通过备份+日志，恢复数据
灵活方法：
delete可以通过，翻转日志（binlog）
三种删除数据情况，也可以通过延时从库恢复

1.4 group by + 聚合函数使用方法

1.应用场景：需对一张表中，按照不同数据特点，需要分组计算统计时，会使用group by + 聚合函数
2.group by 使用核心步骤：
	根据需求，找出分组条件
	根据需求，使用合适的聚合函数

#聚合函数
max()               ：最大值
min()               ：最小值
avg()               ：平均值
count()             ：统计个数
sum()               ：求和
group_concat()      ：列转行

1.select having ， order by limit使用

1）having语句

作用：与where子句类型，having属于后过滤
场景：需要在group by + 聚合函数，在做判断过滤后使用
#栗子：统计中国，每个省的总人口，只显示总人口数大于500万信息
SELECT district,SUM(population) FROM world.city WHERE countrycode='CHN' group by district havingSUM(population)>5000000;

2）order by应用

#栗子：统计中国，每个省的总人口，只显示总人口数大于500万，并且从大到小排序输出
（从小到大输出）
SELECT district,SUM(population) FROM world.city WHERE countrycode='CHN' group by district having SUM(population)>5000000 ORDER BY SUM(population);
（从大到小输出）
SELECT district,SUM(population) FROM world.city WHERE countrycode='CHN' group by district having SUM(population)>5000000 ORDER BY SUM(population)
DESC;

3）limit应用

作用：分页显示结果集
#栗子统计中国，每个省的总人口，只显示总人口数大于500万，并且从大到小排序输出，只显示前五名
SELECT district,SUM(population) FROM world.city WHERE countrycode='CHN' GROUP BY district HAVING SUM(population)>5000000 ORDER BY SUM(population)
DESC LIMIT 5;
#显示6-10行：（跳过5行，再显示5行）
SELECT district,SUM(population) FROM world.city WHERE countrycode='CHN' GROUP BY district HAVING SUM(population)>5000000 ORDER BY SUM(population)
DESC LIMIT 5,5;

SELECT district,SUM(population) FROM world.city WHERE countrycode='CHN' GROUP BY district HAVING SUM(population)>5000000 ORDER BY SUM(population)
DESC LIMIT 5 OFFSET 5;

#显示3-5名
LIMIT 2,3
LIMIT 3 OFFSET 2

1.5 多表连接查询

#环境准备
mysql> use school;
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| course           |
| sc               |
| student          |
| teacher          |
+------------------+

1）作用：

#为什么要使用多表连接查询？
我们的查询需求，需要的数据来自于多张表，单张表无法满足	

2）多表连接查询类型

1> 笛卡尔乘积

mysql> select * from teacher,sc;
mysql> select * from teacher join sc;
将多张表的数据合成到一张大表，方便查询

2> 内连接（应用最广泛）

语法： A join B on A.xx = B.yy

mysql> select * from teacher join sc on teacher.cno=sc.cno;
+--------+-----+-----+-----+-----+-------+
| tname  | tno | cno | sno | cno | score |
+--------+-----+-----+-----+-----+-------+
| old张  |   2 |   0 |   4 |   0 |     0 |
| old郭  |   1 |   0 |   4 |   0 |     0 |
| old高  |   3 |   0 |   4 |   0 |     0 |
+--------+-----+-----+-----+-----+-------+

3> 外连接

left join  :左表所有数据，右表满足条件的数据
right join ：右表所有数据，左表满足条件的数据
#栗子：
mysql> use world;
mysql> select city.name,country.name,city.population from city join country on city.countrycode = country.code where city.population < 100;
+-----------+----------+------------+
| name      | name     | population |
+-----------+----------+------------+
| Adamstown | Pitcairn |         42 |
+-----------+----------+------------+

mysql> select city.name,country.name,city.population from city left join country on city.countrycode = country.code and city.population < 100;

mysql> select city.name,country.name,city.population from city left  join country on city.countrycode = country.code and city.population < 100 order by city.population desc;

简单理解：多表连接实际上是将多张表中，有关联的部分数据合并成一张表。
		在新表中在去做，where，group_by ,order_by,limit,having	

多表连接查询使用技巧栗子：

例1.查询一下wuhan这个城市：国家名，城市名，城市人口，国土面积
--找关联表：
	city：
		城市名（city.name）
		城市人口（city.population）
	country：
		国家名（country.name）
		国土面积（country.surfacearea）
		
--找关联条件：
	mysql> desc city;
		--> city.countrycode
	mysql> desc country;
		--> country.code
	from city 
	join country 
	on city.countrycode=country.code

--罗列其他查询条件
mysql> select   city.name,city.population,country.name,country.surfacearea from city join country on city.countrycode=country.code where city.name='wuhan';
+-------+------------+-------+-------------+
| name  | population | name  | surfacearea |
+-------+------------+-------+-------------+
| Wuhan |    4344600 | China |  9572900.00 |
+-------+------------+-------+-------------+

mysql> select code,code2 from country limit 10;
+------+-------+
| code | code2 |
+------+-------+
| ABW  | AW    |
| AFG  | AF    |
| AGO  | AO    |
| AIA  | AI    |
| ALB  | AL    |
| AND  | AD    |
| ANT  | AN    |
| ARE  | AE    |
| ARG  | AR    |
| ARM  | AM    |
+------+-------+

例2：统计张珊学习了几门课
select student.sno,student.sname,course.cname from student join sc on student.sno = sc.sno join course on sc.cno = course.cno where student.sname='张珊';

例3：查询张珊学习的课程名称有哪些
SELECT student.sno,student.sname,GROUP_CONCAT(course.cname)
FROM student JOIN score
ON student.sno=score.sno
JOIN course
ON score.cno=course.cno
WHERE student.sname='张珊'
GROUP BY student.sno,student.sname;

例4：查询lod郭老师教的学生名
SELECT CONCAT(teacher.tname,"-"，teacher.tno),GROUP_CONCAT(student.sname)
FROM teacher
JOIN course
ON teacher.tno=course.tno
JOIN score 
ON course.cno=score.cno
JOIN student
ON score.sno=student.sno
WHERE teacher.tname='old郭'
GROUP BY teacher.tno,teacher.tname

例5：查询每位老师教课程的平均分，并按从大到小排序
SELECT CONCAT(teacher.tname,"-",teacher.tno),AVG(score.score)
FROM teacher
JOIN course
ON teacher.tno=course.tno
JOIN score
ON course.cno=score.cno
WHERE teacher.tname='old林'
GROUP BY teacher.tno,teacher.tname,course.cno;

例6：查询old高老师所教课程的平均分数
SELECT CONCAT(teacher.tname,"-",teacher.tno),AVG(score.score)
FROM teacher
JOIN course
ON teacher.tno=course.tno
JOIN score
ON course.cno=score.cno
GROUP BY teacher.tno,teacher.tname,course.cno;

例7：查询old张老师所教的不及格的学生姓名
SELECT CONCAT(teacher.tname,"-",teacher.tno),GROUP_CONCAT(CONCAT(student.sname,"-",score.score))
FROM teacher
JOIN course
ON teacher.tno=course.tno
JOIN score
ON course.cno=score.cno
JOIN student 
ON score.sno=student.sno
WHERE teacher.tname='old高' AND score.score < 60
GROUP BY teacher.tno,teacher.tname;

例8：查询所有老师所教学生不及格的信息
SELECT CONCAT(teacher.tname,"-",teacher.tno),GROUP_CONCAT(CONCAT(student.sname,"-",score.score))
FROM teacher
JOIN course
ON teacher.tno=course.tno
JOIN score
ON course.cno=score.cno
JOIN student 
ON score.sno=student.sno
WHERE score.score < 60
GROUP BY teacher.tno,teacher.tname;

例9：查询平均成绩大于60分的同学信息
SELECT score.sno,AVG(score.score)
FROM score
GROUP BY score.sno
HAVING AVG(score.score)>60;		

例10：查询所有同学的学号，姓名，选课数，总成绩
SELECT student.sno,student.sname,COUNT(score.cno),SUM(score.score)
FROM student
JOIN score
ON student.sno=score.sno
GROUP BY student.sno,student.sname;

例11：查询各科成绩最高和最低分，以如下形式显示：课程id，最高分，最低分
SELECT score.cno,MAX(score.score),MIN(score.score)
FROM score
GROUP BY score.score;

例12：统计各位老师，所教课程的及格率（用到了case语句）
SELECT CONCAT(teacher.tname,"-",teacher.tno),CONCAT(COUNT(CASE WHEN score.scor>60 THEN 1 END)/COUNT(score,sno)*100,"%") AS 及格率
FROM teacher 
JOIN course 
ON teacher.tno=course.tno
JOIN score
ON course.cno=score.cno
GROUP BY teacher.tno,teacher.tname

例13：查询每门课程呗选修的学生信息

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

例15：查询选修课程门数超过一门的学生信息

例16：查询选修课程门数：优秀（85分以上），良好（70-85），一般（60-SELECT course.cname
GROUP_CONCAT(CASE WHEN score.score>85 THEN student.sname END) AS 优秀
GROUP_CONCAT(CASE WHEN score.score>70 AND score.score<=85 THEN student.sname END) AS 良好
GROUP_CONCAT(CASE WHEN score.score>=60 AND score.score<=70 THEN student.sname END) AS 一般
GROUP_CONCAT(CASE WHEN score.score<60 THEN student.sname END) AS 不及格
FROM course 
JOIN  score
ON course.cno=score.cno
JOIN student
ON score.sno=student.sno
GROUP BY course.cname;

例17：查询平均成绩大于85的所有学生的学号，姓名和平均成绩

case语句的功能及语法

case when 判断 then 结果 end
#栗子：
mysql> select  case when score.score > 90 then 1 end from score;
+---------------------------------------+
| case when score.score > 90 then 1 end |
+---------------------------------------+
|                                  NULL |
|                                  NULL |
|                                  NULL |
|                                  NULL |
|                                  NULL |
|                                  NULL |
|                                     1 |
|                                     1 |
|                                     1 |
+---------------------------------------+

1.6 多表连接查询优化

作用：强制驱动表
#驱动表是什么？
在多表连接当中，承当for循环中外层循环的角色
此时，MySQL会拿着驱动表的每个满足条件的关联列的值，去依次找到for循环中的关联值一一进行判断和匹配，这种是mysql中最简单的算法，叫做next loop

建议：
1.小表作为驱动表，降低next loop次数
2.left join 可以强制左表为驱动表

#例如：
mysql> select city.name,city.population,country.name,country.surfacearea from city join country on city.countrycode=country.code where city.name='shanghai';
+----------+------------+-------+-------------+
| name     | population | name  | surfacearea |
+----------+------------+-------+-------------+
| Shanghai |    9696300 | China |  9572900.00 |
+----------+------------+-------+-------------+

可以改写为：强制驱动表的left join
mysql> select city.name,city.population,country.name,country.surfacearea from city  left join country on city.countrycode=country.code where city.name='shanghai';

1.7 select 扩展

1）select 别名

1> 列别名

SELECT student.sno AS '学号',student.sname AS '姓名',GROUP_CONCAT(course.cname)'课程列表' FROM student JOIN sc ON student.sno=sc.sno JOIN course ON sc.cno=course.cno WHERE student.sname='张珊' GROUP BY  student.sno,student.sname;

作用：
1.可以定制显示的列名
2.可以在having，order by 子句中调用

#栗子：
SELECT district AS 省,SUM(population) AS 总人口
FROM world.city
WHERE countrycode='CHN'
GROUP BY  district
HAVING 总人口 > 5000000
ORDER BY 总人口  DESC
LIMIT 5 OFFSET 0;

2> 表别名

作用：全局调用定义的别名
#栗子：
SELECT a.sno AS '学号',a.sname AS '姓名',GROUP_CONCAT(c.cname) '课程列表'
FROM student AS a
JOIN sc AS b
ON a.sno=b.sno
JOIN course AS c
ON b.cno=c.cno
WHERE a.sname='老男人'
GROUP BY a.sno,a.sname;

3> distinct应用：去重

mysql> SELECT DISTINCT(countrycode) FROM world.city;

4> union 和 union all

#栗子：查询中国或者美国的城市信息
写法1：mysql> SELECt * FROM world.city WHERE countrycode='CHN' OR countrycode='USA';
写法2：mysql> SELECT * FROM world.city WHERE countrycode in ('CHN','USA');
写法3：mysql> SELECT * FROM world.city WHERE countrycode='CHN' UNION ALL  SELECT *
FROM world.city WHERE countrycode='USA';

#面试题：
union和union all 的区别
1.union：聚合两个结果集，会自动进行结果集去重复
2.union all：聚合两个结果集，不会去重复

2.show语句介绍

1.show databases;             #查询所有的哭
2.show tables;                #查询use到库下的所有表
3.show processlist;           #查询所有用户连接情况
4.show full processlist;      #查询所有用户连接情况的详细信息
5.show charset;               #查询支持的字符集
6.show collation;             #查看支持的校对规则
7.show engines;               #查看支持的引擎信息
8.show privileges;            #查看支持的权限信息
9.show grants for;            #查看某用户的权限
10.show create database;      #查看建库语句
11.show create table;         #查看建表语句
12.show index from;           #查看表的索引信息
13.show engine innodb status; #查询InnoDB引擎状态
14.show status;               #查看数据库状态信息
15.show status like '%%';     #模糊查询数据库状态
16.show variables;            #查看所有数据库参数
17.show variables like '%%';  #模糊查询部分参数
18.show binary logs;          #查询所有二进制日志文件信息
19.show binlog events in;     #查询二进制日志的位置点信息
20.show slave status;         #查询从库状态
21.show relaylog events in;   #查看中继日志事件

#查看mysql支持的show语句
mysql> help show

show tables from world;    #查询world库下的表名

扩展：产品开发流程及pt-osc的工作原理

1.公司接单，开发一个wordpress 博客产品
2.设计产品功能，产品逻辑图
3.研发部门，设计和开发产品功能
	代码+数据库开发（ER图）
4.开发测试功能
5.运维交付生产系统
6.DBA：审核，SQL语句，制定备份策略，日常维护规范，监控，数据库架构设计，性能，故障。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wJNBGw1a-1615372857312)(C:\Users\86150\AppData\Roaming\Typora\typora-user-images\image-20210302191020009.png)]

1、创建一个和源表一样表结构的新表

2、在新表执行DDL语句（空表）

3、在源表创建三个触发器分别对应insert、update、delete操作

4、从源表拷贝数据到新表，拷贝过程中源表通过触发器把新的DML操作更新到新表中

5、rename源表到old表中，把新表rename为源表，默认最后删除源表

information_schema元数据获取

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yKZyRDPC-1615372857314)(C:\Users\86150\AppData\Roaming\Typora\typora-user-images\image-20210303171404429.png)]

七、元数据介绍

元数据是什么？------>类似于linux Inode信息
在MySQL中，DDL语句在表进行操作时，是要锁“元数据表”的，此时所有修改类的命令无法正常运行

1.视图

每次数据库启动，会自动在内存中生成I_S，生成查询MySQL部分元数据信息视图

#什么是视图？
select语句的执行方法，不保存数据本身，虚拟的一张表，不保存数据
I_S中的视图，保存的就是查询元数据的方法

视图的栗子：

vmysql> create view v_select as SELECT district,SUM(population) FROM world.city WHERE countrycode='CHN' GROUP BY district HAVING SUM(population)>5000000 ORDER BY SUM(population)
    -> DESC LIMIT 5 OFFSET 5;
    
mysql> select * from v_select;
+-----------+-----------------+
| district  | SUM(population) |
+-----------+-----------------+
| Guangdong |         9510263 |
| Hubei     |         8547585 |
| Jilin     |         7826824 |
| Peking    |         7569168 |
| Sichuan   |         7456867 |
+-----------+-----------------+

1.1 I_S.tables 视图

作用：保存了所有表的数据字典信息
mysql> use information_schema;
mysql> desc tables;
TABLE_SCHEMA           #表所在的库
TABLE_NAME             #表名
ENGINE                 #表的引擎
TABLE_ROWS             #表的数据行（不是实时的值）
AVG_ROW_LENGTH         #平均行长度
DATA_LENGTH            #表的存储空间大小（不是实时的）
INDEX_LENGTH           #表的索引占用空间大小
DATA_FREE              #表中是否有碎片（以字节为单位）

I_S.tables 企业应用案例

--例1：数据库资产统计---统计每个库下所有表的个数以及表名
SELECT table_schema,COUNT(table_name),GROUP_CONCAT(table_name)
FROM information_schema.tables
GROUP BY table_schema;

--例2：统计每个库的占用空间大小
方法一：
一张表大小公式 = avg_row_length*table_rows+index_length
#
SELECT table_schema,SUM(AVG_ROW_LENGTH*table_rows+index_length)/1024/1024
FROM information_schema.tables
GROUP BY table_schema;

方法二：
一张表大小公式 = data_length
SELECT table_schema,SUM(data_length)/1024/1024
FROM information_schema.tables
GROUP BY table_schema;

--例3：查询数据库（系统库除外），所有非InnoDB表
mysql> select table_schema,table_name from information_schema .tables where engine !='InooDB' and table_schema not in ('sys','performance_schema','information_schema','mysql');
+--------------+-----------------+
| table_schema | table_name      |
+--------------+-----------------+
| ll           | h1              |
| ll           | h2              |
| ltl          | wp-users        |
| school       | course          |
| school       | sc              |
| school       | student         |
| school       | teacher         |
| world        | city            |
| world        | country         |
| world        | countrylanguage |
+--------------+-----------------+

例4：查询数据库（系统库除外），所有非InnoDB表，并且将非InnoDB转换为InnoDB表
单表转换：alter table world.mytl engine=innodb;

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

#报错：ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
#报错处理：
修改配置文件后，重启数据库
[root@db01 ~]# cat /etc/my.cnf
[mysqld]
secure-file-priv=/tmp
cat /tmp/alter.sql