Oracle学习笔记

通过SQL plus客户端通过sql语句操控Oracle数据库，也可以通过页面的形式操控

登录：sqlplus 用户名/密码
 或者在oracle的sql命令行：conn 
创建用户：create user 用户名 identified  by 密码;
赋予权限：grant connect,resource to 用户;
查看用户所有的表名：select table_name
  from user_tables;
查看表结构： desc s_emp;
查看表中的所有内容：select * from s_dept;
显示的时候缩短某一列：col 列名 for a15;
显示当前用户show user
清屏clear screen;
查看系统时间的格式select sysdate from dual;
将系统时间改为英文的格式alter session set nls_date_language=english;
导入文件start c:/summit2.sql
替换：c
2:先定位到错误的行
c/s_em/s_emp
查看上次运行的sql语句：l
执行上次的sql语句：/
追加：a
先定位到错误的行
a  追加的内容(需要注意此时空格需要至少两个
  第一个表示分割 第二个表示真正需要的内容)
插入：i
2:先定位到要插入语句的行
i 插入的内容
删除某一行：del
先定位到你要删除的行
del
将buffer中sql保存到文件中 save filename
将文件输出到buffer中 get filenanme
执行文件 start filename/@ filename/  /
将所有操作保存起来 spool filename
spool off;关闭
转义 select last_name from s_emp where last_name like '/_%' escape '/';
asc 正序由小到大  desc 倒序
字符串的连接：concat
select concat('good','string')
from dual;
取子串的函数substr 从1开始后几位 3(表示个数)
select substr('string',1,3)
from dual;

四舍五入：round
select round(46.66,-1)
from dual;

只舍不取：trunc
select trunc(45.83,-2)
from dual;

取模:mod
select mod(900,300)
from dual;

查询员工所在部门的信息，
包括没有部门号的员工
select d.name,e.last_name
from s_emp e,s_dept d
where e.dept_id=d.id(+)
标准的sql语句
select d.name,e.last_name
from s_emp e left join s_dept d
on e.dept_id=d.id

union 并集
union all 把两个集合都显示出来重复的显示两遍
minus第一个集合除去和第二个和它相同的部分
rownum：记录行号 只能=1或《=一个数
select last_name,salary
from s_emp
where rownum<=6
minus
select last_name,salary
from s_emp
where rownum<=2
intersect交集

select dept_id,avg(salary) from s_emp group by dept_id;
select dept_id,max(salary) from s_emp group by dept_id;
select dept_id,avg(salary) from s_emp group by dept_id having avg(salary)>=1400;
where 后不能出现组函数，组函数在where后执行
select dept_id,sum(salary) from s_emp group by dept_id having sum(salary)>4000;
对不同表分组不会相互影响，对一张表进行分组会影响数据增加
select avg(salary),dept_id,last_name from s_emp group by dept_id,last_name;//26
select avg(salary),dept_id from s_emp group by dept_id;//13
select avg(salary),name,dept_id from s_dept,s_emp where s_dept.id=s_emp.dept_id group by name,dept_id;//12

子查询
select dept_id,avg(salary) from s_emp group  by dept_id;
select * from (select dept_id,avg(salary) from s_emp group  by dept_id) temp;

select s1.name,s2.dept_id,avg(s2.salary)
from s_dept s1, s_emp s2
where s1.id=s2.dept_id
group by s1.name,s2.dept_id
having avg(s2.salary)>=1400
order by s2.dept_id desc;
(处理数据量大，效率低)
select s1.name,s2.dept_id,avg(s2.salary)
from s_dept s1, s_emp s2
group by s1.name,s2.dept_id,s1.id
having avg(s2.salary)>=1400 and s1.id=s2.dept_id
order by s2.dept_id desc;

select s1.last_name,max(s2.salary)
from s_emp s1,s_emp s2
where s1.dept_id=s2.dept_id
group by s1.last_name;

select s1.last_name,max(s2.salary)
from s_emp s1,s_emp s2
group by s1.last_name,s1.salary
having s1.salary=max(s2.salary);

