Justin-刘清政的博客

db/MySQL系列/03-MySQL系列之-SQL基础应用

2019-12-24

一 SQL介绍

1
2
3
4
5
6
7
8
9
10
11
12
13
14
结构化查询语言
有一些标准:89 92 99 03
5.7 以后符合SQL92严格模式
通过sql_mode参数来控制

# 查看sql_mode,sql_mode用来规范sql语句的书写方式
select @@sql_mode; # 查看sql_mode
ONLY_FULL_GROUP_BY, # 5.7新加入
STRICT_TRANS_TABLES,
NO_ZERO_IN_DATE,
NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,
NO_ENGINE_SUBSTITUTION

二 常用SQL分类

1
2
3
4
5
6
7
8
9
# help:客户端功能帮助
# help contents:服务端功能帮助
# help Data Definition
# help DROP DATABASE

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

2.1 客户端命令

image-20200813150640462
1
2
3
4
5
6
7
8
9
10
11
12
13
# 客户端输入help

# \c 结束上一条命令
# \G 格式化输出
# exit,\q,control+d,quit退出会话
# notee和tee 开启日志记录,
tee/tmp/mysql.log # 开启,以后执行的sql都会被记录到日志,包括结果
select * from t2;
# source 导入sql脚本,类似于<,恢复备份
source /root/my.sql
# system 在mysql中执行linux命令
system ls
system cd /tmp && ls

三 数据类型、表属性、字符集

3.1 数据类型

3.1.1 作用

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

3.1.2 种类

数值类型

image-20200813115647750
1
2
3
4
5
6
7
tinyint  : -128~127     1个字节,8个比特位,正负27次方减1
int :-2^31~2^31-1 4个字节,32个比特位,正负231次方减1
bigint : -2^63~2^63-1 8个字节,64个比特位,正负263次方减1
说明:手机号是无法存储到int的。一般是使用char类型来存储收集号

# 创建表指定数据类型
create tabel t1(id int,name varchar(64),age tinyint)

字符类型

image-20200813115855936

image

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
char(11) :
定长 的字符串类型,在存储字符串时,最大字符长度11个,立即分配11个字符长度的存储空间,如果存不满,空格填充。
varchar(11):
变长 的字符串类型看,最大字符长度11个。在存储字符串时,自动判断字符长度,按需分配存储空间。
varchar类型,除了会存储字符串之外,还会额外使用1-2个字节存储字符长度
enum('bj','tj','sh'):
枚举类型,存字符串类型,比较适合于将来此列的值是固定范围内的特点,可以使用enum,可以很大程度的优化我们的索引结构。


# 括号中数字指的是字符长度,所以存英文和中文,其实占得空间是不一样的
# 英文和数字,一个字符是1个字节,中文3个字节,emoji占4个字节
# 因为编码方式规定了utf8,所以不需要自行考虑此问题

# 测试:
create database db1 charset utf8mb4;
create table t2(id int,name varchar(10),sex char(10));
insert into t2 values(1,'aaaaaaaaaa','一二三四五六七八九十');
insert into t2 values(1,'aaaaaaaaaa','一二三四五六七八九十一');
insert into t2 values(1,'aaaaaaaaaa','1234567891');
ERROR 1406 (22001): Data too long for column 'sex' at row 1
desc t2;# 查看表结构
select length(sex) from t2; # 查看字符所占用的空间

# mysql 5.6 超长会存进去,自动截断
# mysql 5.6 超长会报错

varchar最多能存储65535个字节的数据,一般我们最多定义varchar(255),超过255会被转成text类型

时间类型

image-20200813115913262
1
2
3
4
5
DATETIME (8个字节长度)
范围为从 1000-01-01 00:00:00.0000009999-12-31 23:59:59.999999
TIMESTAMP (4个字节长度)
1970-01-01 00:00:00.0000002038-01-19 03:14:07.999999
timestamp会受到时区的影响

二进制类型

image-20200813115928772

json格式

5.6以后支持

3.2 表属性

3.2.1 列属性

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
约束(一般建表时添加):
# primary key :主键约束,
唯一且非空,每个表只能有一个主键,作为聚簇索引
设置为主键的列,此列的值必须非空且唯一,主键在一个表中只能有一个,但是可以有多个列一起构成。

