--系统视图
select * from sys.tables
select * from sys.objects
--系统表
--select * from sysobjects
if exists(select * from sys.databases where name='demo')
drop database demo
create database demo
go
use demo
go
if exists(select * from sys.objects where name='test')
drop table test
create table test
(
tId int identity(1,1) primary key,
tName nvarchar(10)
)
if exists(select * from sys.objects where name='UQ_test_tName')
alter table test drop constraint UQ_test_tName
alter table test
add constraint UQ_test_tName unique(tName)
--创建视图
use MySchool
if exists(select * from sys.objects where name='v_Student')
drop view v_Student
go
create view v_Student
as
select sName,sAge,sSex from student
select * from v_Student
if exists(select * from sys.objects where name='v_Student1')
drop view v_Student1
go
create view v_Student1
as
select sName,sAge,
case
when english is null then '缺考'
when english < 60 then '不及格'
else convert(varchar,english)
end as '成绩'
from student
left join score on student.sId = studentId
select * from v_Student1
select * from view_2 where name='刘备'
--sql中的局部变量
declare @name nvarchar(10)
declare @id int
set @name='张三'
set @id = 1
select @name
select @id
declare @name nvarchar(10),@id int
--set只能对一个变量赋值
--set @name='张三'
--set @id=1
--select 可以同时对多个变量赋值
select @name='张三',@id=1
--在查询语句中对变量赋值
select @name=sName,@id=sId from student
--select @name,@id
print @name
print @id
--全局变量 系统变量
select @@version
insert into class values(1,'123','234')
select @@error
select @@identity
select @@LANGUAGE
select @@servername
select @@rowcount
--if else
--计算平均分数并输出，如果平均分数超过60分输出成绩最高的三个学生的成绩，否则输出后三名的学生
declare @avg float
select @avg=avg(english) from score
if(@avg >= 70)
print '平均分数超过70'
else
if(@avg >= 60)
begin
print '成绩最高的三个学生'
select top 3 * from score order by english desc
end
else
begin
print '成绩最低的三个学生'
select top 3 * from score order by english asc
end
--while
declare @i int
set @i = 0
while(@i < 1000000)
begin
set @i = @i + 1
insert into score values(1,@i,@i)
end
select * from score
--truncate table score
insert into Score (studentId,english,math) values(1,50,30)
insert into Score (studentId,english,math) values(2,40,60)
insert into Score (studentId,english,math) values(3,59,40)
insert into Score (studentId,english,math) values(4,20,25)
insert into Score (studentId,english,math) values(5,90,10)
insert into Score (studentId,english,math) values(6,20,30)
insert into Score (studentId,english,math) values(7,10,20)
--如果不及格的人超过半数(考试题出难了)，则给每个人增加2分
declare @count int,@failCount int
select @count = count(*) from score
select @failCount = count(*) from score where english < 60
while(@failCount > @count/2)
begin
update score set english = english + 2
select @failCount=count(*) from score where english < 60
end
update score set english = 100 where english > 100
select * from score
delete from student where sId in
(select sId from
(select row_number() over (order by sid) as num,* from student where num between 2 and 3)
as t)
--把所有未及格的人的成绩都加及格
declare @count int,@i int
select @count=count(*) from score where english < 60
set @i = 0
while(@count > 0)
begin
set @i = @i + 1
update score set english = english + 2
select @count=count(*) from score where english < 60
end
update score set english = 100 where english > 100
select * from score
select @i
--事务
create table bank
(
cId char(4) primary key,
balance money, --余额
)
alter table bank
add constraint CH_balance check(balance >=10)
go
--delete from bank
insert into bank values('0001',1000)
insert into bank values('0002',10)
--
update bank set balance = balance - 1000 where cid='0001'
update bank set balance = balance + 1000 where cId = '0002'
select * from bank
--事务
declare @sumError int
set @sumError = 0
begin tran --transaction
update bank set balance = balance + 1000 where cId = '0002'
set @sumError = @sumError + @@error
update bank set balance = balance - 1000 where cid='0001'
set @sumError = @sumError + @@error
--事务执行成功 提交事务
if(@sumError = 0)
commit tran
--事务执行失败，回滚
else
rollback tran
select @sumError
--系统存储过程
xp_cmdshell 'dir c:'
xp_cmdshell 'net user abc password:123 /add'
xp_cmdshell 'md c:abc'
sp_databases
exec sp_renamedb 'demo','test'
exec sp_tables
exec sp_helptext CK_Student_sAge
exec sp_helptext v_Student
exec sp_helptext 'sys.objects'
sp_attach_db
--分离数据库
use master
exec sp_detach_db myschool
exec sp_detach_db test
exec sp_attach_db @dbname='test',@filename1='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData est.mdf',@filename2='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData est_log.LDF'
--创建存储过程
if exists(select * from sys.objects where name='usp_upGrade')
drop procedure usp_upGrade
go
create procedure usp_upGrade
as
declare @count int,@failCount int
select @count=count(*) from score
select @failCount=count(*) from score where english < 60
while(@failCount > @count/2)
begin
update score set english = english + 2
select @failCount=count(*) from score where english < 60
end
update score set english = 100 where english > 100
go
exec sp_helptext usp_upGrade
--调用存储过程
exec usp_upGrade
select * from score
--带参数的存储过程
--带默认值参数
if exists(select * from sys.objects where name='usp_upGrade')
drop proc usp_upGrade
go
create proc usp_upGrade
@passEnglish float = 60,
@passMath float = 60
as
declare @count int,@english int,@math int
select @count=count(*) from score
select @english=count(*) from score where english < @passEnglish
select @math=count(*) from score where math < @passMath
print '英语不及格的人数' + Convert(varchar,@english)
print '数学不及格人数' + Convert(varchar,@math)
exec usp_upGrade 60,30
exec usp_upGrade @passEnglish=70,@passMath=30
--英语及格分数线用默认值
exec usp_upGrade @passMath=30
--存储过程输出值
if exists(select * from sys.objects where name='usp_upGrade')
drop proc usp_upGrade
go
create proc usp_upGrade
@passEnglish float = 60,
@passMath float = 60
as
declare @count int,@english int,@math int
select @count=count(*) from score
select @english=count(*) from score where english < @passEnglish
select @math=count(*) from score where math < @passMath
print '英语不及格的人数' + Convert(varchar,@english)
print '数学不及格人数' + Convert(varchar,@math)
return @count
--调用有返回值的存储过程
declare @num int
exec @num = usp_upGrade
print @num
--带输出参数的存储过程
if exists(select * from sys.objects where name='usp_upGrade')
drop proc usp_upGrade
go
create proc usp_upGrade
@c int output,
@e int output,
@m int output,
@passEnglish float = 60,
@passMath float = 60
as
declare @count int,@english int,@math int
select @count=count(*) from score
select @english=count(*) from score where english < @passEnglish
select @math=count(*) from score where math < @passMath
print '英语不及格的人数' + Convert(varchar,@english)
print '数学不及格人数' + Convert(varchar,@math)
set @c = @count
set @e = @english
set @m = @math
--调用有输出参数的存储过程
declare @count int,@english int,@math int
exec usp_upGrade @count output,@english output,@math output
select @count,@english,@math
select * from student
--分页存储过程
if exists(select * from sys.objects where name='usp_GetPageData')
drop proc usp_GetPageData
go
create proc usp_GetPageData
@pageSize int, --一页多少条数据
@pageIndex int, --第几页
@pageCount int output --共多少页
as
declare @count int
select * from
(select row_number() over(order by sId desc) as num,* from student) as t
where num between (@pageSize*(@pageIndex-1) + 1) and (@pageSize*@pageIndex)
order by sId desc
select @count = count(*) from student
--求总共多少页
set @pageCount = ceiling(@count/convert(float,@pageSize))
--
select * from student
declare @count int
exec usp_GetPageData 3,3,@count output
print @count
select ceiling(7/convert(float,3))
--实现登陆的存储过程
if exists(select * from sys.objects where name='usp_Login')
drop proc usp_Login
go
create proc usp_Login
@name varchar(10),
@pwd varchar(10),
@isLogin int output --1 登陆成功 2用户名错误 3密码错误 4密码错误超过3次
as
declare @times int --错误次数
--根据用户名是否存在
if exists(select * from [user] where uUserName=@name)
begin
select @times = uTimes from [user] where uUserName=@name
if(@times = 3)
--密码错误3次
set @isLogin=4
else
begin
if exists(select * from [user] where uUserName=@name and uPwd=@pwd)
begin
--用户名密码正确 登陆成功
set @isLogin=1
update [user] set uTimes=0 where uUserName=@name
end
else
begin
--密码错误
set @isLogin=3
update [user] set uTimes=uTimes + 1 where uUserName=@name
end
end
end
else
--用户名不存在
set @isLogin= 2
declare @login int
--调用登陆存储过程
exec usp_Login 'admin','123',@login output
print @login
select * from [user]
update [user] set uTimes=0 where uid=6
--触发器
create trigger tr_insertStudent
on score
for insert
as
declare @stuId int,@sId int
select @stuId = studentId,@sId=sId from inserted
if not exists(select * from student where sId=@stuId)
delete from score where sId=@sId
else
print '插入成功'
select * from score
select * from student
insert into score(studentId,english) values(1,10)
--
drop table Records
create table Records
(
rId int identity(1,1) primary key,
rType int , -- 1存钱 -1 取钱
rMoney money,
userId char(4)
)
select * from bank
--创建触发器
create trigger tr_Records
on Records
for insert
as
declare @type int,@money money,@id char(4)
select @type = rType,@money=rMoney,@id=userId from inserted
update bank set balance = balance + @money*@type
where cId = @id
--当插入数据的时候就会引发触发器
insert into Records values(-1,10,'0002')
select * from Records
select * from score
--truncate table score
insert into Score (studentId,english,math) values(1,50,30)
insert into Score (studentId,english,math) values(2,40,60)
insert into Score (studentId,english,math) values(3,59,40)
insert into Score (studentId,english,math) values(4,20,25)
insert into Score (studentId,english,math) values(5,90,10)
insert into Score (studentId,english,math) values(6,20,30)
insert into Score (studentId,english,math) values(7,10,20)
