Data Manipulation Language(DML数据操纵语言)，如：对表中的记录操作增删改

Data Query Language(DQL 数据查询语言)，如：对表中数据的查询操作

Data Control Language(DCL 数据控制语言)，如：对用户权限的设置

（1）mysql的sql语法不区分大小写

MySQL的关键字和函数名等不区分大小写，但是对于数据值是否区分大小写，和字符集与校对规则有关。

ci（大小写不敏感），cs（大小写敏感），_bin（二元，即比较是基于字符编码的值而与language无关，区分大小写）

（2）命名时：尽量使用26个英文字母大小写，数字0-9，下划线，不要使用其他符号user_id

（3）建议不要使用mysql的关键字等来作为表名、字段名等，如果不小心使用，请在SQL语句中使用`（飘号）引起来

（4）数据库和表名、字段名等对象名中间不要包含空格

（5）同一个mysql软件中，数据库不能同名，同一个库中，表不能重名，同一个表中，字段不能重名

（6）标点符号：

• 必须成对

• 必须英文状态下半角输入方式

• 字符串和日期类型可以使用单引号’’

• 列的别名可以使用双引号""，给表名取别名不要使用双引号。取别名时as可以省略

• 如果列的别名没有包含空格，可以省略双引号，如果有空格双引号不能省略。

必须成对

必须英文状态下半角输入方式

字符串和日期类型可以使用单引号’’

列的别名可以使用双引号""，给表名取别名不要使用双引号。取别名时as可以省略

如果列的别名没有包含空格，可以省略双引号，如果有空格双引号不能省略。

（7）SQL脚本中如何加注释

• 单行注释：#注释内容

• 单行注释：–空格注释内容 其中–后面的空格必须有

• 多行注释：/* 注释内容 */

单行注释：#注释内容

单行注释：–空格注释内容 其中–后面的空格必须有

多行注释：/* 注释内容 */

showdatabases;

SHOWDATABASES;

#创建表格

#create table student info(...); #表名错误，因为表名有空格

createtablestudent_info(...);

#其中name使用``飘号，因为name和系统关键字或系统函数名等预定义标识符重名了。

CREATETABLEt_stu(

idINT,

`name`VARCHAR(20)

);

selectidas"编号", `name`as"姓名"fromt_stu; #起别名时，as都可以省略

selectidas编号, `name`as姓名 fromt_stu; #如果字段别名中没有空格，那么可以省略""

selectidas编 号, `name`as姓 名 fromt_stu; #错误，如果字段别名中有空格，那么不能省略""

第二章-DDL操作数据库2.1、创建数据库(掌握)

• 语法

语法

字符集(charset)：是一套符号和编码。

• 练习

练习

创建一个day01的数据库（默认字符集)

createdatabaseday01;

创建一个day01_2的数据库,指定字符集为gbk(了解)

createdatabaseday01_2 charactersetgbk;

2.2、查看所有的数据库

• 查看所有的数据库

查看所有的数据库

语法

showdatabases;

• 查看数据库的定义结构【了解】

查看数据库的定义结构【了解】

语法

showcreatedatabase数据库名;

查看day01这个数据库的定义

showcreatedatabaseday01;

2.3、删除数据库

语法

dropdatabase数据库名;

删除day01_2数据库

dropdatabaseday01_2;

2.4、修改数据库【了解】

语法

alterdatabase数据库名 characterset字符集;

修改day01这个数据库的字符集(gbk)

alter database day01 character setgbk;

注意：

• 是utf8，不是utf-8

• 不是修改数据库名

是utf8，不是utf-8

不是修改数据库名

切换数据库, 选定哪一个数据库

use数据库名; //注意: 在创建表之前一定要指定数据库. use数据库名

练习: 使用day01

useday01;

查看正在使用的数据库

selectdatabase;

第三章-DDL操作表3.1、创建表

• 语法

语法

列名 类型 [约束],

列名 类型 [约束]

...

);

• 类型

类型

数值类型

整型系列：xxxInt

• int(M)，必须和unsigned zerofill一起使用才有意义

int(M)，必须和unsigned zerofill一起使用才有意义

浮点型系列：float,double（或real)

• double(M,D)：表示最长为M位，其中小数点后D位

double(M,D)：表示最长为M位，其中小数点后D位

定点型系列：decimal（底层实际上是使用字符串进行存储）

• decimal(M,D)：表示最长为M位，其中小数点后D位

decimal(M,D)：表示最长为M位，其中小数点后D位

位类型：bit

• 字节范围是：1-8，值范围是：bit(1)~bit(64)，默认bit(1)

字节范围是：1-8，值范围是：bit(1)~bit(64)，默认bit(1)

用来存储二进制数。对于位字段，直接使用select命令将不会看到结果。可以使用bit或hex函数进行读取。插入bit类型字段时，使用bit函数转为二进制值再插入，因为二进制码是“01”。常见 SQL 面试题：经典 50 例

日期时间类型

日期时间类型：year, date, datetime, timestamp

注意一下每一种日期时间的表示范围

timestamp和datetime的区别：

• timestamp范围比较小

• timestamp和时区有关

• show variables like ‘time_zone’;

• set time_zone = ‘+8:00’;

• timestamp受MySQL版本和服务器的SQLMode影响很大

• 表中的第一个非空的timestamp字段如果插入和更新为NULL则会自动设置为系统时间。

timestamp范围比较小

timestamp和时区有关

• show variables like ‘time_zone’;

• set time_zone = ‘+8:00’;

show variables like ‘time_zone’;

set time_zone = ‘+8:00’;

timestamp受MySQL版本和服务器的SQLMode影响很大

表中的第一个非空的timestamp字段如果插入和更新为NULL则会自动设置为系统时间。

字符串类型

MySQL中提供了多种对字符数据的存储类型，不同的版本可能有所差异。常见的有：

char,varchar，xxtext，binary，varbinary，xxblob，enum，set等等

• 字符串类型char,varchar(M)

• char如果没有指定宽度，默认为1个字符

• varchar(M)，必须指定宽度

字符串类型char,varchar(M)

• char如果没有指定宽度，默认为1个字符

• varchar(M)，必须指定宽度

char如果没有指定宽度，默认为1个字符

varchar(M)，必须指定宽度

binary和varbinary类似于char和varchar，不同的是它们包含二进制字符串，不支持模糊查询之类的。

一般在保存少量字符串的时候，我们会选择char和varchar；而在保存较大文本时，通常会选择使用text或blob系列。blob和text值会引起一些性能问题，特别是在执行了大量的删除操作时，会在数据表中留下很大的“空洞”，为了提高性能，建议定期时候用optimize table功能对这类表进行碎片整理。可以使用合成的(Synthetic)索引来提高大文本字段的查询性能，如果需要对大文本字段进行模糊查询，MySql提供了前缀索引。但是仍然要在不必要的时候避免检索大型的blob或text值。

enum枚举类型，它的值范围需要在创建表时通过枚举方式显式指定，对于1~255个成员的枚举需要1个字节存储；对于【 255`65535】个成员需要2个字节存储。例如：gender enum(‘男’,‘女’)。如果插入枚举值以外的值，会按第一个值处理。一次只能从枚举值中选择一个。

