数据库-基础

distinct　　去重

select distinct stuId,subject from Subject

如果是多列的话，1，语文，2，语文，这样不视为重复，除非是2，语文，2，语文这样视为重复

where条件过滤

　　多条件过滤：and or not（优先级：not>and>or）

　　区间过滤：between、in

通配符

　　%　　匹配任意多个字符

　　_　　 匹配任意一个字符

//匹配‘赵’开头的
where Name like '赵%'
//匹配第二个字是‘南’的
where Name like '_南%'
//匹配带单引号的,两个引号表示一个引号
where Content like '%''%'
//匹配带数字0-9的
where Content like '%[0-9]%'
//匹配带有‘['的
where Content like '%[[]%'
where Content like '%\[%' escape '\'

空值

//为空
where Content is null
//不为空
where Content is not null

类型转换

//同一种类型的数据相加，不需要转换，而且int类型数据是把数据增加，不是拼接
select  BookTypeId+BookId from Book
//不同类型拼接，需要进行类型转换
//convert（目标类型，转换的表达式，格式规范）
select CONVERT(varchar, BookTypeId)+BookName from Book
//cast(转换的表达式 as 目标类型)
select cast(BookTypeId as varchar)+BookName from Book

日期函数

getdate()　　获取当前日期

dateadd()　　为日期添加一天

//dateadd（单位，个数，日期）
select dateadd(day,1,'2017-8-14')

datediff()　　获得时间差

//获取的月数跟天数无关
select datediff(month,'2017-8-14','2017-10-1')
select datediff(second,'2017-8-14','2017-10-1')

datepart()　　获取部分日期

select datepart(year,'2017-8-14')==select year('2017-8-14')
select datepart(month,'2017-8-14')

字符串函数

lower()　　转小写

Ascii()　　码

select ascii('A')    //65
select ascii('a')    //97

left()　　从左边起截取多少个字符

select left('123456',4)    //1234

len()　　获取字符个数

select len('1234')    //4
select len(N'1234')    //4    N表示用Unicode字符存储，一个字符用两个字节存储

datalength()　　获取长度

select datalength('1234')    //4
select datalength(N'1234')    //8

ltrim()　　去掉左边空格

ISNULL()　　替换(并没有实际替换)

//isnull（表达式，替换的内容）
select createtime,isnull(createtime,getdate()) from book

临时表

　　解决多表链接查询高并发处理的效率

create table #demo()
//全局临时表
create table ##Test()

 X 排他锁　　S 共享锁

select * from Book with(nolock)//不对查询添加共享锁

异常处理

begin try

end try
begin catch

end catch

事务

事务是恢复和并发控制的基本单位

应具有4个属性：原子性、一致性、隔离性、持续性

//数据库
begin transaction    //开启事务
commit transaction    //提交事务
rollback transaction    //回滚事务
//C#    第一种写法
SqlTransaction tran = con.BeginTransaction();
try{
    cmd.CommandText=sql;
    cmd.Transaction=tran;
    cmd.ExectuteNonQuery();
    tran.Commit();        
}
catch(Exception ex){
  tran.Rollback();  
}
//C#    第二种写法
1、添加引用（System.Transactions）
try
            {
                using (TransactionScope scope = new TransactionScope())
                {
                    using (SqlConnection con=new SqlConnection(conStr))
                    {
                        using (SqlCommand cmd=new SqlCommand(sql,con))
                        {
                            con.Open();
                            cmd.ExecuteNonQuery();
                        }
                    }
                    scope.Complete();//提交事务，如果执行前失败，自动回滚
                }
            }
            catch (Exception ex)
            {
                throw;
            }

存储过程

if(exists(select * from  sys.all_objects where name='proc_book'))
    drop proc proc_book
go
create proc proc_book
@pageSize int,
@pageIndex int,
@totalCount int output
as
select *  from 
(select *,ROW_NUMBER() over(order by booktypeid) as num from booktype ) as T
where t.num between ((@pageIndex-1)*@pageSize+1) and (@pageSize*@pageIndex)
select @totalCount=COUNT(*) from BookType
//调用
declare @total int 
exec proc_book 2,2,@total out
print @total

优点：使用存储过程可以让我们只修改存储过程就可以修改程序业务，不需要修改代码

　　　执行速度比ADO.Net程序中写Sql要稍微快一些，快在sql脚本分析上了

缺点：数据库的可移植性低。

　　　维护不方便

自定义函数

//获取书类型的id
create function Fun_GetBookType(@BookTypeName nvarchar(10))
returns int
as
begin
    declare @id int 
    set @id=(select BookTypeId from BookType where BookTypeName=@BookTypeName)
    return @id
end
//调用执行
select dbo.Fun_GetBookType('小说')

表、值函数

//根据id获取书类型，返回table
create function Fun_GetBookTypeInfo(@BookTypeId int)
returns table
as
    return (select * from BookType where BookTypeId=@BookTypeId)
//调用
select * from Fun_GetBookTypeInfo(1)

触发器

　　相当于程序中的事件

//返回插入数据的自增Id
 insert into BookType output inserted.BookTypeId values('历史')

　　触发器

//create trigger 触发器名 on 表
//for || instead of(替换)   操作类型（insert || update || delete）
//as
//begin
//操作语句
//end
//给刚添加的那条数据name后面加上日期
create trigger TR_BookType_Insert on BookType
for insert
as
begin
    declare @bookTypeID int
    select @bookTypeID=BookTypeId from Inserted //  updated || deleted 跟for后面的类型对应
    update BookType set BookTypeName=BookTypeName+case(getdate() as nvarchar(50)) where BookTypeId=@bookTypeID
end

GUID类型

//数据库使用，类型为uniqueidentifier
insert into Book values(newid(),"战狼II")；
//应用程序使用
//千万不要使用Guid id=new Guid();这样返回的是000000-000000-000000-000
Guid.NewGuid()

权限管理

//修改权限
grant select on 表 to 用户

　　创建表，指定架构

create table zhao.Book
(
    Id int primary key identity(1,1) not null,
    Name nvarchar(20) not null
)
select * from zhao.Book

文件组

　　帮助优化高并发访问的效率

　　默认在primary文件组里

create table Book(
 Id int primary key identity（1,1） not null   ,
 Name nvarchar（20） not null
)on 文件组

数据库优化

　通过文件组可以把数据库的表放到不同的磁盘上，达到并行使用多个磁盘的io资源，提高读写效率