发表于: 2005.02.23 23:16
分类: dbms
出处: http://kyle.itpub.net/post/1626/19218
---------------------------------------------------------------
SQL语句分:数据定义语言(Data Definition Language,简称"DDL")、数据操纵语言(Data Manipulation Language,简称"DML")、数据控制语言(Data Control Language,简称"DCL")三部分功能。
本篇主要讲解DML部分，查询是DML部分中最主要的部分，所以重点讲解。查询按表达方式可分为两类：关系代数和关系演算。关系代数是用对关系的运算来表达查询的，而关系演算是用谓词来表达查询的，关系演算根据谓词变元的不同又分为元组关系演算和域关系演算两种。数学理论证明关系代数和关系演算在表达能力上是等价的，只要RDBMS(关系型数据库管理系统)支持，两者是可以相互转换的，这种转换能力可以用来评估一个RDBMS的查询强大与否。Sqlserver这个RDBMS主要采用关系代数来表达查询，今天我们所学习中的查询(select)操作的元素在数据库理论里都有对应的概念。
在sqlserver中：
DML包括：select,update,insert,delete;
DDL包括：create,drop,alter;
DCL包括grant,revoke,deny,execute,while等等。
例表(针对.net学分制软件)：
假定对jx_scorereport(成绩主表)、jx_score(成绩明细)、xs_student(学籍卡)、xs_freshman(录取名单)、
jx_semestercourse(学期课程安排)、jx_course(课程)、jx_class(班级)、jx_classcourse(科目属性设置表)这几张表操作
一、Select语句
1．语法：
select * from table_name where …group by … having…order by …
语法的执行顺序说明：先执行from字句，然后依次是where、group by、having、order by字句，最后才对结果select
2．普通单表查询
select * from jx_score --取所有的学生成绩
select top 100 * from jx_score --取前100条的学生成绩
3．不重复查询
select distinct xm,mz from xs_student --取姓名和民族同时不重复的学生
4．带条件查询(where后面不可跟聚合函数，比较运算符的右边不可出现集合值)
select * from jx_score where kclx='公共任选课' and xqmc='2005-2006学年第一学期' --取2003-2004第二学期选择任选课的学生成绩
5．关联查询(为了避免笛卡儿乘积的产生，对于n个表的连接，至少要有n-1个条件；对于多表共有的字段，则需要在字段前加上表的别名，如a.xm)
select b.bjmc,a.xh,a.xm,a.xb,a.sfzh
from xs_student a,jx_class b
where a.bh=b.bh and datalength(sfzh) not in (15,18) order by b.bjmc --查询没有身份证，或者身份证位数不对的学生。这个语句根据ANSI-92标准也 可以这样写：
select b.bjmc,a.xh,a.xm,a.xb,a.sfzh
from xs_student a join jx_class b on a.bh=b.bh
where datalength(sfzh) not in (15,18) order by b.bjmc
6．模糊查询(like)
select * from sysobjects where xtype='u' and name like 'xz_[A-E]%'
--查询数据库里以"xz_"开头且第4个字符范围在" A-E"的表
说明：%(百分号)表示从0~n个任意字符；_(下划线)表示单个任意字符；[](封闭方括号)表示方括号里列出的任意一个字符，可与连字符(-)一起使用指定一个值的范围；[^]表示任意一个没有在方括号里列出的字符。这些符号称为通配符，只有在跟like一起使用时才有模糊查询的意义，否则会被当作普通字符处理。如果要查询的字符串里包含了通配符，则需用escape来规定转义字符。
7．临时表
select * into #temp from jx_score --将学生的成绩存放在一张临时表里
select * into #tmp from jx_score where 1=2 --生成一张与成绩表结构一样的表#tmp
临时表使用完毕，使用drop table #tmp
表变量
declare @temp table(sid int)
insert @temp select 1
select * from @temp
8．分组(group by)、筛选(having)
select mz,count(*)
from xs_student where xb='女'
group by mz
having(count(*))<100 --查询各个民族里女生少于100的情况
说明：语句先执行where条件，然后在条件里分组(group by)排列，最后在分组里进一步筛选(having)；select选择列表里除聚合函数以外的列都必须出现在group by后面，group by后面不能跟字段别名，不支持任何使用了聚合函数的集合列；having可以包含聚合函数，可以引用选择列表中出现的任意列
9．聚合函数(avg)、分组(group by)、筛选(having)、排序(order by)
select id,kclx,avg(cast(zpresult as float)) as avgresult
from jx_score
where xqmc='2004-2005学年第一学期' and zzfz='百分制'
and zpresult<>'' and zpresult is not null
group by id,kclx
having avg(cast(zpresult as float))<60
order by avg(cast(zpresult as float)) desc --取2004-2005学年第一学期总评成绩是百分制的每种课程类型的平均分小于60按分数排序的学生名单
说明：聚合函数(也称统计函数)共有5个，分别是：avg --求平均值，count --统计数目，max --求最大值，min --求最小值，sum --求和，更具体的用法查看sqlserver联机帮助；排序有两种：asc(升序)，desc(降序)
10．并集运算
select xm,mz into #temp from xs_student
union all
select xm,mz from xs_freshman --将学籍卡里的的学生信息与新生入学登记表里的学生信息合并到临时表#temp里。
说明：union all会取多表并集的重复行；union可以去掉重复行；多表之间的union必须列数相同一一对应，对应的列之间必须可以隐性的转换成相同的数据类型(即兼容)
11．嵌套查询(分为层次嵌套和相关嵌套两种，当嵌套多于2个又称多级嵌套)
⑴．层次嵌套(in,not in,比较运算符)
select * from xs_student
where id in
(select id from jx_score where xqmc='2004-2005学年第一学期'
and zpresult>(select avg(cast(zpresult as float))
from jx_score where zzfz='百分制'
and zpresult<>'' and zpresult is not null)
and zzfz='百分制'and zpresult<>'' and zpresult is not null)
order by xm
任何的层次查询都可以化解成关联查询，如这题也可以写成：
select distinct a.*
from xs_student a,jx_score b
where a.id=b.id and b.xqmc='2004-2005学年第一学期'
and zzfz='百分制'and b.zpresult<>'' and b.zpresult is not null
and b.zpresult>(select avg(cast(zpresult as float))
from jx_score where zzfz='百分制'
and zpresult<>'' and zpresult is not null)
order by a.xm--求2004-2005学年第一学期百分制总评成绩超过全校历年百分制平均分的学生资料
⑵．相关嵌套(exists,not exists)
select distinct a.xqmc,a.bh,a.kcdm,1,1
from jx_score a
where restudy=0 and scorecreate=1 and
not exists(select 1 from jx_classcourse
where xqmc=a.xqmc and bh=a.bh and kcdm=a.kcdm)
and kclx in(select kclx from jx_coursetype where flx<>'任选课')
--原始成绩单里有而科目属性设置里没有的课程（除选修课）
⑶．说明：
in、not in执行的机制是先执行紧跟其后的子查询，然后再执行父查询，判断父查询的关键字是否存在于子查询所得到的集合里；exists、not exists的执行机制是每取得子查询的一条记录马上就与父查询记录进行比较，一直遍历整个子查询到结束，相关查询之间的连接不是列之间的关系，而是表之间的关系，所以在select列表中，通常不需要明确的指定列名，使用*或数字(如1)代替就可以了；使用not in或not exists可以很方便的实现非成员关系型和非存在关系型的查询难题，降低查询复杂度；如果能确定子查询返回的是单值，那么可以使用比较运算符；另外嵌套查询只能放在小括号里；子查询还可以嵌套其他子查询，这就是多级查询，这种层层嵌套的构造正是SQL(Structured Query Language)中"结构化"的含义所在
12．针对公司学分制软件的一个较复杂的应用，融合了以上讲解的很多要素。功能是：取出每个年级每个专业下只有一个班级，且这个行政班又拆成若干个教学班，有的教学班又拆成若干个教学子班的信息
select distinct a.xq,c.jxbdm JX_CourseSchduleSetting_jxbdm,d.bh,
c.pktime,h.xm,i.jsmc,c.jxbdm jx_classsemestercourse_jxbdm,a.xqkcdm,f.kcmc
into #temp1
from jx_semestercourse a,jx_smstcoursespec b,
(select * from jx_smstclass where xqkcdm in(
select xqkcdm from jx_smstclass group by xqkcdm having(count(1))>1)) c,
(select * from jx_class x where exists(
select 1 from (select zydm,njdm from jx_class
group by zydm,njdm having(count(1))=1) y where x.zydm=y.zydm and x.njdm=y.njdm)) d,
jx_specialty e,jx_course f,jx_smstclassteacher g,xz_employee h,jx_classroom i
where a.xqkcdm=b.xqkcdm and a.xqkcdm=c.xqkcdm
and b.njdm=d.njdm and b.zydm=d.zydm and d.zydm=e.zydm and a.kcdm=f.kcdm
and c.jxbdm=g.jxbdm and g.zgh*=h.zgh and c.jsdm*=i.jsdm
and a.skzzfs=1 and a.xq='2006-2007学年第二学期'and charindex('任选课',a.kclx)=0 and a.kcaplx<>0
and c.jdflag=0 --一个行政班拆成两个教学班，教学班下没有再拆班
and c.pktime<>'' --只取安排了时间的教学班信息
union all
select distinct a.xq,j.jxbdm JX_CourseSchduleSetting_jxbdm,d.bh,
j.pktime,h.xm,i.jsmc,c.jxbdm jx_classsemestercourse_jxbdm,a.xqkcdm,f.kcmc
from jx_semestercourse a,jx_smstcoursespec b,
(select * from jx_smstclass where xqkcdm in(
select xqkcdm from jx_smstclass group by xqkcdm having(count(1))>1)) c,
(select * from jx_class x where exists(
select 1 from (select zydm,njdm from jx_class
group by zydm,njdm having(count(1))=1) y where x.zydm=y.zydm and x.njdm=y.njdm)) d,
jx_specialty e,jx_course f,jx_smstclass_childteacher g,xz_employee h,jx_classroom i
,jx_smstclass_child j
where a.xqkcdm=b.xqkcdm and a.xqkcdm=c.xqkcdm
and b.njdm=d.njdm and b.zydm=d.zydm and d.zydm=e.zydm and a.kcdm=f.kcdm
and j.jxbdm=g.jxbdm and g.zgh*=h.zgh and j.jsdm*=i.jsdm
and a.skzzfs=1 and a.xq='2006-2007学年第二学期'and charindex('任选课',a.kclx)=0 and a.kcaplx<>0
and c.jdflag=1 --每个教学班又拆成两个教学子班
and c.pktime<>'' --只取安排了时间的教学班信息
and c.xqkcdm=j.xqkcdm and c.jxbdm=j.belongto
order by h.xm,jx_classsemestercourse_jxbdm
二、Update语句
1．语法：
update table_name set …from … where…
2．单表更新：
update xs_freshman set mz='汉族' where mz is null --将民族为空的新生置为汉族
3．多表集联更新
update jx_score set credit=0
from jx_score a,jx_scorereport b
where a.scorereportid=b.scorereportid and a.zpresult<>' ' and a.credit<>' '
and fzmc='百分制' and (cast(a.zpresult as float)<60) and cast(a.credit as float)>0 --将百分制中总评不及格却获得学分的学生学分改为0
三、Insert语句
1．语法：
insert table_name(,…n) values(,..n)
insert table_one(,…n) select ,…n from table_two …
insert table_name(,…n) exec(@sql) --动态语句
2．单表插入
insert xs_freshman(id,xm,xb,bh,xh,csrq,mz,rxnf)
select newid(),'颜晓琳','女','A0202','020002','2005-02-18','汉族','2005'
说明：对表中的某部分字段操作，字段要一一列出来，值要跟字段一一对应，且数据类型相同。
四、Delete语句
1．语法：
delete table_name where …
delete table_name from table_one,table_two where …
truncate table table_name(整表删除且不写日志)
2．单表删除
delete xs_student where xh='031002023121' --删除某个学生信息
3．集联删除
delete xs_student from xs_student a,jx_score b
where a.id=b.id and b.mark<>1 --删除成绩单里无成绩或成绩无效的学生信息
五、Drop语句(DDL)
drop table table_name --删表
drop database db_name --删数据库
六、条件语句(if…else，case)(DCL)
1．If条件语句
语法：
if logical expression
expressions1
[else
expressions2]
例句：
if (select count(1) from xs_student where xb not in('男','女'))>0
begin
print '这些学生的性别没有正确填写：'
select xh,xm,bh from xs_student where xb not in('男','女')
end
else
print '所有的学生性别都正确填写了'
说明：
如果逻辑判断表达式返回的结果为真，那么执行sql语句组1，否则执行sql语句组2；else和sql语句组2不是必须的，如果没有else条件，那么当逻辑判断表达式返回的结果是假的，就什么操作也不做。
2．Case条件语句
语法：
case
{when logical_expression then relust_expression}
[…n]
else relust_expression
end
例句：
select (case when mz='汉族' then '汉族学生' else '其他民族学生' end) '民族',
xh,xm,bh
from xs_student
order by mz
--将汉族学生区分出来
update xs_student set xkmm=(case datalength(sfzh)
when 15 then substring(sfzh,7,6)
when 18 then substring(sfzh,9,6) end)
--将所有学生的选课密码修改为其出生日期
select a.id,a.kcdm,a.xqmc,a.zporiginalresult,a.makeupresult,a.credit,
newcredit=(case fzmc when '百分制' then (case when cast(zporiginalresult as float)>=60 then cast(b.credit as float) else 0 end)
when '五级制' then (case when zporiginalresult in('及格','中','良','优') then cast(b.credit as float) else 0 end)
end)
into zpxf
from jx_score a,jx_scorereport b
where a.scorereportid=b.scorereportid
and a.zporiginalresult<>'' and a.zporiginalresult is not null
and (makeupresult is null or makeupresult='')
--根据分数取学生该得的实际学分
说明：case语句用于实现多种条件选择，可以避免编写多重的if…else嵌套语句。
七、循环语句(while)(DCL)
语法：
while logical expression
begin
expression
[break]
[continue]
end
例句：
while exists (select zpresult from jx_score where zzfz='百分制' and (cast(zpresult as float))<60 and zpresult<>''
and zpresult is not null and cast(zpresult as float)<>0)
begin
update jx_score set zpresult=(cast(zpresult as float))*1.5
where zzfz='百分制' and zpresult<>'' and zpresult is not null and cast(zpresult as float)<60
select min(cast(zpresult as float)) from jx_score where zzfz='百分制' and zpresult<>'' and cast(zpresult as float)<>0
if (select min(cast(zpresult as float)) from jx_score
where zzfz='百分制' and zpresult<>'' and cast(zpresult as float)<>0 and zpresult is not null)>60
break
else
continue
end
print '所有不及格学生的总评成绩按1.5系数都已改为及格以上'
说明：
当逻辑判断表达式为真时，服务器将重复执行sql语句组。Break的作用是在某些条件发生时，立即无条件跳出循环，并开始执行紧跟在end后面的语句；continue的作用是在某些条件发生时，跳出本次循环，并开始执行下一次循环。
八、其他
1．--表示行注释，/*,*/组合表示块注释
2．取服务器时间：select getdate()
3．查看sqlserver版本：select @@version
4．系统存储过程以sp_开头，系统扩展存储过程以xp_开头
5．局部临时表以#开头，全局临时表以##开头
6．局部变量以@开头，全局变量以@@开头，宣称变量用declare，给变量赋值用select或set
7．标识符的名称规则：以字母或下划线_、@、#开头，后带字母或数字或_、@、#、$，不能使用系统保留关键字，内部不允许有空格或特殊符号；对不遵守上述规定的标识符必须使用界定符号[]限定
8．对象的命名规则：[[server].[database].[user].]object_name(依次为：机器名，数据库名，拥有者名，对象名)
9．语句里所使用的任何符号都必须是半角的
10．任何复杂的sql语句都是由基本的sql语句组合在一起
11．对于视图(view)的使用方法和表(table)是一样的
九、补充
1．本讲带领入门sql语法并且听讲人全部理解也只能达到会对常见需求操作的水平，对于公司目前所用产品这些语法基本够了
2．对select更高级更复杂的应用没有深入展开，只能听讲人以后自己进一步钻研，任何大型数据库的强大都体现在查询功能里
3．没有讲解触发器(trigger)、自定义函数(function)、存储过程(procedure)、事务(transaction)、游标(cursor)等其他数据库对象的应用
4．其他系统提供的函数、运算符、系统存储过程、系统表等等要懂得自己查联机帮助
re: mssql培训计划之SQL(DML部分)语句操作篇 
