SqlServer T-Sql学习

添加数据

• 把a表数据添加到b表中

insert into table_a('字段1','字段2')
	select '字段1','字段2' from table_b;

• 创建一张表并把a表数据添加到新表中

select '字段1','字段2' 
	into new_table
	from table_a;

插入多条数据

insert into table_name('字段1','字段2')
	select 'test1','test_value1'union
	select 'test2','test_value2'union
	select 'test3','test_value3'

数据库连接

--查询Test数据库当前连接的人数
SELECT * FROM master.dbo.sysprocesses WHERE DB_NAME(dbid) = 'Test'
--查询当前连接 另一种写法
SELECT * FROM master.dbo.sysprocesses WHERE dbid IN 
(
SELECT dbid FROM master.dbo.sysdatabases
WHERE NAME='Test'--指定的数据库名字
)
--杀死spid为123的连接
kill 123

数据库备份

--A.bak是已有数据库备份文件 B是新创建的数据库

--要还原的数据库名
RESTORE DATABASE B
--之前已备份好的bak文件
FROM DISK = 'D:\backup\A.bak'
with replace,
--文件名 TO 文件路径名
MOVE 'B' TO 'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER1\MSSQL\DATA\B.mdf',
--文件名 TO 文件路径名
MOVE 'B_log' TO 'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER1\MSSQL\DATA\B_log.ldf'

--修改逻辑文件名 ALTER DATABASE 的 MODIFY FILE 语句更改数据文件或日志文件的逻辑名称

alter database 数据库名 modify file (name=逻辑名,newname=新逻辑名)

--上面的语句是更改了sysfiles和sysfiles1系统表中的name字段

--得到备份文件的逻辑文件名称 可以得到备份文件的信息记录 LogicalName字段为逻辑文件名称
RESTORE FILELISTONLY from disk='H:\Test.bak'

sql抛异常语句

begin try
raiserror('自定义错误',16,1)--自定义异常级别范围11~19
end try
begin catch
declare @error_message varchar(1000)
set @error_message=error_message()
raiserror(@error_message,16,1)
return
end catch

cmd登录sqlserver osql -S /mssqlserver -Usa -Pmima123

N前缀指定后面的字符串为UNICODE

变量

declare @test int,@name nvarchar(20)--声明局部变量
@test int,@name nvarchar(20)--声明全局变量
set @test=1--为变量赋值 仅限单个变量
print @test--打印变量 仅限单个变量

select @test=2, @name='lilei'--select可对多个变量赋值
select @test,@name--select可查询多个数据

select name,@name='李磊' from user--在查询语句中赋值 对查询结果没有影响
print @name
--update赋值 还有什么其他赋值 不多赘述

常用存储过程

exec sp_databases; --查看数据库
exec sp_tables; --查看表
exec sp_columns student;--查看列
exec sp_helpIndex student;--查看索引
exec sp_helpConstraint student;--约束
exec sp_stored_procedures;
exec sp_helptext 'sp_stored_procedures';--查看存储过程创建 定义语句
exec sp_rename student, stuInfo;--修改表 索引 列的名称
exec sp_renamedb myTempDB, myDB;--更改数据库名称
exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库
exec sp_helpdb;--数据库帮助 查询数据库信息
exec sp_helpdb master;

--表重命名
exec sp_rename 'user', 'users';
select * from users;
--列重命名
exec sp_rename 'users.name', 'u_name', 'column';
exec sp_help 'users';
--重命名索引
exec sp_rename N'users.idx_id', N'idx_cid', N'index';
exec sp_help 'users';

--查询所有存储过程
select * from sys.objects where type = 'P';
select * from sys.objects where type_desc like '%pro%' and name like 'sp%';

自定义存储过程

GO不是T-SQL语句 sqlserver数据库方言指定的提交批处理的方式
GO 命令和Transact-SQL语句不能在同一行中 在GO命令行后可包含注释

--创建不带参数存储过程
if (exists (select * from sys.objects where name = 'procName'))
drop proc procName
go
create proc procName
as
select * from users;
--执行存储过程
exec procName;
--修改存储过程
alter proc procName
as
select * from users;

--可变参存储过程
if (object_id('procName', 'P') is not null)
drop proc procName
go
create proc proc_findStudentByName(
@name varchar(20) = '%abc%',--使用通配符
@nextName varchar(20) = '%'
)
as
select * from users where name like @name and name like @nextName;
go

exec procName test,lilei

--参数存储过程
if exists(select name from sysobjects where name= 'procName' and type='P')
drop procedure procName
go
create proc procName
@id int,--输入参数
@name varchar(20) out,--输入输出参数
@address varchar(20) output--输出参数
with recompile--存储过程不缓存
with encryption--存储过程加密
as
set nocount on|off--显示|不显示影响行数
select * from users;
set @id=123
set @name='sqlserver'
set @address='China'
return @id
go

declare @id int,
@name varchar(20)='lilei',
@address varchar(20)
exec @id=procName 1,@name out,@address
print @id--123
print @name--'sqlserver'
print @address--'China'

SQL Server 系统全局变量
@@CONNECTIONS--返回自上次启动以来连接或试图连接的次数
@@CURSOR_ROWS--返回连接上最后打开的游标中当前存在的合格行的数量(返回被打开的游标中还未被读取的有效数据行的行数)
@@DATEFIRST--返回每周第一天的数字
@@ERROR--返回最后执行的SQL 语句的错误代码
@@FETCH_STATUS--返回被FETCH语句执行的最后游标的状态 而不是任何当前被连接打开的游标的状态
@@IDENTITY--返回最后插入的标识值
@@LANGID--返回当前所使用语言的本地语言标识符(ID)
@@LANGUAGE--返回当前连接中处于激活状态的事务条目