set集合类型，可以包含0~64个成员。一次可以从集合中选择多个成员。如果选择了1-8个成员的集合，占1个字节，依次占2个，3个。。8个字节。例如：hoppy set(‘吃饭’,‘睡觉’,‘玩游戏’,‘旅游’)，选择时’吃饭,睡觉’或’睡觉,玩游戏,旅游’

示例

+----------------+--------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+----------------+--------------+------+-----+---------+----------------+

| eid | int(11) | NO | PRI | NULL | auto_increment |

| ename | varchar(20) | NO | | NULL | |

| tel | char(11) | NO | | NULL | |

| gender | char(1) | YES | | 男 | |

| salary | double | YES | | NULL | |

| commission_pct | double(3,2) | YES | | NULL | |

| birthday | date | YES | | NULL | |

| hiredate | date | YES | | NULL | |

| job_id | int(11) | YES | | NULL | |

| email | varchar(32) | YES | | NULL | |

| mid | int(11) | YES | | NULL | |

| address | varchar(150) | YES | | NULL | |

| native_place | varchar(10) | YES | | NULL | |

| did | int(11) | YES | | NULL | |

+----------------+--------------+------+-----+---------+----------------+

约束

• 即规则,规矩 限制;

• 作用：保证用户插入的数据保存到数据库中是符合规范的

即规则,规矩 限制;

作用：保证用户插入的数据保存到数据库中是符合规范的

• not null: 非空 ; eg: username varchar(40) not null username这个列不能有null值

• unique:唯一约束, 后面的数据不能和前面重复; eg: cardNo char(18) unique; cardNo 列里面不可以有重复数据

• primary key；主键约束(非空+唯一); 一般用在表的id列上面. 一张表基本上都有id列的, id列作为唯一标识的

• auto_increment: 自动增长,必须是设置了primary key之后,才可以使用auto_increment

• id int primary key auto_increment; id不需要我们自己维护了, 插入数据的时候直接插入null, 自动的增长进行填充进去, 避免重复了.

not null: 非空 ; eg: username varchar(40) not null username这个列不能有null值

unique:唯一约束, 后面的数据不能和前面重复; eg: cardNo char(18) unique; cardNo 列里面不可以有重复数据

primary key；主键约束(非空+唯一); 一般用在表的id列上面. 一张表基本上都有id列的, id列作为唯一标识的

• auto_increment: 自动增长,必须是设置了primary key之后,才可以使用auto_increment

auto_increment: 自动增长,必须是设置了primary key之后,才可以使用auto_increment

id int primary key auto_increment; id不需要我们自己维护了, 插入数据的时候直接插入null, 自动的增长进行填充进去, 避免重复了.

注意:

• 先设置了primary key 再能设置auto_increment

• 只有当设置了auto_increment 才可以插入null , 否则插入null会报错

先设置了primary key 再能设置auto_increment

只有当设置了auto_increment 才可以插入null , 否则插入null会报错

id列:

• 给id设置为int类型, 添加主键约束, 自动增长

• 或者给id设置为字符串类型,添加主键约束, 不能设置自动增长

给id设置为int类型, 添加主键约束, 自动增长

或者给id设置为字符串类型,添加主键约束, 不能设置自动增长

练习

