sql第04章管理数据.ppt

1、第4章 数据库管理 学习目的与要求 （1）掌握SQL 工具的使用。 （2）了解数据库的结构。 （3）掌握创建、打开、修改及删除数据库的方法。 （4）掌握创建、修改及删除数据表的方法。 （5）掌握创建及删除数据表索引的方法。,4.1数据库的创建与管理,4.1.1 SQL Server系统数据库 1系统数据库 1）Master数据库 2）Model数据库 3）Msdb数据库 4）Tempdb数据库 5）Resource数据库 6）Distribution数据库 2数据库快照,4.1数据库的创建与管理,4.1.2 数据库的创建,1准备创建数据库,1） 确定数据库的名称、所有者（创建数据库的用户）数据

2、库名称：studentcourse 2）确定存储该数据库的数据文件的大小及文件空间增长方式，确定关系、索引，及系统存储参数的配置，确定数据库的存取方法.,主数据文件： 逻辑名称student；物理文件名：C:Datastudent.mdf； 初始大小：3MB；最大空间：UNLIMITED；空间增加量：1MB；属于文件组primary。 次数据文件： 逻辑名称secondsc；物理文件名：C:mydbsecondsc.ndf， 初始大小：3MB，最大空间：50MB，空间增加量：1MB，属于文件组group1。 日志文件： 逻辑名称student_log；物理文件名：C:Logstudent_lo

3、g.ldf，始初大小：1MB，最大空间：20MB，空间增加量：10%。 索引：每一数据表关于主关键字建立索引文件。,2使用SQL Server Management Studio创建数据库,3.1 关系数据库设计思路,图4.1 【创建数据库】界面,图4.2 【新建数据库】对话框,3使用Transact-SQL语言创建数据库,1）命令格式 CREATE DATABASE ONPRIMARY ，N ，N LOG ON ，N,其中，N 表示设置文件属性，格式如下。 (NAME=逻辑文件名， FILENAME=物理文件名称 ，SIZE=数据库文件的初始容量值 ，MAXSIZE=物理文件的最大容量值 |

4、UNLIMITED ，FILEGROWTH=增加容量值 ) ，N 其中，N 表示设置文件组属性，格式如下。 FILEGROUP文件组名称 DEFAULT ，N,2）参数说明 （1）放在“ ”中的“”表示整个“ ”括起来的选项都可省略，如果不省，则“” 括起的选项不能省。使用“”分隔的多个选项，表示只能选择其中一个。 （2）数据库的名称必须符合标识符规则，最长为128个字符。数据库名称在 SQL Server 的实例中必须唯一。数据库的逻辑文件名是数据库在SQL Server中的标识符。FILENAME指定数据库物理文件名称和路径，它和数据库逻辑名称一一对应。文件组的逻辑名称必须在数据库中唯一，

5、不能是系统提供的名称 PRIMARY 和 PRIMARY_LOG。 （3）“ON”定义数据文件；“PRIMARY”定义主文件组中的文件；“LOG ON”定义日志文件。一个数据库只能有一个主文件，如果没有定义主文件，列在数据文件项的第一个文件就是主文件。,3使用Transact-SQL语言创建数据库,（4）数据库文件容量单位可以是KB，MB，GB，TB，缺省值为MB，长度必须为整数，主文件的，最小容量是Model数据库的主文件长度；对于其他类型文件，最小长度为512KB。 （5）MAXSIZE：指定物理文件的最大容量。如果不设置文件的最大尺寸，那么文件的增长最大值将是磁盘的所有空间。UNLIMI

6、TED选项允许文件增长到磁盘已满。 （6）FILEGROWTH：指定文件每次增加容量的大小或百分比，当FILEGROWTH=0时，表示文件不增长。 （7）DEFAULT：指定命名文件组为数据库中的默认文件组。,图4.11 用命令创建Studentcourse数据库界面,例如CREATE DATABASE studentcourse -逻辑数据名称：studentcourse ON PRIMARY -ON 子句指出文件属于PRIMARY组 ( NAME =studentcourse, -NAME指出了对应的逻辑文件名 FILENAME=C:DATAstudentcourse.mdf, -FILE

7、NAME指出物理文件名 SIZE = 3072KB , -Size指出了初始分配空间 MAXSIZE = UNLIMITED, -MAXSIZE指出了最大空间 FILEGROWTH = 1024KB ), -FILEGROWTH指出了文件空间的增长量 FILEGROUP group1 - FILEGROUP子句指出次数据文件.NDF ( NAME = N secondsc , FILENAME = NC:mydb secondsc.ndf , SIZE = 3072KB , MAXSIZE =51200KB, FILEGROWTH = 1024KB ) LOG ON -LOG ON 子句指出新