case when详解

when后跟条件 then后跟翻译值 必须加end 不加else默认匹配到的值显示为null

SELECT CASE sex
WHEN 1 THEN 'male'
WHEN 2 THEN 'female'ELSE 'xxxx' END
FROM  test

SELECT CASE
WHEN sex=1 THEN 'male'
WHEN sex=2 THEN 'female'ELSE 'xxxx' END
FROM  test

--一个case链只展现一列数据且类型一致 多个when 链在一起关联的数据只会匹配第一个when
--如性别为1的已经全部翻译成male 又when名为'张三'的翻译成man 同一个case中不能重复翻译
SELECT CASE
WHEN sex=1 THEN 'male'
WHEN NAME='张三' THEN 'man'ELSE 'xxxx' END
FROM  test

--这种情况不会出现后一个when不执行 when的字段不同
SELECT CASE
WHEN sex=1 THEN 'male'
WHEN NAME='丽丽' THEN 'man'ELSE 'xxxx' END
FROM  test

--如永远无法得到'two'
CASE WHEN col_1 IN ( 'a', 'b') THEN 'one' 
WHEN col_1 IN ('a') THEN 'two' 
ELSE'其他' END

--可以and多个调节
SELECT CASE
WHEN sex=1 AND NAME='张三' THEN 'man'
WHEN sex=2 and NAME='小红' THEN 'woman'ELSE 'xxxx' END
FROM  test

--用Case函数来实现把主键a和b相互交换
UPDATE test
SET id = CASE WHEN id = 'a' THEN 'b' 
WHEN id = 'b' THEN 'a' ELSE id END 
WHERE id IN ('a', 'b');

--游戏中属性调整 每次游戏大更新经常会调整人物数据 需求如下
--属性值5000以上减少10% 2000-4600 增加15%
--某人物属性值5000 条件1 属性值减少10% 变成属性值4500
--接下来运行第二个SQL时 因为此人物属性值是4500在2000到4600的范围之内 需增加15%
--最后此人物属性值为5175 不但没减少 反而增加
--如果想要一个SQL实现此功能 必须并行执行而不是串行执行 用Case when 语句如下
UPDATE test
SET power =
CASE
WHEN power >= 5000 THEN power * 0.9
WHEN power >= 2000 AND power < 4600 THEN power * 1.15
ELSE power END;

cast|convert

cast(expression AS data_type)

convert(data_type expression)

两种函数用法一致 日期转换时用convert cast性能好

expression 有效的SQServer表达式
AS 分隔两个参数 AS前是要处理的数据 AS后是要转换的数据类型 不能使用用户定义的数据类型

转换的类型如下
二进制 BINARY
字符型 CHAR()可带参数
日期 DATE
时间 TIME
日期时间型 DATETIME
浮点数 DECIMAL
整数 SIGNED
无符号整数 UNSIGNED

游标使用

--游标相当于Java中的for-each循环
declare @var nvarchar(max)--声明变量
declare cr1 cursor for select data from user_info--创建游标
open cr1--打开游标
fetch next from cr1 into @var--循环遍历到@var中 此时只是把游标指针往下移动一次
while @@FETCH_STATUS = 0--判断数据是否遍历完
begin
	begin
	--sql语句
	end
fetch next from cr1 into @var--随着循环 游标指针自动往下移动
END
--关闭游标后 游标区数据不可再读 但可使用OPEN再次打开该游标
close cr1--关闭游标
--删除定义游标的数据结构 整个游标消失
deallocate cr1--删除游标

日期获取

--获取指定日期和当前日期直接的月数
select DATEDIFF(MM,'2019-01-01 00:00:00',GETDATE()) as 月数
--获取指定日期和当前日期直接的周数
select DATEDIFF(WW,'2019-01-01 00:00:00',GETDATE()) as 周数
--获取指定日期和当前日期直接的月数
select DATEDIFF(DD,'2019-01-01 00:00:00',GETDATE()) as 天数

uion|union all区别

union all是把结果集直接合并在一起 不做任何处理 效率较高
union是把union all后的结果进行一次distinct 去除重复记录后的结果

排它锁又称写锁(X锁) 共享锁又称读锁(S锁)

1. 写数据时加上X锁 直到事务结束 读的时候不加锁(脏读)
   能避免丢失数据 但可读到未提交或回滚前的内容 Read uncommitted

2. 写数据时加上X锁 直到事务结束 读的时候加上S锁 读完数据释放S锁(不可重复读)
   能避免丢失数据&脏数据 但同一事务中两次读到的内容不一致 Read committed

3. 写数据的时候加上X锁 直到事务结束 读数据的时候加S锁 事务结束释放S锁(可重复读)
   能避免丢失数据&脏数据&不可重复读 Repeatable read

4. 现有两事务A B A修改完某数据后B也对此数据进行修改 然后A操作者执行查询 查到其他人修改后的数据 如同(幻读) 只能事务串行执行
   数据库事务最高隔离级别 Serializable

隔离级别	数据丢失	脏读	不可重复读	幻读
Read uncommitted	No	Yes	Yes	Yes
Read committed	No	No	Yes	Yes
Repeatable read	No	No	No	Yes
Serializable	No	No	No	No

持续更新 sqlserver基础随笔

转载于:https://www.cnblogs.com/setlilei/p/10629470.html