创建一张学生表(含有id字段,姓名字段不能重复,性别字段不能为空默认值为男. id为主键自动增长)

CREATETABLEstudent(

idINTPRIMARY KEYAUTO_INCREMENT, -- 主键自增长

NAMEVARCHAR(30) UNIQUE, -- 唯一约束

gender CHAR(1) NOTNULLDEFAULT'男'

);

3.2、查看表【了解】

查看所有的表

showtables;

查看表的定义结构

语法

desc 表名；

练习: 查看student表的定义结构

desc student;

3.3、修改表【掌握，但是不要记忆】

语法

• 增加一列

增加一列

altertable【数据库名.]表名称 add【column】 字段名 数据类型 first;

altertable【数据库名.]表名称 add【column】 字段名 数据类型 after另一个字段;

• 修改列的类型约束:alter table 表名 modify 字段 类型 约束 ；

• 修改列的名称，类型，约束: alter table 表名 change 旧列 新列 类型 约束;

• 删除一列: alter table 表名 drop 列名;

• 修改表名 : rename table 旧表名 to 新表名;

修改列的类型约束:alter table 表名 modify 字段 类型 约束 ；

修改列的名称，类型，约束: alter table 表名 change 旧列 新列 类型 约束;

删除一列: alter table 表名 drop 列名;

修改表名 : rename table 旧表名 to 新表名;

练习

给学生表增加一个grade字段,类型为varchar(20),不能为空

ALTERTABLEstudent ADDgrade VARCHAR(20) NOTNULL;

给学生表的gender字段改成int类型，不能为空，默认值为1

altertablestudent modifygender varchar(20);

给学生表的grade字段修改成class字段

ALTERTABLEstudent CHANGEgrade classVARCHAR(20) NOTNULL;

把class字段删除

ALTERTABLEstudent DROPclass;

把学生表修改成老师表(了解)

RENAMETABLEstudent TOteacher;

3.4、删除表【掌握】

语法

droptable表名；

把teacher表删除

droptableteacher;

第四章-DML操作表记录-增删改【重点】

准备工作: 创建一张商品表(商品id,商品名称,商品价格,商品数量.)

createtableproduct(

pid intprimary keyauto_increment,

pname varchar(40),

price double,

numint

);

4.1、插入记录

语法

• 方式一: 插入指定列, 如果没有把这个列进行列出来, 以null进行自动赋值了.

方式一: 插入指定列, 如果没有把这个列进行列出来, 以null进行自动赋值了.

eg: 只想插入pname, price , insert into t_product(pname, price) values(‘mac’,18000);

insertinto表名(列,列..) values(值,值..);

注意: 如果没有插入了列设置了非空约束, 会报错的

• 方式二: 插入所有的列,如果哪列不想插入值,则需要赋值为null

方式二: 插入所有的列,如果哪列不想插入值,则需要赋值为null

insert into 表名 values(值,值....);

eg:

insertintoproduct values(null,'苹果电脑',18000.0,10);

insertintoproduct values(null,'华为5G手机',30000,20);

insertintoproduct values(null,'小米手机',1800,30);

insertintoproduct values(null,'iPhonex',8000,10);

insertintoproduct values(null,'iPhone7',6000,200);

insertintoproduct values(null,'iPhone6s',4000,1000);

insertintoproduct values(null,'iPhone6',3500,100);

insertintoproduct values(null,'iPhone5s',3000,100);

insertintoproduct values(null,'方便面',4.5,1000);

insertintoproduct values(null,'咖啡',11,200);

insertintoproduct values(null,'矿泉水',3,500);

4.2、更新记录

• 语法

语法

• 练习

练习

将所有商品的价格修改为5000元

updateproduct setprice = 5000;

将商品名是苹果电脑的价格修改为18000元

UPDATEproduct setprice = 18000WHEREpname = '苹果电脑';

将商品名是苹果电脑的价格修改为17000,数量修改为5

UPDATEproduct setprice = 17000,num= 5WHEREpname = '苹果电脑';

将商品名是方便面的商品的价格在原有基础上增加2元

UPDATEproduct setprice = price+2WHEREpname = '方便面';

4.3、删除记录

• delete

delete

根据条件，一条一条数据进行删除

语法

deletefrom表名 [where条件] 注意: 删除数据用delete,不用truncate

类型

删除表中名称为’苹果电脑’的记录

deletefromproduct wherepname = '苹果电脑';

删除价格小于5001的商品记录

deletefromproduct whereprice < 5001;

删除表中的所有记录(要删除一般不建议使用delete语句，delete语句是一行一行执行，速度过慢)

deletefromproduct;

• truncate 把表直接DROP掉，然后再创建一个同样的新表。删除的数据不能找回。执行速度比DELETE快

truncate 把表直接DROP掉，然后再创建一个同样的新表。删除的数据不能找回。执行速度比DELETE快

• 工作中删除数据

• 物理删除: 真正的删除了, 数据不在, 使用delete就属于物理删除

• 逻辑删除: 没有真正的删除, 数据还在. 搞一个标记, 其实逻辑删除是更新 eg: state 1 启用 0禁用

工作中删除数据

• 物理删除: 真正的删除了, 数据不在, 使用delete就属于物理删除

