Oracle数据库总结11-17

HappyVi

于 2020-11-06 09:15:10 发布

阅读量436

点赞数

文章标签： oracle 数据库

本文链接：https://blog.youkuaiyun.com/qq_30260307/article/details/109525014

版权

本文详细介绍了Oracle数据库的安装配置、用户管理、数据定义与操纵语言、查询技巧及事务处理等内容，覆盖了Oracle数据库的基础到进阶知识。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Oracle数据库

1.1 数据库环境安装

1.Oracle数据库产品是免费的，我们可以从Oracle的官方网站(http://www.oracle.com)下载到程序安装包，Oracle在Windows下的安装非常方便，安装开始后，一直点击安装程序的“下一步”即可。

2. 下载Oracle10g后，解压到一个文件夹下，单击“setup.exe”文件即可启动安装界面。。填写全局数据库名，以及管理员的密码。全局数据库名是数据库在服务器网络中的唯一标识。

3. 数据库创建完毕后，需要设置数据库的默认用户。Oracle中为管理员预置了两个用户分别是SYS和SYSTEM。同时Oracle为程序测试提供了一个普通用户scott，口令管理中，可以对数据库用户设置密码，设置是否锁定。 Oracle客户端使用用户名和密码登录Oracle系统后才能对数据库操作。默认的用户中，SYS和SYSTEM用户是没有锁定的，安装成功后可以直接使用，SCOTT用户默认为锁定状态，因此不能直接使用，需要把SCOTT用户设定为非锁定状态才能正常使用。

*******

Oracle数据库中，默认情况下，所有系统的数据，SQL关键字等都是大写的，在操作过程中，Oracle会自动把这些内容转换为大写，因此用户操作时不需考虑大小写问题，一般情况下，为了良好的程序风格，程序中建议关键字用大写，非关键字可以使用小写。

4.该工具是Oracle系统默认安装下，自带的一个客户端工具。在Windows命令行中输入“sqlplusw”命令，就能够启动该工具了

，输入：sqlplus输入密码后就可以直接使用了

Oracle数据库的端口号是：1521

1.2 数据库的卸载

1、我的电脑右击《管理》选择《服务和应用程序》->《服务》找到《Oracle》右击选择停止。

2、控制面板卸载程序

3、去安装Oracle的目录下面把Oracle的文件夹删除

4、用CMD打开regedit注册表

删除HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Oracle文件夹

删除HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Eventlog\Application\Oracle文件夹

删除HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\Oracle文件夹
删除HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\Eventlog\Application\Oracle文件夹。

删除HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Oracle文件夹
删除HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\Oracle的文件夹。

5、以上操作完成后即表示Oracle已经完全卸载。

1.3 Oracle数据库---scott用户

sqlplus无图形界面,如果想要打开文件，使用 ed 文件路径默认打开的是以.sql结尾的文件如果想要执行某个文件。使用 @ 文件路径如果打开的是.sql文件后缀可以不写.
1、scott是默认用户，需要导入才能使用
2、导入scott的用户 (默认scott用户是锁定状态) @D:\oracle\app\oracle\product\11.2.0\server\rdbms\admin\scott
3、解锁scott用户	alter user scott account unlock;
4、设置scott密码生效	alter User scott identified by tiger;

1.3.1 切换用户

登录sys用户的时候需要以sysdba的身份登录	sys/root as sysdba;
切换用户	conn 用户名/用户密码
注意切换到sys用户	conn sys/root as sysdba;
显示当前用户	show user;
查看用户下所有的表	select * from tab;

1.3.2 sqlplus中的显示格式化

设置每页显示的条数

set pagesize 30;

设置每行显示的宽度

set linesize 300;

设置某个字段显示的宽度

字符串

col 字段名 for a10;

数值

col 字段名 for 9999;（几位数就填几个9）

1.4 Oracle数据库的用户管理

1、用户的管理	创建用户：create user 用户名 identified by 密码;
2、用户的授(包含两个权限 CONNECT,RESOUCE)	grant CONNECT,RESOUCE to 用户;
3、修改密码	ALTER USER 用户名 IDENTIFIED BY 新密码
4、让密码失效：登陆之后需要立即修改密码	ALETR USER 用户名 PASSWORD EXPIRE ;
5、锁定用户	ALTER USER 用户名 ACCOUNT LOCK;
6、解锁用户	ALTER USER 用户名 ACCOUNT UNLOCK;
7、将A用户名的操作权限授予其他用户	ps:在A用户下访问B 用户名的表 GRANT SELECT,INSET,UPDATE ,DELETE ON 用户名.表名 TO 其他用户
8、回收权限	REVOKE CONNECT ,RESOURCE FROM 用户名;
9、删除用户	DROP USER 用户名 (CASCADE); 需要先删除用户名创建的内容
sys管理员的默认密码为change_on_install 如果在一个用户下访问其他用户的表，需要加上模式名称 ps: select * from scott.emp;

1.5 Oracle 数据库数据定义语言Data Definition Language（DDL）

CHAR(length)	存储固定长度的字符串。参数length指定了长度，如果存储的字符串长度小于length，用空格填充。默认长度是1，最长不超过2000字节。
VARCHAR2(length)	存储可变长度的字符串。length指定了该字符串的最大长度。默认长度是1，最长不超过4000字符。
NUMBER(p，s)	既可以存储浮点数，也可以存储整数，p表示数字的最大位数（如果是小数包括整数部分和小数部分和小数点，p默认是38为），s是指小数位数。
FlOAT/DOUBLE	小数型
INT/INTEGER	整数型
DATE	存储日期和时间，存储纪元、4位年、月、日、时、分、秒，存储时间从公元前4712年1月1日到公元后4712年12月31日。
TIMESTAMP	不但存储日期的年月日，时分秒，以及秒后6位，同时包含时区。
CLOB	存储大的文本，比如存储非结构化的XML文档
BLOB	存储二进制对象，如图形、视频、声音等。
ALTER TABLE命令创建表	ALTER TABLE 表名 ADD CONSTRAINT约束名约束内容。
创建表和约束	ALTER TABLE SCORES ADD CONSTRAINT CK_SCORES_TERM CHECK(TERM = 'S1' OR TERM ='S2') 只能在S1和S2中二选一，约束

1.6 Oracle 数据库数据操纵语言（DML）

1.6.1 INSERT 增加表

简单查询分组查询	SELECT *\|列名\|表达式 FROM 表名 WHERE 条件 ORDER BY 列名
根据结果集创建表	CREATE TABLE 表名 AS SELECT语句 EX:INSERT INTO EMP VALUES(1000,'JACK','SALESMAN',7902,SYSDATE,2000,NULL,10); 插入指定的数据 INSERT INTO EMP (EMPNO,ENAME,SAL)VALUES(1001,'ROSE',3000); 修改
复制表结构	如果只复制表结构，只需使查询的条件不成立（比如where 1=2），就不会查询从出任何数据，从而复制一个表结构。 EX： CREATE TABLE INFOS2 AS SELECT * FROM INFOS WHERE 1=2;
根据结果集创建表	INSERT INTO 表名(列名1，列名2……) VALUES (值1，值2……)
在Oracle中，日期是国际化的，不同的区域安装的数据库，默认的日期格式不同，因此为了程序便于移植，日期的输入要使用TO_DATE函数对日期格式化后输入，采用格式化字符串对日期进行格式化时，格式化字符串中字符不区分大小写，常见的格式化字符如下： 1. yyyy表示四位年份 2. mm表示两位月份，比如3月表示为03 3. dd表示两位日期 4. hh24表示小时从0-23，hh12也表示小时从0-11。 5. mi 表示分钟 6. ss表示秒
commit是把用户操作（添加、删除、修改操作）提交，只有提交操作后，数据才能真正更新到表中，否则其他用户无法查询到当前用户操作的结果。
INSERT向表中插入一个结果集	INSERT INTO INFOS2 SELECT * FROM INFOS;
INSERT向表中插入一个常量结果集	INSERT INTO INFOS SELECT 's100106','卢俊义','男',23,5, TO_DATE('2009-8-9 08:00:10','YYYY-MM-DD HH24:MI:SS'), '青龙寺','1001' FROM DUAL;

1.6.2 UPDATE 更新表

UPDATE操作

UPDATE 表名 SET 列名1=值，列名2=值…… WHERE 条件

DELETE FROM 表名称 [WHERE 删除条件] ;

1、删除指定的员工(删除jack)

DELETE FROM EMP WHERE ENAME='JACK';

2、删除若干个员工(1001,7902,7844)

DELETE FROM EMP WHERE EMPNO IN (1001,7902,7844);

3、删除工资最高的员工

DELETE FROM EMP WHERE SAL = (SELECT MAX(SAL) FROM EMP);

删除语句中如果没有WHERE子句,表示全部删除(谨慎)

物理删除：真删

逻辑删除：利用表中的字段做标记。FLAG = 0

SELECT * FROM EMP WHERE FLAG = 1;

1.6.3 DELECT 删除表

DELETE操作

DELETE FROM表名 WHERE 条件

EX：DELETE FROM INFOS WHERE STUID='s100103';

TRUNCAT命令（是一个DDL命令）可以把表中的所有数据一次性全部删除

TRUNCATE TABLE 表名

删除表

DROP TABLE 表名称 [PURGE 清除缓存];

DROP TABLE STUDENT;

DROP TABLE STUDENT PURGE;

TRUNCATE和DELETE都能把表中的数据全部删除，他们的区别是：

1. TRUNCATE是DDL命令，删除的数据不能恢复；DELETE命令是DML命令，删除后的数据可以通过日志文件恢复。

2. 如果一个表中数据记录很多，TRUNCATE相对DELETE速度快。

由于TRUNCATE命令比较危险，因此在实际开发中，TRUNCATE命令慎用。

EX:1、将公司的最低工资改为平均工资

UPDATE EMP SET SAL = (SELECT AVG(SAL) FROM EMP) WHERE SAL = (SELECT MIN(SAL) FROM EMP);

2、将所有在81年入职的员工的入职日期改为今天，工资上涨20%

UPDATE EMP SET HIREDATE = SYSDATE,SAL = SAL*1.2 WHERE HIREDATE BETWEEN '01-1月-81' AND '31-12月-81';

修改语句在没有WHERE子句的时候，表示修改表中所有的记录

1.6.4 SELECT 查询表

DISTINCT消除重复行（高级查询）

SELECT DISTINCT DEPTNO FROM EMP;

1.7 Oracle 数据库操作符

求余运算只能借助函数	MOD(x,y)：返回x除以y的余数。
算术运算	SELECT ENAME,SAL,(SAL*12+2000) FROM EMP WHERE SAL>2000;
=	等于	>	大于
<>或者!=	不等于	<=	小于或者等于
<	小于	>=	大于或者等于
逻辑运算符有三个：AND（与）、OR（或）、NOT（非）
字符串连接操作符（\|\|）	EX：SELECT (ENAME \|\| 'is a ' \|\| JOB) AS "Employee Details" ① 2 FROM EMP 3 WHERE SAL>2000;
Oracle中字符串可以用单引号，也可以用双引号，在别名中存在空格时，必须用双引号。在表名、列名时用双引号。
NULL操作	如果某条记录中有缺少的数据值，就是空值（NULL值）。空值不等于0或者空格，空值是指未赋值、未知或不可用的值。任何数据类型的列都可以包括NULL值，除非该列被定义为非空或者主键。在查询条件中NULL值用IS NULL作条件，非NULL值用NOT IS NULL做条件。
IN 操作	EX：WHERE job = 'SALESMAN ' OR job = 'PRESIDENT ' OR job = 'ANALYST ' WHERE job IN ('SALESMAN', 'PRESIDENT', 'ANALYST') 对应IN操作的还有NOT IN，用法一样，结果相反。
BETWEEN...AND...	在WHERE子句中，可以使用BETWEEN操作符来查询列值包含在指定区间内的行。 EX：WHERE SAL>=1000 AND SAL<=2000 BWTWEEN操作所指定的范围也包括边界。
LIKE模糊查询	在一些查询时，可能把握不准需要查询的确切值，比如百度搜索时输入关键字即可查询出相关的结果，这种查询称为模糊查询。模糊查询使用LIKE关键字通过字符匹配检索出所需要的数据行。字符匹配操作可以使用通配符“%”和“_”: %：表示零个或者多个任意字符。_：代表一个任意字符。

1.8 Oracle 数据库函数

大小写转换：	upper(列\|数据)、lower(列\|数据)
通过输入人名得到这个人的完整信息在sqlplus中输入某个值使用 &标记（了解） SELECT * FROM EMP WHERE ENAME = &标记; SELECT * FROM EMP WHERE ENAME = upper('&name');
首字母大写：	initcap(列\|数据)
字符串长度：	length(列)
字符串替换：	replace(列\|数据,替换数据,新数据)
字符串截取:	substr(列\|数据,开始位置); substr(列\|数据,开始位置,长度)
四舍五入：	round(列\|数值 [保留小数位]);不写保留小数位则不保留如果小数位为负数,则从小数点左边计算。
截取小数位：	trunc(列\|数值 [保留小数位]) 直接截取小数位，默认不保留小数位如果是负数直接整数位为0
取模：取余	mod(列1\|数值1,列2\|数值2)
时间戳	获取系统当前的时间戳 SELECT SYSTIMESTAMP FROM DUAL;
时间戳	获取系统当前的时间 SELECT SYSDATE FROM DUAL;
日期三种计算方式: 日期函数	1、日期+天数 = 日期(n天后)
	2、日期 -天数 = 日期(n天前)
	3、日期-日期 = 天数(时间间隔)
	MONTHS_BETWEEN(日期1，日期2) 两个日期间的相隔的月份
	ADD_MONTHS(日期，月数);在指定的日期上增加月数
	LAST_DAY(日期)指定日期的最后一天
	NEXT_DAY(日期，周几);
	查询所有员工工作年限(月) SELECT TRUNC((SYSDATE-HIREDATE)/30) year FROM EMP; SELECT MONTHS_BETWEEN(SYSDATE,HIREDATE) FROM EMP; 计算毕业时间 SELECT SYSDATE+120 FROM DUAL; SELECT ADD_MONTHS(SYSDATE,-4) FROM DUAL; 查询月底(25号以后)入职的员工 SELECT * FROM EMP WHERE HIREDATE > LAST_DAY(HIREDATE)-5; SELECT LAST_DAY(SYSDATE) FROM DUAL; 找到当前日期的下一个星期几的是哪一天(1表示星期日，7表示星期六) SELECT NEXT_DAY(SYSDATE,1) FROM DUAL;

1.9 Oracle 数据库转换函数

TO_CHAR(列|日期|数字，转换格式)

日期转换成字符串年(yyyy)月(mm)日(dd)时(hh24)分(mi)秒(ss);

EX:将系统当前时间转换成'yyyy-mm-dd'格式

SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd') FROM DUAL;

SELECT TO_CHAR(SYSDATE,'yyyy/mm/dd') FROM DUAL;

SELECT TO_CHAR(SAL)+10 FROM EMP;

SELECT '10' +10 from dual;

数字转换成字符串任意一位数字(9)、货币(L 本地货币)TO_CHAR

SELECT TO_CHAR(1234567876,'999,999,999,999') FROM DUAL;

SELECT TO_CHAR(1234567876,'L999,999,999,999') FROM DUAL;

TO_DATE(字符串,'时间格式');

oracle中默认的时间格式 xx日x月xx年

将'2018-07-04'日期类型的属性插入到EMP表

INSERT INTO EMP (EMPNO,ENAME,HIREDATE,SAL) VALUES(8888,'李四','04-7月-18',3000);

INSERT INTO EMP (EMPNO,ENAME,HIREDATE,SAL) VALUES(8888,'李四',TO_DATE('2018-07-04','yyyy-mm-dd'),3000);

TO_NUMBER(列|数值);

SELECT TO_NUMBER('10') FROM DUAL;

可以将字符串中全是数字的类型转换为数字类型，oracle中如果是全是数字的字符串类型可以直接加减乘除，系统会默认转换成CHAR类型。如果该字符串中不仅仅只有数字的话该TO_NUMBER 类型没有任何作用。

2.0 Oracle 数据库中的多表查询

SELECT E.ENAME,E.HIREDATE,E.JOB,E.SAL,D.DNAME,D.DEPTNO,D.LOC

FROM EMP E,DEPT D

WHERE E.DEPTNO = D.DEPTNO;

查询每个雇员的编号、姓名、职位、基本工资、部门名称、部门位置

1、确定要使用的数据表

EMP:雇员的编号、姓名、职位、基本工资

DEPT:部门名称、部门位置

2、确定数据表之间的关联关系

EMP.DEPTNO = DEPT.DEPTNO;

内连接----->等价连接

SELECT E.EMPNO,E.ENAME,E.JOB,E.SAL,D.DNAME,D.LOC

FROM EMP E,DEPT D

WHERE E.DEPTNO = D.DEPTNO;

一、查询每个雇员编号、姓名、职位、基本工资、工资等级

(提示：emp中的sal在salgrade表中的losal和hisal之间)

1、确定要使用的数据表

EMP : 雇员编号、姓名、职位、基本工资

SALGRADE:工资等级

2、确定数据表之间的关联关系

EMP.SAL BETWEEN LOSAL AND HISAL;

SELECT E.EMPNO,E.ENAME,E.JOB,E.SAL,S.GRADE

FROM EMP E,SALGRADE S

WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;

二、查询每个雇员的编号、姓名、基本工资、部门名称、工资等级

1、确定要使用的数据表

EMP:编号、姓名、基本工资

DEPT:部门名称

SALGRADE:工资等级

2、确定数据表之间的关联关系

E.DEPTNO = D.DEPTNO AND E.SAL BETWEEN S.LOSAL AND S.HISAL;

SELECT E.EMPNO,E.ENAME,E.SAL,D.DNAME,S.GRADE

FROM EMP E,DEPT D,SALGRADE S

WHERE E.DEPTNO = D.DEPTNO AND E.SAL BETWEEN S.LOSAL AND S.HISAL;

右连接---->（+）在左边

SELECT E.ENAME,E.HIREDATE,E.JOB,E.SAL,D.DNAME,D.DEPTNO,D.LOC

FROM EMP E,DEPT D

WHERE E.DEPTNO(+) = D.DEPTNO;

左连接---->（+）在右边

SELECT E.ENAME,E.HIREDATE,E.JOB,E.SAL,D.DNAME,D.DEPTNO,D.LOC

FROM EMP E,DEPT D

WHERE E.DEPTNO = D.DEPTNO(+);

2.1 Oracle 数据库数据集合操作

数据集合操作主要用于数据结果的连接，就相当于多个查询结果连接成一个结果返回。但是要求必须多个查询返回结果列的结构必须一致
UNION	得到两个结果合并到一起，但是会去除重复数据 EX:SELECT * FROM EMP UNION SELECT * FROM DEPT;
UNION ALL	得到两个结果合并到一起，不会去除重复数据,是所有表的集合，包含全部数据 EX:SELECT * FROM EMP UNION ALL SELECT * FROM DEPT;
INTERSECT	得到两个结果的并集，是所有表的交集，相重复的部分 EX:SELECT * FROM EMP INTERSECT SELECT * FROM DEPT;
MINUS	得到第一个结果减去第二个结果的差集 EX:SELECT * FROM EMP MINUS SELECT * FROM DEPT;

2.2 Oracle 数据库计数函数

COUNT(*)

返回表中的数据个数

EX:SELECT COUNT(*) FROM EMP GROUP BY DEPTNO;

COUNT(字段)

返回不为null的数据个数

COUNT(DISTINCT 字段)

返回去除重复之后的数据个数

2.3 Oracle 数据库分组查询

分组统计查询的顺序

④SELECT 分组字段 [别名],分组字段[别名],...

①FORM 表名称 [别名]

②[WHERE 限定条件s]

③[GROUP BY 分组字段,分组字段2....]

⑤[ORDER BY 列名 [ASC|DESC],列名 [ASC|DESC],列名 [ASC|DESC]...]

EX：1、按照职位分组，查询每个职位的名称、人数、平均工资

SELECT JOB,COUNT(*),AVG(SAL)

FROM EMP

GROUP BY JOB;

2、查询每个部门编号，每个部门的人数，最高工资，最低工资

SELECT DEPTNO,COUNT(*),MAX(SAL),MIN(SAL)

FROM EMP

GROUP BY DEPTNO;

oracle中分组统计查询的三点要求：

1、在没有GROUP BY子句的时候，在使用统计函数的时候不能出现任何其他字段

SELECT sal,avg(sal) FROM EMP;错

2、在使用GROUP BY 子句的时候，只允许出现分组字段和统计函数

SELECT DEPTNO,AVG(SAL),ENAME

FROM EMP

GROUP BY DEPTNO;错

3、统计函数可以嵌套，但是嵌套之后，SELECT子句中不能出现其他字段。只能使用嵌套函数。分组字段也不能查询。

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

SELECT DEPTNO,MAX(AVG(SAL)) FROM EMP GROUP BY DEPTNO;错

HAVING子句与WHERE子句的区别

WHERE子句是在GROUP BY 子句之前执行(不允许出现统计函数)，

HAVING在GROUP BY 子句之后执行

所有的分组查询中的执行顺序：

⑤SELECT 分组字段 [别名],分组字段[别名],...

①FORM 表名称 [别名]

②[WHERE 限定条件s]

③[GROUP BY 分组字段,分组字段2....]

④[HAVING 条件....]

⑥[ORDER BY 列名 [ASC|DESC],列名 [ASC|DESC],列名 [ASC|DESC]...]

2.4 Oracle 数据库子查询操作符

子查询 SELECT [DISTINCT] 分组字段[别名],...\|统计函数,(子查询) FROM 表名称(子查询) [WHERE 过滤条件(s)] (子查询) [GROUP BY 分组字段,分组字段...] [HAVING 分组后的过滤条件](子查询) [ORDER BY 字段[ASC\|DESC],..]
WHERE子句：返回单行单列、多行多列、单行多列 HAVING子句：返回单行单列，同时要使用统计函数 FROM 子句：返回多行多列
子查询的操作符	1、IN操作	子查询会返回多行单列的结果,IN操作符的含义在这个多行单列的结果中的其中某一个相 EX：SELECT * FROM EMP WHERE SAL IN(SELECT SAL FROM EMP WHERE JOB = 'MANAGER');
子查询的操作符	2、NOT IN	与IN操作符含义相反,不能与NULL值比较 EX:SELECT * FROM EMP WHERE COMM NOT IN(SELECT COMM FROM EMP WHERE COMM IS NULL);错
子查询的操作符	3、ANY操作	=ANY 与 =IN操作完全一样 EX:SELECT * FROM EMP WHERE SAL =ANY(SELECT SAL FROM EMP WHERE JOB = 'MANAGER');
	4、>ANY	大于最小值 EX:SELECT * FROM EMP WHERE SAL >ANY(SELECT SAL FROM EMP WHERE JOB = 'MANAGER');
	5、<ANY	小于最大值 EX:SELECT * FROM EMP WHERE SAL <ANY( SELECT SAL FROM EMP WHERE JOB ='MANAGER' );
	6、=ALL	不存在
	7、>ALL	大于最大值 EX:SELECT * FROM EMP WHERE SAL >ALL(SELECT SAL FROM EMP WHERE JOB = 'MANAGER');
	8、<ALL	小于最小值 EX:SELECT * FROM EMP WHERE SAL <ALL(SELECT SAL FROM EMP WHERE JOB = 'MANAGER');

2.5 Oracle 数据库子查询

1、在WHERE 子句中使用子查询

EX:1、低于平均工资的员工信息

SELECT *

FROM EMP

WHERE SAL < (SELECT AVG(SAL)

FROM EMP);

2、查询公司最早入职的员工

1)查询入职的日期

