第五章 数据库

1、宋小波一、数据库创建与管理 数据库是数据库是SQL Server用来存放表和索引等数据库用来存放表和索引等数据库对象的逻辑实体。对象的逻辑实体。 数据库文件主要包括三类：数据库文件主要包括三类：u主数据文件（后缀为主数据文件（后缀为.mdf）：该文件包括数据）：该文件包括数据库的启动信息等，并用来存储数据。库的启动信息等，并用来存储数据。u次数据文件（后缀为次数据文件（后缀为.ndf）：这些文件含有不能）：这些文件含有不能置于主数据文件中的所有数据。因为数据库相当置于主数据文件中的所有数据。因为数据库相当大时，就需要有多个次数据文件。大时，就需要有多个次数据文件。u事务日志文件（后缀为事务日志

2、文件（后缀为.ldf）：这些文件包含用）：这些文件包含用于恢复数据库的日志信息。于恢复数据库的日志信息。1. 数据库对象（1） 表：指具体组织和存储数据对象，由行和列组成。（2） 视图：其实也是表，是由从一个或几个基本表中导出的虚拟表而已。（3） 存储过程：是一组经过编译的可以重复使用T-SQl语句代码组合，它在服务器端执行。（4） 触发器：一种特殊的存储过程，它与表相关联。当用户对指定的表进行某种操作。触发器自动执行。（5） 索引：索引主要用来为用户提供一种无需扫描整张表就能实现对数据快速访问的途径，使用索引可以优化查询。数据库包括系统数据库和用户数据库SQL SERVER有四个系统数据库，

3、他们分别是：master数据库、tempdb数据库、model数据库和msdb数据库。1 Master数据库记录了记录了SQL ServerSQL Server系统级的信息，这些信息包括所有的登系统级的信息，这些信息包括所有的登录帐号、系统配置信息、所有数据库的信息，这些表都以录帐号、系统配置信息、所有数据库的信息，这些表都以syssys开开头。常用的头。常用的mastermaster系统表系统表sys_server_infosys_server_info、sysconfigures,sysdatabasessysconfigures,sysdatabases等等2Tempdb数据库该系统数据

4、库主要用于存放所有连接到系统的用户的临时该系统数据库主要用于存放所有连接到系统的用户的临时表和临时存储过程。每次启动表和临时存储过程。每次启动SQL ServerSQL Server时，时，tempdbtempdb数据库又数据库又会被重新创建。会被重新创建。3 Model数据库这是一个模板数据库，所有系统中创建的新数据库的内容，这是一个模板数据库，所有系统中创建的新数据库的内容，在刚创建时和该数据库一样。在刚创建时和该数据库一样。4Msdb数据库MsdbMsdb数据库是数据库是SQK ServerSQK Server代理用来安排报警、作业并记录代理用来安排报警、作业并记录操作员的一个系统数据库

5、操作员的一个系统数据库。2. 创建数据库使用Microsoft SQL Server Management studio创建数据库。（1）在企业管理器中选定被使用的SQl Server服务器在数据库文件夹上单击鼠标右键，弹出一个快捷菜单。单击新建数据库。（2）在该对话框的”常规”选项卡的”数据库名称”文本框中输入要创建数据库的名称，例如输入user。单击位置：这是该数据库存放的路径可以更改。3. 设置数据库选项方法一：第一步：选中sales数据库，单击右键选中属性第二步：进行设置方法二：使用系统存储过程sp_dboption例如：要使用系统存储过程sp_dboption来将数据库Sales的自

6、动收缩功能打开,EXEC SP_DBOPTION Sales,autoshrink,TRUEGO同样，若要打开该数据库的自动关闭EXEC SP_DBOPTION Sales,autoclose,TRUEGO4. 操作数据库a) 显示数据库信息方法一：使用Microsoft SQL Server Management studio步骤：首先选中sales数据库，右键单击选中属性。方法二：使用系统存储过程sp_helpdbEXEC SP_HELPDBb) 修改数据库使用Microsoft SQL Server Management studio第一步：选中sales数据库，右键单击属性第二步：单击

