数据库面试准备

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

4、 创建数据库表代码：-创建数据库表-use studbgoif exists (select * from 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 (selec

5、t * from sysobjects where name='stumarks')drop table stumarkscreate 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)-添加

6、主键pk_stuno是自定义的主键名也可以省略alter table stuinfo add constraint uq_stuid unique (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 stumark

7、sadd constraint fk_stuno foreign key(stuno) references stuinfo(stuno)go5.删除约束-删除约束-alter table stuinfodrop constraint 约束名-如：fk_stuno ck_stuage df_stuaddress uq_stuid pk_stuno面试准备之sql 3数据管理1. t-sql 条件表达式：(1).常量：由一个或多个字母、数字或符号（！ #）组成(2).一元运算符： 仅有一个操作数的运算符 +表示整数 表示负数 表示补数运算符(3).二元运算符：组合两个操作数的运算符 包含 算术运

8、算符 赋值运算符（=） 位运算符 比较运算符 逻辑运算符 字符串串联运算符（+） 或一元运算符= > < >= <= <> （不等于） !（非） (4) .'_' ： 一个字符 a like 'c_' 至多至少两个字符 % ：任意长度字符串 a like 'c% ： 区间 指定范围内的数据 a like '1-9' ：不在该区间（指定范围内）的数据(5).逻辑运算符：and or notin关键字 用来限制返回 可以是多个值2.t-sql 插入数据insert into 表名（列，列，列。） value

9、s(值列、)将现有的表的数据添加到新的已存在的表中： inster into newtable 新表(列) select 列 from 旧表 （可以这样记：好似英语一样：从旧表查询数据插入到新表） 将现有的表的数据添加到新的不存在的表中：select 列 into 新表 from 旧表 以上语句由于标识列不允许被指定，因此我们要创建一个新的标识列，语法如下：select 其他列, identity(数据类型，标识种子，标识增长量) as 列名into 新表form 旧表通过union关键字合并数据进行插入insert 表名（列名）select 列值 unionselect 列值 unionse

10、lect 列值 unionselect 列值 最后一行就不需要union了3.使用t-sql更新数据update 表名 set 列名=新的值 where 条件4.使用t-sql删除数据delete from 表名 where 条件面试准备之sql 4 数据查询use mastergo-创建数据库-if exists (select * from sysdatabases where name='studb')drop database studbcreate database studbon primary(name='studb_data',filename=

11、'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 * from sysobjects where name='stuinfo')drop table stuinfocreate table stuinfo( stuid int identity(1

12、,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='stumarks')drop table stumarkscreate table stumarks( marksid int identity(1,1) primary key n

13、ot 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('张秋丽','s25301','男',18,'北京海淀')insert into stuinfo(stuname,stuno,

14、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','

15、男',28,'威武哈')-向学员成绩表stumarks插入数据-insert into stumarks(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

16、) values('e2005070003','s25303',97,82)-查看数据-select * from stuinfoselect * from stumarks/*=查询数据练习=*/ -1.查询两表的数据-select * from stuinfoselect * from stumarks -2.查询男学员名单- select * from stuinfo where stusex='男' -3.查询笔试成绩优秀的学员情况（成绩在100之间)- select * from stumarks where writtenexam b

17、etween 75 and 100 -4.查询参加本次考试的学员成绩，包括学员姓名，笔试成绩，机试成绩- select i.stuname,m.writtenexam,m.labexam from stuinfo as i inner join stumarks as m on m.stuno = i.stuno -5.统计笔试考试平均分和机试考试平均分- select avg(writtenexam) as 笔试平均成绩,avg(labexam) as 机试平均成绩from stumarksselect avg(writtenexam) 笔试平均成绩,avg(labexam) 机试平均成绩f

18、rom stumarks -6.统计参加本次考试的学员人数select count(stuno) from stumarks -7.查询没有通过考试的人数（笔试或机试小于分）- select count(stuno) from stumarks where writtenexam <= 60 or labexam<=60select * from stumarks where writtenexam is null or labexam is null -查询为全部参加考试的信息 -8.查询学员成绩，显示学号，笔试成绩，机试成绩，平均分- select stuno as 学号,wr

19、ittenexam 笔试,labexam 机试,(writtenexam+labexam)/2 平均成绩from stumarks -9.排名次（按平均分从高到低排序），显示学号、平均分- select stuno as 学号,(writtenexam+labexam)/2 平均成绩from stumarks order by (writtenexam+labexam)/2 descselect stuno as 学号,(writtenexam+labexam)/2 平均成绩from stumarks order by 平均成绩desc -10.排名次（按平均分从高到低排序），显示姓名，笔试成

20、绩，机试成绩，平均分-select i.stuno as 学号,writtenexam 笔试,labexam 机试,(writtenexam+labexam)/2 平均成绩 from stumarks as m inner join stuinfo as i on m.stuno = i.stuno order by 平均成绩desc-根据以上sql语句总结：凡是两个表中有同名的列名就需要用别名却分开来，如果没用别名可以直接查询列明 -11.根据平均分，显示前两名信息，包括姓名、笔试成绩、机试成绩、平均分- select top 2 i.stuno as 学号,writtenexam 笔试,l

21、abexam 机试,(writtenexam+labexam)/2 平均成绩 from stumarks as m inner join stuinfo as i on m.stuno = i.stuno order by 平均成绩desc/*=修改数据练习=*/ -都提分-100分封顶（加分后超过分的，按分计算）-update stumarks set writtenexam = writtenexam + 5update stumarks set writtenexam = 100 where writtenexam>100面试准备之sql 5 事务、索引和视图1. 什么是事务？事务

22、是一种机制、一种操作序列，它包含了一组数据库操作命令，并且所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库要么都执行，要么都不执行。特别适用于多用户同时操作的数据库系统。事务是作为单个逻辑工作单元执行的一系列操作。一个逻辑工作单位必须有4个属性：原子性：事务是一个完整的操作，事务的各元素不可再分。所有元素必须作为一个整体提交或回滚。一致性：当事务完成时，数据必须处于一致状态。隔离性：对数据进行修改时所有并发事务是彼此隔离的。持久性：事务完成后，对系统影响是永久性的。2.创建事务开始事务：begin transaction提交事务：commit transaction回滚（撤销

23、）事务：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 check(currentmoney >= 1)goinsert into bank val

24、ues('张三',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 where custome

25、rname='张三'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

26、 transaction endgoprint '事务后的数据：'select * from bank- * * * * 索* * 引* * * * -索引：它是sql server编排数据的内部方法-索引可以分为以下三种;-唯一索引：不允许两行具有相同的索引值-主键索引：为表定义主键时自动创建唯一索引的特殊类型主键索引要求主键中的每一个值都是唯一的-聚集索引：表中各行的物理顺序与键值的逻辑（索引）顺序相同，表中只能包含一个聚集索引（可以理解为字典的拼音）。-非聚集索引：数据和索引包含指向数据存储的相应位置表中的各行的物理顺序与键值的逻辑顺序不匹配。（可以理解为map）-聚集索

27、引比非聚集索引速度更快-在一个表中只能有一个聚集索引，但是可以有多个非聚集索引，设置某列为主键该列就默认为聚集索引了。-表是可以没有索引的，主键索引不一定就是聚集索引。-索引运用到哪里？-该列频繁被搜索，该列用于对数据排序-劣种就几个不同的值，表中就几行数据就没必要使用索引-语法-create uniqueclustered|nonclustered index index_name on table_name (column_name,column_name.)- with fillfactor = x -填充因子x 为100之间的值-代码use studbgoif exists (sele

28、ct name from sysindexes where name='ix_stumarks_writtenexam')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)-会报错：&

29、#39;index' 附近有语法错误。如果它要作为表提示的一部分，则必须有with 关键字和圆括号，如：select * from stumarks with(index=ix_stumarks_writtenexam) select * from stumarks with(index=ix_stumarks_writtenexam) where writtenexam between 60 and 90- * * * * 视* * 图* * * * -视图：是一种虚拟表，基于一个表或多个表的数据的查询方法。一般作用：筛选表中的行、防止未经许可的用户访问敏感数据、将多个物理数据表抽象

30、为一个逻辑数据表-语法：-create view view_name-as-<select 语句>代码use studbgoif exists(select * from sysobjects where name='view_stuinfo_stumarks')drop view view_stuinfo_stumarksgocreate view view_stuinfo_stumarksasselect 姓名=stuname,学号=stuinfo.stuno,笔试成绩=writtenexam,机试成绩=labexam,平均分=(writtenexam+labe

31、xam)/2 from stuinfo left join stumarks on stuinfo.stuno = stumarks.stunogo面试准备之sql 6 存储过程1.什么是存储过程?存储过程是一次编译可多次运行（存储过程存放在服务器中），预编译好的集合，运行速度快。2.常用系统存储过程代码 - purpose: 常用系统存储过程使用exec sp_databases -列出当前系统中的数据库exec sp_renamedb 'test','test1'-改变数据库名称(单用户访问)use studbgoexec sp_tables -当前数据库中

32、查询的对象的列表exec sp_columns stuinfo -返回某个表列的信息exec sp_help stuinfo -查看表stuinfo的信息exec sp_helpconstraint stuinfo -查看表stuinfo的约束exec sp_helpindex stumarks -查看表stumarks的索引exec sp_helptext 'view_stuinfo_stumarks' -查看视图的语句文本exec sp_stored_procedures -返回当前数据库中的存储过程列表 use mastergoexec xp_cmdshell '

33、mkdir d:bank',no_output-创建文件夹 3.自定义存储过程（.）不带参数的存储过程代码use studbgoif exists(select * from sysobjects where name='proc_stu')drop proc proc_stugocreate procedure proc_stu as declare writtenavg float,labavg float -笔试和机试平均分变量 select writtenavg=avg(writtenexam), labavg=avg(labexam) from stumark

34、s print '笔试平均分：'+convert(varchar(5),writtenavg) print '机试平均分：'+convert(varchar(5),labavg) if (writtenavg>70 and labavg>70) print '本班考试成绩：优秀' else print '本班考试成绩：较差' print '-' print ' 参加本次考试没有通过的学员：' select stuname,stuinfo.stuno,writtenexam,labexam

35、 from stuinfo inner join stumarks on stuinfo.stuno=stumarks.stuno where writtenexam<60 or labexam<60 goexec proc_stu -执行存储过程（.）带输入参数的存储过程代码use studbgo/*-检测是否存在：存储过程存放在系统表sysobjects中-*/if exists (select * from sysobjects where name = 'proc_stu' ) drop procedure proc_stugo/*-创建存储过程-*/cre

36、ate 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,stuinfo.stuno,writtenexam,labexam from stuinfo inner join stumarks on stuinfo.stuno=stumarks.stuno where writtenexam<writtenpass or labexam<labpass goexec proc_stu 60,55exec proc_s