知识点

1.介绍以及安装
2.Oracle常用的SQL(数据类型，基本的SQL，常用的函数，多表关联等)

介绍以及安装

介绍

1.数据库的作用：

可以持久化存储数据，可以为数据与数据之间建立关联关系，方便数据的统计。

2.Oracle数据库

1.关系型的数据库，支持SQL(结构化查询语言)语言。
2.Oracle是一个大型的收费数据库。
3.历史:8 9 9i 10 10i 11g 12g
4.Oracle数据库的体系结构：
Oracle的服务(Oracle Server)=数据库+实例
数据库：有行和列组成的二维模型对应表的集合
实例：将数据库对应二进制文件读取到内存中形成的内容称为实例

实例和硬盘文件：1:1(n:1)

安装

略..

Oracle常用的SQL（数据类型，基本的SQL，常用的函数，多表关联等）

1.SQL简介

SQL(Structured Query Language，结构化查询语言)支持如下命令：
数据定义语言：CREATE(创建)、ALEERT(更改)、TRUNCATE(截断)、DROP(删除)命令。
数据操纵语言：INSERT(插入)、SELECT(选择)、DELETE(删除)、UPDATE(修改)命令。
事务控制语言：COMMIT(提交)、SAVEPOINT(保存点)、ROLLBACK(回滚)命令。
数据控制语言：GRANT(授予)、REVOKE(回收)命令。

2.Oracle常用数据类型

1.字符型：

varchar2(n)：变长字符串，n代表允许的最大字节长度，最大4000字节
char(n):同上，定长字符串，最大2000字节，长度不够会填充半角空格，查询效率搞，用于(手机号、身份证号等确定长度的)

2.数字类型：

number:存储整型或浮点型，最大38位精度 number(n):仅存整数，n代表最大位数number(4)取值范围：-9999~9999
number(p,s):存浮点类型，P代表最大精度(小数位精度和整数精度和的精度)，s代表小数位：price number(5,2) 123.11，123.10，12.1234，1234.5（不可以）

3.日期：

date：存储年月日时分秒，精确到秒
timestamp(n)：时间戳，精确到纳秒，很少用

4：

blob：大二进制数据，最大4G，可以存储图片，视频，音乐等等。
clob：大字符串数据，最大4G，默认数据在4000字节内，存储在表段空间中，超过4000字节会用LOB段存储，查询效率低。

3.Oracle中常用的操作符

1.算术操作符：

算术表达式又NUMBER数据类型的列名，数值常量和连接它们的算数操作符组成(+-*/)

SELECT EMPNO,(SAL+NVL(COMM,0))*12 FROM EMP;
NVL在这里可以防止把COMM中的NULL数据转换位0。

2.比较操作符：

用于比较两个表达式的值。
=,!=,<,>,<=,>=,BETWEEN AND(检查是否在两个值之间)
[NOT] IN(与列表中的值匹配)

select * from emp where empno in(7369,7577,7566,null);
select * from emp where empno not in(7369,7577,7566,null);

[NOT] LIKE(匹配字符模式，*_通配符)

select * FROM emp where ename like '%L%';
select * FROM EMP where EMPNO like '!_k%' ESCAPE '!';

[NOT] IS NULL(检查是否为空)

select * from emp where comm is null;

select * from emp where comm is NOT null;
select * from emp where NOT comm is  null;

3.逻辑运算符

用于组合生成一个真或假的结果。AND OR NOT

SELECT * FROM EMP WHERE SAL>1000 AND JOB='SALESMAN'

4.集合操作符

集合操作符将两个查询的结果组合成一个结果集合。
UNION(联合)：返回两个查询选定不重复的行。(删除重复的行)
UNION(联合所有)：合并两个查询选定的所有行，包括重复的行。
INTERSECT(交集)：只返回两个查询都有的行。
MINUS(减集)：在第一个查询结果中排除第二个查询结果中出现的行。(第一——第二)
使用集合操作符连接起来的SELECT语句中的列应遵循以下规则：
1.通过集合操作连接的各个查询相同列数，匹配列的数据类型；
2.这种查询不应含有LONG类型的列；
3.列标题来自第一个SELECT语句。

select aid, anme from tab1
MINUS
select tid, tname  from tab2;