7、文件选项卡第三步：对数据库进行修改c) 使用DBCC SHRINKFILE命令来压缩数据库中一个数据文件的大小例如：将Goods数据库中的数据文件Goods的大小压缩到5MB。代码：DBCC SHRINKFILE（Goods,5）d) 使用DBCC SHRINKDATABASE命令来压缩数据库大小例如：将数据库Goods的大小压缩到50%DBCC SHRINKDATABASE(Goods,50)e) 使用Microsoft SQL Server Management studio删除数据库第一步：在控制台根目录的树形目录找到pubs数据库，单 击右键选中删除。第二步：单击确定按钮。f) DRO

8、P DATABASE语句也可以用来删除数据库例如：要将www-hr服务器中的数据库pangu和sales删除 代码：Drop database pangu,sales二、表的基本概念 表是SQL Server中存放数据的”容器”,数据库中的所有数据都需要存放在表中。 每列的名字也叫域（字段、属性）例如: Name。 SQL Server中的表一共有两类：即永久表和临时表。 永久表都保存数据库文件中，临时表存储在永久表都保存数据库文件中，临时表存储在tempdbtempdb数据库数据库中的，不再使用时他们会被自动删除。中的，不再使用时他们会被自动删除。 临时表分为局部临时表和全局临时表。 局部临

9、时表局部临时表：以单个：以单个# #符号开头，它们仅对当前用户连接符号开头，它们仅对当前用户连接是可见的，该用户与是可见的，该用户与SQL Server2000SQL Server2000实例断开连接即被自实例断开连接即被自动删除。动删除。 全局临时表：全局临时表：以以#符号开头，他们对任何用户是可见的。符号开头，他们对任何用户是可见的。只有所有引用该表的用户从只有所有引用该表的用户从SQL Server SQL Server 实例断开连接时才实例断开连接时才被自动删除。被自动删除。 SQL Server 系统提供的基本数据类型有二进制数据、字符型数据、Unicode数据日期和时间数据、货币数

10、据和特殊字符等七种类型。IDENTITY列即标示列。IDENTITY列可以在SQL查询中被引用，以此代替该列的列名。1. 创建表步骤：第一步：在企业管理器的控制台根目录左窗口，展开树形 目录到要在其中创建表的数据库。 第二步：展开该数据库，显示出其中的各个数据库对象。 第三步：选中表，右键单击新建表。2. 设置表具体操作如下：第一步：设置表的各种设置。第二步：选中其中一列进行各项设置。第三步：选中一列，右键单击。3. 查看表方法一：使用EXEC SP_HELP times语句查看表。方法二：使用Microsoft SQL Server Management studio查看表步骤：第一步：选中

11、表，单击右键选中修改。第二步：在每列进行设置。 4. 查看表上的约束EXEC Sp_helpconstraint 表名例如，要显示cargo表上所定义的约束。EXEC Sp_helpconstraint cargo使用使用EXEC EXEC Sp_helpconstraintSp_helpconstraint cargo,nomsgcargo,nomsg（若使用若使用nomsgnomsg参参数，则不显示表名）来控制是否在结果中显示表名。数，则不显示表名）来控制是否在结果中显示表名。5. 建立表间的关系 所谓表间关系，就是指在数据库中的某个表和其他数据库对象之间所存在的相互联系，这些数据库对象包

12、括视图、规则、索引、存储过程等。 关系的类型：一对一、一对多和多对一。建立数据库文件中的表间关联，一定要满足以下条件：u要保障建立关联的表具有相同属性的字段。u每个表都要以该字段建立索引。在这一前提下，以其中一个表中的字段（主键）与另一表在这一前提下，以其中一个表中的字段（主键）与另一表中的同名字段（外键）建立关联，两个表间就具有了一定中的同名字段（外键）建立关联，两个表间就具有了一定的关系的关系。 具体操作：我们只要从主键前面的钥匙图标出发，按住鼠标左键，将鼠标拖至外键处，就可以建立两个表关系。三、SQL语言介绍p DDL DDL（Data Definition Language 数据定义语

