oracle SQL操作

#登陆数据库(三种方式)
sqlplus /nolog
sqlplus system/manager
sqlplus

#连接数据库(三种方式)
connect / as sysdba;
connect internal/oracle as sysdba;
conn sys/change_on_install as sysdba;

#启动数据库实例
startup;

#查看当前的所有数据库(两种方式)
select * from v$databases;
select name from v$databases;

#查看数据库结构字段
desc v$databases;

#进入test数据库
database test;

#查看数据库的所有实例
select * from v$instance;

#查询某个用户拥有的权限
select * from session_privs;

#切换用户
conn system/bjpowermode;

#使用dba角色登陆
conn sys/bjpowermode as sysdba;

#给用户scott授权
conn system/bjpowermode;
grant create view to scott;

#撤销授权
grant create view to scott;

#查看当前用户下的所有表
select table_name from user_tables;

#查看表结构
desc dept

#查看用户拥有的数据库对象
select object_name from user_objects;

#查看约束信息
select constraint_name from user_constraints;

#查看用户拥有的表
select * from tab
select table_name from user_tables;

#查看用户拥有的视图
select view_name from user_views;

#查看用户拥有的触发器
select trigger_name from user_triggers;#

#查看用户拥有的序列
select sequence_name from user_sequences;

#查看用户拥有的存储过程
select object_name from user_procedures;

#查看用户拥有的索引
select index_name from user_indexes;

#显示当前用户
show user;

#切换用户
conn system/bjpowernode

#以超级管理员身份登录
conn sys/bjpowernode as sysdba;

#查看所有的用户
select username from dba_users;

#查看用户拥有的权限
select * from session_privs;

#给用户加锁
alter user scott account lock;

#给用户解锁
alter user scott account unlock;

#修改用户密码(数字不能开头)
alter user scott identified by tiger123;

#新建用户
create user test1 identified by test123;

#删除用户及相关对象
drop user test1 cascade;

#给用户授权（多个采用逗号间隔）
grant create session, create table to test1;

#分配空间 users 给用户
create table t_test(id number(10), name varchar2(30))

#以上出现无法创建表，主要原因在于没有分配表空间，也就是我们新建地方。
alter user test1 default tablespace users;

#授权表空间给用户
grant UNLIMITED TABLESPACE to test1;

#一个完整的过程,创建用户、创建表空间、授权、建表
#创建用户
create user egov identified by egov;  
创建表空间
create tablespace egov datafile 'E:\oracle\product\10.2.0\oradata\bjpowern\egov.DBF' size 50m;
#将表空间分配给用户
alter user egov default tablespace egov;
#给用户授权
grant create session, create table, unlimited tablespace to egov;
#以 egov 登陆建立表,tt_test
create table tt_test(id number(10));

#导入和导出命令 imp、exp
#Exp
exp scott/tiger file=C:\EMP.DMF tables=e mp,dept,salgrade
#Imp
imp scott/tiger file=C:\EMP.DMF

#多字段查询
select empno,ename from emp;

#查询时可以带计算
select emppno,ename,sal*12 from emp;

#as 命名别名，as可以省略
select empno as 员工编号,ename as 员工姓名,sal*12 as 年薪 from emp;

#set linesize 设置一行的字符数，默认为80个字符
set linesize 200;

#l(list) 显示缓存区中的最后执行的内容
l;
list;

#重新运行缓存区中的语句
run ,/ ,r 

#save 可以将最后一次在缓存区中执行的语句保存到文件中
save @www.sql;

#get 可以将文件中的sql语句放到缓存区，然后用run，r，/进行执行
get /oracle/www.sql;
/;

#ed(edit) 可以采用记事本来编辑缓存区的内容
edit；

#在数据库中执行sql脚本
@/oracle/www.sql;

#条件查询，等于
select empno,ename,sal from emp where sal=5000;

#不等于
select empno,ename,sal from emp where sal<>5000;
select empno,ename,sal from emp where sal!=5000;

#在某某之间
select empno,ename,sal from emp where sal between 1000 and 3000;
select empno,ename,sal from emp where sal>=1000 and sal<=3000;

#查询为null字段时可以用“null”来天充
select * from emp where comm is null;
select * from emp where comm is not null;

#and并且，or或
select empno,ename,sal from emp where job='MANAGER' and sal>1000;
select empno,ename,sal from emp where job='MANAGER' and job='SALESMAN';

