第四章关系数据库语言SQL

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,第,4,章 关系数据库语言,SQL,1,教学内容：,SQL,语言的特点、,SQL,语言的数据类型、,SQL,语言（定义、操纵、查询、控制、嵌入,SQL,语言）。,教学要求：,1,、理解,SQL,语言的特点、功能和使用方法，,SQL Server,的数据类型。,2,、掌握数据定义、操纵、查询语言；视图的定义和操作、基本,SQL,语句的使用；存储过程和触发器的使用,3,、了解嵌入式,SQL,和动态,SQL,技术、规则的使用。,重 点：,SQL,的查询、定义、操纵功能；存储过程和触发器的使用。,难点：,SQL,的查询功能、定义功能、操纵功能。,2,4.,1,SQL Server 概述,9/16/2024,3,SQL Server,是一个高性能的、多用户的关系型数据库管理系统，由于它强大、灵活的功能，丰富的应用编程接口及精巧的系统结构，深受广大用户的青睐。它是当前最流行的数据库服务器系统之一。,3,1996年，Microsoft推出了SQL Server 6.5版本；,1998年，SQL Server 7.0版本和用户见面；,2000年，Microsoft公司增强了SQL Server 7.0的功能，发布了SQL Server 2000，包括企业版、标准版、开发版和个人版等4个版本。,2005年，Microsoft公司又推出了SQL Server 2005，提供了一个完整的数据管理和分析解决方案，给不同需求的组织和个人带来帮助。,2008年，SQL Server 2008面世。,4,9/16/2024,5,SQL Server2008,的各种版本,版 本,描 述,使用场所,企业版,Enterprise Edition,一个全面的数据管理和商业智能平台，提供企业级的可扩展性、高可用性和高安全性以运行企业关键业务应用,大规模OLTP,大规模报表,先进的分析,数据仓库,标准版,Standard Edition,一个完整的数据管理和商业智能平台，提供最好的易用性和可管理性来运行部门级应用,部门级应用,中小型规模OLTP,报表和分析,工作组版,Workgroup Edition,一个可信赖的数据管理和报表平台，提供各分支应用程序以安全、远程同步和管理功能,分支数据存储,分支报表,远程同步,学习版,Express Edition,提供学习和创建桌面应用程序和小型应用程序，并可被ISVS,重新发布的免费版本,入门级&,学习,免费的ISVS,重发,富桌面端应用,5,一、,SQL Server,数据库的组成,SQL Server,的数据主要以文件的方式保存，主要划分为三类文件：主数据文件、辅助文件、日志文件。,主数据文件,：,存储数据库的启动信息和系统表，也可以存储用户数据。每个数据库包含一个主文件。建议扩展名：,.,mdf,辅数据文件,：可选的,，,保存主文件中容纳不下的数据。可以有,0,到多个辅数据文件。扩展名是,.,ndf,。,6,日志文件,：,保存,恢复数据库所需要的所有数据库的事务日志信息。必须至少有一个日志文件，,建议,扩展名用,.,ldf,。,注意：,一个文件只能存在于一个文件组中，不能跨组。,表、索引以及,text,、,ntext,和,image,数据可以指定存放在特定的文件组中。,文件组只能包含数据文件，日志文件不属于任何文件组。,7,二、,SQL Server,数据存储单位,创建数据库之前，要了解,SQL Server,使用的数据存储单位：,存储页（,page,）：,基本数据存储单位，每页,8KB,字节。,范围（,extent,）：,一个范围是,8,个连续存储页。是存储表或索引的基本单位。,文件组：,数据库中对文件进行分组管理的机制。把相关的文件放入特定文件组中，系统按组管理文件。,8,文件组的类型：,主文件组（缺省文件组）,：存放主数据文件和其他没有指定文件组的文件。,系统表在主文件组中,。,用户定义文件组,：用,CREATE DATABASE,或,ALTER DATABASE,语句中的,FILEGROUP,关键字，或在 企业管理器内的,属性,对话框上指定的任何文件组。,9,三、,SQL Server,数据库,1,SQL Server,数据库类型,系统数据库,用户数据库,10,1,、,SQL Server,数据库类型,SQL Server,的数据存储空间可分为若干个“小”数据库，以便于管理和提高存取效率。,有两类数据库：,系统数据库：,存放,SQL Server,的整体信息。,SQL Server,使用系统数据库操作和管理系统。,用户数据库：,是用户创建的数据库。一个,SQL Server,可以管理一个或多个用户数据库。,11,系统数据库和用户数据库,Model,Tempdb,Master,Msdb,Distribution,pubs,Northwind,User,用户数据库,系统数据库,图123,12,2,、系统数据库,安装,SQL Server,时，系统自动地创建,master,、,model,、,tempdb,、,msdb,、,Distribution,等,系统数据库,。,master,数据库,用来存放系统级的信息，如保存本地及远程用户帐号、与本地服务器有交互的远程服务器、进程、可配置的环境变量、系统错误信息、分配给各数据库的存储空间、系统上可用的磁带和磁盘、活动锁等。,13,model,数据库,为用户数据库提供了样板和原型，包含了每一用户数据库所需要的系统表，它能够被更改成为创建新的数据库的定制结构，对,model,所作的每一个改动都在新数据库中产生影响。,msdb,数据库,支持,SQL Server Agent,。,msdb,数据库中的表,sysdbmainplan,_history,保存了系统维护的工作信息，并且为调度信息提供一个存储区。,14,tempdb,数据库,是,SQL Server,上数据库使用的共享工作空间，为临时表和其它临时工作存储需要提供了一个存储区。,Distribution,数据库,在为复制活动配置,SQL Server,时，系统自动安装,Distribution,数据库。存储复制活动中的历史记录和事务数据。,15,3,、 用户数据库,pubs,和,Northwind,数据库,pubs,和,Northwind,数据库是作为培训工作提供的实例数据库，属于用户数据库。一般来说，当培训工具使用完后，要重新安装它，以便恢复其原貌,。,用户,数据库,作为数据库拥有者（,DBO,）,的用户自己,创建的数据库。他拥有对数据库的所有访问权限。,16,4.2 SQL,语言简介,1,、什么是,SQL,语言,SQL,语言是结构化查询语言，,Structured Query Language,简称,SQL,。,是介于关系代数和,关系演算之间的语言，其功能不仅仅是查询，它是通用的、功能极强的关系数据库语言。,SQL,语言集数据,定义、查询、更新和控制,功能于一体。,SQL,语言已经成为关系数据库的标准语言,17,(1),1974,年，,IBM,圣约瑟实验室的,Boyce,和,Chamberlin,为关系数据库管理系统,System-R,设计的一种查询语言，当时称为,SEQUEL,语言（,Structured English,Query,Language),，,后简称为,SQL,；,(2),1981,年，,IBM,推出关系数据库系统,SQL/DS,，,得到广泛应用；,(3),著名关系数据库管理系统都陆续实现,SQL,语言；,(4),1982,年,，,ANSI,着手制定,SQL,标准，,1986,年公布第一个,SQL,标准,-SQL86,，其主要内容：模式定义、数据操作、嵌入式,SQL,等内容。,2,、,SQL,语言的发展历程,18,(5),1987,年,，,ISO,通过,SQL86,标准；,(6),1989,年,，,ISO,制定,SQL89,标准，它在,SQL86,基础上,增补完整性,描述,(7),1990,年,，我国制定等同,SQL89,的国家标准；,(8),1992,年,，,ISO,制定,SQL92,标准，即,SQL2,；,SQL2,相当庞大，实现了对远程数据库访问的支持,(9),1999,年,，,ANSI,制定,SQL3,标准，在,SQL2,基础上扩充了,面向对象功能,，支持自定义数据类型、提供递归操作、临时视图、更新一般的授权结构、嵌套的检索结构、异步,DML,等；,19,SQL,标准的制定使得几乎所有的数据库厂家都采用,SQL,语言作为其数据库语言。但各家又在,SQL,标准的基础上进行扩充，形成自己的语言。,SQL Server,扩充,SQL,标准形成,Transact-SQL,，,简称,T-SQL,。,20,3,、,SQL,语言应用情况,(1),Oracle,、,Sybase,、,Informix,、,Ingres,、,DB2,、,SQL Server,、,Rdb,等大型数据库管理系统实现了,SQL,语言；,(2),Dbase,、,Foxpro,、,Acess,等,PC,机数据库管理系统部分实现了,SQL,语言；,(3),可以在,HTML(Hypertext Markup Language,超文本标记语言,),中嵌入,SQL,语句，通过,WWW,访问数据库；,(4),在,VC,、,VB,、,Delphi,、,PB,也可嵌入,SQL,语句。,21,4 SQL,语言的特点,语言功能的一体化,:,集,DDL,、,DML,、,DCL,为一体。,结构的一体化,：关系模型中唯一的结构类型就是关系表，这种数据结构的单一性,使得增、删、改、查询等操作都只须使用一种操作符。,高度非过程化,：只须提出“做什么”无须指明“怎样做”。用户不必了解存取路径。,22,面向集合的操作方式,：在元组集合上操作,操作结果仍是元组集合。,两种操作方式、统一的语法结构,：,SQL,语言既是自含式、又是嵌入式语言。,自含式可作为连机交互式使用；嵌入式,SQL,可嵌入到高级语言中使用。,语言简洁、易学易用,：数据定义、操纵和控制只用了,9,个动词。,23,SQL,语言中的主要动词,SQL,功能,动词,数据查询,SELECT,数据定义,CREATE,，,DROP,，,ALTER,数据操纵,INSERT,，,UPDATE,，,DELETE,数据控制,GRANT,，,REVOKE,24,数据定义：,定义被存放数据的结构及完整性。,数据检索：,从数据库中检索数据并使用这些数据。,数据操纵：,通过,SQL,更改数据库中的数据。,存取控制：,限制用户检索、增加和修改数据的权限，保护所存储的数据不被非法存取。,数据共享：,调整数据让并发用户共享。,5,、,SQL,语言的功能,25,6,、,SQL,语言的分类,SQL,语言的命令通常分为四类,1,）、数据定义语言（）,创建、修改或删除数据库中各种对象，包括,SQL,模式、基本表、视图、索引等。,命令：,TABLE,VIEW,INDEX,（1）CREATE,26,（2）ALTER TABLE,2,）、查询语言（,S,）,按照指定的组合、条件表达式或排序检索已存在的数据库中数据，不改变数据库中数据。,命令：,SELECTFROMWHERE,（3）DROP,TABLE,VIEW,INDEX,.,27,3,）、数据操纵语言（）,对已经存在的数据库进行元组的插入、删除、修改等操作。,命令：,INSERT,、,UPDATE,、,DELETE,4,）,、数据控制语言（,D,）,用来授予或收回访问数据库的某种特权、控制数据操纵事务的发生时间及效果、对数据库进行监视。,命令：,GRANT,、,REVOKE,、,COMMIT,、,ROLLBACK,28,Transact-SQL,（,T-SQL,）语言是微软公司对标准,SQL,语言的扩展，它是,SQL Server,与应用程序之间的语言，是,SQL Server,对应用程序开发的应用程序开发接口。,T-SQL,增强了,SQL,语言的功能，同时兼容,SQL,标准。,29,4.3 SQL,语言的数据类型,关系的所有属性都需要用数据类型加以描述，目的是为了给不同的数据分配合适的空间，确定合适的存储形式。,SQL Server,中的数据类型有：系统数据类型和用户自定义数据类型。,30,9/16/2024,31,1,、,SQL Server 2008,的系统数据类型,类别,数据类型定义符,精确数字,bigint,、,int,、,smallint,、,tinyint,、,bit,、,decimal,、,numeric,、,money,、,smallmoney,近似数字,float,、,real,日期和时间,dateime,、,smalldatetime,、,date,、,datetime2,、,datetimeoffset,、,time,（注意,SQL Server 200,中的日期和时间）,字符串,char,、,varchar,、,text,Unicode,字符串,nchar,、,nvarchar,、,ntext,二进制字符串,binary,、,varbinary,、,image,其他数据类型,cursor,、,sql_variant,、,table,、,timestamp,、,uniqueidentifier,、,xml,、,hierarchyid,31,主要数据类型有：,CHAR(n,) :,固定长度非,Unicode,数据的数据类型,NCHAR(n,),：,固定长度,Unicode,数据的数据类型,VARCHAR (n) :,可变长度且非,Unicode,的字符数据，最大长度为,n,，,varchar,是按字节存储的。,NVARCHAR (n) :,可变长,Unicode,字符数据，最大长度为,n,，,nvarchar,是按字符存储的。,32,INT,：,4,字节的整型,REAL,：,4,字节的浮点数,NUMERIC(n,s,),：,实型，最大精度,n,，,s,为小数位数,BINARY,：,最大,长度为,2G,字节的变长二进制,DATETIME,：,日期型,注意：,SQL Server 2000,与,SQL Server 2008,中的日期类型有很大的不同。,33,9/16/2024,34,2,、用户自定义数据类型,当创建用户自定义的数据类型时，必须提供,3,个参数：数据类型的名称、所基于的系统数据类型、数据类型是否允许空值。,sp_addtype,新数据类型,，,，系统数据类型,null_type,34,【,例,】,在数据库,Sales,中，创建用户自定义数据类型,agentName,，,agentName,基于的系统数据类型是变长为,8,的字符，不允许为空。,use sales,exec,sp_addtype,agentname,varchar(8),not null,go,35,9/16/2024,36,3,、表达式,表达式是标识符、值和运算符的组合，在应用时可以对其求值以获取结果。,表达式可以是常量、函数、列名、变量、子查询，还可以用运算符对这些实体进行组合以生成表达式。,比如,(price * 1.5),或,(price +,sales_tax,),。,36,9/16/2024,37,4,、运算符,运算符是一种符号，用来指定要在一个或多个表达式中执行的操作。,运算符,含义,算术运算符,+(,加,),、,-(,减,),、,*,(,乘,),、,/(,除,),、,%(,取模,),比较运算符,=,、,、,=,、,=,、,、,!=,、,!,赋值运算符,=,逻辑运算符,AND,、,OR,、,NOT,37,5,、,SQL,语句格式中使用的,符号,大括号, ,：表示,其中的内容可有多个用竖线分隔的选项，用户必须选择其中一项。,方括号, ,：,可选项，用户根据需要选用。,竖线,|,：表示参数之间,或,的关系。,省略号,“,”,：,表示重复前面的语法单元。,尖括号,：,表示下面有子句定义。,38,4.4 SQL,数据定义语言,DDL,4.4.1,数据库的定义、修改和删除,4.4.2,基本表的定义、修改和删除,4.4.3,索引的定义、修改和删除,39,SQL,数据定义功能如下表所示,：,40,4.4.1,数据库的创建、修改和删除,在建立用户逻辑组件（如,table,）之前必须首先建立数据库。,建立数据库的最实质性任务是向,OS,申请用来存储数据库数据的物理磁盘空间。存储空间以操作系统文件的方式体现。,不同的,DBMS,建立数据库的命令有些区别，本课程是针对,SQL,Server,进行讲解的。包括：,创建数据库、删除数据库、修改数据库,41,三、 创建数据库,Enterprise Manager,中,创建数据库,在,Query Analyzer,中用,Transact_SQL,语句创建数据库,用户必须拥有创建数据库的权限才能在,SERVER,端创建数据库。在,SQL Server 2000,为中创建数据库的方法有：,42,1,、在企业管理器中创建数据库,进入,Enterprise Manager,界面后：,展开并,选择,服务器,组,展开并,选择要建立数据库的,服务器,选中“,数据库,”,按右键选择“,新建数据库,”，或选择“,操作,/,新建数据库,”,43,数据库文件位置、大小,文件增长信息,选择文件位置,44,日志文件位置、大小,文件增长信息,选择文件位置,45,2,、创建数据库的,SQL,语句,CREATE DATABASE,database_name,ON PRIMARY,n, , ,n,LOG ON, ,n , FOR LOAD FOR ATTACH ,: =,(, NAME = logical_file_name, ,FILENAME = ,os_file_name, ,SIZE = size,MAXSIZE = max_size, UNLIMITED ,FILEGROWTH = growth_increment,),n,: =,FILEGROUP,filegroup_name,n,46,例,CREATE DATABASE,my ON,PRIMARY,(,NAME =,my_data, FILENAME =D:test,my.mdf,SIZE = 10 MB, -10 MB,的主要数据库文件,MAXSIZE = 15 MB, FILEGROWTH = 20 % ),LOG ON,(NAME =,my_log,FILENAME =D:test,my.ldf,SIZE = 3 MB , -3 MB,的日志文件,MAXSIZE = 5 MB , FILEGROWTH = 1 MB ),注意：,在建立数据库之前，为操作系统文件指定的目录路径必须存在。,47,例：,建立,jxgl1,数据库，主文件初始大小为,10MB,，最大为,50MB,，增量为,5MB,，日志文件初始大小为,5MB,，最大为,25MB,，增量为,5MB,create database jxgl1 On (name=jxgl1_dat,filename=D:jxgljxgl1.mdf,size=10,maxsize=50,filegrowth=5),log on (name=jxgl1_log,filename=e:zcxjxgl1.ldf,size=5MB,maxsize=25MB,filegrowth,=5MB),48,四、删除数据库,当确认一个数据库没有需要时，才可以删除该数据库。删除数据库的方法：,（,1,）使用企业管理器删除数据库,（,2,）,使用,T-SQL,语句删除数据库,Drop database,jxgl,49,五、 修改数据库,（,1,）数据库改名,（,2,）修改数据库选项,50,1,、数据库改名,使用系统存储过程,sp_renamedb,可以修改数据库的名称，语句格式：,sp_renamedb,old_dbname,new_dbname,例如：,sp_renamedb,zzg, ,zzg1,注意,：（,1,）,给数据库更名前，必须在企业管理器中设置该数据库为单用户状态（,只能有一个用户使用该数据库）。,（,2,）,只有属于,sysadmin,服务器角色的成员可以给数据库更名。,51,2,、修改数据库选项,可以在企业管理器中，也可以在查询分析器中修改数据库的属性、添加数据库数据文件、改变数据库大小等。,用企业管理器修改：,选中要修改属性的数据库，按右键，选择“,属性,”命令，或,选择“,操作,/,属性,”。,用,T-SQL,语句修改：,使用系统存储过程,sp_dboption,使用,ALTER database,命令（自学）,52,ALTER DATABASE,database_name,| MODIFY NAME =,new_database_name,| COLLATE,collation_name,| ,| ,其中：,:=,:=,:=,:=,:=,53,:=,:=, :=, :=, :=, :=, :=, :=, :=, :=,.,54,关系可以有三种类型：,（,1,）基本表,：实际存在的表，它是实际储存数据的逻辑表示。,（,2,）查询表：,查询结果相对应的表。,（,3,）视图表：,由基本表和其它视图导出的表，是虚表，不对应实际存储的数据。,4.4.2,表的创建、修改和删除,55,1,、表的创建,（,1,）使用企业管理器创建表：,选择要新建表的数据库，按右键，选择,新建,/,表,。,（,2,）使用,SQL,语句创建表,在,SQL Server 2000,为中创建表的方法有：,56,（,1,）使用企业管理器创建,管理约束,建立索引,生成,SQL,文本,57,基本表的定义格式,CREATE TABLE,表名,(,列名 数据类型,缺省值,列级约束,列名 数据类型,缺省值,列级约束,.,，,UNIQUE,（,列名,，列名,.,）,，,PRIMARY KEY,（,列名,，列名,）,，,FOREIGN KEY,（,列名,，列名,）,REFERENCES,表名（列名,，列名,）,CKECK (,条件,),),表级约束,58,建表时的注意事项：,（,1,）表名列名命名应用遵守命名规则。,（,2,）,一个用户中表名要唯一，一个表中列名要唯一。,（,3,）数据类型是,SQL Server,所提供的基本数据类型。,（,4,）定义完整性保证数据的正确和有效,59,建表时可定义有关的完整性约束条件，这些完整性约束条件被存入系统的,数据字典,中。,当用户操作表中数据时，由,DBMS,自动检查该操作是否违背完整性约束条件。,注意,:,如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上。如果是单个属性,则可以是表级完整性,也可以是列级完整性约束。,60,完整性约束的种类,DEFAULT:,列的缺省值；,NOT NULL,：,(,列值,),非空值；,UNIQUE,：（,列或列的组合）值唯一；,PRIMARY KEY,：（列或一组列）,主关键字（主码）；,FOREIGN KEY,：,外码定义；,REFERENCES,：,被引用的外部表的表名和列名 ；,CKECK,：,指定表约束条件。,保留字不能用作表名、列名等,61,列级完整性约束的种类,（,1,）,not null,和,null,约束；,（,2,）,primary key,（,一个表只能有一个主键）约束；,（,3,）,unique,：每个,unique,约束都生成一个惟一索引。,（,4,）,foreign key,：参照完整性约束。,（,5,）,default,：缺省值约束；,（,6,）,check,约束：自定义约束。,注意,:,完整性性约束在对数据库进行操纵时起作用,.,62,表级完整性约束的种类,（,1,）,primary key,约束：,当需要多列作为主关键字时需要表级的,primary key,约束,（,2,）,foreign key,：,外部关键字是复合属性时需要表级参照完整性约束。,（,3,）,check,：,当不同的列需要同时约束或有某种函数关系时，需要表级的完整性约束。,63,例：创建,DEPT(,系）,非空和主码约束,CREATE TABLE DEPT (,deptno,CHAR(8),Deptname,nvarchar(20),NOT NULL,/*,列级约束*,/,Deptphno,CHAR(12),Deptadd,varchar(40),PRIMARY KEY (,deptno,) /*,表级完整性,或,CREATE TABLE DEPT (,deptno,CHAR(8),PRIMARY KEY,/*,列级约束*,/,Deptname,nvarchar,NOT NULL,Deptphno,CHAR(12),Deptadd,varchar,(40),64,外码、,unique,约束的定义,create table class (,classno,char(8),primary key,/*,列级约束*,/,classname,char(10),U,nique,/*,列级约束*,/,deptno,char(8),FOREIGN,KEY(deptno,),REFERENCEs,dept,),/*,表级约束*,/,注意：所引用的字段必须是被参照表中的主码。,65,CREATE TABLE,student(sno,CHAR(8),PRIMARY KEY,Sname,nvarCHAR(20) NOT NULL,Ssex,CHAR(1), Sage INT,sclass,CHAR(8),FOREIGN,KEY(sclass,),REFERENCES,class(classno,),问：如果在,class,表中未指定,classno,是主码，则该语句的会执行成功吗？,66,constraint,的用法,create table,sc(sno,char(8),cno,varchar(8),sgrade,int,constraint,sno_cbo,primary key (,sno,cno,),或：,create table,grade(sno,varchar(4),cno,varchar(4),grade,int,primary key (,sno,cno,),67,check,约束、缺省值的定义,create table,职工,(,wno,varchar(5),foreign key references,仓库,(,仓库号,),eno,char(4),wage,int,check (wage=1000 and wage1000 and wage5000) default 1200,primary key (,e,no,),68,2.,表的,修改,（,1,）使用企业管理器修改：,选中要修改的表，按右键，选择,设计表,。,（,2,）使用,SQL,语句修改,69,（,2,） 基本表的修改,增加、删除、修改列定义或完整性约束条件：,ALTER TABLE,表名,ADD,子句,增加新的列定义或列说明,MODIFY,子句,修改表中列定义或列说明,（,SQL Server,中不能用,MODIFY,子句,),DROP,子句,删除表中的列或列说明,ADD,CONSTRAINT,子句,增加约束,DROP,CONSTRAINT,子句,删除约束,70,例,1:,增加外部关键字约束,ALTER TABLE,grade,ADD,FOREIGN KEY (,sno,) REFERENCES student (,sno,),alter table student add,constraint,stu_check3 check (,sno,like 0-9),例,2,增加字段,Alter table student,add phone varchar(20),71,例,3,修改字段,ALTER TABLE STUDENT,MODIFY,sage DATETIME,注意：在,SQL Server,中应为,:,Alter table student,alter column,sage DATETIME,MODIFY,在,SQL Server,不能实现。,72,例,4,删除完整性约束,(,知道完整约束名才能删除）,alter table student,drop,constraint,stu_check3,;,删除属性,(,只有部分,DBMS,允许删除表中的属性,）,例,5,删除列（有些,DBMS,是不允许删除列的）,alter table,职工,drop column,sar,;,73,3.,表的删除,命令格式：,DROP TABLE,table_name,删除表，将基本表的定义、表中的数据、索引，以及由此表导出的视图定义都被删除。,例如，删除表,DEPT,DROP TABLE,DEPT,74,4.4.3,索引,的创建、修改和删除,索引是对数据库表中一个或多个,列,的值进行排序的结构。提供指针以指向存储在表中指定列的数据值，然后根据指定的排序次序排列这些指针。,索引的作用：,有,助于提高查找速度。,数据查找时与使用书的目录很相似：通过搜索索引找到特定的值，然后跟随指针到达包含该值的行。,75,记录,1,590,记录,2,568,记录,3,565,记录,4,570,记录,5,595,记录,6,578,记录,7,550,记录,8,586,记录,9,569,记录,10,592,物理顺序,5,595,10,592,1,590,索引文件,逻辑顺序,记录,5,记录,10,记录,1,索引如图所示,76,例如，查询符合条件的元组,SELECT * FROM,student,WHERE,sname,=,李华,（,1,）无索引：对整个关系进行扫描，当一个关系的记录数很多时（假定包含,20000,个记录），要对每个记录逐一检查核对。查询速度很慢。,（,2,）以,sname,创建索引后：,系统将不再按条件逐条检查记录，而是利用索引来定位符合条件的元组。,77,唯一索引：,不允许两行具有相同的索引值。大多数数据库不允许在具有重复,键,值的表上创建唯一索引。 例如：以姓名建立唯一索引后不能有相同的姓名出现。,配合,NOT NULL,反映主关键字特性。,普通索引：,允许两行具有相同的索引值。,聚集索引：,表中各行的物理顺序与,键,值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。,索引类型,78,1,、索引的创建,（,1,）使用企业管理器创建,（,2,）使用,SQL,语句创建,79,(2),使用,SQL,语句创建索引,CREATE,UNIQUE/DISTINCT CLUSTERED,INDEX,索引名,ON,表名,（,列名,ASC/DESC ,，,列名,ASC/DESC.,）,可在表的一列或多列上建立索引，列名之间用逗号分开；并可指定索引中索引值的排列顺序，,ASC,为升序，,DESC,为降序,缺省值为升序。,UNIQUE,：,唯一索引。,CLUSTERED,：,聚集索引。,80,索引定义示例,例：创建唯一性索引,CREATE UNIQUE INDEX,sname,ON student (,sname,),例：,创建聚集索引,CREATE CLUSTERED INDEX,ginde,ON,sc(,sno,cno,),通常，,DBMS,会自动为主码建索引。,81,假如查询经常涉及到多个列值的检索条件，可在一个关系表上建立多个属性的索引，提高查询效率。否则会降低查询效率。,例：在两个或多个字段上建立索引,选课关系,sc,，,主码,(,sno,cno,),，,经常查询某学号的同学选修某课程的成绩。为两个属性建立索引的语句如下：,CREATE INDEX,Scidx,ON,sc(,sno,cno,);,82,2,、删除索引,语句格式：,DROP INDEX,索引名,例：将,TEACHER,上的索引,tnamex,删除。,DROP INDEX,tnamex,83,维护索引需要一定的时间：,当对基本表进行插入、删除记录操作时，同时要对索引进行维护。因此，索引的存在会影响基本表更新操作的效率。,要仔细考虑实际应用中修改与查询的频率，权衡建索引的利弊后再决定是否创建索引。,例如，若一关系的经常性操作是数据的修改，则不宜建索引。,使用索引的代价,84,一般来说，建索引有几项参考原则：,值得建索引：,记录有一定规模，而查询只局限于少数记录。或,索引列在,where,子句中使用频繁。,先装数据，后建索引：,这样可加快初始数据的录入。如果建表时就建索引，那么在输入初始数据时，每插入一个记录都要维护一次索引。,使用索引的原则,85,索引说明,(1),使用,SQL,语言，用户可以随时在基本表上建立索引、增加新索引、删除索引。,(2),用户只能建立、修改和删除索引，不能在数据操作中引用索引，因为在数据操作执行中如何使用索引完全由系统决定。从而保证了增加和删除索引时不必修改应用程序，支持数据的物理独立性。,86,练习题,1,创建下列各表,:,客户表,(,客户号 主码 第一位是字符,客户名称,联系人,地址,邮政编码,电话,char(12),产品表,(,产品号 主码,产品名称,规格说明 唯一,单价 大于,0);,订购单表,(,客户号 外码 非空,订单号 主码,订购日期 缺省为当前日期,getdate,(),订购明细表,(,订单号 外码,序号,产品号 外码,数量 大于,0,主码是,(,订单号,序号,),87,4.5 SQL,数据操纵语言,DML,对表的查找、插入、删除和更新操作，,SELECT,、,INSERT,、,DELETE,、,UPDATE,。,88,4.5.1,插入语句,INSERT,一个插入语句可以插入一个或多个元组。,（,1,）用,INSERT INTO VALUES(),语句每次只能插入一个元组,：,INSERT INTO,表名,（列名,，列名,）,VALUES,(,值,，值,.),（,2,）用子查询可插入多个元组值：,INSERT INTO,表名,（列名,，列名,）,(,子查询,),89,例：,插入单个元组,（,1,）按顺序给出表中每个列值，插入完整的新元组,insert into dept values,(,jsj,计算机学院,3372599,系办公室,),Insert into class values,(200503,05,计本,3,班,jsj,),insert into student,values(20050103,李,男,20,200501,）,（,2,）插入一个学生的部分列值,。,INSERT INTO,STUDENT,(,sno,sname,),VALUES( ,J20045, ,刘琉,),90,例：利用查询结果,插入多个元组,。,create table student1 (,sno,CHAR(8),Sname,CHAR(20) NOT NULL,classname,char(10),INSERT INTO,student1,(,sno,sname,classname,),( SELECT,sno,sname,classname,FROM,STUDENT,class,WHERE,student.,classno,=,class.,classno,),91,以下的,SQL,语句能否执行成功,为什么,?,insert into,dept(deptno,Deptphno,),values(jsj, 3372599),insert into dept,values(jsj,电气学院,3371018,系办公室,),insert into class,values (200504,05,计本,3,班,jsj,),insert into class,values (200504,05,计本,4,班,jsjxy,),92,4.5.2,更新语句,UPDATE,UPDATE,表名,SET,列名,=,表达式,|(,子查询,),列名,=,表达式,|(,子查询,).,WHERE,条件表达式,修改指定表中满足,WHERE,子句条件的元组,SET,子句用于指定修改方法，即用,的值取代相应属性列值。,WHERE,子句中的条件表达式给出被修改元组应满足的条件,如果省略,WHERE,子句，则表示要修改表中所有元组。,93,例：,UPDATE DEPT SET,deptphno,= 9888,WHERE,deptno,= CS,例：,UPDATE student SET age,=Sage+1,例：将,数据库,课程的成绩提高,10%,。,UPDATE SC SET,sgrade,=,sgrade,*1.1,WHERE,cno,=,c02,94,例,4,，将,200801,班学生的成绩提高,10%,。,UPDATE sc SET,sgrade,=,sgrade,*1.1,WHERE (SELECT,classno,FROM STUDENT WHERE,STUDENT.sno,=,SC.sno,)=200801,95,需要连接条件约束的更新,：,from where,：利用一个表的条件来更新另一个表，其条件类似于连接的条件。,例：将,08,级计本,1,班的学生成绩提高,10,update sc set,sgrade,=,sgrade,*1.1,from,sc,class,，,student,where,sc.sno,=,student.sno,and,student.classno,=,class.classno,and,classname,=,计本,1,班,and left(clsassno,4)=2008,96,带子查询的修改语句,子查询也可以嵌套在,UPDATE,语句中，用以构造执行修改操作的条件。,例：将计算机科学院全体学生的成绩置零。,UPDATE SC SET,Sgrade,=0 WHERE SNO IN (,SELECT SNO FROM Student,WHERE,classno,in select,classno,from class where,deptno,in select,deptno,from dept where,deptname,=,计算机,),97,4.5.3 DELETE,删除语句,DELETE,语句从表中删除一个或多个满足条件的元组。语句格式：,DELETE FROM,表名 ,WHERE,条件表达式,WHERE,子句中的条件表达式给出被删除元组应满足的条件；若不写,WHERE,子句，表示删除表中的所有元组，,但表的定义仍存在。,98,例：删除学生,李楷,的信息,.,DELETE FROM,STUDENT,WHERE,sname,= ,李楷,需要连接条件约束的删除,：,例如：删除选修了“,c01”,课程的记录,Delete from sc from course,where,o,=,o,and,cno,=c01,99,SELECT,语句是,T-SQL,中最重要的一条命令，是从数据库中获取信息的一个基本语句。有了这条语句，就可以实现从数据库的一个或多个表或视图中查询信息。,SELECT,语句的基本格式：,SELECT UNIQUE/DISTINCT,FROM,表名,，表名,., WHERE,条件表达式, GROUP BY,分组的列名, HAVING,对组再选择的,条件表达式,ORDER BY,排序列名,ASC/DESC,4.5.4,查询,100,整个,SELECT,语句的含义：,根据,WHERE,子句的条件表达式，从,FROM,子句指定的基本表或视图中找出满足条件的元组，再按,SELECT,子句中的目标列表达式，选出元组中的属性值形成结果表。,如果有,GROUP,子句,，则将结果按,的值进行分组，该属性列值相等的元组为一个组，每个组产生结果表中的一条记录。通常会在每组中作用集函数。如果,GROUP,子句带,HAVING,短语，则只有满足指定条件的组才给予输出。,如果有,ORDER,子句,，则结果表还要按,的值的升序或降序排序后，再输出。,101,下面将以“,test”,数据库为例来学习,select,命令，“,test”,数据库中包括：,Student(SNO,，,SName,，,SsEX,，,AGE,，,sclass,),；,2),Course(CNO,，,CNname,，,cteacher,CT,学分,),；,3) SC(SNO,，,CNO,，,SCORE),。,4,、,dept,（,DEPTNO,DEPTNAME,DEPTPHON),5,、,CLASS(CLASSNO,CLASSNAME,DEPTNO),102,1,、,查询指定列或全部列（,*的用法）,1,）查询指定的列,例：查询所有学生的,名字和学号,:,SELECT,sname,，,sno,FROM STUDENT,2,）,查询全部的列（可用*代替全部列）,例：查询,200801,班所有学生的信息,:,SELECT,*,FROM STUDENT,WHERE,classno,= ,200801,一、单表查询,103,2,、没有重复值的查询（,DISTINCT,的用法）,例,：,从表,sc,中,查所有选修过课的学生的学号,。,SELECT,DISTINCT,sno,FROM sc,3,、查询经过计算的值,SELECT,子句的,不仅可以是表中的属性列，也可以是有关表达式（属性列经过一定的计算后的结果）或常量。,例,查全体学生的姓名及其出生年份。,SELECT SName,2010-AGE FROM Student,104,4,、 结果排序,例，从表,student,中查询所有学生的编号并将结果排序。语句如下：,SELECT,sno,FROM student,ORDER BY,sno,缺省为升序,查询满足指定条件的元组可以通过,WHERE,子句实现。,5,、指定,WHERE,查询条件,例,6,：查询所有年龄在,21,岁以下的学生姓名及其年龄。,SELECT,SName, AGE FROM Student,WHERE AGE,=,!,!,；,Not (,上述比较运算符构成的比较关系表达式,),确定范围,BETWEEN AND,，,NOT BETWEEN .AND,确定集合,IN,，,NOT IN,字符匹配,LIKE,(_,或,%),，,NOT LIKE,(_,或,%),空值,IS NULL,，,IS NOT NULL,多重条件,AND,，,OR,，,NOT,106,1),、确定范围,例,查询年龄在,20,至,23,岁之间的学生的姓名和年龄。,SELECT,SN