select max(s1.salary),s1.dept_id,s2.last_name,s3.name,s4.name
from s_emp s1,s_emp s2,s_dept s3,s_region s4
where s1.dept_id=s2.dept_id and s1.dept_id=s3.id and s3.region_id=s4.id
group by s1.dept_id,s2.last_name,s2.salary,s3.name,s4.name
having s2.salary=max(s1.salary)
order by s1.dept_id desc;
子查询
select salary from s_emp where last_name='Smith';
select last_name,salary from s_emp where salary >(select salary from s_emp where last_name='Smith');

select avg(salary) from s_emp where dept_id=41 group by dept_id;
select dept_id
from s_emp
group by dept_id
having  avg(salary)>=(select avg(salary) from s_emp where dept_id=41 group by dept_id);

select last_name,dept_id,salary
from s_emp
where dept_id in (
 select dept_id
 from s_emp
 group by dept_id
 having  avg(salary)>(select avg(salary) from s_emp where dept_id=41 group by dept_id)
);

select dept_id,avg(salary)
from s_emp
group by dept_id

select s1.last_name,s1.dept_id,s1.salary,temp.avgsalary,sd.name,sr.name
from s_emp s1,(select dept_id,avg(salary) avgsalary
  from s_emp
  group by dept_id) temp,s_dept sd,s_region sr
where s1.dept_id in (
 select dept_id
 from s_emp
 group by dept_id
 having  avg(salary)>(select avg(salary) from s_emp where dept_id=41 group by dept_id)
)and s1.dept_id=temp.dept_id and s1.dept_id=sd.id and sd.region_id=sr.id
order by s1.dept_id asc;

select s1.last_name,s1.salary,s1.dept_id,temp.avgsalary,sd.name,sr.name
from s_emp s1,(select dept_id,avg(salary) avgsalary
  from s_emp
  group by dept_id) temp,s_dept sd,s_region sr
where s1.dept_id=temp.dept_id and s1.salary>temp.avgsalary and s1.dept_id=sd.id and sd.region_id=sr.id
order by s1.dept_id;

select dept_id
from s_emp
where last_name='Ngao';

select avg(salary)
from s_emp
where dept_id=(select dept_id
from s_emp
where last_name='Ngao')

select s1.last_name,s1.salary,s1.dept_id,temp.avgsalary,sd.name,sr.name
from s_emp s1,(select dept_id,avg(salary) avgsalary from s_emp group by dept_id) temp,s_dept sd,s_region sr
where s1.dept_id in(
   select dept_id
   from s_emp
   group by dept_id
   having avg(salary)>(select avg(salary)
      from s_emp
      where dept_id=(select dept_id
      from s_emp
      where last_name='Ngao')
   )
) and s1.dept_id=temp.dept_id and s1.dept_id=sd.id and sd.region_id=sr.id;

--------------------
select last_name,salary
from s_emp where salary>(select salary from s_emp where upper(last_name)='CHANG');

select last_name,salary,dept_id
from s_emp
where salary>(select salary from s_emp where upper(last_name)='CHANG') or dept_id in(select id from s_dept where region_id=3);

select region_id
from s_dept,s_emp
where s_dept.id=s_emp.dept_id and upper(last_name)='CHANG'
select avg(salary)
from s_emp,s_dept
where s_emp.dept_id=s_dept.id and region_id=(select region_id
      from s_dept,s_emp
      where s_dept.id=s_emp.dept_id and upper(last_name)='CHANG')
select last_name,salary
from s_emp
where salary>(select avg(salary)
   from s_emp,s_dept
   where s_emp.dept_id=s_dept.id and region_id=(select region_id
         from s_dept,s_emp
         where s_dept.id=s_emp.dept_id and upper(last_name)='CHANG'))

select manager_id from s_emp where upper(last_name)='CHANG'
select salary from s_emp where id=(select manager_id from s_emp where upper(last_name)='CHANG')
select last_name,salary from s_emp where salary>(select salary from s_emp where id=(select manager_id from s_emp where upper(last_name)='CHANG'));

select manager_id from s_emp where upper(last_name)='CHANG'
select manager_id from s_emp where id=(select manager_id from s_emp where upper(last_name)='CHANG')
select region_id from s_dept,s_emp where s_dept.id=s_emp.dept_id and s_emp.id=(
 select manager_id from s_emp where id=(select manager_id from s_emp where upper(last_name)='CHANG'))
