SQL学习笔记2
* SqlServer相比MySQLMySQL不支持事务处理，没有视图，没有存储过程和触发器，没有数据库端的用户自定义函数，不能完全使用标准的SQL语法
* 所以在这里我使用 SQL Server 2014 Management Studio来完成一些SQL Server的工作
T-SQL 编码
1：局部变量和全局变量
* 全局变量由系统提供且预先声明，通过在变量名前面添加“@@”符号区别于局部变量。用户只能使用全局变量不能修改全局变量的值。全局变量的作用范围是整个SQL Server系统
* 局部变量由用户自己定义，通过在变量名前面添加“@”符号区别于全局变量
例1：声明一个变量@testDateTime，将getdate()函数的值放入变量中，最后输出变量@testDateTime的值
declare @testDateTime varchar(30) select @testDateTime = getdate() select @testDateTime as '当前时间' go
运行结果
例2：声明一个变量@getAge，将mytestuser表里面id是2的字段的age的值放入变量@getAge中，最后输出@getAge的值
declare @getAge int select @getAge = age from mytestuser where id=2 select @getAge as '2号的年龄' go
运行结果
2：使用运算符
例1：将表达式67%31的值赋给变量@result
declare @result int select @result = 67%31 select @result as '结果' go
运行结果
例2：定义变量x和y，给变量赋值，然后求两个变量的与、或、异或的结果
declare @x int, @y int set @x = 2 set @y = 7 select @x & @y as '与', @x | @y as '或', @x ^ @y as '异或'
运行结果
例3：定义变量x和y，给变量赋值，然后求两个变量中较小的一个
declare @x int, @y int set @x = 12 set @y = 7 if @x < @y select @x as '小数据' else select @y as '小数据'
运行结果
例4：使用连接运算符（+）将两个字符串连接起来
--需要注意的是变量x的长度，如果长度不够，就不能完整的显示数据 declare @x varchar(70) set @x = '米诺八慈'+'的'+'博客园' select @x as '数据连接的结果'
运行结果
3：case语句
例1：如果年龄小于20岁显示“少年”，如果年龄在20岁到30岁之间显示“青年”，如果年龄在30岁以上显示“中年”
/*需要注意的是 1:在case和字段之间是有逗号隔开的 2:在when then之间不能使用&&表示并且，所以可以将条件倒置，以避免出现age<30&&age>20 */ select name,age, case when age>30 then '中年' when age>20 then '青年' when age<20 then '少年' end as level from mytestuser
运行结果
4：while表示循环
例1：显示字符串“米诺八慈”中的每一个字符以及其ASCII码
declare @position int,@str varchar(10) set @position = 1 --需要注意的是在SQL里面字符串的从1开始取值 set @str = '米诺八慈' while @position <=DATALENGTH(@str)--这里的长度是8 begin select SUBSTRING(@str,@position,1), ascii(SUBSTRING(@str,@position,1)) set @position =@position+1 end
运行结果
5：result语句
result可以从查询或过程中无条件退出，可在任何时候从过程、批处理或语句块中退出，而不执行result之后的语句
例1：通过存储过程求用户平均年龄
--删除存储过程 drop procedure mypro --创建存储过程 create procedure mypro @num int as return(select avg(age) from mytestuser where id < @num) --创建查询 declare @avg int ,@number int set @number = 4 exec @avg = mypro @number --将变量num的值传入存储过程并将存储过程的结果赋给变量avg select @avg as '平均年龄'
以上SQL语句一个一个的执行，执行创建查询的结果
用户自定义函数
例1：创建一个函数用于显示出较大的年龄的用户信息
--删除函数 drop function showMax --创建函数 create function showMax(@id1 int,@id2 int) returns varchar(50) --函数名 --函数体 as begin declare @result varchar(50),@name1 varchar(10),@name2 varchar(10),@age1 varchar(10),@age2 varchar(10) select @age1 = age,@name1 = name from mytestuser where id = @id1 select @age2 = age,@name2 = name from mytestuser where id = @id2 if (@age1<@age2) set @result = @name2 +'的年龄是：'+ @age2 else set @result = @name1 +'的年龄是：'+ @age1 return @result end /* 需要注意的是： 1：在函数名里面声明了id1和id2，在函数体里面就不需要声明可以直接使用了 2：必须要用@age1 varchar(10),@age2 varchar(10)将int类型转换成varchar类型，以便与@name2 +'的年龄是：'相连 */ --使用函数 select dbo.showMax(3,4) as maxMess
运行结果
游标
游标实际上就是一种能从包括多条数据记录的结果集中每次提取一条记录的机制
1：游标的类型
1：T-SQL游标
主要用于T-SQL脚本，存储过程和触发器。主要用在服务器上，由客户端发送给服务器的T-SQL语句或批处理、存储过程、触发器中的T-SQL进行管理。不支持提取数据块或多行数据
2：API游标
主要哦用在服务器上
3：客户游标
主要是当在客户机上缓存结果集时使用
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------由于T-SQL游标和API游标使用在服务器端上，也叫作服务器游标或后台游标。客户端游标又被称为前台游标
服务器游标包含：静态游标、动态游标、只进游标、键集驱动游标4种类型
2：游标的5种操作
声明游标 --> 打开游标 --> 读取游标 --> 关闭游标（如果不关闭游标，其他人就可以进入，非常危险） --> 释放游标
存储过程
存储过程以一个名称存储并作为一个单元处理，能够提高系统的应用效率和执行速度
存储过程是一种独立存储在数据库里面的对象，可以接受参数、输出参数、返回单个或多个结果集以及返回值，由应用程序通过调用执行
1：优势
1：模块化程序设计
只需要创建一次并将其存储在数据库里面，以后就可以在程序中调用该过程任意次
2：加快T-SQL语句的执行速度
如果某个操作需要大量的T-SQL语句或许要重复执行，存储过程将比批处理代码的执行快
3：减少网络流量
一条存储过程可以代替多条T-SQL语句，减少了在网络中发送的代码量
4：可以作为安全机制使用
数据库用户可以通过得到权限来执行存储过程，而不必给予用户直接访问数据库的权限
2：存储过程的类型
3：创建存储过程
--创建存储过程，用于查询用户平均年龄 create procedure user_age_avg as select avg(age) as age_avg from mytestuser go --执行存储过程 user_age_avg execute user_age_avg
运行结果
--创建存储过程，用于范围内查询用户的基本信息 create procedure user_infor @max int as select * from mytestuser where age < @max go --执行存储过程 user_infor execute user_infor 20
运行结果
4：查看存储过程
--查看创建存储过程的命令语句 sp_helptext user_age_avg
运行结果
--查看无参存储过程的名称、拥有者、类型、创建时间、以及参数 sp_help user_age_avg
运行结果
--查看有参存储过程的名称、拥有者、类型、创建时间、以及参数 sp_help user_infor
运行结果
5：修改存储过程
--修改存储过程。改成可以判断一个又开始有结尾的范围 alter procedure user_infor @min int,@max int as select * from mytestuser where age<@max and age>@min go --执行修改之后的存储过程 execute user_infor 20,50
执行结果
--修改存储过程。改成可以判断一个又开始有结尾的范围并输出平均年龄和年龄之和 alter procedure user_infor @min int,@max int,@sum int output,@avg int output as select @avg=avg(age),@sum=sum(age) from mytestuser where age<@max and age>@min go --执行修改之后的存储过程 declare @sum int ,@avg int execute user_infor 20,50,@sum output,@avg output print '在20岁到50岁之间的用户的年龄之和为:'+str(@sum)+' 用户平均年龄为:'+str(@avg) --str()将参数转化为字符串
运行结果
6：删除存储过程
drop procedure user_infor
触发器
1：触发器的优点
1：在数据库里面要实现数据的完整性约束，可以使用check约束或者触发器来实现，但是check不允许引用其他表里面的列完成检查工作，而触发器可以引用其他表里面的列完成数据的完整性约束
2：使用预先定义好的错误提示信息或动态自定义的错误提示信息
3：实现数据库中多张表的级联修改
4：比较数据库修改前后数据的状态
5：调用存储过程
6：用户可以使用触发器来保证非规范化数据库中的低级数据的完整性
2：触发器分类
3：创建触发器
--为用户表创建一个简单的DML触发器，在插入和修改数据时会显示提示信息 create trigger mydml on mytestuser for insert,update as print '你修改或者插入了数据' --插入数据测试 insert into mytestuser(id,name,age) values(6,'minuobaciTest',12)
运行结果
--删除触发器 drop trigger mydml --为用户表创建一个简单的触发器，用于了解表inserted和deleted表的功能 create trigger myTrigger on mytestuser for insert,update,delete as select * from inserted select * from deleted --测试 delete from mytestuser where id = 6 insert into mytestuser(id,name,age) values(6,'minuobaciTest',12) update mytestuser set age=84 where id=6
测试修改
测试添加
测试删除
--删除触发器 drop trigger myTrigger --为用户表创建一个简单触发器，在插入时会显示插入年龄 create trigger myTrigger on mytestuser for insert as begin declare @age int set @age=(select age from inserted) print '添加的用户年龄是:'+str(@age) end --测试 insert into mytestuser(id,name,age) values(7,'minuobaci7',12)
运行结果
4：查看触发器
1：通过系统存储过程查看触发器
sp_help myTrigger
查看结果
2：通过sp_helptrigger查看特定表上的触发器相关信息
sp_helptrigger mytestuser
查看结果
5：修改触发器
alter trigger myTrigger on mytestuser for insert,update as print '你修改或者插入了数据' --插入数据测试 insert into mytestuser(id,name,age) values(8,'minuobaci8',12)
测试结果
事务
1：事务的分类
根据系统的设置可以分成两类：
A：系统事务：执行某些语句的时候一条语句就是一个事务。需要注意的是一条语句的对象可能是表中的一行数据也可能是表中的多行数据（比如创建一张表）
B：用户自定义事务
根据运行模式可以分成4类
A：自动提交事务：每一条单独的T-SQL语句就是一个事务。如果没有通过任何语句设置事务，一条T-SQL语句就是一个事务，语句执行完事务就结束
B：显式事务：每个事务都是以begin transaction语句（定义一个事务的开始）、commit transaction语句（提交一个事务）或者rollback transaction语句（回滚事务）明确定义了什么时候开始什么时候结束
C：隐式事务：一个事物完成时新事务隐式开始，但每个事务仍然是以commit transaction或者rollback transaction语句显式结束
D：批处理事务：只能应用于多个活动集（MARS），在MARS会话中启动的T-SQL显式或隐式事务变成批处理级事务
SQL优化
SQL优化的原网页：http://database.51cto.com/art/201407/445934.htm
1：尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2：尽量避免在 where 子句中对字段进行 null 值判断，如果一定要判断，请记住'字段或变量 is null'或 '字段或变量 is not null'，不要使用:'字段或变量=null'或'字段或变量<>null'，否则将导致引擎放弃使用索引而进行全表扫描。最好不要给数据库留NULL，char(100) 型，在字段建立时，空间就固定了，NULL 也需要空间
2：应该尽量避免在 where 子句中使用 != 或 <> 操作符，这些操作符会使引擎放弃使用索引而进行全表扫描。如果一个字段有索引，一个字段没有索引，也导致引擎放弃使用索引而进行全表扫描
3：对于连续的数值，能用 between 和嵌套SQL语句就不要用 in ，in 和 not in 会导致全表扫描
4：避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描
select id from t where num/2 = 100 --最好使用以下SQL语句代替 select id from t where num = 100*2
5：select count(*) from table；这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的
6：索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定
7：尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些
