Oracle语法使用（一）

Oracle体系结构

1. 数据库
   Oracle 数据库是数据的物理存储。这就包括（数据文件 ORA 或者 DBF、控制文件、联机日志、参数文件）。其实 Oracle 数据库的概念和其它数据库不一样，这里的数据库是一个操作系统只有一个库。可以看作是 Oracle 就只有一个大数据库。

2. 实例
   一个 Oracle 实例（Oracle Instance）有一系列的后台进程（Backguound Processes)和内存结构（Memory Structures)组成。一个数据库可以有 n 个实例。

3. 用户
   用户是在实例下建立的。不同实例可以建相同名字的用户。

4. 表空间
   表空间是 Oracle 对物理数据库上相关数据文件（ORA 或者 DBF 文件）的逻辑映射。一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每个数据库至少有一个表空间(称之为 system 表空间)。
   每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件(datafile)。一个数据文件只能属于一个表空间。

5. 数据文件（dbf、ora）
   数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的，真正是在某一个或者多个数据文件中。而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行。
   注： 表的数据，是有用户放入某一个表空间的，而这个表空间会随机把这些表数据放到一个或者多个数据文件中。由于 oracle 的数据库不是普通的概念，oracle 是有用户和表空间对数据进行管理和存放的。
   但是表不是有表空间去查询的，而是由用户去查的。因为不同用户可以在同一个表空间建立同一个名字的表！这里区分就是用户了！

创建表空间
一个数据库下可以建立多个表空间，一个表空间可以建立多个用户、一个用户下可以建立
多个表。

create tablespace first
datafile ‘C:first.dbf’
size 100m
autoextend on
next 10m

first为表空间名称
datafile 指定表空间对应的数据文件
size 后定义的是表空间的初始大小
autoextend on 自动增长 ，当表空间存储都占满时，自动增长
next 后指定的是一次自动增长的大小

创建用户
oracle 数据库与其它数据库产品的区别在于，表和其它的数据库对象都是存储在用户下的。

Oracle 中已存在三个重要的角色：

CONNECT 角色： --是授予最终用户的典型权利，最基本的

RESOURCE 角色： --是授予开发人员的

DBA 角色：拥有全部特权，是系统最高权限，只有 DBA 才可以创建数据库结构，并且系统权限也需要 DBA 授出，且 DBA 用户可以操作全体用户的任意基表，包括删除

进入 system 用户下给用户赋予dba权限，否则无法正常登陆

create user itcastuser
identified by first //用户密码
default tablespace first //表空间名称
grant dba to itcastuser

创建表

create table person(
pid number(10),
name varchar2(10),
gender number(1) default 1,
birthday date );

修改表

