空间数据库技术应用电子教材-结构化查询语言SQL.docx

结构化查询语言SQLSQL （Structured Query Language, SQL）是一种关系数据库语言，介于关系代数和关系 演算之间，其主要功能包括数据定义、数据操纵、数据控制等，其中数据操纵又分为数据查 询和数据更新。SQL功能强大、简单易学，已成为数据库领域的国际标准。1. SQL的特点SQL主要有以下特点：（1）综合统一。SQL在语言风格上统一且功能强大，能够完成各种数据库操作，如典型 的 SELECTFROMWHERE 查询块。 高度非过程化。用户无需了解存取路径，存取路径的选择及SQL语句的操作过程 由系统自动完成。（3）面向集合的操作方式。SQL采用集合操作方式，不仅操作对象、查找结果可以是记 录的集合，且插入、修改、删除、更新等操作的对象也可以是记录的集合。（4）同一种语法结构提供两种使用方式。SQL既是自含式语言，又是嵌入式语言。作为 自含式语言，可以独立地交互使用；作为嵌入式语言，主要是嵌入到其它高级语言中，供程 序员使用。（5）格式简单，易学易用。值得注意的是，SQL只提供对数据库的定义、操作等能力，不能完成屏幕控制、菜单管 理、报表生成等功能，不是一个应用程序开发语言。2. SQL的组成SQL可以对两种基本数据结构进行操作，即表和视图。视图是由不同数据库中满足一定 条件约束的数据组成，用户可以像基本表一样对其进行操作。视图呈现给用户的是数据的部 分内容，这样不但便于用户使用，而且可以提高数据的独立性，便于数据的平安保密。SQL 由数据定义语言（Data Definition Language, DDL）、数据操纵语言（Data Manipulation Language, DML）和数据控制语言（Data Control Language, DCL）组成。（1）数据定义语言。用于创立、修改、删除数据库中的各种对象，包括数据库、表、视 图、索引等。（2）数据操作语言。对已存在的数据库进行记录的插入、修改、更新、删除等操作，分 为数据查询和数据更新两大类。（3）数据控制语言。用于授予或收回访问数据库的某种特权，控制数据操作事务的发生 时间及效果，对数据库进行监视，包括对表和视图的授权、完整性约束的描述、并发控制、 事务控制等。3. 数据定义SQL的数据定义功能包括三局部：定义基本表、定义视图和定义索引。(1)定义基本表定义基本表即是创立一个基本表，对表名及表中所包含的字段、数据类型、大小、约束 等属性做出规定。不同的数据库系统支持的数据类型不同，但大同小异。以下给出Microsoft Access数据库管理系统中提供的常用数据类型。CHAR (n)、TEXT (n)：字符串型，长度为n个中文汉字或英文字母；SMALLINT、INT、REAL、NUMERIC：数字型，分别为短整型、整型、单精度型、双 精度型；DATE、DATETIME：日期/时间型；BIT：逻辑型，是/否。使用SQL定义基本表的语法格式如下：CREATE TABLE表名(列名 数据类型习大小列级完整性约束条件,<列名 >< 数据类型,大小H列级完整性约束条件,<表级完整性约束条件习)；例如，创立一个学生表和选课表(在已翻开的数据库中创立)。CREATE TABLE 学生表(学号 CHAR(12) Primary Key,姓 名 CHAR(8) NOT NULL,性别 CHAR(2),专业 CHAR(20), 出生日期DATE, 家庭地址CHAR(50);CREATE TABLE 选课表(学号 CHAR(12),课程号CHAR(6),课程名CHAR(20),成绩 SMALLINT,Constraint Group Primary Key(学号，课程号);Primary Key表示学生表中的“学号”字段为一主键；Constraint Group Primary Key (学号， 课程号)表示属性组(学号、课程号)是主键字段组。注意，主键只能设置一次，当表中有 两个字段需被同时定义为主键时，必须用Constraint命令。(2)修改基本表使用SQL修改基本表的语法格式如下：ALTER TABLE表名ADD(新列名数据类型大小列级完整性约束条件，n）DROP完整性约束名MODIFY（歹U名数据类型大小，n）;ADD：增加一个新列和该列的完整性约束条件；DROP：删除指定的完整性约束条件；MODIFY：修改原有列的定义。例如，以下三条语句分别表示往学生表中增加一个列名为“备注”，数据类型为字符串， 长度为100的新列；修改学生表中“家庭地址”歹！J,将其修改为字符串型，长度为60；删除 学生表中的“备注”歹U。ALTER TABLE 学生表 ADD 备注 CHAR （100）;ALTER TABLE 学生表 ALTER COLUMN 家庭地址 CHAR （60）;ALTER TABLE 学生表DROP备注；（3）删除基本表使用SQL删除基本表的语法格式如下：DROP TABLE表名;例如，删除学生表。DROP TABLE 学生表；（4）创立索引索引是对表中一个或多个字段的值进行排序，可以利用索引快速访问表中信息。为提高 数据搜索速度，可根据应用环境的需要为一个基本表建立假设干索引。通常，索引的建立和删 除由数据库管理员或创立表的人负责。使用SQL创立索引的语法格式如下：CREATE UNIQUE CLUSTER INDEX索引名ON 表名（列名次序儿列名次序）;UNIQUE：指该索引的每一个索引值只对应一条唯一的记录。CLUSTER：表示要创立的索引是聚簇索引。聚簇索引是指索引项的顺序与表中记录的物 理顺序一致，在一个基本表上只能建立一个聚簇索引。次序：指定索引是按ASC （升序）还是按DESC （降序）排列，默认为ASCo例如，为学生表建立一个按学号升序排列的索引，名为XSXH。CREATE INDEX XSXH ON 学生表（学号 ASC）;使用索引时需注意以下几个问题：1）改变表中的数据时，如增加或删除记录，索引将自动更新。2）索引建立后，当查询使用索引列时，系统将自动使用索引进行查询。3）可以为表建立任意多个索引，但索引越多，数据更新速度越慢。因此，对于经常被用 于查询的表而言，可以为其建立多个索引；而对于经常进行数据更新的表而言，应少建立索引，以提高速度。（5）删除索引创立索引的目的是为了提高搜索速度，但随着索引的增多，数据更新时系统会花费大量 的时间来维护索引。因此，应及时删除不必要的索引。使用SQL删除索引的语法格式如下：DROP INDEX索引名；例如，删除学生表中名为XSXH的索引。DROP INDEX XSXH ON 学生表；4.数据查询SQL数据查询的基本形式是SELECTFROMWHERE查询块，多个查询块可以嵌套。 SELECT命令是SQL最具特色的核心语句。（1） SELECT语句的格式SELECT语句的基本格式如下：SELECT ALL| DISTINCT目标列表达式,v目标列表达式FROM表名或视图名，表名或视图名WHERE v条件表达式习GROUP BY列名lHAVINGv条件表达式习ORDER BY v歹I名 2ASC|DESC;1）命令含义SELECT：根据SELECT子句指定的目标列表达式，选出表中满足条件的记录，结果形 成查询表。FROM：从FROM子句指定的基本表或视图中，根据WHERE子句的条件表达式查找出 满足条件的记录。GROUP BY：将结果按“列名1”的值进行分组，该属性列值相等的记录为一组；如果 GROUP BY子句带有短语HAVING,那么只有满足短语指定条件的分组才会输出。ORDER BY：将结果按“列名2”的值进行升序或降序排列。ALL：表示输出所有记录。DISTINCT：假设结果集中有相同记录，那么只输出一次。2）目标列表达式目标列表达式可以是“列名1,列名2,”的形式；如果FROM子句指定了多个表， 那么列名应该表示为“表名.列名”的形式。列表达式可以使用SQL提供的库函数，如：SUM（列名）：计算某一数值型列的值的总和；AVG（列名）：计算某一数值型列的值的平均值；MAX （列名）：计算某一数值型列的值的最大值；MIN （列名）：计算某一数值型列的值的最小值；COUNT （*）：统计记录条数；COUNT （列名）：统计一列值的个数。SELECT语句既可以完成简单的单表查询，也可以完成复杂的连接查询和嵌套查询。（2）单表查询1）查询所有学生的全部信息。SELECT *FROM学生表；该例如中不含WHERE条件表达式，说明查询所有学生的基本信息。2）查询所学专业为测绘地理信息技术的所有学生姓名、班级、出生日期和家庭地址。SELECT姓名，班级，出生日期，家庭地址FROM学生表WHERE专业名二'测绘地理信息技术'；3）查询学生表中各个学生的姓名、专业名和总学分。SELECT姓名，专业名,总学分FROM XS；4）查询学生表的学生总人数。SELECT COUNT（*）FROM学生表；5）查询年龄在1922岁之间的学生姓名、性别、专业名。SELECT姓名，性别，专业名FROM学生表WHERE 年龄 BETWEEN 19 AND 22;6）查询所有学号以2012开头的学生的详细信息。SELECT *FROM学生表WHERE 学号 LIKE '2012%，;注：Access中以“*”代替“”。7）查询缺少成绩的学生的学号和课程号。SELECT学号，课程号FROM选课表WHERE 成绩 IS NULL;8）查询测绘地理信息技术专业且籍贯为昆明的学生学号，姓名。SELECT学号，姓名FROM学生表WHERE专业名='测绘地理信息技术'AND籍贯='昆明'；9)查询选修了空间数据库技术应用课程的学生学号及成绩，结果按成绩以降序排列。SELECT学号，成绩FROM选课表WHERE课程名='空间数据库技术应用'ORDER BY 成绩 DESC;10)查询选修了课程的学生人数。SELECT COUNT (DISTINCT 学号)FROM选课表；注：DISTINCT的作用是防止重复计算学生人数。其它涉及到MAX、MIN、AVG、GROUP、HAVING等命令的查询不再举例，可参照以 上例如举一反三。(3)连接查询假设查询涉及两个或两个以上的基本表，那么需要进行连接查询。连接查询只需在FROM子 句中指出要连接的表的名称，并在WHERE子句中指定查询条件即可。1)查找所有选修了课程的学生姓名及专业名。SELECT DISTINCT姓名，专业名FROM学生表，选课表WHERE学生表.学号=选课表.学号；2)查找李小多所选课程的课程名和成绩。SELECT '李小多所选课程：'，课程名，成绩FROM 学生表，课程表，选课表WHERE学生表.姓名二'李小多'AND选课表.课程号=课程表.课程号AND学生表.学号=选课表.学号；该查询涉及三个基本表之间的连接运算，用户只需用外键指定连接条件即可。SELECT 子句中允许出现字符串常量，如“李小多所选课程：”起到提示作用，方便查询结果阅读。(4)嵌套查询嵌套查询是指在SELECT-FROM-WHERE查询块内嵌入一个或多个查询块。例如，找出选修空间数据库技术应用课程的学生及专业。SELECT姓名，专业名FROM学生表WHERE 学号 IN (SELECT 学号FROM选课表WHERE课程号IN (SELECT课程号FROM课程表WHERE课程名='空间数据库技术应用'）；该查询在最外层查询体内又嵌套了两层查询。嵌套查询执行时是自下而上进行的，即外 层用到内层查询的结果。在嵌套查询中经常用到谓词IN。此外，许多嵌套查询可以转换成连接查询，但并非所有 的嵌套查询都可以用连接查询代替。5 .数据更新SQL语言的数据更新操作包括插入、修改和删除。（1）插入数据插入语句的基本格式如下：INSERT INTO v表名>属性列1>,属性列2>VALUES （v常量 1>,<常量 2>）；该命令是将新记录插入到指定的表中。假设属性列省略，那么是往表中所有字段中插入数据。例如，向学生表中插入一条记录。INSERT INTO 学生VALUES （'2014089','李万，'男'，22,'测绘地理信息技术'）；此外，SQL允许向表中插入局部字段内容。例如，向课程表中插入一条记录的局部字段。INSERT INTO课程（课程号，课程名称）VALUES （'0126','空间数据库技术应用'）；（2）修改数据修改数据的基本格式如下：UPDATE表名SET v列名1>=表达式习，列名2>=表达式习WHEREv条件 >；该命令用于修改表中满足WHERE查询条件的记录内容，SET用于将新值赋予某个字段 以替代旧值。例如，将课程表中“数据库原理”改为“空间数据库技术应用”。UPDATE课程SET课程名="空间数据库技术应用”WHERE课程名:”数据库原理”；假设无WHERE子句，那么表示修改所有记录的值。例如，将所有课程的学分字段值减lo UPDATE课程SET学分二学分-1；(3)删除数据删除数据的基本格式如下：DELETEFROM v表名WHEREv条件习；用于删除表中满足条件的记录；假设省略WHERE子句，那么表示删除表中所有记录，只保 留表的结构。例如，根据教学计划调整，删除“大学语文”课程。DELETEFROM课程WHERE课程名=大学语文'；6 .数据控制通过对数据库各种权限的授予或回收来管理数据库系统称为数据控制，包括事务管理和 数据保护，即数据库的恢复、并发控制、数据库完整性和平安性等。下面仅以授权和回收权 限为例进行简单介绍。(1)授权授权语句的格式如下：GRANT权限，权限习ON对象类型对象名习TO用户 , 用户-WITH GRANT OPTION；用于将作用在指定操作对象上的操作权限授予指定用户。对象类型可以是表、视图等； 接受权限的用户可以是一个或多个具体的用户，也可以是PUBLIC,即全体用户。假设指定了 WITH GRANT OPTION子句，那么获得某种权限的用户还可以将这种权限再授予其他用户； 反之，只能使用权限。例如，将查询课程表中数据的权限授予所有用户。GRANT SELECTON TABLE 课程TO PUBLIC；7 .回收权限回收权限的格式如下：REVOKE权限,权限 ON对象类型对象名习FROM用户 ,用户 ；此命令的功能是将授予用户的权限回收。当涉及多个用户权限时，收回上级用户权限的同时也收回其对应的所有下级用户权限。例如，回收用户ZHANGSAN和LISI对学生表的更新权限。REVOKE UPDATEON TABLE 学生FROM ZHANGSAN, LISI；