[SQL]必知

#  单行注释, 这种形式有些DBMS不支持
-- 单行注释
/* 

多行注释

*/

-- 选择单个列
SELECT prod_name FROM products;

-- 选择多个列
SELECT prod_id, prod_name, prod_price FROM products

-- 选择所有列
SELECT * FROM products;

-- 返回列的所有数据, 有重复
SELECT vend_id FROM products;

-- 使用DISTINC限制只返回不同的数据, 无重复
SELECT DISTINCT vend_id FROM products;

-- DISTINC 作用于所用选择的列, 返回只要存在不相同的数据 
SELECT  vend_id, prod_price FROM products;
SELECT DISTINCT vend_id, prod_price FROM products;

-- MySQL 返回前三行
SELECT prod_name FROM products LIMIT 3;

-- OFFSET 限制从第二行往后开始选择三行
SELECT prod_name FROM products LIMIT 3 OFFSET 2;

-- 简化写法, 和上等同
SELECT prod_name FROM products LIMIT 2,3;

-- 1. 
SELECT cust_id FROM customers;

-- 2.
SELECT DISTINCT prod_id FROM orderitems; 

-- 3.
-- SELECT * FROM customers;
SELECT cust_id FROM customers; 

-- ORDER BY 子句 按字典顺序从小到大
SELECT prod_name FROM products ORDER BY prod_name;

-- 按多个列排序, 先按价格, 再按名称 
SELECT prod_id, prod_price, prod_name FROM products 
ORDER BY prod_price, prod_name;

-- 使用列的相对位置, 2, 3 表示先按第二列, 再按第三列, 和上等同
SELECT prod_id, prod_price, prod_name FROM products
ORDER BY 2, 3;

-- 使用 DESC (DESCENDING) 关键字 降序, 默认是 ASC (ASCENDING)升序, 可以不写
SELECT prod_id, prod_price, prod_name FROM products
ORDER BY prod_price DESC;

-- 多个列排序, 价格降序, 名字升序
SELECT prod_id, prod_price, prod_name FROM products
ORDER BY prod_price DESC, prod_name ASC;

-- 挑战题
-- 1.
SELECT cust_name FROM customers ORDER BY cust_name DESC;

-- 2.
SELECT cust_id, order_num, order_date FROM orders ORDER BY cust_id, order_date DESC;

-- 3.
SELECT order_item, item_price FROM orderitems ORDER BY order_item DESC, item_price DESC;

-- 4. 没写BY

-- WHERE子句
SELECT prod_name, prod_price From products 
WHERE prod_price = 3.49;

SELECT prod_name , prod_price FROM products 
WHERE prod_price < 10;

SELECT prod_name, prod_price FROM products
WHERE prod_price != 3.49;

-- BETWEEN AND 范围值检查
SELECT prod_name, prod_price FROM products
WHERE prod_price BETWEEN 5 AND 10;

-- IS NULL 空值检查
SELECT cust_name FROM customers 
WHERE cust_email IS NULL;

-- 1.
SELECT prod_id, prod_name FROM products 
WHERE prod_price = 9.49

-- 2.
SELECT prod_id, prod_name FROM products
WHERE prod_price >= 9;

-- 3.
SELECT DISTINCT order_num FROM orderitems 
WHERE order_num >= 100;

-- 4.
SELECT prod_name, prod_price FROM products
WHERE prod_price BETWEEN 3 AND 6 ORDER BY prod_price;

-- 添加多个过滤条件
SELECT prod_id, prod_price, prod_name FROM products
WHERE vend_id = 'DLL01' AND prod_price <= 4;

-- 满足其中一个过滤条件
SELECT prod_id, prod_price, prod_name FROM products 
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

-- IN 限定为值为数组内的数据, 也可以用OR实现
SELECT prod_name, prod_price FROM products
WHERE vend_id IN ('DLL01', 'BRS01') ORDER BY prod_name;

-- NOT 否定条件
SELECT prod_name FROM products 
WHERE NOT vend_id = 'DLL01' ORDER BY prod_name; 

-- 等同于
SELECT prod_name FROM products 
WHERE vend_id <> 'DLL01' ORDER BY prod_name;

-- 1. 
SELECT vend_name FROM vendors 
WHERE vend_country = 'USA' AND vend_state = 'CA';

