- 信息技术基础与应用（下册）
- 王华 吴光成 张雪峰
- 6712字
- 2021-10-15 17:04:27
任务9.4 SQL语句的使用
9.4.1 学习要点
学习要点如下。
♦ Select语句的使用。
♦ Insert语句的使用。
♦ Update语句的使用。
♦ Delete语句的使用。
9.4.2 知识准备
9.4.2.1 SQL概述
SQL前身是著名关系数据库管理系统原型（RDBMS）System R所采用的SEQUEL语言。作为一种访问关系型数据库的标准语言，SQL自问世以来得到了广泛的应用。不仅是著名的大型商用数据库产品Oracle、DB2、Sybase、SQL Server支持它，很多开源的数据库产品，如PostgreSQL、MySQL也支持它。甚至得到一些小型数据库产品的支持产品，如Access。近些年蓬勃发展的NoSQL系统最初宣称不再需要SQL，后来也不得不将No SQL修正为Not Only SQL，支持基本的SQL功能。
蓝色巨人IBM对关系数据库及SQL语言的形成和规范化产生了重大的影响，第1个版本的SQL标准SQL 86就是基于System R系统的。Oracle在1979年率先推出了支持SQL的商用产品，随着数据库技术和应用的发展，为不同RDBMS提供一致的语言成为一种现实的需要。
对SQL标准影响最大的机构是著名的数据库产品制造商，而具体的制定者则是一些非营利机构，如国际标准化组织ISO、美国国家标准委员会ANSI等。各国通常会按照ISO和ANSI标准制定自己的国家标准。
SQL发展的简要历史如下。
（1）1986年，ANSI X3.135-1986，ISO/IEC 9075:1986，SQL-86。
（2）1989年，ANSI X3.135-1989，ISO/IEC 9075:1989，SQL-89。
（3）1992年，ANSI X3.135-1992，ISO/IEC 9075:1992，SQL-92（SQL2）。
（4）1999年，ISO/IEC 9075:1999，SQL:1999（SQL3）。
（5）2003年，ISO/IEC 9075:2003，SQL:2003。
（6）2008年，ISO/IEC 9075:2008，SQL:2008。
（7）2011年，ISO/IEC 9075:2011，SQL:2011。
9.4.2.2 Select语句
1. 基本语法
Select语句主要用来查询数据，其基本语法格式如下。
参数说明如下。
（1）[ALL | DISTINCT]：默认为ALL，代表全部。
（2）DISTINCT：去除重复记录。
（3）<select_list>：查询的字段列表，所有字段用*代替。
（4）{<table_source>} [,…n]：要查询的表名，多个表用“,”隔开。
（5）<search_condition>：查询的条件。
例如，查询“Northwind”数据库中“产品”表中的产品代码、产品名称、标准成本、列出价格等信息，该表中的数据如图9-98所示。
图9-98 “产品”表中的数据
操作步骤如下。
（1）打开“Northwind”数据库。
（2）选择“创建”→“查询设计”命令，在弹出的“显示表”对话框中单击“关闭”按钮。
选择“设计”→“SQL视图”命令，输入SQL语句，如图9-99所示。
图9-99 输入SQL语句
（3）输入如下SQL语句：
（4）单击“运行”按钮，查询结果如图9-100所示。
图9-100 查询结果
2. WHERE子句
WHERE子句用来在查询中限制显示记录的条件，它实际上是一个表达式，如何构造表达式检索满足条件的记录是关键问题。表达式是由运算符、常量、函数等若干部分组成的有意义的算式，单个常量、数值、字段或函数可以看成是表达式的特例。
9.4.2.3 运算符及特殊符号
在Access 2016中，根据运算符的性质可分为算术、比较和逻辑运算符；另外还有一些在表达式中起特殊作用的符号，如字符串运算符、通配符和定界符等，一个表达式可以包含不同的运算符。
（1）算术运算符。
算术运算符主要用来执行数学运算，返回的结果是与之对应的数字，如表9-10所示。
表9-10 算术运算符
（2）字符串运算符。
字符串运算符主要用于连接两个字符串，其运算符是&和＋，返回的结果是字符串。
• &用来强制两个表达式作为一个字符串连接。
例如，"Hello"&"World"结果为"HelloWorld"；"Cheek"&48&"abce"结果为"Cheek48abce"，运算符将数值48强制转换成字符串“48”，然后连接。
• ＋连接两个字符串，要求两端的类型必须一致。
例如，"Hello"＋"World"的结果为"HelloWorld"，此种情况与&运算符的功能相同，而"Cheek"＋48＋"abce"，系统会给出出错信息“类型不匹配”。为了避免与算术运算符“＋”号混淆，一般用“&”号进行两个字符串的连接，而尽量不使用“＋”号。
（3）比较运算符。
比较运算符主要用来执行大、小、等于和不等于的比较，返回的结果是布尔类型的值（True或False，-1或0），如表9-11所示。
表9-11 比较运算符
（4）逻辑运算符。
逻辑运算符主要用来执行与、或、非的判断，返回的结果是布尔类型的值（True或False，-1或0），如表9-12所示。
表9-12 逻辑运算符
（5）其他运算符。
其他运算符如表9-13所示。
表9-13 其他运算符
说明如下。
• Between…And…。
指定值的范围在…到…之间，如在“Northwind”数据库中查询“产品”表中“标准成本”在15～20之间的产品，可以设定条件为：
标准成本 Between 15 And 20
该条件与以下语句等价：
标准成本≥15 And标准成本≤20
完整的查询语句如下。
查询结果如图9-101所示。
图9-101 查询结果
• In。
指定值属于列表中所列出的值，如在“Northwind”数据库中查询“产品”表中“产品名称”为“苹果汁”“蕃茄酱”“桂花糕”的产品，可以设定条件如下。
该条件与以下语句等价。
完整的查询语句如下。
查询结果如图9-102所示。
图9-102 查询结果
• Is。
指定所在字段中是否包含数据，Is Null表示查找该字段中没有数据的记录；Is Not Null表示查找该字段中有数据的记录。
• Like。
查找相匹配的文字，用通配符来设定文字的匹配条件。其中“？”代表任意一个字符，“*”代表任意连续字符，“#”代表任意一个数字位，[0-9]代表数字位，[a-z]代表字母位。
例如，在“Northwind”数据库中查询“产品”表中“产品名称”以“片”结尾的产品，可以设定条件如下。
完整的查询语句如下。
查询结果如图9-103所示。
图9-103 查询结果
再如，在“Northwind”数据库中查询“产品”表中“产品名称”以“片”结尾的产品，并且长度为3，可以设定条件如下。
完整的查询语句如下。
查询结果如图9-104所示。
图9-104 查询结果
9.4.2.4 常用函数
1. 数值函数。
（1）绝对值函数。
• 格式：ABS(<数值表达式>)。
• 功能：求数值表达式值的绝对值。
• 举例：ABS(-7)返回7。
（2）取整函数。
• 格式：INT(<数值表达式>)。
• 功能：取数值表达式值的整数部分值。
• 举例：INT(7.5)返回7。
2. 字符函数
（1）空字符串函数。
• 格式：SPACE(<数值表达式>)。
• 功能：返回由数值表达式的值确定的空格个数组成的字符串。
• 举例：SPACE(8)返回8个空格。
（2）截取左子串函数。
• 格式：LEFT(<字符串表达式>，<数值表达式>)。
• 功能：从字符型表达式左边的第1个字符开始截取子串。
若数值型表达式的值大于0且小于等于字符串的长度，则子串的长度与数值型表达式值相同。例如，LEFT(“我是中国人”，2)返回“我是”。
若数值型表达式的值大于字符串的长度，则给出整个字符串。例如，LEFT(“我是中国人”，8)返回“我是中国人”。
若数值型的表达式小于或等于0，则给出一个空字符串。
（3）截取右子串函数。
• 格式：RIGHT(<字符串表达式>，<数值表达式>)。
• 功能：从字符串表达式的最右端的第1个字符开始，截取数值表达式值对应数量的字符。
• 举例：RIGHT(“我是中国人”，3)返回“中国人”。
（4）测试字符串长度函数。
• 格式：LEN(<字符串表达式>)。
• 功能：返回字符串表达式的字符个数。
• 举例：LEN(“我是中国人”)返回“5”。
（5）删除前导和尾随空格函数。
格式及功能如下。
• LTRIM(<字符串表达式>)，删除字符串表达式的前导空格。
• RTRIM(<字符串表达式>)，删除字符串表达式的尾随空格。
• TRIM(<字符串表达式>)，删除字符串表达式的前导和尾随空格。
举例如下。
• LEN(SPACE(3)+＂我是中国人＂+SPACE(3))返回“11”。
• LEN(LTRIM(SPACE(3)+＂我是中国人＂+SPACE(3)))返回“8”。
• LEN(RTRIM(SPACE(3)+＂我是中国人＂+SPACE(3)))返回“8”。
• LEN(TRIM(SPACE(3)+＂我是中国人＂+SPACE(3)))返回“5”。
（6）截取子串函数。
• 格式：MID(<字符串表达式>，[<n1>,<n2>])。
• 功能：从字符串表达式的左端第n1个字符开始截取n2个字符作为返回的子字符串。
• 举例：MID(“我是中国人”，3，2)返回“中国”。
3. 时间日期函数
（1）系统日期函数。
• 格式：DATE()。
• 功能：返回当前系统日期。
• 举例：DATE()返回“2020-05-22”。
（2）系统时间函数。
• 格式：TIME()。
• 功能：返回当前系统时间。
• 举例：TIME()返回“14:30:30”。
（3）年函数。
• 格式：YEAR(<日期表达式>)。
• 功能：返回年的4位整数。
• 举例：YEAR(DATE())返回“2020”。
（4）月函数。
• 格式：MONTH(<日期表达式>)。
• 功能：返回1～12之间的整数，表示一年的某月。
• 举例：MONTH(DATE())返回“5”。
（5）日函数。
• 格式：DAY(<日期表达式>)。
• 功能：返回值为1～31之间的整数，表示日期中的某一天。
• 举例：DAY(DATE())返回“22”。
（6）系统日期和时间函数。
• 格式：NOW()。
• 功能：返回当前机器系统的日期和时间。
• 举例：NOW()返回“2020-05-22 14:31:22”。
4. 统计函数
（1）求和函数。
• 格式：SUM (<数值型表达式>)。
• 功能：返回字段中值的总和。
• 举例：对“Northwind”数据库中“产品”表中“目标水平”求和的SQL语句如下。
（2）求平均函数。
• 格式：AVG(<数值型表达式>)。
• 功能：求数值类型字段的平均值。
• 举例：对“Northwind”数据库中“产品”表中“目标水平”求平均的SQL语句如下。
（3）统计记录个数函数。
• 格式：COUNT(<字符串表达式>)。
• 功能：统计记录个数。
• 说明：字符串表达式可以是一个字段名（数值类型），或者含有数值型字段的表达式。当用格式COUNT(*)时，将统计所有记录的个数，包括Null值字段的记录。
• 举例：统计“Northwind”数据库中“产品”表中记录条数的SQL语句如下。
（4）求最大和最小值函数。
• 格式：MAX(<字符串表达式>)。
MIN(<字符串表达式>)。
• 功能：返回一组指定字段中的最大和最小值。
• 说明：字符串表达式可以是一个字段名（数字类型），或者含有数字型字段的表达式。
• 举例：查询“Northwind”数据库中“产品”表中“目标水平”最大值或最小值的SQL语句如下。
9.4.2.5 使用举例
打开“Northwind”数据库，完成以下查询。
（1）在“员工”表中查询职务为“销售经理”记录，SQL语句如下。
查询结果如图9-105所示。
图9-105 查询结果
（2）在“员工”表中查询“电子邮件地址”第2个字母是a的所有记录，SQL语句如下。
查询结果如图9-106所示。
图9-106 查询结果
（3）在“订单”表中查询发货城市为“南京”并且发货日期为2006年1月的记录，SQL语句如下。
查询结果如图9-107所示。
图9-107 查询结果
9.4.2.6 Group By、Having、Order By子句
Group By子句是用来进行分组查询的，要与聚合函数搭配使用。例如，统计“Northwind”数据库中“采购订单表”中每个供应商供货的次数可以使用Group By子句来完成，SQL语句如下。
查询结果如图9-108所示。
Having子句用来在分组时控制条件，要与Group By搭配使用。例如，统计“Northwind”数据库中采购订单表中供应商供货次数大于等于3的记录，SQL语句如下。
查询结果如图9-109所示。
图9-108 查询结果
图9-109 查询结果
Order By子句用来排序查询结果，ASC代表升序，DESC代表降序。例如，统计“Northwind”数据库中“采购订单”表中每个供应商供货的次数并按统计次数降序排列，SQL语句如下。
9.4.2.7 子查询
子查询将复杂查询分解为一些有关列的逻辑步骤，结果基于其他查询结果，常见的用法是将其作为一个派生表或作为表达式。
1. 使用子查询作为派生表
（1）在查询中作为一个结果集以表的形式体现。
（2）在FROM子句中作为一个表出现。
（3）和查询的其他部分一起优化。
例如，将“Northwind”数据库中“员工”表的姓氏和名字组合成一个字段进行查询，SQL语句如下。
查询结果如图9-110所示。
图9-110 查询结果
2. 使用子查询作为表达式
（1）子查询被作为一个值或表达式处理。
（2）在查询中执行一次。
例如，打开“Northwind”数据库。计算“订单”表中订单日期与发货日期的间隔天数和平均间隔天数，并显示订单日期、发货日期、间隔天数和平均间隔天数，SQL语句如下。
查询结果如图9-111所示。
图9-111 查询结果
9.4.2.7 Create、Insert、Update、Delete语句
1. Create语句
Create语句主要用来创建表、索引等对象。
（1）创建表。
基本语法如下。
参数说明如表9-14所示。
表9-14 参数说明
例如，创建的student表包含“sno”（学号，整型）、“sname”（姓名，文本），SQL语句如下。
（2）创建索引。
基本语法如下。
参数说明如表9-15所示。
表9-15 参数说明
例如，为“student”表的姓名创建索引，SQL语句如下。
2. Insert语句
Insert语句主要用来在表中插入单条或多条记录，称为“追加查询”。
（1）多条记录追加查询的语法如下。
（2）单条记录追加查询的语法如下。
参数说明如表9-16所示。
表9-16 参数说明
可以使用Insert INTO语句在使用单记录追加查询语法的表中添加一条记录，此时代码指定记录各字段的名称和值。必须指定要分配值的记录的各个字段，以及该字段的值；否则为缺少的列插入默认值或NULL，记录添加到表末。
还可以使用Insert INTO语句通过SELECT … FROM子句追加另一个表或查询中的一组记录，如上文中的多记录追加查询语法所示。在这种情况下，SELECT子句指定要追加到target表的字段。
source或target可以指定一个表或一个查询，如果指定的是查询，则Access数据库引擎将记录追加到该查询指定的任一个表或所有表中。
可以选择是否使用Insert INTO，如果使用，需位于SELECT语句之前。
如果目标表中包含主键，则确保向一个或多个主键字段追加非NULL的唯一值；否则Access数据库引擎不会追加记录。
如果向包含AutoNumber字段的表中追加记录，并需要为追加的记录重新编号，则不要在查询中包含AutoNumber字段；如果要获取AutoNumber字段中的原始值，则在查询中包含该字段。
使用IN子句将记录追加到另一个数据库的表中。
要创建新表，则使用SELECT…INTO语句，而不是创建一个表查询。
若要在运行追加查询前找出将追加的记录，则首先执行并查看使用相同选择条件的查询结果。
追加查询将记录从一个或多个表复制到另一个表，包含追加的记录的表不受该追加查询的影响。
可以使用Values子句为一个新记录中的各字段指定值，而不是从另一个表追加现有的记录。如果省略字段列表，Values子句则必须为表中的每一个字段指定一个值；否则INSERT操作将失败。为要创建的每一条额外的记录分别再使用一个含Values子句的INSERT INTO语句，如在“student”表中插入一条记录的语句如下。
3. Update语句
Update语句用来创建一个更新查询，即基于指定条件在指定表中更改字段中的值，其语法如下。
Update语句不生成结果集，使用更新查询更新记录后则无法撤消此操作。如果要知道已更新的记录，首先检查使用相同条件的选择查询的结果。然后运行更新查询，随时维护数据的备份副本。如果更新了错误的记录，可以从备份副本检索它们。
例如，将“student”表中学号为“20180101”的姓名改为“李四”的语句如下。
4. Delete语句
Delete语句用来创建一个删除查询，从FROM子句列出的一个或多个表中删除满足WHERE子句的记录，其语法如下。
（1）删除多条记录时Delete语句非常有用。
（2）要从数据库中删除整个表，可以使用带有Drop语句的Execute方法。如果删除表，则会丢失结构；相反，使用Delete语句只会删除数据，而表结构和所有表属性（如字段属性和索引）保持不变。
（3）使用删除查询删除记录后，无法撤消该操作。如果要知道删除的记录，则首先检查使用相同条件的选择查询的结果，然后运行删除查询。随时维护数据的备份副本，如果错误地删除了记录，可以从备份副本检索它们。
（4）可以使用Delete语句从与其他具有一对多关系的表中删除记录，级联删除操作会导致在查询中删除关系一端的相应记录时删除表中关系多端的记录。例如，在“学生表”和“成绩表”之间的关系中“学生表”在关系的一端，“成绩表”在关系的多端。如果指定了级联删除选项，则从“学生表”中删除记录会导致删除“成绩表”中相应的成绩记录。
（5）删除查询删除整个记录，而不仅仅是特定字段中的数据。如果要删除特定字段的值，可创建更新查询将值更改为“Null”。
例如，删除学生表中学号为“20180101”学生记录的SQL语句如下。
9.4.3 任务要求
任务要求如下。
（1）启动Access 2016。
（2）打开“学生管理”数据库。
（3）使用Create语句创建一个“辅导员表”，其中包括“辅导员编号”（自动编号、主键）、“辅导员姓名”（文本、长度20）两个字段。
（4）使用Insert语句在“辅导员表”中插入“陈华”“李进”两条记录。
（5）使用Update语句更新“学生基本情况表”的“辅导员”字段，条件是17级和18级的辅导员是陈华，19级和20级是李进。
（6）使用Delete语句删除“学生基本情况表”中“姓名”为“周山”的记录。
（7）使用Select语句查询“学生基本情况表”中“进校时间”大于等于2年且姓张的记录。
（8）使用Create语句为“辅导员表”中的“姓名”字段建立名为“Idx_姓名”的索引。
（9）使用Group By语句统计每个班的学生人数，要求学生人数要大于等于2并按学生人数的降序排列。
9.4.4 实现过程
实现过程如下。
（1）启动Access 2016。
（2）打开“学生管理”数据库。
（3）创建“辅导员表”。单击“创建”→“查询设计”按钮，在弹出的“显示表”对话框中单击“关闭”按钮。单击工具栏中的“SQL视图”，输入如下SQL语句。
（4）单击“运行”按钮创建“辅导员表”。
（5）插入记录，在“SQL视图”中输入如下SQL语句。
（6）单击“运行”按钮，在“辅导员表”中插入两条记录。
（7）修改记录，在“SQL视图”中输入如下语句。
（8）单击“运行”按钮即可修改“学生基本情况表”中的“辅导员”字段。
（9）删除记录，在“SQL视图”中输入如下语句。
（10）单击“运行”按钮删除“学生基本情况表”中“姓名”为“周山”的记录。
（11）查询记录，在“SQL视图”中输入如下语句。
（12）单击“运行”按钮查询“学生基本情况表”中“进校日期”大于等于2年且姓张的记录。
（13）创建索引，在“SQL视图”中输入如下语句。
（14）单击“运行”按钮“为辅导员表”中的“姓名”字段建立名为“Idx_姓名”的索引。
（15）分组统计，在“SQL视图”中输入如下语句。
（16）单击“运行”按钮即可统计每个班的学生人数。
9.4.5 技能训练
技能训练如下。
（1）启动Access 2016。
（2）打开“员工管理”数据库。
（3）使用Create语句创建一个“职务表”，其中包括“职务编号”（自动编号、主键）、“职务名称”（文本、长度20）两个字段。
（4）使用Insert语句在“职务表”中插入“部门经理”“普通员工”两条记录。
（5）使用Update语句更新“职务表”中的“职务编号”字段，更新条件是张三、李四的职务是部门经理，其余为普通员工。
（6）使用Delete语句删除“职务表”中姓名为“李四”的记录。
（7）使用Select语句查询“职务表”中入职日期大于等于3年且姓张的记录。
（8）使用Create语句为“职务表”中的“职务名称”字段建立名为“Idx_职务名称”的索引。
（9）使用Group By语句统计每个部门的人数，要求部门人数要大于等于2并按人数降序排列。