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),
foreign key(dno) references department(dno)
);
- 向表中插入数据
insert into student(sname,sno,birthday,dno,syear,sex,city)
values("小明","201826010326","2020-01-12 12:00:00","A1020",20,"F","上海");
- 修改表数据
update student set sname="小明明"
where sno="201826010326";
- 修改表 – 向表中添加一列:tel
alter table student add tel varchar(11);
- 删除表一行数据
delete from student
where sno="201826010326";
- 删除表
drop table student;
- 查询表中数据
select sno,sname
from student
where dno like 'A%'
order by sno desc;
2. 权限
2.1 角色和用户
- 创建用户
create user Tom identified by "201826010326";
- 创建角色
create role 'group_leader';
- 删除用户
drop user Tom;
- 删除角色
drop role 'group_leader';
2.2 授予权限与收回权限
- 将表student的所有权限授予用户Tom
grant all privileges
on student
to Tom;
- 将表的查询权限、删除数据权限和对属性sname的更改权限授予用户Tom
grant select,delete,update(sname)
on student
to Tom;
- 将表student的查询权限授予全部角色,并允许角色将得到的权限授予其他用户或角色
grant select
on student
to public with grant option;
- 将角色group_leader授予用户Tom
grant 'group_leader'
to Tom;
- 收回权限
将上述4个授权语句中的grant替换为revoke,将to替换为from
revoke select
on student
from Tom;
3. 视图
- 创建视图
create view view_name ( vsname, vson ) as
select sname,sno
from student;
- 删除视图
drop view view_name;
4. 索引
- 创建索引
create index sno_index on student(sno);
create index tel_syear_index
on student(tel desc,syear);
- 删除索引
drop index sno_index;
- 修改索引
alter index sno_index rename to sno_i;