MSSQL总结 - 图文

目录

数据库的创建 ....................................................................................................................... 1 数据表结构的建立与维护 ..................................................................................................... 3 数据查询 ............................................................................................................................... 5 数据表的操作 ....................................................................................................................... 6 视图 ...................................................................................................................................... 8 数据库的完整性 .................................................................................................................. 10 索引 .................................................................................................................................... 11 Transact-SQL语句的使用 ................................................................................................. 13 存储过程 ............................................................................................................................. 21 触发器 ................................................................................................................................ 22 事务（Transaction） .......................................................................................................... 23 分页 .................................................................................................................................... 25

数据库的创建

usemaster go

--先判断是否存在，如果存在，先执行删除

ifexists(select*fromsysdatabaseswherename='student') dropdatabasestudent --然后建库

createdatabasestudent on--primary

( ), ( ) logon ( ), ( ) go

--修改学生信息数据库 alterdatabasestudent modifyfile ( ) go

--添加一个信息文件

name='student_data1',--指定要修改的文件名称 --对该文件进行的修改 size=20mb

name='student_log2',

filename='D:\\SQLDB\\student_log2.ldf', size=3mb, maxsize=50mb, filegrowth=1mb name='student_log1',

filename='D:\\SQLDB\\student_log1.ldf', size=3mb, maxsize=50mb, filegrowth=1mb name='student_data1',

filename='D:\\SQLDB\\student_data1.ndf', size=10mb, maxsize=100mb, filegrowth=10% name='student_data',

filename='D:\\SQLDB\\student_data.mdf', size=10mb, maxsize=100mb, filegrowth=10%

alterdatabasestudent addfile ( ) 思考：

1.数据文件分为哪几类？分别有何作用？

数据文件分为下述3 类。

(1) 主要数据文件(.MDF)：主要存放数据库的启动信息，并用于存储数据。每个数据库有且仅有一个主要数据文件。

(2) 次要数据文件(.NDF)：次要数据文件也是用来存储数据的，它含有不能置于主要数据文件中的所有数据。如果主要数据文件可以包含数据库中的所有数据，那么数据库就不需要次要数据文件。如果数据库很大，主要数据文件的容量超过了系统的限制，就需要设置一个或多个次要数据文件，并将它们放在不同的磁盘上。

(3) 事务日志文件(.LDF)：主要包含用于恢复数据库的所有日志信息，这使得数据库出现故障或崩溃时可以把它恢复到最近状态，从而最大限度的减少由此带来的损失。每个数据库中必须至少包含一个日志文件，也可以有多个日志文件。

name='student_data2',--要添加的文件的名字

filename='D:\\SQLDB\\student_data2.ndf'-- .ndf文件大小默认 1mb

数据表结构的建立与维护

useMySchool go

createtableStudent(

StudentNointnotnull,

LoginPwdnvarchar(50)notnull, StudentNamenvarchar(50)notnull,

)

Sexbitnotnull, GradeIdintnotnull, Phonenvarchar(50)null, Addressnvarchar(255)null, BornDatedatetimenotnull, Emailnvarchar(50)null,

IdentityCardvarchar (18)notnull

useMySchool go

ifexists(select*fromsysobjectswherename='Student1') droptableStudent1

--给表创建主键约束 altertableStudent

addconstraintPK_StuNoprimarykey(StudentNo)

--创建唯一约束 altertableStudent

addconstraintUQ_StuIDunique(IdentityCard)

--创建默认约束，地址未知，显示地址不详 altertableStudent

addconstraintDF_StuAddressdefault('地址不详')forAddress --创建检查约束 altertableStudent

addconstraintCK_stuBornDatecheck (BornDate>='1980-01-01')

altertableGrade

addconstraintPK_GradeIDprimarykey(GradeID)

--添加外键约束 altertableStudent addconstraintFK_Grade

foreignkey(GradeID)referencesGrade(GradeID)

--往表中添加字段

altertableStudentaddStunamenvarchar(50)null altertableStudentdropcolumnStudentNo

altertableStudentaltercolumnStudentNonvarcharnotnull

--删除约束 --删除主键

altertableStudent dropconstraintPK_StuNo

altertableStudent dropconstraintDF_Address

altertableStudent dropconstraintUQ_IdCard

altertableStudent

dropconstraintCK_BornDate

altertableStudent

dropconstraintFK_Student_Grade

1.唯一性约束有什么什么作用？与主键约束有何区别？

