Oracle数据库基本sql语句

--查询当前系统日期时间
select sysdate from dual;
select systimestamp from dual;
--查询伪列
select rowid,emp.* from emp
select rownum,emp.* from emp
--创建表
create table score(
       cid int primary key,
       html number,
       js number,
       ajax number
)
create table java1(
       jid number primary key,--主键
       jname varchar2(20) not null,--非空约束
       phone varchar(12) unique,--唯一约束
       address varchar(30) default '山东淄博',--缺省(默认值)约束
       sex char(2) check(sex in ('男','女')),--检查约束
       cid int,
       foreign key(cid) references score(cid)
)
--创建用户
create user laoliu identified by 123456;
--赋予连接数据库获取资源的权限
grant  connect,resource to laoliu;
--赋予查询权限
grant select,update,delete on scott.emp to laoliu with grant option;
--撤销权限
revoke select,update on scott.emp from laoliu;
select * from scott.emp;
select * from scott.java1;
--复制表以及表数据
create table emp01 as
       select * from emp order by sal;
select * from emp01;
--复制表结构
create table emp02 as
       select * from emp where 1=2;
select * from emp02;
--插入别的表中的数据
insert into emp02(empno,ename,job)
       select empno,ename,job from emp
--内联接
select * from emp join dept on emp.deptno=dept.deptno;
select * from emp e,dept d where e.deptno=d.deptno;
--左外链接:内连接的结果+左表中不满足条件的数据，对应右表字段自动补空
select * from dept left join emp on emp.deptno=dept.deptno;
--右外链接：内连接的结果+右表中不满足条件的数据，对应左标字段自动补空
select * from emp right join dept on emp.deptno=dept.deptno;
--全外链接
select * from emp full join dept on emp.deptno=dept.deptno;
select * from java1
insert into java1 values(2,to_date('1995-06-24','yyyy-mm-dd'),'高','120','山东淄博','男',1);
insert into java1 values(3,to_date('1995-06-24','yyyy-mm-dd'),'吴','122','山东淄博','男',1);
--查询Java1男女更多少人
select sex,count(*) from java1227 group by sex;
--查询Java1中人数大于1的性别
select sex from java1 group by sex having count(*)>1;
--将另一个查询的结果作为此查询的表
select * from(select empno,ename,job from emp);
--分页
--第一页
select * from(select rownum as r,emp.* from emp) where r>=1 and r<=4;
--第二页
select * from(select rownum as r,emp.* from emp) where r>=5 and r<=8;