第三章 SQL 基础及元数据获取

第三章 SQL 基础及元数据获取

1. 名词认识

1.1 sql_mode

1.1.1 作用

数据准确 符合常识 5.7版本启用了严格模式.

1.1.2 查看

mysql> select @@sql_mode;
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

1.2 字符集 (charset)

1.2.1 作用

字符转换 

1.2.2 查看

mysql> show charset;
8.0     默认:  utf8mb4 
8.0 之前默认:  latin1

1.2.3 面试问题

utf8 : 最大存储3字节字符,中文占3字节,数字\字母\特殊符号占1字节
uf8mb4 : 最大存储4字节字符,中文占3字节,数字\字母\特殊符号占1字节,可以存储emoji表情

1.3 排序规则 (collation)

1.3.1 作用

影响排序

1.3.2 查询

mysql> show collation;

1.4 数据类型

1.4.1 作用

控制存储数据的"格式"和规范.

1.4.2 种类

a. 数字

整数 : 
        tinyint   
        int  
        bigint
浮点数:
        decimal

彩蛋:
\1. 区别
tinyint 1bytes 0-255 -128~127
int 4bytes 0-2^32-1 -231~231-1 10位数
bigint 8bytes 0-2^64-1 -263~263-1 20位数
\2. 浮点数你们公司怎么存储的?
金钱(精度要求高)有关的decimal
精度要求不高的,放大N倍,用整数类型.

b. 字符串

    char(10) 
    varchar(10)
    enum('sd','sx','hb','xx'.....)
           1   2  .....

彩蛋: 面试题 : char(10) 和 varchar(10) 区别
共同点: 都是字符串类型,最多都只能存10个字符.

不同点:

• char定长,varchar变长.如果存储变长字符串,一般建议varchar
• varchar类型,额外占用1-2bytes,存储字符长度.

c. 时间类型

    timestamp  4字节      1970-1-1   2038-1-19
    datetime   8字节      10000-1-1  9999-12-31

d. 二进制

e. json

1.5 约束

    PK   primary key   非空且唯一
    NN   not null      非空 
    UK   unique key    唯一 
    FK
建议:  
        1. 每张表设置主键,建议是数字自增列 
        2. 尽量对每个列设置非空

1.6 其他属性

default        默认值
auto_increment 自增长(主键)
unsigned       数字列,无符号(非负数)
comment        注释

2. SQL 介绍

2.1 简介

结构化查询语言. 在RDBMS中通用的一类语言.符合SQL89 SQL92 SQL99 等国际标准.

2.2 种类

DDL : 数据定义语言
DCL : 数据控制语言
DML : 数据操作语言
DQL : 数据查询语言

3. DDL 数据定义语言

3.1 库定义

a. 增

CREATE DATABASE wordpress CHARSET utf8mb4;

b. 查

mysql> show databases;
mysql> show create database test;

c. 删 (不代表生产操作)

mysql> drop database test;

d. 改

mysql> alter database wordpress charset utf8;

规范:
\1. 禁止线上业务系统出现DROP操作.
\2. 库名: 不能大写字母,不能是关键字,不能使数字开头.
\3. 显式的设置字符集.

3.2 表定义

a. 增

USE test;
CREATE TABLE stu (
id      INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname   VARCHAR(64) NOT NULL COMMENT '姓名',
age     TINYINT UNSIGNED NOT NULL DEFAULT 99 COMMENT '年龄',
gender  ENUM('M','F','N') NOT NULL DEFAULT 'N' COMMENT '性别',
telnum  CHAR(15) NOT NULL DEFAULT '0'  COMMENT '手机号'
)ENGINE=INNODB CHARSET=utf8mb4 COMMENT '学生表'; 

CREATE TABLE t1 LIKE student;

b. 查

USE test;
SHOW TABLES;
SHOW TABLES FROM mysql;
DESC student;
SHOW COLUMNS FROM student;
SHOW CREATE TABLE student;

c. 改

-- 在stu表中添加 addr 列
DESC stu;
ALTER TABLE stu ADD COLUMN addr VARCHAR(100) NOT NULL COMMENT '地址';

ALTER TABLE stu ADD a INT AFTER sname;
ALTER TABLE stu ADD b INT FIRST;

-- 删除列 
ALTER TABLE stu DROP a;
ALTER TABLE stu DROP b;
DESC stu;

-- 修改stu表中sname列,数据类型长度为varchar(128)
ALTER TABLE stu MODIFY  sname VARCHAR(128) NOT NULL COMMENT 'xm';
DESC stu;

