目录
1.序
2.题库
3.
Oracle 数据库
数据库本质是用计算机存储数据的一种系统。它是位于 用户 和系统 之间的一种管理软件。
1.序
1.1 登录SQLPLUS
cmd sqlplus [用户名]/[密码][@数据库] [参数] sqlplus sys/orcl as sysdba -- 登录 sys 用户，必须指定 sysdba 或 sysoper 身份 sqlplus system/orcl -- 数据库管理员
1.2 创建一个自己的用户（比如 vip/vip）
create user vip identified by vip; -- 注意，新创建的用户，什么权限都没有，需要授权后才能使用 grant create session to vip; -- 授予登录的权限 grant connect to vip; -- 角色是很多权限的打包，connect 是一种角色，它包含了连接查看数据的一些基本权限 grant dba to vip; -- dba 是绝大多数权限的集合，它基本能做所有事情，所以很少单独授予用户。但在测试环境中，这样，很爽。 -- 上面的创建用户、授予权限两步操作，可以简化为下面一步： grant dba to vip identified by vip; -- 注意，使用分号结尾
1.3 切换到用户
sqlplus vip/vip -- 在 cmd 下 conn vip/vip -- 在 sqlplus 中
1.4 使用
create table aaa (id int);
1.5 激活内置的测试账号，这里面有几张示例库，可以用它们练习下查询
alter user scott account unlock; conn scott/tiger
1.6 修改密码
alter user scott identified by [newpassword];
2.题库 2.1 题库一 在芝加哥工作的人中，谁的工资最高 查询每个部门下有多少员工 查询除去 salesman 所有平均工资超过 1500 的部门 查询在 new york 工作的所有员工的姓名，部门名称和工资信息 查询姓名为 King 的员工的编号，名称跟部门 查询各种工作的最低工资 查询工龄大于10年的所有员工信息 查询每个部门员工数量，平均工资和平均工作年限 统计各部门每个工种的人数，平均工资。 查询从事同一种工作但不属于同一部门的员工信息。 查询所有员工工资都大于1000的部门的信息及员工信息 查询入职日期早于其直接上级的所有员工信息。 列出雇员中（除去mgr为空的人)工资第二高的人。 列出1981年来公司所有员工的总收入（包括sal和comm） 2.2 题库二 查询工资为 2500 到 4000 的人的数量（用不同方式查询）
select count(*) from emp where sal >= 2500 and sal <= 4000; -- vs. select count(*) from emp where sal between 2500 and 4000;
查询部门编号为 10 和 30 的所有人（用不同方式查询） 查询部门编号为 10 和 30 中名字中不含有 ‘C’ 的所有人 查询部门编号为 10 和 30 中名字首字母之外不含有 ‘C’ 的所有人 查询部门编号为 10 和 30 中所有的经理以及名字首字母之外不含有 ‘C’ 的所有人 查询纽约和芝加哥地区所有的经理以及名字首字母之外不含有 ‘C’ 的所有人 查询纽约和芝加哥地区所有的经理以及顶头上司名字的首字母之外不含有 ‘C’ 的所有人 查询每个人的工资等级
-- 初始化数据 create table salgrade as select * from scott.salgrade; -- 分别查看数据 select * from emp; select * from salgrade; -- 杂交 select * from emp, salgrade; -- 过滤掉不合适的 select ename, grade from emp e, salgrade s where e.sal between s.losal and s.hisal;
查询每个部门的平均工资的等级
-- 分析题目 -- 1. 需要先查询出每个部门的平均工资 -- 2. 根据 salgrade 表中的数据，获取每个部门平均工资的等级 -- 这是所有的人 select * from emp; -- 按照部门分组 select deptno, avg(sal) from emp group by deptno; -- 结果： -- | DEPTNO | AVG(SAL) | -- | 30 | 1566.66667 | -- | 20 | 2258.33333 | -- | 10 | 2916.66667 | -- 跟 salgrade 表，杂交，总共 15 条结果 select * from (select deptno, avg(sal) sal from emp group by deptno) t, salgrade s; -- 过滤掉工资范围不合适的数据 select * from (select deptno, avg(sal) sal from emp group by deptno) t, salgrade s where t.sal between s.losal and s.hisal; -- 结果就是这样，就对了 -- | DEPTNO | SAL | GRADE | LOSAL | HISAL | -- | 10 | 2916.66667 | 4 | 2001 | 3000 | -- | 20 | 2258.33333 | 4 | 2001 | 3000 | -- | 30 | 1566.66667 | 3 | 1401 | 2000 | -- 需要将部门名称显示出来，再杂交->过滤一次就可以了 select * from (select deptno, avg(sal) sal from emp group by deptno) t, salgrade s, dept d where t.deptno = d.deptno and t.sal between s.losal and s.hisal; -- 最后的最后，设置显示字段 select dname, grade from (select deptno, avg(sal) sal from emp group by deptno) t, salgrade s, dept d where t.deptno = d.deptno and t.sal between s.losal and s.hisal; -- 最终结果，完美 --| DNAME | GRADE | --| ACCOUNTING | 4 | --| RESEARCH | 4 | --| SALES | 3 |
2.3 题库三 查询每个组最高工资的那些人 有下面一个表，写一条 sql 语句计算男女之差 gendernumber 男 46 女 10 给 emp 中的人加工资，请写出相关语句: 条件加多少 1000元以下 50% 2000元以下 30% 3000元以下 20% 其他 5% 给 emp 中的人加工资，如上。但 1981/5/1 之后来的所有人，只加 2%, 请写出语句。 计算你们从入学到现在过了多少个周末 计算你们从现在到毕业还有多少天，还有多少个周末 计算你们在学校的时间内，每天花费多少钱 查询各种工作中的最低工资
看到最低、最大、平均之类的题目，首先想到的是分组函数的使用。
也就是 group by, having。
select job, min(sal) from emp group by job;
在芝加哥工作的人中，谁的工资最高
[题目] 从 scott 用户的 emp/dept 表中，找到 “来自芝加哥最有钱的那个人” 。
首先，我们需要理清思路。
这里总共有两个条件：
这个人来自芝加哥 这个人是最有钱的，而且是芝加哥最有钱的
我们可以看出，第二个条件是依赖第一个条件的。
所以，分两步查询：
找出所有来自芝加哥的人 从这些人中，找到最有钱的那个。这一步，可以通过 max 函数或者 order by 方式实现。 下面是语句示例：
---- 第一步：找到来自芝加哥的所有人。下面两种写法等价：
select e.* from emp e
join dept d on (e.deptno=d.deptno)
where d.loc='CHICAGO';
select e.* from emp e, dept d
where d.deptno = e.deptno
and d.loc='CHICAGO';
---- 第二步，基于上面结果，筛选出最有钱的那个
-- 可以通过 max 函数
select e.* from emp e, dept d
where e.deptno = d.deptno
and d.loc='CHICAGO'
and sal =
(select max(sal) from emp e, dept d
where e.deptno = d.deptno
and d.loc='CHICAGO');
-- 可以通过 order by 方式
select ename from
(select e.*, d.* from emp e, dept d
where e.deptno = d.deptno
and d.loc='CHICAGO'
order by sal desc)
where rownum = 1;
注意，实现的方式，不止上面的那些。但总体 思路 是一样的。
所以，思路永远是最重要的。
查询所有员工工资都大于1000的部门的信息及员工信息
最核心的问题： 查询出符合条件的部门编号。
第一种思路
1.查询出所有的部门编号
select * from emp;
2.查询出所有工资少于 1000 的人， 我们要把它所在的部门，从上面的结果中排除掉。
select deptno from emp where sal < 1000;
3.将上面查询出的不符合条件的部门排除掉
select distinct deptno from emp where deptno not in (select deptno from emp where sal < 1000);
4.修改上面语句，增加最终的条件，查询所有的其他信息
select d.dname, e.* from emp e join dept d on(e.deptno = d.deptno)
where e.deptno not in
(select deptno from emp where sal < 1000)
第二种思路
使用分组函数（group by / having）
分组函数主要用来统计分析。
一个完整的查询语句如下，其中 group by 和 having 是用来分组和筛选分组。
select [字段] from [表名] where [条件] group by [分组字段] having [对分组结果进行筛选] order by [字段]
示例：
select deptno, -- 分组字段
count(*), -- 人数
sum(sal), -- 工资总和
avg(sal), -- 平均工资
max(sal), -- 最高工资
min(sal) -- 最低工资
from emp group by deptno -- 按照部门分组，进行统计
having avg(sal) > 2000; -- 只显示平均工资大于 2000 的分组
那我们的题目的解决思路就是：
1.按照部门分组
select deptno from emp group by deptno;
2.筛选，排除最低工资小于 1000 的部门。 即：得到符合条件的部门的编号。
select deptno from emp group by deptno having min(sal) > 1000;
3.完善最终语句，得到最终结果。
select * from emp e, dept d
where e.deptno = d.deptno
and e.deptno in (select deptno from emp group by deptno having min(sal) > 1000);
查询当月总共有多少个周五 首先，第一步，得到本月所有日期的结果集，两种方式: 使用已有表的 rownum 构建 使用 oracle 的 connect by level 语句（结构化查询）
得到有 n 条记录的虚表：
select rownum from dba_objects where rownum < 32; select level from dual connect by level < 32;
将虚表转化为我们需要的日期表：
select trunc(sysdate, 'MON') + rownum - 1 from dba_objects where rownum < 32; select trunc(sysdate, 'MON') + level - 1 from dual connect by level < 32;
其次，在上面结果集的基础上进行筛选：
-- 1.
select *
from (select trunc(sysdate, 'MON') + rownum - 1 d
from dba_objects
where rownum < 32)
where to_char(d, 'day') = '星期五'
and d <= last_day(sysdate);
-- 2.
select *
from (select trunc(sysdate, 'MON') + level - 1 d
from dual
connect by level < 32)
where to_char(d, 'day') = '星期五'
and d <= last_day(sysdate);
当然，你也可以将 last_day 这一段放到里面：
select *
from (select trunc(sysdate, 'MON') + level - 1 d
from dual
connect by level <= extract(day from last_day(sysdate)))
where to_char(d, 'd') = 6;
3.管理系统 3.1 学生管理系统
根据我们学校的实际情况，请帮助设计一个学生管理系统。
比如, 学生，老师，班级，课程。按照你自己的设计，酌情增加。
基本步骤：
先用 e-r 图，将实体的关系表述出来。
这样的图能帮助我们理清思路，并能帮助团队间的有效交流。
一定在图画好之后再去着手数据库表的创建。要秉承先设计后实现的思路。
你可以用一些知名的工具(如 visio)去画，也可以手动在纸上画。
根据设计好的图，写出相应的建表语句。 也可以进一步根据实体关系和表，创建相应的 Java 实体类。进一步可以 DAO，进一步可以 Service【可选】 最后，将图跟语句一起上交。这个过程着重思考下项目从设计到编码是怎么一个过程。
需求分析 -> *概要设计* -> 详细实现。
3.2 博客管理系统
首先，设计一个博客表(blog), 至少有下面字段
id author title content(要求是 clob 类型) image(要求是 blob 类型) create_time
用 Java 完成基本的 =CRUD=，并掌握使用 PL/SQL 操作 blob/clob 的技巧。
4.体系结构
Oracle 采取的是 Client/Server 架构。
客户端(client)操作数据库的请求发送后，服务端的监听器(TNSListener)接收到请求，并将其转发给相应的数据库实例(Instance)，再由实例(Instance)去操纵数据库(Database)。 返回操作结果，是一个相反的过程。下面是个简陋的图示:
Client Sqlplus
这是一个轻量级的功能强大的客户端, 是 dba 必须掌握的工具。
用 sqlplus 连接数据库的语法为：
# Usage: # sqlplus 用户名/密码@主机名:端口号/实例名 # 参数个数不是固定的 sqlplus # 会要求你输入用户名密码，默认连接本地 ORACLE_SID 变量指定的数据库 sqlplus vip # 会要求你输入密码 sqlplus vip/vip # 连接本地 ORACLE_SID 变量指定的数据库 sqlplus sys/hello as sysdba # sys 用户必须用 sysdba 或 sysoper 的身份登录 sqlplus vip@192.168.0.111/orcl # 连接 192.168.0.111 机器上的 orcl 数据库，用户名为 vip sqlplus vip@192db # 连接 别名 为 192db 的数据库
我们可以配置 sqlplus 的一些行为，两个命令：
show. 用来显示配置参数 set. 用来设置配置参数
比如：
show all -- 显示所有配置参数 show lines -- 显示 lines 的配置信息 show errors -- 显示错误 set lines[ize] 333 -- 将行宽设置为 333 set pages[ize] 444 -- 将每页的记录数设置为 444 set echo off/on -- 导入外部文件，是否要显示原始 sql 语句 set feedback on/off -- 是否显示“查询到xx数据”等信息 set timing on/off -- 是否显示语句的执行时间 set autocommit on/off -- 是否启用自动提交 set autotrace on/off -- 是否输出执行计划 set serveroutput on/off-- 是否显示来自服务端的信息 column aaa format a22 -- 将列 'aaa' 的宽度限制为 22 个字幕'a'的大小。column 命令很强大，语句也复杂，此处不提。
在 sqlplus 中有缓冲区的概念:
缓冲区是用来记录上一次执行的命令语句的空间。
我们可以通过一些列简单命令，对上一次输入的语句进行一些控制:
增 append/insert 删 delete 改 change 查 list 执行修改后的语句 run 或者 /
例子：
list -- 显示完整的缓存区 list 3 -- 显示并定位到第三行 list 3 5 -- 显示第三行到第五行的内容 list last -- 定位到最后一行
list 3 del -- 删除第三行
list 3 append order by sal -- 定位到第三行，然后追加 order by sal insert order by sal -- 开启新的一行，插入 order by sal
还有其他一些命令：
get D:\aaa.sql -- 将文件加载到缓冲区，但不执行 start D:\aaa.sql -- 将文件加载到缓冲区，并且执行 @D:\aaa.sql -- 是上面一条语句的简写形式 save D:\bbb.sql -- 将缓冲区的内容保存到文件中 edit -- 调用外部编辑器，编辑缓冲区 clear screen -- 清空缓冲区
show user -- 显示当前用户 show parameters -- 显示 oracle 的配置参数 show parameters nls -- 显示 oracle 中所有跟语言配置相关的一些参数 describe emp -- 显示 emp 表的结构信息
JDBC
用 Java 连接数据库，需要用到 jdbc 驱动，它们可以在下面目录中找到：
主目录\product.1.0\dbhome_1\jdbc\lib\*.jar
比如 ojdbc7_g.jar, 7 表示适用于 JDK 版本 1.7, g 表示自带更多调试信息。
TNSListener
TNSListener，是用来监听来自客户端的请求，并将其转发给相对应的服务端实例的一种后台服务。
它是沟通客户端与服务端的一个桥梁。
比如，下面用 sqlplus 客户端将会连接 localhost 上的 orcl 数据库:
sqlplus vip/vip@localhost/orcl
请求会发送到 localhost 主机的 1521 号端口， 作为监听的 TNSListener 收到这个请求后，再把请求转发给对应的 orcl 数据库实例。
所以必须开启监听服务，并且配置正确，才能连接操作数据库。
注：如果用 sqlplus vip/vip 的方式连接数据库，即没有指定连接的机器，那么默认连接的是本机数据库 这种连接是不需要监听服务的，因为为了增加连接速度，这样的本地连接 oracle 会使用一个专用的进程直接连接实例
我们可以使用 Oracle 提供的 lsnrctl 命令操纵监听服务的开启或关闭：
lsnrctl status # 查看状态 lsnrctl stop # 停止监听服务 lsnrctl start # 开启监听服务 lsnrctl reload # 重启监听服务 lsnrctl services # 查看监听的连接情况
我们可以使用 Oracle 的 Net Manager 工具来配置自己的监听器。
实质上，用 Net Manager 配置跟直接修改下面文件的作用是一样的：
主目录\product.1.0\dbhome_1\network\admin\listener.ora
我们在 Net Manager 中对 listener 的配置对应的是这一段代码：
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
)
...
)
只要修改其中的 host/port 等，重启监听服务即可。
Server
Oracle 服务端分为两部分：
Instance 实例 Database 数据库 实例(Instance)
实例, 又称为数据库引擎，由 SGA(System Global Area, 系统全局区) 和 一系列后台进程 组成。 它需要启动才会生成，用来加载并管理一个数据库。
服务启动的大致过程： [读取] 读取系统的 ORACLE_SID 环境变量，确定要启动的实例名字，比如为 xxoo [加载] 从 $ORABASE/admin/xxoo 和 $ORA_HOME/database/SPFILEXXOO.ora 等位置加载相关配置文件。配置文件的名字是根据 sid 来定义的。 [启动] 从配置文件中，读取相关信息，比如数据库名字、数据库控制文件位置、SGA 等信息，并根据这些，初始化数据库加载需要的 内存空间(SGA) 和 相关进程 。 [装载] 根据配置文件中读取的数据库信息，找到各种数据文件位置，并装载数据库。 [启动] 进行数据校验等，如果没有问题，启动数据库。
可以通过查看启动过程协助理解：
-- 首先，登录 sys 用户，只有管理员才有完全操纵数据库的权力 -- shutdown 用来关闭。如果不带参数，默认为 normal ---- immediate 表示立即关闭，如果有未处理完操作，回滚并断开 ---- normal 表示等待所有连接断开才关闭数据库 ---- 其他参数，略 shutdown immediate; -- 启动数据库，分解为三个动作： ---- 启动实例 ---- 利用启动的实例去挂载数据库 ---- 校验并打开数据库 -- 只有完全打开，才能进行完全的数据操作 -- 也可以指定参数，启动到某个阶段。这是在维护数据库中使用的命令。 startup -- 如果不加参数， startup nomount -- 启动到 nomount 阶段 startup mount -- 启动到 mount 阶段 -- 当然，也可以这样分步启动： startup nomount alter database mount alter database open
数据库(Database)
数据库, 是保存在硬盘上的文件集合，它是数据的主要载体。
$OracleBase\oradata\[数据库名字]\
可以从不同的角度去认识数据库，比如物理/逻辑角度：
物理组件
数据库是保存在操作系统的一系列文件。
默认安装情况下，这些文件都在 $ORACLE_BASE/oradata 文件夹下：
oradata/
└── orcl [数据库的名字]
├── CONTROL01.CTL
├── CONTROL02.CTL
├── EXAMPLE01.DBF
├── REDO01.LOG
├── REDO02.LOG
├── REDO03.LOG
├── SYSAUX01.DBF
├── SYSTEM01.DBF
├── TEMP01.DBF
├── UNDOTBS01.DBF
└── USERS01.DBF
从文件角度分析，一个数据库包含下面几类（组件）：
控制文件(control file)。记录数据库的物理结构和其他信息，如数据库名称、各种文件位置等。多副本。
select * from v$controlfile;
数据文件(data file)。用来存储数据的文件，会自动扩张。数据以块为单位进行保存。
select name, status, enabled from v$datafile;
重做日志文件(redo log)。用来记录用户的所有操作，为了备份恢复。 一个数据库至少有两个日志组，每个日志组至少有一个成员，成员之间是镜像关系。 用户的操作会记录到 redo log 中，当一个组记录满了，会自动切换到下一个组。轮流循环。
-- 需要理解 Oracle 日志的思路: -- 它采取了【多个分组，轮流循环写入；每组多成员，互为镜像；保存更多信息，使用归档模式】的方式，保证了记录安全性。 -- 在生产环境中，需要日志调整到不同的磁盘中，这样，即使某个文件损坏，或某块磁盘损坏，都可以通过镜像的日志文件对数据进行恢复。 -- 查看 redo log 日志组 select * from v$log; select * from v$logfile; -- 增加/删除 日志组 alter database add logfile 'd:/sss.rlog' size 100m; alter database drop logfile 'd:/sss.rlog'; -- 清空日志组 alter database clear logfile group 1; alter database clear unarchived logfile group 1; -- 为日志组 增加/删除 成员 alter database add logfile member 'd:/ssss.log' to group 1; alter database drop logfile member 'd:/ssss.log'; -- 重命名文件 -- 首先，在文件夹管理器里，将文件改名，比如，改为 ssss.redolog -- 其次，重启数据库到 mount 状态，然后执行重命名命令 alter database rename file 'd:/ssss.log' to 'd:/ssss.redolog'; -- 日志组一般是在写满的时候自动切换。 -- 我们也可以手动切换 alter system switch logfile;
归档日志文件。是重做日志的补充（redo log 记录的记录是有限的），可以把写满的 redo log 进行备份。
-- Oracle 的归档模式默认是关闭的 -- 归档模式会占用大量空间 -- 但他们用更多的空间，保存更多的历史记录，保障更大的安全性 -- 查看状态 archive log list; -- 切换数据库到归档模式 alter database archivelog; -- 启动 archive log start; -- 查看状态 archive log list;
其他文件 逻辑组件
https://docs.oracle.com/cd/B28359_01/server.111/b28318/physical.htm#CNCPT1082
从 Oracle 内部管理数据的角度，可以将 Oracle 分为4个组件：
表空间(tablespace) 最基本的逻辑结构，是 Oracle 中进行数据恢复的最小单位，容纳着表、索引等对象 数据库是由若干表空间组成的。一个表空间至少对应一个物理文件。 实际开发中，不建议使用默认表空间。请为自己的业务创建自己的表空间。
-- 内置的各种表空间 ---- system/sysaux 系统表空间/系统辅助表空间，用来保存系统字典表和其他信息，数据库创建完会自动生成 ---- users 用户表空间，创建新用户时，默认使用的表空间 ---- temp 临时表空间 ---- undo 回滚表空间 -- 查看表空间信息 select * from v$tablespace; -- 查看所有表空间跟文件对应关系 SELECT FILE_NAME, BLOCKS, TABLESPACE_NAME from dba_data_files; -- 创建表空间 create tablespace xxx datafile 'D:/sss.dbf' size 50m autoextend on next 50m maxsize 1024m; -- 创建临时表空间 create temporary tablespace yyy tempfile 'D:/ANOTHER_TMP.dbf' size 5m; -- 删除表空间 drop tablespace xxx;
段(Segment) 段是对象在数据库中占用的空间 包括索引段、数据段等 表空间被划分为若干区域，每个区域负责存放不同类型数据，这些区域这就是段 区(Extend) 由连续的数据块组成，由 Oracle 自动分配管理 会自动扩展大小 块(Block) 数据块是 Oracle 数据库最小的逻辑单元 它代表在读写操作的时候，每次处理的数据大小是多少 正常情况下，它是操作系统块的整数倍，默认是 8 KB 可以通过参数 db_block_size 控制
show parameters block;
用户权限
安装完 Oracle，缺省有两个用户：
SYS 用户，又叫数据库系统管理员、特权用户，数据库中至高无上的存在。 它是数据库的系统管理员，负责数据库的安装、维护、升级、备份、恢复、优化等操作。 在它之下，保存着数据库所有的系统字典。 不能用 normal 身份登录，必须用 SYSDBA/SYSOPER 身份登录。 SYSTEM 用户，数据库管理员，它拥有 DBA 角色，主要负责对数据库中各种对象，各种资源的管理。 SCOTT 用户，一个示例用户，默认是锁定的，需要解锁使用。
新创建的用户，是不能做任何事情的（甚至不能登录）。 必须要为用户授予权限，才能做相应的事情。 可以说，用户是权限的容器。
权限分为两种：
用户(User)
用户是用于 资源管理 和 权限控制 的一个概念。
-- 创建用户的语法 CREATE USER name IDENTIFIED BY password -- 一个用户，至少拥有用户名和密码 [DEFAULT TABLESPACE users] -- 指定此用户下数据默认保存位置，如果不指定，默认为 Users 表空间 [TEMPORARY TABLESPACE temp] -- 指定临时表空间，默认为 temp 表空间 [QUOTA 100M/unlimited ON USERS] -- 在表空间上有多少空间的使用权力，默认为 0, unlimited 表示无上限 [ACCOUNT LOCK/UNLOCK] -- 账号的初始状态：锁定/未锁定，默认为未锁定 [PASSWORD EXPIRE] -- 账号的初始状态：强制要求重设密码，默认不强制 [PROFILE DEFAULT] -- 指定使用的资源文件，默认是 default ; -- 修改用户的语法 ALTER USER name [跟创建用户的参数是一样的]; -- 删除用户的语法 DROP USER name [cascade];
为了对用户行为有更精细的控制，需要使用 资源文件 (profile)。 资源文件是口令限制、资源限制的命名集合。
-- 增加资源文件的语法 CREATE PROFILE name LIMIT RESOURCE_参数 n/unlimited/default -- 控制的是资源的占用 PASSWORD_参数; -- 控制的是密码的使用 -- 修改资源文件的语法 ALTER PROFILE name ...; -- 删除资源文件的语法 DROP PROFILE name; -- 为用户指定资源文件 ALTER USER name PROFILE profile_name;
常用资源文件选项：
名字类型介绍 SESSIONS_PER_USER resource 每个用户最多能创建的连接数 CONNECT_TIME resource 每个连接最多能保持的时间(分钟) IDLE_TIME resource 每个连接不操作多长时间自动断开(分钟) CPU_PER_SESSION resource 每个连接最多能占用的 CPU 时间(百分之一秒) LOGICAL_READS_PER_SESSION resource 每个连接最多能读取的数据块 PRIVATE_SGA resource 最多占用的 SGA 大小 PASSWORD_LIFE_TIME password 限制指定时间中必须重新修改密码(天) PASSWORD_REUSE_TIME/MAX password 密码重用的限制 FAILED_LOGIN_ATTEMPTS password 允许的密码输错次数 PASSWORD_LOCK_TIME password 因输错密码锁定的天数(天) PASSWORD_VERIFY_FUNCTION password 用于自定义密码验证
操作示例：
-- 所有的用户信息，保存在字典表 dba_users 里面 select * from dba_users where USERNAME='VIP'; -- 所有的资源文件信息，保存在 dba_profiles 里面 select * from dba_profiles where profile='DEFAULT'; -- 创建用户的最基本语句，需要用户名和密码 -- 其他属性采用的都是默认值 create user vip identified by vip; -- 再一个稍微复杂一点的创建语句 create user vip identified by vip default tablespace users temporary temporary temp quota unlimited on users password expire; -- 在实际环境中，一个良好的习惯是： -- 为我们新建的用户创建独立的表空间 create tablespace db_vip datafile 'd:/db_vip.dbf' size 50m; create user vip identified by vip default tablespace db_vip quota unlimited on db_vip password expire; -- 如果想对用户做一些限制 -- 需要为它创建相应的资源文件 create profile vip_profile limit failed_login_attempts 3 password_life_time unlimited sessions_per_user 2; -- 将资源文件分配给用户，也可以在创建用户的时候指定 alter user profile vip_profile;
权限(Privilege)
权限指的是执行特定命令或访问数据库对象的权利，分为系统权限和对象权限。
-- 授权 -- with admin option 可以允许获得权限的用户可以将这个权限继续授予别人 GRANT 权限 [ON 对象] TO 谁 [WITH ADMIN OPTION]; -- 撤销授权 REVOKE 权限 FROM 谁; -- 创建角色 CREATE ROLE 角色名;
操作示例：
-- 对象权限有 select/insert/update/delete/alter/execute 等 -- 系统中所有的系统权限和角色保存在相关字典表中: select * from system_privilege_map; -- 列出系统中所有的系统权限 select * from dba_role; -- 列出系统中所有的角色 -- 查看已授予的权限 select * from dba_sys_privs where grantee='VIP'; select * from dba_tab_privs where grantee='VIP'; select * from dba_role_privs where grantee='VIP'; --- 关于权限的管理 -- 新建用户先 create user vip identified by vip quota 1m on users; -- 新用户什么都干不了，甚至不能登录，如果想让它可以登录，给它一个相应权限： grant create session to vip; -- 现在可以登录 vip 了。但是不能创建表，需要我们给一个相应权限： grant create table to vip; -- 现在，可以创建表了 create table aaa (i int); insert into aaa values (888); commit; -- 上面创建用户、授权的语句可以缩写为： grant create session, create table to vip identified vip quota 1m on user; -- 自己创建的表，自己会有完全的操作权力，但如果想要看别的用户的表，需要授权： grant select on scott.emp to vip; -- 上面就有了查询的权限。但不能修改。如果要修改，需要相应权限。当然，如果想把对象上的所有权力一并授予，需要用到 All: grant all on scott.emp to vip; -- 通过下面语句，可以看到对象权限的授予情况： select * from dba_tab_privs where grantee='VIP';
角色是权限的命名集合，它可以简化授权。
-- 创建角色 create role myrole; -- 将权限赋予角色 grant create session, create table to myrole; grant create view to myrole; -- 甚至可以将另一个角色赋予我们的角色 grant resource to myrole; -- 使用角色 grant myrole to vip; grant myrole to vip with admin option; -- 删除角色 drop role myrole;
[Oracle 预定义角色] Oracle 自带了很多角色，常用的有：
CONNECT，只包含了 create session 等权限，即登录系统的权限
select * from dba_sys_privs where grantee='CONNECT';
RESOURCE，包含了若干 create xxx 权限，即创建对象、创建资源的权限
select * from dba_sys_privs where grantee=RESOURCE';
DBA，包含了200多个系统权限，是用来进行系统管理的角色
grant connect, resource to A_Common_User; grant dba to A_Super_User;
[特殊的 PUBLIC 角色] 公用角色，默认是授予每个用户的。
grant connect to public; -- 这样，不需要进行任何其他操作，所有新建的用户就都拥有登录的功能了。
[最小权限原则] 我们开发的时候，为了方便，经常要做这样的操作：
grant dba to xxx identified by yyy; -- 创建一个用户 xxx，密码为 yyy，授予 dba 角色
但这权力太大，在正式环境中，这样的授权是非常不合适的。一般要遵守 最小权限原则, 也就是：
创建一个用户，授予能完成任务的尽量少的权限，多一个也不好。
比如，我们有客户需要查看 vip 用户下的 student 表，我们需要创建一个用户给他：
-- 第一步，为客户需求创建一个用户 create user guest identified by ***; -- 第二步，授予连接数据库的权限 grant connect to guest; -- 第三步，授予 vip.student 的 select 权限 grant select on vip.student to guest; -- 当然，上面可以再简化为以下。That's All. grant connect to guest identified by ***; grant select on vip.student to guest;
SQL 语句 Data Type
查看数据所占空间的两个函数：
-- 查看所占字节数
select length('你好，世界') from dual;
-- 查看所占字符数，即多少个字母，多少个汉字
select lengthb('您好，美女') from dual;
-- 比如
create table aaa (a varchar2(6));
insert into aaa values ('aaa');
insert into aaa values ('你好');
select a, length(a), lengthb(a) from aaa;
字符类型 char，固定长度，默认1，最大2000，如果长度不够，用空格填充。 varchar2 类型，变长字符串，最多4000字节长度。 nvarchar2，跟上面比，增加了字符集的支持。 long，用于存储大数据，最多 2G。不建议使用，已被 clob 类型替代。
-- 它的使用跟 SQL 的标准 varchar 基本类似，
-- 但是，在 Oracle 中请使用 varchar2 而不是 varchar，
-- varchar2 的效率更高，并且在兼容性上做的更好
-- 初始化的语句为： name varchar2(20)
-- 其中 20 代表最大长度， 默认单位是字节。
-- 如果定义为： name varchar2(20 char)，表示最大保存20个字符长度的字符串。
create table aaa (
a varchar2(20), -- 最大是20个字节长度，默认单位字节
b varchar2(20 byte), -- 最大为20个字节长度，跟上面是一样的
c varchar2(20 char) -- 最大为20个字符长度
);
-- varchar2 最大能保存 4000 个字节
-- 如果是英文，则是 4000 个英文字母
-- 如果是中文，需要按照字符集判断：
-- GBK 用两个字节表示一个汉字，所以 varchar2 最多表示 2000 个汉字
-- UTF-8 是变长字符集，用一个字节表示一个英文字母，用3个或4个字节表示一个汉字，所以，最多可以保持1333个汉字。
select length('你好') from dual; -- 2
select lengthb('你好') from dual; -- 4
-- char 类型是固定长度的，可能会占用更多空间。但是因为长度固定，块的分配管理比较块，效率很高。
-- varchar2 是变长的，会占用尽量少的空间。但是需要消耗更多资源为分配变长空间，效率略低。
-- 所以，用哪一种，酌情而定。
-- nchar/nvarchar2 是 char/varchar2 的字符集支持版本，对多字节字符有算法上的优化。
-- 感觉用的比较少。
数值类型 number，占用 38 位，有自己的内部表示方式，可以表示天文数字。 int/float，number 类型的子类型
-- number 类型的语法为 NUMBER [位数[,小数点数]] create table bbb ( a number, -- 一共 38 位，其他按照插入的值自动判断 b number(5), -- 一共5位 c number(5, 2), -- 一共5位，3位整数，2位小数 d number (*, 2) -- 保存两位小数，其他随意 e number (*, 0), -- 相当于 int f int ); -- 插入的时候，多余的小数会被切掉，但如果整数位超了，会报错 insert into bbb values (111.223, 111.223, 111.223, 111.223, 22); -- 一般情况下，作为表的主键，设为 int 类型就可以了
日期类型 date，存储的年月日，时分秒 timestamp，存储的更详细，包括时区，还有精确到小数点后6位的秒数等 timestamp with time zone，可以设置时区 sysdate/systimestamp 连个函数用来查询当前的日期
-- 显示当前时间
select sysdate from dual;
select systimestamp from dual;
-- 时间的显示格式，由 nls_date/time_format 等参数控制
show parameter nls
-- 如果想格式化时间，可以有下面方法：
-- 1. 更改注册表中变量
-- # set NLS_DATE_FORMAT='yyyy.mm.dd'
-- 2. 更改当前会话中的格式
alter session set NLS_DATE_FORMAT='yyyy.mm.dd';
-- 3. 手动转型：to_char
-------- yyyy 代表四位的年, rr 代表两位的年
-------- mm 代表两位的数字月,
-------- dd 代表两位的数字日,
-------- hh 代表12格式的小时, hh24 代表24格式的小时
-------- mi 代表分钟数
-------- ss 代表秒数
select to_char(sysdate, 'yyyy-mm-dd') from dual;
-- date 比 timestamp 占用更少空间
-- timestamp 比 date 更精确
-- 没有优劣，使用哪一个，按照需求来
-- 插入时间，使用 to_date 转型。
-- 符合标准日期格式的字符串，可以隐式转型。
create table stu (name varchar2(20), birth date default sysdate);
insert into stu values ('aaa', sysdate); -- 当前时间
insert into stu values ('bbb', '20100303'); -- 插入成功，char 自动转型为 date
insert into stu values ('ccc', to_date('1999-01-11', 'yyyy-mm-dd'); -- 手动转型
二进制类型 RAW LONG RAW LOB 类型
Large Object，用来存储大数据。
Oracle 提供了 DBMS_LOG 包对 LOB 类型的数据进行处理。 也可以在 jdbc 中通过 getXLob() 的方式进行 lob 字段的处理。
CLOB，charactor，存储大容量字符串 BLOB，Binary，存储二进制文件，如小图片、小电影 BFile/XMLType 等
create table article ( id int primary key, title varchar2(20) not null, content clob, createtime date default sysdate ); insert into article (id, title, content) values (1, 'Oracle 使用指南', '你好');
伪列(Pseudo Column) rowid
rowid 是 oracle 中的伪列。可以通过下面语句显示:
select d.rowid, d.* from dept d
它是唯一的，不可变的，固定长度的。
它是数据存储物理地址的一种映射。一共有18位，前6位表示对象id，后3位表示fno，后6位表示块编号，最后3位表示行编号。 所以，通过rowid可以最快速度地定位到数据所在的位置。
比如，如果某行数据的 rowid 是 AAAO0fAAFAAAAlmAAA, 那么可以根据它直接定位数据的物理地址：
AAAO0f AAF AAAAlm AAA 对象号(6个字符) 文件号(3个字符) 块号(6个字符) 行号(3个字符)
rowid 是 oracle 特有的。
不建议使用 rowid 作为表的主键。迁移的需求，有改变的风险。
rownum
列出每一行数据的行数，从1开始，自然增长。
-- 基本用法 select rownum, d.* from dept; select rownum, d.* from dept d where rownum < 3; -- 显示前两条 select * from (select rownum rn, d.* from dept d) t where t.rn = 3; -- 只显示第三条 select * from (select * from emp order by sal desc) where rownum<=3; -- 显示 emp 表中工资前三位的雇员信息。
NULL
它是 Oracle 中非常特殊的一种类型。它表示不确定，表示没有值。并且它能转化成所有的类型。 向数据库中插入空字符串时，oracle 会把它自动转化为 null 类型。所以，在查询空字符的时候：
select * from n3 where s = '';
上面的语句是非法，不合适的。应该这样查：
select * from n3 where s is null; select * from n3 where s is not null;
创建表的时候，为了约束插入的数据不能为空，应该在字段的后面写上 not null 约束。
create table n5 (s varchar2(20) not null);
跟 null 做任何的运算，结果仍然是 null.
select null + '' from dual; -- null
运算符 算术运算符
加 / 减 / 乘 / 除
连接运算符
是用来连接字符串的。跟java中的 + 是一致的。
select 'abc' || ' bcd ' as 连接后的结果 from dual; select d.dname || ' 部门' from dept d;
比较运算符
> / < / >= / <= / != / <> /IS NULL / Like / Between / In / Exsist
-- 判断 null 值 select * from n5 where s is null; select * from n5 where s is not null; -- like 模糊查询。慎用，有可能会导致全表扫描，效率低。 -- % 匹配0到多个字符，_ 匹配一个字符 select username from dba_users where username like 'VI_'; select username from dba_users where username like 'SC%'; select username from dba_users where username like '%SC%'; -- in，是 where x = a or x = b or x = c 的一种缩写。下面两条是等价的。 select * from emp where empno in (700, 800, 900); select * from emp where empno = 700 or empno = 800 or empno = 900; -- in 后面跟的不一定是逗号分隔的单项，也可能是一个完整的查询语句。 -- 下面两条结果是一致的 -- 这种 in 慎用 select * from emp where deptno in (select deptno from dept where dname = 'SALES'); select a.* from emp a,dept d where a.deptno =d.deptno and d.dname='SALES'; -- between...and select * from emp where empno between 7800 and 9000; -- 等同于： select * from emp where empno >= 7800 and empno <= 9000;
逻辑运算符
and / or / not
not 的优先级 > and 的优先级 > or 的优先级
集合操作符
Union / UnionAll / Intersect / Minus
-- 生成测试数据 create table dept_01 as select * from dept where rownum < 6; create table dept_02 as select * from dept where rownum < 4; insert into dept_02 values (98, '小吃部', '斗门'); insert into dept_02 values (99, '外卖部', '香洲'); commit; select * from dept_01 union select * from dept_02; select * from dept_01 union all select * from dept_02; select * from dept_01 intersect select * from dept_02; select * from dept_01 minus select * from dpet_02;
Join Query
-- 初始化表
create table loc (id int primary key, name varchar2(20));
create table person (name varchar2(20), locid references loc);
-- 初始化数据
insert into gp values (1, '和平', '万岁');
insert into gp values (2, '自由', '很好');
insert into gp values (3, '民主', '爱国');
insert into gp values (4, '敬业', '友善');
insert into loc values (11, '广东');
insert into loc values (22, '山东');
insert into loc values (33, '湖南');
insert into loc values (44, '江西');
insert into person values ('王新炜', 33);
insert into person values ('吴英平', 33);
insert into person values ('杜志海', 44);
insert into person values ('范锐', 11);
commit;
select * from person;
select * from loc;
-- 查询就是一个逐步过滤的过程
-- 叉乘，内部杂交，虚拟表，16条数据
select * from person, loc;
-- 过滤掉不符合条件的数据。即完成一次外连接。
-- 即：增一表，加一条件。
select * from person, loc where person.locid = loc.id;
-- 其他条件，即在上面的基础上继续过滤
select * from person, loc where person.locid = loc.id and person.name like '王%';
-- 增加另一个表
create table gp (id int primary key, name varchar2(20), logo varchar2(10));
alter table person add (gpid int references gp);
-- 修改每个人的 group
-- update ..
select * from gp;
select * from loc;
select * from person;
update loc set name='中华人民共和国湖南省' where id=33;
commit;
select * from person p, loc l, gp g;
-- 将不符合的排除掉
select * from person p, loc l, gp g
where p.locid = l.id
and p.gpid = g.id
-- and l.name = '江西'
and g.name='和平'
and p.name like '王%';
--
-- oracle 写法，非标准
select * from person p, loc l, gp g
where p.locid = l.id and p.gpid = g.id;
-- ansi sql 写法，join 默认是 inner join
select * from person p
join loc l on p.locid = l.id
join gp g on p.gpid = g.id;
-- 增加一个没有地区的人
insert into person values ('黄秀', null, null);
commit;
-- 显示所有人，并将其地区信息查询出来。
-- 需要使用外连接，将即使不匹配的人也查询出来
-- oracle 写法
select * from person, loc
where person.locid = loc.id(+);
-- 标准写法
select * from person p
left outer join loc l on p.locid = l.id;
Functions Numeric abs/mod 绝对值/求模 sqrt/power/exp 求幂 ceil/floor/round 取整 trunc 截取小数点 sin/cos/ln/log 数学函数 Charactor chr/ascii ascii码 length/lengthb 字符/字节长度 initcap/upper/lower 字母大小写 substr/translate/replace/instr 子串/替换 ltrim/rtrim/lpad/rpad 修剪/填充 Datetime
select sysdate, systimestamp, current_date from dual;
-- x 天之后的日期
select sysdate + 365 from dual;
-- x 个月后的日期
select add_months(sysdate, -5) from dual;
-- 相差几个月
select months_between(to_date('20180501'), sysdate) from dual;
-- 下周 x 的日期
select next_day(sysdate, '星期一') from dual;
select next_day(sysdate, 2) from dual; -- 1 代表星期天, 0 代表星期一, 类推
-- 一个月的最后一天/第一天
select last_day(sysdate) from dual;
select trunc(sysdate,'MONTH') from dual;
select trunc(sysdate,'YEAR') from dual;
-- 获取日期的单个字段
select extract(day from sysdate) from dual;
-- 对日期进行截取
select round(to_date('20170916'),'MONTH') from dual; -- 四舍五入
select trunc(to_date('20170916'),'MONTH') from dual;
Nullable nvl/nvl2/nullif Miscellaneous
decode/case when:
-- 生成数据
create table sss (name varchar2(20), gender int);
insert into sss values ('樱桃小丸子', 2);
insert into sss values ('路飞', 1);
insert into sss values ('大熊', 3);
insert into sss values ('光头强', 1);
commit;
-- 查询，将 gender 的数字转化为男女
-- 1. decode 函数的方式
select name, decode(gender, 1, '雄性',
2, '雌性',
'未知性别') "性别" from sss;
-- 2. case when 的方式
select name, case gender
when 1 then '雄性'
when 2 then '雌性'
else '未知性别'
end "性别" from sss;
-- 3. case when 的另一种方式
select name, case
when gender <= 1 then '雄性'
when gender >= 2 then '雌性'
else '未知性别'
end "性别" from sss;
-- 给 emp 中的人加工资：
---- 1000 元以下的，加 50%
---- 2000 元以下的，加 30%
---- 3000 元以下的，加 20%
---- 其他人 5%
create table emp3 as select * from emp;
-- 如果要将所有的工资翻倍，这么写：
update emp3 set sal = sal*2;
-- 如果不同的人加不同的工资，需要用到 case when:
update emp3 set sal = (case
when sal <= 1000 then sal*1.5
when sal <= 2000 then sal*1.3
when sal <= 3000 then sal*1.2
else sal*1.05
end);
-- 如果 1981/5/1 之前来公司的人按照上面调薪，其他人只调 1% 呢？
-- [TODO]
Aggregate Function
聚合函数是用来统计每个分组的统计信息，它们要跟 group by 一起使用，用来将每个分组所有数据 聚合 成一条统计数据。
包括 max/min/count/avg/sum 等。
-- 按照部门进行分组统计的语句
select deptno, -- 因为按照 deptno 分组，select 中只能有 deptno 字段
count(*), -- 每个分组多少条数据
max(sal), -- 每个分组工资的最大值
min(sal), -- 每个分组工资的最小值
avg(sal), -- 每个分组工资的均值
sum(nvl(comm, 0)) -- 每个分组奖金总和，因为奖金可能为 null，所以需要使用 nvl 进行去空
from emp
group by deptno; -- 分组依据
-- 可以用 having 对结果进行过滤
-- 整个 select 语句执行顺序大致是: where -> group by -> having -> order by
select deptno, count(*), max(sal), min(sal), avg(sal), sum(comm)
from emp
group by deptno
having avg(sal) > 2000;
-- having 等价于嵌套的 where，即上面语句跟下面这条等效。
select * from (
select deptno, count(*), max(sal), min(sal), avg(sal) asal, sum(comm)
from emp
group by deptno
) where asal > 2000;
Analytic Function
https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#SQLRF06174
聚合函数统计的是一个分组的信息，聚合之后每个分组只能得到一条信息，但并不能够获取分组内成员的具体信息。
而分析函数可以详细显示分组内部的统计信息，它显示的信息更全面，语法也更复杂。
大致来说，分析函数的使用语法是这样的:
分析函数() over ([partition by xxx] order by yyy [rows/range ...])
解释：
一个分析函数，需要跟着一个窗口函数。 以 over 开始的部分叫 窗口函数, 它描述了要对哪些数据、按照什么方式进行分析的行为。 窗口函数内部分为三部分，其中 order by 是必须的，其它可选 partition by 指定了数据按照什么方式 分组/分区 order by 指定了在分区之内，数据的排序方式 rows/range 可以对分区的数据进行限定 分析函数有很多，比如 row_number/rank/dense_rank/max/count 等，row_number 等有如下区别
ROW_NUMBER 返回连续的排位，不论值是否相等
RANK 具有相等值的行排位相同，序数随后跳跃
DENSE_RANK 具有相等值的行排位相同，序号是连续的
需要查询组本身的信息，用聚合函数 group by; 需要查询组成员的统计信息，需要用分析函数。
示例：
-- 最基本，窗口描述内只有 order by select e.*, sum(sal) over (order by sal) from emp e; select e.*, row_number() over (order by sal) from emp e; -- 以组为单位，进行排序 select e.*, sum(sal) over (partition by deptno order by sal) from emp e; select e.*, row_number() over (partition by deptno order by sal) from emp e; -- 比如，要查询每个组的最高工资，可以用聚合函数 select deptno, max(sal) from emp group by deptno; -- 但如果要查看工资最高的那个人，聚合函数无能为力，需要用到分析函数 ---- 1. 首先按组排序 select e.*, row_number() over (partition by deptno order by sal desc) from emp e; ---- 2. 过滤，只取排名第一的那个人，ok select * from (select e.*, row_number() over (partition by deptno order by sal desc) rn from emp e) where rn = 1;
Pagination
使用 rownum 或者 row_number()：
-- 注意，这种写法是无效的，因为：rownum > n 这种写法无意义
select * from emp where rownum > 4 and rownum < 8;
-- 为了能使用 rownum>n，我们只需要嵌套一层查询即可
select *
from (select rownum rn, e.* from emp e)
where rn > 4
and rn < 8;
-- 同样，使用 row_number() 分析函数，我们也需要嵌套一层
select *
from (select row_number() over(order by rowid) rn, e.* from emp e)
where rn > 4
and rn < 8;
-- 而下面这种写法，基本上是最合理，最高效的：
select * from (
select a.*, rownum rn from (
select * from emp
) a where rownum < 8)
where rn > 4;
-- 即:
SELECT * FROM (
SELECT a.*, ROWNUM rn FROM (
-- 要查询的语句
) a where ROWNUM < '#最大序号#')
WHERE rn > '#最小序号#';
查询语句执行顺序
完整的查询语句类似是这样的：
select ..., ROWNUM from table where <where clause> group by <columns> having <having clause> order by <columns>;
它的处理顺序是：
首先，执行 FROM/WHERE 语句 为结果赋予 ROWNUM 并同时根据 FROM/WHERE 语句进行 rownum 过滤 执行 SELECT 执行 GROUP BY 执行 HAVING 执行 ORDER BY
PS: 如果存在分析函数(row_number etc)，那么，分析函数的执行会在 order by 之前。
DDL/DML/TCL/DCL
表的约束:
是加在表上的一种对象，能保证每次插入或修改数据的时候，对数据的合法性进行检查，从而避免插入不合理数据。 优点：通过检查，能保证数据的完整性和一致性。 缺点：消耗一定的存储，数据量大的时候，每次检查会有一定的资源损耗
-- 首先，创建表：类型、长度、非空 create table ddl_test1 ( id int, name varchar2(20) not null, birthday date, sex int ); -- 在表 ddl_test1 上增加一个名字为 fk_ddl_sex_01 的约束 -- 作用在 sex 列上 / 关联到 ddl_sex 表的 id 列 alter table ddl_test1 add constraint fk_ddl_sex_01 foreign key (sex) references ddl_sex (id); create table ddl_sex ( id int, value varchar2(10) ); alter table ddl_sex add constraint pk_ddl_sex primary key(id); select * from ddl_sex; ---- 创建约束的几种方式 ---- 第一种方式，先创建表，再初始化数据，最后再加约束 -- 创建表 create table ddl_test2 ( id int, name varchar2(20) not null, birthday date, sex int ); create table ddl_sex2 ( id int, value varchar2(10) ); -- 插入数据 insert into ddl_sex values (1, '男'); insert into ddl_sex values (2, '女'); insert into ddl_test2 values (2, 'xx', sysdate, 1); insert into ddl_sex values (3, '不知'); insert into ddl_test2 values (1, 'xx', sysdate, 1); insert into ddl_test2 values (3, 'xx', sysdate, 1); insert into ddl_test2 values (4, 'xx', sysdate, 1); -- 增加约束 alter table ddl_test2 add constraint fk_0023 foreign key (sex) references ddl_sex; alter table ddl_test2 add constraint pk_test2 primary key (id); ---- 第二种方式，在建表的字段上直接建立约束 create table ddl_test4 ( id int primary key, name varchar2(20) not null, sex constraint hello250 references ddl_sex ); ---- 第三种方式，将建立约束的语句，放到建表语句最后。优点，清晰易于管理。 create table ddl_test5 ( id int, name varchar2(20) not null, sex int, constraint hello260 primary key(id), foreign key (sex) references ddl_sex ); ---- 约束的种类 ---- 主要有：主键约束、非空约束、唯一约束、检查约束、外键约束等 create table ddl_test6 ( id int, sal number(5) ); alter table ddl_test6 add constraint pk_test6 primary key (id); alter table ddl_test6 add constraint ck_2323 check(sal > 1250); insert into ddl_test6 values (1, 500); insert into ddl_test6 values (2, 3500); select * from ddl_test6;
表的命名:
简 明
另外：
表名等在数据库内部会自动转化为大写的形式。如果想使用小写的形式，需要在创建的时候，加双引号。
create table "lowcase_name" (...);
表名尽量使用英文单词，或英文单词缩写词。如果有多个单词连接，请使用下划线。 不要使用复数形式。比如使用 boy 而不是 boys, 使用 student 而非 students. 字段名字有两种方式:
-- 清晰明了不拖泥带水，但多表联合查询，可能出现重复字段 create table boy (id int, name varchar2(20), wechat varchar2(20)); -- 写法丑，但联合查询不会出现重复字段 create table girl (girl_id int, girl_name varchar2(20), girl_wechat varchar2(20));
主键请使用 代理主键, 即没有任何业务关联的字段作为主键。因为直觉上不变的东西，在特定情况下都可能发生变化。 自增，请使用 序列, 最好为每个单独的主键创建一个专用的序列。
-- 首先，保证有创建序列的权限
GRANT CREATE SEQUENCE TO VIP;
-- 创建序列的最简语句
-- 注意，命名中，最好带 seq 等字段，表示这是一个序列
CREATE SEQUENCE seq_boy;
-- 使用的方式很简单
insert into boy values (seq_boy.nextval, 'xxx');
select seq_boy.currval -- 序列当前值
seq_boy.nextval -- 序列下一个值
from dual;
-- 序列可以有更多参数
create sequence seq_boy2
minvalue 2 -- 最小值，默认 1
maxvalue 1000 -- 最大值，默认无限
start with 4 -- 初始值，默认跟 minvalue 相同
increment by 2 -- 步进
nocycle -- 如果到达最大值，是否从开始再次循环
nocache -- 设置缓存
;
-- 修改
alter sequence seq_boy2 cache 10;
alter sequence seq_boy2 maxvalue 2000 increment by 5;
-- 删除
drop sequence seq_boy2;
修改, ALTER:
-- 创建示例表 create table ddl_test1 ( id int primary key, name varchar(2) not null, birth date default sysdate ); -- 各种修改 alter table ddl_test1 rename column id to testid; alter table ddl_test1 modify (name varchar2(40), birth timestamp); alter table ddl_test1 add gender varchar2(10) not null; alter table ddl_test1 drop column gender; -- 作用在序列上的 DDL create sequence seq_aaa; alter sequence seq_aaa start with 222; drop sequence seq_bbb;
查询, select:
-- SELECT 语句中的基本计算，是基于行的
-- 不同行中的数据，不能直接计算
-- 所以，解决的方案是：将不同行的数据整合到同一行中，再做比较
-- 而整合到同一行，有很多方法：
-- 可以是表跟表进行关联；可以是分组；等等。
-- 关键是：思路，思路，思路。
-- 下面是一个例子
-- 查询男、女之差
-- 首先，初始化数据
-- 男 / 46人
-- 女 / 10人
create table aaa (gender char(2), num number);
insert into aaa values ('男', 46);
insert into aaa values ('女', 10);
-- 1. 这是一种方法，
with
a as (select * from aaa where gender = '男'),
b as (select * from aaa where gender = '女')
select a.num - b.num from a, b;
-- 2. 下面是上面方式的等价写法
select a.num - b.num
from (select * from aaa where gender = '男') a,
(select * from aaa where gender = '女') b;
-- 3. 下面是另一种写法
select (select num from aaa where gender = '男') - num from aaa where gender = '女';
-- 4. 最基本的，对表进行关联查询，之后再过滤，再计算
select a.num - b.num from aaa a, aaa b where a.gender='男' and b.gender='女';
-- 5. 或者基于分组
select max(num) - min(num) from aaa;
-- 6.7.8. 其他方法...
同义词/视图
这两种都用来：
精简语句 隐藏细节 同义词(Synonym)
为 表或对象 创建别名：
可以简化 sql 语句 可以隐藏细节 提供公共访问（公有同义词）
-- 查看跟同义词有关的权限
-- 分两种：
--- synonym, 创建来自己使用
--- public synonym, 创建来所有人都可以使用
select * from system_privilege_map where name like upper('%syn%');
-- 将创建同义词的权限授权给用户
grant create synonym to vip;
grant create public synonym to vip;
-- 创建
create synonym e for scott.emp;
create or replace public synonym d for scott.dept;
-- 可以为表之外的对象创建同义词
create sequence seq_aaaaaaforme;
create synonym seq_aaa for seq_aaaaaaforme;
-- 使用，在一切可以使用原先对象的地方
select * from e join d using (deptno);
desc d;
select seq_aaa.nextval from dual;
-- 删除
drop synonym d;
drop public synonym d;
视图(View)
视图, 默认指的是关系视图, 又叫虚表。
不占用数据空间 可以简化语句 可以隐藏细节 可以提升安全
除了关系视图，广义的视图包括：
关系视图(狭义的视图，虚表) 内嵌视图(子查询中的临时结果) 对象视图(面向对象) 物化视图(以空间换时间)
-- 查询跟视图相关的权限
select * from system_privilege_map where name like '%VIEW%';
-- 确保用户有足够权限
grant create view to vip;
grant all on scott.emp to vip;
grant all on scott.dept to vip;
-- 创建视图
create view vemp as select * from scott.emp; -- 最简形式，为单个表创建视图
create view v_emp_vip as select * from scott.emp where sal >= 2000; -- 可以为表的部分数据创建视图
create view vvevip as select * from vevip; -- 可以为视图查询创建视图
create or replace view vevip as -- 可以为多表联合查询的结果集创建视图。
select e.*, d.dname, d.loc
from emp e, dept d
where e.deptno = d.deptno(+)
and e.sal >= 2000;
-- 使用
select * from e;
select * from vemp;
select * from v_emp_vip;
select * from vevip;
select * from vvevip;
-- 删除
drop view vemp;
-- force 强迫！！！不管 select 语句是否有错，都要创建。
-- or replace, 如果视图已存在，覆盖
-- with read only, 创建只读视图
create or replace force view vaaa as select * from wohaoshuai with read only;
-- with check option, 防止更新后的数据, 超出视图的范围
insert into emp (empno, ename, sal) values (9988, '张思', 3333);
create or replace view vbbb as select * from emp where sal > 3332;
select * from vbbb; -- 两条数据，King 和 张思
update vbbb set sal = 2000 where empno = 9988;
select * from vbbb; -- 只剩一条数据。
-- 按照道理，update 只是更新数据，不应该导致结果集变少。所以，需要通过 with check option 来限制不合理的修改
create or replace view vcc as select * from emp where sal > 1500 with check option;
update vbbb set sal = 1000 where empno = 9988; -- 会报错
效率和速度 表分区
分而治之
目的：
安全（鸡蛋不要放在一个篮子里） 效率****（快速找到南方it学院所有姓张的人） 便于维护
场景：
数据量极大（大于 2G） 历史数据比重比较大
分类：
范围分区 列表分区 哈希分区(hash)
在以上分区的基础上，可以两两结合，形成 *复合分区*，但常用的就是两种：
范围-列表分区 范围-哈希分区
范围分区:
-- 创建一个普通表的语句
create table person1 (id int primary key, name varchar2(20), birth date);
-- 数据将会在同一个表空间同一个段内
insert into person1 values (1,