SQL语句及其应用(下)(DQL语句之多表查询)

SQL语句及其应用(下)(DQL语句之多表查询)

一, SQL语句的定义:

概述:

全称叫 Structured Query Language, 结构化查询语言, 主要是实现 用户(程序员) 和 数据库软件(例如: MySQL, Oracle)之间交互用的.

分类:

DDL: 数据定义语言, 主要是操作 数据库, 数据表, 字段, 进行: 增删改查(CURD)
涉及到的关键字: create, drop, alter, show
DML: 数据操作语言, 主要是操作 表数据, 进行: 增删改(CDU) -> 统称为 更新语句.
涉及到的关键字: insert, delete, update
DQL: 数据查询语言, 主要是操作 表数据, 进行: 查询操作®
涉及到的关键字: select, from, where
DCL: 数据控制语言, 主要是 创建用户, 设置权限, 隔离级别等.

通用语法:

1. SQL语句可以写一行, 也可以写多行, 最后用 分号 结尾.
2. SQL语句不区分大小写, 为了阅读方便, 建议关键字大写, 其它都小写.
3. 注释写法:
   /* 多行注释的文本 */
   # 单行注释
   – 单行注释

二, 数据类型的定义

概述:

就是用来限定某列值的范围的, 必须是: 整数, 小数, 字符串, 日期…

常用的数据类型:

• 整型: int
• 浮点型: float, double, decimal
• 日期型: datetime
• 字符串型: varchar(长度)

三, 约束的定义

概述;

在数据类型的基础上, 进一步对该列值做 限定.

(常用的)分类:

单表约束:

