全网最全Oracle学习，SQL语法笔记

• 作者：彭老师
• 日期：2019-06-20
• 类型：Android
• 说明：本文源于彭老师手写摘要，如需转载请带上链接或注明出处！

---

前言

---

堪称全球最贵数据库Oracle

Oracle数据库堪称全球最贵数据库，职业有：DBA、DBM等。（DataBase Administrator、Master）

1977年成立于美国加州红木滩

1978年迁往硅谷，更名：关系式软件公司（RSI）

1982年，更名：Oracle（甲骨文）

1985年，正式发布5.0版本

1989年，正式进入中国市场，并于：2007年，占数据库领域48.6%，

2009年4月20日，以74亿美元收购Sun公司（Java）

• Oracle数据库特点：
• 1、功能强大
• 2、速度最快
• 3、市场占有率最高

Oracle老板：Ellison 埃里森 <1944年出生>

安装Oracle，自带测试用户：scott（斯科特）

他是Oracle开发的第一个员工姓名、全名：Bruce Scott

而密码：tiger，为scott养的宠物猫名

1983年3月，RSI发布Oracle第三版，不过此时scott离开了公司，由于C语言。

• Oracle，对象关系型的数据库管理系统（ORDBMS）是面向对象技术与传统的关系数据库相结合的产物
• 应用领域：
• 1、管理信息系统
• 2、企业数据处理
• 3、因特网
• 4、电子商务

• 突出的优势：
• 1、安全性、完整性、优越性
• 2、跨操作系统，跨硬件平台的数据互操作能力

• 主要特点：
• 1、支持多用户、大事务量的事务处理
• 2、数据安全性和完整性控制
• 3、支持分布式数据处理
• 4、可移植性

• 优越：
• 1、对数据库表进行最佳管理
• 2、处理多个客户端对同一数据的并发访问
• 3、保持数据完整性
• 4、控制数据库访问权限等安全性需求

• 缺点：
• 1、安装时路劲不能有中文
• 2、语句不区分大小写 <除字符串内容>
• 3、用户隔离

---

正课内容

---

第1节 - 表空间操作 创建新用户 解锁锁定 授权操作

-- Oracle创建表空间
create tablespace 表空间名
datafile 'X:\oracle\product\......\xxx.dbf' --表空间文件路径
size 10m --文件初始大小 
autoextend on --自动增长开启
next 1m --每次增长加1兆
maxsize 100m; --最大100兆

-- 删除表空间（包括该空间所有的内容和文件） 
drop tablespace 表空间名 including contents and datafiles;

-- 单纯创建新用户：
create user 用户名 identified by 密码; -- 注意：不能以数字开头

-- 创建新用户，同时设置该用户的默认表空间
create user 用户名 identified by 密码
default tablespace 表空间名;

-- 修改用户的表空间（注意：原表还在原来的表空间、新表创建到修改后的表空间）
Alter user 用户名 default tablespace 新表空间名;

-- 删除用户：
drop user 用户名 cascade;

-- 修改密码：语句与创建新用户一样

-- 锁定、解锁用户：
Alter user scott account lock/unlock;

-- 授权：（只有拥有DBA权限的用户，才可以授予新用户权限）
grant connect, resource, dba to 用户名;

/*
其中：
connect：允许用户连接到数据库的角色（包括了序列：sequence角色）
resource：允许用户使用数据库存储空间的角色
dba：系统管理员
常用的权限组织在一起形成了角色
权限 → 角色 → 用户。简称：权限管理
*/

-- 回收权限：
revoke connect, resource, dba from 用户名;

---

第2节 - 操作权限 查询当前用户语句

-- 单独对用户授予表操作权限
grant select any table to scott; --查看
grant delete any table to scott; --删除
grant update any table to scott; --修改
grant insert any table to scott; --插入
grant create any table to scott; --创建
grant all on 表名 to 用户名; --效果相同

-- 允许用户操作表空间
alter user 用户名 quota unlimited on 表空间名;

-- 查看当前用户语句有
select * from user_sys_privs; --查看当前用户的系统权限
select * from user_role_privs; --查看当前用户被授予的角色
select * from role_sys_privs; --查看角色对应的系统权限

select * from dba_roles; --查看所有角色为dba
select * from user_tablespaces; --查看该用户所有的表空间
select * from all_users; --查看所有用户
select * from all_tables; --查看所有用户的表

-- 查询当前用户所有表
select table_name, tablespace_name from tabs;
select table_name, tablespace_name from user_tables;

---

第3节 - 表的命名规范 创建表 增删改字段操作

-- 创建表
create table 表名
(myid number,
myname varchar (20),
mysex char (2),
myage number (3,0),
regtime date --切记最后不加逗号
) tablespace 表空间名; -- 将表创建到指定表空间

