SQL 的小知识点

       我觉得对于SQL语句，清楚知道它执行的顺序，对于写sql语句非常重要                

                  sql查询中用到的关键词主要包含六个，并且他们的书定顺序依次为 

Select--From--Where--Group by--Having--Order by
但这几关键词的执行顺序与sql语句的书写顺序并不是一样的，而是按照下面的顺序来执行

 From--Where--Group by--Having--Select--Order by

(有些数据库的实现是先Order by--后Select 但其实只是数据表示先后这不影响数据的筛选)

from:需要从哪个数据表检索数据 

where:过滤表中数据的条件,主要对应的是表中的一条条的记录
group by:如何将上面过滤出的数据按照哪个类分组归类
having:对上面已经分组的数据进行过滤的条件
select:查看结果集中的哪个列也就是哪个字段，或列的计算结果

order by :按照什么样的顺序来查看返回的数据  
select关键字 1、用*代替所有列 select * from emp; 2、指定需要返回的列
select ename,deptno ,job from emp;

 

重点写下平时少用的GROUP BY用法：

GROUP BY 子句用来对指定的字段做分组，产生一个汇总信息。

（1）group by语句对select后所选择的字段有一定的限制，即select后没有使用聚合函数的字段必须包含在group by 语句后面的结果集中。（GROUP BY 关键字后跟一个列的列表，称为组合列）.

     以下举例说明：

现在我们就来看看这个GROUP BY 子句有什么用处。
SELECT Table_A.字段2，SUM（ Table_A.字段3）
FROM Table_A
GROUP BY 字段2 ；
执行后会发现这个SQL语句将字段2中的所有记录分成了几组，并将这几组的总数都统计了出来，其中SUM函数是用来产生合计的函数。GROUP BY 字段2，字段2必需要在前面的SELECT出现，如SELECT Table_A.字段2。要更了解再举两个例：
select 字段1，字段2 from Table1  GROUP BY 字段2 ：这是错误的。

select 字段2，sum(字段1) from Table1  GROUP BY 字段2 ：这是可以的，正确的。

（2）不能对数据类型为 ntext、text、image 或 bit 的列使用 GROUP BY 或 HAVING语句。除非在返回的时候使用数据类型转换函数（SUBSTRING 和 CAST）将他们转换为其他数据类型。

（3）在 GROUP BY 语句中，必须指定表或视图列的名称，而不是使用 AS 子句指派的结果集列的名称。

   上面说行有点抽象，其实就是如：SELECT pub_name AS "Book Publisher"

                                                        FROM publishers ORDER BY pub_name ASC
  pub_name AS 后面的"Book Publisher" 就是结果集列名称也叫别名。

   如select 字段1 as 'A' from Table1  GROUP BY A 这是不正确的。

 以下是正确的：
SELECT name, sum(o.Price * o.Quantity) as total
FROM orderLines as o
GROUP BY o.Product as name

因为把列表orderLines as o，这只是表orderLines取别名为o，但o不是结果集列的名称,因为orderLines是真正表的名称，它不是结果集列。而结果集列名称一般指的是select 后字段 As "结果集列的名称"。

（4）GROUP BY 语句中的空值，如果分组列包含一个空值则该行将成为结果中的一个组。如果分组列包含多个空值，则这些空值将放入一个组中。

（5）GROUP BY 语句中可以使用 ALL（可选）关键字，返回由GROUP BY 子句生成的所有组。即不受搜索条件的限制（也就是说此时搜索条件不起作用）。

（6）GROUP BY中的WHERE 和 HAVING 语句

Ａ：WHERE 搜索条件在进行分组操作之前应用，不能使用聚合函数；而 HAVING 搜索条件在进行分组操作之后应用，可以使用聚合函数。