• 逻辑删除: 没有真正的删除, 数据还在. 搞一个标记, 其实逻辑删除是更新 eg: state 1 启用 0禁用

物理删除: 真正的删除了, 数据不在, 使用delete就属于物理删除

逻辑删除: 没有真正的删除, 数据还在. 搞一个标记, 其实逻辑删除是更新 eg: state 1 启用 0禁用

5.2、简单查询

查询所有行和所有列的记录

• 语法

语法

查询商品表里面的所有的列

select* fromproduct;

查询某张表特定列的记录

• 语法

语法

查询商品名字和价格

selectpname, price fromproduct;

去重查询 distinct

• 语法

语法

去重查询商品的名字

SELECTDISTINCTpname,price FROMproduct

注意点: 去重针对某列, distinct前面不能先出现列名

别名查询

• 语法

语法

select别名.* from表 as别名 //表别名(多表查询, 明天会具体讲)

查询商品信息，使用别名

SELECTpid ,pname AS'商品名',price AS'商品价格',numAS'商品库存'FROMproduct

运算查询(+,-,*,/,%等)

把商品名，和商品价格+10查询出来:我们既可以将某个字段加上一个固定值，又可以对多个字段进行运算查询

selectpname ,price+10as'price'fromproduct;

selectname,chinese+math+english astotal fromstudent

注意

• 运算查询字段,字段之间是可以的

• 字符串等类型可以做运算查询，但结果没有意义

运算查询字段,字段之间是可以的

字符串等类型可以做运算查询，但结果没有意义

• 语法

语法

//取出表中的每条数据，满足条件的记录就返回，不满足条件的记录不返回

运算符

1、比较运算符

大于：>

小于：<

大于等于：>=

小于等于：<=

等于：= 不能用于null判断

不等于：!= 或 <>

安全等于: <=> 可以用于null值判断

2、逻辑运算符（建议用单词，可读性来说）

逻辑与：&& 或 and

逻辑或：|| 或 or

逻辑非：! 或 not

逻辑异或：^ 或 xor

3、范围

区间范围：between x and y

not between x and y

集合范围：in(x,x,x)

not in(x,x,x)

4、模糊查询和正则匹配（只针对字符串类型，日期类型）

like 'xxx'模糊查询是处理字符串的时候进行部分匹配

如果想要表示0~n个字符，用%

如果想要表示确定的1个字符，用_

regexp '正则'

5、特殊的null值处理

#（1）判断时

xx is null

xx is not null

xx <=> null

#（2）计算时

ifnull(xx,代替值) 当xx是null时，用代替值计算

• 练习

练习

查询商品价格>3000的商品

select* fromproduct whereprice > 3000;

查询pid=1的商品

select* fromproduct wherepid = 1;

查询pid<>1的商品（！=）

select* fromproduct wherepid <> 1;

查询价格在3000到6000之间的商品

select* fromproduct whereprice between3000and6000;

查询pid在1，5，7，15范围内的商品

select* fromproduct whereid= 1;

select* fromproduct whereid= 5;

select* fromproduct whereid= 7;

select* fromproduct whereid= 15;

select* fromproduct whereidin(1,5,7,15);

查询商品名以iPho开头的商品(iPhone系列)

select* fromproduct wherepname like'iPho%';

查询商品价格大于3000并且数量大于20的商品 (条件 and 条件 and…)

select* fromproduct whereprice > 3000andnum> 20;

查询id=1或者价格小于3000的商品

select* fromproduct wherepid = 1orprice < 3000;

5.4、排序查询

排序是写在查询的后面，代表把数据查询出来之后再排序

• 环境的准备

环境的准备

CREATETABLEstudent(

sidINTPRIMARY KEYauto_increment,

sname VARCHAR(40),

sex VARCHAR(10),

age INT,

score DOUBLE

);

INSERTINTOstudent VALUES(null,'zs','男',18,98.5);

INSERTINTOstudent VALUES(null,'ls','女',18,96.5);

INSERTINTOstudent VALUES(null,'ww','男',15,50.5);

INSERTINTOstudent VALUES(null,'zl','女',20,98.5);

INSERTINTOstudent VALUES(null,'tq','男',18,60.5);

INSERTINTOstudent VALUES(null,'wb','男',38,98.5);

INSERTINTOstudent VALUES(null,'小丽','男',18,100);

INSERTINTOstudent VALUES(null,'小红','女',28,28);

INSERTINTOstudent VALUES(null,'小强','男',21,95);

• 单列排序

单列排序

语法: 只按某一个字段进行排序，单列排序

SELECT字段名 FROM表名 [WHERE条件] ORDERBY字段名 [ASC|DESC]; //ASC: 升序，默认值; DESC: 降序

案例: 以分数降序查询所有的学生

SELECT* FROMstudent ORDERBYscore DESC

• 组合排序

组合排序

语法: 同时对多个字段进行排序，如果第1个字段相等，则按第2个字段排序，依次类推

SELECT字段名 FROM表名 WHERE字段=值 ORDERBY字段名1[ASC|DESC], 字段名2[ASC|DESC];

练习: 以分数降序查询所有的学生, 如果分数一致,再以age降序

SELECT* FROMstudent ORDERBYscore DESC, age DESC

5.5、聚合函数