-- 添加单个字段（add）
alter table 表名
add 字段名 varchar2 (20);

-- 同时添加多个字段（add）
alter table 表名
add (myaddr varchar2 (40), mytel number (8,0));

-- 修改单个字段（modify）
alter table 表名
modify (myaddr varchar2 (80));

-- 同时修改多个字段（modify）
alter table 表名
modify (myaddr varchar2 (120), mytel number (12,0));

-- 删除单个字段（drop column 字段名）
alter table 表名
drop column 字段名;

-- 同时删除多个字段
alter table 表名
drop (字段名, 字段名);

---

第4节 - 字段约束操作 移动表空间 修改表名 删除某列

-- 增加字段默认值（modify）和非空值
alter table mytable -- mytable为表名，下面不做注释
modify myname not null; -- 字段名myname非空，也可以在创建表时直接写
modify mysex default('男'); -- 字段名mysex默认值，default初始值

-- 增加主键约束（primary key）
alter table mytable
-- PK_myid为约束名，myid为字段名
add constraint PK_myid primary key (myid);

-- 增加唯一键约束
alter table mytable
-- UN_myname为约束名，myname为字段名
add constraint UN_myname unique (myname);

-- 增加检查约束（check (条件)）
alter table mytable
-- CK_mysex为约束名，mysex为字段名
add constraint CK_mysex check (mysex in ('男','女')); -- in ()方法含义：xx或者xx

alter table mytable
-- CK_myage为约束名，myage为字段名
add constraint CK_myage check (myage between 15 and 30); -- between方法含义：在xx和xx之间

/*
增加外键约束（foreign key ... references）
① 从表和主表建立关系
② 关联字段为公共字段
*/
alter table 表名
add constraint 从表外键名
foreign key (从表字段) references 主表表名 (主表字段);

-- 查询表的所有约束
select * from user_constraints
where table_name = '表名';

-- 删除单个约束（drop constraint）
alter table mytable
drop constraint UK_myname; -- UK_myname为约束名

-- 清空表数据（约束还在，语法：delete from 表名）
delete from mytable;

/*  
删除表有两种：delete和drop
① 仅删除表，不包括约束
② 彻底删除表，包括所有约束
*/
-- 删除表以及该表的所有约束
drop table 表名 cascade constraints;

-- 仅删除表，不包括约束
drop table 表名;

-- 启用约束：
alter table 表名 enable constraint 约束名;

-- 禁用约束
alter table 表名 disable constraint 约束名;

-- 移动表到新的表空间
alter table 表名 move tablespace 新表空间名;

-- 修改表明
rename 旧表名 to 新表名

-- 删除表中一列
alter table 表名 drop column 字段名;

---

第5节 - 增、删、改、查简介

/*
Oracle数据库重点：增、删、改、查，其中以“查”为精华！
增、删、改语句执行后，效果成功切记提交事务，
否则，关闭不保存操作，仅仅限于内存变动！
*/

-- 单行增加语句 (方法一)
insert into 表名(字段1，字段3，字段6......)
values ('值1', '字符串值2', default, null, sysdate,......);
-- 注：字段可以缺少，值必须一一对应，为默认值时：default，为空时：null

-- 单行增加语句 (方法二)
insert into 表名
values (所有值缺一不可，并一一可对应字段);
-- 注：字段不填，但是值必须全部填充并一一对应

-- 多行插入语句：(多行时不能使用default)
insert into 表名
select 也是所有值缺一不可，并一一对应字段，但没有括号 from dual;
union
select ...... from dual;
......

-- 删，删除字段对应的值 (delete from 表名)
-- 方法一，常用语句
delete from 表名 where 字段名 = '值';

-- 方法二，基本不用 (截断数据，没有日志，无法回滚，不能加条件)
truncate table 表名;

-- 改，修改字段对应的值 (update 表名 set 字段名 = '值')
update 表名 set 字段名 = '值' where 字段名 = '值'; -- 无条件则修改该字段所有值

---

第6节 - 通配符 序列操作 表的备份 别名使用

/*
SQL：结构化查询语句
DML：数据操作语言
DDL：数据定义语言
*/

/*
通配符 (like %上海% 或者 like 张_)
% 百分号：为任意长度，任意内容
_ 下划线：为一个长度，任意内容
一般用作条件，修改或查询其他字段对应值。注意：容易写成=等号
*/
select 字段名或* from 表名 where 字段名 like 通配符;
update 表名 set 字段名 = '值' where 字段名 like 通配符;

-- 新建序列：用于表的ID，自动编号，产生连续数字的工具，从1开始
create sequence 序列名; -- 可以多运行几次next，直到接上已有表序列号为止
select 序列名.currval from dual; -- 查询当前序列号
select 序列名.nextval from dual; -- 查看并产生序列的下一个值
drop sequence 序列名; -- 删除序列