-- 修改stu表sname列为stuname varchar(64) 
ALTER TABLE stu CHANGE sname stuname VARCHAR(64) NOT NULL COMMENT 'xm';
DESC stu;

d. 删 (不代表生产操作)

drop table stu;

DDL 规范
库的DDL:
\1. 禁止线上业务系统出现DROP操作.
\2. 库名: 不能大写字母,不能是关键字,不能使数字开头.一般和业务有关.
\3. 显式的设置字符集.
表的DDL :
\1. 建表时,表名小写, 建议格式: wp_user,不要出现数字开头和大写字母
\2. 显式的设置存储引擎\字符集\表的注释.
\3. 列名要和业务有关
\4. 列的数据类型,讲究:完整\简短\合适,精度不高浮点数,放大N倍.
\5. 每个表必须要有主键,数字自增无关列.
\6. 每个列尽量是非空的,而且设置默认值.
\7. 每个列要有注释.
\8. 变长列,一般选择varchar类型,定长列一般选择char.
\9. 大字段,可以选择附件形式,可以选择ES.
\10. 对于Online-DDL ,对于追加方式添加列,可以online,添加索引可以online(8.0)
其他情况下,需要在数据库低谷时间点去做.如果很紧急,pt-osc或者gh-ost

f. 面试题:

https://www.jianshu.com/p/eba3a5541c4c

4. DCL

grant 
revoke 

5. DML

5.1 作用

对表中的数据行进行增删改查.

5.2 insert

INSERT INTO 
    stu(id,stuname,age,gender,telnum,addr)
VALUES 
    (1,'zs',18,'M','110','bj');

INSERT INTO 
    stu(id,stuname,age,gender,telnum,addr)
VALUES 
    (2,'李四',18,'M','110','bj');

INSERT INTO 
    stu
VALUES 
    (3,'aaa',18,'M','110','bj');
    
INSERT INTO 
    stu
VALUES 
    (4,'aaa',18,'M','110','bj'),
    (5,'aaa',18,'M','110','bj');

INSERT INTO 
stu(stuname,addr)
VALUES
('asdasd','sh');

SELECT * FROM stu;

5.3 update

UPDATE stu SET stuname='w5' WHERE id=4;

注意:生产应用中,update必须要加where条件.
mysql> set global sql_safe_updates=1;
my.cnf

5.4 delete

DELETE FROM stu  WHERE id=5;

5.5 彩蛋

伪删除,使用update替换delete
ALTER TABLE stu ADD state TINYINT NOT NULL DEFAULT 1 COMMENT ‘状态:1为存在,0为不存在’;
SELECT * FROM stu;
UPDATE stu SET state=0 WHERE id=4;
SELECT * FROM stu WHERE state=1;

drop table stu truncate table stu delete from stu 区别 ?
drop : 表定义+数据全部删除,立即释放磁盘空间.
truncate : 整表所有数据全部删除,清空数据页,立即释放磁盘空间.
delete : 逐行"删除"(只是打一个标记),表中每行数据,逻辑删除,不会立即释放磁盘.HWM(高水位线)没有降低

6. DQL-select 单表查询

6.1 select

6.1.1 select 单独使用

a.查询系统变量(配置参数)

mysql> select @@basedir;
mysql> show variables like '%trx%';
mysql> show variables ;

b. 调用函数 
mysql> select now();
mysql> select database();

6.1.2 标准 select 应用(单表)

语法顺序
select 列...  from 表
where 
group by  
having 
order by  
limit  

6.1.3 select+from

-- select+from+where
--- WHERE + 等值查询
--- 中国(CHN)所有城市
SELECT * FROM city WHERE countrycode='CHN';
SELECT NAME,population FROM city WHERE  countrycode='CHN';

--- WHERE + 条件判断(< > >= <= !=)
--- 查询少于100人城市信息
SELECT * FROM city  WHERE population<100;   

--- WHERE + 逻辑判断符(AND OR )
--- 查询中国人口大于500w
SELECT * FROM city 
WHERE 
countrycode='CHN' 
AND 
population>5000000;

--- 查询中国或美国的城市 
SELECT * FROM city 
WHERE
countrycode='chn'
OR 
countrycode='USA'; 

--- WHERE + LIKE 模糊查询
---  查询国家代号是CH开头的城市信息
SELECT * FROM city WHERE countrycode LIKE 'CH%';

--- WHERE + BETWEEN AND 
--- 查询人口数在100w到200w之间的城市信息
SELECT * FROM city
 WHERE  
population BETWEEN 1000000 AND 2000000;

SELECT * FROM city
WHERE  
population >=1000000
AND 
population <=2000000;

