【数据库应用技术】数据的基本操作

1、第五章 数据的基本操作,创建表格的目的是为了利用表格来存储和管理业务数据。数据的操作重点主要包括数据库表中的数据的增加、修改、删除和查询操作。 数据的添加 数据的修改 数据的删除 数据的简单查询 案例的应用举例,第五章 数据的基本操作,数据的添加 1、在企业管理器中添加数据 展开相关数据库节点，单击“表”图标，在明细窗格中右击需要添加数据的表，弹出快捷菜单，单击“打开表返回所有的行”命令，打开相关表的表格窗口即可添加数据。 2、在查询分析器中添加数据 基本语法：INSERT INTO table_name ( column_list ) VALUES ( expression ,.n ) ,第

2、五章 数据的基本操作,数据的添加 语法解释： INTO：一个可选的关键字，可以将它用在 INSERT 和目标表之间 table_name：将要接收数据的表或 table 变量的名称。 (column_list)：要在其中插入数据的一列或多列的列表。必须用圆括 号将column_list括起来，并且用逗号进行分隔。 VALUES：引入要插入的数据值的列表。对于column_list（如果已 指定）中或者表中的每个列，都必须有一个数据值。必 须用圆括号将值列表括起来。如果 VALUES 列表中的值 与表中列的顺序不相同，或者未包含表中所有列的值， 那么必须使用column_list明确地指定存储每

3、个传入值的 列。,第五章 数据的基本操作,数据的添加 举例： 1、最简单的INSERT 语句 在系部表中添加一行记录，查询代码如下： USE STUDENT GO INSERT INTO 系部 (系部代码,系部名称,系主任) VALUES (01,计算机系,杨学全) GO,第五章 数据的基本操作,数据的添加 举例： 2、省略清单的INSERT语句，如果所添加的数据包含全部的数据项 时，可以省略数据项的表达清单。 在系部表中添加一行记录，查询代码如下： USE STUDENT GO INSERT INTO 系部 VALUES (02,经济管理系,崔喜元) GO,第五章 数据的基本操作,数据的添加

4、 举例： 3、省略VALUES清单的INSERT语句。在T-SQL中，有一种简单的 插入多行的方法。这种方法是使用SELECT语句查询出的结果 代替VALUES子句。这种方法的语法结构如下： INSERT INTO table_name (column_name1,column_name2,column_namen) SELECT column_name ,n FROM table_name WHERE search_conditions 功能是从某个表中选择符合条件的数据插入table_name中，这样 可以插入成批数据。,第五章 数据的基本操作,数据的添加 举例： 创建“系部”表的一个副本

5、“系部1”表，将“系部”表的全部数据添加到“系部1”表中。代码如下： CREATE TABLE 系部1 (系部代码char(2)CONSTRAINT pk_xbdm1 PRIMARY KEY, 系部名称varchar(30) NOT NULL, 系主任char(8) ) GO INSERT INTO 系部1 (系部代码,系部名称,系主任) SELECT 系部代码,系部名称,系主任 FROM 系部 GO,第五章 数据的基本操作,数据的添加 总结： 1、插入时，表中的列的数据类型要和给定的值必须一一对应，也 要个数对应。 2、在定义表时，如果说明了表中的某个列的属性定义为not null， 则此属

6、性列上不能取空值，否则出错。 3、如果在插入时省略列清单，则新插入的记录必须在每个属性列 上均有值，且顺序也要与表中定义的顺序一致。 4、如果表中的列定义为IDENTITY属性，则在插入时，不能人为 的插入值。所以此表在插入时，列的清单不能省略。,第五章 数据的基本操作,数据的修改 1、在企业管理器中修改数据 展开相关数据库节点，单击“表”图标，在明细窗格中右击需要添加数据的表，弹出快捷菜单，单击“打开表返回所有的行”命令，打开相关表的表格窗口即可对相应的数据进行修改。 2、在查询分析器中修改数据 UPDATE table_name SET column_name = expression |

7、 DEFAULT | NULL ,.n FROM ,.n WHERE := table_name AS table_alias WITH ( ,.n ) ,第五章 数据的基本操作,数据的修改 语法解释： table_name：需要更新的表的名称。 SET：指定要更新的列或变量名称的列表。 column_name：含有要更改数据的列的名称。 expression | DEFAULT | NULL ：列值表达式。 ：修改数据来源表。 例：将教学计划表中专业代码为0101的“启始周”的值修改为2，代码如下： USE STUDENT GO UPDATE 教学计划 SET 启始周=2 WHERE 专业代

