SQL_Server__归纳整理11-1(修改)

1、.SQL Server 2005 归纳整理(修改)2011年12月19日 20:26:57首发-有错的请提出，本人进行及时修改-本文用于SQL Server 2005考试复习，选择题部分参考课本课后选择题(以老师上课评价的为准)填空部分1、数据库的三种模型(关系模型、网状模型、层次模型)2、SQL有几种语言?（1）数据定义语言（DDL）（2）数据操作语言（DML）（3）数据控制语言（DCL）3、范式的关系概念？关系规范化的目的是为了消除存储异常、减少数据的冗（rong）余(即重复) 以保证数据的完整性和存储效率，范式分为三等级：范式、范式、范式，一般用到范式就好了 ，如果一个关系不满足2NF，

2、则该关系一定也不满足 3NF 。 (在1NF、2NF、3NF范围内)。 (-此处修改-) 主数据文件 *.mdf 主数据文件4、数据库文件分类: 辅助数据文件 *.ndf 事务日志文件 *.ldf通常数据库都具有一个主数据文件和一个或多个事务日志文件。(-此处修改-)5、主键约束要满足什么条件？（1）. 已有数据的主键字段不能为空(NULL)；（2）. 已有数据的主键字段必须唯一，即在这一字段上没有重复的值。6、数据库的完整性：（参考黄皮P171-P190）用 primary key (主键)约束、unique 唯一约束或标识列保证表数据的完整性；用check（检查）约束、default(默认

3、)约束、default(默认值)、rule(规则)保证列数据完整性；使用primary key（主键）约束、foreign key(外键)约束保证表与表之间参照完整性。关系模式的三类完整性约束条件分别是 域完整性约束 、参照完整性约束和 实体（表）完整性约束 。(-此处修改-)约束类型分为主键约束(PRIMARY KEY) 外键约束(FOREIGN KEY) 唯一约束(UNIQUE) 检查约束(CHECK) 默认约束(DEFAULT)7、视图、索引视图，（参考黄皮P212-P224）用于查看（浏览）数据库各表之间的数据(或这查看数据表的关系)提高数据库的开发效率，视图是一张虚拟表。考点：视图的

4、创建，视图删除。索引，（参考黄皮P191-P205）用于提高数据库的查询速度，索引分为(clustered index)聚集索引和( nonclustered index)非聚集索引，考点：会创建、使用索引，重命名索引，删除索引；8、使用什么命令可以改变数据库文件的大小？（参考黄皮P140-P143）ALTER DATABASE 数据库名 ADD FILE (若为日志文件：add log fileNAME = 辅助数据库名FILENAME=C: 辅助数据库名.ndf-文件路径,SIZE=4MB,-大小为MBMAXSIZE=512MB,-最大容量FILEGROWTH=1MB,-每次递增MB )A

5、lter database 命令可一改变数据库文件大小 (-此处修改-)Size=size -修改文件容量但必需比原先的容量大。9、修改字段类型修改列的定义例如有一列为：Create table student(Lie Varchar(12) not null,)修改Lie Varchar(12) not null,中的字段的类型 ，Int 类型；使用Alter Table 命令:Alter Table student /先修改表 Alter Column Lie Int not null,/再修改列go修改列名 将Lie 的名称改为 StuLie, 使用系统存储过程Sp_rename:Sp_

6、rename'student.Lie','StuLie','Column' go10、重命名视图名称将V_ss 改为 V_studentexec sp_rename 'V_ ss ','V_student' go判断改错、编程印刷P94Declare A IntSet A=1Select AgoSet A=A+1 -错误-局部变量A的范围在go 之前有效印刷P94Create Table tblTemp(TempID int not null,TempDate datetime not null,)-(1)请写出S

7、QL语句来显示列TempID是偶数的记录中列TempDate 的年份Select TempDate from tblTemp where TempID%2=0-(2)请写出SQL语句来显示列TempID是该类的最大值的所有记录Select * from tblTemp where TempID=(Select max(TempID) from tblTemp)印刷P115有家企业要用表tblCustomerInfo 来存储用户信息 ，客户的信息包括：代号 int 类型，名称（最长20字），电话（20字符），传真（20字符），备注(1000个汉字)。电话号码和传真要用统一的自定义类型，名称为ty

8、pe_TelphoneNum。-跟据题意创建一个type_TelphoneNum 类型的个字符Create type type_TelphoneNum from varchar(20) null-创建表Create table tblCustomerInfo(代号int identity not null,名称 varchar(80) null,电话type_TelphoneNumm null,备注varchar(2000) null,)-因为是汉字,一个汉字等于2个字符，所以字符数为2*40=80 和2*1000=2000go-更具需要，添加“手机”字段Alter table tblCust

