上按姓名(tname)降序建立唯一索引createuniqueindexteano.ppt

1、名以清修 利以义制 绩以勤勉 汇通天下,李安平,新晋商理念,2020/9/10,欢迎你向我提问！,如果你在学习 中遇到问题,数据库原理与应用,Principle and Application of DataBase,薛东平,2020/9/10,数据库原理与应用,Principle and Application of DataBase,结构化查询语言-SQL,基本操作： 1 SQL简介 2 数据定义命令 3 数据操纵命令 4 数据控制命令 5 视图,2020/9/10,数据库原理与应用,Principle and Application of DataBase,结构化查询语言-SQL,结构化

2、操作： 6 过程化SQL(PL/SQL) 7 嵌入式SQL和动态SQL 8 SQL中的复杂完整性约束 9 小结,2020/9/10,数据库原理与应用,Principle and Application of DataBase,结构化查询语言SQL(Structured Query Language)是一种面向集合的数据库查询语言 。 SQL(Structured Query Language)集数据查询（Data Query）、数据操纵（Data Manipulation）、数据定义（Data Definition）和数据控制（Data Control）于一体。,2020/9/10,数据库原理

3、与应用,Principle and Application of DataBase,2020/9/10,数据库原理与应用,Principle and Application of DataBase,结构化查询语言-SQL,基本操作： 1 SQL简介 2 数据定义命令 3 数据操纵命令 4 数据控制命令 5 视图,2020/9/10,数据库原理与应用,Principle and Application of DataBase,SQL发展,1974年，在IBM圣约瑟实验室的Boyce和Chamberlin在研制System R时提出了在其上要实现一种查询语言，当时称为SEQUEL语言（Struct

4、ured English Query Language），后简称为SQL。 SQL从IBM公司提出经过了System R实现研制的过程，又经过商用的SQL/DS产品研制，以及后来向DB2产品的进一步开发演变。还有Oracle公司以SQL为目标，持续开发其升级的产品。此外还有其它许多公司、科研机构对SQL的研究做出了贡献。,2020/9/10,数据库原理与应用,Principle and Application of DataBase,1986年12月，ANSI公布了SQL语言标准 。第二年，ISO/IEC接受并公布它为ISO/IEC 9075：1986信息处理系统-数据库语言SQL，即SQL的

5、第一个国际标准版本，称为SQL/86。 89年，ISO/IEC又通过并正式公布了第二个版本ISO/IEC 9075：1989信息处理系统-数据库语言具有完整性增强特征的SQL，对SQL/86进行更新，称为SQL/89 。 我国89年提出制订了等同采用SQL/89国家标准的关系数据库语言SQL国家标准 。,SQL发展,2020/9/10,数据库原理与应用,Principle and Application of DataBase,1992年，制定了ISO/IEC 9075：1992信息技术-数据库语言SQL，其文本长达622页，约是SQL/89文本的5倍，即，SQL/92标准。 SQL/99标准

6、是在92年SQL92公布前后就开始进行研究的所谓SQL3。 SQL/99规定的功能完全向上兼容，是SQL/92的超集。 SQL/框架、SQL/基础、SQL/调用级接口(SQL/CLI)、SQL/特久存储模块(SQL/PSM)、SQL/宿主语言联，五个部分的文本共有1300多页,SQL发展,2020/9/10,数据库原理与应用,Principle and Application of DataBase,SQL200n标准在对SQL99原内容作出一定调整增删后，又增添了很多新的特性，反应了新的需求和数据库管理系统的发展方向。与SQL99相比SQL200n标准中的新特性主要有：增添了新的数据类型，主

7、要是集合类型；增加了新的谓词和操作函数；增加了新的语义单元，主要包括：归并(Merge Statement)、OLAP表采样(TableSample)、产生列、标识列和序列产生器等。,SQL发展,2020/9/10,数据库原理与应用,Principle and Application of DataBase,SQL体系结构,2020/9/10,数据库原理与应用,Principle and Application of DataBase,外模式对应于视图和部分基本表，模式对应于基本表，元组称为“行”，属性称为“列”。内模式对应于存储文件。特征如下： 一个SQL模式(Schema)是表和约束的集合

