关于SQL SERVER数据库学习总结.docx

1、关于SQL SERVER数据库学习总结关于SQL SERVER数据库学习总结 对于SQL SERFVER数据库也学了有一阵子了，自己也对自己所学做了一些总结。 我们首先学习数据库设计的一些知识点和用SQL语句建库。 设计数据库步骤:需求分析阶段，概要设计阶段，详细设计阶段， 建数据库的SQL语句如下（包含了如果有该数据库先删除在建立数据库） use masterGOif exists(select * from sysdatabases where name=Wages)DROP database WagesCREATE DATABASE Wages ON ( NAME=Wages_data,

2、 FILENAME=e:projectWages_data.mdf, SIZE=5mb, FILEGROWTH=15% ) LOG ON ( NAME= Wages_log, FILENAME=e:projectWages_log.ldf, SIZE=3mb, FILEGROWTH=15% )GO 为了创建良好的数据库需满足三大范式。 下面是创建表的SQL语句（包含了如果有该表先删除在建表） USE WagesGOif exists(select * from sysobjects where name=WageInfo)DROP table WageInfoCREATE TABLE Wage

3、Info ( CompanyID INT primary key IDENTITY(1,1), CompanyName varchar(50) NOT NULL, Poste varchar(50) NOT NULL,)GOif exists(select * from sysobjects where name=StudentInfo)DROP table StudentInfoCREATE TABLE StudentInfo( ID INT primary key IDENTITY(1,1), Name VARCHAR(50) NOT NULL, CompanyID INT , Poste

4、Date DATETIME , Wage int,)GO 下面是创建约束 语法如下 alter table 表名 add constraint 约束名 约束类型 具体的约束说明 示例 创建外键约束 alter table StudentInfoadd constraint pk_CompanyID foreign key(CompanyID) references WageInfo(CompanyID)GO 插入数据语句如下 insert into WageInfo(CompanyName, Poste)values(微软,职员),(XX,职员),(腾讯,职员),(爱奇艺,职员),(华为,职员

5、)insert into StudentInfo(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)insert into StudentInfo(Name, CompanyID, PosteDate, Wage)values(钱二,null,2016-05-09,NULL) 然后我们学习了变量，变量分全局变量和局部变量。 创建变量语法如下是

6、 declare 变量名 数据类型 局部变量有两种赋值方法 set 变量名=value select 变量名=value 区别是select可一次对多个变量赋值，set只能一次对一个变量赋值。 全局变量只能用不能自己创建和赋值！ 输出语句 print 和select use MySchool go select * from StuInfos 1.班级表 班级编号 (主键) 班级名 (长度固定3位) 班级人数 (默认30) if exists(select * from sysobjects where name=Classes) drop table Classes go create ta

7、ble Classes ( clsid int identity(1,1), -班级编号 clsname varchar(3), -班级名称 clsnums int -班级人数 ) go alter table Classes add constraint pk_clsid_Classes primary key(clsid) alter table Classes add constraint ck_clsname_Classes check(len(clsname)=3) alter table Classes add constraint df_clsnums_Classes defau

8、lt 30 for clsnums go insert into Classes select t10,25 union select t11,18 union select t12,23 2.外键约束 为学生表添加一个clsid列来表示其班级 alter table StuInfos add clsid int 外键约束 alter table StuInfos with nocheck -不对表现有的数据作限制操作 add constraint fk_StuInfos_Classes foreign key(clsid) references Classes(clsid) 删除约束 alt

