Oracle数据库（关系型数据库2）

一.多表查询

1.概念：    （1）用户操作的功能所涉及到的数据往往不在一张表中    （2）通过join（连接）可将多张表连接起来查询数据    （3）通常通过表的主键和外键进行表之间的连接2.表的join方式（1-4都是针对与两张表，而5针对于一张表）    （1）等价连接（=）    （2）不等价连接(<、between and、like等)    （3）外连接（包括左外连接"left outer join"和右外连接"right outer join"）标准SQL中    （4）内连接(join)和全连接(full outer join)(全连接在oracle定义中没有，只有在标准SQL中有，标准SQL定义的语言标准适用于oracle)    （5）自连接    （6）集合运算符union、minus、union all、intersect3.笛卡尔积的避免：    笛卡尔积：以一张表为中心表，中心表的每一行都要与另一张表的每一行一一对应，将数据全部输出。    数据冗余太大，一般选用连接查询。4.采用连接查询：    select table.column,table.column         from table1,table2            where table1.column1=table2.column2;    （1）通常采用主键和外键进行表的连接    （2）有重复的列名一定要通过表名或者别名分隔，建议重不重复都要加上表名或者别名，以大幅度提高性能。    （3）可以为表名提供别名，操作更加方便。5.等价连接    （1）两个表间的连接条件是通过“=”建立        select e.last_name,e.dept_id,d.id,d.name            from s_emp e,s_dept d                where e.dept_id=d.id;    (2)别名（有大小写区分时用双引号括起来）        1.列的别名在select语句中定义，表的别名在from子句中定义。        2.别名只在该语句中有用。        3.一旦给表创建了别名，标识列表时只能通过表的别名，而不能通过表的真名。6.不等价连接    两个表间的连接条件不是通过“=”号建立，可以是“<”、“between and”、以及“like”等，总之不是“=”号；    select e.ename,e.job,e.sal,e.grade         from emp e, salgrade s         where e.sal between s.losal and s.hisal;7.外连接(未匹配的记录查询出来)(oracle中特有的)    (1)左外连接（+只针对oracle数据库）    左外连接等同于在"="右边加"+",将"="左边表中未匹配的记录也查找出来。    select table.column,table.column        from table1,table2            where table1.column=table2.column(+);    (2)右外连接（oracle）    右外连接等同于在"="左边加"+",将"="右边表中未匹配的记录也查找出来。    select table.column,table.column        from table1,table2            where table1.column(+)=table2.column;    (3)oracle中外连接用"+"的方式只能出现在表达式的一边。标准SQL中没有支持。    (4)包含外连接的条件不允许        1.使用in操作符        2.使用or操作符连接到另外一个条件8.外连接（标准SQL中）    （1）左外连接（表名 left outer join 表名 on 条件）    select table.column,table.column        from table1 left outer join table2            on table1.column=table2.column;    （2）右外连接（表名 right outer join 表名 on 条件）    select table.column,table.column        from table1 right outer join table2            on table1.column=table2.column;9.内连接（无法查找出未匹配的记录，与等价连接功能实现相同）    内连接：inner（可不写inner） join    select table.column,table.column        from table1 join table2            on table1.column=table2.column;10.全连接（包含未匹配记录）    全连接:full outer join    select table.column,table.column        from table1 full outer join table2            on table1.column=table2.column;11.自连接(两个相同的表，那么也就可以使用左右外连接、等价连接等两表之间的连接方式)    select worker.last_name||'works for'||mansger.last_name        from s_emp worker,s_emp manager            where worker.manager_id=manager.id;12.集合运算符    （1）union：在进行表连接后会筛选掉重复的记录，所以在表连接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。    select * from A union select * from B；    （2）union all：将两个结果合并后就返回且不进行排序。这样，如果返回的两个结果集有重复的数据，那么返回的结果集就会包含重复的数据。     select * from A union all select * from B；    （3）minus：第一个结果集减去第二个结果集中的记录与第一个结果集重复的记录，剩余的是第一个结果集里边去掉和第二个结果集重复的数据。    select * from A minus select * from B；    （4）intersect：返回两个结果集的交集（返回两个查询中都出现的记录）    select * from A intersect select * from B；13.    （1）rowid：表中记录在内存中的地址（包含对象号、文件号和块号等）。        （1）rowid_object(rowid):对象号        （2）rowid_relative_fno(rowid):文件号        （3）rowid_block_number(rowid):块号        （4）rowid_row_number(rowid):行号    （2）rownum（行号）：在查询到的结果集后加上去的，它总是从1开始        对于oracle的rownum，不支持>、>=、=、between...and(语法不会出现错误，但使用后查询不到任何记录)，只能用<、<=、！=或=1这些符号。

二.组函数

