多表查询–等值连接、自连接、内连接、外连接查询，子查询
笛卡尔乘积现象：每个标的每一行都和其他表的每一行组合
以下题目作为示例以助理解
一、表内容
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | accounting | new york |
| 20 | research | dallas |
| 30 | sales | chicago |
| 40 | operations | xian |
+--------+------------+----------+
4 rows in set (0.00 sec)
mysql> select * from emp emp;
+-------+--------+-----------+------+------------+--------+------+--------+
| empno | ename | job | mgr | hiredate | salary | comm | deptno |
+-------+--------+-----------+------+------------+--------+------+--------+
| 7369 | Smith | clerk | 7902 | 1980-01-17 | 1000 | NULL | 20 |
| 7499 | Allen | salesman | 7698 | 1981-02-20 | 1800 | 300 | 30 |
| 7521 | Ward | salesman | 7698 | 1981-02-22 | 1450 | 500 | 30 |
| 7566 | Jones | manager | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7654 | Martin | salesman | 7698 | 1981-09-28 | 1450 | 1400 | 30 |
| 7698 | Blake | manager | 7639 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | Clark | manager | 7639 | 1981-06-09 | 2450 | NULL | 10 |
| 7788 | Scott | analyst | 7566 | 1987-07-13 | 3000 | NULL | 20 |
| 7839 | king | president | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7844 | Turner | saleman | 7698 | 1987-09-08 | 1700 | 0 | 30 |
| 7876 | Adams | clerk | 7788 | 1987-07-13 | 1300 | NULL | 20 |
| 7900 | James | clerk | 7698 | 1981-12-03 | 1150 | NULL | 30 |
| 7902 | Ford | analyst | 7566 | 1981-12-03 | 3000 | NULL | 20 |
| 7934 | Miller | clerk | 7782 | 1982-02-23 | 1500 | NULL | 10 |
+-------+--------+-----------+------+------------+--------+------+--------+
14 rows in set (0.00 sec)
等值连接查询
通常是在存在主键外键关联关系的表之间的连接进行，使用"="连接相关的表
n个表进行等值连接查询，最少需要n-1个等值条件来约束
- 查询每个部门的所有员工
select dept.dname,emp.ename from dept,emp where dept.deptno = emp.deptno;
自连接查询
表表查询不仅可以在多个表之间进行查询，也可以在一个表之中进行多表查询
-
查询当前公司员工和所属上级员工信息
select e1.empno,e1.ename,e2.empno,e2.ename as from emp e1,emp e2 where e1.mgr = e2.empno;
内连接查询–和等值连接差不多
内连接查询使用 inner join 关键字实现， inner 可以省略。内连接查询时，条件用 on连接，多个条件使用()将其括起来
- 查询每个部门的所有员工
select e.ename as 员工姓名,d.dname 部门姓名 from emp as e inner join dept as d on d.deptno = e.deptno;
# 该题也可用等值连接查询
select e.ename as 员工姓名,d.dname 部门姓名 from emp as e ,dept as d where d.deptno = e.deptno;
外连接查询–分左外连接和右外连接
外连接分为左外连接（ left outer join ） 和右外连接（right outer join）其值outer可以省略。外连接查询时，条件用 on 连接，多个条件使用()将其括起来.左外连接表示以左表为主表，右外连接表示以右表为主表。查询时将主表信息在从表中进行匹配
4.查询每个部门的所有员工
select e.ename,d.dname from emp e left join dept d on d.deptno = e.deptno;
select emp.ename,dept.dname from emp right join dept on dept.deptno = emp.deptno;
子查询
存在于另外一个SQL语句中、被小括号包起来的查询语句就是子查询。相对于子查询来说，在外部直接执行的查询语句被称作主查询
分类:
-
单列子查询: 返回单行单列数据的子查询
-
单行子查询: 返回单行多列数据的子查询
-
多行子查询: 返回数据是多行单列的数据
-
关联子查询: 子查询中如果使用了外部主
SQL中的表或列，就说这个子查询跟外部SQL是相关的
单列子查询–单行单列
1.查询deptname=research的所有员工
select * from emp as e where e.deptno = (select d.deptno from dept as d where d.dname = 'research');
多行子查询
如果子查询返回了多行记录，则称这样的嵌套查询为多行子查询，多行子查询就需要用到多行记录的操作符
多行记录的操作符– in , all , any(some)
-
in子查询中所有的记录 -
any表示大于子查询中的任意一个值，即大于最小值 -
all表示大于子查询中的所有值，即大于最大的值
2.查询公司中比任意一个员工的工资高的所有员工
select * from emp where salary > all(select salary from emp );
3.查询公司中比所有的经理工资高但不是经理的员工
select * from emp e1 where e1.salary > all(select e2.salary from emp e2 where e2.job like '%manager');
多行子查询–单行多列
4.查询公司中和员工Ford薪水和奖金的员工
select * from emp e1 where (e1.salary,e1.comm) = (select e2.salary,e2.comm from emp e2 where e2.ename = 'Ford');
练习
一、表内容
mysql> show tables;
+-----------------+
| Tables_in_store |
+-----------------+
| account |
| cart |
| category |
| goods |
+-----------------+
4 rows in set (0.00 sec)
mysql> select * from account;
+------+-------+----------+------------+--------+-------+-------+---------------------+
| id | phone | password | type | name | point | money | create_time |
+------+-------+----------+------------+--------+-------+-------+---------------------+
| 1 | 111 | 111 | 0x00 | 管理员 | NULL | NULL | 2023-03-07 16:50:00 |
| 2 | 222 | 222 | 0x01 | 张三 | 0 | 350 | 2023-03-11 19:50:00 |
| 3 | 333 | 333 | 0x01 | 李四 | 0 | 268.5 | 2023-03-12 19:50:00 |
| 4 | 444 | 444 | 0x01 | 王五 | 0 | 956.3 | 2023-03-13 19:50:00 |
+------+-------+----------+------------+--------+-------+-------+---------------------+
4 rows in set (0.00 sec)
mysql> select * from cart;
+------+----------+------+------------+---------------------+
| id | goods_no | num | account_id | create_time |
+------+----------+------+------------+---------------------+
| 1 | 1 | 2 | 2 | 2023-03-11 19:50:00 |
| 2 | 2 | 2 | 3 | 2023-03-12 19:50:00 |
| 3 | 4 | 10 | 4 | 2023-03-13 19:50:00 |
| 4 | 6 | 20 | 3 | 2023-03-12 19:50:00 |
| 5 | 9 | 1 | 2 | 2023-03-12 19:50:00 |
| 6 | 2 | 10 | 4 | 2023-03-13 19:50:00 |
+------+----------+------+------------+---------------------+
6 rows in set (0.00 sec)
mysql> select * from category;
+------+----------+
| no | name |
+------+----------+
| 1 | 零食 |
| 2 | 蔬菜 |
| 3 | 水果 |
| 4 | 电子产品 |
| 5 | 酒水 |
+------+----------+
5 rows in set (0.00 sec)
mysql> select * from goods;
+---------+------------+------+-------+-------+---------------------+-------------+
| good_no | goods_name | cost | price | count | create_time | category_no |
+---------+------------+------+-------+-------+---------------------+-------------+
| 2 | 薯片 | 1.5 | 3.5 | 30 | 2023-03-13 19:50:00 | 1 |
| 3 | 鼠标垫 | 20.5 | 25.5 | 20 | 2023-03-13 19:50:00 | 4 |
| 4 | 萝卜 | 0.8 | 1.5 | 50 | 2023-03-13 19:50:00 | 2 |
| 5 | 白菜 | 0.2 | 0.8 | 50 | 2023-03-13 19:50:00 | 2 |
| 6 | 车厘子 | 30 | 50 | 50 | 2023-03-13 19:50:00 | 3 |
| 7 | 芒果 | 5 | 8 | 50 | 2023-03-13 19:50:00 | 3 |
| 8 | 五粮液 | 1350 | 1700 | 60 | 2023-03-13 19:50:00 | 5 |
| 9 | 鼠标 | 40 | 50 | 20 | 2023-03-13 19:50:00 | 4 |
| 1 | 火腿肠 | 0.5 | 2.5 | 50 | 2023-03-13 19:50:00 | 1 |
+---------+------------+------+-------+-------+---------------------+-------------+
9 rows in set (0.00 sec)
二、问题
1.改日期
#将日期增加一天
update account set create_time=date_add(create_time,interval 1 day);
#将日期增加一月
update account set create_time=date_add(create_time,interval 1 month);
注意：此处date_add(create_time,interval 1 day),为一个函数day可为month,year,day,minute
#修改年
date_add(create_time, interval 1 year)
#修改月
date_add(create_time, interval 1 month)
#修改天
date_add(create_time, interval 1 day)
date_add(create_time, interval 1 hour)
date_add(create_time, interval 1 second)
2.改别名
select 列名 as 新的别名 from 哪个表
3.多表查询
select * from account,cart,goods;
4.薯片[火腿肠] [零食]被谁买走了
select account.name,goods.goods_name
from account,cart,goods
where cart.goods_no = goods.good_no
and cart.account_id = account.id
and goods_name = '薯片[火腿肠][零食]';
5.求每个人的购物车总价
select account.name, sum(cart.num * goods.price)
from account,cart,goods,category
where cart.goods_no = goods.good_no
and goods.category_no=category.no
and cart.account_id = account.id group by account.name;
6.求哪天的营业额最高
select sum(cart.num*goods.price),cart.create_time
from goods,cart where cart.goods_no=goods.good_no
group by cart.create_time
order by sum(cart.num*goods.price) desc limit 1;
7.张三最爱在什么时候购物
select cart.create_time
from account,cart
where
account.id=cart.account_id
and
account.create_time=cart.create_time
and
account.name='张三';
8.求哪个商品的利润率最高
select goods.goods_name,(goods.price-goods.cost)/goods.cost
from goods,cart where cart.goods_no=goods.good_no
order by (goods.price-goods.cost)/goods.cost desc limit 1;
9.求2023年3 月 12 日前一周销售的商品
select goods.goods_name
from goods,cart
where cart.goods_no = goods.good_no and cart.create_time
between '2023-03-05' and '2023-03-12';
10.求每个用户的余额
select account.name,account.money-sum(cart.num*goods.price)
from account,cart,goods
where cart.account_id=account.id
and goods.good_no=cart.goods_no
group by account.name,account.money;
11.求每个商品的库存还余多少
select
goods.goods_name,goods.count-sum(cart.num)
from
goods,cart
where
cart.goods_no = goods.good_no
group by goods.goods_name,goods.count;
学习内容总结
吐槽
错题记录
1412
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
