常用SQL 深入執行個體理解

 

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

UNION

SELECT ‘DEF’ AS ‘vendor’,item_no,item_desc,price FROM def_product

UNION

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>=’2005-2-2’ AND invoice_date<=’2005-12-3’)

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