唯一性约束指定表的一列或多列的组合的值在表中具有唯一性，以防止在列中输入重复的值。主键约束与唯一性约束的区别是：

(1) 在一个表中只能定义一个主键约束，但可定义多个唯一性约束。

(2) 指定为主键约束的列不能取空值，但指定为唯一性约束的列允许取空值。 2.CHECK约束有什么作用？

CHECK 约束用于限制输入到一列或多列的值的范围，保证数据库的数据完整性。

数据查询

1.显示“stu_info”表中的“stu_id”、“name”和“sex”字段。字段名称分别指定为“学号”、“姓名”和“性别”。

SELECT stu_id AS 学号,name 姓名,性别=sex FROM stu_info 2.显示“stu_info”表中的所有学生的姓名和年龄。

SELECT name AS 姓名,year(getdate())-year(borndate) as 年龄 from stu_info 3.去掉重复记录的语句为：

SELECT DISTINCT class_id FROM stu_info 4.只显示前三个班级的编号的语句：

SELECT TOP 3 class_id FROM stu_info

嵌套查询有何特点？

(1) 每级查询的from 子句一般只包含一个表名。

(2) 一个嵌套查询总可以分解为若干个单表查询，总可以改写成连接查询。 (3) 若查询结果显示的属性名来自一个表，才可以写成嵌套查询。 (4) 子查询不能使用order by 子句，order by 只能用于最顶层的查询。

数据表的操作

插入多行数据：

1 --需要先建立表Address_IS，其列属性设置参考源表student（insert..select） INSERT INTO Address_IS

SELECT StudentName,Phone,Address,Email FROM Student

2 --无需手动建立表Address_SI，系统自动建立(select..into...) SELECT StudentName,Phone,Address,Email INTO Address_SI FROM Student

3--:UNION关键字合并数据进行插入(union关键字连接) insert into UserInfo (Name,IdCard,BankCard) select 'zhangsan','410','621' union select 'lisi','432' ,'625' union select 'wangwu','421','627' union select 'tianqi','432','625'

删除：

1. 删除表student 中所计算机系的学生信息。

DELETE FROM student WHERE sdept='计算机系' 2. 删除计算机科学系所有学生的选课记录。

DELETE FROM sc WHEREsno in(SELECT sno FROM student WHERE sdept='CS') 1.INSERT 语句的作用是什么？

INSERT 语句用于向已经存在的表中插入新的数据，可以直接在INSERT 语句中指定插入的数据，一次只能插入一行数据；也可以插入子查询的结果，一次可以插入若干行数据。 2．DELETE语句和DROP语句有何区别？

DELETE语句操作是针对表记录的，DROP语句操作对象是数据库、表、视图、存储过程、触发器等。

视图

ifexists(select*fromsysobjectswherename='View_Result_Stu_Subject') dropviewView_Result_Stu_Subject go

--create view ...前后必须要有go createviewView_Result_Stu_Subject as

(selecttop 10 s.StudentNameas姓名,s.StudentNoas学号,r.SubjectResultas成绩,sub.SubjectNameas科目,r.ExamDateas考试时间 fromStudents,Resultr,Subjectsub

wherer.StudentNo=s.StudentNoandr.SubjectId=sub.SubjectIdandsub.SubjectName='使用C#语言开发数据库应用系统

'andr.ExamDate=(selectMAX(ExamDate)fromResultwheresub.SubjectName='使用C#语言开发数据库应用系统')

orderbyr.SubjectResultdesc ) go

--查询视图

select*fromView_Result_Stu_Subject

数据库的完整性

在设计数据库时如何保证数据的完整性？

数据完整性就是指存储在数据库中的数据的一致性和准确性。

索引

useSchoolDB go

ifexists(select*fromsysindexeswherename='IX_StudentName') dropindexStudent.IX_StudentName go

createnonclusteredindexIX_Student_StudentName onstudent(studentName)

withfillfactor=30--填充因子

Transact-SQL语句的使用

useSchoolTest go

--声明变量

declare@namenvarchar(20) declare@ageint --赋值 set只能单个赋值 set@name='zhangsan' set@age=12

--select可以多个赋值 select@name='lisi',@age=21 --查询结果赋值给变量

select@name=StudentName,@age=DATEDIFF(YY,BornDate,GETDATE()) fromStudent

whereStudentNo='S1101001' --输出 print@name print@age

--1、编写T-SQL查找’李文才’及他的相邻学号学生：

--1)找出“李文才”的学号 --2)李文才的学号加1或减1

declare@sNoint select@sNo=StudentNo fromStudent

