Access中的SQL语言

1、Access中的SQL语言1 .SQL概述结构化查询语言(StructuredQueryLanguageSQL读成“sequel")自1974年提出，1975年旧M公司在RDBMS(关系型DBMS)SyatemR上实现以来，由于它功能丰富、使用方便、简洁易学，倍受用户和计算机工业界的欢迎，经不断修改、扩充和完善，现已成为RDBMS的标准语言。众多的RDBMS中实现了SQL语言，当然各数据库厂家在各自的RDBMS中的SQL语言与ISO颁布的标准SQL语言都或多或少有些差别。SQL语言包括了对数据库的所有操作，主要可分为三个部分数据定义语言(DDLDataDefinitionLangua

2、ge),用以定义数据库的逻辑结构，包括定义基本表、索引和视图。数据操纵语言(DMLDataManipulationLanguage),用以对数据的查询和更新，数据更新操作中包括插入、删除和修改三种操作。数据控制语言(DCLDataControlLanguage),用以对基本表和视图的授权即指定具体用户可使用哪些表或视图。SQL语言的特点：(1) 语言风格统一：尽管有不同功能的DDL、DML和DCL,但语言风格相同，再SQL语言既能独立地使用于联机交互方式，又能嵌入到高级程序设计语言如C、C+等语言中，而且在两种不同地使用方式下的SQL语句的语法基本一样；(2) 面向集合的操作方式：因为RDB的

3、结构是关系，即记录的集合，也是SQL语言的操作对象，所以操作速度比其他语言快；(3) 语言简洁，易学易用：尽管SQL的功能很强，但它的核心语句只有九条(CREATE、ALTER、DROP、SELECTINSERT、DELETE、UPDATE、GRANT和REVOKE),加之它的结构化特性，非常便于学习和使用。2 .在Access中使用SQL语句SQL语句的输入：打开一个已有的Access数据库，在“对象”栏中选中“查询”，在“列表框”内选定“在设计视图中创建查询”命令，双击之，或按“设计”按钮，系统将显示一个查询窗口和一个“显示表”对话框，关闭“显示表”对话框，在不关闭查询窗口的情况下，Acc

4、ess窗口的菜单栏上才会出现查询菜单，该查询菜单下有“SQL特定查询”子菜单、视图菜单中会出现“SQL视图”命令、同时原来显示在菜单栏下的数据库工具栏自动被切换为“查询设计”工具栏，其中的左上角会有一个视图切换按钮。无论用上述三种方法的哪一种，均可在Access窗口中显示供输入SQL语句的“查询”窗口。注意：在该窗口中只能输入一条SQL语句！但可分行输入，系统会把分号“；作为命令的结束标志，但一般在输入命令时，不必人为地输入分号。当需要分行输入时，不能把Sql语言的关键字或字段名分在不同行。Sql语句中所有的标点符号和运算符号均为ASCII字符！每两个单词之间至少要有一个空格或有必要的逗号！S

5、QL语句的保存：关闭"SQL语句输入”窗口，命名为一个查询SQL语句的执行：在“SQL语句输入”窗口中输入一个完整的SQL语句后，按“查询设计工具栏”中的运行按钮“！”，或在数据库窗口中双击已被保存的相应查询。SQLI.数据表定义语句前面我们是使用Access窗口中菜单命令的方式建立数据表的。现在，我们开始学习用语言来创建数据表。首先打开图书管理数据库文件“图书管理.mdb”，且按上述方法打开输入SQL语句的“查询”窗口。输入Sql语句：CREATETABLE图书（总编号CHAR（6）PRIMARYKEY，分类号CHAR（6）NOTNULL,书名CHAR（20），作者CHAR（15）

6、,出版社CHAR（10），单价SINGLE）运行该Sql语句后，在数据库窗口中打开数据表图书的设计视图，可核对一下用Sql语句和用窗口菜单命令建表的区别。说明：在MicrosoftAccess2000中的Sql语言是通过MicrosoftJet数据库引擎来支持Sql语言的，而MicrosoftJetSQL语言与标准SQL语言有若干的差别。MicrosoftJetSQL语言所支持的SQL数据类型CHAR(n)orTEXT(n)MEMO文本型备注型INTorINTEGER数字（长整型）介于2147,483,648至U2,147,483,647的长整型数SMALLINTorSHORT数字（短整型）介

