第4章 数据的查询与更新 4.1 SQL查询语句格式 4.2 简单查询 4.3 统计查询 4.4 连接查询 4.5 嵌套查询

Presentation on theme: "第4章 数据的查询与更新 4.1 SQL查询语句格式 4.2 简单查询 4.3 统计查询 4.4 连接查询 4.5 嵌套查询"— Presentation transcript:

1 第4章 数据的查询与更新 4.1 SQL查询语句格式 4.2 简单查询 4.3 统计查询 4.4 连接查询 4.5 嵌套查询
4.2 简单查询 4.3 统计查询 4.4 连接查询 4.5 嵌套查询 4.6 SQL操作功能语句 4.7 使用企业管理器查询与更新表

2 4.1 SQL查询语句基本格式 注意：顺序不能变! SELECT 列名1,列名2,…… [ INTO 新表名 ]
[ FROM 表名1,表名2,……] [ WHERE 条件表达式 ] [ GROUP BY 列名1,列名2,……] [ HAVING 条件表达式 ] [ ORDER BY 列名1 [ ASC|DESC ] ,……] 注意：顺序不能变!

3 4.2 简单查询 4.2.1 select子句 select子句用于指定要查询的特定表中的列 例1：查询师生管理库中教师表的所有信息
4.2 简单查询 4.2.1 　select子句 select子句用于指定要查询的特定表中的列 例1：查询师生管理库中教师表的所有信息 use 教学管理 打开数据库 select * from 教师 *代表所有字段 例2：查询所有教师的姓名和工资信息 select 姓名,工资 from 教师 --各字段以“,”分隔，顺序可任意指定 例3：查询所有教师的姓名和年工资 select 姓名,年工资=工资*12 from 教师 select 姓名,工资*12 as 年工资 from 教师 /*对于不在表中的列而是由表中已有列派生出来的列名，可以用“=”或“as”指定*/

4 4.2 简单查询 例4：查询教师表中职称的种类 select distinct 职称 from 教师
4.2 简单查询 例4：查询教师表中职称的种类 select distinct 职称 from 教师 /*distinct 用于去掉重复项*/ 例5：查询学生表中前3位学生 select top 3 * from 学生 查询学生表中前20%的学生 select top 20 percent * from 学生 /*top n /top n percent用于限制返回行数*/

5 4.2 简单查询 4.2.2 可选项into子句 into 新表名
4.2 简单查询 4.2.2 　可选项into子句 into 新表名 into子句用于指定所要生成的新表的名称。可以是永久表，也可以在表名前加#，则生成临时表。 例： select * into tb from 教师 是永久表 select * into #tb from 教师 --是临时表 问题：临时表的内容怎么看到？ select * into tb from 教师 where 1=2 问题：表tb 会产生吗？ 结构复制

6 4.2 简单查询 4.2.3 必选项from子句 from 表名1,表名2,…… from子句用于指定要查询的表或者视图 例：
4.2 简单查询 4.2.3 　必选项from子句 from 表名1,表名2,…… from子句用于指定要查询的表或者视图 例： select * from 教师 select a.姓名,a.工资 from 教师 as a as：可以为每个表起一个别名。一旦为某个表起了别名后，就只能使用其别名。as关键字也可以省略。

7 4.2 简单查询 4.2.4 可选项where子句 where 条件表达式
4.2 简单查询 4.2.4 　可选项where子句 where 条件表达式 where子句用来限定查询的范围和条件，只有满足条件的记录（条件结果为.T.的记录）才被查询到。 　 ＜P66表4.1 条件运算符＞

8 4.2.4 where 条件表达式 1. 比较运算符 例：查询工资少于1230的教师信息 （不少于）
select * from 教师 where 工资<2000 select * from 教师 where 工资!<2000 例：查询学生表中1985.7后出生的人 select * from 学生 where 出生日期>= ‘ 1985/7/1’ 2. 范围运算符 例：查询工资在2000至3000之间的教师信息 select * from 教师 where 工资 between 2000 and 3000

