MySQL数据库应用学习笔记--3.多表链接、子查询、视图、事务控制

最新推荐文章于 2023-07-25 09:15:13 发布

大梦想家a

最新推荐文章于 2023-07-25 09:15:13 发布

阅读量217

点赞数

文章标签：数据库 mysql 学习

本文链接：https://blog.csdn.net/achuxin123/article/details/107820791

版权

MySQL数据库应用

集合操作符
多表连接
- 练习
子查询
- 练习
视图
事务控制

集合操作符

UNION ALL ：合并多个查询结果，可能会包含重复记录
UNION ：合并多个查询的结果，不包含重复记录

集合操作符可以将两个及两个以上查询的结果组合成一个结果集返回。要使用集合操作符，多个查询返回的记录必须具有相同的列数，列的数据类型也必须匹配，但列名可以不同。

多表连接

两个表的内连接

TableName1 INNER JOIN TableName2 ON [...]

示列：
1、检索每个产品的编号、名称以及该产品的类型编号和名称。
2、检索员工编号、姓名以及该员工的工资等级

-- 检索每个产品的编号、名称以及该产品的类型编号和名称。
SELECT product_id,products.name prd_name,products.product_type_id,product_types.name type_name
FROM products INNER JOIN product_types ON products.product_type_id=product_types.product_type_id;
-- 检索员工编号、姓名以及该员工的工资等级
SELECT e.employee_id '员工编号',CONCAT(e.first_name,' ',e.last_name) '姓名',s.salary_grade_id '工资等级'
FROM employees e INNER JOIN salary_grades s ON e.salary>=s.low_salary AND e.salary<=s.high_salary
ORDER BY e.employee_id ASC;

等值连接时，如果关联列的列名相同，则可使用USING关键字

TableName1 INNER JOIN TableName2 USING(columnName)

示列：
1、检索每个产品的编号、名称以及该产品的类型编号和名称。
注：关联列列名前不能加表名或别名进行限制；

-- 检索每个产品的编号、名称以及该产品的类型和名称
SELECT * FROM product_types t INNER JOIN products p USING(product_type_id)

SELECT p.product_id '产品编号',p.name '名称',product_type_id '类型',t.name '类型名'
FROM product_types t INNER JOIN products p USING(product_type_id)

多表内连接
示列：
检索顾客编号、顾客名、该顾客购买的产品名、产品的类型名、所购买产品的数量

# 检索顾客编号、顾客名、该顾客购买的产品名、产品的类型名、所购买产品的数量
SELECT c.customer_id '顾客编号',CONCAT(c.first_name,' ',c.last_name) '顾客名',p.name '产品名',t.name '产品类型名',pu.quantity '数量'
FROM purchases pu INNER JOIN products p ON p.product_id=pu.product_id INNER JOIN product_types t ON p.product_type_id=t.product_type_id INNER JOIN customers c ON c.customer_id=pu.customer_id;

左外连接、右外连接、全连接

tableName1 [left|right] OUTER JOIN tableName2 ON [...]

示列：
1、查询产品名、该产品的类型名，某些产品暂时还没有类型。

-- 查询产品名、该产品的类型名，某些产品暂时还没有类型。
#左外连接
SELECT p.name '产品名',t.name '类型名'
FROM products p LEFT OUTER JOIN product_types t USING(product_type_id);
#右外连接
SELECT p.name '产品名',t.name '类型名'
FROM  product_types t RIGHT OUTER JOIN  products p USING(product_type_id);

2、查询产品名、该产品的类型名，某些产品类型暂时还没有对应的产品，也要求输出类型名。

-- 查询产品名、该产品的类型名，某些产品类型暂时还没有对应的产品，也要求输出类型名。
SELECT p.name '产品名',t.name '类型名'
FROM  products p RIGHT OUTER JOIN product_types t USING(product_type_id);
SELECT p.name '产品名',t.name '类型名'
FROM  product_types t LEFT OUTER JOIN products p USING(product_type_id);

3、查询输出所有的产品名、该产品的类型名。
注：MySQL不支持 full outer join（全外连接）

SELECT p.name '产品名',t.name '类型名'
FROM  product_types t FULL OUTER JOIN products p USING(product_type_id);

交叉连接（笛卡尔积）

tableName1 CROSS JOIN tableName2

练习

1、检索产品表中，每个产品的编号、名称以及所属类型的编号和名称。
2、检索员工表中，每个员工的编号、姓名以及他的上司的编号、姓名，没有上司就输出空。
3、检索每个产品的购买信息，包括：产品编号、名称、订购者编号、名称、订购数量。没有被购买过的产品也要输出。
4、检索顾客表中，出生日期为NULL的顾客的编号、姓名和电话。