SELECT MIN(HIREDATE) FROM EMP;

2)将查询的日期作为WHERE子查询的语句

SELECT *

FROM EMP

WHERE HIREDATE = (SELECT MIN(HIREDATE)

FROM EMP) ;

3、查询与WARD职位相同，工资相等的员工信息

1)查询WARD的部门和工资

SELECT JOB,SAL FROM EMP WHERE ENAME = 'WARD';

2)将查询的结果作为WHERE子查询的语句

SELECT *

FROM EMP

WHERE (JOB,SAL) = (SELECT JOB,SAL

FROM EMP

WHERE ENAME = 'WARD')

AND ENAME <> 'WARD';

2、在HAVING子句中使用子查询

EX:1、查询高于平均工资的职位名称、职位人数、平均工资

SELECT JOB,COUNT(EMPNO),AVG(SAL)

FROM EMP

GROUP BY JOB

HAVING AVG(SAL) > (SELECT AVG(SAL) FROM EMP);

3、再FROM 子句中使用子查询

EX:1、查询每个部门名称、位置、部门人数

SELECT DEPTNO,COUNT(EMPNO)

FROM EMP

GROUP BY DEPTNO;

SELECT D.DNAME,D.LOC,TEMP.COUNT

FROM DEPT D,(SELECT E.DEPTNO,COUNT(EMPNO) COUNT

FROM EMP E

GROUP BY DEPTNO) TEMP

