Oracle基础

Oracle

am.2019/10/22
C:\app\Administrator\product
C:\app\Administrator\oradata
C:\app\Administrator\oradata\orcl
XX.CTL 控制文件
XX.DBF 数据文件
XX.LOG 日志文件

jdk路径
C:\app\Administrator\product\11.1.0\db_1\jdk\bin

数据库文件默认存放路径
C:\app\Administrator\product\11.1.0\db_1\database

(mysql -u root -p)
cmd dos命令行执行
sqlplus 用户名/密码
sqlplus system/123

连接Oracle服务器
conn 用户名/密码 as 连接身份@服务器连接字符串

-- 创建表空间
create tablespace 表空间名 datafile '数据文件名' size 大小;

create tablespace T128 datafile 'T128.dbf' size 10M
AUTOEXTEND ON;

create tablespace T128 datafile 'C:\T128\T128.dbf' size 10M

/*
autoextend on 自动扩展表空间
*/

--删除非空表空间
drop tablespace 表空间名 including contents;

-- 查看表空间名字情况
select tablespace_name from dba_tablespaces;

-- 查看表结构
desc[ribe] 表名;

desc dba_tablespaces;

--创建用户使用表空间
create user 用户名 identified by 密码 default tablespace 表空间;

create user T128 identified by 123 default tablespace T128;

-- 授权
grant 权限 to 用户名;

grant dba to t128; --管理员权限
grant connect to t128; --连接权
session 会话权

use 数据库名(mysql)
-- 指定用户登录
connect 用户名/密码;

--创建表
varchar2
int
char
date

--创建表auto_test设置id的值为自增长
create table auto_test(
id int not null auto_increment primary key,
name varchar2(10)
);

--序列sequence
drop sequence seq_abc;

create sequence seq_abc
start with 1 maxvalue 99999;

select seq_abc.nextval
select seq_abc.currval from dual;

select to_char(sysdate,'yyyymmdd')||empno||seq_abc.nextval,empno,ename from emp;

--课后练习

--创建一个员工管理系统(表空间)
create tablespace emp datafile 'emp.dbf' size 10M autoextend on;

--创建一个用户使用该表空间
create user yc identified by 123 default tablespace emp;

--用system用户授权DBA给以上用户
grant dba to yc;

--连接该用户
conn yc/123;

--为员工管理系统建如下表:员工信息表、部门表、员工考勤表
create table emp_info (
no int primary key,
name varchar2(10) not null,
sex char(2),
bir date,
tel number(11,0),
indate date
)

create table dept(
no int primary key,
name varchar2(20)
)

create table kq(
no int,
name varchar2(10),
dept varchar2(20),
clockin date,
clockout date,
constraint fk_kq_emp_no foreign key(no) references emp_info(no)
)

--自习设计以上表(提供数据库设计文档)

自行设计
ER图

-- 修改表结构之添加约束
alter table emp_info add constraint pk_emp primary key(no);

数据类型

--date 日期和时间类型
select sysdate from dual:
15-4月-2000

to_char(参数) --将参数转换成为字符串
to_date(参数) --将字符串转换成为日期型格式

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from emp_info;

--拷贝表
create table 表名 as select 字段 from 表名2;

--拷贝表结构
create table 表名 as select 字段 from 表名2 where 不成立条件;

--事务create/drop/truncate
insert/update/delete
提交/回退

-- || 连接符
select 'abc'||name from emp_info;
select '员工编号：'||no||'员工姓名：'||name from emp_info;

--连接 concat()
select concat('hello','world') from dual;

--检查约束
create table t1(id int check(id>=1 and id<=100),name char(10));

check

日期向前推2月
select add_months(sysdate,-2) from dual;

日期向后推2天
select sysdate+2 from dual;

向后推2个小时
select sysdate+2/24 from dual;

select last_day(sysdate) from dual;

--四舍五入
select round(sysdate,'mm') from dual;

--离当前最近的下一个礼拜二
select next_day(sysdate,'星期二') from dual;

查询跟张三来自同一个地方的学生姓名