8、码=0101 GO,第五章 数据的基本操作,数据的删除 1、在企业管理器中删除数据 展开相关数据库节点，单击“表”图标，在明细窗格中右击需要添 加数据的表，弹出快捷菜单，单击“打开表返回所有的行”命令， 打开相关表的表格窗口即可对相应的数据删除操作。 2、在查询分析器中删除数据 语法如下： DELETE table_name FROM ,.n WHERE := table_name AS table_alias ,.n ) ,第五章 数据的基本操作,数据的删除 语法解释： table_name：是要从其中删除行的表的名称。 FROM ：指定附加的 FROM 子句。 table_name AS

9、table_alias ：是为删除操作提供标准的表名。 WHERE：指定用于限制删除行数的条件。如果没有提供 WHERE 子句，则 DELETE 删除表中的所有行。 ：指定删除行的限定条件。对搜索条件中可以包含的谓词数量没有限制。 功能：就是从指定的表中删除符合条件的所有元组，如果省略 WHERE子句，表示删除表中所有元组。,第五章 数据的基本操作,数据的删除 功能：就是从指定的表中删除符合条件的所有元组，如果省略WHERE子句，表示删除表中所有元组。 例： 删除教师表中没有姓名的记录。代码如下： USE STUDENT GO DELETE 教师 WHERE 姓名 IS NULL GO,第五章

10、 数据的基本操作,数据的删除 清空数据库操作 语法注释： TRUNCATE TABLE：为关键字 table_name：为要删除所用记录的表名。 delete：此操作在日志当中有记录，可以进行恢复 truncate：此操作在日志当中没有记录，不能进行数据恢复 例：用TRUNCATE TABLE 语句清空课程注册表。代码如下： USE STUDENT GO TRUNCATE TABLE 课程注册 GO,第五章 数据的基本操作,数据的查询操作 语法形式如下： SELECT select_list INTO new_table_name FROM table_list WHERE search_co

11、nditions GROUP BY group_by_list HAVING search_conditions ORDER BY order_list ASC | DESC ,第五章 数据的基本操作,数据的查询操作 常用语句的用法解释： 1、 SELECT子句用于指定所选择的要查询的特定表中的 列，它可以是星号（*）、表达式、列表、变量等。 2、INTO子句用于指定所要生成的新表的名称。 3、FROM子句用于指定要查询的表或者视图，最多可以指定16个表或者视图，用逗号相互隔开。 4、WHERE子句用来限定查询的范围和条件。 5、GROUP BY子句是分组查询子句。 6、HAVING子句用于指

