局部变量_先声明再赋值
声明局部变量
DECLARE @变量名 数据类型
DECLARE @name varchar(20)
DECLARE @id int
赋值
SET @变量名 =值
--set用于普通的赋值
SELECT @变量名 = 值
--用于从表中查询数据并赋值，，可以一次给多个变量赋值
例如：
SET @name=‘张三’
SET @id = 1
SELECT @name = sName FROM student WHERE sId=@id
输出变量的值
SELECT 以表格的方式输出,可以同时输出多个变量
PRINT 以文本的方式输出，一次只能输出一个变量的值
SELECT @name,@id
PRINT @name
PRINT @id
变量种类
变量分为：
局部变量：
局部变量必须以标记@作为前缀 ，如@Age int
局部变量：先声明，再赋值
全局变量（系统变量）：
全局变量必须以标记@@作为前缀，如@@version
全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值
讲解要点： 和C语言中的变量进行类比，让学员容易接受。 强调： 1.局部变量标志：一个◎；全局变量标志：两个◎ 2. 我们可以定义局部变量，但不能定义全局系统变量，但我们能读取全局系统变量的值。 如当前是否执行有错误：@@ERROR
| 变量 | 含义 |
| @@ERROR | 最后一个T_SQL错误的错误号 |
| @@IDENTITY | 最后一次插入的标识符 |
| @@LANGUAGE | 当前使用的语言的名称 |
| @@MAX_CONNECTIONS | 可以创建的同时连接的最大数目 |
| @@ROWCOUNT | 受上一个SQL语句影响的行数 |
| @@SERVERNAME | 本地服务器的名称 |
| @@TRANSCOUNT | 当前连接打开的事务数 |
| @@VERSION | SQL Server的版本信息 |
print 'SQLServer的版本'+@@VERSION
print '服务器名称: '+@@SERVERNAME
print ‘最后一次放生的错误号'+convert(varchar(5),@@ERROR)
print @@identity
IF ELSE
IF(条件表达式)
BEGIN --相当于C#里的{
语句1
……
END--相当于C#里的}
ELSE
BEGIN
语句1
……
END
例
计算平均分数并输出，如果平均分数超过60分输出成绩最高的三个学生的成绩，否则输出后三名的学生 declare @avg float select @avg = avg(english) from score print '平均分数' + convert(varchar(20),@avg) if(@avg > 60) Begin print '前三名‘ select top 3 sName,english from student inner join score on student.sId=score.studentId order by english desc End Else begin print '后三名' select top 3 sName,english from student inner join score on student.sId=score.studentId order by english asc end
declare @avg float select @avg = avg(english) from score print '平均分数' + convert(varchar(20),@avg) if(@avg > 60) begin print '前三名' select top 3 sName,english from student inner join score on student.sId=score.studentId order by english desc end else begin print '后三名' select top 3 sName,english from student inner join score on student.sId=score.studentId order by english asc end
WHILE循环
WHILE(条件表达式) BEGIN --相当于C#里的{ 语句 …… BREAK END --相当于C#里的}
如果不及格的人超过半数(考试题出难了)，则给每个人增加2分
把所有未及格的人的成绩都加及格
--delete from Score insert into Score (studentId,english) values(1,50) insert into Score (studentId,english) values(2,40) insert into Score (studentId,english) values(3,59) insert into Score (studentId,english) values(4,20) insert into Score (studentId,english) values(5,90) insert into Score (studentId,english) values(6,20) insert into Score (studentId,english) values(7,10)
--如果不及格的人超过半数(考试题出难了)，则给每个人增加分 declare @count int --参加考试总人数 declare @failCount int --不及格人数 select @count = count(*) from score select @failCount = count(*) from score where english < 60 while @failCount > @count/2 begin update score set english = english + 2 select @failCount = count(*) from score where english < 60 end update score set english = 100 where english > 100 select * from score
--把所有未及格的人的成绩都加及格 declare @count int while(1=1) begin select @count = count(*) from score where english < 60 if(@count > 0) begin update score set english = english + 2 end else break end update score set english = 100 where english > 100 select * from score
事务-为什么需要事务
如，转账问题：
假定钱从A转到B，至少需要两步：
A的资金减少
然后B的资金相应增加
update bank set balance=balance-1000 where cid='0001' update bank set balance=balance + 1000 where cid='0002'
-查看结果。 SELECT * FROM bank
-事务 create table bank ( cId char(4) primary key, balance money, --余额 ) alter table bank add constraint CH_balance check(balance >=10) go --delete from bank insert into bank values('0001',1000) insert into bank values('0002',10) go
update bank set balance=balance-1000 where cid='0001' update bank set balance=balance + 1000 where cid='0002'
什么是事务(Transaction)
事务：同生共死
指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)--也就是由多个sql语句组成，必须作为一个整体执行
这些sql语句作为一个整体一起向系统提交，要么都执行、要么都不执行
语法步骤：
开始事务：BEGIN TRANSACTION
事务提交：COMMIT TRANSACTION
事务回滚：ROLLBACK TRANSACTION
判断某条语句执行是否出错：
全局变量@@ERROR；
@@ERROR只能判断当前一条T-SQL语句执行是否有错，为了判断事务中所有T-SQL语句是否有错，我们需要对错误进行累计；
例：SET @errorSum=@errorSum+@@error
select * from bank --使用事务 begin transaction declare @error int set @error = 0 update bank set balance=balance-1000 where cid='0001' set @error = @error + @@error update bank set balance=balance + 1000 where cid='0002' set @error = @error + @@error if @error != 0 rollback transaction else commit transaction go select * from bank
存储过程
存储过程---就像数据库中运行方法(函数)
和C#里的方法一样，由存储过程名/存储过程参数组成/可以有返回结果。
前面学的if else/while/变量/insert/select 等，都可以在存储过程中使用
优点：
执行速度更快 – 在数据库中保存的存储过程语句都是编译过的
允许模块化程序设计 – 类似方法的复用
提高系统安全性 – 防止SQL注入
减少网络流通量 – 只要传输 存储过程的名称
系统存储过程
由系统定义，存放在master数据库中 名称以“sp_”开头或”xp_”开头
自定义存储过程
由用户在自己的数据库中创建的存储过程
系统存储过程
| 系统存储过程 | 说明 |
| sp_databases | 列出服务器上的所有数据库 |
| sp_helpdb | 报告有关指定数据库或所有数据库的信息 |
| sp_renamedb | 更改数据库的名称 |
| sp_tables | 返回当前环境下可查询的对象的列表 |
| sp_columns | 回某个表列的信息 |
| sp_help | 查看某个表的所有的信息 |
| sp_helpconstraint | 查看某个表的约束 |
| sp_helpindex | 查看某个表的索引 |
| sp_stored_procedures | 列出当前环境中的所有存储过程 |
| sp_password | 添加或修改登录账户的密码 |
| sp_helptext | 显示默认值、未加密的存储过程、用户定义的 存储过程、触发器或视图的时机文本 |
创建存储过程
定义存储过程的语法 CREATE PROC[EDURE] 存储过程名 @参数1 数据类型 = 默认值 OUTPUT, @参数n 数据类型 = 默认值 OUTPUT AS SQL语句 参数说明： 参数可选 参数分为输入参数、输出参数 输入参数允许有默认值 EXEC 过程名 [参数]
--大部分学生不及格，提分，直到一半学生及格为止 if exists(select * from sysobjects where [name]='usp_upGrade') drop proc usp_upGrade go create proc usp_upGrade as begin declare @count int --记录总人数 set @count = (select count(*) from score) while @count/2 < (select count(*) from score where english<60) begin update score set english= english+ 2 end update score set english = 100 where english> 100 end go
编写存储过程
考试题出难了，降低及格分数线
编写存储过程usp_upGrade
要求传入参数:@pass float
掉用存储过程，及格分数线，给没及格的人提分
-带输入参数的存储过程 --题出难了，降低及格分数线 if exists(select * from sysobjects where [name]='usp_upGrade1') drop proc usp_upGrade1 go create proc usp_upGrade1 @pass float = 60 as begin declare @count int --记录总人数 set @count = (select count(*) from score) while @count/2 < (select count(*) from score where english<@pass) begin update gradeInfo set grade = grade + 2 end update score set english = 100 where english> 100 end exec usp_upGrade1 80
调用带参数的存储过程
无参数的存储过程调用：
Exec usp_upGrade
有参数的存储过程两种调用法：
EXEC usp_upGrade2 60,55 ---按次序
EXEC usp_upGrade2 @english=55,@math=60 --参数名
参数有默认值时：
EXEC usp_upGrade2 --都用默认值
EXEC usp_upGrade2 1 --第一个用默认值
EXEC usp_upGrade2 1,5 --不用默认值
若想英语的用默认值，数学的及格分数改了怎么办？
存储过程中使用输出参数
如果希望在加分的过程中想输出总共加了多少次分
输出参数关键字：OUTPUT
实现登陆的存储过程
触发器
触发器是一种特殊类型的存储过程，它不同于前面介绍过的一般的存储过程。
一般的存储过程通过存储过程名称被直接调用，而触发器主要是通过事件进行触发而被执行。
触发器是一个功能强大的工具，在表中数据发生变化时自动强制执行。触发器可以用于SQL Server约束、默认值和规则的完整性检查，还可以完成难以用普通约束实现的复杂功能。
那究竟何为触发器？在SQL Server里面也就是对某一个表的一定的操作，触发某种条件，从而执行的一段程序。触发器是一个特殊的存储过程。 常见的触发器有三种：分别应用于Insert , Update , Delete 事件
常用语法
CREATE TRIGGER triggerName ON Table for UPDATE|INSERT|DELETE AS begin … end
触发器-插入
CREATE TRIGGER tr_updateStudent ON score for INSERT AS Begin declare @sid int,@scoreid int select @sid = studentId,@ scoreid=sid from inserted if exists(select * from student where sid=@sid) print ‘插入成功’ else delete from score where sid = @scoreId End Insert into score (studentId,english) values(100,100)
触发器-删除
CREATE TRIGGER tr_deleteStudent ON student for delete AS begin insert into backupStudent select * from deleted End Delete from student where sId=1
数据库的范式
表设计后，很可能结构不合理，出现数据重复保存，简称数据的冗余，这对数据的增删改查带来很多后患，所以我们需要审核是否合理，就想施工图设计后，还需要其他机构进行审核图纸是否设计合理一样。
如何审核呢？需要一些有关数据库设计的理论指导规则，这些规则业界简称数据库的范式。
对数据库审核 三大范式
第一范式的目标是确保每列的原子性 如果每列都是不可再分的最小数据单元（也称为最小的原子单元），则满足第一范式（1NF）
例如，如果关于员工的关系中有一个工资属性，而工资又由更基本的两个数据项基本工资和岗位工资组成，则这个员工的关系模式就不满足1NF。 员工表(员工号,姓名,工资)进行分解，使其满足1NF条件。
员工表(员工号,姓名,基本工资,岗位工资)
如果一个关系满足1NF，并且除了主键以外的其他列，都依赖与该主键，则满足第二范式（2NF） 第二范式要求每个表只描述一件事情
如果一个关系满足2NF，并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式（3NF）
第三范式要求其它列必须直接依赖于主键
使用三大范式减少了数据冗余，但是牺牲了查询性能
所以有时为了性能，需要做适当折中，适当牺牲规范化的要求，来提高数据库的性能。
SQL面试题
1列出EMPLOYEES表中各部门的部门号，最高工资，最低工资
2列出EMPLOYEES表中各部门EMPLOYEE_JOB为'CLERK'的员工的最低工资，最高工资
3对于EMPLOYEES中最低工资小于1000的部门，列出EMPLOYEE_JOB为'CLERK'的员工的部门号，最低工资，最高工资
4根据部门号由高而低，工资有低而高列出每个员工的姓名，部门号，工资
5列出'张三'所在部门中每个员工的姓名与部门号
6列出每个员工的姓名，工作，部门号，部门名
7列出EMPLOYEES中工作为'CLERK'的员工的姓名，工作，部门号，部门名