8、建数据库的日志文件.LDF ( NAME = Nstudentcourse_log, FILENAME= C:LOGstudentcourse_log.ldf , SIZE = 1024KB , MAXSIZE = 20480KB , FILEGROWTH = 10%),3）创建学生选课数据库,4.1.3 管理数据库,1查看数据库信息,1）命令格式 EXEC sp_helpdb 数据库名 2）功能 查看指定数据库的相关数据文件信息、数据库拥有者、创建时间等信息。若缺省数据库名，则显示所有数据库信息。 【例4.1】查看学生选课“studentcourse”数据库的信息。 方法一：使用SQL Se

9、rver Management Studio查看数据库信息 方法二：使用系统存储过程命令查看数据库信息。 EXEC sp_helpdb studentcourse 【例4.2】查看所有数据库信息。 EXEC sp_helpdb,4.1.3 管理数据库,2打开数据库,1）命令格式 USE 2）功能 使指定数据库成为当前数据库 【例4.3】打开学生选课“studentcourse”数据库。 方法一：使用SQL Server Management Studio打开数据库 方法二：使用命令。 USE studentcourse,3修改数据库,1）命令格式 Alter Database 数据库名 Add

10、File,N To Filegroup 文件组名称 |Add Log File ,N |Remove File 逻辑文件名称 With Delete |Modify File |Modify Name=新数据库名称 |Add Filegroup新增文件组名称 |Remove Filegroup 文件组名称 |Modify Filegroup 原文件组名称 文件组属性|Name=新文件组名称 2）功能 Add File：向数据库添加文件。Add Logfile：向数据库添加日志文件。Remove File：从数据库中删除文件。Modify File：对文件进行修改，包括SIZE、FILEGROW

11、TH和MAXSIZE，每次只能对一个属性进行修改。Modify Name：重新命名数据库。Add | Remove | Modify Filegroup：向数据库中添加删除修改文件组,【例4.4】向数据库Studentcourse中添加一个名为group2的文件组，并在该文件组中添加一个名为Studentcourse2、路径为默认的次数据文件，初始值大小为 2MB ，最大值为50MB，文件以1MB增长；再添加一个名为Studentcourse_Log2的日志文件，初始值大小为1MB ，最大值为100MB，文件以10%增长 。然后对数据库中Studentcourse2文件重命名为Studentc

12、ourse_2，最后把该文件从数据库中移除。 方法一：使用SQL Server Management Studio 方法二：使用SQL语言修改数据库。 命令如下所示： alter DATABASE studentcourse add filegroup group2 -新增group2文件组 GO,3修改数据库,4.1.3 管理数据库,4.1.3 管理数据库,alter DATABASE studentcourse add file (NAME =studentcourse2, -新增studentcourse2次数据文件 FILENAME = C:Program FilesMicrosoft

13、 SQL ServerMSSQL.1MSSQLDATA studentcourse2.ndf, SIZE = 2MB , MAXSIZE =50MB, FILEGROWTH =1MB ) to filegroup group2 Go alter DATABASE studentcourse modify file -修改studentcourse主数据文件的文件增长2MB ( NAME =studentcourse, FILEGROWTH =2MB ) Go,3修改数据库,alter DATABASE studentcourse add LOG file -新增studentcourse_lo

14、g2日志文件 ( NAME = Nstudentcourse_log2, FILENAME = C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL DATA studentcourse_log2.ldf, SIZE =1MB , MAXSIZE = 100MB , FILEGROWTH = 10%) Go alter DATABASE studentcourse modify file -对数据库中次数据文件重命名成studentcourse_2 (name=studentcourse2, newname=studentcourse_2) Go,3修

15、改数据库,alter DATABASE studentcourse remove file studentcourse_2 -从数据库中移除studentcourse_2次要数据文件,3修改数据库,4. 删除数据库 1）命令格式 DROP DATABASE ,N 2)功能 其功能是删除指定数据库。,3修改数据库,1）命令格式 DBCC SHRINKDATABASE (数据库名,Target_Percent) Notruncate|Truncateonly 2）功能 压缩指定数据库。 【例4.6】压缩学生选课studentcourse数据库，使其最大可用空间为30%。 方法一：使用SQL Ser