-- Oracle表的备份
create table 新表名
as
select * from 旧表名;

-- Oracle简单查询 (scott自带两张表：emp, dept)
select * from 表名; -- 查询表的所有信息
select 字段1, 字段2, ...... from 表名; -- 查询表的部分字段信息
-- 去掉重复内容查询 (多行数据的值在字段内完全一样，则只显示一行值)
select distinct 字段名, ...... from 表名;

-- Oracle 语法不区分大小写，但字符串内区分！
select * from emp where ename = 'jack'; -- 错误写法
select * from EMP whEre Ename = 'JACK'; -- 正确写法

-- 给字段取别名 (无单引号，别名包括含空格时则需要双引号：as 自定义别名)
select empno as 编号, deptno as "部门 编号" from emp; -- as 可以省略不写
select 字段名 as 中文注释, 字段名 中文注释 from 表名;

/*
① as可以省略，可写可不写
② 空格特殊："中文 中文" from 表名; 一般不用加引号
③ 取别名不是修改，只是暂时在查询结果中改个显示名字
*/

---

第7节 - 算数操作 字符串拼接 排序 模糊查询 between and用法 in用法

-- 空值null与其他数据操作时结果都会变为null
select sal + comm as 总工资 from emp; -- 没有提成的，变null
-- 问题：没有提成的职员都会变空值
select sal + nvl(comm, 0) as 总工资 from emp; -- 把空值转为0
-- 解决：在有可能为空的字段前加上nvl(字段名, 替代值)

-- 字符串拼接（|| java中的或者条件符，可以拼接任何类型），还有一种concat方法
select '员工信息：' || empno || ename || sal || ... as 别名 from emp;

-- 排序（order by 字段名; asc为默认升序，可以省略不写，desc为降序，要写）
select * from emp order by sal asc/desc;

-- 多字段排序：（注意：over恰好相反）
select * from emp order by sal desc, empno desc;
-- 先按工资降序排序，如有相同值再按部门编号排降序

-- 针对有空值字段排序（Oracle认为空值是最大值）
-- 升序asc看不出问题，desc降序问题就出来了。解决方案：nulls last
select * from emp order by comm desc nulls last;

-- 模糊查询：like 通配符(%或_)
-- 查询姓名以'J'开头的职员信息 
select * from emp where ename like 'J%'; -- 字符串内区分大小写
-- 查询名字中含有'A'的职员名字
select ename as 姓名 from emp where ename like '%A%';
-- 查询'J'开头，总共4个字母的人
select * from emp where ename like 'J____'; -- 4个_通配符

-- 空值查询：查询所有奖金为空/不为空的职员
select * from emp where comm is null/is not null;

-- between and 用法（可用于数字之间也可以用于Date时间之间）区间选值
select * from emp where deptno >= 10 and deptno <= 20; -- between 10 and 20
select * from emp where hiredate between '1-1月-1981' and '31-12月-1981';

-- in 用法（or或者，后面复杂查询语句有用到） 绝对是多选一有值
select * from emp where sal = 1000 or sal = 2000;
select * from emp where sal in(1000, 2000);

---

第8节 - 伪列用法 表取别名 聚合函数count sum avg min max用法

-- 伪列中 rownum 作为条件时，只能使用小于或小于等于
-- 用伪列查询字段时，必须要给表名加别名，否则报错：缺失表达式

-- 给表取别名（避免繁多重复表名代码，简写效果）
-- 一旦给表定义别名，那么该表所有字段名都也要加上别名
select e.ename, e.job, e.sal from emp e;

-- 显示出emp表中的前8行记录（使用rownum）
select rowid, rownum, e.* from emp e where rownum <= 8;

/* 
数据统计：统计某个字段的数据
① 聚合分组关键字：count、sum、avg、min、max、group by、having
② 其中五个聚合函数为：count(个数) sum(求和) avg(平均值) min(最小值) max(最大值)
③ 聚合分组含义：查询语句中加入了聚合函数，在进行了分组查询。
*/

-- count用法：专门用来统计某表中某个字段值的个数
select count(ename) from emp; -- 统计单个字段行数
select count(*) from emp; -- 统计表的总行数
-- 特殊写法：不重复个数查询：distinct
select count(distinct job) from emp;

-- sum用法：统计某个(数字)字段中所有数据的总和(忽略空值)
select sum(sal) + sum(comm) as 别名 from emp;

-- avg用法：统计某个(数字)字段中所有数据的平均值(忽略空值)
select avg(sal), avg(comm) from emp;

-- min、max用法：可以用于比较任何类型的字段
select max(sal), min(comm) from emp; -- 比较数字
select max(hiredate) from emp; -- 比较进入公司时间
select min(ename) from emp; -- 比较名字的字母A~Z，中文不行

