oracle增删改查

------------------------------ 使用system/orcl用户登录操作----------------------
–1.oracle体系结构
--------全局数据库，这里指物理磁盘上的数据库（物理结构，一个真实存在的磁盘目录），一般一台oracle服务器有1个全局数据库，文件占1G多。oracle允许一台
-------- 一台电脑安装多个全局数据库，但是不建议这样做。因为一个全局数据库可以放所有的数据。
-------- 这里与mysql不一样，mysql是随意创建数据库。 一个全局数据库包含N多个数据文件。
--------用户，用户在oracle里面是来隔离数据的，mysql通过数据库为单位隔离数据，而oracle以用户为单位隔离数据。
--------表空间，是逻辑结构，用户的数据存放在表空间上的，表空间的数据是在数据文件上的。表空间可以在逻辑上
-------- 给数据文件划分多个区域，每个区域是一个表空间。数据文件的数据是有多个表空间组成。
--------数据文件，是全局数据库里面一个数据文件（物理结构，一个真实存在的磁盘文件）。一个全局数据库包含N多个数据文件。
--------数据库实例服务（数据库实例），是一个服务进程，用于执行sql命令，将变化更新到数据文件上。

–2.使用oracle存取数据步骤
–第1步骤：创建表空间
–创建语法：
create tablespace 表空间名字
datafile ‘磁盘数据文件地址’ size 数据文件大小
autoextend on next 自动增长的大小
–例子
create tablespace itheima91
datafile ‘c:\itheima91.dbf’ size 100m
autoextend on next 10m
–第2步骤：创建用户
–创建语法：
create user 用户名 identified by 密码
default tablespace 表空间名字;
–例子
create user ith identified by itheima91
default tablespace itheima91;

–使用新用户登录试一下：结果登录不了，因为权限不够
–第3步骤：授权
–语法1
grant 权限1,权限2,… to 用户名;
–语法2
grant 角色1,角色2,… to 用户名;
–角色，一个角色包含多个权限
–oracle系统内置3个就是，connect,resource,dba
-------connect角色，连接到oracle的权限
-------resource角色，操作自己的数据对象（创建自己的表，增删改查里面的数据），不可以操作其他用户数据
-------dba角色，超级管理员角色，当前system就是dba.拥有一切权限
–为了方便，将dba给到用户
grant dba to itheima91;

-------------------------以itheima91/itheima91登录进行如下操作-------------------------------------
select * from session_privs;–查看当前用户所属角色权限列表，dba拥有161个权限
SELECT * FROM USER_ROLE_PRIVS;–查看当前用户的所属角色

–3.表的管理
–创建表语法：
语法：
create table 表名(
字段1 数据类型 [default默认值]
字段2 数据类型 [default默认值]
…
字段n 数据类型 [default默认值]
);
–创建一个person表
create table person(
id number(8) primary key,
name varchar2(100) not null,
gender varchar2(2),
birthday date
);
–表删除
–方式1：删除表数据
delete from person;
–方式2：移出表结构
drop table person;

–表修改
–增加一个字段
alter table person add (address varchar2(100));
–修改字段名字
alter table person rename column address to address2;
–修改字段数据类型
alter table person modify (address2 varchar2(200));

–表的更新（增、删、改）

–删除
delete from person where id=2;

–修改
update person set address2=‘东莞’ where id=1;

–查询
select * from person;

–增加数据
insert into person(id,name,gender,birthday,address2)
values(1,‘张三’,‘男’,to_date(‘2018-01-01’,‘yyyy-MM-dd HH24:mi:ss’),‘广州’);

insert into person
values(2,‘李四’,‘女’,to_date(‘2018-01-01’,‘yyyy-MM-dd HH24:mi:ss’),‘深圳’);
–oracle有事物机制，需要对每条增、删、改数据手动提交事物。

