Oracle笔记(包含与MySQL的对比)

Oracle数据库
一、数据库
数据库:DB
关系型数据库:描述元素之间关系，组织
数据到二维表
二维表:行(记录)和列(字段)

二、SQL语言
所有关系型数据库使用相同或相似语言

数据定义语言 DDL 结构
create\alter\drop\truncate

数据操纵语言 DML 数据
insert\update\delete

事务处理语言 TPL 结合DML
commit\rollback

数据查询语言 DQL 查询(重点)
select

数据控制语言 DCL 权限
grant\revoke\create user

三、oracle常用数据类型
数值
number(p,s):p表示总位数，s表示小数位数

字符串(必须加单引号)
char(n):定长

varchar2(n):变长

日期
date
oracle默认日期格式 ‘DD-MON-YY’
形如:‘18-8月-20’

四、客户端操作
//打开
oracle 11g->sql plus

//登录
用户名:scott
密码:1234

//查看当前用户
show user;

//当前用户下有哪些表
select table_name from user_tables;

//表结构
desc 表名;

//查看系统时间
//默认日期格式’DD-MON-YY’
select sysdate from dual;//‘17-8月-20’

//修改当前会话默认日期格式
//当前会话有效
alter session
set nls_date_format=‘yyyy-mm-dd’;

五、表结构操作
//创建部门表、员工表 (数据类型)
create table dept_xu(
deptno number(2),
dname varchar2(10),
location varchar2(12)
);

create table emp_xu(
empno number(4),
ename varchar2(10),
position varchar2(16),
salary number(7,2),
bonus number(5,2),
hiredate date,
leader number(4),
deptno number(2)
);

//修改结构(了解)
增加字段
alter table dept_xu
add des varchar2(20);

修改字段
alter table dept_xu
modify des char(20);

删除字段
alter table dept_xu
drop (des);

//删除表(彻底)
drop table dept_xu;

六、表数据操作
//增加数据
语法结构
字符串必须加单引号

insert into 表 values(值1,值2);

insert into 表(字段1,字段2)
values(值1,值2);

插入数据时，时间类型数据必须考虑
格式匹配问题(
当前会话格式<->日期数据格式)

alter session set
nls_date_format=‘yyyy-mm-dd’;

//数据一行显示 默认80 当前会话有效
set linesize 200;

//更新数据
update 表 set 字段=值 where 条件;

//删除数据
delete from 表 where 条件;

七、基础查询
1)简单查询
select * from emp_xu;

select empno,ename
from emp_xu
where empno=1004;

2)空值
总结:
a.任何数据类型可以取空值(插入数据)
b.空值参与算术运算结果为空(空值处理)
c.空值参与连接(||)结果相当于空值
不存在(没有影响)

select ename,salary,bonus,
salary+bonus month_salary
from emp_xu;

3)空值处理函数
nvl(e1,e2):e1为空e2代替 e1和e2数据
类型必须一致

select ename,salary,bonus,
salary+nvl(bonus,0) month_salary
from emp_xu;

4)连接操作 ||
select ename || bonus message
from emp_xu;

5)去重 distinct
select distinct position
from emp_xu;

6)大小写
SQL语句不区分大小写，数据(单引号内容)
区分大小写

upper(‘a’):转大写
lower(‘b’):转小写

SELECT ENAME,position
from emp_xu
where lower(position)=‘analyst’;

7)in和not in使用
in(列表项):判断等于列表项中任意一项

in等价于=any
select ename,position
from emp_xu
where position =any(‘Analyst’,‘Clerk’,null);

not in(列表项):判断不等于所有项
列表项中空值必须去掉

select ename,deptno
from emp_xu
where deptno not in(10,20,null);

8)模糊查询
like
_表示1个字符
%表示0到多个字符

9)空值判断
肯定:is null
否定:is not null

10)数字函数
round(数字,小数位):四舍五入
trunc(数字,小数位):截取

select trunc(123.456,2) from dual;

11)日期函数
sysdate:当前系统时间
months_between(d1,d2):两个日期相隔月份
add_months(d,n):日期上增加月份
last_day(d):当前月份的最后一天