#优先级,想要先运算，尽量使用括号
select * from emp where sal>1000 and (deptno=20 or deptno=30);

#in 包含(忽略空值),not in (不忽略空值)
select * from emp where job in('MANAGER','SALESMAN');
select * from emp where job not in('MANAGER','SALESMAN');

#like 模糊匹配，M开头，M结尾，包含M，第二个字符为M
select * from emp where ename like 'M%';
select * from emp where ename like '%M';
select * from emp where ename like '%M%';
select * from emp where ename like '_M%';

#单一字段排序
select * from emp order by sal;
select * from emp order where sal>1000 by sal;
select * from emp order by sal asc;
select * from emp order by sal desc;

#多字段排序
select * from emp order by sal desc,ename desc;

#使用字段未知排序
select * from emp order by 6;

#lower 转换为小写
select lower(ename) from emp;

#upper 转换为大写
select * from emp where job=upper('manager');

#substr('被截取的字符串',从哪一位开始截取,截取的长度) 取子串
select * from emp where substr(ename,1,1)='M';

#length 取长度
select length(ename) from emp;

#trim 去空格,去首尾空格，不去中间
select * from emp where job=trim('MANAGER   ');

#to_date 将字符串转换成日期
select * from emp where HIREDATE='20-2 月 -81';
select * from emp where hiredate=to_date('1981-02-20 00:00:00','YYYY-MM-DD HH24:MI:SS');

#修改oracle默认日期
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

#to_char 将数字转换为字符串
select empno,ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp where hiredate>to_date('1981-02-20 00:00:00','YYYY-MM-DD HH24:MI:SS');
select empno,ename,to_char(sal,'$999,999') from emp;
select empno,ename,to_char(sal,'$999,999.00') from emp;

#to_number 将字符串转换成数字
select * from emp where sal>to_number('1500','999,999');

#nvl 将null转换成一个具体的值
select empno,ename,sal,comm,sal+nvl(comm,0) from emp;

#case 分支语句
select empno,ename,job,sal,(case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 end) as newsal from emp;

#decode 分支语句
select empno,ename,job,sal,decode(job,'MANAGER',sal*1.1,'SALESMAN',sal*1.5) as newsal from emp;

#round 四舍五入
select round(1234567.4567,2) from dual;

#count 取得记录数，忽略null，null也会获取,distinct 去重
select count(*) from emp;
select count(comm) from emp;
select count(distinct job) from emp;

#联合去重
select distinct job,deptno from emp;

#sum 求和
select sum(sal) from emp;
select sum(sal+nvl(comm,0)) from emp;

#avg 取平均
select avg(sal) from emp;

#max 取最大值
select max(sal) from emp;
select avg(to_char(hiredate,'yyyy-mm-dd')) from emp;

#min 取最小值
select min(sal) from emp;
select min(hiredate) from emp;

#组合聚合函数
select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;

#分组查询
select job,sum(sal) from emp group by job;
select job,deptno,sum(sal) from emp group by job,deptno;

#对分组后的数据进行过滤
select job,avg(sal) from emp group by job having avg(sal)>2000;

#连接查询
select a.ename,b.dname from emp a,dept b where a.deptno=b.deptno;
select a.ename,a.sal,b.grade from emp a,salgrade b where a.sal between b.losal and b.hisal;
select ename,dname from emp, dept where emp.deptno=dept.deptno;
select ename,dname from emp a, dept b where a.deptno=b.deptno;
select a.ename,b.ename from emp a, emp b where a.mgr=b.empno;

select ename,dname from emp a join dept b on a.deptno=b.deptno where sal>2000;
select ename,dname from emp a inner join dept b on a.deptno=b.deptno where sal >2000;
select ename, dname from emp a, dept b where a.deptno=b.deptno and sal>2000;

select a.ename,b.dname from emp a right join dept b on a.deptno=b.deptno;
select a.ename,b.dname from dept b left join emp a on a.deptno=b.deptno;
select a.ename,b.dname from dept b left outer join emp a on a.deptno=b.deptno;
select a.ename,b.dname from emp a right outer join dept b on a.deptno=b.deptno;
select e.ename,m.ename mname from emp e, emp m where m.empno(+) = e.mgr
select a.ename,b.deptno from emp a right outer join dept b on a.deptno=b.deptno;
select a.ename,b.deptno from dept b left outer join emp a on a.deptno=b.deptno;