-- 附带条件查询：查询10部门的平均工资
select avg(sal) from emp where deptno = 10;

-- sum、avg忽略空值，不是数字时报错：无效数字，而min、max可以用于比较任何类型

---

第9节 - 聚合分组group by使用 语句写法分解

/* 
聚合分组，分组group by的概念：
① 将一个或多个字段的相同值分成一组
② 取代了一行一行查询繁琐
*/
select 字段A, sum(字段), avg(字段) from 表名 group by 字段A; -- 字段A注意前后呼应

-- 统计每个部门/每个职位的平局工资
select deptno/job, avg(sal) from emp group by deptno/job;

-- 统计不同职位的人数
select job, count(*) from emp group by job;

-- 难题：统计每个部门的平均工资，低于2000的不现实，结果按降序排列
select deptno, avg(sal) from emp
-- where deptno in(10, 30) -- 如果规定在某部门内查询，作为条件插入
group by deptno
having avg(sal) >= 2000 -- 在聚合分组查出结果后，如果需要再次筛选则必须要用having
order by avg(sal) asc; -- 排序永远放在查询语句的最后

/* group by 注意事项：
① 分组了后，无法查看原表字段
② 可以用sum、avg......函数去统计
③ 写法也要留意
*/

/* 聚合分组重点分析，写法分解：（正确写法非常重要）
① 查询表所有信息语句
② where条件范围语句
③ 分组 group by 字段名 语句
④ 再次筛选(条件) having聚合函数(条件)语句
⑤ 最后加入排序语句：order by 字段名 asc/desc
*/

---

第10节 - 内置函数 日期Date使用 日期转换 月份差 天份差 星期几 提取日期

-- 日期函数：Date

-- 日期转换(一般采用字符串转换的形式描述日期) to_date(日期字符串, 日期格式)
select to_date('2010-11-25', 'yyyy-mm-dd') from dual;

-- 显示某个数据：以函数调用的结果，不是来源于表
select xxx from dual;

-- 查询当前系统时间 sysdate
select sysdate from dual;

-- 在一段时间上添加月份 add_months(日期, 数字) 数字可以为负数
select add_months(sysdate, 6) from dual;
-- 查询emp表中，进入公司时间的4个月之后的时间
select add_months(hiredate, 4) from emp;

-- 两个日期之间的月份差(返回小数) months_between(日期, 日期) 大日期写前面，否则负数
select months_between(sysdate, '1-1月-1990') from dual; -- 除以12可以算出年份
-- 查询emp表中，在公司做了多久
select months_between(sysdate, hiredate) from emp;

-- 相差的天数：减法
select ename, sysdate - hiredate from emp;

-- 某日期所在月份的最后一天 last_day(日期) 返回日期
select last_day('10-2月-2012') from dual;

-- 指定日期之后第一个星期几是什么日期 next_day(日期, 星期几)
select next_day(sysdate, 2) from dual; -- 下个星期一是多少号
-- 注意：国外的Date算法是星期日到星期六，索引：0-6，而这里next-day算法为1-7
-- 虽然一周算法一样，但是月份不同1-12月，而java Date API中是0-11
-- 小提示，在Oracle中，String字符串显示在左下角，int数值显示在右下角

-- 提取日期中的年，月，日 (返回int类型)
select extract(year from sysdate) from dual;
select extract(month from sysdate) from dual;
select extract(day from sysdate) from dual;

-- 查询emp表中，5月份入职公司的职员 (作为条件使用)
select * from emp
where extract(month from hiredate) = 5;

---

第11节 - to_char用法 字符串长度length用法 vsize字节算法

-- to_char(日期, 格式字符串) 注意：返回String类型

-- 将日期转为字符串
select to_char(sysdate, 'yyyy"年"mm"月"dd"日"') from dual;

-- 获取日期部分
select to_char(sysdate, 'yyyy') from dual; -- 年
select to_char(sysdate, 'mm') from dual; -- 月
select to_char(sysdate, 'dd') from dual; -- 日
select to_char(sysdate, 'hh24') from dual; -- 小时
select to_char(sysdate, 'mi') from dual; -- 分
select to_char(sysdate, 'ss') from dual; -- 秒
select to_char(sysdate, 'd') from dual; -- 星期几
select to_char(sysdate, 'ddd') from dual; -- 今年的第几天

-- 查询emp表中是1980年入职的职员
select * from emp
where to_char(hiredate, 'yyyy') = '1980';

-- 查询emp表中，所有周末入职的职员 (星期六：7，星期日：1)
select * from emp
where to_char(hiredate, 'd') in ('7', '1');

-- 字符串String长度方法 length(字符串) vsize(字符串)
select length('hello world!你好') from dual;
select vsize('hello world!你好') from dual;

