数据库操纵语言（更新 插入 删除）

数据库操纵语言（更新 插入 删除）

SQL> alter table a add age number(9);

Table altered.

删表

SQL> drop table a;

Table dropped.

建表

SQL> create table a(
  2  id number(10),
  3  name varchar2(20));

Table created.

建视图
SQL> create view is_a
  2  as
  3  select id,age
  4  from a
  5  where id=1;

View created.

更新数据

1）插入元组

SQL> insert into a values(2,'b',3);

1 row created.

2）插入子查询

修改数据

1）修改某一个元组的值

SQL> update a set age=16 where name='a';

1 row updated.

2）修改多个元组的值

SQL> update a set id=id+1;

4 rows updated.

删除数据

1)删除某一个元祖的值

SQL> delete from a where name='w';

1 row deleted.

2）删除多个元祖的值

SQL> delete from a;

3 rows deleted.

创建用户

SQL> create role l;

Role created.

a的权限赋予l

SQL> grant insert on a to l;

Grant succeeded.

SQL>  grant l to h;

Grant succeeded.

收回用户l的select权限

SQL> revoke select on a from l;

Revoke succeeded.