联结与查询联结与查询

文章图片
about-BY-gentle.jpg

文章图片
image.png

根据上图的关系创建４张表分别是订单表、用户表、订单详情表、商品表

订单表：订单ｉｄ、订单时间、下单人
订单详情：订单ｉｄ、商品id、折扣、商品数量
商品表:商品ｉｄ、商品的价格、商品的名称
用户表：用户的ｉｄ、用户名、用户地址、用户所在的省
分别在表里面添加如下数据：
商品表
商品ｉｄ商品价格商品名称
１18.00 洗发水
２20.00沐浴露
３２8.00 牛奶
４５8.00 榴莲
５７8.00 牛肉
用户表
用户ｉｄ用户名用户地址用户省份
１李白北京市通州北京
２白聚义北京市朝阳北京
３赵子龙襄阳市赵店湖北
４王伟石家庄马家堡河北
５吕布保定绵阳镇河北
２白义北京市通州北京
订单表：
订单ｉｄ订单时间下单人
１２０１８－０９－１１１
２２０１８－０９－１２２
３２０１８－０９－１４３
４２０１８－０９－１４２
５２０１８－０９－１８１
订单详情：
订单ｉｄ商品id折扣商品数量
１１0.8 2
１20.9 3

30.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 joincustomer 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)

mysql> select * fromcustomer 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)