15.SQL Server数据的基本操作.ppt

1、数据的基本操作,数据的基本操作,5.1数据的增删改 5.2 简单查询 5.3 案例应用举例,5.1数据的增删改,5.1.1数据的添加 5.1.2数据的修改 5.1.3数据的删除,5.1.1数据的添加,语法格式如下： INSERT INTO table_name ( column_list ) VALUES ( expression ,.n ) ,5.1.1数据的添加,语法注释： INTO：一个可选的关键字，可以将它用在 INSERT 和目标表之间。 table_name：将要接收数据的表或 table 变量的名称。 (column_list)：要在其中插入数据的一列或多列的列表。必须用圆括号将

2、column_list括起来，并且用逗号进行分隔。 VALUES：引入要插入的数据值的列表。对于column_list（如果已指定）中或者表中的每个列，都必须有一个数据值。必须用圆括号将值列表括起来。如果 VALUES 列表中的值与表中列的顺序不相同，或者未包含表中所有列的值，那么必须使用column_list明确地指定存储每个传入值的列。,5.1.1数据的添加,1.最简单的INSERT 语句 例5.1：在系部表中添加一行记录，查询代码如下： USE STUDENT GO INSERT INTO 系部 (系部代码,系部名称,系主任) VALUES (01,软件学院,唐伟奇) GO,5.1.1数

3、据的添加,2.省略清单的INSERT语句 例5.2：在系部表中添加一行记录，查询代码如下： USE STUDENT GO INSERT 系部 VALUES (02,经济贸易系,王庆国) GO,5.1.1数据的添加,3.省略VALUES清单的INSERT语句 在T-SQL中，有一种简单的插入多行的方法。这种方法是使用SELECT语句查询出的结果代替VALUES子句。这种方法的语法结构如下： INSERT INTO table_name (column_name1,column_name2,column_namen) SELECT column_name ,n FROM table_name WH

4、ERE search_conditions,5.1.1数据的添加,例5.3: 创建“系部”表的一个副本“系部1”表，将“系部”表的全部数据添加到“系部1”表中。代码如下： USE Student GO CREATE TABLE 系部1 (系部代码char(2)CONSTRAINT pk_xbdm1 PRIMARY KEY, 系部名称varchar(30) NOT NULL, 系主任char(8) ) GO,5.1.1数据的添加,INSERT INTO 系部1 (系部代码,系部名称,系主任) SELECT 系部代码,系部名称,系主任 FROM 系部 GO 4.向学生选课系统各表中添加数据,5.1

5、.2数据的修改,语法格式如下： UPDATE table_name SET column_name = expression | DEFAULT | NULL ,.n FROM ,.n WHERE := table_name AS table_alias WITH ( ,.n ) ,5.1.2数据的修改,语法注释： table_name：需要更新的表的名称。 SET：指定要更新的列或变量名称的列表。 column_name：含有要更改数据的列的名称。 expression | DEFAULT | NULL ：列值表达式。 ：修改数据来源表。,5.1.2数据的修改,例5.4 ：将教学计划表中专业

6、代码为0101的“启始周”的值修改为2，代码如下： USE STUDENT GO UPDATE 教学计划 SET 起始周=2 WHERE 专业代码=0101 GO,5.1.3数据的删除,1.使用DELETE 语句删除数据 DELETE 语句的语法格式如下： DELETE table_name FROM ,.n WHERE := table_name AS table_alias ,.n ) ,5.1.3数据的删除,语法注释： l table_name：是要从其中删除行的表的名称。 l FROM ：指定附加的 FROM 子句。 l table_name AS table_alias ：是为删除操

7、作提供标准的表名。 l WHERE：指定用于限制删除行数的条件。如果没有提供 WHERE 子句，则 DELETE 删除表中的所有行。 l ：指定删除行的限定条件。对搜索条件中可以包含的谓词数量没有限制。,5.1.3数据的删除,例5.5 ：删除教师表中没有姓名的记录。代码如下： USE STUDENT GO DELETE 教师 WHERE 姓名 IS NULL GO,5.1.3数据的删除,2.使用TRUNCATE TABLE清空表格 语法: TRUNCATE TABLE table_name 语法注释： TRUNCATE TABLE：为关键字 table_name：为要删除所用记录的表名。 例5

8、.6： 用TRUNCATE TABLE 语句清空课程注册表。代码如下： USE STUDENT GO TRUNCATE TABLE 课程注册 GO,5.2 简单查询,5.2.1 SELECT 语句的基本语法格式 5.2.2选择表中的若干列 5.2.3选择表中的若干记录 5.2.4对查询的结果排序 5.2.5对数据进行统计 5.2.6用查询结果生成新表 5.2.7合并结果集,5.2.1 SELECT 语句的基本语法格式,SELECT语句的语法形式如下： SELECT select_list INTO new_table_name FROM table_list WHERE search_cond

