mssql培训计划之SQL(DML部分)语句操作篇

===========================================================

mssql培训计划之SQL(DML部分)语句操作篇

作者: kyle(http://kyle.itpub.net)
发表于: 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等等。

例表：
假定对score（成绩）、student（学籍卡）、course（课程）、classcourse（科目属性设置表）这四张表操作

一．Select语句
1．语法：
select * from table_name where …group by … having…order by …
语法的执行顺序说明：先执行from字句，然后依次是where、group by、having、order by字句，最后才对结果select
2．普通单表查询
select * from score --取所有的学生成绩
select top 100 * from score --取前100条的学生成绩
3．不重复查询
select distinct xm,mz from student –取姓名和民族同时不重复的学生
4．带条件查询(where后面不可跟聚合函数，比较运算符的右边不可出现集合值)
select * from score where kclx='任选课' and xqmc='2003-2004学年第二学期' –取2003-2004第二学期选择任选课的学生成绩
5．关联查询（为了避免笛卡儿乘积的产生，对于n个表的连接，至少要有n-1个条件；对于重复的字段，则需要在字段前加上表的别名，如a.xm）
select a.xm,a.xb,c.kcmc,b.qmresult from student a,score b,course c where a.id=b.id and b.kcdm=c.kcdm and cast(b.qmresult as float)<60 and b.qmresult <>'' –取参加考试成绩小于60分的学生姓名、性别、课程名称和成绩。这个语句根据ANSI-92标准也可以这样写：
select a.xm,a.xb,c.kcmc,b.qmresult from student a join score b on a.id=b.id join course c on b.kcdm=c.kcdm where cast(b.qmresult as float)<60 and b.qmresult <>''
6．临时表
select * into #temp from score –将学生的成绩存放在一张临时表里
select * into #tmp from score where 1=2 –生成一张与成绩表结构一样的表#tmp
临时表使用完毕，使用drop table #tmp
表变量
declare @temp table(sid int)
insert @temp select 1
select * from @temp
7．分组(group by)、筛选(having)
select mz,count(*)
from student where xb='女'
group by mz
having(count(*))<100
说明：语句先执行where条件，然后在条件里分组（group by）排列，最后在分组里进一步筛选（having）；select选择列表里除聚合函数以外的列都必须出现在group by后面，group by后面不能跟字段别名，不支持任何使用了聚合函数的集合列；having可以包含聚合函数，可以引用选择列表中出现的任意列
8．聚合函数(avg)、分组(group by)、筛选(having)、排序(order by)
select id,kclx,avg(cast(qmresult as float)) as avgresult
from score
where xqmc='2003-2004第二学期' and len(qmresult) in (2,3)
group by id,kclx
having avg(cast(qmresult as float))<60
order by avg(cast(qmresult as float)) desc–取2003-2004学年第一学期参加考试每种课程类型的平均分小于60的学生按分数排序的情况
说明：聚合函数（也称统计函数）共有5个，分别是：avg --求平均值，count --统计数目，max --求最大值，min --求最小值，sum --求和，更具体的用法查看sqlserver联机帮助；排序有两种：asc（升序），desc（降序）
10．并集运算
select xm,mz into #temp from student
union all
select xm,mz from student –将学籍卡里的的学生信息与新生入学登记表里的学生信息合并到临时表#temp里。
说明：union all会取多表并集的重复行；union可以去掉重复行；多表之间的union必须列数相同一一对应，对应的列之间必须可以隐性的转换成相同的数据类型（即兼容）
11．嵌套查询（分为层次嵌套和相关嵌套两种，当嵌套多于2个又称多级嵌套）
⑴．层次嵌套(in,not in,比较运算符)
select * from student
where id in
(select id from score where xqmc='2003-2004第一学期' and qmresult>(select avg(cast(qmresult as float)) from score))
任何的层次查询都可以化解成关联查询，如这题也可以写成：
select distinct a.*
from student a,score b
where a.id=b.id and b.xqmc='2003-2004第一学期' and b.qmresult>(select avg(cast(qmresult as float)) from score)
--求2003-2004第一学期参加期末成绩超过全校历年平均分的学生资料
⑵．相关嵌套(exists,not exists)
select distinct a.xqmc,a.bh,a.kcdm,1,1
from score a
where restudy=0 and
not exists (select 1 from classcourse where xqmc=a.xqmc and bh=a.bh and kcdm=a.kcdm)--原始成绩单里有而科目属性设置里没有的课程
⑶．说明：
in、not in执行的机制是先执行紧跟其后的子查询，然后再执行父查询，判断父查询的关键字是否存在于子查询所得到的集合里；exists、not exists的执行机制是每取得子查询的一条记录马上就与父查询记录进行比较，一直遍历整个子查询到结束，相关查询之间的连接不是列之间的关系，而是表之间的关系，所以在select列表中，通常不需要明确的指定列名，使用*或数字（如1）代替就可以了；使用not in或not exists可以很方便的实现非成员关系型和非存在关系型的查询难题，降低查询复杂度；如果能确定子查询返回的是单值，那么可以使用比较运算符；另外嵌套查询只能放在小括号里；子查询还可以嵌套其他子查询，这就是多级查询，这种层层嵌套的构造正是SQL(Structured Query Language)中“结构化”的含义所在

二．Update语句
1．语法：
update table_name set …from … where…
2．单表更新：
update student set xh='女' where id='000dc023-0812-4aa9-abb8-75b8bcfbc9e9' --改学生性别
3．多表集联更新
update score set qmresult ='0' from student a,score b where a.id=b.id and a.xm='王博宇' --将王博宇的所有期末成绩都改为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 student(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 student where xh='031002023121' –删除某个学生信息
3．集联删除
delete student from student a,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 student where xb not in('男','女'))>0
begin
print '这些学生的性别没有正确填写：'
select xh,xm,bh from 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 student order by mz
说明：case语句用于实现多种条件选择，可以避免编写多重的if…else嵌套语句。

七．循环语句（while）（DCL）
语法：
while logical expression
begin
expression
[break]
[continue]
end
例句：
while exists (select qmresult from score where (cast(qmresult as float))<60 and qmresult<>' ')
begin
update score set qmresult=(cast(qmresult as float))*1.5
select min(qmresult) from score
if (select min(cast(qmresult as float)) from score where ceiling(qmresult)<>0)>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）等其他数据库对象的应用
4．其他系统提供的函数、运算符、系统存储过程、系统表等等要懂得自己查联机帮助

kyle 发表于:2005.02.23 23:16 ::分类: ( dbms ) ::阅读:(3724次) :: 评论 (29)