-- 2.
SELECT order_num, prod_id, quantity FROM orderitems 
WHERE quantity >= 100 AND prod_id IN('BR01', 'BR02', 'BR03');

-- 3.
SELECT prod_name, prod_price FROM products 
WHERE prod_price >= 3 AND prod_price <= 6  ORDER BY prod_price;

-- 4. ORDER BY 应该放子句最后

-- '%' 通配符表示任何字符出现任意次数,包括0次, 比如匹配Fish开头
SELECT prod_id, prod_name FROM products 
WHERE prod_name LIKE 'Fish%';

-- 模式匹配中间为 特定字符串
SELECT prod_id, prod_name FROM products
WHERE prod_name LIKE '%bean bag%';

-- 模式匹配两端为 特定字符串
SELECT prod_id, prod_name FROM products
WHERE prod_name LIKE 'F%y';

-- '_' 匹配单个字符, 多少个 '_' 就匹配多少个字符
SELECT prod_id, prod_name FROM products 
WHERE prod_name LIKE '__ inch teddy bear';

-- SELECT cust_contact FROM customers
-- WHERE cust_contact LIKE '[^JM]%';
-- MySql 不支持 '[]'集合匹配

-- 1.
SELECT prod_name, prod_desc FROM products
WHERE prod_desc LIKE '%toy%' ORDER BY prod_name;

-- 2. 
SELECT prod_name, prod_desc FROM products
WHERE NOT prod_desc Like '%toy%' ORDER BY prod_name;

-- 3.
SELECT prod_name, prod_desc FROM products
WHERE prod_desc LIKE '%toy%' AND prod_desc LIKE '%carrots%';

-- or
SELECT prod_name, prod_desc FROM products
WHERE prod_desc LIKE '%toy%carrots%' OR prod_desc LIKE '%carrots%toy%';

-- 4.
SELECT prod_name, prod_desc FROM products 
WHERE prod_desc LIKE '%toy%carrots%';

-- CONCAT 函数拼接字段, 不同数据库实现不同SQL Server使用+号。DB2、Oracle、PostgreSQL 和 SQLite 使用||
SELECT CONCAT(vend_name, '(', vend_country, ')') 
FROM vendors ORDER BY vend_name;

-- AS 指定别名(alias)
SELECT CONCAT(vend_name, '(', vend_country, ')') AS vend_title
FROM vendors ORDER BY vend_name;

-- 计算新列 quantity*item_price 并取别名 expanded_price
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price
FROM orderitems WHERE order_num = 20008;

-- SELECT 语句 省略了 FORM 还可以仅访问和处理表达式
-- CURDATE() 函数返回日期
SELECT CURDATE();

-- RTRIM TRIM LTRIM 函数能去除空格 
SELECT TRIM('   ab  c   ');  --  'ab  c'

-- 挑战题
-- 1.
SELECT vend_id AS vid, 
vend_name AS vname, 
vend_address AS vaddress, 
vend_city AS vcity 
FROM vendors ORDER BY vname;

-- 2.
SELECT prod_id, prod_price, 
prod_price*0.9 AS sale_price 
FROM products;

-- UPPER() 
SELECT vend_name, UPPER(vend_name) AS vend_name_upcase FROM vendors 
ORDER BY vend_name;

-- SOUNDEX(),  对发音进行匹配
SELECT cust_name, cust_contact FROM customers 
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');


-- SUBSTR(str FROM pos FOR len)
SELECT CONCAT(SUBSTR("go000ogle", 1, 2), SUBSTR("go000ogle", 6, 4)) AS correct;
-- 这个函数下标从 1 开始左闭合取子串真的反常识, 第一次发现一门语言下标从1开始.

-- MySQL 中用 YEAR() 从字符串中提取年份, 不难想到还有 MONTH() DAY()...
-- 不同数据库提供了非常多而全的函数, 用的时候直接查就行了
SELECT order_num, order_date FROM orders 
WHERE YEAR(order_date) = '2020';

-- 计算squareroot, e^x
SELECT SQRT(16), EXP(1);

-- 挑战题
-- 1.
SELECT cust_id, cust_name, 
UPPER(CONCAT(SUBSTR(cust_contact FROM 1 FOR 2), SUBSTR(cust_city FROM 1 FOR 3)))AS user_login  FROM customers; 

