Access中的SQL语言.docx
- 文档编号:13240196
- 上传时间:2023-06-12
- 格式:DOCX
- 页数:18
- 大小:49.13KB
Access中的SQL语言.docx
《Access中的SQL语言.docx》由会员分享,可在线阅读,更多相关《Access中的SQL语言.docx(18页珍藏版)》请在冰点文库上搜索。
Access中的SQL语言
Access中的SQL语言
1.SQL概述
结构化查询语言(StructuredQueryLanguageSQL读成“sequel”)自1974年提出,1975年IBM公司在RDBMS(关系型DBMS)SyatemR上实现以来,由于它功能丰富、使用方便、简洁易学,倍受用户和计算机工业界的欢迎,经不断修改、扩充和完善,现已成为RDBMS的标准语言。
众多的RDBMS中实现了SQL语言,当然各数据库厂家在各自的RDBMS中的SQL语言与ISO颁布的标准SQL语言都或多或少有些差别。
SQL语言包括了对数据库的所有操作,主要可分为三个部分①数据定义语言(DDLDataDefinitionLanguage),用以定义数据库的逻辑结构,包括定义基本表、索引和视图。
②数据操纵语言(DMLDataManipulationLanguage),用以对数据的查询和更新,数据更新操作中包括插入、删除和修改三种操作。
③数据控制语言(DCLDataControlLanguage),用以对基本表和视图的授权即指定具体用户可使用哪些表或视图。
SQL语言的特点:
(1)语言风格统一:
尽管有不同功能的DDL、DML和DCL,但语言风格相同,再SQL语言既能独立地使用于联机交互方式,又能嵌入到高级程序设计语言如C、C++等语言中,而且在两种不同地使用方式下的SQL语句的语法基本一样;
(2)面向集合的操作方式:
因为RDB的结构是关系,即记录的集合,也是SQL语言的操作对象,所以操作速度比其他语言快;
(3)语言简洁,易学易用:
尽管SQL的功能很强,但它的核心语句只有九条(CREATE、ALTER、DROP、SELECT、INSERT、DELETE、UPDATE、GRANT和REVOKE),加之它的结构化特性,非常便于学习和使用。
2.在Access中使用SQL语句
SQL语句的输入:
打开一个已有的Access数据库,在“对象”栏中选中“查询”,在“列表框”内选定“在设计视图中创建查询”命令,双击之,或按“设计”按钮,系统将显示一个查询窗口和一个“显示表”对话框,关闭“显示表”对话框,在不关闭查询窗口的情况下,①Access窗口的菜单栏上才会出现查询菜单,该查询菜单下有“SQL特定查询”子菜单、②视图菜单中会出现“SQL视图”命令、③同时原来显示在菜单栏下的数据库工具栏自动被切换为“查询设计”工具栏,其中的左上角会有一个视图切换按钮。
无论用上述三种方法的哪一种,均可在Access窗口中显示供输入SQL语句的“查询”窗口。
注意:
①在该窗口中只能输入一条SQL语句!
但可分行输入,系统会把分号“;”作为命令的结束标志,但一般在输入命令时,不必人为地输入分号。
当需要分行输入时,不能把Sql语言的关键字或字段名分在不同行。
②Sql语句中所有的标点符号和运算符号均为ASCII字符!
③每两个单词之间至少要有一个空格或有必要的逗号!
SQL语句的保存:
关闭“SQL语句输入”窗口,命名为一个查询
SQL语句的执行:
在“SQL语句输入”窗口中输入一个完整的SQL语句后,按“查询设计工具栏”中的运行按钮“!
”,或在数据库窗口中双击已被保存的相应查询。
I.数据表定义语句
前面我们是使用Access窗口中菜单命令的方式建立数据表的。
现在,我们开始学习用SQL语言来创建数据表。
首先打开图书管理数据库文件“图书管理.mdb”,且按上述方法打开输入SQL语句的“查询”窗口。
输入Sql语句:
CREATETABLE图书(总编号CHAR(6)PRIMARYKEY,分类号CHAR(6)NOTNULL,
书名CHAR(20),作者CHAR(15),出版社CHAR(10),单价SINGLE)
运行该Sql语句后,在数据库窗口中打开数据表图书的设计视图,可核对一下用Sql语句和用窗口菜单命令建表的区别。
说明:
在MicrosoftAccess2000中的Sql语言是通过MicrosoftJet数据库引擎来支持Sql语言的,而MicrosoftJetSQL语言与标准SQL语言有若干的差别。
1MicrosoftJetSQL语言所支持的SQL数据类型
ØCHAR(n)orTEXT(n)文本型
ØMEMO备注型
ØINTorINTEGER数字(长整型)介于–2,147,483,648到2,147,483,647的长整型数
ØSMALLINTorSHORT数字(短整型)介于–32,768到32,767的短整型数
ØBYTE数字(字节)介于0到255的整型数
ØREALorSINGLE数字(单精度)默认有四位小数
ØFLOATorDOUBLE数字(双精度)
ØDATEorTIME日期/时间型
ØCURRENCYorMONEY货币型
ØCOUNTER(n)自动编号型(从整数n起)
ØYESNO是/否型
2若字段名中有空格或除了汉字外的非字母性字符,则应用一对方括号[]将字段名括起来,如[FirstName]
3数据完整性约束条件:
它是用来定义数据表之间的关联关系和数据表内的字段应满足的条件的,当用户操作数据表时,DBMS会自动检查操作是否违背已定义的完整性约束条件。
同一个完整性约束条件如果涉及到一个表中的多个字段,则该约束条件必须定义在表级,否则,一般定义在字段级(即列级)。
完整性约束条件主要有下列三种:
实体完整性约束条件:
NOTNULL不能为空,NULL的含义是无意义,不能与空字符或数值0等同
UNIQUE唯一性,即表中各记录的该字段值各不相同
PRIMARYKEY主键或称主码(同时,具有上两约束条件)
参照完整性约束条件:
REFERENCES<表名>(<字段名>)该字段所取值应来自指定表内的指定字段的值
用户定义完整性约束条件:
不能在Access的Sql中使用,只能在表的设计视图中的字段有效性规则中输入
ISNULL
[NOT]Between<表达式>And<表达式>
如:
Between0And100可取0到100之间的数
[NOT]In(<表达式列表>)
如:
In(’法律’,’经济法’,’刑事司法’)仅可取表达式列表中所指定的有限个值
由此可见,
(1)创建基表的Sql命令格式为:
CREATETABLE<表名>(<字段名><数据类型>[<字段级完整性约束条件>]
[,<字段名><数据类型>[<字段级完整性约束条件>]]
…………
[,<表级完整性约束条件>]
)
类似地,我们可以根据
读者(借书证号字符型长度为5非空每位读者的借书证号均不相同
姓名字符型长度为4
性别字符型长度为1
部门字符型长度为5
职称字符型长度为5)
建立读者数据表。
请同学们自己完成。
CREATETABLE读者(借书证号TEXT(5)PRIMARYKEY,姓名TEXT(4),性别TEXT
(1),
部门TEXT(5),职称TEXT(5))
那么对于流通数据表
流通(总编号字符型长度为6主键,且来自于图书实体
借书证号字符型长度为5非空,且来自于读者实体
借阅日期日期型)
还是请同学们自己完成。
CREATETABLE流通(总编号TEXT(6)PRIMARYKEYREFERENCES图书(总编号),
借书证号TEXT(5)NOTNULLREFERENCES读者(借书证号),
借阅日期DATE)
注意:
由于流通表的建立要参照图书表与读者表,所以必须待图书表与读者表建立后方可建立流通表。
同理,必须在主表中有记录后,才能在相关表中输入记录。
我们现在在图书表中开始输入记录:
INSERTINTO图书VALUES(‘100001’,’ww001’,’橘子红了’,’郑重王要’,’人民文学出版社’,31.8)
INSERTINTO图书(总编号,分类号,书名,单价)VALUES('100002','ww002','追忆似水年华(上)',68)
可见,插入一条具体录的Sql语句为:
INSERTINTO<表名>[(<字段名列表>)]VALUES(<字段值列表>)
当插入的记录中有部分字段值不确定时,可以仅输入部分字段值,但必须指明相应的字段名,而未指明的字段值为NULL。
故上述第二条记录插入命令等价于
INSERTINTO图书VALUES('100002','ww002','追忆似水年华(上)',NULL,NULL,68)
请再输入以下三条命令
INSERTINTO图书VALUES('100002','ww003','追忆似水年华(下)',NULL,NULL,68)
INSERTINTO图书VALUES(NULL,'ww003','追忆似水年华(下)',NULL,NULL,68)
INSERTINTO流通VALUES('100002','90002',#02/14/98#)
看看能不能执行上述三条命令,若不能,请说明原因。
通过上述对创建表命令和插入记录命令的学习,我们可以大致了解到
Sql命令的基本格式为:
<命令动词>[<子句列表>]
其中<命令动词>是指明该命令的功能,子句可以有多种,
如:
标识子句TABLE<表名>、INDEX<表名>、VIEW<视图名>
目标子句INTO<表名>
字段(值)列表子句<字段名列表>|VALUES(<字段值列表>)
完整性约束条件子句
…………
以后,还将学到更多的子句。
(2)修改表结构
增加字段:
ALTERTABLE<表名>ADD<字段名><数据类型>[<字段级完整性约束条件>][,<字段名><数据类型>[<字段级完整性约束条件>]……
修改字段类型:
ALTERTABLE<表名>ALTER<字段名><数据类型>[,<字段名><数据类型>[<字段级完整性约束条件>]……
删除字段:
ALTERTABLE<表名>DROP<字段名列表>
注意:
在对数据表的结构进行修改前,必须关闭该表!
请同学自己先在读者数据表中增加两个字段,字段名依次为联系电话和年龄,数据类型分别为长整型和短整型,并观察每个命令执行后的效果。
ALTERTABLE读者ADD联系电话INT,年龄SHORT
再将联系电话字段的数据类型改为有8个字符的字符型,年龄字段的类型改为字节型数字。
ALTERTABLE读者ALTER联系电话CHAR(8),年龄BYTE
再删除联系电话字段
ALTERTABLE读者DROP联系电话,住址
(3)删除表
DROPTABLE<表名列表>
II.数据更新
前面,我们已经学会了在数据表中插入记录的命令,对数据的更新还包括修改数据,删除记录等操作。
注意:
若我们对已经同其它数据表建立了关联关系的数据表中的记录的关联字段值进行更新(修改或删除)时,为了保持数据的一致性(即相容性),系统一般会拒绝此操作。
那么,若要坚持更新,又能保持保持数据的一致(即被关联数据表中的外码——关联字段——的值也会自动更新,则必须要预先在关系(即关联)窗口中,编辑关系具有级联更新和级联删除功能(参考7.3.1用ACCESS建立关系型数据库的步骤5)
(1)修改表记录
UPDATE<表名>SET<字段名>=<表达式>[,<字段名>=<表达式>……][WHERE<条件>]
啊呀,不好。
我在输入读者数据表时,不小心将所有读者的姓名都输入反了,即应该是男的,却输入了女,而应该是女的,反到输入了男。
我曾经先后用下面两条命令
UPDATE读者SET性别=’女’WHERE性别=’男’
UPDATE读者SET性别=’男’WHERE性别=’女’
命令倒没错,但,结果却不符合本意。
请同学帮帮忙:
UPDATE读者SET性别=NULLWHERE性别=’男’
UPDATE读者SET性别=’男’WHERE性别=’女’
UPDATE读者SET性别=’女’WHERE性别ISNULL
再在读者数据表中输入每位读者的年龄,并添加下列三记录(不一定要用SQL语句)。
99001、张三郎、null、null、null、62
99002、黄阿三、null、null、null、66
99002、黄海、null、null、null、66
在读者表中将姓黄且单名的同志的年龄减1岁
UPDATE读者SET年龄=年龄-1WHERE姓名LIKE’黄?
’
在读者表中将姓名中带有“三”字的同志的性别设为男
UPDATE读者SET性别=’男’WHERE姓名LIKE’*三*’
在读者表中将年龄为62或66的职工的部门字段值设为“退休”
UPDATE读者SET部门=’退休’WHERE年龄IN(62,66)
通过上述练习,我们对Sql命令中的条件子句应该有了基本的理解
条件子句格式为:
WHERE<条件表达式>,其中条件表达式经运算后得到真(YES)或假(NO)
而条件表达式常为:
<字段名><条件运算符><表达式>
条件运算符一般有①比较运算符:
=、>、<、>=、<=、!
=、<>
②[NOT]BETWEEN<表达式1>AND<表达式2>:
判断字段值是否落在指定的范围(<表达式1>,<表达式2>)中。
③[NOT]IN(<表达式列表>):
判断字段值是否等于指定的若干个值之一。
④[NOT]LIKE<可带有通配符的字符串表达式>:
判断字符型字段值中是否与指定的字符串相匹配,通配符有?
表示一个非空字符、*表示0个或连续的任意多个字符。
(例如,Like"Smith"),或用通配符来指定前两个字符为Sm的字符串(例如,Like"Sm*")。
⑤[NOT]ISNULL:
判断字段值是否为空。
(2)删除表记录
DELETEFROM<表名>[WHERE<条件>]
那么,删除读者数据表中职称字段值为空的记录的Sql命令应该如何书写?
DELETEFROM读者WHERE职称ISNULL
III.数据查询
数据查询是用户根据需要从数据库中提取所需的数据,这也是建立数据库的最主要目的之一,所以,数据查询是对数据库最为常规的操作。
数据查询语句的一般格式为:
SELECT[ALL|DISTINCT]<目标表达式表>
FROM<基表名|视图名列表>
[WHERE<记录过滤条件表达式>]
[GROUPBY<列名表>]
[HAVING<组过滤条件表达式>]
[ORDERBY<列名>[ASC|DESC]][,<列名>[ASC|DESC]]……]
对此,暂时我们先不作过多的解释,为了便于在操作中理解SELECT语句的使用,这里我们先建立了一个名为“选课管理.mdb”的数据库,且其中已有下列三个数据表:
数据表S,结构为Sno(学号)文本型6个字符主键
Sname(姓名)文本型4个字符不能为空值
Ssex(性别)文本型1个字符
Sage(年龄)字节型
Sdept(专业)文本型10个字符
数据表C,结构为Cno(课程号)文本型2个字符主键
Cname(课程名)文本型8个字符非空且各不相同
Cpno(先修课程号)文本型2个字符参照本表中的Cno
Ctime(课时数)字节型
Ccredit(学分)字节型
数据表SC,结构为Sno(学号)文本型6个字符非空且参照S表中的Sno
Cno(课程号)文本型2个字符非空且参照C表中的Cno
Grade(成绩)字节型
其中Sno和Cno一起为主键
其中,建立表C的命令为:
CREATETABLEC(Cnochar
(2)PrimaryKey,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),即它是数据表级的完整性条件!
注②:
若数据表中有字段有对本表中另一字段的参照完整性约束条件,则要注意记录输入的先后顺序,被参照的记录要先输入,参照其它记录的记录要后输入!
且三个数据表中已有若干条记录
表S为:
SnoSnameSsexSageSdept
99001张敏男20MA
99002刘丰男21IS
99003王翔男19CS
99004陆逸女18MA
99005李明女23CS
表C为:
CnoCnameCpnoCtimeCcredit
2高等数学1086
6数据处理543
7C语言6723
5数据结构7724
1数据库5724
3信息系统1543
4操作系统6724
表SC为:
SnoCnoGradeSnoCnoGrade
9900128299001669
9900179499002650
9900276399003756
9900355199003175
9900478799004594
9900438899004192
现在开始数据查询,先在一个表中进行数据的查询
●单表查询
1.在S表中查询全体学生的全部属性
命令为:
SELECT*FROMS
2.在S表中查询全体学生的姓名、学号和年龄
命令为:
SELECTSname,Sno,SageFROMS
3.在S表中查询全体学生的姓名、性别和专业
命令为:
4.在S表中查询全体学生的姓名和出生的年份
命令为:
SELECTSname,2005-SageFROMS
或用命令:
SELECTSname,2005-SageAS[BirhYear]FROMS
两者在效果上有何区别?
5.在C表中查询每门课程的1个学分相当的课时数
命令为:
6.在SC表中查询有学生选修的课程号
命令为:
SELECTCnoFROMSC
若用命令:
SELECTDISTINCTCnoFROMSC
两者在效果上有何区别?
从而理解单词DISTINCT的作用
7.在SC表中查询出参加选修的学生的学号(一位学生可能选修多门课程)。
可用命令:
8.在S表中查询所有年龄不超过20岁的女生的姓名和专业
命令为:
SELECTSname,SdeptFROMSWhereSage<=20andSsex=’女’
9.在C表中查询课时数大于90和小于60的课程名和课时数
可用命令:
SELECTCname,CtimeFROMCWhereCtimeNOTBETWEEN60AND90
还可用命令:
SELECTCname,CtimeFROMCWhere
10.在SC表中查询选修了1号或5号或7号课程的学生的学号及选修的课程号。
可用命令:
SELECTSnoFROMSCWhereCnoin(‘1’,’5’,’7’)
还可用命令:
11.在SC表中查询1号或5号或7号课程均不选修的学生的学号
可否用命令:
SELECTSnoFROMSCWhereCnoNOTin(‘1’,’5’,’7’)
12.在S表中查询姓刘的学生的记录
可用命令:
SELECTFROMWhereSnamelike‘刘*’
13.在S表中查询姓名的第二个字为‘明’的学生的姓名和性别
可用命令:
SELECTFROMWhereSnamelike‘?
明*’
14.在C表中查询课程名以“数据”开头的记录
可用命令:
15.在C表中查询课程名中有“数”字的课程号、课程名和课时数
可用命令:
16.在C表中查询课程名中第三、四个汉字为“系统”的课程号、课程名和课时数
可用命令:
17.在C表中查询所有课程的记录,并以学分由少到多的升序排序
可用命令:
SELECT*FROMCORDERBYCcreditDESC
注:
查询所得的临时表在显示时,还可将临时表中的记录按某些属性的值排序,升序的关键字为ASC(默认值,即可省略),降序的关键字为DESC
18.在C表中查询课程名中有“数”字的课程名、课时数和学分,并按学分升序、若学分相同则按课时数降序排序
可用命令:
●集函数与分组查询
1.在SC表中查询出选修的总人次、最高分、最低分和平均分。
可用命令:
SELECTCOUNT(*),MAX(Grade),MIN(Grade),AVG(Grade)FROMSC
2.查询出选修7号课程的总分、最高分、最低分和平均分。
可用命令:
3.查询出选修各门课程的课程号、学生数、最高分、最低分和平均分。
可用命令:
SELECTCno,COUNT(*),MAX(Grade),MIN(Grade),AVG(Grade)FROMSCGROUPBYCno
若查询的结果表达式或条件中有集函数(又称统计函数),则一般要对记录按某个属性值进行分组,属性值相同的记录归并在同一个组中,属性值不同的记录在不同的组,分组后,查询操作的对象就不再是一条一条的记录,而是一个个的组了!
对于组,可以取到属性值相同的字段(一般是分组依据字段),而对于属性值不同的字段只能用集函数取到统计结果而再也无法取到具体一条记录的字段值!
4.查询出选修各门课程的课程号、学生数、最高分、最低分和平均分,并按课程号降序排列。
可用命令:
5.查询出至少选修满3门课程的学生的学号。
可用命令:
6.查询出每门选修课成绩均及格的学生的学号及平均成绩。
可用命令:
7.查询出既有不及格成绩又有及格成绩的学生的学号。
可用命令:
8.查询出至少有2门课程不及格的学生的学号。
可用命令:
●连接查询
(1)什么时候需要用连接查询
当查询的结果表达式,或查询的记录过滤条件中包含有来自两个或两个以上数据表的字段,这时,必须打开多个数据表,表与表之间必定会根据一定的连接条件进行连接,在内存中形成一个临时表(又称中间表),以后的所有查询操作都是针对这个临时表进行的。
(2)系统执行内连接的过程
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Access 中的 SQL 语言