Oracle_Day04(子查询,DML数据操纵语言,事务)
子查询
- 子查询是在主查询查询之前执行的查询
- 子查询的结果是为主查询服务的
SELECT * FROM 表名 WHERE 过滤字段 关系运算符 (SELECT 列 FROM 表名)
子查询要包含在括号内
子查询往往作为主查询的比较的条件
单行子查询:
子查询的结果为一条记录
-- 查询出工资比WARD薪资高的所有人
SELECT * FROM emp WHERE sal >(SELECT sal FROM emp WHERE ename='WARD')
-- 查询出job 与7566 相同,且sal比7499大的员工的姓名、职位、薪资
SELECT empno,ename,job,sal
FROM emp
WHERE job =(SELECT job from emp where empno = 7566)
AND sal > (SELECT sal from emp where empno=7499)
在子查询中使用分组函数:
-- 查询出所有员工中工资最低的员工的姓名、职位、薪资
SELECT ename,job,sal
FROM emp
WHERE sal = (SELECT min(sal) FROM emp);
在子查询中进行分组过滤:
-- 查询最低工资大于10号部门最低工资的部门id和最低工资
SELECT deptno, min(sal)
FROM emp
GROUP BY deptno
HAVING min(sal) > (SELECT min(sal) FROM emp WHERE deptno =10);
子查询中需要注意的问题:
在使用子查询的时候子查询的结果必须是单行记录,这时需要根据要求来进行结果的匹配
多行子查询:
子查询的结果为多行,需要使用多行操作符
操作符 | 含义 |
---|---|
IN | 等于其中的任意一个 |
ANY | 和子查询返回的结果中的任意一个比较 |
ALL | 和子查询中的所有的结果进行比较 |
-- 查询其他职位中比job为“SALESMAN”的任一员工工资低的员工的姓名、职位、薪资
SELECT ename,job,sal
FROM emp
WHERE job <>'SALESMAN'
AND sal < ANY(SELECT sal FROM emp WHERE job = 'SALESMAN')
-- 查询其他职位中比job为“SALESMAN”的所有员工工资低的员工的姓名、职位、薪资
SELECT ename,job,sal
FROM emp
WHERE job <>'SALESMAN'
AND sal < ALL(SELECT sal FROM emp WHERE job = 'SALESMAN')
分页查询:
数据伪列:列不存在,但是可以直接使用的列称为伪列
Oracle中的伪列:sysdate ,rownum ,rowid
-- Rownum的使用
SELECT rownum ,empno,ename,job FROM emp WHERE job='SALESMAN';
Rownum是oracle自动生成的 一个行号,是实时变化的
--查询 emp表中的第1条记录
SELECT rownum,empno,ename,job FROM emp WHERE rownum=1;
--查询emp表中的前5条纪律
SELECT rownum,empno,ename,job FROM emp WHERE rownum <= 5;
分页:
--显示第一页的数据,从1--5
SELECT *
FROM ( SELECT empno,ename,job,hiredate,sal,rownum rn FROM emp WHERE rownum <= 5 ) temp
WHERE temp.rn > 0
--显示第二页的数据 从6--10
SELECT *
FROM ( SELECT empno,ename,job,hiredate,sal,rownum rn FROM emp WHERE rownum <= 10 ) temp
WHERE temp.rn > 5
--显示第三页的数据 从11--15
SELECT *
FROM ( SELECT empno,ename,job,hiredate,sal,rownum rn FROM emp WHERE rownum <= 15 ) temp
WHERE temp.rn > 10
当前的页数:currentPage ,每页显示的条数:pages
起始条数:(CurrentPage- 1) * pages
结束的条数:(CurrentPage- 1) * pages +pages
--另外的写法:
SELECT *
FROM (SELECT rownum r, emp.* FROM emp) b
WHERE b.r > 5 AND b.r < 11
DML数据操纵语言
插入数据:
INSERT :
INSERT INTO 表名(列名, 列名, 列名, 列名…)VALUES (值,值,值,值,值)
--简单写法:
INSERT INTO 表名 VALUES (值,值,值,值,值)
使用insert语句可以一次向数据库中插入一条记录
在插入时对于字符和日期需要使用单引号引起来
-- 向emp表中插入一条记录
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
VALUES(9999,'李四','程序员',7788,to_date('2020-10-1','yyyy-mm-dd'),2500,NULL,10);
--简单写法
INSERT INTO emp VALUES(8888,'张三','程序员',7788,to_date('2021-1-1','yyyy-mm-dd'),3500,NULL,20);
向表中插入空值:
--给dept表中插入一条记录:隐式插入空值
INSERT INTO dept(deptno,dname) VALUES (50,'技术部');
--显式插入空值
INSERT INTO dept VALUES (60,'财务部',null);
给一个字段中是否可以插入空值,那么需要看数据库的设计
脚本的方式 Navicat不支持。
从其他表中拷贝数据:
--创建一张emp1表 表只有结构 没有数据
CREATE TABLE emp1 (
empno NUMBER(4) CONSTRAINT PK_EMP1 PRIMARY KEY,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);
SELECT * FROM emp1;
--从其他表中拷贝数据
INSERT INTO emp1
SELECT * FROM emp ;
拷贝部分数据:
--创建一张emp1表 表只有结构 没有数据
CREATE TABLE emp1 (
empno NUMBER(4) CONSTRAINT PK_EMP1 PRIMARY KEY,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);
SELECT * FROM emp1;
--从其他表中拷贝数据
INSERT INTO emp1
SELECT * FROM emp WHERE deptno = 30;
拷贝或插入数据时出现中文乱码解决方式:
--解决插入数据中文乱码
--获取系统当前使用的字符集
SELECT userenv('language') FROM dual;
SELECT * FROM v$nls_parameters;
增加系统的环境变量的配置
NLS_LANG:AMERICAN_AMERICA.ZHS16GBK(第一条语句返回的结果)
更新数据:
语法:UPDATE 表名 SET 列明=值, 列名=值… WHERE 条件
更新可以一次更新一条或多条记录
--将EMP表中的8888员工的部门改为50
UPDATE emp SET deptno=50
WHERE empno=8888
如果不加条件的执行更新语句将导致表中的数据全部被更新
在更新中也可以使用子查询
--更新7369员工的job和工资,使其和7902保持相同
UPDATE emp1
SET job = (SELECT job FROM emp1 WHERE empno=7902), sal = (SELECT sal FROM emp1 WHERE empno=7902)
WHERE empno=7369;
-- 更新emp1中empno为7698的员工的job相同的员工的 deptno为empno为7788的员工的deptno
UPDATE emp1
SET deptno = (SELECT deptno FROM emp1 WHERE empno =7788)
WHERE job = (SELECT job FROM emp1 WHERE empno = 7698);
删除数据:
语法:DELETE FROM 表名 WHERE 条件
删除数据必须加条件,如果不加条件则整个数据库会被清空
--删除8888员工
DELETE FROM emp1 WHERE empno = 8888;
在删除中使用子查询:
--删除emp1表中工资最高的员工
DELETE FROM emp1 WHERE sal=(SELECT max(sal) FROM emp);
在oracle中删除语句中可以省略from关键字
DELETE emp1 WHERE empno = 8888;
事务
事务是对数据库一组逻辑操作单元,进行的一次完整性操作。事务的操作中,数据从一种状态转变为另一种状态。
数据库事务的组成:一个或多个DML语句;DDL语句 DCL语句
事务往往以DML语句开始
事务的结束:
- COMMIT(事务提交)
- ROLLBACK (事务回滚)
- DDL语句(自动提交)
- 会话的结束
- 系统异常终止
事务特性(ACID):
- 原子性:事务在执行时,其中所关联的一组逻辑相关的操作,要么都成功,要么都失败;
- 一致性:查询的数据始终是一致的。
- 隔离性:对于其他会话来说,不能读取到其他会话未提交的事务。事务是不可见的。
- 持久性:事务一旦提交,对于数据库来说,此时的事务的执行结果将具有持久性,结果被完整的保存。
锁:
锁的分类:
- 排他锁(独占锁):排斥其他的排他锁和共享锁。
- 共享锁:排斥其他的排他锁,但是不排斥其他的共享锁。
锁的类型:
- DML锁:主要用于保证数据的完整性,TX(行锁) TM(表锁)
- DDL锁(数据字典锁):主要用于保护数据库对象的结构,表,索引,视图等结构的定义。
- SYSTEM锁:主要是保护数据库的内部结构
只有当产生事务的时候 ,才会产生锁,保证数据的完整性,一致性,正确性。
自动加锁:做DML操作时,insert update delete
Oracle的特有操作: select … for update 由oracle自动完成加锁
控制事务:
事务、DELETE、 INSERT、UPDATE当执行完成之后,如果我们确认事务的操作是准确,我们需要将数据进行持久化保存,此时我们就需要将数据进行commit
如果操作之后,认为操作不当,需要回到修改之前的状态,此时我们就可以进行rollback
设置保存点,回滚到保存点:
使用 savepoint 创建保存点
使用rollback to 回滚到刚才使用savepoint创建的保存点上
savepoint point1;
rollback to point1;
事务的进程:
自动提交的情况:
- DDL
- DCL
- 会话正常结束
- 会话异常结束或者系统异常此时都会导致自动回滚
事务相关的操作为什需要提交或者可以进行回滚?
- 当我们在进行insert udate delete操作的时候,数据并不会直接被写入到数据库文件,而是写入到缓存区,所以当我们提交的时候就是将缓存区的数据提交到数据库文件,执行写入操作,而当我们进行回滚的时候,此时会将缓存区的数据进行清空。
事务相关操作:
- 提交:当执行了insert udate delete操作的时候,在执行结束之后,都需要进行提交操作,只有提交了之后,数据的修改才会是持久。
- 回滚:当操作时,发生了异常此时希望数据回到初始状态,此时就需要回滚数据ROLLBACKE.回滚可以取消数据的改变,恢复数据的状态,同时释放锁。
回滚只能针对没有提交的操作,一旦提交了,则不能执行回滚。
创建和管理表:
Oralce是通过用户去管理数据的,要在oracle中存储数据,必须先创建表空间,再创建用户,接着创建对象(表,索引,视图…)
表空间:一个或多个数据文件的逻辑集合。
表空间的分类:
- 永久表空间:存放永久性的数据
- 临时表空间:保存临时数据
- Undo表空间: 保存数据修改前的镜像。
一个数据库下可以创建多个表空间,一个表空间由可以被多个用户所拥有,一个用户下可以创建多个表
-- 创建表空间
create tablespace lanqiao --表空间的名称
--数据文件的存放路径 对应虚拟机中的路径
datafile 'C:\app\Adminstrator\virtual\oradata\orcl\lanqiao.dbf'
size 100m --表空间的初始容量
autoextend on --表示表空间可以自动增长
next 10m --自动增长时的每次增长的空间
--查看所有表空间
SELECT * FROM DBA_TABLESPACES;
--创建临时表空间
create temporary tablespace lanqiao_temp
tempfile 'C:\app\Adminstrator\virtual\oradata\orcl\lanqiao_temp.dbf'
size 50m
autoextend on
next 10
maxsize 20480M
extent management local;
当用户创建成功之后,用户由于没有分配权限,此时不能登陆
权限分配模型:
权限->角色->用户
Oracle中默认存在三个重要的角色:connect角色、resource角色、dba 角色;
- Connect: 是授予最终用户的典型的角色 ,最基本
- 创建会话、修改会话、建立数据库连接、创建序列、创建同义词、创建视图
- RESOURCE 是授予开发人员的
- 建表、建立触发器、存储过程
- DBA:拥有全部的特权,是系统的最高权限,只有DBA才可以创建数据库结构,并且系统权限也需要DBA授权,DBA用户可以操作全体用户的任意的表,包括删除
--为用户授权
grant connect,resource to zb;
Oralce 的数据类型:
No | 数据类型 | 描述 |
---|---|---|
1 | Varchar,Varchar2 | 表示一个字符串 Varchar定长 Varchar2可变长度 |
2 | NUMBER Number(n) | 表示整数 长度为n |
3 | NUMBER | Number(m,n) 表示整数 总长度为m 小数为n 整数m-n |
4 | DATE | 表示日期类型 |
5 | CLOB | 表示大的文本类型 可存放4G内容 |
6 | BLOB | 表示大的二进制数据 可存放4G内容 |
建表:
--创建表
create table person(
pid number(10),
pname varchar2(20),
gender number(1) default 1,
birthday date
);
插入数据:
--插入数据
INSERT INTO person(pid,pname,gender,birthday) VALUES (1,'张三',1,to_date('2000-12-12','yyyy-mm-dd'));
commit;
SELECT * FROM person;
INSERT INTO person(pid,pname,gender,birthday) VALUES (2,'张三',1,sysdate);
删除表:
Drop table 表名;
表的修改:
表添加列:alter table 表名 add(列名 类型 默认值,[列名 类型 默认值])
表修改列:alter table 表名 modify(列名 类型 默认值,[列名 类型 默认值])
--给person增加address
ALTER TABLE person ADD(address varchar2(50));
--将address的字段的长度修改为100
ALTER TABLE person MODIFY (address varchar2(100));
序列:
实现主键列的自动增长:
创建序列:
--创建序列:
CREATE SEQUENCE autoPid;
--nextval :取得序列的下一个值
-- currval:取得序列的当前值
SELECT AUTOPID.NEXTVAL FROM dual;
SELECT autopid.currval FROM dual;
--创建复杂的序列
CREATE SEQUENCE autoPid1
increment by 10 --每次增长步长
start with 100;
/*
maxvalue --最大值
minvalue--最小值
cycle/nocycle--是否是循环序列
*/
SELECT AUTOPID1.NEXTVAL FROM dual;
SELECT autopid1.currval FROM dual;
--序列在实际中的使用
INSERT INTO person(pid,pname,gender,birthday,address)
VALUES(autopid.nextval,'王五',0,sysdate,'太原')