--分组
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=' '