13、言），是用于描述数据库中要存储的现实世界实体的语言。 DDL操作对象和对象的属性，这种对象包括数据库本身，以及数据库对象，如：表、视图等等，DDL对这些对象和属性的管理和定义具体表现在Create、Drop和Alter上。 DDL不对具体的数据进行操作。1. DDL的主要操作语句1）Create语句：可以创建数据库和数据库的一些对象。u使用CREATE DATABASE语句创建数据库，示例如下：CREATE DATABASE SalesON(NAME=Sales_dat, 数据库文件名FILENAME=d:sales_dat.mdf,数据库文件存放路径SIZE=10MB,文件大小MAXSIZE

14、=25MB,最大容量FILEGROWTH=5MB)自动增长幅度5MBLOG ON(NAME=Sales_log,日志文件名FILENAME=d:sales_log.ldf,日志文件存放位置SIZE=5MB,日志文件大小MAXSIZE=25MB,最大容量25MBFILEGROWTH=5MB)自动增长幅度MBGOu 创建表基本格式：CREATE TABLE 表名(列名1 数据类型等信息列名2数据类型等信息列名3数据类型等信息.)对表加备注:comment on table 表名 is 备注内容;对表中字段加备注:comment on column 表名.字段名 is 备注内容;例：CREATE T

15、ABLE Customers(客户号 intIDENTITY(1,1)PRIMARY KEY,姓名 varchar(20) NOT NULL,公司 varchar(50),职位 varchar(20),地址 varchar(100),电话 varchar(20)UNIQUE)对每一列使用命名约束CONSTRAINT 约束名 列约束例：CREATE TABLE cargo( 货号 int IDENTITY(1,1), 品名 varchar(20) CONSTRAINT PK_goosname PRIMARY KEY, 描述 varchar(200), 库存量 int, 进货地 varchar(5

16、0), 价格 money)u 创建索引 一般格式为： CREATE UNIQUE CLUSTER INDEX ON ( , . ) ：指定要建索引的基本表。 ：指定要建索引的列名。索引可以建在一列或多列上，各列名之间用逗号分隔。 ：每个后可指定索引值的排列次序，包括ASC（升序）和DESC（降序）两种，缺省值为ASC。 UNIQUE：表明每一个索引值只对应唯一的数据记录。 CLUSTER：建立聚簇索引：表中的记录按索引项的顺序存放。 一个基本表最多只能建立一个聚簇索引。一个基本表最多只能建立一个聚簇索引。 聚簇索引列数据的更新会导致表中记录的物理移动，代价聚簇索引列数据的更新会导致表中记录的物

17、理移动，代价较大，因此经常更新的列不宜建聚簇索引。较大，因此经常更新的列不宜建聚簇索引。例： 为Student、Course、SC建立索引。 CREATE UNIQUE INDEX Stusno ON Student(Sno) /*默认升序*/CREATE UNIQUE INDEX Coucno ON Course(Cno) /*默认升序*/CREATE UNIQUE INDEX SCno ON SC(Sno ASC, Cno DESC)u创建视图 基本格式为：CREATE VIEW (,.) AS WITH CHECK OPTION说明：a) 子查询中不能含ORDER BY。b) WITH

18、CHECK OPTION表示对视图进行增、删、改 时要满足子查询中的条件表达式。c) 视图的列名或者全部省略，或者全部指定。下面给出几个例子例1：建立IS系学生的视图CREATE VIEW IS_Student AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept=IS DBMS执行CREATE VIEW语句时只把视图定义存入数据字典,并不执行其中的SELECT语句。只有对视图进行查询时,才按视图的定义从基本表中将数据查出。 例2：建立IS系学生的视图，并要求进行修改和插入操作时仍须保证该视图只有IS系的学生。 CREATE VIEW IS_S

19、tudent AS SELECT Sno, Sname, Sage, Sdept FROM Student WHERE Sdept=IS WITH CHECK OPTION以后用命令对视图进行插入、修改和删除操作时，DBMS会自动检查Sdept=IS这个条件。 视图也可以定义在多个基本表上： 例3：建立IS系选修了1号课程的学生视图CREATE VIEW IS_S1(Sno, Sname, Grade) AS SELECT S.Sno, Sname, Grade FROM Student S, SC WHERE Sdept=IS AND S.Sno=SC.SnoAND SC.Cno=1 以后用