select empno,ename from emp where empno in(select distinct mgr from emp);
select empno,ename from emp where empno not in(select distinct mgr from emp where mgr is not null);
select distinct mgr from emp where mgr is not null;
select empno,ename from emp where empno in(select distinct mgr from emp where mgr is not null)
select empno, ename, sal from emp where sal>(select avg(sal) from emp);

select deptno,avg(sal) avg_sal from emp group by deptno;
select a.deptno,a.avg_sal,b.grade from (select deptno, avg(sal) avg_sal from emp group by deptno) a, salgrade b where a.avg_sal between b.losal and b.hisal;
select e.empno, e.ename, e.deptno, (select dname from dept where deptno = e.deptno) as dname from emp e;

#union 合并集合
select * from emp where job='MANAGER'unionselect * from emp where job='SALESMAN';
select * from emp union select * from dept;

#minus 移出集合
select * from emp where deptno in(10, 20) and sal <=2000;
select * from emp where deptno in(10, 20) minus select * from emp where sal>2000;

#rownum 隐含字段，在select语句执行之后才有值
select rownum,a.* from emp a;
select * from emp where rownum <=5;
select rownum,empno,ename,sal from emp where rownum<=5 order by sal desc;
select empno,ename,sal from (select empno, ename, sal from emp order by sal desc) where rownum<=5；

#使用rownum进行分页
select empno, ename, sal
from 
(
    select rownum r, empno, ename, sal 
    from 
    (
        select empno, ename, sal from emp order by sal
    ) 
    where rownum <=2
)where r>0;

#普通数据插入(不建议，防止字段发生更改)
insert into emp values(999,'zhangshan','MANAGER',NULL,NULL,200,100,10)

#指定字段数据插入(建议)
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(8887,'zhangsan','MANAGER',null,sysdate,200,100,10);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(8888,'zhangsan','MANAGER',null,to_date('2001-01-01','yyyy-mm-dd'),200,100,10);

#表内容复制(只能复制内容，约束不能复制)
create table emp_bak as select * from emp;

#将查询的数据直接放入已经存在的表中
insert into emp_bak select * from emp where job='MANAGER';

#数据更改
update emp set sal=sal+sal*0.1 where job='MANAGER';

#数据删除
delete from emp where comm=300;
delete from emp where comm is null;

#设置事务的隔离级别
set transaction isolation level SERIALIZABLE;

#创建表
create table t_student(
    student_id number(10),
    student_name varchar2(30),
    sex char(2) default '男',
    birthday date default sysdate,
    email varchar2(30),
    classes_id number(3)
)

#向刚创建的表中插入数据
insert into t_student(student_id,student_name,email,classes_id) values(1111,'lisi','lisi@152.net',10);

#创建表并加入非空约束(not null)起名(student_name_not_null)
create table t_student(
    student_id number(10),
    student_name varchar2(30) constraint student_name_not_null not null,
    sex char(2) default '男',
    birthday date default sysdate,
    email varchar2(30),
    classes_id number(3)
)

#创建表并加入唯一约束(unique)并起名(email_unique)
create table t_student(
    student_id number(10),
    student_name varchar2(30),
    sex char(2) default '男',
    birthday date default sysdate,
    email varchar2(30) constraint email_unique unique,
    classes_id number(3)
)

#效果同上(加入的为表级约束)
create table t_student(
    student_id number(10),
    student_name varchar2(30),
    sex char(2) default '男',
    birthday date default sysdate,
    email varchar2(30),
    classes_id number(3),
    constraint email_unique unique(email)
)

#创建表并加入主键约束(primary key)
create table t_student(
    student_id number(10) primary key,
    student_name varchar2(30),
    sex char(2) default '男',
    birthday date default sysdate,
    email varchar2(30),
    classes_id number(3)
)
create table t_student(
    student_id number(10),
    student_name varchar2(30),
    sex char(2) default '男',
    birthday date default sysdate,
    email varchar2(30),
    classes_id number(3),
    constraint student_id_pk primary key(student_id)
)
create table t_student(
    student_id number(10),
    student_name varchar2(30),
    sex char(2) default '男',
    birthday date default sysdate,
    email varchar2(30),
    classes_id number(3),
    constraint student_id_name_pk primary key(student_id,student_name)
)