连接操作符(||)，例：A||B。
用于将两个或者多个字符串合并一个字符串，或者将一个字符串与一个数值合并在一起。

select empno||'--姓名：'||ename eidname from emp;

4.Oracle常用函数

dual：同义词:

sys.dual=dual,主要用于测试，可以查询任何内容，但是只有一条结果。
函数接受一个或者多个参数并返回一个值。

单行函数

也称标量函数，对于从表中查询的每一行，该函数都返回一个值。单行函数出现在SELECT/WHERE子句中。

1.日期函数：

对日期进行运算，根据硬土产生日期/数值类型的结果。
1、ADD_MONTHS(d,n)：返回指定日期加上月数后的日期值。

SELECT ADD_MONTHS(SYSDATE, 2)FROM dual;
当前时间两个月后

SELECT EMPNO,HIREDATE,ADD_MONTHS(HIREDATE, 2) FROM EMP;

2、MONTH_BETWEEN(d1,d2)：返回两个日期间的月数:

SELECT MONTHS_BETWEEN(SYSDATE+365, SYSDATE-365) FROM dual;
因为一年可能366也可能365

3、LAST_DAY(d)：返回指定日期当前月的最后一天的日期值

SELECT LAST_DAY(SYSDATE) FROM dual;

4、ROUND(d.[fmt])：返回指定日期四舍五入格式(UEAR,MONTH,DAY)后的日期值

SELECT ROUND(MONTHS_BETWEEN(SYSDATE+365, SYSDATE-365), 1)FROM dual;

5、NEXT_DAY(d,day)：返回指定当前周的第几天(1(周日)-7(周六))
最好比今天的日期大，不能小于或者等于，否则显示的是下一周的日期。

SELECT NEXT_DAY(SYSDATE, 3) FROM dual;

SELECT NEXT_DAY(SYSDATE, 1) FROM dual;

6、TRUNC(D,[FMT])：返回指定日期阶段为格式后的日期值

SELECT TRUNC(SYSDATE, 'month') FROM DUAL;

7、EXTRACT(FMY FROM D)：返回指定日期提取的格式的值

SELECT EXTRACT(YEAR FROM DATE '2007-04-01') FROM dual;

2.字符函数

字符函数接收字符输入，并返回字符或数值。
1、INITCAP(char)：首字母大写
2、LOWER(char)：转换为小写 toLowerCase(“sdSSDF”)--->sdssdf
3、UPPER(char)：转换为大写
4、LTRIM(char, set)：左裁切 ltrim()
5、RTRIM(char, set)：右裁切 rtrim()
6、TRANSLATE(char, 字符A, 字符B)：将字符A逐个替换为字符B

select TRANSLATE('----ffff-fgfg','-','_') from dual;

7、REPLACE(char, search_str, replace_str)：字符串替换

select REPLACE('----ffff-fgfg','ff','jj') from dual;

8、INSTR(char, substr[,pos])：查找子串位置

select INSTR('----ffff-fgfg','f',6) from dual;
从第六个开始查找，得到的结果是下标

9、SUBSTR(char, pos, len)：取子字符串substr
10、CONCAT(char1, char2)：连接字符串

SELECT CONCAT(EMPNO, ENAME) FROM EMP;

11、CHR(ascii)：根据 ASCII 码返回对应字符串
12、LPAD / RPAD 左 / 右填充
13、LPAD ('function', 15 , '=') ：返回 '=======function'
14、TRIM：开头或结尾(或开头和结尾)裁剪特定的字符，默认裁剪空格。
TRIM ([LEADING | TRAILING] trim_char) 16、LENGTH(char)返回字符串的长度

select empno, length(ename) from emp;

15、DECODE：逐个值进行字符串替换
DECODE (expr, search1, result1, search2, result2, [ ,default])
等价于mysql中的

select tid,(case sex when '0' then '男' when '1' then '女' end) sex from tt;

3、数字函数

数字函数接受数字输入并返回数值作为输出结果。
ABS(n) 取绝对值
CEIL(n) 向上取值
FLOOR(n) 向下取整
SIN(n) 正弦值
COS(n) 余弦值
POWER(m, n) 指数函数
SQRT(n) 平方根
MOD(m, n) 取余
ROUND(m, n) 小数点后精度四舍五入
TRUNC(m, n) 小数点后精度截断