select min(salary) from s_emp,s_dept where s_emp.dept_id=s_dept.id and s_dept.region_id=(
 select region_id from s_dept,s_emp where s_dept.id=s_emp.dept_id and s_emp.id=(
 select manager_id from s_emp where id=(select manager_id from s_emp where upper(last_name)='CHANG')))
select last_name,salary from s_emp where salary>(select min(salary) from s_emp,s_dept where s_emp.dept_id=s_dept.id and s_dept.region_id=(
 select region_id from s_dept,s_emp where s_dept.id=s_emp.dept_id and s_emp.id=(
 select manager_id from s_emp where id=(select manager_id from s_emp where upper(last_name)='CHANG'))))

select salary from s_emp where upper(last_name)='CHANG'
select sales_rep_id from s_customer
select last_name,salary from s_emp where salary>(select salary from s_emp where upper(last_name)='CHANG')and id in(select sales_rep_id from s_customer)

select dept_id from s_emp where upper(last_name)='CHANG'
select salary from s_emp where upper(last_name)='CHANG'
select sum(salary)-(select salary from s_emp where upper(last_name)='CHANG')
 from s_emp where dept_id=(select dept_id from s_emp where upper(last_name)='CHANG') group by dept_id;

select count(*) from s_customer where sales_rep_id not in(11,12)

select dept_id from s_emp where upper(last_name)='CHANG'
select avg(salary) from s_emp where dept_id=(select dept_id from s_emp where upper(last_name)='CHANG')
select avg(salary),dept_id from s_emp group by dept_id having avg(salary)>(select avg(salary) from s_emp where dept_id=(select dept_id from s_emp where upper(last_name)='CHANG'))

select region_id from s_dept,s_emp where s_dept.id=s_emp.dept_id and upper(last_name)='CHANG'
select avg(salary) from s_emp,s_dept where s_emp.dept_id=s_dept.id and region_id=(select region_id from s_dept,s_emp where s_dept.id=s_emp.dept_id and upper(last_name)='CHANG')
select dept_id from s_emp where upper(last_name)='CHANG'
select s1.id,s1.last_name,s1.salary,s2.name,s3.name
from s_emp s1,s_dept s2,s_region s3
where s1.dept_id=s2.id and s2.region_id=s3.id and (
salary>(select avg(salary) from s_emp,s_dept where s_emp.dept_id=s_dept.id and region_id=(select region_id from s_dept,s_emp where s_dept.id=s_emp.dept_id and upper(last_name)='CHANG'))
or dept_id !=(select dept_id from s_emp where upper(last_name)='CHANG'))
--------------------------
select last_name from s_emp where id=10;
运行时参数(字符串要加引号或者定义是提前写好)
select last_name from s_emp where id=&id;
select id,last_name from s_emp where last_name='&name';
select last_name from s_emp &str;(str=where id=10)
select last_name,salary,dept_id from s_emp where salary>&a and salary<&b;

define undefine定义常量
def A=s_emp
select last_name from &A;
undef A
accept name prompt'请输入name变量的值'//先提示一下在定义常量的值
accept name prompt'请输入name的值'hide//输入时看不见值
def //查看定义过的值
------------------------------------
must_be(缺一不可)
may_be(可能有)
#：唯一，可能为主键
*：非空
o:可有可无
虚线：may be
实线：must be
竖杠(|)：要强制在|一方建立一个联合主键，将对方ID拿过来做联合主键
伞状图标代表多的一方
数据库有6种范式，能达到第三就行
第一范式1nf：每个列里的值不能在分割。如爱好，可以分为足球，篮球。所以不满足
第二范式2nf：满足第一范式基础上表中的非主键列都必须依赖主键列  订单表中订单编号是主键。订单名称，订单日期依赖，订单中产品生产地（依赖产品编号）不依赖，不满足
第三范式3nf：满足第二范式基础上，非主键类都必须依赖主键列，而不能佳节的依赖、传递
 （订单表：订单编号 主键
 订单编号 订单名称 顾客编号 顾客姓名）顾客编号决定顾客姓名，有传递关系不满足