16、ver Management Studio压缩数据库 方法二：使用SQL命令压缩数据库 DBCC SHRINKDATABASE(Studentcourse, 30 ),4.1.3 管理数据库,5压缩数据库,4.1.3 管理数据库,【例4.7】压缩学生选课数据库studentcourse中的一个secondsc次数据文件，将其压缩为2MB。 方法一：使用SQL Server Management Studio 方法二：使用SQL命令。 DBCC SHRINKFILE (secondsc ,2),5压缩数据库,4.2.1 数据系统视图,1Sysobjects 系统视图 2Syscolumns系统视

17、图 3Sysindexes系统视图 4Sysusers系统视图 5Sysdatabases系统视图 6Sysdepends系统视图 7Sysconstraints系统视图,4.2 数据表的创建,数据类型及其确定原则 见教材P60 SQL Server 的九大类数据类型 1）整型数据类型(Integer) 2）精确数字数据类型(Exact Numeric) 3）近似数字数据类型(Approximate Numeric) 4）货币数据类型(Monetary) 5）日期和时间数据类型(Date Time) 6）字符数据类型(Character) 7）二进制数据类型 8）特殊数据类型 9）自定义数据类

18、型(UDT)EXEC,4.2.2 数据类型,【例4.10】建一个以Datetime为基础的出生日期（Birthday）可为空的数据类型。 方法一：使用SQL Server Management Studio 方法二：使用SQL命令。 EXEC SP_ADDTYPE Birthday，Datetime，Null,4.2.2 数据类型,4.2.3 创建数据表结构,1熟悉CREATE TABLE语句的格式 1）CREATE TABLE语句的简化格式 CREATE TABLE 服务器名.数据库名.架构名.|数据库名.架构名.|架构名.数据库表名 ( 列名，数据类型 NOT NULL identity(

19、初值,步长) DEFAULT 默认值 UNIQUE PRIMARY KEY CLUSTERED | NONCLUSTERED ， 列名，数据类型 NOT NULL DEFAULT 默认值UNIQUE ，n ， 列名 AS 计算列值的表达式，n ， CONSTRAINT 主键约束名 PRIMARY KEY(属性名)， CONSTRAINT 检查约束名 CHECK ( 逻辑表达式)，n， FOREIGN KEY (外键属性) REFERENCES 参照表(参照属性) ，n ) ON 文件组|默认文件组,2）CREATE TABLE语句的说明,【例4.11】创建数据库“book_shop”的数据表b

20、ook，数据表由书号、书名、出版社、出版日期、单价、数量、总价（单价*数量）、电子邮件地址和数据库表使用者字段组成。其中书号列定义为主键并且为系统自动编号即标识列，种子值（起始值）为1000，增量为1，要求出版社字段的值只能是高教、浙大、电子和中央四个之一，电子邮件地址字段中必须包含符号，单价必须大于0，数量必须大于等于0，出版日期的默认值设置为当前日期函数。 （1）标识IDENTITY 属性 （2）计算所得的列 （3）空值NULL约束 （4）PRIMARY KEY 约束 （5）UNIQUE 约束 （6）DEFAULT约束 （7）CHECK 约束 （8）FOREIGN KEY 约束,方法一：使

