《SQL 2005 select 详尽用法》

      最近一直很忙， 好久没有更新博客了，现在就贴上自己在学习SQL 2005 过程中的学习笔记吧，自认为总结的还是不错的，与君共勉，如有错误之处，还请高手们不吝赐教！！

   数据库查询是数据库中一个最重要也是最基本的功能，它是从数据库中检索符合条件的数据记录的选择过程。SQL Server 2005的数据库查询使用T-SQL语言，其基本的查询语句是SELECT语句。

本章主要介绍SQL Server 2005数据库查询的方法及使用。

涉及的知识点:

数据库查询

数据汇总

排序

分组

子查询

集合操作

存储查询结果

函数查询

   练习以上知识点实际上是练习使用select语句和其他语句的组合。

1.数据库查询

   

1.1 选择列

选择列指的是通过限定返回结果的列组成结果表。

选择指定列

选择指定列指的是选择一个表中的部分列，各列名之间用逗号隔开。

首先做三张表以作下面的演示：（代码大家可以直接复制）

Student表：

Course表：

选课表：SC

--1.查询全体学生的学号与姓名。

SELECT Sno，Sname

FROM Student；

--2. 查询全体学生的姓名、学号、所在系。

SELECT Sname，Sno，Sdept

FROM Student；

查询全部列:可以使用"*"简单代替

--3  查询全体学生的详细记录。

SELECT  Sno，Sname，Ssex，Sage，Sdept 

FROM Student；

或

SELECT  *

FROM Student；

定义列别名:在选择列的同时给该列赋予新的名字,使用as关键字

--4.查询所有学生的姓名、性别，并将相应的列名换为name、sex。

Select Sname as name,Ssex as sex 

from student

替换结果中数据(结果经过计算)

--5.  查全体学生的姓名及其出生年份。

SELECT Sname,2012-Sage

FROM Student

--使用CASE函数给每个学生的分数设定等级。

SELECT Sno,Cno,Grade,等级=

CASE

  WHEN Grade>=90 THEN '优秀'

  WHEN Grade>=80 THEN '良好'

  WHEN Grade>=70 THEN '中等'

  when Grade>=60 then '及格'

  ELSE '不及格'

END

FROM SC

1.2 选择行

选择行指的是通过限定返回结果的行组成结果表。选择行可以和选择列一起使用。

1. 消除结果中重复行

在对表进行查询时，有时查询结果有许多重复行。SELECT语句使用DISTINCT关键字消除结果中的重复行。其语法格式如下：

DISTINCT column_name [,column_name…]

说明：DISTINCT关键字对后面的所有列消除重复行。一个SELECT语句中DISTINCT只能出现一次，而且必须放在所有列名之前。

--7 . 查询选修了课程的学生学号。

 SELECT Sno FROM SC;

或(默认 ALL)

        SELECT ALL Sno  FROM SC;

/*

--//等价SELECT all Sno FROM SC

SELECT Sno FROM SC

--DISTINCT

SELECT DISTINCT Sno FROM SC 

*/

限制结果返回行数

如果SELECT语句返回结果有很多行，可以使用TOP关键字限定返回行数。在SQL Server 2000中，关键字TOP后只能常数数值。

在SQL Server 2005中进行了改进，TOP后还可以使用数值表达式。其语法格式如下：

TOP n [PERCENT]

说明：其中n表示返回结果的前n行，n PERCENT表示返回结果的前n%行。

top 后跟数字

--8.查询课程表中前门课的信息。

select top 3 Cno from SC

top后跟表达式

--9.查询学生表中前人的信息。

declare @i_temp int 

set @i_temp=3

select top (@i_temp) * 

from Student

限制结果返回行的条件

在限定返回结果的行操作时，最重要的就是通过条件限制，SELECT语句中WHERE子句是最常用、最重要的条件子句。

在WHERE子句指出查询的条件，系统找出符合条件的结果。其语法格式如下：

WHERE < operator 1> [AND < operator 2>…][AND | OR < operator >…]

WHERE子句常用的查询条件

(1) 表达式比较

   在WHERE子句的<比较条件>中使用比较运算符：

    =，>，<，>=，<=，!= 或 <>，!>，!<，

