--use master(SysDatabases表在master中)
--建库
if exists( select * from sysdatabases where name='Test')
drop database Test
create database Test
on
(
name='User_data',
fileName='E:\DataBase\User_data.mdf',
size=3mb,
maxsize=10mb,
filegrowth=1mb
)
log on
(
name='User_log',
fileName='E:\DataBase\User_log.ldf',
size=3mb,
maxsize=10mb,
filegrowth=1mb
)
--切库
use Test
go
--建表(syobject表 保存着新建的表，约束)
if exists(select * from sysobjects where name='UserType')
drop table UserType
create table UserType
(
typeId int identity(1,1) primary key not null,
typeName varchar(50) not null
)
go
if exists(select * from sysobjects where name='User')
drop table [User]
create table [User]
(
uId int identity(1,1) primary key not null,
uLoginName varchar(20) ,
uPwd varchar(10),
uTypeId int ,
uSex char(2),
uAge int ,
)
go
--唯一约束
--删除约束
if exists(select * from Sysobjects where name='UQ_User_ULoginName')
alter table [user] drop constraint UQ_User_ULoginName
--添加约束
alter table [user]
add constraint UQ_User_ULoginName unique(uLoginName)
--删除约束
if exists(select * from sysobjects where name='UQ_Type_TypeName')
alter table [UserType] drop constraint UQ_Type_TypeName
--添加约束
alter table [UserType] add constraint UQ_Type_TypeName unique(TypeName)
go
--默认约束
--删除约束
if exists(select * from sysobjects where name='DF_User_uPwd')
alter table [user] drop constraint DF_User_uPwd
--添加约束
alter table [user]
add constraint DF_User_uPwd default('000000') for uPwd
--删除约束
if exists(select * from sysobjects where name='DF_User_Usex')
alter table [user] drop constraint DF_User_Usex
--添加约束
alter table [user]
add constraint DF_User_Usex default('男') for uSex
go
--检查约束
--删除约束
if exists(select * from sysobjects where name='CK_User_USex')
alter table [user] drop constraint CK_User_USex
--添加约束
alter table [user]
add constraint CK_User_USex check(uSex='男' or uSex='女')
--删除约束
if exists(select * from sysobjects where name='CK_User_UAge')
alter table [user] drop constraint CK_User_UAge
--添加约束
alter table [user]
add constraint CK_User_UAge check(Uage between 18 and 30)
go
--外键约束
--删除约束
if exists(select * from sysobjects where name ='FK_User_UserType')
alter table [user] drop constraint FK_User_UserType
--添加约束
alter table [user] add constraint FK_User_UserType foreign key(uTypeId) references UserType(typeId)
go
-- 添加数据
insert into UserType
select '管理员' union
select '经理' union
select '董事长'
insert into [user] values('amdim',default,1,default,20)
insert into [user] values('amdim1','123',2,'女',23)
insert into [user]
select 'amdim2','123',2,'女',22 union
select 'amdim3','123',1,'男',25
--删除，修改数据
select * from userType
select * from [user]
--delete from [user] where uid=4
--update [User] set uPwd='123',Usex='男'
--删除表内的信息不改变表结构
truncate table [User]
--删除表并破坏表结构（即整个表被删除）
--drop table [User]
--distinct滤重复
select distinct * from UserType
select distinct * from [User]
--聚合函数 max min avg count sum
select max(uage) as '最大年龄', min(uage) as '最小年龄',
avg(uage) as '平均年龄',sum(uage) as '总年龄',count(*) as '行数' from [user]
--or 或者 and 并且
select * from [User] where uSex='男' and (uTypeId= 1 or uTypeId=2)
--between ...and...
select * from [User] where uSex='男' and uAge between 18 and 20
--in和 not in的用法
select * from [user] where uTypeId in (1,2)
select * from [user] where uTypeId not in (1)
--like '%'
select * from [user] where uLoginName like 'a%'
--[]匹配符匹配[abc]中的的字符
select distinct * from [User]
select * from [user] where uLoginName like '[a][m][d][i][m][1-4]'
--_匹配符单个字符
select * from [user] where uLoginName like '[a][m][d][i][m]_'
--判断是否是null时 必须用null
select * from [user] where uTypeId is null
select * from [user] where uTypeId is not null
--order by(desc 降序)(asc 升序)(不能对text 和img 进行排序)
select * from [user] order by uTypeId asc
select * from [user] order by uid desc
--字符串函数
select left(uLoginName,1) from [User] --向左截取字符串
select right(uLoginName,1) from [User] --向右截取字符串
select upper('abc') --转换大写
select lower('aaBB') --转换小写
select substring('aabb',1,2) --截取字符串
select replace('abcd','cd','123') --字符串替换
--日期函数 季度quarter yy 年 mm月 day日 hour时 minute分 ss秒
select getDate()
select dateadd(yy,-2,getDate()) --日期添加
select datediff(yy,'1990-12-9 12:53:4',getDate())--日期向减（第三个参数是被减数）
select datepart(minute,'1990-12-9 12:53:4') --截取时间的yy年 mm月 day日 hour时 minute分 ss秒 weekday星期 quarter季度
--当前月的判断 要判断年
select * from biao
where month(getdate()) = month(ziduan) and year(getdate()) = year(ziduan)
--datediff
select * from biao where datediff(month,ziduan,getdate())=0
--的到日期的一部分
select datename(weekday,getdate())
--group by 注意where不能出现在group by后
--select语句后面的列，必须在聚合函数中或者出现在group by后面
--having中使用的列。必须出现在select中
select count(*) from [User] group by uTypeId having uTypeId=1
--类型转换
select '班级号：' + cast(9.88 as varchar(10))
select cast(9.88 as int)
select convert(int,9.88)
select round(9.88888,2)
--类型转换时候 只取日期部分
select convert(varchar(10),getdate(),21)
select convert(varchar(100),getdate(),23)
--isnull看是否为空如果是空就用一个值代替
select avg(isnull(uage,0)) from [user]
--cross join 交叉连接 笛卡尔积
select * from [user] cross join UserType
--inner join.... on... 内连接
select * from [user] inner join UserType on UTypeID=typeid
--left join..... on...左连接
select * from [user] left join UserType on UTypeID=typeid
--left join.....on.... 右连接
select * from [user] right join userType on UTypeId=typeid
--创建视图
create view view_user_uType
as
select * from [user] inner join UserType on UTypeId=typeid
select * from view_user_uType
--定义变量
--给变量赋值
--select 可以同时给多个变量赋值,在查询语句中对变量赋值
--set 只能对单个变量赋值
declare @name varchar(50),@number int
set @name ='2222'
set @number=12
print @name
print @number
select @name='2222333',@number=15
print @name
print @number
--系统定义的变量
select @@error
select @@identity
--if else 语法
if (@avg >= 90)
begin
end
else
begin
end
--while
while(条件)
begin
end
--事务 同生共死
--总错误
declare @sumError int
set @sumError = 0
begin transaction
update bank set balance=balance-800 where cid='0001'
set @sumError = @sumError + @@error
update bank set balance=balance + 800 where cid='0002'
set @sumError = @sumError + @@error
if(@sumError > 0)
begin
print '回滚'
rollback transaction
end
else
begin
print '提交'
commit transaction
end
--存储过程
create proc usp_upGrade
@pass float
as
select * from score where english > @pass
exec usp_upGrade 45(调用存储过程)
drop proc usp_upGrade1
create proc usp_upGrade1
@passEng float,@passMath float
as
select * from score where english>@passEng
union all
select * from score where math>@passMath
exec usp_upGrade1 60,60
--参数的默认值
create proc usp_upGrade2
@passEng float=60,@passMath float=60
as
select * from score where english>@passEng
union all
select * from score where math>@passMath
exec usp_upGrade2
exec usp_upGrade2 45
exec usp_upGrade2 @passMath=45
exec usp_upGrade2 @passEng=45,@passMath=45
--带输出参数的存储过程
alter proc usp_upGrade4
@passEng float,@cnt int output
as
begin
set @cnt = 0
declare @count int,@failEng int,@failMath int
select @count=count(*) from score
select @failEng=count(*) from score where english<@passEng
while(@failEng > @count/2)
begin
set @cnt=@cnt + 1
update score set english = english+2
select @failEng=count(*) from score where english<@passEng
end
update score set english =100 where english > 100
end
declare @c int
exec usp_upGrade4 45,@c output
select @c
--触发器 inserted deleted
create trigger tr_score
on score
for insert,update
as
begin
declare @sId int
--当前插入的学生的id
select @sId=studentId from inserted
--判断学号在学生表中是否存在
if exists(select * from student where sId=@sId)
print '插入成功'
else
delete from score where studentId=@sId
end
create trigger tr_class
on class
for delete
as
begin
delete from student where sClassId in
(select cId from deleted)
end
