目录
7.2返回 2020 年 1 月的所有订单的订单号和订单日期
8.2确定 Products 表中价格不超过 10 美元的最贵产品的价格
10.2返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件
10.4从 Products 表中检索所有的产品名称以及对应的销售总数
13.2组合 Products 表中的产品名称和 Customers 表中的顾客名称
1检索数据
1.1 已订购产品的清单——去重
我的代码：group by 分组
select prod_id from OrderItems
group by prod_id其他方法： distinct 搜索去重：select distinct [列名1，列名2，...] from [表名]
select distinct prod_id
from OrderItems1.2 检索所有列
我的代码：匹配所有列
select * from Customers 指定列名：
select cust_id, cust_name from Customers （很简单对不对，不要着急，慢慢来）
2排序检索数据
2.1检索顾客名称并且排序
我的正确代码：
select cust_name
from Customers
order by cust_name descorder by用法总结：http://mp.weixin.qq.com/s/5QDt4u1IdDFUcmWPbFUqzA
2.2对顾客ID和日期排序
我的代码：虽然结果正确，但是认真看题不能轻敌粗心
select cust_id,order_num
from Orders
order by cust_id asc,order_num desc正确代码：虽然结果一样，但是题目是再按订单日期倒序排列，是order_date降序，而不是order_num。
select cust_id,order_num
from Orders
order by cust_id asc,order_date desc2.3按照数量和价格排序
我的代码：两个降序
select quantity,item_price
from OrderItems
order by quantity desc,item_price desc根据列索引位置：这里1,2，对应表示前面两个列名
select quantity,item_price
from OrderItems
order by 1 desc,2 desc3过滤数据
3.1 返回固定价格的产品
我的代码：过滤——where
select prod_id,prod_name
from Products
where prod_price= 9.49where用法总结：http://mp.weixin.qq.com/s/mHH3jU14zJQZIISIJBOd3w
3.2返回产品并且按照价格排序
我的代码：between...and
select prod_name,prod_price
from Products
where prod_price between 3 and 6
order by prod_price或用不等式：
select prod_name,prod_price
from Products
where prod_price >= 3 and prod_price<=6
order by prod_price注意：`order` 语句必须放在 where 之后
4高级数据过滤
4.1检索供应商名称
我的代码：
select vend_name
from Vendors
where vend_state = 'CA' 多列筛选 使用 and链接：这需要按国家[USA]和州[CA]进行过滤，没准其他国家也存在一个CA
select vend_name
from Vendors
where vend_state = 'CA' and vend_country='USA'4.2 检索并列出已订购产品的清单
我的代码：用in，本来ID准备用or链接，但是不行（写法不对）
select order_num,prod_id,quantity
from OrderItems
where prod_id in ('BR01','BR02','BR03')
and quantity>100or用法正确写法：‘ prod_id =’要重复多次
select order_num,prod_id,quantity
from OrderItems
where (prod_id = 'BR01'or prod_id = 'BR02'or prod_id = 'BR03')
and quantity >=100;这应该类似or，逻辑或：
SELECT order_num, prod_id, quantity
FROM OrderItems
WHERE quantity >= 100 AND prod_id REGEXP 'BR01'|'BR02'|'BR03'用between and也可以，因为ID是有顺序的：
select order_num,prod_id,quantity
from OrderItems
where prod_id between 'BR01'and 'BR03'
and quantity >=100;5用通配符进行过滤
5.1检索产品（一）——包含
我的代码：字符串包含——用like和通配符，%后不用a和空格
select prod_name,prod_desc
from Products
where prod_desc like '%toy%'like用法：
1）如以Sir开头的：where winner like 'Sir%'
2）含有三个a的国家：where name like '%a%a%a'
3）首都 需是 国家名称 的扩展：where capital like concat('%',name,'%')
一些其他方法：
-- like
select prod_name, prod_desc from Products where prod_desc like "%toy%"
-- regexp
select prod_name, prod_desc from Products where prod_desc regexp "toy"
-- instr
select prod_name, prod_desc from Products where instr(prod_desc, "toy") > 0
-- locate
select prod_name, prod_desc from Products where locate("toy", prod_desc) > 0
-- position
select prod_name, prod_desc from Products where position("toy" in prod_desc) > 05.2检索（二）——不包含
我的代码：未出现——否定
select prod_name,prod_desc
from Products
where prod_desc not like '%toy%'
order by prod_name一些其他方法代码：
select prod_name, prod_desc
from Products
# where instr(prod_desc, 'toy')=0
# where prod_desc not like '%toy%'
# where prod_desc not regexp 'toy'
# having locate('toy', prod_desc) = 0
having instr(prod_desc, 'toy') = 0
# having position('toy' in prod_desc) = 0
order by prod_name5.3检索（三）——同时包含
我的代码：
select prod_name,prod_desc
from Products
where prod_desc like '%toy%' and prod_desc like '%carrots%'一些其他方法：
select
prod_name,
prod_desc
from Products
where
(prod_desc like '%toy%' and prod_desc like '%carrots%')
and
(prod_desc regexp 'toy' and prod_desc regexp 'carrots')
and
(prod_desc regexp '(.*toy.*carrots.*)|(.*carrots.*toy.*)')
and
(instr(prod_desc,'toy') > 0 and instr(prod_desc,'carrots') > 0)
and
(position('toy' in prod_desc) > 0 and position('carrots' in prod_desc))
and
(locate('toy',prod_desc) > 0 and locate('carrots',prod_desc) > 0);5.4 检索（四）——先后出现
我的代码：
select prod_name,prod_desc
from Products
where prod_desc like '%toy%carrots%'其他方法：
select prod_name,prod_desc
from Products
where prod_desc regexp 'toy.carrots'6 创建计算字段
6.1打折
我的代码：
select prod_id,prod_price,prod_price*0.9 sale_price
from Products加上round函数:
select
prod_id,
prod_price,
round(prod_price*0.9,3) as sale_price
from
Productsround函数学习链接：MySQL ROUND函数详解|极客笔记
6.2 别名
as(可省略)
select vend_id,
vend_name as vname,
vend_address vaddress,
vend_city vcity
from Vendors
order by vend_name asc7 使用函数处理数据
7.1顾客登录名
我的代码：不知道怎么取前几个字符呢
select cust_id,cust_name,
concat('cust_contact',2,'cust_city',3) as user_login
from Customers正确代码：
select cust_id,cust_name,
upper(concat(substring(cust_contact,1,2),substring(cust_city,1,3)))
as user_login
from Customers所考知识点：
1.截取函数：substring()
用法：SUBSTRING(str ,n ,m)：返回字符串str从第n个字符截取到第m个字符；
2.拼接函数：concat()
用法：select concat(A,B) 或者select A || B
3.大写函数UPPER()
7.2返回 2020 年 1 月的所有订单的订单号和订单日期
我的代码：
select order_num,order_date
from Orders
where order_date like '2020-01%'
order by order_date asc其他方法：
select order_num, order_date
from Orders
where Year(order_date)='2020' and Month(order_date)='01'
order by order_date;select order_num, order_date
from Orders
where date_format(order_date, '%Y%m') = '202001'
order by order_date;8汇总数据
8.1确定已售出产品项 BR01 的总数
我的代码：总数用sum，筛选用where
select sum(quantity) items_ordered
from OrderItems
where prod_id = 'BR01'8.2确定 Products 表中价格不超过 10 美元的最贵产品的价格
我的代码：最大值max，不超过<=
select max(prod_price) max_price
from Products
where prod_price <=109 分组数据
9.1 每个供应商成本最低的产品
我的代码：每个供应商——分组，成本最低——min
select vend_id,min(prod_price) cheapest_item
from Products
group by vend_id
order by cheapest_item9.2返回订单数量总和不小于100的所有订单的订单号
我的代码：
select order_num
from OrderItems
group by order_num
having sum(quantity)>=100
order by order_num9.3 计算总和
我的报错代码：
select
order_num,
(item_price * quantity) total_price
from
OrderItems
group by
order_num
having
sum(item_price * quantity) >= 1000
order by
order_num我的代码改正：思路对的，但是写法细节有误（select 后取别名，没有sum的话，item_price * quantity会报错的）
select
order_num,
sum(item_price * quantity) total_price
from
OrderItems
group by
order_num
having
total_price >= 1000
order by
order_num
10使用子查询
10.1返回购买价格为 10 美元或以上产品的顾客列表
我的代码：通过两个表对应的ID号相等，找到满足条件的对应数据条
select cust_id
from Orders
where order_num in (select order_num
from OrderItems
where item_price>=10)注意： where后是in，而不是=，写=可能不会报错，但是会漏掉信息。
也可以用后面学的关联表法：
select b.cust_id
from OrderItems a
join Orders b
on a.order_num = b.order_num
where a.item_price>=10;10.2返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件
我的代码：两个子查询嵌套——套娃，多个子查询还是后面表链接方便
select
cust_email
from
Customers
where
cust_id in (
select
cust_id
from
Orders
where
order_num in (
select
order_num
from
OrderItems
where
prod_id = 'BR01'
)
)
表连接方法：
select c.cust_email
from Customers c join Orders o
on c.cust_id=o.cust_id
join OrderItems om
on om.order_num = o.order_num
where om.prod_id='BR01'10.3返回每个顾客不同订单的总金额
我的报错代码：思路感觉木有问题
select o.cust_id,sum(m.quantity) total_ordered
from Orders o,OrderItems m
where (o.order_num,total_ordered) in
(select order_num,sum(quantity) total_ordered
from OrderItems
group by order_num
order by total_ordered desc)正确代码：无比怀念表链接
SELECT cust_id,
(SELECT SUM(quantity * item_price)
FROM OrderItems
WHERE OrderItems.order_num = Orders.order_num) AS total_ordered
FROM Orders
ORDER BY total_ordered DESC;
10.4从 Products 表中检索所有的产品名称以及对应的销售总数
我的代码：在select后直接子查询
用having Products.prod_id=OrderItems.prod_id来对应数据，否则返回就不止一个数据了。
select prod_name,
(select sum(quantity)
from OrderItems
group by prod_id
having Products.prod_id=OrderItems.prod_id) quant_sold
from Products这种方法不用group by也可以：是从Products表出发，每条记录与OrderItems表中的所有记录比对找出prod_id相等的数据，然后对qunantity求和，所以不需要GROUP BY也是可以的。
select prod_name,
(select sum(quantity)
from OrderItems
where Products.prod_id=OrderItems.prod_id) quant_sold
from Products方法2：在from后子查询
SELECT prod_name,quant_sold
FROM Products, (SELECT prod_id, SUM(quantity) AS quant_sold
FROM OrderItems
GROUP BY prod_id) AS OI
WHERE Products.prod_id = OI.prod_id;11联结表
11.1返回顾客名称和相关订单号
我的代码：
select c.cust_name,o.order_num
from Customers c join Orders o
on c.cust_id=o.cust_id
order by cust_name,order_num asc注意：on c.cust_id=o.cust_id 可以换成 using(cust_id)
11.2返回顾客名称和相关订单号以及每个订单的总价
我的报错代码
select c.cust_name,o.order_num,
(select sum(quantity*item_price) OrderTotal
from OrderItems,Orders
where OrderItems.order_num=Orders.order_num)
from Customers c join Orders o
using(cust_id)
order by c.cust_name,o.order_num这样也不对
select c.cust_name,o.order_num,sum(om.quantity*om.item_price) OrderTotal
from Customers c join Orders o
on c.cust_id = o.cust_id
join OrderItems om
on om.order_num = o.order_num
group by o.order_num
order by c.cust_name,o.order_num
改正：还要按照c.cust_name进行分组，因为一个顾客可能会有多个订单
select c.cust_name,o.order_num,sum(om.quantity*om.item_price) OrderTotal
from Customers c join Orders o
on c.cust_id = o.cust_id
join OrderItems om
on om.order_num = o.order_num
group by c.cust_name,o.order_num
order by c.cust_name,o.order_num11.3确定最佳顾客
我的代码：
select c.cust_name,sum(om.item_price*om.quantity) as total_price
from OrderItems om join Orders o
on om.order_num=o.order_num
join Customers c
on c.cust_id=o.cust_id
group by o.order_num,c.cust_name
having total_price>=1000
order by total_price12创建高级联结
12.1检索每个顾客的名称和所有的订单号——左连接
我的代码：
select c.cust_name,o.order_num
from Customers c left join Orders o
using(cust_id)
order by c.cust_name如果主表在后就是右链接
select cust_name, order_num
from Orders a right join Customers b
on a.cust_id=b.cust_id
order by cust_name12.2 返回产品名称和与之相关的订单号——外连接
我的报错代码：
select prod_name,order_num
from Products outer join OrderItems
using(prod_id)
order by prod_name 外连接 (OUTER JOIN)分为三种
1. 左外连接 (LEFT OUTER JOIN 或 LEFT JOIN）：左表的记录将会全部表示出来，而右表只会显示符合搜索条件的记录，右表记录不足的地方均为NULL
2. 右外连接 (RIGHT OUTER JOIN 或 RIGHT JOIN)：与左(外)连接相反，右(外)连接，右表的记录将会全部表示出来，而左表只会显示符合搜索条件的记录，左表记录不足的地方均为NULL
3. 全外连接 (FULL OUTER JOIN 或 FULL JOIN)：左表和右表都不做限制，所有的记录都显示，两表不足的地方用null 填充
MySQL中不支持全外连接，可以使用 UNION 来合并两个或多个 SELECT 语句的结果集
正确代码：
select prod_name,order_num
from Products left join OrderItems
using(prod_id)
order by prod_name12.3返回产品名称和每一项产品的总订单数
我的代码：每一项产品的总订单数，要按照产品名称prod_name分组，其实有点没有明白为什么按照prod_id不行？？
select prod_name,count(order_num) orders
from Products left join OrderItems
using(prod_id)
group by prod_name
order by prod_name12.4列出供应商及其可供产品的数量
我的代码：
select p.vend_id,count(p.prod_id) as 'prod_id'
from Vendors v left join Products p
using(vend_id)
group by v.vend_id
order by v.vend_id答案不对：因为供应商a00013供应的商品不在Products表中，所以不应该select p.vend_id，而是select v.vend_id
正确代码：
select v.vend_id,count(p.prod_id) as 'prod_id'
from Vendors v left join Products p
using(vend_id)
group by v.vend_id
order by v.vend_id13组合查询
13.1将两个 SELECT 语句结合起来
我的冗余代码：
select prod_id,quantity
from OrderItems
where (prod_id,quantity) in
((select prod_id,quantity
from OrderItems
where quantity = 100),
(select prod_id,quantity
from OrderItems
where prod_id like 'BNBG%'))
union的用法：
select * from OrderItems where quantity =100
union
select * from OrderItems where prod_id like "BNBG%"
order by prod_id
13.2组合 Products 表中的产品名称和 Customers 表中的顾客名称
我的代码：
select * from Products p
union
select * from Customers c
order by prod_nameunion与union all 都是行合并，前者去重，后者不去重
（半天就干完啦，我真棒！！！晚上要开会，估计整不成进阶了，明天？明天周五图书馆不开门要打游戏呢，emmm那就零碎时间来几道吧）
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
