数据库事务,游标,触发器,存储过程,索引,数字，日期转换为字符,字符串操作,查询，分类，内连接,外连接,全连接,模糊查询,范围查询,5种聚合函数,分组查询,主键，外键，标识列

 

 

----------------事务，并发事务------------------------------------------------------------
数据库事务是什么，作用，并发事务产生的问题及解决方案？
由一系列T-SQL语句构成的逻辑工作单元；完成一定的业务逻辑，操作封装起来，形成边界，将一系列的操作打包执行；

事务的应用？ 多个表的操作，处理全部成功，或由任何一个错误则全部不成功，回滚到最初状态

事务的作用：增强了数据的安全性，提高了处理效率，维护数据的完整性；

事务的特性：ACID：
1.原子性atomic,一个事务整体提交或回滚
2.一致性：condemoltent数据必须保持一致性，数据状态一致
3.隔离性：isolated多个事务之间是彼此隔离的，独立性
4.持久性：durabiliy，持久化了，永久保存的

1.事务创建
begin try
开启事务begin tran
insert
update
delete
提交事务commit tran
end try
begin catch
回滚事务rollback tran
end catch

多个事务并发，是不是互不影响呢？
2.数据库事务并发：产生的4种问题
1.数据丢失或覆盖更新：同一行，a=35;b=36，一个事务在不知道其他事务存在的情况下，对数据进行修改
2.脏读：一个事务读取了另一个事务并未提交的数据
3.不可重复读：多个事务访问同一条数据，每次读取的数据不同
4：幻象读：第1次读取的数据行与第2行读取的不一致；

解决办法：在事务种设置事务隔离级别；隔离级别设置低，会造成很多并发问题，

隔离级别：
1.read committed 默认的级别；避免脏读；会产生不可重复读，幻象读
2.read uncommitted 0级别，上面4种并发问题都会产生
3.repeatable read：避免脏读，不可重复读；产生 幻象读
4.serializable：避免脏读，不可重复读，幻象读

