计算机二级access辅导讲义 Ch06

,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,单击此处编辑母版标题样式,30,Access,数据库与程序设计,第,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,合并查询,6.1 SQL,语言概述,SQL,语言是数据库的标准语言，主要特点：,（,1,）高度集成化,集数据定义、数据操纵、数据查询和数据控制功能于一体。,（,2,）高度非过程化,用,SQL,语言进行数据操作，不必告诉计算机怎么做，只要提出做什么，系统就可以自动完成全部工作。,（,3,）面向集合的操作方式,采用集合操作方式，操作对象和操作结果都是记录集。,（,4,）简洁易学,SQL,语言完成核心功能只用了,9,个命令动词，且接近英语的自然语法。,（,5,）用法灵活,SQL,语言既可以独立使用，也可以嵌入到高级语言中使用。,6.2,数据定义,表的定义：,create,表的修改：,alter,表的删除：,drop,6.2.1,定义表,1,在,Access,中使用,SQL,语句,（,1,）打开查询设计视图。,（,2,）执行“查询,|SQL,特定查询,|,数据定义”命令，在“数据定义查询”窗口中输入,SQL,语句。,在“数据定义查询”窗口中,一次只能输入一条,SQL,语句,。,（,3,）单击工具栏的“运行”按钮 ， 执行,SQL,语句。,（,4,）根据需要，将,SQL,语句保存为一个查询对象，或直接关闭查询窗口。,定义表（续,1,）,2,使用,CREATE TABLE,语句定义表,CREATE TABLE ( () NOT NULL,PRIMARY KEY | UNIQUE , () NOT NULL,PRIMARY KEY | UNIQUE , ),字段的数据类型必须用字符表示。,定义单字段主键或唯一键时，可以直接在字段名后加上,PRIMARY KEY,或,UNIQUE,关键字。,定义多字段主键或唯一键，应使用,PRIMARY KEY,或,U,NIQUE,子句。,使用,references,子句可以在两表之间建立关系。,数据类型,Text,文本,Btye,字节,Integer,长整型,Single,单精度数字,Float,双精度数字,Currency,货币,Memo,备注,Date,日期,/,时间,Logical,是,/,否,OLEObject,OLE,对象,Not null,该字段不允许为空值,定义表（续,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,),范例,2,在“教学管理”数据库中，使用,SQL,语句定义一个名为,COURSE,的表，结构为：课程号,(,文本型，,3,字符,),、课程名,(,文本型，,15,字符,),、学分,(,字节型,),，课程号为主键。,定义表（续,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,表通过“学号”建立关系,定义表（续,4,）,3,使用,CREATE INDEX,语句建立索引,CREATE UNIQUE INDEX ON (ASC|DESC,ASC|DESC,) WITH PRIMARY,UNIQUE,指定唯一索引，,WITH PRIMARY,指定主索引。,索引值的排列方式：,ASC,升序，,DESC,降序，默认为升序,【,例,6.4】,使用,SQL,语句建立索引，,COURSE,表按课程名建立唯一索引，索引名称为,cname,。,SC,表按课程号升序和成绩降序建立索引，索引名称为,cno_score,。,create unique index,cname,on course(,课程名,),create index,cno_score,on sc(,课程号,成绩,desc,),6.2.2,修改表,1,修改字段,ALTER,TABLE ALTER (),使用该命令是修改表的结构，不能修改字段名。,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,电话号码,6.2.3,删除表,1,删除索引,DROP INDEX ON ,2,删除表,DROP TABLE ,删除表后，在表上定义的索引也一起被删除。,【,例,6.6】,删除,COURSE,表中名为“,CNAME”,的索引项。,drop index,cname,on course,6.3,数据操纵,插入记录：,insert,更新记录：,update,删除记录：,delete,6.3.1,插入记录,INSERT INTO (,，,) VALUES(,),如果,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,个字段,6.3.2,更新记录,UPDATA SET = ,=,，, WHERE ,如果不带,WHERE,子句，则更新表中所有的记录。,如果带,WHERE,子句，则只更新表中满足条件的记录。,【,例,6.8】,将,STUDENT,表中所有女生的“贷款否”字段改为“否”。,update student,set,贷款否,=no,where,性别,=,女,6.3.3,删除记录,DELETE FROM WHERE ,如果不带,WHERE,子句，则删除表中所有的记录（该表对象仍保留在数据库中）。如果带,WHERE,子句，则只删除表中满足条件的记录。,【,例,6.9】,删除,STUDENT,表中学号为“,900008”,的学生记录。,delete from student where,学号,=900008”,6.4,数据查询,数据查询：,select,包括：单表查询,多表查询,嵌套查询,合并查询,6.4.1,查询的基本语法,SELECT,语句的基本格式,:,SELECT,ALL|DISTINCT TOP PERCENT, AS ,FROM,AS , AS WHERE AND GROUP BY HAVING ORDER BY ASC|DESC ,查询的基本语法（续,）,SELECT,命令中各子句与查询设计器中各项之间的对应关系。,单击工具栏上的“视图”按钮，从视图列表中选择“,SQL,视图”选项或“设计视图”选项，可以在,SELECT,命令输入窗口和查询设计器窗口之间切换。,6.4.2,单表查询,单表查询仅涉及一个表的查询。,1,查询表中的若干列,从表中选择需要的目标列。格式：,SELECT ,，, FROM ,（,1,）查询指定的字段 在目标列中指定要查询的各字段名。,（,2,）查询所有的字段 在目标列中使用“*” 。,（,3,）消除重复的记录 在字段名前加上,DISTINCT,关键字。,（,4,）查询计算值,【,例,6.10】,查询“学生”表中所有学生的姓名、性别和学号。,【,例,6.11】,查询“课程”表中所有课程的信息。,单表查询（续,1,）,2,选择查询,从表中选出满足条件的记录。格式：,SELECT FROM WHERE ,WHERE,子句中的条件是一个逻辑表达式，由多个关系表达式通过逻辑运算符连接而成。,【,例,6.15】,查询“学生”表中计算机系女生的学号和姓名。,select,学号,姓名,from,学生,where,性别,=“,女”,and,系部,=“,计算机”,【,例,6.17】,查询“选课”表中成绩在,80,90,分之间的记录。,select * from,选课,where,成绩,between 80 and 90,单表查询（续,2,）,3,排序查询,使用,ORDER BY,子句可以对查询结果按照一个或多个列的升序（,ASC,）,或降序（,DESC,）,排列，默认是升序。格式,:,ORDER BY ASC|DESC,【,例,6.20】,查询,80,90,分的记录，同一门课程按成绩降序排。,select * from,选课,where,成绩,between 80 and 90,order by,课程号,成绩,desc,使用,TOP,短语可以选出排在前面的若干记录。格式：,TOP ,或,TOP PERCENT,TOP,子句必须和,ORDER BY,子句同时使用。,【,例,6.21】,查询“选课”表中成绩排在前,5,名的记录。,select,top 5,* from,选课,order by,成绩,desc,单表查询（续,3,）,4,分组查询,使用,GROUP BY,子句可以对查询结果按照某一列的值分组。,分组查询通常与,SQL,聚合函数一起使用，先按指定的数据项分组，再对各组进行合计。如果未分组，则聚合函数将作用于整个查询结果。,常用聚合函数：,COUNT,、,AVG,、,SUM,、,MIN,、,MAX,【,例,6.22】,统计“学生”表中的学生人数。,select,count(*) AS,总人数,from,学生,【,例,6.23】,统计“学生”表中各系的学生人数。,select,系部,count(*) as,各系人数,from,学生,group by,系部,单表查询（续,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,当,WHERE,子句、,GROUP BY,子句、,HAVING,子句同时出现时，先执行,WHERE,子句，从表中选取满足条件的记录，然后执行,GROUP BY,子句对选取的记录进行分组，再执行,HAVING,短语从分组结果中选取满足条件的组。,6.4.3,多表查询,1.,多表查询同时涉及两个或多个表的数据。,2.,多表查询时，通常需要指定两个表的联接条件，该条件放在,WHERE,子句中，格式为：,SELECT FROM , WHERE . = .,3.,联接条件中的联接字段一般是两个表中的公共字段或语义相同的字段。,4.,在,SELECT,命令中可以使用表的别名。格式：,SELECT ,FROM , WHERE . = .,多表查询（续,1,）,【,例,6.27】,查询所有学生的学号、姓名、选修的课程号和成绩。,select,学生,.,学号,姓名,课程号,成绩,from,学生,选课,where,学生,.,学号,=,选课,.,学号,或,select,xs,.,学号,姓名,课程号,成绩,from,学生,xs,选课,xk,where,xs,.,学号,=,xk,.,学号,【,例,6.28,1】,查询,85,分以上的学号、姓名、选修课程号和成绩。,select,xs,.,学号,姓名,课程号,成绩,from,学生,xs,选课,xk,where,xs,.,学号,=,xk,.,学号,and,成绩,85,WHERE,子句中同时包含了联接条件和查询条件,多表查询（续,2,）,多表查询时，还可以使用另一种联接格式：,SELECT FROM ,INNER JOIN | LEFT JOIN | RIGHT JOIN ON . = .,INNER JOIN,内部联接,LEFT JOIN,左外部联接,RIGHT JOIN,右外部联接,【,例,6.28,2】,查询,85,分以上的学号、姓名、选修课程号和成绩。,select,xs,.,学号,姓名,课程号,成绩,from,学生,xs,inner join,选课,xk,on,xs,.,学号,=,xk,.,学号,where,成绩,85,6.4.4,嵌套查询,嵌套查询是将一个,SELECT,语句包含在另一个,SELECT,语句的,WHERE,子句中，嵌套查询也称为子查询。,子查询（内层查询）的结果用作建立其父查询（外层查询）的条件，因此，子查询的结果必须有确定的值。,利用嵌套查询可以将几个简单查询构成一个复杂查询，从而增强,SQL,的查询能力。,【,例,6.30】,查询“王小丽”选修的课程和成绩。,select,学号,课程号,成绩,from,选课,where,学号,= ( select,学号,from,学生,where,姓名,=“,王小丽”,),括号内的,SELECT,语句为子查询，括号不能缺少。,6.4.5,合并查询,将两个,SELECT,语句的查询结果通过并运算（,UNION,）,合并为一个查询结果。,进行合并查询时，要求两个查询结果具有相同的字段个数，并且对应字段的数据类型也必须相同。,【,例,6.32】,查询法律系和金融系学生的学号、姓名和性别。,select,学号,姓名,性别,from,学生,where,系部,=“,法律”,union select,学号,姓名,性别,from,学生,where,系部,=,金融,