9 4.2.4 where 条件表达式 3. 列表运算符 例：在课程表中查询课程号为001、003、004的三门课程信息
select * from 课程 where 课程号 in ('001', '003', '004') 4. 模式匹配运算符 P67　 通配符：%:代表0或多个任意字符；_：代表单个字符；[ ]：代表在[]范围内的单个字符；[^]代表不在[]范围内的单个字符。 例：在课程表中查找课程名中带“公安”的课程信息　 where 课程名 like ('%公安%')

10 4.2.4 where 条件表达式 注意：like只对字符型数据! 例：查找课程名的第二个字是“安”的课程信息
select * from 课程 where 课程名 like (‘_安%’) 例：查找课程号为003至005的课程信息 select * from 课程 where 课程号 like ('00[3-5]') 例：查找课程号不在003至005范围内的课程信息 where 课程号 like ('00[^345]')

11 4.2.4 where 条件表达式 5. 空值判断运算符 例：查找选修课成绩表中，成绩为空的信息（不空）
select * from 选修课成绩 where 成绩 is null select * from 选修课成绩 where 成绩 is not null 6. 逻辑运算符 NOT AND OR 例：查询工资多于（等于）2000的男教师信息 select * from 教师 where 工资>=2000 and 性别=‘男’ 例：查询工资少于2000或多于3500的教师信息 where 工资<2000 or 工资>3500 NOT可以用到前面讲到的多个运算符中。

12 4.2 简单查询 4.2.5 可选项order by子句 order by 列名1 [ asc|desc ] ,……
4.2 简单查询 4.2.5 　可选项order by子句 order by 列名1 [ asc|desc ] ,…… 用来按升序或降序显示查询结果 例：查询教师信息，并按工资降序显示 select * from 教师 order by 工资 desc 例：查询教师信息，先按职称升序排列，职称相同的再按工资降序排列 select * from 教师 order by 职称,工资 desc

13 4.2 简单查询 4.2.6 合并结果集 select 语句 union select 语句 例：
4.2 简单查询 4.2.6 　合并结果集 select 语句 union select 语句 例： select * from 教师 where 职称= '教授' union select * from 教师 where 职称= '副教授' 注意：①列数必须相同，数据类型必须相同； ②默认将从最后结果中删除重复的记录。

14 4.3 统计查询 对查询结果进行统计，例如求和、平均值、最大值、最小值和个数，可以使用以下三种方法：
4.3 统计查询 对查询结果进行统计，例如求和、平均值、最大值、最小值和个数，可以使用以下三种方法： 1）集合函数(SUM,AVG,MAX,MIN,COUNT) 2）GROUP BY 子句 3）COMPUTE 子句

15 4.3.1 使用集合函数 集合函数用来对查询结果集中的记录进行汇总计算，例如求和、平均值、最大值、最小值、个数等。集合函数有：
使用集合函数 集合函数用来对查询结果集中的记录进行汇总计算，例如求和、平均值、最大值、最小值、个数等。集合函数有： AVG SUM MAX MIN COUNT 1）AVG ：求平均值 例1：查询教师表中的平均工资 select 平均工资=avg(工资) from 教师 例2：查询选修课成绩中的平均成绩 select avg(成绩) as 平均成绩 from 选修课成绩 问题：NULL值如何处理？ 注意：在计算中NULL值被忽略！ AVG只能对一列数值型字段进行计算

16 4.3.1 使用集合函数 2）SUM：求和 例：查询教师表中的工资总和 select sum(工资) 工资总和 from 教师
使用集合函数 2）SUM：求和 例：查询教师表中的工资总和 select sum(工资) 工资总和 from 教师 3）MAX：求最大值 例：查询教师表中的最高工资 select max(工资) 最高工资 from 教师 4）MIN：求最小值 例：查询教师表中的最低工资 select min(工资) 最低工资 from 教师

17 4.3.1 使用集合函数 5） COUNT：统计查询结果集中记录的个数 例1：查询教师表中的教师人数
使用集合函数 5） COUNT：统计查询结果集中记录的个数 例1：查询教师表中的教师人数 select count(*) as 教师人数 from 教师 select count(工号) as 教师人数 from 教师 例2：查询教师表中的职称类别 select count(职称) as 职称种类数 from 教师 select count(distinct 职称) as 职称种类数 from 教师 例3：查询教师表中平均工资，最高工资，教师人数 select 平均工资=avg(工资),max(工资) as 最高工资,count(工号) 教师人数 from 教师