-- 查询所有学生身份证的长度
select length(stucard) from studentinfo;

-- 查询地址长度大于5的学生信息
select * from studentinfo
where length(stuaddress) >= 5;

-- 长度length方法作为约束时 将地址长度限制为2-10之间
alter table studentinfo
add constraint CK_addr check(length(stuaddress) between 2 and 10);
-- 注意：length方法计算的字数不分中英，汉字只算一个字

-- 计算字节数 vsize方法 中文汉字算2个字节
select length('hello你好'), vsize('hello你好') from dual;

---

第12节 - 拼接字符串 截取字符串 去空格 内容替换 多重替换 查找字符索引

-- 拼接字符串 concat(字符串, 字符串) 效果没有'||'好
select concat(concat('hello', 'world'), '!!!') from dual; -- helloworld!!!

-- 截取字符串 substr(字符串, 开始索引, 长度)
select substr('hello world!', 7, 5) from dual; -- 长度不填取到末尾

-- 去空格 trim(字符串) 只能去掉前后空格不能去掉字符串中间的空格
select trim(' h ello ') from dual;

-- 内容替换 replace(字符串, 替换内容, 替换字符) 当成一个整体去替换
select replace(' h el lo', ' ','') from dual; -- hello
select replace('hello world', 'l', 'x') from dual; -- hexxo worxy
select replace('hello world', 'lo', 'xy') from dual; -- helxy world

-- 多重替换 translate(字符串, 替换内容, 替换字符) 不当成整体每个字节替换
select translate('hello world', 'lo', 'xy') from dual; -- hexxy worxd

-- 查找字符索引 instr(字符串, 查找内容) 返回int，没有得到则是0.类似java String中index of
select instr('hello world', 'x') from dual;

-- 找出家住上海的学员
select * from studentinfo
where instr('stuaddress', '上海') > 0;

-- 查找出以'张'开头的学员
select * from studentinfo
where instr(stuname, '张') = 1;

-- 重载方法 instr(字符串, 查找内容, 开始索引, 第多少个查找内容)
select instr('simon@sina.com.cn.xx', '.', 12) from dual; -- 从索引12开始第一个'.'的位置
select instr('simon@sina.com.cn.xx', '.', 12, 2) from dual; -- 从索引12开始第二个'.'的位置

---

第13节 - ascii码 大小写转换 数学函数 转换函数

-- ascii码 ascii(字符) chr(数字)
select ascii('a') from dual;
select char(97) from dual;

-- 大小写转换 upper(字符串) lower(字符串)
select upper('hello') from dual; -- 转大写
select lower('HELLO') from dual; -- 转小写

-- 数学函数
select power(2, 10) from dual; -- 求幂
select abs(-123) from dual; -- 绝对值
select mod(3, 10) from dual; -- 取模
select ceil(99.0001) from dual; -- 向上取整
select floor(99.9999) from dual; -- 向下取整
select round(99.2) from dual; -- 四舍五入取整
select round(99.946, 2) from dual; -- 四舍五入，精确位数
select dbms_random.value(1, 100) from dual; -- 随机范围数

-- 转换函数 to_char(内容, 格式) to_number(数字字符) to_date(日期字符, 日期格式)
-- to_char将数字转为字符串
select to_char(123) from dual;
-- L999.999中的L为本国货币符号代码
select to_char(123.456789, '$999.999') from dual; -- 123.456
select to_char(123.456789, 'L999.999') from dual; -- ￥123.456

-- 打印出 NO0001-NO1000效果
create sequence seq_no; -- 创建序列
select 'NO' || trim(to_char(seq_no.nextval, '0000')) from dual;

-- to_number将字符串转为数字
select to_number('123') from dual;

-- 空值转为替代值 nvl(字段, 替代值)
select sal + nvl(comm, 0) from emp;

---

第14节 - 高级查询 表连接join 内连接

-- 分析函数 高级查询 高级排序功能

-- 按工资升序排序查询职员信息，并显示序号
select rownum, e.* from emp e order by sal; -- 第一步
select rownum, Y.* from (select * from emp order by sal) Y; -- 第二步

-- 高级查询用到表：emp dept
select * from emp;
select * from dept;

-- 连接查询 (内连接)
-- 公共字段：描述相同信息的字段 (关键的关键)
-- 语法公式：表名 join 表名 on 公共字段

-- 查询所有职员以及所在部门的详细信息
select * from emp join dept on emp.deptno = dept.deptno;

/*
Oracle表连接：将两张或多张表中公共字段值相同的数据行连成一行。
主外键一定是公共字段，公共字段不限于只是主外键
*/

/*
内连接：两张表中互相无法连接的数据则不会显示
注释：与公共字段无关的数据无法显示
*/

