SQL必会必知复习笔记

-- 注释快捷键ctrl+/

-- 检索多个列
select prod_name, prod_id, prod_price
from products;

-- 检索所有列
select *
from products;

-- 检索出多个相同值
SELECT vend_id FROM Products;

-- 检索出多个相同值输出一个值
SELECT DISTINCT vend_id FROM Products;

-- 限制结果，返回一定行的数量

-- 1、在SQL Server 和 Access 使用SELECT时，使用TOP关键字来限制最多返回行
SELECT TOP 5 prod_name FROM Products;

-- 2、使用的是 DB2
SELECT prod_name
FROM Products
FETCH FIRST 5 ROWS ONLY;

-- 3、使用 Oracle，需要基于 ROWNUM（行计数器）来计算行
SELECT prod_name FROM Products WHERE ROWNUM <=5;

-- 4、使用 MySQL、 MariaDB、 PostgreSQL 或者 SQLite，需要使用 LIMIT子句
SELECT prod_name FROM products LIMIT 5;

-- 补充4，为了得到后面5行数据
SELECT prod_name FROM products LIMIT 5 OFFSET 5;

-- 3.1 排序数据
-- ORDER BY 子句取一个或多个列的名字，据此对输出进行排序
SELECT prod_name FROM products ORDER BY prod_name

-- 3.2 按多个列排序
-- 仅在多个行具有相同的 prod_price 值时才对产品按 prod_name 进行排序
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name;

-- 3.3 按列位置排序
SELECT prod_id, prod_price, prod_name FROM products ORDER BY 2, 3;

-- 3.4 指定排序方向
-- 默认升序，进行降序排序，必须指定 DESC 关键字
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;
-- 如果想在多个列上进行降序排序，必须对每一列指定 DESC 关键字

-- 第四课 过滤数据
-- 4.1 使用WHERE子句
SELECT prod_name, prod_price FROM products WHERE prod_price = 3.49;

-- 4.2 WHERE 子句操作符
-- 4.2.1 检查单个值
SELECT prod_name, prod_price FROM products WHERE prod_price < 10;
-- WHERE 与 ORDER BY 同时使用
SELECT prod_name, prod_price FROM products WHERE prod_price < 10 ORDER BY prod_price DESC;

-- 4.2.2 不匹配检查
SELECT vend_id, prod_name FROM products where vend_id <> 'DLL01';

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

-- 4.2.4 空值检查
SELECT prod_name FROM products WHERE prod_price IS NULL

SELECT cust_name FROM customers WHERE cust_email IS NULL;

-- 第5课 高级数据过滤
-- 5.1.1 AND操作符
SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id = 'DLL01' AND prod_price <= 4;

-- 5.1.2 OR操作符
SELECT prod_name, prod_price FROM products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

-- 5.1.3 求值顺序
-- SQL（像多数语言一样）在处理OR操作符前，优先处理 AND 操作符。输出不是预期效果。
SELECT prod_name,prod_price FROM products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' AND prod_price >= 10;

-- 此问题的解决方法是使用圆括号对操作符进行明确分组
SELECT vend_id, prod_name, prod_price FROM products WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >= 10;
-- 总结：任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符。

-- 5.2 IN操作符
SELECT prod_name, prod_price FROM products WHERE vend_id IN ( 'DLL01', 'BRS01' )
ORDER BY prod_price

-- 5.3 NOT操作符
SELECT vend_id, prod_name FROM products WHERE NOT vend_id = 'DLL01' ORDER BY prod_name;

-- 第6课 用通配符进行过滤
-- 6.1 LIKE 操作符
-- 6.1.1 百分号( % ) 通配符
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%';

-- 6.1.2 下划线 ( _ ) 通配符
-- 下划线的用途与%一样，但它只匹配单个字符，而不是多个字符。
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '__ inch teddy bear';

SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '% inch teddy bear';

-- 6.1.3 方括号 ( [] ) 通配符
-- 方括号（[]）通配符用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符。
-- 只有Access 和 SQL server支持该用法
SELECT cust_contact FROM customers WHERE cust_contact LIKE '[JM]%' ORDER BY cust_contact;

-- 第七课 创建计算字段
-- 7.2 拼接字段
-- Access 和 SQL Server 使用+号
SELECT vend_name + '(' + vend_country + ')' FROM vendors ORDER BY vend_name;

