Oracle数据库 面试宝典

数据库
一、where与having
    WHERE用于行过滤。
    SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。
    
二、字符串与日期，数字之间相互转化
    到字符串：to_char()；到日期：to_date()；到数字：To_number(char,’format’)；
    
三、in与exsits区别
    In：跟在WHERE 子句后面规定多个筛选值（where 列名 in (值1,值2,值3)）；
        （确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候，首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选）
    Exsits：将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，这一行行可作为外查询的结果行，否则不能作为结果。
        （指定一个子查询，检测行的存在。遍历循环外表，然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。）
    IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。
    
四、like模糊查询
    通配符：%代替0到多个字符，_代替一个字符
    
五、nvl函数
    Nvl(e1,e2)：如果e1的值为null，返回e2，否则返回e1本身；
    Nvl2(e1,e2,e3)：如果e1的值为null，返回e3，否则返回e2；
    
六、排序oder by
    升序asc，降序desc，默认为升序；多列排序：oder by 列名,列名
    
七、快速建表，复制数据
1复制表中的数据到新建的表中，表不存在
    create table tableName1 as select * from tableName2 
2复制表中的数据
    insert into tableName select * from tableName  
    
八、大数据量

九、提高查询效率
    1优化SQL查询，去掉不必要的列；2建立查询条件索引；
    
十、UNION, UNION ALL
    UNION 操作符用于合并两个或多个 SELECT 语句的结果集；UNION 内部的 SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型，每条 SELECT 语句中的列的顺序必须相同。
    UNION 操作符选取不同的值，UNION ALL可以选取重复的数值；
    
十一、常用函数
    Dual，伪表；
    CASCADE，级联删除；
    ||，连接字符串；
    SUBSTR (char, n, len)，取子字符串，选取n位开始，长度为len的子串；
    LENGTH(char)，找出一个字符串的长度；
    INSTR (char, substr)，查找子串位置，
    LPAD | RPAD (str,len,padstr)，返回字符串str，左/右填充至len长度，长度大于len则缩减；
    TRIM()，移除掉一个字串中的字头或字尾，最常见的用途是移除字首或字尾的空白；
    REPLACE (char, str1, str2)，字符串替换，将子串str1替换为str2；
    LOWER(char)，变为小写；
    UPPER(char)，变为大写
    INITCAP(char)，首字母变为大写；
    Floor()，向下取整；
    Ceil()，向上取整；
    Round()，四舍五入；
    
十二、日期相关的函数
    SYSDATE，返回当前日期；
    ADD_MONTHS(date,n)函数可以得到date之前或之后n个月的新日期；
    LAST_DAY(date)，返回当前月的最后一天；
    MONTHS_BETWEEN (date1, date2)，用于计算date1和date2之间有几个月；
    NEXT_DAY(date,week),返回下周某一天的日期；
    TO_CHAR(date,’day’)，返回日期date是周几；
例子：
返回两个日期之间的天数：
    select floor(sysdate - to_date('20161018','yyyymmdd')) from dual;
    
十三、Case when与DECODE
    Case函数一旦满足了某一个WHEN ，则这一条数据就会退出CASE WHEN，而不再考虑其他CASE；
    decode（列名，值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值）；
    
十四、数据库三范式
    1、所有字段值都是不可分解的原子值；
    2、一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中；
    3、每一列数据都和主键直接相关，而不能间接相关；
    
十五、左连接、右连接、内连接
    内连接：INNER JOIN返回两个表中联结字段相等的行；
    左连接：LEFT JOIN返回包括左表中的所有记录和右表中联结字段相等的记录；
    右连接：RIGHT JOIN返回包括右表中的所有记录和左表中联结字段相等的记录；
    
十六、having与聚合函数
    GROUP BY 语句可结合一些聚合函数来使用，HAVING语句在GROUP BY语句后筛选数据。
        SELECT column_name, aggregate_function(column_name)
        FROM table_name
        WHERE column_name operator value
        GROUP BY column_name;
常用的聚合函数
    AVG()：返回数值列的平均值；
        SELECT AVG(column_name) FROM table_name
    COUNT()：返回匹配指定条件的行数；
        SELECT COUNT(column_name) FROM table_name;
    FIRST()/LAST() ：返回指定的列中第一个/最后一个记录的值；
        SELECT FIRST/LAST (column_name) FROM table_name;
    MAX()/MIN() ：返回指定列的最大值/最小值；
        SELECT MIN/MAX(column_name) FROM table_name;
    SUM() ：返回数值列的总数；
        SELECT SUM(column_name) FROM table_name;
        
十七、视图
    视图是基于 SQL 语句的结果集的可视化的表；视图中的字段就是来自一个或多个数据库中的真实的表中的字段。
    CREATE VIEW view_name AS
    SELECT column_name(s)
    FROM table_name
    WHERE condition

十八、存储过程
    存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。
    --1、一组为了完成特定功能的SQL语句集
    --2、第一次编译后再次调用不需要再次编译（高效）
    --3、不同的数据库存储过程定义的方式不同，思路是相同的，语法不同
    CREATE OR REPLACE Procedure myProcedure(Name Varchar2)
