一、单项选择题 2. 在SQL Server中,保存所有的临时表和临时存储过程的是______
A.Master数据库 B.Tempdb数据库 C.Model数据库 D.Msdb数据库
A B C D
B
[考点] 维护操作
[解析] 总体而言,SQL Server有如下4个默认的数据库:Master、Model、Tempdb和Msdb。
(1)Master
Master数据库(主数据库)保存放在SQL Server实体上的所有数据库元数据的详细信息,它还是将引擎固定起来的粘合剂。由于如果不使用Master数据库,那么SQL Server就不能启动,所以,必须要小心地管理好这个数据库。因此,对这个数据库进行常规备份是十分必要的。这个数据库还包括了诸如系统登录、配置设置、已连接的Server信息、扩展存储过程等。
(2)Model
Model数据库(模型数据库)是一个用来在实体上创建新用户数据库的模板数据库,可以把任何存储过程、视图、用户等放在模型数据库里,这样在创建新数据库的时候,新数据库就会包含存放在模型数据库里的所有对象了。
(3)Tempdb
Tempdb数据库存有临时对象,例如全局和本地临时表和存储过程。这个数据库在SQL Server每次重启的时候都会被重新创建,而其中包含的对象是依据模型数据库里定义的对象被创建的。除了这些对象,Tempdb还存有其他对象,例如表变量、来自表值函数的结果集以及临时表变量。由于Tempdb会保留SQL Server实体上所有数据库的对象类型,所以,对数据库进行优化配置是非常重要的。
(4)Msdb
Msdb数据库用来保存数据库备份、SQL Agent信息、DTS程序包和SQL Server任务等信息,以及诸如日志转移这样的复制信息。
从SQl Server Studio中可以查看所有的数据库,如下图所示。
二、简答题 1. SQL Server如何获取系统时间?
利用函数GETDATE可以获取系统时间,查询语句为SELECT GETDATE()。
[考点] 函数
2. SQL Server如何查看版本?
可以通过图形界面查询,也可以通过SQL语句查询来获取数据库的版本,SQL语句为SELECT@@VERSION,如下: Microsoft SQL Server 2008(RTM)-10.0.1600.22(Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition on Windows NT 5.1<X86> (Build 2600:Service Pack 3) (VM)
[考点] 函数
3. 用一条SQL语句查询出每门课都大于80分的学生姓名:
name kecheng fenshu
张三 语文 81
张三 数学 75
李四 语文 76
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 90
SELECT DISTINCT NAME FROM TABLE WHERE NAME NOT IN (SELECT DISTINCT NAME FROM TABLE WHERE FENSHU<=80);。
[考点] SQL部分
设教学数据库中有三个基本表: 学生表S(S拌,SNAME,AGE,SEX),其属性表示学生的学号、姓名、年龄和性别;选课表SC(S#,C#,GRADE),其属性表示学生的学号、所学课程的课程号和成绩;课程表C(C#,CNAME,TEACHER),其属性表示课程号、课程名称和任课教师姓名。 下面的题目都是针对上述三个基本表操作的。4. 试写出下列插入操作的SQL语句:
把SC表中每门课程的平均成绩插入另一个已存在的表SC_C(C#,CNAME,AVG_GRADE)中,其中,AVG_GRADE为每门课程的平均成绩。
INSERT INTO SC_C(C#, CNAME,AVG_GRADE)SELECT SC.C#,C.CNAME,AVG(GRADE) FROM SC,C WHERE SC.C#=C.C# GROUP BY SC.C#,C.CNAME;
[考点] SQL部分
5. 试写出下列删除操作的SQL语句:
从SC表中把WU老师的女学生选课元组删去。
DELETE FROM SC WHERE S# IN (SELECT S# FROM S WHERE SEX='女') AND C# IN (SELECT C# FROM C WHERE TEACHER='WU');
6. 设有如下关系表:
供应者:SUPPLIER (SNO,SNAME,CITY),其中,SNO为供应者编号,SNAME为供应者姓名,CITY为所在城市。
零件:PART(PNO,PNAME,WEIGHT),其中,PNO为零件号,PNAME为零件名称,WEIGHT为重量。
工程:JOB(JNO,JNAME,CITY),其中,JNO为工程号,JNAME为工程名,CITY为所在城市。
联系关系:SPJ(SNO,PNO,JNO,QTY),其中,QTY为数量。
1)查找给工程J1提供零件P1的供应者号SNO。
2)查找在北京的供应者给武汉的工程提供零件的零件号。
3)查找由供应者S1提供的零件名PNAME。
4)查找CITY值为上海的工程号和名称。
5)将工程J3的城市改为广州。
6)将所有重20公斤的零件改为重10公斤。
7)将给工程J1提供零件P1的供应者S1改为S2。
8)将值(S3,麦苗,上海)加到SUPPLIER中。
9)删除所有上海工程的数据。
1)SELECT SNO FROM SPJ,PART,JOBWHERE SPJ.PNO=PART.PNO ANDSPJ.JNO=JOB.JNO AND PART.PNAME='P1' AND JOB.JNAME='J1'; 2)SELECT PNOFROM SPJ,PART,JOB WHERE SPJ.PNO=PART.PNO AND SPJ.JNO=JOB.JNOAND JOB.CITY='武汉' AND SUPPLIER.CITY='北京'; 3)SELECT PNAME FROM PART WHERE PNO IN(SELECT PNO FROM SPJ, SUPPLIER WHERE SPJ.SNO=SUPPLIER.SNOAND SUPPLIER.SNAME='S1'); 4)SELECT JNO,JNAME FROM JOB WHERE CITY='上海'; 5)UPDATE JOB SET CITY='广州' WHERE JNAME='J3'; 6)UPDATE PART SET WEIGHT='10公斤' WHERE WEIGHT='20公斤'; 7)UPDATE SUPPLIER SET SNAME='S2' WHERE SNAME='S1' AND SNO IN (SELECT SNO FROM SPJ,JOB,PART WHERE SPJ.JNO=JOB.JNOAND JOB.JNAME='J1' AND SPJ.PNO=PART.PNOAND PART.PNAME='P1'); 8)INSERT INTO SUPPLIER VALUES (;S3','麦苗','上海'); 9)DELETE FROM SPJ WHERE JNO IN (SELECT JNO FROM JOB WHERE CITY='上海'); DELETE FROM JOB WHERE CITY='上海';--需要注意的是,上述语句的顺序不能弄反。
学生信息管理系统中有张表STUDENT,其中有字段ID、NAME、SEX、BIRTH,请回答如下问题:7. 找出NAME相同的学生(用一句SQL语句);
SELECT*FROM STUDENT WHERE NAME IN (SELECT NAME FROM STLIDENT GROUP BY NAME HAVING COUNT(NAME)>1);
[考点] SQL部分
8. 用一句SQL语句把学生SEX为男的改为女,女的改为男。
UPDATE STUDENT SET SEX=CASE SEX WHEN '男' THEN'女'ELSE'男'END;
9. 假设有表数据:TABLE
所要结果:
请写出获得此结果的SQL语句。
本题考查的是聚合函数和子查询,先按照ID列进行分组找出NUM最大的值,然后回表查询即可得最终结果,最终SQL语句如下: SELECT*FROM TABLEWHERE NUM IN (SELECT MAX(NUM) FROM TABLE GROUP BYID);
[考点] SQL部分
10. SQL Server如何启动?
在Windows服务控制台里手动启动,这个也是最常用的方式。键入“Win+R”键打开运行窗口,然后输入services.msc打开服务窗口。另外,通过Windows命令窗口,可以使用“net start mssqlserver”命令手动启动SQL Server数据库。
11. SQL Server有Linux版本吗?
有。微软在2016年推出了Linux系统的SQL Server预览版,并于2017年全面发布这款产品。微软发言人表示,预览版已经支持Ubuntu,该公司今后还将支持红帽企业版Linux和其他平台。
12. SQL Server、Access、Oracle三种数据库之间的区别是什么?
Access是一种桌面数据库,只适合于数据量少的应用系统,在处理少量数据和单机访问的数据时是很好的,效率也很高。但是Access数据库有一定的极限,如果数据达到100MB左右,那么很容易造成Access假死,或者消耗掉服务器的内存导致服务器崩溃。 SQL Server是基于服务器端的中型数据库,适合大容量数据的应用,在处理海量数据的效率、后台开发的灵活性、可扩展性等方面强大。因为现在数据库都使用标准的SQL语言对数据库进行管理,所以,如果是标准SQL语言,那么两者基本上都可以通用的。SQL Server还有更多的扩展,可以用存储过程、函数等。 Oracle是基于服务器的大型数据库,主要应用于银行、证券类业务等。
[考点] 维护操作
13. SQL Server的两种存储结构是什么?
SQL Server的两种存储结构是页与区间。 1)页:用于数据存储的连续的磁盘空间块,SQL Server中数据存储的基本单位是页,磁盘I/O操作在页级执行,页的大小为8KB,每页的开头是96字节的页头,用于存储有关页的系统信息,包括页码、页类型、页的可用空间以及拥有该页的对象的分配单元ID。 2)区间:区是管理空间的基本单位,一个区是8个物理上连续的页(即64KB)的集合,所有页都存储在区中。SQL Server有两种类型的区:统一区和混合区。 ①统一区:由单个对象所有,区中的所有8页只能由一个对象使用。 ②混合区:最多可由8个对象共享。区中8页中的每页可以由不同对象所有,但是一页总是只能属于一个对象。
[考点] 维护操作
14. 在SQL Server中,请用SQL创建一张本地临时表和全局临时表,里面包含两个字段ID和IDVALUES,类型都是INT型,并解释两者的区别?
在SOL Server中,临时表有两种类型:本地临时表和全局临时表。临时表与永久表相似,但临时表存储在Tempdb中,当不再使用时会自动删除。本地临时表只对创建这个表的用户的SESSION可见,对其他进程是不可见的。当创建它的进程消失时,这个临时表就自动删除。本地临时表的名称以单个数字符号(#)打头。全局临时表对整个SQL Server实例都可见,但是所有访问它的SESSION都消失的时候,它也自动删除。全局临时表的名称以两个数字符号(##)打头。它们的创建语句如下: 本地临时表:CREATE TABLE #XX (ID INT,IDVALUES INT); 全局临时表:CREATE TABLE ##XX(ID INT,IDVALUES INT);
[考点] 维护操作
15. SQL server物理有哪几种类型的文件?
SOL Server数据库文件组成如下:
1)主数据文件:默认扩展名为.mdf。
2)辅助数据文件:默认扩展名为.ndf(一个数据库可以创建多个.ndf文件)。
3)事务日志文件:默认扩展名为.1df(记录对数据库的所有操作,但不包含所操作的数据)。
所有的数据文件和日志文件默认位置在C:/ProgramFiles/MicrosoftSQLServer/MSSQL.n/MSSQL/Data(其中,n是标识已安装的SQL Server实例名称一实例名)。需要注意的是,应当将所有的数据和对象存储在.ndf文件中,而.mdf文件只负责存储数据目录,这样可以有效地避免访问时的磁盘争用。
物理文件组成也可以参考下图。
[考点] 维护操作
16. SQL Server中的锁分为哪几种?
SQL Server中的锁分为以下几种:
名称
简介
何时使用
读
写
共享锁(Share Lock, S锁,读锁)
S锁是可以查看但无法修改和删除的一种数据锁。若事务T对数据对象 A加上S锁,则事务T只能读A;其他事务只能再对A加S锁,而不能加 X锁,直到T释放A上的S锁。这就保证了其他事务可以读A,但在T释 放A上的S锁之前不能对A做任何修改
当执行SELECT时,数 据库会自动使用S锁
Y
N
排它锁(eXclusive Lock,X锁,独占锁, 写锁,互斥锁)
如果事务T对数据A加上X锁后,则其他事务不能再对A加任何类型 的锁。获得X锁的事务既能读数据,又能修改数据
执行INSERT、UPDATE、 DELETE时数据库会自 动使用X锁
Y
Y
更新锁(Update Lock,U锁)
U锁意味着事务即将要使用X锁,它目前正在扫描数据,以确定要使用 X锁锁定的那些行。它用于可更新的资源中,防止当多个会话在读取、锁 定以及随后可能进行的资源更新时发生常见形式的死锁。使用U锁可以提 高处理并发查询的吞吐量
读阶段 操作阶段 更新阶段
Y Y N
N N N
意向锁(Intent Lock, I锁)
I锁是一种用于警示的锁,用于建立锁的层次结构。I锁包含三种类型: 意向共享(IS)、意向排它(IX)和意向排它共享(SIX)
锁的标记
架构锁(Schema Lock,Sch锁,模式 锁)
Sch锁分为架构修改(Schema Modify,Sch-M)锁和架构稳定性(Schema Stability,Sch-S)锁。拥有Sch-M锁期间,Sch-M锁将阻止对表进行并发 访问。这意味着Sch—M锁在释放前将阻止所有外围操作。某些DML操作 使用sch-M锁阻止并发操作访问受影响的表。Sch-S锁不会阻止某些事务 锁,其中包括X锁。因此,在编译查询的过程中,其他事务(包括那些针 对表使用X锁的事务)将继续运行。但是,无法针对表执行获取Sch-M锁 的并发DDL操作和并发DML操作
当修改表结构时使用, 即数据库引擎在执行 DDL操作(例如,添加列 或删除表)的过程中使用 Sch-M锁。当数据库引擎 在编译和执行查询时使 用Sch-S锁
N
N
大容量更新锁(Bulk Update Lock,BU锁)
数据库引擎在将数据大规模复制到表中时,指定TABLOCK提示或使用 sp_tableoption选项(将数据表设置为table lock on bulk load),则是使用BU 锁。BU锁允许多个线程将数据并发地大容量加载到同一表,以降低数据 表的锁定竞争,同时防止其他不进行大容量加载数据的进程访问该表
在向表进行大容量数 据复制且指定了 TABLOCK提示时使用
N
N
在以上表格中需要注意的是,同一资源可以加多个S锁,但是只能加一个X锁。
[考点] 锁
17. SQL Servet如何查询阻塞?
SQL Server的阻塞查询主要来自sys.sysprocesses。通常在处理时需要加入其他相关的视图或表,例如sys.dm_exec_connections,sys.dm_exec_sql_text。
[考点] 锁
18. SQL Server提供的3种恢复模型都是什么?有什么区别?
SQL Servet提供了3种恢复模型,分别是:
1)简单恢复,允许将数据库恢复到最新的备份。
2)完全恢复,允许将数据库恢复到故障点状态。
3)大容量日志记录恢复,允许大容量日志记录操作。
这些模型都是针对不同的性能、磁盘和磁带空间以及保护数据丢失的需要。例如,当选择恢复模型时,必须考虑下列业务要求之间的权衡:
1)大规模操作的性能(如创建索引或大容量装载)。
2)数据丢失表现(如已提交的事务丢失)。
3)事务日志空间损耗。
4)备份和恢复过程的简化。
根据正在执行的操作,可以有多个适合的模型。选择了恢复模型后,设计所需的备份和恢复过程。下表提供了三种恢复模型的优点和含义的概述。
恢复模型
优点
工作损失表现
能否恢复到即时点?
简单恢复
1)允许高性能大容量复制操作 2)收回日志空间以使空间要求最小
必须重做自最新的数据库或差异备份后所发生 的更改
可以恢复到任何备份的结 尾处,随后必须重做更改
完全恢复
1)数据文件丢失或损坏不会导致工作损失 2)可以恢复到任意即时点(例如,应用 程序或用户错误之前)
1)正常情况下没有 2)如果日志损坏,那么必须重做自最新的目志 备份后所发生的更改
可以恢复到任何即时点
大容量日志 记录恢复
允许高性能大容量复制操作。大容量操作 使用最少的日志空间
如果日志损坏,或者自最新的日志备份后发生了 大容量操作,那么必须重做自上次备份后所做的更 改,否则不丢失任何工作
可以恢复到任何备份的结 尾处,随后必须重做更改
简单恢复所需的管理最少。在简单恢复模型中,数据只能恢复到最新的完整数据库备份或差异备份的状态。不使用事务日志备份,而使用最小事务日志空间。一旦不再需要日志空间从服务器故障中恢复,日志空间便可重新使用。与完整模型或大容量日志记录模型相比,简单恢复模型更容易管理,但如果数据文件损坏,那么数据损失表现会更高。
完全恢复和大容量日志记录恢复模型为数据提供了最大的保护性。这些模型依靠事务日志提供完全的可恢复性,并防止最大范围的故障情形所造成的工作损失。完全恢复模型提供最大的灵活性,可将数据库恢复到更早的即时点。
大容量日志记录模型为某些大规模操作(如创建索引或大容量复制)提供了更高的性能和更低的日志空间损耗,不过这将牺牲时间点恢复的某些灵活性。很多数据库都要经历大容量装载或索引创建的阶段,因此可能希望在大容量日志记录模型和完全恢复模型之间进行切换。
[考点] 备份恢复