第1章 SQL介绍
1.什么是SQL
属于关系型数据库产品中专⽤的语句。结构化查询语句。
2.SQL标准
SQL-89 \ SQL-92 \ SQL-99 \ SQL03 .....
3.SQL模式
作⽤：
影响到了SQL语句的执⾏⾏为。为了让数据库在存、取能够满⾜⽣活的常识、科学的逻辑，让这些数据有意义。
例⼦：
现实⽣活中常识：
⽇期： 1000-9999 年 1-12 ⽉ 1-31⽇
科学逻辑：
除法：除数不能为0
查看sql_mode:
mysql> select @@sql_mode;
ONLY_FULL_GROUP_BY : 针对group by语句执⾏时的规范
STRICT_TRANS_TABLES : 针对事务表启动严格模式
NO_ZERO_IN_DATE ： 2010-00-10
NO_ZERO_DATE ： 0000-00-00
ERROR_FOR_DIVISION_BY_ZERO ： 除数不能为0
NO_AUTO_CREATE_USER ： 是否⾃动创建⽤户
NO_ENGINE_SUBSTITUTION : 建表是使⽤了⼀个不⽀持的存储引擎报错。
4.SQL的分类
DDL ： 数据定义语⾔
对于： 库 、表（元数据）的 增、删、改
建库、删库、修改库、建表、删表、修改表
DCL ： 数据控制语⾔
grant
revoke
DML ： 数据操作语⾔
表的数据⾏进⾏的增、删、改、查
DQL ： 数据查询语⾔
对于表数据⾏查看
对于元数据查看
第2章 数据库对象属性介绍
1.数据库对象
库的对象:
库名
库属性：字符集、校对规则
表的对象:
表名
列 ：
列名
列属性：数据类型、约束、其他属性
⾏
表属性：引擎、字符集、校对规则、其他
2.字符集
种类说明:
1.utf8 最⼤⽀持3字节的字符。不⽀持emoji字符
2.utf8mb4 最⼤⽀持4字节的字符。⽀持emoji字符
注意：
8.0之前，默认字符集latin1,8.0之后是utf8mb4。
我们建议使⽤utf8mb4.
设置⽅法:
默认字符集参数
建库
建表
3.列属性
3.1 数字类型
整数：
tinyint 1字节 = 8位 = 00000000 - 11111111 = 2^8个 = 0 - 255
, -127-128
int 4字节 = 32位 = 2^32个 = 0 - 2^32-1
,-2^31-2^31-1 ,10位数
bigint 8字节 0 - 2^64-1
,-2^63-2^63-1 ,20位数
浮点数：
decimal(m,n)
3.2 字符串类型
char(N) :
N字符个数，最⼤不超过255
定⻓的字符串类型。
例如： char(10) ,最多存10个字符,只要10个字符以内，都10个字符⻓度的存储空间。剩
余⽤空格填充。
varchar(M) ：
M字符个数，最⼤不超过65535
变⻓的字符串类型。会额外占⽤1-2字节存储字符⻓度。255字符之内，额外1字节，255以
上，额外2字节
例如： varchar(10),最多存10个字符,按需分配存储空间。
abc 3 =4
asdasdadasd 1000 =1002
enum('bj','sh','tj','heibei','henan',.....)
district enum('bj','sh','tj','heibei','henan',.....)
3.3 时间类型
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会受到时区的影响
4.列约束
主键 primary key （PK）
1.⼀张表只能有⼀个，可以有多个列构成
2.特点： ⾮空+唯⼀
3.建议每张表都设置主键，有利于索引的应⽤，通常是使⽤⾃增的数字列更佳。
⾮空 not null
1.特点： 必须录⼊值。
2.建议： 每个列最好设置为，有利于索引的应⽤。
唯⼀ unique key
1.特点： 不能有重复值。
⽆符号 unsigned
1.特点： 针对数字列，⽆符号设定。
5.其他属性
表属性 ：
engine ： 存储引擎设置，默认是innodb，也是我们建议的。
charset ： utf8mb4
comment : 注释。
列属性 ：
default : 默认值。⼀般是在not null 配合使⽤
auto_increment： 数字列⾃增⻓。⼀般是在主键列配合使⽤
comment ： 列的注释，建议每个列都有
第2章 DDL数据库定义语⾔
1.库定义
1.1 库定义规范
1. 库名不能数字开头
2. 库名要和业务有关
3. 库名不要有⼤写字符
原因：为了多平台兼容。
4. 建库需要显示指定字符集。建议是utf8mb4。
5. ⽣产中禁⽤普通⽤户的drop database权限。
1.2 创建库
CREATE DATABASE oldjie CHARSET utf8mb4;
CREATE DATABASE school CHARSET utf8mb4;
1.3 修改库
CREATE DATABASE oldjie;
SHOW CREATE DATABASE oldjie;
SHOW CREATE DATABASE oldjie;
ALTER DATABASE oldli CHARSET utf8mb4;
修改库客户端默认字符集
SHOW VARIABLES LIKE '%char%';
SET NAMES utf8mb4;
修改配置⽂件添加默认字符集参数
vim /etc/my.cnf
[mysqld]
character_set_server=utf8mb4;
1.4 查看库
show databases;
SHOW CREATE DATABASE oldjie;
1.5 删除库
DROP DATABASE oldjie;
2.表定义
2.1 表定义规范
1. 建表
a. 表名：
不能数字开头
业务有关
不要⼤写字⺟
不要超过18字符
不能是关键字
b. 存储引擎使⽤InnoDB
c. 5.7版本以后，字符集使⽤utf8mb4
d. 列名要和业务有关，不要超过18个字符
e. 选择合适、⾜够、简短数据类型
f. 建议每个列设置not null
g. 每个列要有注释
h. 每个表要有主键
i. 针对not null 列，可以设定默认值。
j. 表注释
2. 修改表
a. 添加列，使⽤追加式添加列
b. 修改列属性，尽量使⽤modify语句
c. 修改表定义，建议在业务不繁忙期间进⾏。尽量采⽤pt-osc或者gh-ost⼯具减少业务影响。
推荐软件:
yearing 开源SQL审核⼯具。
inception SQL审核⼯具.
2.2 创建表
建表语句如下：
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT '学⽣姓名',
`age` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '学⽣年龄',
`gender` char(1) COLLATE utf8mb4_bin NOT NULL DEFAULT 'n' COMMENT '学
⽣性别',
`address` enum('北京','深圳','上海','⼴州','重庆','未知') COLLATE
utf8mb4_bin NOT NULL DEFAULT '未知' COMMENT '省份',
`intime` datetime NOT NULL COMMENT '⼊学时间',
`shenfen` char(18) COLLATE utf8mb4_bin NOT NULL COMMENT '身份证',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
2.3 查看表
use oldjie;
show tables;
show create table student;
desc student;
2.4 复制⼀张表
1 create table stu like student;
2.5 修改表定义
增加列：在student表中添加telnum列
use oldjie;
DESC `student`;
ALTER TABLE `student`
ADD COLUMN telnum CHAR(11) NOT NULL UNIQUE KEY DEFAULT '0' COMMENT '⼿机号' ;
不建议的⽅式：
在gender列后增加列
alter table oldjie.student
add column a CHAR(11) not null unique key default '0' comment '⼿机号'
after gender ;
desc student;
在第⼀列添加列
alter table oldjie.student
add column b CHAR(11) not null unique key default '0' comment '⼿机号' first ;
desc student;
删除列：（不代表⽣产操作，危险！！！！）
alter table student drop a;
alter table student drop b;
alter table student drop telnum;
2.6 修改表属性
修改表名：
ALTER TABLE student RENAME TO st;
修改引擎：
CREATE TABLE t1(id INT) ENGINE=MYISAM;
SHOW CREATE TABLE t1;
ALTER TABLE t1 ENGINE=INNODB;
SHOW CREATE TABLE t1;
修改字符集：
CREATE TABLE t2(id INT) CHARSET=utf8;
SHOW CREATE TABLE t2;
ALTER TABLE t2 CHARSET=utf8mb4;
SHOW CREATE TABLE t2;
2.7 修改列属性
修改列名：
ALTER TABLE st CHANGE shenfen cardnum CHAR(18) NOT NULL DEFAULT '0'
COMMENT '身份证';
修改默认值：
ALTER TABLE st CHANGE cardnum cardnum CHAR(18) NOT NULL DEFAULT '1'
COMMENT '身份证';
修改数据类型：
ALTER TABLE st MODIFY cardnum CHAR(20) NOT NULL DEFAULT '1' COMMENT '身份证';
2.8 删除表
drop table stu;
第4章 DML数据操作语⾔
1.INSERT 插⼊表数据
标准：
INSERT INTO
st(id,NAME,age,gender,address,intime,cardnum,num)
VALUES(1,'张三',18,'m','北京','2020-09-06','666666',10);
SELECT * FROM st;
部分列录⼊：
INSERT INTO
st(NAME,intime,num)
VALUES('李四',NOW(),1111);
SELECT * FROM st;
修改时间列的默认值为NOW()
ALTER TABLE st MODIFY intime DATETIME NOT NULL DEFAULT NOW() COMMENT '⼊学时间';
DESC st;
再次插⼊数据:
INSERT INTO
st(NAME,num)
VALUES('王五',11112);
SELECT * FROM st;
省略写法:
desc st;
insert into
st
values(5,'张三',18,'m','北京','2020-04-27','666666',10);
select * from st;
2.UPDATE 修改表数据
更新数据⾏:
UPDATE st SET NAME='张六' WHERE id=3;
SELECT * FROM st;
update st set name='张qi' , age=21 where id=4;
select * from st;
3.DELETE/UPDATE/TRUNCATE
删除表数据 3.1 DELETE
DELETE FROM st WHERE id=3;
SELECT * FROM st;
3.2 伪删除
update 替代 dalete, 添加状态列，1代表存在，0代表删除
第⼀步：增加状态列
ALTER TABLE st ADD COLUMN state TINYINT NOT NULL DEFAULT 1 COMMENT '状态列,0是删除,1是存在';
DESC st;
第⼆步：使⽤update 替换 delete
原：
delete from st where id=4
修改后：
UPDATE st SET state=0 WHERE id=4;
第三步：替换原来查询业务语句
原：
select * from st;
改变后：
SELECT * FROM st WHERE state=1;
4.⾯试题
题⽬：
drop table t1,truncate table t1,delete from t1 区别？
解答：
drop table t1;
作⽤：
1.删除所有表数据，删除整个表段（rm ibd ）,属于物理性质，会释放磁盘空间。
2.删除表定义 (rm frm , 元数据也会被删除)
truncate table t1;
作⽤：
1.保留表结构，清空表段中的数据⻚。属于物理删除，会释放磁盘空间。
delete from t1;
作⽤:
1.删除数据⾏。逐⾏删除。保留表结构，属于逻辑性质删除。只是标记删除，不会⽴即释放磁盘空间。
2.所以delete操作会产⽣碎⽚。
第5章 DQL数据查询语⾔
1.如何学习业务
1.查看表定义
desc city;
2.了解字段的意义
id : 主键 1-N数字
name ：城市名
countrycode ：国家编码(三个字⺟？CHN,USA)
3.查看部分数据
select * from city where id<10;
district : 区域 （省、州、县）
population : 城市⼈⼝
4.查看解释说明
查看comment的解释说明
2.SELECT 查询数据
2.1 查询数据库服务器配置参数
select @@port;
select @@server_id;
select @@basedir;
select @@datadir;
select @@socket;
select @@innodb_flush_log_at_trx_commit;
替代⽅法：
show variables;
show variables like '%trx%';
2.2 查询内置函数
help Functions;
select DATABASE();
select NOW();
select USER();
select CONCAT("hello world");
select user,host from mysql.user;
SELECT CONCAT("数据库⽤户:",USER,"@",HOST,";") FROM mysql.user;
2.3 多⼦句执⾏顺序
select 列
from 表
where 条件
group by 列
having 条件
order by 列
limit 条件
2.4 查询表中所有数据(⼩表)
use world;
select id,name,countrycode,district,population
from city;
或者
select id,name,countrycode,district,population
from world.city;
或者
select * from city;
2.5 查询部分列数据
查询所有城市名及⼈⼝信息
select name,population from city;
查询city表中，所有中国的城市信息
select * from city where countrycode = 'CHN';
查询⼈⼝数⼩于100⼈城市信息
SELECT * FROM city WHERE Population<100;
查询中国,⼈⼝数超过500w的所有城市信息
SELECT * FROM city WHERE countryCode='CHN' AND Population<5000000;
查询中国或美国的城市信息
SELECT * FROM city WHERE countryCode='CHN' OR countryCode='USA';
SELECT * FROM city WHERE countryCode IN ('CHN','USA');
查询⼈⼝数为100w-200w（包括两头）城市信息
SELECT * FROM city WHERE Population >= 1000000 AND Population <=
2000000;
SELECT * FROM city WHERE Population BETWEEN 1000000 AND 2000000;
查询中国或美国，⼈⼝数⼤于500w的城市
SELECT * FROM city WHERE (countryCode='CHN' OR countryCode='USA') AND
Population > 5000000;
SELECT * FROM city WHERE countryCode IN ('CHN','USA') AND Population > 5000000;
查询城市名为qing开头的城市信息
SELECT * FROM city WHERE NAME LIKE 'qing%';
3.GROUP BY 聚合函数
3.1 聚合函数
count() 统计数量
sum() 求和
avg() 平均数
max() 最⼤值
min() 最⼩值
group_concat() 列转⾏
3.2 group by 分组功能原理
1. 按照分组条件进⾏排序
2. 进⾏分组列的去重复
3. 聚合函数将其他列的结果进⾏聚合
3.3 group by练习
统计city表的⾏数
SELECT COUNT(*) FROM city;
统计中国城市的个数
SELECT COUNT(*) FROM city WHERE countryCode='CHN';
统计中国的总⼈⼝数
SELECT SUM(Population) FROM city WHERE countryCode='CHN';
统计每个国家的城市个数
SELECT countryCode,COUNT(NAME) FROM city GROUP BY countryCode;
统计每个国家的总⼈⼝数
SELECT countryCode,SUM(Population) FROM city GROUP BY countryCode;
统计中国每个省的城市个数及城市名列表
SELECT district, COUNT(NAME),GROUP_CONCAT(NAME)
FROM city
WHERE countrycode='CHN' GROUP BY district;
4.HAVING 聚合判断
4.1 作⽤
主要应⽤在group by之后需要的判断。
4.2 练习
统计每个国家的总⼈⼝数，只显示总⼈⼝超过1亿⼈的信息
SELECT countrycode,SUM(population)
FROM city
GROUP BY countrycode
HAVING SUM(population)>100000000;
5.ORDER BY 聚合排序
查询所有城市信息，并按照⼈⼝数排序输出
SELECT * FROM city ORDER BY population;
查询中国所有的城市信息，并按照⼈⼝数从⼤到⼩排序输出
SELECT * FROM city WHERE countryCode='CHN' ORDER BY population DESC;
每个国家的总⼈⼝数，总⼈⼝超过5000w的信息,并按总⼈⼝数从⼤到⼩排序输出
SELECT countrycode,SUM(population)
FROM city
GROUP BY countrycode
HAVING SUM(population) > 50000000
ORDER BY SUM(population) DESC;
6.LIMIT 分⻚查询
查询中国所有的城市信息，并按照⼈⼝数从⼤到⼩排序输出，只显示前⼗名
select *
from city
where countrycode = 'CHN'
order by population desc
limit 10 ;
查询中国所有的城市信息，并按照⼈⼝数从⼤到⼩排序输出，只显示6-10名
select *
from city
where countrycode = 'CHN'
order by population desc
limit 5,5
select *
from city
where countrycode = 'CHN'
order by population desc
limit 5 offset 5;
解释:
-- limit M,N : 跳过M⾏，显示N⾏
-- limit N offset M : 跳过M⾏，显示N⾏
7.⼦句执⾏顺序
select select_list from where group_by having order by limit
4 1 2 3 5 6 7
select select_list 4
from 1
where 2
group_by 3
having 5
order by 6
limit 7