#not null :非空约束
列值不能为空,也是表设计的规范,尽可能将所有的列设置为非空。可以设置默认值为0

# unique key:唯一键
列值不能重复

# unsigned:无符号
针对数字列,非负数。

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

3.2.2 表的属性

1
2
3
4
5
存储引擎:
InnoDB(默认的)
字符集和排序规则:
utf8
utf8mb4

3.3 字符集和校对规则

3.3.1 字符集(charset)

1
2
3
4
5
6
7
8
9
10
# show charset;
有非常多,现在只关注如下两种
utf8
utf8mb4 # 5.6以后出现,8.0以后默认使用utf8mb4,8.0以前默认是latin1(拉丁)
# 差别:
utf8:最大存储长度,单个字符最多3个字节
utf8mb4支持的编码比utf8更多,比如emoji字符,emoji字符,一个字符占4个字节

# 查看库的字符编码
show create databaes mysql

3.3.2 校对规则(排序规则,collation)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
show collation;
# 影响排序规则
a
b
A
aB
Ba
select ascii('a'); # 查看a的ascii码
如果大小写敏感排序一个样
如果大小写敏感排序另一个样

# 简单来说就是:大小写是否敏感,默认不敏感
+--------------------------+----------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 |
| utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 |

四 DDL应用

4.1 数据定义语言

数据定义语言,对库和表进行操作,操作mysql的对象,即库和表,对元数据进行操作

4.2 库定义

4.2.1 创建

4.2.1 创建数据库

1
2
3
4
5
6
7
8
9
10
11
12
create database school;
create schema sch;
show charset; # 查看支持的字符集
show collation; # 查看支持的校对规则,collation
CREATE DATABASE test CHARSET utf8;
create database xyz charset utf8mb4 collate utf8mb4_bin;

建库规范:
1.库名不能有大写字母
2.建库要加字符集
3.库名不能有数字开头,不能使用保留字段(database,table)
4.库名要和业务相关

建库标准语句

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

4.2.2 删除(生产中禁止使用)

1
mysql> drop database lqz;

4.2.3 修改

1
2
3
4
5
6
SHOW CREATE DATABASE school;
ALTER DATABASE school CHARSET utf8;
# 注意:
只能改库属性,不能改库名(只能改字符集)
修改字符集,修改后的字符集一定是原字符集的严格超集
从小往大改,从utf8改到utf8mb4可以,从utf8mb4改成utf8可能会乱码

4.2.4 查询库相关信息(DQL)

1
2
show databases; # 查看库
show create database lqz; # 查看具体信息

4.3 表定义

4.3.1 创建

1
2
3
4
5
create table stu(
列1 属性(数据类型、约束、其他属性) ,
列2 属性,
列3 属性
)

4.3.2 建表

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
1. 表名小写(多平台兼容性问题,window不区分大小写,linux严格大小写),
2. 不能是数字开头,名字不要太长15个字符以内
3. 注意字符集和存储引擎
4. 表名和业务有关,不能使用关键字
5. 选择合适的数据类型:合适,简短,足够
6. 必须有主键,每个列都要有注释
7. 每个列设置为非空,无法保证非空,用0来填充。

4.3.2 删除(生产中禁用命令)

1
drop table t1;

4.3.3 修改

  1. 在stu表中添加qq列
1
2
DESC stu;
ALTER TABLE stu ADD qq VARCHAR(20) NOT NULL UNIQUE COMMENT 'qq号';
  1. 在sname后加微信列
1
ALTER TABLE stu ADD wechat VARCHAR(64) NOT NULL UNIQUE  COMMENT '微信号' AFTER sname ;
  1. 在id列前加一个新列num
1
2
ALTER TABLE stu ADD num INT NOT NULL COMMENT '数字' FIRST;
DESC stu;
  1. 把刚才添加的列都删掉(危险)
1
2
3
ALTER TABLE stu DROP num;
ALTER TABLE stu DROP qq;
ALTER TABLE stu DROP wechat;
  1. 修改sname数据类型的属性
