3. SQL语言的应用 3.1 SQL历史和优点 3.2 数据查询 3.3 数据操纵.

Presentation on theme: "3. SQL语言的应用 3.1 SQL历史和优点 3.2 数据查询 3.3 数据操纵."— Presentation transcript:

1 3. SQL语言的应用 3.1 SQL历史和优点 3.2 数据查询 3.3 数据操纵

2 3. SQL语言的应 SQL全称是结构化查询语言 目前SQL语言是关系数据库的标准语言
Structured Query Language---SQL 目前SQL语言是关系数据库的标准语言

3 3. SQL语言的应用 SQL语句分成三类，各类所包含的语句如下：
数据操纵语言DML（Data Manipulation Language） SELECT，INSERT，UPDATE和DELETE 数据定义语言DDL（Data Definition Language） CREATE，ALTER和DROP 数据控制语言DCL（Data Control Language） 相关的权限分配

4 3.1 SQL历史和优点 3.1.1 SQL的历史和标准 3.1.2 SQL的优点 1. 非过程化语言 2. 统一的查询语言
3. 所有关系数据库的公共语言

5 3.2 数据查询 3.2.1 数据查询建立与保存的方法 1. 使用查询向导建立查询 2. 使用查询设计器建立查询
3. 使用SQL语句建立查询 4. 查询的保存与修改

6 【例3-1】使用查询向导1 单击功能区“创建”命令选项卡中的“查询向导”按钮。

7 使用查询向导2 在出现的“新建查询”对话框中选择“简单查询向导”后，单击“确定”按钮。

8 使用查询向导3 选择为“学生表”，并将“可用字段”中的“学号、姓名、入学成绩”加入到“选定字段”列表框中。然后单击“下一步”按钮。

9 使用查询向导4 单击“下一步”。

10 使用查询向导5 选择“打开查询查看信息”单选框，并单击“完成”按钮

11 使用查询向导6 部分查询结果

12 【例3-2】查询设计器1

13 查询设计器2 选择学生表进行添加，然后双击“学号”和“姓名”字段。

14 查询设计器3

15 查询设计器4 单击“设计”命令选项卡中的“运行”按钮

16 【例3-3】SQL语句1---创建查询

17 SQL语句2-关闭“显示表”对话框

18 SQL语句3----切换到SQL视图

19 SQL语句4----编写SQL语句

20 SQL语句5----运行查询

21 SQL语句6----查询结果

22 4. 查询的保存与修改 保存查询对话框。保存的查询会出现在Access对象导航窗口中。
如果选择“打开”则直接运行了查询。 可以看到，通过在查询选项卡上单击鼠标右键，可以在SQL视图和设计视图间切换。

23 图 3-21 修改已保存的查询

24 3.2.2 单表查询 SQL语言提供了SELECT语句进行数据库的查询，该语句具有灵活的使用方式和丰富的功能。其一般格式为：
SELECT [ALL | DISTINCT] <目标列表达式> [,<目标列表达式>, …] FROM <表名或视图名> [, <表名或视图名>, …] [WHERE <条件表达式>] [GROUP BY <表达式 > [HAVING <条件表达式>] ] [ORDER BY <表达式> [ASC |DESC]];

25 单表查询---SELECT语句 SELECT语句既可以完成简单的单表查询，也可以完成复杂的连接查询和嵌套查询。 注意：
（1）在编写SQL语句之前，必须知道数据库的模式 （2）为提高可读性，减少编写错误和有利于SQL语句的维护，SQL必须写成多行。 （3）SQL语句中，表达式和SQL中的符号的书写必须使用半角符号

26 1.单表基本查询 所谓单表查询是指FROM子句后面只有一个表的SELECT语句。

27 1.单表基本查询 【例3-4】返回“学生表”中的所有行和所有列。 SELECT不需要WHERE子句，可以返回所有行；要返回所有列，则在<目标列表达式>使用 * 即可。 select * from 学生表 【例3-5】查询成绩表中的所有记录。 select * from 成绩表;