---------函数-标量函数，内嵌表值函数，多声明表值函数---------------------------------
1.作用：字符串处理，类型转换
2.函数类型：标量函数，内嵌表值函数，多声明表值函数
标量函数: 对单一值操作，返回单一值 ,查询使用，不能修改数据
create fuction [dbo].GetUserAge(@userId int）
returns int
--with encryption 加密
as
begin
declare @age int
select @age=age from UserInfors where UserId=@userId 查询语句，不可以有修改数据的操作
return @age
end
go

函数调用: select dbo.GetUserAge(3)，select dbo.GetUserAge(default)，
语法：returns 返回类型，return 返回值 表
创建：指定了函数的所有者，调用时必须指定函数的所有者

内嵌表值函数:
概念：功能上相当于一个参数化的视图，没有begin end
create function [dbo].GetUserInfo(@userId int)
returns table
as
return (
select userId,UserName,age from UserInfos where UserId=@userId
）
go

调用：select * from dbo.GetUserInfo（23）
语法：返回值只能时table,没有begin end,只能时return(select 语句）

多声明表值函数:
概念：有begin end,返回表，定义表的结构
create function [dbo].SearchUsers(@uName varchar(50))
returns @users table(
Id int not null primary key,
UName varchar(50) not null,
UAge int null
）
begin
insert into @users(Id,UName,UAge)
select UserId,UserName,Age from UserInfos where UserName like '%' +@uName + '%"
return --后面什么也不跟
end
go

调用：select dbo.SearchUsers('a')
注意点：指定了函数的所有者，调用时必须指定函数的所有者

对比；
标量：return 返回值类型, begin end ,return 表达式，变量
内嵌：return 返回table ,return (select 语句)
多声明：return 返回@users table(表结构定义) ,begin end, return

----------------------------游标的使用------------------------------------------
1.游标概念：对数据查询结果集的访问机制，用户对结果集追条访问，对行数据读写
2.原理：读取数据存放在内存，所以读取的数据集不可太大，适用数据小的情况
3.分类：静态游标，动态，只进，键集驱动游标
静态游标：获取结果集后，对数据库如何操作，结果集不变，除非从新获取结果集
动态：前后滚动，对数据库的操作会影响结果集，相互联动影响获取数据库实时数据；
只进：不支持滚动，从头读到尾，对数据库所做的修改是可见的，只进不退

4.静态游标不支持滚动，消耗资源少，效率高--只读取数据
5. 游标声明周期：声明--打开--读取数据--关闭--释放游标

demo：
--1.定义游标
delcare user_cursor cursor local --global 全局游标
for
--查询t-sql
select UserId,UserName,Age from UserInfos where UserId > 2
open user_cursor --打开游标
declare @varUser cursor, @uId int,@uName varchar(50),@age int --@varUser 读取每行的数据
set @varUser=user_cursor
fetch next from @varUser into @uId,@uName,@age --next 向下提取，prior,first,last,absolute
while @@fetch_status=0
begin
print cast(@uId as varchar) +','+@uNmae+','+convert(varchar,@age)
fetch next from @varUser into @uId,@uName,@age
end
close user_cursor --关闭游标
deallocate user_cursor

--------------------触发器----------------------
1.概念：一种特殊的存储过程，
2.调用：通过事件触发，自动执行，对表的插入，更新，删除操作时自动触发
3.分类：DDL触发器：create,alter,drop
DML触发器：insert,update,delete
4.DML：after触发器：insert,update,delete
instead of： insert,update，delete

--------------------存储过程------------------------
1.定义：一种为了完成特定功能的一个或一组sql语句集合，经编译后存储在服务器端的数据库中
2.分类：
系统存储过程：master数据库中，在其他数据库中可以直接调用，不必加数据库名；创建数据库时，系统存储过程会自动创建
自定义存储过程：用户自己创建的，可以传入参数，也可以返回值
执行：execute/exec spname parameter(多个，以逗号分开）
3.优点：提高应用程序的通用性和可移植性，多次调用，可以修改
4.缺点：专门维护，占用空间

5.语法：
create proc 存储过程名
@userId int,
@userName varchar(50)
as
begin
--t-sql
end

-------------索引-------------------------------------------------
1.作用：提高查询速度，
2.索引是什么？一本书，目录查询
3.索引--目录，快速定位我们要查找的数据，避免扫描整个表
4.索引缺点：占用存储空间，
5.分类：
聚集索引：主键索引，逻辑顺序与存储的物理顺序是一致的；一个表可以没有聚集索引，有只有一个
非聚集索引：唯一索引，逻辑顺序与存储的物理顺序不是一致的；可以有多个，可以没有

组合索引：多个列上建立索引；
索引列：小数据类型，访问速度快；
要合理的创建索引

6.脚本创建索引：
--默认非聚集
create clustered index PK_UserInfos --聚集索引
on UserInfos(UserId)
with(
drop_existing=on --先删除原有的
)

create unique nonclustered index PK_UserInfos --非聚集索引
on UserInfos(UserName)
with(
drop_existing=on --先删除原有的
)

create nonclustered index PK_UserInfos --复合索引
on UserInfos(UserName，DeptId)
with(
drop_existing=on --先删除原有的
)

注意：一个表只能有一个聚集索引，可以有多个非聚集索引

--------------数据库类型转换------------把数字，日期转换为字符-----------------------
1.支持数字 + 数字 求和；字符+字符 拼接
2.select 'abc'+convert(varchar,2)
convert(varchar,value) --把数字，日期转换为字符
cast(表达式 as 类型）：cast(2 as varchar)
convert(varchar(10),getdate(),100)

---------------字符串操作-------------------------------------------------
1.查找开始出现的位置：select CHARINDEX('bc','abcd') --数据库的索引从1开始；PATINEX('%bc%','abcd')
2.大小写转换：LOWER('ABCD'),UPER('abcd')
3.去空格:ltrim(' abc');rtrim(' abc d ')
4.去子串:select left('abcdef',4);right('abcdefg',4) --返回字符串从右边去4个长度，substring('abcde,3,4)
5.重复次数：select replicate('abc',4) --将abc重复4次
6.替换：replace('abced',bc','ss')
7.顺序颠倒:reverse('abcdef')
8.去长度：len('abc')

-------------------------连接查询，分类，内连接,外连接,全连接---------------------------------
1.连接查询：根据2个或多个表的关系，来查询数据，实现多表查询
2.分类：
内连接：inner join 使用比较运算符：= > < >= <= <> 查询条件匹配的数据
select userid,u.deptId
from UserInfo u
inner join DeptInfos d on u.DeptId=d.DepeId
where Age >25

select userid,u.deptId
from UserInfo u,DeptInfos d
where u.DeptId=d.DepeId and Age >25

外连接：
左外连接：left join on =left out join返回左表的所有行，右表比配有数据显示，没有显示Null
右外连接: right join on =right out join

全连接：
full join=full out join 返回左边和右表的所有行，有匹配显示数据，没有显示null

交叉连接：
cross join where u.id=d.id --只显示匹配到的结果集，没有条件是返回row * row

-----------------------------模糊查询 Like ，匹配效率不高----------------------------------
1. % 0个或多个,匹配字符和数字类型
like '%a%' 任意位置含a
like '%a' 以a结尾
like 'a%' 以a开头

2._ 匹配单个字符,限制表达式的字符长度 like '_____' -5个字符长度的
3.[] 范围匹配，括号中所有字符中的一个ad[dmin]n
4.[~] :

-------------------------------范围查询 in,not in,beween----------------------
1. 比较运算符
2. in(2,3,4);not in(2,3,4)
3. between and =age >=20 and age <=33

-------------------------------聚合查询 5种聚合函数-----------------------------
1.聚合函数：对一组值执行计算并返回单一的值；count
2.5种聚合函数：
count:
sum
avg
max
min

------------------------------分组查询-----------------------
select ---
where ---
group by ---

------------------------------主键，外键，标识列-----------------------
创建了主键，就自动创建了聚集索引
外键：一般在2个表直接建立关联，外键，必须是在另外一个表里是主键
2个表一但建立外键关系：外键值必须是另一个主键表里存储的值，有约束关系，
一个表只有一个主键，可以有多个外键

主键约束：primary key
外键：foreign key 约束
unique约束：
check约束；
default约束：