主题：[推荐]第六部分课堂讲稿——SQL语言之一

第六部分 SQL语言
     1 简介
     1．1 发展历史
     SQL语言是Structured Query Language的简称，在1974年由Boyce和Chamberlin提出，1975年在System R上实现，现已成为数据库管理系统的标准查询语言。
     它的发展经历过很多阶段，1986年由ANSI（American National Standards Institute）公布86版和1992年的SQL 92版（被称为SQL 2），1987年ISO（International Organization For Standardization）通过，后来在SQL 2的基础上又提出了SQL 3，支持递归（Recursion）、触发器（Trigger）和对象（Object）等面向对象的特性。
     本章讲述SQL语言的基本内容和SQL Server2000中的SQL语言使用方法。

     1．2 组成
     数据查询语句（Data Query Language）：select
     数据操纵语句（Data Manipulation Language）：insert，update，delete
     数据定义语句（Data Definition Language）：create，drop，alter
     数据控制语句（Data Control Language）：grant，revote

     2 数据查询语句
     2．1 投影
     1）简单字段：
     select * from stu

     2）使用别名
     select name as '姓名',height as '身高' from stu
     （as可以省略）

     3）常数列
     select name,height,'米' as '单位' from stu

     4）计算列（函数的使用）
     select name as '姓名',round(height*100,1,0) as '身高','CM' as '单位' from stu
     select name as '姓名',convert(int,height*100) as '身高' from stu
     select name as '姓名',convert(char(3),convert(int,height*100))+'米' as '身高' from stu
     select name,year(getdate())-year(birthday) as 'age' from stu
     select distinct substring(name,1,1) from stu

     2．2 选择
     1）简单条件
     select * from stu where height>1.76
     select * from stu where name in ('黎明','姚华')
     select * from stu where substring(name,1,1)='李'
     select top 1 * from stu
     2）使用通配符
     select * from stu where name like '李%'
     select * from stu where name like '李_'
     select * from stu where name='李_'
     select * from stu where name like 'x%%x%' escape 'x'

     2．3 结果排序
     1）简单排序
     select * from stu order by height
     select * from stu order by name
     select * from stu order by height desc
     select top 1 * from stu order by height desc

     2）多字段排序
     select * from stu order by name,height desc

     2．4 连接
     1）内连接
     select * from stu,grade where stu.number=grade.number
     select star.name,singer.name from star inner join singer on star.address = singer.address

     2）自连接
     select star1.name,star2.name from star as star1 inner join star as star2 on star1.address = star2.address and star1.name<star2.name

     from后面可以跟具有关系型的数据变量，如：
     declare @t table(a varchar(10),b varchar(10))
     insert into @t values ('1','2')
     insert into @t values ('3','4')
     select * from @t

     3）外连接
     select name from stu left outer join grade on stu.number=grade.number where grade is null

     2．5 空值
     产生的两个原因在于记录插入和外连接，作为外连接的空值问题不大，但是表中的空值问题较多，如下面的语句无法显示身高为空值的记录：
     select * from stu where height>1.80 or height<=1.80

     空值应该理解为“有值但是不知道”，所以比较结果为“不知道（Unknown）”，被当成“假”处理。如果空值比较和其他条件通过逻辑运算符放在一块，可以将逻辑真理解为1，逻辑假理解为0，空值比较结果理解为1/2，and运算为求最小值运算，or运算为求最大值运算，如果结果为“不知道”，则视为逻辑假。

     2．6 聚合运算
     比较：
     select convert(int,height*100) from stu
     select max(height) from stu
     聚合运算函数也是函数，但是与一般函数的主要区别在于两点：一是针对所有记录的字段值进行一次处理，二是得到一个值，而一般函数是针对一个记录一次处理，有几条记录就会得到几个值。所以被称为“聚合函数（Aggregate）”，主要有五个，分别为count，max，min，sum和avg。

     2．6．1 简单的聚合运算函数
     1）count
     如：
     select count(height) from stu
     select count(sex) from stu
     select count(distinct sex) from stu
     select count(distinct height) from stu
     select count(*) from stu

     2）max，min，sum和avg
     select max(height) from stu
     select min(height) from stu
     select sum(height) from stu
     select avg(height) from stu
     强调max和min不能对一些二进制数据类型进行比较，sum和avg只能对数值型数据类型进行运算。

     3）强调在聚合函数的使用时，不能同时使用一些具有多个返回值的其他字段投影，如：
     select name,min(height) from stu
     但是VFP可以，将显示最后一条学生记录的姓名，不论哪种情况，都没有逻辑意义。

     2．6．2 结合分组的聚合运算函数
     如统计男女生的人数分布情况：
     select count(*) from stu where sex=1
     select count(*) from stu where sex=0

     没有显示出性别，再如：
     select min(sex),count(*) from stu where sex=1
     select min(sex),count(*) from stu where sex=0

     但是这种方法不实用，一是因为没有穷尽其他条件中的数值，如不同分数的人数分布等；二是显示出多个关系结果，不方便处理。

     合理的做法是使用分组来进行，如：
     select sex,count(*) from stu group by sex
     select height,count(*) from stu group by height
     select number,avg(grade) from grade group by number
     select min(stu.name),avg(grade) from stu inner join grade on stu.number=grade.number group by stu.number order by avg(grade)

     利用分组运算模拟distinct子句：
     select height from stu group by height
     等价于：
     select distinct height from stu

     2．6．3 使用分组条件的聚合运算函数
     1）having和where的等价使用
     看下面几个例子，如：
     查询身高大于1.75米的学生身高分布情况：
     一种方法是先选择再分组，使用where：
     select height,count(*) from stu where height>1.75 group by height

     另一种方法是先分组再按组进行选择，使用having：
     select height,count(*) from stu group by height having height>1.75

     上述方法等价，等价原因在于条件中字段与分组字段为同一字段。

     2）必须使用having的情况
     下面的例子必须使用having，如查询总人数大于等于2人的身高组和人数分布：
     select height,count(*) from stu group by height having count(*)>=2

     使用where将报错，如：
     select height,count(*) from stu where count(*)>=2 group by height
     原因是聚合函数不应出现在 WHERE 子句中，除非该聚合位于 HAVING 子句或选择列表所包含的子查询中，并且要对其进行聚合的列是外部引用。

     再如查询平均分大于85的优秀学生姓名：
     select min(stu.name) from stu inner join grade on stu.number=grade.number group by stu.number having avg(grade)>85

     所以，条件如果有聚合函数，只能使用分组条件having，也就是说，只有在分组后才能比较。

     3）必须使用where的情况
     下面的例子必须使用where，如查询男生中不同身高的人数分布：
     select height,count(*) from stu where sex=1 group by height

     使用having将报错，如：
     select height,count(*) from stu group by height having sex=1
     原因是列 'stu.sex' 在 HAVING 子句中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中，也就是说，对于每个组而言，sex值不惟一，无法根据它来决定选择哪个组。

     强调六个子语句的使用顺序：
     Select
     From
     Where
     Group by
     Having
     Order by

     2．7 生成表的查询语句
     为非标准语句，所以不同数据库管理系统的写法往往都不一样：
     SQL Server：select * into stu1 from stu
     VFP：select * from stu into table stu1
     Oracle：create table stu1 as (select * from stu);

     2．8 交叉表
     介绍Access、VFP和Excel中的交叉表查询。

     2．9 子查询
     一个查询语句成为另外一个查询语句的组成部分，形成子查询。主要用于多表查询，比连接易于理解，但是性能不高。
     如：
     查询黎明选修的课程：
     select * from grade where number=(select number from stu where name='黎明')

     查询最高学生的姓名：
     select name from stu where height=(select max(height) from stu)

     查询没有选修课程的学生：
     select * from stu where number not in(select number from grade)

     查询和黎明身高相同的学生姓名：
     select number,name,sex from stu where height=(select height from stu where name='黎明') and name<>'黎明'