数据的基本操作教学课件

21、没有人陪你走一辈子，所以你要适应孤独，没有人会帮你一辈子，所以你要奋斗一生。22、当眼泪流尽的时候，留下的应该是坚强。23、要改变命运，首先改变自己。24、勇气很有理由被当作人类德性之首，因为这种德性保证了所有其余的德性。--温斯顿．丘吉尔。25、梯子的梯阶从来不是用来搁脚的，它只是让人们的脚放上一段时间，以便让别一只脚能够再往上登。数据的基本操作数据的基本操作21、没有人陪你走一辈子，所以你要适应孤独，没有人会帮你一辈子，所以你要奋斗一生。22、当眼泪流尽的时候，留下的应该是坚强。23、要改变命运，首先改变自己。24、勇气很有理由被当作人类德性之首，因为这种德性保证了所有其余的德性。--温斯顿．丘吉尔。25、梯子的梯阶从来不是用来搁脚的，它只是让人们的脚放上一段时间，以便让别一只脚能够再往上登。数据的基本操作5.4子查询5.3连接查询第五章－－数据的基本操作5.1数据的添加、修改和删除5.2简单查询目录5.1数据的添加、修改和删除SQLServer数据厍的新表建好后，表中并不包含任何记录，要想实现数据的存储，必须向表中添加数据。同样要实现表的良好管理，则经常需要修改表中的数据。本节主要介绍数据的添加、修改和删除。在数据的基本操作中，常用到Transact-SQL语句，我们应先掌握如表所示的SQL语句的语法规则。图5.2“专业”表结构图5.3.1简单添加数据语句

图5.3.2企业管理器中查看运行结果

【例5.2】在结构如图5.4所示的“班级”表中添加2004级电子商务班，查询代码如下：USEstudentGOINSERT班级图5.4“班级”表结构VALUES('20041521','2004电子商务班','0103','01','<null>')GO

图5.4“班级”表结构

图5.5执行添加数据语句后的结果

【例5.3】创建“课程”表的一个副本“课程l”表，将“课程”表的全部数据添加到“课程l”表中。代码如下：USEstudentGOCREATEtable课程1(课程号char(4)notnull,课程名char(20)notnull,学分smallintnull)GOINSERTINTO课程1(课程号,课程名,学分)SELECT课程号,课程名,学分FROM课程GO图5.6增加多行数据语句执行结果将上述代码在查询分析器中运行，用户可以看到在“课程l”中增加了4行数据，如图5.6所示。（1）向“系部”表中添加以下如图5.7所示的四条记录，代码如下：USEStudentGOINSERT系部(系部代码,系部名称,系主任)VALUES(‘01’,‘计算机系’,‘徐才智’)GOINSERT系部(系部代码,系部名称,系主任)VALUES('02','经济管理系','张博')GO

INSERT系部(系部代码,系部名称,系主任)

VALUES('03','数学系','徐裕光')

GO

INSERT系部(系部代码,系部名称,系主任)

VALUES('04','外语系','李溅波')

GO图5.7表结构及增加四条记录及执行结果图5.7表结构及增加四条记录及执行结果(2)向“专业”表添加以下如图5.8所示的七条记录，代码如下：USEStudentGOINSERT专业(专业代码,专业名称,系部代码)VALUES('0101','软件工程','01')GOINSERT专业(专业代码,专业名称,系部代码)VALUES('0102','信息管理','01')GOINSERT专业(专业代码,专业名称,系部代码)VALUES('0201','经济管理','02')GOINSERT专业(专业代码,专业名称,系部代码)VALUES('0202','会计','02')GOINSERT专业(专业代码,专业名称,系部代码)VALUES('0203','工商管理','02')GOINSERT专业(专业代码,专业名称,系部代码)VALUES('0301','经济数学','03')GOINSERT专业(专业代码,专业名称,系部代码)VALUES('0401','国际商贸英语','04')GO图5.8表结构及增加七条记录及执行结果

图5.8表结构及增加七条记录及执行结果

