数据库技术与应用第4章-关系数据库标准语言SQL.ppt

《数据库技术与应用第4章-关系数据库标准语言SQL.ppt》由会员分享，可在线阅读，更多相关《数据库技术与应用第4章-关系数据库标准语言SQL.ppt（80页珍藏版）》请在三一办公上搜索。

1、1,第4章 SQL Server和SQL语言,本章主要内容：认识SQL Server 及其基本使用；数据库标准语言SQL及其使用（建立数据库、查询数据、更新数据）。,2,4.1.1 SQL Server2000概述,SQL Server 是一个客户/服务器关系型数据库管理系统。使用Transact-SQL在客户机和SQL Server 之间发送请求。,4.1 SQL Server简介,SQL Server2000是Microsoft 公司在2000年推出的SQL Server数据库管理系统。,3,企业版(Enterprise Edition)支持SQL Server2000的所有特性，可作为大

2、型Web站点、企业的数据库服务器。标准版(Standard Edition)用于小型的工作组或部门。,4.1.1 SQL Server2000概述,SQL Server2000的常用版本有：,4,个人版(Personal Edition)用于单机系统或客户机。开发版(Developer Edition)：用于程序员开发应用程序，需要SQL Server2000作为数据存储设备。,4.1.1 SQL Server2000概述,5,SQL Server的软硬件平台要求:硬件要求：目前的计算机CPU、内存和硬盘等都能满足SQL Server2000的要求。软件要求：SQL Server 2000标准

3、版和企业版只能安装在服务器版的操作系统中。Windows 2000和Windows XP操作系统，只能安装个人版和开发版的SQL Server 2000。,4.1.1 SQL Server2000概述,6,4.1.1 SQL Server2000概述,表4.1.1 不同版本SQL Server 2000对操作系统的要求,7,4.1.2 SQL Server2000 的主要组件,SQL Server2000的安装：见“SQL Server 2000安装”课件,8,4.1.2 SQL Server2000 的主要组件,SQL Server2000的主要组件:,9,1.企业管理器企业管理器是最主要的

4、管理工具，可以完成绝大部分数据库管理工作。主要为：注册和管理SQL Server服务器 创建和管理数据库和数据库对象 备份和还原数据库和事务日志 设置任务调度、警报和提供服务器的控制功能 创建与管理用户账号和组,4.1.2 SQL Server2000的主要组件,10,2.查询分析器查询分析器是一个图形化的数据库编程接口。通过查询分析器 可以输入和执行各种Transact-SQL语句。例如：在“查询”窗口中输入语句：SELECT 学号,姓名,性别,出生日期 FROM 学生 WHERE“班级”=计00-1,4.1.2 SQL Server2000的主要组件,11,4.1.2 SQL Server

5、2000的主要组件,12,3.系统数据库SQL Server 2000安装好后，系统自动创建6个系统数据库。它们存放在Microsoft SQL Server默认安装目录下的MSSQLData文件夹中，数据库文件的扩展名为.mdf，数据库日志文件的扩展名为.ldf。,4.1.2 SQL Server 2000的主要组件,13,(1)Master数据库Master数据库记录了SQL Server系统的所有信息。这些信息包括所有的登录信息、系统设置信息、SQL Server的初始化信息和其它系统数据库及用户数据库的相关信息。Master数据库对应的主数据文件是Master.mdf，日志文件是Mas

6、tlog.ldf。,4.1.2 SQL Server2000 的主要组件,14,(2)Model数据库Model数据库是所有用户数据库和Tempdb数据库的模板数据库。当用户使用CREATE DATABASE命令建立新的数据库时，新数据库的第一部分总是通过复制Model数据库中的内容创建，剩余部分由空页填充。Model 数据库对应的主数据文件是Model.mdf，日志文件是Modellog.ldf。,4.1.2 SQL Server2000 的主要组件,15,(3)Msdb数据库Msdb数据库是代理数据库，主要用于SQL Server Agent,为其警报、任务调度和记录操作员的操作提供存储空

