数据库（MySQL）刷题及知识学习

秋招对我好点！！！

提前批秋招快开始了，数据库方面的知识还没怎么看，先跟着牛客网刷题和《MySQL必知必会》学习！
冲！！！

牛客网刷题链接

01 检索数据

SQL1 从 Customers 表中检索所有的 ID

# 检索id
select cust_id
from Customers;

SQL2 检索并列出已订购产品的清单

SELECT DISTINCT prod_id
FROM OrderItems;

SQL3 检索所有列

SELECT cust_id,cust_name
FROM Customers;

---

02 排序检索数据

SQL4 检索顾客名称并且排序

SELECT cust_name
FROM Customers
ORDER BY cust_name DESC;

SQL5 对顾客ID和日期排序

SELECT cust_id,order_num
FROM Orders
ORDER BY cust_id,order_date DESC;

SQL6 按照数量和价格排序

SELECT quantity,item_price
FROM OrderItems
ORDER BY quantity DESC,item_price DESC;

SQL7 检查SQL语句

SELECT vend_name
FROM Vendors
ORDER BY vend_name DESC;

---

03 过滤数据

SQL8 返回固定价格的产品

SELECT prod_id,prod_name
FROM Products
WHERE prod_price = 9.49

SQL9 返回更高价格的产品

SELECT prod_id,prod_name
FROM Products
WHERE prod_price>=9

SQL10 返回产品并且按照价格排序

SELECT prod_name,prod_price
FROM Products
WHERE prod_price BETWEEN 3 and 6
ORDER BY prod_price;

SQL11 返回更多的产品

SELECT DISTINCT order_num
FROM OrderItems
WHERE quantity>=100;

---

04 高级数据过滤

SQL12 检索供应商名称

SELECT vend_name
FROM Vendors
WHERE vend_country = 'USA'  and vend_state = 'CA';

SQL13 检索并列出已订购产品的清单

SELECT order_num,prod_id,quantity
FROM OrderItems
WHERE prod_id in ('BR01','BR02','BR03') and quantity>=100;

SQL14 返回所有价格在 3美元到 6美元之间的产品的名称和价格

SELECT prod_name,prod_price
FROM Products
WHERE prod_price BETWEEN 3 and 6
ORDER BY prod_price;

SQL15 纠错2

SELECT vend_name 
FROM Vendors 
WHERE vend_country = 'USA' AND vend_state = 'CA'
ORDER BY vend_name ;

---

05 用通配符进行过滤

SQL16 检索产品名称和描述（一）

SELECT prod_name,prod_desc
FROM Products
WHERE prod_desc LIKE '%toy%';

SQL17 检索产品名称和描述（二）

SELECT prod_name,prod_desc
FROM Products
WHERE prod_desc NOT LIKE '%toy%'   # 我去 还真的是not like
ORDER BY prod_name;

SQL18 检索产品名称和描述（三）

SELECT prod_name,prod_desc
FROM Products
WHERE prod_desc LIKE '%toy%' AND prod_desc LIKE '%carrots%';

SQL19 检索产品名称和描述（四）

SELECT prod_name,prod_desc
FROM Products
WHERE prod_desc LIKE '%toy%carrots%' ;

---

06 创建计算字段

SQL20 别名

SELECT vend_id, vend_name AS vname,vend_address AS vaddress,vend_city AS vcity
FROM Vendors
ORDER BY vname;

SQL21 打折

SELECT prod_id,prod_price,prod_price*0.9 AS sale_price
FROM Products;

---

07 使用函数处理数据

SQL22 顾客登录名

SELECT cust_id, cust_name, 
UPPER(concat(SUBSTRING(cust_name,1,2),SUBSTRING(cust_city,1,3)))  AS user_login
FROM Customers;

SQL23 返回 2020 年 1 月的所有订单的订单号和订单日期

SELECT order_num,order_date
FROM Orders
WHERE YEAR(order_date) = 2020 AND MONTH(order_date) = 1
ORDER BY order_date;

---

08 汇总数据

SQL24 确定已售出产品的总数

SELECT SUM(quantity) AS items_ordered
FROM OrderItems;

SQL25 确定已售出产品项 BR01 的总数

SELECT SUM(quantity) AS items_ordered
FROM OrderItems
WHERE prod_id = 'BR01';

SQL26 确定 Products 表中价格不超过 10 美元的最贵产品的价格

SELECT MAX(prod_price) AS max_price
FROM Products
WHERE prod_price <=10;

---

分组数据

SQL27 返回每个订单号各有多少行数

SELECT order_num,COUNT(*) AS order_lines
FROM OrderItems
GROUP BY order_num
ORDER BY order_lines;

SQL28 每个供应商成本最低的产品

SELECT vend_id, MIN(prod_price) AS cheapest_item
FROM Products
GROUP BY vend_id
ORDER BY cheapest_item;

SQL29 返回订单数量总和不小于100的所有订单的订单号

SELECT order_num
FROM OrderItems
GROUP BY order_num   
HAVING SUM(quantity)>=100
ORDER BY order_num;

SQL30 计算总和

SELECT order_num, SUM(item_price *quantity)  AS total_price
FROM OrderItems
GROUP BY order_num
HAVING total_price>=1000
ORDER BY order_num;

SQL31 纠错3

SELECT order_num, COUNT(*) AS items 
FROM OrderItems 
GROUP BY order_num 
HAVING COUNT(*) >= 3 
ORDER BY items, order_num;

---

010 使用子查询

SQL32 返回购买价格为 10 美元或以上产品的顾客列表

SELECT DISTINCT cust_id
FROM Orders
WHERE order_num in(
    SELECT order_num
    FROM OrderItems
    WHERE item_price>=10
);

SQL33 确定哪些订单购买了 prod_id 为 BR01 的产品（一）

SELECT DISTINCT cust_id,order_date
FROM Orders
WHERE order_num in (
    SELECT order_num
    FROM OrderItems
    WHERE prod_id = 'BR01'
)
ORDER BY order_date;

SQL34 返回购买 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'
    )
);

SQL35 返回每个顾客不同订单的总金额

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;