whereStudentName='李文才'

--set不能这样赋值，编译器通不过 --set @sNo =StudentNo --from Student

--where StudentName='李文才'

--返回NULL值

set@sNo=(selectDATEDIFF(YY,BornDate,GETDATE()) fromStudent whereStudentNo='')

selectStudentNo,StudentName fromStudent

whereStudentNoin(@sNo,@sNo+1,@sNo-1)

print'姓名' print'年龄'

--全局变量

insertintoGrade(GradeName)values('大四') select@@IDENTITY,@@ROWCOUNT,@@LANGUAGE

print'语言编号：'+@@LANGUAGE print'版本号：'+@@VERSION

select@@LANGUAGEas'语言编号',@@VERSIONas'版本号'

--声明并使用T-SQL变量打印图形：用字符“★”拼成下面的三角图形

declare@star1char set@star1='*' print@star1 print@star1+@star1 print@star1+@star1+@star1 print@star1+@star1+@star1+@star1

--查询学号是20011的学生姓名和年龄，并输出比他大1岁和小1岁的学生信息 declare@borndatedatetime

select@borndate=(selectBornDatefromStudentwhereStudentNo='20011') print@borndate

selectStudentNo,StudentNamefromStudentwhereBornDatebetweenDATEADD(YY,-1,@borndate)andDATEADD(YY,1,@borndate)

declare@sageint,@snamenvarchar(10);

select@sname=StudentName,@sage=DATEDIFF(YY,BornDate,GETDATE()) fromStudent

whereStudentNo='20011';

selectStudentName fromStudent

whereDATEDIFF(YY,BornDate,GETDATE())in(@sage,@sage+1,@sage-1)

--查询学号是10000的学生参加2016年2月15日的“Java Logic”课程考试的成绩，要求输出学生姓名和成绩

declare@name1nvarchar(20),@scoreint --查学生姓名

select@name1=StudentName fromStudent

whereStudentNo='10000' --查成绩

select@score=r.StudentResult fromResultr,Subjects

wherer.SubjectNo=s.SubjectNoands.SubjectNamelike'%java%'andr.ExanDate='2016-2-17'andr.StudentNo='10000'

print@name1+cast(@scoreaschar(3))

ifexists(select*fromsysobjectswherename='qqA') droptableqqA createtableqqA ( )

ifexists(select*fromsysobjectswherename='qqB') droptableqqB createtableqqB ( )

--create table qqB --(

-- id intidentity(1,1) primary key, -- qnoint references qqA(qno) --)

declare@datadatetime select@data=max(ExanDate) fromResultinnerjoinSubject

onResult.SubjectNo=Subject.SubjectNo whereSubjectName='Java Logic'; --print @data

declare@myavgdecimal(5,2) select@myavg=avg(StudentResult) fromResultinnerjoinSubject

onResult.SubjectNo=Subject.SubjectNo

idintidentity(1,1)primarykey, qnoint,

phonechar(11)check(len(phone)=11) foreignkey (qno)referencesqqA(qno), --check(len(phone)=11)

--constraint CK_qqA_phone check(len(phone)=11) qnointprimarykey,

qnamenvarchar(10)default ('zhangsan')

whereSubjectName='Java Logic'andExanDate=@data

print'平均值：'+convert(varchar(5),@myavg)

if(@myavg>70)

========================================================================== --? 统计并显示最近的一次Java Logic考试平均分

--? 如果平均分在70以上，显示“考试成绩优秀”，并显示前三名学生的考试信息 --? 如果在70以下，显示“考试成绩较差”，并显示后三名学生的考试信息

declare@lastExamdatetime select@lastExam=max(r.ExamDate) fromResultr,Subjects

wherer.SubjectNo=s.SubjectNoands.SubjectName='java' print@lastExam

declare@avgScoredecimal

select@avgScore=AVG(r.StudentResult) fromResultr,Subjects

wherer.SubjectNo=s.SubjectNoands.SubjectName='java'andr.ExamDate=@lastExam

begin end begin end

print'考试成绩差，后三名的成绩为：' selecttop 3 StudentNo,StudentResult fromResultinnerjoinSubject

onResult.SubjectNo=Subject.SubjectNo

whereSubjectName='Java Logic'andExanDate=@data orderbyStudentResult

print'考试成绩优秀，前三名的成绩为：' selecttop 3 StudentNo,StudentResult fromResultinnerjoinSubject

onResult.SubjectNo=Subject.SubjectNo

whereSubjectName='Java Logic'andExanDate=@data orderbyStudentResultdesc