select a.name, a.address from stu a,stu b
where a.address= b.address
and b.name='张三';

字符串截断：substr()
字符串长度：length()
内容替换：replace()

select substr('hello',1,3),length('hello'),replace('hello','l','x') from dual;

日期-数字=日期
日期+数字=日期
日期-日期=数字（天数）

--当前日期
select sysdate from fual;

--求出星期数:当前日期数-生日日期=天数 /7=星期数
select no,name,round((sysdate-bir)/7) from emp;

作业：

--创建一个部门表
create table dept(
deptid int primary key,
deptname varchar2(20)
);

--创建一个员工信息表
create table emp(
empid int primary key check(empid>0),
deptid int not null,
name varchar2(10),
sex varchar(4) check(sex in ('男','女','未知')),
bir date,
height numeric(3,2) check( height>0 and height<3 ),
tel numeric(11) check(tel like '1%' and length(tel)=11 ) ,
gz numeric(7,2) check(gz between 5000 and 50000),
constraint fk_emp_dept_deptid foreign key(deptid) references dept(deptid)
);

--创建一个考勤表
create table kq(
empid int,
ontime date,
offtime date,
constraint fk_kq_emp_empid foreign key(empid) references emp(empid)
);

insert into dept(deptid) values(1);
insert into dept(deptid) values(2);
insert into dept(deptid) values(3);
update dept set deptname='开发部' where deptid=1;
update dept set deptname='测试部' where deptid=2;
update dept set deptname='运营部' where deptid=3;

commit

select * from dept;

insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(1,1,'西沉','男','15-4月-1998',1.80,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(2,1,'路飞','男','05-5月-2000',1.76,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(3,1,'索隆','男','11-11月-1998',1.83,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(4,2,'娜美','女','03-7月-1999',1.78,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(5,2,'乔巴','男','24-12月-2005',1.58,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(6,2,'山治','男','01-6月-1998',1.82,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(7,3,'乌索普','男','1-4月-2000',1.75,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(8,3,'弗兰奇','男','07-8月-1979',1.90,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(9,3,'布鲁克','男','12-12月-1937',1.92,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(10,1,'罗宾','女','04-5月-1989',1.81,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(11,2,'小樱','女','03-3月-2000',1.76,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(12,3,'鸣人','男','05-9月-2000',1.56,'18692391446',50000);

commit

select * from emp;

insert into kq(empid,ontime,offtime) values(1,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into kq(empid,ontime,offtime) values(2,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into kq(empid,ontime,offtime) values(3,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into kq(empid,ontime,offtime) values(4,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into kq(empid,ontime,offtime) values(5,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into kq(empid,ontime,offtime) values(6,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into kq(empid,ontime,offtime) values(7,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into kq(empid,ontime,offtime) values(8,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into kq(empid,ontime,offtime) values(9,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));

commit

select * from kq;

--取年份
select to_char(sysdate,'yyyy') from dual;

--查询1999年后出生且性别为女的员工信息并按出生年份降序排序
select * from emp
where to_char(bir,'yyyy')>1999
and sex='女'
order by to_char(bir,'yyyy') desc;

select * from emp
where extract(year from bir)>1999
and sex='女'
order by extract(year from bir) desc;

-- 取出给定范围的月数
select months_between(sysdate, date'2019-11-24') from emp;

--计算年龄
select floor(months_between(sysdate,bir)/12) from emp;

select floor((sysdate-bir)/365) from emp;

select to_char(sysdate,'yyyy')-to_char(bir,'yyyy') from emp;

select extract(year from sysdate)-extract(year from bir) from emp;

--查询年龄21岁以上员工信息并按员工编号和年龄降序排序
select * from emp
where floor(MONTHS_BETWEEN(sysdate,bir)/12)>21
order by empid,floor(MONTHS_BETWEEN(sysdate,bir)/12) desc;

select * from emp
where to_char(sysdate,'yyyy')-to_char(bir,'yyyy')>21
order by empid,to_char(sysdate,'yyyy')-to_char(bir,'yyyy') desc;

select * from emp
where floor((sysdate-bir)/365) >21
order by empid,floor((sysdate-bir)/365) desc;

-- ||字符串连接符
select '员工编号是：'||empid||',姓名是:'||name from emp;

--查询每个年龄员工的数量并按数量排序:年龄、数量
select floor((sysdate-bir)/366) as 年龄,count(*) as 数量 from emp
group by floor((sysdate-bir)/366)
order by 年龄,数量;

--查询工资最高的员工的员工编号、姓名、性别、年龄、工资
select empid,name,sex,floor((sysdate-bir)/365) from emp
where gz=(select max(gz) from emp);

--查询10月份各部门的迟到人数在2人以上的信息,并按人数降序排序:部门名称、迟到人数
select c.deptname as 部门名称,count(distinct a.empid) as 迟到人数 from kq a,emp b,dept c
where a.empid=b.empid and b.deptid=c.deptid
and to_char(a.ontime,'hh24:mi')>'08:20' and to_char(a.ontime,'mm')=10
group by c.deptname
having count(distinct a.empid)>=2
order by 迟到人数 desc;

--行号
select name,bir,rownum from emp where rownum<=5;

--排名
select row_number() over(order by sex desc),name,bir from emp;

-- 按身高排名
select empid,name,height,row_number() over(order by height) 名次 from emp;

--身高一样，名次相同
select empid,name,height,rank() over(order by height) 名次 from emp;

--身高一样，名次相同，不跳号
select empid,name,height,dense_rank() over(order by height) 名次 from emp;

--merge into

--树形结构查询
start with..connect by

select * from scott.emp start with empno=7369 connect by prior mgr=empno;

.人员表中增加上级编号字段,记录该员工的上级员工编号

.对人员表造树形结构数据，将数据更新为只有一个人的上级ID为空，
所有人的上级ID均为其他人，呈现出父子孙的多层关系数据
.将以上数据按照父、子、孙的先后关系查询呈现出来

.将人员表第一层员工的基本工资涨为20000,第二层涨1000,
第3层如果原来大于6000则增加500,否则增加400，其它层次一律涨200

select ename,sal,
decode(level,
1,20000,
2,sal+1000,
3,decode(sign(sal-6000),1,sal+500,sal+400),sal+200) x
from emp start with empno=7839 connect by prior empno=mgr;

select empno,empname,sal,level,
(case
when level=1 then 20000
when level=2 then sal+1000
when level=3 and sal>=6000 then sal+500
when level=3 and sal<6000 then sal+400
else sal+200
end)x
from emp start with empno=7839 connect by prior empno=mgr;

--多表关联更新
update emp a set sal=
(
select(case
when level=1 then 20000
when level=2 then b.sal+1000
when level=3 and b.sal>=6000 then b.sal+500
when level=3 and b.sal<6000 then b.sal+400
else b.sal+200
end)x from emp b where a.empno=b.empno
start with b.empno=7839 connect by prior b.empno=b.mgr
);

--创建一个部门表
create table dept(
deptid int primary key,
deptname varchar2(20)
);

--创建一个员工信息表
create table emp(
empid int primary key check(empid>0),
deptid int not null,
name varchar2(10),
sex varchar(4) check(sex in ('男','女','未知')),
bir date,
height numeric(3,2) check( height>0 and height<3 ),
tel numeric(11) check(tel like '1%' and length(tel)=11 ) ,
gz numeric(7,2) check(gz between 5000 and 50000),
constraint fk_emp_dept_deptid foreign key(deptid) references dept(deptid)
);

--创建一个考勤表
create table kq(
empid int,
ontime date,
offtime date,
constraint fk_kq_emp_empid foreign key(empid) references emp(empid)
);

insert into dept(deptid) values(1);
insert into dept(deptid) values(2);
insert into dept(deptid) values(3);
update dept set deptname='开发部' where deptid=1;
update dept set deptname='测试部' where deptid=2;
update dept set deptname='运营部' where deptid=3;

commit

select * from dept;

insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(1,1,'西沉','男','15-4月-1998',1.58,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(2,1,'路飞','男','05-5月-2000',1.76,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(3,1,'索隆','男','11-11月-1998',1.83,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(4,2,'娜美','女','03-7月-1999',1.78,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(5,2,'乔巴','男','24-12月-2005',1.58,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(6,2,'山治','男','01-6月-1998',1.82,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(7,3,'乌索普','男','1-4月-2000',1.75,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(8,3,'弗兰奇','男','07-8月-1979',1.90,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(9,3,'布鲁克','男','12-12月-1937',1.92,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(10,1,'罗宾','女','04-5月-1989',1.81,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(11,2,'小樱','女','03-3月-2000',1.76,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(12,3,'鸣人','男','05-9月-2000',1.56,'18692391446',50000);

commit

select * from emp;

insert into kq(empid,ontime,offtime) values(1,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into kq(empid,ontime,offtime) values(2,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into kq(empid,ontime,offtime) values(3,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into kq(empid,ontime,offtime) values(4,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into kq(empid,ontime,offtime) values(5,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into kq(empid,ontime,offtime) values(6,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into kq(empid,ontime,offtime) values(7,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into kq(empid,ontime,offtime) values(8,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into kq(empid,ontime,offtime) values(9,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));

commit

select * from kq;

--取年份
select to_char(sysdate,'yyyy') from dual;

--查询1999年后出生且性别为女的员工信息并按出生年份降序排序
select * from emp
where to_char(bir,'yyyy')>1999
and sex='女'
order by to_char(bir,'yyyy') desc;

select * from emp
where extract(year from bir)>1999
and sex='女'
order by extract(year from bir) desc;

-- 取出给定范围的月数
select months_between(sysdate, date'2019-11-24') from emp;

--计算年龄
select floor(months_between(sysdate,bir)/12) from emp;

select floor((sysdate-bir)/365) from emp;

select to_char(sysdate,'yyyy')-to_char(bir,'yyyy') from emp

select extract(year from sysdate)-extract(year from bir) from emp

--查询年龄21岁以上员工信息并按员工编号和年龄降序排序
select * from emp
where floor(MONTHS_BETWEEN(sysdate,bir)/12)>21
order by empid,floor(MONTHS_BETWEEN(sysdate,bir)/12) desc;

select * from emp
where to_char(sysdate,'yyyy')-to_char(bir,'yyyy')>21
order by empid,to_char(sysdate,'yyyy')-to_char(bir,'yyyy') desc;

select * from emp
where floor((sysdate-bir)/365) >21
order by empid,floor((sysdate-bir)/365) desc;

-- ||字符串连接符
select '员工编号是：'||empid||',姓名是:'||name from emp;

--查询每个年龄员工的数量并按数量排序:年龄、数量
select floor((sysdate-bir)/366) as 年龄,count(*) as 数量 from emp
group by floor((sysdate-bir)/366)
order by 年龄,数量;

--查询工资最高的员工的员工编号、姓名、性别、年龄、工资
select empid,name,sex,floor((sysdate-bir)/365) from emp
where gz=(select max(gz) from emp);

--查询10月份各部门的迟到人数在2人以上的信息,并按人数降序排序:部门名称、迟到人数
select c.deptname as 部门名称,count(distinct a.empid) as 迟到人数 from kq a,emp b,dept c
where a.empid=b.empid and b.deptid=c.deptid
and to_char(a.ontime,'hh24:mi')>'08:20' and to_char(a.ontime,'mm')=10
group by c.deptname
having count(distinct a.empid)>=2
order by 迟到人数 desc;

select name 姓名 from emp;

--行号
select name,bir,rownum from emp where rownum<=5;

--排名
select row_number() over(order by sex desc),name,bir from emp;

-- 按身高排名
select empid,name,height,row_number() over(order by height) 名次 from emp;

--身高一样，名次相同
select empid,name,height,rank() over(order by height) 名次 from emp;

--身高一样，名次相同，不跳号
select empid,name,height,dense_rank() over(order by height) 名次 from emp;