7、间。Msdb数据库对应的主数据文件是Msdb.mdf，日志文件是Msdblog.ldf。,4.1.2 SQL Server2000 的主要组件,16,(4)Tempdb数据库是一个临时数据库。Tempdb数据库由整个系统的所有数据库使用。它为所有的临时表、临时存储过程及其它临时操作提供存储空间。SQL Server每次启动时，Tempdb 数据库被重新建立。当用户与SQL Server断开联系时，其临时表和存储过程被自动删除。对应的主数据文件是Tempdb.mdf，日志文件是Templog.ldf。,4.1.2 SQL Server2000 的主要组件,17,(5)Pubs数据库和Northw

8、ind数据库Pubs数据库:提供一个样本数据库作为学习工具。Northwind数据库：提供一个样本数据库作为学习工具。,4.1.2 SQL Server2000 的主要组件,18,4.SQL Server 服务管理器服务管理器是在服务器端运行的程序，其界面如图所示。,4.1.2 SQL Server 2000的主要组件,服务管理器用来启动、暂停、继续和停止数据库服务器的实时服务。,19,在访问数据库之前，必须启动SQL Server数据库服务。SQL Server数据库服务的启动有两种方法：启动Windows时自动启动，可以通过服务管理器进行设置。通过“开始Microsoft SQL Serv

9、er服务管理器”进行启动。,4.1.2 SQL Server 2000的主要组件,20,4.1.3 创建数据库和基本表,1.创建数据库要创建数据库必须拥有创建数据库的权限。在初始情况下，只有系统管理员可以创建数据库。在数据库创建完成后，它的创建者自动成为其所有者。,21,4.1.3 创建数据库和基本表,创建数据库的工作主要是:确定数据库名称；确定数据文件的名称、存放位置和增长信息等；确定事务日志文件名称、存放位置和增长信息等。,22,创建数据库方法有三种：使用企业管理器创建数据库；使用Transact-SQL语言创建;使用向导创建数据库（略讲）。,4.1.3 创建数据库和基本表,本节介绍使用企

10、业管理器创建数据库。,23,使用企业管理器创建数据库(电脑操作演示，建立student数据库)如下图,4.1.3 创建数据库和基本表,24,25,每个数据库最多可创建200万个基本表，用户创建基本表时，最多可以定义1024个字段。有两种方法创建基本表：a.使用Transact-SQL创建基本表；b.使用企业管理器创建基本表。,2.创建基本表,本节介绍使用企业管理器创建基本表。,4.1.3 创建数据库和基本表,26,创建基本表的主要工作：确定基本表名；确定每个字段的名称、数据类型、字段宽度、是否可为空等；确定每个表的主键。,(1)数据类型常用数据类型见下表所示。,4.1.3 创建数据库和基本表,

11、27,表4.1.2 常用的数据类型,28,(2)在企业管理器中创建基本表选定数据库，在其下“表”项上打开快捷菜单(如下图),选择“新建表”菜单命令，进入其编辑器。如图所示.,4.1.3 创建数据库和基本表,29,三、创建基本表,30,4.1.4 管理数据库和基本表,1.删除数据库和基本表选择要删除的数据库或表，按键盘上的“删除”键，或打开快捷菜单，选择“删除”菜单命令即可。（电脑操作演示）,2.修改数据库和基本表结构（电脑操作演示）见下页图。,31,4.1.4 管理数据库和基本表,32,3.备份和恢复数据库(1)备份数据库：为数据库建立备份，以便数据库发生故障后，用备份恢复数据库。选择要备份的

12、数据库，然后，方法1：打开快捷菜单，选择“所有任务”菜单项，再选择“备份数据库”菜单命令。方法2：选择“工具/备份数据库”菜单命令。(2)恢复数据库：（电脑操作演示）,4.1.4 管理数据库和基本表,33,4.1.4 管理数据库和基本表,34,4.导入和导出数据数据的导入和导出指将其他SQL Server服务器上数据库或其他数据源数据(如Excel工作表)导入到本地SQL Server服务器上的数据库中,或反之。数据的导入和导出利用SQL Server提供的数据转换服务DTS来完成。例如：将教学数据库数据导出到Excel表格中(电脑操作演示)。,4.1.4 管理数据库和基本表,35,4.1.4

