-- 作者:admin
-- 发布时间:2008/2/20 15:10:27
-- [推荐]第六部分课堂讲稿——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<>\'黎明\'
[此贴子已经被作者于2010-12-11 19:48:50编辑过]
|