--TOP关键字
USE NorthWind
GO
SELECT TOP 10 *
FROM customers
GO
--TOP integer PERCENT:用百分比表示返回行数
USE NorthWind
GO
SELECT TOP 10 PERCENT *
FROM customers
GO
--DISTINCT关键字(不重复的)
USE pubs
GO
SELECT DISTINCT country
FROM publishers
GO
USE pubs
GO
SELECT country
FROM publishers
GO
--LIKE子句 %(百分号):从0到n个任意字符;_(下划线):表示单个任意字符;[](封闭方括号):表示方括号里列出的任意一个字符
--[^]:任意一个没有在方括号里列出的字符。、
USE pubs
GO
SELECT au_lname+','+au_fname
FROM authors
WHERE au_fname LIKE 'D%'
GO
USE pubs
GO
SELECT au_lname,au_fname,phone,au_id
FROM authors
WHERE au_id LIKE '72_%'
GO
USE pubs
GO
SELECT au_lname,au_fname,phone,au_id
FROM authors
WHERE au_id LIKE '72[2345]_%'
GO
USE pubs
GO
SELECT au_lname,au_fname,phone,au_id
FROM authors
WHERE au_lname LIKE 'Dul[a-z]'
GO
--UNION逻辑联合
USE pubs
GO
SELECT pub_id
FROM publishers
UNION
SELECT title
FROM titles
WHERE price>$20
UNION
SELECT au_lname
FROM authors
WHERE au_lname LIKE 'D%'
GO
--GROUP BY子句和HAVING关键字
USE pubs
GO
SELECT type,avg(price) 'avg_price'
FROM titles
WHERE price>$10
GROUP BY type
HAVING AVG(price)>$18
GO
--EXISTS关键字
USE pubs
GO
SELECT title_id,au_id
FROM titleauthor
WHERE EXISTS
(SELECT * FROM authors WHERE authors.au_id=titleauthor.au_id AND state='CA')
ORDER BY title_id
GO