体系结构
数据库-数据库实例-表空间(逻辑单位)(用户)-数据文件(物理单位)
地球-一个国家-省份(逻辑单位)(公民)-山川河流(物理单位)
通常情况下,Oracle数据库只会有一个实例ORCL,
新建一个项目:
MYSQL : 创建一个数据库,创建相应的表
Oracle: 创建一个表空间,创建用户,用户去创建表
Oracle和MYSQL的差别
Oracle是多用户的, MYSQL是多数据库的
1. 遵循SQL标准
2. 不同厂商,不同的数据库产品,但是有自己的方言
3. 使用自己的方言,也能够完成相同的功能
4. Oracle安全级别要高,MYSQL开源免费
基本查询:
SQL : 结构化查询语言
请说一下SQL的分类以及每类常见的操作符都有哪些
四类:
DDL : 数据定义语言 create alter drop truncate
DML : 数据操纵语言 insert update delete
DCL : 数据控制语言 安全 授权 grant revoke
DQL : 数据查询语言 select from子句 where子句
查询语句的结构:
select [列名] [*] from 表名 [where 条件] [group by 分组条件] [having 过滤] [order by 排序]
注意
select 1+1; --在Oracle等于报错 ,在MYSQL中输出结果是2
dual : oracle中的虚表 ,伪表, 主要是用来补齐语法结构
select 1+1 from dual;
select * from dual;
select 1 from emp;
查询表中记录个数
select count(1) from emp;
1代表第一个字段，效率比*高。
select count(*) from emp;
别名
别名查询: 使用as 关键字, 可以省略
别名中不能有特殊字符或者关键字, 如果有就加双引号
select ename 姓名, sal 工资 from emp;
select ename "姓 名", sal 工资 from emp;
去除重复数据
distinct
多列去除重复: 每一列都一样才能够算作是重复
单列去除重复
select distinct job from emp;
多列去除重复的
select distinct job,deptno from emp;
查询中四则运算
select 1+1 from dual;
查询员工年薪 = 月薪* 12
select sal*12 from emp;
查询员工年薪+奖金
select sal*12 + comm from emp; --如果comm中的记录为null，结果不准确
nvl 函数 : 如果参数1为null 就返回参数2
select sal*12 + nvl(comm,0) from emp;
注意: null值 , 代表不确定的 不可预知的内容 , 不可以做四则运算
字符串拼接:
java : + 号拼接
Oracle 特有的连接符: || 拼接
在Oracle 中 ,双引号主要是别名的时候使用, 单引号是使用的值, 是字符
concat(str1,str2) 函数, 在mysql和Oracle中都有
查询员工姓名 : 姓名:SCOTT
select ename from emp;
使用拼接符
select '姓名:' || ename from emp;
使用函数拼接
select concat('姓名:',ename) from emp;条件查询 : [where后面的写法]
关系运算符: > >= = < <= != <>
逻辑运算符: and or not
其它运算符:
like 模糊查询
in(set) 在某个集合内
between..and.. 在某个区间内
is null 判断为空
is not null 判断不为空
查询每月能得到奖金的员工信息
select * from emp where comm is not null;
查询工资在1500--3000之间的员工信息
select * from emp where sal between 1500 and 3000;
select * from emp where sal >= 1500 and sal <= 3000;
查询名字在某个范围的员工信息 ('JONES','SCOTT','FORD')
select * from emp where ename in ('JONES','SCOTT','FORD');匹配单个字符
如果有特殊字符, 需要使用escape转义
模糊查询: like
% 匹配多个字符
_单个字符
查询员工姓名第三个字符是O的员工信息
select * from emp where ename like '__O%';
查询员工姓名中,包含%的员工信息
select * from emp where ename like '%\%%' escape '\';
select * from emp where ename like '%#%%' escape '#';
排序 : order by
升序: asc ascend
降序: desc descend
排序注意null问题 :nulls first | last
同时排列多列, 用逗号隔开
查询员工信息,按照奖金由高到低排序
select * from emp order by comm desc nulls last; --nulls last把值为空的放在后面
查询部门编号和按照工资 按照部门升序排序, 工资降序排序
select deptno, sal from emp order by deptno asc, sal desc;
函数
单行函数: 对某一行中的某个值进行处理
数值函数
字符函数
日期函数
转换函数
通用函数
多行函数: 对某一列的所有行进行处理
max()
min()
count()
sum()
avg()
统计员工工资总和
select sum(sal) from emp; --忽略空值
统计员工奖金总和 2200
select sum(comm) from emp;
统计员工人数
select count(1) from emp;
这里用1，也是为了方便，当然如果数据量较大的话，也可以提高速度，因为写count(*)的话会所有列扫描，这里用1的话或者用字段名的话，只扫描你写的那个列其实1就代表你这个查询的表里的第一个字段
统计员工的平均奖金
select avg(comm) from emp; --报错误 ，comm有空值
统计员工的平均奖金
select sum(comm)/count(1) from emp;
select ceil(sum(comm)/count(1)) from emp;
数值函数
select ceil(45.926) from dual; --46
select floor(45.926) from dual; --45
四舍五入
select round(45.926,2) from dual; --45.93
select round(45.926,1) from dual; -- 45.9
select round(45.926,0) from dual; --46
select round(45.926,-1) from dual; --50
select round(45.926,-2) from dual; --0
select round(65.926,-2) from dual; --100
截断
select trunc(45.926,2) from dual; --45.92
select trunc(45.926,1) from dual; -- 45.9
select trunc(45.926,0) from dual; --45
select trunc(45.926,-1) from dual; --40
select trunc(45.926,-2) from dual; --0
select trunc(65.926,-2) from dual; --0
求余
select mod(9,3) from dual; --0
select mod(9,4) from dual; --1
字符函数
substr(str1,起始索引,长度)
select substr('abcdefg',0,3) from dual; --abc
select substr('abcdefg',1,3) from dual; --abc
select substr('abcdefg',2,3) from dual; --bcd
注意: 起始索引不管写 0 还是 1 都是从第一个字符开始截取
获取字符串长度
select length('abcdefg') from dual;去除字符左右两边的空格
select trim(' hello ') from dual;替换字符串
Select replace('hello','l','a') from dual;
取整
select ceil(-12.5) from dual; -12
select floor(12.5) from dual; 12
日期函数
查询今天的日期
select sysdate from dual;
查询3个月后的今天的日期
select add_months(sysdate,3) from dual;
查询3天后的日期
select sysdate + 3 from dual;
查询员工入职的天数
select sysdate - hiredate from emp;
select ceil(sysdate - hiredate) from emp;
查询员工入职的周数
select (sysdate - hiredate)/7 from emp;
查询员工入职的月数
select months_between(sysdate,hiredate) from emp;
查询员工入职的年份
select months_between(sysdate,hiredate)/12 from emp;
转换函数
字符转数值
select 100+'10' from dual; --110 默认已经帮我们转换
select 100 + to_number('10') from dual; --110
数值转字符
select to_char(sal,'$9,999.99') from emp;
select to_char(sal,'L9,999.99') from emp;
to_char(1210.73, '9999.9') 返回 '1210.7'
to_char(1210.73, '9,999.99') 返回 '1,210.73'
to_char(1210.73, '$9,999.00') 返回 '$1,210.73'
to_char(21, '000099') 返回 '000021'
to_char(852,'xxxx') 返回' 354'
日期转字符
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
只想要年
select to_char(sysdate,'yyyy') from dual; --2017
只想要日
select to_char(sysdate,'d') from dual; --2 代表一个星期中第几天
select to_char(sysdate,'dd') from dual; --10 代表一个月中的第几天
select to_char(sysdate,'ddd') from dual; --100 代表一年中的第几天
当前星期
select to_char(sysdate,'day') from dual; --monday
select to_char(sysdate,'dy') from dual; --mon 星期的简写
字符转日期
select to_date('2017-04-10','yyyy-mm-dd') from dual;查询1981年 -- 1985年入职的员工信息
select * from emp where hiredate between to_date('1981','yyyy') and to_date('1985','yyyy');通用函数
nvl(参数1,参数2) 如果参数1 = null 就返回参数2
nvl2(参数1,参数2,参数3) 如果参数1 = null ,就返回参数3, 否则返回参数2
nullif(参数1,参数2) 如果参数1 = 参数2 那么就返回 null , 否则返回参数1
coalesce: 返回第一个不为null的值
案例
select nvl2(null,5,6) from dual; --6;
select nvl2(1,5,6) from dual; --5;
select nullif(5,6) from dual; --5
select nullif(6,6) from dual; --null
select coalesce(null,null,3,5,6) from dual; --3
笛卡尔积
两个表的乘积，但是实际开发中没什么意义，利用连接来消除笛卡儿积。
内联接
隐式内联接
等值内联接
select * from emp e1,dept d1 where e1.deptno = d1.deptno;
不等值内联接
select * from emp e1,dept d1 where e1.deptno <> d1.deptno;
自连接链接
查询员工编号员工姓名和此员工的经理的编号和姓名
select e1.empno,e1.ename,e1.mgr,m1.ename from emp e1,emp m1 where e1.mgr = m1.empno;
查询员工编号、员工姓名、部门名称、经理的编号姓名
select e1.empno,e1.ename,e1.mgr,d1 dname ,m1.ename from emp e1,dept d1 ,emp m1 ,where e1.mgr = m1.empno and e1.deptno = d1.deptno;
显式内联接
select * from 表1 inner join 表2 on 连接条件
查询员工编号员工姓名和此员工的经理的编号和姓名
select * from emp e1 inner join dept d1 on e1.deptno = d1.deptno;
外连接
左外连接（Mysql）
左表中所有记录显示出来，如果右表没有对应的记录为空
select * from emp e1 left outer join dept d1 on e1.deptno = d1.deptno;
右外连接（ Mysql）
右表中所有记录显示出来，如果左表没有对应的记录为空
select * from emp e1 rightouter join dept d1 on e1.deptno = d1.deptno;
Oracle中的外连接（+）
把所有的员工信息打印出来，如果没有对应的部门通过（+）方式添加空值
select * from emp e1,dept d1 where e1.deptno = d1.deptno(+);
实际上是如果dept没有对应的记录就加上空值
把所有的部门查询出来，如果没有对应的员工就加空值
select * from emp e1,dept d1 where e1.deptno(+) = d1.deptno;
子查询
查询语句中嵌套查询语句，用来解决类似：“查询最高工资的员工的信息”等复杂的查询语句。
查询最高工资的员工的信息：
1. 查询出最高工资 :5000
select max(sal) from emp;
2. 工资等于最高工资
select * from emp where sal = (select max(sal) from emp;);
单行子查询
可以使用> >= = <= <> !=等操作：
查询出比雇员7654的工资高同时和7788从事相同工作的员工信息
1. 雇员7654的工资：1250
select sal form emp where empno = 7654;
2. 7788从事的工作
select job from emp where empno = 7788;
3. 两个条件合并(错误，最高工资应该动态获取)
select * from emp where sal > 1250 and job = 'ANALYST';
3. 两个条件合并(正确，利用子查询)
select * from emp where sal > (select sal form emp where empno = 7654) and job = (select job from emp where empno = 7788);
查询每个部门最低工资的员工信息和他所在部门信息
查询每个部门最低工资
select deptno,min(sal) minsal from emp group by deptno;
2. 查询员工工资=部门最低工资的员工
- - 两个链接条件，首先是员工表的deptno = 部门表的deptno
- - 并且员工的工资 = 部门表此部门最低工资
select * from emp e1,(select deptno,min(sal) minsal from emp group by deptno) t1 where e1.deptno = t1.deptno and e1.sal = t1.minsal;
3. 查询员工所在部门相关信息
select * from emp e1,(select deptno,min(sal) minsal from emp group by deptno) t1 , dept d1 where e1.deptno = t1.deptno and e1.sal = t1.minsal and e1.deptno = d1.deptno;
多行子查询
in、not in、any、all、exists
查询领导信息
1. 查询所有经理的编号
select mgr from emp;
select distinct mgr from emp;
2. 结果
select * from emp where empno in (select mgr from emp);
查询不是领导的信息（错误）
select * from emp where empno not in (select mgr from emp);
上面的SQL是不正确的，因为子查询返回的结果集有null，官方文档表示无论如何都不要在子查询使用 not in，而not in（集合）就相当于<>all（集合）。万一集合中有空值就会报错，因为<>等判断是不能对null操作的。
正确的SQL
select * from emp where empno not in (select mgr from emp where mgr is not null);
exists（查询语句）
当查询语句有结果时候返回true，否则返回的是false，数据量比较大的时候非常高效。
查询无结果
select * from emp where exists(select * from emp where deptno = 123456); --- 123456不存在
查询有结果
select * from emp where exists(select * from emp where deptno = 20); --- 20不存在
查询有员工的部门信息
select * from dept d1 where exists(select * from emp e1 where e1.deptno = d1.deptno);
查询是一条一条查询的，首先找到需要操作的表dept，dept表的第一条数据的deptno为10，再去执行where条件，拿着deptno为10号的部门记录去emp表依次对比，emp表中如果有deptno为10的数据exists返回true，则把deptno为10的部门表记录打印出来，即此部门有员工信息。
rownum：伪列
系统自动生成的一列，实际上表示行号，默认其实在为1，再查询一条rownum加一。
查询员工表数据，加上行号的一列
select rownum,e1.* from emp e1;
下方代码查询不到任何记录
select rownum,e1.* from emp e1 where rowmnum >2;
查询rownum小于6的记录(可以查询到)
select rownum,e1.* from emp e1 where rowmnum < 6;
rownum不能做大于号判断，可以在小于号判断。
找到员工表中工资最高的前三名
（错误）
select rownum,e1.* from emp order by sal desc;
上方的代码查询出来的数据是根据sal进行排序的但是，rownum都是乱的，是因为先执行rownum再执行order by。SQL执行顺序为：from .. where .. group by..having ..select ...order by。
找到员工表中工资最高的前三名
（正确）
select e1.* from e1 emp order by sal desc;
--把上方查询道德看作一个表查询，rownum不会乱
select rownum,t1.* from(select e1.* from e1 emp order by sal desc) t1;
-- 只要头三条
select rownum,t1.* from(select e1.* from e1 emp order by sal desc) t1 where rownum <= 3;rowid：每行记录存放的真实的物理地址