-- or
SELECT cust_id, cust_name,
CONCAT(UPPER(LEFT(cust_contact, 2)), UPPER(LEFT(cust_city, 3))) AS user_login
FROM customers;

-- 2.
SELECT order_num, order_date FROM orders WHERE YEAR(order_date) = '2020' AND MONTH(order_date) = '1' ORDER BY order_date;

-- 对特定行求平均
SELECT AVG(prod_price) AS avg_price FROM products
WHERE vend_id = 'DLL01';

-- COUNT(*) 对表中行数计算, 不论是否值为 NULL
SELECT COUNT(*) AS num_cust FROM customers;

-- 对指定列统计个数, 会忽略NULL
SELECT COUNT(cust_email) AS num_cust FROM customers;

SELECT MAX(prod_price) AS max_price FROM products;

SELECT MIN(prod_price) AS min_price FROM products;

SELECT SUM(quantity) AS item_ordered FROM orderitems
WHERE order_num = 20005;

SELECT SUM(item_price*quantity) AS total_price FROM orderitems
WHERE order_num = 20005;

SELECT AVG(DISTINCT prod_price) AS avg_price FROM products
WHERE vend_id = 'DLL01';

SELECT COUNT(*) AS num_items,
MIN(prod_price) AS price_min,
MAX(prod_price) AS price_max,
AVG(prod_price) AS price_avg FROM products;

-- 挑战题
-- 1.
SELECT SUM(quantity) AS total_quantity FROM orderitems;

-- 2.
SELECT SUM(order_item) AS total_item_BRO1 FROM orderitems 
WHERE prod_id = 'BR01';

-- 3.
SELECT MAX(prod_price) AS max_price FROM products 
WHERE prod_price <= 10;

--  GROUP BY 子句分组 对每个组数据进行聚集
SELECT vend_id, COUNT(*) AS num_prods FROM products 
GROUP BY vend_id;

-- HVAING 过滤分组
SELECT cust_id, COUNT(*) AS orders FROM orders
GROUP BY cust_id HAVING COUNT(*) = 1;

-- 先用WHERE过滤行, 再用HAVING 过滤分组
SELECT vend_id, COUNT(*) AS num_prods FROM products 
WHERE prod_price >= 4 
GROUP BY vend_id HAVING COUNT(*) >= 2;

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

-- 挑战题
-- 1. 
SELECT order_num, COUNT(*) AS order_lines FROM orderitems
GROUP BY order_num ORDER BY order_lines;

-- 2.
SELECT vend_id, MIN(prod_price) AS cheapest_item FROM products
GROUP BY vend_id ORDER BY cheapest_item DESC;

-- 3.
SELECT order_num, SUM(quantity) AS total_quantity FROM orderitems
GROUP BY order_num HAVING SUM(quantity) >= 100 ORDER BY order_num;

-- 4.
SELECT order_num, SUM(item_price*quantity) AS total_price FROM orderitems
GROUP BY order_num HAVING total_price >= 1000;
-- GROUP BY clause must constains all nonaggregated column 

-- 5. 
-- GROUP BY clause must constains all nonaggregated column 

SELECT cust_name, cust_contact FROM customers
WHERE cust_id IN (
	SELECT cust_id FROM orders
	WHERE order_num IN (
		SELECT order_num FROM orderitems
		WHERE prod_id = 'RGAN01' 
	)
);

SELECT cust_name, cust_state, 
(SELECT COUNT(*) FROM orders 
WHERE orders.cust_id = customers.cust_id) AS orders FROM customers
ORDER BY cust_name;

-- orders 是一个计算字段，它是由圆括号中的子查询建立的。该子查询对检索出的每个顾客执行一次。
-- WHERE子句 比较Orders表中的cust_id和当前正从Customers表中检索的cust_id

-- 挑战题
-- 1.
SELECT cust_id FROM orders
WHERE order_num IN
	(SELECT order_num FROM orderitems 
	WHERE item_price >= 10);

-- 2. 
SELECT cust_id, order_date FROM orders 
WHERE order_num IN
-- 在orderitem中拿到'BR01'的 order_num
(SELECT order_num FROM orderitems 
WHERE prod_id = 'BR01')
ORDER BY order_date;

