SQL介绍

  • 结构化查询语言，关系型数据库中通用的一类语言
  • 5.7 以后符合SQL92严格模式
  • 通过sql_mode参数来控制

常用SQL分类

MySQL客户端自带的功能

1
mysql> help

Server端分类命令

1
mysql> help contents
  • DDL：数据定义语言
  • DCL：数据控制语言
  • DML：数据操作语言
  • DQL：数据的查询语言

SQL的各种名词

sql_mode SQL模式

作用：规范SQL语句的书写方式，符合逻辑

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

数据类型

作用

保证数据的准确性和标准性。

种类

数值类型

img
说明：手机号是无法存储到int的。一般是使用char类型来存储收集号

字符类型

img

char(11) ：
定长的字符串类型,在存储字符串时，最大字符长度11个，立即分配11个字符长度的存储空间，如果存不满，空格填充。
varchar(11):
变长的字符串类型看，最大字符长度11个。在存储字符串时，会先判断字符长度，按需分配存储空间。除了会存储字符串之外，还会额外使用1-2字节存储字符长度

以上两种数据类型选择需考虑周全，会影响到索引应用。括号中，设置的是字符的个数，无关字符类型。但是，不同种类的字符，占用的存储空间是不一样的，对于英文和数字，每个字符占1个字节长度，对于中文，占用空间大小要考虑字符集

enum(‘bj’,’js’,’sz’) 枚举类型

​ 1 2 3 “下标索引”

枚举类型，比较适合于将来此列的值是固定范围内的特点，可以使用enum,可以很大程度的优化我们的索引结构。

时间类型

img

DATETIME(8个字节)
范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。
TIMESTAMP(4个字节)
1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。
timestamp会受到时区的影响

二进制类型

img

json类型

列属性

列值不能为空，也是表设计的规范，尽可能将所有的列设置为非空。可以设置默认值为0

约束(一般建表时添加):

  • primary key ：主键约束
    设置为主键的列，此列的值必须非空且唯一，主键在一个表中只能有一个，但是可以有多个列一起构成。
  • not null ：非空约束
    列值不能为空，也是表设计的规范，尽可能将所有的列设置为非空。可以设置默认值为0
  • unique key ：唯一键
    列值不能重复
  • unsigned ：无符号
    针对数字列，非负数。

其他属性:

  • key :索引
    可以在某列上建立索引，来优化查询,一般是根据需要后添加
  • default :默认值
    列中，没有录入值时，会自动使用default的值填充
  • auto_increment:自增长
    针对数字列，顺序的自动填充数据（默认是从1开始，将来可以设定起始点和偏移量）
  • **comment ** : 注释

字符集和校对规则

字符集(charset)

查看当前mysql版本支持的字符集

1
mysql> show charset;
  • utf8
  • utf8mb4(支持的编码比utf8更多，如emoji)，8.0+默认

校对规则(collation)

查看当前mysql版本支持的校对规则

1
mysql> show collation;

作用：影响到排序的操作，简单来说就是对大小写是否敏感

SQL应用

client

  • help|?：查看帮助

  • \c：结束上条命令运行，类似ctrl+c

  • \G：格式化输出

  • exit|\q：退出MySQL会话(ctrl+d)

  • source：导入SQL脚本，类似<

  • system：执行shell命令

server

DDL应用

数据定义语言

注意：在MySQL中，DDL语句在对表进行操作时，是要锁”元数据表”(类似于Linux的Inode信息)的。此时，所有修改类命令无法正常进行

所以：

  • 在对于大表，业务繁忙的表，进行DDL操作时，要谨慎
  • 尽量避开业务繁忙期

库定义

创建数据库

1
2
3
4
5
6
create database school;
create schema sch;
show charset;
show collation;
create DATABASE test CHARSET utf8;
create database xyz charset utf8mb4 collate utf8mb4_bin;

建库规范：
1.库名不能有大写字母

2.建库要加字符集

3.库名不能有数字开头

4.库名要和业务相关

建库标准语句

1
2
mysql> create database db charset utf8mb4;
mysql> show create database xuexiao;

删除(生产中禁止使用)

1
mysql> drop database kinmfer;

修改

1
2
SHOW CREATE DATABASE school;
ALTER DATABASE school CHARSET utf8mb4;

注意：修改字符集，修改后的字符集一定是原字符集的严格超集

查询库

1
2
show databases；
show create database kinmfer；

表定义

创建

