总结SQL常用语句

1. 表

1. 建表

   	create table student
   	(	sname varchar(30) not null, #属性名称+属性类型（长度）+完整性约束
   		sno varchar(20) not null,
   		birthday date not null,
   		syear int check(syear between 1 and 100) not null,
   		sex char(1) check ( sex in ('F','M')) not null,
   		city varchar(100) check ( city in ('上海','北京')),
   		primary key(sno),  #定义主键，如果主键是两个及以上属性的组合：primary key(sno,sname)             
   		foreign key(dno) references department(dno)   #定义外键
   	);
   

2. 向表中插入数据

insert into student(sname,sno,birthday,dno,syear,sex,city) #这个括号内的属性顺序要和下面插入数据的顺序一致
values("小明","201826010326","2020-01-12 12:00:00","A1020",20,"F","上海");

3. 修改表数据

update student set sname="小明明"
where sno="201826010326";

4. 修改表 – 向表中添加一列：tel

alter table student add tel varchar(11);

5. 删除表一行数据

delete from student
where sno="201826010326";

6. 删除表

drop table student;

7. 查询表中数据

select sno,sname
from student
where dno like 'A%'   #dno的格式为第一位是A
order by sno desc;    #将结果按照sno排序

2. 权限

2.1 角色和用户

1. 创建用户

create user Tom identified by "201826010326";  #创建用户一定要有identified（用户的密码）

2. 创建角色

create role 'group_leader';

3. 删除用户

drop user Tom;

4. 删除角色

drop role 'group_leader';

2.2 授予权限与收回权限

1. 将表student的所有权限授予用户Tom

grant all privileges
on student
to Tom;

2. 将表的查询权限、删除数据权限和对属性sname的更改权限授予用户Tom

grant select,delete,update(sname)
on student
to Tom;

3. 将表student的查询权限授予全部角色，并允许角色将得到的权限授予其他用户或角色

grant select
on student 
to public with grant option;

4. 将角色group_leader授予用户Tom

grant 'group_leader'
to Tom;

5. 收回权限
   将上述4个授权语句中的grant替换为revoke，将to替换为from

revoke select
on student
from Tom;

3. 视图

1. 创建视图

create view view_name ( vsname, vson ) as
select sname,sno
from student;

2. 删除视图

drop view view_name;

4. 索引

1. 创建索引

create index sno_index on student(sno);

create index tel_syear_index
on student(tel desc,syear);

2. 删除索引

drop index sno_index;

3. 修改索引

alter index sno_index rename to sno_i;