18 4.3.2 使用GROUP BY子句 group by列名用于对查询结果按指定列进行分组，然后对每一组进行汇总计算。
例1：求每个学生选修课的平均分 select 学号,avg(成绩) from 选修课成绩 group by 学号 注：select 后的列名只能是集合函数或是group by 后的列名 having 条件表达式是对生成的组进行筛选，要有分组。 例2：求多于一门的每个学生选修课的平均分 select 学号,avg(成绩) from 选修课成绩 group by 学号 having count(*)>1

19 where与having where 是先对表中记录进行筛选，而having 是对生成的组进行筛选。where子句中不能有集合函数。
例：找出男职工的平均工资大于1600的职称 select 职称 from 教师 where 性别='男' group by 职称 having avg(工资)>1600 有时两者可以互换。 例：求除“001”以外的每门选修课的平均成绩 select 课程号,avg(成绩) from 选修课成绩 where 课程号<> '001' group by 课程号　 或 select 课程号,avg(成绩) from 选修课成绩 group by 课程号 having 课程号<> '001'

20 练习 1、查出选了2门或2门以上课程的学生的学号 select 学号 from 选修课成绩 group by 学号
having count(*) >=2 2、查询成绩均在65分以上的课程信息 select 课程号 from 选修课成绩 group by 课程号 having min(成绩)>65

21 4.3.3 使用COMPUTE子句 compute子句对查询结果中的所有记录进行汇总计算，并显示所有参加汇总记录的详细信息。
语法：compute 集合函数 [by 列名] 例1 显示教师信息和平均工资信息： select * from 教师 compute avg(工资) 例2：查询教师的工号、工资和平均工资 select 工号,工资 from 教师 compute avg(工资)

22 使用COMPUTE子句 computer 后用by 列名可进行分组统计，但必须先排序再分组，即 compute by必须与order by连用 例：按性别对教师进行分组，并查询平均工资及其他详细信息 select * from 教师 order by 性别 compute avg(工资) by 性别

23 4.4 连接查询 有时需要从多个表中取数据形成结果，这些表之间就要进行连接。连接分为： 交叉连接 cross 内连接 inner join
4.4 连接查询 有时需要从多个表中取数据形成结果，这些表之间就要进行连接。连接分为： 交叉连接 cross 内连接 inner join 外连接（又分为左连接left outer join、右连接right outer join 和全连接full outer join） 自连接 join

24 4.4.1 交叉连接 将两个表不加任何条件地组合在一起，即将第一个表中的所有记录分别与第二个表中的每条记录组成新记录。 例：
交叉连接 将两个表不加任何条件地组合在一起，即将第一个表中的所有记录分别与第二个表中的每条记录组成新记录。 例： select * from 学生,教师 或 select * from 学生 cross join 教师 m×n条记录， b1+b2个字段 在实际应用中一般没意义

25 4.4.2 内连接 自然连接是去掉重复属性的等值连接 将两个表中满足连接条件的记录组合在一起 例：查询学生的学号、姓名、所选课程号和成绩
内连接 将两个表中满足连接条件的记录组合在一起 例：查询学生的学号、姓名、所选课程号和成绩 格式一： select 学生.学号,姓名,课程号,成绩 from 学生 inner join 选修课成绩 on 学生.学号=选修课成绩.学号 条件：on 主键=外键 格式二： from 学生,选修课成绩 where 学生.学号=选修课成绩.学号 注：当select后的字段在两个表中都存在时，必须指明是在哪个表中取该字段。 自然连接是去掉重复属性的等值连接

26 在命令中可以使用表的别名 如：select 学生.学号,姓名,课程号,成绩 from 学生,选修课成绩 where 学生.学号=选修课成绩.学号 可以写成：select a.学号,姓名,课程号,成绩 from 学生 a,选修课成绩 b where a.学号=b.学号

