create table s ##建表
(
sno char(5) primary key,
sn varchar(8) not null,
sex char(2) not null check (sex in ('f','m')),
age int not null check (age>0),
dept varchar(20),
constraint sn_u unique(sn),
)
create table sc
(
sno char(5) not null constraint s_f foreign key references s(sno),
cno char(5) not null,
score numeric(3),
constraint s_c_f primary key(sno,cno),
constraint c_f foreign key(cno) references c(cno)
)
drap table s; ##删除表
alter table s add scome datatime; ##增加表的一个字段
alter table s drop scome; ##删除表的一个字段
alter table s alter column age smallint;##修改表的一个字段
alter table sc nocheck constraint c_f; ##删除约束
create clustered index s_sno on s(sn); ##建聚集索引
create unique index s_sno on s(sno);##建唯一索引
drop index s.s_sno; ##删除索引
insert into sc(sno,cno) values('s7','c1'); ##插入一条记录
delete from s where sno='s7'; ##删除一条记录
delete from s; ##删除所有记录
update s set age=22 where sno='s3'; ##更新一条记录
select * from s where age<20 order by age desc; ## 普通查询+排序
select cno,count(sno) from sc group by cno having count(*)>=3;
##分组查询 sum,avg,max,min(distinct)
查询条件:
=,>,<,>=,<=,!=,<>,!>,!<
between and,not between and
in,not in;like,not like
is null,is not null;and,or,not
通配符:
% 多个字符
_ 1个字符
[a-f] a-f 内1个字符
[^a-f] a-f 外1个字符
create view is_s
as select sno,sn,age from s where dept='info';##建立视图
drop view is_s ; ##删除视图
update is_s set sn='liuceng' where sno='s2'; ##更新视图
select * from is_s where age<20; ##使用视图
grant create table to u1; ##授权系统权限
revoke create table from u1; ##收回建表权限
grant select/insert/delete/update on s to u1; ##授权角色权限
revoke select on s from u1; ##收回角色权限
(
sno char(5) primary key,
sn varchar(8) not null,
sex char(2) not null check (sex in ('f','m')),
age int not null check (age>0),
dept varchar(20),
constraint sn_u unique(sn),
)
create table sc
(
sno char(5) not null constraint s_f foreign key references s(sno),
cno char(5) not null,
score numeric(3),
constraint s_c_f primary key(sno,cno),
constraint c_f foreign key(cno) references c(cno)
)
drap table s; ##删除表
alter table s add scome datatime; ##增加表的一个字段
alter table s drop scome; ##删除表的一个字段
alter table s alter column age smallint;##修改表的一个字段
alter table sc nocheck constraint c_f; ##删除约束
create clustered index s_sno on s(sn); ##建聚集索引
create unique index s_sno on s(sno);##建唯一索引
drop index s.s_sno; ##删除索引
insert into sc(sno,cno) values('s7','c1'); ##插入一条记录
delete from s where sno='s7'; ##删除一条记录
delete from s; ##删除所有记录
update s set age=22 where sno='s3'; ##更新一条记录
select * from s where age<20 order by age desc; ## 普通查询+排序
select cno,count(sno) from sc group by cno having count(*)>=3;
##分组查询 sum,avg,max,min(distinct)
查询条件:
=,>,<,>=,<=,!=,<>,!>,!<
between and,not between and
in,not in;like,not like
is null,is not null;and,or,not
通配符:
% 多个字符
_ 1个字符
[a-f] a-f 内1个字符
[^a-f] a-f 外1个字符
create view is_s
as select sno,sn,age from s where dept='info';##建立视图
drop view is_s ; ##删除视图
update is_s set sn='liuceng' where sno='s2'; ##更新视图
select * from is_s where age<20; ##使用视图
grant create table to u1; ##授权系统权限
revoke create table from u1; ##收回建表权限
grant select/insert/delete/update on s to u1; ##授权角色权限
revoke select on s from u1; ##收回角色权限