1.SQL介绍
结构化的查询语言，关系型数据库中通用的一类语言。
SQL标准 89 92 99 03
MYSQL
2.SQL 常用类型
2.1 mysql 客户端自带的功能
mysql>help
2.2 Server端分类命令
mysql>help contents
DDL : 数据定义语言 Data Definition language
DCL : 数据控制语言 Data Control language
DML : 数据操作语言 Data Manipulation language
DQL : 数据查询语言 Data Query language
3.SQL的各种名词
3.1 sql_mode SQL模式（5.7版本 不同版本的sql_mode规范不同）。
作用:规范SQL语句的书写形式。
mysql> select @@sql_mode;
@@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.varchar类型，在存储数据时，会先判断字符长度，然后合理分配存储空间。而char类型，不会判断，立即分配空间。在固定长度的列中，还会推荐选择char类型。
例子：
varchar(10)
abcde---->1.判断字符长度----->2.在申请空间---->3.存字符--->4.申请1个字节 存储5这个数字
char(10)
abcde---> 1.申请10个字符空间---->2.存字符+剩下的空格填充。
2.varchar类型，除了会存储字符串之外，还会额外使用1-2字节存储字符长度。
abcdef --->6+1
aaaaaaaaaa...254 ,254 +1
aaaaaaaaaaaaa.....1000,1000+2
3.应用场景
字符串固定长度的话:char
不固定用varchar类型
4.括号中数字问题
括号中，设置的是，字符的个数，无关字符类型。但是，不同种类的字符，占用的存储空间是不一样的。
对于英文和数字，每个字符占1个字节长度。
对于中文，占用空间大小，要考虑字符集。
utf8，utf8mb4,每个中文占3个字节。emoji字符，占4个字节长度。总长度不能超过数据类型的最大长度。
varchar:最大长度为65535。
枚举：enum
例子：
id num name shenfen sheng
1 8607 wx 533177x 云南
1 8607 wx 533177x 北京
1 8607 wx 533177x 河南
1 8607 wx 533177x 四川
1 8607 wx 533177x 山西
1 8607 wx 533177x 贵州
枚举就是：对一些基本不会变的东西，可以给他们一个代号，比如省份基本不变，可以给云南为1，北京为2，四川为3，用数字来代替省份。
好处：中文字符相比较于英文和数字占用的长度更多，空间更多，可以用小的代替大的。
- 节省存储空间
- 有利于数据库索引
时间类型
3.3.4二进制类型
3.3.5 json类型
{
id:1
name:'zhangsan'
}彩蛋：以上两种数据类型选择需考虑周全，会影响到索引应用。
3.4 约束
PK ：主键约束，作用：唯一+非空，每张表 只能有一个主键，作为聚簇索引。
not all ：非空约束，作用：必须非空，我们建议每个列都设置非空。
unique key : 唯一约束 作用：必须不重复的值
unsigned : 针对数字列，非负数。
3.5 其他属性
default ：默认属性
comment ：注释
4.SQL 应用
4.1 client
\c 结束上条命令的运行
\G 格式化输出
\q 退出mysql会话 （ctrl + d）
source 导出SQL脚本，类似于 <
system 调用linux命令
4.2 Server
linux中的一切皆命令，linux中的一切皆文件。
Mysql中一切SQL，Mysql一切皆库，表。
--- oldguo
4.2.1 DDL 数据库定义语言
（1）库定义 ：库名 库属性
创建库:
CREATE DATABASE OLDGUO CHARSET utf8mb4;
规范:
1.库名:小写，业务有关，不要数字开头，库名不要太长，不能使用保留字符串。
2.必须定制字符集。
（2）查库
mysql>show databases;
mysql>show create database oldguo;
（3）修改库
mysql>alter database oldguo charset utfmb4;
（4）删除库:危险，不代表生产操作。
注意：生产数据库中，除了管理员，任何人没有删库权限。
mysql>drop database oldguo;
（1）创建表
开发人员得工作:
CREATE TABLE `wp_users` (
`id` int(11) 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 DEFAULT CHARSET=utf8mb4
建表规范:
1.表名:
小写字母
不能数字开头
表名和业务有关
名字不要太长
不能使用关键字
2.必须设置存储引擎和字符集
3.数据类型：合适 简短 足够
4.必须要有主键
5.每个列尽量设置not all，s设定默认值
6.每个列要有注释
7.列名不要太长
（2）查询表
mysql>show tables;
mysql>desc t1;
mysql>show create table t1;
（3）修改表
例子：1.添加手机号码
mysql>alter table t1 add column shouji bigint not null unique key comment '手机号码’;
2.将手机数据类型修改为char(11)
mysql>alter table t1 modify shouji char(11) not null unique key comment '手机号码'
3.删除手机号列
mysql>alter tables t1 drop shouji;
注意：电话号码有13位，int最大10位，选择最好选择bigint 或者char
自己手撕的一张表
CREATE TABLE `wp_users` (
`id` int(11) 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 DEFAULT CHARSET=utf8mb4
1.线上DDL（alter）操作对生产的影响？
SQL审核平台:yearing,inception
说明:
元数据是什么？----->类似于linux Inode信息。（Inode信息相当于linux系统的数据库，里面存着各种各样的信息）。
在MYSQL中，DDL对表进行操作时，是要锁"元数据表的"。此时，所有修改类的命令无法正常运行。
所以:
在对于大表，业务繁忙的表，进行线上DDL操作时，需要谨慎。
面试回答要点:
1.SQL语句的意思是什么？
以上4条语句是对2张核心业务表，进行DDL加列操作。
2.以上操作带来的影响？
在MYSQL中，DDL语句在对表进行操作时，是要锁“元数据表的”。此时，所有修改类的命令无法正常运行。在对于大表，业务繁忙的表，进行线上DDL操作时，要谨慎。
3.建议
（1）尽量避开业务繁忙期间，进行DDL，走流程（发邮件一定要确认）。
（2）建议使用工具：pt-osc(pt-online-schema-change) gh-ost,减少锁表的影响。
（3）数据库8.0，可以不借助工具，8.0以前建议使用工具。
2.DCL 数据控制语言
grant
revoke
3.DML
（1）insert
-- 标准
-- insert into
-- student(id,sname,age,gender,addr,cometime,telnum)
-- values (3,'武哥',18,2,'重庆',now(),888)
-- 简约的
INSERT INTO
student
VALUES (4,'武少',19,2,'上海',NOW(),666);
-- 部分录入
INSERT INTO
student(sname,telnum)
VALUES ('雄少',122);
-- 批量录入
INSERT INTO
student(sname,telnum)
VALUES ('武少'，111),('何明瑞',222),('杨坤'，333);
（2）update
（3）delete
-- update delete
-- 修改指定数据行的值
-- 前提:必须要明确要改那一行，一般更改和删除需要配合where条件
UPDATE student SET sname='小武' WHERE id=17;
DELETE FROM student WHERE id=17;
SELECT * FROM student
拓展:
-- 伪删除
-- 需求：删除id为1的数据行
-- 原操作
DELETE FROM student WHERE id=1;
-- 查询数据
SELECT * FROM student;
-- 增加一个state列
ALTER TABLE student ADD COLUMN state TINYINT NOT NULL DEFAULT 1 COMMENT "状态";
-- 删除数据改为update
UPDATE student SET state=0 WHERE id=1;
-- 查询删除的数据
SELECT * FROM student WHERE state=1;
问:delete * from student,drop table student,truncate table student 的区别和联系？
delete * from student：
逐行删除，数据行多，操作很慢，并没有在物理磁盘上真正删除，只是在存储层面打标记，存储空间不释放。HWM高水位线不会下降。
drop table student：
将表结构和数据行物理层次删除。
truncate table student：
清空表段中的所有数据页，物理层次删除全表数据，存储空间马上释放，HWM高水位线马上下降。
4.DQL 数据查询语言
4.1 select
4.1.1 功能
获取表中的数据行
4.1.2 select 单独使用 （MYSQL独家）
（1）select配合内置函数使用
select now（）;
select concat(user,"@",host) from mysql.user;
select version();
select user();
select database();
help function
（2）计算
mysql> select 10*100;
（3）查询数据库参数
mysql> show variables like '%trx%';
mysql> select @@port
mysql> select @@datadir
mysql> select @@socket
4.1.3 select 标准用法(配合其他子句使用)
-- 单表
前提:
默认执行顺序
select
1.from 表1，表2.....
2.where 过滤条件1，过滤条件2 ...
3.group by 条件列1，条件列2
3.5 select_list 列名列表
4.having 过滤条件1 过滤条件2 ...
5.order by 条件列 条件列2
6.limit 限制
执行顺序逻辑图:
-- 1.select 配合 from 子句的使用
-- 语法:
-- select 列 from 表；cat /etc/psswd
-- 例子1:
-- 查询表中的所有数据，类似于： cat /etc/passwd
SELECT user_id,username,nickname,PASSWORD,telephone FROM t_user;
SELECT * FROM t_user;
-- 2.select + from +where 配合使用 ==》 相当于grep
-- 2.1 where 配合比较判断符号 =,>,<,>=,<=,!=
-- 例子3：查询user表中，和明锐的所有信息。
SELECT * FROM t_user WHERE nickname='hemingrui';
-- 例子4：查询user表中小于158的数
SELECT * FROM t_user WHERE username<15877801461;
-- 2.2 where 配合 like 语句 模糊查询
-- 例子五：查询user表中，nickname是he开头的信息；
SELECT*FROM t_user WHERE nickname LIKE 'he%';
-- 注意:like语句在使用时，切记不要出现前面带%的模糊查询，不走索引。
-- 问题例子：
SELECT*FROM t_user WHERE nickname LIKE '%he%';
-- 2.3 where 配合逻辑连接字符 AND OR AND:交集，OR:并集
-- 例子六：查询中国人口大于500w的城市。
SELECT * FROM world.city
WHERE countrycode='CHN' AND population > 500w;
-- 例子7：查询中国或者美国的城市信息
SELECT*FROM world.city
WHERE countrycode='CHN' OR countrycode='USA';
-- 查询中国或美国的城市信息，并且人数超过500w
SELECT * FROM world.city
WHERE countrycode='CHN' OR countrycode='USA'
AND population > 500w;
-- 例子 8：
SELECT * FROM world.city
WHERE countrycode IN ('CHN','USA') AND population > 500w;
-- 2.4 where 配合between and
-- 例子9:查询城市人口数在100w到200w之间的。
SELECT * FROM world.city
WHERE population=100w AND population=200w;
SELECT * FROM world.city
WHERE population BETWEEN 100w AND 200w;
-- 3.select + from + where + group by
-- group by 配合聚合函数使用(max(),min(),count(),avg(),sum(),group_concat) 使用
聚合函数：
-- max () : 最大值
-- min () : 最小值
-- count () : 统计个数
-- avg () : 平均数
-- sum () : 求和
-- group_concat : 列转行
说明:碰到group BY 必然会有聚合函数
例子:统计city中，每个国家的城市个数。
SELECT countrycode,countid
FROM world.city
GROUP BY countrycode;
统计中国，每个省的城市个数。
SELECT district，count(id)
FROM world.cityCHN
WHERE countrycode='CHN'
GROUP BY district;
例子12 ： 统计每个国家的总人口
countrycode SUM(population)
CHN 1111
USA 222
JPN 333
SELECT countrycode,SUM(population)
FROM world.city
GROUP BY countrycode;
例子13 ： 统计中国，每个省的总人口
SELECT district,SUM(popuation)
FROM world.city
WHERE countrycode='CHN'
GROUP BY district;
例子14 ： 统计中国，每个省的总人口，城市个数，城市名列表
SELECT ,district,SUM(population),COUNT(id),NAME
FROM world.city
WHERE country = 'CHN'
GROUP BY district
SQL_MODE的区别
sql_mode = only_full_group
-- 说明：select list 中的列，要么是group By 的条件,要么在聚合函数中
原理：mysql不支持，结果集是1行对多行的显示方式。
MySQL>
selecct district,SUM(population),COUNT(id),GROUP_CONCAT(NAME)
FROM world.city
WHERE countrycode='CHN'
GROUP BY district;
-- 单表查询： having , order by,limit 使用
1.1 HAVING 语句
作用：与where子句类型相同，having属于后过滤
-- 例子15:统计出中国，每个省的总人口，只显示总人口数大于500w的信息
SELECT district,SUM(population)
FROM world.city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>500w
-- order by 应用
-- 例子16：统计中国，每个省的总人口数，只显示总人口数大于500w信息，并且按照总人口从大到小排序输出
-- order by 默认从小到大 order by desc 从大到小
SELECT district,SUM(population)
FROM world.city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population) > 500w
ORDER BY SUM(population) DESC;
1.3 limit应用
作用:分页显示结果集
-- 例子17：统计中国，每个省的总人口，只显示总人口数大于500wx信息，并且按照总人口从大到小排序输出，只显示前5名
SELECT district,SUM(population)
FROM world.city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population) > 500w
ORDER BY SUM(population) DESC
LIMIT 5;
SELECT district,SUM(population)
FROM world.city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population) > 500w
ORDER BY SUM(population)
LIMIT 5,5;
SELECT district,SUM(population)
FROM world.city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population) > 500w
ORDER BY SUM(population) DESC
LIMIT 5 OFFSET 5;
USE school
CREATE TABLE student(
sno INT NOT NULL AUTO_INCREMENT PRIMARY KEY 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=utf8mb4;
CREATE TABLE course(
cno INT NOT NULL PRIMARY KEY COMMENT '编程序号',
cname VARCHAR(20) NOT NULL COMMENT '课程名字',
tno INT NOT NULL COMMENT '教师编号'
)ENGINE=INNODB CHARSET=utf8mb4;
CREATE TABLE sc(
sno INT NOT NULL COMMENT '学号',
cno INT NOT NULL COMMENT '课程编号',
score INT NOT NULL DEFAULT 0 COMMENT '成绩'
)ENGINE=INNODB CHARSET=utf8mb4;
CREATE TABLE teacher(
tno INT NOT NULL PRIMARY KEY COMMENT '教师编号',
tname VARCHAR(20) NOT NULL COMMENT '教师名字'
)ENGINE=INNODB CHARSET=utf8mb4;
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');
INSERT INTO course(cno,cname,tno)
VALUES
(1001,'linux',101),
(1002,'python',102),
(1003,'mysql',103);
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
-- 2.1作用
为什么要使用多表查询？
我们的查询需求，需要的数据，来自多张表，单张表无法满足。
-- 2.2.1 笛卡尔乘积
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 |
+-----+--------+------+--------+-----+
-- 2.2.2 内连接(应用最广泛)
A JOIN B
ON A.XX;
SELECT * FROM teacher
JOIN course
ON teacher.tno=course.tno;
-- 2.2.3 外连接
LEFT JOIN ：左表所有数据，右表满足条件的数据
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;
RIGHT JOIN : 左表所有数据，右边满足条件的数据
简单理解：多表连接实际上是将多张表中，有关联的部分数据，合并成一张表
2.4 多表连接查询例子
-- 例子1：查询一下wuhan这个城市：国家名，城市名，城市人口数，国土面积
1.找关联表 ：
city ：
城市名：city.name
城市人口数：city.population
country:
国家名：country.name
国土面积：country.surfacearea
2.找关联条件 ：
mysql>DESC city
----> city.countrycode
mysql>DESC country
----> country.code
3.罗列其他查询条件
SELECT country.name,city.name,city.population,country.surfacearea
FROM city
JOIN country
ON country.code=city.countrycode
WHERE city.name='wuhan';
USE school
SELECT *
FROM course;
SELECT *
FROM student;
SELECT *
FROM sc;
SELECT *
FROM teacher;
例子2：统计zhang3，学习了几门课
1.找关联表：
student :student.sname
sc :COUNT(sc.cno)
FROM student ON sc
2.找关联关系
ON student.sno=sc.sno
3.罗列其他条件
SELECT student.sno,student.sname,COUNT(sc.cno)
FROM student
JOIN sc
ON student.sno=sc.sno
WHERE sname='zhang3'
GROUP BY student.sno,student.sname;
例子3：查询zhang3，学习的课程名称有哪些
1.找关联表
student ：student.sname student.sno
course : course.cname course.cno
sc : sc.cno sc.sno
SELECT student.sno,student.sname,GROUP_CONCAT(course.cname)
FROM sc
JOIN student
ON sc.sno=student.sno
JOIN course
ON sc.cno=course.cno
WHERE student.sname='zhang3'
GROUP BY student.sno,student.sname;
例子4：查询oldguo老师教的学生名
1.找关联表
teacher：teacher.tname,teacher.tno
student：student.sname,student.sno,
sc : sc.sno,sc.cno
course : course.cno,course.tno
SELECT teacher.tno,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 student.sno=sc.sno
WHERE teacher.tname='oldguo'
GROUP BY teacher.tno,teacher.tname;
例子5：查询oldguo所教课程的平均分数
teacher:teacher.tname,teacher.tno
course :course.cname course.cno,course.tno
sc : sc.cno sc.score
SELECT teacher.tno,teacher.tname,GROUP_CONCAT(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;
例子6：每位老师所教课程的平均分，并按平均分排序
表：
teacher：teacher.tname,teacher.tno;
course : course.cno,course.cname,course.tno;
sc : sc.sno,sc.cno,sc.score
SELECT teacher.tno,GROUP_CONCAT(teacher.tname),GROUP_CONCAT(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
ORDER BY AVG(sc.score) DESC;
例子7：查询oldguo所教的不及格的学生名字
teacher:tno,tname
course :tno,cno,cname
sc :sno,cno,score
student:sno,sname
SELECT teacher.tno,teacher.tname,GROUP_CONCAT(course.cname),GROUP_CONCAT(student.sname),GROUP_CONCAT(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 teacher.tname='oldguo' AND sc.score>60
GROUP BY teacher.tno,teacher.tname;
例子8：查询所有老师所教学生不及格的信息
teacher:tno,tname
course :tno,cno
sc :sno,cno,score
student:sno,sname,sage,ssex
SELECT teacher.tname,student.sno,student.sname,student.sage,student.ssex,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;
例子9：查询平均成绩大于60分的同学的学号和平均成绩
student:sno,sname
sc :sno,cno,score
SELECT GROUP_CONCAT(student.sno),AVG(sc.score)
FROM student
JOIN sc
ON student.sno=sc.sno
WHERE sc.score>60;
例子10：查询所有同学的学号，姓名，选课数，总成绩
例子11：查询各科成绩最高，最低得分：以如下形式显示：课程ID，最高分，最低分。。
例子12：统计各位老师，所教课程的及格率。
例子12：统计各位老师，所教课程的及格率。
思考：
1：表
teacher:tname,tno
course :cno,cname,tno
sc :cno,score
2：关联关系
SELECT CONCAT(teacher.tno,"_",teacher.tname),CONCAT(CONCAT(COUNT(CASE WHEN sc.score>60 THEN 1 END))/ COUNT(sc.sno)*100,"%") AS 及格率
FROM teacher
JOIN course
ON teacher.tno=course.tno
JOIN sc
ON course.cno=sc.cno
GROUP BY teacher.tno,teacher.tname;
例子13：查询每门课程被选修的学生数。
例子14：查询出只选修了一门课程的全部学生的学号和姓名
例子15：查询选修课程门数超过1门的学生信息
例子16：统计每门课程：优秀（85分以上），良好（70-85），一般（60-70），不及格（小于60）的学生列表 ***
course ：course.name,cno
sc ： sno,cno,score
student：sno,sname
CASE WHEN 判断 THEN 结果 END
SELECT course.cname AS 课程名,
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.cname;
例子17：查询平均成绩大于85的所有学生的学号，姓名和平均成绩。
2.5 外连接
作用：强制驱动表
驱动表是是什么？
在多表连接当中，承担for循环中外层循环的角色。
此时，MYSQL会拿着驱动表的每个满足条件的关联列的值，去依次找到for循环内循环的关联值一一进行判断和匹配。
建议：1.小表作为驱动表，降低next loop次数。
2.left JOIN 可以强制左表为驱动表
例子：
SELECT student.sno,student.sname,COUNT(sc.cno)
FROM student
JOIN sc
ON student.sno=sc.sno
WHERE sname='zhang3'
GROUP BY student.sno,student.sname;
改为：强制驱动表
SELECT student.sno,student.sname,COUNT(sc.cno)
FROM student
LEFT JOIN sc
ON student.sno=sc.sno
WHERE sname='zhang3'
GROUP BY student.sno,student.sname;
强制驱动表时有where条件时尽量将驱动表设置为where条件后面的字段表。
3.select 补充
3.1 别名应用
3.1.1 列别名，表别名
SELECT student.sno,student.sname,COUNT(sc.cno)
FROM student
LEFT JOIN sc
ON student.sno=sc.sno
WHERE sname='zhang3'
GROUP BY student.sno,student.sname;
别名:
SELECT student.sno AS 学号,student.sname AS 名字,COUNT(sc.cno) AS 成绩
FROM student AS a
LEFT JOIN sc AS b
ON a.sno=b.sno
WHERE sname='zhang3'
GROUP BY student.sno,student.sname;
别名：也就是给表字段起个自己看着方便熟悉的名字。
作用：全局调用定义的别名。
3.3 union 和 union all
--查询学生zhang3和li4的所有信息
SELECT *
FROM student
WHERE sname='zhang3'
OR sname='li4';
SELECT *
FROM student
WHERE sname
IN ('zhang3','li4');
SELECT *
FROM student
WHERE sname='zhang3'
UNION
SELECT *
FROM student
WHERE sname='li4';
SELECT *
FROM student
WHERE sname='zhang3'
UNION ALL
SELECT *
FROM student
WHERE sname='li4';
面试：union 和 union all区别
union：聚合两个结果集，会自动进行结果集去重复。
union all： 聚合两个结果集，不会去重复。
5.information_schema 元数据获取
5.0 介绍
每次数据库启动，会自动在内存中生成I_S,生成查询MYSQL部分元数据信息视图。
视图（封装）
select语句的执行方法。不保存数据本身。
I_S 中的视图，保存的就是查询元数据的方法。
例如：
原：SELECT student.sno,student.sname,COUNT(sc.cno)
FROM student
LEFT JOIN sc
ON student.sno=sc.sno
WHERE sname='zhang3'
GROUP BY student.sno,student.sname;
封装：CREATE VIEW V_select
AS SELECT student.sno,student.sname,COUNT(sc.cno)
FROM student
LEFT JOIN sc
ON student.sno=sc.sno
WHERE sname='zhang3'
GROUP BY student.sno,student.sname;
SELECT* FROM V_select;
5.1 I_S.tables
作用：保存了所有表的数据字典信息
mysql> desc tables;
TABLE_SCHEMA 表所在的库
TABLE_NAME 表名
ENGINE 表的引擎
TABLE_ROWS 表的数据行 （不是实时）
AVG_ROW_LENGTH 平均行长度
DATA_LENGTH 表使用的存储空间大小
INDEX_LENGTH 表索引使用的空间大小
DATA_FREE 表中是否有碎片
5.2 I_S.tables 企业应用案例
-- 例子1:数据库资产统计-统计每个库，所有表的个数，表名
SELECT table_schema,COUNT(table_name),GROUP_CONCAT(table_name)
FROM information_schema.tables
GROUP BY table_schema;
mysql> DESC TABLES;
TABLE_SCHEMA 表所在的库
TABLE_NAME 表名
ENGINE 表的引擎
TABLE_ROWS 表的数据行 （不是实时）
AVG_ROW_LENGTH 平均行长度
DATA_LENGTH 表使用的存储空间大小
INDEX_LENGTH 表索引使用的空间大小
DATA_FREE 表中是否有碎片
-- 例子2：统计每个库占用空间总大小。
思考：
库空间=平均行长度*行+索引空间的大小
SELECT table_schema,SUM(AVG_ROW_LENGTH*table_rows+index_length)
FROM information_schema.tables
GROUP BY table_schema;
库空间=表使用的存储空间大小（非实时）
SELECT table_schema,SUM(DATA_LENGTH) AS '空间大小'
FROM information_schema.tables
GROUP BY table_schema
order by SUM(DATA_LENGTH/1024) desc;
例子3：查询业务数据库(系统库除外)，所有非InnoDB表。
SELECT table_schema,table_name
FROM information_schema.tables
WHERE ENGINE != 'InnoDB' AND table_schema NOT IN('sys','performance_schema','information_schema','mysql');
例子4：查询业务数据库，所有非INnodb的表，并将其转化为INNODB的表
ALTER TABLE school.schema ENGINE=MYISAM;
SELECT CONCAT("alter table ",table_schema,".",table_name," engine=innodb;")
FROM information_schema.tables
WHERE ENGINE !='innodb' AND table_schema NOT IN('sys','mysql','performance_schema','information_schema')
INTO OUTFILE '/tmp/alter.sql';
查看各个库使用空间的大小
select TABLE_SCHEMA AS 库名,SUM((DATA_LENGTH+INDEX_LENGTH)/1024/1024) AS 数据库空间使用大小（M）
from information_schema.tables
WHERE TABLE_SCHEMA is not null
group by TABLE_SCHEMA
order by SUM((DATA_LENGTH+INDEX_LENGTH)/1024/1024) desc;
show variables like '%slow_query_log%'; 慢日志路径查询
扫一扫
7674
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