#创建两个表并设置外键(foreign key)
create table t_classes(
    classes_id number(3),
    classes_name varchar2(30),
    constraint t_classes_classes_id_pk primary key(classes_id)
)
create table t_student(
    student_id number(10),
    student_name varchar2(30),
    sex char(2) default '男',
    birthday date default sysdate,
    email varchar2(30),
    classes_id number(3) references t_classes(classes_id),
    constraint student_id_pk primary key(student_id)
)

#外键的删除(先删除子再删除父)
delete from t_classes;

#创建外键时使用表级约束
create table t_student(
    student_id number(10),
    student_name varchar2(30),
    sex char(2) default '男',
    birthday date default sysdate,
    email varchar2(30),
    classes_id number(3),
    constraint student_id_pk primary key(student_id),
    constraint student_fk_classes_id foreign key(classes_id) references t_classes(classes_id)
)

#自定义检查约束(check),不建议使用
create table t_student(
    student_id number(10),
    student_name varchar2(30),
    sex char(2) default '男',
    birthday date default sysdate,
    email varchar2(30),
    classes_id number(3) not null references t_classes(classes_id),
    constraint student_id_pk primary key(student_id),
    constraint student_check_sex check(sex in('男','女'))
)

create table t_student(
    student_id number(10),
    student_name varchar2(30) not null,
    sex char(2) not null, 
    birthday date default sysdate,
    email varchar2(30) unique,
    classes_id number(3),
    constraint student_id_pk primary key(student_id),
    constraint student_fk_classes_id foreign key(classes_id) references t_classes(classes_id),
    constraint student_check_sex check(sex in('男','女'))
)

#向表结构里添加字段
alter table t_student add(contact_tel varchar2(40));

#向表结构里修改字段
alter table t_student modify(student_name varchar2(50));

#向表结构里删除字段
alter table t_student drop(contact_tel);

#删除约束
alter table t_student drop constraint STUDENT_FK_CLASSES_ID;

#添加约束
alter table t_student add constraint t_classes_fk_classes_id foreign key(classes_id) references t_classes(classes_id);

#修改约束
alter table t_student modify(student_name varchar2(50) null);

#删除表
drop table t_classes;

#建立索引
create index t_student_birthday on t_student(birthday);

#删除索引
drop index T_STUDENT_BIRTHDAY;

#创建视图
create view v_dept_avg_sal as select a.deptno,a.avg_sal,b.grade from (select deptno,avg(sal) avg_sal from emp group by deptno) a,salgrade b where a.avg_sal between b.losal and b.hisal;

#删除视图
drop view V_DEPT_AVG_SAL;

#创建序列
create sequence seq_student_id start with 1 increment by 1;

#创建完整的序列
create sequence my_seq_01
start with 100
minvalue 100
maxvalue 1000
increment by 10
cycle  no cycle
cache 10;

#使用序列，向t_student中插入数据(发生错误时会出现断号)
insert into t_student(student_id,student_name,sex,email,classes_id) values(SEQ_STUDENT_ID.nextval,'','女','zhangsan442@163.net',10);

#删除序列
drop sequence SEQ_STUDENT_ID;

#创建存储过程
create or replace procedure proc_test(in_var number,out_var out sys_refcursor)
as
begin
    open out_var for select * from emp where deptno=in_var;
end;

#执行存储过程
var ret refcursor
exec proc_test(20,:ret)
print :ret

#触发器(特殊的存储过程),先建t_log表，建sequence，建触发器，向emp加入数据
create table t_log (
    log_id number(10) primary key,
    log_time date
) 

create sequence seq_log_id start with 1 increment by 1;

create or replace trigger tri_test 
after insert on emp 
begin
    insert into t_log(log_id, log_time) values(seq_log_id.nextval, sysdate);
end; 

insert into emp(empno, deptno) values(7777, 10);

#游标
create or replace procedure proc_sal
is
    cursor c is 
        select * from emp for update;
begin
    for v_emp in c loop
        if (v_emp.job = 'MANAGER') then
            update emp set sal = sal + sal*0.2 where current of c;
        elsif (v_emp.job = 'SALESMAN') then 
            update emp set sal = sal + sal*0.1 where current of c;
        else
            update emp set sal = sal + sal*0.05 where current of c;
        end if;
end loop; 
commit;
end;

#执行游标
exec proc_sal;