聚合函数用于统计，通常会和分组查询一起使用，用于统计每组的数据

• 聚合函数列表

聚合函数列表

语法

SELECT聚合函数(列名) FROM表名 [where条件];

案例

-- 求出学生表里面的最高分数

SELECTMAX(score) FROMstudent

-- 求出学生表里面的最低分数

SELECTMIN(score) FROMstudent

-- 求出学生表里面的分数的总和(忽略null值)

SELECTSUM(score) FROMstudent

-- 求出学生表里面的平均分

SELECTAVG(score) FROMstudent

-- 求出学生表里面的平均分（缺考了当成0分处理）

SELECTAVG(IFNULL(score,0)) FROMstudent

-- 统计学生的总人数 (忽略null)

SELECTCOUNT(sid) FROMstudent

SELECTCOUNT(*) FROMstudent

注意: 聚合函数会忽略空值NULL

我们发现对于NULL的记录不会统计，建议如果统计个数则不要使用有可能为null的列，但如果需要把NULL也统计进去呢？我们可以通过 IFNULL(列名，默认值) 函数来解决这个问题. 如果列不为空，返回这列的值。如果为NULL，则返回默认值。

-- 求出学生表里面的平均分（缺考了当成0分处理）

SELECTAVG(IFNULL(score,0)) FROMstudent;

5.6、分组查询

GROUP BY将分组字段结果中相同内容作为一组，并且返回每组的第一条数据，所以单独分组没什么用处。分组的目的就是为了统计，一般分组会跟聚合函数一起使用

• 分组

分组

语法

SELECT字段1,字段2... FROM表名 [where条件] GROUPBY列 [HAVING条件];

案例

-- 根据性别分组, 统计每一组学生的总人数

SELECTsex '性别',COUNT(sid) '总人数'FROMstudent GROUPBYsex

-- 根据性别分组，统计每组学生的平均分

SELECTsex '性别',AVG(score) '平均分'FROMstudent GROUPBYsex

-- 根据性别分组，统计每组学生的总分

SELECTsex '性别',SUM(score) '总分'FROMstudent GROUPBYsex

• 分组后筛选 having

分组后筛选 having

分组后的条件，不能写在where之后，where关键字要写在group by之前

根据性别分组, 统计每一组学生的总人数> 5的(分组后筛选)

SELECTsex, count(*) FROMstudent GROUPBYsex HAVINGcount(sid) > 5

根据性别分组，只统计年龄大于等于18的，并且要求组里的人数大于4

SELECTsex '性别',COUNT(sid) '总人数'FROMstudent WHEREage >= 18GROUPBYsex HAVINGCOUNT(sid) > 4

• where和having的区别【面试】

where和having的区别【面试】

where 子句作用

• 1)对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，即先过滤再分组。

• 2)where后面不可以使用聚合函数

1)对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，即先过滤再分组。

2)where后面不可以使用聚合函数

having字句作用

1. having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤。

1. having后面可以使用聚合函数

1. having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤。

having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤。

1. having后面可以使用聚合函数

having后面可以使用聚合函数

• 语法

语法

• 案例

案例

-- limit 关键字是使用在查询的后边，如果有排序的话则使用在排序的后边

-- limit的语法: limit offset,length 其中offset表示跳过多少条数据，length表示查询多少条数据

SELECT* FROMproduct LIMIT0,3

-- 查询product表中的前三条数据(0表示跳过0条，3表示查询3条)

SELECT* FROMproduct LIMIT3,3

-- 查询product表的第四到六条数据(3表示跳过3条，3表示查询3条)

-- 分页的时候，只会告诉你我需要第几页的数据，并且每页有多少条数据

-- 假如，每页需要3条数据，我想要第一页数据: limit 0,3

-- 假如,每页需要3条数据，我想要第二页数据: limit 3,3

-- 假如，每页需要3条数据，我想要第三页数据: limit 6,3

-- 结论: length = 每页的数据条数，offset = (当前页数 - 1)*每页数据条数

-- limit (当前页数 - 1)*每页数据条数, 每页数据条数

5.8、查询的语法小结select...from...where...group by...order by...limit

select...from...where...

select...from...where...order by...

select...from...where...limit...

select...from...where...order by...imit

第六章 数据库三范式

好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响。建立科学的，规范的数据库就需要满足一些规则来优化数据的设计和存储，这些规则就称为范式。

6.1、第一范式: 确保每列保持原子性

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，如下表所示。

如果不遵守第一范式，查询出数据还需要进一步处理（查询不方便）。遵守第一范式，需要什么字段的数据就查询什么数据（方便查询）

6.2、第二范式: 确保表中的每列都和主键相关

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示

这样就产生一个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则。

而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中，就非常完美了。如下所示

<img src="imgs/tu_13.png"style="zoom: 67%;"/>

这样设计，在很大程度上减小了数据库的冗余。如果要获取订单的商品信息，使用商品编号到商品信息表中查询即可

6.3、第三范式: 确保每列都和主键列直接相关,而不是间接相关

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。

<img src="imgs/tu_14.png"style="zoom:67%;"/>

这样在查询订单信息的时候，就可以使用客户编号来引用客户信息表中的记录，也不必在订单信息表中多次输入客户信息的内容，减小了数据冗余

第七章 外键约束7.1、外键约束的概念