28 图 例3-1的查询结果

29 1. 单表基本查询 2） 查询指定的列 给定一个数据表，要查询指定的列，必须在SELECT的<目标列表达式>中指定列名，这个操作为对该表实行投影操作。 【例3-6】检索“学生表”中的学号、姓名、性别和籍贯字段。 select 学号,姓名,性别,籍贯 from 学生表; 查询结果略。

30 1. 单表基本查询 3） 查询经过计算的值或更改列标题名 SELECT语句中，可以使用运算符来对列进行计算得到结果，要注意的是，这些运算只针对检索后的结果，它不会影响保存在数据库中的数值。 此外SELECT提供了<AS 字段名>更改字段名的方法。

31 1. 单表基本查询 【例3-7】对“学生表”的“入学成绩”除以600，求相对成绩，其显示的字段名为“相对成绩”。 select 学号, 姓名, 入学成绩/600 as 相对成绩 from 学生表; 【例3-8】求所有学生在2010年的年龄。 select 学号,姓名, year(出生日期) as 年龄 from 学生表;

32 2. 选择表中的若干元组 （选择操作） 选择一个表中的若干元组（或记录）操作，是对该表实行选择操作。其方法是使用SELECT语句的WHERE子句中的条件。 1）消除取值重复的行 指定DISTINCT短语，表示在计算时要去除重复行。如果不指定DISTINCT短语或指定ALL短语（ALL为默认值），则表示不取消重复值。

33 消除取值重复的行 Distinct子句 【例3-9】输出学生表中所有的籍贯。 select 籍贯 from 学生表 order by 籍贯; 【例3-10】显示学生表中的学生来自全国哪些地方，即有哪些不同的籍贯。 select distinct 籍贯 from 学生表 order by 籍贯; 结果为去除了重复籍贯的23条记录，而原学生表中有30位同学。

34 选择操作---Where子句 2）查询满足条件的元组 WHERE子句常用的查询条件。

35 WHERE子句中的条件 操作符 类型 操作符 含义 关系运 算符 = 等于 < > 不等于 > 大于 >= 大于等于
小于 <= 小于等于 BETWEEN…AND 确定范围

36 WHERE子句中的条件2 逻辑运算符 AND 与， 用于多重条件 OR 或， NOT 非， 用于条件取非 属于（不属于） [NOT] IN
确定集合 通配运算符 LIKE 字符匹配

37 用关系运算符构造条件---数值 【例3-11】查找所有入学成绩大于等于600的同学的学号、姓名和入学成绩。 select 学号,姓名,入学成绩 from 学生表 where 入学成绩 >= 600; 这是数字类型查询例子，其查询结果略。

38 用关系运算符构造条件---逻辑 关系运算符包括：>、>=、=、<、<=、< >。
【例3-12】查找学生表中的贷款的学生学号和姓名。 select 学号, 姓名, 贷款否 from 学生表 where 贷款否 = True; 这是逻辑类型查询例子。

39 用关系运算符构造条件---日期 【例3-13】求1991年7月以后出生的学生学号和姓名。 select 学号,姓名,出生日期 from 学生表 where 出生日期>=#1991/07/01# 这是日期类型查询例子。

40 用关系运算符构造条件---Between
谓词BETWEEN…AND…用来查找属性值在指定范围内的元组，其中BETWEEN后是范围的下限（即低值），AND后是范围的上限（即高值）。

41 用关系构造条件---Between例子 【例3-14】求入学成绩在550到630间的学生学号、姓名和入学成绩。 select 学生表.学号, 学生表.姓名, 学生表.入学成绩 from 学生表 where 学生表.入学成绩 between 550 and 630;

42 用关系构造条件---Between例子 上述SQL语句可以等价于如下SQL语句。 select 学生表.学号, 学生表.姓名, 学生表.入学成绩 from 学生表 where 学生表.入学成绩 >= and 学生表.入学成绩 <= 630;