–oracle没有默认自增长字段，需要序列解决
–序列，是oracle一个数据对象，专门用于管理生成有序的数字
–创建语法
create sequence 序列名字
[increment by 数字] --序列自增值，不写，默认1，每次递增1
[start width 数字] --设置序列的起始值，不设置默认从1开始
[maxvalue 最大值 | nomaxvalue] --设置最大值，最大值是28个9，也可以没有最大值，没有最大值的最大为27个9。默认是有最大值的。
[cache 数字 | nocache] --设置缓存的，当使用了当前序列值后，oracle内存会自动生成缓存个数数字以供使用。如果没有设置那么每次使用序列时在生成。默认灭有缓存
[cycle| nocycle] --设置是否循环，默认不循环。当前序列值达到最大值时就从新从起始值开始。

–简写方式
create sequence seq_person_id;
–复杂方式
create sequence seq_person_id
increment by 1
start width 1
cache 5 ;

–使用序列

select seq_person_id.nextval from dual; --获取或查看下一个序列值
select seq_person_id.currval from dual; --获取或查看当前序列值

–dual是虚拟的表，专门用于查询数据使用，因为oracle查询数据必须是select 字段 from 表，from是不能丢
select seq_person_id.nextval; --这样查询数据是错误的，因为oracle查询数据必须有from.mysql里面查询数据from不是必须的。

–所以以后insert语句使用序列
insert into person(id,name,gender,birthday,address2)
values(seq_person_id.nextval,‘王五2’,‘男’,to_date(‘2018-01-01’,‘yyyy-MM-dd HH24:mi:ss’),‘广州’);

select * from person order by id;

–修改scott密码为tiger

---------------------以scott/tiger用户登录进行操作--------------------------------------
–介绍表结构
select * from dept;–部门表
select * from emp;–员工表
select * from salgrade;–薪水级别表

–4.单行函数，专门用于查询每一行使用的函数
–包含，字符函数、数值函数、日期函数、转换函数、通用函数等5类

–1.字符函数
–1.1 把小写的字符转换成大写的字符
select upper(‘abc’) from dual;
–1.2 把大写字符变成小写字符
select lower(‘ABC’) from dual;
–1.3 字符串替换函数
select replace(‘abc hello world’,‘hello’,‘world’) from dual;
–2.数值函数
–2.1 四舍五入函数,round(数字,n),n代表保留数字的几位小数，n不写保留整数
select round(123.456) from dual --123
select round(123.456,2) from dual --123.46

–3.日期函数
关键字：sysdate,获取系统当前时间
select sysdate from dual;
公式
日期+数字=日期，数字单位是天
日期-数字=日期，数字单位是天
日期-日期=数字，单位是天
–sql起别名
select ‘abc’ as “名字” from dual
select ‘abc’ as 名字 from dual
select ‘abc’ 名字 from dual
–范例：查询雇员进入公司的天数。
select empno,ename,hiredate,round(sysdate-hiredate) 入职天数 from emp;

–范例：查询雇员进入公司的周数。
select empno,ename,hiredate,round((sysdate-hiredate)/7) 入职周数 from emp;

–范例：查询雇员进入公司的月数。
– 计算2个日期差值返回月数 months_between(日期1，日期2)，
select empno,ename,hiredate,round(months_between(sysdate,hiredate)) 入职月数 from emp;

–范例：查询雇员进入公司的年数。
select empno,ename,hiredate,round(months_between(sysdate,hiredate)/12) 入职年数 from emp;

–4.转换函数
时间格式：fmyyyy-MM-dd HH:mi:ss
yyyy/YYYY,年
mm/MM,月
DD/dd,日
hh/HH,代表12小时制
hh24/HH24,代表24小时制
mi,分钟
ss,秒
fm，去掉补0数字
–4.1 TO_CHAR:日期转换字符串函数
select to_char(hiredate,‘fmyyyy-MM-dd hh24:mi:ss’) from emp;
–4.2 TO_DATE:字符串转日期函数
select to_date(‘2018-01-01’,‘yyyy-mm-dd hh24:mi:ss’) from dual;