1
ALTER TABLE stu MODIFY sname VARCHAR(128)  NOT NULL ;
  1. 将sgender 改为 sg 数据类型改为 CHAR 类型(change需要把原来不需要修改的也带上)
1
2
ALTER TABLE stu CHANGE sgender sg CHAR(1) NOT NULL DEFAULT 'n' ;
DESC stu;

注意:

在mysql中,DDL语句在对表进行操作时,是要锁“元数据表”的,此时,所有修改类的命令无法运行

(元数据:记录表的各种信息,对数据锁定,才能修改,否则都去改,就会出问题)

大表加一列,业务繁忙的表,要谨慎

8.0以前版本需要借助,可以借助pt-osc(pt-online-shaema-change),gh-ost工具进行DDL操作

4.3.4 表属性查询(DQL)

1
2
3
4
5
use school
show tables; # 查看该库下所有表
desc stu; # 查看表结构
show create table stu;# 查看详细建表语句
CREATE TABLE ceshi LIKE stu;

五 DCL应用 ****

1
2
grant 
revoke

六 DML应用

6.1 作用

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

6.2 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;


# HWM:记录自增数字,高水位线

6.3 update

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

6.4 delete(危险!!)

1
DELETE FROM stu  WHERE id=3;

全表删除:

1
2
3
4
5
6
7
8
9
10
11
12
DELETE FROM stu;
truncate table stu;
drop table stu;
区别:
delete: DML操作, 是逻辑性质删除,逐行进行删除,数据库很多,速度慢,并没有在磁盘上真正删除,磁盘空间不会立即释放,自增的值,也不会释放(HWM高水位线不会降低),
truncate: DDL操作,物理层次删除,对与表段中的数据页进行清空,速度快,立即释放控件,HWM高水位线会降低
drop:将表结构(元数据)和物理层次删除
# 常规方法:
以上三者,都能通过备份+日志,恢复数据
# 灵活办法:
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 )

7.1 单独使用

1
2
3
4
5
6
7
8
9
-- select @@xxx 查看系统参数,配置文件中配的都可以查看
SELECT @@port;
SELECT @@basedir;
SELECT @@datadir;
SELECT @@socket;
SELECT @@server_id;

show variables; # mysql中的500多个参数
show variables like '%or%';

– select 函数();

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT NOW(); # 当前时间
SELECT DATABASE(); # 当前数据库
SELECT USER(); # 当前登录用户
SELECT CONCAT("hello world"); # 字符串拼接
SELECT CONCAT(USER,"@",HOST) FROM mysql.user;
SELECT GROUP_CONCAT(USER,"@",HOST) FROM mysql.user;
select version(); # 当前数据库版本号
# 相关函数查询,官方文档或者使用help
https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html?tdsourcetag=s_pcqq_aiomsg

help contents
help functions
help String Functions

# 标准sql,其他关系型数据库,必须要有from
select NOW() from dual;

# 计算
select 10*100

默认执行顺序

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
select 列,列
from1,表2。。
where 过滤条件1 过滤条件2。。
group by 条件1 条件2。。
having 过滤条件1 过滤条件2。。
order by 条件列1 条件列2
limit 限制

# d
select
1 from1,表2。。。
2 where 过滤条件1 过滤条件2。。
3 group by 条件1 条件2。。
3.5 select_list name,age 列名列表
4 having 过滤条件1 过滤条件2。。
5 order by 条件列1 条件列2
6 limit 限制


# 完整select 执行顺序
(6) SELECT 列
(8) DISTINCT <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(7) HAVING <having_condition>
(9) ORDER BY <order_by_condition>
(10) LIMIT <limit_number>

导入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql < world.sql
库:world
表:
city
country
countrylanguage


city:城市表
DESC city;
ID : 城市ID
NAME : 城市名
CountryCode: 国家代码,比如中国CHN 美国USA
District : 区域
Population : 人口

7.2 单表子句-from

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

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

1
SELECT * FROM city ;

– 查询city表中,id和姓名

1
SELECT id ,name  FROM city;

7.3 单表子句-where

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

7.3.1 where配合等值查询

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

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

– 查询北京市的信息

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

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

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

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

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

