oracle笔记整理2

--创建员工信息表
create table employee
(
empno number(4) not null,--员工编号
ename varchar2(10),  --员工姓名
job varchar2(9),     --员工工种
mgr number(4),       --上级经理编号
hiredate date,       --受雇日期
sal number(7,2),     --员工薪水
comm number(7,2),    --福利
deptno number(2)     --部门编号
);
select * from employee;
--插入数据
insert into employee(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(1,'张一','程序员',1,'2016-02-05',3000,2000，1);
insert into employee(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(2,'张二','开发人员',3,'2017-06-05',5000,2000，2);
insert into employee(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(3,'张三','测试人员',1,'2014-11-05',8000,5000，2);
insert into employee(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(4,'张四','运维人员',2,'2017-02-02',10000,2300，1);
insert into employee(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(5,'张五','发布',3,'2016-02-05',6300,36000，3);
--将scott里面的emp数据导入此表
insert into employee select * from scott.emp;
commit;
select * from employee;
--创建部门表和上级经理表
create table bumen
(
buno number(2),--部门编号
buname varchar2(50)  --部门名称
);
select * from bumen;
--往部门表里面插入数据
insert into bumen(buno,buname)
values(1,'开发部');
insert into bumen(buno,buname)
values(2,'测试部');
insert into bumen(buno,buname)
values(3,'设计部');
insert into bumen(buno,buname)
values(4,'运维部');
insert into bumen(buno,buname)
values(5,'管理部');
insert into bumen(buno,buname)
values(7902,'财务部');
insert into bumen(buno,buname)
values(7968,'福利部');
insert into bumen(buno,buname)
values(7698,'奖金部');
insert into bumen(buno,buname)
values(7839,'娱乐部');
insert into bumen(buno,buname)
values(7566,'搬砖部');
insert into bumen(buno,buname)
values(7788,'前台部');
insert into bumen(buno,buname)
values(7566,'看看部');
insert into bumen(buno,buname)
values(7782,'2222部');
insert into bumen(buno,buname)
values(7902,'1111部');
insert into bumen(buno,buname)
values(7839,'物流部');
insert into bumen(buno,buname)
values(7788,'废物部');
commit;
select * from bumen;
--创建上级经理表
create table jinli
(
jno number(4),--经理编号
jname varchar2(50)  --经理名称
);
commit;
select * from employee;
--给经理表里面插入数据
insert into jinli(jno,jname)values(1,'穆雄雄');
insert into jinli(jno,jname)values(2,'成志龙');
insert into jinli(jno,jname)values(3,'杜永杰');
insert into jinli(jno,jname)values(4,'王帅帅');
insert into jinli(jno,jname)values(5,'张思淼');
insert into jinli(jno,jname)values(7839,'李白');
insert into jinli(jno,jname)values(7499,'杜甫');
insert into jinli(jno,jname)values(7521,'王伟');
insert into jinli(jno,jname)values(7566,'王安石');
insert into jinli(jno,jname)values(7654,'冯巩');
insert into jinli(jno,jname)values(7698,'曾巩');
insert into jinli(jno,jname)values(7782,'欧阳修');
insert into jinli(jno,jname)values(7788,'王杰');
insert into jinli(jno,jname)values(7844,'马强');
insert into jinli(jno,jname)values(7876,'刘三单');
insert into jinli(jno,jname)values(7876,'李安');
insert into jinli(jno,jname)values(7900,'曹春喜');
insert into jinli(jno,jname)values(7902,'付婷婷');
insert into jinli(jno,jname)values(7934,'孙中天');
insert into jinli(jno,jname)values(7369,'李昂');
insert into jinli(jno,jname)values(7499,'金钟管');
insert into jinli(jno,jname)values(7521,'张瑞前');
insert into jinli(jno,jname)values(7566,'五保前');
insert into jinli(jno,jname)values(7654,'张何婷');
insert into jinli(jno,jname)values(7369,'孙浩');
select * from jinli;
delete from jinli where jno>5
--删除编号重复的，保留一个
delete from employee where rowid not in
(
select max(rowid) from employee group by empno 
having (count(empno)>1)   --查询编号重复的
union
select max(rowid) from employee group by empno 
having (count(empno)=1)   --查询编号不重复的
);
--删除编号大于5的员工信息
delete from employee where empno >5;
select * from employee;
--添加约束
select * from bumen;
--先设置主键（员工表）
alter table employee
add  constraint pk_empno primary key (empno);
--给部门表添加主键
alter table bumen
add constraint pk_buno primary key (buno);
--给经理表添加主键
alter table jinli
add constraint pk_jno primary key (jno);
--员工编号作为主键，部门编号作为外键与部门表相关联
alter table employee
add constraint fk_deptno foreign key(deptno) references bumen(buno);
--给员工信息表里面添加两列（电话，地址）
alter table employee
add(empTel_no varchar2(12),
empAdress varchar2(20)
);
--删除电话和地址列
alter table employee
drop(empTel_no,empAdress);
--按照薪水从高到低显示数据
select * from employee order by sal desc;
--上级3(分页查询)
--查询员工薪水从高到低的排序为3-5的记录
SELECT * 
FROM (SELECT e.*,rownum rn FROM (SELECT * FROM employee ORDER BY sal DESC) e)  
WHERE rn>=3 AND rn<=5;
--网上找的分页查询
--分页查询1
select * from 
(
select a.*,rownum rn from
(
select * from scott.emp
)
a
)
where rn between 3 and 5;
--分页查询2
select * from 
(
select a.*,rownum rn from 
(
select * from scott.emp
)a
where rownum <=5
)
where rn >=3;
--自己写的分页查询
select * from (select a.*,rownum rn from (select * from employee order by sal desc) a) where 
rn>=2 and rn<=5
--使用分析函数对员工表进行查询
--DENSE_RANK必须大写
select * from (
select employee.*,DENSE_RANK( ) over (partition by deptno order by sal desc) "DENSE_RANK"
from employee )e
where DENSE_RANK=2;
--上机5
--round函数（四舍五入）
/*如何使用 Oracle Round 函数 (四舍五入)
描述 : 传回一个数值，该数值是按照指定的小数位元数进行四舍五入运算的结果。
SELECT ROUND(number, [ decimal_places ] ) FROM DUAL
参数:
number : 欲处理之数值
decimal_places : 四舍五入 , 小数取几位 ( 预设为 0 )
Sample :
select round(123.456, 0) from dual;          回传 123 
select round(123.456, 1) from dual;          回传 123.5 
select round(123.456, 2) from dual;          回传 123.46 
select round(123.456, 3) from dual;          回传 123.456 
select round(-123.456, 2) from dual;        回传 -123.46
*/
---日期四舍五入
select ename "姓名",to_char(round (hiredate,'yyyy'),'YYYY')||'年度' 入职年度 from employee;
--列出至少有一个雇员的所有部门
--distinct(查找重复的列)
select distinct deptno from employee;
--列出薪金比张三多的员工
select * from employee where sal >(select sal from employee where ename='张三');
--列出所有职位为程序员的姓名及其部门的名称
select ename,job,bumen.buname from employee,bumen where employee.deptno=bumen.buno and job='程序员';
--列出各种工作类别的最低薪金，显示最低薪金大于15000的记录
select job, min(sal) from employee group by job having min(sal)>1500;
--找出个月最后一天受雇的所有雇员
select * from employee where hiredate=last_day(hiredate);
/*
oracle的日期函数last_day 意思是得到每月的最后一天,用这个函数,我们可以得到各种不同的日期.
1:得到当前月第一天与最后一天
*/
select
to_char(trunc(sysdate,'MONTH'),'yyyymmdd')
 firstday
, to_char(last_day(trunc(sysdate,'MONTH')),'yyyymmdd') lastday
     from dual;
     
--2:得到上月第一天与上月最后一天
SELECT to_char( last_day(add_months(SYSDATE, -2)) + 1 ,'yyyymmdd') firstday
,to_char(last_day(add_months(SYSDATE, -1)),'yyyymmdd') 
lastday 
FROM dual;
--3:得到上上个月第一天与上上个月最后一天
SELECT
to_char( last_day(add_months(SYSDATE, -3)) + 1 ,'yyyymmdd') 
firstday
,to_char(last_day(add_months(SYSDATE, -2)),'yyyymmdd')
 lastday 
FROM dual;
--4:得到下个月第一天与下个月最后一天
SELECT to_char( last_day(add_months(SYSDATE, 0)) + 1 ,'yyyymmdd') 
firstday
,to_char(last_day(add_months(SYSDATE, 1)),'yyyymmdd')
 lastday 
FROM dual;
/*
======================================================
简答题p38
======================================================
*/
--1.创建订单表
create table orders
(
order_id number(12) primary key,           --订单编号
order_date date not null,                  --订货日期
order_mode varchar2(8) not null,           --订货模式
customer_id number(6) not null,            --客户编号
order_status number(2),                    --订单状态
order_total number(8,2),                   --总定价
sales_rep_id number(6),                    --销售代表id
promotion_id number(6)                     --推广员id
)
--创建客户表
create table customers
(
customer_id number(6) not null,            --客户编号
cust_fiest_name varchar2(20) not null,     --名
cust_last_name varchar2(20) not null,      --姓氏
nls_languages varchar2(3),                 --语言
nls_territory varchar2(30),                --地域
credit_limit number(9,2),                  --信贷限额
cust_email varchar2(30),                   --邮箱
account_mgr_id number(6),                  --客户经理
marital_status varchar2(30),               --婚烟状态
gender char(1)                             --性别
)
--修改字段
alter table customers modify (gender char(2));
alter table customers modify (nls_languages varchar2(10));
select * from customers
insert into customers (customer_id,cust_fiest_name,cust_last_name,nls_languages,nls_territory,credit_limit,cust_email,account_mgr_id,marital_status,gender)values('1','虎虎','王','汉语','山西吕梁','3000','wanghuhu@163.com',1,'未婚','男');
insert into customers (customer_id,cust_fiest_name,cust_last_name,nls_languages,nls_territory,credit_limit,cust_email,account_mgr_id,marital_status,gender)values('2','雄雄','穆','英语','山西吕梁','8000','muxiongxiong@163.com',2,'未婚','男');
insert into customers (customer_id,cust_fiest_name,cust_last_name,nls_languages,nls_territory,credit_limit,cust_email,account_mgr_id,marital_status,gender)values('3','永杰','杜','俄语','中国北京','1568','duyongjie@163.com',1,'未婚','男');
insert into customers (customer_id,cust_fiest_name,cust_last_name,nls_languages,nls_territory,credit_limit,cust_email,account_mgr_id,marital_status,gender)values('4','娇娇','宋','法语','山西太原','6000','songjiaojiao@163.com',3,'已婚','女');
insert into customers (customer_id,cust_fiest_name,cust_last_name,nls_languages,nls_territory,credit_limit,cust_email,account_mgr_id,marital_status,gender)values('5','思淼','张','汉语','山东济南','6320','zahngsimiao@163.com',3,'未婚','男');
insert into customers (customer_id,cust_fiest_name,cust_last_name,nls_languages,nls_territory,credit_limit,cust_email,account_mgr_id,marital_status,gender)values('6','春熙','曹','法语','山东济南','3600','caochunxi@163.com',2,'已婚','男');
insert into customers (customer_id,cust_fiest_name,cust_last_name,nls_languages,nls_territory,credit_limit,cust_email,account_mgr_id,marital_status,gender)values('6','儿子','王','法语','AMERICA','56000','america@163.com',3,'已婚','男');
insert into customers (customer_id,cust_fiest_name,cust_last_name,nls_languages,nls_territory,credit_limit,cust_email,account_mgr_id,marital_status,gender)values('8','孙子','Fssdfs','韩语','中国北京','6320','Fssdfs@163.com',4,'未婚','女');
select * from customers
--往订单里面添加数据
select * from orders;
insert into orders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id)
values(1,'2017-02-09','网上下单',2,1,323.23,1,2);
insert into orders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id)
values(2,'2016-11-09','上门购买',1,2,56.00,2,1);
insert into orders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id)
values(3,'2017-12-20','熟人推荐',3,1,6000,1,2);
insert into orders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id)
values(4,'2015-12-02','网上下单',5,2,365,2,2);
insert into orders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id)
values(5,'2017-12-09','上门购买',3,1,3210,1,2);
insert into orders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id)
values(6,'2014-11-11','网上下单',3,1,630,2,2);
insert into orders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id)
values(7,'2017-01-01','上门购买',2,1,6300,1,2);
commit;
select * from orders;
--1.查询客户表中所有不重复的分地域(distinct)
select distinct nls_territory from customers;
--2.在订单表中找出总定价在1000-6000之间的订单号
select order_id 订单编号,b.cust_fiest_name 顾客姓氏,c.ename 经理名字 from orders a join customers b on a.customer_id=b.customer_id join employee c on b.account_mgr_id=c.empno
 where order_id in