1
2
3
4
5
create table 库.表(
	列1  属性（数据类型、约束、其他属性） ，
	列2  属性，
	列3  属性
)
1
2
3
4
5
6
7
8
9
USE school;
CREATE TABLE stu(
id      INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname   VARCHAR(255) NOT NULL COMMENT '姓名',
sage    TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄',
sgender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别' ,
sfz     CHAR(18) NOT NULL UNIQUE  COMMENT '身份证',
intime  TIMESTAMP NOT NULL DEFAULT NOW() COMMENT '入学时间'
) ENGINE=INNODB CHARSET=utf8 COMMENT '学生表';

建表规范：

  1. 表名小写(多平台兼容性问题)
  2. 不能是数字开头
  3. 注意字符集和存储引擎
  4. 表名和业务有关
  5. 选择合适的数据类型
  6. 每个列都要有注释
  7. 列名不要太长
  8. 每个列设置为非空，无法保证非空，用0来填充。
  9. 不能使用关键字
  10. 必须要有主键

删除(生产中禁用命令)

1
drop table t1;

查询

1
2
3
show tables；
desc stu;
show create table stu；

修改

在stu表中添加qq列

1
2
DESC stu;
ALTER TABLE stu ADD qq VARCHAR(20) NOT NULL UNIQUE COMMENT 'qq号';

在sname后加微信列

1
ALTER TABLE stu ADD wechat VARCHAR(64) NOT NULL UNIQUE  COMMENT '微信号' AFTER sname ;

在id列前加一个新列num

1
2
ALTER TABLE stu ADD num INT NOT NULL COMMENT '数字' FIRST;
DESC stu;

把刚才添加的列都删掉(危险)

1
2
3
ALTER TABLE stu DROP num;
ALTER TABLE stu DROP qq;
ALTER TABLE stu DROP wechat;

修改sname数据类型的属性

1
ALTER TABLE stu MODIFY sname VARCHAR(128)  NOT NULL ;

将sgender 改为 sg 数据类型改为 CHAR 类型

1
2
ALTER TABLE stu CHANGE sgender sg CHAR(1) NOT NULL DEFAULT 'n' ;
DESC stu;

修改表名

1
alter table ts01 rename to ts01_new;

…

DCL应用

  • grant
  • revoke

DML应用

作用

对表中的数据行进行增、删、改

insert

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
--- 最标准的insert语句
INSERT INTO stu(id,sname,sage,sg,sfz,intime) 
VALUES
(1,'zs',18,'m','123456',NOW());
SELECT * FROM stu;

--- 省事的写法
INSERT INTO stu 
VALUES
(2,'ls',18,'m','1234567',NOW());

--- 针对性的录入数据
INSERT INTO stu(sname,sfz)
VALUES ('w5','34445788');

--- 同时录入多行数据
INSERT INTO stu(sname,sfz)
VALUES 
('w55','3444578d8'),
('m6','1212313'),
('aa','123213123123');
SELECT * FROM stu;

update

1
2
3
4
DESC stu;
SELECT * FROM stu;
UPDATE stu SET sname='zhao4' WHERE id=2;
注意：update语句必须要加where。

delete（危险！！）

1
DELETE FROM stu  WHERE id=3;

全表删除:

1
2
3
4
5
6
7
DELETE FROM stu;
drop table stu;
truncate table stu;
区别:
delete: DML操作, 是逻辑性质删除,逐行进行删除,速度慢.HWM不会降低
drop: 将表结构(元数据)和数据行物理层次删除
truncate: DDL操作,对与表段中的数据页进行清空,速度快，磁盘空间立即释放，HWM高水位线会降低

常规办法：

都可以通过备份+日志恢复数据。

灵活办法：

delete可以通过反转日志(binlog)，也可以通过延时从库进行恢复

伪删除：用update来替代delete，最终保证业务中查不到（select）即可

1
2
3
4
5
6
7
1.添加状态列
ALTER TABLE stu ADD state TINYINT NOT NULL DEFAULT 1 ;
SELECT * FROM stu;
2. UPDATE 替代 DELETE
UPDATE stu SET state=0 WHERE id=6;
3. 业务语句查询
SELECT * FROM stu WHERE state=1;

DQL应用(select )

获取表中的数据行

单独使用

1
2
3
4
5
6
-- select @@xxx 查看系统参数
SELECT @@port;
SELECT @@basedir;
SELECT @@datadir;
SELECT @@socket;
SELECT @@server_id;

– select 函数();

1
2
3
4
5
6
7
SELECT NOW();
SELECT DATABASE();
SELECT version();
SELECT USER();
SELECT CONCAT("hello world");
SELECT CONCAT(USER,"@",HOST) FROM mysql.user;
SELECT GROUP_CONCAT(USER,"@",HOST) FROM mysql.user;

