/*

以下代码是对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;

删除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;

创建同义词/别名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）Oracle分页【老师写】

2）找到员工表中工资最高的前三名

select * from （select * from emp order by sal desc）where rownum<=3;

3）找到员工表中薪水大于本部门平均薪水的员工

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);

4）将今天讲过的OracleSQL，删除答案，自已练习一遍，使用SQL Developer 工具

//------------------------------------------------------------------------------------------------------