21、用SQL Server Management Studio 方法二：使用命令。 EXEC sp_helpdb studentcourse CREATE database bookshop Go CREATE TABLE book (书号 int identity(1000,1) NOT FOR REPLICATION PRIMARY KEY CLUSTERED, 书名 char(20) not null, 出版社 char(20), 出版日期 datetime DEFAULT (getdate(), 单价 smallint check(单价0), 数量 smallint check(数量=0)

22、, 总价 as 单价*数量, 电子邮件地址 varchar(25), check(电子邮件地址 like %), check (出版社 in (高教,浙大,电子,中央),2）CREATE TABLE语句的说明,【例4.12】创建数据库学生选课“studentcourse”的数据表S、C、SC，数据表结构如表3.6，表3.7，表3.8所示 各表的完整性约束如表3.9 ，表3.10 ，表3.11所示。 方法一：使用SQL Server Management Studio创建数据表 方法二：使用SQL命令。 1）创建课程表C的语句如下： CREATE TABLE C (课程号 Char (3) NO

23、T NULL, 课程名 Varchar (20) NOT NULL, 学分 Smallint NULL, 预选课程号 Char (3) NULL, 教师 Char (8) NULL, CONSTRAINT FK_Pcno FOREIGN KEY(预选课程号) REFERENCES C(课程号) CONSTRAINT CK_Cno CHECK(课程号 Like 0-90-9) CONSTRAINT PK_C PRIMARY KEY CLUSTERED(课程号 ASC ) ) ON PRIMARY,2）CREATE TABLE语句的说明,【例4.12】创建数据库学生选课“studentcourse

24、”的数据表S、C、SC，数据表结构如表3.6，表3.7，表3.8所示 方法二：使用SQL命令。 2）创建学生基本信息表S的语句如下： CREATE TABLE S ( 学号 Char (6) NOT NULL DEFAULT (J0400), 姓名 Char (8) NOT NULL, 性别 Char (2) NOT NULL, 出生日期 Datetime NOT NULL DEFAULT (19800101), 系 Varchar (20) NOT NULL, 电话 Char (8) NULL, CHECK (学号 Like A-Z 0-9 0-9 0-9 0-9 ), CHECK (性别

25、=女 OR 性别 =男), CHECK (电话 Like 0-90-90-9 -0-90-90-90-9 ), CONSTRAINT PK_S PRIMARY KEY CLUSTERED ( 学号 ASC) ) ON PRIMARY,2）CREATE TABLE语句的说明,【例4.12】创建数据库学生选课“studentcourse”的数据表S、C、SC，数据表结构如表3.6，表3.7，表3.8所示 各表的完整性约束如表3.9 ，表3.10 ，表3.11所示。 方法二：使用SQL命令。 3）创建学生选课数据表SC的语句如下： CREATE TABLE SC ( 学号 Char (6) NOT

26、NULL, 课程号 Char (3) NOT NULL, 成绩 Smallint NULL, FOREIGN KEY(课程号) REFERENCES C (课程号), FOREIGN KEY( 学号 ) REFERENCES S (学号), CHECK (成绩=(0) AND 成绩=(100) OR 成绩 IS NULL), PRIMARY KEY CLUSTERED (学号 ASC, 课程号 ASC),2）CREATE TABLE语句的说明,【例4.13】返回有关所有对象的信息。 USE Master; GO EXEC Sp_Help; GO 【例4.14】返回学生选课“studentcou

27、rse”中学生表的信息。 USE Studentcourse GO EXEC Sp_Help s,4.2.5 查看数据表,1修改表的结构,1）命令格式 ALTER TABLE ALTER COLUMN 类型（宽度）NULL|NOT NULL |ADD 类型（宽度）NULL|NOT NULL完整性约束,n |DROP COLUMN CASCADERESTRICT,n |DROP CONSTRAINT|ALL ,n 2）功能 ALTER TABLE：将要修改的当前数据库中的指定数据表的表名。 ALTER COLUMN：修改当前数据库中的指定数据表的指定属性。 ADD：向当前数据库中的指定数据表增加

28、指定属性或列级完整性约束。 DROP COLUMN：删除当前数据库中的指定数据表中的指定属性。 DROP：删除当前数据库中的指定数据表中的指定列级完整性约束。,4.2.6 修改数据表,【例4.15】修改当前数据库“studentcourse”中S表的系属性改成char(25)，增加一个入学时间字段，它的数据类型为date time，并设置默认值为getdate()，最后删除入学时间字段。 方法一：使用SQL Server Management Studio 方法二：使用命令。 EXEC sp_helpdb studentcourse ALTER TABLE s ALTER COLUMN 系ch

29、ar(25) Go ALTER TABLE s ADD 入学时间 datetime Go ALTER TABLE s ADD CONSTRAINT DF_sj DEFAULT(getdate() for 入学时间 GO,4.2.6 修改数据表,ALTER TABLE s DROP DF_sj GO ALTER TABLE s DROP COLUMN 入学时间,4.2.6 修改数据表,2修改表的名称,1）命令格式 Sp_rename , 2）功能 重命名当前数据库中的指定数据表名。 【例4.16】重命名数据库bookshop的数据表book名称，改为“书籍资料”。 方法一：使用SQL Serve

30、r Management Studio 方法二：使用SQL命令。 Sp_rename book, 书籍资料,4.2.6 修改数据表,1删除数据库信息,1）命令格式 DROP TABLE 表名 2）功能 删除表 【例4.17】删除当前数据库中的表S。 方法一：使用SQL Server Management Studio 方法二：使用SQL命令。 DROP TABLE S；,4.2.7 删除数据表,4.3.1 使用SQL Server Management Studio插入、修改与删除数据,4.3 数据库表的操作,4.3.2 使用T-SQL语句进行插入、修改和删除数据,1 插入数据,2更新数据,3

31、删除记录,插入数据记录的方法有三种，第一种是利用SQL Server Management Studio，第二种是使用SELECT查询语句（本节暂不介绍），第三种是使用INSERT命令。 1）命令格式 INSERT INTO 数据表名 (列名表) VALUES (元组值) INSERT INTO 数据表名(列名表) SELECT 查询语句 INSERT INTO 数据表名(列名表) DEFAULT VALUES 2）功能 向指定数据表的属性列插入数据，VALUES 后跟的元组值为属性列提供数据。其中列名表中的属性排列顺序和VALUES 后跟的元组值的排列顺序要一致。对应的数据类型要一致。如果没

32、有指定列名表，则表示数据表中的所有属性列。 “DEFAULT VALUES”选项会将默认值插入到该属性列中，如果某列没有默认值，允许则向该列插入空值NULL，如果某列不允许空值也没有默认值，则会出错。,1 插入数据,【例4.19】以下示例使用属性列显式指定插入到每个列的值。 方法一：使用SQL Server Management Studio 方法二：使用SQL命令。 USE Studentcourse GO INSERT INTO s(学号,姓名,性别,出生日期,系) VALUES (L0401, 张云龙, 男, 1987-11-11,路桥系) Go SELECT * FROM s,1 插入

33、数据,【例4.20】将查询结果插入数据表，如将学号L0401、成绩80以及课程表中所有课程号插入到sc中。 方法一：使用SQL Server Management Studio 方法二：使用SQL命令。 USE Studentcourse GO INSERT INTO sc SELECT L0401,课程号,80 FROM c Go SELECT * FROM sc,1 插入数据,1）命令格式 UPDATE 基本表名 SET 列名=值表达式，列名=值表达式 WHERE 条件表达式 2）功能 更新指定基本表，满足WHERE子句条件的记录的指定属性值。其中值表达式可以是常量、变量、表达式。若缺省W

34、HERE，则修改表中的所有元组。但在进行修改操作时，需注意数据库的一致性。 【例4.21】更新s表中的所有行出生日期列中的值变为原出生日期值加1。 方法一：使用SQL Server Management Studio 方法二：使用SQL命令 USE Studentcourse GO UPDATE s SET 出生日期=出生日期+1 GO SELECT * FROM s,2更新数据,【例4.22】将选C01课程的学号是L0401的学生的成绩改成85分。 USE Studentcourse GO UPDATE sc SET 成绩=85 WHERE 课程号=C01 AND 学号=L0401 GO S

35、ELECT * FROM sc GO 【例4.23】将张云龙学生的成绩减少5分。 USE Studentcourse GO UPDATE sc SET 成绩=成绩-5 WHERE 学号 IN (SELECT 学号 FROM s WHERE 姓名=张云龙) GO SELECT * FROM sc,2更新数据,1）命令格式 DELETE FROM 基本表名 WHERE 条件表达式 2）功能 删除表中满足条件的所有元组。 【例4.24】删除学号为L0401的学生选课信息。 USE Studentcourse GO DELETE FROM sc Where 学号=L0401 GO SELECT * F

36、ROM sc 【例4.25】从SC 表中删除所有行 USE Studentcourse GO DELETE FROM sc GO SELECT * FROM sc,3删除记录,1索引的作用 1）加速数据检索 2）优化查询 3）强制数据完整性。 2索引的分类 1）聚集索引 2）非聚集索引 3）唯一索引,4.4 索引管理,4.4.1 索引概述,1）命令格式 CREATEUNIQUECLUSTERED|NOCLUSTEREDINDEX 索引名 ON数据表名|视图名（字段名表ASC|DESC,n） WITH PAD_INDEX ,FILLFACTOR=填充因子 ,IGNORE_DUP_KEY ,STA

37、TISTICS_NORECOMPUTE ON 文件组名 2）功能 同一个表中是唯一索引的名称最多达128个字符。 只有在指定的索引名称存在时，才能使用DROPEXISTING选项，该项说明首先删除指定表的索引后再重新构造它。 “UNIQUE”表示建立唯一索引。 CLUSTERED表示建立聚集索引，NOCLUSTERED表示建立非聚集索引。,4.4.2 创建索引,4.4.2 创建索引,表4.17 “studentcourse”索引情况表,【例4.28】使用SQL命令，在数据库“studentcourse”中的数据表S中，关于“学号”建立聚集索引，关于“姓名”建立非聚集索引。 CREATE INDEX IN_姓名 on s(姓名) CREATE unique clustered INDEX