第4章关系数据库标准语言sqlppt课件

数据库技术及应用DB 第 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， 日志文件是 Msdblog.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的可 变长 文本 类 型 ,长 度最大 231-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 课号 =0002 4. 分组 64 实际中我们常常会遇到这样的查询要求，如 “ 查询每个部门的工资总和、平均工资和职 工总数 ” 等等。对这类查询，我们可以使用 GROUP BY分组子句，按 “ 部门 ” 分组，同 一部门的职工在一组，求出各部门的统计值 。 例 11 计算各系年龄大于 22岁的学生的人数。 SELECT 所属系 , COUNT(*) AS 人数 FROM 学生 WHERE YEAR(GETDATE()- YEAR(出生日期 )=22 GROUP BY 所属系 4. 分组 65 例 12 找出学生人数不足 10人的系及其相应 的总人数。 SELECT 所属系， COUNT(*) AS 人数 FROM 学生 GROUP BY 所属系 HAVING COUNT(*)=3 4. 分组 66 当 查询涉及两个或两个以上的表时，要用连 接查询。连接分为内连接和外连接。 5. 连接查询 例 14 查询选修 0002课成绩为 90分以上的学生的 学号和姓名。 SELECT 学生 .学号 , 姓名 FROM 学生 WHERE 学生 .学号 =选课 .学号 AND 选课 .课号 =0002 AND 成绩 =90 67 例 15 查询至少选修了 0001、 0002两门课 的学生学号。 SELECT SC1.学号 FROM 选课 AS SC1 , 选课 AS SC2 WHERE SC1.学号 =SC2.学号 AND SC1.课号 =0001 AND SC2.课号 =0002 注意 ：自身连接时，要为表起别名。 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”课 信息插入选课表。 (对于必修课，一个班级的全部学生都选， 可成批插入选课记录 ) IN