SQL Server Transact-SQL高级查询

高级查询在数据库中用得是最频繁的，也是应用最广泛的。

Ø  基本常用查询

[sql]  view plain copy print ?

1. --select  
2. select * from student;  
3.   
4. --all 查询所有  
5. select all sex from student;  
6.   
7. --distinct 过滤重复  
8. select distinct sex from student;  
9.   
10. --count 统计  
11. select count(*) from student;  
12. select count(sex) from student;  
13. select count(distinct sex) from student;  
14.   
15. --top 取前N条记录  
16. select top 3 * from student;  
17.   
18. --alias column name 列重命名  
19. select id as 编号, name '名称', sex 性别 from student;  
20.   
21. --alias table name 表重命名  
22. select id, name, s.id, s.name from student s;  
23.   
24. --column 列运算  
25. select (age + id) col from student;  
26. select s.name + '-' + c.name from classes c, student s   
27. where s.cid = c.id;  
28.   
29. --where 条件  
30. select * from student where id = 2;  
31. select * from student where id > 7;  
32. select * from student where id < 3;  
33. select * from student where id <> 3;  
34. select * from student where id >= 3;  
35. select * from student where id <= 5;  
36. select * from student where id !> 3;  
37. select * from student where id !< 5;  
38.   
39. --and 并且  
40. select * from student where id > 2 and sex = 1;  
41.   
42. --or 或者  
43. select * from student where id = 2 or sex = 1;  
44.   
45. --between ... and ... 相当于并且  
46. select * from student where id between 2 and 5;  
47. select * from student where id not between 2 and 5;  
48.   
49. --like 模糊查询  
50. select * from student where name like  '%a%';  
51. select * from student where name like  '%[a][o]%';  
52. select * from student where name not like  '%a%';  
53. select * from student where name like 'ja%';  
54. select * from student where name not like '%[j,n]%';  
55. select * from student where name like '%[j,n,a]%';  
56. select * from student where name like '%[^ja,as,on]%';  
57. select * from student where name like '%[ja_on]%';  
58.   
59. --in 子查询  
60. select * from student where id in (1, 2);  
61.   
62. --not in 不在其中  
63. select * from student where id not in (1, 2);  
64.   
65. --is null 是空  
66. select * from student where age is null;  
67.   
68. --is not null 不为空  
69. select * from student where age is not null;  
70.   
71. --order by 排序  
72. select * from student order by name;  
73. select * from student order by name desc;  
74. select * from student order by name asc;  
75.   
76. --group by 分组  
77. 按照年龄进行分组统计  
78. select count(age), age from student group by age;  
79. 按照性别进行分组统计  
80. select count(*), sex from student group by sex;  
81. 按照年龄和性别组合分组统计，并排序  
82. select count(*), sex from student group by sex, age order by age;  
83. 按照性别分组，并且是id大于2的记录最后按照性别排序  
84. select count(*), sex from student where id > 2 group by sex   
85. order by sex;  
86. 查询id大于2的数据，并完成运算后的结果进行分组和排序  
87. select count(*), (sex * id) new from student where id > 2   
88. group by sex * id order by sex * id;  
89.   
90. --group by all 所有分组  
91. 按照年龄分组，是所有的年龄  
92. select count(*), age from student group by all age;  
93.   
94. --having 分组过滤条件  
95. 按照年龄分组，过滤年龄为空的数据，并且统计分组的条数和现实年龄信息  
96. select count(*), age from student group by age having age is not null;  
97.   
98. 按照年龄和cid组合分组，过滤条件是cid大于1的记录  
99. select count(*), cid, sex from student group by cid, sex   
100. having cid > 1;  
101.   
102. 按照年龄分组，过滤条件是分组后的记录条数大于等于2  
103. select count(*), age from student group by age   
104. having count(age) >= 2;  
105.   
106. 按照cid和性别组合分组，过滤条件是cid大于1，cid的最大值大于2  
107. select count(*), cid, sex from student group by cid, sex   
108. having cid > 1 and max(cid) > 2;  

 Ø  嵌套子查询

       子查询是一个嵌套在select、insert、update或delete语句或其他子查询中的查询。任何允许使用表达式的地方都可以使用子查询。子查询也称为内部查询或内部选择，而包含子查询的语句也成为外部查询或外部选择。

       # from (select … table)示例

