*外联结--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