https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html?tdsourcetag=s_pcqq_aiomsg

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限制

单表子句-from

1
2
SELECT 列1,列2 FROM 表
SELECT  *  FROM 表

例子:
– 查询stu中所有的数据(不要对大表进行操作)

1
SELECT * FROM stu ;

– 查询stu表中,学生姓名和入学时间

1
SELECT sname,intime FROM stu;

单表子句-where

1
SELECT col1,col2 FROM TABLE WHERE colN 条件;

where配合等值查询

例子:
– 查询中国(CHN)所有城市信息

1
SELECT * FROM city WHERE countrycode='CHN';

– 查询北京市的信息

1
SELECT * FROM city WHERE NAME='peking';

– 查询甘肃省所有城市信息

1
SELECT * FROM city WHERE district='gansu';

where配合比较操作符(> < >= <= <>)

例子:
– 查询世界上少于100人的城市

1
SELECT * FROM city WHERE population<100;

where配合逻辑运算符(and or )

例子:
– 中国人口数量大于500w

1
SELECT * FROM city WHERE countrycode='CHN' AND population>5000000;

– 中国或美国城市信息

1
SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA';

where配合模糊查询

例子:
– 查询省的名字前面带guang开头的

1
2
SELECT * FROM city WHERE district LIKE 'guang%';    
注意:%不能放在前面,因为不走索引.

where配合in语句并且人口数量大于500w

– 中国或美国城市信息

1
SELECT * FROM city WHERE countrycode IN('CHN','USA') AND population>5000000;

where配合between and

例子:
– 查询世界上人口数量大于100w小于200w的城市信息

1
2
SELECT * FROM city  WHERE population >1000000 AND population <2000000;
SELECT * FROM city  WHERE population BETWEEN 1000000 AND 2000000;

group by + 常用聚合函数

作用

根据 by后面的条件进行分组，方便统计，by后面跟一个列或多个列

常用聚合函数

1
2
3
4
5
6
**max()**      ：最大值
**min()**      ：最小值
**avg()**      ：平均值
**sum()**      ：总和
**count()**    ：个数
group_concat() : 列转行

例子1：统计世界上每个国家的总人口数.

1
2
USE world
SELECT countrycode ,SUM(population)    FROM  city  GROUP BY countrycode;

例子2： 统计中国各个省的总人口数量

1
SELECT district,SUM(Population) FROM city  WHERE countrycode='chn' GROUP BY district;

例子3：统计中国，每个省的总人口，城市个数，城市名列表

1
select district,SUM(Population),count(id),name FROM city WHERE countrycode='chn' GROUP BY district;

会报错！！！

说明：select list中的列，要么是group by的条件，要么就在聚合函数中出现

原理：MySQL不支持结果集是一对多行的显示方式

解决办法

1
select district,SUM(Population),count(id),group_concat(name) FROM city WHERE countrycode='chn' GROUP BY district;

having

作用：与where子句类型，having属于后过滤

1
where|group|having

例子4：统计中国每个省的总人口数，只打印总人口数小于100

1
2
3
4
5
SELECT district,SUM(Population)
FROM city
WHERE countrycode='chn'
GROUP BY district
HAVING SUM(Population) < 1000000 ;

order by + limit

作用

实现先排序，by后添加条件列

应用案例

查看中国所有的城市，并按人口数进行排序(从大到小)

1
SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC;

统计中国各个省的总人口数量，按照总人口从大到小排序

1
2
3
4
5
SELECT district AS 省 ,SUM(Population) AS 总人口
FROM city
WHERE countrycode='chn'
GROUP BY district
ORDER BY 总人口 DESC ;

统计中国,每个省的总人口,找出总人口大于500w的,并按总人口从大到小排序,只显示前三名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT  district, SUM(population)  FROM  city 
WHERE countrycode='CHN'
GROUP BY district 
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 3 ;

LIMIT N ,M --->跳过N,显示一共M行
LIMIT 5,5

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

distinct：去重复

1
2
SELECT countrycode FROM city ;
SELECT DISTINCT(countrycode) FROM city;

联合查询- union all

1
2
3
4
5
6
7
8
9
10
11
12
-- 中国或美国城市信息

SELECT * FROM city 
WHERE countrycode IN ('CHN' ,'USA');

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

说明:一般情况下,我们会将 IN 或者 OR 语句 改写成 UNION ALL,来提高性能
UNION     去重复
UNION ALL 不去重复

join 多表连接查询

案例准备

按需求创建一下表结构:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
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;

