4.24sql学习

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