WHERE D.DEPTNO = TEMP.DEPTNO(+);

4、在SELECT子句中使用子查询(了解,一般不使用)

2、查询每个员工的编号、姓名、职位、部门名称

SELECT E.EMPNO,E.ENAME,E.JOB,D.DNAME

FROM EMP E,DEPT D

WHERE E.DEPTNO = D.DEPTNO;

SELECT E.EMPNO,E.ENAME,E.JOB,

(SELECT D.DNAME

FROM DEPT D

WHERE E.DEPTNO = D.DEPTNO) DNAME

FROM EMP E;

此查询语句会出现'1+N' 的问题

2.6 Oracle 数据库表约束的修改

NOT NULL	非空
UNIQUE	字段不重复
PRIMARY KEY	主键
CHECK	控制字段范围

1、增加约束

ALTER TABLE 表名称 ADD CONSTRAINT 约束名称约束类型(字段)

EX:将stu表中的sid添加主键约束

ALTER TABLE STU ADD CONSTRAINT PK_SID PRIMARY KEY(SID);

因为NOT NULL 约束是在创建字段的时候就要加上,所有不能添加NOT NULL

约束。如果非要添加,只能通过修改表结构来添加NOT NULL 约束。

2、删除约束

ALTER TABLE 表名称 DROP CONSTRAINT 约束名称