联合主键，单独可能不是唯一的，联合起来是非空唯一的

-----------------------------------------
视图view 虚拟出一个姓名 部门表，实际上底层还是从两个表中操作
序列sequence 向S_emp中传数据只需传姓名。。。而不需要id，制动插入了
 id排到10，有可能多线程出错
 高低位序列 单位100 1100 00随机产生（用的不多）
索引index 检索快一点
create table test(
id number(7)
)
desc test
---------删除表----------
drop table test
conn system/oracle
create table oracle.test(
id number(7)
)
desc oracle.test
conn oracle/oracle
desc test
drop table test
select sequence_name from user_sequences;
select s_emp_id.nextval from dual;(假列)
先创主键表，再创外键表
先删外键表，再删主键表

主键约束:(约束的名字（表名——属性——约束缩写）)primary key
id number(7) constraint test_id_pk primary key
主键的列级约束格式
column(列) typedate(数据类型) constraint(关键字) constraintName(约束名字) constraintType(约束类型)
主键的表级约束格式：放在整个建表语句最后
constraint constraintName constraintType(column)
constraint test_id_pk primary key(id)

外键约束列级约束格式foreign key
column dateType constraint constraintName references pktableName主键表名（pkcolumn主键列名）
dept_id number(7) constraint s_emp_dept_id_fk references s_dept(id)
外键约束表级约束格式
constraint constraintName foreign key(column) references pktableName(pkcolumn)
constraint s_emp_dept_id_fk foreign key(dept_id) references s_dept(id)

选择性约束列级约束check
column datetype constraint constraintName check(column in(list))
gender varchar2(10) constraint student_gender_ck check(gender in('male','female'))
选择性约束表级约束
constraint constraintName column check(column in(list))
constraint student_grnder_ck grnder check(gender in('male','female'))

联合主键
constraint constraintName primary key(column1,cooumn2...)
联合外键
constraint constrainName foreign key(column1,column2...) references pktableName(pkcolumn1,pkcolumn2..)
联合唯一
constraint constraintName unique(column1,column2...)
-----创建表-----
create table s_dept2(
id number(7) constraint s_dept2_pk primary key,
name varchar2(25) constraint s_dept2_name_nn not null,
region_id number(7) constraint s_dept2_region_id_fk references s_region(id),
constraint s_dept2_name_region_id_uk unique(name,region_id));

desc s_dept2
insert into s_dept2 values(1,'briup',1);
insert into s_dept2 values(1,'briup',1);//主键约束
insert into s_dept2 values(2,'briup',1);//联合唯一约束
insert into s_dept2 values(3,'null',1);//非空约束
insert into s_dept2 values(4,'briup',100);//完整性约束，区域 表没有100

----创建表----
create table s_emp2(
id number(7) constraint s_emp2_id_pk primary key,
last_name varchar2(25) constraint s_emp2_last_name_nn not null,
first_name varchar2(25),
userid varchar2(8) constraint s_emp2_userid_nn not null constraint s_emp2_userid_uk unique,
start_date date default sysdate,
comments varchar2(25),
manager_id number(7),
title varchar2(25),
dept_id number(7) constraint s_emp2_dept_id_fk references s_dept(id),
salary number(11,2),
commission_pct number(4,2) constraint s_emp2_commission_pct_ck check(commission_pct in(10,12.5,15,17.5,20))
);
---- 创建一张表，来自子查询内容----
create table emp_41
as
select *from s_emp
where dept_id=41;
-------删除表-------
drop table emp_41

create table emp_41(id,last_name)
as
select id,last_name from s_emp
where dept_id=41;