Ｂ：HAVING 语法与 WHERE 语法类似，但 HAVING 可以包含聚合函数。HAVING 子句可以引用选择列表中显示的任意项。
 { 附说明其实聚合函数只能在以下位置作为表达式使用：

• SELECT 语句的选择列表（子查询或外部查询）。

• HAVING 子句。}

  例如查找某个至少包含六本书出版商的计算中，下面示例使用 HAVING COUNT(*) > 5 消除返回的总数小于六本书的出版商：
SELECT pub_id, total = SUM(ytd_sales)FROM titlesGROUP BY pub_idHAVING COUNT(*) > 5

下面是结果集：
pub_id total ------ ----------- 0877 44219 1389 24941 (2 row(s) affected)

理解应用 WHERE、GROUP BY 和 HAVING 子句的正确序列对编写高效的查询代码会有所帮助：

    WHERE 子句用来筛选 FROM 子句中指定的操作所产生的行。
    GROUP BY 子句用来分组 WHERE 子句的输出。
    HAVING 子句用来从分组的结果中筛选行。

对于可以在分组操作之前或之后应用的搜索条件，在 WHERE 子句中指定它们更有效。这样可以减少必须分组的行数。应当在 HAVING 子句中指定的搜索条件只是那些必须在执行分组操作之后应用的搜索条件。

Microsoft® SQL Server™ 2000 查询优化器可处理这些条件中的大多数。如果查询优化器确定 HAVING 搜索条件可以在分组操作之前应用，那么它就会在分组之前应用。查询优化器可能无法识别所有可以在分组操作之前应用的 HAVING 搜索条件。建议将所有这些搜索条件放在 WHERE 子句中而不是 HAVING 子句中。


二、重点写多表连接查询

若一个查询涉及到两个或两个以上的表，则称之为多表连接查询。可从多个表中提取数据并组合成新的纪录。

连接查询主要包括内连接、外连接和交叉连接等。

1、内连接

内连接的格式为：

FROM  表1 [INNER] JOIN 表2 ON〈连接条件〉

连接条件的格式：

表1.列名〈比较运算符〉表2. 列名

例13：

SELECT sname,cno,grade

FROM  student  JION sc ON student.sno=sc.sno

WHERE  sdept=’计算机系’

——查询计算机系学生的修课情况，并列出学生的名字、所修课程的课程号和成绩。

SELECT sname,cname,grade

FROM  student  s  JION  sc ON  s.sno=sc.sno

JION  course  c ON  c.cno=sc.cno

WHERE  sdept=’计算机系’  AND  cname=’VB’

 ——查询信息系修了VB课程的学生的修课成绩，并列出学生姓名、课程名和成绩。

重点（1）：一旦为表指定了别名，则在查询语句中的其他地方，所有用到表名的地方都要使用别名，而不能再使用原表名。为表指定别名的格式：表名|表别名

（2）每连接一张表，就需要加一个JION子句。

例14：

SELECT sname,sdept

FROM  student  s  JION  sc ON  s.sno=sc.sno

JION  course  c  ON  c.cno=sc.cno

WHERE   cname=’VB’

——查询所有修了VB课程的学生的修课情况，并列出学生姓名和所在的系。

上述查询之所以用到sc表，是因为student和course表没有可以进行连接的列（语义相同的列）。

例15：

SELECT sdept,AVG(grade) as Averagegrade

FROM  student  s  JION  sc

ON  s.sno=sc.sno

GROUP  BY  sdept

——查询每个系的学生的考试平均成绩。（有分组的多表连接查询）

例16：

SELECT cno,COUNT(*) AS Total,AVG(grade) as Avggrade, MAX(grade) as MaxGrade,MIN(grade) as Mingrade

FROM  student  s  JION  sc ON  s.sno=sc.sno

WHERE  sdept=’计算机系’  GROUP  BY cno

——查询计算机系每门课程的选课人数、平均成绩、最高成绩和最低分。（有分组和行过滤的多表连接查询）

2、自连接

自连接是一种特殊的内连接，他是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表。

使用自连接时必须为表取别名，使之在逻辑上称为两张表。