EX:将stu表中的sid删除主键约束

ALTER TABLE STU DROP CONSTRAINT PK_SID;

通常情况下，修改约束是不建议使用的，也就是说数据约束要在数据库设计之初就应该设计好。

2.7 Oracle数据库修改表结构

1.重命名表名：	ALTER TABLE 表名 RENAME 新表名; EX: ALTER TABLE TEACHER RENAME TEACHERS;
2.向表中添加一列：	ALTER TABLE 表名 ADD 字段名字段类型; EX: ALTER TABLE TEACHERS ADD INFO TEXT;
3.删除表中一列：	ALTER TABLE 表名 DROP COLUMN 列名； EX: ALTER TABLE TEACHERS DROP COLUMN INFO;
4.修改表中一列的数据类型：	ALTER TABLE 表名 MODIFY 字段名字段类型 ; EX: ALTER TABLE TEACHERS MODIFY SAL DECIMAL(7,2);
5.重命名一列：	ALTER TABLE 表名 CHANGE COLUMN 旧字段名新字段名新字段类型 EX: ALTER TABLE TEACHERS CHANGE COLUMN NAME T_NAME VARCHAR(30);

2.8 Oracle 数据库事务

事务的特点

特性：ACID

1.原子性（A）: 事务其实就是执行一组SQL语句，他们中只要有一条语句执行失败，整个事务都失败。