3）向“班级”表添加以下如图5.9所示的四条记录，代码如下：USEstudentGOINSERT班级（班级代码,班级名称,专业代码,系部代码,备注）VALUES('010101001','01级软件工程001班','0101','01','<null>')GOINSERT班级（班级代码,班级名称,专业代码,系部代码,备注）VALUES('010102002','01级信息管理002班','0102','01','<null>')GOINSERT班级（班级代码,班级名称,专业代码,系部代码,备注）VALUES('010201001','01级经济管理001班','0201','02','<null>')GOINSERT班级（班级代码,班级名称,专业代码,系部代码,备注）VALUES('010202001','01级会计002班','0202','02','<null>')GO图5.9表结构及增加四条记录及执行结果图5.9表结构及增加四条记录及执行结果（4）向“学生”表添加以下如图5.10所示的四条数据记录：USEstudentGOINSERT学生(学号,姓名,性别,出生日期,入学时间,班级代码,系部代码,专业代码)VALUES(‘010101001001’,‘张斌’,‘男’,‘1970-5-4’,‘2001-9-18’,‘010101001’,‘01’)GOINSERT学生VALUES(‘010102002001’,‘周红瑜’,‘女’,‘1972-7-8’,‘2001-9-18’,‘010102002’,‘01’)GOINSERT学生VALUES(‘010201001001’,‘贾凌云’,‘男’,‘1974-9-1’,‘2002-9-18’,‘010201001’,‘02’)GOINSERT学生VALUES(‘010202002001’,‘向雪林’,‘女’,‘1976-10-1’,‘2002-9-18’,‘010202002’,‘02’)GO图5.10表结构及增加四条记录及执行结果

（5）向“学生”表添加以下如图5.11所示的四条数据记录：USEstudentGOINSERT课程(课程号,课程名,学分)VALUES(‘0001’,’大学语文’,’4’)GOINSERT课程VALUES(‘0002’,’高等数学’,’4’)GOINSERT课程(课程号,课程名,学分)VALUES(‘0003’,’计算机基础’,’4’)GOINSERT课程(课程号,课程名,学分)VALUES(‘0004’,’数据库概论’,’4’)GO图5.11表结构及增加四条记录及执行结果

5.1.2数据的修改

在数据输入过程中，可能会出现输入错误，或是因为时间变化而需要更新数据。这都需要修改数据。修改表中的数据可以使用企业管理器、查询设计器的图形界面进行修改，也可以使用查询设计器的“打开表”窗口修改(参见第四章)。这里我们主要介绍T-SQL的UPDATE语句实现修改的方法，UPDATE的语法格式如下：UPDATEtable_nameSET{column_name={expression|DEFAULT|NULL]}[，…n][FROM{<table_source>}[,…n]][WHERE<search_condition>]<table_source>::=Table_name[[AS]table_alias][WITH(<table_hint>[,…n])]【例5.4】将“教学计划”表中专业代码为“0101”的“开课学期”的值改为第2学期，代码如下：USEstudentGOUPDATE教学计划SET开课学期=2WHERE专业代码=’0101’GO【例5.5】将“课程注册”表中所有记录的成绩值改为(“注册号”-10000415)表达式的值，学分为3分，代码如下：USEstudentGOUPDATE课程注册SET成绩=(注册号-10000415),学分=3GO5.1.3数据的删除

随着系统的运行，表中可能产生一些无用的数据，这些数据不仅占用空间，而且还影响查询的速度，所以应该及时地删除它们。删除数据可以使用DELETE语句和TRUNCATETABLE语句。1.使用DELETE语句删除数据

从表中删除数据，最常用的是DELETE语句。DELETE语句的语法格式如下：DELETEtable_name[FROM{<table_source>}[,…n]][WHERE{<search_condition>}]<table_source>::=table_name[[AS]table_alias][,…n]]【例5.6】删除“课程注册”表中的所有记录。代码如下：USEstudentGODELETE课程注册GO【例5.7】删除“教师”表中没有姓名的记录。代码如下：USEstudentGODELETE教师WHERE姓名ISNULLGO2.使用TRUNCATETABLE清空表格

使用TRUNCATETABLE语句删除所有记录的语法格式为：

TRUNCATETABLEtable_name【例5.9】用TRUNCATETABLE语句清空“课程注册”表。代码如下：USEstudentGOTRUNCATETABLE课程注册GO5.2简单查询

数据库存在的意义在于将数据组织在一起，以方便查询。“查询”的含义就是用来描述从数据库中获取数据和操纵数据的过程。SQL语言中最主要、最核心的部份是它的查询功能。查询语言用来对已经存在于数据库的数据按照特定的组合、条件表达式或者一定次序进行检索。其基本格式是由SELECT子句、FROM子句和WHERE子句组成的SQL查询语句：SELECT<列名表>FROM<表或视图名>WHERE<查询限定条件>5.2.1完整的SELECT语句的基本语法格式

虽然SELECT语句的完整语法较复杂，但是其主要的语法格式可归纳如下：SELECTselect_list

[INTOnew_table_name]FROMtable_list[WHEREsearch_conditions][GROUPBYgroup_by_expression][HAVINGsearch_conditions][ORDERBYorder_expression[ASC|DESC]]5.2.2选择表中的若干列

