sql server基础知识
2019-09-29 本文已影响0人
Jure_joe
**Sql server****数据库知识**
1、 主键自增identity
create table user
(
id int,identity(1,1) primary key,
…
)
2、 分组中的compute by
Select * from table_name order by age compute max(salary) ,min(salary),sum(salary), avg(salary) by age;
3、 exists字段使用
select * from clerk_table where exists(select * from clerk_table where school =’‘黑龙江’)
4、 all 和 any字段的使用
select * from clerk_table salary > any (select salary fom clerk_table where school =
‘哈工大’)
要求工资大于后边所有条件的条件
select * from clerk_table salary > all (select salary fom clerk_table where school =
‘哈工大’)
5、 top查询
查询前几条记录
select top 3 * from clerk_table
查询前百分之十
select top 20 percent * from clerk_table
6、 into关键字使用
如果表不存在会自动进行创建，将另外一张表查询到的记录放入该表中
Select * into new_table from clerk_table where salary > 1000
7、 union interset关键字使用
集合并和集合交运算，第一个为两个表一起显示，第二个为两个表分开显示
select * from clerk_table union select * from new_table
select * from clerk_table interest select * from new_table
8、 等值链接
select * from A,B where A.x = B.x and A.age=18
9、 内链接
select * from A inner join B on A.x=B.x where A.age=18
10、外连接 左表或右表全数据，另一张表中的数据在新查询结果的表中如果没有数据会显示空值
select * from A left/right join B on A.x=B.x where A.age=18
11、全连接
select * from A full join B on A.x=B.x where A.age=18
12、查看索引
exec help_index clerk_table
13、创建索引,以工资字段进行创建，并使用倒序
create index new_index clerk_table(salary desc)
14、更改索引名称
exec sp_rename ‘clerk_table.new_index’,’new_index2’,’index’
15、删除索引
drop index clerk_table.new_index2
16、创建视图,视图本身就是依赖sql语句存在的，使用视图安全性较好，使一些没有权限的用户无法看到完整的表信息，但是性能不好，毕竟使用后，底层还是将其拆分为最底层的sql语句
create view view_name as select * from clerk_table where salary>2800
17、修改视图
alter view view_name as select * from clerk_table where salary <1220
18、查询视图
select * from view_name
19、删除视图
drop viw_name
**T-SQL****语法**
1、 变量的声明（declare）、变量的赋值（set 或者 select）,set只能为当一变量的赋值，select可以为多个变量赋值，中间用逗点隔开即可
declare @test_var int
set @test_var = 1000
select @test_var=3000
2、 输出（print 或 select）print输出一个，select可输出多个
print @test_var
select @test_var ,@test_var2
3、 全局变量以两个@开头
4、 注释：
/*
这是多行注释
*/
--这是单行注释
declare @test_var INT ,@test_var2 varchar(60)
select @test_var = 1000,@test_var2='jure_joe'
select @test_var,@test_var2
5、 begin…end程序块，类似于java中的代码块
--这是一个begin....end程序块
BEGIN
declare @test_var int , @test_var2 varchar(60),@test_var3 varchar(60)
select @test_var=2000,@test_var2='你这头猪',@test_var3='妞妞'
IF @test_var!=2000
print @test_var2
ELSE
print @test_var3
END
6、 case语句
BEGIN
declare @test_var int , @test_var2 varchar(60)
set @test_var=100
set @test_var2 =
CASE
when @test_var>=90 and @test_var<=100 then '优秀'
when @test_var<90 and @test_var>=80 then '良好'
when @test_var<80 and @test_var>=60 then '及格'
else
'不及格'
END
select '学员成绩：'+ @test_var2
END
7、 while语句 break语句、continue语句
declare @sum int,@test_var int
select @test_var=0,@sum=0
while @test_var<=100
begin
set @sum=@sum+@test_var
set @test_var=@test_var+1
end
select '求得的和为:',@sum
8、 goto语句
declare @sum int,@test_var int
select @test_var=0,@sum=0
--自定义标签，指定跳转的地点
test_target:
set @sum = @sum + @test_var
set @test_var = @test_var+1
while @test_var<=100 goto test_target
select '求得的和为:',@sum
9、 算术函数：SIN()函数、cos()、tan()…
declare @test_var int,@test_var2 int
set @test_var =100
--算术平方根
set @test_var2 = SQRT(@test_var)
print @test_var2
——ROUND函数
declare @test_var float,@test_var2 float
set @test_var =75.16431
--保留为两位小数
set @test_var2 = round(@test_var,2)
print @test_var2
10、字符串的操作函数left()、right()、substring()、reverse()、replace()、upper()、lower()….
11、函数创建
create function test_function (@test_var int) returns int
as
begin
declare @sum int
set @sum = @test_var*8
return @sum
end
12、函数的调用print 或 select
print dbo.test_function(20)
select dbo.test_function(190)
13、使用循环语句调用函数
declare @test_var int
set @test_var=0
while @test_var<=20
begin
print dbo.test_function(@test_var)
set @test_var = @test_var+1
end
14、函数修改
alter function test_function (@test_var int) returns int
as
begin
declare @sum int
set @sum = @test_var*979
return @sum
end
15、存储过程：在本地编写t-sql会大量占用带宽，但是在服务器这边做好存储过程后，会减少t-sql的使用，一次性完成，减少不必要的带宽，避免频繁修改应用程序，若想改，修改存储过程即可；
（1）、系统存储过程
a、sp_attach_db:附加数据库
b、sp_detach_db:分离数据库，并没有把数据库删除
c、sp_rename:给对象改名
Exec sp_rename 就数据库名 新数据库名
d、sp_help：显示数据库相关数据
e、sp_changedbowner:改变数据库所有者
Exec sp_changedbowner ‘所有者’
f、sp_password:改密码
Exec sp_password 旧密码 新密码 登录名
16、存储过程的创建
create proc new_proc @test_param varchar(60),@test_param2 varchar(59)
as
begin
print '第一个参数：'+@test_param
print '第二个参数：'+@test_param2
end
17、存储过程的调用
exec dbo.new_proc '大家好','不想活了'
18、存储过程中输出参数output的使用，相当于返回值
(1)、创建
create procedure [dbo].[ner_proc2] @test_var int,@test_var2 int,@test_var3 int output
as
begin
set @test_var3 = @test_var +@test_var2
end
(2)、调用
declare @test_var int
set @test_var = 60
print @test_var
execute dbo.ner_proc2 600,900,@test_var output
print @test_var
19、存储过程中增、删、改、查的使用
(1)、查询
create procedure pro_select @test_var varchar(40)
as
begin
select * from dbo.student where name = @test_var
end
(2)、查询调用
declare @test_var varchar(40)
set @test_var = '杨添佑'
execute dbo.pro_select @test_var
20、存储过程的修改，在数据库中直接右键，点击修改，或者将创建存储过程的语句中将create关键字改为alter
(1)、修改
ALTER procedure [dbo].[pro_select] @test_var int output
as
begin
select @test_var=MAX(change) from dbo.student
end
(2)、调用
declare @test_var int
execute dbo.pro_select @test_var output
select @test_var as change
21、存储过程改名：sp_rename 旧名字 新名字
删除存储过程：drop procedure 数据库名字
**游标的使用**
1、游标的创建：for read only 为只读游标，不写为普通游标 ，for update 为游标的更新
declare test_cusor cursor for
select * from dbo.student
for read only
2、游标的调用：open关键字为打开游标，打开后才能使用，和使用数据库的use关键字差不多，想使用游标的话，使用fetch关键字
open test_cusor
--逐条向后查询
fetch next from test_cusor
3、来回自由跳动的游标:scroll
declare test_cusor1 cursor scroll for
select * from dbo.student
for read only
--打开游标
open test_cusor1
--直接跳到最后的一条游标
fetch last from test_cusor1
--直接跳到第一条游标
fetch first from test_cusor1
--执行下一条游标
fetch next from test_cusor1
--从后往前执行
fetch prior from test_cusor1
--跳转到指定的游标
fetch absolute 3 from test_cusor1
--相对游标，使用relative关键字，以当前的游标为原点，向前使用负数，向后使用正数
fetch relative 3 from test_cusor1
**触发器**
1、 触发器的创建：指对该表进行增删改操作时会调用该触发器（事后触发器，关键字为for）
create trigger test_trigger on dbo.student for insert as
begin
print '触发工作开始了'
end
2、 更新触发器：
create trigger trigger_update on dbo.student for update as
begin
print '更新触发器触发了'
end
3、 删除触发器：
create trigger trigger_select on dbo.student for delete as
begin
print '删除了'
end
4、 通过存储过程指定触发器的顺序：sp_settriggerorder为系统的存储过程，@triggername为触发器的名称，@order为指定触发的顺序，有first,last,null;null为不指定触发器的顺序，@stmttype为指定对表执行什么操作所对应的触发类型，有update,delete,insert
sp_settriggerorder @triggername='test_trigger',@order='last',@stmttype='insert'
5、 替代触发器：使用关键字为instead of,即为替代了对表的增删改操作，不会对表的内容进行影响
create trigger trigger_insteadof on dbo.student instead of insert as
begin
print '触发了'
end
6、 如何使用t-sql语句修改触发器，将创建的create关键字改为alter即可；删除触发·起则使用drop trigger 触发起名字
7、 查看触发起基本信息的存储过程：sp_help
exec sp_help trigger_update