4、转换函数

转换函数将一种数据类型转换为另一种数据类型。
TO_CHAR (d|n, [,fmt]) 格式化日期 / 数值

SELECT TO_CHAR(SYSDATE,'mm/dd/yyyy hh:mi:ss') FROM dual;

TO_DATE (char [,fmt]) 将 fmt模型格式的字符串转换为日期型

SELECT TO_DATE('01/01/2004', 'mm/dd/yyyy') FROM DUAL;

TO_NUMBER (char) 将包含数字的的字符串转换为数值型

SELECT TO_NUMBER('1') FROM DUAL;

5、其他函数

NVL (exp, exp2) 如果 exp 为空返回 exp2；如果非空返回 exp
NVL2 (exp, exp2, exp3) 如果 exp 为空返回 exp3；如果非空返回 exp2

SELECT NVL2(comm, comm, 0)+sal FROM emp;

NULLIF (exp1, exp2) 比较两表达式，相等返回空值，不等则返回 exp1

SELECT NULLIF(COMM, 300)FROM EMP;

6、分组函数 / 聚合函数

分组函数基于一组行返回结果，即为每一组行返回单个值。

AVG (columname) 返回指定列的平均值
MAX (columname) 返回指定列的最大值
MIN (columname) 返回指定列的最小值
SUM (columname) 返回指定列的总值

COUNT
COUNT (*) 统计所有行个数，包括重复行和空值得行
COUNT (columname) 统计指定列非空值的个行数
COUNT (DISTINCR columname) 统计指定列中非重复，非空值得行个数

GROUP BY 子句
用于将信息表划分为组，对查询结果按组进行聚合运算，为每组返回一个结果。
HAVING 子句
用来指定 GROUP BY 子句的检索条件。

7.rownum 位列

根据查询的结果集生成的一组从1开始的连续的数据，只能用来查询前n条数据；

SELECT ROWNUM rm,e.* FROM emp e WHERE ROWNUM<=10;

从结果集中再查询:

select * from (select rownum rm,e.* from emp e where rownum<=20) tmp where tmp.rm>=10;

8.分组查询

group by(查询结果分组统计)和having子句(限制分组显示结果)
1、显示每个部门的平均工资和最高工资：

SELECT AVG(sal),max(sal),deptno FROM emp group by deptno;

2、显示每个部门的每种岗位的平均工资和最低工资：

SELECT AVG(sal),min(sal),deptno,job from emp GROUP BY deptno,job;

3、显示平均工资低于2000的部门号和他的平均工资：

SELECT AVG(SAL),MAX(SAL),DEPTNO FROM EMP GROUP BY DEPTNO HAVING AVG(SAL)>2000;

总结：
1)、分组函数只能出现再选择列表，having，order by子句中；
2)、如果再select语句中同时包含group by，having，order by，那么执行顺序是group by，having，order by。
3)、在选择列中如果有列、表达式和分组函数，那么这些列和表达式必须有一个出现子group by 子句中，否则就会出错。

9.子查询

1、子查询：嵌入在其他sql语句中的SELECT查询。
单行子查询：返回一行数据的子查询语句。显示与SMITH同一部门的所有员工。

--从右往左执行
Select * from emp where deptno=(select deptno from emp where ename='SMITH');

多行子查询：返回多行数据的子查询。查询和部门10的工作相同的雇员的名字，岗位，工资和部门号。

SELECT * FROM EMP WHERE JOB IN(SELECT DISTINCT JOB FROM EMP WHERE DEPTNO=10);

多行查询中使用all操作符。显示工资比部门20的所有员工的工资高的员工的姓名，工资和部门号。

SELECT ENAME,SAL,DEPTNO FROM EMP WHERE SAL>ALL(SELECT SAL FROM EMP WHERE DEPTNO=30);

在多行查询相中使用any操作符。显示工资比部门30的任意的一个员工的工资高的员工的姓名，工资和部门号。

SELECT ename,sal,deptno FROM emp WHERE sal>any(SELECT sal FROM emp WHERE deptno=30);

多列子查询。查询与smith的部门和岗位完全相同的所有雇员。

SELECT deptno,job FROM emp WHERE ename='SMITH';
SELECT * FROM emp WHERE (deptno,job)=(SELECT deptno,job FROM emp WHERE ename='SMITH');