8、。 一个表(Table)是行(Row)的集合，每行是列(Column)的序 列，每列对应一个数据项。 个表可以是一个基本表，也可以是一个视图，基本表是实 际存储在数据库中的表，视图是一个虚表。 一个基本表可以跨一个或多个存储文件，一个存储文件也可 存放一个或多个基本表 。,SQL体系结构,2020/9/10,数据库原理与应用,Principle and Application of DataBase,SQL语言主要分成四个部分： 数据定义：这一部分也称为“DDL”，用于定义SQL模式、基本表、视图和索引。 数据操纵：这一部分也称为“DML”,数据操纵分成数据查询和数据更新两类，其中数据更新又分

9、成插入、删除和修改三种操作。 数据控制：这一部分也称为“DCL”，包括对基本表和视图的授权，完整性规则的描述，事务控制语句等。 嵌入式SQL：这一部分内容涉及到SQL语句嵌入在宿主语言程序中的使用规则。,SQL体系结构,2020/9/10,数据库原理与应用,Principle and Application of DataBase,SQL特点,SQL语言的特点： 1.集DDL、DML、DCL于一体 SQL语言集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动 。 2.高度非过程化 SQL语言进行数据操作，用户只需提出“

10、做什么”，而不必指明“怎么做”，用户无需了解存取路径 。 3.面向集合的操作方式 集合操作,2020/9/10,数据库原理与应用,Principle and Application of DataBase,SQL语言的特点： 4.一种语法提供两种操作方式 交互式操作 嵌入式操作 5. 功能强大、语言简洁 设计巧妙，语言十分简洁 ，核心功能只用了9个动词：CREATE、DROP、ALTER、SELECT、INSERT、UPDATE、DELETE、GRANT、REVOKE,SQL特点,2020/9/10,数据库原理与应用,Principle and Application of DataBase,

11、结构化查询语言-SQL,基本操作： 1 SQL简介 2 数据定义命令 3 数据操纵命令 4 数据控制命令 5 视图,2020/9/10,数据库原理与应用,Principle and Application of DataBase,数据定义命令,数据库结构的建立与删除 建立 CREATE SCHEMA 数据库名 AUTHORIZATION ； 举例：创建教师和研究生的科研档案的数据库结构(scientific_research )，并授权给用户user1。 CREATE SCHEMA “scientific_research ” AUTHORIZATION user1 ；,2020/9/10,数

12、据库原理与应用,Principle and Application of DataBase,数据库结构的建立与删除 删除 DROP SCHEMA ； CASCADE表示在删除数据库结构的同时删除数据库中所有的对象(表、视图等)； RESTRICT表示只有当数据库中没有任何下属的对象时才能执行DROP SCHEMA语句。 举例：删除数据库“scientific_research ”，要求连同其下属对象一起删除。 DROP SCHEMA scientific_research CASCADE；,数据定义命令,2020/9/10,数据库原理与应用,Principle and Application

13、of DataBase,数据定义命令,表结构的建立、删除与修改 建立表 CREATE TABLE 表名( 属性1 数据类型 缺省值，列约束, 属性2 数据类型 缺省值，列约束, 属性n 数据类型缺省值，列约束 ,表级完整性约束条件)；,2020/9/10,数据库原理与应用,Principle and Application of DataBase,说明： 表级完整性约束条件是对整个表的完整性约束，常用的是外码的设置，语句： FOREIGN KEY (外码属性组) REFERENCES 被参照表名(被参照列)。,数据定义命令,2020/9/10,数据库原理与应用,Principle and Ap