-- 别名的优势使用
select * from emp e
join dept d on e.deptno = d.deptno; -- join前的inner可以省略

/*
表为什么要去别名？
表名太长时，可以用别名代替

但，公共字段名相同呢？
一样用对应的别名点出来，声明来源

公共字段相同并不要求字段名一样
只要值的类型是一样
*/

-- 学生成绩信息，显示对应学生姓名 (两张表)
select * from studentinfo s join studentexam e on s.stuid = e.estuid;

-- 学生名字，班级编号，带班老师姓名 (三张表)
select s.stuname, c.classnumber, t.teachername
from studentinfo s join classinfo c on s.sclassid = c.classid
join teacherinfo t on c.cteacherid = t.teacherid;
-- 步骤：连接表1表2，公共字段写完了，再join表三on公共字段

---

第15节 - 高级连表查询 连表步骤 外连接 交叉连接

-- 高级连表查询第二种写法 (常用)
select 字段,字段,字段 from 表名, 表名, 表名 where 公共字段 and 公共字段;
-- 切记：不管要做什么操作，先连接好所用的表！

select s.stuname, c.classnumber, t.teachername
from studentinfo s, classinfo c, teacherinfo t
where s.sclassid = c.classid
and
c.cteacherid = t.teacherid;

/*
连表步骤十分重要：
① 先连接
② 再看需求操作
③ 再对应修改查询语句
*/

-- 实战：where条件中公共字段加入and条件，例如：查询'张三'的考试次数
select count(*) from studentinfo s, studentexam e
where s.stuid = e.estuid
and
s.stuname = '张三';

-- 外连接：left right full (只能用join 表名 on 公共字段)
-- 含义：以一张表作为主表，主表信息全部显示，从表只显示与主表能搭配的数据
select * from emp e left join dept d on e.depton = d.depton;
-- 同理right：右表全部列出，左表只显示匹配数据。(匹配对应公共字段)
-- 同理full：两边数据都显示，没有匹配上的公共字段的值为空

-- 交叉连接 cross 一通乱连没有on公共字段
select * from emp e cross join dept t;

-- 查询成绩大于80分的学员姓名和考试科目
select s.stuname, e.examsubject, e.examresult
from studentinfo s join studentexam e
on s.stuid = e.estuid
where e.examresult >= 80;
-- 第二种写法
select s.stuname, e.examsubject, e.examresult
from studentinfo s, studentexam e
where s.stuid = e.estuid
and
e.examresult >= 80;

-- 代码规范问题，第一：分步写。第二：注释写上

---

第16节 - 简单子查询 嵌套查询 in的用法

-- 简单子查询 也叫嵌套查询，以一张表查出结果作为条件
/*
子查询详解：
① 只有一张表，条件嵌套其他表
② 大查询(小查询)
③ 小查询作为条件融入大查询
变相理解：类似java中，定义其变量，得到返回值后，再作为条件变量参数
*/

-- 语法公式：(注意：公共字段=子查询返回值应为公共字段)
select * from 表1
where 表1某字段 = 
(select 表2与表1公共字段 from 表2
where 表2字段 = (条件));

-- 查询emp表中所有ACCOUNTING部门的职员
select * from emp where deptno = 
(select deptno from dept where dname = 'ACCOUNTING');

-- 查询出'李四'带的班级信息
select * from classinfo where cteacherid = 
(select teacherid from teacherinfo where teachername = '李四');
-- 由此看出：表1 where 字段 = (表2查询结果返回值一定要为表1字段);

-- 查询所有高于平均工资的职员信息
select * from emp where sal > (select avg(sal) from emp);

-- 难点：in的用法晋级 比较操作符的子查询查询出的结果必须只有一个值
-- 查询所有地址在NEW YORK与DALLAS的部门职员
select * from emp
where deptno in -- 此处必须要用in，因为查询出的结果肯定不止一个
(select deptno from dept where loc in('NEW YORK', 'DALLAS'));
-- in用法深入：一个字段可以在多个值之间任取，多个值一定是一列(一个字段中)中多个行

-- 查询出所有参加Java考试的学生
select * from studentinfo
where stuid in -- 此处用in更保险，即使一个值也可以用in
(select estuid from studentexam where examsubject = 'Java');

-- 用连接查询显示没有考试的学生
select * from studentinfo s full join studentexam e
on s.stuid = e.estuid
where e.examid is null; -- 不为空则为：is not null

-- 查询所有与'SMITH'同部门同职位的职员
select * from emp
where job = (select job from emp where ename = 'SMITH')
and
deptno = (select deptno from emp where ename = 'SMITH');

-- 第二种写法
select * from emp where (job, deptno)
in -- 注意：如果是不同部门则需要分开写
(select job, deptno from emp where ename = 'SMITH');

---

