Oracle数据库

Oracle数据库 入门教程

版权声明：本文为博主原创文章，遵循 CC 4.0 by-sa 版权协议，转载请附上原文出处链接和本声明。

本文链接：https://blog.csdn.net/qq_42589612/article/details/80943957

Oracle数据库

1.1 数据库环境安装

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

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

  2. 数据库创建完毕后，需要设置数据库的默认用户。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" 1
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(列|数据)
字符串长度：ength(列)
字符串替换：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 数据库表约束的修改

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 currpagepagesize
WHERE TEMP.RN > 10;(currpage-1)pagesize

oracle分页查询公式
SELECT 列1,列2...
FROM (SELECT ROWNUM RN,列1,列2...
FROM 表名称
WHERE RN<(pagesizecurrpage)) 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 实际案例

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;
</article>

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 212,332评论 6赞 493
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 90,508评论 3赞 385
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 157,812评论 0赞 348
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 56,607评论 1赞 284
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 65,728评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,919评论 1赞 290
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,071评论 3赞 410
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,802评论 0赞 268
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,256评论 1赞 303
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,576评论 2赞 327
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,712评论 1赞 341
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,389评论 4赞 332
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,032评论 3赞 316
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,798评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,026评论 1赞 266
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,473评论 2赞 360
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,606评论 2赞 350

推荐阅读更多精彩内容

  • Oracle数据库学习一
    1.简介 数据存储有哪些方式？电子表格，纸质文件，数据库。 那么究竟什么是关系型数据库？ 目前对数据库的分类主要是...
    乔震阅读 1,710评论 0赞 2
  • Oracle数据库学习二
    5.多表查询 多表查询 目的：从多张表获取数据 前提：进行连接的多张表中有共同的列 等连接 通过两个表具有相同意义...
    乔震阅读 1,216评论 0赞 0
  • Oracle 数据库 知识总结
    Oracle 基础 一、概述 Oracle的安装 下载地址：oracle官网卸载： 安装目录/dbhome_1/d...
    七弦桐语阅读 831评论 2赞 10
  • D27 五公里有感
    原定的宝地广场到浦联公寓，因为一碗小馄饨路线变得长远。 跟往常一样，下班后的五公里成了期待的美事。大约一公里左右，...
    范琳琳123阅读 198评论 0赞 2
  • 日精进打卡第二十六天
    孔方圆：国学践行22期学员，六项精进299期同期 【日精进第二十六天】 《六项精进》大纲，背诵3遍，共计78遍； ...
    孔方圆阅读 198评论 0赞 1