create user lsw identified by lsw123;
--分配权限或角色(需要 上级用户授权)
grant connect ,resource to lsw;
--取消权限或角色(需要上级用户解除)
revoke connect ,resource from lsw;
--创建表
create table student(
sno int,
sname varchar2(20),
sex char(2),
age number,
enterDate date,
clazz varchar2(20),
email varchar2(50)
)
--增加一列
alter table student add adds varchar2(200);
--删除一列
alter table student drop column adds;
--修改一列
alter table student modify adds varchar2(300);
--删除表
drop table student ;
insert into student values (1,'张三','男',30,sysdate,'1','1@qq.com');
select * from student;
--创建一个序列
create sequence seq_student;
--获取一个序列的下一个值
select seq_student.nextval from dual;
--获取一个序列的当前值
select seq_student.currval from dual;
--插入一行数据
insert into student values (seq_student.nextval,'张三','男',30,sysdate,1,'1@qq.com');
--创建一个索引
create index idx_student_sname on student(sname);
--删除索引
drop index idx_student_sname
-- 创建一个视图
create view vi_student
as select * from student;
-- 查询一个视图
select * from vi_student ;
-- 修改视图
create or replace view vi_student
as select sname,sex,email from student;
-- 删除视图
drop view vi_student;
-- 创建一个多表视图
create view vi_student_clazz
as
select s.*,c.chead,c.cname from student s left join clazz c
on s.cno =c.cno
select * from vi_student_clazz
select * from clazz;
事务
update clazz set cname='1班' where cname='sxt1班';
commit;
update clazz set cname='sxt2班' where cname='2班';
rollback;
-- rowid 系统自定义行号
select rowid,clazz.* from clazz
-- rownum 查询用的伪列,行号
select rownum,clazz.* from clazz;
-- 查询员工表中前三行数据
select * from emp where rownum<=3;
-- 查询员工表中第三行到第五行的数据
select * from emp where rownum<=5;
select sal from emp
order by sal desc
-- top-n
select rownum,t.* from (select sal from emp order by sal desc) t
where rownum <=3;
-- oracle 分页查询
select * from(select rownum r,t.* from (select sal from emp order by sal desc) t
where rownum <=15) a
where a.r>=11