1． 在UPDATE语句中使用子查询同时改变多行的值
UPDATE employee SET department=’Training’
WHERE department <> ‘Training’
AND sales < (SELECT AVG(sales) FROM employee
WHERE department <> ‘Training’)
2． 使用SELECT语句将一个表中的行插入另一表
INSERT INTO report
(customer_id,salesman_id,exchangemoney)
SELECT
customers. customer_id,salesman. salesman_id,money. exchangemoney
FROM
Customers,salesman,money
WHERE customers. customer_id=43 AND salesman_id=41
3． 快速删除表中所有的行
TRUNCATE TABLE employee
4． 使用UPDATE语句根据另一表中的值改变表的值
UPDATE employee SET status=’Key Manager’
WHERE employee_id IN
(SELECT salesperson_id FROM customer
WHERE customer_number IN
(SELECT customer_id FROM orders
GROUP BY customer_ID
HAVING SUM(order_total)>1000000))
5． 事务处理模型
BEGIN TRANSACTION
INSERT INTO trans_table VALUES (2,’sun’)
DELETE FROM trans_table WHERE row_number=4
DELETE FROM trans_table WHERE row_number=2
INSERT INTO trans_table VALUES (3,’fun’)
ROLLBACK
6． 多表查询
customer [cust_ID,name,address,salesrep]
employee [salesrep_ID,name]
SELECT cust_ID,customer.name,address,employee.name
FROM customer,employee
WHERE salesrep=salesrep_ID AND employee.name=’Alan’
7． 用UNION一次返回多个查询结果
SELECT ‘ABC’ AS ‘vendor’,item_no,item_desc,price FROM abc_product
SELECT ‘DEF’ AS ‘vendor’,item_no,item_desc,price FROM def_product
SELECT ‘GHI’ AS ‘vendor’,item_no,item_desc,price FROM ghi_product
ORDER BY item_no
列的数量相同，并且类型对应，不要内部使用ORDER BY
如果使用UNION ALL，则返回结果不会消除重复行
8． INTERSECT EXCEPT和UNION的区别
UNION：通过将两个表中的行合并，并清除重复的行产生结果表
INTERSECT：创建的是一个表与另一个表重复的行组成的结果表
EXCEPT：创建的是一个表与另一个表非重复的行组成的结果表
9． 使用IN和NOT IN判式选择项
UPDATE invoice SET sales_tax=invoice_total*0.07
WHERE ship_to_state IN (‘NV’,’CA’,’UT’,’TX’)
判断一行中是否有一个列值处于在一系列值中，类似于用OR
10． 找出某列空值的行
SELECT * FROM employee WHERE manager=NULL 错误
SELECT * FROM employee WHERE manager IS NULL 正确
11． 理解WHERE子句中的ALL
SELECT * FROM titles
WHERE
(SELECT SUM(qty_sold) FROM sales WHERE sales.isbn=title.isbn)
>
ALL (SELECT SUM(qty_sold) FROM sales WHERE sales.isbn=title.isbn)
12． 理解WHERE子句中的SOME和ANY
SELECT * FROM employee WHERE
sales>SOME (SELECT sales FROM employee WHERE office<>1)
只要有一个满足条件就可以
13． 理解WHERE子句中的UNIQUE
SELECT emp_ID,name FROM employee
WHERE UNIQUE
(SELECT salesperson FROM invoices WHERE invoice_date>=’
UNIQUE表示要么没有记录，要么只有一条记录。。。
14． 理解组合查询和GROUP BY
SELECT SUM(invoice_total) AS ‘Total Sales’, AVG(invoice_total) AS ‘Average Invoice’
FROM invoices
GROUP BY cust_ID
ORDER BY ‘Total Sales’
不加GROUP BY cust_ID是统计全部的总值和平均值，加GROUP BY cust_ID是统计每个顾客帐号的总值和平均值
15． 理解WHERE子句和HAVING子句之间的差别
HAVING 应该紧跟在GROUP BY子句之后
SELECT state,SUM(income) FROM customer
WHERE state IN (‘CA’,’NV’,’LA’)
GROUP BY state
HAVING SUM(income)<10000
16． 使用INNER JOIN选择一个表与另一表的行相关的所有行
SELECT * FROM table_a INNER JOIN table_b
ON(table_a.name= table_b.name)
Select * from table_a , table_b
Where table_a.name= table_b.name