在遵循三范式的前提下，很多时候我们必须要进行拆表，将数据分别存放在多张表中，以减少冗余数据。但是拆分出来的表与表之间是有着关联关系的，我们必须得通过一种约束来约定表与表之间的关系，这种约束就是外键约束

7.2、外键约束的作用

外键约束是保证一个或两个表之间的参照完整性,外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。

7.3、创建外键约束的语法

• 在建表时指定外键约束

在建表时指定外键约束

字段名1数据类型 primary key,

字段名2数据类型 ,

....,

[constraint外键约束名] foreignkey(从表字段) references主表名(主表字段) [onupdate外键约束等级][ondelete外键约束等级]

#外键只能在所有字段列表后面单独指定

#如果要自己命名外键约束名，建议 主表名_从表名_关联字段名_fk

);

• 在建表后指定外键约束

在建表后指定外键约束

7.4、删除外键约束的语法ALTERTABLE表名称 DROPFOREIGNKEY外键约束名;

#查看约束名 SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';

#删除外键约束不会删除对应的索引，如果需要删除索引，需要用ALTER TABLE 表名称 DROP INDEX 索引名;

#查看索引名 show index from 表名称;

7.5、外键约束的要求

• 在从表上建立外键，而且主表要先存在。

• 一个表可以建立多个外键约束

• 通常情况下，从表的外键列一定要指向主表的主键列

• 从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样

在从表上建立外键，而且主表要先存在。

一个表可以建立多个外键约束

通常情况下，从表的外键列一定要指向主表的主键列

从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样

• Cascade方式：在主表上update/delete记录时，同步update/delete掉从表的匹配记录

• Set null方式：在主表上update/delete记录时，将从表上匹配记录的列设为null，但是要注意子表的外键列不能为not null

• No action方式：如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作

• Restrict方式：同no action, 都是立即检查外键约束

• Set default方式（在可视化工具SQLyog中可能显示空白）：父表有变更时,子表将外键列设置成一个默认的值，但Innodb不能识别

• 如果没有指定等级，就相当于Restrict方式

Cascade方式：在主表上update/delete记录时，同步update/delete掉从表的匹配记录

Set null方式：在主表上update/delete记录时，将从表上匹配记录的列设为null，但是要注意子表的外键列不能为not null

No action方式：如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作

Restrict方式：同no action, 都是立即检查外键约束

Set default方式（在可视化工具SQLyog中可能显示空白）：父表有变更时,子表将外键列设置成一个默认的值，但Innodb不能识别

如果没有指定等级，就相当于Restrict方式

createtabledept(

idintprimary key,

dept_name varchar(50),

dept_location varchar(50)

);

-- 员工表

CREATETABLEemp(

eid intprimary key,

namevarchar(50) notnull,

sex varchar(10),

dept_id int

);

-- 给员工表表的dept_id添加外键指向部门表的主键

altertableemp addforeignkey(dept_id) referencesdept(id)

第八章 多表间关系8.1、一对多关系

• 概念

概念

一对多的关系是指: 主表的一行数据可以同时对应从表的多行数据，反过来就是从表的多行数据指向主表的同一行数据。

• 应用场景

应用场景

分类表和商品表、班级表和学生表、用户表和订单表等等

• 建表原则

建表原则

将一的一方作为主表，多的一方作为从表，在从表中指定一个字段作为外键，指向主表的主键

• 建表语句练习

建表语句练习

CREATETABLEcategory(

cid INTPRIMARY KEYAUTO_INCREMENT,

cname VARCHAR(50)

);

-- 创建商品表

CREATETABLEproduct(

pid INTPRIMARY KEYAUTO_INCREMENT,

pname VARCHAR(50),

price DOUBLE,

cid INT

)

-- 给商品表添加一个外键

altertableproduct addforeignkey(cid) referencescategory(cid)

8.2、多对多关系

• 概念

概念

两张表都是多的一方，A表的一行数据可以同时对应B表的多行数据，反之B表的一行数据也可以同时对应A表的多行数据

• 应用场景

应用场景

订单表和商品表、学生表和课程表等等

• 建表原则

建表原则

因为两张表都是多的一方，所以在两张表中都无法创建外键，所以需要新创建一张中间表，在中间表中定义两个字段，这俩字段分别作为外键指向两张表各自的主键

• 建表语句练习

建表语句练习

CREATETABLEstudent(

sidINTPRIMARY KEYAUTO_INCREMENT,

sname VARCHAR(50)

);

-- 创建课程表

CREATETABLEcourse(

cid INTPRIMARY KEYAUTO_INCREMENT,

cname VARCHAR(20)

);

-- 创建中间表

CREATETABLEs_c_table(

sno INT,

cno INT

);

-- 给sno字段添加外键指向student表的sid主键

ALTERTABLEs_c_table ADDCONSTRAINTfkey01 FOREIGNKEY(sno) REFERENCESstudent(sid);

-- 给cno字段添加外键指向course表的cid主键

ALTERTABLEs_c_table ADDCONSTRAINTfkey03 FOREIGNKEY(cno) REFERENCEScourse(cid);

8.3、一对一关系(了解)

• 第一种一对一关系

第一种一对一关系