12、定分组子句的条件。,第五章 数据的基本操作,数据的查询操作 1、输出表中的所有列 查询“学生表”中全体学生的记录。代码如下： USE STUDENT GO SELECT * FROM 学生 GO 2、输出表中部分列 查询全体教师的教师编号和姓名。代码如下： USE STUDENT GO SELECT 教师编号,姓名 FROM 教师 GO,第五章 数据的基本操作,数据的查询操作 3、为结果集内的列指定别名 查询学生表中全体学生的姓名及年龄。代码如下： USE STUDENT GO SELECT 姓名,YEAR(GETDATE()-YEAR(出生日期) AS 年龄 FROM 学生 GO 4、消除取

13、值重复的行 查询选修了课程的学生号。程序清单如下： USE STUDENT GO SELECT DISTINCT 学号 FROM 课程注册 GO,第五章 数据的基本操作,数据的查询操作 5、限制返回行数 查询课程注册表中的前三条记录的信息。代码如下： USE STUDENT GO SELECT TOP 3 PERCENT * FROM 课程注册 GO,第五章 数据的基本操作,数据的查询操作 常用的WHERE查询条件 ：,第五章 数据的基本操作,数据的查询操作 1、比较大小 比较运算符是比较两个表达式大小的运算符,它各运算符的含义是 =（等于），（大于） ，=（大于或等于），（不等于） !=（不

14、等于），! （不大于） 逻辑运算符NOT可以与表较运算符同用，对条件求非。 2、多重条件查询 用户可以使用逻辑运算符AND、OR、NOT 连接多个查询条件，实 现多重条件查询。 逻辑运算符使用格式如下： NOT 逻辑表达式 AND | OR NOT 逻辑表达式,第五章 数据的基本操作,数据的查询操作 例：查询课程注册表成绩大于50分的记录。代码如下： USE STUDENT GO SELECT * FROM 课程注册 WHERE 成绩=50 GO 例：查询课程注册表中0001成绩在60分到65分之间的同学的学号。 USE STUDENT GO SELECT 学号,成绩 FROM 课程注册 WH

15、ERE 课程号=0001 AND 成绩=60 AND 成绩75 GO,第五章 数据的基本操作,数据的查询操作 3、确定范围 范围运算符BETWEENAND和 NOT BETWEENAND可以 查找属性值在（或不在）指定的范围内的记录。其中BETWEEN后 是范围的下限（即低值），AND后是范围的上限（即高值）。 例：查询出生日期在1980年至1982年之间的学生姓名、学号和出生日 期。代码如下： USE STUDENT GO SELECT 姓名,学号,出生日期 FROM 学生 WHERE 出生日期 BETWEEN 1980-01-01 AND 1982-12-31 GO,第五章 数据的基本操作

16、,数据的查询操作 4、确定集合 确定集合运算符IN和NOT IN可以用来查找属性值属于(或不属于) 指定集合的记录， 例：查询计算机系、经济管理系的班级名称与班级编号。代码如下： USE STUDENT GO SELECT 班级代码,班级名称 FROM 班级 WHERE 系部代码 IN(01,02) GO,第五章 数据的基本操作,数据的查询操作 5、字符匹配 T-SQL提供了字符匹配运算符LIKE进行字符串的匹配运算，实现这类模糊查询。其一般语法格式如下： NOT LIKE ESCAPE 其含义是查找指定的属性列值与相匹配的记录。可以是一个完整的字符串，也可以是含有通配符%和_，其中通配符包括

17、如下四种： % ：代表任意长度的字符串（长度可以是0）的字符串。 _ ：代表任意单个字符。 ：表示集合，即方括号里列出的任意一个字符。也可以是字符范围 ：表示非集合，即不在方括号里的列出的任意一个字符。 例：查询学生中姓“王”的同学的信息。代码如下： SELECT * FROM 学生 WHERE 姓名 LIKE 王% GO,第五章 数据的基本操作,数据的查询操作 5、字符匹配 如果用户要查询的字符串本身就含有%或_ _，这时就需要使用ESCAPE 短语对通配符进行转义了 。 例：有一门课程的名称是“Delphi_6.0”，那么查询它的课程号和课程名 INSERT INTO 课程 (课程号, 课

18、程名, 备注) VALUES (0005, Delphi_6.0, 程序设计) GO SELECT 课程号,课程名 FROM 课程 WHERE 课程名 LIKE Delphi/_6.0 ESCAPE/ GO ESCAPE / 短语表示 /是换码字符，这样匹配串中紧跟在/之后的字符“_”不再具有通配符的含义，转意为普通的“_”字符。,第五章 数据的基本操作,数据的查询操作 6、涉及空值的查询 有时某些列可能暂时没有确定的值，这时用户可以不输入该列的值，那么这列的值为NULL。空值运算符 IS NULL 用来判断指定的列值是否为空。 例：查询班级表中备注字段为空的班级信息。代码如下： SELECT

19、 * FROM 班级 WHERE 备注 IS NULL GO 这里的IS 运算符不能用（=）代替。,第五章 数据的基本操作,数据的查询操作 1、对查询结果进行排序ASC | DESC 升序|降序： 例：查询全体学生信息，查询结果按所在班级代码的降序排列，同一个班的按照升序排列。 SELECT * FROM 学生 ORDER BY 班级代码 DESC ,学号 ASC GO,第五章 数据的基本操作,数据的查询操作,2、使用集合函数对查询结果数据统计： 例：查询学生的成绩平均分。代码如下： SELECT AVG(成绩) AS 平均分 FROM 课程注册 GO,第五章 数据的基本操作,数据的查询操作

20、3、对结果进行分组，并对分组以后的结果用HAVING进行条件的筛选 例 : 查课程注册表中课程选课人数3人以上的各个课程号和相应的 选课人数。代码如下：,SELECT 课程号 ,COUNT(*) AS 选课人数 FROM 课程注册 GROUP BY 课程号 HAVING COUNT(*)=3,第五章 数据的基本操作,数据的查询操作 4、使用COMPUTE或COMPUTE BY子句进行汇总 例：查询所有学生所有成绩的总和。代码如下： SELECT * FROM 课程注册 ORDER BY 学号 COMPUTE SUM(成绩) GO,第五章 数据的基本操作,数据的查询操作 5、用查询结果生成新表

21、例： 创建一个空的学生表的 副本。代码如下： SELECT * INTO 学生副本 FROM 学生 WHERE 1=2 GO,第五章 数据的基本操作,数据的查询操作 6、合并结果集 例：查询课程注册表中0102专业的学生学号及课程成绩大于78分的学生学号。 SELECT * FROM 课程注册 WHERE 专业代码=0102 UNION SELECT * FROM 课程注册 WHERE 成绩78 GO,第五章 数据基本操作,数据基本操作的概述 等值与非等值连接查询 自身连接查询 外连接查询 复合连接条件查询 子查询,第五章 数据基本操作,数据基本操作的概述 笛卡儿乘积_ 不带连接谓词的连接，两

22、个表元组的交叉乘积。又称为非限制连接查询。结果集的列为两个表属性列的和。 9行（结果）=3行（表1）*3行（表2）,产品名称,第五章 数据基本操作,数据基本操作的概述,第五章 数据基本操作,数据基本操作的概述 一般情况下笛卡儿乘积返回的结果大多数是冗余的，没有实际的应用意义的，所以这种连接世界使用的很少。所以要避免笛卡儿乘积的出现，给出where条件来限制查询结果。 等值与非等值连接查询 自身连接查询 外连接查询 复合连接条件查询,第五章 数据基本操作,等值与非等值连接查询 连接查询过程当中，连接两个表的条件为“=”时，称为等值连接。使用其他运算符的连接成为非等值连接。 等值连接运算符： =

23、非等值连接运算符： ，= ，= ，!= 在连接查询的过程当中，可以根据实际情况使用任意的合法的条件运算符（逻辑条件、确定范围、确定集合、字符匹配等条件）。等值连接的过程类似于交叉连接，只是 在连接的过程当中只拼接满足条件的记录到结果集当中。,第五章 数据基本操作,等值与非等值连接查询 等值连接的基本语法： SELECT 列名列表 FROM 表名1 INNER JOIN 表名2 ON 表名1.列名=表名2.列名 INNER：可选关键字，指定连接类型为内连接。 表名1 JOIN 表名2 ：等值连接对象的连接体。 ON 表名1.列名=表名2 .列名 ：是等值连接的连接条件，通常情况下 为“ON 主键

24、=外键”。,第五章 数据基本操作,等值与非等值连接查询 例 ：用等值连接的方法连接“产品”表和“产品销售”表，代码如下： USE STUDENT GO SELECT * FROM 产品 JOIN 产品销售 ON 产品.产品编号 = 产品销售.产品编号,第五章 数据基本操作,等值与非等值连接查询 通过上面的例子可以看到在结果集当中“产品编号”为重复列，在等值连接中，把目标列中重复的列去掉则为自然连接。 例 ：自然连接产品表和产品销售表。代码如下： SELECT 产品.产品编号 , 产品名称 , 销量 FROM 产品 JOIN 产品销售 ON 产品.产品编号=产品销售.产 品编号,第五章 数据基本

25、操作,自身连接查询 连接操作不仅可以在两个不同的表之间进行，也可以是一个表与自身进行的连接，称为表的自身连接查询。使用自身连接时，必须为表指定两个别名。 例：查询选修了两门或两门以上课程的学生学号和课程号。 代码如下： USE STUDENT GO SELECT a.学号 , a.课程号 FROM 课程注册 AS a JOIN 课程注册 AS b ON a.学号=b.学号 AND a.课程号!=b.课程号 GO,第五章 数据基本操作,自身连接查询 结果：,第五章 数据基本操作,外连接查询 在通常的连接操作中，只有满足条件的记录才能在结果集里输出。如前边的例子，产品0002键盘及产品0004鼠标

26、没有在结果集里出现，因为这些产品没有在产品销售表中出现，也就是没有产品编号为0002和0004的销售记录。但是，用户有时想以产品表为主体列出产品的销售情况，若某个产品没有销售量，则只输出产品的基信息，其销售量为空值即可。这时就需要外连接。 外连接有分为左外连接、右外连接、全外连接三种。外连接除产生内连接生成的结果集外，还可以使一个表（左、右外连接）或两个表（全外连接）中的不满足连接条件的记录也出现在结果集中。,第五章 数据基本操作,外连接查询 1、左外连接 左外连接就是将左表作为主表，主表中所有记录分别与右表的每一条记录进行连接组合，结果集中除了满足连接条件的记录外，还有主表中不满足连接条件的

27、记录并在右表的相应列上填充NULL值。 左外连接的语法： SELECT 列名列表 FROM 表名1 LEFT OUTER JOIN 表名2 ON 表名1.列名=表名2.列名,第五章 数据基本操作,外连接查询 例：将产品表左外连接产品销售表。代码如下： SELECT * FROM 产品 LEFT OUTER JOIN 产品销售 ON 产品.产品编号=产品销售.产品编号 结果：,第五章 数据基本操作,外连接查询 2、右外连接 右外连接就是将右表作为主表，主表中所有记录分别与左表的每一条记录进行连接组合，结果集中除了满足连接条件的记录外，还有主表中不满足连接条件的记录并在左表的相应列上填充NULL值

28、。 右外连接的语法如下： SELECT 列名列表 FROM 表名1 RIGHT OUTER JOIN 表名2 ON 表名1.列名=表名2.列名,第五章 数据基本操作,外连接查询 例：将产品表右外连接产品销售表。代码如下： SELECT * FROM 产品 RIGHT OUTER JOIN 产品销售 ON 产品.产品编号=产品销售.产品编号 GO 结果：,第五章 数据基本操作,外连接查询 3、全外连接 全外连接就是将左表所有记录分别与右表的每一条记录进行连接组合，结果集中除了满足连接条件的记录外，还有左、右表中不满足连接条件的记录并在左、右表的相应列上填充NULL值。 全外连接的语法如下： SE

29、LECT 列名列表 FROM 表名1 FULL OUTER JOIN 表名2 ON 表名1.列名 = 表名2.列名,第五章 数据基本操作,外连接查询 例：用全连接的方法连接产品表与产品销售表。代码如下： SELECT * FROM 产品 FULL OUTER JOIN 产品销售 ON 产品.产品编号=产品销售.产品编号 GO 结果：,第五章 数据基本操作,复合连接条件查询 在上面讲述的各个连接查询中，ON 连接条件表达式只有一个条件，ON连接表达式中可以有多个连接条件，称为复合连接条件。连接操作除了可以是两个表的连接，一个表与其自身的连接外，还可以是两个以上的表进行连接，我们称之为多表连接。

30、在多表连接的过程当中，使用合法正确的连接条件，称之为复合连接条件查询。,第五章 数据基本操作,复合连接条件查询 例：查询成绩在75分以上的学生的学号、姓名，选修课的课程号、课 程名、专业代码、成绩，任课教师的教师编号、姓名。代码如下： SELECT B.课程号,C.教师编号,C.姓名,A.学号,A.姓名,B.专业代码 ,B.专业学级,B.学期,B.成绩 FROM 学生 AS A JOIN 课程注册 AS B ON A.学号=B.学号 AND B.成绩75 JOIN 教师 AS C ON B.教师编号=C.教师编号 GO,第五章 数据基本操作,子查询 子查询又称为嵌套查询即将一个查询块嵌套在另一

31、个查询块的WHERE子句或HAVING子句的条件中，这样的查询语句称为子查询。 例：,查询选修了0002号课程的学生的学生姓名 子查询可以嵌套在外部 SELECT、INSERT、UPDATE 或 DELETE 语句的 WHERE 或 HAVING 子句内，或者其它子查询中。,第五章 数据基本操作,子查询 1、带有IN运算符的子查询 在带有IN运算符的子查询中，子查询的结果是一个结果集。父查询通过IN运算符将父查询中的一个表达式与子查询结果集中的每一个值进行比较，如果相等，父查询中的查询条件表达式返回TRUE ，否则返回FALSE。NOT IN运算符与IN运算符结果相反。,第五章 数据基本操作,

32、子查询 1、带有IN运算符的子查询 例：使用产品和产品销售两个表，查询有产品销售记录的产品信息。查询代码如下： USE STUDENT GO SELECT * FROM 产品子查询 WHERE 产品编号 IN (SELECT 产品编号 FROM 产品销售 ),第五章 数据基本操作,子查询 2、带有比较运算符的子查询 在带有比较运算符的子查询中，子查询的结果是一个单值。父查询通过比较运算符将“父查询”中的表达式与子查询结果（单值）进行比较，如果相等运算的结果为TRUE，否则返回FALSE。 常用的比较运算符有： ，= ， ，!= ，! ，! 。,第五章 数据基本操作,子查询 2、带有比较运算符的子