SQL

2021-04-29

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
针对英文字符串大小写问题

列的属性与约束

# 主键： primary key (PK)
唯一、非空
适合做自增的轻快：数字列，整数列，无关列
聚集索引列？ 
是一种约束，也是一种索引类型，在一张表中只能有一个主键。

# 非空： Not NULL 
我们建议，对于普通列来讲，尽量设置not null 
默认值 default ： 数字列的默认值使用0 ,字符串类型，设置为一个nil null

# 唯一：unique 
不能重复

# 自增 auto_increment
针对数字列，自动生成顺序值

# 无符号 unsigned
针对数字列，既为非负数，用此类型可以增加数据长度!  

# 注释 comment

DDL

库

# 建库
create database oldguo charset utf8mb4;

# 查看库具体信息
show create database oldguo;

# 改库 
alter database oldguo1 charset utf8mb4;

# 删库
drop database oldguo1;

表

# 建表
create table oldguo (
ID int not null primary key AUTO_INCREMENT comment '学号',
name varchar(255) not null comment '姓名', 
age tinyint unsigned not null default 0 comment '年龄',
gender enum('m','f','n') NOT null default 'n' comment '性别'
)charset=utf8mb4 engine=innodb;

# 修表
在上表中添加一个手机号列15801332370.
alter table oldguo add telnum char(11) not null unique comment '手机号';

online-DDL 工具

# 不锁表，对在线业务影响较小
pt-osc

练习

# 查看列的信息
DESC  oldguo;

# 添加一个状态列
ALTER TABLE oldguo ADD state TINYINT  UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态列';

# 删除state列(不代表生产操作,会删除对应列上的数据)
ALTER TABLE oldguo DROP state;

# 在name后添加 qq 列 varchar(255)
ALTER TABLE oldguo ADD qq VARCHAR(255) NOT NULL UNIQUE  COMMENT 'qq' AFTER name;

# 练习 在name 之前添加wechat列 
ALTER TABLE oldguo ADD wechat VARCHAR(255) NOT NULL UNIQUE COMMENT '微信' AFTER ID;

# 在首列上添加 学号列：sid(linux58_00001)
ALTER TABLE oldguo ADD sid VARCHAR(255) NOT NULL UNIQUE COMMENT '学生号' FIRST;

# 修改name数据类型的属性
ALTER TABLE oldguo  MODIFY NAME VARCHAR(128)  NOT NULL ;

# 将gender 改为 gg , 数据类型改为 CHAR 类型
ALTER TABLE oldguo  CHANGE gender gg CHAR(1) NOT NULL DEFAULT 'n' ;

DML