9、omerInfoAdd 手机 type_TelphoneNum nullGo印刷P186某班级的学生成绩表包括“学号”，“课程代号”，“成绩”，“学期号”，请统计学期号为“第三学期”的每个学生所有科目的平均成绩Select 学号,avg(成绩) from 成绩表where 学期号='第三学期' group by 学号印刷P186题目8.9Create table 部门表(部门号char(2) not null primary key,部门名称varchar(30) not null,)Create table 雇员表(雇员代号 char(4) not null primary

10、key,雇员名称 varchar(20) not null,)Create table 任职表(雇员代号 char(4) not null,部门号char(2) not null,开始时间datetime not null,备注varchar(1000) null, constraint pl_works primary key(雇员代号,部门号,开始时间),constraint Fk_Employees Foreign key(雇员代号)references 雇员表(雇员代号) on delete cascade,constraint Fk_Deparements Foreign key(部

11、门号)references 部门表(部门号)答案参考：select 雇员名称 from 雇员表 as A where not exists (select B.部门名称from 部门表as B where B.部门号 not in (select C.部门号 from 任职表 as C where A.雇员代号 = C.雇员代号)印刷P254假设有创建如下两张表，并有许多数据：Create Table tblDeptinfo(DeptID char (2) not null primary key ,DeptName varchar(20) not null,Remarks varchar(2

12、55) null,)Create Table tblStudinfo(StudID char(8) not null primary key,Name varchar(20) not null,Brithday datetime null,DeptID char(2) not null,)-学生信息表tblStudinfo 的列DeptID 用来存放学生所在系部代号.该列引用信息表tblDeptinfo的列DeptID,但是没有约束,要求创建约束：Alter Table tblStudinfoadd constraint C_depforeign key (DeptID) references

13、 tblDeptinfo(DeptID)以下错误在哪里？create view productsviewas select productID , price ,companyfrom supplier join products on suppliers.ID = products.suppllierIDorder by productID -错误，原因:不能在定义视图的查询中包含order by 语句-错在最后一行附综合项目操作：（包含了数据库的创建、表的创建、数据的查询、数据的完整性、视图、存储过程、触发器、函数、备份数据库）销售数据库Sales中存在这样的三张表：1.客户表（Custo

14、mer） 客户编号(CusNo)姓名(CusName)地址(Address)电话(Tel)C001杨婷北002李和平上003叶新成004冯辰诚上海021-872359652.产品表(Product)产品编号(ProNo)品名(ProName)单价(price)库存数量(Stocks)P0001液晶电视5600.00800P0002空调2390.00460P0003洗衣机3700.00600P0004电热水器890.001203.销售表(ProOut)销售日期(SaleDate)客户编号(CusNo)产品编号(Pro

15、No)销售数量(Quantity)2007-10-27C001P000132007-11-06C004P0003402007-12-27C001P000352008-3-15C002P0002122008-05-02C003P0002212008-05-02C003P000192008-09-21C004P0001302008-11-21C004P000173按要求完成下列题目：（要求以下各小题均使用Transact-SQL语句完成！）（） 建立销售数据库Sales的以上三个表。（） 实现各表的实体完整性要求。（） 在Customer上基于CusName列创建非惟一索引；在Product上基于

16、ProName列创建惟一索引；在ProOut上基于SaleDate列创建聚集的、惟一索引。（） 在ProOut表上设置外键，保证录入时的产品代码都在产品表中存在的。（） 创建一规则限定只能录入大于的值，然后将其绑定到ProOut表上Quantity列。（） 查询销售数量超过100的产品号。（） 创建一自定义函数sumMoney，要求能够利用该函数计算出销售金额，并进行测试，利用该函数计算出每种产品(ProNo)的销售金额。（） 查询购买了产品号“P0002”的客户编号、客户名和电话，查询结果按客户名降序排列。（） 在查询的基础上创建一张新表Cus，要求显示客户“C004”在2008年购买的产品

17、号、数量。（） 创建视图viewPro，要求显示每种产品的销售量和销售金额（） 创建存储过程p_Pro, 要求能够根据指定的客户编号，统计该客户购买每种产品的产品号、数量。（） 创建一个触发器t_Stocks，要求当插入、更新、删除销售表(ProOut)的销售记录时，根据销售数量(Quantity)的变化，能更新产品表(Product)中相应的库存数量(Stocks) （） 备份该数据库Stu_info。create database Sale-（）建立销售数据库Sales的以上三个表。gouse Sale-（）实现各表的实体完整性要求。create table Customer(CusNo

18、int PRIMARY KEY not null,CusName varchar(10) not null,Address varchar(25) null,Tel varchar(13) null)create table Product(ProNo varchar(10) primary key not null,ProName varchar(10) not null,Price money null,Stocks varchar(10) not null)create table ProOut(SaleDate datetime null,CusNo int not null,ProN

19、o varchar(10) not null,Quantuty int null,check(Quantuty>0)-（）创建一规则限定只能录入大于的值，然后将其绑定到ProOut表上Quantity列。)gocreate index pk_Customer on Customer(CusName)-（）在Customer上基于CusName列创建非惟一索引；CREATE UNIQUE INDEX IX_Product ON Product (ProName)-(3)在Product上基于ProName列创建惟一索引；CREATE UNIQUE CLUSTERED INDEX IX_Pr

20、oOut ON ProOut (SaleDate)-(3)在ProOut上基于SaleDate列创建聚集的、惟一索引。Alter Table ProOut Add Constraint pk_ProOut_Product ForEign Key (ProNo) References Product(ProNo)-（）在ProOut表上设置外键，保证录入时的产品代码都在产品表中存在的。use Saleselect * from ProOut where(Quantuty>100)-（）查询销售数量超过的产品号。-（）创建一自定义函数sumMoney，要求能够利用该函数计算出销售金额，并进行

21、测试，利用该函数计算出每种产品(ProNo)的销售金额。create function sumMoney (x money,y int)returns floatasbegin return(x*y)endgo-测试select PO.ProNo,ProName,dbo.sumMoney (price,sum(Quantuty) as '销售金额' from Product as P,ProOut as PO where P.ProNo=PO.ProNoselect P.CusNo,CusName,Tel from ProOut as p,Customer as C where

22、 P.ProNo='P0002' and P.CusNo = C.CusNo order by CusName desc-（）查询购买了产品号“P0002”的客户编号、客户名和电话，查询结果按客户名降序排列。select CusNo,sum(Quantuty)from ProOut where CusNo ='C004' and SaleDate>'2007-12-31'-（）在查询的基础上创建一张新表Cus，要求显示客户“C004”在年购买的产品号、数量。create view viewPro-（）创建视图viewPro，要求显示每种产品的销售量和销售金额asselect PO.ProNo,sum(Quantity) as '销售量',sum(Quantuty * price) as &#