Oracle_Day04(子查询，DML数据操纵语言，事务)

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,'太原')