第17节 - 内联视图子查询 临时表 辨别别名

-- 内联视图子查询
-- 将一个查询结果当成临时表来使用，放在语句中与其他表关联
select * from (select *from emp) Y;

-- 需要注意的是：使用连接查询作为子查询时，要去掉重复字段即公共字段
select * from
(select e.*, d.dname, d.loc from emp e, dept d 
where e.deptno = d.deptno) Y;

/*
查询结果暂存TEMP
① 放from后面，而不是放where后面
② 表1后面加上逗号，因为后面也是新表
③ 连接查询时，去掉重复字段只留一个，因为表中不能有重复字段名
所以e.*所有emp字段，包括deptno。而d.dname, d.loc就不能再写d.deptno
*/

-- 查询职员信息以及所在部门的平均工资
select * from emp e,
(select deptno, avg(sal) from emp group by deptno) Y
where e.deptno = Y.deptno;

-- 查询工资高于每个部门平均工资的人
select * from emp e,
(select deptno, avg(sal) as avgsal
from emp group by deptno) Y
where e.deptno = Y.deptno -- 原表与新表挂钩
and e.sal > Y.avgsal; -- 新表新字段avgsal与原表sal比较

/*
仔细辨别别名
① 原表别名
② 字段别名
③ 新表别名
*/

---

第18节 - 高级查询难题解析 伪列妙用 分页查询

-- 命题：显示学员信息，以及所在班级的平均分

/*
分析：
① 查询班级编号
② 查询班级的平均分 (与①作为新表)
③ 将上面的结果与学生信息表连接
*/

-- 写法原型
select * from studentinfo s, studentexam e, classinfo c
where s.stuid = e.estuid and s.sclassid = c.classid;
-- ② 修改平均分：把*换成 avg(examresult) as avge 所有班级考试总平均分
-- ③ 分组，语句末尾加上 group by c.classid,对应平均分之前加入c.classid,
-- ④ 大括号全部括起来，当做新表，并取名Y
-- ⑤ 大查询 select * from studentinfo s, (新表) Y
-- ⑥ 末尾加上条件 where s.sclassid = Y.classid 公共字段，连表

select * from studentinfo s
(select c.classid, avg(examresult) as avge
from studentinfo s, studentexam e, classinfo c
where s.stuid = e.estuid
and s.sclassid = c.classid
group by c.classid) Y
where s.sclassid = Y.classid;

-- 查询工资最低5人，使用伪列rownum与排序
select rownum, Y.* from
(select * from emp order by sal) Y
where rownum > 5;
-- 关键：排序原表order，得到新表，在用rownum排序新表
-- 避免order by与rownum冲突，有点像偷梁换柱

-- Oracle分页查询经常要用到
-- 原理：将伪列转换成临时表的一个普通列(字段)
select * from
(select rownum RN, e.* from emp e) Y
where Y.RN > 1 and Y.RN <= 5;
/*
一页只显示5行数据
伪列不能大于等于，好比没有1,2,3...就没有5，不能凭空出现
所以只能再次偷梁换柱，将伪列变成新表中的一个字段
最后，再将该字段写条件，这样就可以写大于和指定的5
Y.RN 实质上是不存在的
*/

---

最后给大家上一段：标准的连接Oracle数据库的示例Java代码

public void testOracle() {
    Connection con = null; // 创建一个数据库连接
    PreparedStatement pre = null; // 创建预编译语句对象，一般都是用这个而不用Statement
    ResultSet result = null; // 创建一个结果集对象
    try {
        Class.forName("oracle.jdbc.driver.OracleDriver"); // 加载Oracle驱动程序
        System.out.println("开始尝试连接数据库！");
        // 127.0.0.1是本机地址，DBname是精简版Oracle的默认数据库名
        String url = "jdbc:oracle:" + "thin:@127.0.0.1:1521:DBname";
        String user = "simon"; // 用户名,系统默认的账户名
        String password = "123456"; // 你安装时选设置的密码
        con = DriverManager.getConnection(url, user, password); // 获取连接
        System.out.println("连接成功！");
        String sql = "select * from student where name=?"; // 预编译语句，“？”占位符代表参数
        pre = con.prepareStatement(sql); // 实例化预编译语句
        pre.setString(1, "西门提督"); // 设置参数，前面的1表示参数的索引，而不是表中列名的索引
        result = pre.executeQuery(); // 执行查询，注意括号中不需要再加参数
        while (result.next())
            // 当结果集不为空时
            System.out.println("学号:" + result.getInt("id") + "姓名:"
                    + result.getString("name"));
    }
    catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            // 逐一将上面的几个对象关闭，因为不关闭的话会影响性能、并且占用资源
            // 注意关闭的顺序，最后使用的最先关闭
            if (result != null)
                result.close();
            if (pre != null)
                pre.close();
            if (con != null)
                con.close();
            System.out.println("数据库连接已关闭！");
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }
}

