oracle数据库

select systimestamp from dual;
--查询伪列
select rownum,emp.* from emp
select rowid,emp.*from emp
--建表
create table java1203(
       sname varchar2(20),
       ssex char(2),
       sbirthday date,
       sage int)
drop table java1203

select * from java1203

--约束建表
create table java1203(
       ssid int primary key,--主键约束
       sname varchar2(20) not null,--非空约束
       ssex char(3) check(ssex in ('男','女')),--检查约束
       sstatus int default(1),--默认值约束
       sbirthday date,
       idcard varchar2(20) unique--唯一约束       
       )
insert into java1203 values(1,'蔡志浩','男',2,to_date('2005-01-01','yyyy-MM-dd'),'111')
create table class1203(
       cid int primary key,
       ssid int,
       foreign key(ssid) references java1203(ssid)--外键约束
       )
--复制表结构(不包含数据)
create table ajava1203 as select * from java1203 where 1=2;
select * from ajava1203
--复制整张表
create table bjava1203 as select * from java1203
create table cjava1203 as select ssid,sname from java1203
select * from cjava1203
--插入其他表中的数据
create table emp03 as select * from emp where 1=2;
insert into emp03 select * from emp where sal>3000;
select * from emp03
--创建用户并获取权限
create user aa identified by 123456;--创建用户
grant connect,resource to aa;--授予权限

--全外连接
select * from emp
select * from dept
select ename,job,mgr ,sal,dname from emp full join dept on emp.deptno=dept.deptno
-- from 子查询
select dname from
(select * from dept where deptno>20)
where deptno>30
-- 分页查询
select rownum,ename,sal,job,comm from emp
where rownum>=1 and rownum<=4;
select ('员工编号为'||empno||'姓名为'||ename) from emp
--字符函数
select initcap(lower(ename)) from emp
select ename,decode(deptno,20,'产品部',30,'开发部')as 部门 from emp;
--计算年份差
select extract(year from sysdate) -
extract(year from to_date('1997-08-08','yyyy-MM-dd'))from dual
--转换函数
select to_char(0.123,'$0.9999') from dual;
SELECT TO_CHAR(sysdate,'YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS') FROM dual;　　
select to_number('100')+1 from dual;
--其他函数
select * from emp
select ename,(sal+nvl(comm,0))as 总工资 from emp;
--分析函数
--row_number()
select emp.*,row_number() over(order by sal desc)as num from emp
--rank()
select emp.*,rank() over(order by sal desc)as num from emp;
--dense_rank()
select emp.*,dense_rank() over(order by sal desc)as num from emp;
--创建同义词
CREATE  USER  test  IDENTIFIED  BY  test;
GRANT  CONNECT , CREATE  SYNONYM TO test;
GRANT  SELECT   ON   SCOTT.EMP  TO test;
GRANT  DELETE  ON   SCOTT.EMP TO test;
GRANT  UPDATE  ON  SCOTT.EMP TO test;
--创建同义词
create synonym staff for scott.emp;
select * from staff;
--创建公有同义词
CREATE   PUBLIC   SYNONYM  pub FOR  SCOTT.emp;
select * from pub
--创建序列
create sequence seq_java1203
Start with 1
increment by 1;
select * from java1203;
delete from java1203;
--利用序列实现自增 
insert into java1203 values(seq_java1203.nextval,'黑寡妇','女',1,sysdate,seq_java1203.curral)
--创建视图
create view emp_dept as select empno,ename,job,mgr,emp.deptno,dname,loc from emp join dept on emp.deptno=dept.deptno;
grant create view to scott;
select * from emp_dept
--创建索引
create table t_testseq
(
  id number,
  name varchar2(10)
);
create sequence seq_value
start with 1
increment by 1;

BEGIN
    FOR  v_temp  in  1..100000  LOOP
    INSERT  INTO  t_testseq
     values(seq_value.nextval,'abcde');
    END LOOP;
END;
--0.039
select * from t_testseq where id=8900;
create index test_index on t_testseq(id);
select * from t_testseq where id=8900
declare
     i number default 99;
 begin
   i:=i+1;
   dbms_output.put_line(i);
  end;
  call getarea2(5,6)