SQL入门笔记（中）

主要内容：查询、联结、表与行列的增删

• 子查询

#类似于SELECT语句在IN中的嵌套，功能是查询某表某列和另表某列的关联
#在WHERE語句中的嵌套，可以被联结代替
SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
                    FROM OrderItems
                    WHERE prod_id = 'RGAN01'); 
/*先从OrderItems表查询RGAN01产品相关的订单编号，再从Order表中查询这些订单编号对应的顾客编号*/
/*注意事项：子查询的SELECT语句只能查询单列*/
#在SELECT首句中的嵌套
SELECT cust_name,
       cust_state,
       (SELECT COUNT(*)
        FROM Orders
        WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;
/*COUNT()对Orders表的orders列进行计数，并通过AS代替了orders列。子查询中，COUNT()表示的是Orders表中的某个cust_id对应Customers中多少个相同项。*/

• 内联结

#己见：所有信息汇总到一张表会造成信息赘余，可分主体造子表，每个子表都包含了各主体下各项的详情
#创建联结，一次性查询两张表的多列数据
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id -- 将供应商表中的供应商id和产品表中的联结起来
#另一种表达方式
SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products -- 供应商表“内联结”产品表
 ON Vendors.vend_id = Products.vend_id; -- 用ON不用HWERE
#多表联结
SELECT prod_name, vend_name, prod_price, quantity
FROM OrderItems, Products, Vendors
WHERE Products.vend_id = Vendors.vend_id
  AND OrderItems.prod_id = Vendors.vend_id
  AND order_num = 20007; -- 供应商表联结产品表，产品表联结订单项目表，然后加一个限制条件

• 其他联结方式

#自联结
SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers AS c1, Customers AS c2 -- 给表格起别名
WHERE c2.cust_contact = 'Jim Jones' -- 设置c2表只有contact为Jim Jone的记录
  AND c2.cust_name = c1.cust_name; -- c1和c2（相同的表）相联结，因为联结凭据是cust_name，所以调出的c1记录全是Jim Jone所在顾客公司的记录。
#自然联结（结果不会有重复项）
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
 AND OI.order_num = O.order_num
 AND prod_id = 'RGAN01';
/*对于其中一个表采用SELECT *即可，其他表的列依旧使用明确的子集来完成。一般内联结都是自然联结*/
#外联结
SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders -- 外联结。LEFT表示从左表选择cust_id所有项，此外联结表示顾客表中所有顾客及其订单情况。换成RIGHT，从右表选择cust_id所有项；换成FULL，从两表的cust_id列中找合集的所有项。
 ON Customers.cust_id = Orders.cust_id;
---
FROM Customers INNER JOIN Orders -- 内联结。从两表的cust_id列中找交集的所有项。
 ON Customers.cust_id = Orders.cust_id;
#联结两表，顺便数据透视
SELECT Customers.cust_id,
       COUNT(Orders.order_num) AS num_ord
FROM Customers INNER Orders
 ON Customers.cust_id = Orders.cust_id -- 内联结。也可换成其他形式。
GROUP BY Customers.cust_id;

• 复合查询

#多条SELECT语句合并在一起，查询结果会纵向合并成一个结果输出
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION -- 使用UNION时，所有重复行都会被删去；若要保留，则用UNION ALL。
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All'
ORDER BY cust_name, cust_contact;
/*以上可表达为一个SELECT语句，用OR把两个WHERE条件组合*/

• 插入（行）数据

#注：需要有特定安全权限
#插入完整行
INSERT INTO Customers(cust_id,
                      cust_name,
                      cust_address,
                      cust_city,
                      cust_state,
                      cust_zip,
                      cust_country,
                      cust_contact,
                      cust_email) -- 往Customers表中插入一行，括号内为列标题，这个括号可以删，内部顺序可调序
VALUES('100000000006',
       'Toy Land',
       'New York',
       'NY',
       '1111',
       'USA',
       NULL,
       NULL); -- 依据列标题的顺序匹配新添的值

#插入部分行
/*省略第一个括号中的cust_contact和cust_email和第二个括号中的两个NULL，即是插入部分*/
#已有表一，将表二的被筛选结果搬至表一
INSERT INTO Customers(cust_id,
                      cust_contact,
                      cust_email,
                      cust_name,
                      cust_addresss,
                      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 CustNew
WHERE cust_city = 'NY'; -- 从CustNew表SELECT并筛选出来的数据，插入到Customers表中

#创建一个新表，把老表数据全部搬至新表
SELECT * -- 也可以明确列
INTO CustCpoy
FROM Customers;

#联结多张老表，筛选数据创建新表
SELECT cust_id, prod_price, prod_id
INTO CustCopy
FROM Customers AS C, Products AS P
WHERE C.cust_id = P.cust_id;

• 篡改和删除（行）数据

#篡改数据
UPDATE Customers
SET cust_email = 'lianpeiwei19970317@gmail.com' -- feel free to contact me if you have any question
    cust_contact = 'Peiwei Lian'
    cust_country = NULL
WHERE cust_id = '100000000005'; -- 更新该编号的顾客公司的邮箱地址和联系人，并删去该公司所在国家信息，设为NULL

#删除行
DELETE FROM Customers
WHERE cust_id = '100000000006';

• 创建和删除表+增删列数据

#创建表
CREATE TABLE Products
(
  prod_id     CHAR(10)       NOT NULL, 
#CHAR(10)表示强制10位的字符数据格式
  vend_id     CHAR(10)       NOT NULL     DEFAULT '0000001',
#默认值设置为‘0000001’，如果缺值就显示该值
  order_time  DATETIME       NOT NULL     DEFAULT GETDATE(),
#DATETIME表示日期格式，默认值设置为系统日期
  prod_name   CHAR(254)      NOT NULL,
  prod_price  DECIMAL(8,2)   NOT NULL, 
#DECIMAL(8,2)表示8位整数+2位小数的数值数据格式
  prod_desc   VARCHAR(1000)  NULL 
#VARCHAR（1000）表示非强制但需小于等于1000位的字符数据格式；NULL表示允许该列没有值或缺值，NULL可以不写，因为是默认设置
);
/*其他常见数据格式：INTEGER整数 DATETIME日期格式；注：允许NULL值的列不能作为主键*/
#删除表
DROP TABLE Products
#重命名表
SQL Server用sp_rename命令[也可以直接在软件上修改]


#更新表
#增加列
ALTER TABLE Vendors -- 更新的表叫做Vendors
ADD vend_phone CHAR(20); -- 所有的DBMS都支持的操作，增加格式为CHAR(20)的vend_phone列
#删除列
ALTER TABLE Vendors
DROP COLUMN vend_phone;