-----只有非空约束能进来------
drop table emp_41
create table emp_41
as
select id,commission_pct from s_emp
where dept_id=41;
select * from emp_41
------向表中插入数据（check不起作用）-----
insert into emp_41 values(100,5)
-----------------------------------------------------------
select * from dictionary;
select count(*) from dictionary;
---user_objects----
desc user_objects;
select object_name,object_type from user_objects;
select distinct object_type from user_objects;
----------查看当前用户有那些表---------
select object_name from user_objects where object_type='TABLE'
select table_name from user_tables;
select object_name from user_objects where object_type='SEQUENCE'
-----user_constraints-----约束
desc user_constraints
select constraint_name,constraint_type from user_constraints;
select constraint_name,constraint_type from user_constraints where table_name='S_EMP';
------user_cons_columns------
select constraint_name,column_name from user_cons_columns where table_name='S_EMP'
---建表---
drop table emp_41;
create table emp_41(
id number(7) primary key)
select constraint_name,column_name from user_cons_columns where table_name='EMP_41'
---------------------------------------------------------
select * from emp_41;
desc emp_41;
drop table emp_41;
create table emp_41
as
select id,last_name,salary
from s_emp
where dept_id=41;
select * from emp_41;
desc emp_41;
----insert-----
insert into emp_41 values(100,'briup',1000);
insert into emp_41(id,last_name) values(101,'briup2')
insert into emp_41(id) values(102)//至少包含所有非空列

drop table emp_41;
create table emp41
as
select id,userid,start_date
from s_emp
where dept_id=41;
select * from emp41;
-----USER,SYSDATE---------------------------
insert into emp41 values(100,USER,SYSDATE)
-------------to_date--------------------
alter session set nls_date_language=english;
insert into emp41 values(100,user,to_date('01-sep-17','dd-mm-yy'))
----------&可变变量---------------
insert into emp41(id,userid) values(&emp_id,'tom')
------accept------
accept emp_id prompt 'emp_id的值：'
undefine emp_id
accept emp_id prompt 'emp_id:';
insert into emp41(id,userid) values(&emp_id,'tom');
----------清空表=-----------通过子查询注入数据
truncate table emp41;
insert into emp41
select id,userid,start_date
from s_emp
where dept_id=41;
----------------------------------------------------修改表里的数据--------------------------------------------
---------update-------改
update emp41 set userid='tom' where id=2
update emp41 set id=3,userid='jack' where id=2
update emp41 set userid='tom'
update s_dept set region_id=100 where id=10;//region_id在s_region表中必须存在
-------------delete---------删除表中的数据
delete from emp41 where id=3;
delete from emp41;
delete from s_dept where id=10;//有完整性约束不能删除

DML 查询语句要commit或回滚，才有效
DDL 定义语句不需要,会自己提交
DCL 控制语句，自动提交 
----commit exit----都会提交
drop table emp41;
create table emp_41
as
select id,last_name
from s_emp;
delete from emp_41;//在打开还在
commit//不在了

insert into emp_41 values(1,'tom');
exit//在进去插入成功

update emp_41 set last_name='wang' where id=1;
delete from emp_41 where id=1;

--------rollback--------回滚
update emp_41 set id=2 where id=1;
savepoint a;
delete from emp_41;
savepoint b;
rollback;
select * from emp_41;
rollback to a;//a不存在回到初始状态时没有a

update emp_41 set id=4 where id=1;
savepoint a;
delete from emp_41;
savepoint b;
rollback to b;
rollback to a;//可以查到
----------------------------------------修改表本身，约束、列、表名字-------------------------------
--------------------------DDL语句--------------------------------------
drop table emp_41;
create table emp_41
as
select id,salary
from s_emp
where dept_id=41;
-------------------alter------------增加一列
alter table emp_41
add age number(7);
------modify----------更改新增列默认值和约束-----
alter table emp_41
modify age number(3);

alter table emp_41
modify age number(3) default 18;//之前的数据还为空，但新增的默认18
insert into emp_41(id,salary) values(100,1000);

alter table emp_41
modify age number(3) constraint emp_41_nn not null;//不能修改之前有空值
 解决方案
  delete from emp_41;
   commit;
  alter table emp_41
  modiry age number(3) constraint emp_41_nn not null;

alter table emp_41
modify age varchar2(10);
 ----------add-表级约束----------