1
SELECT * FROM city WHERE population<100;

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

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

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

– 中国或美国城市信息

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

7.3.4 where配合模糊查询

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

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

7.3.5 where配合in语句

– 中国或美国城市信息

1
2
3
4
5
6
7
SELECT * FROM city WHERE countrycode ='CHN'  or countrycode ='USA';
SELECT * FROM city WHERE countrycode IN ('CHN' ,'USA');
# 错误
SELECT * FROM city WHERE countrycode ='CHN' or countrycode ='USA'AND population>5000000;

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

7.3.6 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;

7.4 group by + 常用聚合函数

7.4.1 作用

1
根据 by后面的条件进行分组,方便统计,by后面跟一个列或多个列
image-20200813182847212

7.4.2 常用聚合函数

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

7.4.3 例子:

例子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 countrycode,COUNT(id)  FROM city GROUP BY countrycode;

例子4:统计中国,每个省总人口,城市个数,城市名列表(重点)

1
2
3
4
5
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
# 原因:违反了sql_mode=only_full_group_by,SELECT list必须是group by的字段和聚合函数,name不属于,因为name有很多,在mysql5.6之前可以查询,但是只取name的第一个,5.7以后直接报错,这是合理的
select district,sum(Population),count(id),group_concat(name) from world.city where countrycode='CHN' group by district;

7.5 having

1
where|group|having

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

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

7.6 order by + limit

7.6.1 作用

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

7.6.2 应用案例

  1. 查看中国所有的城市,并按人口数进行排序(从大到小)
1
SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC;
  1. 统计中国各个省的总人口数量,按照总人口从大到小排序
1
2
3
4
5
SELECT district AS 省 ,SUM(Population) AS 总人口
FROM city
WHERE countrycode='chn'
GROUP BY district
ORDER BY 总人口 DESC ;
  1. 统计中国,每个省的总人口,找出总人口大于500w的,并按总人口从大到小排序,只显示前三名
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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
LIMIT 5 OFFSET 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;

7.7 distinct:去重复

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

7.8 联合查询- union all

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 求并集
-- 中国或美国城市信息
SELECT * FROM city
WHERE countrycode IN ('CHN' ,'USA');

SELECT * FROM city
WHERE countrycode ='CHN' or countrycode ='USA';

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

说明:一般情况下,我们会将 IN 或者 OR 语句 改写成 UNION ALL,来提高性能
都是聚合两个结果集
UNION 去重复
UNION ALL 不去重复

7.9 join 多表连接查询

7.9.0 案例准备

按需求创建一下表结构:

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;

7.9.1 语法

1
2
3
4
5
6
7
8
9
10
笛卡尔积:select * from teacher,course;
内连接(inner join):
select * from teacher join course on teacher.tno=course.tno;
select city.name,country.name,city.population from city join country on city.countrycode = country.code and city.population <100;
外连接:
left join:左表所有数据和右表满足条件的数据
select * from teacher join course on teacher.tno=course.tno;
select city.name,country.name,city.population from city left join country on city.countrycode = country.code and city.population <100;
right join:右表所有数据和左表满足条件的数据
select city.name,country.name,city.population from city right join country on city.countrycode = country.code and city.population <100;
image-20200813203607215

查询张三的家庭住址

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

7.9.2 例子:

  1. 查询一下世界上人口数量小于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
  1. 查询城市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';

7.9.3 别名

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';

7.9.4 多表SQL练习题

  1. 统计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'
  1. 查询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'
  1. 查询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';
  1. 查询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'

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

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 ;
  1. 查询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;

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

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;

注意:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
join 左边的为左表,右边的为右边

驱动表:驱动表会拿着每一行,逐一的跟另一个表匹配,匹配成功就合并
#双层for循环
for 每一行 in 驱动表:
for 每一行 in 另一个表:
匹配成功合并
# 假设驱动表是1000行,另一个表是10行,至少要1000次循环,如果拿另一个表去找,最少10次
多表关联,驱动表要选小表,降低next loop次数

对于内连接来讲,我们没法控制驱动表是谁,完全由优化器决定,如果要人为干预,要把内连接写成外连接的方式
如果使用left join 可以强制左表为驱动表