选择表中的全部列或部分列这就是表的投影运算。这种运算可以通过SELECT子句给出的字段列表来实现。字段列表中的列可以是表中的列，也可以是表达式列。所谓表达式列就是多个列运算后产生的列或者是利用函数计算后所得的列。1.输出表中的所有列

2.输出表中部分列

【例5.10】查询“学生”表中全体学生的记录。代码如下：USEstudentGOSELECT*FROM学生GOUSEstudentGOSELECT教师编号,姓名,职称FROM教师GO3.

为结果集内的列指定别名所有列

【例5.12】

查询“教师”表中全体教师的姓名及年龄。代码如下：USEStudentGOSELECT姓名,YEAR(GETDATE())-YEAR(出生日期)AS年龄FROM教师GO图5.18带有别名的查询5.2.3选择表中的若干记录

选择表中的若干记录这就是表的选择运算。这种运算可以通过增加一些谓词(例如WHERE子句)等来实现。1.

消除取值重复的行

【例5.13】查询选修了课程的学生号。查询代码如下：USEStudentGOSELECT学号

FROM课程注册GOUSEStudentGOSELECTDISTlNCT学号FROM课程注册GO上述代码执行结果如图5.19所示，选课的学生号有重复，共有16行记录。下面的代码就去掉了重复学号，仅有4行记录，执行结果如图5.20所示。

2.

限制返回行数【例5.14】

查询“课程注册”表中的前3条记录的信息。代码如下：USEStudentGOSELECTTOP3*FROM课程注册GO3.

查询满足条件的元组【例5.15】

查询“课程注册”表成绩大于50分的记录。代码如下：USEStudentGOSELECT*FROM课程注册WHERE成绩>=50GO5.2.4对查询的结果排序

用户可以使用ORDERBY子句对查询结果按照一个或多个属性列的升序(ASC)或降序(DESC)排列，默认为升序。如果不使用ORDERBY子句，则结果集按照记录在表中的顺序排列。ORDERBY子句的语法格式如下：ORDERBY{列名[ASC|DESC]}[，…n]【例5.22】查询选修了“0001”号课程的同学的学号，并按成绩的降序排列。代码如下：USEStudentGOSELECT学号,成绩FROM课程注册WHERE课程号=’0001’ORDERBY成绩DESCGO5.2.5对数据进行统计

用户经常需要对结果集进行统计，例如求和、平均值、最大值、最小值、个数等，这些统计可以通过集合函数、COMPUTE子句、GROUPBY子句来实现。1.

1．

使用集合函数

【例5.24】

查询“教师”表中教师总数。代码如下：USEStudentGOSELECTCOUNT(*)AS教师总数FROM教师GO2.

对结果进行分组

【例5.26】查询“课程注册”表中课程选课人数4人以上的各个课程号和相应的选课人数。代码如下：USEStudentGOSELECT课程号,COUNT(*)AS选课人数图5.33分组统计FROM课程注册GROUPBY课程号HAVINGCOUNT(*)>=4GO3.

使用COMPUTE子句

【例5.27】查询所有学生所有成绩的总和。代码如下：SELECT*FROM课程注册ORDERBY学号COMPUTESUM(成绩)GO5.2.6用查询结果生成新表

在实际的应用系统中，用户有时需要将查询结果保存成一个表，这个功能可以通过SELECT语句中的INTO子句实现。INTO子句语法格式如

INTO新表名【例5.29】创建“课程注册”表的一个副本。代码如下：USEStudentGOSELECT*INTO课程注册副本FROM课程注册GOSELECT*FROM课程注册副本GO5.2.7合并结果集使用UNION语句可以将多个查询结果集合并为一个结果集，也就是集合的合并操作。UNION子句的语法格式如下：

SELECT语句{UNIONSELECT语句}[，…n]【例5.31】查询“课程注册”表中0102专业的学生学号及课程成绩大于70分小于79分的学生学号，且按成绩降序排列记录。代码如下：SELECT*FROM课程注册WHERE专业代码='0102'UNIONSELECT*FROMWHERE成绩>=70And成绩<79ORDERBY成绩DESCGO5.3连接查询

前面所讲的查询是单表查询。若一个查询同时涉及两个或两个以上的表，则称为连接查询。连接查询是关系数据库中最主要的查询，包括等值与非等值查询、自然连接、自身连接查询、外连接查询和复合条件连接查询等。5.3.1交叉连接查询

