T-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