联结与查询
2018-08-15 本文已影响6人
叫我老村长
about-BY-gentle.jpg
image.png
- 根据上图的关系创建４张表分别是订单表、用户表、订单详情表、商品表
订单表：订单ｉｄ、订单时间、下单人
订单详情：订单ｉｄ、商品id、折扣、商品数量
商品表:商品ｉｄ、商品的价格、商品的名称
用户表：用户的ｉｄ、用户名、用户地址、用户所在的省
分别在表里面添加如下数据：
商品表
商品ｉｄ 商品价格 商品名称
１ 18.00 洗发水
２ 20.00 沐浴露
３ ２8.00 牛奶
４ ５8.00 榴莲
５ ７8.00 牛肉
用户表
用户ｉｄ 用户名 用户地址 用户省份
１ 李白 北京市通州 北京
２ 白聚义 北京市朝阳 北京
３ 赵子龙 襄阳市赵店 湖北
４ 王伟 石家庄马家堡 河北
５ 吕布 保定绵阳镇 河北
２ 白义 北京市通州 北京
订单表：
订单ｉｄ 订单时间 下单人
１ ２０１８－０９－１１ １
２ ２０１８－０９－１２ ２
３ ２０１８－０９－１４ ３
４ ２０１８－０９－１４ ２
５ ２０１８－０９－１８ １
订单详情：
订单ｉｄ 商品id 折扣 商品数量
１ １ 0.8 2
１ 2 0.9 3
-
3 0.9 1
-
4 1.0 2
-
5 0.5 3
-
4 0.6 2
-
查出每个订单的详情信息以及商品信息和购买用户信息
-
计算出订单２的商品的总价格
-
查找出每个订单详情的商品信息
-
根据订单表查找出下单人的详细信息
-
使用子查询的方式，查找出所有订单表中所有已下单的用户的姓名和地址
-
使用关联查询的方式，在用户表查找出在北京通州的用户和用户id >3的用户（分别展示出去重和不去重的结果）
mysql> select * from orderdetail inner join product on orderdetail.productid=product.productid;
+---------+-----------+----------+----------+-----------+-----------+-------------+
| orderid | productid | discount | quantity | productid | unitprice | productname |
+---------+-----------+----------+----------+-----------+-----------+-------------+
| 1 | 1 | 1 | 2 | 1 | 18 | 洗发水 |
| 2 | 3 | 1 | 3 | 3 | 28 | 牛奶 |
| 3 | 4 | 1 | 2 | 4 | 58 | 榴莲 |
| 4 | 5 | 1 | 3 | 5 | 78 | 牛肉 |
| 5 | 4 | 1 | 2 | 4 | 58 | 榴莲 |
+---------+-----------+----------+----------+-----------+-----------+-------------+
5 rows in set (0.00 sec)
mysql> select * from orde join orderdetail on orde.orderid=orderdetail.orderid;
+---------+------------+------------+---------+-----------+----------+----------+
| orderid | orderdate | customerid | orderid | productid | discount | quantity |
+---------+------------+------------+---------+-----------+----------+----------+
| 1 | 2018-09-11 | NULL | 1 | 1 | 1 | 2 |
| 2 | 2018-09-12 | NULL | 2 | 3 | 1 | 3 |
| 3 | 2018-09-14 | NULL | 3 | 4 | 1 | 2 |
| 4 | 2018-09-14 | NULL | 4 | 5 | 1 | 3 |
| 5 | 2018-09-18 | NULL | 5 | 4 | 1 | 2 |
+---------+------------+------------+---------+-----------+----------+----------+
5 rows in set (0.00 sec)
mysql> select * from customer join orde on orde.customerid=customer.customerid;
+------------+--------------+--------------------+--------------+---------+------------+------------+
| customerid | customername | customeraddr | customercity | orderid | orderdate | customerid |
+------------+--------------+--------------------+--------------+---------+------------+------------+
| 1 | 李白 | 北京市通州 | 北京 | 1 | 2018-09-11 | 1 |
| 2 | 白聚义 | 北京市朝阳 | 北京 | 2 | 2018-09-12 | 2 |
| 3 | 赵子龙 | 襄阳市赵店 | 湖北 | 3 | 2018-09-14 | 3 |
| 4 | 王伟 | 石家庄马家堡 | 河北 | 4 | 2018-09-14 | 4 |
| 5 | 吕布 | 保定绵阳镇 | 河北 | 5 | 2018-09-18 | 5 |
+------------+--------------+--------------------+--------------+---------+------------+------------+
5 rows in set (0.01 sec)
mysql> select (select unitprice from product where productid=2)*quantity*discount as zongjia from orderdetail where orderid=2;
+---------+
| zongjia |
+---------+
| 60 |
+---------+
1 row in set (0.00 sec)
mysql> select * from orderdetail inner join product on orderdetail.productid=product.productid;
+---------+-----------+----------+----------+-----------+-----------+-------------+
| orderid | productid | discount | quantity | productid | unitprice | productname |
+---------+-----------+----------+----------+-----------+-----------+-------------+
| 1 | 1 | 1 | 2 | 1 | 18 | 洗发水 |
| 2 | 3 | 1 | 3 | 3 | 28 | 牛奶 |
| 3 | 4 | 1 | 2 | 4 | 58 | 榴莲 |
| 4 | 5 | 1 | 3 | 5 | 78 | 牛肉 |
| 5 | 4 | 1 | 2 | 4 | 58 | 榴莲 |
+---------+-----------+----------+----------+-----------+-----------+-------------+
5 rows in set (0.00 sec)
mysql> select * from orde inner join customer on orde.customerid=customer.customerid;
+---------+------------+------------+------------+--------------+--------------------+--------------+
| orderid | orderdate | customerid | customerid | customername | customeraddr | customercity |
+---------+------------+------------+------------+--------------+--------------------+--------------+
| 1 | 2018-09-11 | 1 | 1 | 李白 | 北京市通州 | 北京 |
| 2 | 2018-09-12 | 2 | 2 | 白聚义 | 北京市朝阳 | 北京 |
| 3 | 2018-09-14 | 3 | 3 | 赵子龙 | 襄阳市赵店 | 湖北 |
| 4 | 2018-09-14 | 4 | 4 | 王伟 | 石家庄马家堡 | 河北 |
| 5 | 2018-09-18 | 5 | 5 | 吕布 | 保定绵阳镇 | 河北 |
+---------+------------+------------+------------+--------------+--------------------+--------------+
5 rows in set (0.00 sec)
5
mysql> select * from customer where customerid=any(select customerid from orde);
+------------+--------------+--------------------+--------------+
| customerid | customername | customeraddr | customercity |
+------------+--------------+--------------------+--------------+
| 1 | 李白 | 北京市通州 | 北京 |
| 2 | 白聚义 | 北京市朝阳 | 北京 |
| 3 | 赵子龙 | 襄阳市赵店 | 湖北 |
| 4 | 王伟 | 石家庄马家堡 | 河北 |
| 5 | 吕布 | 保定绵阳镇 | 河北 |
+------------+--------------+--------------------+--------------+
5 rows in set (0.00 sec)
6
mysql> select * from customer where customeraddr='北京市通州' union select * from customer where customerid>3;
+------------+--------------+--------------------+--------------+
| customerid | customername | customeraddr | customercity |
+------------+--------------+--------------------+--------------+
| 1 | 李白 | 北京市通州 | 北京 |
| 6 | 白义 | 北京市通州 | 北京 |
| 4 | 王伟 | 石家庄马家堡 | 河北 |
| 5 | 吕布 | 保定绵阳镇 | 河北 |
+------------+--------------+--------------------+--------------+
4 rows in set (0.00 sec)
mysql> select * from customer where customeraddr='北京市通州' union ALL select * from customer where customerid>3;
+------------+--------------+--------------------+--------------+
| customerid | customername | customeraddr | customercity |
+------------+--------------+--------------------+--------------+
| 1 | 李白 | 北京市通州 | 北京 |
| 6 | 白义 | 北京市通州 | 北京 |
| 4 | 王伟 | 石家庄马家堡 | 河北 |
| 5 | 吕布 | 保定绵阳镇 | 河北 |
| 6 | 白义 | 北京市通州 | 北京 |
+------------+--------------+--------------------+--------------+
5 rows in set (0.00 sec)