简单理解：多表连接实际上是将多张表中有关联的数据合并成一张表，在新表中再去做where group having order by limit

语法

img

笛卡尔乘积

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
mysql> select * from teacher,course;
+-----+--------+------+--------+-----+
| tno | tname  | cno  | cname  | tno |
+-----+--------+------+--------+-----+
| 103 | oldguo | 1001 | linux  | 101 |
| 102 | hesw   | 1001 | linux  | 101 |
| 101 | oldboy | 1001 | linux  | 101 |
| 103 | oldguo | 1002 | python | 102 |
| 102 | hesw   | 1002 | python | 102 |
| 101 | oldboy | 1002 | python | 102 |
| 103 | oldguo | 1003 | mysql  | 103 |
| 102 | hesw   | 1003 | mysql  | 103 |
| 101 | oldboy | 1003 | mysql  | 103 |
+-----+--------+------+--------+-----+
9 rows in set (0.04 sec)

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

内连接(最广泛)

A join B on A.xx=B.yy

1
2
3
4
5
6
7
8
9
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.04 sec)

外连接

left join:左表所有数据，右表满足条件的数据

right join:右表所有数据，左表满足条件的数据

驱动表是什么？

在多表连接当中，承当for循环中外层循环的角色。
此时，MySQL会拿着驱动表的每个满足条件的关联列的值，去依次找到for循环内循环中的关联值一一进行判断和匹配。

建议:将结果集小的表设置为驱动表更加合适。可以降低next loop的次数。
对于内连接来讲，我们是没法控制驱动表是谁，完全由优化器决定。
如果，需要人为干预，需要将内连接写成外连接的方式。left join可以强制左表为驱动表

查询张三的家庭住址

1
2
3
4
SELECT A.name,B.address FROM
A JOIN  B
ON A.id=B.id
WHERE A.name='zhangsan'

例子：

查询一下世界上人口数量小于100人的城市名和国家名

1
2
3
4
5
SELECT b.name ,a.name ,a.population
FROM  city  AS a
JOIN  country AS b
ON    b.code=a.countrycode
WHERE  a.Population<100

查询城市shenyang，城市人口，所在国家名（name）及国土面积（SurfaceArea）

1
2
3
4
SELECT a.name,a.population,b.name ,b.SurfaceArea
FROM city  AS a JOIN country AS b
ON a.countrycode=b.code
WHERE a.name='shenyang';

别名

1
2
3
4
5
6
7
8
9
列别名,表别名
SELECT 
a.Name AS an ,
b.name AS bn ,
b.SurfaceArea AS bs,
a.Population AS bp
FROM city AS a  JOIN country AS b
ON a.CountryCode=b.Code
WHERE a.name ='shenyang';

多表SQL练习题

统计zhang3,学习了几门课

1
2
3
4
5
6
SELECT st.sname , COUNT(sc.cno)
FROM student AS st
JOIN
sc
ON st.sno=sc.sno
WHERE st.sname='zhang3'

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

1
2
3
4
5
6
7
SELECT st.sname , GROUP_CONCAT(co.cname)
FROM student AS st
JOIN sc
ON st.sno=sc.sno
JOIN course AS co
ON sc.cno=co.cno
WHERE st.sname='zhang3'

查询oldguo老师教的学生名.

1
2
3
4
5
6
7
8
9
SELECT te.tname ,GROUP_CONCAT(st.sname)
FROM student AS st
JOIN sc
ON st.sno=sc.sno
JOIN course AS co
ON sc.cno=co.cno
JOIN teacher AS te
ON co.tno=te.tno
WHERE te.tname='oldguo';

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

1
2
3
4
5
6
7
SELECT te.tname,AVG(sc.score)
FROM teacher AS te
JOIN course AS co
ON te.tno=co.tno
JOIN sc
ON co.cno=sc.cno
WHERE te.tname='oldguo'

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

1
2
3
4
5
6
7
8
SELECT te.tname,AVG(sc.score)
FROM teacher AS te
JOIN course AS co
ON te.tno=co.tno
JOIN sc
ON co.cno=sc.cno
GROUP BY te.tname
ORDER BY AVG(sc.score) DESC ;

查询oldguo所教的不及格的学生姓名

1
2
3
4
5
6
7
8
9
SELECT te.tname,st.sname,sc.score
FROM teacher AS te
JOIN course  AS co
ON te.tno=co.tno
JOIN sc
ON co.cno=sc.cno
JOIN student AS st
ON sc.sno=st.sno
WHERE te.tname='oldguo' AND sc.score<60;

查询所有老师所教学生不及格的信息