-- 10.查询所有年龄在岁以下的学生姓名及其年龄。

select Sname ,Sage 

from Student

where Sage<20

或  

SELECT Sname，Sage 

FROM    Student 

WHERE NOT Sage >= 20

--11.查询考试成绩有不及格的学生学号。

select Sno 

from SC

where Grade<60

(2) 限制范围

--12.查询年龄在~23岁之间的学生的姓名、系别和年龄。

-- between a and b 相当于[a,b]

select  Sname , Sdept , Sage 

from Student

where Sage between 18 and 23

--13.查询年龄不在~23岁之间的学生姓名、系别和年龄。

select  Sname , Sdept , Sage 

from Student

where Sage not between 18 and  23

(3) 确定集合

使用谓词     IN <值表>,  NOT IN <值表>

          <值表>：用逗号分隔的一组取值

--14.查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。

select Sname,Ssex

from Student

where Sdept in ('IS','MA')

--同上等价

SELECT Sname,Ssex

FROM   Student

WHERE  Sdept= 'IS' OR Sdept= 'MA' 

--15.查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。

SELECT Sname，Ssex

FROM Student

 WHERE Sdept NOT IN ( 'IS'，'MA'，'CS' )

 

 

(4) 字符串匹配

 谓词LIKE可以用来进行字符串匹配

[NOT] LIKE  ‘<匹配串>’ [ESCAPE ‘<换码字符>’]

<匹配串>：指定匹配模板

匹配模板：固定字符串或含通配符的字符串

      当匹配模板为固定字符串时，

      可以用 = 运算符取代 LIKE 谓词

      用 != 或 < >运算符取代 NOT LIKE 谓词

      

通配符:

%: (百分号)  代表任意长度（长度可以为）的字符串

  例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab 等都满足该匹配串

_ :(下横线)  代表任意单个字符

  例：a_b表示以a开头，以b结尾的长度为的任意字符串。如acb，afb等都满足该匹配串

-- 16.查询学号为的学生的详细情况。

SELECT *    

FROM  Student  

WHERE  Sno ='95001'

等价于：

    SELECT  * 

      FROM  Student 

      WHERE Sno = '95001'

      

--17.查询所有姓刘学生的姓名、学号和性别。

select *

from Student

where Sname like '刘%'

--18.查询所有不姓刘学生的姓名、学号和性别。

select *

from Student

where Sname not like '刘%'

ESCAPE 短语：

当用户要查询的字符串本身就含有 % 或 _ 时，要使用ESCAPE ‘<换码字符>’短语对通配符进行转义。

使用换码字符将通配符转义为普通字符。

--19.查询姓名中第二个字是‘%’的学生。

select sname 

from student

where Sname like '_/%' ESCAPE '/'

--同以上等价

select sname 

from student

where sname like '_6%' ESCAPE '6'

==>:DSCAPE后的内容是转义符号，并且该转移符号只能是单个字符，如下面的写法是错误的：

select sname 

from student

where sname like '_6AB%' ESCAPE '6AB'

--错误信息：

--消息506，级别16，状态2，第1 行

--在LIKE 谓词中指定的转义符"6AB" 无效。

(5)涉及空值的查询

使用谓词 IS NULL 或 IS NOT NULL

 “IS NULL”不能用 “= NULL”代替

 

--20.某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。

select Sno , Cno, Grade 

from SC

where Grade is  null

--21. 查所有有成绩的学生学号和课程号。

SELECT Sno，Cno

FROM  SC

WHERE  Grade IS NOT NULL

(6) 多重条件查询

用逻辑运算符AND和OR来联结多个查询条件

AND的优先级高于OR

可以用括号改变优先级

可用来实现多种其他谓词的等价形式

[NOT] IN

[NOT] BETWEEN …  AND  …

-- 22.查询计算机系年龄在岁以下的学生姓名。

select Sname

from Student

where Sdept='IS' and Sage<20

--改写.

--14.查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。

SELECT Sname，Ssex

FROM Student

WHERE Sdept IN ( 'IS'，'MA'，'CS' )

可改写为：