-- 3.
SELECT cust_id, 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'))

-- 4.
SELECT cust_id,
       (SELECT SUM(quantity * item_price)
        FROM OrderItems
        WHERE order_num IN (SELECT order_num
                            FROM Orders
                            WHERE Orders.cust_id = Customers.cust_id)) AS total_ordered
FROM Customers
ORDER BY total_ordered DESC;
-- 5.
SELECT prod_name, 
(SELECT SUM(quantity) FROM orderitems
WHERE orderitems.prod_id = products.prod_id
) AS quant_sold 
FROM products;

SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id;

-- 直接这么写会得到笛卡尔积, WHERE 子句是查找过程中的过滤条件
SELECT *
FROM vendors, products;

SELECT vend_name, prod_name, prod_price 
FROM vendors
INNER JOIN products ON vendors.vend_id = products.vend_id;

-- 与上面的WHERE建立联结效果相同, ANSI SQL 规范首选 INNER JOIN 语法.

SELECT prod_name, vend_name, prod_price, quantity 
FROM orderitems, products, vendors
WHERE products.vend_id = vendors.vend_id
AND orderitems.prod_id = products.prod_id
AND order_num = 20007;


-- 子查询和联结比较
SELECT cust_name, cust_contact 
FROM customers
WHERE cust_id IN (
	SELECT cust_id FROM orders
	WHERE order_num IN (
		SELECT order_num FROM orderitems
		WHERE prod_id = 'RGAN01'));

SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num
AND prod_id = 'RGAN01';

-- 挑战题
-- 1.
SELECT cust_name, order_num FROM customers, orders
WHERE customers.cust_id = orders.cust_id
ORDER BY cust_name, order_num;

SELECT cust_name, order_num FROM customers
INNER JOIN orders ON customers.cust_id = orders.cust_id
ORDER BY cust_name, order_num;

-- 2.
SELECT cust_name, order_num,  
(SELECT SUM(quantity*item_price) FROM orderitems 
WHERE orderitems.order_num = orders.order_num)
AS order_total FROM customers, orders
WHERE customers.cust_id = orders.cust_id
ORDER BY cust_name, order_num;

-- 时不我待
SELECT cust_name, orders.order_num, SUM(quantity*item_price) AS order_total 
FROM orders, orderitems, customers
WHERE customers.cust_id = orders.cust_id 
AND orders.order_num = orderitems.order_num
GROUP BY cust_name, orders.order_num
ORDER BY cust_name, orders.order_num;

-- 这里的 GROUP BY 中 cust_name 其实可以不加
-- 可以这么理解:

-- a. 先进行笛卡尔积
SELECT *
FROM orders, orderitems, customers
WHERE customers.cust_id = orders.cust_id 
AND orders.order_num = orderitems.order_num
-- b. 执行 WHERE 子句的过滤联结条件
WHERE customers.cust_id = orders.cust_id 
AND orders.order_num = orderitems.order_num
-- c. 然后按cust_name 和 order_num 分组, 不分先后 由于order_num是结果中的主键, 按这种分组方式可映射多个 cust_name, 所以cust_name可有可无
GROUP BY cust_name, orders.order_num
-- d. 返回选择的行, SUM()函数负责计算每个分组中的值
SELECT cust_name, orders.order_num, SUM(quantity*item_price) AS order_total 


-- 3.
SELECT order_date, cust_id
FROM orders, orderitems
WHERE orders.order_num = orderitems.order_num
AND prod_id = 'BR01'
ORDER BY order_date;

SELECT order_date, cust_id
FROM orders
INNER JOIN orderitems 
ON orderitems.order_num = orders.order_num
AND prod_id = 'BR01'
ORDER BY order_date;
-- 有点意思

-- 4.
SELECT customers.cust_id, cust_email 
FROM customers
INNER JOIN orders ON orders.cust_id = customers.cust_id
INNER JOIN orderitems ON orderitems.order_num = orders.order_num 
WHERE prod_id = 'BR01';

-- 5.
SELECT cust_name, SUM(item_price*quantity) AS total_price
FROM orderitems
INNER JOIN orders ON orders.order_num = orderitems.order_num
INNER JOIN customers ON customers.cust_id = orders.cust_id
GROUP BY cust_name HAVING SUM(item_price*quantity) >= 1000;


