SQL数据库完全操作手册.docx

1、SQL数据库完全操作手册SQL数据库完全操作手册SQL是 Structured Quevy Language( 结构化查询语言 ) 的缩写。 SQL是专为数据库而建立的操作命令集， 是一种功能齐全的数据库语言。 在使用它时， 只需要发出“做什么”的命令，“怎么做”是不用使用者考虑的。 SQL功能强大、简单易学、使用方便，已经成为了数据库操作的基础，并且现在几乎所有的数据库均支持 SQL。#1 二、 SQL数据库数据体系结构SQL数据库的数据体系结构基本上是三级结构， 但使用术语与传统关系模型术语不同。在 SQL中，关系模式 ( 模式 ) 称为“基本表” (base table) ；存储模式 (

2、 内模式 ) 称为“存储文件” (stored file) ；子模式 ( 外模式 ) 称为“视图” (view) ；元组称为“行” (row) ；属性称为“列” (column) 。名称对称如 00100009a ：#1 三、 SQL语言的组成在正式学习 SQL语言之前，首先让我们对 SQL语言有一个基本认识，介绍一下 SQL 语言的组成：1. 一个 SQL数据库是表 (Table) 的集合，它由一个或多个 SQL模式定义。2. 一个 SQL表由行集构成，一行是列的序列( 集合 ) ，每列与行对应一个数据项。3. 一个表或者是一个基本表或者是一个视图。 基本表是实际存储在数据库的表， 而视图是

3、由若干基本表或其他视图构成的表的定义。4. 一个基本表可以跨一个或多个存储文件， 一个存储文件也可存放一个或多个基本表。每个存储文件与外部存储上一个物理文件对应。5. 用户可以用 SQL语句对视图和基本表进行查询等操作。在用户角度来看， 视图和基本表是一样的，没有区别，都是关系( 表格 ) 。6.SQL 用户可以是应用程序，也可以是终端用户。 SQL语句可嵌入在宿主语言的程序中使用，宿主语言有 FORTRAN，COBOL， PASCAL，PL/I ，C 和 Ada 语言等。 SQL用户也能作为独立的用户接口，供交互环境下的终端用户使用。#1 四、对数据库进行操作SQL包括了所有对数据库的操作，

4、主要是由 4 个部分组成：1. 数据定义： 这一部分又称为“ SQL DDL”， 定义数据库的逻辑结构， 包括定义数据库、基本表、视图和索引 4 部分。2. 数据操纵： 这一部分又称为“ SQL DML”，其中包括数据查询和数据更新两大类操作，其中数据更新又包括插入、删除和更新三种操作。3. 数据控制：对用户访问数据的控制有基本表和视图的授权、完整性规则的描述，事务控制语句等。4. 嵌入式 SQL语言的使用规定：规定 SQL语句在宿主语言的程序中使用的规则。下面我们将分别介绍：#2 ( 一) 数据定义SQL数据定义功能包括定义数据库、基本表、索引和视图。首先，让我们了解一下 SQL所提供的基本

5、数据类型： ( 如 00100009b)1. 数据库的建立与删除(1) 建立数据库：数据库是一个包括了多个基本表的数据集，其语句格式为：CREATE DATABASE 其它参数其中， 在系统中必须是唯一的，不能重复，不然将导致数据存取失误。其它参数因具体数据库实现系统不同而异。例：要建立项目管理数据库 (xmmanage)，其语句应为：CREATE DATABASE xmmanage(2) 数据库的删除：将数据库及其全部内容从系统中删除。其语句格式为： DROP DATABASE 例：删除项目管理数据库 (xmmanage)，其语句应为：DROPDATABASExmmanage2. 基本表的定

6、义及变更本身独立存在的表称为基本表， 在 SQL语言中一个关系唯一对应一个基本表。 基本表的定义指建立基本关系模式，而变更则是指对数据库中已存在的基本表进行删除与修改。(1) 基本表的定义：基本表是非导出关系，其定义涉及表名、列名及数据类型等，其语句格式为：CREATE TABLE. ( 数据类型 缺省值 NOT NULL / NULL ， 数据类型 缺省值 NOT NULL / NULL .， UNIQUE ( 列名，列名 .) ， PRIMARY KEY( 列名 ) ， FOREIGN KEY( 列名，列名 .)REFERENCE ( 列名，列名 .) ， CHECK(条件 ) 其它参数

7、)其中，数据库名 . 指出将新建立的表存放于该数据库中；新建的表由两部分组成：其一为表和一组列名，其二是实际存放的数据 ( 即可在定义表的同时，直接存放数据到表中 ) ；列名为用户自定义的易于理解的名称，列名中不能使用空格；数据类型为上面所介绍的几种标准数据类型；NOTNULL/NULL指出该列是否允许存放空值， SQL语言支持空值的概念， 所谓空值是“不知道”或“无意义”的值，值得注意的是数据“ 0”和空格都不是空值，系统一般默认允许为空值，所以当不允许为空值时，必须明确使用 NOT NULL；，UNIQUE将列按照其规定的顺序进行排列， 如不指定排列顺序， 则按列的定义顺序排列； PRIM

