oracle基础操作

oracle

一.概念

字符串/字符 、日期 ： 单引号

1.大小写问题：

​ a.命令/关键字：不敏感（不区别）
​ b.数据：敏感（区分)

2.运算符：

​ 操作运算符： + - / * %
​ 关系运算符： > >= < <= = !=或<>
​ 如果是null，必须用is ，或is not
​ 逻辑运算福： or and not

3.null:

​ is/is not
​ null的计算：
​ 任何数字 和null结算，结果为null

​ 需要对null进行处理：null->0
​ nvl:if
​ nvl(comm,0 )

4.distinct:对查询出的结果集去重

例：select distinct deptno from emp

5.修改oracle默认的日期格式 ：

默认：DD-MON-RR
修改：
alter session set NLS_DATE_FORMAT = ‘yyyy-mm-dd’ ;

6.显式转换：

字符->字数
select to_number(‘￥123,456.7’, ‘L999,999.9’) from dual ;

字数->字符

字符-日期

日期-字符
to_char

7.多行函数：组函数、 聚合函数：

count/max/min/avg/sum

8.SQL语句的类型：

DQL：数据查询语言select
DML：数据操作语言,insert delete update --》可以回退（可以进行事务操作）
DDL ：数据定义语言 create/drop /truncate /alter table
DCL:数据控制语言 grant, revoke

二.操作

1.创建emp（员工表） 和 DEPT（部门表）

CREATE TABLE EMP(
EMPNO NUMBER(4),
ENAME VARCHAR(20),
JOB VARCHAR(10),
HIREDATE DATE,
SAL NUMBER(4),

DEPTNO NUMBER(5),

PRIMARY KEY(EMPNO)
);

create table dept(

deptno NUMBER(5),

dname varchar(10),

primary key(deptno)

);

2.插入数据（当前时间在oracle 当中是sysdate）

INSERT INTO EMP VALUES(001,‘张三’,‘保洁’,sysdate,‘90’,601);
INSERT INTO EMP VALUES(002,‘李四’,‘保洁’,sysdate,‘90’,601);
INSERT INTO EMP VALUES(003,‘王五’,‘保安’,sysdate,‘100’,603);
INSERT INTO EMP VALUES(004,‘刘六’,‘电工’,sysdate,‘150’,606);
INSERT INTO EMP VALUES(005,‘葛二蛋’,‘跑腿’,sysdate,‘60’,602);

INSERT INTO EMP VALUES(006,‘龟孙’,‘保安’,sysdate,100,‘603’);

3.全表查询

SELECT * FROM EMP;

4.更新语句（把员工号为005的员工的部门编号改为607）

UPDATE EMP SET DEPTNO = 607 WHERE EMPNO = 5;

5.分组：group by

查询各个部门的平均工资： SELECT DEPTNO , AVG(SAL) FROM EMP GROUP BY DEPTNO;

6.where 和 having

对行筛选用where

对组进行筛选用Having

可以在Having使用多行函数count min avg
但是 不能在where中使用多行函数

例：查询各个部门的平均工资高于90的部门编号（其实就是先查各个部门平均薪资，然后使用having再找高于90的）

SELECT DEPTNO , AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) > 90;

7.any：“只需要满足一个即可，存在一个就可以”

例如以下两条语句是等价的：

select *from emp where sal > any(select sal from emp) ;

select *from emp where sal > (select min(sal) from emp) ;

8.all：全部“必须满足所有”

例如以下两条语句是等价的：

select *from emp where sal > all(select sal from emp) ;
select *from emp where sal > (select max(sal) from emp) ;

9.in:在某些范围内（多行操作符，在子查询中常用）

例：查询部门编号为601和607的员工信息

select *from EMP where DEPTNO in (601,606);

10：子查询（子查询的结果中不要有NULL！！）

例：查询保洁部，跑腿部的员工信息

select * from emp where deptno in (select deptno from dept where dname=‘保洁’ or dname=‘跑腿’);

例：查询工资大于张三的所有员工信息

select *from emp where sal > (select SAL from emp where ename = ‘张三’ )；

11.多表连接查询：

1.交叉连接（笛卡尔积）:所有情况的组合 ，不推荐使用

select * from emp ,dept ;

2.内连接 ：多张表通过 相同字段进行匹配，只显示匹配成功的数据

a.
select * from emp e ,dept d
where e.deptno = d.deptno ;
b.
select * from emp e
inner join dept d
on e.deptno = d.deptno
不等值连接（一般不用）
select * from emp e ,dept d
where e.deptno <= d.deptno ;