14、plication of DataBase,举例1：建立教师和研究生科研档案数据库中的教师信息表teacher。,CREATE TABLE teacher( tno CHAR(9) PRIMARY KEY, tname CHAR (8), tsex CHAR (2),tage INTEGER, tbirth DATE, twork DATE, tposition CHAR (6), tpolit CHAR (6), tedu CHAR (6);,数据定义命令,2020/9/10,数据库原理与应用,Principle and Application of DataBase,举例2：建立教师和研究

15、生科研档案数据库中的论文信息表paper。,数据定义命令,CREATE TABLE paper( pno char(6) primary key， pname char(40)， plevel char(10)， ppub char(20)， pdate date， pdept char(20);,2020/9/10,数据库原理与应用,Principle and Application of DataBase,数据定义命令,举例3：建立论文信息表和教师信息表之间的联系表 (发表)tprole1,CREATE TABLE tprole1( tno CHAR(9) NOT NULL， pno CH

16、AR (6) NOT NULL， ratepoint NUMBER(6,2)， ppoint SMALLINT， norder SMALLINT)；,2020/9/10,数据库原理与应用,Principle and Application of DataBase,数据类型,2020/9/10,数据库原理与应用,Principle and Application of DataBase,数据类型,2020/9/10,数据库原理与应用,Principle and Application of DataBase,表结构的建立、删除与修改 删除表 DROP TABLE 表名 RESTRICT | CA

17、SCADE； CASCADE ，DROP TABLE命令将从数据库中删除指定表的所有信息，包括依赖于该基本表的视图、触发器、存储过程、函数等相关对象；如果选择选项RESTRICT或缺省，则不允许存在任何依赖于该基本表的相关对象(视图、触发器、存储过程、函数等)，否则DROP TABLE命令将被拒绝。,数据定义,2020/9/10,数据库原理与应用,Principle and Application of DataBase,举例：删除教师研究生的科研档案数据库中的论文信息表paper。 DROP TABLE paper;,数据定义,2020/9/10,数据库原理与应用,Principle and

18、 Application of DataBase,表结构的建立、删除与修改 修改表,修改属性的数据类型 增加属性列 删除属性列 增加和删除完整性约束条件 (唯一性、主键、外键),数据定义,2020/9/10,数据库原理与应用,Principle and Application of DataBase,1修改属性的数据类型 ALTER TABLE MODIFY (属性名 数据类型名); 举例：修改teacher表(教师信息表)的属性列tage(年龄)的数据类型，由原来的integer修改为smallint类型。 ALTER TABLE teacher MODIFY tage SMALLINT;

19、2 增加属性列 ALTER TABLE ADD 完整性约束条件； 举例：在paper表(论文信息表)中增加“作者姓名”属性列，数据类型设置为字符型。 ALTER TABLE paper ADD author char(10);,数据定义,2020/9/10,数据库原理与应用,Principle and Application of DataBase,3删除属性列 ALTER TABLE DROP COLUMN ； 举例： 删除paper表(论文信息表)中的author属性。 ALTER TABLE paperDROP COLUMN author； 4 增加主键和外键、唯一性约束 （PRIMAR

20、Y KEY FOREIGN KEY UNIQUE） ALTER TABLE ADD ； 举例：假设在创建paper表时没有定义主键，现要求将pno(论文编号)设置为主键。 ALTER TABLE paperADD PRIMARY KEY(pno)；,数据定义,2020/9/10,数据库原理与应用,Principle and Application of DataBase,举例：增加外码键。 alter table tprole1 add FOREIGN KEY(pno) REFERENCES paper(pno); 举例：删除属性的唯一性约束。 alter table paper drop u

21、nique(pname);,数据定义,2020/9/10,数据库原理与应用,Principle and Application of DataBase,数据定义,索引的建立和删除 建立索引,CREATE UNIQUE/CLUSTER INDEX ON ( ASC/DESC ， ASC/DESC)； 举例：在教师信息表(teacher)上按姓名(tname)降序建立唯一索引。 CREATE UNIQUE INDEX teano ON teacher(tname DESC)；,在许多DBMS中， 如果创建表结构时建立了主键， 系统将自动以主键升序建立唯一索引,2020/9/10,数据库原理与应用,

22、Principle and Application of DataBase,索引的建立和删除 删除索引 DROP INDEX ；,数据定义,2020/9/10,数据库原理与应用,Principle and Application of DataBase,结构化查询语言-SQL,基本操作： 1 SQL简介 2 数据定义命令 3 数据操纵命令 4 数据控制命令 5 视图,2020/9/10,数据库原理与应用,Principle and Application of DataBase,数据操纵,数据输入(INSERT) 输入新数据 INSERT INTO (属性列表) VALUES()； 举例： 向

23、教师信息表中插入教师“李丽”的信息。,INSERT INTO teacher VALUES(200305313, 李丽, 女,35, TO_DATE(1971-01-25, yyyy/mm/dd), TO_DATE(1994-07-01, yyyy/mm/dd), 副教授, 农工, 硕士);,注意：数据类型、属性列的一致性，完整性约束,2020/9/10,数据库原理与应用,Principle and Application of DataBase,数据操纵,数据输入(INSERT) 输入查询结果 INSERT INTO () SELECT查询；,将一次查询结果的数据成批插入到指定表中,举例：从

24、论文信息表paper中复制论文编号(pno)、论文名称(pname)和论文出版刊物(ppub)到新表p_temp中，要求只复制论文级别为“国家核心”的那部分数据信息。,2020/9/10,数据库原理与应用,Principle and Application of DataBase,首先建立表p_temp： CREATE TABLE p_temp( pno char(6) not null primary key, pname char(40), ppub char(20);,数据操纵,将paper表中相应的列值复制到新建的p_temp表中： INSERT INTO p_temp SELECT

25、pno,pname,ppub FROM paper WHERE plevel=国家核心；,2020/9/10,数据库原理与应用,Principle and Application of DataBase,数据操纵,数据更新(UPDATE) UPDATE SET =，= WHERE ;,UPDATE命令用提供的常量或表达式计算结果替代属性现有值，来更新由WHERE条件所决定的行中若干属性列的值，当命令中缺省WHERE子句时将更新所有元组。,2020/9/10,数据库原理与应用,Principle and Application of DataBase,数据操纵,举例：将教师“李丽”的职称修改为“

26、教授”。 UPDATE teacher SET tposition= 教授 WHERE tname= 李丽；,举例：将所有教师的年龄增加1岁。 UPDATE teacher SET tage=tage+1；,2020/9/10,数据库原理与应用,Principle and Application of DataBase,数据操纵,数据删除(DELETE) DELETE FROM WHERE ；,DELETE命令将删除指定条件下的所有元组，当缺省WHERE条件子句时将删除指定表中的所有元组。,2020/9/10,数据库原理与应用,Principle and Application of Data

27、Base,数据操纵,举例：从教师信息表teacher中删除编号为“200305310”的教师信息。 DELETE FROM teacher WHERE tno= 200305310;,举例：从论文信息表paper中删除2000年以前发表的论文信息。 DELETE FROM paper WHERE pdateTO_DATE(2000-1, yyyy/mm);,在ORACLE环境下验证的,2020/9/10,数据库原理与应用,Principle and Application of DataBase,数据操纵,举例：从教师信息表teacher中删除编号为“200305310”的教师信息。 DELE

28、TE FROM teacher WHERE tno= 200305310;,举例：删除全部。 DELETE FROM teacher；,2020/9/10,数据库原理与应用,Principle and Application of DataBase,数据操纵,数据保存(COMMIT) COMMIT；,保证对表的内容的修改被物理地存储在磁盘上,恢复数据(ROLLBACK) ROLLBACK ；,COMMIT前，可以用ROLLBACK命令将数据库恢复到前一状态,保证数据库更新的完整性,2020/9/10,数据库原理与应用,Principle and Application of DataBase,

29、数据操纵,查询(SELECT),简单查询 复杂查询 多表查询 嵌套子查询 集合查询,2020/9/10,数据库原理与应用,Principle and Application of DataBase,数据操纵,查询(SELECT) 简单查询,SELECT DISTINCT|ALL FROM WHERE GROUP BY HAVING ORDER BY ASC|DESC；,SELECT命令从FROM指定的表中检索出符合WHERE子句中指定条件的所有元组,所有列,重复行,2020/9/10,数据库原理与应用,Principle and Application of DataBase,数据操纵,举例：从教师信息表teacher中查询所有教师的编号、姓名和职称。 SELECT tno,tname,tposition FROM teacher；,举例：列出所有论文信息记录。 SELECT * FROM paper;,举例：查找论文信息表中登记过的所有论文级别，消除重复行。 SELECT DISTINCT plevel FROM paper；,2020/9/10,数据库原理与应用,Principle and Application of DataBase,SELECT DISTINCT|ALL FROM WHERE GROUP BY HAVING ORDER BY ASC|DESC；,数