2.一致性（C）：即在事务开始之前和事务结束以后，数据库的设定是一样的。

3.隔离性（I）：当使用事务对数据进行操作时，我们另外的会话是无法对此条数据进行操作的。我们的InnoDB使用的是数据行锁。

4.持久性（D）：当事务成功，数据将提交到数据库，数据就被持久化了。所有的DDL语句默认是自动提交的，所有的DML 语句需要手工提交；

事务的特点

所有的关系型数据库在操作的时候都应该考虑到事务带来的影响。

每一个用户在操作oralce数据库的是都会开启一个SEESION会话。

默认情况下ORALCE数据库的DML语句是手动提交的。DDL语句是自动提交

提交数据： commit;

回滚数据： rollback;

设置回滚点

也可以设置标签进行回滚：

也可以设置回滚点

savepoint 名字;

回滚到回滚点

rollback to 名字;

2.9 Oracle 数据库序列

CREATE SEQUENCE 序列名

[INCREMENT BY 步长]

[START WITH 开始值]

[MAXVALUE 最大值|NOMAXVALUE][MINVALUE 最小值|NOMINVALUE]

[CYCLE | NOCYCLE] 循环

[CACHE |NOCACHE] 缓存

序列提供了两个函数

序列名.nextval; 当前序列的下一个值