select ename,
round(
months_between(sysdate,hiredate)) months
from emp_xu;

select add_months(sysdate,2)
from dual;

select last_day(sysdate)
from dual;

12)转换函数
to_char(日期,格式):将日期数据按照指定
日期格式转成日期字符数据

select to_char(sysdate,
‘yyyy-mm-dd hh24:mi:ss’)
from dual;

to_date(字符,格式):转成日期数据

select to_date(‘17-8月-20’,
‘DD-MON-YY’)
from dual;//转换后得到date数据，日期
显示格式按照当前会话日期格式显示

13)组函数
count(*)
max(列名)
min(列名)
数值
sum(列名)
avg(列名)

//select后面数据要么被组函数包围要么
出现在group by后面
select ename,max(salary)
from emp_xu;//oracle报错

select deptno,max(salary)
from emp_xu
group by deptno;

14)排序
先有结果集再排序
order by 列 asc|desc

15)分组
group by 列 having 条件

//查询平均薪水大于5000部门，按照平均
薪水排序
select deptno,
avg(nvl(salary,0)) avg_salary
from emp_xu
where deptno is not null
group by deptno
having avg(nvl(salary,0))>5000
order by avg_salary desc;

16)基础查询
执行过程:
from->where->group by->having->
select->order by

17)子查询
非关联子查询:
select ename,salary
from emp_xu
where salary=(
select max(salary)
from emp_xu
);
a.执行过程
b.比较符选择
c.多值多列

关联子查询:
select ename,salary,deptno
from emp_xu e
where salary<(
select avg(nvl(salary,0))
from emp_xu
where deptno=e.deptno
);

exists:判断满足某种关系

select empno,ename
from emp_xu e
where exists(
select 1 from emp_xu
where leader=e.empno
);
select leader from emp_xu;

18)表间关联查询
内连接:
表1(驱动表) join 表2(匹配表) on 条件
遍历驱动表在匹配表中找匹配记录
保留匹配上的记录

外连接:
严格区分哪个表是驱动表

匹配上的记录保留，匹配不上的记录
匹配一行空行 

左外连接
右外连接
全外连接
表1 full join 表2 on 条件

insert into emp_xu(empno,ename)
values(1013,'张三');
insert into dept_xu(deptno,dname)
values(50,'后勤部');
commit;

//两个表中数据全部出现在全外连接
的结果集中
select ename,dname
from emp_xu e full join 
dept_xu d 
on e.deptno=d.deptno;

八、oracle排名分页
关键字:rownum

rownum是一个伪列，对查询返回数据行
进行编号，从1开始。

//查询员工表中前5条记录
select empno,ename,rownum
from emp_xu
where rownum<6;

//查询员工表中第4、第5、第6条记录
//rownum 不能用大于号
select empno,ename,rownum
from emp_xu
where rownum>3 and rownum<7;

//集合(union合集/差集minus/交集intersect)
select empno,ename
from emp_xu
where rownum<7
minus
select empno,ename
from emp_xu
where rownum<4;

select empno,ename
from
(select empno,ename,rownum rn
from emp_xu
where rownum<7)
where rn>3;

实际应用:
select empno,ename,position
from(
select e.*,rownum rn
from emp_xu e)
where rn between 1 and 3;

//每页显示5条记录
第1页 1-5
2 6-10
3 11-15

公式:
page:第几页
pageSize:每页的记录数

int beginValue=(page-1)pageSize+1;
int endValue=pagepageSize;

九、约束条件
主键 primary key
不能重复+不能为空
oracle主键不支持自增

非空 not null

唯一 unique

外键 foreign key

检查 check

//演示
create table stu_xu(
id number(4) primary key,
name varchar2(10) not null,
email varchar2(20) unique,
sex char(1) check(sex in(‘F’,‘M’))
);

insert into stu_xu
values(1001,‘张三’,‘123@qq.com’,‘F’);

十、oracle常用对象
1)表 table

2)视图 view
视图没有数据，视图是表的投影
视图隐藏不需要列简化查询

select empno,ename
from emp_xu
where deptno=20;

//创建视图
create view emp_view as
select empno,ename
from emp_xu
where deptno=20;

//查看视图
select * from emp_view;