20、命令对视图进行插入、修改和删除操作时，DBMS会自动检查Sdept=IS这个条件。视图可以定义在视图上，或定义在基本表和视图上：例4：建立IS系选修了1号课程且成绩在90分以上的学生视图CREATE VIEW IS_S2 AS SELECT Sno, Sname, Grade FROM IS_S1 WHERE Grade=90带表达式的视图：视图的列可以是不存在的虚拟列。例5：定义一个反映学生出生年份的视图CREATE VIEW BT_S(Sno, Sname, Sbirth) AS SELECT Sno, Sname, 2005-Sage FROM Student分组视图：子查询中含集函数和

21、GROUP BY子句。例6：将学生的学号及他的平均成绩定义为一个视图。 CREAT VIEW S_G(Sno, Gavg) AS SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno 由“SELECT *”建立的视图：例7：将Student表中所有女生记录定义为一个视图。 CREATE VIEW F_Student(stdnum,name,sex,age,dept) AS SELECT * FROM Student WHERE Ssex=女问题：视图建立后，视图的列名和Student表的列名就构成了一一对应关系，如果以后修改了Student的结构,这个对应关系

22、就不成立了,视图也就不能工作了。办法：办法： a) a) 建立视图时明确指明属性列名，而不用建立视图时明确指明属性列名，而不用SELECT SELECT * * CREATE VIEW F_Student(stdnum,name,sex,age,dept) CREATE VIEW F_Student(stdnum,name,sex,age,dept) AS SELECT Sno, Sname, Ssex, Sage, Sdept AS SELECT Sno, Sname, Ssex, Sage, Sdept FROM Student WHERE Ssex= FROM Student WHERE

23、 Ssex=女女 StudentStudent表增加列后视图仍能工作表增加列后视图仍能工作, ,但删除列仍不行。但删除列仍不行。 b) b) 修改基本表后删除原视图后重建，这是最保险的方法。修改基本表后删除原视图后重建，这是最保险的方法。2）Drop语句：可以删除数据表、索引、触发程序、条件约束以及数据表的权限等。u 删除表基本格式：drop table 表名 cascade constraint例：删除SC表，Couser表和Student表DROP TABLE SC , Course, Student注意：1) SC一定要写在Course和Student前面。 2) 基本表定义一旦删除，表

24、中的数据、索引都将自动被删除掉。执行删除操作一定要格外小心。u删除视图 基本格式：drop view 例：删除视图IS_S1 DROP VIEW IS_S1注意： 执行此语句后，IS_S1视图的定义将从数据字典中删除。 由IS_S1视图导出的IS_S2视图的定义仍在数据字典中，但已无法使用了，应同时删除。u删除索引 基本格式：DROP INDEX ., .例：删除索引Stusno、 Coucno、 SCnoDROP INDEX Student.Stusno,Course.Coucno,SC.SCno1)索引一经建立，就由DBMS使用和维护它,不需用户干预;2)如果数据增删改频繁，系统会花费许多

25、时间来维护索引, 这时，可以删除一些不必要的索引。3） Alter语句：修改数据表定义及属性。u使用ALTER DATABASE语句的SET子句对数据库选项进行设置，代码如下：ALTER DATABASE SalesSET recursive_triggers ONu使用ALTER DATABASE语句来完成增加数据库的容量等工作，使用DBCC SHRINKDATABASE或DBCC SHRINKFILE命令来减少（压缩）数据库的容量或数据库文件大小。例1：增加数据库Goods的数据文件goods的大小ALTER DATABASE GoodsMODIFY FILE(Name=goods,SIZ

26、E=20MB)例2：为goods数据库增加次要的数据库文件goods2ALTER DATABASE GoodsADD FILE(NAME=Goods2,FILENAME=D:Goods2.ndf,SIZE=2MB,MAXSIZE=10MB,FILEGROWTH=5%)u修改基本表 一般格式为： ALTER TABLE ADD 完整性约束 DROP COLUMN ALTER COLUMN NOT NULL DROP CONSTRAINT : 指定要修改的基本表；ADD子句: 用于增加新列和新的完整性约束；DROP COLUMN子句: 用于删除指定列ALTER COLUMN 子句：用于修改原有的列

27、定义DROP CONSTRAINT 子句：用于删除完整性约束； 下面看几个例子：例1：向Student表增加Scity列 ALTER TABLE Student ADD Scity CHAR(20) CONSTRAINT CK3 CHECK( Scity IN (杭州, 宁波) ) 如果表中已有数据，则新增加的列一律为空值。如果表中已有数据，则新增加的列一律为空值。例2：将年龄的数据类型改为SMALLINT并允许取空值 。 ALTER TABLE Student ALTER COLUMN Sage SMALLINT NULL1) 1)若有约束对象访问该列，则不允许修改，除非删除这些若有约束对象