-- 1、检索产品表中，每个产品的编号、名称以及所属类型的编号和名称。
SELECT p.product_id '编号',p.name '名称',product_type_id '类型编号',t.name '类型名称'
FROM products p INNER JOIN product_types t USING(product_type_id);
-- 2、检索员工表中，每个员工的编号、姓名以及他的上司的编号、姓名，没有上司就输出空。
SELECT e.employee_id '员工编号',CONCAT(e.first_name,' ',e.last_name) '员工姓名',manager_id '上司编号',CONCAT(m.first_name,' ',m.last_name) '上司姓名'
FROM employees e LEFT OUTER JOIN more_employees m USING(manager_id);
-- 3、检索每个产品的购买信息，包括：产品编号、名称、订购者编号、名称、订购数量。没有被购买过的产品也要输出。
SELECT product_id '产品编号',p.name '名称',pu.customer_id '订购者编号',pu.quantity '订购数量',CONCAT(pu.first_name,' ',pu.last_name) '订购者名称'
FROM (SELECT * FROM purchases pu INNER JOIN customers c USING(customer_id)) pu RIGHT OUTER JOIN products p USING(product_id);
-- 4、检索顾客表中，出生日期为NULL的顾客的编号、姓名和电话。
SELECT *
FROM customers c WHERE c.dob IS NULL;

子查询

在外部的select、update或delete语句内部包含的select语句称为子查询。
在查询语句根据返回记录的行数分为：“单行子查询” 和 “多行子查询”。
如果返回的记录只有一行一列，则称为标量子查询。
如果子查询中引用了外部SQL语句中的一列或多列，称为关联子查询；

1、单行子查询
示列：
（1）查询和员工’Fred’具有同一个上司的其他员工的编号和姓名。
（2）查询价格大于“所有产品平均价格”的产品的编号、产品名。价格。
（3）对产品按照产品类型进行分组，输出产品组平均价格高于“全部产品平均价格”的产品类型编号和平均价格。

USE store;
#单行子查询
-- （1）查询和员工'Fred'具有同一个上司的其他员工的编号和姓名。
SELECT mee.employee_id '编号',CONCAT(mee.first_name,' ',mee.last_name) '姓名'
FROM employees mee
WHERE mee.manager_id=(
			SELECT me.manager_id
			FROM more_employees me
			WHERE me.first_name='Fred')
AND mee.first_name<>'Fred';

-- （2）查询价格大于“所有产品平均价格”的产品的编号、产品名。价格。
SELECT p.product_id '编号',p.name '产品名',p.price '价格'
FROM products p
WHERE p.price > (SELECT AVG(price) FROM products);

-- （3）对产品按照产品类型进行分组，输出产品组平均价格高于“全部产品平均价格”的产品类型编号和平均价格。
SELECT pro.product_type_id '产品类型编号',AVG(pro.price) '平均价格'
FROM products pro
GROUP BY pro.product_type_id
HAVING AVG(pro.price)>(SELECT AVG(price) FROM products)

2、多行子查询
子查询可能返回多条记录，所以在外部SQL语句中需使用IN，ANY，ALL等操作符。
示列：
（1）查询购买了编号为2的产品的顾客的信息。
（2）查询产品订购数量小于1号产品和2号产品订购数量的产品的编号和产品名。
（3）检索每一个产品的编号，产品名，被购买的次数，被购买的数量放在FROM字句中的子查询也叫做内联视图。

-- （1）查询购买了编号为2的产品的顾客的信息。
SELECT c.*
FROM customers c
WHERE c.customer_id IN (SELECT pu.customer_id
			 FROM purchases pu
			 WHERE pu.product_id=2);
-- （2）查询产品订购数量小于1号产品和2号产品订购数量的产品的编号和产品名。
SELECT p.product_id '产品编号',p.name '产品名'
FROM products p
WHERE p.product_id IN (SELECT pu.product_id
FROM purchases pu
GROUP BY pu.product_id
HAVING SUM(pu.quantity)<ALL(SELECT SUM(pu.quantity)
			FROM purchases pu
			GROUP BY pu.product_id
			HAVING pu.product_id=1 OR pu.product_id=2
			))
-- （3）检索每一个产品的编号，产品名，被购买的次数，被购买的数量
SELECT p.product_id,p.name,IFNULL(temp.c,0) '次数',IFNULL(temp.s,0) '数量'
FROM products p LEFT OUTER JOIN (SELECT pu.product_id,COUNT(pu.product_id) c,SUM(pu.quantity) s
				FROM purchases pu
				GROUP BY pu.product_id) temp
				USING(product_id)

3、关联子查询
子查询会引用外部SQL语句中的一列或多列。在查询时，关联子查询对于外部查询中的每一行都会运行一次。
示列：
查询价格高于同类产品平均价格的产品的信息

