# 整行注释 (很少得到支持)
/* 多行
   注释
*/
SELECT ... -- 行内注释
FROM ...

-- 检索单个列
SELECT prod_name FROM products;
-- 检索多个列
SELECT prod_id, prod_name, prod_price FROM products;
-- 检索所有列
SELECT * FROM products;
-- 检索不同的值: DISTINCT 作用于 **所有的列**, 不仅仅是跟在其后的那一列
SELECT DISTINCT vend_id FROM products;

SELECT ...
FROM ...
ORDER BY            -- ORDER BY 总是 SELECT 语句中的 **最后一条子句**
    1,              -- 支持按相对位置排序. 支持混合使用列名和相对列位置
    prod_price DESC,-- 默认是升序 ASC, 指定 DESC 以降序. 只会应用到指定列
    prod_name       -- 用非检索的列排序数据是完全合法的

SELECT ... FROM ...
WHERE prod_price < 10;

SELECT ... FROM ...
WHERE cust_contact LIKE '[^JM]%'

SELECT
    quantity * item_price -- 算术计算: +, -, *, /
    AS expanded_price     -- 使用 AS 指定别名

FROM vendors;

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products;

SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2

-- 配合 IN 进行过滤
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
        -- 使用完全限定列名. 如果在 SELECT 语句中操作多个表, 就应使用完全限定列名避免歧义
        WHERE orders.cust_id = customer.cust_id) AS orders
FROM customers;

-- 等值联结 / 内联结: 基于两个表之间的相等测试
-- 1: 使用 WHERE 创建
SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
-- 2: 使用 inner JOIN 创建, ON 指定联结条件 (推荐)
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id

-- 外连接: 包含了在相关表中没有关联行的行
-- 1. 左外联结
SELECT customers.cust_id, orders.order_num
FROM customers LEFT outer JOIN orders
ON customers.cust_id = orders.cust_id
-- 2. 右外联结: RIGHT outer join
-- 3. 全外连接: FULL OUTER join (Access, MariaDB, MySQL, SQLite 不支持)

-- 自联结: 联结两个相同的表
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';

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

-- INSERT INTO: 插入一行完整行或部分列
INSERT INTO customers(cust_id, cust_name) -- 虽然括号和其内的列名能省略, 最好不要这么做
values('`0006', 'Toy Land');

-- INSERT SELECT: 插入多行检索出的数据
INSERT INTO customers(cust_id, cust_name)
SELECT cust_id, cust_name
FROM custnew;

-- SELECT INTO: 导出数据
SELECT * INTO custcopy -- 会创建一个 custcopy 表, 把 customers 所有内容复制到新表中
FROM customers;

UPDATE customers
SET cust_email = NULL
WHERE cust_id = '10005';

DELETE FROM customers
WHERE cust_id = '10006';

-- 1. 创建表
CREATE TABLE orderitems
(
    order_num INTEGER NOT NULL,
    order_item INTEGER NOT NULL,
    prod_id CHAR(10) NOT NULL,
    quantity INTEGER NOT NULL default 1, -- 指定默认值 (推荐)
    item_price DECIMAL(8,2) NOT NULL
)
-- 2. 更新表
-- 理想情况下, 不要在表中包含数据时对其进行更新
-- 应该在改动前做完整的备份
ALTER TABLE vendors
add vend_phone CHAR(20);

-- 3. 删除表
DROP TABLE custcopy;

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

-- 2. 使用
SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'RGAN01';

-- 3. 删除
DROP view productcustomers;

-- 1. 创建
-- DB2, MariaDB, MySQL, SQL Server
DECLARE custcursor CURSOR
FOR
SELECT * FROM customers;
-- Oracle, PostgreSQL
DECLARE CURSOR custcursor
IS
SELECT * FROM customers;

-- 2. 打开
OPEN CURSOR custcursor;

-- 3. 使用

-- 4. 关闭
CLOSE CUSTCURSOR

-- 1. 在表定义中
CREATE TABLE ordersitems
(
    -- 主键约束
    id INTEGER NOT NULL PRIMARY key,
    -- 唯一约束
    hash CHAR(10) NOT NULL unique,
    -- 检查约束
    quantity INTEGER NOT NULL CHECK (quantity > 0)
    -- 外键约束
    cust_id CHAR(10) NOT NULL REFERENCES customers(cust_id)
);
-- 2. 使用 ALTER table, ADD CONSTRAINT
ALTER TABLE orderitems
add CONSTRAINT PRIMARY KEY (id),
add CONSTRAINT unique (hash),
add CONSTRAINT FOREIGN KEY (cust_id) REFERENCES customers(cust_id)

CREATE INDEX prod_name_ind
ON products (prod_name);