–5.通用函数
–5.1 空值处理nvl
–语法：nvl(字段,当前字段为空null返回的值)
–计算所有员工年薪
select empno,ename,sal12+comm 年薪 from emp; --问题，comm为null之后与其他数字相加后依然是null
–将comm如果null返回0
select empno,ename,sal,comm,sal12+nvl(comm,0) 年薪 from emp;
–5.2 Decode 函数,用于条件判断
语法：decode(字段,‘aa’,‘当字段值为aa时返回的值’,‘bb’,‘当字段值为bb时返回的值’,…,‘以上都不符合返回的值’)
–将职位转换为中文
select empno,ename,job,decode(job,
‘CLERK’,‘业务员’,
‘SALESMAN’,‘销售员’,
‘MANAGER’,‘经理’,
‘ANALYST’,‘分析师’,
‘PRESIDENT’,‘总裁’,
‘无业’
) 职位 from emp;
–5.2 case when，用于调节判断
语法：case 字段
when 值1 then 当字段值为值1时返回的值
when 值2 then 当字段值为值2时返回的值
…
else 以上都不符合返回的值
end

–实现上面功能

select empno,ename,job,case job
when ‘CLERK’ then ‘业务员’
when ‘SALESMAN’ then ‘销售员’
when ‘MANAGER’ then ‘经理’
when ‘ANALYST’ then ‘分析师’
when ‘PRESIDENT’ then ‘总裁’
else ‘无业’
end 职位 from emp;

– 5.多行函数
–范例：查询出所有员工记录数
select count(*) from emp;
–范例：查询出来员工最低工资
select min(sal) from emp;

–范例：查询出员工的最高工资
select max(sal) from emp;

–范例：查询出员工的平均工资
select avg(sal) from emp;

–范例：查询出 20 号部门的员工的工资总和
select sum(sal) from emp where deptno=20;

–6.分组统计
–sql语句关键字执行顺序：from where group select having order by
–范例：查询出每个部门的平均工资
select deptno,round(avg(sal)) from emp group by deptno;
–范例：查询每个部门的人数
select deptno,count(*) from emp group by deptno

–范例：按部门分组，查询出部门名称和部门的员工数量
select dept.deptno,dept.dname,count(*) from emp,dept where emp.deptno=dept.deptno group by dept.deptno --错误的

–上面的sql语句，oracle里面执行报错，mysql执行成功
–oracle分组常见错误说明，oracle分组要求如下：
–1.分组sql语句中select查询字段只能2个部分组成，分组函数和分组字段。（聚合函数都是分组函数）
select emp.deptno,count(*) from emp,dept where emp.deptno=dept.deptno group by dept.deptno --错误的
–2.分组的字段与查询的字段必须是同一个表同一个字段

–所有优化
select dept.deptno, dept.dname, count()
from emp, dept
where emp.deptno = dept.deptno
group by dept.deptno,dept.dname
–范例：查询出部门人数大于 5 人的部门
select dept.deptno, dept.dname, count()
from emp, dept
where emp.deptno = dept.deptno
group by dept.deptno,dept.dname
having count(*)>5

–范例：查询出部门平均工资大于 2000 的部门
select dept.deptno, dept.dname, avg(sal)
from emp, dept
where emp.deptno = dept.deptno
group by dept.deptno,dept.dname
having avg(sal)>2000

– 7.多表查询

–包含：内连接和外链接

–多表查询
–范例：查询员工表和部门表
–emp表14条数据
select * from emp;
–dept表4条数据
select * from dept;

select * from emp,dept order by emp.empno --56条，笛卡尔积，每个表的所有数据都会与另一个表所有数据连接一次。

–去掉笛卡尔积的冗余数据，使用表连接（内连接和外链接）

–范例：查询出雇员的编号，姓名，部门的编号和名称，地址
select e.empno,e.ename,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno

–范例：查询出每个员工的上级领导
–(员工编号、员工姓名、员工部门编号、员工工资、领导编号、领导姓名、领导工资)
select e1.empno, e1.ename, e1.deptno, e1.sal, e2.empno, e2.ename, e2.sal
from emp e1, emp e2
where e1.mgr = e2.empno

–范例: 在上一个例子的基础上查询该员工的部门名称
select e1.empno, e1.ename, e1.deptno, e1.sal,d1.dname, e2.empno, e2.ename, e2.sal
from emp e1, emp e2,dept d1
where e1.mgr = e2.empno and e1.deptno=d1.deptno