7、于T2,768至U32,767的短整型数BYTEREALorSINGLEFLOATorDOUBLEDATEorTIMECURRENCYorMONEYCOUNTER(n)YESNOD若字段名中有空格或除了汉字外的非字母性字符，来，如FirstName数字（字节）介于0到255的整型数数字（单精度）默认有四位小数数字（双精度）日期/时间型货币型自动编号型（从整数n起）是/台型则应用一对方括号将字段名括起D数据完整性约束条件：它是用来定义数据表之间的关联关系和数据表内的字段应满足的条件的，当用户操作数据表时，DBMS会自动检查操作是否违背已定义的完整性约束条件。同一个完整性约束条件如果涉及到一个表中

8、的多个字段，则该约束条件必须定义在表级，否则，一般定义在字段级（即列级）。完整性约束条件主要有下列三种：实体完整性约束条件：NOTNULL不能为空，NULL的含义是无意义，不能与空字符或数值0等同UNIQUE唯一性，即表中各记录的该字段值各不相同PRIMARYKEY主键或称主码（同时，具有上两约束条件）参照完整性约束条件：REFERENCES表名（字段名）该字段所取值应来自指定表内的指定字段的值用户定义完整性约束条件：不能在Access的Sql中使用，只能在表的设计视图中的字段有效性规则中输入ISNULLNOTBetween表达式And表达式如：Between0And100可取0至U100之间

9、的数NOTIn（表达式列表）如：In（法律，'经济法,刑事司法）仅可取表达式列表中所指定的有限个值由此可见，（1）创建基表的Sql命令格式为：CREATETABLE表名（字段名数据类型字段级完整性约束条件,字段名数据类型字段级完整性约束条件,表级完整性约束条件）类似地，我们可以根据读者（借书证号字符型长度为5非空每位读者的借书证号均不相同姓名字符型长度为4性别字符型长度为1部门字符型长度为5职称字符型长度为5)建立读者数据表。请同学们自己完成O那么对于流通数据表流通（总编号字符型长度为6主键,且来自于图书实体借书证号字符型长度为5非空，且来自于读者实体借阅日期日期型）还是请同学们自己完

10、成。注意：由于流通表的建立要参照图书表与读者表，所以必须待图书表与读者表建立后方可建立流通表。同理，必须在主表中有记录后，才能在相关表中输入记录。我们现在在图书表中开始输入记录：INSERTINTO图书VALUES（100001','ww001','橘子红了','郑重王要','人民文学出版社',31.8）INSERTINTO图书（总编号，分类号，书名，单价）VALUES（'100002','ww002','追忆似水年华（上）,68）可见，插入一条具体录的Sql语句为：INSERTIN

11、TO表名（字段名列表）VALUES（字段值列表）当插入的记录中有部分字段值不确定时，可以仅输入部分字段值，但必须指明相应的字段名，而未指明的字段值为NULL。故上述第二条记录插入命令等价于INSERTINTO图书VALUES（'100002','ww002','追忆似水年华（上）',NULL,NULL,68）请再输入以下三条命令INSERTINTO图书VALUES（'100002','ww003','追忆似水年华（下）',NULL,NULL,68）INSERTINTO图书VALUES（NULL,&#

12、39;ww003','追忆似水年华（下）',NULL,NULL,68）INSERTINTO流通VALUES（'100002','90002',#02/14/98#）看看能不能执行上述三条命令，若不能，请说明原因。通过上述对创建表命令和插入记录命令的学习，我们可以大致了解到Sql命令的基本格式为：命令动词子句列表其中命令动词是指明该命令的功能，子句可以有多种，如：标识子句TABLE表名、INDEX表名、VIEW视图名目标子句INTO表名字段（值）列表子句字段名列表|VALUES（字段值列表）完整性约束条件子句,以后，还将学到更多的子句。（2