[sql]  view plain copy print ?

1. 将一个table的查询结果当做一个新表进行查询  
2. select * from (  
3. select id, name from student where sex = 1  
4. ) t where t.id > 2;  

上面括号中的语句，就是子查询语句（内部查询）。在外面的是外部查询，其中外部查询可以包含以下语句：

       1、 包含常规选择列表组件的常规select查询

       2、 包含一个或多个表或视图名称的常规from语句

       3、 可选的where子句

       4、 可选的group by子句

       5、 可选的having子句

       #  示例

[sql]  view plain copy print ?

1. 查询班级信息，统计班级学生人生  
2. select *,   
3. (select count(*) from student where cid = classes.id) as num   
4. from classes order by num;  

       #  in, not in子句查询示例

[sql]  view plain copy print ?

1. 查询班级id大于小于的这些班级的学生信息  
2. select * from student where cid in (  
3.     select id from classes where id > 2 and id < 4  
4. );  
5.   
6. 查询不是班的学生信息  
7. select * from student where cid not in (  
8.     select id from classes where name = '2班'  
9. )  

            in、not in 后面的子句返回的结果必须是一列，这一列的结果将会作为查询条件对应前面的条件。如cid对应子句的id；

       # exists和not exists子句查询示例

[sql]  view plain copy print ?

1. 查询存在班级id为的学生信息  
2. select * from student where exists (  
3. select * from classes where id = student.cid and id = 3  
4. );  
5.   
6. 查询没有分配班级的学生信息  
7. select * from student where not exists (  
8. select * from classes where id = student.cid  
9. );  

       exists和not exists查询需要内部查询和外部查询进行一个关联的条件，如果没有这个条件将是查询到的所有信息。如：id等于student.id；

       #  some、any、all子句查询示例

[sql]  view plain copy print ?

1. 查询班级的学生年龄大于班级的学生的年龄的信息  
2. select * from student where cid = 5 and age > all (  
3. select age from student where cid = 3  
4. );  
5.   
6. select * from student where cid = 5 and age > any (  
7. select age from student where cid = 3  
8. );  
9.   
10. select * from student where cid = 5 and age > some (  
11. select age from student where cid = 3  
12. );  

Ø  聚合查询

       1、 distinct去掉重复数据

[sql]  view plain copy print ?

1. select distinct sex from student;  
2. select count(sex), count(distinct sex) from student;  

       2、 compute和compute by汇总查询

[sql]  view plain copy print ?

1. 对年龄大于的进行汇总  
2. select age from student   
3. where age > 20 order by age compute sum(age) by age;  
4.   
5. 对年龄大于的按照性别进行分组汇总年龄信息  
6. select id, sex, age from student   
7. where age > 20 order by sex, age compute sum(age) by sex;  
8.   
9. 按照年龄分组汇总  
10. select age from student   
11. where age > 20 order by age, id compute sum(age);  
12.   
13. 按照年龄分组，年龄汇总，id找最大值  
14. select id, age from student   
15. where age > 20 order by age compute sum(age), max(id);  

       compute进行汇总前面是查询的结果，后面一条结果集就是汇总的信息。compute子句中可以添加多个汇总表达式，可以添加的信息如下：

              a、 可选by关键字。它是每一列计算指定的行聚合

              b、 行聚合函数名称。包括sum、avg、min、max、count等

              c、 要对其执行聚合函数的列

              compute by适合做先分组后汇总的业务。compute by后面的列一定要是order by中出现的列。

       3、 cube汇总

              cube汇总和compute效果类似，但语法较简洁，而且返回的是一个结果集。

[sql]  view plain copy print ?

1. select count(*), sex from student group by sex with cube;  
2. select count(*), age, sum(age) from student   
3. where age is not null group by age with cube;  

              cube要结合group by语句完成分组汇总

Ø  排序函数

      排序在很多地方需要用到，需要对查询结果进行排序并且给出序号。比如：

      1、  对某张表进行排序，序号需要递增不重复的

      2、  对学生的成绩进行排序，得出名次，名次可以并列，但名次的序号是连续递增的

      3、  在某些排序的情况下，需要跳空序号，虽然是并列

      基本语法

[sql]  view plain copy print ?