27 4.4.3 外连接 外连接可产生内连接生成的结果，同时还可以使一个或两个表中的不满足连接条件的记录也出现在结果中。
外连接 外连接可产生内连接生成的结果，同时还可以使一个或两个表中的不满足连接条件的记录也出现在结果中。 左连接：将左表的所有记录分别与右表的每一条记录进行连接组合。左表中全部记录保留在查询结果中，若右表中有满足ON条件记录，则连接进入查询结果，否则以.NULL.填充 格式：select 列名 from 表名1 left [outer] join 表名2 on 表名1.列名=表名2.列名 例：查所有学生的选修课成绩（没有选课的也要显示） select 学生.学号,姓名,课程号,成绩 from 学生 left outer join 选修课成绩 on 学生.学号=选修课成绩.学号

28 4.4.3 外连接 2. 右连接：右表中全部记录保留在查询结果中，若左表中有满足ON条件记录，则连接进入查询结果，否则以.NULL.填充
外连接 2. 右连接：右表中全部记录保留在查询结果中，若左表中有满足ON条件记录，则连接进入查询结果，否则以.NULL.填充 例： 查所有教师所授的课程号（没有授课的也要显示） select 姓名,课程号 from 教师任课 right outer join 教师 on 教师.工号=教师任课.工号 3. 全连接：两个表全部记录在查询结果，符合ON条件则连接，否则，以.NULL.填充 例：显示所有学生选课和成绩信息 select 学生.学号,姓名,课程号,成绩 from 学生 full outer join 选修课成绩 on 学生.学号=选修课成绩.学号

29 4.4.4 复合连接条件查询 例：查询教授的姓名和所授课程信息 格式1： select 姓名,课程.*
复合连接条件查询 例：查询教授的姓名和所授课程信息 格式1： select 姓名,课程.* from 教师 join 教师任课 join 课程 on 教师任课.课程号=课程.课程号 on 教师.工号=教师任课.工号 where 职称='教授' 注意：两个连接条件on的顺序要与from后join表的顺序相反

30 4.4.4 复合连接条件查询 例：查询教授的姓名和所授课程信息 格式2： select 姓名,课程.* from 教师 join 教师任课
复合连接条件查询 例：查询教授的姓名和所授课程信息 格式2： select 姓名,课程.* from 教师 join 教师任课 on 教师.工号=教师任课.工号 join 课程 on 教师任课.课程号=课程.课程号 where 职称='教授'

31 4.4.4 复合连接条件查询 例：查询教授的姓名和所授选修课程信息 格式3： select 姓名,课程.* from 教师,教师任课,课程
复合连接条件查询 例：查询教授的姓名和所授选修课程信息 格式3： select 姓名,课程.* from 教师,教师任课,课程 where 教师.工号=教师任课.工号 and 教师任课.课程号=课程.课程号 and 职称='教授'

32 4.4.5 自连接 自连接就是一张表的两个副本之间的连接，左表中的每一行与右表中的所有行比较，满足条件的放到结果集中。 例：查询同名的学生
自连接 自连接就是一张表的两个副本之间的连接，左表中的每一行与右表中的所有行比较，满足条件的放到结果集中。 例：查询同名的学生 select * from 学生 a, 学生 b where a.学号<>b.学号 and a.姓名=b.姓名 或：select * from 学生 a join 学生 b on a.姓名=b.姓名 and a.学号<>b.学号 自连接中的一个表被引用两次，必须在from中使用别名区分两个引用。自连接相当于两个内容完全一样的表的联接

33 选学题目：在选修成绩表中，对选修001号课程的学生排名次
select a.学号,count(*) as 名次 from 选修课成绩 a join 选修课成绩 b on a.成绩<=b.成绩 where a.课程号='001' and b.课程号='001' group by a.学号 order by 名次