Is
--定义各种变量
    ename2 Varchar2(32);
    job2 Varchar2(32);
    Begin
--写业务逻辑处理
    Select ename,job Into ename2,job2   From emp Where ename = Name;
    dbms_output.put_line(ename2);
    End myProcedure;
    
十九、触发器
    触发器对表进行插入、更新、删除的时候会自动执行的特殊存储过程。触发器可以分为两类：DML触发器和DDL触发器，DDL触发器它们会影响多种数据定义语言语句而激发，这些语句有create、alter、drop语句，DML触发器分为：after触发器（之后触发），insert触发器，update触发器，delete触发器。
    
二十、约束类型
    NOT NULL（非空）、
    UNIQUE（唯一）、
    主键、
    外键、
    CHECK
    
二十一、序列
    序列是一种数据库对象，用来自动产生一组唯一的序号；
    CREATE SEQUENCE sequencename
     [INCREMENT BY n]                 定义序列增长步长，省略为1
     [START WITH m]                   序列起始值，省略为1，一经创建不能修改
     [{MAXVALUE n | NOMAXVALUE}]     序列最大值，
     [{MINVALUE n | NOMINVALUE}]     序列最小值
     [{CYCLE | NOCYCLE}]             到达最大值或最小值后，继续产生序列（默认NOCYCLE）
     [{CACHE n | NOCACHE}];          CACHE默认是20

二十一、理解rownum
    rownum用于标记结果集中结果顺序的一个字段，它的特点是按顺序标记，而且是连续的，换句话说就是只有有rownum=1的记录，才可能有rownum=2的记录。rownum关键字只能和<或者<=直接关联，如果是>或者=则需要给他起个别名。
    
二十二、oracle分页
    select * from (
        select * from (
            select s.*,rownum rn from student s 
        ) where rn<=5)
     where rn>0
     
二十三、Oracle数据库体系
    1 Oracle数据库的基本类型：char，varchar2，number，date，clob；
    2 Oracle的物理结构：数据文件，控制文件和日志文件；
    3 Oracle的逻辑结构：
        表空间--最大的逻辑存储结构，与物理上的数据文件相对应；
        段--数据库终端用户将处理的最小单位；
        区--磁盘空间分配的最小单位；
        块--管理存储空间的最基本单位；
            
必背的sql语句
1：oracle 分页
    select * from (select t.*, rownum rn from (select * from menu order by id desc) t where          rownum < 10) where rn >=5
2: mysql 分页
    select * from music where id limit 5,5
3：oracle中如何快速将一张表的数据复制到另外一张表中(另外一张表不存在，另外一张       表存在，但数据为空)

1、.不存在另一张表时：
    create  table  新表  as  select * from 将要复制的表
2、存在另一张表时：
    insert  into  新表名  select  字段  from  将要复制的表名
    
4：音乐专辑
查询出special <app:ds:special>表中的id  专辑名 并下面有多少首歌曲
    Select  s.id , min(s.sname),count(m.mid)  from  special s  inner
    join  ms  m  on  s.id=m.id  group  by  s.id
5：快速删除一张表（不可事物回滚，也就是没有日志记录）
    TRUNCATE from 表名
6：inner join
    select 查找信息 from 表名 1 inner join 表名2 on 表名1.列名 = 表名2.列名
7：left join
    左外连接    select 查找信息 from 表名1 left join 表名2 on 表名1.列名 = 表名2.列名
8：right join
    右外连接  select 查找信息 from 表名1 right join 表名2 on 表名1.列名 = 表名2.列名
9：oracle中查询遍历树形结构（start  with）
    select * from extmenu
    start with pid=1
    connect by prior id = pid
快速删除父节点以及父节点下的所有节点：
    Delete from extmenu where id in (
    elect * from extmenu
    start with pid=1
    connect by prior id = pid
    )
10：查询出来60-70,80-90,95-100学生的信息
    select * from stu where chengji between 60 and 70 or between 80 and 90 or between 95 and 100
    select * from stu where chengji > 60 and chengji < 70 or chengji > 80 and chengji < 90 or chengji > 95 and chengji < 100
11：用exists替换in------进行联表查询
    select * from dept where exists(select * from emp where emp.deptno=dept.deptno);
或
    select  *  from  dept  d  inner  join  emp  e  on  d.deptno = e.deptno(只查询出两表共同拥有的字段数据)
12：删除表中的重复数据：
    delete from xin a where a.rowid != (
    select max(b.rowid) from xin b
    where a.name = b.name
    );
13：row_number()，rank() over ，dense_rank() over 按工资排序
    select sal,
    row_number() over(order by sal desc) rank1,
    rank() over(order by sal desc) rank,
    dense_rank() over(order by sal desc) drank
    from emp
14：select * from (select emp.* from(
    dense_rank() over(partition by departNo order by sal desc)
    rk from emp )
    Where rk=4