关于SQL SERVER数据库学习总结.docx
《关于SQL SERVER数据库学习总结.docx》由会员分享，可在线阅读，更多相关《关于SQL SERVER数据库学习总结.docx（8页珍藏版）》请在冰豆网上搜索。
关于SQLSERVER数据库学习总结
关于SQLSERVER数据库学习总结
对于SQLSERFVER数据库也学了有一阵子了，自己也对自己所学做了一些总结。
我们首先学习数据库设计的一些知识点和用SQL语句建库。
设计数据库步骤:
需求分析阶段，概要设计阶段，详细设计阶段，建数据库的SQL语句如下（包含了如果有该数据库先删除在建立数据库）usemaster
GO
ifexists（select*fromsysdatabaseswherename='Wages'）
DROPdatabaseWages
CREATEDATABASEWages
ON
（
NAME='Wages_data',
FILENAME='e:
\project\Wages_data.mdf',
SIZE=5mb,
FILEGROWTH=15%
）
LOGON
（
NAME='Wages_log',
FILENAME='e:
\project\Wages_log.ldf',
SIZE=3mb,
FILEGROWTH=15%
）
GO为了创建良好的数据库需满足三大范式。
下面是创建表的SQL语句（包含了如果有该表先删除在建表）USEWages
GO
ifexists（select*fromsysobjectswherename='WageInfo'）
DROPtableWageInfo
CREATETABLEWageInfo
（
CompanyIDINTprimarykeyIDENTITY（1,1）,
CompanyNamevarchar（50）NOTNULL,
Postevarchar（50）NOTNULL,
）
GO
ifexists（select*fromsysobjectswherename='StudentInfo'）
DROPtableStudentInfo
CREATETABLEStudentInfo
（
IDINTprimarykeyIDENTITY（1,1）,
NameVARCHAR（50）NOTNULL,
CompanyIDINT,
PosteDateDATETIME,
Wageint,
）
GO下面是创建约束语法如下altertable表名addconstraint约束名约束类型具体的约束说明示例创建外键约束altertableStudentInfo
addconstraintpk_CompanyIDforeignkey（CompanyID）referencesWageInfo（CompanyID）
GO插入数据语句如下insertintoWageInfo（CompanyName,Poste）values
（'微软','职员'）,
（'XX','职员'）,
（'腾讯','职员'）,
（'爱奇艺','职员'）,
（'华为','职员'）
insertintoStudentInfo（Name,CompanyID,PosteDate,Wage）values
（'张三',1,'2016-05-05',2500）,
（'李四',2,'2016-05-06',2600）,
（'王五',3,'2016-05-07',3000）,
（'赵二',4,'2016-05-08',4000）,
（'钱一',5,'2016-05-09',5000）
insertintoStudentInfo（Name,CompanyID,PosteDate,Wage）values（'钱二',null,'2016-05-09',NULL）然后我们学习了变量，变量分全局变量和局部变量。
创建变量语法如下是declare@变量名数据类型局部变量有两种赋值方法set@变量名=valueselect@变量名=value区别是select可一次对多个变量赋值，set只能一次对一个变量赋值。
全局变量只能用不能自己创建和赋值！
！
！
输出语句print和selectuseMySchoolgoselect*fromStuInfos1.班级表班级编号（主键）班级名（长度固定3位）班级人数（默认30）ifexists（select*fromsysobjectswherename='Classes'）droptableClassesgocreatetableClasses（clsidintidentity（1,1）,--班级编号'clsnamevarchar（3）,--班级名称clsnumsint--班级人数）go'altertableClassesaddconstraintpk_clsid_Classesprimarykey（clsid）altertableClassesaddconstraintck_clsname_Classescheck（len（clsname）=3）altertableClassesaddconstraintdf_clsnums_Classesdefault30forclsnumsgoinsertintoClassesselect't10',25unionselect't11',18unionselect't12',232.外键约束为学生表添加一个clsid列来表示其班级altertableStuInfosaddclsidint外键约束altertableStuInfoswithnocheck--不对表现有的数据作限制操作addconstraintfk_StuInfos_Classesforeignkey（clsid）referencesClasses（clsid）删除约束altertableStuInfosdropconstraintfk_StuInfos_Classes3.建表同时建约束,老师表编号（主键标识列）名称（非空）性别（男或女,默认男）年龄（12-60）电话（唯一）班级编号（外键）ifexists（select*fromsysobjectswherename='TeaInfos'）droptableTeaInfosgocreatetableTeaInfos（idintprimarykeyidentity（1,1）,--编号namevarchar（10）notnull,--名称sexchar
（2）check（sex='男'orsex='女'）default'男',--性别ageintcheck（age>=12andagetelchar（11）unique,--电话clsidintforeignkeyreferencesClasses（clsid）--班级）go一:
操作变量--1--声明变量@temp值为1并打印出来声明变量关键字:
declaredeclare@tempint--声明set@temp=1--赋值print@temp--输出declare@tempint=1--声明的同时赋值print@temp--输出赋值方式一:
用set输出方式一:
用printdeclare@tempint--声明select@temp=1--赋值select@temp--输出赋值方式二:
用select输出方式二:
用select--2--声明三个变量存储'姓名、性别、年龄',再打印信息,格式如下:
姓名:
杰达姆,性别:
男,年龄:
18--声明declare@namevarchar（10）,@sexchar
（2）,@ageint--赋值select@name='杰达姆',@sex='男',@age=18--输出print'姓名:
'+@name+',性别:
'+@sex+',年龄:
'+cast（@ageasvarchar）--convert（varchar,@age）--两个转型函数:
1.convert--语法:
convert（类型,数据）2.cast--语法:
case（数据as类型）--3--select*fromStuInfo打印张秋丽的信息（来自于student数据库的数据）,格式如下:
姓名:
张秋丽性别:
男年龄:
18--声明declare@namevarchar（10）='张秋丽',@sexchar
（2）,@ageint--赋值:
来自于表内select@sex=stuSex,@age=stuAgefromstuinfowherestuName=@name--输出print'姓名:
'+@nameprint'性别:
'+@sexprint'年龄:
'+cast（@ageasvarchar）--4--查询李文才的左右同桌的信息declare@seatintselect@seat=stuSeatfromstuinfowherestuname='李文才'select*fromstuinfowherestuSeat=@seat-1orstuSeat=@seat+1--5--查询比张秋丽大10岁以上的学生信息declare@ageintselect@age=stuAgefromstuinfowherestuName='张秋丽'select*fromstuinfowherestuAge>=10+@age总结:
set和select的区别1.set一次只能为一个变量赋值,而select能同时为多个变量赋值2.set只能赋一个固定的值,而select能动态的赋值（值来自于表内）select的作用1.查询SQL语句,如:
select*from表名2.赋值操作,如:
select变量名=值3.输出操作,如:
select变量名二:
控制流程结构:
if,else--1--声明变量number,并赋值,然后判断是偶数还是奇数,结果如下:
当前值为11,它是一个奇数declare@numberintset@number=12if（@number%2=0）print'该数为偶数'elseprint'该数为奇数'--2--根据输入的年龄显示人的生长阶段未成年人=100declare@ageintset@age=21if（@ageprint'青年人'elseif（@ageprint'成年人'elseif（@ageprint'老年人'elseprint'超人'select*fromStuInfoselect*fromStuExam--3--统计笔试平均分,如果平均分超过70分打印'优秀'同时显示前三名学员的考试信息否则打印'较差'显示后三名学生信息declare@avgScoreintselect@avgScore=AVG（writtenExam）fromStuExamif（@avgScore>=70）beginprint'本班成绩优秀'selecttop3*fromStuExamorderbywrittenExamdescendelsebeginprint'本班成绩较差'selecttop3*fromStuExamorderbywrittenExamend三:
循环结构--1--输出九九次'我爱你'declare@iint=1while（@i--2--计算1-100的累加和,结果格式:
1-100的累加和为:
xxxdeclare@iint=1,@sumint=0while（@i--3--不停的提高学生笔试成绩2分,让所有学生的笔试成绩都及格declare@countint--用来记录不及格的人数while（1=1）begin--计算不及格的人数select@count=COUNT（*）fromStuExamwherewrittenExam四:
多分支语句--1--请声明变量@name表示学生姓名,对该学生年龄进行划等级具体如下:
12岁前显示:
'小学生'12-17显示'初中生'18-22显示'高中生'23-28显示'大学生'28以上'超人'结果格式:
xxx是一个xxxdeclare@namevarchar（20）='小强',@ageint=23,@resultvarchar（10）--多分支set@result=casewhen@age--输出print@name+'是一个'+@result--2--显示学号、笔试成绩、等级,数据如下:
笔试成绩:
90以上的--A等80以上的--B等70以上的--C等60以上的--D等60以下的--E等stuNowrittenExamgrades2530360D等s2530240E等s2530177C等s2531845E等selectstuNo,writtenExam,grade=casewhenwrittenExam>=90then'A等'whenwrittenExam>=80then'B等'whenwrittenExam>=70then'C等'whenwrittenExam>=60then'D等'else'E等'endfromStuExam--3--请根据平均分和下面的评分规则，编写T-SQL语句查询学员的成绩优：
90分以上良：
80－89分中：
70－79分差：
60－69分极差：
60分以下selectAVG（writtenExam）,A=casewhenAvg（writtenExam）>90then'优'whenAvg（writtenExam）>80andAvg（writtenExam）70andAvg（writtenExam）60andAvg（writtenExam）--4--问题:
根据如下规则对机试成绩进行反复加分,直到机试平均分超过85分为止请编写T-SQL语句实现,注:
（循环+多分支）90分以上:
不加分80-89分:
加1分70-79分:
加2分60-69分:
加3分60分以下:
加5分declare@aintwhile（1=1）beginselect@a=AVG（labExam）fromdbo.stuExamif（@a=90thenlabExamwhenlabExam>=80thenlabExam+1whenlabExam>=70thenlabExam+2whenlabExam>=60thenlabExam+3elselabExam+5endendelsebreakendselectAVG（labExam）AS机试成绩平均分fromdbo.stuExam查询子查询：
查询中再查询,通常是以一个查询作为条件来供另一个查询使用
语法：
select列表from表名
where>（子查询）
注意：
使用比较运算符时，要求子查询只能返回一条或空的记录！
示例：
要求查出比我小的学生信息
select*fromstudent
wherestudentnoin
（selectstudentnofromstudent
wherestudentname='我'）NOTIN:
确定给定的值是否与子查询或列表中的值相匹配,如果不匹配则反回真。
使用方法：
在需要子查询返回多数据时使用。
语法：
select列表from表名
where列名notin（子查询）
示例：
查询未参加过考试的学生信息
select*fromstudent
wherestudentnonotin
（selectstudentnofromResult）1.EXISTS子查询
EXISTS:
exists关键字能够检测数据是否存在，如果存在返回真。
语法
ifexists（子查询）
语句
示例：
查询本校学生有没有叫张三的
ifexists（select*fromstudent
wherestudentname='张三'）
begin
print'有叫张三的'
end2.NOTEXISTS子查询
NOTEXISTS:
exists关键字能够检测数据是否存在，如果不存在返回真
语法
ifnotexists（子查询）
语句
示例：
查询本校学生是不是没有叫张三的
ifnotexists（select*fromstudent
wherestudentname='张三'）
begin
print'没有叫张三的'
end1：
非相关子查询是独立于外部查询的子查询，子查询总共执行一次，执行完毕后将值传递给外部查询。
2：
相关子查询的执行依赖于外部查询的数据，外部查询执行一行，子查询就执行一次。
数据是否存在，如果不存在返回真示例：
检索出在work表中每一个部门的最高基本工资的职工资料select*fromworkawhere基本工资=（selectmax（基本工资）fromworkbwherea.部门名称=b.部门名称）事物视图索引语法begintransactioncommittransactionrollbacktransaction视图创建视图索引ifexists（select*fromsysobjectswherename='视图名'）dropview视图名createview视图名asSQL语句索引ifexists（selectnamefromsysindexwherename='索引'）dropindex表名。
索引名create索引类型index索引名on表名（列名）withfillfactor=30GO一存储过程的概念存储过程是在数据库管理系统保存的，预先编译的，能实现某种功能的SQL程序，它是数据库应用中运用比较广泛的一种数据对象。
为什么需要存储过程？
1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
2.当对数据库进行复杂操作时，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
3.存储过程可以重复使用,可减少数据库开发人员的工作量。
4.安全性高,可设定只有某些用户才具有对指定存储过程的使用权存储过程的优点：
1.模块化程序设计2.执行速度块，效率高3.减少网络流量4.具有良好的安全性二系统存储过程SQL_SERVER提供系统存储过程，它们是一组预编译的T-SQL语句，系统存储过程提供了管理数据库和更新表的机制，并充当从系统表中检索信息的快捷方式。
常用的系统存储过程系统存储过程说明sp_databases列出服务上的所有数据库sp_helpdb报告有关指定数据库或所有数据库的信息sp_renamedb更改数据库的名称sp_tables返回当前环境下可查询的对象的列表sp_columns返回某个表列的信息sp_help返回某个表的所有信息sp_helpconstraint查看某个表的约束sp_helpindex查看某个表的索引sp_stored_procedures列出当前环境中的所有存储过程sp_password添加或修改登录账户的密码三用户自定义的存储过程1.创建不带参数的存储过程Createprocusp_selectstuAsSelectStudentName,Gender,GradeId,Phonefromdbo.Student调用存储过程：
execusp_selectstu2.创建带入参数的存储过程Createprocusp_stuInfo@gradeidint=2（默认）AsSelect*fromstudentwheregradeId=@gradeid调用存储过程：
execusp_stuInfo23.创建带出参数的存储过程createprocusp_selectGrade@namenvarchar（10）,@gradeidintoutputAsSelect@gradeid=gradeidfromstudentwherestudentname=@nameprint@gradeid调用存储过程：
declare@idintexecusp_selectGrade'李小龙',@idoutput4、带通配符参数存储过程Createprocusp_one@namenvarchar（10）asselect*fromdbo.StudentwhereStudentNamelike@nameexecusp_one'李%'5、不缓存存储过程缓存就是数据交换的缓冲区（称作Cache），当某一硬件要读取数据时，会首先从缓存中查找需要的数据，如果找到了则直接执行，找不到的话则从内存中找。
由于缓存的运行速度比内存快得多，故缓存的作用就是帮助硬件更快地运行。
SqlServer系统内存管理在没有配置内存最大值，很多时候我们会发现运行SqlServer的系统内存往往居高不下。
这是由于他对于内存使用的策略是有多少闲置的内存就占用多少，直到内存使用虑达到系统峰值时（预留内存根据系统默认预留使用为准，至少4M），才会清除一些缓存释放少量的内存为新的缓存腾出空间。
这些内存一般都是SqlServer运行时候用作缓存的，例如你运行一个select语句，执行个存储过程，调用函数；1.数据缓存：
执行个查询语句，SqlServer会将相关的数据页（SqlServer操作的数据都是以页为单位的）加载到内存中来，下一次如果再次请求此页的数据的时候，就无需读取磁盘了，大大提高了速度。
2.执行命令缓存：
在执行存储过程，自定函数时，SqlServer需要先二进制编译再运行，编译后的结果也会缓存起来，再次调用时就无需再次编译。
createprocproc_tempwithrecompileasselect*fromstudentexecproc_temp6，加密存储过程execsp_helptext储存过程名可以查看储存过程代码createprocproc_temp_encryptionwithencryptionasselect*fromstudent;go--存储过程的内容不会被轻易看到（虽然解密也是有可能的）。
--应用这个，我们可以对某
升级会员 