8、ARY KEY用于指定表的主键 ( 即关系中的主属性 ) ，实体完整性约束条件规定：主键必须是唯一的，非空的；， FOREIGNKEY ( 列名，列名 .)REFERENCE( 列名，列名 .)是用于指定外键参照完整性约束条件，FOREIGNKEY指定相关列为外键，其参照对象为另外一个表的指定列， 即使用 REFERENCE引入的外表中的列， 当不指定外表列名时， 系统将默认其列名与参照键的列名相同，要注意的是： 使用外键时必须使用参照， 另外数据的外键参照完整性约束条件规定：外键的值要么与相对应的主键相同，要么为空值( 具体由实现系统不同而异 )， CHECK用于使用指定条件对存入表中的数据

9、进行检查，以确定其合法性，提高数据的安全性。例：要建立一个学生情况表(student)CREATE TABLE student /创建基本表 student(st_class CHAR(8) ， /定义列 st_class班级，数据类型为8 位定长字符串st_no CHAR(10) NOT NULL ，/ 定义列 st_no 学号，类型为 10 位定长字符串，非空st_name CHAR(8) NOT NULL ，/ 定义列 st_name 姓名，类型为8 位定长字符串，非空st_sex CHAR(2) ， / 定义列 st_sex 性别，类型为 2 位定长字符串st_age SMALLINT

10、 ， / 定义列 st_age年龄，类型为短整型PRIMARY KEY (st_no)/定义 st_no学号为主键。例：要建立课程设置表(subject)CREATE TABLE subject/ 创建基本表 subject(su_no CHAR(4) NOT NULL ，/ 定义列 su_no 课号，类型为 4 位定长字符串，非空su_subject CHAR(20) NOT NULL， / 定义列 su_subject 课程名，类型为20 位定长字符串，非空su_credit INTEGER ， /定义列 su_credit学分，类型为长整数su_period INTEGER ， /定义列

11、 su_period学时，类型为长整数su_preno CHAR(4) ，/定义列 su_preno 先修课号，类型为 4 位定长字符串PRIMARY KEY(su_no)/定义 su_no 课号为主键。例：要建立学生选课表(score)CREATE TABLE score /创建基本表 score(st_no CHAR(10) ， / 定义列 st_no 学号，类型为 10 位定长字符串su_no CHAR(4) ， / 定义列 su_no 课号，类型为 4 位定长字符串sc_score INTEGER NULL ， / 定义列 sc_score ，类型为长整形，可以为空值FOREIGN K

12、EY (st_no) REFERENCE student， / 从表 student 中引入参照外键 st_no ，以确保本表与表 student 的关联与同步FOREIGN KEY (suno) REFERENCE subject)/从表 subject 中引入参照外键su_no ，以确保本表与表 subject 的关联与同步(2) 基本表的删除：用以从数据库中删除一个基本表及其全部内容，其语句格式为：DROP TABLE . 表名例如：将上面建立的表都删除DROP TABLE student ， subject ， score(3) 基本表的修改：在基本表建立并使用一段时间后，可能需要根据

13、实际要求对基本表的结构进行修改，即增加新的属性或删除属性。增加属性的语句格式为：ALTER TABLE . 表名 ADD( 数据类型 缺省值 NOT NULL / NULL， 数据类型缺省值 NOT NULL / NULL .， UNIQUE (列名，列名 .) ， PRIMARY KEY(列名 ) ， FOREIGN KEY(列名，列名 .) REFERENCE ( 列名，列名 .)， CHECK(条件 ) 其它参数 )例如：在基本表 student 中加入列 stborn 出生日期，数据类型为 DATE，且不能为空值ALTER TABLE student ADD (stborn DATE

14、NOT NULL)删除属性的语句格式为：ALTER TABLE . 表名 DROP( 数据类型 缺省值 NOT NULL / NULL， 数据类型 缺省值 NOT NULL / NULL .)例如：将基本表 student 中的列 st_age 删除ALTER TABLE student DROP (st_age)3. 视图定义与删除在 SQL中，视图是外模式一级数据结构的基本单位。它是从一个或几个基本表中导出的表，是从现有基本表中抽取若干子集组成用户的“专用表”。这种构造方式必须使用 SQL中的 SELECT语句来实现。在定义一个视图时，只是把其定义存放在系统的数据中，而并不直接存储视图对应

15、的数据，直到用户使用视图时才去求得对应的数据。(1) 视图的定义：定义视图可以使用CREATE VIEW语句实现，其语句格式为：CREATE VIEW视图名 AS SELECT 语句从一个基本表中导出视图：例：从基本表 student 中导出只包括女学生情况的视图CREATE VIEW WOMANVIEW AS创/建一个视图 WOMANVIEWSELECTst_class ，st_no ， st_name，st_age / 选择列 st_class， st_no，st_name，st_age显示FROM student /从基本表student引入WHERE st_sex=女 / 引入条件为性

16、别为“女”，注意字符变量都使用单引号引用从多个基本表中导出视图：例如：从基本表 student 和 score 中导出只包括女学生且分数在 60 分以上的视图 CREATEVIEW WOMAN_SCORE AS定义/视图 WOMANSCORESELECT student.st_class，student.st_no， student.st_name， student.st_age，score.sc_score / 有选择性显示相关列FROM student.score / 从基本表 student 和 score 中引入WHERE studen.st_sex= 女 AND score.sc_s

17、core=60 AND student.st_no=score.st_no/ 选择条件：性别为“女” 且分数在 60 分以上。并使用 st_no 将两表联系起来。以后如果进行这一视图的应用，则只需使用语句SELECT * FROM WOMAN_SCORE其/中“ * ”为通配符，代表所有元素(2) 视图的删除：用于删除已不再使用的视图，其语句格式如下：DROP VIEW视图名例：将上面建立的 WOMAN_SCORE视图删除DROP VIEW WOMAN_SCORE4. 索引的定义与删除索引属于物理存储概念， 而不是逻辑的概念。 在 SQL中抛弃了索引概念， 直接使用主键概念。值得一提的是，有些

18、关系 DBMS同时包括索引机制和主键机制，这里我们推荐使用主键机制，因为它对系统资源占用较低且效率较高。(1) 索引的定义：索引是建立在基本表之上的，其语句格式为：CREATE UNIQUE INDEX 索引名 ON. 表名 ( 列名 ASC/DESC，列名 ASC/DESC .)这里，保留字 UNIQUE表示基本表中的索引值不允许重复，若缺省则表示索引值在表中允许重复； DESC表示按索引键降序排列，若缺省或 ASC表示升序排列。例：对基本表 student 中的 st_no 和 st_age 建立索引，分别为升序与降序，且索引值不允许重复CREATE UNIQUE INDEX STINDE

19、X ON/创建索引 STINDEXstudent(st_no ASC ， st_age DESC)/ 对 student 中的 st_no 和 st_age 建立索引(2) 索引的删除：DROP INDEX索引名例：删除上面建立的索引 STINDEXDROP INDEX STINDEX#2 ( 二 ) 数据查询SQL 是一种查询功能很强的语言，只要是数据库存在的数据，总能通过适当的方法将它从数据库中查找出来。 SQL中的查询语句只有一个： SELECT，它可与其它语句配合完成所有的查询功能。 SELECT语句的完整语法，可以有SELECT 目标表的列名或列表达式集合FROM基本表或 ( 和 )

20、 视图集合WHERE条件表达式GROUP BY列名集合6 个子句。完整的语法如下： HAVING组条件表达式 ORDER BY列名集合整个语句的语义如下：从 FROM子句中列出的表中，选择满足WHERE子句中给出的条件表达式的元组，然后按 GROUPBY子句 ( 分组子句 ) 中指定列的值分组，再提取满足HAVING子句中组条件表达式的那些组， 按 SELECT子句给出的列名或列表达式求值输出。 ORDER子句 ( 排序子句 ) 是对输出的目标表进行重新排序，并可附加说明 ASC(升序 ) 或 DESC(降序 ) 排列。在 WHERE子句中的条件表达式 F 中可出现下列操作符和运算函数：算术比

21、较运算符： ， ，=， =，。逻辑运算符： AND， OR， NOT。集合运算符： UNION(并 ) ， INTERSECT(交 ) ， EXCEPT(差 ) 。集合成员资格运算符： IN ， NOT IN谓词： EXISTS(存在量词 ) ， ALL， SOME， UNIQUE。聚合函数： AVG(平均值 ) ， MIN(最小值 ) ， MAX(最大值 ) ， SUM(和 ) ， COUNT(计数 ) 。F 中运算对象还可以是另一个 SELECT语句，即 SELECT语句可以嵌套。上面只是列出了 WHERE子句中可出现的几种主要操作， 由于 WHERE子句中的条件表达式可以很复杂，因此 S

22、ELECT句型能表达的语义远比其数学原形要复杂得多。下面，我们以上面所建立的三个基本表为例，演示一下 SELECT的应用：1. 无条件查询例：找出所有学生的的选课情况SELECT st_no ， su_noFROM score例：找出所有学生的情况SELECT*FROM student“* ”为通配符，表示查找FROM中所指出关系的所有属性的值。2. 条件查询条件查询即带有 WHERE子句的查询，所要查询的对象必须满足 WHERE子句给出的条件。例：找出任何一门课成绩在 70 以上的学生情况、课号及分数SELECT UNIQUE student.st_class ， student.st_no

23、 ， student.st_name ， student.st_sexstudent.st_age ， score.su_no ， score.scoreFROM student ， scoreWHERE score.score=70 AND score.stno=student.st_no，这里使用 UNIQUE是不从查询结果集中去掉重复行，如果使用DISTINCT 则会去掉重复行。 另外逻辑运算符的优先顺序为 NOTANDOR。例：找出课程号为 c02 的，考试成绩不及格的学生SELECT st_noFROM scoreWHERE su_no=c02AND score603. 排序查询排序

24、查询是指将查询结果按指定属性的升序 (ASC)或降序 (DESC)排列，由 ORDERBY子句指明。例：查找不及格的课程，并将结果按课程号从大到小排列SELECT UNIQUE su_noFROM scoreWHERE score80 )这里需要明确的是： 当查询涉及多个基本表时用嵌套查询逐次求解层次分明， 具有结构程序设计特点。在嵌套查询中， IN 是常用到的谓词。若用户能确切知道内层查询返回的是单值，那么也可用算术比较运算符表示用户的要求。5. 计算查询计算查询是指通过系统提供的特定函数 ( 聚合函数 ) 在语句中的直接使用而获得某些只有经过计算才能得到的结果。常用的函数有：COUNT(*

25、) 计算元组的个数COUNT(列名 ) 对某一列中的值计算个数SUM(列名 ) 求某一列值的总和 ( 此列值是数值型 )AVG(列名 ) 求某一列值的平均值 ( 此列值是数值型 )MAX(列名 ) 求某一列值中的最大值MIN(列名 ) 求某一列值中的最小值例：求男学生的总人数和平均年龄SELECT COUNT(*)， AVG(st_age)FROM studentWHERE st_sex=男例：统计选修了课程的学生的人数SELECT COUNT(DISTINCT st_no)FROM score注意：这里一定要加入人统计，所以要使用#2 ( 三 ) 数据更新DISTINCT，因为有的学生可能选

26、修了多门课程，但统计时只能按 DISTINCT 进行过滤。1数据更新包括数据插入、删除和修改操作。它们分别由 INSERT 语句， DELETE语句及UPDATE语句完成。这些操作都可在任何基本表上进行，但在视图上有所限制。其中，当视图是由单个基本表导出时， 可进行插入和修改操作， 但不能进行删除操作； 当视图是从多个基本表中导出时，上述三种操作都不能进行。1. 数据插入将数据插入 SQL的基本表有两种方式：一种是单元组的插入，另一种是多元组的插入。单元组的插入： 向基本表 score 中插入一个成绩元组 (100002 ，c02，95) ，可使用以下语句：INSERT INTO score(

27、st_no ，su_no ，score) VALUES( 100002， c02， 95)由此，可以给出单元组的插入语句格式：INSERT INTO 表名 ( 列名 1，列名 2 ) VALUES(列值1，列值2 )其中，列名序列为要插入值的列名集合， 列值序列为要插入的对应值。 若插入的是一个表的全部列值，则列名可以省略不写如上面的 (st_no ， su_no ， score) 可以省去；若插入的是表的部分列值，则必须列出相应列名，此时，该关系中未列出的列名取空值。多元组的插入：这是一种把 SELECT语句查询结果插入到某个已知的基本表中的方法。例如： 需要在表 score 中求出每个学生

28、的平均成绩， 并保留在某个表中。 此时可以先创建一个新的基本表 stu_avggrade ，再用 INSERT语句把表 score 中求得的每一个学生的平均成绩( 用 SELECT求得 ) 插入至 stu_avggrade 中。CREATE TABLE stu_avggrade(st_no CHAR(10) NOT NULL ，/ 定义列 st_no 学号，类型为 10 位定长字符串，非空age_grade SMALLINT NOT NULL )/ 定义列 age_grade 平均分，类型为短整形，非空INSERT INTO stu_avggrade(st_no ， age_grade)SELECT st_no ， AVG(score)FROM scoreGROUP BY st_no / 因为要求每一个学生所有课程的平均成绩，必须按学号分组进行计算。2. 数据删除SQL 的删除操作是指从基本表中删除满足WHERE的记录。如果没有 WHERE子句，则删除表中全部记录，但表结构依然存在。其