1. 排序函数 over([分组语句] 排序子句[desc][asc])  
2. 排序子句 order by 列名, 列名  
3. 分组子句 partition by 分组列, 分组列  

      #  row_number函数

            根据排序子句给出递增连续序号

[sql]  view plain copy print ?

1. 按照名称排序的顺序递增  
2. select s.id, s.name, cid, c.name, row_number()   
3. over(order by c.name) as number   
4. from student s, classes c where cid = c.id;  

      #  rank函数函数

            根据排序子句给出递增的序号，但是存在并列并且跳空

[sql]  view plain copy print ?

1. 顺序递增  
2. select id, name, rank() over(order by cid) as rank from student;  
3.   
4. 跳过相同递增  
5. select s.id, s.name, cid, c.name,   
6. rank() over(order by c.name) as rank   
7. from student s, classes c where cid = c.id;  

      # dense_rank函数

            根据排序子句给出递增的序号，但是存在并列不跳空

[sql]  view plain copy print ?

1. 不跳过，直接递增  
2. select s.id, s.name, cid, c.name,   
3. dense_rank() over(order by c.name) as dense   
4. from student s, classes c where cid = c.id;  

      #  partition by分组子句

            可以完成对分组的数据进行增加排序，partition by可以与以上三个函数联合使用。

[sql]  view plain copy print ?

1. 利用partition by按照班级名称分组，学生id排序  
2. select s.id, s.name, cid, c.name,   
3. row_number() over(partition by c.name order by s.id) as rank   
4. from student s, classes c where cid = c.id;  
5.   
6. select s.id, s.name, cid, c.name,   
7. rank() over(partition by c.name order by s.id) as rank   
8. from student s, classes c where cid = c.id;  
9.   
10. select s.id, s.name, cid, c.name,   
11. dense_rank() over(partition by c.name order by s.id) as rank   
12. from student s, classes c where cid = c.id;  

      #  ntile平均排序函数

            将要排序的数据进行平分，然后按照等分排序。ntile中的参数代表分成多少等分。

[sql]  view plain copy print ?

1. select s.id, s.name, cid, c.name,   
2. ntile(5) over(order by c.name) as ntile   
3. from student s, classes c where cid = c.id;  

Ø  集合运算

      操作两组查询结果，进行交集、并集、减集运算

      1、 union和union all进行并集运算

[sql]  view plain copy print ?

1. --union 并集、不重复  
2. select id, name from student where name like 'ja%'  
3. union  
4. select id, name from student where id = 4;  
5.   
6. --并集、重复  
7. select * from student where name like 'ja%'  
8. union all  
9. select * from student;  

      2、 intersect进行交集运算

[sql]  view plain copy print ?

1. --交集（相同部分）  
2. select * from student where name like 'ja%'  
3. intersect  
4. select * from student;  

      3、 except进行减集运算

[sql]  view plain copy print ?

1. --减集（除相同部分）  
2. select * from student where name like 'ja%'  
3. except  
4. select * from student where name like 'jas%';  

Ø  公式表表达式

       查询表的时候，有时候中间表需要重复使用，这些子查询被重复查询调用，不但效率低，而且可读性低，不利于理解。那么公式表表达式可以解决这个问题。

      我们可以将公式表表达式（CET）视为临时结果集，在select、insert、update、delete或是create view语句的执行范围内进行定义。

[sql]  view plain copy print ?

1. --表达式  
2. with statNum(id, num) as   
3. (  
4.     select cid, count(*)   
5.     from student   
6.     where id > 0  
7.     group by cid  
8. )  
9. select id, num from statNum order by id;  
10.   
11. with statNum(id, num) as   
12. (  
13.     select cid, count(*)   
14.     from student   
15.     where id > 0  
16.     group by cid  
17. )  
18. select max(id), avg(num) from statNum;  

Ø  连接查询

     1、 简化连接查询

[sql]  view plain copy print ?

1. --简化联接查询  
2. select s.id, s.name, c.id, c.name from student s, classes c  
3. where s.cid = c.id;  

      2、 left join左连接

[sql]  view plain copy print ?

1. --左连接  
2. select s.id, s.name, c.id, c.name from student s   
3. left join classes c on s.cid = c.id;  

      3、 right join右连接

[sql]  view plain copy print ?

