文章目录
01、安装注意：
- 压缩文件全部解压到一个 database 文件里面，然后进行安装。
- 安装时的安装路径里面不能有汉字或者空格。
登录密码：tiger
02、oracle 使用
1、登录oracle
cmd -> sqlplus
sys as sysdba
密码
2、解锁账户
# 解锁账户
alter user 用户名 account unlock;
# 锁定账户
alter user 用户名 account lock;
3、切换用户
# 切换到普通用户
conn 用户名/密码
# 切换到超级管理员
conn sys as sysdba
4、查看当前用户
show user;
5、查看表空间中的数据表
select table_name from user_tables;
6、查看某张表信息
select * from 表名字
03、Oracle相关服务
1、Oracle ORCL VSS Writer Service
Oracle卷映射拷贝写入服务，VSS（Volume Shadow Copy Service）能够让存储基础设备（比如磁盘，阵列等）创建高保真的时间点映像，即映射拷贝（**shadow copy**）。它可以在多卷或者单个卷上创建映射拷贝，同时不会影响到系统的系统能。（非必须启动）
2、OracleDBConsolexx（xx表示实例名称）
Oracle数据库控制台服务，orc是Oracle的实例标识，默认的实例为orcl。在运行Enterprise Manager（企业管理器OEM）的时候，需要启动这个服务。（非必须启动）
3、OracleJobSchedulerORCL
Oracle作业调度（定时器）服务，ORCL是Oracle实例标识。（非必须启动）
4、OracleMTSRecoveryService
服务端控制。该服务允许数据库充当一个微软事务服务器*MTS*、****COM/COM+对象和分布式环境下的事务的资源管理器。（非必须启动）
5、OracleOraDb11g_home1ClrAgent
Oracle数据库.NET扩展服 务的一部分。（非必须启动）
6、OracleOraDb11g_home1TNSListener
监听器服务，服务只有在数据库需要远程访问的时候才需要。（非必须启动，下面会有详细详解）。
（监听器服务，服务只有在数据库需要远程访问时才需要（无论是通过另外一台主机还是在本地通过SQL*Net 网络协议都属于远程访问），不用这个服务就可以访问本地数据库，它的缺省启动类型为自动。服务进程为TNSLSNR.EXE，参数文件Listener.ora，日志文件listener.log，控制台LSNRCTL.EXE，默认端口1521、1526。）
7、OracleServicexx（xx表示实例名称）
数据库服务(数据库实例)，是Oracle核心服务该服务，是数据库启动的基础， 只有该服务启动，Oracle数据库才能正常启动。(必须启动)
（数据库服务，这个服务会自动地启动和停止数据库。如果安装了一个数据库，它的缺省启动类型为自动。服务进程为ORACLE.EXE，参数文件initSID.ora，日志文件SIDALRT.log，控制台SVRMGRL.EXE、SQLPLUS.EXE。）
对新手来说，要是只用Oracle自带的sqlplus的话，只要启动OracleServiceORCL即可，要是使用PL/SQL Developer等第三方工具的话，OracleOraDb11g_home1TNSListener服务也要开启。OracleDBConsoleorcl是进入基于web的EM必须开启的，其余服务很少用。注：ORCL是数据库实例名，默认的数据库是ORCL，你可以创建其他的，即OracleService+数据库名。
04、数据库对象
数据库对象，是数据库的组成部分，有表、约束 索引、视图、序列、同义词、触发器存储过程、函数等。
| 对象 | 解释 |
|---|---|
| 表 | 表是用来存放用户数据的对象，由行和列组成，列就是字段，行就是表中的记录 |
| 约束 | 保证数据完整性的规则，设置在单个字段或者多个字段组合上，写入这些字段的数据必须符合约束的限制 |
| 视图 | 虚表，是一个命名的查询，用于改变基表数据的显示，简化查询。访问方式与表相同，同样可使用查询语句 |
| 索引 | 构建于表的单字段或者字段组合上，用于加速对表中数据的查询 |
| 序列 | 产生顺序的不重复数字串，被作为主键约束值的参照 |
| 同义词 | 数据库对象的别名 |
05、权限
1、系统权限
允许用户在数据库中执行指定的行为，一般可 以理解成比较通用的一类权限。
| 系统权限 | 解释 |
|---|---|
| sysoper | 启动停止数据库，恢复数据库等 |
| sysdba | 所有SYSOPER功能的管理权限；创建数据库等权限 |
2、对象权限
允许用户访问和操作一个指定的对象，该对象 是一个确切存储在数据库中的命名对象。
| 权限分类 \ 对象类型 | 表（table） | 试图（view） | 序列（sequence） | 存储（procedure） |
|---|---|---|---|---|
| select(选择) | √ | √ | √ | |
| insert(插入) | √ | √ | ||
| update(更改) | √ | √ | ||
| delete(删除) | √ | √ | ||
| alter(修改) | √ | √ | ||
| index(索引) | √ | |||
| reference(引用) | √ | √ | ||
| execute(执行) | √ |
3、授予对象权限
grant 权限 | [all privileges] | [(column)] on [schema.]对象名 to {用户|public} [with grant option];
# all privileges：指定对象的所有权限
# (column)：在授予INSERT、REFERENCES或UPDATE权限时可以 指定列；
# schema：指定用户名，如果省略，默认为当前用户
# public：授予权限给所有用户
# with grant option：允许被授予权限的用户再授予对象权给其他人
4、回收对象权限
revoke 对象权限种类 on 对象名 from user;
06、用户管理
sys：系统内置的超级管理员 用于分配管理数据库账户、表空间，等其他对象。
scott：系统内置的普通管理员，示例账户。
1、创建用户
# 创建用户
create user 用户名 identified by 密码
[default tablespace tablespace_default] # 表示该用户在创建数据对象时使用的默认空间表
2、修改密码
超级管理员可以修改任何普通用户的密码，而不需要知道用户的旧密码。
# 修改密码
alter user 用户名 identified by 密码
3、删除用户
# 删除的用户中没有任何对象
drop user 用户名
# 删除的用户中有对象时
drop user 用户名 cascade
4、查看数据库中的所有用户
# 查看所有的用户
select * from dba_users;
# 查看所有用户名
# dba_开头的是查全库所有的，all_开头的是查当前用户可以看到的，user_开头的是查当前用户的
select username from user_users;
select username from dba_users;
上课时创建了一个 test 用户
5、赋予用户权限
用户被创建后，没有任何权限，包括登录。用户如果想登录至少有“create session”的权限。
# 须超级管理员给用户授权 #
grant 权限1,权限2 to 用户名;
# 建立连接的权限
grant create session to 用户名
# 创建表的权限
grant create table to 用户名;
# 赋予用户查询、插入某张表的权限（需要在表所在用户下使用以下语句赋予权限）
grant select on 表名 to 用户名;
grant insert on 表名 to 用户名;
grant read,write on directory dpdata1 to scott;
# 给用户无限表空间权限
grant unlinmited tablespace to 用户名;
6、回收权限
revoke 权限1,权限2 from 用户名;
7、用户状态
| 用户状态 | 解释 |
|---|---|
| OPEN | 正常状态，为用户帐号初始创建后状态 |
| EXPIRED | 密码过期状态，用户下次登录的时候需要修改密码 |
| LOCKED | 锁定状态，不能执行任何Oracle相关操作 |
07、角色的管理
1、创建角色
如果系统预定义的权限不符合用户的需要，那么数据库管理员可以创建更多的角色。
create role 角色名
2、为角色授权
# 为角色授权
grant 权限列表 to 角色列表
3、通过角色为用户授权
# 通过角色为用户授权
crant 角色列表 to 用户列表
4、回收权限
# 通过角色从用户回收权限
revoke 角色 from 用户;
# 从角色回收权限
revoke 权限 from 角色;
5、删除角色
drop role 角色;
6、预定义角色
Oracle数据库预先定义好的角色,通常包括：
| 角色 | 概述 |
|---|---|
| dba | 该角色中的权限通常赋给数据库管理员 |
| connect | 是授予最终用户的典型权利，最基本的权利，能够连接到ORACLE数据库中，并在对其他用户的表有访问权限时，做SELECT、UPDATE、INSERTT等操作。 |
| resource | 是授予开发人员的，能在自己的方案中创建表、序列、视图等。 |
7、查看角色所包含的权限
# 查看当前用户下角色中包含的系统权限
select * from role_sys_privs where role='角色名';
# 查看数据库下角色中包含的系统权限
select * from dba_sys_privs where grantee='角色名'
08、对用户和角色理解
狼人杀
你自己 = 用户
身份牌 = 角色
09、表空间
创建表空间时必须创建数据文件，增加数据文件时也必须指定表空间。
1、创建表空间
create tablespace 表空间名
datafile 'C:\Oracle\my_tablespace\wu_space.dbf' # 数据文件的名称和路径
size 3m
autoextend on next 1m # 设置文件为自动扩展或非自动扩展。如果时自动扩展，则设置 next 的值。
maxsize 50m;
# 注意文件夹是否存在 #
2、给用户指定表空间
# 创建用户同时设置默认表空间
create user 用户名 identified by 密码
default tablespace 表空间名
！！！最好是创建用户的时候就指定默认的表空间！！！
# 用户存在的情况下指定默认表空间（指定了表空间然后还需要指定配额）
alter user 用户名 default tablespace 表空间名;
# 给用户设置表空间并分配一定的配额（只有这样了才能在数据表中添加数据）
alter user 用户名 quota 尺寸 on 表空间名字;
3、查询所有的表空间
# dba_开头的是查全库所有的，all_开头的是查当前用户可以看到的，user_开头的是查当前用户的
select tablespace_name from user_tablespaces;
select tablespace_name from dba_tablespaces;
上课时创建了一个 wu_space 表空间。
10、dba_，all_，user_的区别
dba_开头的是查全库所有的，all_开头的是查当前用户可以看到的，user_开头的是查当前用户的
11、表
1、创建表
# 必须具备创建表的权限、一定的存储空间
create table 表名(
列名 数据类型 约束,
列名 数据类型 约束,
... ...
);
a、示例
create table student(
sno number primary key,
sname varchar2(50) not null,
sbirth date,
sgrade number(2) foreign key references dept(deptno) ,
ssex char(5) check(ssex='男' or ssex='女'),
stel char(11),
sid varchar2(18) unique,
smail varchar2(30)
);
# 注意：
在自己创建的用户下操作，分配表空间
列名不要用敏感词
不要都使用char
create table xi(
xiid number(2) primary key,
xiname varchar2(50) unique,
xiboss varchar2(50) not null,
xibelong varchar2(8) check(xibelong='南湖校区' or xibelong='浑南校区')
);
create table class(
cno number(6) primary key,
cname varchar2(20) unique,
xiid number(2) references xi(xiid)
);
insert into xi values(1,'计算机科学系','张三','南湖校区')
insert into xi values(1,'计算机科学系','张三','南湖校区')
b、用子查询语法创建表
CREATE TABLE table[(column, column...)] AS subquery;
# 示例：
CREATE TABLE dept10
AS
SELECT empno, ename, sal＋1000 newSalary
FROM emp WHEREdeptno=10;
2、数据类型
a、字符型
| 数据类型 | 说明 |
|---|---|
| CHAR(size) | 固定长度字符型数据，长度的大小以字节为单位，默认和最小长度为1；最大长度为2000 |
| VARCHAR2 (size) | 可变长度字符数据，最小长度是1；最大长度是4000 |
| CLOB | 可变长度字符数据，最大可存储4G数据 |
b、数值型
| 数据类型 | 说明 |
|---|---|
| NUMBER | 数值型：可以表示整数，也可以表示小数，表示范围-10的125次方到10的126次方 |
| NUMBER(n) | 整型 |
| NUMBER(p,s) | 数值型：总长度为p，小数位最大为s位，整数位最大为p-s位，p的范围从1到38，s的范围从-84到127 |
c、日期型
| 数据类型 | 说名 |
|---|---|
| DATE | 包括年月日时分秒 |
| TIMESTAMP | 9i之后新增的，进度比DATE更高一些，可以精确到毫秒 |
d、图片类型
| 数据类型 | 说明 |
|---|---|
| BLOB | 最大可存储4G二进制的数据，可以存放图片，声音，文件 最大可存储4G二进制的数据，可 |
3、引用另一个用户的表
如果一个表不属于当前用户，如果引用它，必须把用户名 放在表名的前面。例如，scott.emp
4、修改表
a、添加列
ALTER TABLE 表名 ADD (列名 数据类型[DEFAULT 默认值] [,列名 数据类型]...);
b、修改列
ALTER TABLE 表名 MODIFY (列名 数据类型[DEFAULT 默认值] [,列名 数据类型]...);
# 添加默认值示例
ALTER TABLE dossier MODIFY(sex DEFAULT ‘男’);
c、删除列
ALTER TABLE 表名 DROP (列名 [,列名]);
d、修改列名
ALTER TABLE 表名 RENAME COLUMN 原有列名 TO 新列名
5、删除表
DROP TABLE 表名；
6、重命名表
rename old_name TO new_name;
7、截断表
就是清空表
truncate table 表名;
truncate是DDL语言只能删除表中所有记录释放存储空间 ，使用rollback不可以回滚。
delete是DML语言，可以删除指定记录，不释放存储空间，使用 rollback可以回滚。
8、约束
a、约束类型
| 约束 | 说明 |
|---|---|
| not null | 非空约束，指定某列的所有行数据不能包含空值（常用） |
| unique | 唯一性约束，指定列或者列的组合 的所有行数据必须 唯一 |
| primary key | 主键约束，表的每行的唯一性标识，指定列或者列的组合的所有行数据必须唯一，不允许为null |
| foreign key | 外键约束，在列及引用列上建立的一种强制依赖关系（默认有关系，但一般不建立外键关系） |
| check | 检查性约束，在列上指定一个必须满足的条件（不要用） |
| default | 默认约束（少用） |
b、定义外键约束
# 定义在列一级
CREATE TABLE emp_fk1(
empno NUMBER(4),
ename VARCHAR2(10) NOT NULL,
job VARCHAR2(9),
deptno NUMBER(7,2) NOT NULL CONSTRAINT emp_deptno_fk REFERENCES dept (deptno),
sal number(7,2)
);
# 定义在表一级
CREATE TABLE emp_fk2(
empno NUMBER(4),
ename VARCHAR2(10) NOT NULL,
deptno NUMBER(7,2) NOT NULL,
CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno) REFERENCES dept (deptno) ON DELETE CASCADE
);
# emp_deptno_fk：约束名
# FOREIGN KEY: 定义在子表的列中
# REFERENCES: 引用列所在的父表
# ON DELETE CASCADE：当父表中的行被删除时，子表中相依 赖的行同时被删除；
# ON DELETE SET NULL：当父表的行被删除时，子表中相依赖 的行被转换为空值；
注：无ON DELETE CASCADE 或 ON DELETE SET NULL选项，当附表中的行被删除时，如果父表中的行在子表中被引用，则提 示不能被删除。
c、追加约束
ALTER TABLE 表名 ADD [CONSTRAINT 约束名字] 约束类型 (受约束影响的列的名字);
d、删除约束
ALTER TABLE 表名 DROP PRIMARY KEY|UNIQUE(受约束影响的列的名字)|CONSTRAINT 约束名 [CASCADE];
# cascade 级联删除
12、数据操作
1、插入数据
插入数据之前必须有相应的权限，并且要给表的所属用户设置表空间并分配一定的配额。
insert into 表名 (列名列表) values (值列表)
# 如果省略列名列表，则默认包括所有列。
# 字符和日期类型数据必须要用单引号括起来。
insert into 表名 (列名列表)
子查询
sysdate 函数记录当前日期类型。
a、插入日期型数据示例
INSERT INTO 表名 VALUES (2296,'AROMANO','SALESMAN',7782,TO_DATE('1997-2-3','YYYY-M-DD'),1300, NULL, 10);
# 月份为...月的时候，用mon
insert into student values('A001','张三','男',to_date('01-5月-05','yy-mon-dd'),100,1);
# 当要插入的日期格式为‘2000年1月1日’时
insert into student values('A001','张三','男',to_date('2000年1月1日','yyyy"年"m"月"d"日"'),100,1);
-
注意：
如果没有指定年份而是只给了第几年，则默认会是当前系统的年份。
例如：你想的是1988年但是只给了88，那么Oracle将默认会是2088
2、显示表结构
desc 表名
3、修改表数据
按照指定条件修改表中的某些行的列数据。
UPDATE 表名
SET column = value [, column = value]
[WHERE condition];
# 示例：
update emp
set sal=8888
where empno=7788;
4、删除数据：delete
DELETE [FROM] 表名
[WHERE condition];
13、事务
数据库中，通过事务来保证数据的一致性。
事务特性：原子性、一致性、隔离性、持久性。
1、提交/回滚
# 提交：以成功的方式结束事务
commit
# 回滚：以失败的方式结束事务
rollback
2、设置保存点
如果在一个事务内，想要回滚到指 定位置，不是回滚到事务的起始点，可以通过保存点(SAVEPOINT)来实现
savepoint savepoint_name;
# 回滚到指定保存点
rollback to savepoint_name;
a、示例：
create table test(
id number(3),
name char(5)
);
INSERT INTO test(id,name) values(1, 'a');
INSERT INTO test(id,name) values(2, 'b');
SAVEPOINT s1;
INSERT INTO test(id,name) values(3, 'c');
INSERT INTO test(id,name) values(4, 'd');
DELETE FROM test WHERE id in (1,3);
ROLLBACK TO s1;
DELETE FROM test WHERE id in (2,4);
COMMIT;
ROLLBACK;
3、锁
用来保证数据一致性的一种机制。
锁由Oracle自动管理，如一个DML操作，ORACLE默认的机制是在DML操作影响的行记录上自动加锁；当你的操作结束后会释放锁。
查询语句不会锁定任何记录，如果在查询语句后面加FOR UPDATE子句会锁定查询所影响的行记录;
14、简单的查询语句
1、结构化查询语言分类
| 语言 | 示例 |
|---|---|
| TPL（数据库处理语言） | commit、rollback |
| DDL（数据库定义语言） | create、alter、drop、truncate（截断表） |
| DCL（数据库控制语言） | grant、revoke |
| DML（数据库操作语言） | insert、delete、update |
| DQL（数据库查询语言） | select |
2、select语句
SELECT [distinct]{*|column|expression[alias]}
into 表名
FROM table
where 条件表达式
group by conditions
having conditions
order by conditions; -- 对结果集进行排序
# distinct：消除重复行
# 字符型数值区分大小写
3、列别名
-
方式1：列名 列别名
-
方式2：列名 as 列别名
-
注意：
当列别名中包含空格、要求区分大小写、含有特殊字符时需要加双引号。
4、连接操作符
SELECT ename||job AS "Employees" 2 FROM emp;
# 将表emp中的ename列和job列连接起来显示
5、比较操作符
=、>、>=、<、<=、<>（不等于）
6、运算符
a、特殊比较运算符
| 运算符 | 含义 |
|---|---|
| between…and… | 判断比较的值是否在某个范围内 |
| in(集合列表) | 判断比较的值是否和集合列表中的任何一个字相等 |
| like | 判断要比较的值是否满足部分匹配（模糊查询） |
| is null | 判断要比较的值是否为控制null |
模糊查询中的两个通配符
%：代表0或任意更多的字符
_：代表一个字符
b、逻辑运算符
| 逻辑运算符 | 含义 |
|---|---|
| and | 逻辑与，要求两个条件都为真结果才为真 |
| or | 逻辑或，只需要两个条件中的一个为真结果就返回真 |
| not | 逻辑非，用来对条件表达式取反 |
... WHERE deptno NOT IN (10, 20)
... WHERE sal NOT BETWEEN 3000 AND 5000
... WHERE ename NOT LIKE 'D%'
... WHERE mgr IS NOT NULL
优先级
| 优先级 | 运算分类 | 运算符举例 |
|---|---|---|
| 1 | 算术运算符 | *, , +, *, |
| 2 | 连接运算符 | || |
| 3 | 比较运算符 | =, <>, <, >, <=, >= |
| 4 | 特殊比较运算符 | between…and…，in，like，is，null |
| 5 | 逻辑非 | not |
| 6 | 逻辑与 | and |
| 7 | 逻辑或 | or |
练习题
# 代码练习
# 练习3：
select ename from emp
where ename like 'W%';
select ename from emp
where ename like '%T_';
select ename,comm from emp
where comm is null;
# 练习4：
# 1.查询工资超过2000并且职位是MANAGER,或者职位是SALESMAN的员工姓名、职位、工资
select ename,job,sal from emp
where sal>2000 and job = 'MANAGER';
# 2.查询工资超过2000并且职位是MANAGER或SALESMAN的员工姓名 、职位、工资。
select ename,job,sal from emp
where sal>2000 and job = 'MANAGER' or job = 'SALESMAN';
# 3.查询部门在10或者20，并且工资在3000到5000之间的员工姓名 、部门、工资。
select deptno,job,sal from emp
where deptno in (10,20) and sal between 3000 and 5000;
# 4.查询入职日期在81年，并且职位不是SALES开头的员工姓名、入职日期、职位。
select ename,hiredate,job from emp
where hiredate between to_date('1981-01-01','yyyy-mm-dd') and to_date('1981-12-31','yyyy-mm-dd') and job not like 'SALES%';
# 5.查询职位为SALESMAN或MANAGER，部门编号为10或者20，姓名包含A的员工姓名、职位、部门编号。
select ename,job,deptno from emp
where job in ('SALESMAN','MANAGER') or deptno in (10,20) or ename like '%A%';
7、order by子句
对结果集进行排序。
ASC（默认）：升序
DESC：降序
注意：ORDER BY 子句必须写在SELECT语句的后
排序规则(以升序为例)
| 类型 | 规则 |
|---|---|
| 数字 | 升序排列小值在前，大值在后。即按照数字大小顺序由小到大排列。 |
| 日期 | 升序排列相对较早的日期在前，较晚的日期在后。例 如’01-SEP-06’在’01-SEP-07’前 |
| 字符 | 升序排列按照字母由小到大的顺序排列，即由A-Z排列； 中文升序按照字典顺序排列。 |
| 空值 | 升序排列 中排在后，在降序排列 中排在最开始。 |
练习题
# 练习5
1.查询部门在20或30的员工姓名，部门编号，并按照工资升序排序排序。
select ename,deptno from emp
where deptno in (20,30)
order by sal;
2.查询工资在2000-3000之间，部门不在10号的员工姓名，部门编号，工资，并按照部门升序，工资降序排序。
select ename,deptno,sal from emp
where sal between 2000 and 3000 or deptno <> 10
order by septno;
select ename,deptno,sal from emp
where sal between 2000 and 3000 or deptno <> 10
order by sal desc;
3.查询入职日期在82年至83年之间，职位以 SALES或者MAN开头的员工姓名，入职日期，职位，并按照入职日期降序排序。
select ename,hiredate,job from emp
where hiredate between '1 1月 1982' and '31 12月 1983' and job like 'SALES%' or job like 'MAN%'
order by hiredate desc;
# 课后作业
1.查询入职时间在1982-7-9之后，并且不从事SALESMAN工作的员工姓名、入职时间、职位。
select ename,hiredate,job from emp
where hiredate > to_date('1982-7-9','yyyy-mm-dd') and job <> 'SALESMAN';
2.查询员工姓名的第三个字母是a的员工姓名。
select ename from emp
where ename like '__A%';
3.查询除了10、20号部门以外的员工姓名、部门编号。
select ename,deptno from emp
where deptno not in (10,20);
4.查询部门号为30号员工的信息，先按工资降序排序，再按姓名升序排序。
select * from emp
where deptno = 30
order by sal desc,ename;
5.查询没有上级的员工(经理号为空)的员工姓名。
select ename from emp
where mgr is null;
6.查询工资大于等于4500并且部门为10或者20的员工的姓名、工 资、部门编号。
select ename,sal,deptno from emp
where sal >= 4500 and deptno in (10,20);
15、函数
1、字符函数
a、大小写转换函数
| 函数 | 说明 |
|---|---|
| lower(列名|表达式) | 将大写或大小写混合的字符转换成小写 |
| upper(列名|表达式) | 将小写或大小写混合的字符转换成大写 |
| initcap(列名|表达式) | 将每个单词的第一个字母转换成大写，其余的字母都转换成小写 |
b、字符处理函数
| 函数 | 说明 | 示例 | 值 |
|---|---|---|---|
| concat(值1|express ion1,值2 expression2) | 连接两个值，等同于|| | CONCAT(‘Good’, ‘String’) | GoodString |
| substr(column l expression,n1[,n2]） | 返回第一个参数中，从第n1位开始，长度为n2的子串。 如果n2省略，取第n1位开始的所有字符。 如果n1是负值，表示从第一个参数的后面第abs(n1)位开始向右取长度为n2的子串。 | SUBSTR(‘String’,1,3) | Str |
| length(column |expression) | 取字符长度 | LENGTH(‘String’) | 6 |
| instr(s1,s2,[,n1],[n2]) | 返回s1中，子串s2从n1开始，第n2次出现的位置。n1,n2默认值 为1 | INSTR(‘String’, ‘r’) | 3 |
| lpad(s1,n1,s2) | 返回s1被s2从左面填充到n1长度后的字符串 | LPAD(sal,10,’*’) | ******5000 |
| rpad(s1,n1,s2) | 返回s1被s2从右面填充到n1长度后的字符串。 | RPAD(sal,10,’*’) | 5000****** |
| trim(leading | trailing | both trim_character from trim_source) | 去除字符串头部或尾部（头尾）的字符 | TRIM(‘S’ FROM ‘SSMITH’) | MITH |
| replace(s1,s2,s3) | 把s1中的s2用s3替换 | REPLACE（‘abc’,‘b’,‘d’) | abc |
2、数值函数
| 函数 | 说明 | 示例 | 值 |
|---|---|---|---|
| round(column | expression, n) | 将列或表达式所表示的数值四舍五入到小数点后的第n位 | round(45.926, 2) | 45.93 |
| trunc(column | expression , n) | 将列或表达式所表示的数值截取到小数点后的第n位 | TRUNC(45.926, 2) | 45.92 |
| MOD(m,n) | 取m除以n后得到的余数 | MOD(1600, 300) | 100 |
dual表
dual：是一张虚表，不能保存任何数据，只有一个字段， 一行记录。当我们不希望从任何表中读取数据，同时又想 利用SQL引擎中的计算表达式的能力帮我们运算时，就可以使用DUAL表。
3、日期函数
Oracle默认的日期格式是：DD-MON-RR
a、日期类型数学运算
日期类型可以加减数字，功能是在该日期上加减对应的天数。如：’10-AUG-06’+15结果是’25-AUG-06’ 。
日期类型之间可以进行减操作，功能是计算两个日期之间间隔了多少天。如：’10-AUG-06’-‘4-AUG-06’结果四舍五入后是6天
如果需要对一个日期进行加减相应小时操作，可以使用 n/24来实现
b、常用日期函数
| 函数 | 说明 |
|---|---|
| SYSDATE | 返回系统日期 |
| MONTHS_BETWEEN(日期1,日期2) | 返回两个日期类型数据之间间隔的自然月数 |
| ADD_MONTHS(指定日期,月数) | 返回指定日期加上相应的月数后的日期 |
| NEXT_DAY(某一日期,下一个指定日期) | 返回某一日期的下一个指定日期 |
| LAST_DAY(指定日期) | 返回指定日期当月最后一天的日期 |
| ROUND(date[,'fmt’]) | 将date按照fmt指定的格式进行四舍五入，fmt为可选项，如果没有指定fmt，则默认为DD，将date四舍五入为最近的天。 格式码：世纪CC，年YY，月MM，日DD，小时HH24，分MI，秒SS |
| TRUNC(date[，‘fmt’) | 将date按照fmt指定的格式进行截断，fmt为可选项，如果没有指定fmt，则默认为‘DD’，将date截取为最近的天。 |
EXTRACT([YEAR] [MONTH][DAY] FROM [日期类型表达式]) | 返回日期类型数据中的年份、月份或者日。 |
4、转换函数
| 函数 | 说明 |
|---|---|
| TO_CHAR(date, ‘fmt’) | 将日期、数值转换为字符型数据 |
| TO_NUMBER(char[, fmt]) | 将一个字符串转换为数值型数据 |
| TO_DATE(char[, ‘fmt’]) | 将一个字符串转换为日期型数据 |
TO_CHAR 用于日期型
| 格式码 | 说明 |
|---|---|
| YYYY | 完整的年份数字表示 |
| YEAR | 年份的英文表示 |
| MM | 用两位数字来表示月份 |
| MONTH | 月份的全名 |
| DAY | 星期几 |
| DY | 用3个英文字符缩写来表示星期几 |
TO_CHAR 用于数值型
| 格式码 | 说明 |
|---|---|
| 9 | 一位数字 |
| 0 | 显示前导零 |
| $ | 显示美元符号 |
| L | 显示本地货币符号，羊角符 |
| . | 显示小数点 |
| , | 显示千位符 |
5、通用函数
a、与空值相关的一些函数
| 函数 | 说明 |
|---|---|
| NVL (expr1,expr2) | 如果expr1不是null,返回expr1,否则返回expr2 |
| NVL2(expr1,expr2,expr3) | 如果expr1不是null,返回expr2,否则返回expr3 |
| NULLIF(expr1,expr2) | 比较两个表达式,如果相等，返回null,否则，返回第 一个表达式 |
| COALESCE( 表达式 1 , 表达式 2 ,… 表达式 n ) | 函数是对NVL 函数的扩展。COALESCE函数的功能是返回第一个不为空的参数，参数个数不受限制 |
b、条件处理函数
case函数
# 语法
CASE expr
WHEN comparison_expr1 THEN return_expr1
[WHEN comparisonexpr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
# 示例：
# 在员工表中查询出员工的工资，并计算应交税款：如果工资小于1000,税率为0，如果工资大于等于1000并小于2000， 税率为10％，如果工资大于等于2000并小于3000，税率为 15％，如果工资大于等于3000，税率为20％。
select sal,(case
when sal<1000 then 0
when sal>=1000 and sal<2000 then sal*0.1
when sal>=2000 and sal <3000 then sal*0.15
when sal>=3000 then sal*0.2
else 0 end
) 税款 from emp;
decode函数
DECODE(字段 | 表达式 , 条件 1 , 结果 1[, 条件 2 , 结果 2 … ,][,缺省值])
# 示例：
SELECT ename, deptno,
decode(deptno,
10,'销售部',
20,'技术部',
30, '管理部',
'无') deptname
FROM emp;
6、聚合函数
| 函数 | 说名 |
|---|---|
| avg(x[distinct | all]) | 计算列的平均值 |
| count(x[distinct | all]) | 统计满足条件的非空的行记录数。 如果是count(*)的话则不会忽略非空的行记录。 |
| max(x[distinct | all]) | 计算列的最大数 |
| min(x[distinct | all]) | 计算列的最小数 |
| sum(x[distinct | all]) | 计算列的总和 |
16、多表连接
1、笛卡尔积
一个表中的所有行和另一个表的所有行都进行了连接（配对）。
发生情况:
- 连接条件被省略
- 连接条件是无效的
2、表别名
# 使用表别名前
SELECT emp.empno, emp.ename, emp.deptno, dept.deptno, dept.loc
FROM emp, dept
WHERE emp.deptno=dept.deptno;
# 使用表别名后
SELECT e.empno, se.ename, e.deptno, d.deptno, d.loc
FROM emp e, dept d
WHERE e.deptno= d.deptno;
3、内连接
一般用关键字 ineer join 来实现，其中 inner 关键字可以省略。
# 语法格式：
select columns_list
from table_name1 [inner] join table_name2
on 连接条件;
a、等值连接
# 语法：
SELECT table1.column, table2.column FROM table1,table2
WHERE table1.column1=table2.column2 [and 其他查询条件]; - 连接条件
select table1.colum, table.colum
from table1 join table2
on table1.column1=table2.column2 [and 其他查询条件];
b、非等值连接
# 查询每个员工的编号，姓名，工资，工资等级，所在工作城市，按照工资等级进行升序排序。
select e.empno 员工编号,e.ename 姓名,e.sal 工资,s.grade 工资等级,d.loc 工作城市
from emp e,dept d,salgrade s
where e.deptno = d.deptno and e.sal between s.losal and s.hisal
order by s.grade;
select e.empno 员工编号,e.ename 姓名,e.sal 工资,s.grade 工资等级,d.loc 工作城市
from emp e
join dept d on e.deptno = d.deptno
join salgrade s on e.sal between s.losal and s.hisal
order by s.grade;
4、外连接
a、(+)
# 语法：
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column(+)=table2.column;
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column=table2.column(+);
# 查询所有雇员姓名，部门编号，部门名称，包括没有员工的部门也要显示出来
SELECT e.ename 姓名,d.deptno 部门编号,d.dname 部门名称
FROM emp e,dept d
WHERE e.deptno(+) = d.deptno
ORDER BY e.deptno;
+号可以这样来理解： + 表示补充，即哪个表有加号，这个表就是匹配表。如果加号写在右表，左表就是全部显示，所以是左连接。如果加号写在左表，右表就是全部显示，所以是右连接。
b、左外连接
左外连接以FROM子句中的左边表为基表，该表所有行数据 按照连接条件无论是否与右边表能匹配上都会被显示出来。
SELECT e.ename,e.deptno,d.loc
FROM emp e LEFT OUTER JOIN dept d ON (e.deptno = d.deptno);
c、右外连接
右外连接以FROM子句中的右边表为基表，该表所有行数据 按照连接条件无论是否与左边表能匹配上，都会被显示出 来。
SELECT e.ename,e.deptno,d.loc
FROM emp e RIGHT OUTER JOIN dept d ON (e.deptno = d.deptno);
d、全外连接
全外连接返回两个表等值连接结果，以及两个表中所有等值连接失败的记录
SELECT e.ename,e.deptno,d.loc
FROM emp e FULL OUTER JOIN dept d ON (e.deptno = d.deptno);
5、自连接
是一个表通过某种条件和本身进行连接的一种方式，就如同多个表连接一样。
6、交叉连接
交叉连接会产生连个表的交叉乘积，和两个表之间的笛 卡尔积是一样的；
SELECT emp.empno,emp.ename,emp.sal,emp.deptno,dept.loc
FROM emp CROSS JOIN dept;
7、自然连接
自然连接是对两个表之间相同名字和数据类型的列进行的等值连接；
如果两个表之间相同名称的列的数据类型不同， 则会产生错误。
SELECT empno,ename,sal,deptno,loc
FROM emp NATURAL JOIN dept;
using子句
USING子句可以指定用某个或某几个相同名字和数据类型的列作为连接条件。
NATURAL JOIN子句和USING子句是相互排斥的，不能同时使用。
SELECT e.ename,e.ename,e.sal,deptno,d.loc
FROM emp e JOIN dept d USING (deptno)
WHERE deptno = 20;
on子句
自然连接条件基本上是具有相同列名的表之间的等值连接； 如果要指定任意连接条件，或指定要连接的列，则可以使用ON子句；
8、内连接和外连接的区别
内连接消除了与另一个表中的任何不匹配的行，而外连接扩展了内连接的结果集，除了返回所有匹配的行外，返回一部分或全部不匹配的行，这就要取决于外连接的种类。
练习题
练习1
1.写一个查询，显示所有员工姓名，部门编号，部门名称。
select e.ename,e.deptno,d.dname
from emp e,dept d
where e.deptno = d.deptno;
2.写一个查询，显示所有工作在CHICAGO并且奖金不为空的员工姓名，工作地点，奖金
select e.ename,d.loc,e.comm
from emp e,dept d
where e.deptno = d.deptno and d.loc = 'CHICAGO' and e.comm is not null;
3.写一个查询，显示所有姓名中含有A字符的员工姓名，工作地点。
select e.ename,d.loc
from emp e,dept d
where e.deptno = d.deptno and e.ename like '%A%';
练习2
1.查询所有雇员姓名，部门编号，部门名称，包括没有员工的部门也要显示出来
SELECT e.ename, d.deptno, d.dname
FROM emp e,dept d
WHERE e.deptno(+) = d.deptno
ORDER BY e.deptno;
练习4
1.创建一个员工表和部门表的交叉连接。
select * from emp cross join dept;
2.使用自然连接，显示入职日期在80年5月1日之后的员工姓名，部门名称，入职日期
select e.ename,d.dname,e.hiredate
from emp e
natural join dept d
where e.hiredate > to_date('1980-5-1','yyyy-mm-dd');
3.使用USING子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点
select e.ename,d.dname,d.loc
from emp e
join dept d using (deptno)
where d.loc = 'CHICAGO';
4.使用ON子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点，薪资等级
select e.ename,d.dname,d.loc,s.grade
from emp e
join dept d on d.deptno=e.deptno
join salgrade s on e.sal between s.losal and s.hisal
where d.loc = 'CHICAGO';
5.使用左连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。
select e1.ename 员工姓名,e2.ename 经理姓名
from emp e1
left join emp e2 on e1.mgr = e2.empno;
6.使用右连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。
select e2.ename 员工姓名,e1.ename 经理姓名
from emp e1
right join emp e2 on e1.empno = e2.mgr;
课后作业
1.显示员工SMITH的姓名，部门名称，直接上级名称
select e1.ename,d.dname,e2.ename boss
from emp e1
join dept d on (e1.deptno = d.deptno)
join emp e2 on (e1.mgr = e2.empno)
where e1.ename = 'SMITH';
2.显示员工姓名，部门名称，工资，工资级别，要求工资级别大于4级。
select e.ename,d.dname,e.sal,s.grade
from emp e
join dept d on (e.deptno = d.deptno)
join salgrade s on (e.sal between s.losal and s.hisal)
where s.grade > 4;
3.显示员工KING和FORD管理的员工姓名及其经理姓名。
select e1.ename 员工,e2.ename 经理
from emp e1
join emp e2 on e1.mgr = e2.empno
where e2.ename in ('KING','FORD');
4.显示员工姓名，参加工作时间，经理名，参加工作时间， 要求参加时间比经理早。
select e1.ename 员工姓名,e1.hiredate 员工参加工作时间,e2.ename 经理名,e2.hiredate 经理参加工作时间
from emp e1
join emp e2 on e1.mgr = e2.empno
where e1.hiredate<e2.hiredate;
17、分组查询
group by子句
group by子句一般与聚合函数使用。
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
在select列表中除了分组函数那些项，所有列都必须包含在 group by 子句中，但是 group by 中所指定的列并不是必须出现在select列表中。
如果使用了 聚合函数，那么就必须使用 group by 子句。
消除重复记录
distinct：消除重复记录。
SELECT COUNT(DISTINCT deptno) FROM emp;
空值处理
除了COUNT（*）之外，其它所有聚合函数都会忽略列中的空值，然后再进行计算。
nvl函数：使分组函数强制包含含有孔子的记录。
SELECT AVG(NVL(comm,0)) FROM emp;
having子句
having 子句和 where 子句的相似之处都是定义搜索条件。唯一不同的就是 having 子句中可以包含聚合函数，但是在 where 子句中则不能包含聚合函数。
SELECT deptno, max(sal)
FROM emp
GROUP BY deptno
HAVING max(sal)>2900;
SELECT语句执行过程
- 通过FROM子句中找到需要查询的表；
- 通过WHERE子句进行非分组函数筛选判断；
- 通过GROUP BY子句完成分组操作；
- 通过HAVING子句完成组函数筛选判断；
- 通过SELECT子句选择显示的列或表达式及组函数；
- 通过ORDER BY子句进行排序操作。
练习题
select COUNT(*) 员工人数,max(e.sal) 最高工资,min(e.sal) 最低工资
from emp e left join dept d on e.deptno=d.deptno
where d.loc='CHICAGO';
课后作业
1.查询部门平均工资在2500元以上的部门名称及平均工资。
select d.dname 部门名称,avg(e.sal) 平均工资
from emp e
join dept d on e.deptno = d.deptno
group by d.dname
having avg(sal)>2500;
2.查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序。
select e.job 岗位,avg(e.sal) 平均工资
from emp e
group by e.job
having e.job not like 'SA%'
order by avg(e.sal) desc;
3.查询部门人数在2人以上的部门名称、最低工资、最高工资,并对求得的工资进行四舍五入到整数位。
select d.dname 部门名称,round(min(nvl(e.sal,0))) 最低工资,round(max(nvl(e.sal,0))) 最高工资
from emp e
right join dept d on e.deptno = d.deptno
group by d.dname
having count(d.dname)>2;
4.查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。
select e.job,sum(e.sal)
from emp e
join dept d on e.deptno = d.deptno
group by e.job
having sum(e.sal)>=2500 and job<>'SALESMAN';
5.显示经理号码和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，不包括最低工资小于3000的，按最低工资由高到低排序。
select e2.mgr,e2.ename,min(e1.sal)
from emp e1
left join emp e2 on e1.mgr = e2.empno
group by e2.mgr,e2.ename
having min(e1.sal)>3000
order by min(e1.sal) desc;
6.写一个查询，显示每个部门最高工资和最低工资的差额。
select max(sal)-min(sal)
from emp
group by deptno;
18、子查询
括号内的查询叫做子查询（内部查询）。
多行运算符
IN
判断是否与子查询的任意的一个返回值相同。
ANY
表示和子查询的任意一行结果进行比较，有一个满足条件即可。
| 说明 | |
|---|---|
| <ANY | 表示小于子查询结果集中的任意个，即小于最大值就可以 |
| >ANY | 表示大于子查询结果集中的任意一个，即大于最小值就可以 |
| =ANY | 表示等于子查询结果中的任意一个，即等于谁都可以，相当于IN |
ALL
表示和子查询的所有行结果进行比较，每一行必须都满足条件。
| 说明 | |
|---|---|
| <ALL | 表示小于子查询结果集中的所有行，即小于最小值。 |
| >ALL | 表示大于子查询结果集中的所有行，即大于最大值 |
| =ALL | 表示等于子查询结果集中的所有行,即等于所有值，通常无意义。 |
多列子查询
多列子查询可以在一个条件表达式内同时和子查询的多个 列进行比较。
多列子查询通常用IN操作符完成。
# 查询出和1981年入职的任意一个员工的部门和 职位完全相同员工姓名部门职位入职日 职位完全相同员工姓名、部门、职位、入职日 期,不包括1981年入职员工。
SELECT ename, deptno, job, hiredate
FROM emp
WHERE (deptno, job) IN
(SELECT deptno,job 5FROMemp
FROM emp
WHERE to_char(hiredate,'YYYY')='1981')
AND to_char(hiredate,'YYYY')<>'1981';
如果子查询中有一条空值，则这条空值可能会导致主查询没有记录返回。因为所有的条件和空值比较结果都是空值。
可以使用 nvl(a1,a2) 来解决。
rownum 列
ROWNUM是个伪列 。功能是在每次查询时，返回结果集的顺序号， 这个顺序号是在记录输出时才一步一步产生的，第一行显示为1，第二行为2，以此类推。
注意：
- 如下SQL语句，SELECT * FROM EMP WHERE ROWNUM>2;查询 不到任何记录，因为ROWNUM是在记录输出时才生成，且总是 从1开始所以输出的第一条记录不满足>2的条件被过滤掉，第二条的ROWNUM又成了1，又不满足>2的条件，又被过滤掉， 依此类推，所以永远没有满足条件的记录，返回为空。所以 对于ROWNUM只能执行<、<=运算，不能执行>、>=或一个区间运算Between…And等
- ROWNUM和ORDER BY一起使用时，因为ROWNUM在记录输出时生成，而ORDER BY子句在最后执行，所以当两者一起使用时， 需要注意ROWNUM实际是已经被排了序的ROWNUM。即rownum的优先级高于order by。
分页查询
SELECT b.*
FROM (SELECT ROWNUM rn,[列名1,列名2,....列名n] FROM 表名1,[表名2,...表名n]
WHERE [条件表达式AND ] ROWNUM <=目标页数*每页记录数) b
WHERE rn> (目标页数-1)*每页记录数
或
SELECT b.*
FROM (SELECT ROWNUM rn,[列名1,列名2,....列名n] FROM 表名1,[表名2,...表名n]
[WHERE条件表达式])b
WHERE rn<=目标页数*每页记录数 and rn> (目标页数-1)*每页记录
# 注意别名rownum的情况
练习题
练习1
1.查询入职日期最早的员工姓名，入职日期
select ename,hiredate
from emp
where hiredate = (select min(hiredate) from emp);
2.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称
select ename,sal,dname
from emp e join dept d on e.deptno=d.deptno
where sal > (select sal from emp where ename='SMITH') and loc = 'CHICAGO';
3.查询入职日期比20部门入职日期最早的员工还要早的员工姓名，入职日期
select ename,hiredate
from emp
where hiredate>(select min(hiredate) from emp where deptno=20);
4.查询部门人数大于所有部门平均人数的的部门编号，部门名称，部门人数
select deptno,(select dname from dept where e.deptno=deptno),count(ename)
from emp e
group by deptno
having count(ename)>(select avg(count(ename)) from emp group by deptno);
练习2
1.查询入职日期比10部门任意一个员工晚的员工姓名、入职日期，不包括10部门员工
select ename,hiredate
from emp
where hiredate>any(select hiredate from emp where deptno=10) and deptno<>10
2.查询入职日期比10部门所有员工晚的员工姓名、入职日期，不包括10部门员工
select ename,hiredate
from emp
where hiredate>all(select hiredate from emp where deptno=10) and deptno<>10
3.查询职位和10部门任意一个员工职位相同的员工姓名，职位，不包括10部门员工
select ename,job
from emp
where job=any(select job from emp where deptno=10) and deptno<>10
练习3
1.查询职位及经理和10部门任意一个员工职位及经理相同的员工姓名，职位，不包括10部门员工
select ename,job,mgr
from emp
where deptno<>10 and (job,mgr) in(select job, mgr from emp where deptno=10);
2.查询职位及经理和10部门任意一个员工职位或经理相同的员工姓名，职位，不包括10部门员工
select ename,job,mgr
from emp
where deptno<>10 and
(job in(select job from emp where deptno=10) or mgr in (select mgr from emp where deptno=10 ));
练习4
1.查询比自己职位平均工资高的员工姓名、职位部门名称，职位平均工资
select ename,dname,avgSal
from emp e
left join dept d
on e.deptno=d.deptno
inner join (select job,avg(sal) avgSal from emp group by job) x
on e.job=x.job
where e.sal>x.avgSal;
2.查询职位和经理同员工SCOTT或BLAKE完全相同的员工姓名、职位，不包括SCOOT和BLAKE本人。
select ename,job
from emp e
inner join (select job,mgr from emp where ename='SCOTT' or ename='BLAKE') x
on e.job=x.job and e.mgr=x.mgr
where ename<>'SCOTT' and ename<>'BLAKE';
3.查询不是经理的员工姓名。
select ename
from emp e
where empno not in (select distinct nvl(mgr,0) from emp);
练习5
1.查询入职日期最早的前5名员工姓名，入职日期。
select rownum, ename,hiredate
from emp
where rownum <=5;
2.查询工作在CHICAGO并且入职日期最早的前2名员工姓名，入职日期。
select rownum,e.ename,hiredate
from emp e join dept d on e.DEPTNO = d.DEPTNO
where d.loc ='CHICAGO' and rownum<3;
练习6
1.按照每页显示5条记录，分别查询第1页，第2页，第3页信息，要求显示员工姓名、入职日期、部门名称。
select *
from(select rownum rn,e.ename ,e.hiredate,d.dname
from emp e,dept d
where e.deptno = d.deptno)
where rn<=5 and rn>0;
select *
from(select rownum rn,e.ename ,e.hiredate,d.dname
from emp e,dept d
where e.deptno = d.deptno)
where rn<=10 and rn>5;
select *
from(select rownum rn,e.ename ,e.hiredate,d.dname
from emp e,dept d
where e.deptno = d.deptno)
where rn<=15 and rn>10;
练习7
1.按照每页显示5条记录，分别查询工资最高的第1页，第2页，第3页信息，要求显示员工姓名、入职日期、部门名称、工资。
select b.*,rownum
from(select a.*,rownum rn
from (select ename,hiredate,dname,sal
from emp e join dept d
on e.deptno=d.deptno
order by sal desc
) a
where rownum<=5
) b
where rn=1;
select b.*,rownum
from(select a.*,rownum rn
from (select ename,hiredate,dname,sal
from emp e join dept d
on e.deptno=d.deptno
order by sal desc
) a
where rownum<=10
) b
where rn>5;
select b.*,rownum
from(select a.*,rownum rn
from (select ename,hiredate,dname,sal
from emp e join dept d
on e.deptno=d.deptno
order by sal desc
) a
where rownum<=15
) b
where rn>10;
课后作业
1.查询工资高于编号为7782的员工工资，并且和7369号员工从事相同工作的员工的编号、姓名及工资。
select empno,ename,sal
from emp
where sal > (select sal from emp where empno = 7782) and job = (select job from emp where empno = 7369);
2.查询工资最高的员工姓名和工资。
select ename,sal
from emp
where sal = (select max(sal) from emp);
3.查询部门最低工资高于 10号部门最低工资的部门的编号、 名称及部门最低工资。
select e.deptno,d.dname,min(e.sal)
from emp e
left join dept d on e.deptno = d.deptno
group by e.deptno,d.dname
having min(e.sal) > (select min(sal) from emp where deptno = 10);
4.查询员工工资为其部门最低工资的员工的编号和姓名及工资。
select empno,ename,sal
from emp
where sal in (select min(sal) from emp group by deptno);
5.显示经理是KING的员工姓名，工资。
select e1.ename,e1.sal
from emp e1
left join emp e2 on e1.mgr = e2.empno
where e2.ename = 'KING';
select ename,sal
from emp
where mgr = (select empno from emp where ename = 'KING');
6.显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间。
select ename,sal,hiredate
from emp
where hiredate > (select hiredate from emp where ename = 'SMITH');
7.使用子查询的方式查询哪些职员在NEW YORK工作。
select ename
from emp
where deptno in (select deptno from dept where loc = 'NEW YORK');
8.写个查询显示和员工SMITH工作在同个部门的员工姓名，雇用日期，查询结果中排除SMITH。
select ename,hiredate
from emp
where deptno in (select deptno from emp where ename = 'SMITH') and ename <> 'SMITH';
9.写一个查询显示其工资比全体职员平均工资高的员工编 号、姓名。
select empno,ename
from emp
where sal > (select avg(sal) from emp);
10.写一个查询显示其上级领导是King的员工姓名、工资。
select ename,sal
from emp
where mgr = (select empno from emp where ename = 'KING');
11.显示所有工作在RESEARCH部门的员工姓名，职位。
select ename,job
from emp
where deptno = (select deptno from dept where dname = 'RESEARCH');
12.查询每个部门的部门编号、平均工资，要求部门的平均工资高于部门20的平均工资。
select deptno,avg(sal)
from emp
group by deptno
having avg(sal) > (select avg(sal) from emp group by deptno having deptno = 20);
13.查询大于自己部门平均工资的员工姓名，工资，所在部门平均工资，高于部门平均工资的额度。
select ename,sal,a.ag 部门平均工资,sal-a.ag 高于部门平均工资
from emp e
left join (select deptno,avg(sal) ag from emp group by deptno) a on e.deptno = a.deptno
where sal > a.ag;
14.列出至少有一个雇员的所有部门
select a.dname
from (select d.dname,count(e.deptno) ct
from emp e
left join dept d
on e.deptno = d.deptno
group by e.deptno,d.dname) a
where a.ct>=1;
15.列出薪金比SMITH多的所有雇员
select ename
from emp
where sal>(select sal from emp where ename = 'SMITH');
16.列出入职日期早于其直接上级的所有雇员
select s.ename
from (select e1.ename,e1.hiredate h1,e2.hiredate h2 from emp e1 left join emp e2 on e1.mgr = e2.empno) s
where s.h1 < s.h2;
17.找员工姓名和直接上级的名字
select e1.ename 员工姓名,e2.ename 直接上级
from emp e1
left join emp e2
on e1.mgr = e2.empno;
18.显示部门名称和人数
select d.dname,count(e.deptno)
from emp e
left join dept d
on e.deptno = d.deptno
group by e.deptno,d.dname;
19.显示每个部门的最高工资的员工
select *
from emp
where (deptno,sal) in (select deptno,max(sal) from emp group by deptno);
20.显示出和员工号7369部门相同的员工姓名，工资
select ename,sal
from emp
where deptno = (select deptno from emp where empno = 7369);
21.显示出和姓名中包含"W"的员工相同部门的员工姓名
select ename
from emp
where deptno in (select deptno from emp where ename like '%W%');
22.显示出工资大于平均工资的员工姓名，工资
select ename,sal
from emp
where sal > (select avg(sal) from emp);
23.显示出工资大于本部门平均工资的员工姓名，工资
select ename,sal
from emp e
left join (select deptno,avg(sal) ag from emp group by deptno) a
on e.deptno = a.deptno
where sal > a.ag;
24.显示每位经理管理员工的最低工资，及最低工资者的姓名
select ename,sal
from emp
where (deptno,sal) in (select deptno,min(sal) from emp group by deptno);
25.显示比工资最高的员工参加工作时间晚的员工姓名，参加工作时间
select ename,hiredate
from emp
where hiredate > (select hiredate from emp where sal = (select max(sal) from emp));
26.显示出平均工资最高的部门的平均工资及部门名称
select *
from(select avg(e.sal) salavg,d.dname ddname
from emp e
left join dept d
on e.deptno = d.deptno
group by e.deptno,d.dname
order by salavg desc)
where ROWNUM = 1;
exists 操作符
EXISTS判断是否“存在”。
规则如下：
子查询中如果有记录找到，子查询语句不会继续执行， 返回值为TRUE；子查询中如果到表的末尾也没有记录找到，返回值为 FALSE。
EXISTS 子查询并没有确切记录返回，只判断是否有记录存 在，而且只要找到相关记录，子查询就不需要再执行，然后再进行下面的操作。这样大大提高了语句的执行效率。
NOT EXISTS正好相反，判断子查询是否没有返回值。如果没有返回值，表达式为真，如果找到一条返回值，则为假。
19、层次查询
语法
SELECT [LEVEL], column, expr...
FROM table # 只能从单独的一个表中选择
[WHERE condition(s)]
[START WITH condition(s)]
[CONNECT BY PRIOR condition(s)];
# LEVEL：节点的层次，伪列，由查询的起点开始算起为1，依次类推。
# Condition：是一个比较式。
# START WITH（必须有）：指定层次的根行(起点)。
# CONNECT BY PRIOR（必须有）：指定存在父与子行的关系列。
20、视图
语法
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW 视图名称 [(列名[, column]...)]
AS 完整的select语句
[WITH CHECK OPTION [CONSTRAINT 约束名]]
[WITH READ ONLY]
# OR REPLACE：如果创建的视图已经存在，则修改原视图的定义。
# FORCE：不管视图所基于的表是否存在，都会创建该视图。
# NOFORCE：只有视图所基于的表存在，才会创建该视图。
# WITH CHECK OPTION：一个约束条件，通过视图所插入或修改的数据行必须 满足视图所定义的查询；
# CONSTRAINT：增加约束
# WITH READ ONLY：确保在该视图上不能进行任何DML操作（一般不用，都会默认视图用来查，并不会用它来增删改）
# 注意：
列名的数量必须和视图所对应查询语句的列数量相等
删除视图
drop view 视图名;
相关数据字典
- USER_VIEWS：字典中包含了视图的定义。
- USER_UPDATABLE_COLUMNS：字典包含了哪些列可 以更新、插入、删除。
- USER_OBJECTS：字典中包含了用户的对象。
21、序列
序列是按照一定规则能自动增加/减少数字的一种数据库对 象。
通常可以使用序列自动地生成主键值。
创建序列
CREATE SEQUENCE [schema.] 序列名
[INCREMENT BY n] # 序列连续两个值之间的间隔n，默认为1
[START WITH n] # 序列起始值n该项省略起始值为1
[MAXVALUE n | NOMAXVALUE]
[MINVALUE n | NOMINVALUE]
[CYCLE | NOCYCLE] # 表示序列在达到最大值或最小值之后是否继续产生序列值，NOCYLE表示不再产生，NOCYLE是默认选项。
[CACHE n | NOCACHE]; # 表示序列值被服务器预先分配并存储在内存中，NOCACHE表示不预先分配并存储，CACHE 20是默认选项
# MAXVALUE n ：序列最大值；
# MINVALUE n：序列最小值；
# NOMINVALUE：指定序列无最小值
序列属性
CURRVAL：表示序列返回的当前值；
NEXTVAL：表示序列返回的下一个值；
CURRVAL在被引用之前，必须先使用NEXTVAL来产生一个序列值；
可用语句序列名.CURRVAL或序列名.NEXTVAL来访问序列;
使用规则(不能使用)：
您可以在下列情况使用NEXTVAL 和CURRVAL：
- 在视图的SELECT列表中
- 包含DISTINCT关键字的SELECT语句中
- 含有GROUP BY, HAVING, ORDER BY子句的SELECT语句中
- SELECT, DELETE, UPDATE 语句的子查询中
- 含有DEFAULT表达式的CREATE TABLE、ALTER TABLE 语句中
示例：
#创建序列test_seq，起始值为10，每次增长 2最大值100最小值9循环序列每次缓存 2，最大值100，最小值9，循环序列，每次缓存 10
# 创建
create sequence test_seq
start with 1
increment by 1
maxvalue 1000
cache 5;
# 使用
insert into student (id,name,sex)
values
(test_seq.nextval,'hanf','男');
select test_seq.currval from dual;
修改序列
ALTER SEQUENCE [schema.]sequencename
[INCREMENT BY n]
[MAXVALUE n | NOMAXVALUE]
[MINVALUE n | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE n | NOCACHE];
# 修改序列的语法没有start with语句
删除序列
DROP SEQUENCE [schema.]序列名;
22、索引
索引应当创建在 常查少改 的基础上创建。
rowid
rowid 是一个伪列系统自动产生，系统自动产生。
ROWID能唯一标示每一条数据库行记录的物理地址，通过 ROWID 能快速定位到一条行记录。
创建索引
-
自动创建: 当有PRIMARY KEY 或者UNIQUE 约束时，数据库会自动创建一个索引；
-
手动创建: 用户使用创建索引语法来进行创建；
CREATE [ UNIQUE|BITMAP] index 索引名称 ON 表名 (列名1 ASC|DESC, 表达式 ASC|DESC,…) [REVERSE]; # 建议索引命名格式：idx_表名_列名
适合创建索引情况
-
表数据量很大
-
要查询的结果集在2%-4%左右
-
经常用来做WHERE条件中的列或者多表连接的列
-
查询列的数据范围分布很广
-
查询列中包含大量的NULL值因为空值不包含在索引中
不适合创建索引情况
- 数据量很小的表
- 在查询中不常用来作为查询条件的列
- 频繁更新的表
- 索引列作为表达式的一部分被使用时，比如常查 询的条件是SALARY*12，此时在SALARY列上创建索引是没有效果的
- 查询条件中有单行函数时，用不上索引 查询条件中有单行函数时，用不上索引
索引缺点
-
占用空间；
-
降低DML的操作速度；
删除索引
drop index 索引名;
相关数据字典
USER_INDEXES 数据字典视图包含索引的名称及其唯一性；
USER_IND_COLUMNS 数据字典视图包含索引名称、表名以及列名；
示例：
# 创建索引
CREATE UNIQUE INDEX adopt_time_index ON pet(adopt_time DESC);
CREATE BITMAP INDEX type_id_bitmap_index ON pet(type_id);
CREATE INDEX health_love_index ON pet(health,love);
CREATE INDEX to_char_index ON pet(TO_CHAR(adopt_time,'YYYY'));
CREATE INDEX master_id_reverse_index ON pet(master_id) REVERSE;
# 查看索引个数
select index_name,index_type,table_name from user_indexes order by table_name;
# 查看索引所占空间
select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&index_name');
23、同义词
是指向数据库对象（如：表、视图、序列、存储过程等） 的数据库指针。
好处
- 可以简化对数据库对象的访问;
- 方便对其他用户表的访问;
- 简化过长的对象名称;
- 节省大量的数据库空间，对不同用户的操作同一张表没 有多少差别;
- 扩展的数据库的使用范围，能够在不同的数据库用户之 间实现无缝交互;
- 同义词可以创建在不同一个数据库服务器上，通过网络 实现连接;
创建同义词
CREATE [PUBLIC] SYNONYM 同义词
FOR [schema.]对象名;
同义词的两种类型
| 类型 | 解释 |
|---|---|
| 私有（PRIVATE） | 是在指定的方案中创建的，并且只允许拥有它的方案访问 |
| 公有（PUBLIC） | 由PUBLIC方案所拥有，所有的数据库方案都可以引用他们 方案都可以引用他们 |
删除同义词
drop synonym 同义词;
24、集合运算
intersect 和 minus 是Oracle特有的。
1、联合（union）
返回由任一查询结果集包含的行，并且去除重复行,并且按照查询结果集的第一列升序排序
规则：
- 多个被联合的查询语句所选择的列数和列的数据类型必须一致， 列的名字不必相同 列的名字不必相同。
- 如果多个查询结果都有NULL值，整个结果中只包含一个NULL值。
- 每个查询不能包含自己的Order by子句，只能在联合之后使用 Order by子句。
# 查询编号为7839的员工当前工资、岗位及 历史工资、岗位。
SELECT sal,job FROM emp WHERE empno=7839
UNION
SELECT sal,job FROM emp_jobhistory WHERE empno=7839;
完全联合运算（union all）
返回由任一查询结果集包含的行，并且包含重复行，默认情况下不对结果集进行排序。
规则：
- 多个被联合的查询语句所选择的列数和列的数据类型必须一致 列的名字不必相同， 列的名字不必相同。
- 如果多个查询结果都有NULL值，不被去掉。
- 每个查询不能包含自己的Order by子句，只能在联合之后使用 Order by子句。
注意：
使用UNION ALL会比UNION的速度快，因为省去了去掉重复 记录和排序的时间。
2、相交（intersect）
返回多个查询结果集的公有行。
规则：
-
多个查询语句所选择的列数和列的数据类型必须一致，列的名字不必相同。
-
相交运算不忽略空值。
3、相减（minus）
返回第一个查询中存在而第二个查询中不存在的行记录。
规则：
- 多个查询语句所选择的列数和列的数据类型必须一 致，列的名字不必相同。
- 相减运算不忽略空值
25、三范式
第一范式：每一列属性都是不可再分的属性值，确保每一列的原子性。
第二范式：每一行的数据只能与其中一列相关，即一行数据只做一件事。只要数据列中出现数据重复，就要把表拆分开来。
第三范式：满足第二范式，数据不能存在传递关系，即没个属性都跟主键有直接关系而不是间接关系。像：a–>b–>c 属性之间含有这样的关系，是不符合第三范式的。
数据库的设计范式有助于减少冗余、更新异常、插入异常、和删除异常。但是范式越高可能带来处理速度和逻辑复杂的问题。
常用命令
| 功能 | 命令 |
|---|---|
| 检查语句 | show error; |
| 编辑 | edit; |
| 清屏 | clear screen; |
| 显示当前用户 | show user; |
| 查看当前用户的角色 | select * from user_role_privs; |
| 当前用户的系统权限 | select * from user_sys_privs; |
| 当前用户的表级权限 | select * from user_tab_privs; |
| 设置sqlplus环境中一行所显示的最多字符数 | set linesize 值; |
导出数据
# 按用户导出
expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1;
# 按表导出
expdp scott/tiger@orcl TABLES=dog,dept dumpfile=expdp.dmp DIRECTORY=dpdata1;
# 按表空间导出
system/manager DIRECTORY=dpdata1 DUMPFILE=USERS.dmp TABLESPACES=USERS,example;
导入数据
# 导入到用户下
impdp scott/tiger DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=scott;
# 导入表空间
impdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=example;
设置查询格式
column 列名 format a5
select * from Table;
column username format a5
column pwd format a3
select * from users;
column topic,content,author format a5
select * from message;
扫一扫
1484
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