序列名.currval; 当前序列的值

删除序列：

DROP SEQUENCE 序列名称;

序列只能在INSERT语句中使用.不能在CREATE中使用(区别MySQL)

3.0 Oracle数据库数据编号 ROWNUM

SELECT ROWNUM,ENAME FROM EMP;

SELECT ROWNUM,ENAME FROM EMP WHERE DEPTNO = 10;

ROWNUM是ORACLE中的数据伪列,是根据数据的顺序自动生成的，不是固定的

SELECT ROWNUM,ENAME FROM EMP ORDER BY SAL;

主要包含两个作用

1、取出第一行数据

SELECT * FROM EMP WHERE ROWNUM = 1;

2、取出前N行数据

SELECT * FROM EMP WHERE ROWNUM < 10;

查询emp表中前5条数据

SELECT * FROM EMP WHERE ROWNUM <= 5;

查询emp表中前10条数据

SELECT * FROM EMP WHERE ROWNUM <= 10;

查询emp表中6~10条数据

SELECT * FROM EMP WHERE ROWNUM BETWEEN 6 AND 10;错误

查询emp表中11~15条数据(第三页currpage = 3,每一页显示5条数据 pagesize = 5 )

SELECT * FROM (

SELECT ROWNUM RN,EMPNO,ENAME,JOB,HIREDATE,SAL,COMM,MGR,DEPTNO

FROM EMP

WHERE ROWNUM <= 15) TEMP currpage*pagesize