3.外连接

左外连接：以左表为基准（左表数据全部显示），去匹配右表数据，如果匹配成功 则全部显示；匹配不成功，显示部分(无数据部分 用NULL填充)
a.（oracle独有）
select * from emp e ,dept d
where e.deptno = d.deptno(+) ;

b.
select * from emp e
left outer join dept d
on e.deptno = d.deptno

右外连接
右外连接：以右表为基准（右表数据全部显示），去匹配左表数据，如果匹配成功 则全部显示；匹配不成功，显示部分(无数据部分 用NULL填充)
a.（oracle独有）
select * from emp e ,dept d
where e.deptno(+) = d.deptno;

b.
select * from emp e
right outer join dept d
on e.deptno = d.deptno

全外连接 = 左外 + 右外连接 - 去重

三.DML：数据操作语言,insert delete update --》可以回退（可以进行事务操作）

增加数据insert

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

字段名和字段值一一对象：数据类型、个数、顺序

insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
values(2222,‘zhangsan’,‘MANAGER’,7788,‘19-9月 -88’,9998,1000,10);

可以省略字段名：

1.values插入的数据 必须是 完整的字段，并且顺序和默认顺序一致
2.目前使用的SQL99标准，可以省略字段名（如果是SQL92则不能省略,MyCat）
insert into emp
values(3322,‘LISI’,‘MANAGER’,7788,‘19-9月 -88’,9998,1000,10);

如果插入的数据不完整，可以协商 部分字段名

insert into emp(EMPNO,ENAME,JOB)
values(444,‘zhan’,‘MANAGER’);

动态输入插入的值(&) Scanner input = new Scanner(System.in); input.next();

insert into emp(EMPNO,ENAME,JOB)
values(&empno,&xxx,&job);
如果是字符、日期： 仍然需要加’ ’

insert into emp(EMPNO,ENAME,&otherName)
values(‘5555’,‘kkk’,&otherValue);

批量插入数据

1.创建新表(批量插入之前不存在)

​ emp ->复制 mytab

把emp表所有数据都批量插入mytab表

CREATE TABLE mytab
AS
SELECT * FROM emp;

把emp表部分字段数据都批量插入mytab表

CREATE TABLE mytab2
AS
SELECT ename , job FROM emp;

把薪资小于100的员工某些字段的信息导入mytab3表

create table mytab3
as
select empno,ename ,job from emp
where sal < 100;

还可以用于快速创建表结构：（创建一个mytab4空表，但字段与emp表一致）

create table mytab4
as
select *from emp where 1=0 ;

使用begin…end

begin
insert into emp
values(1221,‘LISI’,‘MANAGER’,7788,‘19-9月 -88’,9998,1000,10);
insert into emp
values(1223,‘LISI’,‘MANAGER’,7788,‘19-9月 -88’,9998,1000,10);
end ;

删除delete

delete from 表名 ;
delete from emp where empno >7900;
1.加where
2. delete from 表名 ;

全表删除：

1. delete from emp ; 可以回退
   truncate table emp ;不能回退
   原因： DML：insert update delete ->可以回退, 而truncate属于DDL

2.测试二者执行时间
打开执行时间：
set timing on/off
对于少量数据： delete 效率高 ，一行一行删除
对于海量数据：truncate效率高 ， a.drop table 丢弃整张表 ，b.重新创建表

3.delete支持闪回， truncate不支持闪回
4.delete不会释放空间 （换两个地方存储数据[undo空间]），trucante会
5.delete会产生碎片，truncate不会
如果碎片太多，需要整理碎片：a. alter table 表名 move ; b.导出导入

修改update

update 表名 set 字段名1=字段名1 , 字段名2=字段名2,字段名3=字段名3… where …

update emp set ename = ‘x’ ,job =‘y’ where empno>7900;

四.DDL：数据定义语言 create/drop /truncate /alter table

创建表

create table mytab6
(
id number ,
name varchar(10),
age number
)
;
注意事项：
1.权限和空间问题
2.表名的规定：
a.必须以字母开头
b.表名只能包含： 大小写字母、数字、_、$、#
c.长度 1-30个字符
d.不能与数据库中其他对象重名(表，视图、索引、触发器、存储过程…)
e.不能与 保留字重名
查看保留字：DBA账户
sqlplus / as sysdba

查看保留字:

​ select *from v$reserved_words order by keyword asc ;

设置某个字段的宽度：

字符
col KEYWORD for a10
数字
col LENGTH for 9999

修改表:

a.追加新列

alter table mytab6 add myother varchar2(10) ;