–范例：在上一个例子的基础上查询出每个员工 工资等级和他的上级领导工资等级
select e1.empno, e1.ename, e1.deptno, e1.sal,d1.dname,s1.grade, e2.empno, e2.ename, e2.sal,s2.grade
from emp e1, emp e2,dept d1,salgrade s1,salgrade s2
where e1.mgr = e2.empno
and e1.deptno=d1.deptno
and e1.sal between s1.losal and s1.hisal
and e2.sal between s2.losal and s2.hisal

–范例：查询出所有员工的上级领导
–上面内连接，发现少了一个员工和所属领导，因为总裁没有上级领导，所以少了。所以必须采用外链接解决
–外链接：左连接=左外连接，右连接=右外连接
select e1.empno, e1.ename, e1.deptno, e1.sal, e2.empno, e2.ename, e2.sal
from emp e1
left join emp e2 on e1.mgr=e2.empno
–上面的sql标签外连接方式，oracle还有自己独有方式
select e1.empno, e1.ename, e1.deptno, e1.sal, e2.empno, e2.ename, e2.sal
from emp e1, emp e2
where e1.mgr = e2.empno(+)
–范例：查询出所有的部门下的员工，要求把没有员工的部门也展示出来
select d.deptno,e.* from dept d
left join emp e on d.deptno = e.deptno

select d.deptno,e.* from dept d,emp e where d.deptno = e.deptno(+)

– 8.子查询
---------子查询- select---------
–查询比SCOTT工资高的员工
select SAL from emp where ename=‘SCOTT’
select * from emp where sal>3000
–优化，使用子查询
select * from emp where sal>(select SAL from emp where ename=‘SCOTT’)

–查询工资最低的员工
select * from emp where sal=(select min(sal) from emp)

–查询出和scott同部门并且同职位的员工
select * from emp
where job = (select job from emp where ename=‘SCOTT’)
AND deptno=(select deptno from emp where ename=‘SCOTT’)
and ename!=‘SCOTT’
– ORACLE不等于 != 或者 <>
–上面子查询可以优化
select * from emp
where (job,deptno) = (select job,deptno from emp where ename=‘SCOTT’)
and ename!=‘SCOTT’
–查询每个部门的最低工资和最低工资的员工和部门名称

select t.,e.,d.dname from (
select min(sal) minSal,deptno from emp group by deptno
) t,emp e,dept d
where t.deptno=e.deptno and t.minSal=e.sal and t.deptno=d.deptno

–子查询好处
----1.子查询比多表连接性能高
----2.子查询可以返回一个字段，多个字段
–emp表10条，dept表10条
select emp.,dept. from emp,dept where emp.deptno=dept.deptno and dept.deptno=20 --这个sql语句是连接100万次，去掉笛卡尔积

–20号部门只有1个员工，部门表10万条
select emp.,dept. from (select * from emp where deptno=20),dept where emp.deptno=dept.deptno ----这个sql语句是连接10万次，

----------分页
–员工数据分页查询，每页显示5条，查询第一页、第二页数据、第三页
–oracle没有limit，oracle有伪列rownum，可以给每条数据加序号
select emp.,rownum from emp;
–查询第一页
select emp.,rownum from emp where rownum<6;
–查询第二页
select emp.,rownum from emp where rownum>5 and rownum<11; --错误的，因为伪列rownum不可以使用大于号
–可以将伪列转换为真实列，之后再使用大于号
select * from (
select emp.,rownum rowno from emp
) t where t.rowno>5 and t.rowno<11

–第三页
select * from (
select emp.*,rownum rowno from emp
) t where t.rowno>10 and t.rowno<16

–按照工资由高到低查询第一页第二页
select t1.,rownum rowno from (
select emp. from emp order by sal desc
) t1

– 第一页
select * from (
select t1.,rownum rowno from (
select emp. from emp order by sal desc
) t1) t2 where t2.rowno>0 and t2.rowno<6

– rownum 不能直接使用*，必须表名.*