34 4.5 嵌套查询（子查询） 注意：子查询总是写在( )中。
4.5 嵌套查询（子查询） 一个select…from …语句称为一个查询块，将一个查询块嵌套在另一个查询块的条件中的查询称为嵌套查询。 　上层的查询块称为外层查询或父查询，下层查询块称为内层查询或子查询。 例：找出和“陈东风”挣同样工资的教师信息 select * from 教师 where 工资= (select 工资 from 教师 where 姓名=‘陈东风') 注意：子查询总是写在(　)中。

35 带有比较运算符的嵌套查询 通过比较运算符>,<,>=,<=,<>,!=,!>,!< 将一个表达式的值与子查询返回的单值进行比较。 例1：找出低于平均工资的教师 select * from 教师 where 工资< (select avg(工资) from 教师) 例2：列出课程中，学分最高的课程信息 select * from 课程 where 学分= (select max(学分) from 课程)

36 4.5.2 带有in运算符的嵌套查询 使用IN，若表达式与子查询结果集中的任何一个值相等，则满足条件。 例1：查询已选课的学生信息
select * from 学生 where 学号 in (select 学号 from 选修课成绩) 例2：查询已选课但还没有成绩的学生信息 select * from 学生 where 学号 in (select 学号 from 选修课成绩 where 成绩 is null)

37 4.5.2 带有in运算符的嵌套查询 例：查询成绩均在65分以上的课程信息
select * from 课程 where 课程号 not in (select 课程号 from 选修课成绩 where 成绩<=65) 考虑到成绩为NULL的情况： select * from 课程 where 课程号 not in (select 课程号 from 选修课成绩 where 成绩<=65) and 课程号 in (select 课程号 from 选修课成绩 where 成绩 is not null)

38 4.5.2 带有in运算符的嵌套查询 注意：SQL Server支持多重嵌套 例：找出学生“张小明”选修的课程信息
select * from 课程 where 课程号 in (select 课程号 from 选修课成绩 where 学号 in (select 学号 from 学生 where 姓名='张小明')) 注意：SQL Server支持多重嵌套 也可以利用连接查询实现 select a.* from 课程 a,选修课成绩 b,学生 c where a.课程号=b.课程号 and b.学号=c.学号 and 姓名='张小明'

39 4.6 SQL操作语句 添加数据 INSERT语句 修改数据 UPDATE语句 删除数据 DELETE语句 TRUNCATE TABEL语句

40 4.6.1 添加数据 注意：这里缺的字段“学分”用默认值或NULL值赋值。 INSERT语句用来向表中添加数据。
添加数据 INSERT语句用来向表中添加数据。 格式1：INSERT 表名[(字段名)] VALUES （字段值） 功能：直接给各列赋值，一次添加一条记录 例-向课程表中添加新纪录： ①INSERT 课程 VALUES (‘010’, ‘犯罪学’,3) 例：向学生表中添加新纪录 ②INSERT 学生 VALUES('020306','王莉',default, ' ',' ') ③INSERT 课程(课程号,课程名) VALUES ('100', '犯罪学') 注意：这里缺的字段“学分”用默认值或NULL值赋值。

41 INSERT语句 如果表中有标识列怎么办？ 例-假设学生简表中有学号、姓名和性别三列，其中“学号”是标识列 向学生间表中添加一行数据：
INSERT 学生简表(姓名,性别) VALUES(‘赵长河’, ‘女’) INSERT 学生简表(姓名) VALUES(‘郭德纲’) INSERT 学生简表 VALUES('赵山',default) 注意：不能对标识列赋值 添加数据不能违反完整性约束

42 INSERT语句 INSERT语句还可以有另一种格式，即把SELECT的结果添加到表中，可以一次向表中添加多条记录。
例：创建一个与教师表结构一样的表JS，表中仅有教授的记录。 方法1： SELECT * INTO JS FROM 教师 WHERE 职称= '教授' 方法2： ①SELECT * INTO JS FROM 教师 WHERE 1=2 --结构复制 ②INSERT JS SELECT *FROM 教师 WHERE 职称= '教授'

43 INSERT语句 例：创建一个与教师表结构一样的表JS，表中仅有教授记录的工号和姓名。
①SELECT * INTO JS FROM 教师 WHERE 1=2 ② INSERT JS(工号,姓名) SELECT 工号,姓名 FROM 教师 WHERE 职称= '教授' 注：select语句的列名列表必须和insert语句的列名列表的列数、列序、列的数据类型都要兼容。

44 4.6.2 修改数据 UPDATE语句用来修改表中的数据，一次可以修改一行或多行数据。 格式1：
修改数据 UPDATE语句用来修改表中的数据，一次可以修改一行或多行数据。 格式1： UPDATE 表名 SET 列名1=表达式，…… [WHERE 条件表达式] 例①改PhotoShop课程的课程名改为“图像处理”，学分改为4 UPDATE 课程 SET 课程名='图像处理',学分=4 WHERE 课程名='PhotoShop' 例②给所有教师增加100元工资 UPDATE 教师 SET 工资=工资+100

45 UPDATE语句 UPDATE语句还可以有另一种格式，根据一个表中的数据修改另一个表中的数据。 格式2：
UPDATE 表名 SET 列名1=表达式，…… [FROM 源表名] [WHERE 条件表达式] 例-计算orders表中的总金额,为goods表中的价格乘以order表中的数量 UPDATE orders SET 总金额=价格*数量 FROM goods WHERE orders.货品名称=goods.货品名称

46 4.6.3 删除数据 DELETE语句用来删除表中的数据，一次可以删除一行或多行。 格式1： DELETE 表名 [WHERE 条件表达式]
删除数据 DELETE语句用来删除表中的数据，一次可以删除一行或多行。 格式1： DELETE 表名 [WHERE 条件表达式] 例：①DELETE 选修课成绩 WHERE 成绩 is null ②DELETE 选修课成绩 仅余表结构 DELETE语句还可以有另一种格式，根据一个表中的数据删除另一个表中的数据。 格式2: DELETE 表名 [FROM 源表名] [WHERE 条件表达式] 例：删除李萍的所有选修课记录 delete 选修课成绩 from 学生 where 姓名= '李萍' and 学生.学号=选修课成绩.学号

47 TRUNCATE TABLE语句 TRUNCATE TABEL语句用来删除表中的所有数据。 格式：TRUNCATE TABEL 表名
功能上=DELETE，但TRUNCATE TABLE语句要快，因为该语句一次删除所有行，只将对表数据页面的释放操作记录到日志中。而DELETE语句在删除每一行时都要把删除操作记录在日志上。 DELETE语句可以通过事务回滚来恢复删除的数据。 DROP TABLE 表名——删除表结构和数据

48 总结：SQL操作语句 添加数据 INSERT语句 修改数据 UPDATE语句 删除数据 DELETE语句 TRUNCATE TABLE语句
特别提醒！ 添加、修改和删除数据时，不得违反数据完整性。

49 SQL语言总结 数据定义语言（DDL，Data Definition Language）
数据操纵语言（DML，Data Manipulation Language） 数据控制语言（DCL，Data Control Language）

50 数据定义语言（DDL） 数据定义语言是指用来定义和管理数据库以及数据库中的各种对象的语句，这些语句包括CREATE、ALTER和DROP等语句。在SQL Server 2000中，数据库对象包括表、视图、触发器、存储过程、规则、默认、用户自定义的数据类型等。这些对象的创建、修改和删除等都可以通过使用CREATE、ALTER、DROP等语句来完成。

51 数据操纵语言（DML） 数据操纵语言是指用来查询、添加、修改和删除数据库中数据的语句，这些语句包括SELECT、INSERT、UPDATE、DELETE等。

52 数据控制语言（DCL）(略) 数据控制语言是用来设置或者更改数据库用户或角色权限的语句，这些语句包括GRANT、DENY、REVOKE等。
例：GRANT CREATE DATABASE, CREATE TABLE TO Mary, John GRANT INSERT, UPDATE, DELETE ON authors TO Mary, John 在默认状态下，只有系统管理员等成员才有权利执行数据控制语言。

53 4.7 使用企业管理器查询与更新表 打开表→查询，“查询设计器”窗口
4.7 使用企业管理器查询与更新表 打开表→查询，“查询设计器”窗口 在查询设计器中可以进行对表的查询、插入、删除、修改操作（通过‘更改查询类型’实现功能转换）。