13、）修改表结构增加字段：ALTERTABLE表名ADD字段名数据类型字段级完整性约束条件习,字段名数据类型字段级完整性约束条件,修改字段类型：ALTERTABLE表名ALTER字段名数据类型,字段名数据类型字段级完整性约束条件,删除字段：ALTERTABLE表名DROP字段名列表注意：在对数据表的结构进行修改前，必须关闭该表！请同学自己先在读者数据表中增加两个字段，字段名依次为联系电话和年龄，数据类型分别为长整型和短整型，并观察每个命令执行后的效果。再将联系电话字段的数据类型改为有8个字符的字符型，年龄字段的类型改为字节型数字。再删除联系电话字段（3）删除表DROPTABLE表名列表II .数据

14、更新前面，我们已经学会了在数据表中插入记录的命令，对数据的更新还包括修改数据，删除记录等操作。注意：若我们对已经同其它数据表建立了关联关系的数据表中的记录的关联字段值进行更新（修改或删除）时，为了保持数据的一致性（即相容性），系统一般会拒绝此操作。那么，若要坚持更新，又能保持保持数据的一致（即被关联数据表中的外码一一关联字段一一的值也会自动更新，则必须要预先在关系（即关联）窗口中，编辑关系具有级联更新和级联删除功能（参考7.3.1用ACCESS建立关系型数据库的步骤5）（1）修改表记录UPDATE表名SET字段名=表达式,字段名=表达式,WHERE条件可啊呀，不好。我在输入读者数据表时，不小心

15、将所有读者的姓名都输入反了，即应该是男的，却输入了女，而应该是女的，反到输入了男。我曾经先后用下面两条命令UPDATE读者SETT生另='女'WHERET生另='男'UPDATE读者SETT生另='男'WHERET生另='女命令倒没错，但，结果却不符合本意。请同学帮帮忙：再在读者数据表中输入每位读者的年龄，并添加下列三记录（不一定要用SQL语句）。99001、张三郎、null、null、null、6299002、黄阿三、null、null、null、6699002、黄海、null、null、null、66在读者表中将姓黄且单名的同志的年龄

16、减1岁UPDATE读者SET年龄=年龄一1WHERE姓名LIKE'黄？在读者表中将姓名中带有“三”字的同志的性别设为男UPDATE读者SET性另1='男'WHERE姓名LIKE'*三*'在读者表中将年龄为62或66的职工的部门字段值设为“退休”UPDATE读者SET部门='退休'WHERE年龄IN（62,66）通过上述练习，我们对Sql命令中的条件子句应该有了基本的理解条件子句格式为：WHERE条件表达式,其中条件表达式经运算后得到真（YES）或假（NO）而条件表达式常为：字段名条件运算符表达式条件运算符一般有比较运算符：=、V、=、=、

17、！=、NOTBETWEEN表达式1AND表达式2:判断字段值是否落在指定的范围（表达式1,表达式2）中。NOTIN（表达式列表）:判断字段值是否等于指定的若干个值之一。NOTLIKE可带有通配符的字符串表达式:判断字符型字段值中是否与指定的字符串相匹配，通配符有？表示一个非空字符、*表示0个或连续的任意多个字符。（例如，Like"Smith"）,或用通配符来指定前两个字符为Sm的字符串（例如，Like"Sm*"）。NOTISNULL:判断字段值是否为空。（2）删除表记录DELETEFROM表名WHERE条件那么，删除读者数据表中职称字段值为空的记录的Sq

18、l命令应该如何书写？III .数据查询数据查询是用户根据需要从数据库中提取所需的数据，这也是建立数据库的最主要目的之一，所以，数据查询是对数据库最为常规的操作。数据查询语句的一般格式为：SELECTALL|DISTINCT目标表达式表FROM基表名|视图名列表WHERE记录过滤条件表达式GROUPBY列名表HAVING组过滤条件表达式ORDERBY歹名ASC|DESC儿歹名ASC|DESC,对此，暂时我们先不作过多的解释，为了便于在操作中理解SELECT语句的使用，这里我们先建立了一个名为“选课管理.1mdb的数据库，且具中已侣卜列一午数据表：数据表S,结构为Sno（学号）文本型6个字符主键S