SELECT cust_name, SUM(item_price*quantity) AS total_price
FROM orderitems, orders, customers
WHERE orders.order_num = orderitems.order_num 
AND customers.cust_id = orders.cust_id
GROUP BY cust_name HAVING SUM(item_price*quantity) >= 1000;

SELECT cust_name, cust_contact
-- 给表起 别名 AS, ORACLE的DB中可以不加AS
FROM customers AS C, orders AS O, orderitems AS OI
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01';

-- 使用自联结
SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM customers AS c1, customers AS c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';

-- OR 使用标准的内联结自联结
SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM customers AS c1 
INNER JOIN customers AS c2 ON c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';

-- 相同效果子查询, 在一张表中, 查找 cust_contact为某值的cust_name, 再由cust_name查找对应的信息
SELECT cust_id, cust_name, cust_contact
FROM customers
WHERE cust_name = 
(SELECT cust_name FROM customers 
WHERE cust_contact = 'Jim Jones');

SELECT C.*, O.order_num, O.order_date, OI.prod_id, OI.quantity, OI.item_price
FROM customers AS C, orders AS O, orderitems AS OI
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01';

-- 等同于使用 JOIN
SELECT customers.*, orders.order_num, orders.order_date, orderitems.prod_id,orderitems.quantity, orderitems.item_price
FROM customers
JOIN orders ON customers.cust_id = orders.cust_id
JOIN orderitems ON orders.order_num = orderitems.order_num
AND prod_id = 'RGAN01';

-- 外连接, LEFT/RIGHT 指定包括其所有行的表
-- 比如这里的 LEFT 指定的就是保留左边的表 customers
SELECT customers.cust_id, orders.order_num
FROM customers
LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;

-- 上面等同于
SELECT customers.cust_id, orders.order_num
FROM orders
RIGHT OUTER JOIN customers ON customers.cust_id = orders.cust_id;

-- 可以比较下区别, 就算左边没有匹配条件的行也会保留, 右表的值置NULL
SELECT customers.cust_id, orders.order_num, customers.cust_id, orders.cust_id
FROM customers, orders ORDER BY customers.cust_id;

SELECT customers.cust_id, orders.order_num
FROM customers
INNER JOIN orders ON customers.cust_id = orders.cust_id;

SELECT customers.cust_id, COUNT(orders.order_num) AS num_ord
FROM customers
INNER JOIN orders ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

SELECT customers.cust_id, COUNT(orders.order_num) AS num_ord
FROM customers
LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

-- 挑战题
-- 1.
SELECT cust_name, order_num 
FROM customers
INNER JOIN orders 
ON customers.cust_id = orders.cust_id;

-- 2. 
SELECT cust_name, order_num 
FROM customers
LEFT OUTER JOIN orders 
ON customers.cust_id = orders.cust_id;

-- 3.
SELECT prod_name, order_num 
FROM products
LEFT OUTER JOIN orderitems
ON products.prod_id = orderitems.prod_id;

-- 4.
SELECT prod_name, COUNT(order_num) AS orders 
FROM products
LEFT OUTER JOIN orderitems
ON products.prod_id = orderitems.prod_id
GROUP BY prod_name
ORDER BY prod_name;

-- 5.
SELECT vendors.vend_id, COUNT(prod_id)
FROM vendors
LEFT OUTER JOIN products 
ON products.vend_id = vendors.vend_id
GROUP BY vendors.vend_id;

