共计 20390 个字符，预计需要花费 51 分钟才能阅读完成。
第一章 SQL 的介绍
1.1、什么是 sql
- SQL：Structure Query Language。（结构化查询语言）, 通过 sql 操作数据库(操作数据库, 操作表, 操作数据)
- SQL 被美国国家标准局（ANSI）确定为关系型数据库语言的美国规范，起初被国际化规范组织（ISO）驳回为关系数据库语言的国际标准
- 各数据库厂商 (MySql,oracle,sql server) 都反对 ISO 的 SQL 规范。
- 各数据库厂商在规范的根底上做了本人的扩大。各个数据库本人特定的语法
1.2、sql 的分类
- Data Definition Language (DDL 数据定义语言) 如：操作数据库，操作表
- Data Manipulation Language(DML 数据操纵语言)，如：对表中的记录操作增删改
- Data Query Language(DQL 数据查询语言)，如：对表中数据的查问操作
- Data Control Language(DCL 数据管制语言)，如：对用户权限的设置
1.3、MySQL 的语法标准和要求
（1）mysql 的 sql 语法不辨别大小写
MySQL 的关键字和函数名等不辨别大小写，然而对于数据值是否辨别大小写，和字符集与校对规定无关。
ci（大小写不敏感），cs（大小写敏感），_bin（二元，即比拟是基于字符编码的值而与 language 无关，辨别大小写）
（2）命名时：尽量应用 26 个英文字母大小写，数字 0 -9，下划线，不要应用其余符号 user_id
（3）倡议不要应用 mysql 的关键字等来作为表名、字段名等，如果不小心应用，请在 SQL 语句中 应用 `（飘号）引起来
（4）数据库和表名、字段名等对象名两头不要蕴含空格
（5）同一个 mysql 软件中，数据库不能同名，同一个库中，表不能重名，同一个表中，字段不能重名
（6）标点符号：
- 必须成对
- 必须英文状态下半角输出形式
- 字符串和日期类型能够应用单引号’’
- 列的别名能够应用双引号 ””，给表名取别名不要应用双引号。取别名时 as 能够省略
- 如果列的别名没有蕴含空格，能够省略双引号，如果有空格双引号不能省略。
（7）SQL 脚本中如何加正文
- 单行正文：# 正文内容
- 单行正文：–空格正文内容 其中–前面的空格必须有
- 多行正文：/ 正文内容 /
# 以下两句是一样的，不辨别大小写
show databases;
SHOW DATABASES;
#创建表格
#create table student info(...); #表名谬误，因为表名有空格
create table student_info(...);
#其中 name 应用 `` 飘号，因为 name 和零碎关键字或零碎函数名等预约义标识符重名了。CREATE TABLE t_stu(
id INT,
`name` VARCHAR(20)
);
select id as "编号", `name` as "姓名" from t_stu; #起别名时，as 都能够省略
select id as 编号, `name` as 姓名 from t_stu; #如果字段别名中没有空格，那么能够省略 ""select id as 编 号, `name` as 姓 名 from t_stu; #谬误，如果字段别名中有空格，那么不能省略""第二章 -DDL 操作数据库
2.1、创立数据库(把握)
- 语法
create database 数据库名 [character set 字符集][collate 校对规定] 注: []意思是可选的意思字符集(charset)：是一套符号和编码。
- 练习
创立一个 day01 的数据库（默认字符集)
create database day01;创立一个 day01_2 的数据库, 指定字符集为 gbk(理解)
create database day01_2 character set gbk;2.2、查看所有的数据库
- 查看所有的数据库
语法
show databases; - 查看数据库的定义构造【理解】
语法
show create database 数据库名;查看 day01 这个数据库的定义
show create database day01; 2.3、删除数据库
语法
drop database 数据库名;删除 day01_2 数据库
drop database day01_2;2.4、批改数据库【理解】
语法
alter database 数据库名 character set 字符集;批改 day01 这个数据库的字符集(gbk)
alter database day01 character set gbk;留神：
- 是 utf8，不是 utf-8
- 不是批改数据库名
2.5、其余操作
切换数据库, 选定哪一个数据库
use 数据库名; // 留神: 在创立表之前肯定要指定数据库. use 数据库名练习: 应用 day01
use day01;查看正在应用的数据库
select database();第三章 -DDL 操作表
3.1、创立表
-
语法
create table 表名(列名 类型 [束缚], 列名 类型 [束缚] ... ); - 类型
数值类型
整型系列：xxxInt
- int(M)，必须和 unsigned zerofill 一起应用才有意义
浮点型系列：float,double（或 real)
-
double(M,D)：示意最长为 M 位，其中小数点后 D 位
例如：double(5,2)示意的数据范畴[-999.99,999.99]，如果超过这个范畴会报错。定点型系列：decimal（底层实际上是应用字符串进行存储）
- decimal(M,D)：示意最长为 M 位，其中小数点后 D 位
位类型：bit
- 字节范畴是：1-8，值范畴是：bit(1)~bit(64)，默认 bit(1)
用来存储二进制数。对于位字段，间接应用 select 命令将不会看到后果。能够应用 bit()或 hex()函数进行读取。插入 bit 类型字段时，应用 bit()函数转为二进制值再插入，因为二进制码是“01”。
日期工夫类型
日期工夫类型：year, date, datetime, timestamp
留神一下每一种日期工夫的示意范畴
timestamp 和 datetime 的区别：
- timestamp 范畴比拟小
-
timestamp 和时区无关
- 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)，必须指定宽度
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, 主动的增长进行填充进去, 防止反复了.
留神:
- 先设置了 primary key 再能设置 auto_increment
- 只有当设置了 auto_increment 才能够插入 null , 否则插入 null 会报错
id 列:
- 给 id 设置为 int 类型, 增加主键束缚, 主动增长
- 或者给 id 设置为字符串类型, 增加主键束缚, 不能设置主动增长
练习
创立一张学生表(含有 id 字段, 姓名字段不能反复, 性别字段不能为空默认值为男. id 为主键主动增长)
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键自增长
NAME VARCHAR(30) UNIQUE, -- 惟一束缚
gender CHAR(1) NOT NULL DEFAULT '男'
);3.2、查看表【理解】
查看所有的表
show tables;查看表的定义构造
语法
desc 表名；练习: 查看 student 表的定义构造
desc student;3.3、批改表【把握，然而不要记忆】
语法
- 减少一列
alter table【数据库名.]表名称 add【column】字段名 数据类型;
alter table【数据库名.]表名称 add【column】字段名 数据类型 first;
alter table【数据库名.]表名称 add【column】字段名 数据类型 after 另一个字段;- 批改列的类型束缚:alter table 表名 modify 字段 类型 束缚；
- 批改列的名称，类型，束缚: alter table 表名 change 旧列 新列 类型 束缚;
- 删除一列: alter table 表名 drop 列名;
- 批改表名 : rename table 旧表名 to 新表名;
练习
给学生表减少一个 grade 字段, 类型为 varchar(20), 不能为空
ALTER TABLE student ADD grade VARCHAR(20) NOT NULL;给学生表的 gender 字段改成 int 类型，不能为空，默认值为 1
alter table student modify gender varchar(20);给学生表的 grade 字段批改成 class 字段
ALTER TABLE student CHANGE grade class VARCHAR(20) NOT NULL;把 class 字段删除
ALTER TABLE student DROP class;把学生表批改成老师表(理解)
RENAME TABLE student TO teacher;3.4、删除表【把握】
语法
drop table 表名；把 teacher 表删除
drop table teacher;第四章 -DML 操作表记录 - 增删改【重点】
筹备工作: 创立一张商品表(商品 id, 商品名称, 商品价格, 商品数量.)
create table product(
pid int primary key auto_increment,
pname varchar(40),
price double,
num int
);4.1、插入记录
语法
- 形式一: 插入指定列, 如果没有把这个列进行列出来, 以 null 进行主动赋值了.
eg: 只想插入 pname, price , insert into t_product(pname, price) values(‘mac’,18000);
insert into 表名(列, 列..) values(值, 值..);留神: 如果没有插入了列设置了非空束缚, 会报错的
- 形式二: 插入所有的列, 如果哪列不想插入值, 则须要赋值为 null
insert into 表名 values(值, 值 ….);
eg:
insert into product values(null,'苹果电脑',18000.0,10);
insert into product values(null,'华为 5G 手机',30000,20);
insert into product values(null,'小米手机',1800,30);
insert into product values(null,'iPhonex',8000,10);
insert into product values(null,'iPhone7',6000,200);
insert into product values(null,'iPhone6s',4000,1000);
insert into product values(null,'iPhone6',3500,100);
insert into product values(null,'iPhone5s',3000,100);
insert into product values(null,'方便面',4.5,1000);
insert into product values(null,'咖啡',11,200);
insert into product values(null,'矿泉水',3,500);4.2、更新记录
- 语法
update 表名 set 列 = 值, 列 = 值 [where 条件]- 练习
将所有商品的价格批改为 5000 元
update product set price = 5000;将商品名是苹果电脑的价格批改为 18000 元
UPDATE product set price = 18000 WHERE pname = '苹果电脑';将商品名是苹果电脑的价格批改为 17000, 数量批改为 5
UPDATE product set price = 17000,num = 5 WHERE pname = '苹果电脑';将商品名是方便面的商品的价格在原有根底上减少 2 元
UPDATE product set price = price+2 WHERE pname = '方便面';4.3、删除记录
- delete
依据条件，一条一条数据进行删除
语法
delete from 表名 [where 条件] 留神: 删除数据用 delete, 不必 truncate类型
删除表中名称为’苹果电脑’的记录
delete from product where pname = '苹果电脑';删除价格小于 5001 的商品记录
delete from product where price < 5001;删除表中的所有记录(要删除个别不倡议应用 delete 语句，delete 语句是一行一行执行，速度过慢)
delete from product;-
truncate
把表间接 DROP 掉，而后再创立一个同样的新表。删除的数据不能找回。执行速度比 DELETE 快truncate table 表; -
工作中删除数据
- 物理删除: 真正的删除了, 数据不在, 应用 delete 就属于物理删除
- 逻辑删除: 没有真正的删除, 数据还在. 搞一个标记, 其实逻辑删除是更新 eg: state 1 启用 0 禁用
第五章 -DQL 操作表记录 - 查问【重点】
5.1、根本查问语法
select 要查问的字段名 from 表名 [where 条件] 5.2、简略查问
查问所有行和所有列的记录
-
语法
select * form 表查问商品表外面的所有的列
select * from product;
查问某张表特定列的记录
-
语法
select 列名, 列名, 列名... from 表查问商品名字和价格
select pname, price from product;去重查问 distinct
-
语法
SELECT DISTINCT 字段名 FROM 表名; // 要数据截然不同能力去重去重查问商品的名字
SELECT DISTINCT pname,price FROM product留神点: 去重针对某列, distinct 后面不能先呈现列名
别名查问
-
语法
select 列名 as 别名 , 列名 from 表 // 列别名 as 能够不写 select 别名.* from 表 as 别名 // 表别名(多表查问, 今天会具体讲)查问商品信息，应用别名
SELECT pid ,pname AS '商品名',price AS '商品价格',num AS '商品库存' FROM product运算查问(+,-,*,/,% 等)
把商品名，和商品价格 +10 查问进去: 咱们既能够将某个字段加上一个固定值，又能够对多个字段进行运算查问
select pname ,price+10 as 'price' from product;
select name,chinese+math+english as total from student留神
- 运算查问字段, 字段之间是能够的
- 字符串等类型能够做运算查问，但后果没有意义
5.3、条件查问(很重要)
-
语法
select ... from 表 where 条件 // 取出表中的每条数据，满足条件的记录就返回，不满足条件的记录不返回运算符
1、比拟运算符
大于：>
小于：<
大于等于：>=
小于等于：<=
等于：= 不能用于 null 判断
不等于：!= 或 <>
平安等于: <=> 能够用于 null 值判断2、逻辑运算符（倡议用单词，可读性来说）
逻辑与：&& 或 and
逻辑或：|| 或 or
逻辑非：! 或 not
逻辑异或：^ 或 xor3、范畴
区间范畴：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 * from product where price > 3000;查问 pid= 1 的商品
select * from product where pid = 1;查问 pid<>1 的商品（！=）
select * from product where pid <> 1;查问价格在 3000 到 6000 之间的商品
select * from product where price between 3000 and 6000;查问 pid 在 1，5，7，15 范畴内的商品
select * from product where id = 1;
select * from product where id = 5;
select * from product where id = 7;
select * from product where id = 15;
select * from product where id in (1,5,7,15);查问商品名以 iPho 结尾的商品(iPhone 系列)
select * from product where pname like 'iPho%';查问商品价格大于 3000 并且数量大于 20 的商品 (条件 and 条件 and…)
select * from product where price > 3000 and num > 20;查问 id= 1 或者价格小于 3000 的商品
select * from product where pid = 1 or price < 3000;5.4、排序查问
排序是写在查问的前面，代表把数据查问进去之后再排序
-
环境的筹备
# 创立学生表(有 sid, 学生姓名, 学生性别, 学生年龄, 分数列, 其中 sid 为主键主动增长) CREATE TABLE student( sid INT PRIMARY KEY auto_increment, sname VARCHAR(40), sex VARCHAR(10), age INT, score DOUBLE ); INSERT INTO student VALUES(null,'zs','男',18,98.5); INSERT INTO student VALUES(null,'ls','女',18,96.5); INSERT INTO student VALUES(null,'ww','男',15,50.5); INSERT INTO student VALUES(null,'zl','女',20,98.5); INSERT INTO student VALUES(null,'tq','男',18,60.5); INSERT INTO student VALUES(null,'wb','男',38,98.5); INSERT INTO student VALUES(null,'小丽','男',18,100); INSERT INTO student VALUES(null,'小红','女',28,28); INSERT INTO student VALUES(null,'小强','男',21,95); - 单列排序
语法: 只按某一个字段进行排序，单列排序
SELECT 字段名 FROM 表名 [WHERE 条件] ORDER BY 字段名 [ASC|DESC]; //ASC: 升序，默认值; DESC: 降序案例: 以分数降序查问所有的学生
SELECT * FROM student ORDER BY score DESC- 组合排序
语法: 同时对多个字段进行排序，如果第 1 个字段相等，则按第 2 个字段排序，顺次类推
SELECT 字段名 FROM 表名 WHERE 字段 = 值 ORDER BY 字段名 1 [ASC|DESC], 字段名 2 [ASC|DESC];练习: 以分数降序查问所有的学生, 如果分数统一, 再以 age 降序
SELECT * FROM student ORDER BY score DESC, age DESC5.5、聚合函数
聚合函数用于统计，通常会和分组查问一起应用，用于统计每组的数据
- 聚合函数列表
语法
SELECT 聚合函数(列名) FROM 表名 [where 条件];案例
-- 求出学生表外面的最高分数
SELECT MAX(score) FROM student
-- 求出学生表外面的最低分数
SELECT MIN(score) FROM student
-- 求出学生表外面的分数的总和(疏忽 null 值)
SELECT SUM(score) FROM student
-- 求出学生表外面的平均分
SELECT AVG(score) FROM student
-- 求出学生表外面的平均分（缺考了当成 0 分解决）SELECT AVG(IFNULL(score,0)) FROM student
-- 统计学生的总人数 (疏忽 null)
SELECT COUNT(sid) FROM student
SELECT COUNT(*) FROM student留神: 聚合函数会疏忽空值 NULL
咱们发现对于 NULL 的记录不会统计，倡议如果统计个数则不要应用有可能为 null 的列，但如果须要把 NULL 也统计进去呢？咱们能够通过 IFNULL(列名，默认值) 函数来解决这个问题. 如果列不为空，返回这列的值。如果为 NULL，则返回默认值。
-- 求出学生表外面的平均分（缺考了当成 0 分解决）SELECT AVG(IFNULL(score,0)) FROM student;5.6、分组查问
GROUP BY 将分组字段后果中雷同内容作为一组，并且返回每组的第一条数据，所以独自分组没什么用途。分组的目标就是为了统计，个别分组会跟聚合函数一起应用
- 分组
语法
SELECT 字段 1, 字段 2... FROM 表名 [where 条件] GROUP BY 列 [HAVING 条件];案例
-- 依据性别分组, 统计每一组学生的总人数
SELECT sex '性别',COUNT(sid) '总人数' FROM student GROUP BY sex
-- 依据性别分组，统计每组学生的平均分
SELECT sex '性别',AVG(score) '平均分' FROM student GROUP BY sex
-- 依据性别分组，统计每组学生的总分
SELECT sex '性别',SUM(score) '总分' FROM student GROUP BY sex- 分组后筛选 having
分组后的条件，不能写在 where 之后，where 关键字要写在 group by 之前
依据性别分组, 统计每一组学生的总人数 > 5 的(分组后筛选)
SELECT sex, count(*) FROM student GROUP BY sex HAVING count(sid) > 5依据性别分组，只统计年龄大于等于 18 的，并且要求组里的人数大于 4
SELECT sex '性别',COUNT(sid) '总人数' FROM student WHERE age >= 18 GROUP BY sex HAVING COUNT(sid) > 4- where 和 having 的区别【面试】
where 子句作用
- 1)对查问后果进行分组前，将不合乎 where 条件的行去掉，即在分组之前过滤数据，即先过滤再分组。
- 2)where 前面不能够应用聚合函数
having 字句作用
- 1) having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤。
- 2) having 前面能够应用聚合函数
5.7、分页查问
-
语法
select ... from .... limit a ,b
-
案例
-- 分页查问 -- limit 关键字是应用在查问的后边，如果有排序的话则应用在排序的后边 -- limit 的语法: limit offset,length 其中 offset 示意跳过多少条数据，length 示意查问多少条数据 SELECT * FROM product LIMIT 0,3 -- 查问 product 表中的前三条数据(0 示意跳过 0 条，3 示意查问 3 条) SELECT * FROM product LIMIT 3,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、创立外键束缚的语法
-
在建表时指定外键束缚
create table [数据名.]从表名( 字段名 1 数据类型 primary key , 字段名 2 数据类型 , ...., [constraint 外键束缚名] foreign key (从表字段) references 主表名(主表字段) [on update 外键束缚等级][on delete 外键束缚等级] #外键只能在所有字段列表前面独自指定 #如果要本人命名外键束缚名，倡议 主表名_从表名_关联字段名_fk ); -
在建表后指定外键束缚
alter table 从表名称 add [constraint 外键束缚名] foreign key (从表字段名) references 主表名(主表被参照字段名) [on update xx][on delete xx];7.4、删除外键束缚的语法
ALTER TABLE 表名称 DROP FOREIGN KEY 外键束缚名; #查看束缚名 SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称'; #删除外键束缚不会删除对应的索引，如果须要删除索引，须要用 ALTER TABLE 表名称 DROP INDEX 索引名; #查看索引名 show index from 表名称;7.5、外键束缚的要求
- 在从表上建设外键，而且主表要先存在。
- 一个表能够建设多个外键束缚
- 通常状况下，从表的外键列肯定要指向主表的主键列
- 从表的外键列与主表被参照的列名字能够不雷同，然而数据类型必须一样
7.6、外键束缚等级
- Cascade 形式：在主表上 update/delete 记录时，同步 update/delete 掉从表的匹配记录
- Set null 形式：在主表上 update/delete 记录时，将从表上匹配记录的列设为 null，然而要留神子表的外键列不能为 not null
- No action 形式：如果子表中有匹配的记录, 则不容许对父表对应候选键进行 update/delete 操作
- Restrict 形式：同 no action, 都是立刻查看外键束缚
- Set default 形式（在可视化工具 SQLyog 中可能显示空白）：父表有变更时, 子表将外键列设置成一个默认的值，但 Innodb 不能辨认
- 如果没有指定等级，就相当于 Restrict 形式
7.7、外键束缚练习
-- 部门表
create table dept(
id int primary key,
dept_name varchar(50),
dept_location varchar(50)
);
-- 员工表
CREATE TABLE emp(
eid int primary key,
name varchar(50) not null,
sex varchar(10),
dept_id int
);
-- 给员工表表的 dept_id 增加外键指向部门表的主键
alter table emp add foreign key(dept_id) references dept(id)第八章 多表间关系
8.1、一对多关系
- 概念
一对多的关系是指: 主表的一行数据能够同时对应从表的多行数据，反过来就是从表的多行数据指向主表的同一行数据。
- 利用场景
分类表和商品表、班级表和学生表、用户表和订单表等等
- 建表准则
将一的一方作为主表，多的一方作为从表，在从表中指定一个字段作为外键，指向主表的主键
-
建表语句练习
-- 创立分类表 CREATE TABLE category( cid INT PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(50) ); -- 创立商品表 CREATE TABLE product( pid INT PRIMARY KEY AUTO_INCREMENT, pname VARCHAR(50), price DOUBLE, cid INT ) -- 给商品表增加一个外键 alter table product add foreign key(cid) references category(cid)8.2、多对多关系
- 概念
两张表都是多的一方，A 表的一行数据能够同时对应 B 表的多行数据，反之 B 表的一行数据也能够同时对应 A 表的多行数据
- 利用场景
订单表和商品表、学生表和课程表等等
- 建表准则
因为两张表都是多的一方，所以在两张表中都无奈创立外键，所以须要新创建一张两头表，在两头表中定义两个字段，这俩字段别离作为外键指向两张表各自的主键
-
建表语句练习
-- 创立学生表 CREATE TABLE student( sid INT PRIMARY KEY AUTO_INCREMENT, sname VARCHAR(50) ); -- 创立课程表 CREATE TABLE course( cid INT PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(20) ); -- 创立两头表 CREATE TABLE s_c_table( sno INT, cno INT ); -- 给 sno 字段增加外键指向 student 表的 sid 主键 ALTER TABLE s_c_table ADD CONSTRAINT fkey01 FOREIGN KEY(sno) REFERENCES student(sid); -- 给 cno 字段增加外键指向 course 表的 cid 主键 ALTER TABLE s_c_table ADD CONSTRAINT fkey03 FOREIGN KEY(cno) REFERENCES course(cid);8.3、一对一关系(理解)
- 第一种一对一关系
咱们之前学习过一对多关系，在一对多关系中主表的一行数据能够对应从表的多行数据，反之从表的一行数据则只能对应主表的一行数据。这种一行数据对应一行数据的关系，咱们能够将其看作一对一关系
- 第二种一对一关系
A 表中的一行数据对应 B 表中的一行数据，反之 B 表中的一行数据也对应 A 表中的一行数据，此时咱们能够将 A 表当做主表 B 表当做从表，或者是将 B 表当做主表 A 表当做从表
- 建表准则
在从表中指定一个字段创立外键并指向主表的主键，而后给从表的外键字段增加惟一束缚
第九章 多表关联查问
多表关联查问是应用一条 SQL 语句，将关联的多张表的数据查问进去
9.1、环境筹备
-- 创立一张分类表(类别 id, 类别名称. 备注: 类别 id 为主键并且主动增长)
CREATE TABLE t_category(
cid INT PRIMARY KEY auto_increment,
cname VARCHAR(40)
);
INSERT INTO t_category values(null,'手机数码');
INSERT INTO t_category values(null,'食物');
INSERT INTO t_category values(null,'鞋靴箱包');
-- 创立一张商品表(商品 id, 商品名称, 商品价格, 商品数量, 类别. 备注: 商品 id 为主键并且主动增长)
CREATE TABLE t_product(
pid INT PRIMARY KEY auto_increment,
pname VARCHAR(40),
price DOUBLE,
num INT,
cno INT
);
insert into t_product values(null,'苹果电脑',18000,10,1);
insert into t_product values(null,'iPhone8s',5500,100,1);
insert into t_product values(null,'iPhone7',5000,100,1);
insert into t_product values(null,'iPhone6s',4500,1000,1);
insert into t_product values(null,'iPhone6',3800,200,1);
insert into t_product values(null,'iPhone5s',2000,10,1);
insert into t_product values(null,'iPhone4s',18000,1,1);
insert into t_product values(null,'方便面',4.5,1000,2);
insert into t_product values(null,'咖啡',10,100,2);
insert into t_product values(null,'矿泉水',2.5,100,2);
insert into t_product values(null,'法拉利',3000000,50,null);
-- 给 商品表增加外键
ALTER TABLE t_product ADD FOREIGN KEY(cno) REFERENCES t_category(cid);9.2、穿插查问【理解】
穿插查问其实就是将多张表的数据没有条件地连贯在一起进行展现
-
语法
select a. 列,a. 列,b. 列,b. 列 from a,b ; select a.*,b.* from a,b ; -- 或者 select * from a,b; - 练习
应用穿插查问类别和商品
select * from t_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 [字段, 字段, 字段] from a,b where 连贯条件 (b 表外面的外键 = a 表外面的主键) - 显式内连贯查问
显式内连贯查问外面是有 inner join 关键字
select [字段, 字段, 字段] from a [inner] join b on 连贯条件 [where 其它条件]- 内连贯查问练习
查问所有类别下的商品信息, 如果该类别下没有商品则不展现
-- 1 隐式内连贯形式
select *from t_category c, t_product p WHERE c.cid = p.cno;
-- 2 显示内连贯形式
-- 查问手机数码这个分类下的所有商品的信息以及分类信息
SELECT * FROM t_product tp INNER JOIN t_category tc ON tp.cno = tc.cid WHERE tc.cname = '手机数码';
SELECT * from t_category c INNER JOIN t_product p ON c.cid = p.cno- 内连贯查问的特点
主表和从表中的数据都是满足连贯条件则可能查问进去，不满足连贯条件则不会查问进去
9.4、外连贯查问
咱们发现内连贯查问进去的是满足连贯条件的公共局部，如果要保障查问出某张表的全副数据状况下进行连贯查问. 那么就要应用外连贯查问了. 外连贯分为左外连贯和右外连贯
- 左外连贯查问
概念
以 join 右边的表为主表, 展现主表的所有数据, 依据条件查问连贯左边表的数据, 若满足条件则展现, 若不满足则以 null 显示。能够了解为：在内连贯的根底上保障右边表的数据全副显示
语法
select 字段 from a left [outer] join b on 条件练习
查问所有类别下的商品信息，就算该类别下没有商品也须要将该类别的信息展现进去
SELECT * FROM t_category c LEFT OUTER JOIN t_product p ON c.cid = p.cno- 右外连贯查问
概念
以 join 左边的表为主表, 展现左边表的所有数据, 依据条件查问 join 右边表的数据, 若满足则展现, 若不满足则以 null 显示。能够了解为：在内连贯的根底上保障左边表的数据全副显示
语法
select 字段 from a right [outer] join b on 条件练习
查问所有商品所对应的类别信息
SELECT * FROM t_category c RIGHT OUTER JOIN t_product p ON c.cid = p.cno9.5、union 联结查问实现全外连贯查问
首先要明确，联结查问不是多表连贯查问的一种形式。联结查问是将多条查问语句的查问后果合并成一个后果并去掉反复数据。
全外连贯查问的意思就是将左表和右表的数据都查问进去，而后依照连贯条件连贯
-
union 的语法
查问语句 1 union 查问语句 2 union 查问语句 3 ... -
练习
# 用左外的 A union 右外的 B SELECT * FROM t_category c LEFT OUTER JOIN t_product p ON c.cid = p.cno union SELECT * FROM t_category c RIGHT OUTER JOIN t_product p ON c.cid = p.cno9.6、自连贯查问
自连贯查问是一种非凡的多表连贯查问，因为两个关联查问的表是同一张表，通过取别名的形式来虚构成两张表，而后进行两张表的连贯查问
-
筹备工作
-- 员工表 CREATE TABLE emp ( id INT PRIMARY KEY, -- 员工 id ename VARCHAR(50), -- 员工姓名 mgr INT , -- 上级领导 joindate DATE, -- 入职日期 salary DECIMAL(7,2) -- 工资 ); -- 增加员工 INSERT INTO emp(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'); #查问孙悟空的下级 SELECT employee.*,manager.ename mgrname FROM emp employee,emp manager where employee.mgr=manager.id AND employee.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 * from t_product where price = (select max(price) from t_product)②子查问是多值后果，那么可对其应用（【not】in(子查问后果)，或 >all(子查问后果)，或 >=all(子查问后果)，<all(子查问后果)，<=all（子查问后果)，或 >any(子查问后果)，或 >=any(子查问后果)，<any(子查问后果)，<=any（子查问后果)）
# 查问价格最高的商品信息
SELECT * FROM t_product WHERE price >=ALL(SELECT price FROM t_product)
select * from t_product order by price desc limit 0,110.2、from 型
子查问的后果是多行多列的后果，相似于一张表格。
必须给子查问取别名，即长期表名，表的别名不要加“”和空格。
-- 思路一: 应用连贯查问
-- 应用外连贯，查问出分类表的所有数据
SELECT tc.cname,COUNT(tp.pid) FROM t_category tc LEFT JOIN t_product tp ON tp.cno = tc.cid GROUP BY tc.cname
-- 思路二: 应用子查问
-- 第一步: 对 t_product 依据 cno 进行分组查问，统计每个分类的商品数量
SELECT cno,COUNT(pid) FROM t_product GROUP BY cno
-- 第二步: 用 t_category 表去连贯第一步查问进去的后果，进行连贯查问, 此时要求查问出所有的分类
SELECT tc.cname,IFNULL(tn.total,0) '总数量' FROM t_category tc LEFT JOIN (SELECT cno,COUNT(pid) total FROM t_product GROUP BY cno) tn ON tn.cno=tc.cid10.3、exists 型
# 查问那些有商品的分类
SELECT cid,cname FROM t_category tc WHERE EXISTS (SELECT * FROM t_product tp WHERE tp.cno = tc.cid);链接：blog.csdn.net/qq_42076902/article/details/121701974