1. --右连接  
2. select s.id, s.name, c.id, c.name from student s   
3. right join classes c on s.cid = c.id;  

      4、 inner join内连接

[sql]  view plain copy print ?

1. --内连接  
2. select s.id, s.name, c.id, c.name from student s   
3. inner join classes c on s.cid = c.id;  
4.   
5. inner可以省略  
6. select s.id, s.name, c.id, c.name from student s   
7. join classes c on s.cid = c.id;  

      5、 cross join交叉连接

[sql]  view plain copy print ?

1. --交叉联接查询，结果是一个笛卡儿乘积  
2. select s.id, s.name, c.id, c.name from student s   
3. cross join classes c  
4. --where s.cid = c.id;  

      6、 自连接（同一张表进行连接查询）

[sql]  view plain copy print ?

1. --自连接  
2. select distinct s.* from student s, student s1   
3. where s.id <> s1.id and s.sex = s1.sex;  

Ø  函数

      1、  聚合函数

            max最大值、min最小值、count统计、avg平均值、sum求和、var求方差

[sql]  view plain copy print ?

1. select   
2. max(age) max_age,   
3. min(age) min_age,   
4. count(age) count_age,   
5. avg(age) avg_age,   
6. sum(age) sum_age,   
7. var(age) var_age   
8. from student;  

      2、  日期时间函数

[sql]  view plain copy print ?

1. select dateAdd(day, 3, getDate());--加天  
2. select dateAdd(year, 3, getDate());--加年  
3. select dateAdd(hour, 3, getDate());--加小时  
4. --返回跨两个指定日期的日期边界数和时间边界数  
5. select dateDiff(day, '2011-06-20', getDate());  
6. --相差秒数  
7. select dateDiff(second, '2011-06-22 11:00:00', getDate());  
8. --相差小时数  
9. select dateDiff(hour, '2011-06-22 10:00:00', getDate());  
10. select dateName(month, getDate());--当前月份  
11. select dateName(minute, getDate());--当前分钟  
12. select dateName(weekday, getDate());--当前星期  
13. select datePart(month, getDate());--当前月份  
14. select datePart(weekday, getDate());--当前星期  
15. select datePart(second, getDate());--当前秒数  
16. select day(getDate());--返回当前日期天数  
17. select day('2011-06-30');--返回当前日期天数  
18. select month(getDate());--返回当前日期月份  
19. select month('2011-11-10');  
20. select year(getDate());--返回当前日期年份  
21. select year('2010-11-10');  
22. select getDate();--当前系统日期  
23. select getUTCDate();--utc日期  

      3、  数学函数

[sql]  view plain copy print ?

1. select pi();--PI函数  
2. select rand(100), rand(50), rand(), rand();--随机数  
3. select round(rand(), 3), round(rand(100), 5);--精确小数位  
4. --精确位数，负数表示小数点前  
5. select round(123.456, 2), round(254.124, -2);  
6. select round(123.4567, 1, 2);  

      4、  元数据

[sql]  view plain copy print ?

1. select col_name(object_id('student'), 1);--返回列名  
2. select col_name(object_id('student'), 2);  
3. --该列数据类型长度  
4. select col_length('student', col_name(object_id('student'), 2));   
5. --该列数据类型长度  
6. select col_length('student', col_name(object_id('student'), 1));   
7. --返回类型名称、类型id  
8. select type_name(type_id('varchar')), type_id('varchar');  
9. --返回列类型长度  
10. select columnProperty(object_id('student'), 'name', 'PRECISION');  
11. --返回列所在索引位置  
12. select columnProperty(object_id('student'), 'sex', 'ColumnId');  

      5、  字符串函数

[sql]  view plain copy print ?

