Oracle 学习随笔（二）

Oracle 的表操作

1 修改表（test）：
（1）添加字列：
alter table 表名 add 新增列名 数据类型；
（2）修改字段属性：
alter table 表名 modify 列名 新数据类型；
（3）删除字列：
alter table 表名 drop column 列名;
（4）更改字列名
alter table 表名 rename column 列名 to 新列名;
（5）把一个表的字段批量插入另一个表：（字段要对应）
insert into test select id,name from person;
insert into test（id，name） select id,name from person;

2.基本语句
（1）添加字列：
insert into() values();
（2）查询信息：
select * from 表名；
（3）删除字列：
delect from 表名 [ where 条件]；
（4）修改信息
update 表名 set column=values where 条件；

3 特殊需求语句：
（1）在名字显示前加上‘姓名：’（字符串连接符 ||）
select ‘姓名：’ || t_name 姓名 from test;
（2）一列同时显示编号和名字：（当别名中有空格或者其他字符是要双引）
select id ||’:’|| t_name as “编号：姓名” from test;
（3）日期作为条件书写格式（显示2020/6/1）
select * from emp where hiredate=‘1-6月-2020’;
–需求2：查询10号和20号以及没有部门的员工部门的员工；
SELECT * FROM emp WHERE deptno IN (10,20) OR deptno IS NULL;
–需求3：查询不是10号和20号以及没有部门的员工部门的员工
SELECT * FROM emp WHERE deptno NOT IN (10,20) AND deptno IS NOT NULL;
（4）Order by排序（分别不使用别名、使用别名、使用列号来排序：列号是你所查询的字段对应的列号）
SELECT ename,sal12 FROM emp ORDER BY sal 12 ASC;
SELECT ename 姓名,sal12 年薪 FROM emp ORDER BY 年薪 ASC;
SELECT ename 姓名,sal12 年薪 FROM emp ORDER BY 2 ASC;
nulls last的使用（把空值放到最后）
SELECT ename,sal*12 FROM emp ORDER BY sal *12 DESC NULLS LAST;
（5）计算平均工资时，如何把工资为空的也平均到内（正常聚合函数会自动过滤空值）
SELECT AVG(nvl(sal,0)) FROM emp;
（6）去重的两种方式：distinct和group by（group by效率更高，但是只能显示去重的字段）
select count(distinct(deptno)) from emp
select deptno from emp group by deptno
（7）any和all操作符（子查询多个返回值的操作）
查询工资比30号部门任意一个员工的工资高的员工信息
SELECT * FROM emp WHERE sal >ANY(SELECT sal FROM emp WHERE deptno=30);
查询工资比30号部门所有员工的工资高的员工信息
SELECT * FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=30);

4 分组子句的要求（group by）
在SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中，既查询字段都要在group by 后面。
反之，包含在 GROUP BY 子句中的列不必包含在SELECT 列表中

5 Where和having的选择
（1）having是在有聚合函数时进行条件筛选
（2）Having必须和group by 配合使用不可以单独使用
（3）Having是先扫面全部在进行筛选，效率低，where是先筛选在扫描部分字段，效率高

6 where—>group by—>having

7 为什么要使用表的别名
（1）使用别名可以简化查询。
（2）使用表名前缀可以提高执行效率。–SQL性能优化方案
（3）在不同表中具有相同列名的列，可以用表的别名作为前缀来加以区分。

8 表链接的几种方式：
笛卡尔集：select * from emp,dept
笛卡尔集产生的条件：
省略连接条件
连接条件无效

9 Oracle的多表关联的类型分为：
内连接、外连接、自连接。
（1）内连接分为：等值内连接、不等值内连接
（2）外连接分为：左外连接、右外连接、全外连接
全外连接两种方式：
（一）SELECT * FROM emp t1 LEFT JOIN dept t2 on t1.deptno=t2.deptno
UNION
SELECT * FROM emp t1 RIGHT JOIN dept t2 ON t1.deptno=t2.deptno;
（二）SELECT * FROM emp t1 FULL JOIN dept t2 ON t1.deptno=t2.deptno;

（3）自连接：就是将一张表当成两张表来查询
–查询员工信息，要求同时显示员工和员工的领导的姓名
SELECT * FROM emp t1,emp t2 WHERE t1.mgr=t2.empno;
（4）子查询注意事项
关于格式：子查询要包含在括号内，最好有合理的书写风格。
子查询的位置：可以放在主查询的where、select、having、from的后面。不可以放在主查询的group by后面。
子查询和主查询可以是同一张表，也可以不是是不同一张表，只要子查询返回的结果在主查询中能使用即可。
关于使用操作符：单行操作符对应单行子查询，多行操作符对应多行子查询。
执行顺序：一般子查询先执行，再执行主查询；
关于排序：一般不在子查询中使用order by；但在top-N分析问题中，必须在子查询中使用order by。

10 伪列
（1）概念：
伪列是在oracle中的一个虚拟的列。
列的数据是由ORACLE进行维护和管理的，用户不能对这个列修改，只能查看。
所有的伪列要得到值必须要显式的指定。
最常用的两个伪列：rownum和rowid。
（2）rownum（行号）：
是在查询操作时由ORACLE为每一行记录自动生成的一个编号。
每一次查询ROWNUM都会重新生成。（查询的结果中Oracle给你增加的一个编号，根据结果来重新生成）
（3）rownum永远按照默认的顺序生成。（不受order by的影响）
rownum只能使用< <=，不能使用> >=符号，原因是：Oracle是基于行的数据库，行号永远是从1开始，即必须有第一行，才有第二行。
（4）ROWID（记录编号）：
是表的伪列，是用来唯一标识表中的一条记录，并且间接给出了表行的物理位置，定位表行最快的方式。
主键:标识唯一的一条业务数据的标识。主键是业务给用户用的。不是给数据库用的。
记录编号rowid：标识唯一的一条数据的。主要是给数据库用的。类似UUID。
ROWID的产生：使用insert语句插入数据时，oracle会自动生成rowid并将其值与表数据一起存放到表行中。这与rownum有很大不同，rownum不是表中原本的数据，只是在查询的时候才生成的。

11 分页：
Mysql中分页关键字是limit；
Oracle中分页依靠rownum：
（1）首先子查询获取最大行数
（2）利用子查询生成的表，这时候的r是e表中的一个字段，可以用> 、>=符合，进行分页
select e.* from (select rownum r,emp.* from emp where rownum<=3) e where r>1;