WHERE TEMP.RN > 10;(currpage-1)*pagesize

oracle分页查询公式

SELECT 列1,列2...

FROM (SELECT ROWNUM RN,列1,列2...

FROM 表名称

WHERE RN<(pagesize*currpage)) TEMP

WHERE TEMP.RN > (pagesize-1)*currpage;

3.1 Oracle 数据库同义词

如：如果要在其他用户访问scott用户的下的emp表,需要加上模式名称

‘scott.emp’，而且是scott用户授予了其他用的增删改查的权限

如：SELECT SYSDATE FROM DUAL;

SELECT TNAME FROM TAB WHERE TNAME = 'DUAL';

通过查询发现DUAL表是sys用户下的表。按道理应该sys.dual去访问

原因是因为oracle给sys.dual创建了一个同义词dual;

创建同义词的语法：

CREATE [PUBLIC] SYNONYM 同义词名称 FOR 用户名.表名;

EX:CREATE PUBLIC SYNONYM emp FOR scott.emp;

SELECT * FROM EMP;(此时的EMP就是刚刚创建的同义词) 如果不加PUBLIC只能在创建的那个用户下使用，如果加了PUBLIC则可以在所有的用户下面使用。这个创建需要在管理员用户下

删除创建的同义词

DROP SYNONYM emp;

3.2 Oracle 数据库视图

视图(VIEW)的基本概念就是将复杂的查询进行封装

语法：创建视图的语法

CREATE [OR REPLACE] VIEW 视图名称 AS 子查询;

EX:CREATE VIEW myview AS SELECT * FROM EMP WHERE DEPTNO =30;

使用视图：

EX：SELECT ENAME,JOB FROM myview;

创建的视图会被存放在‘user_views’字典表中。

SELECT * FROM USER_VIEWS;

普通用户需授权后才能创建视图

默认在scott用户下无法创建视图权限不足

授予scott创建视图的权限

GRANT CREATE VIEW TO scott;

替换视图：

CREATE OR REPLACE VIEW myview AS SELECT * FROM EMP WHERE DEPTNO =30;

DEPTNO =30;就是视图的创建条件

如果视图不存在则创建视图，如果视图已经存在则替换视图

修改视图的创建条件

UPDATE MYVIEW SET deptno = 40 WHERE empno= 7521; 此时会改变原表中的信息。

如果想要原表中的信息不被更改，需要加上关键字

执行这个SQL语句会改变视图的创建条件。为了保护视图的创建条件不被

修改，可以加上'WITH CHECK OPTION'

EX:CREATE OR REPLACE VIEW myview AS SELECT * FROM EMP WHERE DEPTNO =30 WITH CHECK OPTION;

但是加上个条件只会让创建条件不被修改，而其他字段可以修改

UPDATE MYVIEW SET sal = 99999 WHERE empno= 7521;

如果想要让视图的所有字段都不会被修改加上WITH READ ONLY

CREATE OR REPLACE VIEW myview AS SELECT * FROM EMP WHERE DEPTNO =30 WITH READ ONLY;

UPDATE MYVIEW SET sal = 99999 WHERE empno= 7521;

此时只会改变视图中的显示，并不会改变原表中的任何信息，此时值赋予了视图中查看功能，但是修改，删除等功能将不被允许

删除创建的视图

DROP VIEW myview;

3.3 Oracle 数据库数据的导入导出

导出：

1、创建导入文件的路径(d:\\backup)

2、通过命令行进入文件路径

3、exp命令

4、登陆用户名

默认导出的文件名为expdat.dmp

导入：

1、进入到备份的文件夹

2、执行imp命令

9.0 实际案例

Oracle数据库

1.1 数据库环境安装