交叉连接又称非限制连接，也叫广义笛卡尔积。两个表的广义笛卡尔积是两表中记录的交叉乘积，结果集的列为两个表属性列的和，其连接的结果会产生一些没有意义的记录，并且进行该操作非常耗时。因此该运算实际很少使用，仅供对读者理解交叉连接过程之用。

交叉连接的连接过程学号

姓名

性别

系部代码

专业代码

010101001001

张斌

男

01

0101

010102002001

周红瑜

女

01

0102

010201001001

贾凌云

男

02

0201

010202002001

向雪林

女

02

0202

专业代码

专业名称

系部代码

0101

软件工程

01

0102

信息管理

01

0201

经济管理

02

0202

会计

02

表5.2“学生”表

表5.3“专业”表学号

姓名

性别

系部代码

专业代码

专业代码*

专业名称系部代码*010101001001

张斌

男

01

01010101软件工程

01

010102002001

周红瑜女

01

0102

0101软件工程

01

010201001001

贾凌云男

02

0201

0101软件工程

01

010202002001

向雪林女

02

0202

0101软件工程

01

010101001001

张斌男

01

0101

0102

信息管理01

010102002001

周红瑜女

01

0102

0102

信息管理01

010201001001

贾凌云男

02

0201

0102

信息管理01

010202002001

向雪林女

02

0202

0102

信息管理01

010101001001

张斌男

01

0101

0201

经济管理02

010102002001

周红瑜女

01

0102

0201

经济管理02

010201001001

贾凌云男

02

0201

0201

经济管理02

010202002001

向雪林

女02

0202

0201

经济管理02010101001001

张斌

男

01

01010202

会计

02010102002001

周红瑜女01

0102

0202

会计

02010201001001

贾凌云男

02

0201

0202

会计

02010202002001

向雪林

女02

0202

0202

会计

02表5.4交叉连接的结果表

执行连接操作的过程如下：把“学生”表中每一条记录取出（共有4条记录），与“专业”表中的第一条记录拼接，形成表5.4的前4条记录；同样地，再取出“学生”表中每一条记录，与“专业”表中的第二条、第三条、第四条记录分别拼接，从而形成表5.4的后12条记录（共形成了4×4＝16条记录）。2．

交叉连接的语法格式交叉连接的语法格式：SELECT列表列名FROM表名1CROSSJOIN表名2其中，CROSSJOIN为交叉表连接关键字。

【例5.33】使用上例中的“学生”

表、“专业”表，实现交叉查询。代码如下：USEstudentGOSELECT学号,姓名,性别,学生.系部代码,学生.专业代码,专业.专业代码,专业名称,专业.系部代码FROM学生CROSSJOIN专业5.3.2等值与非等值连接查询

用来连接两个表的条件称为连接条件或连接谓词，格式为：[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>【例5.34】用等值连接方法连接“学生”表和“专业”表，观察通过专业代码连接后的结果与交叉连接的结果有何区别。代码如下：USEstudentGOSELECT学号,姓名,性别,学生.系部代码,学生.专业代码,专业.专业代码,专业名称,专业.系部代码FROM学生INNERJOIN专业ON学生.专业代码=专业.专业代码5.3.3自身连接查询

连接操作既可在多表之间进行，也可是一个表与其自己进行连接，称为表的自身连接。使用自身连接时，必须为表指定两个别名，以示区别。5.3.4外连接查询

1.

左外连接（LEFTOUTERJOIN）【例5.37】将“学生”表左外连接“专业”表。代码如下：USEstudentGOSELECT学号,姓名,性别,学生.系部代码,学生.专业代码,专业.专业代码,专业名称,专业.系部代码FROM学生LEFTOUTERJOIN专业ON学生.专业代码=专业.专业代码2.

右外连接（RIGHTOUTERJOIN）【例5.38】将“学生”表右外连接“专业”表。代码如下：USEstudentGOSELECT学号,姓名,性别,学生.系部代码,学生.专业代码,专业.专业代码,专业名称,专业.系部代码FROM学生RIGHTOUTERJOIN专业ON学生.专业代码=专业.专业代码3.

完全外连接（FULLOUTERJOIN）

【例5.39】将“学生”表完全外连接“专业”表。代码如下：USEstudentGOSELECT学号,姓名,性别,学生.系部代码,学生.专业代码,专业.专业代码,专业名称,专业.系部代码FROM学生FULLOUTERJOIN专业ON学生.专业代码=专业.专业代码5.3.5复合连接条件查询

上面各个连接查询中，ON连接条件表达式只有一个条件，允许ON连接表达式有多个连接条件，称为复合条件连接，或多表连接。

5.4子查询

5