使用SQL语句操作表结构

--删除员工表
drop table db_user;


--删除角色表
drop table db_role;


--清空回收站（recycle bin）
purge recyclebin;


--创建角色信息表
create table db_role(
       roleId varchar2(8) not null primary key,
       roleName varchar2(10)
);


--创建员工信息表
create table db_user(
       id varchar2(8) not null primary key,
       name varchar2(10),
       age number(4),
       sex varchar2(4),
       phone varchar2(13),
       city varchar2(16),
       roleId varchar2(8),
       foreign key(roleId) references db_role(roleId)
);


--向角色表中插入数据
insert into db_role values('2013101','普通管理员'); 
insert into db_role values('2013102','部门经理'); 
insert into db_role values('2013103','顾问');
insert into db_role values('2013104','超级用户');


--提交
commit;


--向用户表插入数据
insert into db_user(id,name,age,sex,phone,city,roleId) values('20131001','王鹤',28,'男','1582342342','北京','2013101');                
insert into db_user(id,name,age,sex,phone,city,roleId) values('20131002','李崇',24,'男','1582342342','深圳','2013102');
insert into db_user(id,name,age,sex,phone,city,roleId) values('20131003','郭玉',26,'女','1582342342','上海','2013103');
insert into db_user(id,name,age,sex,phone,city,roleId) values('20131004','张赫',22,'男','1582342342','北京','2013101');
insert into db_user(id,name,age,sex,phone,city) values('20131005','李文婷',27,'女','1582342342','上海');
insert into db_user(id,name,age,sex,phone,city,roleId) values('20131006','李峰',23,'男','1582342342','北京','2013102');
insert into db_user(id,name,age,sex,phone,city,roleId) values('20131007','海涛',25,'男','1582342342','长沙','2013101');


commit;


--查询
select * from db_user where sex='男';


select id,name, age from db_user;


select * from db_user where age<24;


select * from db_user where city='北京';


select * from db_user where id='20131002';


select * from db_user where sex='男' and age<24;


select * from db_user where sex='男' or age<24;


--修改数据


update db_user set name='李易峰';
commit;


update db_user set name='李易小峰',age=26,phone='12345667' where id='20131006';
commit;
select * from db_user;


--删除
delete from db_user where sex='女';
select * from db_user;


delete  from db_user;
commit;
select * from db_user;