1. select ascii('a');--字符转换ascii值  
2. select ascii('A');  
3. select char(97);--ascii值转换字符  
4. select char(65);  
5. select nchar(65);  
6. select nchar(45231);  
7. select nchar(32993);--unicode转换字符  
8. select unicode('A'), unicode('中');--返回unicode编码值  
9. select soundex('hello'), soundex('world'), soundex('word');  
10. select patindex('%a', 'ta'), patindex('%ac%', 'jack'), patindex('dex%', 'dexjack');--匹配字符索引  
11. select 'a' + space(2) + 'b', 'c' + space(5) + 'd';--输出空格  
12. select charIndex('o', 'hello world');--查找索引  
13. select charIndex('o', 'hello world', 6);--查找索引  
14. select quoteName('abc[]def'), quoteName('123]45');  
15. --精确数字  
16. select str(123.456, 2), str(123.456, 3), str(123.456, 4);  
17. select str(123.456, 9, 2), str(123.456, 9, 3), str(123.456, 6, 1), str(123.456, 9, 6);  
18. select difference('hello', 'helloWorld');--比较字符串相同  
19. select difference('hello', 'world');  
20. select difference('hello', 'llo');  
21. select difference('hello', 'hel');  
22. select difference('hello', 'hello');  
23. select replace('abcedef', 'e', 'E');--替换字符串  
24. select stuff('hello world', 3, 4, 'ABC');--指定位置替换字符串  
25. select replicate('abc#', 3);--重复字符串  
26. select subString('abc', 1, 1), subString('abc', 1, 2), subString('hello Wrold', 7, 5);--截取字符串  
27. select len('abc');--返回长度  
28. select reverse('sqlServer');--反转字符串  
29.   
30. select left('leftString', 4);--取左边字符串  
31. select left('leftString', 7);  
32. select right('leftString', 6);--取右边字符串  
33. select right('leftString', 3);  
34. select lower('aBc'), lower('ABC');--小写  
35. select upper('aBc'), upper('abc');--大写  
36. --去掉左边空格  
37. select ltrim(' abc'), ltrim('# abc#'), ltrim('  abc');  
38. --去掉右边空格  
39. select rtrim(' abc    '), rtrim('# abc#   '), rtrim('abc');  

      6、  安全函数

[sql]  view plain copy print ?

1. select current_user;  
2. select user;  
3. select user_id(), user_id('dbo'), user_id('public'), user_id('guest');  
4. select user_name(), user_name(1), user_name(0), user_name(2);  
5. select session_user;  
6. select suser_id('sa');  
7. select suser_sid(), suser_sid('sa'), suser_sid('sysadmin'), suser_sid('serveradmin');  
8. select is_member('dbo'), is_member('public');  
9. select suser_name(), suser_name(1), suser_name(2), suser_name(3);  
10. select suser_sname(), suser_sname(0x01), suser_sname(0x02), suser_sname(0x03);  
11. select is_srvRoleMember('sysadmin'), is_srvRoleMember('serveradmin');  
12. select permissions(object_id('student'));  
13. select system_user;  
14. select schema_id(), schema_id('dbo'), schema_id('guest');  
15. select schema_name(), schema_name(1), schema_name(2), schema_name(3);  

      7、  系统函数

[sql]  view plain copy print ?

1. select app_name();--当前会话的应用程序名称  
2. select cast(2011 as datetime), cast('10' as money), cast('0' as varbinary);--类型转换  
3. select convert(datetime, '2011');--类型转换  
4. select coalesce(null, 'a'), coalesce('123', 'a');--返回其参数中第一个非空表达式  
5. select collationProperty('Traditional_Spanish_CS_AS_KS_WS', 'CodePage');  
6. select current_timestamp;--当前时间戳  
7. select current_user;  
8. select isDate(getDate()), isDate('abc'), isNumeric(1), isNumeric('a');  
9. select dataLength('abc');  
10. select host_id();  
11. select host_name();  
12. select db_name();  
13. select ident_current('student'), ident_current('classes');--返回主键id的最大值  
14. select ident_incr('student'), ident_incr('classes');--id的增量值  
15. select ident_seed('student'), ident_seed('classes');  
16. select @@identity;--最后一次自增的值  
17. select identity(int, 1, 1) as id into tab from student;--将studeng表的烈属，以/1自增形式创建一个tab  
18. select * from tab;  
19. select @@rowcount;--影响行数  
20. select @@cursor_rows;--返回连接上打开的游标的当前限定行的数目  
21. select @@error;--T-SQL的错误号  
22. select @@procid;  

      8、  配置函数

[sql]  view plain copy print ?