(
select order_id from orders where order_total>=1000 and order_total<=6000
);
--3.在顾客表（Customers）中找出所在地域为AMERICA的客户经理名称（Employee表的ename列）和薪水（employee表的sal列）。
select c.ename,c.sal from customers a join employee c on a.account_mgr_id=c.empno
where nls_territory='AMERICA'
--4.在顾客表（Customers）中找出所在地域为AMERICA、山东济南、山西吕梁和中国北京的客户编号及语言。
select customer_id,nls_languages from Customers 
where nls_territory='AMERICA' or  nls_territory='山东济南' or nls_territory='中国北京';
--5.在顾客表（Customers）中找出姓氏首字母为”F”的客户编号和邮箱。
select customer_id 客户编号,cust_email 邮箱 from customers 
where cust_last_name like 'F%';
--6.查出所有客户姓名和所下的订单编号
select cust_last_name,cust_fiest_name,b.order_id from customers a 
join orders b on a.customer_id =b.customer_id
where a.customer_id in
(
select customer_id from orders
);
/*
2. 根据员工表（Employee表）编写一个语句，只有当最低工资少于 5000 而且最高工资超过7000时，才显示部门 ID 以及该部门支付的最低工资和最高工资。
*/
select deptno 部门编号,max(sal) 最高工资,min(sal) 最低工资 from employee
group by deptno 
having min(sal)<5000 and max(sal)>7000;
/*
3. 根据员工表（Employee表）编写一个语句，显示各部门的每个工作类别中支付的最高工资
*/
select deptno,job,max(sal) from employee
group by deptno,job
order by deptno,job;
select to_char(sysdate,'YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS') from dual;
select to_char(sysdate,'YYYY"年"MM"月"DD"日" HH24:MI:SS') from dual;
select * from dual;
select SYS_CONTEXT('USERENV','TERMINAL') from dual;--获得主机名
select TO_DATE ('2013-1-1','yyyy-mm-dd') from dual;--将字符串转换成日期类型
select to_date('21210213','yyyy-mm-dd') from dual; --将字符串转换成日期类型
--删除重复的记录，保留一条
select max(rowid) from student 
group by sage,sname
having (count(sage||sname)>1);
union 
select max(rowid) from student
group by sname,sage
having (count(sage||sname)=1);
/*
创建自增长
*/
--新建一个表
create table grade
(
id number(2) not null primary key,
name varchar2(12)
)
--创建一个序列
create sequence grade_add
start with 1   --从1开始
increment by 1  --每次加1
nocycle --一直累加
--创建一个触发器执行
create or replace trigger grade_c
before insert on grade for each row when (new.id is null)
begin 
select grade_add.nextval into:new.id from dual;
end;
insert into grade(name) values('12班');
insert into grade (name) values('13班');
insert into grade (name) values('14班');
insert into grade(name) values('15班');
insert into grade (name) values('16班');
insert into grade (name) values('17班');
select * from grade;