19、name（姓名）文本型4个字符不能为空值Ssex(性另1J)文本型1个字符Sage（年龄）字节型Sdept（专业）文本型10个字符数据表C,结构为Cno（课程号）文本型2个字符主键Cname（课程名）文本型8个字符非空且各不相同Cpno（先修课程号一）文本型2个字符参照本表中的CnoCtime（课时数）字节型Ccredit(学分)字节型数据表SC,结构为Sno（学号）文本型6个字符非空且参照S表中的SnoCno（课程号）文本型2个字符非空且参照C表中的CnoGrade（成绩）字节型其中Sno和Cno一起为主键其中，建立表C的命令为：CREATETABLEC(Cnochar(2)PrimaryK

20、ey,Cnamechar(8)notnullunique,Cpnochar(2)referencesC(Cno),Ctimebyte,Ccreditbyte)建立表SC的命令为：CREATETABLESC(Snotext(6)notnullreferencesS(Sno),Cnotext(2)notnullreferencesC(Cno),Gradebyte,PrimaryKey(Sno,Cno)注：当表中要用多个属性值组成主键时，必须在建表语句中单独成为一个子句，如：PrimaryKey(Sno,Cno),即它是数据表级的完整性条件！注：若数据表中有字段有对本表中另一字段的参照完整性约束条件

21、，则要注意记录输入的先后顺序，被参照的记录要先输入，参照其它记录的记录要后输入！且三个数据表中已有若干条记录表S为：SnoSnameSsexSageSdept99001张敏男20MA99002刘丰男21IS99003王翔男19CS99004陆逸女18MA99005李明女23CS表C为：CnoCnameCpnoCtimeCcredit2高等数学10866数据处理5437C语百67235数据结构77241数据库57243信息系统15434操作系统6724表SC为：SnoCnoGradeSnoCnoGrade9900128299001669990017949900265099002763990037

22、56990035519900317599004787990045949900438899004192现在开始数据查询，先在一个表中进行数据的查询单表查询1 .在S表中查询全体学生的全部属性命令为：SELECT*FROMS2 .在S表中查询全体学生的姓名、学号和年龄命令为：SELECTSname,Sno,SageFROMS3 .在S表中查询全体学生的姓名、性别和专业命令为：4 .在S表中查询全体学生的姓名和出生的年份命令为：SELECTSname,2005-SageFROMS或用命令：SELECTSname,2005-SageASBirhYearFROMS两者在效果上有何区别？5 .在C表中查询

23、每门课程的1个学分相当的课时数命令为：6 .在SC表中查询有学生选修的课程号命令为：SELECTCnoFROMSC若用命令：SELECTDISTINCTCnoFROMSC两者在效果上有何区别？从而理解单词DISTINCT的作用7 .在SC表中查询出参加选修的学生的学号(一位学生可能选修多门课程)。可用命令：8 .在S表中查询所有年龄不超过20岁的女生的姓名和专业命令为：SELECTSname,SdeptFROMSWhereSage<=20andSsex='女'9 .在C表中查询课时数大于90和小于60的课程名和课时数可用命令：SELECTCname,CtimeFROMCW

24、hereCtimeNOTBETWEEN60AND90还可用命令：SELECTCname,CtimeFROMCWhere10 .在SC表中查询选修了1号或5号或7号课程的学生的学号及选修的课程号。可用命令：SELECTSnoFROMSCWhereCnoin(1','5','7)还可用命令：11 .在SC表中查询1号或5号或7号课程均不选修的学生的学号可否用命令：SELECTSnoFROMSCWhereCnoNOTin(10,'5','7')12 .在S表中查询姓刘的学生的记录可用命令：SELECTFROMWhereSnamelike

25、刘*'13 .在S表中查询姓名的第二个字为明的学生的姓名和性别可用命令：SELECTFROMWhereSnamelike?明*'14 .在C表中查询课程名以“数据”开头的记录可用命令：15 .在C表中查询课程名中有“数”字的课程号、课程名和课时数可用命令：16 .在C表中查询课程名中第三、四个汉字为“系统”的课程号、课程名和课时数可用命令：17 .在C表中查询所有课程的记录，并以学分由少到多的升序排序可用命令：SELECT*FROMCORDERBYCcreditDESC注：查询所得的临时表在显示时，还可将临时表中的记录按某些属性的值排序，升序的关键字为ASC(默认值，即可省略)

26、，降序的关键字为DESC18 .在C表中查询课程名中有“数”字的课程名、课时数和学分，并按学分升序、若学分相同则按课时数降序排序可用命令：集函数与分组查询1 .在SC表中查询出选修的总人次、最高分、最低分和平均分。可用命令：SELECTCOUNT(*),MAX(Grade),MIN(Grade),AVG(Grade)FROMSC2 .查询出选修7号课程的总分、最高分、最低分和平均分。可用命令：3 .查询出选修各门课程的课程号、学生数、最高分、最低分和平均分。可用命令：SELECTCno,COUNT(*),MAX(Grade),MIN(Grade),AVG(Grade)FROMSCGROUPBY

27、Cno若查询的结果表达式或条件中有集函数(又称统计函数)，则一般要对记录按某个属性值进行分组，属性值相同的记录归并在同一个组中，属性值不同的记录在不同的组，分组后，查询操作的对象就不再是一条一条的记录，而是一个个的组了！对于组，可以取到属性值相同的字段(一般是分组依据字段)，而对于属性值不同的字段只能用集函数取到统计结果而再也无法取到具体一条记录的字段值！4 .查询出选修各门课程的课程号、学生数、最高分、最低分和平均分，并按课程号降序排列。可用命令：5 .查询出至少选修满3门课程的学生的学号。可用命令：6 .查询出每门选修课成绩均及格的学生的学号及平均成绩。可用命令：7 .查询出既有不及格成绩

28、又有及格成绩的学生的学号。可用命令：8 .查询出至少有2门课程不及格的学生的学号。可用命令：连接查询(1)什么时候需要用连接查询当查询的结果表达式，或查询的记录过滤条件中包含有来自两个或两个以上数据表的字段，这时，必须打开多个数据表，表与表之间必定会根据一定的连接条件进行连接，在内存中形成一个临时表(又称中间表)，以后的所有查询操作都是针对这个临时表进行的。(2)系统执行内连接的过程若同时打开表S和SC,且有连接条件S.Sno=SC.SNO,即命令中有源子句如下：FROMSINNERJOINSCONS.Sno=SC.SNO,则系统对两表的连接如下图SnoSnameSsexSageSdept99

29、001张敏男20MA99002刘丰男21IS99003王翔男19CS99004陆逸一女18MASnoCnoGrade9900128299001669990017949900265099002763990031759900355199004787990041929900438899004594S.SnoS.SnameS.SsexS.SageS.SdeptSC.SnoSC.CnoSC.Grade99001张敏男20MA9900128299001张敏男20MA9900166999001张敏男20MA9900179499002刘丰男21IS9900265099002刘丰男21IS99002763990

30、03王翔男19CS9900317599003王翔男19CS9900355199004陆逸女18MA9900478799004陆逸女18MA9900419299004陆逸女18MA9900438899004陆逸女18MA99004594(3)系统执行查询命令的过程若是多表查询，则首先根据连接条件进行连接形成一个中间表；若有记录的过滤条件，则先对记录进行过滤(又称筛选)；若查询的结果表达式或条件中有集函数(又称统计函数)，则一般要再对记录按某个属性值进行分组；若有组过滤条件，则对组进行过滤，只有满足组过滤条件的组才会在最后的查询目标中生成一条记录； 根据查询的目标表达式生成查询目标表中的一条条记录； 若有排序要求，