43 用关系构造条件---Between例子 如果要求入学成绩不在550到630间的学生学号、姓名和入学成绩，使用下列SQL语句。 select 学生表.学号, 学生表.姓名, 学生表.入学成绩 from 学生表 where 学生表.入学成绩 not between 550 and 630;

44 （2）用逻辑运算符构造多重条件 SELECT语句提供逻辑运算符AND和OR，可用来组合联结多个查询条件。AND的含义是表示多个条件间的“与”、“同时”或“并且”关系，OR的含义是表示多个条件间的“或”关系。这里ＡND的优先级高于OR，但我们可以用括号改变优先级。

45 用逻辑运算符构造多重条件---例子 【例3-15】查找入学成绩在550分以上的少数民族学生的学号、姓名和少数民族否。 select 学号,姓名,民族,入学成绩 from 学生表 where 入学成绩 >= 550 and 民族 <> “汉族” 查询结果略。

46 用逻辑运算符构造多重条件---例子 【例3-16】查找入学成绩在570分以上的女性学生，显示学号、姓名、性别和入学成绩。 select 学号,姓名,性别,入学成绩 from 学生表 where 入学成绩 >= and 性别 = ‘女’ 查询结果略。

47 （3）确定集合 SELECT提供谓词IN用来查找属性值在指定集合的方法。而NOT IN表示属性值不在指定集合。

48 确定集合---例子 【例3-17】求籍贯为“江西南昌”或“四川成都”的学生学号、姓名和籍贯。 select 学生表.学号, 学生表.姓名, 学生表.籍贯 from 学生表 where 学生表.籍贯 in (“四川成都”,“江西南昌”);

49 确定集合---例子 上述SQL语句可以等价于如下SQL语句。 select 学生表.学号, 学生表.姓名, 学生表.籍贯 from 学生表 where 学生表.籍贯 = “四川成都” or 学生表.籍贯 = “江西南昌”; 查询结果略。

50 确定集合---例子 【例3-18】求籍贯不为“江西南昌”和“四川成都”的学生学号、姓名和籍贯。 select 学生表.学号, 学生表.姓名, 学生表.籍贯 from 学生表 where 学生表.籍贯 not in ("四川成都","江西南昌");

51 确定集合---例子 上述SQL语句可以等价于如下SQL语句。 select 学生表.学号, 学生表.姓名, 学生表.籍贯 from 学生表 where 学生表.籍贯 <> "四川成都" and 学生表.籍贯 <> "江西南昌";

52 （4）字符匹配 谓词LIKE可以用来进行字符串的匹配。其一般语法格式如下： [NOT] LIKE ‘ <匹配串> ’ [ESCAPE ‘ <换码字符> ’ ] 其含义是查找指定的属性列值与<匹配串>相匹配的元组。

53 字符匹配 <匹配串>可以是一个完整的字符串，也可以含有通配符*和?。
*（星号）代表在任意位置（长度可以为0）上的任意字符。例如a*b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab等都满足该匹配串。 ?（问号）代表一个位置上任意字符。例如a?b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串。

54 （4）字符匹配---例子 【例3-19】查找以姓“杨”开头的学生学号和姓名。 select 学号,姓名 from 学生表 where 姓名 like “杨*”;

55 字符匹配---例子 【例3-20】查找以“慧”字为最后一个字符的学生学号和姓名。 select 学号,姓名 from 学生表 where 姓名 like “*慧”;

56 字符匹配---例子 【例3-21】查找第二个字符为“建”字的学生学号和姓名。 select 学号,姓名 from 学生表 where 姓名 like “?建*”; 思考：下列SQL语句与上面的有何不同。 select 学号,姓名; from 学生表; where 姓名 like “*建*”

57 字符匹配---例子 【例3-22】查找江西籍的男性学生的学号和姓名 select 学号,姓名,性别,籍贯 from 学生表 where 籍贯 like “江西*” and 性别 = “男”;

