sqlservel常用语法

--sql server数据库2008以及2008r2常用的语法和查询
--自定义变量的赋值和使用
use student
insert into course values('计算机原理与技术') 
insert into course values('数据库概论') 
insert into course values('网络安全原理') 
insert into course values('大学数学')
insert into course values('线性代数') 
select * from course
insert into dept values('软件工程') 
insert into dept values('金融管理') 
insert into dept values('人文学院') 
select * from dept
insert into student1 values('张三','1') 
insert into student1 values('李四','1') 
insert into student1 values('王五','1') 
insert into student1 values('前六','2') 
insert into student1 values('旺达','2') 
insert into student1 values('孙氏','2')
insert into student1 values('李二','1') 
insert into student1 values('离散','1') 
insert into student1 values('赵六','1')  
select * from student1
insert into sudentcourse values('80','1','1','1') 
insert into sudentcourse values('60','2','2','2') 
insert into sudentcourse values('65','3','3','3') 
insert into sudentcourse values('85','4','4','3') 
insert into sudentcourse values('75','6','5','2') 
insert into sudentcourse values('76','7','4','1') 
insert into sudentcourse values('77','8','3','3') 
insert into sudentcourse values('76','9','2','2') 
select * from sudentcourse
declare @sum int
select @sum=COUNT(*) from sudentcourse where cno='2'
select @sum 
declare @sum1 int
select @sum1=COUNT(*) from student1
select @sum1
declare @credit float
update sudentcourse set @credit=scgrade where sno='7'
select @credit
declare @grade char(20)
update student1 set @grade=sname where sno=1
select @grade
declare @grad1 int
update student1 set @grad1=sno where sname='李四'
select @grad1
declare @score int
set @score=(select COUNT(*) from student1)
select @score
declare @score1 int 
set @score1=@@ROWCOUNT
select @score1--使用自定义的变量


USE book
create table userBook(
userBookId int identity(1,1) primary key,
userName char(10)
)
create table bookType(
bookTypeId int identity(1,1) primary key,
bookType char(10)
)
create table book1(
book1Id int identity(1,1) primary key,
book1Name char(10),
bookTypeId int,
CONSTRAINT FK_bookTypeId FOREIGN KEY(bookTypeId) REFERENCES bookType(bookTypeId)
)
create table borrowBook(
borrowBookId int identity(1,1) primary key,
userBookId int,
book1Id int,
CONSTRAINT FK_userBookId FOREIGN KEY(userBookId) REFERENCES userBook(userBookId),
CONSTRAINT FK_book1Id FOREIGN KEY(book1Id) REFERENCES book1(book1Id)
)
drop table userBook
--查询要用到的关键字
常用函数getdate（） avg（） count（）
select * from student where student.name is null;--查询是否为空或者is not null查询空值
select * from student where student.sno in (select sno from student);--in的用法表示在这个集合内的元素都符合
select * from student where name like 's%';--like的用法表示模糊查询
select * from student where name='sa' or name='ca';--or的用法和and的类似表示a或者b
select * from student where score between 10 and 20;--between and的用法表示在。。之间
--内连接连表查询
select * from book1 inner join bookType on book1.bookTypeId=bookType.bookTypeId
inner join borrowBook on book1.book1Id=borrowBook.book1Id 
inner join userBook on borrowBook.book1Id=userBook.userBookId--内连接查询语句


--创建规则
create rule userName_rule
AS @value='sa' or @value='sa1'--创建字符规则
EXEC sp_bindrule userName_rule,'userBook.userName'--绑定规则
drop rule userName_rule--删除规则
insert into userBook values('sa')
insert into userBook values('sa5')
EXEC sp_unbindrule 'userBook.userName'
create rule userName1_rule--创建模糊约束规则
AS @value like 's%a'
EXEC sp_bindrule userName1_rule,'userBook.userName'--绑定规则
drop rule userName1_rule--删除规则
insert into userBook values('sa')
insert into userBook values('sa1')
EXEC sp_unbindrule 'userBook.userName'--解绑规则
create default bookName_default AS '数据库概论1'--创建默认约束
exec sp_bindefault bookName_default,'book1.book1Name'--绑定默认约束
exec sp_unbindefault 'book1.book1Name'--解绑约束
drop default bookName_default--删除默认约束
insert into book1 values(1)--没有测出来慎用
select * from book1


--使用case显示结果并分类
select book1.book1Name,课程=  --用case查询显示结果
CASE--以case开始
WHEN book1.book1Id='1' THEN '必修'--必须包含when后跟判断语句then后跟需要显示的结果
WHEN book1.book1Id BETWEEN 1 AND 2 THEN '选修'
WHEN book1.book1Id='3' THEN '辅修'
END--以end结束
from book1


