Oracle：批量操作、视图、序列、简单SQL程序
/*
以下代码是对emp表进行显示宽度设置
*/
col empno for 9999;
col ename for a10;
col job for a10;
col mgr for 9999;
col hiredate for a12;
col sal for 999999;
col comm for 9999;
col deptno for 99;
col tname for a12;
set pagesize 50;
--创建新表xxx_emp，复制emp表中的数据，同时复制emp表的所有数据
create table xxx_emp
as
select * from emp;
//------------------------------------------------------------------------------------------------------
向emp表中插入一条记录（按表结构中列的默认顺序依次列出各个列的值）
insert into emp
values(4455,'JACK','IT',7788,sysdate,5000,100,40);
向emp表中插入一条记录（按指定列的顺序列出各个列的值）
insert into emp(ename,empno,job,mgr,hiredate,sal,comm,deptno)
values('MARRY',5566,'IT',7788,sysdate,5000,100,40);
向emp表中插入NULL值（采用显示插入NULL值），前提是允许该字段为NULL
insert into emp(ename,empno,job,mgr,hiredate,sal,comm,deptno)
values('SISI',6677,'IT',7788,sysdate,5200,NULL,40);
向emp表中插入NULL值 (采用隐式插入NULL值）,前提是允许该字段为NULL
insert into emp(ename,empno,job,mgr,hiredate,sal,deptno)
values('SOSO',8899,'IT',7788,sysdate,5200,40);
使用&占位符，动态输入值，&可以运用在任何一个DML语句中，在values子句中使用
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(&empno,'&ename','&job',&mgr,'&hiredate',&sal,&comm,&deptno);
使用&占位符，动态输入值，&可以运用在任何一个DML语句中，在from子句中使用
select * from &table_name;
使用&占位符，动态输入值，&可以运用在任何一个DML语句中，在select子句中使用
select empno,ename,&col_name from &table_name;
将xxx_emp表中所有20号记录的员工，复制到emp表中，相当于批量插入，前提是emp表要存在
insert into emp
select *
from xxx_emp
where deptno=20;
将'SMITH'的工资增加20%
update emp
set sal=sal*1.2
where ename = upper('smith');
将'SMITH'的工资设置为20号部门的平均工资
步一：查询20号部门的平均工资
select deptno,avg(sal)
from emp
group by deptno;
步二：将SMITH的工资设置为2191
update emp
set sal=2191
where ename = 'SMITH';
子查询：
update emp
set sal = (
select avg(sal)
from emp
group by deptno
)
where ename = 'SMITH';
删除无佣金的员工
delete from emp
where comm is null;
删除工资比所有部门平均工资小的员工
步一：查询部门的平均工资
select min(avg(sal)) from emp group by deptno;
步二：删除工资比2191低的员工
delete from emp where sal<2191;
子查询：
delete
from emp
where sal<(
select min(avg(sal))
from emp
group by deptno
);
将emp表丢入回收站
查询回收站【show recyclebin;】
清空回收站【purge recyclebin;】
彻底删除emp表（使用关键字purge）
依据xxx_emp表结构，创建emp表的结构，但不会插入数据
create table emp
as
select *
from xxx_emp
where 1=2;
向emp表，批量插入来自xxx_emp表中部门号为20的员工信息
insert into emp(empno,ename,job,sal)
select empno,ename,job,sal
from xxx_emp
where deptno=20;
依据xxx_emp表，只创建emp表，不复制数据，且emp表只包括empno,ename字段
create table emp(empno,ename)
as
select empno,ename
from xxx_emp
where 1<>1;
向emp表（只含有empno和ename字段），批量插入xxx_emp表中部门号为20的员工信息
insert into emp(empno,ename)
select empno,ename
from xxx_emp
where deptno=20;
第一天任务DML语句（事务开始）
insert into emp(empno,ename,sal) values(7777,'JACK',5000);
设置回滚点
savepoint day01;
第二天任务DML语句
insert into emp(empno,ename,sal) values(8888,'MARRY',4500);
设置回滚点
savepoint day02;
第三天任务
delete from emp where empno=8888;
回滚到day02
rollback to savepoint day02;
回滚到day01
rollback to savepoint day01;
全部回滚
rollback;
提交事务
commit;
Oracle支持的2种事务隔离级别
1）read committed（默认）
2）serializable
MySQL支持的4种事务隔离级别
1)repeatable（默认）
2)read commit
3)read uncommited
4)serializable
//------------------------------------------------------------------------------------------------------
从scott用户空间导航到sysdba用户空间
conn / as sysdba;
查询当用户
show user;
以sysdba身份解锁hr帐户
alter user hr account unlock;
以sysdba身份设置hr帐户的密码为123456
alter user hr identified by 123456;
从scott用户空间导航到hr用户空间
conn hr/123456;
查询当前用户
show user;
在scott用户空间下，查询hr用户空间下的employees表的first_name和last_name字段
在默认情况下，用户只能查询自已空间内的表
select first_name,last_name,salary from hr.employees;
使用列默认值，创建用户表
create table student(
id number(4),
name varchar2(6),
salary number(6,2) default 5000
);
insert into student(id,name,salary)
values(1234,'abcdef',5555.55);
insert into student(id,name,salary)
values(1234,'哈哈',5555.55);
insert into student(id,name,salary)
values(1,'哈',5555.555);
以sysdba身份，授权scott用户create table权限【grant create table to scott;】
grant create table to scott;
以sysdba身份，回收scott用户create table权限【revoke create table from scott;】
为emp表增加image列
alter table emp
add image blob;
修改ename列的长度为30
alter table emp
modify ename varchar2(30);
删除image列
alter table emp
drop column image;
重名列名ename为username
alter table emp
rename column ename to username;
将emp表重命名employees
rename emp to employees;
创建表customers(先)和orders(再)，使用primary key/not null/unique/foreign key约束
create table customers(
id number(1) primary key,
name varchar2(6) not null,
tel varchar2(11) unique
);
create table orders(
id number(1) primary key,
orderno varchar2(8) not null,
price number(3) not null,
c_id number(1),
constraint c_id_FK foreign key(c_id) references customers(id) on delete set null
);
insert into customers values(1,'JACK','13912341234');
insert into orders values(1,'order100',100,1);
insert into orders values(2,'order200',200,1);
constraint c_id_FK foreign key(c_id) references customers(id) on delete cascade
delete from customers where name = 'JACK';
cascade="delete"
constraint c_id_FK foreign key(c_id) references customers(id) on delete set null，前提产c_id字段可以为NULL
delete from customers where name = 'JACK';
无cascade="delete"属性
项目中：当与hibernate结合时，级联应该由hibernate去完成，而不是由oracle本身完成
创建表students，使用check约束
create table students(
id number(1) primary key,
name varchar2(6) not null unique,
gender varchar2(3) check( gender in ('男','女') ),
salary number(4) check( salary >=4000 and salary <=8000 )
);
insert into students values(1,'XX','女',3999+1);
//------------------------------------------------------------------------------------------------------
基于emp表所有列，创建视图emp_view_1，默认当前用户无权利创建视图
create view emp_view_1
as
select * from emp;
切换到管理员sysdba用户空间
conn / as sysdba;
在sysdba下，授权scott用户create view权限
grant create view to scott;
在sysdba下，回收scott用户create view权限
revoke create view from scott;
切换到普通用户scott
conn scott/123456;
基于emp表指定列，创建视图emp_view_2，该视图包含编号/姓名/工资/年薪/年收入（子查询中使用列别名）
create view emp_view_2
as
select empno "编号",ename "姓名",sal "薪水",sal*12 "年薪",sal*12+NVL(comm,0) "年收入"
from emp;
基于emp表指定列，创建视图emp_view_3(a,b,c,d,e)，包含编号/姓名/工资/年薪/年收入（视图中使用列名）
create view emp_view_3(a,b,c,d,e)
as
select empno "编号",ename "姓名",sal "薪水",sal*12 "年薪",sal*12+NVL(comm,0) "年收入"
from emp;
查询emp_view_3创建视图的结构
desc emp_view_3;
修改emp_view_3(id,name,salary,annual,income)视图
create or replace view emp_view_3(id,name,salary,annual,income)
as
select empno,ename,sal,sal*12,sal*12+NVL(comm,0)
from emp;
创建视图emp_view_4，视图中包含【各部门的最低工资，最高工资，平均工资】
表：
select deptno "部门号",min(sal) "最低工资",max(sal) "最高工资",avg(sal) "平均工资"
from emp
group by deptno;
视图（虚表）：
create or replace view emp_view_4
as
select deptno "部门号",min(sal) "最低工资",max(sal) "最高工资",avg(sal) "平均工资"
from emp
group by deptno;
前提：必须是经常访问的字段和记录
删除视图emp_view_1中的7788号员工，使用delete操作
delete from emp_view_1 where empno=7788;
如果视图做（insert/update/delete）操作会引响表
修改emp_view_1为只读视图【with read only;】
create or replace view emp_view_1
as
select * from emp
with read only;
delete from emp_view_1 where ename = 'SMITH';
删除emp_view_1/2/3/4/5视图
drop view emp_view_1;
删除整个视图，不会删除真实的基表
但是删除、更新、插入视图中的记录时，会引影真实的基表
项目中，强烈提倡将视图做成read only的。
对于，经常被客户查询的字段，提倡做成视图
为emp表的empno字段，创建序列emp_empno_seq
create sequence emp_empno_seq;
或
create sequence emp_empno_seq
start with 10
increment by 3
minvalue 10
nocycle
cache 5;
查询emp_empno_seq序列的当前值currval和下一个值nextval，第一次使用应该选用nextval
select emp_empno_seq.nextval from dual;
select emp_empno_seq.currval from dual;
向emp表插入记录，empno字段使用序列值
insert into emp(empno,ename,sal)
values(emp_empno_seq.nextval,'JACK',5000);
修改emp_empno_seq序列的maxvalue属性，不能修改start with，只能删除该序列后，重建序列
alter sequence emp_empno_seq
increment by 3;
查看序列：select * from user_sequences;
查看索引：select * from user_indexes;
查看视图：select * from user_views;
查看序列：select * from user_sequences;
查看同义词：select * from user_synonyms;
查看存储过程：select * from user_procedures;
查看表，视图：select * from tab;
查看数据库中对应表结构：desc 表名;
查看当前用户：show user;
删除emp_empno_seq序列
drop sequence emp_empno_seq;
为emp表的empno字段，创建索引emp_empno_idx
create index emp_empno_idx
on emp(empno);
为emp表的ename,job字段，创建索引emp_ename_job_idx
create index emp_ename_job_idx
on emp(ename,job);
删除emp_empno_idx和emp_ename_job_idx索引
drop index emp_emp_idx;
drop index emp_ename_job_idx;
创建视图emp_view_1
create view emp_view_1
as
select * from emp
where deptno=20;
创建视图emp_view_1的同义词/别名empview1
create synonym empview1
for emp_view_1;
给scott用户赋予别名权限：grant CREATE SYNONYM to scott ;
创建同义词/别名empview1
drop synonym empview1;
//------------------------------------------------------------------------------------------------------
写一个PLSQL程序，输出"hello world"字符串【dbms_output.put_line('需要输出的字符串');】
begin
dbms_output.put_line('hello world');
dbms_output.put_line('hello world');
end;
/
设置显示PLSQL程序的输出结果，默认情况下，不显示PLSQL程序的输出结果
set serveroutput on;//打开服务端的输出
set serveroutput off;//关闭服务端的输出（默认）
使用基本类型变量和常量，求10+100的和
declare
--以下是变量声明
result number(3) := 0;
msg varchar2(12);
begin
--将10+100的和，覆给result变量
result := 10 + 100;
msg := '结果为：';
dbms_output.put_line(msg || result);
end;
/
使用引用型变量%type，输出7369号员工姓名和工资，【表名.列名%type】【常用】
declare
--声明pename和psal二个变量
/*
pename varchar2(10);
psal number(7,2);
*/
pename emp.ename%type;
psal emp.sal%type;
begin
--将ename和sal获得的值，覆给x和y变量
select ename,sal into pename,psal from emp where empno=7369;
--显示
dbms_output.put_line('7369号员工的姓名：' || pename);
dbms_output.put_line('7369号员工的工资：' || psal);
end;
/
使用记录型变量%rowtype，输出7788号员工姓名和工资，【表名%rowtype】
declare
--record_emp变量与emp表的记录类型一样，即emp表有几个字段，那么record_emp就有几个字段
record_emp emp%rowtype;
begin
select * into record_emp from emp where empno=7788;
dbms_output.put_line('7788号员工的姓名：' || record_emp.ename);
dbms_output.put_line('7788号员工的姓名：' || record_emp.sal);
end;
/
使用if-else显示"10>5"或"10<5"
begin
if 10>=5 then
dbms_output.put_line('10>=5');
else
dbms_output.put_line('10<5');
end if;
end;
/
使用if-elsif-elsif-else显示今天星期几，并且显示是"工作日"还是"休息日"
declare
pday varchar2(9);
begin
select to_char(sysdate,'day') into pday from dual;
dbms_output.put_line('今天是：' || pday);
if pday='星期六' then
dbms_output.put_line('休息日');
elsif pday='星期日' then
dbms_output.put_line('休息日');
elsif pday not in ('星期六','星期日') then
dbms_output.put_line('工作日');
end if;
end;
/
//------------------------------------------------------------------------------------------------------
动手练习：
1）找到员工表中工资最高的前三名
select * from （select * from emp order by sal desc）where rownum<=3;
2）找到员工表中薪水大于本部门平均薪水的员工
select emp.empno,emp.ename,emp.sal,a.avgsal,a.deptno from emp,(select deptno,avg(sal) as avgsal from emp group by deptno) a where sal >avgsal and emp.deptno in (a.deptno);