《数据库面试准备.doc》由会员分享，可在线阅读，更多相关《数据库面试准备.doc（13页珍藏版）》请在三一文库上搜索。
1、面试准备之SQL 111. 设计数据库的步骤：（1）.需求分析阶段（2）.概要设计阶段:绘制E-R模型图（3）.详细设计阶段：E-R模型图转换为数据库表（4）.代码编写阶段（5）.测试阶段（6）.部署安装 2.概要设计阶段：（1）.收集信息（2）.标识对象（3）.标识每一个对象需要存储的详细信息（4）.标识对象之间的关系如：第一步我们一般是看需求分析文档，然后第二步找出实体 第三步设置对应的实体类的属性 最后就是设置各个实体之间的关系（比如主外键关系 或 一对多 多对一 多对多等关系） 3.绘制E-R图（1）.实体（2）.属性（3）.关系（4）.映射基数（5）.实体关系图矩形表示实体集椭圆表示
2、属性菱形表示关系集直线用来连接属性和实体集，也用来联系实体集合关系集 直线可以有方向（箭头）用来表示关系集的映射基数：1:1,1:N,M:N 4.三大范式第一范式：确保每列的原子性；第二范式：在第一范式的基础上， 是否存在部分依赖的关系（针对复合主键） 是否只描述一件事情；第三范式：在第二范式的基础上，不存在着传递依赖的关系。面试准备之SQL 2数据库的实现（T-SQL建库建表）1. 数据库文件包括:主数据文件：*.mdf次要数据文件：*.ndf日志文件：*.ldf ( l 是 L 的小写)2.使用T-SQL创建数据库代码：use mastergo-创建数据库-if exists (selec
3、t * from sysdatabases where name=stuDB)drop database stuDBcreate database stuDBon primary(name=stuDB_data,filename=D:stuDB_data.mdf,size=3mb,maxsize=10mb,filegrowth=1mb)log on(name=stuDB_log,filename=D:stuDB_data.ldf,size=1mb,filegrowth=1mb)3.使用T-SQL 创建数据库表代码：-创建数据库表-use stuDBgoif exists (select * f
4、rom sysobjects where name=stuInfo)drop table stuInfocreate table stuInfo( stuName varchar(20) not null, stuNo char(6) not null, stuAge int not null, stuID numeric(18,0),-身份证 stuSeat smallint identity(1,1), stuAddress text)goif exists (select * from sysobjects where name=stuMarks)drop table stuMarksc
5、reate table stuMarks( ExmaNo char(7) not null, -考号 stuNo char(6) not null,-学号 writtenExam int not null,-笔试成绩 LabExam int not null-机试成绩)go4. 添加约束代码：-添加约束-alter table stuinfo -修改stuinfo表add constraint PK_stuNo primary key (stuNo)-添加主键PK_stuNo是自定义的主键名也可以省略alter table stuinfo add constraint UQ_stuID uni
6、que (stuID) -添加唯一约束alter table stuinfo add constraint DF_stuAddress default (地址不详) for stuAddress-添加默认 不填默认地址不详alter table stuinfo add constraint CK_stuAge check(stuAge between 18 and 60) -添加检查约束18-60岁alter table stuMarksadd constraint FK_stuNo foreign key(stuNo) references stuInfo(stuNo)go5.删除约束-删除
7、约束-alter table stuinfodrop constraint 约束名-如：FK_stuNo CK_stuAge DF_stuAddress UQ_stuID PK_stuNo面试准备之SQL 3数据管理1. T-SQL 条件表达式：(1).常量：由一个或多个字母、数字或符号（！ #）组成(2).一元运算符： 仅有一个操作数的运算符 +表示整数 表示负数 表示补数运算符(3).二元运算符：组合两个操作数的运算符 包含 算术运算符 赋值运算符（=） 位运算符 比较运算符 逻辑运算符 字符串串联运算符（+） 或一元运算符= = = （不等于） !（非） (4) ._ ： 一个字符 A
8、LIKE C_ 至多至少两个字符 % ：任意长度字符串 A LIKE C% ： 区间 指定范围内的数据 A LIKE 1-9 ：不在该区间（指定范围内）的数据(5).逻辑运算符：And Or NotIn关键字 用来限制返回 可以是多个值2.T-SQL 插入数据Insert into 表名（列，列，列。） values(值列、)将现有的表的数据添加到新的已存在的表中： inster into newtable 新表(列) select 列 from 旧表 （可以这样记：好似英语一样：从旧表查询数据插入到新表） 将现有的表的数据添加到新的不存在的表中：select 列 into 新表 from 旧
9、表 以上语句由于标识列不允许被指定，因此我们要创建一个新的标识列，语法如下：select 其他列, identity(数据类型，标识种子，标识增长量) as 列名into 新表form 旧表通过Union关键字合并数据进行插入insert 表名（列名）select 列值 unionselect 列值 unionselect 列值 unionselect 列值 最后一行就不需要union了3.使用T-SQL更新数据update 表名 set 列名=新的值 where 条件4.使用T-SQL删除数据delete from 表名 where 条件面试准备之SQL 4 数据查询use mastergo
10、-创建数据库-if exists (select * from sysdatabases where name=stuDB)drop database stuDBcreate database stuDBon primary(name=stuDB_data,filename=D:stuDB_data.mdf,size=3mb,maxsize=10mb,filegrowth=1mb)log on(name=stuDB_log,filename=D:stuDB_data.ldf,size=1mb,filegrowth=1mb)-创建数据库表-use stuDBgoif exists (select
11、 * from sysobjects where name=stuInfo)drop table stuInfocreate table stuInfo( stuId int identity(1,1) primary key not null, stuName varchar(20) not null, stuNo varchar(20) not null, stuSex char(6) not null, stuAge int not null, stuAddress text null)goif exists (select * from sysobjects where name=st
12、uMarks)drop table stuMarkscreate table stuMarks( marksId int identity(1,1) primary key not null, ExamNo varchar(50) not null, -考号 stuNo char(6) not null,-学号 writtenExam int null,-笔试成绩 LabExam int null-机试成绩)go-向学员信息表stuInfo插入数据-INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress)VALUES(张秋丽,s25
13、301,男,18,北京海淀)INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress) VALUES(李斯文,s25303,女,22,河南洛阳)INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge) VALUES(李文才,s25302,男,31)INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress) VALUES(欧阳俊雄,s25304,男,28,威武哈)-向学员成绩表stuMarks插入数据-INSERT INTO stuMar
14、ks(ExamNo,stuNo,writtenExam,LabExam) VALUES(E2005070001,s25301,80,58)INSERT INTO stuMarks(ExamNo,stuNo,writtenExam) VALUES(E2005070002,s25302,50)INSERT INTO stuMarks(ExamNo,stuNo,writtenExam,LabExam) VALUES(E2005070003,s25303,97,82)-查看数据-select * from stuInfoselect * from stuMarks/*=查询数据练习=*/ -1.查询两
15、表的数据-select * from stuInfoselect * from stuMarks -2.查询男学员名单- select * from stuInfo where stuSex=男 -3.查询笔试成绩优秀的学员情况（成绩在100之间)- select * from stumarks where writtenexam between 75 and 100 -4.查询参加本次考试的学员成绩，包括学员姓名，笔试成绩，机试成绩- select i.stuName,m.writtenExam,m.LabExam from stuInfo as i inner join stuMarks
16、as m on m.stuNo = i.stuNo -5.统计笔试考试平均分和机试考试平均分- select avg(writtenExam) as 笔试平均成绩,avg(LabExam) as 机试平均成绩from stuMarksselect avg(writtenExam) 笔试平均成绩,avg(LabExam) 机试平均成绩from stuMarks -6.统计参加本次考试的学员人数select count(stuno) from stumarks -7.查询没有通过考试的人数（笔试或机试小于分）- select count(stuno) from stumarks where wri
17、ttenExam = 60 or labexam100面试准备之SQL 5 事务、索引和视图1. 什么是事务？事务是一种机制、一种操作序列，它包含了一组数据库操作命令，并且所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库要么都执行，要么都不执行。特别适用于多用户同时操作的数据库系统。事务是作为单个逻辑工作单元执行的一系列操作。一个逻辑工作单位必须有4个属性：原子性：事务是一个完整的操作，事务的各元素不可再分。所有元素必须作为一个整体提交或回滚。一致性：当事务完成时，数据必须处于一致状态。隔离性：对数据进行修改时所有并发事务是彼此隔离的。持久性：事务完成后，对系统影响是永久性的
18、。2.创建事务开始事务：begin transaction提交事务：commit transaction回滚（撤销）事务：rollback transaction代码 use studbgoif exists(select * from sysobjects where name = bank)drop table bankcreate table bank( customerName char(10), -顾客姓名 currentMoney money -余额)go-增加检查约束账户余额不能小于alter table bankadd constraint CK_currentMoney ch
19、eck(currentMoney = 1)goinsert into bank values(张三,1000)insert into bank values(李四,1)select * from bank- * * * * 事* * 务* * * * -use studbgoset nocount on -不显示受影响的行数信息print 事务之前的数据：select * from bankgobegin transaction declare errorSum intset errorSum=0update bank set currentMoney = currentMoney-1000
20、where customername=张三update bank set currentMoney = currentMoney+1000 where customername=李四set errorSum = errorSum + 1 print 事务中的数据：select * from bankif errorSum 0 begin print 交易失败，回滚事务 rollback transaction endelse begin print 交易成功，提交事务，写入硬盘，永久的保存 commit transaction endgoprint 事务后的数据：select * from b
21、ank- * * * * 索* * 引* * * * -索引：它是SQL Server编排数据的内部方法-索引可以分为以下三种;-唯一索引：不允许两行具有相同的索引值-主键索引：为表定义主键时自动创建唯一索引的特殊类型主键索引要求主键中的每一个值都是唯一的-聚集索引：表中各行的物理顺序与键值的逻辑（索引）顺序相同，表中只能包含一个聚集索引（可以理解为字典的拼音）。-非聚集索引：数据和索引包含指向数据存储的相应位置表中的各行的物理顺序与键值的逻辑顺序不匹配。（可以理解为MAP）-聚集索引比非聚集索引速度更快-在一个表中只能有一个聚集索引，但是可以有多个非聚集索引，设置某列为主键该列就默认为聚集索
22、引了。-表是可以没有索引的，主键索引不一定就是聚集索引。-索引运用到哪里？-该列频繁被搜索，该列用于对数据排序-劣种就几个不同的值，表中就几行数据就没必要使用索引-语法-create uniqueclustered|nonclustered index index_name on table_name (column_name,column_name.)- with fillfactor = x -填充因子x 为100之间的值-代码use studbgoif exists (select name from sysindexes where name=IX_stuMarks_writtenEx
23、am)drop index stuMarks.IX_stuMarks_writtenExam -查询是否已经存在该索引如果存在就删除create nonclustered index IX_stuMarks_writtenExam on stuMarks(writtenExam) with fillfactor=30 -填充因子预留空间go-查询select * from stumarks (index=IX_stuMarks_writtenExam)-会报错：index 附近有语法错误。如果它要作为表提示的一部分，则必须有WITH 关键字和圆括号，如：select * from stumar
24、ks with(index=IX_stuMarks_writtenExam) select * from stumarks with(index=IX_stuMarks_writtenExam) where writtenExam between 60 and 90- * * * * 视* * 图* * * * -视图：是一种虚拟表，基于一个表或多个表的数据的查询方法。一般作用：筛选表中的行、防止未经许可的用户访问敏感数据、将多个物理数据表抽象为一个逻辑数据表-语法：-create view view_name-as-代码use studbgoif exists(select * from s
25、ysobjects where name=view_stuinfo_stumarks)drop view view_stuinfo_stumarksgocreate view view_stuinfo_stumarksasselect 姓名=stuname,学号=stuinfo.stuno,笔试成绩=writtenexam,机试成绩=labexam,平均分=(writtenexam+labexam)/2 from stuinfo left join stumarks on stuinfo.stuno = stumarks.stunogo面试准备之SQL 6 存储过程待添加的隐藏文字内容21.什
26、么是存储过程?存储过程是一次编译可多次运行（存储过程存放在服务器中），预编译好的集合，运行速度快。2.常用系统存储过程代码 - Purpose: 常用系统存储过程使用EXEC sp_databases -列出当前系统中的数据库EXEC sp_renamedb test,test1-改变数据库名称(单用户访问)USE stuDBGOEXEC sp_tables -当前数据库中查询的对象的列表EXEC sp_columns stuInfo -返回某个表列的信息EXEC sp_help stuInfo -查看表stuInfo的信息EXEC sp_helpconstraint stuInfo -查看表
27、stuInfo的约束EXEC sp_helpindex stuMarks -查看表stuMarks的索引EXEC sp_helptext view_stuInfo_stuMarks -查看视图的语句文本EXEC sp_stored_procedures -返回当前数据库中的存储过程列表 use mastergoexec xp_cmdshell mkdir D:bank,no_output-创建文件夹 3.自定义存储过程（.）不带参数的存储过程代码use studbgoif exists(select * from sysobjects where name=proc_stu)drop proc
28、 proc_stugocreate procedure proc_stu AS DECLARE writtenAvg float,labAvg float -笔试和机试平均分变量 SELECT writtenAvg=AVG(writtenExam), labAvg=AVG(labExam) FROM stuMarks print 笔试平均分：+convert(varchar(5),writtenAvg) print 机试平均分：+convert(varchar(5),labAvg) IF (writtenAvg70 AND labAvg70) print 本班考试成绩：优秀 ELSE prin
29、t 本班考试成绩：较差 print - print 参加本次考试没有通过的学员： SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExam60 OR labExam60 GOexec proc_stu -执行存储过程（.）带输入参数的存储过程代码USE stuDBGO/*-检测是否存在：存储过程存放在系统表sysobjects中-*/IF EXISTS (SELECT * FROM syso
30、bjects WHERE name = proc_stu ) DROP PROCEDURE proc_stuGO/*-创建存储过程-*/CREATE PROCEDURE proc_stu writtenPass int, labPass int- 可以添加默认值这样执行可以是这样的exec proc_stu 不用指定参数了 AS print 笔试及格线：+convert(varchar(5),writtenPass) print 机试及格线：+convert(varchar(5),labPass) print - print 参加本次考试没有通过的学员： SELECT stuName,stuI
31、nfo.stuNo,writtenExam,labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExamwrittenPass OR labExamlabPass GOexec proc_stu 60,55exec proc_stu 60,labPass=55exec proc_stu writtenPass=60,labPass=55exec proc_stu writtenPass=60,55-这一行会报错：必须传递参数2，并以name = value 的形式传递后续的参
32、数。- 一旦使用了name = value 形式之后，所有后续的参数就必须以name = value 的形式传递 （.）带输出参数的存储过程代码USE stuDBGO/*-检测是否存在：存储过程存放在系统表sysobjects中-*/IF EXISTS (SELECT * FROM sysobjects WHERE name = proc_stu ) DROP PROCEDURE proc_stuGO/*-创建存储过程-*/CREATE PROCEDURE proc_stu notpassSum int OUTPUT, -OUTPUT关键字，否则视为输入参数 writtenPass int=6
33、0, -默认参数放后 labPass int=60 -默认参数放后 AS print 笔试及格线：+convert(varchar(5),writtenPass) + 机试及格线：+convert(varchar(5),labPass) print - print 参加本次考试没有通过的学员： SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExamwrittenPass OR labExamlabPass /*-统计并返回没有通过考试的学员人数-*/ SELECT notpassSum=COUNT(stuNo)FROM stuMarks WHERE writtenExamwrittenPass OR labExam=3 print 未通过人数：+convert(varchar(5),sum)+ 人,超过%,及格分数线还应下调ELSE print 未通过人数：+convert(varchar(5),sum)+ 人,已控制在%以下，及格分数线适中GO