SELECT Sname，Ssex

FROM   Student

WHERE  Sdept= ' IS ' OR Sdept= ' MA' OR Sdept= ' CS '

改写.

--12查询年龄在~23岁（包括岁和岁）之间的学生的姓名、系别和年龄。ÿ    SELECT Sname，Sdept，Sage

FROM Student

WHERE Sage BETWEEN 20 AND 23；

可改写为：

SELECT Sname，Sdept，Sage

FROM Student

WHERE Sage>=20 AND Sage<=23

2.数据汇总（使用集函数）

5类主要集函数

(1)计数

COUNT（[DISTINCT|ALL] *）

COUNT（[DISTINCT|ALL] <列名>）

(2)计算总和

SUM（[DISTINCT|ALL] <列名>）

(3)计算平均值

AVG（[DISTINCT|ALL] <列名>）

(4)求最大值

MAX（[DISTINCT|ALL] <列名>）

(5)求最小值

MIN（[DISTINCT|ALL] <列名>）

DISTINCT短语：在计算时要取消指定列中的重复值

ALL短语：不取消重复值

ALL为缺省值

--25.查询学生总人数。

SELECT COUNT(*)

FROM  Student

--26.查询选修了课程的学生人数。

SELECT COUNT(DISTINCT Sno)

FROM SC；

注：用DISTINCT以避免重复计算学生人数

--27.计算号课程的学生平均成绩。

SELECT AVG(Grade)

FROM SC

WHERE Cno= ' 2 '

--28.查询选修号课程的学生最高分数。

SELECT MAX(Grade)

FROM SC

WHER Cno= ' 2 '

3.排序

使用ORDER BY子句可以按一个或多个属性列排序升序：ASC；降序：DESC；缺省值为升序当排序列含空值时

ASC：排序列为空值的元组最前显示

DESC：排序列为空值的元组最后显示 

 

--23查询选修了号课程的学生的学号及其成绩，查询结果按分数升序排列。

select Sno , Grade

from SC

where Cno='3' 

ORDER BY Grade asc

--24查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

SELECT  *

FROM  Student

ORDER BY Sdept,Sage DESC

4.对查询结果分组 

GROUP BY子句用于分组 

分组：按指定的一列或多列值分组，值相等的为一组

--29.查询CS表中选课学生的学号。

select  sno  

from SC 

--group by sno 

--方法同下亦可

select DISTINCT sno  

from SC 

分组的目的：细化集函数的作用对象,未对查询结果分组时，集函数将作用于整个查询结果,

对查询结果分组后，集函数将分别作用于每个组，即每个组都有一个函数值。

--30.求各个课程号及相应的选课人数

select Cno, count(Sno) as 选课人数

from SC

group by Cno

注意：使用GROUP BY子句后，SELECT子句的列名列表中只能出现分组属性和集函数

使用HAVING短语筛选最终输出结果

只有满足HAVING短语指定条件的组才输出

HAVING短语与WHERE子句的区别：作用对象不同

WHERE子句作用于表或视图，从中选择满足条件的元组。

HAVING短语作用于组，从中选择满足条件的组。

--31.查询选修了门以上课程的学生学号。

select Sno

from SC

group by Sno

having  count(Sno)>2

--32.☆☆☆查询有门以上课程是分以上的学生的学号及（分以上的）课程数

SELECT  Sno   ,count(grade) as '80分课程数'

FROM   SC

WHERE Grade>=80

group by Sno

having  COUNT(*)>=3

--注:对于数字在前的别名，需要别名上加上' '，不然数字不能够识别，导致错误

【连接】

【连接】指的是通过限定返回结果，将多个表的数据组成结果表，即用一个SELECT语句可以完成从多个表中查询数据。连接对结果没有特别的限制，具有很大的灵活性。

T-SQL提供了两种连接方式：传统连接方式和SQL连接方式。

1. 传统连接方式

传统连接方式是指使用FROM…WHERE连接多表。其语法格式如下：

SELECT column_name [,column_name,…          

FROM table_name [,table_name,…]

WHERE condition 

--33.查询每个学生选修课程的信息，输出学生姓名和课程名。

select