13、 管理数据库和基本表,36,4.1.4 管理数据库和基本表,37,4.1.4 管理数据库和基本表,38,4.1.4 管理数据库和基本表,39,4.2 SQL语言,SQL是Structured Query Language的缩写,它是1974年由Boyce和Chamberlin提出的，并在IBM公司San Jose 实验室研制的关系数据库实验系统SYSTEM R上实现。由于SQL功能丰富、语言简洁、易学易用，很受用户欢迎。为此，80年代，计算机商家们纷纷推出各自支持SQL的软件或者与SQL的接口软件。现在SQL已是世界上最广泛使用的数据库语言。,40,4.2 SQL语言,SQL语言由三部分组成，

14、包括数据定义语言DDL、数据操作语言DML、数据控制语言DCL。也就是SQL语言具有数据查询、数据操纵、数据定义和数据控制功能。其中最重要的是数据查询功能。SQL语言是一种非过程化的语言。用SQL语句解决一个问题时，用户只需要告诉系统要干什么就可以了，实现过程是由系统自动完成的。另外，SQL语言还具有面向集合操作，语法简单，易学易用等特点。,41,表4.2.1 常用的SQL命令,4.2 SQL语言,42,4.2.1 数据定义,SQL的数据定义功能主要包括三部分：基本表的定义、视图的定义和索引的定义。,43,1.CREATE TABLE 语句表定义,CREATE TABLE 表名(列名1 数据类

15、型 列级完整性约束条件，列名2 数据类型 列级完整性约束条件，表级完整性约束条件),定义表时，要给出，构成表的每个字段名及其数据类型和长度。主码、参照完整性定义、用户定义完整性。,4.2.1 数据定义,44,例：建立学生教学数据库的3个表。S(Sno,Sname,Ssex,Sage,Sdept)SC(Sno,Cno,Grade)（仅以此表举例）C(Cno,Cname,Pcno,Credit),4.2.1 数据定义,45,CREATE TABLE SC(Sno CHAR(6)NOT NULL,Cno CHAR(6)NOT NULL,Grade Smallint CHECK(Grade BETWE

16、EN 0 AND 100),CONSTRAINT pk PRIMARY KEY(Sno,Cno),FOREIGN KEY(Sno)REFERENCES S(Sno),FOREIGN KEY(Cno)REFERENCES C(Cno),4.2.1 数据定义,46,说明：,(1)字段级完整性约束 NOT NULL(不允许取空值)和NULL(允许取空值)PRIMARY KEY(声明字段为主键)UNIQUE(唯一性约束,字段取值不能重复)FOREIGN KEY:参照完整性的外键约束。DEFAULT：为字段指定默认值。CHECK子句：实现字段级用户定义约束。,4.2.1 数据定义,47,(2)表级完整性

17、约束 PRIMARY KEY(声明字段为主键)FOREIGN KEY:参照完整性的外键约束。CHECK子句：实现用户定义完整性约束。,4.2.1 数据定义,48,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),注：完整性约束可

18、以使用CONSTRAINT关键字命名。否则系统将自动为每个约束命名。,4.2.1 数据定义,49,2.ALTER TABLE语句表的修改,ALTER TABLE 表名 ADD 新列名 数据类型 完整性约束 DROP 完整性约束名 DROP COLUMN 列名 ALTER COLUMN 列名 数据类型,新增的属性不能定义为“NOT NULL”因为关系的原来元组在新列上的值取空值。,4.2.1 数据定义,50,例1：向”学生”表增加“入学时间”字段，其数据类型为日期型；将“出生日期”的数据类型改为“日期型”；删除“所属系”字段。,ALTER TABLE 学生 ADD 入学时间 DatetimeAL

19、TER TABLE 学生 ALTER COLUMN 出生日期 DatetimeALTER TABLE 学生 DROP COLUMN 所属系,4.2.1 数据定义,51,3.DROP TABLE语句删除基本表当某个表不再需要时，可以删除。一般格式：DROP TABLE 表名例：DROP TABLE S,4.2.1 数据定义,52,4.建立索引 当数据表中记录很多时，查询速度成为一个突出的问题。若要提高查询速度，可以在涉及的字段上建立索引。建立索引使用CREATE命令。语法格式为：CREATE UNIQUE CLUSTER INDEX ON,4.2.1 数据定义,53,说明：若查询中涉及到索引字段