alter table emp_41
add constraint emp_41_age_uk unique(age);//modify只能约束Not null 其他约束必须用add 因为add是表级约束，not null必须列级写法
--------drop--------
alter table emp_41
drop column age;
-------
alter table emp_41
add dept_id number(7)
insert into emp_41 values(1,1000,10000)
alter table emp_41
add constraint emp_41_dept_id_fk foreign key(dept_id) references s_dept(id)//s_dept 中id没有10000
delete from emp_41;
select constraint_name from user_constraints where table_name='S_DEPT'
----------删除约束drop----------先删外键约束再删主键约束-
select constraint_name from user_constraints where table_name='EMP_41'
drop table emp_41;
create table emp_41(
id number(7) constraint emp_41_id_pk primary key,
name varchar2(10) constraint emp_41_name_nn not null,
age number(7) default 18);
select constraint_name from user_constraints where table_name='EMP_41';
alter table emp_41
drop constraint emp_41_name_nn;
-------------cascade--级联操作，删除一张表的主键约束同时删除外键约束
alter table s_dept
drop primary key cascade;
select constraint_name from user_constraints where table_name='S_EMP'
------------级联删除
drop table s_region;//删不掉
drop table s_region cascade constraint;//S_dept 外键约束没了
-------raname------改变表名 列名-
rename s_dept2 to s_dept3;//s_dept2 就不存在了
alter table s_dept3 rename column region_id to region_id2;
----truncate--删除所有行不需要提及--
truncate table s_dept;
--------------------------------------------序列--给主键服务----------------------------------
----创建序列--
select table_name from user_tables;
select * from s_emp2;
create table emp_41(
id number(7) constraint emp_41_id_pk primary key,
last_name varchar2(25) constraint emp_41_last_name_nn not null)
insert into emp_41 values(1,'tom');
insert into emp_41 values(1,'tom');//违反唯一约束
解决方案
 create sequence emp_41_id
 increment by 1
 start with 1
 maxvalue 5;
select sequence_name from user_sequences;
desc user_sequences;
select sequence_name,increment_by from user_sequences;
-----nextval currval--------下一个值，当前值，必须先用nextval
select emp_41_id.nextval from dual;
select emp_41_id.currval from dual;
----------通过nextval插入id---------
insert into emp_41 values(emp_41_id.nextval,'tom');
---------查看档期那的值----------
select emp_41_id.currval from sys.dual;//当序列没取到最大值时有效
-------改变序列的自增，最大值-------无法更改开始值
alter sequence emp_41_id
increment by 2
maxvalue 20;
insert into emp_41 values(emp_41_id.nextval,'tom');
select * from emp_41
-----更改开始值---不行
alter sequence emp_41_id
start with 16;
-----删除序列-----
drop sequence emp_41_id;
select sequence_name from user_sequences;

create sequence emp_41_id
increment by 1
start with 1;//序列只能作用当前用户

insert into s_dept values(emp_41_id.nextval,'briup',10);//可以作用当前用户不同表
-------------------视图view---虚表：数据的不同显示效果 提取不同表的内容整合成一张表------------安全性，隐藏来源，只读-------------
----创建view--------权限不足
create view view1
as select id,last_name,salary
from s_emp
where dept_id=41;
conn system/system;
grant create view to oracle;
conn oracle/oracle;
desc view1;
select * from view1;
update view1 set last_name='tom' where id=2;//s_emp也会改变
-----view---------取别名
1 drop view view1;
 select view_name from user_views;
 create view view1
 as
 select id,last_name employee//查询时取别名
 from s_emp
 where dept_id=41;
 select * from view1;
2 create or replace view view1(id_number,last_name)//创建时去别名
 as
 select id,last_name
 from s_emp
 where dept_id=41;
----view--------可以包含组函数
create or replace view view1
as
select  min(e.salary) m,max(salary) b,avg(e.salary) a,count(*) c
from s_emp e,s_dept d
where e.dept_id=d.id
group by d.name
order by avg(e.salary) desc
----------删除视图----
drop view view1;
--------force------即使基表不存在也能创建视图
create view view2
as
select * from s_emp3;
create force view view2
as
select * from s_emp3;
select view_name from user_views;//视图表已创建
drop view view2;
---------with read only-----提高安全性
create view view1
as
select id,last_name
from s_emp
with read only
select * from view1;
delete from view1;//删不掉
update view1 set last_name='tom' where id=1;//不能更新
-----------------------index----------------相当于书的目录---------服务任何键，该键重复被查询---------------------
create table emp_41
as
select id,last_name
from s_emp
where dept_id=41;
create index emp_41_last_name_idx
on emP_41(last_name)