9、itions GROUP BY group_by_list HAVING search_conditions ORDER BY order_list ASC | DESC ,5.2.1 SELECT 语句的基本语法格式,常用语句的用法： SELECT子句用于指定所选择的要查询的特定表中的列，它可以是星号（*）、表达式、列表、变量等。 INTO子句用于指定所要生成的新表的名称。 FROM子句用于指定要查询的表或者视图，最多可以指定16个表或者视图，用逗号相互隔开。 WHERE子句用来限定查询的范围和条件。 GROUP BY子句是分组查询子句。 HAVING子句用于指定分组子句的条件。,5.2.2

10、选择表中的若干列,1.输出表中的所有列 例5.7 ：查询“学生表”中全体学生的记录。代码如下： USE STUDENT GO SELECT * FROM 学生 GO,5.2.2选择表中的若干列,2.输出表中部分列 例5.8：查询“课程”表中的课程号和课程名称。代码如下： USE student GO SELECT 课程号,课程名称 FROM 课程 GO,5.2.2选择表中的若干列,3.为结果集内的列指定别名 例5.9：查询学生表中全体学生的姓名及年龄。代码如下： USE STUDENT GO SELECT 姓名,YEAR(GETDATE()-YEAR(出生日期) AS 年龄 FROM 学生 G

11、O,5.2.3选择表中的若干记录,1.消除取值重复的行 在表中，可能会包含重复值。这并不成问题，不过，有时您也许希望仅仅列出不同（distinct）的值 例5.10 ：查询选修了课程的学生号。程序清单如下： USE STUDENT GO SELECT DISTINCT 学号 FROM 课程注册 GO,5.2.3选择表中的若干记录,2.限制返回行数 例5.11 ：查询课程注册表中的前三条记录的信息。代码如下： USE STUDENT GO SELECT TOP 3 * FROM 课程注册 GO,5.2.3选择表中的若干记录,3.查询满足条件的元组 常用的查询条件 ：,5.2.3选择表中的若干记录

12、,例5.12 ：查询年龄小于或等于35岁教师的信息。代码如下： USE student GO SELECT * FROM 教师 WHERE YEAR(GETDATE()-YEAR(出生日期)=35 GO,5.2.3选择表中的若干记录,例5.13：查询出生日期在1971年至1980年之间的教师工号、姓名和出生日期。代码如下： USE student GO SELECT 教师工号,姓名,出生日期 FROM 教师 WHERE 出生日期 BETWEEN 1971-01-01 AND 1980-12-31 GO,5.2.3选择表中的若干记录,例5.14：查询软件学院、经济贸易系的班级名称与班级编号。 代

13、码如下： USE student GO SELECT 班级代码,班级名称 FROM 班级 WHERE 所在系部 IN(01,02) GO,5.2.3选择表中的若干记录,例5.15：查询“学生”表中姓“刘”的同学的信息。代码如下： USE student GO SELECT * FROM 学生 WHERE 姓名 LIKE 刘% GO,5.2.3选择表中的若干记录,例5.16：有一门课程的名称是“Delphi_6.0”，那么查询它的课程号和课程名称的代码如下： USE student GO INSERT INTO 课程 (课程号, 课程名称, 备注) VALUES (521309, Delphi_

14、6.0, 程序设计) GO SELECT 课程号,课程名称 FROM 课程 WHERE 课程名称 LIKE Delphi_6.0 ESCAPE GO -ESCAPE的意思是告诉Sql server，这里出现的这个字符是个escape char, 他后面所跟的的那个字符就按该字符的字面意义解释，不要解释成广义替代符。_ ESCAPE 的意思就是不要把_当作替代符，而就是下划线。如果要找一个本身已经被定义为escape char的字符，就要连续用两个escape char， ESCAPE 就代表一个。,5.2.3选择表中的若干记录,例5.17：查询“班级”表中备注字段为空的班级信息。 代码如下：

15、SELECT * FROM 班级 WHERE 备注 IS NULL GO 这里的IS 运算符不能用“=”代替。 将上述代码在查询编辑器中输入并执行，执行结果如图5.19所示。,5.2.3选择表中的若干记录,例5.18：查询“教师”表中出生日期在1971年至1983年之间的女教师信息。代码如下： SELECT * FROM 教师 WHERE 出生日期 BETWEEN 1971-01-01 AND 1983-12-31 AND 性别=女 GO 将上述代码在查询编辑器中输入并执行，结果如图5.20所示。,5.2.4对查询的结果排序,例5.19 :查询男教师的基本信息，按年龄降序排列。 代码如下： U

16、SE student GO SELECT * FROM 教师 WHERE 性别=男 ORDER BY YEAR(GETDATE()-YEAR(出生日期) DESC GO,5.2.4对查询的结果排序,例5.20 :查询全体学生信息，查询结果按所在的班级的班级代码降序排列，同一个班的按照学号升序排列。 代码如下： USE student GO SELECT * FROM 学生 ORDER BY 班级代码 DESC ,学号 ASC GO,5.2.5对数据进行统计,1.使用集合函数 例5.21 :查询“学生”表中学生总数。代码如下： USE student GO SELECT COUNT(*) AS

17、学生总数 FROM 学生 GO,5.2.5对数据进行统计,2.对结果进行分组 例5.22 :查询“课程注册”表中课程选课人数3人以上的各个课程号和相应的选课人数。代码如下： USE student GO SELECT 课程代码,COUNT(*) AS 选课人数 FROM 课程注册 GROUP BY 课程代码 HAVING COUNT(*)=3 GO,5.2.5对数据进行统计,3.使用COMPUTE子句 例5.23:查询所有学生所有成绩的总和。代码如下： USE student GO SELECT * FROM 课程注册 ORDER BY 学号 COMPUTE SUM(成绩) GO - COMP

18、UTE用于同时查看明细和汇总,5.2.6用查询结果生成新表,例5.24:创建“班级”表的一个副本。代码如下： USE student GO SELECT * INTO 班级副本 FROM 班级 GO SELECT * FROM 班级副本 GO,5.3 案例应用举例,1模糊查询 使用student数据库，从“学生”表中查询姓“张”或“刘”或“罗”的同学的信息，查询结果按姓名排序。 USE student GO SELECT * FROM 学生 WHERE 姓名LIKE 刘,张,罗% ORDER BY 姓名 GO,5.3 案例应用举例,2集合查询 使用student数据库，从“课程注册”表中查询选

19、修了“521231 ”或“521234 ” 号课程的同学的学号和成绩，查询结果按课程号的升序和成绩的降序排列。 USE student GO SELECT 课程号,学号,成绩 FROM 课程注册 WHERE 课程号 IN(521231,521234) ORDER BY 课程号ASC,成绩DESC GO,5.3 案例应用举例,3使用集合函数 使用student数据库，从“课程注册”表中查询选修了“521231”号课程的学生人数、最高成绩、最低成绩和平均成绩。 USE student GO SELECT COUNT(*) AS 学生总数,MAX(成绩) AS 最高成绩, MIN(成绩) AS 最低

20、成绩,AVG(成绩) AS 平均成绩 FROM 课程注册 WHERE 课程代码=521231 GO,5.3 案例应用举例,4结果分组 使用student数据库，从“课程注册”表中查询总成绩大于200分的同学的学号和总成绩。 USE student GO SELECT 学号,SUM(成绩) AS 总成绩 FROM 课程注册 GROUP BY 学号 HAVING SUM(成绩)200 GO,合并结果集,查询“课程注册”表中0101专业的学生信息及课程成绩大于78分的学生信息。代码如下： USE student GO SELECT 学号,选择了52123号课程 AS 备注 FROM 课程注册 WHE

21、RE 课程代码=521231 UNION SELECT 学号,选择了521235号课程 AS 备注 FROM 课程注册 WHERE 课程代码=521235 GO,5.4.1带有IN运算符的子查询,例6.8 ：使用“系部”表和“班级”表，查询软件学院和经贸系的班级信息。代码如下： USE student GO SELECT * FROM 班级 WHERE 系部代码IN (SELECT 系部代码 FROM 系部 WHERE 系部名称 IN (软件学院,经贸系) ) GO,6.2.2带有比较运算符的子查询,例6.9 ：列出选修了“521231”号课程，其成绩高于该课程平均分的学生的信息。代码如下：

22、USE student GO SELECT * FROM 学生 WHERE 学号IN (SELECT 学号 FROM 课程注册 WHERE 成绩 (SELECT AVG(成绩) FROM 课程注册 WHERE 课程代码=521231 ) AND 课程代码=521231) GO,6.2.3带有ANY 或ALL运算符的子查询,例6.10 ：查询比“计应0932”班中某一学生年龄小的其他班的学生学号与姓名。代码如下： USE student GO SELECT 学号,姓名 FROM 学生 WHERE 出生年月ANY (SELECT 出生年月 FROM 学生 WHERE 姓名=邝晓曦) AND 所在班级计应0932 GO,6.2.4带有EXISTS运算符的子查询,例6.11：查询所有选修了“521234”课程的学生的学号与姓名。代码如下： USE student GO SELECT 学号,姓名 FROM 学生 WHERE EXISTS (SELECT * FROM 课程注册 WHERE 学号=学生.学号AND 课程代码=521234) GO,6.3 案例应用举例,6.3.1自动注册必修课 6.3.2重修未取得学分的必修课 6.3.3查询学生各门课程的成绩 6.3.4查询学生的总学分,附件,-(1)使用“学生”表生成“学生1”表的表结构。 US