sql基础、主键、游标、规则约束、存储过程、触发器

一、基本查询

--分组
select count(*) from cmp group by cls 




-------------------------------
--表由表头(field,column,item)和记录(row,record)
--对表头的操作 增删改查
--create table drop select alter
--对记录 增删改查
--insert delete select update




--create database test
--create table t2(id int,cls varchar(10),na varchar(10),sex int,age int,phone int)
select * from t2


--修改表头
alter table t1 add oth varchar(30) --添加
alter table t1 drop column oth      --删除
--修改记录
insert into t2 values(1,'计算本1601','Jack',1,21,13284)
update t1  set 19=20 where na=李四


--查询
select from where order by (排序) /group by (分组)   
select * from t1 cross join t2                --两表连在一起


select * from t1

union --intersect,except (与，并，非)

select * from t2 

二、主键

---保证数据规范性,完整性
primary key 主键 唯一标识代表该记录
foreign key 外键  在另外表中是关键字
unique 唯一
default 默认
check  检查 check(性别='男' or 性别='女')
not null 非空


select *from pplt
create table pplt
(id varchar(18) primary key,
na varchar(20),
gn varchar(1) check(gn='f' or gn='h'),
st varchar(18) foreign key references pplt(id),
ag int check(ag>=20 and ag <=60),
nn varchar(10) default('汉族'),
pl varchar(10) not null --政治面貌
)
insert into pplt (id,gn,pl)values('411221199511150000','f','主席')

三、游标

--open cursor_name
--fetch into @_     取数据 
--close cursor_name
--deallocate  cursor_name    撤销
create table ji1614(first1 varchar(100))
bulk insert ji1614 from 'D:\123.txt 'with (fieldterminator=' ',rowterminator='\n')
select * from ji1614
alter table ji1614 
add  jna char(50)  
update ji1614 set jna=substring(jna,1,3)
drop table ji1614
--游标
select * from ji1614
declare @bj varchar(20),@jn varchar(10)
declare @i int,@ fs 
declare cji cursor for select jcls,jnam from ji1614
open cji                         
--while @@fetch_status=0  begin
if i<=9
fetch  next  from cji into @bj,@jn


--firsst last absolute relative prior


print @bj+@jn
--end 
close cji
deallocate cji


---------------------------
update UR set where current of ur 


create table  cmp(cls varchar(20),no varchar(20),name varchar(10),)
bulk insert cmp
from 'D:\345.txt'
with(fieldterminator=' ',rowterminator='\n')
select * from cmp


declare @nm varchar(20)
declare ur cursor for select name from cmp
open ur
fetch from ur into @nm
update cmp set name=name+'A' where current of ur 
close ur

四、规则和约束

--rule and constraint (规则和约束)


create rule rule_name as @sex='f' or @sex='m' --创建规则
exec sp_bindrule 'rule_name','classmates.sex' --绑定规则
exec sp_unbindrule'classmates.sex' --解除
drop rule rule_name --删除规则


select * from classmates
create table classmates(no varchar(20),name varchar(10),sex varchar(2),birth date)
insert into classmates (name,birth)values ('zhangsan',cast('3 20 2018'as date) )
print getdate() --插入时间先看系统时间是什么格式




将出生日期设定不小于现在,姓名不能以数字开头


create rule birth  as @bth>=getdate()
exec sp_bindrule 'birth','classmates.birth'



五、存储过程

--存储过程 procedure
--create drop
create  procedure pr
@nm varchar(20)
as
begin
select no,name from cmp where name=@nm
end




execute pr '杨一' --显示存储过程


-----------------------------------
--alter
--drop procedure pr1
create procedure pr1
@i int
as
begin 
declare @s int,@j int
set @s=0
set @j=1
while @j<=@i
begin
set @s=@s+@j
set @j=@j+1
end
select @s


end


execute pr1 2


六、触发器

as
begin


select '谁动了我的表'


end




select * from  cmp
insert into cmp values(' jsj',' 0001',' tom')
update cmp  set cls ='dianqi'  where no='0001'
delete from cmp where cls=' '






--------------------------
--create table oper(user_name varchar(10),user_time datetime,user_opertp varchar(20))
--drop table oper
--drop trigger cmptrg
--create 
alter trigger cmptrg 
on cmp
for insert,update  ,delete    
as
begin


if exists(Select 1 from inserted) and not exists( select 1 from deleted)
insert into oper values('yangcheng',getdate(),'insert')


if not exists(Select 1 from inserted) and not exists( select 1 from deleted)
insert into oper values('yangcheng',getdate(),'update')


if  not exists(Select 1 from inserted) and exists( select 1 from deleted)
insert into oper values('yangcheng',getdate(),'delete')


end


--select * from oper
insert into cmp values('a','1','2')
update cmp  set cls ='dianqi'  where no='0001'
delete from cmp where cls=' '