58 字符匹配---例子 【例3-23】查找江西和湖南籍的学生学号、姓名、性别和籍贯。 select 学号,姓名,性别,籍贯 from 学生表 where 籍贯 like “江西*” or 籍贯 like “湖南*”; 查询结果略。 【例3-24】查找非江西和湖南籍的学生学号、姓名、性别和籍贯。 select 学号,姓名,性别,籍贯 from 学生表 where not (籍贯 like “江西*”) and not (籍贯 like “湖南*”); 查询结果略。

59 （5）查询结果输出到新表 SELECT默认输出给用户浏览。SELECT同时提供INTO子句来将查询结果输出到新表中，其格式为： SELECT [ALL | DISTINCT] <目标列表达式> [,<目标列表达式>,…] [INTO <目标>] FROM <表名或视图名> [, <表名或视图名>,…] [WHERE <条件表达式>] [GROUP BY <表达式 > [HAVING <条件表达式>] ] [ORDER BY <表达式> [ASC |DESC]]; INTO <目标>表示将查询结果保存到新表中。

60 （5）查询结果输出到新表 【例3-25】将籍贯为“江西”和“湖南”的学生学号、姓名、性别和籍贯输出到“湘赣学生名单”。 select 学号,姓名,性别,籍贯 into 湘赣学生名单 from 学生表 where 籍贯 like “江西*” or 籍贯 like “湖南*”;

61 3）对查询结果排序 用户可以用ORDER BY子句对查询结果按照一个或多个属性列的升序（ASC）或降序（DESC）排列，缺省值为升序。
【例3-26】查询所有学生的入学成绩，查询结果按入学成绩的降序排列。 select 学号,姓名,入学成绩 from 学生表 order by 入学成绩 desc 部分查询结果如图3-26所示。

62 3）对查询结果排序 【例3-27】查询所有学生的入学成绩，查询结果按入学成绩的升序排列。 select 学号,姓名,入学成绩 from 学生表 order by 入学成绩; 查询结果略。 SELECT语句支持多个关键字的排序。 【例3-28】对所有学生按入学成绩排降序和出生日期排降序输出学号、姓名、入学成绩和出生日期。 select 学号,姓名,入学成绩,出生日期 from 学生表 order by 入学成绩 desc, 出生日期 desc; 其部分查询结果如图3-27所示。

63 4）使用聚合函数 为了进一步方便用户，增强检索功能，SQL提供了许多聚合函数。所谓聚合函数是指对一个关系进行求和（sum）、求平均值（avg）等运算。

64 4）使用聚合函数 函数 含义 AVG(<表达式>) 计算查询的指定字段中所包含的一组值的算术平均值（Average）。
COUNT( * ) 统计元组个数。 COUNT(<表达式>) 计算查询所返回的记录数。 MIN(<表达式>) 返回查询的指定字段中包含的一组值的最小值。 MAX(<表达式>) 返回查询的指定字段中包含的一组值的最大值。 SUM(<表达式>) 返回查询的指定字段中包含的一组值的总和。

65 4）使用聚合函数 【例3-29】求学生表中入学成绩在600分以上的人数。 select count(*) from 学生表 where 入学成绩 >= 600; 结果为14人。 思考：求江西籍学生人数。 【例3-30】求学生表中入学成绩的平均成绩。 select avg(入学成绩) from 学生表; 结果为 分。

66 5）对查询结果分组 SELECT使用GROUP BY子句来进行分组。我们通过例子来说明分组的功能。
【例3-31】按籍贯分类，求不同籍贯的同学人数。 select 籍贯,count(*) from 学生表 group by 籍贯 order by count(*) desc; 部分查询结果如图3-28所示。 【例3-32】求各个年级的班数。 select 年级,count(*) from 班级表 group by 年级; 结果略。 注意：使用GROUP BY子句时，在SELECT的字段列表中，凡没有出现在聚合函数中的字段，必须出现在GROUP BY子句中。

67