1. set datefirst 7;--设置每周的第一天，表示周日  
2. select @@datefirst as '星期的第一天', datepart(dw, getDate()) AS '今天是星期';  
3. select @@dbts;--返回当前数据库唯一时间戳  
4. set language 'Italian';  
5. select @@langId as 'Language ID';--返回语言id  
6. select @@language as 'Language Name';--返回当前语言名称  
7. select @@lock_timeout;--返回当前会话的当前锁定超时设置（毫秒）  
8. select @@max_connections;--返回SQL Server 实例允许同时进行的最大用户连接数  
9. select @@MAX_PRECISION AS 'Max Precision';--返回decimal 和numeric 数据类型所用的精度级别  
10. select @@SERVERNAME;--SQL Server 的本地服务器的名称  
11. select @@SERVICENAME;--服务名  
12. select @@SPID;--当前会话进程id  
13. select @@textSize;  
14. select @@version;--当前数据库版本信息  

      9、  系统统计函数

[sql]  view plain copy print ?

1. select @@CONNECTIONS;--连接数  
2. select @@PACK_RECEIVED;  
3. select @@CPU_BUSY;  
4. select @@PACK_SENT;  
5. select @@TIMETICKS;  
6. select @@IDLE;  
7. select @@TOTAL_ERRORS;  
8. select @@IO_BUSY;  
9. select @@TOTAL_READ;--读取磁盘次数  
10. select @@PACKET_ERRORS;--发生的网络数据包错误数  
11. select @@TOTAL_WRITE;--sqlserver执行的磁盘写入次数  
12. select patIndex('%soft%', 'microsoft SqlServer');  
13. select patIndex('soft%', 'software SqlServer');  
14. select patIndex('%soft', 'SqlServer microsoft');  
15. select patIndex('%so_gr%', 'Jsonisprogram');  

      10、  用户自定义函数

            # 查看当前数据库所有函数

[sql]  view plain copy print ?

1. --查询所有已创建函数  
2. select definition,* from sys.sql_modules m   
3. join sys.objects o on m.object_id = o.object_id  
4. and type in('fn', 'if', 'tf');   

            #  创建函数

[sql]  view plain copy print ?

1. if (object_id('fun_add', 'fn') is not null)  
2.     drop function fun_add  
3. go  
4. create function fun_add(@num1 int, @num2 int)  
5.     returns int  
6. with execute as caller  
7. as  
8.     begin  
9.         declare @result int;  
10.         if (@num1 is null)  
11.             set @num1 = 0;  
12.         if (@num2 is null)  
13.             set @num2 = 0;  
14.         set @result = @num1 + @num2;  
15.         return @result;  
16.     end  
17. go  
18. 调用函数  
19. select dbo.fun_add(id, age) from student;  
20.   
21. --自定义函数，字符串连接  
22. if (object_id('fun_append', 'fn') is not null)  
23.     drop function fun_append  
24. go  
25. create function fun_append(@args nvarchar(1024), @args2 nvarchar(1024))  
26.     returns nvarchar(2048)  
27. as  
28.     begin  
29.         return @args + @args2;  
30.     end  
31. go  
32.   
33. select dbo.fun_append(name, 'abc') from student;  

            #  修改函数

[sql]  view plain copy print ?

1. alter function fun_append(@args nvarchar(1024), @args2 nvarchar(1024))  
2.     returns nvarchar(1024)  
3. as  
4.     begin  
5.         declare @result varchar(1024);    
6.         --coalesce返回第一个不为null的值   
7.         set @args = coalesce(@args, '');  
8.         set @args2 = coalesce(@args2, '');;  
9.         set @result = @args + @args2;  
10.         return @result;  
11.     end  
12. go  
13.   
14. select dbo.fun_append(name, '#abc') from student;  

            #  返回table类型函数

[sql]  view plain copy print ?

1. --返回table对象函数  
2. select name, object_id, type from sys.objects where type in ('fn', 'if', 'tf') or type like '%f%';  
3.   
4. if (exists (select * from sys.objects where type in ('fn', 'if', 'tf') and name = 'fun_find_stuRecord'))  
5.     drop function fun_find_stuRecord  
6. go  
7. create function fun_find_stuRecord(@id int)  
8.     returns table  
9. as  
10.     return (select * from student where id = @id);  
11. go  
12.   
13. select * from dbo.fun_find_stuRecord(2);  

转自：http://blog.csdn.net/ibm_hoojo/article/details/6608772