else

print@avgScore

if(@avgScore>70)

begin

print'考试成绩优秀' selecttop 3

s.StudentName,r.StudentResult,CONVERT(char(10),r.ExamDate,20),'java'as考试科目

end begin

print'考试成绩较差' selecttop 3

fromResultr,Students

wherer.StudentNo=s.StudentNo orderbyr.StudentResultdesc

else

s.StudentName,r.StudentResult,CONVERT(char(10),r.ExamDate,20),'java'as考试科目

--? 检查学生“Winforms”课最近一次考试是否有不及格（60分及格）的学生。如有，每人加2分，高于95分的学生不再加分，直至所有学生这次考试成绩均及格。 --? 第一步：统计没通过的人数 --? 第二步：如果有人没通过，加分 --? 第三步：循环判断

declare@lastExam1datetime declare@failCountint declare@subjectidint --课程编号

end

fromResultr,Students

wherer.StudentNo=s.StudentNoandr.ExamDate=@lastExam orderbyr.StudentResult

select@subjectid=SubjectNo fromSubject

whereSubjectNamelike'%java%' --最近一次考试时间找到

select@lastExam1=MAX(r.ExamDate) fromResultr,Subjects

wherer.SubjectNo=s.SubjectNoands.SubjectNo=@subjectid print@lastExam1 while(1=1)

begin end

--不及格人数

select@failCount=count(*) fromResultr

whereSubjectNo=@subjectidandExamDate=@lastExam1andStudentResult<60 --每人加2分，高于95分的学生不再加分，直至所有学生这次考试成绩均及格。 if(@failCount>0)

end break begin

updateResultsetStudentResult+=2

whereSubjectNo=@subjectidandExamDate=@lastExam1andStudentResult<95

else

--查询结果

selects.StudentName,r.StudentResult fromResultr,Students

wherer.StudentNo=s.StudentNoandSubjectNo=@subjectidandExamDate=@lastExam1

-------------------------------CASE-END

--? 采用美国ABCDE五级打分制显示学生Java Logic课最近一次考试成绩 --? A级: 90分以上 --? B级： 80－89分 --? C级: 70－79分

--? D级： 60－69分 --? E级： 60分以下

declare@lastExam2datetime declare@subjectid2int

select@subjectid2=SubjectNo

fromSubjectswhereSubjectNamelike'java'

select@lastExam2=ExamDate

fromResultwhereSubjectNo=@subjectid2

selects.StudentNo,s.StudentName, case

whenr.StudentResult>=90 then'A级' whenr.StudentResult>=80 then'B级' whenr.StudentResult>=70 then'C级' whenr.StudentResult>=60 then'D级' else'E级'

endas'成绩等级' fromResultr,Students

wherer.StudentNo=s.StudentNoandSubjectNo=@subjectid2andExamDate=@lastExam2

--批处理

createtablePunish ( ) go

insertintoPunish

selectStudentNo学号,count(*)不及格次数,''处理意见

StudentNointprimarykey, FailCountintnotnull, Advicenvarchar(10)

fromResult

whereStudentResult<60 groupbyStudentNo go

updatePunishsetAdvice= ( ) go

selectStudentNo,FailCount,Advice fromPunish

case end

whenFailCount>3 then'开除学籍' whenFailCount>2 then'肄业处理' else'给予警告'

存储过程

1

--? 创建存储过程，查询Java Logic最近一次考试平均分以及未通过考试的学员名单

ifexists(select*fromsysobjectswherename='usp_Result_AvgFail_Output') dropprocusp_Result_AvgFail_Output go

createprocusp_Result_AvgFail_Output as ')

--print @subNo declare@subNoint