1.2 数据库的卸载

1.3 Oracle数据库---scott用户

1.3.1 切换用户

1.3.2 sqlplus中的显示格式化

1.4 Oracle数据库的用户管理

1.5 Oracle 数据库数据定义语言Data Definition Language（DDL）

1.6 Oracle 数据库数据操纵语言（DML）

3.0 Oracle数据库数据编号 ROWNUM

3.1 Oracle 数据库同义词

3.2 Oracle 数据库视图

3.3 Oracle 数据库数据的导入导出

9.0 实际案例

1、查询每个雇员编号、姓名、职位、基本工资、工资等级

(提示：emp中的sal在salgrade表中的losal和hisal之间)

SELECT EMPNO,ENAME,JOB,SAL,GRADE

FROM EMP,SALGRADE

WHERE SAL BETWEEN LOSAL AND HISAL;

2、查询每个雇员的编号、姓名、基本工资、部门名称、工资等级

SELECT EMPNO,ENAME,SAL,DNAME,GRADE

FROM EMP E,SALGRADE,DEPT D

WHERE E.DEPTNO =D.DEPTNO AND(SAL BETWEEN LOSAL AND HISAL);

1、查询工资高于在30部门的所有员工的姓名、工资、部门名称、部门人数
第一步：查询部门30 的所有员工的工资(多行单列的结果)
SELECT SAL
FROM EMP
WHERE DEPTNO = 30;
第二步：查询高于30部门的工资的员工姓名、工资(使用>ALL)
SELECT ENAME,SAL
FROM EMP
WHERE  SAL >ALL (SELECT SAL
FROM EMP
WHERE DEPTNO = 30);
第三步：查询部门名称（多表查询）;
SELECT E.ENAME,E.SAL,D.DNAME
FROM EMP E,DEPT D
WHERE  E.SAL >ALL (SELECT SAL
FROM EMP
WHERE DEPTNO = 30)
   AND D.DEPTNO = E.DEPTNO;

第四步：查询部门人数
SELECT DEPTNO,COUNT(EMPNO)
FROM EMP
GROUP BY DEPTNO;

SELECT E.ENAME,E.SAL,D.DNAME,TEMP.COUNT
FROM EMP E,DEPT D,(SELECT DEPTNO,COUNT(EMPNO) COUNT
FROM EMP
GROUP BY DEPTNO
   ) TEMP
WHERE  E.SAL >ALL (SELECT SAL
   FROM EMP
   WHERE DEPTNO = 30)
   AND D.DEPTNO = E.DEPTNO
   AND E.DEPTNO = TEMP.DEPTNO;

2、查询工资比‘SMITH’或‘ALLEN’ 高的所有员工的编号、姓名、部门名称、领导姓名、最高、低工资
第一步：查询‘SMITH’或‘ALLEN’ 的工资
SELECT SAL
FROM EMP
WHERE ENAME IN('SMITH','ALLEN');

第二步：查询高于SMITH’或‘ALLEN’ 员工姓名、编号(在WHERE子句中使用子查询)
SELECT ENAME,EMPNO
FROM EMP
WHERE SAL >ANY(SELECT SAL
   FROM EMP
   WHERE ENAME IN('SMITH','ALLEN'))
  AND ENAME NOT IN  ('SMITH','ALLEN');

第三步：查询领导姓名
SELECT E.ENAME,E.EMPNO,M.ENAME
FROM EMP E,EMP M
WHERE E.SAL >ANY(SELECT SAL
   FROM EMP
   WHERE ENAME IN('SMITH','ALLEN'))
  AND E.ENAME NOT IN  ('SMITH','ALLEN')
  AND E.MGR = M.EMPNO(+);

第四步：查询部门信息
SELECT E.ENAME,E.EMPNO,M.ENAME,D.DNAME
FROM EMP E,EMP M,DEPT D
WHERE E.SAL >ANY(SELECT SAL
   FROM EMP
   WHERE ENAME IN('SMITH','ALLEN'))
  AND E.ENAME NOT IN  ('SMITH','ALLEN')
  AND E.MGR = M.EMPNO(+)
  AND E.DEPTNO = D.DEPTNO;
第五步：查询最高、底工资

SELECT E.ENAME,E.EMPNO,M.ENAME,D.DNAME,TEMP.MAX,TEMP.MIN,TEMP.AVG,TEMP.COUNT
FROM EMP E,EMP M,DEPT D,(
SELECT DEPTNO,MAX(SAL) MAX,MIN(SAL) MIN,AVG(SAL)AVG,COUNT(EMPNO) COUNT
FROM EMP
    GROUP BY DEPTNO) TEMP
WHERE E.SAL >ANY(SELECT SAL
   FROM EMP
   WHERE ENAME IN('SMITH','ALLEN'))
  AND E.ENAME NOT IN  ('SMITH','ALLEN')
  AND E.MGR = M.EMPNO(+)
  AND E.DEPTNO = D.DEPTNO
  AND TEMP.DEPTNO = E.DEPTNO;