# 建表
CREATE TABLE `oldguo` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '??',
  `name` varchar(255) NOT NULL COMMENT '??',
  `qq` varchar(255) NOT NULL COMMENT 'qq',
  `age` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '??',
  PRIMARY KEY (`id`),
  UNIQUE KEY `qq` (`qq`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4

########## insert
# 简单方法插入数据
INSERT INTO oldguo VALUES(1,'oldguo','22654481',18);

# 最规范方法插入数据(重点记忆)
INSERT INTO oldguo(NAME,qq,age) VALUES ('oldboy','74110',49);

# 批量插入数据
INSERT INTO oldguo(NAME,qq,age) VALUES ('oldwang','55522',29),('oldli','22214',22);

########## UPDATE 
# UPDATE 
UPDATE oldguo SET qq='123456' WHERE id=5 ;

# 
########## DELETE
DELETE FROM oldguo WHERE id=5;

# 生产需求：将一个大表全部数据清空(可以使用 DELETE 或 TRUNCATE)
DELETE FROM oldguo;	TRUNCATE TABLE oldguo;
DELETE 和 TRUNCATE 区别:
1. DELETE 逻辑逐行删除，不会降低自增长的起始值。
效率很低，碎片较多，会影响到性能
2. TRUNCATE ，属于物理删除，将表段中的区进行清空，不会产生碎片。性能较高。

# 生产需求：使用update替代delete，进行伪删除
1. 添加状态列state (0代表存在，1代表删除)
ALTER TABLE oldguo ADD state TINYINT NOT NULL DEFAULT 0 ;
2. 使用update模拟delete 
DELETE FROM oldguo WHERE id=6;
替换为
UPDATE oldguo SET state=1 WHERE id=6

DQL

单独使用select

select @@xxxx;获取参数信息。
mysql> select @@port;
mysql> show variables like '%innodb%';  # 查询mysql中所有参数信息

select 函数();
mysql> select database();	# 查看当前use的数据库
mysql> select now();
mysql> select version();

SQL92标准的使用语法

# select语法执行顺序（单表）

select开始 ---->
from子句 --->
where子句--->
group by子句--->
select后执行条件--->
having子句 ---->
order by ---->
limit

from

# 例子：查询city表中的所有数据
USE world;
SELECT * FROM city;   --->适合表数据行较少，生产中使用较少。
SELECT * FROM world.city;

#  例子： 查询name和population的所有值
SELECT NAME , population FROM city;
SELECT NAME , population FROM world.city;

where

# WHERE 配合 等值查询(=)
--- 查询中国的城市信息
SELECT *   
FROM   world.city  
WHERE  countrycode='CHN';
--- 查询美国的城市信息
SELECT *   
FROM   world.city  
WHERE  countrycode='USA';

# WHERE 配合 不等值(> < >= <=  <>)
--- 查询一下世界上人口小于100人的城市
SELECT *   
FROM  world.city  
WHERE population<100;

# WHERE 配合 模糊(LIKE)
--- 查询国家代号是C开头的城市
SELECT *  
FROM   world.city  
WHERE countrycode 
LIKE 'C%';

--- 注意：like 语句在MySQL中，不要出现%在前面的情况。因为效率很低，不走索引。
--- 错误的里
SELECT *  
FROM   world.city  
WHERE countrycode 
LIKE '%C%';

# WHERE 配合 逻辑连接符(AND OR)
--- 查询城市人口在1w到2w之间的城市
SELECT * 
FROM city
WHERE population >= 10000 
AND Population <= 20000;

SELECT * 
FROM city 
WHERE population 
BETWEEN 10000 AND 20000;

--- 查询一下中国或美国的城市信息
SELECT * 
FROM city 
WHERE countrycode='CHN' OR countrycode='USA';

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

建议改写为，以下语句：
SELECT * 
FROM city 
WHERE countrycode='CHN' 
UNION ALL 
SELECT * 
FROM city 
WHERE countrycode='USA';

group by

GROUP BY 配合聚合函数应用
常用聚合函数：
AVG()
COUNT()
SUM()
MAX()
MIN()
GROUP_CONCAT()

--- 统计每个国家的总人口 
SELECT   countrycode,SUM(population) FROM city GROUP BY countrycode ;
--- 统计每个国家的城市个数
1.拿什么站队
GROUP BY  countrycode 
2. 拿什么统计
城市id，name
3. 统计的是什么？
COUNT(id)
SELECT countrycode,COUNT(id) FROM city GROUP BY countrycode;

--- 统计并显示 每个国家的省名字列表
SELECT countrycode,GROUP_CONCAT(district)  FROM city GROUP BY countrycode;

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

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

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

having

--- 统计中国，每个省的总人口大于1000w的省及人口数
SELECT  district ,SUM(population) FROM city 
WHERE countrycode='CHN'
GROUP BY district 
HAVING  SUM(population)>10000000

说明: having后的条件是不走索引的，可以进行一些优化手段处理。

order by

--- 统计中国，每个省的总人口数，并以人口数降序输出省内人数大于1000W的省
SELECT  district ,SUM(population) 
FROM city 
WHERE countrycode='CHN'
GROUP BY district 
HAVING  SUM(population)>10000000
ORDER BY SUM(population) DESC  ;

--- 例子：查询中国所有的城市，并以人口数降序输出
SELECT * FROM city WHERE countrycode='CHN' ORDER BY  population DESC;

limit

配合order by 一起使用，无法单独存在

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

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

SELECT * 
FROM city 
WHERE countrycode='CHN' 
ORDER BY  population DESC
LIMIT 5,3;

SELECT * 
FROM city 
WHERE countrycode='CHN' 
ORDER BY  population DESC
LIMIT 3 OFFSET 5;

LIMIT M,N     跳过M行，显示N行
LIMIT X OFFSET Y 跳过Y行，显示X行＃＃

创建表用于多表查询

use school

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

# teacher  教师表
tno：     教师编号
tname：教师名字

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

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



CREATE TABLE student (
sno int(11) PRIMARY key,
sname varchar(20),
sage tinyint(3) unsigned,
ssex enum('f','m')
)

CREATE TABLE teacher(
tno int(11) PRIMARY key,
tname varchar(20)
)

CREATE TABLE course(
cno int(11) PRIMARY KEY,
cname varchar(20),
tno int(11)
)

CREATE TABLE sc(
sno int(11),
cno int(11),
score int(11)
)



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


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

多表连接查询

　　当需要查询的数据来源于多张表，就需要进行多表连接查询了。

传统的连接：基于where条件

找表之间的关系列
排列查询条件

--- 人口数量小于100人的城市,所在国家的国土面积(城市名，国家名，国土面积)
select city.name,country.name ,country.surfacearea 
from city,country 
where city.countrycode = country.code 
and city.population<100

内连接

语法

A   B

A.x   B.y 
1. 找表之间的关系列 
2. 将两表放在join左右
3. 将关联条件了放在on后面
4. 将所有的查询条件进行罗列

select A.m,B.n
from  
A  join  B
on A.x=B.y
where 
group by 
order by 
limit

示例

--- 1. 查询人口数量小于100人的国家名，城市名，国土面积
select city.name,country.name,country.surfacearea
from city 
join country
on city.countrycode = country.code
where city.population < 100;


--- 2. 查询oldguo老师和他教课程名称
select teacher.tname,course.cname 
from teacher 
join course
on teacher.tno=course.tno
where teacher.tname="oldguo";

--- 3. 统计一下每门课程的总成绩(如果用course.cname进行分组，可能会存在课程重名的情况，有两个linux课程)
select course.cname,sum(sc.score)
from course 
join sc
on course.cno=sc.cno
group by course.cno;

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

--- 5. 查询所有老师教的学生姓名列表
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
group by teacher.tno;

--- 6. 查询oldboy老师教的不及格学生的姓名
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 = "oldboy"and sc.score<60;
group by teacher.tno;


--- 7. 统计zhang3,学习了几门课
SELECT student.sname ,COUNT(sc.cno)
FROM student
JOIN sc
ON student.sno = sc.sno
WHERE student.sname='zhang3';

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

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

--- 10.查询oldguo所教课程的平均分数
select teacher.tname,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 course.cno;

--- 11.每位老师所教课程的平均分,并按平均分排序
select teacher.tname,avg(sc.score)
from teacher
join course
on teacher.tno = course.tno
join sc
on course.cno = sc.cno
GROUP BY teacher.tno
ORDER BY avg(sc.score)
DESC

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

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

外连接

left join：除了与右边关联的数据，左边不关联的数据也保留，也就是左表的所有数据都保留，不关联的数据对应的右边列以null进行填充。

left join 的意义在于可以指定左表，而内连接则不可以，左表使用比较小的表，右表用比较大的表，让优化器更容易识别。

换句话说left join可以选择更为合适的坐标进行连接查询，优化内连接

# and是在生成临时表的时候使用的条件，不管on的条件是否起到作用，都会返回左表 (table_name1) 的行。
SELECT a.name,b.name ,b.surfacearea 
FROM city AS a 
LEFT JOIN country AS b  
on a.countrycode=b.code 
and a.population<100 ;

# where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。
SELECT a.name,b.name ,b.surfacearea 
FROM city AS a 
LEFT JOIN country AS b  
ON a.countrycode=b.code 
WHERE   a.population<100

distinct

对某一列进行去重，将多行相同的数据合并为一行

mysql> select count(name) from world.city;
+-------------+
| count(name) |
+-------------+
|        4079 |
+-------------+
1 row in set (0.00 sec)

mysql> select count(distinct(name)) from world.city;
+-----------------------+
| count(distinct(name)) |
+-----------------------+
|                  3998 |
+-----------------------+
1 row in set (0.01 sec)

union all

三张表合并成一张表，利用子查询和union all。

提前条件是：三张表的列必须完全相同。

select sum(单价*数量) from 
(select 牌子,单价,数量  from 啤酒 
union all 
select 牌子,单价,数量  from 饮料
union all 
select 牌子,单价,数量  from 矿泉水);

别名

表别名

SELECT te.tname  ,GROUP_CONCAT(st.sname) 
FROM teacher AS te
JOIN course AS co 
ON te.tno = co.tno
JOIN sc as s
ON co.cno = s.cno
JOIN student AS st
ON s.sno = st.sno
WHERE te.tname='oldguo' AND s.score<60
GROUP BY te.tno;

列别名

select count(distinct(name)) as 个数  from world.city;

show

是对直接对tables表进行查询的操作的封装;便于查询元数据信息。

show databases;           			查看所有数据库名
show tables;   		      			查看当前库下的表名
show tables from world;   			查看world数据库下的表名
show create database d1     			查看建库语句
show create table t1        			查看建表语句
show grants for root@'localhost' 	查看用户权限信息
show charset						查看所有的字符集
show collation						查看校对规则
show full processlist				查看数据库连接情况
show status							查看数据库的整体状态
show status	like '%lock%'			模糊查看数据库的整体状态
show variables 						查看数据库所有变量情况
show variables 	like '%innodb%'		查看数据库所有变量情况
show engines						查看所有支持存储引擎
show engine innodb status  			查看所有innodb存储引擎状态情况
show binary logs					查看二进制日志情况
show binlog events in 				查看二进制日志事件 
show relaylog events in 			查看relay日志事件
show slave status 					查看从库状态
show master status 					查看数据库binlog位置信息
show index from						查看表的索引情况

作者：Soulboy