-- DB2、 Oracle、 PostgreSQL、 SQLite 和Open Office Base 使用||
SELECT vend_name || '(' || vend_country || ')' FROM vendors ORDER BY vend_name;

-- MySQL
SELECT CONCAT(vend_name, ' (', vend_country , ')') FROM vendors ORDER BY vend_name;

-- 使用别名
SELECT CONCAT(vend_name, ' (', vend_country, ')') AS vend_titie FROM vendors ORDER BY vend_name;

-- 7.3 执行算术计算
SELECT prod_id, quantity, item_price FROM orderitems WHERE order_num = 20008;

SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM orderitems WHERE order_num = 20008 ORDER BY expanded_price;

-- 第八课 使用函数处理数据
-- 不同数据库的函数不尽相同，可通过查阅文档使用
-- 8.2.1 文本处理函数
-- 常用的函数：LOWER(), UPPER(), LTRIM(), RTRIM()
SELECT vend_name, UPPER(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;

-- 8.2.2 日期和时间处理函数
SELECT order_num FROM orders where YEAR(order_date) = 2012;

-- 第九课 汇总数据
-- 9.1 聚集函数
-- 9.1.1 AVG()函数
SELECT AVG(prod_price) AS avg_price FROM products;

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

-- 9.1.2 COUNT()函数
SELECT COUNT(*) AS num_cust FROM customers

SELECT COUNT(cust_email) AS num_cust FROM customers

-- 9.1.3 MAX() 函数
SELECT MAX(prod_price) AS max_price FROM products

-- 9.1.4 MIN() 函数
SELECT MIN(prod_price) AS min_price FROM products;

-- 9.1.5 SUM()函数
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;

-- 9.2 聚集不同值
-- Microsoft Access 在聚集函数中不支持 DISTINCT，因此下面的例子不适合于 Access。
SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 'DLL01';

SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 'DLL01';
-- 对比二者间学习

-- 9.3 组合聚集函数
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;

-- 第10课 分组数据
-- 10.1 数据分组
SELECT COUNT( *) AS num_prods FROM products WHERE vend_id = 'DLL01';

-- 10.2 创建分组
SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;

SELECT vend_id,AVG(prod_price) as avg_price
FROM products GROUP BY vend_id;

-- 10.3 过滤分组
-- HAVING 用于分组过滤，WHERE 用于行过滤
SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;

-- 10.4 分组和排序
SELECT order_num, COUNT(*) AS items FROM orderitems GROUP BY order_num HAVING COUNT(*) >= 3;

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

-- 第十一课 使用子查询
-- 11.2 利用子查询进行过滤
SELECT order_num FROM orderitems WHERE prod_id = 'RGAN01';

SELECT cust_id FROM orders WHERE order_num IN (20007, 20008);

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 WHERE cust_id IN (SELECT cust_id FROM orders WHERE order_num in (SELECT order_num FROM orderitems WHERE prod_id = 'RGAN01'));

-- 注意：作为子查询的 SELECT 语句只能查询单个列

-- 11.3 作为计算字段使用子查询
SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name;

-- 第十二课 联结表
-- 12.2 创建联结
SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id;

SELECT vend_name, prod_name, prod_price FROM vendors, products;

-- 12.2.2 内联接
SELECT vend_name, prod_name, prod_price FROM vendors INNER join products ON vendors.vend_id = products.vend_id;

-- 12.2.3 联结多个表
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 操作的最有效方法
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));

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';

-- 第十三课 创建高级联结
-- 13.1 使用表别名
SELECT cust_name, cust_contact 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';

-- 注意：Oracle 中没有 ASOracle 不支持 AS 关键字。要在 Oracle 中使用别名，可以不用 AS，简单地指定列名即可（因此，应该是 Customers C， 而不是 Customers AS C）。

-- 13.2 使用不同类型的联结
-- 13.2.1 自联结
-- 使用子查询建立自联结
SELECT cust_id, cust_name, cust_contact FROM customers WHERE cust_name = (SELECT cust_name FROM customers WHERE cust_contact = 'Jim Jones');

-- 使用DBMS联结比处理子查询快。
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';

-- 13.2.2 自然联结
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 OI.prod_id = 'RGAN01';

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';

