【安博培训技术】Oracle2 基本SQL查询20130909

基本SQL查询


回顾
Oracle 中的 SYSTEM 用户和 SYS 用户具有管理权限，而 SCOTT 用户只有基本的权限 
SQL 是通用的数据库语言
SQL 命令可分为数据定义语言、数据操纵语言、事务控制语言和数据控制语言
Oracle 支持的数据类型包括字符、数值、日期时间、RAW 和 LOB 等
伪列：ROWID、ROWNUM
数据完整性：实体完整性、域完整性、引用完整性、自定义完整性


目标
掌握并熟练应用简单查询(单表)
掌握并熟练应用联接查询(多表)
内联接
交叉联接
外联接
掌握并熟练应用分组聚合语法
理解并熟练应用子查询语法
单值
多值
多行多列


SQL（Structured Query Language）即为结构化查询语言，它的主要功能就是同各种数据库建立联系，进行沟通。数据库的框架，是数据库的数据结构形式.


SQL的组成部分
DDL(数据定义语言)：维护(定义、修改、删除)SQL模式对象.
DML (数据操纵语言) ：包括数据查询和数据更新(增、删、改).
DCL (数据控制语言) ：对数据的访问控制(授予权限、取消权限).
TCL (事务控制语言) ：对事务的控制(提交、回滚、保存点)


简单查询(单表)
1.对于多个不同的条件，可在WHERE子句中使用关键字OR，AND，和NOT逻辑地联接起来 
2.目标列表和WHERE子句里可以使用算术操作 
注：在关键字 AS 后面的double是第二个列的新名字。 这个技巧可以用于目标列表里的每个元素， 给它们赋予一个在结果列中显示的新的标题。 这个新的标题通常称为别名。这个别名不能在该查询的其他地方使用。 
selete f_pname,f_price from t_part 
where f_name = 'Bolt' and (f_price = 20 or f_price <=15);
select f_pname, f_double =f_price*2 from t_part 
where f_price*2 < =25;
几种特殊用法
选择无重复的行
在SELECT子句，使用DISTINCT关键字
SQL> SELECT DISTINCT f_city FROM t_supplier;
使用列别名及表别名
为列表达式提供不同的名称
该别名指定了列标题
SQL> SELECT f_id, f_name, f_department as dep
     FROM t_student;
SQL> SELECT stu.f_name, cour.f_name, f_grade
FROM t_student stu,t_course cour,t_sells
WHERE stu.f_id=f_stuid and cour.f_id=f_courseid;
利用现有的表创建表
语法：
          CREATE TABLE <new_table_name> AS
          SELECT column_names FROM <old_table_name>;
SQL> CREATE TABLE t_stubak
      AS SELECT * FROM t_student;
SQL> CREATE TABLE t_stuclass1 
      AS SELECT * FROM t_student
           where f_class = 1;
SQL> CREATE TABLE t_stu 
      AS SELECT * FROM t_student
      WHERE 1 = 2;
联接查询-普通联接
普通联接(联合)的计算方法是这样的：首先算出笛卡儿积t_supplier×t_part×t_sells，然后选出那些满足WHERE子句里给出的条件的记录（也就是说，公共命名属性的值必须相等），最后我们映射出除f_date,f_pname,f_price,f_sname,f_city这些属性。 
另外，关于别名运算符AS，我们要注意以下两点：
>对于长度较短的表名以及不会产生混淆的字段名尽量不用别名，因为别名总是没有原名更具有表述性。
>别名尽量简洁和富于表述，这样我们在使用时能更好的识别它。 
select f_date,f_pname,f_price,f_sname,f_city
from t_sells SE,t_supplier SU,t_part PA
where SE.f_sid=SU.f_sid and SE.f_pid=PA.f_pid;
除了上面的这种方式，我们实现普通联接还可以采用下面这样的SQL JOIN语法


select f_date,f_pname,f_price,f_sname,f_city
from t_sells
join t_supplier on (t_sells.f_sid=t_supplier.f_sid)
join t_part on (t_sells.f_pid=t_part.f_pid);
无条件联接(cross join) 
一个交叉联接(CROSS JOIN)接收两个分别有N行和M行的表T1和T2，然后返回一个包含交叉乘积 N×M 条记录的联接表；显然交叉联接生成的是表的笛卡尔积。
     CROSS JOIN实际上就是一个INNER JOIN ON TRUE
我们想知道零件和供应商有多少可能的组合
select * from t_part cross join t_supplier;
当然我们也可以不使用join
select * from t_part,t_supplier;
条件联接(join)-[ inner ] join
[说明] 当创建内联接时，包含NULL的列不与任何值匹配，因此不包括在结果集内。空值不与其它的空值匹配。 
如果出现需要空值匹配的情况，需要使用外连接来实现
内联接仅显示两个联接表中的匹配行的联接，是默认联接类型，其效果与普通联接相同，即T1 JOIN T2相当于T1 INNER JOIN T2。
例如我们可以联接t_part表和t_sells表以创建显示零件销售情况的结果集，在内联接中，结果集内不包含没有销售记录的零件，也不包含没有零件名的销售记录。
select f_id,f_date,f_pname 
from t_part inner join t_sells on t_part.f_pid=t_sells.f_pid;
对于左外连接(LEFT [OUTER] JOIN)：首先，执行一次INNER JOIN；然后，如果T1里有一行对任何 T2 的行都不满足联接条件，那么返回一个联接行，该行的 T2 的字段为空(null)。我们可以简单的理解为：LEFT [OUTER] JOIN联接成的表无条件地包含T1(JOIN左边的表)里的所有行。
select f_sname,f_city,f_id,f_date 
from t_supplier left join t_sells on t_supplier.f_sid=t_sells.f_sid;
对于右外连接(RIGHT [OUTER] JOIN)：首先，执行一次INNER JOIN；然后，如果T2里有一行对任何 T1 的行都不满足联接条件，那么返回一个联接行，该行的 T1 的字段为空(null)。我们可以简单的理解为：RIGHT [OUTER] JOIN联接成的表无条件地包含T2(JOIN右边的表)里的所有行。
select f_sname,f_city,f_id,f_date 
from t_sells right join t_supplier on t_sells.f_sid=t_supplier.f_sid;
对于完整外连接(FULL [OUTER] JOIN)：首先，执行一次INNER JOIN；然后，如果T1里有一行对任何 T2 的行都不满足联接条件，那么返回一个联接行，该行的 T2 的字段为空(null)；再然后，如果T2里有一行对任何 T1 的行都不满足联接条件，那么返回一个联接行，该行的 T1 的字段为空(null)。我们可以简单的理解为：FULL [OUTER] JOIN 相当于LEFT [OUTER] JOIN和RIGHT [OUTER] JOIN查询的结果集再通过UNION(不是UNION ALL)连接在一起。


