SQL
SQL 标准
- SQL92
- SQL99
Mysql5.7 版本之后启用了 SQL_Mode 严格模式
SQL 语句类型
| SQL 语言类型 | 英文缩写 | 作用 |
|---|---|---|
| DDL：数据定义语言 | data definition language | 建库、建表、改表 |
| DCL：数据控制语言 | data control language | GRANT 和 REVOKE 两个指令构成 |
| DML：数据操作语言 | data manipulation language | INSERT、UPDATE、DELETE |
| DQL：数据查询语言 | data query language | 查询数据 |
数据类型
制数据的规范性，让数据有具体含义，在列上进行控制
| 数据类型 | 种类 | 功能 | 说明 |
|---|---|---|---|
| char、varchar | 字符类型 | 255 65535 定长（固定存储空间） 变长（按需） | varchar 存数据时，首先进行字符串长度判断，按需分配存储空间，会单独占用一个字节来记录此次的字符长度，超过 255 之后，需要两个字节长度记录字符长度。 |
| enum | 枚举类型 | enum('bj','sh','sz','cq','hb',......) | 数据行较多时，会影响到索引的应用数字类禁止使用 enum 类型 |
| tinyint、int | 数字 | ||
| timestamp、datetime | 时间 |
表属性
存储引擎 ：engine = InnoDB
字符集 ：charset = utf8mb4
utf8 中文 三个字节长度
utf8mb4 中文 四个字节长度 才是真正的 utf8 支持 emoji 字符
排序规则（校对规则） collation
针对英文字符串大小写问题
列的属性与约束
1# 主键： primary key (PK)
2唯一、非空
3适合做自增的轻快：数字列，整数列，无关列
4聚集索引列？
5是一种约束，也是一种索引类型，在一张表中只能有一个主键。
6
7# 非空： Not NULL
8我们建议，对于普通列来讲，尽量设置not null
9默认值 default ： 数字列的默认值使用0 ,字符串类型，设置为一个nil null
10
11# 唯一：unique
12不能重复
13
14# 自增 auto_increment
15针对数字列，自动生成顺序值
16
17# 无符号 unsigned
18针对数字列，既为非负数，用此类型可以增加数据长度!
19
20# 注释 comment
DDL
库
1# 建库
2create database oldguo charset utf8mb4;
3
4# 查看库具体信息
5show create database oldguo;
6
7# 改库
8alter database oldguo1 charset utf8mb4;
9
10# 删库
11drop database oldguo1;
表
1# 建表
2create table oldguo (
3ID int not null primary key AUTO_INCREMENT comment '学号',
4name varchar(255) not null comment '姓名',
5age tinyint unsigned not null default 0 comment '年龄',
6gender enum('m','f','n') NOT null default 'n' comment '性别'
7)charset=utf8mb4 engine=innodb;
8
9# 修表
10在上表中添加一个手机号列15801332370.
11alter table oldguo add telnum char(11) not null unique comment '手机号';
online-DDL 工具
1# 不锁表，对在线业务影响较小
2pt-osc
练习
1# 查看列的信息
2DESC oldguo;
3
4# 添加一个状态列
5ALTER TABLE oldguo ADD state TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态列';
6
7# 删除state列(不代表生产操作,会删除对应列上的数据)
8ALTER TABLE oldguo DROP state;
9
10# 在name后添加 qq 列 varchar(255)
11ALTER TABLE oldguo ADD qq VARCHAR(255) NOT NULL UNIQUE COMMENT 'qq' AFTER name;
12
13# 练习 在name 之前添加wechat列
14ALTER TABLE oldguo ADD wechat VARCHAR(255) NOT NULL UNIQUE COMMENT '微信' AFTER ID;
15
16# 在首列上添加 学号列：sid(linux58_00001)
17ALTER TABLE oldguo ADD sid VARCHAR(255) NOT NULL UNIQUE COMMENT '学生号' FIRST;
18
19# 修改name数据类型的属性
20ALTER TABLE oldguo MODIFY NAME VARCHAR(128) NOT NULL ;
21
22# 将gender 改为 gg , 数据类型改为 CHAR 类型
23ALTER TABLE oldguo CHANGE gender gg CHAR(1) NOT NULL DEFAULT 'n' ;
DML
1# 建表
2CREATE TABLE `oldguo` (
3 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '??',
4 `name` varchar(255) NOT NULL COMMENT '??',
5 `qq` varchar(255) NOT NULL COMMENT 'qq',
6 `age` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '??',
7 PRIMARY KEY (`id`),
8 UNIQUE KEY `qq` (`qq`)
9) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4
10
11########## insert
12# 简单方法插入数据
13INSERT INTO oldguo VALUES(1,'oldguo','22654481',18);
14
15# 最规范方法插入数据(重点记忆)
16INSERT INTO oldguo(NAME,qq,age) VALUES ('oldboy','74110',49);
17
18# 批量插入数据
19INSERT INTO oldguo(NAME,qq,age) VALUES ('oldwang','55522',29),('oldli','22214',22);
20
21########## UPDATE
22# UPDATE
23UPDATE oldguo SET qq='123456' WHERE id=5 ;
24
25#
26########## DELETE
27DELETE FROM oldguo WHERE id=5;
28
29# 生产需求：将一个大表全部数据清空(可以使用 DELETE 或 TRUNCATE)
30DELETE FROM oldguo; TRUNCATE TABLE oldguo;
31DELETE 和 TRUNCATE 区别:
321. DELETE 逻辑逐行删除，不会降低自增长的起始值。
33效率很低，碎片较多，会影响到性能
342. TRUNCATE ，属于物理删除，将表段中的区进行清空，不会产生碎片。性能较高。
35
36# 生产需求：使用update替代delete，进行伪删除
371. 添加状态列state (0代表存在，1代表删除)
38ALTER TABLE oldguo ADD state TINYINT NOT NULL DEFAULT 0 ;
392. 使用update模拟delete
40DELETE FROM oldguo WHERE id=6;
41替换为
42UPDATE oldguo SET state=1 WHERE id=6
DQL
单独使用 select
1select @@xxxx;获取参数信息。
2mysql> select @@port;
3mysql> show variables like '%innodb%'; # 查询mysql中所有参数信息
4
5select 函数();
6mysql> select database(); # 查看当前use的数据库
7mysql> select now();
8mysql> select version();
SQL92 标准的使用语法
1# select语法执行顺序（单表）
2
3select开始 ---->
4from子句 --->
5where子句--->
6group by子句--->
7select后执行条件--->
8having子句 ---->
9order by ---->
10limit
from
1# 例子：查询city表中的所有数据
2USE world;
3SELECT * FROM city; --->适合表数据行较少，生产中使用较少。
4SELECT * FROM world.city;
5
6# 例子： 查询name和population的所有值
7SELECT NAME , population FROM city;
8SELECT NAME , population FROM world.city;
where
1# WHERE 配合 等值查询(=)
2--- 查询中国的城市信息
3SELECT *
4FROM world.city
5WHERE countrycode='CHN';
6--- 查询美国的城市信息
7SELECT *
8FROM world.city
9WHERE countrycode='USA';
10
11# WHERE 配合 不等值(> < >= <= <>)
12--- 查询一下世界上人口小于100人的城市
13SELECT *
14FROM world.city
15WHERE population<100;
16
17# WHERE 配合 模糊(LIKE)
18--- 查询国家代号是C开头的城市
19SELECT *
20FROM world.city
21WHERE countrycode
22LIKE 'C%';
23
24--- 注意：like 语句在MySQL中，不要出现%在前面的情况。因为效率很低，不走索引。
25--- 错误的里
26SELECT *
27FROM world.city
28WHERE countrycode
29LIKE '%C%';
30
31# WHERE 配合 逻辑连接符(AND OR)
32--- 查询城市人口在1w到2w之间的城市
33SELECT *
34FROM city
35WHERE population >= 10000
36AND Population <= 20000;
37
38SELECT *
39FROM city
40WHERE population
41BETWEEN 10000 AND 20000;
42
43--- 查询一下中国或美国的城市信息
44SELECT *
45FROM city
46WHERE countrycode='CHN' OR countrycode='USA';
47
48SELECT *
49FROM city
50WHERE countrycode IN ('CHN','USA');
51
52建议改写为，以下语句：
53SELECT *
54FROM city
55WHERE countrycode='CHN'
56UNION ALL
57SELECT *
58FROM city
59WHERE countrycode='USA';
group by
GROUP BY 配合聚合函数应用
常用聚合函数：
AVG()
COUNT()
SUM()
MAX()
MIN()
GROUP_CONCAT()
1--- 统计每个国家的总人口
2SELECT countrycode,SUM(population) FROM city GROUP BY countrycode ;
3--- 统计每个国家的城市个数
41.拿什么站队
5GROUP BY countrycode
62. 拿什么统计
7城市id，name
83. 统计的是什么？
9COUNT(id)
10SELECT countrycode,COUNT(id) FROM city GROUP BY countrycode;
11
12--- 统计并显示 每个国家的省名字列表
13SELECT countrycode,GROUP_CONCAT(district) FROM city GROUP BY countrycode;
14
15--- 统计中国每个省的城市名列表
16SELECT District,GROUP_CONCAT(NAME)
17FROM city
18WHERE countrycode='CHN'
19GROUP BY district;
20
21--- 统计一下中国,每个省的总人口数
22SELECT district ,SUM(population) FROM city
23WHERE countrycode='CHN'
24GROUP BY district
25
26--- 统计一下中国,每个省的平均人口
27SELECT district ,AVG(population) FROM city
28WHERE countrycode='CHN'
29GROUP BY district
having
1--- 统计中国，每个省的总人口大于1000w的省及人口数
2SELECT district ,SUM(population) FROM city
3WHERE countrycode='CHN'
4GROUP BY district
5HAVING SUM(population)>10000000
6
7说明: having后的条件是不走索引的，可以进行一些优化手段处理。
order by
1--- 统计中国，每个省的总人口数，并以人口数降序输出省内人数大于1000W的省
2SELECT district ,SUM(population)
3FROM city
4WHERE countrycode='CHN'
5GROUP BY district
6HAVING SUM(population)>10000000
7ORDER BY SUM(population) DESC ;
8
9--- 例子：查询中国所有的城市，并以人口数降序输出
10SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC;
limit
配合 order by 一起使用，无法单独存在
1SELECT *
2FROM city
3WHERE countrycode='CHN'
4ORDER BY population DESC
5LIMIT 5;
6
7SELECT *
8FROM city
9WHERE countrycode='CHN'
10ORDER BY population DESC
11LIMIT 10;
12
13SELECT *
14FROM city
15WHERE countrycode='CHN'
16ORDER BY population DESC
17LIMIT 5,3;
18
19SELECT *
20FROM city
21WHERE countrycode='CHN'
22ORDER BY population DESC
23LIMIT 3 OFFSET 5;
24
25LIMIT M,N 跳过M行，显示N行
26LIMIT X OFFSET Y 跳过Y行，显示X行＃＃
创建表用于多表查询
1use school
2
3# student 学生表
4sno： 学号
5sname：学生姓名
6sage： 学生年龄
7ssex： 学生性别
8
9# teacher 教师表
10tno： 教师编号
11tname：教师名字
12
13#course 课程表
14cno： 课程编号
15cname：课程名字
16tno： 教师编号
17
18# score 成绩表
19sno： 学号
20cno： 课程编号
21score：成绩
22
23
24
25CREATE TABLE student (
26sno int(11) PRIMARY key,
27sname varchar(20),
28sage tinyint(3) unsigned,
29ssex enum('f','m')
30)
31
32CREATE TABLE teacher(
33tno int(11) PRIMARY key,
34tname varchar(20)
35)
36
37CREATE TABLE course(
38cno int(11) PRIMARY KEY,
39cname varchar(20),
40tno int(11)
41)
42
43CREATE TABLE sc(
44sno int(11),
45cno int(11),
46score int(11)
47)
48
49
50
51INSERT INTO student(sno,sname,sage,ssex)
52VALUES (1,'zhang3',18,'m');
53
54INSERT INTO student(sno,sname,sage,ssex)
55VALUES
56(2,'zhang4',18,'m'),
57(3,'li4',18,'m'),
58(4,'wang5',19,'f');
59
60INSERT INTO student
61VALUES
62(5,'zh4',18,'m'),
63(6,'zhao4',18,'m'),
64(7,'ma6',19,'f');
65
66INSERT INTO student(sname,sage,ssex)
67VALUES
68('oldboy',20,'m'),
69('oldgirl',20,'f'),
70('oldp',25,'m');
71
72
73INSERT INTO teacher(tno,tname) VALUES
74(101,'oldboy'),
75(102,'hesw'),
76(103,'oldguo');
77
78
79INSERT INTO course(cno,cname,tno)
80VALUES
81(1001,'linux',101),
82(1002,'python',102),
83(1003,'mysql',103);
84
85
86INSERT INTO sc(sno,cno,score)
87VALUES
88(1,1001,80),
89(1,1002,59),
90(2,1002,90),
91(2,1003,100),
92(3,1001,99),
93(3,1003,40),
94(4,1001,79),
95(4,1002,61),
96(4,1003,99),
97(5,1003,40),
98(6,1001,89),
99(6,1003,77),
100(7,1001,67),
101(7,1003,82),
102(8,1001,70),
103(9,1003,80),
104(10,1003,96);
多表连接查询
当需要查询的数据来源于多张表，就需要进行多表连接查询了。
传统的连接：基于 where 条件
- 找表之间的关系列
- 排列查询条件
1--- 人口数量小于100人的城市,所在国家的国土面积(城市名，国家名，国土面积)
2select city.name,country.name ,country.surfacearea
3from city,country
4where city.countrycode = country.code
5and city.population<100
内连接
语法
1A B
2
3A.x B.y
41. 找表之间的关系列
52. 将两表放在join左右
63. 将关联条件了放在on后面
74. 将所有的查询条件进行罗列
8
9select A.m,B.n
10from
11A join B
12on A.x=B.y
13where
14group by
15order by
16limit
示例
1--- 1. 查询人口数量小于100人的国家名，城市名，国土面积
2select city.name,country.name,country.surfacearea
3from city
4join country
5on city.countrycode = country.code
6where city.population < 100;
7
8
9--- 2. 查询oldguo老师和他教课程名称
10select teacher.tname,course.cname
11from teacher
12join course
13on teacher.tno=course.tno
14where teacher.tname="oldguo";
15
16--- 3. 统计一下每门课程的总成绩(如果用course.cname进行分组，可能会存在课程重名的情况，有两个linux课程)
17select course.cname,sum(sc.score)
18from course
19join sc
20on course.cno=sc.cno
21group by course.cno;
22
23--- 4. 查询oldguo老师教的学生姓名列表
24SELECT teacher.tname,GROUP_CONCAT(student.sname)
25FROM teacher
26JOIN course
27ON teacher.tno = course.tno
28JOIN sc
29ON course.cno = sc.cno
30JOIN student
31ON sc.sno = student.sno
32WHERE teacher.tname='oldguo'
33GROUP BY teacher.tname;
34
35--- 5. 查询所有老师教的学生姓名列表
36select teacher.tname,group_concat(student.sname)
37from teacher
38join course
39on teacher.tno = course.tno
40join sc
41on course.cno = sc.cno
42join student
43on sc.sno = student.sno
44group by teacher.tno;
45
46--- 6. 查询oldboy老师教的不及格学生的姓名
47select teacher.tname,group_concat(student.sname)
48FROM teacher
49JOIN course
50ON teacher.tno = course.tno
51JOIN sc
52ON course.cno = sc.cno
53JOIN student
54ON sc.sno = student.sno
55where teacher.tname = "oldboy"and sc.score<60;
56group by teacher.tno;
57
58
59--- 7. 统计zhang3,学习了几门课
60SELECT student.sname ,COUNT(sc.cno)
61FROM student
62JOIN sc
63ON student.sno = sc.sno
64WHERE student.sname='zhang3';
65
66--- 8. 查询zhang3,学习的课程名称有哪些?
67select student.sname,group_concat(course.cname)
68from student
69join sc
70on student.sno = sc.sno
71join course
72on sc.cno = course.cno
73where student.sname = "zhang3";
74
75--- 9. 查询oldguo老师教的学生名.
76SELECT teacher.tname,GROUP_CONCAT(student.sname)
77FROM teacher
78JOIN course
79ON teacher.tno = course.tno
80JOIN sc
81ON course.cno = sc.cno
82JOIN student
83ON sc.sno = student.sno
84WHERE teacher.tname='oldguo'
85GROUP BY teacher.tname;
86
87--- 10.查询oldguo所教课程的平均分数
88select teacher.tname,course.cname,AVG(sc.score)
89from teacher
90join course
91on teacher.tno = course.tno
92join sc
93on course.cno = sc.cno
94where teacher.tname = "oldguo"
95GROUP BY course.cno;
96
97--- 11.每位老师所教课程的平均分,并按平均分排序
98select teacher.tname,avg(sc.score)
99from teacher
100join course
101on teacher.tno = course.tno
102join sc
103on course.cno = sc.cno
104GROUP BY teacher.tno
105ORDER BY avg(sc.score)
106DESC
107
108--- 12.查询oldguo所教的不及格的学生姓名
109SELECT teacher.tname,GROUP_CONCAT(student.sname)
110FROM teacher
111JOIN course
112ON teacher.tno = course.tno
113JOIN sc
114ON course.cno = sc.cno
115JOIN student
116ON sc.sno = student.sno
117WHERE teacher.tname='oldguo' AND sc.score<60
118GROUP BY teacher.tno;
119
120--- 13.查询所有老师所教学生不及格的信息
121select teacher.tname,group_concat(student.sname)
122from teacher
123join course
124on teacher.tno = course.tno
125join sc
126on course.cno = sc.cno
127JOIN student
128ON sc.sno = student.sno
129where sc.score < 60
130group by teacher.tno
外连接
left join：除了与右边关联的数据，左边不关联的数据也保留，也就是左表的所有数据都保留，不关联的数据对应的右边列以 null 进行填充。
left join 的意义在于可以指定左表，而内连接则不可以，左表使用比较小的表，右表用比较大的表，让优化器更容易识别。
换句话说 left join 可以选择更为合适的坐标进行连接查询，优化内连接
1# and是在生成临时表的时候使用的条件，不管on的条件是否起到作用，都会返回左表 (table_name1) 的行。
2SELECT a.name,b.name ,b.surfacearea
3FROM city AS a
4LEFT JOIN country AS b
5on a.countrycode=b.code
6and a.population<100 ;
7
8# where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。
9SELECT a.name,b.name ,b.surfacearea
10FROM city AS a
11LEFT JOIN country AS b
12ON a.countrycode=b.code
13WHERE a.population<100
distinct
对某一列进行去重，将多行相同的数据合并为一行
1mysql> select count(name) from world.city;
2+-------------+
3| count(name) |
4+-------------+
5| 4079 |
6+-------------+
71 row in set (0.00 sec)
8
9mysql> select count(distinct(name)) from world.city;
10+-----------------------+
11| count(distinct(name)) |
12+-----------------------+
13| 3998 |
14+-----------------------+
151 row in set (0.01 sec)
16
union all
三张表合并成一张表，利用子查询和 union all。
提前条件是：三张表的列必须完全相同。
1select sum(单价*数量) from
2(select 牌子,单价,数量 from 啤酒
3union all
4select 牌子,单价,数量 from 饮料
5union all
6select 牌子,单价,数量 from 矿泉水);
别名
表别名
1SELECT te.tname ,GROUP_CONCAT(st.sname)
2FROM teacher AS te
3JOIN course AS co
4ON te.tno = co.tno
5JOIN sc as s
6ON co.cno = s.cno
7JOIN student AS st
8ON s.sno = st.sno
9WHERE te.tname='oldguo' AND s.score<60
10GROUP BY te.tno;
列别名
1select count(distinct(name)) as 个数 from world.city;
show
是对直接对 tables 表进行查询的操作的封装;便于查询元数据信息。
1show databases; 查看所有数据库名
2show tables; 查看当前库下的表名
3show tables from world; 查看world数据库下的表名
4show create database d1 查看建库语句
5show create table t1 查看建表语句
6show grants for root@'localhost' 查看用户权限信息
7show charset 查看所有的字符集
8show collation 查看校对规则
9show full processlist 查看数据库连接情况
10show status 查看数据库的整体状态
11show status like '%lock%' 模糊查看数据库的整体状态
12show variables 查看数据库所有变量情况
13show variables like '%innodb%' 查看数据库所有变量情况
14show engines 查看所有支持存储引擎
15show engine innodb status 查看所有innodb存储引擎状态情况
16show binary logs 查看二进制日志情况
17show binlog events in 查看二进制日志事件
18show relaylog events in 查看relay日志事件
19show slave status 查看从库状态
20show master status 查看数据库binlog位置信息
21show index from 查看表的索引情况