accesssql结构化查询语言

1、第4章结构化查询语言SQL重点概念：SQL的特点SQL的数据定义SQL的数据查询SQL的数据操纵SQL的数据控制14.1 SQL概述一、SQL语言： SQL（Structured Query Language结构化查询语言）是一种介于关系代数与关系演算之间的语言，其功能包括查询、操纵、定义和控制4个方面。是一种通用的、功能强大的关系数据库语言。目前已经成为关系数据库的标准语言。大多数数据库均用SQL作为共同数据存取语言和标准接口，从而使不同数据库系统之间的相互操作有了共同的基础。24.1 SQL概述 SQL语言结构简洁，功能强大，简单易学，所以自从IBM公司1981年推出以来，SQL语言得到了

2、广泛的应用。如今无论是像Oracle、Sybase、Informix、SQL server这些大型的数据库管理系统，还是像Visual Foxpro、PowerBuilder、Access等这些PC上常用的数据库开发系统，都支持SQL语言作为查询语言。 34.1 SQL概述 二、 SQL的特点 SQL语言类似于英语的自然语言，简洁易用 SQL语言是一种非过程语言，即用户只要提出“做什么”即可，不必管具体操作过程，也不必了解数据的存取路径，只要指明所需数据即可。 SQL语言是一种面向集合的语言，每个命令的操作对象是一个或多个关系，结果也是一个关系。SQL语言既是自含式语言，又是嵌入式语言。可独立

3、使用，也可嵌入到宿主语言中。自含式语言可以独立使用交互命令，适用于终端用户、应用程序员和DBA。嵌入式语言使其嵌入在高级语言中使用，供应用程序员开发应用程序。SQL语言具有:数据查询、数据定义、数据操纵、数据控制四种语言一体化的功能。4三、SQL语言两个基本概念：基本表和视图。基本表（BASE TABLE）：是独立存在的表，不是由其它的表导出的表。一个关系对应一个基本表，一个或多个基本表对应一个存储文件。视图（VIEW）：是一个虚拟的表，是从一个或几个基本表导出的表。它本身不独立存在于数据库中，数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中。当基本表中的数据

4、发生变化时，从视图中查询出来的数据也随之改变。4.1 SQL概述5SQL语言支持关系数据库的三级模式结构：外模式：视图和部分基本表模式：数据库中所有的基本表内模式：存储文件SQL视图1视图2基本表1基本表2基本表3基本表4存储文件1存储文件2外模式模式内模式4.1 SQL概述6四、SQL基本语句：SQL基本功能动词数据定义CREATE，DROP，ALTER数据查询SELECT数据操纵INSERT，UPDATE，DELETE数据控制GRANT，REVOTE注意：ACCESS不直接支持SQL的数据定义和视图，也不直接支持SQL的授权控制。这些操作通过VBA代码利用DAO（数据访问对象）进行。4.1

5、 SQL概述7数据定义语言（Data Definition Language）Create、Drop、Alter定义一组关系（基本表）、说明各关系的信息各关系的模式各属性的值域完整性约束索引安全性和权限4.2 SQL数据定义8一、创建与删除数据库1、数据库的创建创建数据库的语句格式为：CREATE DATABASE AUTHORIZATION ；例如，CREATE DATABASE ST-COURSE AUTHORIZATION 王非；4.2 SQL数据定义92、数据库的删除DROP DATABASE CASCADE/RESTRICT；删除的方式有两种：CASCADE（连锁式）方式：执行DRO

6、P时，将SQL模式及下属的基本表、视图、索引等元素全部删除。RESTRICT（约束式）方式：执行DROP时，仅当SQL模式中没有任何下属元素时，才能删除，否则拒绝执行DROP命令。4.2 SQL数据定义10二、基本表1、创建基本表CREATE TABLE (列级完整性约束条件，列级完整性约束条件，)； 是基本表的名字，可由一个或多个属性组成。建表的同时可以定义与该表有关的完整性约束条件。如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则即可以定义在列级，也可以定义在表级。4.2 SQL数据定义11一般都支持的数据类型：CHAR（n）：字符串，长度为n个西文字符。INTEGER或