-- 13.2.3 外联结
SELECT customers.cust_id, orders.order_num FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id

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

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

-- Access、 MariaDB、 MySQL、 Open Office Base 和 SQLite 不支持 FULL OUTER JOIN 语法。
SELECT customers.cust_id, orders.order_num FROM orders FULL JOIN customers ON orders.cust_id = customers.cust_id;

-- 13.3 使用带聚集函数的联结
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 JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id;

-- 第十四课 组合查询
-- 14.2.1 使用UNION
SELECT cust_name, cust_contact, cust_email FROM customers WHERE cust_state IN ('IL', 'IN', 'MI');

SELECT cust_name, cust_contact, cust_email FROM customers WHERE cust_name = 'Fun4All';

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';

SELECT cust_name, cust_contact, cust_email FROM customers WHERE cust_state IN ('IL', 'IN', 'MI') OR cust_name ='Fun4All';

-- 14.2.4 对组合查询结果排序
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;

-- 第十五课 插入数据
-- 15.1 数据插入
INSERT INTO customers VALUES ('1000000006', 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA', NULL, NULL);

-- 15.1.3 插入检索出的数据
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_countact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_conntry FROM custnew

-- 15.2 从一个表复制到另一个表
SELECT * INTO custcopy FROM customers;

-- MariaDB、 MySQL、 Oracle、 PostgreSQL 和 SQLite使用的语法稍有不同
CREATE TABLE CustCopy AS
SELECT * FROM Customers;

SELECT * FROM custcopy

-- 第十六课 更新和删除数据
-- 16.1 更新数据
UPDATE customers
SET cust_email = 'kim@thetoystore.com'
WHERE cust_id = '1000000005';

-- 多行更新
UPDATE customers
SET cust_contact = 'Sam Roberts',
cust_email = 'sam@toyland.com'
WHERE cust_id = '1000000006';

-- 删除某个列的值，可设置它为NULL
UPDATE customers SET cust_email = NULL
WHERE cust_id = '1000000005';

SELECT * FROM customers;

-- 16.2 删除数据
DELETE FROM customers WHERE cust_id = '1000000006';
-- 如果省略 WHERE 子句，它将删除表中每个顾客

-- 第十七课 创建和操纵表
-- 17.1 创建表
-- 17.1.1 表创建基础
CREATE TABLE productsdemo
(
prod_id CHAR(10) NOT NULL,
vend_id CHAR(10) NOT NULL,
prod_name CHAR(10) NOT NULL,
prod_price DECIMAL(8,2) NOT NULL,
prod_desc VARCHAR(1000) NULL
);

-- 17.1.2 使用NULL值
-- 如果插入没有值的列，将返回错误，且插入失败
CREATE TABLE ordersdemo
(
order_num INTEGER NOT NULL,
order_date DATETIME NOT NULL,
cust_id CHAR(10) NOT NULL
);

-- 创建混合NULL和NOT NULL 列表
CREATE TABLE vendorsdemo
(
vend_id CHAR(10) NOT NULL,
vend_name CHAR(50) NOT NULL,
vend_address CHAR(50) ,
vend_city CHAR(50) ,
vend_state CHAR(5) ,
vend_zip CHAR(10) ,
vend_country CHAR(50)
);

-- 17.1.3 指定默认值
CREATE TABLE orderitemsdemo
(
order_num INTEGER NOT NULL,
order_item INTEGER NOT NULL,
prod_id CHAR(10) NOT NULL,
quatity INTEGER NOT NULL DEFAULT 1,
item_price DECIMAL(8,2) NOT NULL
);

-- 17.2 更新表
ALTER TABLE vendorsdemo
ADD vend_phone CHAR(20);

ALTER TABLE vendorsdemo
DROP COLUMN vend_phone;

DROP TABLE vendorsdemo;

-- 18.1 视图
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';

-- 18.2 创建视图
-- 18.2.1 利用视图简化复杂的联结
CREATE VIEW ProductCustomers 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 ProductCustomers;

SELECT * FROM ProductCustomers;
UPDATE ProductCustomers SET cust_contact = 'Jok' where prod_id = 'BR01';

SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';

-- 18.2.2 用视图重新格式化检索出的数据
SELECT CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country),')') AS vend_title FROM vendors ORDER BY vend_name;

