计算机二级access辅导讲义Ch.ppt

2 第6章 SQL语言 6.1 SQL语言概述 6.2 数据定义 6.2.1 定义表 6.2.2 修改表 6.2.3 删除表 6.3 数据操纵 6.3.1 插入记录 6.3.2 更新记录 6.3.3 删除记录 6.4 数据查询 6.4.1 查询的基本语法 6.4.2 单表查询 6.4.3 多表查询 6.4.4 嵌套查询 6.4.5 合并查询 3 6.1 SQL语言概述 SQL语言是数据库的标准语言，主要特点： （1）高度集成化 n 集数据定义、数据操纵、数据查询和数据控制功能于一体。 （2）高度非过程化 n 用SQL语言进行数据操作，不必告诉计算机怎么做，只要提 出做什么，系统就可以自动完成全部工作。 （3）面向集合的操作方式 n 采用集合操作方式，操作对象和操作结果都是记录集。 （4）简洁易学 n SQL语言完成核心功能只用了9个命令动词，且接近英语的 自然语法。 （5）用法灵活 n SQL语言既可以独立使用，也可以嵌入到高级语言中使用。 4 6.2 数据定义 表的定义：create 表的修改：alter 表的删除：drop 5 6.2.1 定义表 1在Access中使用SQL语句 （1）打开查询设计视图。 （2）执行“查询|SQL特定查询|数据定义”命令，在“数据定义查 询”窗口中输入SQL语句。 n 在“数据定义查询”窗口中一次只能输入一条SQL语句。 （3）单击工具栏的“运行”按钮 ， 执行SQL语句。 （4）根据需要，将SQL语句保存为一个查询对象，或直接关闭 查询窗口。 6 定义表（续1） 2使用CREATE TABLE语句定义表 CREATE TABLE ( () NOT NULL PRIMARY KEY | UNIQUE , () NOT NULL PRIMARY KEY | UNIQUE , ) n 字段的数据类型必须用字符表示。 n 定义单字段主键或唯一键时，可以直接在字段名后加上 PRIMARY KEY或UNIQUE关键字。 n 定义多字段主键或唯一键，应使用PRIMARY KEY或UNIQUE子 句。 n 使用references子句可以在两表之间建立关系。 7 数据类型 Text 文本 Btye 字节 Integer 长整型 Single 单精度数字 Float 双精度数字 Currency 货币 Memo 备注 Date 日期/时间 Logical 是/否 OLEObject OLE对象 Not null 该字段不允许为空值 8 定义表（续2） 【例6.1】使用SQL语句定义一个名为STUDENT的表： 学号(文本型，6字符)、姓名(文本型，3字符)、性别(文本型，1 字符)、出生日期(日期/时间型)、贷款否(是/否型)、简历（备注 型）、照片（OLE对象型），学号为主键，姓名不允许为空值。 create table student ( 学号 text(6) primary key , 姓名 text(3) not null, 性别 text(1), 出生日期 date, 贷款否 logical, 简历 memo, 照片 OLEObject ) 9 范例2 在“教学管理”数据库中，使用SQL语句定义一个名为COURSE的 表，结构为：课程号(文本型，3字符)、课程名(文本型，15字 符)、学分(字节型)，课程号为主键。 10 定义表（续3） 【例6.3】定义SC的表：学号（文本型，6字符）、课程号(文本 型，3字符)、成绩(单精度型)，主键由学号和课程号组成。 create table sc ( 学号 text(6), 课程号 text(3), 成绩 single, primary key(学号,课程号) ) 如果要在STUDENT、COURSE、SC之间通过“学号”和“课程号”字 段建立关系，可以修改SC表的定义。 create table sc ( 学号 text(6) references student(学号), 课程号 text(3) references course(课程号), 成绩 single, primary key(学号,课程号) ) Sc表和course表通过“课程号”建立关系 Sc表和student表通过“学号”建立关系 11 定义表（续4） 3使用CREATE INDEX语句建立索引 CREATE UNIQUE INDEX ON (ASC|DESC,ASC|DESC,) WITH PRIMARY n UNIQUE 指定唯一索引，WITH PRIMARY 指定主索引。 n索引值的排列方式：ASC升序，DESC降序，默认为升序 【例6.4】 使用SQL语句建立索引，COURSE表按课程名建立唯 一索引，索引名称为cname。SC表按课程号升序和成绩降序建 立索引，索引名称为cno_score。 create unique index cname on course(课程名) create index cno_score on sc(课程号,成绩 desc) 12 6.2.2 修改表 1修改字段 ALTER TABLE ALTER () n 使用该命令是修改表的结构，不能修改字段名。 2添加字段 ALTER TABLE ADD () 3删除字段 ALTER TABLE DROP 【例6.5】 在STUDENT表中增加一个“电话号码”字段(长整型)， 然后将该字段修改为文本型（8字符），最后删除该字段。 alter table student add 电话号码 integer alter table student alter 电话号码 text(8) alter table student drop 电话号码 13 6.2.3 删除表 1删除索引 DROP INDEX ON 2删除表 DROP TABLE n 删除表后，在表上定义的索引也一起被删除。 【例6.6】 删除COURSE表中名为“CNAME”的索引项。 drop index cname on course 14 6.3 数据操纵 插入记录：insert 更新记录：update 删除记录：delete 15 6.3.1 插入记录 INSERT INTO (,，) VALUES(,) n 如果INTO后缺省字段名，则必须为新记录中的每个字段都 赋值，且插入数据的类型和顺序要与表中定义的字段一一对应 。 【例6.7】 向STUDENT表中插入2个学生记录。 Insert into student values(“900009“,“黄娟“,“女“,#1990-5-26#,yes,null,null) 每个字段都有取值，所以into子句后可以省略字段名 Insert into student(学号,姓名,性别) values(“900008“,“李荣辉“,“男“) 只有3个字段被赋值，所以into子句后面必须说明是哪3个字段 16 6.3.2 更新记录 UPDATA SET = ,=， WHERE n 如果不带WHERE子句，则更新表中所有的记录。 n 如果带WHERE子句，则只更新表中满足条件的记录。 【例6.8】 将STUDENT表中所有女生的“贷款否”字段改为“否”。 update student set 贷款否=no where 性别=“女“ 17 6.3.3 删除记录 DELETE FROM WHERE n 如果不带WHERE子句，则删除表中所有的记录（该表对象 仍保留在数据库中）。如果带WHERE子句，则只删除表中满 足条件的记录。 【例6.9】 删除STUDENT表中学号为“900008”的学生记录。 delete from student where 学号=“900008” 18 6.4 数据查询 数据查询：select 包括：单表查询 多表查询 嵌套查询 合并查询 19 6.4.1 查询的基本语法 SELECT语句的基本格式: SELECT ALL|DISTINCT TOP PERCENT AS FROM AS , AS WHERE AND GROUP BY HAVING ORDER BY ASC|DESC 20 查询的基本语法（续） SELECT命令中各子句与查询设计器中各项之间的对应关系。 单击工具栏上的“视图”按钮，从视图列表中选择“SQL视图”选项 或“设计视图”选项，可以在SELECT命令输入窗口和查询设计器 窗口之间切换。 21 6.4.2 单表查询 单表查询仅涉及一个表的查询。 1查询表中的若干列 n 从表中选择需要的目标列。格式： SELECT ,， FROM （1）查询指定的字段 在目标列中指定要查询的各字段名。 （2）查询所有的字段 在目标列中使用“*” 。 （3）消除重复的记录 在字段名前加上DISTINCT关键字。 （4）查询计算值 【例6.10】 查询“学生”表中所有学生的姓名、性别和学号。 【例6.11】 查询“课程”表中所有课程的信息。 22 单表查询（续1） 2选择查询 n 从表中选出满足条件的记录。格式： SELECT FROM WHERE n WHERE子句中的条件是一个逻辑表达式，由多个关系表达 式通过逻辑运算符连接而成。 【例6.15】 查询“学生”表中计算机系女生的学号和姓名。 select 学号,姓名 from 学生 where 性别=“女” and 系部=“计算机” 【例6.17】 查询“选课”表中成绩在8090分之间的记录。 select * from 选课 where 成绩 between 80 and 90 23 单表查询（续2） 3排序查询 n 使用ORDER BY子句可以对查询结果按照一个或多个列的升 序（ASC）或降序（DESC）排列，默认是升序。格式: ORDER BY ASC|DESC 【例6.20】 查询8090分的记录，同一门课程按成绩降序排。 select * from 选课 where 成绩 between 80 and 90 order by 课程号, 成绩 desc n 使用TOP短语可以选出排在前面的若干记录。格式： TOP 或 TOP PERCENT TOP子句必须和ORDER BY子句同时使用。 【例6.21】 查询“选课”表中成绩排在前5名的记录。 select top 5 * from 选课 order by 成绩 desc 24 单表查询（续3） 4分组查询 n 使用GROUP BY子句可以对查询结果按照某一列的值分组。 n 分组查询通常与SQL聚合函数一起使用，先按指定的数据项 分组，再对各组进行合计。如果未分组，则聚合函数将作用于 整个查询结果。 n 常用聚合函数：COUNT、AVG、SUM、MIN、MAX 【例6.22】 统计“学生”表中的学生人数。 select count(*) AS 总人数 from 学生 【例6.23】 统计“学生”表中各系的学生人数。 select 系部, count(*) as 各系人数 from 学生 group by 系部 25 单表查询（续4） 如果分组后还要求按一定的条件对这些组进行筛选，可以在 GROUP BY子句后加上HAVING短语指定筛选条件。 HAVING短语必须和GROUP BY子句同时使用。 【例6.25】 查询选修了3门以上课程的学生学号。 select 学号 from 选课 group by 学号 having count(*)=3 【例6.26】 查询选修了3门及以上课程，并且成绩在75分以上 的学生学号。 select 学号 from 选课 where 成绩=75 group by 学号 having count(*)=3 n 当WHERE子句、GROUP BY子句、HAVING子句同时出现时 ，先执行WHERE子句，从表中选取满足条件的记录，然后执 行GROUP BY子句对选取的记录进行分组，再执行HAVING短 语从分组结果中选取满足条件的组。 26 6.4.3 多表查询 1. 多表查询同时涉及两个或多个表的数据。 2. 多表查询时，通常需要指定两个表的联接条件，该条件放在 WHERE子句中，格式为： SELECT FROM , WHERE . = . 3. 联接条件中的联接字段一般是两个表中的公共字段或语义相 同的字段。 4. 在SELECT命令中可以使用表的别名。格式： SELECT FROM , WHERE . = . 27 多表查询（续1） 【例6.27】 查询所有学生的学号、姓名、选修的课程号和成绩 。 select 学生.学号,姓名,课程号,成绩 from 学生,选课 where 学生.学号=选课.学号 或 select xs.学号,姓名,课程号,成绩 from 学生 xs, 选课 xk where xs.学号= xk.学号 【例6.281】 查询85分以上的学号、姓名、选修课程号和成 绩。 select xs.学号,姓名,课程号,成绩 from 学生 xs, 选课 xk where xs.学号= xk.学号 and 成绩85 n WHERE子句中同时包含了联接条件和查询条件 28 多表查询（续2） 多表查询时，还可以使用另一种联接格式： SELECT FROM INNER JOIN | LEFT JOIN | RIGHT JOIN ON . = . n INNER JOIN 内部联接 n LEFT JOIN 左外部联接 n RIGHT JOI