关于SQL SERVER数据库学习总结

对于SQL SERFVER数据库也学了有一阵子了，自己也对自己所学做了一些总结。

我们首先学习数据库设计的一些知识点和用SQL语句建库。

设计数据库步骤:需求分析阶段，概要设计阶段，详细设计阶段，

建数据库的SQL语句如下（包含了如果有该数据库先删除在建立数据库）

use master
GO
if exists(select * from sysdatabases where name='Wages')
DROP database Wages
CREATE DATABASE Wages
ON
(
NAME='Wages_data',
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 Wages
GO
if exists(select * from sysobjects where name='WageInfo')
DROP table WageInfo
CREATE TABLE WageInfo
(
CompanyID INT primary key IDENTITY(1,1),
CompanyName varchar(50) NOT NULL,
Poste varchar(50) NOT NULL,
)
GO
if exists(select * from sysobjects where name='StudentInfo')
DROP table StudentInfo
CREATE TABLE StudentInfo
(
ID INT primary key IDENTITY(1,1),
Name VARCHAR(50) NOT NULL,
CompanyID INT ,
PosteDate DATETIME ,
Wage int,
)
GO

下面是创建约束

语法如下

alter table 表名

add constraint 约束名约束类型具体的约束说明

示例

创建外键约束

alter table StudentInfo
add constraint pk_CompanyID foreign key(CompanyID) references WageInfo(CompanyID)
GO

插入数据语句如下

insert into WageInfo(CompanyName, Poste)values
('微软','职员'),
('百度','职员'),
('腾讯','职员'),
('爱奇艺','职员'),
('华为','职员')
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)

然后我们学习了变量，变量分全局变量和局部变量。

创建变量语法如下是

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 table 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 default 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)

删除约束

alter 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, --名称

sex char(2) check(sex='男' or sex='女') default '男', --性别

age int check(age>=12 and age<=60), --年龄

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 --输出

赋值方式一:用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)

--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

--输出 print '姓名:'+@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 * from 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 set @number=12 if(@number%2=0) print '该数为偶数' else print '该数为奇数' -

-2-- 根据输入的年龄显示人的生长阶段未成年人<18,青年人<30,成年人<60,老年人<100,超人>=100

declare @age int set @age=21

if(@age<18) print '未成年人' else if(@age<30)

print '青年人'

else if(@age<60)

print '成年人'

else if(@age<100)

print '老年人'

else print '超人'

select * from StuInfo select * from StuExam

--3-- 统计笔试平均分,如果平均分超过70分打印'优秀' 同时显示前三名学员的考试信息否则打印'较差'显示后三名学生信息

declare @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<=99) begin print '第'+convert(varchar,@i)+'我爱你' set @i+=1 end

--2-- 计算1-100的累加和,结果格式:1-100的累加和为:xxx declare @i int=1, @sum int=0 while(@i<=100) begin set @sum+=@i set @i+=1 end print '1-100的累加和为:'+convert(varchar,@sum)

--3-- 不停的提高学生笔试成绩2分,让所有学生的笔试成绩都及格

declare @count int --用来记录不及格的人数 while(1=1) begin --计算不及格的人数 select @count=COUNT(*) from StuExam where writtenExam<60 --判断 if(@count=0) break --退出死循环 else update StuExam set writtenExam+=2 end select * from StuExam

四:多分支语句

--1-- 请声明变量@name表示学生姓名,对该学生年龄进行划等级具体如下: 12岁前显示:'小学生' 12-17显示'初中生' 18-22显示'高中生' 23-28显示'大学生' 28以上'超人' 结果格式:xxx是一个xxx

declare @name varchar(20)='小强', @age int=23, @result varchar(10)

--多分支 set @result=case when @age<12 then '小学生' when @age<17 then '初中生' when @age<22 then '高中生' when @age<28 then '大学生' else '超人' end

--输出 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=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分以上良：80－89分中：70－79分差：60－69分极差：60分以下

select AVG(writtenExam),A=case when Avg(writtenExam)>90 then '优' when Avg(writtenExam)>80 and Avg(writtenExam)<89 then '良' when Avg(writtenExam)>70 and Avg(writtenExam)<79 THEN '中' when Avg(writtenExam)>60 and Avg(writtenExam)<69 then '差' when Avg(writtenExam)<60 then '极差' end from stuExam

--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<85) begin update dbo.stuExam set labExam =case when labExam>=90 then labExam when labExam>=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 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: exists 关键字能够检测数据是否存在，如果存在返回真。
语法
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='张三' )
begin
print '没有叫张三的'
end

1：非相关子查询是独立于外部查询的子查询，子查询总共执行一次，执行完毕后将值传递给外部查询。 2：相关子查询的执行依赖于外部查询的数据，外部查询执行一行，子查询就执行一次。数据是否存在，如果不存在返回真

示例：检索出在work表中每一个部门的最高基本工资的职工资料 select * from work a where 基本工资=(select max(基本工资) from work b where a.部门名称=b.部门名称)

事物视图索引

语法

begin transaction

commit transaction

rollback transaction

视图

创建视图索引

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程序，它是数据库应用中运用比较广泛的一种数据对象。

为什么需要存储过程？

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.创建不带参数的存储过程

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 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 @name

exec usp_one '李%'

5、不缓存存储过程

缓存就是数据交换的缓冲区(称作Cache)，当某一硬件要读取数据时，会首先从缓存中查找需要的数据，如果找到了则直接执行，找不到的话则从内存中找。由于缓存的运行速度比内存快得多，故缓存的作用就是帮助硬件更快地运行。

Sql Server系统内存管理在没有配置内存最大值，很多时候我们会发现运行Sql Server的系统内存往往居高不下。这是由于他对于内存使用的策略是有多少闲置的内存就占用多少，直到内存使用虑达到系统峰值时（预留内存根据系统默认预留使用为准，至少4M），才会清除一些缓存释放少量的内存为新的缓存腾出空间。

这些内存一般都是Sql 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

--存储过程的内容不会被轻易看到（虽然解密也是有可能的）。

--应用这个，我们可以对某些关键的存储过程进行加密。

--但此时，存储过程仍然能被execute、alter和drop。

exec proc_temp_encryption;

exec sp_helptext 'proc_temp'

exec sp_helptext 'proc_temp_encryption'

（注意：加密存储过程前应该备份原始存储过程，且加密应该在部署到生产环境前完成。）