9、er table StuInfos drop constraint fk_StuInfos_Classes 3.建表同时建约束,老师表 编号 (主键 标识列) 名称 (非空) 性别 (男或女,默认男) 年龄 (12-60) 电话 (唯一) 班级编号 (外键) if exists(select * from sysobjects where name=TeaInfos) drop table TeaInfos go create table TeaInfos ( id int primary key identity(1,1), -编号 name varchar(10) not null, -名

10、称 sex char(2) check(sex=男 or sex=女) default 男, -性别 age int check(age=12 and age tel char(11) unique, -电话 clsid int foreign key references Classes(clsid) -班级 ) go 一:操作变量 -1- 声明变量temp值为1并打印出来 声明变量关键字: declare declare temp int -声明 set temp=1 -赋值 print temp -输出 declare temp int=1 -声明的同时赋值 print temp -输出

11、 赋值方式一:用set 输出方式一:用print declare temp int -声明 select temp=1 -赋值 select temp -输出 赋值方式二:用select 输出方式二:用select - 2- 声明三个变量存储姓名、性别、年龄,再打印信息,格式如下: 姓名:杰达姆,性别:男,年龄:18 -声明 declare name varchar(10), sex char(2), age int -赋值 select name=杰达姆,sex=男,age=18 -输出 print 姓名:+name+,性别:+sex+,年龄: +cast(age as varchar) -

12、convert(varchar,age) -两个转型函数: 1.convert - 语法: convert(类型,数据) 2.cast - 语法: case(数据 as 类型) -3- select * from StuInfo 打印张秋丽的信息(来自于student数据库的数据),格式如下: 姓名:张秋丽 性别:男 年龄:18 -声明 declare name varchar(10)=张秋丽, sex char(2), age int -赋值:来自于表内 select sex=stuSex,age=stuAge from stuinfo where stuName=name -输出 prin

13、t 姓名:+name print 性别:+sex print 年龄:+cast(age as varchar) -4- 查询李文才的左右同桌的信息 declare seat int select seat=stuSeat from stuinfo where stuname=李文才 select * from stuinfo where stuSeat=seat-1 or stuSeat=seat+1 -5- 查询比张秋丽大10岁以上的学生信息 declare age int select age=stuAge from stuinfo where stuName=张秋丽 select * f

14、rom stuinfo where stuAge=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 number int

15、set number=12 if(number%2=0) print 该数为偶数 else print 该数为奇数 - -2- 根据输入的年龄显示人的生长阶段 未成年人=100 declare age int set age=21 if(age print 青年人 else if(age print 成年人 else if(age print 老年人 else print 超人 select * from StuInfo select * from StuExam -3- 统计笔试平均分,如果平均分超过70分打印优秀 同时显示前三名学员的考试信息否则 打印较差显示后三名学生信息 declare

16、 avgScore int select avgScore=AVG(writtenExam) from StuExam if(avgScore=70) begin print 本班成绩优秀 select top 3 * from StuExam order by writtenExam desc end else begin print 本班成绩较差 select top 3 * from StuExam order by writtenExam end 三:循环结构 -1- 输出九九次我爱你 declare i int=1 while(i -2- 计算1-100的累加和,结果格式:1-100

17、的累加和为:xxx declare i int=1, sum int=0 while(i -3- 不停的提高学生笔试成绩2分,让所有学生的笔试成绩都及格 declare count int -用来记录不及格的人数 while(1=1) begin -计算不及格的人数 select count=COUNT(*) from StuExam where writtenExam 四:多分支语句 -1- 请声明变量name表示学生姓名,对该学生年龄进行划等级 具体如下: 12岁前显示:小学生 12-17显示初中生 18-22显示高中生 23-28显示大学生 28以上超人 结果格式:xxx是一个xxx d

18、eclare name varchar(20)=小强, age int=23, result varchar(10) -多分支 set result=case when age -输出 print name+是一个+result -2- 显示学号、笔试成绩、等级,数据如下: 笔试成绩:90以上的-A等 80以上的- B等 70以上的- C等 60以上的- D等 60以下的- E等 stuNo writtenExam grade s25303 60 D等 s25302 40 E等 s25301 77 C等 s25318 45 E等 select stuNo, writtenExam, grade

19、=case when writtenExam=90 then A等 when writtenExam=80 then B等 when writtenExam=70 then C等 when writtenExam=60 then D等 else E等 end from StuExam -3- 请根据平均分和下面的评分规则，编写T-SQL语句查询学员的成绩 优 ：90分以上 良 ：8089分 中 ：7079分 差 ：6069分 极差 ：60分以下 select AVG(writtenExam),A=case when Avg(writtenExam)90 then 优 when Avg(writ

20、tenExam)80 and Avg(writtenExam)70 and Avg(writtenExam)60 and Avg(writtenExam) -4- 问题: 根据如下规则对机试成绩进行反复加分, 直到机试平均分超过85分为止 请编写T-SQL语句实现,注:(循环+多分支) 90分以上: 不加分 80-89分: 加1分 70-79分: 加2分 60-69分: 加3分 60分以下: 加5分 declare a int while(1=1) begin select a=AVG(labExam)from dbo.stuExam if(a=90 then labExam when lab

21、Exam=80 then labExam+1 when labExam=70 then labExam+2 when labExam=60 then labExam+3 else labExam+5 end end else break end select AVG(labExam) AS 机试成绩平均分 from dbo.stuExam 查询 子查询：查询中再查询,通常是以一个查询作为条件来供另一个查询使用语法： select 列表 from 表名 where （子查询）注意： 使用比较运算符时，要求子查询只能返回一条或空的记录！示例： 要求查出比我小的学生信息 select * from

22、student where studentno in （select studentno from student where studentname=我） NOT IN:确定给定的值是否与子查询或列表中的值相匹配,如果不匹配则反回真。使用方法： 在需要子查询返回多数据时使用。语法： select 列表 from 表名 where 列名 not in（子查询）示例： 查询未参加过考试的学生信息 select * from student where studentno not in (select studentno from Result) 1.EXISTS 子查询 EXISTS: exis

23、ts 关键字能够检测数据是否存在，如果存在返回真。语法 if exists(子查询) 语句示例： 查询本校学生有没有叫张三的 if exists( select * from student where studentname=张三 ) begin print 有叫张三的 end 2. NOT EXISTS 子查询 NOT EXISTS: exists 关键字能够检测数据是否存在，如果不存在返回真语法 if not exists(子查询) 语句示例： 查询本校学生是不是没有叫张三的 if not exists( select * from student where studentname=张

24、三 ) begin print 没有叫张三的 end 1：非相关子查询是独立于外部查询的子查询，子查询总共执行一次，执行完毕后将值传递给外部查询。 2：相关子查询的执行依赖于外部查询的数据，外部查询执行一行，子查询就执行一次。数据是否存在，如果不存在返回真 示例：检索出在work表中每一个部门的最高基本工资的职工资料 select * from work a where 基本工资=(select max(基本工资) from work b where a.部门名称=b.部门名称) 事物视图索引 语法 begin transaction commit transaction rollback t

25、ransaction 视图 创建视图索引 if exists(select * from sysobjects where name=视图名) drop view 视图名 create view 视图名 as SQL语句 索引 if exists(select name from sysindex where name=索引) drop index 表名。索引名 create 索引类型 index 索引名 on 表名（列名） with fillfactor=30 GO 一 存储过程的概念 存储过程是在数据库管理系统保存的，预先编译的，能实现某种功能的SQL程序，它是数据库应用中运用比较广泛的一

26、种数据对象。 为什么需要存储过程？ 1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。 2.当对数据库进行复杂操作时，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。 3.存储过程可以重复使用,可减少数据库开发人员的工作量。 4.安全性高,可设定只有某些用户才具有对指定存储过程的使用权 存储过程的优点： 1.模块化程序设计 2.执行速度块，效率高 3.减少网络流量 4.具有良好的安全性 二 系统存储过程 SQL_SERVER 提供系统存储过程，它们是一组预编译的T-SQL语句，系

27、统存储过程提供了管理数据库和更新表的机制，并充当从系统表中检索信息的快捷方式。 常用的系统存储过程系统存储过程 说明 sp_databases 列出服务上的所有数据库 sp_helpdb 报告有关指定数据库或所有数据库的信息 sp_renamedb 更改数据库的名称 sp_tables 返回当前环境下可查询的对象的列表 sp_columns 返回某个表列的信息 sp_help 返回某个表的所有信息 sp_helpconstraint 查看某个表的约束 sp_helpindex 查看某个表的索引 sp_stored_procedures 列出当前环境中的所有存储过程 sp_password 添加

28、或修改登录账户的密码 三 用户自定义的存储过程 1.创建不带参数的存储过程 Create proc usp_selectstu As Select StudentName,Gender,GradeId,Phone from dbo.Student 调用存储过程：exec usp_selectstu 2.创建带入参数的存储过程 Create proc usp_stuInfo gradeid int=2 （默认） As Select * from student where gradeId=gradeid 调用存储过程：exec usp_stuInfo 2 3.创建带出参数的存储过程 create

29、 proc usp_selectGrade name nvarchar(10),gradeid int output As Select gradeid=gradeid from student where studentname=name print gradeid 调用存储过程： declare id int exec usp_selectGrade 李小龙,id output 4、 带通配符参数存储过程 Create proc usp_one name nvarchar(10) as select * from dbo.Student where StudentName like nam

30、e exec usp_one 李% 5、 不缓存存储过程 缓存就是数据交换的缓冲区(称作Cache)，当某一硬件要读取数据时，会首先从缓存中查找需要的数据，如果找到了则直接执行，找不到的话则从内存中找。由于缓存的运行速度比内存快得多，故缓存的作用就是帮助硬件更快地运行。 Sql Server系统内存管理在没有配置内存最大值，很多时候我们会发现运行Sql Server的系统内存往往居高不下。这是由于他对于内存使用的策略是有多少闲置的内存就占用多少，直到内存使用虑达到系统峰值时（预留内存根据系统默认预留使用为准，至少4M），才会清除一些缓存释放少量的内存为新的缓存腾出空间。 这些内存一般都是Sql

31、 Server运行时候用作缓存的，例如你运行一个select语句， 执行个存储过程，调用函数； 1. 数据缓存：执行个查询语句，Sql Server会将相关的数据页（Sql Server操作的数据都是以页为单位的）加载到内存中来，下一次如果再次请求此页的数据的时候，就无需读取磁盘了，大大提高了速度。 2.执行命令缓存：在执行存储过程，自定函数时，Sql Server需要先二进制编译再运行，编译后的结果也会缓存起来， 再次调用时就无需再次编译。 create proc proc_temp with recompile as select * from student exec proc_temp 6，加密存储过程 exec sp_helptext 储存过程名 可以查看储存过程代码 create proc proc_temp_encryption with encryption as select * from student; go -存储过程的内容不会被轻易看到（虽然解密也是有可能的）。 -应用这个，我们可以对某