b.修改列
修改列的长度
alter table mytab6 modify myother varchar2(20) ;
修改列的类型

	alter table mytab6 modify  myother number ;
注意： blob/clob不能修改  ->先删除此列，重新追加

	alter table mytab6 add myother2 blob ;

	alter table mytab6 modify  myother2 number ;

c删除列
alter table mytab6 drop column myother2 ;

d重命名列
alter table mytab6 rename column myother to myother3 ;

删除表
select *from tab; 表以及回收站中的表
drop table mytab6; -->放在了回收站
查看回收站
show recyclebin;

清空回收站
purge recyclebin;

还原回收站
闪回

删除表 并清空： drop table test02 purge ;

五.事务

概念：作为单个逻辑工作单元执行的一系列操作
四大特性:ACID

转账：
zs->ls
1000

:update : zs -1000
ls +1000

Atomicity原子性：要么都成功，要么都失败。


Consistency:一致性 :事务执行前后 ，总量保持一致


Isolation隔离性:各个事务并发执行时，彼此独立



Durability:持久性：持久化操作。

事务的生命周期：
（MySQL: 自动提交，自动将每一条DML语句直接commit ）
Oracle:手工提交
事务的开始标识： 第一条DML
事务的中间过程： 各种DML操作
结束：
a.提交
i.显示提交：commit
ii.隐式提交（自动提交）：正常退出exit（ctrl+c）、DCL(grant …to…, revoke …from )、DDL(create … ,drop …)

	b.回滚
		i.显示回滚：rollback
		ii.隐式回滚：异常退出（宕机、断电）

保存点savepoint:
打游戏： 10 ： 1 ,2(savepoint) , 3,4,5 (savepoint) ,6,7,8 -->rollback

语法：  x a b    savepoint  保存点名字

insert into xx values(1,‘zs’);
insert into xx values(2,‘ls’);

savepoint initdate ;

insert into xx values(3,‘ww’);

rollback to savepoint initdate ;

事务的隔离级别：
多个事务会产生很多并发问题：
1.脏读：当一个事务正在访问数据，并对此数据进行了修改（1->2），但是这种修改【还没有提交到数据库(commit)】； 此时，另一个事务也在访问这个数据 。本质： 某个事务（客户端）读取到的数据是 过时的。
2.不可重复读： 在一个事务内（客户端）内，多次读取同一个数据，但结果不同。
本质：就是事务A拿到了 被其他事务B修改并提交后的数据

	3.幻读（虚读）：在一个事务内（客户端）内，多次读取同一批数据，但结果不同。


不可重复读和幻读的区别：
	a.不可重复读指的是对于“同一条”数据的查询操作  a ->b
	 幻读对于“多条数据”的查询操作，数据量数：  20条 -> 18条  
	b.不可重复读：update
	  幻读：insert|delete 

四种隔离级别的程度 依次递进（解决 并发的效果，越来越 稳定） ，但是性能越来越低。
并发性 、可用性 本身就是矛盾的。

Oracle只支持其中两种：Read Committed（默认），Serializable
（oracle自身扩种了一种 read only，实际 read only隶属于 Serializable级别 ）

切换四种隔离级别：
set transaction isolation level Serializable;

切换read only：
set transaction read only ;

MySQL 支持全部的四种

六.索引

索引：
类似于书的目录
索引类型 默认B树索引（默认）、位图索引

create 类型 名字

create index 索引名

create index myindex on emp(deptno) ;
主键默认 就是索引

create index myindex on emp(deptno ,sal ) ;

什么时候 适合建立索引：

​ 数据集中的列,经常在where中使用的列， 数据量大
​ 数据集中的列：主键列(empno,id)不集中，但是因为 会被频繁使用 ，因此也适合建索引

empno :
1
2
3

empno,ename ,job,sal…20

select *from emp where age = 23 ;

字段：字典

deptno :
1000 -> 10

empno :
1
2
3

删除索引

drop index 索引名
drop index myindex ;

数据库对比

1.时间函数：

在数据库表中添加bai默认的当前日期列du:
表中添加日期列,在其默认zhi值中，填上默认日期的dao函数即可。
oracle 当中是sysdate！
sql server中是GETDATE()
MySQL：curdate()
’yyyy-mm-dd’的格式返回今天的日期，可以直接存到date字段中。
不包括当前的时间 只显示年月日：需要你读取出来的时候，删除掉后面的时间部分。
因为数据库会自动加上 0:00:00 的。

2.关键字约束（数据类型）

oracle中的数字类型都用number，但是mysql就要具体详细分int，long等等。