20、时，系统会自动选择合适的索引，大大提高查询速度。索引一经建立，就由系统使用和维护它，不需要用户干预。当增加、修改、删除数据表中记录时，系统会自动维护索引。维护索引需要花费一些时间，故建立多少索引，需要权衡后处理。UNIQUE：每一个索引值只对应惟一的数据记录。,4.2.1 数据定义,54,CLUSTER：建立的索引是聚簇索引。所谓聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织。在一个表上只能建立聚簇索引，因此应在最常查询的列上建立聚簇索引以提高查询效率。次序是指ASC或DESC，默认值为ASC。5删除索引 DROP INDEX,4.2.1 数据定义,55,说明：1一个SELECT语

21、句可由五个子句组成，但只有前两个子句是必不可少的，其它子句可以缺省。,4.2.2 SQL查询,SELECT ALL|DISTINCT 目标列表FROM 表名1，表名2 WHERE 条件表达式1 GROUP BY 列名1 HAVING 条件表达式2 ORDER BY 列名2 ASC|DESC；,56,2SELECT子句告诉系统查找什么数据。所要的数据可以是表的属性、表达式或常量。3FROM子句告诉系统从哪里查找数据。即给出查询涉及的一个或多个表(基本表,视图,查询表)。4WHERE子句给出查询条件。只有满足条件的元组才是查询所要的。,4.2.2 SQL查询,57,5GROUP BY子句用于分组查

22、询，它将结果按的值进行分组，每组的元组在该属性列上值相等。HAVING短语用来指定组应该满足的条件，只有满足条件的组才予输出。每组的数据作为查询结果的一行，有多少组，查询结果表就有多少行。6如果有ORDER子句，则结果表还要按的值的升序或降序排序。,4.2.2 SQL查询,58,以教学数据库为例介绍该命令的应用。学生(学号,姓名,性别,出生日期,班级,所属系,)课程(课号,课名,学分,学时,开课系)选课(学号,课号,成绩,),4.2.2 SQL查询,59,1.选择记录,例1 查询年龄为空值的学生信息。,例2 查询在19781983年之间出生的学生信息(不在之间)SELECT*FROM 学生 W

