1 / 44

第四章 关系数据库的建立

第四章 关系数据库的建立. SQL 概述 数据库的创建与管理 基本表的创建与管理 数据查询 数据更新 视图的创建与管理. 第一节 SQL 概述. 一、 SQL 的特点与功能 二、 SQL Server 概述 三、 SQL Server 2000 的主要组件. 一、 SQL 的特点与功能. 1. 功能 除了查询（ Query ），还包括操纵（ Manipulation ）、定义（ Definition ）和控制（ Control ）。 2. 特点： (1) 高度非过程化 (2) 面向集合的操作方式

melvin-hansen
Download Presentation

第四章 关系数据库的建立

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 第四章 关系数据库的建立 • SQL概述 • 数据库的创建与管理 • 基本表的创建与管理 • 数据查询 • 数据更新 • 视图的创建与管理

  2. 第一节 SQL概述 一、SQL的特点与功能 二、SQL Server概述 三、SQL Server 2000的主要组件

  3. 一、SQL的特点与功能 1.功能 除了查询（Query），还包括操纵（Manipulation）、定义（Definition）和控制（Control）。 2.特点： (1) 高度非过程化 (2) 面向集合的操作方式 (3) 简洁易学，灵活易用 (4) 使用方式灵活方便

  4. 标准SQL语言只能完成数据库的大部分操作，仍然有小部分操作标准SQL语言无法胜任，许多数据库厂商针对自己的数据库软件版本做了某些扩充和修改，增加了一些非标准的SQL语言。 • 经扩充后的SQL语言称为扩展SQL语言。应用于Microsoft公司的数据库产品SQL Server中的Transact-SQL 语言就是一种典型的扩展SQL语言。

  5. 二、SQL Server概述 1. SQL Server 的发展简史 2. SQL Server 2000的特点 (1) 与Internet的紧密结合 (2) 可扩展性和可用性 (3) 企业级数据库 (4) 简单、友好的操作方式 (5) 数据仓库支持

  6. 3. SQL Server 2000的体系结构 (1) 客户机／服务器（C/S）体系结构 (2) 数据库体系结构 (3) 关系数据库引擎体系结构

  7. 三、 SQL Server 2000的主要组件 1. SQL Server 2000的服务器端组件 （1）SQL Server Service （2）SQL Server Agent （3）MS DTC (Microsoft Distributed Transaction Coordinator Service，微软分布式事务协调器 （4）Microsoft Search Service 2. SQL Server 2000客户端组件 （1) 企业管理器：可以完成几乎所有操作。 （2）查询分析器：编辑和执行SQL语句。

  8. 第二节 数据库的创建与管理 一、SQL Server数据库的构成 二、创建数据库 三、修改数据库 四、删除数据库

  9. 一、SQL Server数据库的构成 1.SQL Server的数据库文件 （1）主数据文件 用于存储数据库数据，并包含数据库的启动信息。扩展名是.mdf。 （2）辅助数据文件 用于存储不能置于主数据文件中的所有数据。扩展名是.ndf。 （3）日志文件 用于存储对数据库中数据的操作记录。扩展名是.ldf。

  10. 2. SQL Server 2000的默认数据库 （1）系统数据库 • master--记录了SQL Server所有的服务器级系统信息，所有的注册账户和密码以及所有的系统设置信息，所有用户定义数据库的存储位置和初始化信息。 • model--记录了所有的临时表格、临时数据和临时创建的存储过程 • empdb--建立新数据库的模板，包含了将复制到每个数据库中的系统表 • msdb --被 SQL Server Agent用于进行复制、作业调度以及管理报警等活动

  11. （2）示例数据库 • pubs--模仿一个图书出版公司建立的数据库模型。有出版者(publishers)、出版物（titles）、作者（authors）、书店（stores）、员工(employee)等表 。 • Northwind--模仿一个贸易公司的数据库模型。雇员（Employees）,顾客（Customers），运输商（Shipper），供货商（Supplier），销售区（Territories），订单（Order）等表 。

  12. 1. 使用企业管理器创建数据库 二、创建数据库 2. 使用Transact-SQL语句创建数据库 CREATE DATABASE 数据库名 [ ON [ < 数据文件定义1 > [ ,<数据文件定义2>,...]] [ , < 文件组1 > [ ,<文件组2> ] ] [ LOG ON { < 日志文件定义1 > [ ,<日志文件定义2>,…]}]

  13. 例4.1要创建一个名为pubs1数据库，指定数据库初始大小为10MB，最大容量为50MB，数据库文件的增量为5MB，并同步建立事务日志。例4.1要创建一个名为pubs1数据库，指定数据库初始大小为10MB，最大容量为50MB，数据库文件的增量为5MB，并同步建立事务日志。 CREATE DATABASE pubs1 ON ( NAME = pubs_dat, FILENAME = 'c:\data\pubs1dat.mdf', SIZE = 10 MB, MAXSIZE = 50 MB, FILEGROWTH = 5 MB ) LOG ON ( NAME = 'pubs_log', FILENAME = 'c:\data\pubs1log.ldf', SIZE = 5 MB, MAXSIZE = 25 MB, FILEGROWTH = 5 MB )

  14. 2. 使用Transact-SQL语句修改数据库 三、修改数据库 1. 使用企业管理器修改数据库 ALTER DATABASE 数据库名 { ADD FILE <数据文件定义1 > [ ,< 数据文件定义2>,... ] | ADD LOG FILE < 日志文件定义１> [ ,<日志文件定义2>,…] | REMOVE FILE 数据文件名 | MODIFY FILE < 数据文件定义 > | MODIFY NAME = 新数据库名 }

  15. 例4.3将pubs1数据库的容量扩充到80MB。 ALTER DATABASE pubs1 MODIFY FILE (NAME = pubs_dat, SIZE = 80MB) 例4.4 向pubs1数据库添加一个 5 MB 大小的辅助数据文件。 ALTER DATABASE pubs1 ADD FILE ( NAME = pubsdat2, FILENAME = 'c:\Data\pubsdat2.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB)

  16. 四、删除数据库 1. 使用企业管理器删除数据库 DROP DATABASE 数据库名 例4.5 删除 Archive 数据库。 DROP DATABASE Archive 2. 使用Transact-SQL语句删除数据库

  17. 第三节 基本表的创建与管理 一、创建基本表 二、修改基本表 三、删除基本表 四、建立与删除索引

  18. 一、创建基本表 1. 基本表结构的定义 （1）使用企业管理器定义基本表的结构 （2）使用SQL的CREATE TABLE语句定义基本表 CREATE TABLE <表名> (<列名1><数据类型>[列级完整性约束条件] [,<列名2><数据类型>[列级完整性约束条件]]… [,<表级完整性约束条件>])

  19. 例4.6 创建表4-5所示的publishers表。 CREATE TABLE publishers ( pub_id char(4) NOT NULL UNIQUE, /*列级完整性约束，pub_id 取值唯一，不许取空值*/ pub_name varchar (40) , city varchar (20) , state char (2), country varchar(30) DEFAULT ‘USA’ )

  20. 2. 基本表数据的输入 （1）用企业管理器为表输入数据 （2）使用SQL的INSERT语句为表输入数据 INSERT INTO <表名> [<列名列表>] VALUES（<值列表>）表 例4.7向employee表中插入一条新员工记录（’PMA42628M’, ‘Accorti’, 13, 35, ‘0877’, 1992-08-27）。 INSERT INTO employee VALUES(‘PMA42628M’,’Accorti’,13,35,’0877’,’1992-08-27’)

  21. 二、修改基本表 用SQL的ALTER TABLE语句修改基本表的一般格式为： ALTER TABLE<表名> [ADD <新列名><数据类型>[<完整性约束>]] [ADD PRIMARY KEY <列名>] [ADD FOREIGN KEY <列名> REFERENCES <表名>（<列名>）] [DROP <完整性约束>] [DROP COLUMN <列名>] [ALTER COLUMN <列名><新数据类型>]

  22. 例4.9为employee表增加新列fname，数据类型为20个可变长字符，并为pub_id列添加外键约束。例4.9为employee表增加新列fname，数据类型为20个可变长字符，并为pub_id列添加外键约束。 ALTER TABLE employee ADD fname varchar(20) ADD FOREIGN KEY (pub_id) REFERENCES publishers(pub_id) 例4.10将publishers表中的state列的数据类型改为10个可变长字符。 ALTER TABLE publishers ALTER state varchar(10)

  23. 三、删除基本表 用SQL的DROP TABLE语句删除基本表的一般格式是： DROP TABLE <表名> 例4.11删除原来创建的employee表。 DROP TABLE employee

  24. 四、建立与删除索引 （1）建立索引 CREATE [UNIQUE][CLUSTER]INDEX<索引名> ON <表名>（<列名>[<次序>][,<列名>[<次序>]]…） 例：在employee表中的lname列上建立一个聚簇索引 CREATE CLUSTER INDEX ind-lname ON employee(lname) （2）删除索引 DROP INDEX <索引名> 例4.12删除employee表中的ind-lname索引。 DROP INDEX ind-lname

  25. 第四节 数据查询 一、单表查询 二、连接查询 三、嵌套查询

  26. 一、单表查询 1.选择表中的若干列 • 查询指定列 例4.12 查询employee表中全体员工的工号、姓和名。 SELECT emp_id,fname,lname FROM employee • 查询全部列 例4.13 查询全体员工的详细记录。 SELECT * FROM employee • 查询经过计算的值 例4.14 查询全体员工的工号和姓名。 SELECT emp_id, fname+‘’ +lname emp_name FROM employee

  27. 2. 选择表中的若干元组 在SELECT语句中加入WHERE子句 例4.16查询所有公司编号为’0877’且级别大于35的员工工号。 SELECT emp_id FROM employee WHERE pub_id=’0887’ AND job_lvl>35 例4.17查询所有在’1991-1-1’到’1996-12-31’之间聘用的员工工号与聘用时间。 SELECT emp_id, hire_date FROM employee WHERE hire_date BETWEEN ’1991-1-1’ AND ’1996-12-31’

  28. 例4.18查询所有位于“Boston”和“New York”的出版公司的公司名和城市。 SELECT pub_name,city FROM publishers WHERE city IN (‘Boston’,’New York’) 例4.19查询所有工号以’P’开头的员工工号和姓名。 SELECT emp_id, fname, lname FROM employee WHERE emp_id LIKE ‘P%’ 例4.20查询所有州为空的出版公司的信息。 SELECT * FROM publishers WHERE state IS NULL 注意这里的IS不能用等号(＝)代替。

  29. 消除取值重复的行：在SELECT语句中加入DISTINCT关键字消除取值重复的行：在SELECT语句中加入DISTINCT关键字 例4.21查询employee表中所有员工的级别及所在出版公司的编号。 SELECT job_lvl, pub_id FORM employee 比较： SELECT DISTINCT job_lvl, pub_id FORM employee

  30. 3.对查询结果排序 在SELECT语句中加入ORDER BY子句 例4.22查询employee表中所有公司编号为’9952’的员工信息。结果以聘用时间按升序排列。 SELECT * FROM employee WHERE pub_id=’ 9952’ ORDER BY hire_date 4.使用计算函数汇总数据 在SELECT后使用表4-10给出的函数 例如：查询employee表中job_id的最大值和最小值 SELECT MAX(job_id),MIN(job_id) FROM employee 注：计算函数不能与其它未使用函数的列混合出现，也不能出现在WHERE子句中。

  31. 5.对查询结果分组 在SELECT语句中加入GROUP BY子句 例4.23查询employee表中同一出版公司的人数。 SELECT pub_id, COUNT(*) num FROM employee GROUP BY pub_id • 如果分组后，还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，要用HAVING代替WHERE子句。 例4.24查询employee表中人数超过3人的出版公司编号。 SELECT pus_id FROM employee GROUP BY pus_id HAVING COUNT(*)>3 注：查询列要么是分组依据列，要么是计算函数。

  32. 二、连接查询 连接查询中用来连接两个表的条件称为连接条件，其一般格式为： [<表名1>.<列名1><比较运算符>[<表名2>.]<列名2> 例4.25查询各员工所对应的公司情况，要求显示员工工号、对应的公司名、公司所在城市。 SELECT employee.emp_id, publishers.pub_name, publishers.city FROM employee, publishers WHERE employee.pub_id=publishers.pub_id 还可以使用：… FROM 表1 JOIN表2 ON 连接条件 包括内连接：INNER JOIN 外连接：LEFT OUTER JOIN，RIGHT OUTER JOIN, FULL OUTER JOIN

  33. 三、嵌套查询 在SQL语言中，—个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语条件中的查询称为嵌套查询。 例4.26查询与姓Accorti具有相同公司编号的所有员工（但不包括Accorti）的信息。 SELECT * FROM employee WHERE pub_id IN ( SELECT pub_id FROM employee where lname=’Accorti’) AND lname!=’Accorti’

  34. 嵌套查询可实现多表之间相关内容的查询。 例4.27查询级别为35的员工所对应的出版公司的名称与所在城市。 SELECT pub_name, city FROM publishers WHERE pub_id IN ( SELECT pub_id FROM employee where job_lvl=35)

  35. 第五节 数据更新 一、插入数据 二、修改数据 三、删除数据

  36. 一、插入数据 • 插入单个元组 见第三节（使用SQL的INSERT语句为表输入数据） • 插入子查询结果 INSERT INTO 表名[(<属性列1>[，<属性列名2>]..)] 子查询 例4.28将employee表中所有级别大于100的员工工号、姓名存入一个新表emp1（emp_id, name）中。 INSERT INTO emp1 SELECT emp_id, fname+ ‘ ’+lname FROM employee WHERE job_lvl>100

  37. 二、修改数据 UPDATE <表名> SET <属性列1>=<表达式1>[，<属性列2>=<表达式2>]… [WHERE<条件表达式>] • 修改一个元组的值 • 修改多个元组的值 • 带子查询的修改语句

  38. 例4.29将employee表中员工号为’PMA42628M’的员工的聘用日期改为’1992-08-30’。例4.29将employee表中员工号为’PMA42628M’的员工的聘用日期改为’1992-08-30’。 UPDATE employee SET hire_date=’1992-08-30’ WHERE emp_id=’PMA42628M’ 例4.30将所有员工的级别上调1级。 UPDATE employee SET job_lvl= job_lvl+1 例4.31将对应出版公司名为’Binnet&Hardley’的员工的工作等级修改为12。 UPDATE employee SET job_id=12 WHERE pub_id = (SELECT pub_id FROM publishers WHERE pub_nameid=’Binnet&Hardley’)

  39. 三、删除数据 DELETE FROM 表名 [WHERE<条件表达式>] • 删除一个元组的值 • 删除多个元组的值 • 带子查询的删除语句

  40. 例4.32由于员工号为’ PMA42628M’的员工离职，需要在员工表employee中将其删除。 DELETE FROM employee WHERE emp_id=’ PMA42628M’ 例4.33 删除所有出版公司的元组。 DELETE FROM publishers 例4.34删除对应出版公司所在州为’NY’的员工的记录。 DELETE FROM employee WHERE pub_id IN (SELECT pub_id FROM publishers WHERE state=’NY’)

  41. 第六节 视图的创建与管理 一、创建视图 CREATE VIEW <视图名>[<列名1>[，<列名2>]…] AS <子查询> [WITH CHECK OPTION] 例4.35创建一个只包括公司编号为'9952'的员工信息的视图v_employee。 CREATE VIEW v_employee AS SELECT emp_id,fname,lname,job_id,job_lvl,hire_date, pub_id FROM employee WHERE pub_id= '9952' WITH CHECK OPTION

  42. 二、查询视图 同查询表 三、更新视图 类似更新表 (1) 若视图是由两个以上基本表导出的，则此视图不允许更新。 (2) 若视图的字段来自字段表达式或常数，则不允许对此视图执行Insert和Update操作，但允许执行Delete操作。 (3) 若视图定义中含有Group By或Distinct子句，则此视图不允许更新。 (4) 若视图中有嵌套查询，并且内层查询的From子句涉及的表也是导出该视图的基本表，则此视图不允许更新。 (5) 一个不允许更新的视图上定义的视图也不允许更新。

  43. 五、视图的作用 四、删除视图 DROP VIEW <视图名> （1）为用户集中数据，简化用户的数据查询和处理。 （2）屏蔽数据库的复杂性，有利于数据库的重构。 （3）简化用户权限的管理，加强对机密数据的安全保护。 （4） 多角度看待同一数据，有利于数据共享。

  44. 复习思考题 3、4、6、8、10（双）

More Related