MySQL必知必会笔记----表格部分（2/2）

1.联结表

• 等值联结（equijoin)/内联结（inner join)

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 INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;

• 联结多个表

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;
/*这个例子显示订单20007中的物品。订单物品存储在OrderItems表中。
每个产品按其产品 ID存储，它引用Products表中的产品。这些产品通
过供应商 ID联结到Vendors表中相应的供应商，供应商 ID存储在每个
产品的记录中*/

2.创建高级联结

• 自联结
  假如要给与 Jim Jones同一公司的所有顾客发送一封信件。这个查询要求首先找出 Jim Jones工作的公司，然后找出在该公司工作的顾

SELECT cust_id, cust_name, cust_contact
	FROM Customers
	WHERE cust_name = (SELECT cust_name
						FROM Customers
						WHERE cust_contact = 'Jim Jones');

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 Customers.cust_id, Orders.order_num
	FROM Customers LEFT OUTER JOIN Orders
	ON Customers.cust_id = Orders.cust_id;

• 全外联结（full outer join），它检索两个表中
  的所有行并关联那些可以关联的行。与左外联结或右外联结包含一个表
  的不关联的行不同，全外联结包含两个表的不关联的行

SELECT Customers.cust_id, Orders.order_num
	FROM Orders FULL OUTER JOIN Customers
	ON Orders.cust_id = Customers.cust_id;

3.组合查询

执行多个查询（多条 SELECT语句），并将结果作为一个查询结果集返回(UNION从查询结果集中自动去除了重复的行)如果想返回所有的匹配行，可使用UNION ALL而不是UNION。

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

4.插入数据

INSERT INTO Customers
VALUES('1000000006',
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA',
NULL,
NULL);

INSERT INTO Customers(cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email)
VALUES('1000000006',
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA',
NULL,
NULL);

• 想把另一表中的顾客列合并到Customers表中

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_contact,
	cust_email,
	cust_name,
	cust_address,
	cust_city,
	cust_state,
	cust_zip,
	cust_country
FROM CustNew;

• 从一个表复制到另一个表

CREATE TABLE CustCopy AS
SELECT * FROM Customers;

5.更新和删除数据

• 更新数据

UPDATE Customers
SET cust_contact = 'Sam Roberts',
cust_email = 'sam@toyland.com'
WHERE cust_id = '1000000006';

UPDATE Customers
SET cust_email = NULL
WHERE cust_id = '1000000005';

• 删除数据

DELETE FROM Customers
WHERE cust_id = '1000000006';

下面是许多 SQL程序员使用 UPDATE或 DELETE时所遵循的重要原则。

1. 除非确实打算更新和删除每一行，否则绝对不要使用不带 WHERE子句
   的 UPDATE或 DELETE语句。
2. 保证每个表都有主键（如果忘记这个内容，请参阅第 12课），尽可能
   像 WHERE子句那样使用它（可以指定各主键、多个值或值的范围）。
3. 在 UPDATE或 DELETE语句使用 WHERE子句前，应该先用 SELECT进
   行测试，保证它过滤的是正确的记录，以防编写的 WHERE子句不正确。
4. 使用强制实施引用完整性的数据库（关于这个内容，请参阅第 12课），
   这样 DBMS将不允许删除其数据与其他表相关联的行。
5. 有的 DBMS允许数据库管理员施加约束，防止执行不带 WHERE子句
   的 UPDATE或 DELETE语句。如果所采用的 DBMS支持这个特性，应
   该使用它。
6. 若是 SQL没有撤销（undo）按钮，应该非常小心地使用 UPDATE和 DELETE，
   否则你会发现自己更新或删除了错误的数据。

6.创建和操纵表

CREATE TABLE Products
(
prod_id CHAR(10) NOT NULL,
vend_id CHAR(10) NOT NULL,
prod_name CHAR(254) NOT NULL,
prod_price DECIMAL(8,2) NOT NULL,
prod_desc VARCHAR(1000) NULL
);

• 指定默认值

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

• 更新表

ALTER TABLE Vendors
ADD vend_phone CHAR(20);
//这条语句给 Vendors表增加一个名为 vend_phone的列，其数据类型为CHAR

ALTER TABLE Vendors
DROP COLUMN vend_phone;

• 删除表

DROP TABLE CustCopy;

7. SQL代码规范

• SQL代码规范
  SQL编程格式的优化建议
  SQL Style Guide