第4章-关系数据库标准语言SQL

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,第,4,章,SQL Server,和,SQL,语言,本章主要内容,：,认识,SQL Server,及其基本使用；,数据库标准语言,SQL,及其使用,（建立数据库、查询数据、更新数据）。,1,4.1.1 SQL Server2000,概述,SQL Server,是,一个客户,/,服务器关系型数据库管理系统。使用,Transact-SQL,在,客户机和,SQL Server,之间发送请求。,4.1,SQL Server,简介,SQL Server2000,是,Microsoft,公司在,2000,年推出的,SQL Server,数据库管理系统。,2,企业版,(Enterprise Edition),支持,SQL Server2000,的,所有特性，可作为大型,Web,站点、企业的数据库服务器。,标准,版,(Standard,Edition),用于小型的工作组或部门。,4.1.1 SQL Server2000,概述,SQL Server2000,的常用版本有：,3,个人版,(,Personal Edition),用于单机系统或客户机。,开发版,(Developer Edition),：,用于程序员开发应用程序 ，需要,SQL Server2000,作为数据存储设备。,4.1.1 SQL Server2000,概述,4,SQL Server,的软,硬件平台要求,:,硬件要求,：,目前的计算机,CPU,、,内存和硬盘等都能满足,SQL Server2000,的,要求。,软件要求,：,SQL Server 2000,标准版和企业版,只能安装在服务器版的操作系统中。,Windows 2000,和,Windows XP,操作系统，只能安装,个人版和开发版,的,SQL Server 2000,。,4.1.1 SQL Server2000,概述,5,4.1.1 SQL Server2000,概述,版本或组件,操作系统要求,企业版,Windows Server,版,标准版,Windows Server,版,个人版,Windows,各个版本,开发版,Windows Server,版,Windows Workstation,版,Windows Professional,版,客户端,Windows,各个版本,表,4.1.1,不同版本,SQL Server 2000,对操作系统的要求,6,4.1.2 SQL Server2000,的主要组件,SQL Server2000,的安装：,见“,SQL Server 2000,安装,”课件,7,4.1.2 SQL Server2000,的主要组件,SQL Server2000,的主要组件,:,SQL Server,主要组件,8,1.,企业管理器,企业管理器是最主要的管理工具，可以完成绝大部分数据库管理工作。主要为：, 注册和管理,SQL Server,服务器, 创建和管理数据库和数据库对象, 备份和还原数据库和事务日志, 设置任务调度、警报和提供服务器的控制功能, 创建与管理用户账号和组,4.1.2 SQL Server2000,的,主要组件,9,2.,查询分析器,查询分析器是一个图形化的数据库编程接口。通过查询分析器 可以输入和执行各种,Transact-SQL,语句。,例如,：在“查询”窗口中输入语句：,SELECT,学号,姓名,性别,出生日期,FROM,学生,WHERE,“,班级,”,=,计,00-1,4.1.2 SQL Server2000,的,主要组件,10,4.1.2 SQL Server2000,的,主要组件,11,3.,系统数据库,SQL Server 2000,安装好后，系统自动创建,6,个系统数据库。它们存放在,Microsoft SQL Server,默认安装目录下的,MSSQLData,文件夹,中，数据库文件的扩展名为,.,mdf,，,数据库日志文件的扩展名为,.,ldf,。,4.1.2 SQL Server 2000,的,主要组件,12,(1) Master,数据库,Master,数据库记录了,SQL Server,系统的所有信息。这些信息包括所有的登录信息、系统设置信息、,SQL Server,的,初始化信息和其它系统数据库及用户数据库的相关信息。,Master,数据库对应的主数据文件是,Master.mdf,，,日志文件是,Mastlog.ldf,。,4.1.2 SQL Server2000,的主要组件,13,(2) Model,数据库,Model,数据库是所有用户数据库和,Tempdb,数据库的模板数据库。,当用户使用,CREATE DATABASE,命令建立新的数据库时，新数据库的第一部分总是通过复制,Model,数据库中的内容创建，剩余部分由空页填充。,Model,数据库对应的主数据文件是,Model.mdf,，,日志文件是,Modellog.ldf,。,4.1.2 SQL Server2000,的主要组件,14,(3),Msdb,数据库,Msdb,数据库是,代理数据库,，主要用于,SQL Server Agent,为其警报、任务调度和记录操作员的操作提供存储空间。,Msdb,数据库对应的主数据文件是,Msdb,.mdf,，,日志文件是,Msdb,log.ldf,。,4.1.2 SQL Server2000,的主要组件,15,(4),Tempdb,数据库,是一个临时数据库。,Tempdb,数据库由整个系统的所有数据库使用。,它为所有的临时表、临时存储过程及其它临时操作提供存储空间。,SQL Server,每次启动时，,Tempdb,数据库被重新建立。当用户与,SQL Server,断开联系时，其临时表和存储过程被自动删除。,对应的主数据文件是,Tempdb.mdf,，,日志文件是,Templog.ldf,。,4.1.2 SQL Server2000,的主要组件,16,(5) Pubs,数据库和,Northwind,数据库,Pubs,数据库,:,提供一个样本数据库作为学习工具,。,Northwind,数据库：提供一个样本数据库作为学习工具,。,4.1.2 SQL Server2000,的主要组件,17,4. SQL Server,服务管理器,服务管理器是在服务器端运行的程序，其界面如图所示。,4.1.2 SQL Server 2000,的,主要组件,服务管理器用来启动、暂停、继续和停止数据库服务器的实时服务。,18,在访问数据库之前，必须启动,SQL Server,数据库服务。,SQL Server,数据库服务的启动有两种方法,：,启动,Windows,时自动启动，可以通过服务管理器进行设置。,通过“开始,Microsoft SQL Server,服务管理器”进行启动。,4.1.2 SQL Server 2000,的,主要组件,19,4.1.3,创建数据库和基本表,1.,创建数据库,要创建数据库必须拥有创建数据库的权限。在初始情况下，只有系统管理员可以创建数据库。在数据库创建完成后，它的创建者自动成为其所有者。,20,4.1.3,创建数据库和基本表,创建数据库的工作主要是,:,确定数据库名称；,确定数据文件的名称、存放位置和增长信息等；,确定事务日志文件名称、存放位置和增长信息等。,21,创建数据库方法有三种,：,使用企业管理器创建数据库；,使用,Transact-SQL,语言创建,;,使用向导创建数据库（,略讲,）。,4.1.3,创建数据库和基本表,本节介绍使用企业管理器创建数据库。,22,使用企业管理器创建数据库,(,电脑操作演示，建立,student,数据库,),如下图,4.1.3,创建数据库和基本表,23,24,每个数据库最多可创建,200,万个基本表，用户创建基本表时，最多可以定义,1024,个字段。,有两种方法创建基本表：,a.,使用,Transact-SQL,创建基本表；,b.,使用企业管理器创建基本表。,2.,创建基本表,本节,介绍使用企业管理器创建基本表。,4.1.3,创建数据库和基本表,25,创建基本表的主要工作：,确定基本表名；,确定每个字段的名称、数据类型、字段宽度、是否可为空等；,确定每个表的主键。,(1),数据类型,常用数据类型见下表所示。,4.1.3,创建数据库和基本表,26,数据类型,说明,数据类型,说明,Bit,二进制位类型,Money,货币类型,Tinyint,无,符号单字节整型,Datetime,日期,/,时间型,Smallint,短,整型,2,个字节,Image,OLE,对象,Int,长整型,4,个字节,Varchar(n,),最长为,n,的可变长文本类型,Bigint,大,整型,8,个字节,Text(n,),长为,n,的可变长文本类型,长度最大,2,31,-1,Decimal,Numeric,数值类型,(,固定精度和小数位,),Char(n),固定长度为,n,的文本类型,Real,单精度浮点型,4,字节,Binary,二进制类型,float,双精度浮点型,8,字节,VarBinary,可变长二进制类型,表,4.1.2,常用的数据类型,27,(2),在企业管理器中创建基本表,选定数据库，在其下“表”项上打开快捷菜单,(,如下图,),选择“新建表”菜单命令，进入其编辑器。,如图所示,.,4.1.3,创建数据库和基本表,28,三、 创建基本表,29,4.1.4,管理数据库和基本表,1.,删除数据库和基本表,选择要删除的数据库或表，按键盘上的“删除”键，或打开快捷菜单，选择“删除”菜单命令即可。（,电脑操作演示,）,2.,修改数据库和基本表结构,（,电脑操作演示,）见下页图。,30,4.1.4,管理数据库和基本表,“设计表”项是修改表结构,“打开表”项是浏览表数据,31,3.,备份和恢复数据库,(1),备份数据库：,为数据库建立备份，以便数据库发生故障后，用备份恢复数据库,。,选择要备份的数据库,，然后，,方法,1,：打开快捷菜单，选择“所有任务”菜单项，再选择“备份数据库” 菜单命令。,方法,2,：,选择“工具,/,备份数据库”菜单命令。,(2),恢复数据库：,（,电脑操作演示,）,4.1.4,管理数据库和基本表,32,4.1.4,管理数据库和基本表,33,4.,导入和导出数据,数据的导入和导出,指将其他,SQL Server,服务器上数据库或其他数据源数据,(,如,Excel,工作表,),导入到本地,SQL Server,服务器上的数据库中,或反之。,数据的导入和导出,利用,SQL Server,提供的数据转换服务,DTS,来完成。,例如,：将教学数据库数据导出到,Excel,表格中,(,电脑操作演示,),。,4.1.4,管理数据库和基本表,34,4.1.4,管理数据库和基本表,35,4.1.4,管理数据库和基本表,36,4.1.4,管理数据库和基本表,37,4.1.4,管理数据库和基本表,38,4.2 SQL,语言,SQL,是,Structured Query Language,的缩写,它是,1974,年由,Boyce,和,Chamberlin,提出的，并在,IBM,公司,San Jose,实验室研制的关系数据库实验系统,SYSTEM R,上实现。,由于,SQL,功能丰富、语言简洁、易学易用，很受用户欢迎。为此，,80,年代，计算机商家们纷纷推出各自支持,SQL,的软件或者与,SQL,的接口软件。现在,SQL,已是世界上最广泛使用的数据库语言。,39,4.2 SQL,语言,SQL,语言由,三部分组成，包括,数据定义语言,DDL,、数据操作语言,DML,、数据控制语言,DCL,。也就是,SQL,语言具有数据查询、数据操纵、数据定义和数据控制功能。其中最重要的是数据查询功能。,SQL,语言是一种非过程化的语言,。用,SQL,语句解决一个问题时，用户只需要告诉系统要干什么就可以了，实现过程是由系统自动完成的。另外，,SQL,语言还具有面向集合操作，语法简单，易学易用等特点。,40,分类,命令动词,功能,数据查询,SELECT,查询数据,数据定义,CREATE,建立基本表、视图等,DROP,建立基本表、视图等,ALTER,修改基本表、视图等,数据操纵,INSERT,插入记录,UPDATE,修改记录,DELETE,删除记录,数据控制,GRANT,授权,REVOKE,回收权限,表,4.2.1,常用的,SQL,命令,4.2 SQL,语言,41,4.2.1,数据定义,SQL,的数据定义功能主要包括三部分：基本表的定义,、,视图的定义和索引的定义。,42,1.,CREATE TABLE,语句,表定义,CREATE TABLE,表名,(,列名,1,数据类型,列级完整性约束条件,，列名,2,数据类型,列级完整性约束条件, ,，表级完整性约束条件, ),定义表时，要给出,，构成表的每个字段名及其数据类型和长度。主码、参照完整性定义、用户定义完整性。,4.2.1,数据定义,43,例,：建立学生教学数据库的,3,个表。,S(Sno,Sname,Ssex,Sage,Sdept,),SC(Sno,Cno,Grade,),（,仅以此表,举例,）,C(Cno,Cname,Pcno,Credit,),4.2.1,数据定义,44,CREATE TABLE SC,(,Sno,CHAR(6) NOT NULL,Cno,CHAR(6) NOT NULL,Grade,Smallint,CHECK(Grade BETWEEN 0 AND 100) ,CONSTRAINT,pk,PRIMARY,KEY(Sno,Cno,),FOREIGN,KEY(Sno,) REFERENCES,S(Sno,),FOREIGN,KEY(Cno,) REFERENCES,C(Cno,) ),4.2.1,数据定义,45,说明：,(1),字段级完整性约束,NOT NULL,(,不允许取空值,),和,NULL,(,允许取空值,),PRIMARY KEY,(,声明字段为主键,),UNIQUE(,唯一性约束,字段取值不能重复,),FOREIGN KEY:,参照完整性的外,键,约束。,DEFAULT,：,为,字段指定默认值。,CHECK,子句,：实现字段级用户定义约束。,4.2.1,数据定义,46,(2),表级完整性约束,PRIMARY KEY (,声明字段为主键,),FOREIGN KEY,:,参照完整性的外键约束。,CHECK,子句：实现用户定义完整性约束。,4.2.1,数据定义,47,CREATE TABLE SC,(,Sno,CHAR(6) NOT NULL FOREIGN KEY REFERENCES,S(Sno,),Cno,CHAR(6) NOT NULL,Grade,Smallint,default 0,CONSTRAINT k1 CHECK(Grade BETWEEN 0 AND 100) ,PRIMARY,KEY(Sno,Cno,),FOREIGN,KEY(Cno,) REFERENCES,C(Cno,) ),注,：完整性约束可以使用,CONSTRAINT,关键字命名。否则系统将自动为每个约束命名。,4.2.1,数据定义,48,2. ALTER TABLE,语句,表的修改,ALTER TABLE,表名,ADD,新列名 数据类型 完整性约束,DROP,完整性约束名,DROP COLUMN,列,名,ALTER COLUMN,列名 数据类型,新增的属性不能定义为,“,NOT NULL,”,因为关系的原来元组在新列上的值取空值。,4.2.1,数据定义,49,例,1,：向,”,学生”表增加“入学时间”字段,，,其数据类型为日期型；将“出生日期”的数据类型改为“日期型”；删除“所属系”字段 。,ALTER TABLE,学生,ADD,入学时间,Datetime,ALTER TABLE,学生,ALTER COLUMN,出生日期,Datetime,ALTER TABLE,学生,DROP COLUMN,所属系,4.2.1,数据定义,50,3. DROP TABLE,语句,删除基本表,当某个表不再需要时，可以删除。,一般格式：,DROP TABLE,表名,例：,DROP TABLE S,4.2.1,数据定义,51,4.,建立索引,当数据表中记录很多时，查询速度成为一个突出的问题。若要提高查询速度，可以在涉及的字段上建立索引。 建立索引使用,CREATE,命令。,语法格式为,：,CREATE UNIQUE CLUSTER,INDEX ON , ,4.2.1,数据定义,52,说明：, 若查询中涉及到索引字段时，系统会自动选择合适的索引，大大提高查询速度。, 索引一经建立，就由系统使用和维护它，不需要用户干预。当增加、修改、删除数据表中记录时，系统会自动维护索引。, 维护索引需要花费一些时间，故建立多少索引，需要权衡后处理。,UNIQUE,：每一个索引值只对应惟一的数据记录。,4.2.1,数据定义,53,CLUSTER,：建立的索引是聚簇索引。所谓聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织。在一个表上只能建立聚簇索引，因此应在最常查询的列上建立聚簇索引以提高查询效率。, 次序是指,ASC,或,DESC,，默认值为,ASC,。,5,删除索引,DROP INDEX ,4.2.1,数据定义,54,说明：,1,一个,SELECT,语句可由五个子句组成，但只有前两个子句是必不可少的，其它子句可以缺省。,4.2.2 SQL,查询,SELECT,ALL|DISTINCT ,目标列表,FROM,表名,1 ,，表名,2,WHERE,条件表达式,1 ,GROUP BY,列名,1 ,HAVING,条件表达式,2 ,ORDER BY,列名,2 ASC | DESC ,；,55,2,SELECT,子句告诉系统查找什么数据。所要的数据可以是表的属性、表达式或常量。,3,FROM,子句告诉系统从哪里查找数据。即给出查询涉及的一个或多个表,(,基本表,视图,查询表,),。,4,WHERE,子句给出查询条件。只有满足条件的元组才是查询所要的。,4.2.2 SQL,查询,56,5,GROUP BY,子句用于分组查询，它将结果按,的值进行分组，每组的元组在该属性列上值相等。,HAVING,短语用来指定组应该满足的条件，只有满足条件的组才予输出。每组的数据作为查询结果的一行，有多少组，查询结果表就有多少行。,6,如果有,ORDER,子句，则结果表还要按,的值的升序或降序排序。,4.2.2 SQL,查询,57,以,教学数据库,为例介绍该命令的应用。,学生,(,学号,姓名,性别,出生日期,班级,所属系, ),课程,(,课号,课,名,学分,学时,开课系,),选课,(,学号,课号,成绩, ),4.2.2 SQL,查询,58,1.,选择记录,例,1,查询年龄为空值的学生信息,。,例,2,查询在,19781983,年之间出生的学生信息,(,不在,之间,),SELECT,*,FROM,学生,WHERE YEAR(,出生日期,),BETWEEN,1978,AND,1982,SELECT * FROM,学生,WHERE YEAR(GETDATE()-YEAR(,出生日期,),IS NULL,Getdate,():,系统函数，返回系统当前日期。,YEAR(,日期,),：系统函数，返回“日期”中的年份。,59,2.,选择字段,例,3,查询选修了,001,课且有成绩的学生学号。,SELECT,学号,FROM,选课,WHERE,课号,=001,And,成绩,IS NOT NULL,例,4,查询有不及格成绩的学生学号。,SELECT,DISTINCT,学号,FROM,选课,WHERE,成绩, 20,ORDER BY,学号,例,8,查询学生选课信息，将结果按学号升序排列，学号相同时再按照成绩降序排列显示。,SELECT * FROM,选课,ORDER BY,学号,成绩,DESC,62,合计函数,SELECT,子句中的目标列可以是合计,函数，用于数据统计工作，,主要有：,COUNT,（,*,）,计算元组个数,COUNT,（,DISTINCT | ALL,）,计算一列中值的个数,SUM,（,DISTINCT | ALL ,）,计算一列值的总和,AVG,（,DISTINCT | ALL ,）,计算一列值的平均值,MAX,（,）,求一列值中的最大值,MIN,（,）,求一列值中的最小值,4.,分组,63,例,9,查询选修了课程的学生人数。,SELECT COUNT( DISTINCT,学号,),FROM,选课,例,10,查询选,002,课的学生人数以及该课的最高、最低成绩、平均成绩。,SELECT,COUNT( *),AS,人数, MAX(,成绩,) AS,最高成绩, MIN(,成绩,) AS,最低成绩, AVG(,成绩,),AS,平均成绩,FROM,选课,WHERE,课号,=,00,0,2,4.,分组,64,实际中我们常常会遇到这样的查询要求，如“查询每个部门的工资总和、平均工资和职工总数”等等。对这类查询，我们可以使用,GROUP BY,分组子句，按“部门”分组，同一部门的职工在一组，求出各部门的统计值。,例,11,计算各系年龄大于,22,岁的学生的人数。,SELECT,所属系, COUNT(*) AS,人数,FROM,学生,WHERE,YEAR(GETDATE()-,YEAR(,出生日期,)=22,GROUP BY,所属系,4.,分组,65,例,1,2,找出学生人数不足,10,人的系及其相应的总人数。,SELECT,所属系，,COUNT(*) AS,人数,FROM,学生,GROUP BY,所属系,HAVING COUNT(*)=3,4.,分组,66,当,查询涉及两个或两个以上的表时，要用连接查询。连接分为内连接和外连接。,5.,连接查询,例,1,4,查询选修,0002,课成绩为,90,分以上的学生的学号和姓名。,SELECT,学生,.,学号,姓名,FROM,学生,WHERE,学生,.,学号,=,选课,.,学号,AND,选课,.,课号,=,0002,AND,成绩,=90,67,例,1,5,查询至少选修了,0001,、,0002,两门课的学生学号。,SELECT SC1.,学号,FROM,选课,AS SC1,选课,AS,SC2,WHERE SC1.,学号,=SC2.,学号,AND,SC1.,课号,=,000,1,AND SC2.,课号,=,000,2,注意,：自身连接时，要为表起别名。,AS,可省写。,5.,连接查询,68,4.2.3,数据更新语句,SQL,中数据更新包括,插入数据,、,修改数据,和,删除数据,三条语句。与查询语句,SELECT,不同，数据更新语句一次只能对一个表中数据进行更新操作。,69,1.,插入数据（,INSERT,）,有,两种格式：,格式,1,：,INSERT INTO,表名,(,字段,1,，字段,2),VALUES,(,常量,1,，常量,2),格式,2,：,INSERT INTO,表名,(,字段,1,，字段,2),子查询,4.2.3,数据更新语句,70,例,16,将计,00-2,班所有学生选修“,0001”,课信息插入选课表。,(,对于必修课，一个班级的全部学生都选，可成批插入选课记录,),INSERT INTO,选课,(,学号,课号,),SELECT,学生,.,学号,0001,FROM,学生,WHERE,班级,=,计,00-2,4.2.3,数据更新语句,71,2.,删除数据（,DELETE,）,格式：,DELETE FROM,表,1,WHERE,条件,例,17,删除选修,0009,课的学生选课记录。,DELETE FROM,选课,WHERE,课号,=,0009,4.2.3,数据更新语句,72,3,.,更新数据（,UPDATE,）,格式：,UPDATE,表名,SET,字段,1=,表达式,1, ,字段,2 =,表达式,2 ,WHERE,条件,例,18,将学生,990101,的姓名改为,王平,。,UPDATE,学生,SET,姓名,=,王平,WHERE,课号,=,990101,4.2.3,数据更新语句,73,视图的主要作用,(1),可以满足不同用户的需求。,(2),提供一种安全机制，将用户限制到指定的数据集中，数据集的内容由定义视图的查询产生。,(3),保证了基本表数据和应用程序间的数据独立性。,4.2.4,创建视图,74,2.,创建视图,视图由子查询来定义的，子查询的结果表就是视图表。,格式为：,CREATE VIEW,(),AS,4.2.4,创建视图,75,4.2.4,创建视图,说明：,给出视图的列名。它是可选项，若给出此项，则表示给子查询的,SELECT,子句所选择的数据重新起个名字作为视图的列名，它们依次对应；当省略时，表示视图的列名取子查询中,SELECT,子句给出的列名。,76,4.2.4,创建视图,例,1,建立计算机系的学生视图表,CS_S,。,CREATE VIEW CS_S AS,SELECT,学号,姓名,性别,出生日期,班级,FROM,学生,WHERE,所属系,like “,计算机系”,例,2,建立一个反映学生学号、姓名、所选课程名和成绩的视图,S_sc_view,。,create view s_sc_view,as,select,学生,.,学号,姓名,选课,.,课号,课名,成绩,from,学生,选课,课程,WHERE,学生,.,学号,=,选课,.,学号,and,选课,.,课号,=,课程,.,课号,77,4.2.4,创建视图,3.,删除视图,DROP VIEW,视图名,4.,视图的使用,视图也是表，一旦建立好就可以象基本表一样使用。,78,作业,实验四,本章习题,79,联系我们,：,同济大学计算机系计算机基础教研室,Http:/,谢谢！,80,