set@subNo=(selectSubjectIdfromSubjectwhereSubjectName='深入.NET平台和C#编程@avgScorefloatoutput, @failCountintoutput

go

declare@avgfloat,@failCountint

execusp_Result_AvgFail_Output@avgoutput,@failCountoutput

print'深入.NET平台和C#编程平均值为：'+cast(@avgasvarchar(8)) print'不及格人数为：'+convert(varchar(8),@failCount)

select@failCount=COUNT(*) fromStudent

whereStudentNoin(selectStudentNofromResultwhereSubjectResult<60) select@avgScore=avg(SubjectResult) fromResult

whereSubjectId=@subNo and

ExamDate=(selectMAX(ExamDate)fromResultwhereSubjectId=@subNo)

触发器

USE[ClassDB] GO

/****** Object: Trigger [dbo].[trig_classinfo_insert] Script Date: 2016/8/25 9:11:16 ******/ SETANSI_NULLSON GO

SETQUOTED_IDENTIFIERON GO

ALTERtrigger[dbo].[trig_classinfo_insert] on[dbo].[classinfo] afterinsert as

updateclasetclanum+=1

wherecid=(selectcidfrominserted)

createtriggertrig_classinfo_delete onclassinfo afterdelete as

select*fromdeleted updateclasetclanum-=1

wherecid=(selectclassidfromdeleted)

deletefromclassinfowherestuid='0004'

触发器的作用是什么？

在 SQL Server里，可以用两种方法来保证数据的有效性和完整性：约束(CHECK)和触发器(TRIGGER)。 触发器是一种特殊类型的存储过程，也是提前编译好的SQL 语句的集合。当对数据表实施插入、修改、删除操作时，触发器会被自动执行，以检查数据的处理是否符合数据的有效性和完整性。

事务（Transaction）

概念：

作为单个逻辑工作单元的一系列操作，多个操作作为一个整体向系统提交，要么都执行，要么都不执行

特性：

事务必须具备以下四个属性，简称ACID属性： 1. 原子性(Atomicity)

a) 事务是一个完整的操作，事务的各步骤操作是不可分割的（原子性），要么都执行，要么

都不执行

2. 一致性(Consistency)

a) 当事务完成时，数据必须处于一致状态

3. 隔离性(Isolation)

a) 当事务之间彼此隔离、独立，它不应以任何方式依赖于或影响其他事务 4. 永久性(Durability)

a) 事务完成后，它对数据库的的修改被永久保持

使用TSQL语句管理事务

开始事务：begin transaction 提交事务：commit transaction

回滚（撤销）事务：rollback transaction

--创建表

ifexists(select*fromsysobjectswherename='bank') droptablebank CREATETABLEbank ( )

insertintobank(bankCard,customerName,balance) values('5412548796541236547','张三',1000),

begintransaction--开始事务

declare@errotSumint--声明变量：统计错误

('5412548796541564212','李四',1)

bankCardchar(19)primarykeycheck (len(bankCard)=19), customerNamevarchar(10)notnull,

--check只是在写入数据时才约束,但是插入的时候可以为空 balancemoneycheck(balance>=1)notnull

set@errotSum=0 ---初始化错误次数

updatebanksetbalance-=800 wherebankCard='5412548796541236547' set@errotSum+=@@ERROR

updatebanksetbalance+=800 wherebankCard='5412548796541564212' set@errotSum+=@@ERROR

if@errotSum>0

select*frombank

begin end begin end

print'转账成功' committransaction print'转账失败' rollbacktransaction

else

分页

Select..not in..

ifexists(select*fromsysobjectswherename='usp_Student_page') dropprocusp_Student_page go

createprocusp_Student_page as

declare@totalint select@total=count(*) fromStudent

if(@total<(@pageSize*@pageCurrent))

begin

if(@total%@pageSize<>0)

set@pageTotal=@total%@pageSize+1

@pageTotalintoutput, @pageCurrentint, @pageSizeint

else

set@pageTotal=@total%@pageSize

print'请输入正确的页码数总条数为：'+cast(@totalaschar(5))+'总页数为：

'+cast(@pageTotalaschar(5)) go

declare@pageTotalint

execusp_Student_page@pageTotaloutput,2,5 print@pageTotal

end

selecttop (@pageSize)*fromStudentwhereStudentNonotin

(selecttop ((@pageCurrent-1)*(@pageSize))StudentNofromStudent) else

row_number()分页

select ROW_NUMBER() over(order by studentNo) as 序号, * from Student

select row_number() over (order by [SubjectResult] desc) as 成绩,* from Result

createprocusp_Bank_Page2 as

else begin

IF(@rowSatart>@pageTotal) BEGIN END

PRINT'总条数为：'+CAST(@pageTotalASVARCHAR(20))+'请确认输入数目' SELECT@pageTotal=COUNT(*) FROMCustomer

declare@rowSatartint,@rowEndint

set@rowSatart=(@pageCurrent-1)*@pageSize+1 set@rowEnd=@pageCurrent*@pageSize @pageCurrentint, @pageSizeint, @pageTotalINTOUTPUT

go

select*from

(select*,ROW_NUMBER()over (orderbycustId)asnumberfromcustomer)ast wheret.numberbetween@rowSatartand@rowEnd end

本文来源：https://www.bwwdw.com/article/6v45.html