MSSQL扫盲系列(3)-SELECT
SELECT是个大话题，分成单独一篇
基本查找
View Code
SELECT * FROM USERS
--查询全部数据
SELECT * FROM USERS WHERE USERNAME LIKE 'K%'
--查询所有用户名以K打头用户的全部信息
SELECT TOP 1 * FROM USERS WHERE USERNAME LIKE 'K%'
--查询第一个用户名以K打头用户的全部信息
SELECT USERNAME,[PASSWORD],AGE=DATEDIFF(YEAR,BIRTHDAY,GETDATE()) FROM USERS WHERE USERNAME LIKE 'K%'
--DATEDIFF(YEAR,BIRTHDAY,GETDATE())可以用来求年龄
--查询所有用户名以K打头用户的部分信息
SELECT @@IDENTITY
--史上最短查询,用的非常多的查询
SELECT ROWID=IDENTITY(INT,1,1),USERNAME,[PASSWORD] INTO # FROM USERS
--带计数的查询
SELECT * FROM #
DROP TABLE #
条件
View Code
SELECT DISTINCT USERNAME FROM USERS
--获得不重复用户名
SELECT TOP 10 USERNAME FROM USERS
--TOP 是优化SQL的一个很好的选择
SELECT TOP 10 * FROM USERS WHERE ID NOT IN(SELECT TOP 30 ID FROM USERS)
--简单的SQL分页,使用IN，在有限的范围查找数据
SELECT * FROM USERS WHERE EXISTS(SELECT TOP 1 1 FROM AREA WHERE ID= [ADDRESS])
--使用EXISTS,判断是否存在
SELECT TOP 10 * FROM USERS WHERE BIRTHDAY BETWEEN '1800-1-1' AND '2000-1-1'
--使用BETWEEN AND 查询位于某范围的值
SELECT * FROM USERS WHERE USERNAME LIKE 'K%'
--使用LIKE,查询所有用户名以K打头用户的全部信息，
--具体通配符请转到 http://www.w3school.com.cn/sql/sql_wildcards.asp
排序
View Code
SELECT TOP 10 * FROM USERS
--数据原样
SELECT TOP 10 * FROM USERS ORDER BY USERNAME ASC
--顺序查找记录，ASC可以省略
SELECT TOP 10 * FROM USERS ORDER BY USERNAME DESC
--倒序查找
SELECT TOP 10 * FROM USERS ORDER BY USERNAME DESC,GENDER ASC
--使用用户名倒序，性别顺序(这个貌似说不过去，能懂就行)
分组
View Code
--###################################################
--GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组##
--###################################################
SELECT TOP 20 USERNAME FROM USERS GROUP BY USERNAME
SELECT TOP 20 USERNAME FROM USERS ORDER BY USERNAME
--这个GROUP BY 和ORDER BY USERNAME DESC 效果相同
--现在来个比较面试的，数据如下
CREATE TABLE #([NAME] NVARCHAR(50) NOT NULL,SALE INT NOT NULL)
--临时表，人名，工资
INSERT INTO # ([NAME],SALE) VALUES ('B',100)
INSERT INTO # ([NAME],SALE) VALUES ('B',1000)
INSERT INTO # ([NAME],SALE) VALUES ('A',5)
INSERT INTO # ([NAME],SALE) VALUES ('A',50)
INSERT INTO # ([NAME],SALE) VALUES ('C',2)
INSERT INTO # ([NAME],SALE) VALUES ('C',20)
INSERT INTO # ([NAME],SALE) VALUES ('B',1)
INSERT INTO # ([NAME],SALE) VALUES ('B',10)
INSERT INTO # ([NAME],SALE) VALUES ('A',500)
INSERT INTO # ([NAME],SALE) VALUES ('A',5000)
INSERT INTO # ([NAME],SALE) VALUES ('C',200)
INSERT INTO # ([NAME],SALE) VALUES ('C',2000)
--插入一些数据
SELECT * FROM #
--所有数据
SELECT SUM(SALE) S,[NAME] FROM # GROUP BY [NAME]
--获得每个人的总工资
SELECT [NAME] FROM # GROUP BY [NAME] HAVING SUM(SALE) >2000
--获取总工资大于2000的人名
SELECT NAME,SALE FROM # GROUP BY NAME,SALE
--按人名和工资分组
DROP TABLE #
JOIN
View Code
CREATE TABLE #(FK INT NOT NULL,FA CHAR(1) NOT NULL)
CREATE TABLE ##(FK INT NOT NULL,FA CHAR(1) NOT NULL)
--建了两个临时表，一个本地，一个全局
INSERT INTO # VALUES(1,'A')
INSERT INTO # VALUES(3,'B')
INSERT INTO # VALUES(4,'C')
SELECT * FROM #
INSERT INTO ## VALUES(1,'X')
INSERT INTO ## VALUES(2,'Y')
INSERT INTO ## VALUES(4,'Z')
SELECT * FROM ##
--插入数据
SELECT A.FK,A.FA,B.FK,B.FA FROM # A ,## B
SELECT A.FK,A.FA,B.FK,B.FA FROM # A CROSS JOIN ## B
--两种的写法不同，但是效果一样，都是求笛卡尔积
SELECT A.FK,A.FA,B.FK,B.FA FROM # A ,## B WHERE A.FK=B.FK
SELECT A.FK,A.FA,B.FK,B.FA FROM # A INNER JOIN ## B ON A.FK=B.FK
--两种的写法不同，但是效果一样，都是内部链接
SELECT A.FK,A.FA,B.FK,B.FA FROM # A LEFT JOIN ## B ON A.FK=B.FK
SELECT A.FK,A.FA,B.FK,B.FA FROM # A LEFT OUTER JOIN ## B ON A.FK=B.FK
--两种的写法不同，但是效果一样，都是左外连接
--此处获得数据条目和本地临时表A相同
SELECT A.FK,A.FA,B.FK,B.FA FROM # A RIGHT JOIN ## B ON A.FK=B.FK
SELECT A.FK,A.FA,B.FK,B.FA FROM # A RIGHT OUTER JOIN ## B ON A.FK=B.FK
--两种的写法不同，但是效果一样，都是右外连接
--此处获得数据条目和全局临时表B相同
SELECT A.FK,A.FA,B.FK,B.FA FROM # A FULL JOIN ## B ON A.FK=B.FK
SELECT A.FK,A.FA,B.FK,B.FA FROM # A FULL OUTER JOIN ## B ON A.FK=B.FK
--两种的写法不同，但是效果一样，都是全连接
--此处获得数据条目是AB两表中连接键的不重复总数
DROP TABLE #,##
UNION
View Code
CREATE TABLE #(FK INT NOT NULL,FA CHAR(1) NOT NULL)
CREATE TABLE ##(FK INT NOT NULL,FA CHAR(1) NOT NULL)
--还是两个临时表
INSERT INTO # VALUES(1,'A')
INSERT INTO # VALUES(3,'B')
INSERT INTO # VALUES(4,'C')
SELECT * FROM #
INSERT INTO ## VALUES(1,'A')
INSERT INTO ## VALUES(1,'X')
INSERT INTO ## VALUES(2,'Y')
INSERT INTO ## VALUES(4,'Z')
SELECT * FROM ##
--插入数据
--#########################################
--UNION 求并集,INTERSECT求交集，EXCEPT求差集######
--#########################################
SELECT * FROM # UNION SELECT * FROM ## ORDER BY FA
--合并两个SQL结果并消除重复记录，字段数要相等
SELECT * FROM # UNION ALL SELECT * FROM ## ORDER BY FA
--合并两个SQL结果不消除重复记录，字段数要相等
SELECT * FROM # INTERSECT SELECT * FROM ## ORDER BY FA
--求两个SQL结果的相同数据，字段数要相等
SELECT * FROM # EXCEPT SELECT * FROM ## ORDER BY FA
--求两个SQL结果的差集，即第一个结果中第二个结果没有的部分
DROP TABLE #,##
附加部分
CASE
View Code
CREATE TABLE #(GENDER BIT)
INSERT INTO # SELECT CASE
WHEN RAND()>0.5 THEN 0
ELSE 1
END
--第一个CASE
INSERT INTO # SELECT CASE WHEN RAND()>0.5 THEN 0 ELSE 1 END
INSERT INTO # SELECT CASE WHEN RAND()>0.5 THEN 0 ELSE 1 END
INSERT INTO # SELECT CASE WHEN RAND()>0.5 THEN 0 ELSE 1 END
INSERT INTO # SELECT CASE WHEN RAND()>0.5 THEN 0 ELSE 1 END
INSERT INTO # SELECT CASE WHEN RAND()>0.5 THEN 0 ELSE 1 END
INSERT INTO # SELECT CASE WHEN RAND()>0.5 THEN 0 ELSE 1 END
INSERT INTO # SELECT CASE WHEN RAND()>0.5 THEN 0 ELSE 1 END
--0男，1女
SELECT * FROM #
SELECT 性别= CASE
WHEN GENDER=0 THEN '男'
WHEN GENDER=1 THEN '女'
ELSE ''
END
FROM #
--第二个CASE
DROP TABLE #
WITH(更多，点击HERE)
为了能演示，这里新建了两张表，详细SQL如下
View Code
CREATE TABLE T1 (ID INT IDENTITY(1,1) PRIMARY KEY,[ADDRESS] NVARCHAR(3))
CREATE TABLE T2 (ID INT IDENTITY(1,1) PRIMARY KEY,
USERNAME NVARCHAR(10) NOT NULL,
ADDRESSID INT REFERENCES T1(ID)
)
--创建两个表，因为不能为临时表建立外键，所以必须是真实表
DECLARE @I INT
SET @I=0
WHILE @I<=100
BEGIN
INSERT INTO T1 VALUES(CHAR(65+RAND()*25)+CHAR(65+RAND()*25)+CHAR(65+RAND()*25))
SET @I=@I+1
END
--为第一个表插入数据
SET @I=0
WHILE @I<=200
BEGIN
INSERT INTO T2 VALUES(CHAR(65+RAND()*25)+CHAR(65+RAND()*25),1+CONVERT(INT,RAND()*100) )
SET @I=@I+1
END
--为第二个表插入数据
--DROP TABLE T2, T1
--删除做的准备
开始正题
View Code
--需要查询居住地地名[ADDRESS]以C开头的所有人信息
--从数据库的样子看，必须得从两个表里查了
--两个表JOIN一下么，OK
SELECT T2.* FROM T2 JOIN T1 ON T2.ADDRESSID=T1.ID WHERE T1.[ADDRESS] LIKE 'C%'
--结果出来了
--啊哈，还好有子查询，于是
SELECT T2.* FROM T2 WHERE T2.ADDRESSID IN ( SELECT ID FROM T1 WHERE T1.[ADDRESS] LIKE 'C%')
--先从T1中查找所有以C开头的地名主键
--再从它中查找出对于地名主键的人信息
--纳尼，还有其他方法，是的
--据目测，中间数据的结果不是很多,用表变量吧
DECLARE @T TABLE(ID INT)
--定义了表变量
INSERT INTO @T SELECT ID FROM T1 WHERE [ADDRESS] LIKE 'C%'
--先从T1中查找所有以C开头的地名主键,并放置到这个临时表里
SELECT * FROM T2 WHERE ADDRESSID IN (SELECT ID FROM @T)
--再从StateProvince查询出这些州代码所对应的州信息
--伟大的WITH现身
;WITH T AS (SELECT ID FROM T1 WHERE [ADDRESS] LIKE 'C%' )
--和上面的方法貌似很像
SELECT * FROM T2 WHERE ADDRESSID IN (SELECT ID FROM T)
--一样能查出
分页
View Code
--使用ID大小和TOP分页,最好的效率
SELECT TOP 10--页大小
USERNAME,[PASSWORD] --查询字段
FROM USERS --表名
WHERE ID >=(
SELECT ISNULL(MAX(ID),0) FROM (
SELECT TOP 30--页大小*页码-1 [10*(4-1)]
ID FROM USERS ORDER BY ID ) T --别名T必须
) ORDER BY ID
--使用ID和TOP分页,效率次之
SELECT TOP 10--页大小
USERNAME,[PASSWORD] --查询字段
FROM USERS --表名
WHERE ID NOT IN (
SELECT TOP 30--页大小*页码-1 [10*(4-1)]
ID FROM USERS --ORDER BY ID可选
) --ORDER BY ID可选
--使用ROW_NUMBER()
SELECT TOP 10 USERNAME,[PASSWORD] FROM
(SELECT ROW_NUMBER() OVER (ORDER BY ID) ROWID,USERNAME,[PASSWORD] FROM USERS)
T WHERE ROWID BETWEEN 30 AND 40
--子查询
;WITH T AS (SELECT ROW_NUMBER() OVER (ORDER BY ID) ROWID,USERNAME,[PASSWORD] FROM USERS)
SELECT TOP 10 USERNAME,[PASSWORD] FROM T WHERE ROWID >30
--WITH加简单条件
;WITH T AS (SELECT ROW_NUMBER() OVER (ORDER BY ID) ROWID,USERNAME,[PASSWORD] FROM USERS)
SELECT TOP 10 USERNAME,[PASSWORD] FROM T WHERE ROWID BETWEEN 30 AND 40
--WITH加BETWEEN