1
2
3
4
5
6
7
8
9
SELECT te.tname,st.sname,sc.score
FROM teacher AS te
JOIN course  AS co
ON te.tno=co.tno
JOIN sc
ON co.cno=sc.cno
JOIN student AS st
ON sc.sno=st.sno
WHERE sc.score<60;

case when 判断 then 结果 end

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select concat(teacher .tname, "_",teacher.tno) ,concat(count(case when score.score>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;
+-----------------------------------------+-----------+
| concat(teacher .tname, "_",teacher.tno) | 及格率    |
+-----------------------------------------+-----------+
| oldboy_101                              | 100.0000% |
| hesw_102                                | 66.6667%  |
| oldguo_103                              | 75.0000%  |
+-----------------------------------------+-----------+
3 rows in set (0.09 sec)

information_schema.tables视图

1
2
3
4
5
6
7
8
9
DESC information_schema.TABLES
TABLE_SCHEMA    ---->库名
TABLE_NAME      ---->表名
ENGINE          ---->引擎
TABLE_ROWS      ---->表的行数（不是特别实时）
AVG_ROW_LENGTH  ---->表中行的平均行（字节）
INDEX_LENGTH    ---->索引的占用空间大小（字节）
DATA_LENGTH     ---->表使用的存储空间大小（不是特别实时）
DATA_FREE       ---->表中是否有碎片

查询整个数据库中所有库和所对应的表信息

1
2
3
SELECT table_schema,GROUP_CONCAT(table_name)
FROM  information_schema.tables
GROUP BY table_schema;

统计所有库下的表个数

1
2
3
SELECT table_schema,COUNT(table_name)
FROM information_schema.TABLES
GROUP BY table_schema

查询所有innodb引擎的表及所在的库

1
2
SELECT table_schema,table_name,ENGINE FROM information_schema.`TABLES`
WHERE ENGINE='innodb';

统计world数据库下每张表的磁盘空间占用

1
2
SELECT table_name,CONCAT((TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB") AS size_KB
FROM information_schema.tables WHERE TABLE_SCHEMA='world';

统计所有数据库的总的磁盘空间占用

1
2
3
4
5
6
SELECT
TABLE_SCHEMA,
CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB") AS Total_KB
FROM information_schema.tables
GROUP BY table_schema;
mysql -uroot -p123 -e "SELECT TABLE_SCHEMA,CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024,' KB') AS Total_KB FROM information_schema.tables GROUP BY table_schema;"

生成整个数据库下的所有表的单独备份语句

1
2
3
4
5
6
7
8
模板语句：
mysqldump -uroot -p123 world city >/tmp/world_city.sql
SELECT CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql" )
FROM information_schema.tables
WHERE table_schema NOT IN('information_schema','performance_schema','sys')
INTO OUTFILE '/tmp/bak.sh' ;

CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql" )

107张表，都需要执行以下2条语句

1
2
3
4
5
6
ALTER TABLE world.city DISCARD TABLESPACE;
ALTER TABLE world.city IMPORT TABLESPACE;
SELECT CONCAT("alter table ",table_schema,".",table_name," discard tablespace")
FROM information_schema.tables
WHERE table_schema='world'
INTO OUTFILE '/tmp/dis.sql';

查询业务数据库（系统库除外），所有非InnoDB表。将非InnoDB表转化为InnoDB

1
2
3
4
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';

但是要在my.cnf中加入

1
2
[mysqld]
secure-file-priv=/tmp

show 命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
show  databases;                          #查看所有数据库
show tables;                              #查看当前库的所有表
SHOW TABLES FROM                          #查看某个指定库下的表
show create database world                #查看建库语句
show create table world.city              #查看建表语句
show  grants for  root@'localhost'        #查看用户的权限信息
show charset                              #查看字符集
show collation                            #查看校对规则
show processlist;                         #查看数据库连接情况
show index from                           #表的索引情况
show status                               #数据库状态查看
SHOW STATUS LIKE '%lock%';         		  #模糊查询数据库某些状态
SHOW VARIABLES                            #查看所有配置信息
SHOW variables LIKE '%lock%';             #查看部分配置信息
show engines                              #查看支持的所有的存储引擎
show engine innodb status\G               #查看InnoDB引擎相关的状态信息
show binary logs                          #列举所有的二进制日志
show master status                        #查看数据库的日志位置信息
show binlog events in                     #查看二进制日志事件
show slave status \G                      #查看从库状态
SHOW RELAYLOG EVENTS                      #查看从库relaylog事件信息
desc  (show colums from city)             #查看表的列定义信息
http://dev.mysql.com/doc/refman/5.7/en/show.html