//删除视图
drop view emp_view;

补充:
权限不足
//最高权限者
sqlplus /as sysdba

//给scott分配创建视图权限
grant create view to scott;

//回收权限
revoke create view from scott;

//连接scott
conn scott;

3)索引 index
加速数据库查询，提高查询效率

//显示语句执行时间
//当前会话有效
set timing on;

select empno,ename from emp_xu;

4)序列 sequence
序列是一种用于生成唯一数字值的数据库
对象，序列的值按照递增或递减自动
生成。

通常用来自动生成表的主键值(oracle),
序列是一种高效率获取唯一主键值的
途径。

特性:
序列产生连续不同的数字值，序列是
数据库独立对象，表可以用序列产生的
值作为主键值，也可以不同。
序列可以为一个表或多个表产生主键值，
建议一个序列为一个表产生主键值。

//创建第一个序列
//默认从1开始，步进为1，递增
create sequence first_sequence;
//测试
select first_sequence.nextval
from dual;
select first_sequence.currval
from dual;

//创建第二个序列
//从100开始步进为10递增
create sequence second_sequence
start with 100
increment by 10;
//测试
select second_sequence.nextval
from dual;
select second_sequence.currval
from dual;

//第三个序列
create sequence third_sequence
start with 100
maxvalue 100
increment by -10;
//测试
select third_sequence.nextval
from dual;
select third_sequence.currval
from dual;

演示:
利用序列产生值作为表的主键值

//创建表
create table temp(
id number(4) primary key,
name varchar2(10)
);

//创建序列
create sequence temp_sequence
start with 1000;

//插入数据
insert into temp
values(temp_sequence.nextval,‘张三’);
insert into temp
values(temp_sequence.nextval,‘李四’);

//查看
select * from temp;//1001 1002

ps:
利用序列产生的值插入表中，
存在’延迟段’特性，序列会自动跳过
第一个值

Mysql和Oracle对比(不同地方)

1)数据类型
mysql:int\double\char\varchar\date
oracle:number\varchar2\date

2)客户端操作
mysql:
root\1234
show databases;
use jsd;
create database jsd
default character set utf8;
set names gbk;
show tables;
desc 表名;
select now();
select user();
oracle:
scott\1234
show user;
select table_name from user_tables;
desc 表名;
select sysdate from dual;
alter session set
nls_date_format=‘格式’;
set timing on;
set linesize 200;

3)空值
mysql:
数据类型
算术运算结果空(ifnull(e1,e2))
连接(concat())
oracle:
数据类型
算术运算结果空(nvl(e1,e2))
连接(||)没有影响

4)大小写
mysql:默认不区分 binary标识

oracle:SQL语句不区分 数据区分

5)函数(单行函数、组函数)
mysql:
//不报错
select ename,max(salary)
from emp_xu;

oracle:
//直接报错
select ename,max(salary)
from emp_xu;

6)集合操作
mysql:
union\union all 不去重
oracle:
union 去重 排序\union all 不去重不排序
minus 差集
intersect 交集

7)分页
mysql:limit
select * from emp_xu limit ?,?;

oracle:rownum
select *
from (
select e.*,rownum rn
from emp_xu e
)
where rn between ? and ?;

8)约束条件
mysql:
primary key\not null\unique\foreign key

oracle:
多一个check约束

9)主键自增
mysql:
支持主键自增
primary key auto_increment

oracle:
不支持主键自增
利用序列对象来实现

10)日期转换
mysql:
date_format(日期,
‘%X-%m-%d %H:%i:%s’)
oracle:
to_date(‘日期’,
‘yyyy-mm-dd hh24:mi:ss’)

11)分组
mysql:
//不报错 数据有问题 group_concat()
select deptno,position
from emp_xu
where deptno is not null
group by deptno;

oracle:
//直接报错
select deptno,position
from emp_xu
where deptno is not null
group by deptno;

12)事务
mysql:
自动提交
在innodb(默认)存储引擎级别情况下
可支持事务

oracle:手动提交 完全支持事务

13)SQL语句
mysql:sql有很多扩展
分页limit
select now();
insert可以同时插入多行数据

oracle:sql显得稳重和传统