alter table person add(addr varchar2(10);//增加列addr
alter table person modify(addr varchar2(20);//修改列长度
alter table person rename addr to address;//修改列名

数据库表数据的更新
必须执行commit语句

增加：INSERT INTO 表名[(列名 1，列名 2，…)]VALUES(值 1，值 2，…)

全部修改：UPDATE 表名 SET 列名 1=值 1，列名 2=值 2，…
局部修改：UPDATE 表名 SET 列名 1=值 1，列名 2=值 2，…WHERE 修改条件；

删除： DELETE FROM 表名 WHERE 删除条件;

序列 sequnce
实现主键自动增长功能，一般都是一张表用一个序列。

dual是虚表，因为from后面必须加表明，此处未用到表

create sequnce sequnceid;
select sequnceid.nextval from dual;//查看序列下一个内容
select sequnceid.currval from dual;//查看序列当前内容

插入数据需要自增主键即：

insert into person(pid,name) values(sequnceid.nextval,‘名字’);//先创建序列再引用

单行函数：1.字符函数 upper ，lower

select upper (‘smith’) from dual;//转大写
select lower (‘SMITH’) from dual;//转小写

2.数值函数 round

select round (12.534，2) from dual//四舍五入到小数后第两位 //12.53
select round (12.534, -2) from dual//判断到小数前第两位
//0 (54.55,-2)得到100

3.日期函数
查询员工入职到现在几天 ----sysdate当前日期

select sysdate-e.hiredate from emp e;

查询明天的这个时候

select sysdate+1 from dual;

查询出emp表中所有员工入职距离现在几月。
–months_between 按月份计算

select months_between(sysdate,e.hiredate) from emp e;
(sysdate,e.hiredate)/12 //查询至今几年

查询出emp表中所有员工入职距离现在几周。

select round((sysdate-e.hiredate)/7) from emp e;

4.转换函数 to_char , to_date

select to_char(sysdate, ‘fm yyyy-mm-dd hh24:mi:ss’) from dual; //日期转字符串
select to_date(‘2020-4-25 14:39:50’, ‘fm yyyy-mm-dd hh24:mi:ss’) from dual; //字符串转日期

5.通用函数 nvl ,case when then end
奖金里面有null值，如果null值和任意数字做算术运算，结果都是null。

select e.sal*12+nvl(e.comm, 0) from emp e;//为null取0值

条件表达式的通用写法，mysql和oracle通用
—给emp表中员工起中文名

select e.ename,
case e.ename
when ‘SMITH’ then ‘曹贼’
when ‘ALLEN’ then ‘大耳贼’
when ‘WARD’ then ‘诸葛小儿’
–else ‘无名’
end
from emp e;

—判断emp表中员工工资，如果高于3000显示高收入，如果高于1500低于3000显示中等收入，其余显示低收入

select e.sal,
case
when e.sal>3000 then ‘高收入’
when e.sal>1500 then ‘中等收入’
else ‘低收入’
end
from emp e;

----oracle中除了起别名，都用单引号。
----oracle专用条件表达式
select e.ename,
decode(e.ename,
‘SMITH’, ‘曹贼’,
‘ALLEN’, ‘大耳贼’,
‘WARD’, ‘诸葛小儿’,
‘无名’) “中文名”
from emp e;

多行函数 1.统计记录数 count()
----不建议使用 count(*)， 可以使用一个具体的列以免影响性能。 count(1)或者count(ename)

selcet count(1) from emp;//查询出所有员工的记录数

**2.最小，大，平均值查询 **

select min (sal) from emp;//最小值
max (sal) //最大值
avg (sal) //平均值

3.求和函数 sum()
查询出 20 号部门的员工的工资总和

select sun(sal) from emp t where t.deptno = 20;

分组统计 group by
查询每个部门的人数 ,select的要与分组条件的名字一样，否则报错。select后面有几个名字条件就要有几个，条件逗号相隔。

select deptno,count(ename) from emp group by deptno;

查询出部门人数大于 5 人的部门,所有条件都不能用别名来判断

select d.deptno , d.dname , count(ename) from emp.e ,dept d where e.deptno = d.deptno group by d.deptno,d.dname having count(ename) > 5;

用where是过滤分组前的数据，having是过滤分组后的数据。

—表现形式：where必须在group by之前，having是在group by之后。

—查询出每个部门工资高于800的员工的平均工资
—然后再查询出平均工资高于2000的部门

select e.deptno, avg(e.sal) asal from emp e where e.sal>800 group by e.deptno having avg(e.sal)>2000;

多表查询 1.等值连接
查询到两表编号相等的数据

select *from emp e, dept d where e.deptno=d.deptno;

2.内连接

select * from emp e inner join dept d on e.deptno = d.deptno;
—查询出所有部门，以及部门下的员工信息。没有关联的不显示

3.外连接

—查询所有员工信息，以及员工所属部门，右外连接就显示右边的部门数据和对应的员工数据，没有关联的部门数据也显示。

select * from emp e right join dept d on e.deptno=d.deptno
left join on //左外连接，显示全部的员工数据及对应的部门数据，没有对应的，连接上的显示null

—oracle中专用外连接,哪边外连接，(+)就在对面
select * from emp e, dept d where e.deptno(+) = d.deptno;

4.自连接
自连接其实就是站在不同的角度把一张表看成多张表。

select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno;

------查询出员工姓名，员工部门名称，员工领导姓名，员工领导部门名称

select e1.ename, d1.dname, e2.ename, d2.dname
from emp e1, emp e2, dept d1, dept d2
where e1.mgr = e2.empno
and e1.deptno=d1.deptno
and e2.deptno=d2.deptno;

5.子查询
子查询:在一个查询的内部还包括另一个查询，则此查询称为子查询。
在返回多条记录的子查询可以把它的结果集当做一张表，给起个别名

—查询出工资和SCOTT一样的员工信息

select * from emp where sal in (select sal from emp where ename = ‘SCOTT’)

—子查询返回一张表

—1，先查询出每个部门最低工资

select deptno, min(sal) msal from emp group by deptno;

—2，三表联查，查询出每个部门最低工资，和最低工资员工姓名，和该员工所在部门名称。

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

分页查询 rownum
----oracle中的分页
—rownum行号：当我们做select操作的时候，每查询出一行记录，就会在该行上加上一个行号，行号从1开始，依次递增，不能跳着走。

----排序操作会影响rownum的顺序

----如果涉及到排序，但是还要使用rownum的话，我们可以再次嵌套查询。

select rownum,t.* from(select rownum,e.* from emp e order by e.sal desc)t; //查询到所有的倒序工资的员工数据并赋予行号，从一开始

----emp表工资倒叙排列后，每页五条记录，查询第二页。

rownum行号不能写上大于一个正数,起别名方可。

select * from (select rownum rn，tt.* from(
select * from emp order by sal desc) tt where rownum<11)where rn>5;
//得到倒叙数据的行数小于11后数据，然后判断大于5的数据