28、访问该列，则不允许修改，除非删除这些对象对象2) 2)修改原有的列定义有可能会破坏已有数据。修改原有的列定义有可能会破坏已有数据。例3：删除Scity列。ALTER TABLE Student DROP COLUMN Scity若有约束对象访问该列，则不允许删除。若有约束对象访问该列，则不允许删除。例4：先删除约束对象CK1、CK3、UQ2, 然后执行例3和例4。 ALTER TABLE Student DROP CK1, CK3, UQ2p DML DML（Data Manipulation Language 数据操控语言）用于查询数据库以及操作已有数据库中的数据的计算机语言。具体是指是SE

29、LECT查询、UPDATE更新、INSERT插入、DELETE删除。 DML分成交互型DML和嵌入型DML两类。依据语言的级别，DML又可分成过程性DML和非过程性DML两种。 交互型交互型DML:DML:这类这类DMLDML自成系统，可在终端上直接对数据自成系统，可在终端上直接对数据库进行操作。库进行操作。 嵌入型嵌入型DML:DML:这类这类DMLDML是嵌入在主语言中使用。此时主语是嵌入在主语言中使用。此时主语言是经过扩充能处理言是经过扩充能处理DMLDML语句的语言。语句的语言。 过程性过程性DML:DML:用户编程时，不仅需要指出用户编程时，不仅需要指出“做什么做什么”( (需要需要

30、什么样的数据什么样的数据) )，还需要指出，还需要指出“怎么做怎么做”( (怎么获得数据怎么获得数据) )。层状、网状的层状、网状的DMLDML属于过程性语言。属于过程性语言。 非过程性非过程性DML:DML:用户编程时，只需要指出用户编程时，只需要指出“做什么做什么”，不，不需要指出需要指出“怎么做怎么做”。关系型。关系型DMLDML属于非过程性语言。属于非过程性语言。1) DML的主要操作语句uINSERT语句用于向数据库表或者视图中加入一行数据。语法形式如下： INSERT INTO table_or_view (column_list) VALUES(data_values) tabl

31、e_or_view是指要插入新记录的表或视图； column_list是可选项，指定待添加数据的列； VALUES子句指定待添加数据的具体值。 列名的排列顺序不一定要和表定义时的顺序一致。但当指定列名表时VALUES子句值的排列顺序必须和列名表中的列名排列顺序一致，个数相等，数据类型一一对应。在进行数据插入操作时须注意以下几点：（1）必须用逗号将各个数据分开，字符型数据要用单引号括起来。（2）INTO子句中没有指定列名，则新插入的记录必须在每个属性列上均有值，且VALUES子句中值的排列顺序要和表中各属性列的排列顺序一致。（3）将VALUES子句中的值按照INTO子句中指定列名的顺序插入到表中

32、。（4）对于INTO子句中没有出现的列，则新插入的记录在这些列上将取空值，如上例的SCORE即赋空值。但在表定义时有NOT NULL约束的属性列不能取空值。 INSERTINSERT语句语句例：创建SC表（学生选课表），并向SC表中插入一条选课记录（S7,C1）。程序清单如下：CREATE TABLE SC( sno char(10) NOT NULL, cno char(2) NULL, /*课程编号字段*/ score numerical(4,1) NULL /*成绩字段*/ )GoINSERT INTO SC (sno,cno) VALUES (3130050101, c1)GoINSE

33、RTINSERT语句语句插入多行数据的语法格式为：INSERT INTO table_or_view (column_list) 子查询例 求出各位学生的平均成绩，把结果存放在新表AVGSCORE中。程序清单如下：/*首先建立新表AVGSCORE，用来存放学号和学生的平均成绩。*/CREATE TABLE AVGSCORE(SNO CHAR(10),AVGSCORE SMALLINT) Go/*利用子查询求出SC表中各位学生的平均成绩，把结果存放在新表AVGSCORE中。*/INSERT INTO AVGSCORESELECT SNO,AVG(SCORE) FROM SCGROUP BY SN