Join小结
所有类型的JOIN都可以链接在一起或者嵌套在一起，这时T1和T2都可以是联接生成的表。我们可以使用圆括弧控制JOIN的顺序，如果我们不主动控制，那么联接顺序是从左到右。
当组合多个表的数据时，必须决定把什么表放在FROM子句，把什么表放在ON子句中。比如，我们要查询各个供应商及零件的销售情况，这时销售表(t_sells)处于零件表(t_part)和供应商表(t_supplier)之间的位置，因此我们将销售表(t_sells)放在FROM子句，以销售表(t_sells)为基础，建立其与零件表(t_part)和供应商表(t_supplier)之间的联系，这样结构更加清晰一些。


分组聚合
SQL中提供的聚合函数：
    Min(最小值)、Max(最大值)、Sum(字段求和)、Count(记录数)、Avg(平均值)
select avg(f_price) as avg_price from t_part;
select count(f_pid) as count_part from t_part;
分组聚合：
    上面聚合函数是对于所有记录进行聚合的，有时候我们需要先对记录分组再进行聚合
    对记录的分组是通过关键字GROUP BY实现的，如GROUP BY F1,F2,F3，则当且仅当两条记录在所有属性F1,F2,F3上达成一致，它们才是同一组的
查询每个供应商销售零件数量，并按供应商编号排序
select t_sells.f_sid,f_sname,sum(f_count) as part_count 
from t_sells,t_supplier
where t_sells.f_sid=t_supplier.f_sid
Group by t_sells.f_sid,f_sname
Order by t_sells.f_sid asc;
Having子句：
    HAVING子句主要用于聚集查询中，在GROUP之后过滤掉那些不符合条件的组
    与where的区别？
select t_supplier.f_sid,t_supplier.f_sname,count(t_sells.f_pid) as sell_count
from t_sells,t_supplier
where t_sells.f_sid=t_supplier.f_sid
group by t_supplier.f_sid,t_supplier.f_sname
having count(t_sells.f_pid)>1
order by t_supplier.f_sid;
子查询
普通子查询：
子查询亦称子选择，它和普通查询一样，都产生一个结果集/值，当我们需要一个中间结果集/值时，我们就需要使用子查询。
返回单值的子查询通常用在WHERE和HAVING子句里
select * from t_part
where f_price>(select f_price from t_part where f_pname='Screw');
[思路]对于一个查询语句的返回结果而言，可分为三类：单值、多值、多行多列，据此我们分别讲述子查询的三种用法：普通子查询、 exists子句和in子句、from子句子查询
[说明]先从例子入手，引导学员认识子查询
单行操作符，对应单行子查询，多行操作符，对应多行子查询
EXISTS子查询：
    形式为where [not] exists (子查询)
统计哪些不销售任何零件的供应商 （比如说，我们想把这些供应商从数据库中删除）
select * from t_supplier
where not exists
  (select * from t_sells where t_sells.f_sid=t_supplier.f_sid);
注意：对于本例而言，我们在WHERE子句的内层SELECT里使用了来自外层SELECT的t_supplier.f_sid。 正如前面所说的，子查询为每个外层查询计算一次，也就是说，t_sells.f_sid的值总是从外层SELECT的实际记录中(t_supplier.f_sid)取得的。
[NOT] IN子查询：
这种情况适用于子查询中返回多个值时的情况
    形式为where fieldname [not] in (子查询)
同上例，统计哪些不销售任何零件的供应商，我们也可以用IN子查询
select * from t_supplier
where f_id not in
  (select distinct f_id from t_sells);
另一个例子：查询出所在城市为Lindon、Rome和Jinan的供应商的名称
select f_sname from t_supplier where f_city in(‘Lindon’,’Rome’,’Jinan’);
在FROM里面的子查询：
当子查询返回一个结果集时，那么它就相当于一个普通的表，因此，在FROM子句我们同样可以使用子查询。这个特性很有用，FROM里的子查询还可以让我们获得多于一个回合的分组/聚集特性，而不需要求助于临时表。这种嵌套可以使用多次，大大提高了查询的功能。
查询出所有供应商中的最大的销售MAX(SUM(F_COUNT))?
select max(product_count) as avgPrice
from (select sum(f_count) as product_count
         from t_sells
         group by f_sid) subtable;


总结
掌握并熟练应用简单查询(单表)
掌握并熟练应用联接查询(多表)
内联接
交叉联接
外联接
掌握并熟练应用分组聚合语法
理解并熟练应用子查询语法
单值
多值
多行多列