显示高于自己部门平均工资的员工的信息：
1)、查询各部门的平均工资:

SELECT DEPTNO,AVG(SAL) MYSAL FROM EMP GROUP BY DEPTNO;

2)、将上面的查询看作是一张子表:

SELECT
	a2.ename,
	a2.sal,
	a2.deptno 
FROM
	emp a2,
	( SELECT DEPTNO, AVG( SAL ) MYSAL FROM EMP GROUP BY DEPTNO ) a1 
WHERE
	a2.DEPTNO = a1.DEPTNO 
	AND a2.SAL > a1.MYSAL;

在from中使用子查询的时候，该子查询会被当做成一个视图对待，称为内嵌视图。在from中使用子查询时必须制定别名。

10.多表联合查询

1.内联接：

1.隐式内联接：
等值内联接：where e1.deptno=d1.deptno;
不等值内联接：where e1.deptno<>d1.deptno;
自联接：字节联接自己；显示内联接：select * from 表1 inner join 表2 on 连接条件。其中inner关键字可以省略。
-查询员工编号，员工姓名，经理的编号，经理的姓名

SELECT
	e1.EMPNO,
	e1.ENAME,
	d1.dname,
	e1.MGR,
	m1.ENAME 
FROM
	emp e1,
	EMP m1,
	DEPT d1 
WHERE
	e1.MGR = m1.EMPNO 
	AND e1.DEPTNO = d1.DEPTNO;

-查询员工编号，员工姓名，员工的部门名称，经理的编号，经理的姓名：

SELECT
	e1.EMPNO,
	e1.ENAME,
	d1.dname,
	e1.MGR,
	m1.ENAME,
	d2.dname 
FROM
	EMP e1,
	EMP m1,
	DEPT d1,
	DEPT d2 
WHERE
	e1.MGR = m1.EMPNO 
	AND e1.DEPTNO = d1.DEPTNO 
	AND m1.DEPTNO = d2.DEPTNO;

2.外连接

1、左外连接：left outer join

连接效果：左侧的表中的数据都会被显示出来，但是右侧表的数据，只有和左侧匹配上的字段才会被查询出来！否则否是显示Null。
SQL语法格式：
语法1：

select * 
        from 表1 left[outer] join 表2
        on 表1.字段1=表2.字段1;

语法2：

select * 
       from 表1,表2
       where 表1.字段1=表2.字段1(+);

案例：
第一种：

SELECT
	tm.*,
	d.DNAME 
FROM
	DEPT d
	LEFT JOIN ( SELECT avg( sal ) salavg, max( sal ) maxsal, deptno FROM EMP GROUP BY DEPTNO ) tm ON d.DEPTNO = tm.DEPTNO;

第二种：

SELECT
	tm.*,
	d.DNAME 
FROM
	DEPT d,
	( SELECT avg( sal ) salavg, max( sal ) maxsal, deptno FROM EMP GROUP BY DEPTNO ) tm 
WHERE
	d.DEPTNO = tm.DEPTNO ( + );

2.右外连接：right outer join

连接效果：右侧的表中的全部数据都会被显示出来，但是左侧表的数据，只有和右侧匹配的字段才会被查询出来！否则都会显示Null
SQL语法格式：

语法1：
select *
     from 表1  right [outer] join 表2
     on 表1.字段1=表2.字段1;

语法2：
select *
     from 表1,表2
     where 表1.字段1(+)=表2.字段1;

案例：
第一种：

SELECT
	tm.*,
	d.DNAME 
FROM
	DEPT d
	RIGHT JOIN ( SELECT avg( sal ) salavg, max( sal ) maxsal, deptno FROM EMP GROUP BY DEPTNO ) tm ON d.DEPTNO = tm.DEPTNO;

第二种：

SELECT
	tm.*,
	d.dname 
FROM
	dept d,
	( SELECT avg( sal ) salavg, max( sal ) maxsal, deptno FROM emp GROUP BY deptno ) tm 
WHERE
	d.deptno ( + ) = tm.deptno;

3.全外连接：full/all outer join

SQL语法格式：

select * 
    from 表1 full[outer] join 表2
    on 表1.字段1=表2.字段1;