我们之前学习过一对多关系，在一对多关系中主表的一行数据可以对应从表的多行数据，反之从表的一行数据则只能对应主表的一行数据。这种一行数据对应一行数据的关系，我们可以将其看作一对一关系

• 第二种一对一关系

第二种一对一关系

A表中的一行数据对应B表中的一行数据，反之B表中的一行数据也对应A表中的一行数据，此时我们可以将A表当做主表B表当做从表，或者是将B表当做主表A表当做从表

• 建表原则

建表原则

在从表中指定一个字段创建外键并指向主表的主键，然后给从表的外键字段添加唯一约束

第九章 多表关联查询

多表关联查询是使用一条SQL语句，将关联的多张表的数据查询出来

9.1、环境准备-- 创建一张分类表(类别id,类别名称.备注:类别id为主键并且自动增长)

CREATETABLEt_category(

cid INTPRIMARY KEYauto_increment,

cname VARCHAR(40)

);

INSERTINTOt_category values(null,'手机数码');

INSERTINTOt_category values(null,'食物');

INSERTINTOt_category values(null,'鞋靴箱包');

-- 创建一张商品表(商品id,商品名称,商品价格,商品数量,类别.备注:商品id为主键并且自动增长)

CREATETABLEt_product(

pid INTPRIMARY KEYauto_increment,

pname VARCHAR(40),

price DOUBLE,

numINT,

cno INT

);

insertintot_product values(null,'苹果电脑',18000,10,1);

insertintot_product values(null,'iPhone8s',5500,100,1);

insertintot_product values(null,'iPhone7',5000,100,1);

insertintot_product values(null,'iPhone6s',4500,1000,1);

insertintot_product values(null,'iPhone6',3800,200,1);

insertintot_product values(null,'iPhone5s',2000,10,1);

insertintot_product values(null,'iPhone4s',18000,1,1);

insertintot_product values(null,'方便面',4.5,1000,2);

insertintot_product values(null,'咖啡',10,100,2);

insertintot_product values(null,'矿泉水',2.5,100,2);

insertintot_product values(null,'法拉利',3000000,50,null);

-- 给 商品表添加外键

ALTERTABLEt_product ADDFOREIGNKEY(cno) REFERENCESt_category(cid);

9.2、交叉查询【了解】

交叉查询其实就是将多张表的数据没有条件地连接在一起进行展示

• 语法

语法

selecta.*,b.* froma,b ;

--或者

select* froma,b;

• 练习

练习

使用交叉查询类别和商品

select* fromt_category,t_product;

通过查询结果我们可以看到，交叉查询其实是一种错误的做法，在查询到的结果集中有大量的错误数据，我们称交叉查询到的结果集是笛卡尔积

• 笛卡尔积

笛卡尔积

假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。

9.3、内连接查询

交叉查询产生这样的结果并不是我们想要的，那么怎么去除错误的、不想要的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过**关联关系(主外键关系)**去除笛卡尔积。这种通过条件过滤去除笛卡尔积的查询，我们称之为连接查询。连接查询又可以分为内连接查询和外连接查询，我们先学习内连接查询

• 隐式内连接查询

隐式内连接查询

隐式内连接查询里面是没有inner join关键字

select[字段,字段,字段] froma,b where连接条件 (b表里面的外键 = a表里面的主键 )

• 显式内连接查询

显式内连接查询

显式内连接查询里面是有inner join关键字

select[字段,字段,字段] froma [inner] joinb on连接条件 [ where其它条件]

• 内连接查询练习

内连接查询练习

查询所有类别下的商品信息,如果该类别下没有商品则不展示

-- 1 隐式内连接方式

select*fromt_category c, t_product p WHEREc.cid = p.cno;

-- 2 显示内连接方式

-- 查询手机数码这个分类下的所有商品的信息以及分类信息

SELECT* FROMt_product tp INNERJOINt_category tc ONtp.cno = tc.cid WHEREtc.cname = '手机数码';

SELECT* fromt_category c INNERJOINt_product p ONc.cid = p.cno

• 内连接查询的特点

内连接查询的特点

主表和从表中的数据都是满足连接条件则能够查询出来，不满足连接条件则不会查询出来

9.4、外连接查询

我们发现内连接查询出来的是满足连接条件的公共部分， 如果要保证查询出某张表的全部数据情况下进行连接查询. 那么就要使用外连接查询了. 外连接分为左外连接和右外连接

• 左外连接查询

左外连接查询

概念

以join左边的表为主表,展示主表的所有数据,根据条件查询连接右边表的数据,若满足条件则展示,若不满足则以null显示。可以理解为：在内连接的基础上保证左边表的数据全部显示

语法

select字段 froma left[outer] joinb on条件

练习

查询所有类别下的商品信息，就算该类别下没有商品也需要将该类别的信息展示出来

SELECT* FROMt_category c LEFTOUTERJOINt_product p ONc.cid = p.cno

• 右外连接查询

右外连接查询

概念

以join右边的表为主表,展示右边表的所有数据,根据条件查询join左边表的数据,若满足则展示,若不满足则以null显示。可以理解为：在内连接的基础上保证右边表的数据全部显示

语法

select字段 froma right[outer] joinb on条件

练习

查询所有商品所对应的类别信息