23、HERE YEAR(出生日期)BETWEEN 1978 AND 1982,SELECT*FROM 学生 WHERE YEAR(GETDATE()-YEAR(出生日期)IS NULLGetdate():系统函数，返回系统当前日期。YEAR(日期)：系统函数，返回“日期”中的年份。,60,2.选择字段,例3 查询选修了001课且有成绩的学生学号。,SELECT 学号 FROM 选课 WHERE 课号=001 And 成绩 IS NOT NULL,例4 查询有不及格成绩的学生学号。SELECT DISTINCT 学号 FROM 选课 WHERE 成绩60,61,例5 查询数学系、计算机系和信息系学生

24、的信息。SELECT*FROM 学生WHERE 所属系 IN(数学系,计算机系,信息系)例6 查询不是数学系、计算机系和信息系学生的信息。SELECT*FROM 学生 WHERE 所属系 NOT IN(数学系,计算机系,信息系),2.选择字段,62,3.排序,例7 查询年龄大于20岁的学生信息，并将结果按学号排序显示。SELECT*FROM 学生 WHERE YEAR(GETDATE()-YEAR(出生日期)20 ORDER BY 学号 例8 查询学生选课信息，将结果按学号升序排列，学号相同时再按照成绩降序排列显示。SELECT*FROM 选课ORDER BY 学号,成绩 DESC,63,合计

25、函数 SELECT子句中的目标列可以是合计函数，用于数据统计工作，主要有：COUNT（*）计算元组个数 COUNT（DISTINCT|ALL）计算一列中值的个数 SUM（DISTINCT|ALL）计算一列值的总和 AVG（DISTINCT|ALL）计算一列值的平均值 MAX（）求一列值中的最大值 MIN（）求一列值中的最小值,4.分组,64,例9 查询选修了课程的学生人数。SELECT COUNT(DISTINCT 学号)FROM 选课 例10 查询选002课的学生人数以及该课的最高、最低成绩、平均成绩。SELECT COUNT(*)AS 人数,MAX(成绩)AS最高成绩,MIN(成绩)AS

26、最低成绩,AVG(成绩)AS 平均成绩 FROM 选课WHERE 课号=0002,4.分组,65,实际中我们常常会遇到这样的查询要求，如“查询每个部门的工资总和、平均工资和职工总数”等等。对这类查询，我们可以使用GROUP BY分组子句，按“部门”分组，同一部门的职工在一组，求出各部门的统计值。例11 计算各系年龄大于22岁的学生的人数。SELECT 所属系,COUNT(*)AS 人数 FROM 学生 WHERE YEAR(GETDATE()-YEAR(出生日期)=22GROUP BY 所属系,4.分组,66,例12 找出学生人数不足10人的系及其相应的总人数。SELECT 所属系，COUNT

27、(*)AS 人数 FROM 学生 GROUP BY 所属系 HAVING COUNT(*)=3,4.分组,67,当查询涉及两个或两个以上的表时，要用连接查询。连接分为内连接和外连接。,5.连接查询,例14 查询选修0002课成绩为90分以上的学生的学号和姓名。SELECT 学生.学号,姓名FROM 学生 WHERE 学生.学号=选课.学号 AND 选课.课号=0002 AND 成绩=90,68,例15 查询至少选修了0001、0002两门课的学生学号。SELECT SC1.学号FROM 选课 AS SC1,选课 AS SC2 WHERE SC1.学号=SC2.学号 AND SC1.课号=000

28、1 AND SC2.课号=0002注意：自身连接时，要为表起别名。AS可省写。,5.连接查询,69,4.2.3 数据更新语句,SQL中数据更新包括插入数据、修改数据和删除数据三条语句。与查询语句SELECT不同，数据更新语句一次只能对一个表中数据进行更新操作。,70,1.插入数据（INSERT）,有两种格式：格式1：INSERT INTO 表名(字段1，字段2)VALUES(常量1，常量2)格式2：INSERT INTO 表名(字段1，字段2)子查询,4.2.3 数据更新语句,71,例16 将计00-2班所有学生选修“0001”课信息插入选课表。(对于必修课，一个班级的全部学生都选，可成批插入

29、选课记录),INSERT INTO 选课(学号,课号)SELECT 学生.学号,0001 FROM 学生 WHERE 班级=计00-2,4.2.3 数据更新语句,72,2.删除数据（DELETE）,格式：DELETE FROM 表1 WHERE 条件,例17 删除选修0009课的学生选课记录。DELETE FROM 选课 WHERE 课号=0009,4.2.3 数据更新语句,73,3.更新数据（UPDATE）,格式：UPDATE 表名 SET 字段1=表达式1,字段2=表达式2 WHERE 条件,例18 将学生990101的姓名改为王平。UPDATE 学生 SET 姓名=王平 WHERE 课号

30、=990101,4.2.3 数据更新语句,74,视图的主要作用(1)可以满足不同用户的需求。(2)提供一种安全机制，将用户限制到指定的数据集中，数据集的内容由定义视图的查询产生。(3)保证了基本表数据和应用程序间的数据独立性。,4.2.4 创建视图,75,2.创建视图 视图由子查询来定义的，子查询的结果表就是视图表。格式为：CREATE VIEW()AS,4.2.4 创建视图,76,4.2.4 创建视图,说明：给出视图的列名。它是可选项，若给出此项，则表示给子查询的SELECT子句所选择的数据重新起个名字作为视图的列名，它们依次对应；当省略时，表示视图的列名取子查询中SELECT子句给出的列名

31、。,77,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 选课.课号=课程.课号,78,4.2.4 创建视图,3.删除视图 DROP VIEW 视图名,4.视图的使用 视图也是表，一旦建立好就可以象基本表一样使用。,79,作业,实验四本章习题,80,联系我们：同济大学计算机系计算机基础教研室,谢谢！,