#数据库
create database 数据库;
drop database 数据库;

#表
create table table1 (
    学号 char(4) primary key,
    姓名 char(20) not null
)

#索引
create unique index idx on S(Sno DESC);#降序排列

#视图
create view view_name as
select * from s where city = 'New York';
drop view view_name;

#角色
create role role_name;
grant all privileges on S to role_name with grant option;
drop role role_name;

#索引：
create [unique|cluster(聚集)]index Index_name on S(Sno);
drop index index_name;

#基本查询
select Sno from S;

#带别名
select Sno 学号 , Grade 成绩 , joinGroup 加入社团 from S;

#带条件
select Sno from S where Sage > 18;
select Sno from S where Sage between 18 and 20;
select Sno from S where name not in ('Yukinoshita','Yui');
select Sno from S where name like 'Yukinoshita%';
select Sno from S where joinGroup is not null;

#带排序
select Sno from S order by Grade DESC;
select top 10 Sno from S order by Grade DESC;#选择前10名的学生。

#聚集
select avg(Grade) from S;
select count(*) from S;
select count(distinct joinGroup) from S;#相同内容只计数一次
select Sno from S group by Sno having count(distinct chooseClass) > 3;

#连接
select S.* , SC.* from S,SC where S.Cno = SC.Cno;
select S.Sno , S.name , S.city , SPJ.qty from s , SPJ 
where S.Sno = SPJ.Sno and SPJ.Jno = 'J001' and SPJ.qty >300;

#嵌套
select Sname from S where Sno in (select Sno from SC where Cno = '1001');

#插入
insert into S values('114514','李田所','C','回家部','24');
select * into S from 下北泽高 where Sname like '李田所';#把信息复制到大学

#修改
update S set Grade = 'B' where Sno = 114514;

#删除
delete from S where Sno = 114514;

#批量
update S set Sage = Sage +1 ;

#带子查询
update SPJ set QTY = QTY - 50 where JNO in( select JNO from J 
                                           where City = '天津');
  
#alter
alter table tableName drop column columnName;#删除列
alter table tableName add column Sbirth char(20);#添加列
alter table tableName alter column columnname datatype# 改变数据类型

#drop
drop table user index role ......;#drop一般删除数据结构或对象（包括属性列）		，
#而delete则倾向于删除对象中的元组。

#创建
create view S_BJ_VIEW as 
select * from S where city = '北京' with check option;

#删除
drop view ViewName;

#查询
select Sno,Sname from S_BJ_VIEW where STAT > 'B';
select * from S_AVGQTY where QAVG >=300;

#视图消解法：
#定义的视图：
create view S_AVGQTY(SNO,PNO,QAVG)
as select SNO,PNO,AVG(QTY) from SPJ group by SNO,PNO;
#查询语句：select * from S_AVGQTY where QAVG >=300;
select * from S_AVGQTY where QAVG >=300;
#转换后：
select SNO,PNO,AVG(QTY) from SPJ group by SNO,PNO having AVG(QTY)>=300;

#更新数据：
insert into S_BJ_VIEW value('S7','北京114514电子厂','B','北京');

#删除：
delete from S_BJ_VIEW where SNAME = '北京114514电子厂';

#创建用户
create user userName for login loginName with default_schema = schName;
create login loginName with password = ..., default_database = DB_NAME;

#创建角色
create role RoleName; 
grant RoleName to UserName with admin option;

#权限管理
grant all privileges on S to Rolename/Username; #with grant option:可以传递权限
revoke all privileges on S from Rolename/Username;
deny update on S to Rolename/Username;#拒绝这一权限，使其加入其他有update权限组后仍然不能执行该操作。

#主码定义：
create table s(
    Sno char(4) primary key;
    ......
)

create table s(
    ......
    primary key (Sno);
)
create table s (
	......
    primary key(Sno,Pno,Jno);
)

#参照完整性：
create table s(
	......
    foreign key (SNO) references s(SNO);
)

#其他完整性：
not null #非空
unique # 唯一
default 'defaultValue' #默认值

#check约束
create table s(
	......
    STAT Char(2) check(STAT in ('A','B','C')),
    ......
)

#constraint命令
create table s (
	......
    Sname char(20) constraint c1 not null # 此时c1这一完整性约束(constraint)的值为not null
)
alter table s drop constraint c1;
alter table s add constraint c2 check(......);

create table ...(
    ......
    Sname char(20) primary key;#在列级
    
    ......
    primary key (Sname);#在表级
    
    primary key (Sname,Sno);#在表级，多列

)

create table S2(
    ......
    foreign key (Sno) references S(Sno)#表级定义参照完整性
    on delete cascade on update cascade,#级联删除、更新
)

create table R1(
    Sno char(20) not null,#非空
    ...... unique,#列值唯一
    ......check( ......in(......集合))
    
    check (Ssex = 'female'or Sname not like 'Ms.%');
    
    
    #constraint
    Sno char(20) constraint C1 check(......);#形成一个名为C1的约束条件
    
    ......
    constraint C2 check(......)#在表级定义约束条件
)

alter table R1 drop constraint C1;#删除约束条件
alter table R1 create constraint c3 check(......);#创建约束条件，表级。

create assertion assertion_name check (......);

create assertion A1 check(60>=select count(*) from SC where C = 'Cource1');
create assertion A2 check(60>=all(select count(*) from SC group by ...));

create trigger trigger_name before/after update on SC
referencing new/old row as variable#触发器内设变量：原值和新值
for each row / statement#每行触发还是每条语句触发
when(......)#触发条件，比如new.grade>=old.grade
insert into Table_name(......) values (......)

drop trigger trigger_name;#删除（删除对象用drop）