CREATE VIEW vendorLocations AS SELECT CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country),')') FROM vendors;

SELECT * FROM vendorLocations;

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

SELECT * FROM CustomerEMailList;

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

CREATE VIEW OrderItemsExpanded AS SELECT order_num, prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM orderitems;

SELECT * FROM OrderItemsExpanded where order_num = 20008;

-- 创建存储过程
CREATE PROCEDURE productpricing()
BEGIN
SELECT AVG(prod_price) AS priceaverage FROM products;
END;

CALL productpricing();

-- 删除存储过程
DROP PROCEDURE productpricing;

-- 使用变量
CREATE PROCEDURE productpricing(
OUT pl DECIMAL(8,2),
OUT ph DECIMAL(8,2),
OUT pa DECIMAL(8,2)
)
BEGIN
SELECT MIN(prod_price)
INTO pl
FROM products;
SELECT MAX(prod_price)
INTO ph
FROM products;
SELECT AVG(prod_price)
INTO pa
FROM products;
END;

CALL productpricing ( @pricelow, @pricehigh, @priceaverage );

SELECT @priceaverage,@pricelow,@pricehigh;

-- 使用 IN 和 OUT 参数
CREATE PROCEDURE ordertotal(
IN onumber INT,
OUT ototal DECIMAL(8,2)
)
BEGIN
SELECT SUM(item_price*quantity) FROM orderitems WHERE order_num = onumber INTO ototal;
END;

CALL ordertotal(20005, @total);

SELECT @total;

CALL ordertotal(20009, @total);
SELECT @total;

DROP PROCEDURE ordertotal;

-- 23.3.5 建立智能存储过程
CREATE PROCEDURE ordertotal(
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
-- 声明局部变量 total, taxrare
DECLARE total DECIMAL(8,2);
DECLARE taxrare INT DEFAULT 6;

SELECT SUM(item_price*quantity) FROM orderitems
WHERE order_num = onumber INTO total;

IF taxable THEN
SELECT total+(total/100*taxrare) INTO total;
END IF;

SELECT total INTO ototal;
END;

CALL ordertotal(20005, 0, @total);
SELECT @total;

CALL ordertotal(20005, 1, @total);
SELECT @total;

-- 23.3.6 检查存储过程
SHOW CREATE PROCEDURE ordertotal;

-- 24章使用游标
-- 24.2.1 创建游标
CREATE PROCEDURE processorders
BEGIN
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
END;

-- 24.2.2 打开和关闭游标
OPEN ordernumbers;

CLOSE ordernumbers;

-- 24.2.3 使用游标数据
-- 创建processorders()先删除之前的例子
DROP PROCEDURE processorders;

CREATE PROCEDURE processorders()
BEGIN
DECLARE o INT;
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;

OPEN ordernumbers;

FETCH ordernumbers INTO o;

CLOSE ordernumbers;
END;

-- 完整的使用过程
SELECT * FROM orders;

CREATE PROCEDURE processorders()
BEGIN
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
DECLARE t DECIMAL(8,2);

DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

CREATE TABLE IF NOT EXISTS
ordertotals(order_num INT, total DECIMAL(8,2));

OPEN ordernumbers;

REPEAT
FETCH ordernumbers INTO o;
CALL ordertotal(o, 1, t);
INSERT INTO ordertotals(order_num, total)
VALUES(o, t);
UNTIL done END REPEAT;

CLOSE ordernumbers;
END;

CALL processorders();

SELECT * FROM ordertotals;

-- 使用触发器
-- 25.2 创建触发器
SELECT * FROM products;

CREATE TRIGGER newproduct AFTER INSERT ON products FOR
EACH ROW SELECT 'Product added' INTO @prod_add;

INSERT INTO products VALUES('BNBG04', 'DLL01', 'Rabbit bean bag toy', '3.49', 'Rabbit bean bag toy， comes with bean bag carrots');

SELECT @prod_add;

-- 25.3 删除触发器
DROP TRIGGER newproduct;

-- 25.4 使用触发器
-- 25.4.1 INSERT 触发器
CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num INTO @neworder_num;

DROP TRIGGER neworder;

CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num INTO @ASD;

INSERT INTO orders(order_date, cust_id) VALUES (NOW(), 10001);
SELECT @neworder_num;