---

补充：

---

Oracle结构：Oracle数据库：数据的集合，被视为一个逻辑单元

Oracle实例：管理数据库的后台进程和内存结构的集合

Oracle由系统文件组成，这些文件为数据库信息提供实际物理存储区

物理结构：包括数据库中的一组操作系统文件

逻辑结构：数据库创建之后形成的逻辑概念之间的关系

• 物理文件分三类：
• 1、数据文件： .dbf 用于存储数据库数据，如：表、索引数据
• 2、控制文件： .ctl 记录数据库物理结构的二进制文件
• 3、日志文件： .log 记录所有操作修改信息、用于故障恢复

逻辑组件： 数据库 → 表空间 → 表 → 段 → 区 → 数据块

• 安装Oracle时注意事项：
• 1、安装Oracle数据库路径不能有中文
• 2、安装之后，开始 → Oracle → NetManager，配置IP或机器名一致
• 3、安装PL-SQL，破解、汉化
• 4、登录 system，密码为安装时设置，ORCL、Normal （修改system密码改为sysdba）

Oracle监听服务项：

cmd命令（Win + R）：services.msc可直接打开系统服务

必须开启下面两个服务才能使用，强烈建议设置为手动，否则开机时间很长很长

监听服务： OracleOraDblog_homelTNSListener

Oracle服务： OracleServiceORCL

表空间、段的概念：

表空间是数据库中最大的逻辑单元（其中有默认安装的system）

一个数据文件只能与一个表空间关联，反之不同！（表空间可以包括多个数据文件）

段：①数据段、②索引段、③回退段、④临时段

• 注意：
• 1、区是段分配的空间，它由连续的数据块组成
• 2、区不能跨数据文件存在

数据块、模式的概念：

数据块：是Oracle所能分配、读取或写入的最小存储单元

管理数据文件的存储空间是以数据块为单位

模式：是对用户所创建的数据库对象总称

包括：表、视图、索引、同义词、序列、过程和程序包等

Oracle配置文件（与配置工具NetManager同步）

Oracle客户端 tnsnames.ora

Oracle服务端 listener.ora

• 如需更改system用户密码：
• 1、system、密码任意、ORCL、SYSDBA。登录
• 2、语句：Alter user system identified by 新密码

system用户：系统管理员，本身拥有DBA权限（DataBase Administrator）

用于：管理数据库用户、管理数据库权限

默认测试用户： scott（需要解锁才可以使用操作）

PL-SQL为第三方工具、可以汉英互转。（Oracle自带SqlPlus）

DOS命令：

监听服务： cmd → lsnrctl start/stop

服务端： cmd → net start/stop oracleserviceorcl

附：

cmd → sqlplus DOS窗口

cmd → sqlplusw 界面版

连接数据库：conn或conn 用户名/密码@网络服务器名;

特殊用法：conn → system as sysdba → 密码 → 进入sysdba模式

断开数据库：disc;

显示当前用户：show user;

退出：exit;

进入编辑：edit;

查看某表结构：desc 表名;

运行：F8

写入文件：spool 文件路径 spool off;

设置屏幕显示行数（默认100）：set linesize 1000;

设置每页显示的数目：set pagesize 50;

设置字符超长度换行：set wrap off

注意：无论在DOS、SqlPlus还是PL-SQL中语句都要用分号;结尾

• Oracle字符数据类型：
• 1、char 长度固定、存储字母数字值、列长度可以为 1-2000 字节
• 2、varchar2 重写了varchar()方法、长度可变、存储字母数字值、大约在 1-4000 字节
• 3、long 长度可变、最多存储 2GB、一个表只能有一列，不为主键，不为键索引，不为参数

Oracle数值数据类型：

可以存储整数、浮点数和实数。最高精度为38位

语法： number(P[ ,S])

其中P表示精度，S表示小数点位数

• 日期（Date）用法：
• 默认格式：dd-mm月-yyyy
• 日期类型：
• 1、Date 精确到秒，如：当前时间：sysdate
• 2、Timestamp 精确到小数点后6位，如：systimestamp

二进制数据：

Raw规定长度，存储 2000字节

longRaw可变长度，存储 2GB

• 大对象数据类型：LOB
• 1、4GB 非结构化信息，如声音和视频
• 2、允许对数据进行高效、随机、分段访问

• Oracle伪列初始：
• 1、伪列是表的原数据，但并没有存储在表中
• 2、可以从表中查询，但不能插入、更新、删除他们的值

如：Rowid 或 Rownum

Rowid：是表中行的存储地址，唯一标示一行，快速定位

Rownum：查询返回的结果集中行的序列，可以限制查询返回的行数

---