数据库原理电子教案：第四章-SQL-Server表管理.doc

第4章 SQL Server表管理课题： SQL Server表管理目的要求：掌握SQL Server2005数据库的数据类型掌握SQL Server2005数据库表的SQL命令创建方法掌握SQL Server2005数据库表的SQL命令管理方法掌握SQL Server2005数据库表的基本组成重点难点：用SQL命令建立SQL Server2005表课外作业布置：复习本节课内容，完成本章习题 表与视图复习提问1.指出下面3种扩展名对应的文件类型： .mdf .ndf .ldf2. 建立数据库命令的命令是什么？3.用SQL命令建立数据库时，说出表示以下短语的功能：name=filename=size=maxsize=filegrowth=log on4.修改数据库worker，将数据库名称改为employee，写出对应的SQL命令。5.删除数据库worker和视图vi，分别写出SQL命令。6.在SQL Server2005中有个数据库Master，对应的文件是C盘上的Master.mdf和Master.ldf，指出此数据库的物理文件和逻辑文件。4.1 SQL Server中的数据类型1整数型可用于存储精确的整数，包括bigint（8B），int（4B），smallint（2B）和tinyint（1B） 4种类型。它们的区别在于存储的范围不同。 tinyint数据存储为单精度型、无符号整型、8b（1个字节）的数值形式，范围为0255。2精确数值型精确数值型数据由整数部分和小数部分构成。包括：decimal：十进制型，如:decimal(10,5)numeric：数值型，如：numeric(10)、 numeric(10，5)这2种类型用法相同，长度有5，9，13等，且总位数不得小于小数位数。3近似数值型近似数值型数据可以存储精度不是很高，但数据的取值范围却又非常大的数据。包括2种：real：占4Byte，可用普通方法和科学计数法表示float:占8Byte，可以写成float(n)的形式，n精度是115。可用普通方法和科学计数法表示，如12.3456e44货币型在SQL Server中用十进制数来表示货币值。分为2种：money:长度为8Byte，如$326779.1234，精确到万分之一smallmoney: 4Byte，如$23.333，4.51e8， $4.51e85位（bit）型在SQL Server中位型相当于逻辑型，只接收0和1，占用1个字节存储空间，表示是或不是。6字符型字符型数据指由字母、数字和其他特殊符号（如标点、$，#，）、汉字构成的字符串，设计表时应该考虑最大长度，最好留有余地，赋值时要用 引起来，包括4种类型，其默认值都是50。char:char(n)，18000，默认为1，不够时用空格补齐，超过时截掉后面的。varchar: varchar(n),最多255，可变长，长度超过20时，不会用空格补齐，便于节省空间。nchar: nchar(n)，n是14000，支持中英法等多国文字，不够时用空格补齐，超过时截掉后面的。nvarchar: nvarchar(n)，n是14000，长度超过20时，不会用空格补齐，便于节省空间。7文本和图像型当存储的字符数目大于8000时使用文本型，导入图形应用图像型。包括3种：text:存储文本数据，长度为16 ，最大为2311Byte。ntext:采用unicode编码，长度为16 ，最大容量2301Byte，可接受多种编码字符。image:存储图片，长度为16，容量可到2311Byte，但不可以直接输入。8日期时间型在SQL Server中日期时间型的数据以字符串的形式表示，即要用单引号括起来，有两种类型。datetime:日期和时间的结合体，长度为8。范围：1753.1.1 0时9999.12.31 23:59:59。格式：MM/DD/YYYY hh:mm:ss AM/PM 日期分隔符可以是 /或 -，也可表示为 YYYY/MM/DD，时间分隔符是： ，中间用空格分开。可以只要日期，也可以只要时间，也可以组合使用，可只精确到月或分。smalldatetime:长度为4，范围是：1900.1.12079.6.6。其它要求同datetime。9二进制类型有2种类型，常用来存贮图形、声音等数据，不可以直接输入，长度默认为50，最大8000。binary:格式是binary(n)，数据的存贮长度固定的，即n+4字节，若长度小于n，余下部分用0补齐。Varbinary：格式是Varbinary(n)，长度可变，若长度小于n，余下部分不用0补齐。10特殊类型Timestamp:时间戳，长度是8（与datetime相同），系统提供一个唯一值，反映数据修改的相对顺序，相当于一个单调上升的计数器，当所定义的列在更新或插入新行时，此列值自动更新并自动填写。如果列名为Timestamp，系统自动设置为Timestamp类型。Uniqueidentifier:唯一标识型，长度为16，是根据网卡地址和CPU时钟产生的，通过函数newid()获得，全球各地机器产生的此值都不同，但用户可以修改。Sql_variant:可变类型，长度不可以设置，能保存除文本、图形数据和timestamp外的其它任何数据。User_datatype:用户自定义类型，根据数据库中“用户定义的数据类型”确定。例：定义数据类型address，命令是：exec sp_addtype address,varchar(80),not null删除自定义数据类型，命令是：exec sp_droptype address4.2 创 建 表一、引例【例4-1】用SQL语句完成创建student_Info表。CREATE TABLE student_Info( student_ID INT not null PRIMARY KEY, student_Name CHAR(10) NULL, student_Sex CHAR(2) NOT NULL, born_Date DATETIME, class_NO INT, tele_Number CHAR(10), ru_Date DATETIME, address VARCHAR(50), comment VARCHAR(200) )二、使用CREATE TABLE语句创建表语法格式： CREATE TABLE 表名 ( 列名 类型(长度) 列约束条件 ,n )说明：o ,n：表明可以重复前面的内容。在本语法中表明可以定义多个字段。 o 规定长度的数据类型不允许用户再定义长度。列约束条件包括：o NULL或NOT NULL 能否为空，默认为NULLo PRIMARY KEY 主键o UNIQUE 值是否唯一o Foreign key 列名 references 表名(列名)o Check (条件表达式)o Default (常量表达式) 说明常量值只能是：常量、内部函数、null三、建表实例1要求：建立一个工人表，包括编号、姓名、性别、出生日期、入厂日期、职称、备注Create table worker(编号 int not null primary key,姓名 char(8) not null,性别 char(2) null,出生日期 datetime null,入厂日期 datetime not null ,职称 varchar(20) ,工资 money null default 2005,备注 ntext null)第8教案 实训4：用SQL命令创建表实训4：用SQL命令创建表（一）、实训目的掌握利用查询分析器通过SQL命令建立数据库表的方法。掌握用企业管理器查询数据库表的方法。掌握通过SQL命令管理数据库表的方法。（二）、实训内容1、建表实例1（注意：默认值及主键定义方法）要求：建立一个工人表，包括编号、姓名、性别、出生日期、入厂日期、职称、备注Create table worker(编号 int not null,姓名 char(8) not null primary key(编号,姓名),性别 char(2) null,出生日期 datetime null,入厂日期 datetime not null default (getdate(),职称 varchar(20) null,工资 money null, 备注 ntext null)2、建表实例（注意：唯一性及约束规则的建立）要求：建立一个工人信息表，包括编号、姓名、职务，要求主键为编号，姓名具有唯一性Create table 职工信息表(编号 char(10),姓名 char(8),职务 char(12),Constraint pk_no primary key(编号),Constraint pk_name unique(姓名)说明：使用唯一性约束的字段可以为空，一个表中可定义多个唯一性字段。3、建表实例（注意检查约束的建立）要求：建立一个基本信息表，包括编号、姓名、性别、电话，要求性别只接受“F”和“M”，电话只能是8位数字Create table 基本信息表(编号 char(10),姓名 char(8) check(len(姓名)=8),性别 char(2) default M,Constraint chk_sex check(性别 in (F,M),电话 intConstraint chk_tel check(电话 like 0-90-90-90-90-90-90-90-9)说明：一个数据库中不允许出现同名的约束名。4、建表实例（注意：外键约束的建立）要求：有一个职工信息表(编号,姓名,性别,年龄)，其中主键是编号。现要建立一个工资表(编号、基本工资、奖金、扣款)，编号来自职工信息表中的编号。Create table 工资表(编号 char(10) not null primary key foreign key references 职工信息表(编号),基本工资 float not null,奖金 float null,扣款 float null,合计 float)说明：当前键与外键的类型要匹配，宽度相同，外键必须是另一张表的主键或唯一性约束字段。5、利用企业管理器对所建进行查看、参数修改、删除操作。6、利用查询分析器对对所建进行查看、参数修改、删除操作。第9教案 表记录管理课题：表记录管理目的要求：掌握SQL Server2005表的管理方法掌握SQL Server2005记录的输入方法掌握SQL Server2005记录的管理方法掌握SQL Server2005视图管理方法重点难点：用SQL命令进行记录的处理课外作业布置：复习本节课内容，完成本章习题4.3 修改表的结构复习提问 1、如何将表建立在数据库Northwind中，说出在查询分析器中的操作过程。2、建表时，要设置性别只能为“男”或“女”，如何设置限制条件？3、建表时有一个字段参加工作日期，将其默认值设置系统日期，如何设置限制条件？4、建表时有2个字段学号和课程号，设置这2个字段都设置为主键，如何设置限制条件？一、使用企业管理器修改表结构（1）打开“企业管理器”并展开服务器，继续展 开“数据库”，并展开要修改表的数据库，本例 是“Student”。在“表”项上单击鼠标，右边会出 现表对象，右击要修改的表名，并选择 “设计 表”命令。 （2）这时会出现创建表结构时的窗口，然后 对表结构做修改就可以了。最后保存退出。 二、使用ALTER TABLE语句修改表结构1语法格式ALTER TABLE table_name ALTER COLUMN 列名 类型 NULL|NOT NULL ADD 列名 类型 NULL|NOT NULL DROP COLUMN 列名 ,n其它参数与建表相同。如果表中已有记录，修改表结构后可能会导致数据的值发生改变，以适应新结构的要求。2应用举例在数据库factory中建立一个工人信息表employee，然后在表中增加一个salary字段，删除表中的age字段，修改cq字段的数据类型。Use factoryCreate table employee(id char(8) primary key,name char(20) not null,department char(20) null,age int,cq int,)alter table employeeAdd salary float,drop column age,Alter column cq decimal(4,1)应当这样修改：alter table employeeAdd salary floatalter table employeedrop column agealter table employeeAlter column cq decimal(4,1)说明：修改表结构时，每次只能修改一个字段，不能同时修改多个字段。如果要修改多个字段，则需要多次使用alter table 命令。4.4 添加表中的记录一、使用企业管理器添加记录使用企业管理器添加记录的步骤如下。（1）右击要添加记录的表名，并选择打开表命令，进而选择“返回所有行”。（2）如果是第一次执行该命令，出现的窗口的二维表中是没有任何内容的。（3）在标有“*”的一行输入相应数据就可以了。二、使用INSERT语句添加记录 1语法INSERT INTO 表名 （列名1,列名2）VALUES (值1 , 值2)2参数说明INSERT语句每次只能插入一条记录，如果要插入N条记录，则要使用N次INSERT语句。如果包括全部列，则列名列表可以省略，但值要与表中列的顺序一一对应，类型一致，个数相等，不可省略任何一个。插入记录后，系统自动按照主键值排列记录的顺序。Student库的student_info表结构为：（student_ID,student_name,student_sex,born_date,class_NO,tele_Number,ru_date,address,comment)例1：insert student_infoValues(20080105,张小强,男,1990-12-20,200801,5578678,2008-09-12,长沙市香樟路22号,中共预备党员,888888,Football)也可以这样写：例2：insert student_info（student_ID,student_name,student_sex,born_date,class_NO,tele_Number,ru_date,address,comment)Values(20080105,张小强,男,1990-12-20,200801,5578678, 2008-09-12, 长沙市香樟路22号,中共预备党员)Student库的student_info表结构为：（student_ID,student_name,student_sex,born_date,class_NO,tele_Number,ru_date,address,comment)例3：insert student_info（student_ID,student_name,student_sex,born_date,class_NO)Values(20080105,张小强,男,1990-12-20,2008010)说明：其它没有赋值的各列，系统自动设为null。日期型字段中：可以省略日，但年和月不可省略，如1990-12-20,系统也接受1990-12这种日期，而在企业管理器中输入1990-12时，系统自动修改为1990-12-1。4.5 修改表中的记录一、使用企业管理器修改记录 通过企业管理器修改记录和添加记录的操作非常类似 ，操作起来比较简单，具体可参考实际操作。二、使用update语句修改记录 语法格式：UPDATE 表名 SET 列名=值,nWHERE 条件表达式参数说明：若没有指定条件，则不会筛选记录，即所有记录的这一列的值全部改为同一个值。【例4-7】将陈白露的tele_Number改为“2625786”。UPDATE student_InfoSET tele_Number=2625786WHERE student_Name=陈白露说明：如果省略了WHERE子句，那么表明不需要筛选记录，即所有的记录的tele_Number字段的值都改为“2092786”。与使用企业管理器修改表中记录比较，使用语句可以对满足某条件的记录进行大批量的修改，快捷很多。思考：将表result_info表中的所有result字段值都增加2，该如何设置？4.6 删除表中的记录一、使用企业管理器删除记录方法1：删除单条记录右击记录，执行“删除”。方法2：删除连续多条记录：按shift键配合或拖动鼠标，选择多条记录，右击，执行“删除”。 二、使用DELETE语句删除记录 1语法DELETE FROM 表名WHERE 条件2参数说明：若省略了WHERE子句的话表明是要删除表中所有的记录，这时候就成了空表（结构还存在）。【例4-8】将1980年出生的学生删除。DELETE FROM student_InfoWHERE YEAR(born_Date)=19804.7 删 除 表一、使用企业管理器删除表右击表，选择“删除”。二、使用DROP TABLE语句删除表格式：DROP TABLE table_name例：drop table 职工信息表说明：表删除后，结构和记录都会被删除。第10教案 实训5：记录与视图操作实训5：记录与视图操作（一）、实训目的掌握利用查询分析器向表添加记录的方法。掌握用企业管理器和查询分析器管理表记录的方法。掌握通过SQL命令建立视图和管理视图的方法。（二）、实训内容1、用企业管理器输入记录2、用查询分析器输入记录insert student_info（student_ID,student_name,student_sex,born_date,class_NO,tele_Number,ru_date,address,comment)Values(20080105,张小强,男,1990-12-20,200801,5578678, 2008-09-12, 长沙市香樟路22号,中共预备党员)3、用企业管理器修改记录4、用查询分析器修改记录将“张小强”的备注由中共预备党员改为中共正式党员。4.8 视图的概念视图（view）是虚拟的表，它的数据来自于一个或多个表甚至是视图。使用视图有下列好处：限制用户检索的数据。视图相当于提供了一个特定的“窗口”，用户所看到的数据只跟用户的需求有关系。方便数据的操作。视图可以将几个表的数据集中到一起，对该视图的操作也就是操作了几个表，简化了操作界面。一、使用企业管理器创建视图在Student数据库中，以创建“班号为200501的学生情况”视图为例，说明在企业管理器中创建视图的过程。（1）打开“企业管理器”并展开服务器，继续展开“数据库”，并展开要建立视图的数据库，本例是“Student”。在“视图”项上右击鼠标，执行“新建视图”命令，如图所示。（2）接着弹出“创建视图”的窗口，在这个窗口中从上往下有4个窗格。（3）在第一个窗格中单击鼠标右键，从弹出的菜单中选择“添加表”命令。（4）接着出现“添加表”对话框，在“添加表”对话框中选择要加入的表、视图或函数。本例中需用到“student_Info”这张表，所以选中这张表后单击“添加”按钮就完成了添加表的工作。如果要加入多张表，借助Ctrl和Shift键可以进行多选。（5）在第二个窗格中选择创建视图所需的字段，可以指定列的别名、排序方式和规则等。本例中需要先选择所有列，选择所有列可以直接在“*”前的小方框打上勾号，也可以将所有列前面的小方框打上勾号，再设定“class_No”字段的准则为“200501”。（6）单击“保存”按钮，在出现的对话框中输入视图名。本例输入“班号为200501的学生情况”即可。4.9.2 使用CREATE VIEW创建视图1语法CREATE VIEW 视图名 列名 ,nWITH ENCRYPTIONAS SELECT 语句WITH CHECK OPTION 说明：为了保密可以使用WITH ENCYPTION对存放的CREATE VIEW文本加密。但是该选项使用后，创建者也是没有办法再看到的。所以创建者应在其他地方做个备份。WITH CHECK OPTION表示对视图进行UPDATE、INSERT和DELETE操作时，要保证更新、插入或删除的记录满足视图中SELECT语句的条件表达式。SELECT语句的完整格式：select from where列名表示字段的别名，即字段在视图中的输出名。【例4-9】创建选修了课程号为1的学生情况视图。CREATE VIEW 选修了课程号1的学生AS SELECT student_Name,class_No,result FROM student_Info,result_Info,course_info WHERE student_Info.student_Id=result_Info.student_Id and result_Info.course_No=1说明： CREATE VIEW 必须是第一条语句才能执行【例4-10】创建班号为“200501”的学生情况视图。CREATE VIEW 班号为200501的学生情况AS SELECT student_Name,class_No,Tel_number FROM student_Info WHERE class_NO=2005014.10 管 理 视 图一、查看视图通过系统存储过程sp_helptext查看生成视图的代码。例如： Use Student Exec sp_helptext 班号为200501的学生情况 说明：如果系统存储过程不是批处理语句的第一句，那么需要用Execute命令，可以简写为Exec。如果是第一句，可以省略Exec，直接写存贮过程名。如：sp_helptext 班号为200501的学生情况单引号可以省略。二、删除视图删除视图有两种方法。一是通过企业管理器，其操作方法与删除表的方法一样。另外一种方法是通过SQL语句。语法格式DROP VIEW 视图名 ,n参数说明DROP VIEW一次可以删除多个视图。举例：删除视图“班号为200501的学生情况”。drop view 班号为200501的学生情况三、通过视图修改基本表中的数据1、修改视图格式：alter view 视图名(列名1,列名2,)Asselect 语句例：修改视图alter view 班号为200501的学生情况(student_id,student_name,Tele_number,Student_sex)Asselect student_id,student_name,Tele_number,Student_sexFrom Student_info说明：select后面的列名列表必须要和视图列表中的字段名相同2、向视图中插入记录格式：insert into 视图名values(值列表）例：向视图“班号为200501的学生情况”插入一条记录insert into 班号为200501的学生情况values(20011123,李成龙,7788778,男)说明：此命令在向视图插入记录的同时也向视图对应的表（称为基表）student_info插入了一条记录。当视图所依赖的表有2个以上时，则不允许向视图插入记录，因为会影响到多个基表。3、修改视图中记录的内容格式：update 视图名set 列名值例：在视图“班号为200501的学生情况”中，将”李成龙”的名字改为“魏成龙”。Update 班号为200501的学生情况Set student_name=魏成龙Where student_name=李成龙说明：此命令在向视图修改记录的同时也向视图对应的表（简称基表）student_info也做了修改。当视图所依赖的表有2个以上时，则不允许修改视图的记录，因为会影响到多个基表。4、删除视图中记录的内容格式：deletefrom 视图名where 条件例：在视图“班号为200501的学生情况”中，将 “魏成龙”的记录删除。deletefrom 班号为200501的学生情况where student_name=魏成龙说明：此命令在向视图修改记录的同时删除了基表student_info的相应记录。当视图所依赖的表有2个以上时，一般不允许删除视图的记录，因为会影响到多个基表。