• primary key 主键约束, 特点: 非空, 唯一, 一般结合 auto_increment(自动增长, 自增)一起使用.`
• not null 非空约束, 即: 该列值不能为null, 但是可以 重复.
• unique 唯一约束, 即: 该列值必须不重复, 但是可以 为空.
• default 默认约束, 等价于Python的 缺省参数.

多表约束:

• foreign key 外键约束

四, DQL语句(数据查寻语言)

DQL: 数据查询语言, 主要是操作 表数据, 进行: 查询操作®
涉及到的关键字: select, from, where

多表查询

(1) 多表查询 -> 多表建表 -> 一对多关系

图解

多表关系定义

概述:

MySQL是一种关系型数据库, 采用 数据表 来存储数据, 且表与表之间是有关系的.
(eg: 一对多, 多对多, 一对一, …)

举例:

• 一对多: 部门表和员工表, 客户表和订单表, 分类表和商品表…
• 多对多: 学生表和选修课表, 订单表和商品表, 学生表和老师表…
• 一对一: 一个人有1个身份证号, 1家公司只有1个注册地址, 1个法人.

建表原则:

• 一对多: 在多的一方新建1列, 充当外键列, 去关联1的一方的主键列.
• 多对多: 新建中间表, 该表至少有3列(自身主键, 剩下两个当外键), 分别去关联多的两方的主键列.
• 一对一: 直接放到一张表中.

结论(记忆):

• 外表的外键列, 不能出现主表的主键列没有的数据.
• 约束是用来保证数据的完整性和安全性的.
• 添加 和 删除外键约束的格式如下:
  • 添加外键约束: alter table 外表名 add [constraint 外键约束名] foreign key(外键列名) references 主表名(主键列名);
  • 删除外键约束: alter table 外表名 drop foreign key 外键约束名;

案例

案例需求

新建部门表(dept, department) 和 员工表(emp), 他们之间是 一对多的关系, 请用外键约束, 完成限定.

实现思路

1. 切库, 查表.
2. 新建部门表.
3. 新建员工表, 指定外键列.
4. 方式1: 建表时, 直接添加外键.
5. 格式: [constraint 外键约束名] foreign key(外键列名) references 主表名(主键列名).
6. 方式2: 建表后, 添加外键. 前提: 表数据之间必须是合法的.
7. 格式: alter table 外表名 add [constraint 外键约束名] foreign key(外键列名) references 主表名(主键列名);
8. 给部门添加数据.
9. 给员工表添加数据.
10. 查看数据表.
11. 删除外键约束.
12. 格式: alter table 外表名 drop foreign key 外键约束名;

# 1. 切库, 查表.
use new_day;
show tables;
# 2. 新建部门表(创建 主表 -> 部门表).
drop table dept;
create table dept(
	id int primary key auto_increment, # 部门id
	name varchar(10)		# 部门名字		
);

# 3.新建员工表, 指定外键列(创建 外表 -> 员工表).
drop table emp;
create table emp(
	id int primary key auto_increment, 	# 员工id
	name varchar(10),		# 员工姓名
	salary int,				# 员工工资
	dept_id int				# 员工所属的部门id
	# 4. 方式1: 建表时, 直接添加外键.
	# 5. 格式: [constraint 外键约束名] foreign key(外键列名) references 主表名(主键列名)
	, constraint fk_dept_emp foreign key(dept_id) references dept(id)
);
# 6. 方式2:建表后, 添加外键约束, 前提: 表数据之间必须是合法的.
# 7. 格式: alter table 外表名 add [constraint 外键约束名] foreign key(外键列名) references 主表名(主键列名);
alter table emp add foreign key(dept_id) references dept(id);

# 8. 给部门添加数据.
insert into dept values(null, '人事部'), (null, '财务部'), (null, '研发部'), (null, '行政部');

# 9. 给员工表添加数据.
insert into emp values(null, '胡歌', 33333, 1);		# 可以
insert into emp values(null, '胡歌', 22222, 2);		# 可以 
insert into emp values(null, '坤哥', 66666, 10);	# 不可以 , 外表的外键列不能出现主表的主键列没有的数据.

# 10. 查看表数据.
select * from dept;
select * from emp;

# 11. 删除外键约束.
# 12. 格式: alter table 外表名 drop foreign key 外键约束名;
alter table emp drop foreign key fk_dept_emp;

(2)多表查询 -> 准备数据

格式

# 格式1:
select * from 表名1, 表名2;
# 格式2:
select * from 表名1 join 表名2;

案例

案例需求

多表查询 -> 准备数据

实现思路

1. 创建hero表.
2. 创键kongfu表.
3. 添加表数据.
4. 插入hero数据.
5. 插入kongfu数据.
6. 查看表数据.
7. 格式1: select * from 表名1, 表名2;
8. 格式2: select * from 表名1 join 表名2;
9. 查询结果 = 两张表的笛卡尔积, 即: 表A的总条数 * 表B的总条数, 会产生大量的脏数据, 实际开发一般不用.

# 1. 创建hero表.
create table hero(
	hid	  int primary key auto_increment,	# 英雄id
	hname varchar(255),						# 英雄名
	kongfu_id int							# 功夫id 			
);
# 2. 创建kongfu表.
create table kongfu(
	kid		int primary key auto_increment,	# 功夫id
	kname	varchar(255)					# 功夫名
);
# 3. 添加表数据.
# 4. 插入hero数据.
insert into hero values(1, '鸠摩智', 9), (3, '乔峰', 1), (4, '虚竹', 4), (5, '段誉', 12);
# 5. 插入kongfu数据.
insert into kongfu values(1, '降龙十八掌'), (2, '乾坤大挪移'), (3, '猴子偷桃'), (4, '天山折梅手');
# 6. 查看表数据.
select * from hero;
select * from kongfu;
# 7. 格式1: select * from 表名1, 表名2;
# 8. 格式2: select * from 表名1 join 表名2;
# 9. 查询结果 = 两张表的笛卡尔积, 即: 表A的总条数 * 表B的总条数, 会产生大量的脏数据, 实际开发一般不用.
select * from hero, kongfu;
select * from hero join kongfu;
 

(3)多表查询 -> 内连接(inner join)

内连接的定义:

概述:

多表查询 内连接的查询结果为: 表的交集.

格式:

# 格式1(隐式内连接): select * from 表1, 表2 where 关联条件;
select * from hero as h, kongfu as kf where h.kongfu_id = kf.kid;	# 标准写法.
select * from hero, kongfu where kongfu_id = kid;					# as可以省略, 因为没有重名字段, 数据表.字段名 可以直接写为 字段名

# 格式2(显式内连接)(推荐使用, 效率高): select * from 表1 inner 表2 on 关联条件;		# 细节: inner可以省略不写.
select * from hero as h inner join kongfu as kf on h.kongfu_id = kf,kid;

案例

案例需求

多表查询 -> 内连接(inner join)

实现思路

1. 创建hero表.
2. 创键kongfu表.
3. 添加表数据.
4. 插入hero数据.
5. 插入kongfu数据.
6. 查看表数据.
7. 使用内连接, 进行多表查询
8. 查询结果: 表的交集.
9. 方法1:隐式内连接.
10. 格式1: select * from 表1, 表2 where 关联条件;
11. 方法2: 显式内连接(推荐使用, 效率高).
12. 格式2: select * from 表1 inner join 表2 on 关联条件(细节: inner可以省略不写.);

# 1. 创建hero表.
create table hero(
	hid	  int primary key auto_increment,	# 英雄id
	hname varchar(255),						# 英雄名
	kongfu_id int							# 功夫id 			
);
# 2. 创建kongfu表.
create table kongfu(
	kid		int primary key auto_increment,	# 功夫id
	kname	varchar(255)					# 功夫名
);
# 3. 添加表数据.
# 4. 插入hero数据.
insert into hero values(1, '鸠摩智', 9), (3, '乔峰', 1), (4, '虚竹', 4), (5, '段誉', 12);
# 5. 插入kongfu数据.
insert into kongfu values(1, '降龙十八掌'), (2, '乾坤大挪移'), (3, '猴子偷桃'), (4, '天山折梅手');
# 6. 查看表数据.
select * from hero;
select * from kongfu;
# 7. 使用内连接, 进行多表查询.
# 8. 查询结果, 表的交集.
# 9. 方法1: 隐式内连接.
# 10. 格式1: select * from 表1, 表2 where 关联条件;
select * from hero as h, kongfu as kf where h.kongfu_id = kf.kid;	# 标准写法
select * from hero, kongfu where kongfu_id = kid;					# as可以省略, 因为没有重名字段, 数据库.字段名 可以直接写为 字段名

# 11. 方法2: 显式内连接(推荐使用, 效率高).
# 12. 格式2: select * from 表1 inner join 表2 on 关联条件;		# 细节: inner可以省略不写.
select * from hero as h inner join kongfu as kf on h.kongfu_id = kf.kid;
select * from hero as h join kongfu as kf on h.kongfu_id = kf.kid;
 

(4)多表查询 -> 外连接(outer join)

外连接的定义

概述

• 左外连接: 查询结果 = 左表的全集 + 交集.
• 右外连接: 查询结果 = 右表的全集 + 交集.
• 满外连接(全连接): 查询结果 = 左外连接 + 右外连接 的结果.

格式

# 左外连接格式:
select * from 表1 left outer join 表2 on 关联条件;		# outer可以省略不写.
#右外连接格式:
select * from 表1 right outer join 表2 on 关联条件;		# outer可以省略不写.
# 满外链接(全连接)格式:
# select * from 表1 full outer join 表2 on 关联条件;		# outer可以省略不写.			格式如此, 但是MySQL不支持full outer join写法.
select * from 表1 left outer join 表2 on 关联条件;
union distinct	# 合并, 并去重.
# 细节: distinct 可以省略不写.
# union
union all		# 合并, 不去重.
select * from 表1 right outer join 表2 on 关联条件;

案例

案例需求

多表查询 -> 外连接(outer join)

实现思路

1. 创建hero表.
2. 创键kongfu表.
3. 添加表数据.
4. 插入hero数据.
5. 插入kongfu数据.
6. 查看表数据.
7. 使用外连接, 进行多表查询.
8. 场景1: 左外连接, 查询结果 = 左表的全集 + 交集.
9. 格式: select * from 表1 left outer join 表2 on 关联条件; # outer可以省略不写.
10. 场景2: 右外连接, 查询结果 = 右表的全集 +交集.
11. 格式: select * from 表1 right outer join 表2 on 关联条件; # outer可以省略不写.
12. 场景3: 满外链接(全连接), 查询结果 = 左外连接 + 右外连接 的结果.
13. 用 union 关键字 把 左外连接和右外连接的结果, 合并到一起, 形成 满外连接的效果.
14. 格式:
15. select* from 表1 left outer join 表2 on 关联条件; # outer可以省略不写.
16. union distinct # 合并, 并去重, 细节: distinct可以省略不写.
17. union
18. union all # 合并, 不去重.
19. select * from 表1 right outer join 表2 on 关联条件; # outer可以省略不写.

# 1. 创建hero表.
create table hero(
	hid	  int primary key auto_increment,	# 英雄id
	hname varchar(255),						# 英雄名
	kongfu_id int							# 功夫id 			
);
# 2. 创建kongfu表.
create table kongfu(
	kid		int primary key auto_increment,	# 功夫id
	kname	varchar(255)					# 功夫名
);
# 3. 添加表数据.
# 4. 插入hero数据.
insert into hero values(1, '鸠摩智', 9), (3, '乔峰', 1), (4, '虚竹', 4), (5, '段誉', 12);
# 5. 插入kongfu数据.
insert into kongfu values(1, '降龙十八掌'), (2, '乾坤大挪移'), (3, '猴子偷桃'), (4, '天山折梅手');
# 6. 查看表数据.
select * from hero;
select * from kongfu;
# 7. 使用外连接, 进行多表查询.
# 8. 场景1: 左外连接, 查询结果 = 左表的全集 + 交集.
# 9. 格式: select * from 表1 left outer join 表2 on 关联条件;		# outer可以省略不写.
select * from hero h left outer join kongfu kf on h.kongfu_id = kf.kid;
select = from hero h left join kongfu kf on h,kongfu_id = kf.kid;		# 效果同上, outer可以省略不写.
 
# 10. 场景2: 右外连接, 查询结果 = 右表的全集 +交集.
# 11. 格式: select * from 表1 right outer join 表2 on 关联条件;		# outer可以省略不写.
select * from hero h right outer join kongfu kf on h.kongfu_id = kf.kid;
select = from hero h right join kongfu kf on h,kongfu_id = kf.kid;		# 效果同上, outer可以省略不写.

# 12. 场景3: 满外链接(全连接), 查询结果 = 左外连接 + 右外连接 的结果.
# 13. 用 union 关键字 把 左外连接和右外连接的结果, 合并到一起, 形成 满外连接的效果.
# 14. 格式:
# 15. select* from 表1 left outer join 表2 on 关联条件;		# outer可以省略不写.
# 16. union distinct		# 合并, 并去重, 细节: distinct可以省略不写.
# 17. union
# 18. union all				# 合并, 不去重.
# 19. select * from 表1 right outer join 表2 on 关联条件;	# outer可以省略不写.
select * from hero h left join komhfu kf on h.kongfu_id = kf.kid		# 左外连接
# union distinct		# 合并, 并去重, 细节: distinct可以省略不写.
# union
union all				# 合并, 不去重.
select * from hero h right join komhfu kf on h.kongfu_id = kf.kid		# 右外连接

(5)多表查询 -> 子查询

子查询的定义

概述:

一个SQL语句的查询条件，需要依赖另1个S0L语句的查询结果，这种写法就叫: 子查询.
外表的查询叫：父查询（主查询），里边的查询叫：子查询.

格式:

select * from 表名 where 字段 = (select 字段 from 表名 where ...)

案例

案例需求

查询价格最高的商品的信息，只要商品名，价格，分类1d即可.

实现思路

1. 创建商品表.
2. 添加表数据.
3. 查看表数据.
4. 需求1: 查询所有的商品信息.
5. 需求2: 查看商品名 和 商品价格.
6. 需求3: 查询价格最高的商品的信息，只要商品名，价格，分类1d即可.
7. 分解版:
8. 步骤1: 查找商品最高的 单价.
9. 步骤2: 查找单价最高的 商品信息.
10. 合并版: 子查询.
11. 主查询(父查询), 子查询.
12. 实际开发写法, 连接查询.

# 1. 创建商品表.
create table product
(
	pid 		int primary key auto_increment, # 商品id, 主键
	pname		varchar(20), 	# 商品名
	price		double, 		# 商品单价
	category	varchar(32)		# 商品的分类id
);

# 2. 添加数据表.
INSERT INTO product(pid,pname,price,category_id) VALUES(null,'联想',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(null,'海尔',3000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(null,'雷神',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(null,'杰克琼斯',800,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(null,'真维斯',200, null);
INSERT INTO product(pid,pname,price,category_id) VALUES(null,'花花公子',440,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(null,'劲霸',2000,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(null,'香奈儿',800,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(null,'相宜本草',200, null);
INSERT INTO product(pid,pname,price,category_id) VALUES(null,'面霸',5,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(null,'好想你枣',56,'c004');
INSERT INTO product(pid,pname,price,category_id) VALUES(null,'香飘飘奶茶',1,'c005');
INSERT INTO product(pid,pname,price,category_id) VALUES(null,'海澜之家',1,'c002');

# 3. 查看表数据.
# 4. 需求1: 查询所有的商品信息
select * from product;
select pid, pname, pricem category_id from product;	# 效果同上.

# 5. 需求2: 查看商品名 和 商品价格.
select pname, price from product;

# 6. 需求3: 查询价格最高的商品的信息，只要商品名，价格，分类1d即可.
# 7. 分解版:

# 8. 步骤1: 查找商品最高的 单价.
select max(price) from product;
# 9. 步骤2: 查找单价最高的 商品信息.
select * from product where price = 5000;

# 10. 合并版: 子查询.
# 11. 主查询(父查询),          子查询.
select * from product where price = (select max(price) from product);
# 12. 实际开发写法, 连接查询.
select 
	*
from 
	product p
join
	(select max(price) price from product) t1
on
	p.price = t1.price;

(6)多表查询 -> 自关联查询

图解

自关联查询的定义

概述:

表自己和自己做关联查询, 称之为: 自关联(自连接)查询.

格式:

可以是交叉查询, 内连接, 外连接…

案例 行政区域表 -> 省市区

案例需求

记录省市区的信息

• 复杂的写法: 搞三张表,分别记录省, 市, 区的关系.
• 简单的写法: 用1张表存储, 然后用的时候, 通过 自关联查询 实现即可.
• 字段

自身id	自身名字	父级id
4100000	河南省	0
410100	郑州市	410000
410200	开封市	410000
410300	洛阳市	410000
410700	新乡市	410000
410101	二七区	410100
410102	经开区	410100
410701	红旗区	410700
410702	卫滨区	418700
410721	新乡县	410700

实现思路

1. 查看区域表的信息.
2. 查询河南省的信息.
3. 查看河南省所有的市.
4. 查看新乡市所有的县区.
5. 查看河南省所有的市, 县区信息.
6. 根据你的身份证号前6位, 查询你的家乡.

# 1. 查看区域表的信息.
select * from areas;

# 2, 查询河南省的信息.
select * from areas where title = '河南省';

# 3. 查看河南省所有的市.
select * from areas where pid = 410000;

# 4. 查看新乡县所有的县区.
select * from areas where pid = 410700;

# 5. 查看河南省所有的市,  县区信息.
select
	province.id, province.title, 	# 省的id, 名字
	city.id, city.title,			# 市的id, 名字
	county.id, county.title			# 县区的id, 名字
from 
	areas as county		# 县区
join
	areas as city on county.pid = city.id 		# 市
join
	areas as province on city.pid = province.id		# 省
where
	province.title = '河南省';
# 6. 根据你的省份证号前6位, 查询你的家乡.
select
	province.id, province.title, 		# 省的id, 名字
	city.id, city.title, 				# 市的id, 名字
	county.id, city.title				# 区县的id, 名字
from
	areas as county		# 县区
join
	areas as city on county.pid = city.id		# 市
join
	areas as province on city.pid = province.id		# 省
where
	copunty.id = '142222';

总结

• Q1:多表查询的方式?
  • 多表查询 -> 多表建表 -> 一对多关系
  • 多表查询 -> 准备数据
  • 多表查询 -> 内连接(inner join)
  • 多表查询 -> 外连接(outer join)
  • 多表查询 -> 子查询
  • 多表查询 -> 自关联查询