6.1.4 group by + 聚合函数

group by 作用及原理

按照固定条件进行分组。
原理画图说明。

sql_mode

ONLY_FULL_GROUP_BY

mysql> select countrycode ,sum(population) from world.city group by countrycode;

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'world.city.Population' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

如果有group by, select 后的列,要么是group by条件,要么是在聚合函数里处理.

例子 :

-- 统计每个国家的总人口 

SELECT  countrycode , SUM(Population)   FROM  city
GROUP BY  countrycode;


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

-- 统计中国每个省的城市个数

SELECT  district ,COUNT(*)  FROM  city 
WHERE countrycode='CHN'
GROUP BY district 

-- 统计中国每个省 :城市个数,城市名列表.
SELECT  district ,COUNT(*) ,GROUP_CONCAT(NAME)  FROM  city 
WHERE countrycode='CHN'
GROUP BY district ;

6.1.5 having group by 后过滤

SELECT  district ,COUNT(*)  FROM  city 
WHERE countrycode='CHN'
GROUP BY district 
HAVING COUNT(*) >10

6.1.6 order by

--  查询中国所有城市,并按人口数排序输出
SELECT * FROM city 
WHERE countrycode='CHN'
ORDER BY Population DESC;

-- 统计中国每个省的总人口,过滤输出总人口超过1000w,从大到小排序输出.

SELECT   district , SUM(population) FROM city 
WHERE countrycode='CHN'
GROUP BY district 
HAVING  SUM(population)>10000000
ORDER BY SUM(population) DESC ;

6.1.7 limit

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


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

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

6.1.8 union 和 union all

(SELECT   district , SUM(population) FROM city 
WHERE countrycode='CHN'
GROUP BY district 
HAVING  SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 3)
UNION ALL 
(SELECT   district , SUM(population) FROM city 
WHERE countrycode='CHN'
GROUP BY district 
HAVING  SUM(population)>5000000
ORDER BY SUM(population) 
LIMIT 3)

7. DQL-Select 多表连接

7.1 连接方式

a. 笛卡尔乘积

mysql> select * from a,b;

b. 内连接

mysql> select * from a join b on a.id=b.aid;

c. 外连接

mysql> select * from a left join b on a.id=b.aid;
mysql> select * from a right  join b on a.id=b.aid;
mysql> select * from a left join b on a.id=b.aid and  b.aid is null;

7.2 例子:

7.2.1 准备工作

student ：学生表

sno： 学号
sname：学生姓名
sage： 学生年龄
ssex： 学生性别

teacher ：教师表

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

course ：课程表

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

score ：成绩表

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

项目构建
CREATE DATABASE school CHARSET utf8mb4;
USE school
source /root/school.sql

7.2.2 练习

-- 统计下每个学生平均成绩 

-- a.分析题意,找出所有相关表 
student
sc

-- b. 找到以上表的直接或间接关联条件
student.sno
sc.sno

-- c. 列举你要查询的列条件 
student.sname  
sc.score 

-- d. 组合 

SELECT student.sname,AVG(sc.score) 
FROM student JOIN sc ON student.sno=sc.sno
GROUP BY student.sno;


-- 练习 : 
-- 1.  每位学生学习的课程门数

SELECT student.sname,COUNT(sc.score)
FROM student JOIN sc  ON student.sno=sc.sno 
GROUP BY student.sno;


-- 2.  每位老师所教的课程门数

SELECT teacher.tname ,COUNT(course.cname)
FROM teacher JOIN course
ON teacher.tno=course.tno
GROUP BY teacher.tno;

-- 3.  每位老师所教的课程门数和名称

SELECT teacher.tname ,COUNT(course.cname),GROUP_CONCAT(course.cname)
FROM teacher JOIN course
ON teacher.tno=course.tno
GROUP BY teacher.tno;

-- 4.  每位学生学习的课程门数和名称
SELECT student.sname,COUNT(sc.score),GROUP_CONCAT(course.cname)
FROM student
JOIN sc 
ON student.sno=sc.sno
JOIN course
ON sc.cno=course.cno
GROUP BY student.sno;



-- 多表SQL练习题
-- 多表SQL练习题
-- 1. 统计zhang3,学习了几门课

SELECT student.sname ,COUNT(*) 
FROM student 
JOIN sc 
ON student.sno=sc.sno
WHERE student.sname='zhang3';

-- 2. 查询zhang3,学习的课程名称有哪些?

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

-- 3. 查询oldguo老师教的学生名.
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

-- 4. 查询oldguo所教课程的平均分数

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;