7、INT：全字长整数，范围从负10位整数到正ll位整数。 SMALLINT：半字长整数，范围从一99999到999999。 DECIMAL（x，y）：十进制数，包括小数点及符号位共x位，其中y位小数。 FLOAT（x，y）：双字长浮点数，范围从o1e一307到o9e十308 以指数形式表DATE：日期型，格式为YYYY-MM-DD。4.2 SQL数据定义124.2 SQL数据定义例1 建立一个“借出信息”表，它由“资产编号”、“借用人”、“出借人”、“批复人”、“出借日期”、“借用理由”6个属性组成，其中“资产编号”属性不能为空，并且其值是唯一的。CREATE TABLE 借出信息(资产编号 C

8、HAR(5) NOT NULL UNIQUE，借用人 CHAR（8），出借人 CHAR（2），批复人 INT，出借日期 DATE，借用理由 CHAR（15）；134.2 SQL数据定义2、删除基本表 当某个基本表不再使用时，可用DROP TABLE命令进行删除，其格式为：DROP TABEL ；例2 删除“借出信息”表。 DROP TABLE 借出信息； 基本表一旦删除，表中的数据和在此表上建立的索引都将自动被删除掉，而建立在此表上的视图虽然仍然保留，但已无法引用。因此执行删除操作一定要格外小心。144.2 SQL数据定义3、修改基本表 随着应用环境和应用需求的变化，有时需要修改已建立好的基本

9、表，包括增加新列、增加新的完整性约束条件、修改原有的列定义或删除已有的完整性约束条件等。修改基本表的命令为ALTER TABLE，其格式为： ALTER TABLE ADD完整性约束 DROP MODIFY；154.2 SQL数据定义例3 向借出信息表增加拟还日期列，数据类型为日期型。ALTER TABLE借出信息ADD 拟还日期 DATE；例4 将借出信息表中资产编号数据类型改为半字长整数。ALTER TABLE借出信息MODIFY 资产编号 SMALLINT；例5 删除借出信息表中资产编号必须取值唯一的约束。ALTER TABLE借出信息DROP UNIQUE（资产编号）；164.2 SQ

10、L数据定义三、索引 建立索引是加快表的查询速度的有效手段。当我们需要在一本书中查找某些信息时，往往首先通过目录找到所需信息的对应页码，然后再从该页码中找出所要的信息，这种做法比直接翻阅书的内容速度要快。如果把数据库表比作一本书，那么表的索引就是这本书的目录，可见通过索引可以大大加快表的查询。 在基本表上可以建立一个或多个索引，以提供多种存取路径，加快查找速度。一般来说，建立与删除索引由数据库管理员（DBA）或表的属主（即建立表的人）负责完成。系统在存取数据时会自动选择合适的索引作为存取路径，用户不必也不能选择索引。174.2 SQL数据定义 1建立索引建立索引使用CREATE INDEX命令，

11、格式为：CREATEUNIQUECLUSTERINDEXON（， ） 其中指定要建索引的基本表的名字。索引可以建立在该表的一列或多列上，各列名之间用逗号分隔。每个后面还可以用指定索引值的排列次序，包括ASC（升序）和DESC（降序）两种，缺省值为ASC。 UNIQUE表示索引中的每一个索引值只对应唯一的数据记录。 CLUSTERE表示要建立的索引是聚簇索引。184.2 SQL数据定义 例6 为“固定资产管理系统”数据库中的“资产信息”、“借出信息”、“系统用户”3个表建立索引。其中“资产信息”表按“资产编号”升序建立唯一索引，“借出信息”表按“资产编号”建立唯一索引，“系统用户”表按“用户ID

12、”升序建唯一索引。 CREATE UNIQUE INDEX 资产编号1 ON 资产信息（资产编号）； CREATE UNIQUE INDEX 资产编号2 ON 借出信息（资产编号）； CREATE UNIQUE INDEX 用户ID ON 系统用户（用户ID ASC）；194.2 SQL数据定义2删除索引 索引一经建立，就由系统使用和维护它，不需要用户干预。建立索引是为了减少查询操作的时间，但如果数据增删改频繁，系统就会花费许多时间来维护索引。这时，可以删除一些不必要的索引。删除索引使用DROP INDEX命令，其格式为：DROP INDEX ；例7 删除“借出信息”表中的“资产编号2”索引。

13、 DROP INDEX 资产编号2； 注：删除索引时，系统会同时从数据字典中删去有关该索引的描述。 204.2 SQL数据定义四、视图 视图是从一个基本表或几个基本表或视图导出的表，它与基本表不同，是一个虚表。换句话说，数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。基本表中的数据发生变化，从视图中查询出的数据也就随之改变了。从这个意义上讲，视图就像一个窗口，透过它可以看到数据库中自己感兴趣的数据及其变化。 视图一经定义，就可以和基本表一样被查询、被删除，也可以在一个视图之上再定义新的视图，但对视图的更新（增、删、改）操作则有一定的限制。 214.2 SQL数

14、据定义 1定义视图的命令格式为： CREATE VIEW ， AS 子查询 WITH CHECK OPTION； 例1 建立管理工程系的读者视图 CREATE VIEW GLREADER AS SELECT 姓名，性别，职称，地址 FROM 读者 WHERE 单位=管理工程系 WITH CHECK OPTION；224.2 SQL数据定义例2 建立各个单位当前借阅图书情况的简单统计视图，名称为DW-READER。 CREATE VIEW DW-READER（单位，借阅人次） AS SELECT 单位，COUNT（总编号） FROM 借阅，读者 WHERE 读者.借书证号=借阅.借书证号234.

15、2 SQL数据定义 2删除视图 该语句的格式为：DROP VIEW 视图名例3 删除视图DWREADER DROP VIEW DWREADER 视图建好后，若导出此视图的基本表被删除了，该视图将失效，但一般不会被自动删除。 一个视图被删除后，由该视图导出的其它视图也将失效，用户应该使用DROP VIEW语句将它们一一删除。 244.2 SQL数据定义3视图的查询 视图定义后，用户就可以像对基本表进行查询一样对视图进行查询了。DBMS执行对视图的查询时，首先进行有效性检查，检查查询涉及的表、视图等是否在数据库中存在，如果存在，则从数据库字典中取出查询涉及的视图的定义，把定义中的子查询和用户对视图

16、的查询结合起来，转换成对基本表的查询，然后再执行这个经过修正的查询。 将对视图的查询转换为对基本表的查询的过程称为视图的消解。254.2 SQL数据定义例4 找出管理系的所有女职工姓名和地址信息。 SELECT 姓名,地址FROM GL-READER WHERE 性别=女； DBMS执行此查询时，将其与GL-READER视图中的子查询 SELECT 姓名，性别，职称，地址FROM 读者 WHERE 单位=管理工程系 结合起来，转换成对基本表借阅和读者的查询SELECT 姓名，地址FROM 读者WHERE 单位=管理工程系 AND性别=女264.2 SQL数据定义4更新视图 更新视图包括插入（I

17、NSERT）、删除（DELETE）和修改（UPDATE）三类操作。 由于视图是不实际存储数据的虚表，因此对视图的更新、最终要转换为对基本表的更新。 例5 将管理系的女职工的地址改为校内。 UPDATE GLREADER SET 地址=校内 WHERE 性别=女； 转换后的更新语句为 UPDATE读者 SET 地址=校内 WHERE 单位=管理工程系 AND性别=女；274.3 SQL数据操纵一、SELECT查询语句 数据库查询是数据库的核心操作，SQL语言提供了SELECT语句进行数据库的查询。 SELECT语句如何实现三种专门的关系运算：投影、选择、连接。284.3 SQL数据操纵SELEC

18、T的基本语法SELECT ALL/DISTINCT，目标列表表达式FROM，表名或视图名WHEREGROUP BYHAINGORDER BY ASC/DESC注意：表示在实际的语句中要采用所需内容进行代替 表示可以根据需要进行选择，也可以不选 表示必须选项294.3 SQL数据操纵SELECT说明 为了从海量数据中筛选出我们感兴趣的数据，我们必须有一定过程，这一过程描述如下：从哪些数据源中选择数据。由from后面跟随的表名指定。连接运算选择出满足哪些条件的纪录。由where后条件语句决定。选择运算显示数据中的哪些字段。由select后的字段列表决定，用*号表示数据中的所有字段，并且可对数据进行

19、汇总计算。投影运算是否要对数据分组，按什么字段分组。由group by后的字段列表决定。显示数据是否要排序，按哪些字段如何排序。由order by后的字段列表决定。ASC升序，DESC降序，默认为升序。304.3 SQL数据操纵 WHERE子句的条件表达式可以使用下列运算符： 比较运算符： =、=、BETWEEN 逻辑运算符AND、OR、NOT 集合元素包含运算符IN、NOT IN 存在量词EXISTS（SELECT子查询） 集合运算符 UNION（并）、MINUS（差）、INTERSECTION（交） 通配符LIKE_、LIKE %314.3 SQL数据操纵1简单查询例1 找出资产编号为00

20、3的名称及价格。SELECT 资产名称，购买价格FROM 资产信息WHERE 资产编号=003；例2 查看所有借出信息的全部情况。SELECT *FROM 借出信息；例3 列出借出信息中的所有拟还日期。SELECT DISTINCT 拟还日期FROM 借出信息；324.3 SQL数据操纵例4 查找资产信息表中所有资产状态为报废的产品的产品名称及购买价格，结果按单价降序排列。SELECT 资产名称，购买价格 FROM 资产信息WHERE 资产状态=报废 ORDER BY 购买价格 DESC；例5 查找购买价格在1000元和5000元之间的资产名称及编号，结果按编号升序、价格降序排列。SELECT

21、 资产编号，资产名称，购买价格FROM 资产信息WHERE 购买价格 BETWEEN 1000 AND 5000ORDER BY 资产编号ASC ，购买价格DESC ；334.3 SQL数据操纵例6 查找资产状态库中和出借中的所有资产名称及编号。SELECT 资产编号，资产名称，资产状态FROM 资产信息WHERE 资产状态 IN（在库中 ，出借中）；例7 查找资产编号以“TH”打头的所有名称及存放位置。SELECT 资产编号，存放位置FROM 资产信息WHERE 资产编号 LIKETH%；344.3 SQL数据操纵例8 检索借用人和出借人是同一个人的名单。SELECT X.借用人FROM 借

22、出信息X，借出信息YWHERE X. 借用人=Y.出借人 由于同一个关系在一个查询模块中出现两次，必须引入别名X、Y，并在SELECT和WHERE子句中用别名加以限定354.3 SQL数据操纵2联接查询 例9 查找所有借阅了图书的读者姓名及所在单位。SELECT DISTINCT 姓名，单位FROM 读者，借阅WHERE 读者.借书证号=借阅.借书证号；例10 找出李晶所借的所有图书的书名及借阅日期。SELECT 姓名，所借图书：，书名，借阅日期FROM 图书，借阅，读者WHERE 读者.借书证号=借阅.借书证号 AND 借阅.总编号=图书.总编号AND 姓名=李晶；364.3 SQL数据操纵

23、例11 查找价格在20元以上已借出的图书，结果按单价降序排列。SELECT *FROM 图书，借阅WHERE 图书.总编号=借阅.总编号 AND 单价=20ORDER BY 单价 DESC；374.3 SQL数据操纵3嵌套查询 例12 找出借阅“C语言程序设计”的读者姓名及所在单位。SELECT 姓名，单位FROM 读者，借阅，图书WHERE 读者.借书证号=借阅.借书证号 AND 借阅.总编号=图书.总编号 AND 书名=C语言程序设计；384.3 SQL数据操纵例13 找出读者的姓名、所在单位、他们与王鸣一在同一天借了书。SELECT 姓名， 单位，借阅日期FROM 读者，借阅WHERE

24、借阅.借书证号=读者.借书证号 AND 借阅日期 IN（SELECT 借书日期 FROM 借阅，读者 WHWRE 借阅.借书证号=借书证号 AND 姓名=王鸣一）394.3 SQL数据操纵例14 找出藏书中比高教出版社所有图书单价更高书籍。SELECT * FROM 图书 WHERE 单价ALL（SELECT DISTINCT单价 FROM 图书WHERE 出版单位=高等教育出版社）；例15 找出藏书中所有与数据库导论或数据库原理在同一个出版单位出版的书。SELECT * FROM 图书 WHERE 出版单位=ANY（SELECT 出版单位 FROM 图书WHERE 书名 IN （数据库导论，

25、数据库原理）；404.3 SQL数据操纵4使用库函数查询SQL的库函数有：计算函数 COUNT（*）计算元组的个数； COUNT对列的值计算个数；求和SUM对某一列的值求和；求平均值AVG对某一列的值求平均值；求最大值MAX找出一列值中的最大值；求最小值MIN找出一列值中的最小值。414.3 SQL数据操纵例17 求科学出版社图书的最高、最低和平均价格。SELECT 最高：，MAX（单价），最低：， MIN（单价），平均：，AVG（单价）FROM 图书 WHERE 出版单位=科学出版社；例18 求计算机科学系当前借阅了图书的读者人数。SELECT 借书人数：，COUNT（DISTINCT 借书

26、证号） FROM 借阅 WHERE 借书证号 IN（SELECT 借书证号 FROM 读者WHERE 单位=计算机科学系）；424.3 SQL数据操纵例19 用库函数查询例14的询问。找出藏书中比高等教育出版社的所有图书单价更高的书籍。SELECT * FROM 图书 WHERE 单价（SELECT MAX（单价） FROM 读者WHERE 单位=高等教育出版社）；例20 求出各个出版社图书的最高、最低和平均价格。SELECT 出版单位,MAX(单价),MIN（单价）,AVG（单价）FROM 图书 GROUP BY 出版单位；434.3 SQL数据操纵例21 分别找出各个单位当前借阅图书的读者

27、人次。SELECT 单位，借书人次：，COUNT（借书证号）FROM 借阅,读者WHERE 读者.借书证号=借阅.借书证号 GROUP BY 单位；例22 找出藏书中各个出版单位的册数、价值总额， 并按总价降序，总价相同者按册数降序排列。SELECT 出版单位,册数:,COUNT(*),总价:,SUM(单价)FROM 图书 GROUP BY 出版单位 ORDER BY SUM（单价），COUNT（*） DESC； 444.3 SQL数据操纵例23 找出当前至少借阅了5本图书的读者及所在单位。SELECT 姓名，单位 FROM 读者 WHERE 借书证号 IN （SELECT 借书证号 FROM

28、 借阅GROUP BY 借书证号 HAVINT COUNT（*）=5）;例24 分别找出借书人数超过10个人的单位及人数。SELECT 单位，借书人数：，COUNT（DISTINCT 借书证号）FROM 借阅，读者WHERE 读者.借书证号=借阅.借书证号GROUP BY 单位 HAVINT COUNT（DISTINCT 借书证号）10；454.3 SQL数据操纵例25 找出没有借阅任何图书的读者及所在单位。SELECT 姓名，单位FROM 读者WHERE NOT EXISTS （SELECT * FROM 借阅 WHERE 借阅.借书证号=读者.借书证号）；464.3 SQL数据操纵5集合运

29、算 例26 有一个校友通信录关系，包含姓名、职称和单位属性，相应的数据定义与读者关系一致。求校友与读者中具有教授、副教授职称人员的并集。SELECT 姓名，职称，单位FROM 读者WHERE 职称 IN （教授，副教授）UNIONSELECT 姓名，职称，单位FROM 校友 WHERE 职称 IN（教授，副教授）；474.3 SQL数据操纵二、插入 1插入单个元组 insert into （，,） values（，）例1 向图书（总编号，分类号，书名，作者，出版单位，单价）表中新加一个元组。 INSERT INTO 图书 VALUES（446943，TP31/138，计算机基础， 杨大华，高教

30、出版社，17.10）； 例2 向图书表中插入一个元组的部分属性。 INSERT INTO 图书（总编号，书名，单价） VALUES（，数据库原理及应用，16.50）； 484.3 SQL数据操纵2插入子查询结果INSERT INTO （，,） 子查询；例3 建立一个名为DWJS的各单位借阅图书情况统计基本表，每隔一段时间，向此基本表里追加一次数据。CREATE TABLE DWJS（单位 CHAR（20）， 借书人数 SMALLINT， 借书人次 SMALLINT）；INSERT INTO DWJS（单位，借书人数，借书人次） SELECT 单位，COUNT（DISTINCT 借书证号）， C

31、OUNT（总编号） FROM 借阅，读者 WHERE 读者.借书证号=借阅.借书证号GROUP BY 单位；494.3 SQL数据操纵 格式为： DELETE from where 例1 删除借书证号所借总编号为的借阅登记。 DELETE FROM 借阅 WHERE 借书证号= AND 总编号=；例2 删除借书证号以90开头的所有读者登记和借阅登记。 DELETEFROM 读者WHERE 借书证号=90%； DELETE FROM 借阅WHERE 借书证号=90%； 504.3 SQL数据操纵三、更新格式：update set =，=where ;例1 将总编号为的图书填上作者和出版单位。UPDATE 图书 SET 作者=王民，出版单位=希望WHERE 总编号=；例2 将所有图书的单价上调5%。UPDATE 图书 SET 单价=单价*1.05；例3 将书名中包含计算机的书分类号改为TP31/138。 UPDATE 图书 SET 分类号=TP31/138 WHERE 书名=%计算机；514.3 SQL数据操纵例4 把借书证号改为。UPDATE 读者SET 借书证号=WHERE 借书证号=； 为保持数据的一致性，借阅关系也要更新。UPDATE