SQL必知必会要点记录（4/6）

SQL必知必会要点记录（4/6）

*外联结--LEFT OUTER JOIN:选择左侧表中的所有行；RIGHT OUTER JOIN:选择右侧表中的所有行
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 Customers FULL OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id ;

*聚集函数与联结
1.内联结与COUNT()
SELCET 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;

2.外联结与COUNT()
SELCET 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;

*组合查询之UNION
1.1 检索美国三个州的顾客信息：客户姓名、联系人和邮箱地址
SELECT cust_name , cust_contact , cust_email
FROM Customers
WHERE cust_state IN('IL','IN','MI');

1.2 检索客户Fun4All 的信息
SELECT cust_name , cust_contact , cust_email
FROM Customers
WHERE cust_name ='Fun4All';

2.组合查询：会删除重复记录
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';
2可等价于：
SELECT cust_name , cust_contact , cust_email
FROM Customers
WHERE cust_state IN('IL','IN','MI')
OR WHERE cust_name ='Fun4All';

*组合查询：保留重复记录，so easy
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';

*UNION之后加入排序
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 cust_name , cust_contact , cust_email;

*数据插入INSERT INTO(泛指行记录的插入)
INSERT INTO Customers
VALUES('10006',
    'Toy Land',
    '123 Any Street',
    'New York',
    'NY',
    '11111',
    'USA',
    NULL,
    NULL);
缺点：该种插入方式依赖于列的次序，在列次序存在变动可能性的情况下，该语句并不合适

*升级版INSERT INTO：不依赖原表列的顺序结构
INSERT INTO Customers(cust_id,
                cust_name,
                cust_address,
                cust_city,
                cust_state,
                cust_zip,
                cust_country,
                cust_contact,
                cust_email)
VALUES('10006',
    'Toy Land',
    '123 Any Street',
    'New York',
    'NY',
    '11111',
    'USA',
    NULL,
    NULL);

*升升级版INSERT INTO：不需要填充的列可以不写出
INSERT INTO Customers(cust_id,
                cust_name,
                cust_address,
                cust_city,
                cust_state,
                cust_zip,
                cust_country)
VALUES('10006',
    'Toy Land',
    '123 Any Street',
    'New York',
    'NY',
    '11111',
    'USA');

*INSERT SELECT:将CustNew表中的顾客数据合并（append）到Customers表中
INSERT INTO Customers(cust_id,
                cust_name,
                cust_address,
                cust_city,
                cust_state,
                cust_zip,
                cust_country,
                cust_contact,
                cust_email)
SELECT     cust_id,
    cust_name,
    cust_address
    cust_city,
    cust_state,
    cust_zip,
    cust_country,
    cust_contact,
    cust_email
FROM CustNew;

*表的复制SELECT INTO：创建CustCopy新表，将Customers表内容复制到CustCopy表中
SELECT *
INTO CustCopy
FROM Customers;

*更新与删除数据：UPDATE | DELETE
1.1更新客户电子邮件地址
UPDATE Customers
SET cust_email = 'kim@thetoystore.com'
WHERE cust_id = '10005';

1.2更新多个列
UPDATE Customers
SET cust_email = 'sam@toyland.com',
       cust_contact = 'Sam Roberts'
WHERE cust_id = '10006';

1.3删除某列的值
UPDATE Customers
SET cust_email = NULL
WHERE cust_id = '10005';

2.1删除行，删除列用UPDATE(1.3)
DELETE FROM Customers
WHERE cust_id = '10006';

2.2与2.1效果等价
TRUNCATE TABLE FROM Customers
WHERE cust_id = '10006';

*创建表CREATE TABLE