1.组函数（多行函数）简介：作用于行集的组函数给每组一个结果（将若干行分成几个组）。2.组函数出现于select的列和having子句中，不能出现在where子句中。3.在select声明的group by子句将行细分为更小的组，having子句用来限制结果集。4.组函数使用语法：    （1）select column，group_function            from  table                [where condition]                [group by group_by_expression]                [having group_condition]                [order by cplumn];5.常见组函数介绍：    （1）avg（distinct|all|n）:求列的平均值    （2）count（distinct|all|expr|*）：统计行（记录数）    （3）max（distinct|all|expr）：求列的最大值    （4）min（distinct|all|expr）：求列的最小值    （5）sum（distinct|all|n）：求列的总和    （6）variance（distinct|all|n）：离散差    （7）stddev（distinct|all|n）：标准差    distinct：唯一值（不包括重复值）    all：所有值（包括重复值），不包括空值(默认)    n：代表所操作的列为数值类型    expr：代表所操作的列可为任意数据类型    示例：        select avg(all salary),max(salary),min(salary),sum(salary)            from s_emp where upper(title) like "SALES%";        select min(last_name),max(distinct last_name) from s_emp;        //count（）中*和列的区别：列值为空将不能查询出来，*都会查询出来        select count(*) from s_emp where dept_id=31;        select count(commission_pct) from s_emp where dept_id=31;6.组函数归纳总结：    （1）count（*）：统计行（包括空行）    （2）count（col）：不计算空的列值，其他组函数都不对空值进行计算    （3）只能操作数值类型列：AVG（）和SUM（）    （4）能操作字符串、日期以及数值类型列：count（）、max（）和min（）

三.GROUP BY

1.使用原因：为了分组    select column,group_function        from table             [where condition]            [group by group_by_expression]            [order by column];    （1）group by后面只能跟列名，不能跟别名。        select credit_rating,count(*) "#Cust"            from s_customer                group by credit_rating;    （2）group by里边的列不是组函数中的列，group by 后面可以跟多列。group by子句中若出现多列时按照多列联合唯一进行分组，即列不能重复，查询出来的结果不重复。        select dept_id,title,count(*)             from s_customer                group by dept_id,title;    （3）group by子句中的列不是必须出现select中，若果出现在select中更有意义。        select count(*)             from s_emp                 group by dept_id;    （4）若果在select中有组函数和列，列需要通过group by进行分组，否则语法无法通过        select dept_id,count(*)             from s_emp                 group by dept_id;

四.HAVING

1.having子句中现在group by之后,它是给group by分组的情况加上限定条件，当限定条件里有组函数的时候，要使用having子句。2.where子句里可以使用任何限定条件，只是不可以带组函数。3.执行的先后次序：    先执行where子句进行过滤，在进行分组，接着执行组函数，然后执行having子句对分组结果进行限定，最后是order by子句。语法： select column,gropu_function         from table            [where condition]            [group by group_by_expression]            [having group_condition]            [order by column];4.示例：

5.group by和having子句的使用注意点：    （1）若分组列的值为null，照样进行分组。    （2）在select语句中可以不使用组函数而直接进行分组。    （3）使用了having子句后，必须使用group by 子句。    （4）若果要对组函数的结果进行限制，那么必须使用group by子句，并且也要使用having子句。    （5）如果having子句后面跟的不是组函数而是列，那么group by子句必须根据这个列进行分组。

五.子查询

1.子查询的概念：    （1）子查询就是嵌套查询。    （2）语法：        select select_list             from table                 where expr operator                 (select select_list                    from table);    （3）子查询是一个完整的select语句，可以拥有group by、having子句，可以使用组函数，可以从多个表查询结果。    （4）子查询外围的语句可以是select语句，也可以是其他语句（如create table、insert等语句）。    （5）根据返回的结果是单个值还是多个值可以分为：        单行子查询：使用单行比较符"="(只有一个结果)        多行子查询：使用多行比较符"in"（有多个结果）    （6）子查询出现的位置：        where子句        having子句        from子句2.子查询的使用：    （1）单行子查询的示例1：        select title,last_name            from (select last_name,title                    from s_emp                    where title='Stock Clerk')            where last_name='Smith';            等同于：            select title,last_name                from s_emp                    where title='Stock Clerk' and last_name='Smith';    （2）单行子查询示例2：        select last_name,title,dept_id            from s_emp                where dept_id=(select dept_id                                 from s_emp                                where last_name='biri');    （3）单行子查询示例3：    select dept_id,avg(salary)        from s_emp            group by dept_id                having avg(salary)>                    (select avg(salary)                        from s_emp                            where dept_id=32);    （4）多行子查询示例1：        select last_name,first_name,title            from s_emp                where dept_id in (                    select id                        from s_dep                            where name='Finance'                                or region_id=2                );    (5)面试子查询问题：        1.从s_emp表中查询3-5行的内容：（必须使用别名）        select r,id,last_name            from (select rownum r,s.*                from s_emp s)            where r between 3 and 5;        2.从s_emp表中查询第3行的内容：        select r,id,last_name            from (select rownum r,s.*                from s_emp s)            where r=3;        3.从s_emp表中找出薪水最高的三名员工：        select rownum,last_name,salary            from (select last_name,salary                    from s_emp                     order by salary desc)            where rownum<=3;        4.从s_emp表中找出那些工资高于他们所在部门的平均工资的员工：                select last_name,dept_id,allary                    from s_emp a                    where salary>(select avg(salary)                                from s_emp                                where dept_id=a.dept_id);