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

 

posted @ 2012-03-10 09:59  方外老和尚  阅读(...)  评论(...编辑  收藏