34、O UPDATEUPDATE语句语句 UPDATE语句用于修改数据库表或视图中特定记录或者字段的数据，其语法形式如下： UPDATE table_or_view SET =,= WHERE 其中：table_or_view是指要修改的表或视图；SET子句给出要修改的列及其修改后的值，其中column为要修改的列名，expression为其修改后的值；WHERE子句指定待修改的记录应当满足的条件，WHERE子句省略时，则修改表中的所有记录。 UPDATEUPDATE语句语句 例一个带有WHERE条件的修改语句。程序清单如下：use adventureworksgoupdate person.ad

35、dress set city=Boston where addressid=1 例 将所有学生年龄增加1岁程序清单如下：UPDATE S SET AGE=AGE+1 UPDATEUPDATE语句语句 例 创建把讲授C5课程的教师的工资增加100元。 程序清单如下： /*T表（教师基本情况表）的结构为T(TNO,TN,SEX,AGE,PROF,SAL,DEPT)分别表示教师的编号，姓名，性别，年龄，职称，工资，系别。TC表（教师授课表）的结构为TC(TNO,CNO)分别表示教师的编号，课程编号。*/ UPDATE T SET SAL=SAL+100 WHERE TNO IN (SELECT T.

36、TNO FROM T,TC WHERE T.TNO=TC.TNO AND TC.CNO=C5) /*通过连接查询找到讲授C5课程的教师编号。*/DELETEDELETE语句语句 使用DELETE语句可以删除表中的一行或多行记录，其语法格式为： DELETE FROM table_or_view WHERE 其中：table_or_view是指要删除数据的表或视图；WHERE子句指定待删除的记录应当满足的条件，WHERE子句省略时，则删除表中的所有记录。 下面是删除一行记录的例子。 例 删除张益琳教师的记录。 程序清单如下： DELETE FROM T WHERE TN= 张益琳DELETEDE

37、LETE语句语句 下面是删除多行记录的例子。 例 3-15 删除所有教师的授课记录。 程序清单如下： DELETE FROM TC 执行此语句后，TC表即为一个空表，但其定义仍存在数据字典中。 例3-16 删除李明同学选课的记录。 DELETE FROM SC WHERE SNO= (SELECT SNO FROM S WHERE SN= 李明)uSelect查询简单的T-SQL查询只包括选择列表、FROM子句和Where子句。它们分别说明所查询列、查询的表或视图、以及搜索条件等。 例如，下面的语句查询testtable表中姓名为“张三”的nickname字段和email字段。 Select

38、nickname,email FROM testtable Where name=张三 选择列表 选择列表(select_list)指出所查询列，它可以是一组列名列表、星号、表达式、变量(包括局部变量和全局变量)等构成。 1、选择所有列 例：下面语句显示testtable表中所有列的数据：Select * FROM testtable 2、选择部分列并指定它们的显示次序 查询结果集合中数据的排列顺序与选择列表中所指定的列名排列顺序相同。例：Select nickname,email FROM testtable 3、更改列标题 在选择列表中，可重新指定列标题。定义格式为： 列标题=列名 列名

39、列标题 例：下列语句使用汉字显示列标题 Select 昵称=nickname,电子邮件=email FROM testtable 4、删除重复行 Select语句中使用ALL或DISTINCT选项来显示表中符合条件的所有行或删除其中重复的数据行，默认为ALL。使用DISTINCT选项时，对于所有重复的数据行在Select返回的结果集合中只保留一行。 5、限制返回的行数使用TOP n PERCENT选项限制返回的数据行数，TOP n说明返回n行，而TOP n PERCENT时，说明n是表示一百分数，指定返回的行数等于总行数的百分之几。例：Select TOP 2 *FROM testtable Select TOP 20 PERCENT * FROM testtable FROM子句 FROMFROM子句指定子句指定SelectSelect语句查询及与查询相关语句查询及与查询相关的表或视图。在的表或视图。在FROMFROM子句中最多可指定子句中最多可指定256256个表个表或视图，它们之间用逗号分隔。或视图，它们之间用逗号分隔。 在在FROMFROM子句同时指定多个表或视图时，如果子句同时指定多个表或视图时，如果选择列表中存在同名列，这时应使用对象名限定选择列表中存在同名列，这