-- 5. 每位老师所教课程的平均分,并按平均分排序

SELECT teacher.tname,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 , course.cno
ORDER BY AVG(sc.score) DESC;


-- 6. 查询oldguo所教的不及格的学生姓名
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


-- 7. 查询所有老师所教学生不及格的信息
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

-- 8. 查询平均成绩大于60分的同学的学号和平均成绩；
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)>60

-- 9. 查询所有同学的学号、姓名、选课数、总成绩；
SELECT student.sno ,student.sname,COUNT(*),SUM(sc.score)
FROM student 
JOIN sc 
ON student.sno=sc.sno
JOIN course
ON sc.cno=course.cno
GROUP BY student.sno

-- 10.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 

SELECT course.cno,MAX(sc.score),MIN(sc.score)
FROM course
JOIN sc
ON course.cno=sc.cno
GROUP BY course.cno;

-- 11.查询每门课程被选修的学生数
SELECT course.cname,COUNT(sc.sno)
FROM course
JOIN sc 
ON course.cno=sc.cno
GROUP BY course.cno 

-- 12.查询出只选修了一门课程的全部学生的学号和姓名
SELECT course.cname,GROUP_CONCAT(CONCAT(student.sno,":",student.sname))
FROM course 
JOIN sc
ON course.cno=sc.cno 
JOIN student
ON sc.sno=student.sno
GROUP BY course.cno

-- 13.查询选修课程门数超过1门的学生信息
SELECT student.sname,COUNT(*)
FROM student
JOIN sc
ON student.sno=sc.sno
GROUP BY student.sno
HAVING COUNT(*)>1

-- 14.查询平均成绩大于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
-- 15.统计各位老师,所教课程的及格率

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

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

8. 别名应用

SELECT student.sno as '学号' ,student.sname as '姓名',AVG(sc.score) as '平均分'
FROM student
JOIN sc 
ON student.sno=sc.sno
GROUP BY student.sno
HAVING AVG(sc.score)>85

a. 列别名 
b. 表别名

自己扩展:
\1. 子查询
\2. 内置函数
\3. 存储过程,函数,触发器,事件,视图.

9. 元数据获取

9.1 show

show databases  
show tables 
show privileges
show grants for 
show create database /table 
show charset
show collation
show processlist
show variables 
show status 

show index from 
show engines
show binary logs 
show binlog events in 
show master status 
show slave status  
show slave hosts 
show plugins 
show engine innodb status 

2.2 information_schema

2.2.1. 介绍

视图. 查询元数据的方法

2.2.2. tables视图介绍

TABLE_SCHEMA 表所在库
TABLE_NAME 表名
ENGINE 存储引擎
TABLE_ROWS 表的行数(粗略统计)
AVG_ROW_LENGTH 平均行长度(粗略统计)
INDEX_LENGTH 索引长度(粗略统计)
DATA_FREE 碎片数
TABLE_COMMENT 表注释

2.2.2. tables视图应用例子:

1. 统计所有库,对应的表个数和名字列表 
库名       表个数       表名     
world       3           city,a,b 

select table_schema,count(*) ,group_concat(table_name)
from information_schema.tables
group by table_schema;
    

2. 统计每个库的数据量大小
select table_schema,sum(TABLE_ROWS*AVG_ROW_LENGTH+index_length)/1024
from information_schema.tables
group by table_schema;
    
3. 拼接语句 

a. 查询一下业务数据库中,非InnoDB的表
SELECT  table_schema , table_name ,engine FROM information_schema.tables 
WHERE 
table_schema NOT IN ('mysql','sys','information_schema','performance_schema') 
AND ENGINE <>'innodb';        

b. 将非InnoDB表批量替换为InnoDB
SELECT  concat("alter table ",table_schema,".",table_name," engine=innodb;") FROM information_schema.tables 
WHERE 
table_schema NOT IN ('mysql','sys','information_schema','performance_schema','world') 
AND ENGINE <>'innodb' into outfile '/tmp/alter.sql' ; 


mysqldump -uroot -p123 world city >/databak/world_city.sql

SELECT  concat("mysqldump -uroot -p123 ",table_schema," ",table_name," >/databak/",table_schema,"_",table_name,".sql") FROM information_schema.tables 
WHERE 
table_schema NOT IN ('mysql','sys','information_schema','performance_schema') into outfile '/tmp/bak.sh' ;

2.2.3 COLUMNS

TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
DATA_TYPE
COLUMN_KEY
COLUMN_COMMENT

select table_schema,table_name ,column_name from information_schema.columns
where table_schema NOT IN ('mysql','sys','information_schema','performance_schema');