SELECT* FROMt_category c RIGHTOUTERJOINt_product p ONc.cid = p.cno

9.5、union联合查询实现全外连接查询

首先要明确，联合查询不是多表连接查询的一种方式。联合查询是将多条查询语句的查询结果合并成一个结果并去掉重复数据。

全外连接查询的意思就是将左表和右表的数据都查询出来，然后按照连接条件连接

• union的语法

union的语法

• 练习

练习

SELECT* FROMt_category c LEFTOUTERJOINt_product p ONc.cid = p.cno

union

SELECT* FROMt_category c RIGHTOUTERJOINt_product p ONc.cid = p.cno

9.6、自连接查询

自连接查询是一种特殊的多表连接查询，因为两个关联查询的表是同一张表，通过取别名的方式来虚拟成两张表，然后进行两张表的连接查询

• 准备工作

准备工作

CREATETABLEemp (

idINTPRIMARY KEY, -- 员工id

ename VARCHAR(50), -- 员工姓名

mgr INT, -- 上级领导

joindate DATE, -- 入职日期

salary DECIMAL(7,2) -- 工资

);

-- 添加员工

INSERTINTOemp(id,ename,mgr,joindate,salary) VALUES

(1001,'孙悟空',1004,'2000-12-17','8000.00'),

(1002,'卢俊义',1006,'2001-02-20','16000.00'),

(1003,'林冲',1006,'2001-02-22','12500.00'),

(1004,'唐僧',1009,'2001-04-02','29750.00'),

(1005,'李逵',1006,'2001-09-28','12500.00'),

(1006,'宋江',1009,'2001-05-01','28500.00'),

(1007,'刘备',1009,'2001-09-01','24500.00'),

(1008,'猪八戒',1004,'2007-04-19','30000.00'),

(1009,'罗贯中',NULL,'2001-11-17','50000.00'),

(1010,'吴用',1006,'2001-09-08','15000.00'),

(1011,'沙僧',1004,'2007-05-23','11000.00'),

(1012,'李逵',1006,'2001-12-03','9500.00'),

(1013,'小白龙',1004,'2001-12-03','30000.00'),

(1014,'关羽',1007,'2002-01-23','13000.00');

#查询孙悟空的上级

SELECTemployee.*,manager.ename mgrname FROMemp employee,emp manager whereemployee.mgr=manager.id ANDemployee.ename='孙悟空'

• 自连接查询练习

自连接查询练习

查询员工的编号，姓名，薪资和他领导的编号，姓名，薪资

#这些数据全部在员工表中

#把t_employee表，即当做员工表，又当做领导表

#领导表是虚拟的概念，我们可以通过取别名的方式虚拟

SELECT employee.id "员工的编号",emp.ename "员工的姓名",emp.salary "员工的薪资",

manager.id "领导的编号",manager.ename "领导的姓名",manager.salary "领导的薪资"

FROM emp employee INNER JOIN emp manager

#emp employee：employee.，表示的是员工表的

#emp manager：如果用manager.，表示的是领导表的

ON employee.mgr = manager.id # 员工的mgr指向上级的id

#表的别名不要加""，给列取别名，可以用""，列的别名不使用""也可以，但是要避免包含空格等特殊符号。

第十章 子查询

如果一个查询语句嵌套在另一个查询语句里面，那么这个查询语句就称之为子查询，根据位置不同，分为：where型，from型，exists型。注意：不管子查询在哪里，子查询必须使用括起来。

10.1、where型

①子查询是单值结果(单行单列)，那么可以对其使用（=，>等比较运算符）

# 查询价格最高的商品信息

select* fromt_product whereprice = (selectmax(price) fromt_product)

②子查询是多值结果，那么可对其使用（【not】in(子查询结果)，或 >all(子查询结果)，或>=all(子查询结果)，<all(子查询结果)，<=all（子查询结果)，或 >any(子查询结果)，或>=any(子查询结果)，<any(子查询结果)，<=any（子查询结果)）

# 查询价格最高的商品信息

SELECT* FROMt_product WHEREprice >=ALL(SELECTprice FROMt_product)

select* fromt_product orderbyprice desclimit0,1

10.2、from型

子查询的结果是多行多列的结果，类似于一张表格。

必须给子查询取别名，即临时表名，表的别名不要加“”和空格。

-- 思路一: 使用连接查询

-- 使用外连接，查询出分类表的所有数据

SELECTtc.cname,COUNT(tp.pid) FROMt_category tc LEFTJOINt_product tp ONtp.cno = tc.cid GROUPBYtc.cname

-- 思路二: 使用子查询

-- 第一步:对t_product根据cno进行分组查询，统计每个分类的商品数量

SELECTcno,COUNT(pid) FROMt_product GROUPBYcno

-- 第二步: 用t_category表去连接第一步查询出来的结果，进行连接查询,此时要求查询出所有的分类

SELECTtc.cname,IFNULL(tn.total,0) '总数量'FROMt_category tc LEFTJOIN(SELECTcno,COUNT(pid) total FROMt_product GROUPBYcno) tn ONtn.cno=tc.cid

10.3、exists型# 查询那些有商品的分类

SELECTcid,cname FROMt_category tc WHEREEXISTS(SELECT* FROMt_product tp WHEREtp.cno = tc.cid);

---END---