例17：

SELECT S2.sname,S2.sdept

FROM  student  S1  JION  Student  S2

 ON  s1.sdept=s2.sdept

WHERE s1.name=’刘晨’ AND s2.name!= ’刘晨’

——查询与刘晨在同一个系学习的学生的姓名和所在系。

3、外连接

在内连接中，只有满足连接条件的元组才能作为结果输出，而外连接既可以输出满足条件的元组，也可以输出不满足条件的元组。

外连接的语法格式为：

FROM 表1 LEFT|RIGHT [OUTER] JION 表2 ON 〈连接条件〉

LEFT  OUTER  JION——左外连接，不管表1中的元组是否满足连接条件，均输出表1的内容；

RIGHT  OUTER  JION——右外连接，不管表2中的元组是否满足连接条件，均输出表2的内容。

例18：

SELECT student.sno,sname,cno,grade

FROM  student  LEFT  OUTER  JION  SC

ON  student.sno=sc.sno

——查询修了课的学生和没有修课的学生情况。

例19：

SELECT cname  FROM  course c LEFT  JION  SC

ON  c.cno=sc.cno

WHERE  sc.cno  is  NULL

——查询出哪些课程没有人选，列出课程名。

注：外连接一般只在两个表上进行。

4、合并多个结果集

将两个或两个以上的查询结果集合合并为一个结果集，使用UNION，其格式为：

SELECT  语句1

UNION[ALL]

SELECT  语句2

UNION[ALL]

SELECT  语句n

其中：ALL表示在合并结果集中包含所有查询语句产生的全部记录，包括重复的记录。如果没有指定ALL，则系统自动删除合并后结果集中的重复记录。

使用UNION的两个基本规则是：

（1）所有查询语句中列的个数和列的顺序必须相同；

（2）所有查询语句中对应列的数据类型必须兼容。

例20：

SELECT sno,sname,sage,sdept FROM student where sdept=’计算机系’

UNION

SELECT sno,sname,sage,sdept FROM student where sdept=’信息系’

ORDER BY  sage  DESC

——计算机系学生查询结果与信息系学生查询结果合并，并按年龄从大到小排列。

5、将查询结果保存到新表中

通过在SELECT语句中使用INTO子句实现。格式为：

SELECT  查询列表序列  INTO  新表名

FROM  数据源

…..其他行过滤、分组等语句

用INTO子句创建的新表可以是永久表，也可以是临时表。临时表又根据其使用范围分为两种：局部临时表和全局临时表。

局部临时表通过在表名前边加一个‘#’来表识，局部临时表的生存期为创建此局部临时表的连接的生存期，它只能在创建此局部临时表的当前连接中使用。

全局临时表通过在表名前加‘##’来标识，全局临时表的生存期为创建全局临时表的连接的生存期，并且在生存期内可以被所有的连接使用。

例21：

SELECT sname,cname,grade INTO  f

FROM student  s  JION  SC ON  s.sno=sc.sno

JION  course c  ON  c.cno=sc.cno

where sdept=’计算机系’

——将计算机系学生的姓名、修课的课名和成绩存入永久表f中。

6、使用TOP限制结果集

列出结果集中的前几个结果。语法格式：

Top  n  [percent]  [with  ties]

Top  n表示取查询结果的前n行；

with  ties：表示包括并列的结果；

Top谓词写在SELECT单词的后边（如果有DITINCT的话，则TOP是写在DITINCT的后边），查询列表的前边。

例22：

SELECT  TOP 3 sname,sage,sdept  FROM  student ORDER  BY sage  desc

——查询年龄最大的三个学生的姓名、年龄及所在的系。若要包含并列第三名的学生，可在TOP 3后面加with  ties即可。

注意：在使用TOP字句时，需要用ORDER BY字句对其进行相应的排序。

评论

1 楼 MA_ZERUI 2013-03-25  

不错不错，精华啊。