-- 查询价格高于同类产品平均价格的产品的信息
SELECT p.*
FROM products p
WHERE p.price>(SELECT AVG(pr.price)
		FROM products pr
		GROUP BY pr.product_type_id
		HAVING p.product_type_id=pr.product_type_id);

4、如果只关心子查询是否有结果而不关心结果是什么，此时可使用EXISTS操作符，该操作符用于检查是否有检索结果，只要有结果该操作符就返回true。
示列：
检查负责管理其他员工的员工的信息

-- 检索负责管理其它员工的员工的信息
SELECT e.*
FROM employees e
WHERE EXISTS (SELECT *
		FROM employees em
		WHERE em.manager_id=e.employee_id);

NOT EXISTS 是相反的【】判断，即不存在返回的记录时该操作符返回true。
示列
使用NOT EXISTS检索从来未被迈出的产品的信息

-- 使用NOT EXISTS检索从来未被卖出的产品的信息
SELECT p.*
FROM products p
WHERE NOT EXISTS (SELECT *
		FROM purchases pu
		WHERE pu.product_id=p.product_id)

练习

1、基于products表，查询其价格是同类产品中价格最高或者是最低的产品的编号、产品名称。
2、基于employees表，查询手下没有下级的员工编号、姓名。
3、基于employees表，查询手下员工超过两名的管理人员的编号和姓名。

-- 1、基于products表，查询其价格是同类产品中价格最高或者是最低的产品的编号、产品名称。
SELECT p.product_id '产品编号',p.name '产品名称'
FROM products pepidemics
WHERE p.price IN (SELECT MIN(p.price)
			FROM products pro
			GROUP BY pro.product_type_id
			HAVING p.product_type_id=pro.product_type_id
			);
			
-- 2、基于employees表，查询手下没有下级的员工编号、姓名。
SELECT em.employee_id '编号',CONCAT(em.first_name,' ',em.last_name) '姓名'
FROM employees em
WHERE NOT EXISTS (SELECT * FROM employees e WHERE em.employee_id=e.manager_id);

-- 3、基于employees表，查询手下员工超过两名的管理人员的编号和姓名。
SELECT e.employee_id,CONCAT(e.first_name,' ',e.last_name) '姓名'
FROM employees e
WHERE e.employee_id IN (SELECT em.employee_id FROM more_employees em GROUP BY manager_id HAVING COUNT(em.manager_id)>2)
-- 3、基于employees表，查询手下员工超过两名的管理人员的编号和姓名。
SELECT e1.`employee_id`,CONCAT(e1.`first_name`,e1.`last_name`)AS "姓名"
FROM employees e1
WHERE EXISTS (SELECT COUNT(1) FROM employees e2 WHERE e2.`manager_id` = e1.`employee_id` GROUP BY e2.`manager_id` HAVING COUNT(1)>=2);

视图

视图和表很相似，从视图中检索数据和从表中检索数据的方法完全相同。但视图并不存储数据，视图是建立在基表上的预定义查询。通过视图得到的数据完全来自于对基表的查询。
使用视图的优点：

屏蔽查询的复杂性；
对用户屏蔽部分数据；
隐藏基表；

创建视图：

CREATE [OR REPLACE] VIEW VIEW_NAME [(aliasNameList)] AS sub_query [WITH CHECK OPTION]

说明：

view_name 视图名称；
aliasNameList列别名列表，个数必须和子查询列数相同；
sub_query 针对基表进行查询的语句；
WITH CHECK OPTION 确保通过视图插入的记录能在视图中看到；

事务控制

MySQL中，默认事务是自动提交的。
事务操作：

SET AUTOCOMMIT = 1 (默认)，#自动提交事务
SET AUTOCOMMIT = 0 ，#手动提交事务
START TRANSACTION ，#启动新事物（MySQL特有）
COMMIT , #提交事务
ROLLVACK ，#回滚事务
SAVEPOINT 回滚点，#设置回滚点

大梦想家a

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
MySQL数据库应用学习笔记--3.多表链接、子查询、视图、事务控制

MySQL数据库应用集合操作符多表连接子查询视图事务控制集合操作符UNION ALL ：合并多个查询结果，可能会包含重复记录UNION ：合并多个查询的结果，不包含重复记录集合操作符可以将两个及两个以上查询的结果组合成一个结果集返回。要使用集合操作符，多个查询返回的记录必须具有相同的列数，列的数据类型也必须匹配，但列名可以不同。多表连接两个表的内连接等值连接时，如果关联列的列名相同，则可使用USING关键字多表内连接左外连接、右外连接、全连接交叉连接（笛卡尔积）
复制链接

扫一扫