总结:
1 小表作为驱动表,降低next loop次数
2 left join 可以强制左表为驱动表

7.9.5 综合练习

1
2
3
4
5
6
7
8
9
1. 查询平均成绩大于60分的同学的学号和平均成绩;
2. 查询所有同学的学号、姓名、选课数、总成绩;
3. 查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
4. 统计各位老师,所教课程的及格率
5. 查询每门课程被选修的学生数
6. 查询出只选修了一门课程的全部学生的学号和姓名
7. 查询选修课程门数超过1门的学生信息
8. 统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表
9. 查询平均成绩大于85的所有学生的学号、姓名和平均成绩

7.9.6 补充

别名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 1 表别名
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;

# 表别名可以在全局使用,只在当次查询中有用

# 2 列别名
select student.sno as '学号',student.sname as '姓名' from studnet;
# 列别名在哪些子句中可以使用
在having之前都不能使用(参照上面select执行顺序)

八 元数据信息

8.1 逻辑表有关组成部分

image-20200814010107629
1
2
3
4
5
6
# 每次数据库启动,会自动在内存中生成nformation_schema,生成查询mysql部分元数据的视图
# 视图:select 语句的执行方法,不保存数据本身
## 创建视图
create view v_city as select name from city where id <10;
## 使用视图
select * from v_city;

8.2 information_schema.tables视图

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
use information_schema; # 进入information_schema
show tables; # 可以看到所有视图
# 我们研究tables这个视图
desc tables;
# DESC information_schema.TABLES
TABLE_SCHEMA ---->表所在库名
TABLE_NAME ---->表名
ENGINE ---->引擎
TABLE_ROWS ---->表的行数(不是特别实时)
AVG_ROW_LENGTH ---->表中行的平均行长度(字节)
INDEX_LENGTH ---->索引的占用空间大小(字节)
DATA_FREE ---->表中是否有碎片

# 所有的
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
TABLE_TYPE
ENGINE
VERSION
ROW_FORMAT
TABLE_ROWS
AVG_ROW_LENGTH
DATA_LENGTH
MAX_DATA_LENGTH
INDEX_LENGTH
DATA_FREE
AUTO_INCREMENT
CREATE_TIME
UPDATE_TIME
CHECK_TIME
TABLE_COLLATION
CHECKSUM
CREATE_OPTIONS
TABLE_COMMENT

# 使用场景
资产统计,自动化运维平台,多少个库,多少个表,占用空间怎么样,统计表增长
  1. 查询整个数据库中所有库和所对应的表信息
1
2
3
4

SELECT table_schema,GROUP_CONCAT(table_name)
FROM information_schema.tables
GROUP BY table_schema;
  1. 统计所有库下的表个数
1
2
3
SELECT table_schema,COUNT(table_name)
FROM information_schema.TABLES
GROUP BY table_schema
  1. 查询所有innodb引擎的表及所在的库
1
2
SELECT table_schema,table_name,ENGINE FROM information_schema.`TABLES`
WHERE ENGINE='innodb';
  1. 统计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. 统计所有数据库的总的磁盘空间占用
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. 统计每个库,所有表的个数,表名
1
select table_schema,count(table_name),GROUP_CONCAT(table_name) from TABLES group by table_schema;
  1. 统计每个库占用空间大小
1
2
3
select table_schema,sum(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024 from information_schema.tables group by table_schema;

select table_schema,sum(DATA_LENGTH)/1024 from information_schema.tables group by table_schema;
  1. 生成整个数据库下的所有表的单独备份语句
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" )
  1. 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';

九 show 命令

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
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 full processlist; #查看数据库连接情况详细信息
show privileges #查看权限信息
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 evnets in #查看二进制日志事件
show slave status \G #查看从库状态
SHOW RELAYLOG EVENTS in #查看从库relaylog事件信息
desc (show colums from city) #查看表的列定义信息
http://dev.mysql.com/doc/refman/5.7/en/show.html
help show 查看其它的
使用支付宝打赏
使用微信打赏

点击上方按钮,请我喝杯咖啡!

扫描二维码,分享此文章