-- UNION 将两条SELECT的查询结果合并成一条
-- 等同于多个条件的 WHERE, 性能可能分情况有差别 
SELECT cust_name, cust_contact, cust_email
FROM customers
WHERE cust_state IN ('IL', 'IN', 'MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM customers
WHERE cust_name = 'Fun4ALL';

-- UNION ALL 不取消重复行
SELECT cust_name, cust_contact, cust_email
FROM customers
WHERE cust_state IN ('IL', 'IN', 'MI')
UNION ALL
SELECT cust_name, cust_contact, cust_email
FROM customers
WHERE cust_name = 'Fun4All';

-- 组合查询只允许一条 ORDER BY, 放最后排序整个结果集
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All'
ORDER BY cust_name, cust_contact;

-- 挑战题
-- 1.
SELECT prod_id, quantity FROM orderitems
WHERE quantity = 100
UNION
SELECT prod_id, quantity FROM orderitems
WHERE prod_id LIKE 'BNBG%'
ORDER BY prod_id;

-- 2.
SELECT prod_id, quantity FROM orderitems
WHERE quantity = 100
ORDER BY prod_id;

SELECT prod_id, quantity FROM orderitems
WHERE prod_id LIKE 'BNBG%'
ORDER BY prod_id;

-- 3.
SELECT prod_name
FROM products
UNION
SELECT cust_name
FROM customers
ORDER BY prod_name;
-- that's nonsensial indeed, cust_name list in the list of prod_name

-- 4. ORDER BY 只能存在一个放在最后 

-- 插入一行, VALUES数组按模式顺序填充
INSERT INTO customers 
VALUES(1000000006,
	  'Slacr Land',
	  'Fuck Street',
	  'New York',
	  'NY',
	  '11111',
	  'USB',
	  NULL,
	  NULL);
			
-- 更安全的方法, 给出明确的列名, 次序可以变更			
INSERT INTO customers(cust_name,
					 cust_id,
					 cust_address,
					 cust_city,
					 cust_state,
					 cust_zip,
					 cust_country,
					 cust_contact,
					 cust_email)
VALUES('Slacr Land',
       1000000006,
       'Fuck Street',
       'New York',
       'NY',
       '11111',
       'USA',
       NULL,
       NULL);

INSERT INTO Customers(cust_id,
					  cust_name,
					  cust_address,
					  cust_city,
					  cust_state,
					  cust_zip,
					  cust_country)
VALUES(1000000006,
 	   'Toy Land',
 	   '123 Any Street',
 	   'New York',
 	   'NY',
 	   '11111',
 	   'USA');

 -- 选择插入, 从 cust_new 表中选择数据插入customer表
 INSERT INTO customers(cust_id,
                       cust_contact,
                       cust_email,
                       cust_name,
                       cust_address,
                       cust_city,
                       cust_state,
                       cust_zip,
                       cust_country)
SELECT cust_id,
       cust_contact,
       cust_email,
       cust_name,
       cust_address,
       cust_city,
       cust_state,
       cust_zip,
       cust_country
FROM cust_new;

-- 创建新表, 用原有表的数据, 可以使用更多条件的SELECT
CREATE TABLE cust_copy AS SELECT * FROM customers;

-- SQL SERVER
SELECT * INTO CustCopy FROM Customers;

-- 挑战题
-- 1. 
INSERT INTO customers(cust_name,
					  cust_id,
					  cust_address,
					  cust_city,
					  cust_state,
					  cust_zip,
					  cust_country,
					  cust_contact,
					  cust_email)
VALUES('slacr',
	   '001',
		 'CY',
		 'CQ',
		 'NAN',
		 '1024',
		 'CN',
		 'hacr',
		 'xxx');
			 			 
 -- 2.
CREATE TABLE backup_orders AS SELECT * FROM orders;
CREATE TABLE backup_orderitems AS SELECT * FROM orderitems;

UPDATE customers 
SET cust_email = 'shit@shool.edu'
WHERE cust_id = 1000000005;

-- 更新多个列
UPDATE customers 
SET cust_contact = 'slacr',
	cust_email = NULL
WHERE cust_id = 1000000006;

-- 删除指定条目
DELETE FROM customers 
WHERE cust_id = 'slacr';

-- 省略 WHERE 子句会删除所有

-- 挑战题
-- 1.
UPDATE customers 
SET cust_state = UPPER(cust_state);
UPDATE vendors 
SET vend_state = UPPER(vend_state);

-- 2.
SELECT * FROM customers WHERE cust_id = 1000000006;

DELETE FROM customers 
WHERE cust_id = 1000000006;

CREATE TABLE products
(
	prod_id		CHAR(10)		NOT NULL,
	vend_id   	CHAR(10)		NOT NULL,
	prod_name 	CHAR(254)		NOT NULL,
	prod_price 	DECIMAL(8,2) 	NOT NULL	DEFAULT 1,
	prod_desc  	VARCHAR(1000)	NULL
	-- NULL 可以不写为默认值, 设置了NOT NULL 限定的列不可为空, 插入时也必须给值
	-- DEFAULT 设置默认值
)

ALTER TABLE vendors
ADD vend_phone CHAR(20);

ALTER TABLE vendors 
DROP COLUMN vend_phone;

DROP TABLE cust_copy;

ALTER TABLE backup_orders RENAME copy_orders;

-- 挑战题
ALTER TABLE vendors
ADD COLUMN vend_web VARCHAR(1000);

UPDATE vendors 
SET vend_web = 'www.slacr.site'
WHERE vend_country = 'USA';

-- 利用视图简化复杂联结
CREATE VIEW prod_cust 
AS SELECT cust_name, cust_contact, prod_id
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num;

-- 删除视图
DROP VIEW prod_cust;

-- 对视图检索
SELECT cust_name, cust_contact 
FROM prod_cust
WHERE prod_id = 'RGAN01';

-- 使用视图重新格式化选择出来的数据
-- 在SELECT前面补上CREATE VIEW 语句就行了
CREATE VIEW vendor_locations AS 
SELECT CONCAT(vend_name, '  [ ', vend_country, ' ]')
AS vend_title
FROM vendors;

-- 使用视图过滤不想要的数据
CREATE VIEW cust_email_list AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;

SELECT * FROM cust_email_list;

-- 使用视图与计算字段
CREATE VIEW order_items_expanded AS
SELECT prod_id, quantity, item_price, quantity*item_price 
AS expanded_price FROM orderitems
WHERE order_num = 20008;

SELECT * FROM order_items_expanded;

-- 挑战题
-- 1.
CREATE VIEW cust_with_orders AS
SELECT customers.* FROM customers
JOIN orders ON customers.cust_id = orders.cust_id;

SELECT * FROM cust_with_orders;

-- 2. MYSQL中没问题, 但有些数据库创建VIEW不能加ORDER BY字段, 可以创建后再检索视图

CREATE PROCEDURE IF NOT EXISTS HighPriceProd(IN price DECIMAL(8, 2))
BEGIN 
	SELECT prod_id, prod_price FROM products
	WHERE prod_price >= price
	ORDER BY prod_price;
END
-- 删除
DROP PROCEDURE HighPriceProd;
-- 调用
CALL HighPriceProd(5);

--
CREATE PROCEDURE SetPrice(IN pid CHAR(10), IN price DECIMAL(8,2))
BEGIN 
	UPDATE products
	SET prod_price = price WHERE prod_id = pid;
	SELECT prod_id, prod_price FROM products;
END

DROP PROCEDURE SetPrice;
CALL SetPrice('BR01', 100);

-- 利用 @ 标识全局变量
SET @price = 200, @pid = 'BR01';
CALL SetPrice(@pid, @price);

-- OUT 输出参数
CREATE PROCEDURE outTest(OUT test INT)
BEGIN 
	SELECT test;
	SET test = 99;
	SELECT test;
END

SET @test = 111;  
CALL outTest(@test);   -- NULL, 不接受参数

SELECT @test; -- 99, 调用存储过程

-- 创建存储函数
CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) 
DETERMINISTIC
RETURN CONCAT('Hello,', s, '!');

SELECT hello('world');

DROP FUNCTION hello;

INSERT INTO orders VALUES( 200010, '2023-02-08 00:00:00', '1000000001');

START TRANSACTION;
SAVEPOINT del_1;
DELETE FROM orders WHERE order_num = 200010;
-- ROLLBACK;  -- ROLLBACK可以撤销上一句SQL
INSERT INTO orders VALUES( 200111, '2023-02-10 00:00:00', '1000000001');
ROLLBACK TO del_1;   -- 回退到保存点
COMMIT;
-- 只有COMMIT结束 才会完成整个TRANSACTION, 默认自动COMMIT


-- By default, MySQL runs with autocommit mode enabled. 
-- SET autocommit = {0|1}

CREATE PROCEDURE test()
BEGIN 
    DECLARE str CHAR(50); 
    DECLARE name_cursor CURSOR FOR SELECT cust_name FROM customers;

    OPEN name_cursor;

    read_loop: LOOP 
        FETCH name_cursor INTO str;
        IF str LIKE "%T%" THEN
            LEAVE read_loop;
        END IF;
    END LOOP;

    CLOSE name_cursor;

    SELECT * FROM customers WHERE cust_name = str;
END

CALL test();