declare @value int--用户自定义局部变量必须标明类型，出现go时代表变量作用范围结束，（@@声明全局变量，一般不用有固定的函数）
--用户自定义函数的创建与使用


--用户自定义函数
create function book_avg(@booktypeId int) --返回每种类型书的平均价格
returns float --创建自定义标量函数变量的声明可有可无，必须返回，returns跟返回的值得类型
begin --标量函数必须以begin开始end结束，中间必须使用return（返回的条件的的执行）
return(select AVG(book1.book1price) from book1 where book1.bookTypeId=@booktypeId )
AS -创建函数都有AS
end
drop function book_avg --删除函数
select dbo.book_avg(2) as 平均价格 --使用标量函数


--创建内联表值函数，返回书籍的全部信息
create function books(@bookId int)
returns table --返回一个列表
AS
return(select * from book1 where book1.book1Id=@bookId)--没有begin，end，括号内是返回的属性的条件
drop function books
select * from dbo.books(1)--使用内联表函数


--创建标量函数返回多个值
create function books_stat(@booksid int)
returns @query table --返回一张表，必须定义一张临时表来接收参数
(
bookid int,
bookname char(10),
bookprice int
)
AS
begin
insert @query--将查询到的参数insert到新的表中，与标量函数一样有begin，end，return
select book1.book1Id,book1.book1Id,book1.book1price from book1 where book1.book1Id=@booksid
return
end
drop function books_stat
select * from books_stat(1)--使用多表值函数


--存储过程的创建与使用,还可以使用语句块不常用在此未测试
--存储过程接收输入参数并以输出参数的形式为调用过程或批处理返回多个值
--存储过程主要用于重复编译的命令而提高效率,一次编译重复使用都是(DLL)可以装载和执行的动态链接库
create procedure serach_book--普通的存储过程的创建必须存在procedure关键字
as select * from book1 inner join bookType on book1.bookTypeId=bookType.bookTypeId
drop procedure serach_book--删除存储过程
exec serach_book--使用存储过程,如果不是批处理的第一条语句,则用execute执行存储过程


create procedure Condition_serach--创建带条件的存储过程
AS select * from book1 inner join bookType on book1.bookTypeId=bookType.bookTypeId where bookType.bookType='计算机'
drop procedure Condition_serach2
execute Condition_serach--执行存储过程


alter procedure Condition_serach--修改查询过程必须含有with encryption关键字
with encryption
AS select * from book1 inner join bookType on book1.bookTypeId=bookType.bookTypeId where bookType.bookType='金融'
EXECUTE Condition_serach
--创建带输入参数的存储过程
create procedure parameter1 @value char(10)--传参的存储过程支持%等通配符可是结果测试不出
as select * from book1 inner join bookType on book1.bookTypeId=bookType.bookTypeId where bookType.bookType like @value
drop procedure parameter1
exec parameter1 '计算机'


--创建带有多个参数的存储过程
create procedure serachbooks @value1 int,@value2 char(10)
as select * from book1 inner join bookType on book1.bookTypeId=bookType.bookTypeId where bookType=@value2 and book1.book1price=@value1
drop procedure serachbooks
execute serachbooks @value1=20,@value2='计算机'--这种参数的值得指定方法没有顺序，别的方法未使用


--创建带有输出参数的存储过程
create procedure seracheR @value1 char(10),@value2 real output--输出的变量的类型必须是real
AS select @value2=book1price from book1 where book1Name=@value1
drop procedure seracheR--删除存储过程
declare @value real--局部变量只能在你定义的语句中使用,或者使用时必须选中否则可能不生效
execute seracheR 'c',@value output--必须声明一个变量来接收输出的real类型变量
select @value


--触发器的创建与使用
--触发器不能在临时表或者视图创建
--触发器只能在当前的数据库创建，一个触发器只能对应一张表


create trigger tr_userBook on userBook--创建普通触发器
for delete--for后面是操作的类型，入insert，delete，update，这是after触发器，代表执行操作后才触发
as-必须有as
declare @sum varchar(100)--如果出现的结果不对,可能就是你的字符串长度不够类似错误
select @sum=STR(@@ROWCOUNT)+'名学生被删除'
select @sum--代表局部变量的查询
return 
drop trigger tr_userBook
delete from userBook where userBook.userBookId>17


create trigger overbooks on book1--创建触发器并且使用if语句进行控制非法操作
for update
as 
if(select book1Count from book1 where book1Id=1)<3
begin
print '此书籍数量不足不许借出'
rollback transaction
end
else 
commit transaction 
drop trigger overbooks
update book1 set book1Count=book1Count-1 where book1Id=1 
select *  from book1
select * from userBook
insert into userBook values('tianyu')


--创建触发器实现多表同步
create trigger insert_after on userBook 
for insert 
as
insert into user1(userName)
select ins.userName from inserted ins
drop trigger insert_after
select * from user1