关系型数据库基础及应用（三）——SQL查询（以Oracle为主）

SQL(基础查询)

基本查询语句

from子句

SELECT * FROM DUAL;

· SELECT用于指定要查询的列；FROM指定要从那个表中查询；

· 如果查询所有字节用*；如果只查询特定的，可以直接在SELECT后面指定列名，用逗号隔开；

使用别名

· 在SQL语句中可以通过使用列的别名改变标题的显示样式，或者表示计算结果的含义；

· 使用语法是列的别名跟在列名后，中间可以加或者不加一个“AS”关键字；

· 如果希望别名中区分大小写字符，或者别名中包含字符或空格，则必须使用双引号引起来；

SELECT empno AS id,ename "Name",sal*12 "Salary" FROM emp;

where子句

--查询部门10下的员工信息
SELECT * FROM emp WHERE deptno=10;

· 在SELECT语句中，可以在WHERE子句中使用比较操作符限制查询结果；

· 如果和数字比较，可以使用单引号引起，也可以不用；

· 如果和字符及日期类型的数据比较，则必须用单引号；

查询条件（常用的）

使用>、<、>=、<=、!=、<>、=等条件加入到where语句中查询

--查询职员表中薪水低于2000元的职员信息
SELECT ename,sal FROM emp where sal < 2000;

使用LIKE条件（模糊查询）

SELECT ename,job FROM emp WHERE ename LIKE'_A%';

· 比较操作符LIKE用来做模糊查询；

· 当用户在执行查询时，不能完全确定某些信息的查询条件，或者只知道信息的一部分，可以借助LIKE来实现

· LIKE需要借助两个通配符；这两个通配符可以配合使用，构造灵活匹配条件；

  %：表示0到多个字符；

  _：表示单个字符

使用IN和NOT IN

--查询职位是MANAGER或者CLERK的员工
SELECT ename,job FROM emp WHERE job IN('MANAGER','CLERK');

· 比较操作符IN(list)用来取出符合列表范围中的数据；

· list表示值列表，当列或表达式匹配与列表中的任何一个值时，条件为TRUE,该条记录则被显示出来；

· IN也可以理解为一个范围比较操作符，只不过这个范围是一个指定的值列表；

· NOT IN(list)取出不符合此列表中的数据记录

BETWEEN...AND...

--查询薪水在1500-3000之间的职员信息
SELECT ename,sal FROM emp WHERE sal BETWEEN 1500 AND 3000;

使用IS NULL和IS NOT NULL

· 空值NULL是一个特殊的值，比较的时候不能使用“=”号，必须使用IS NULL，否则不能得到正确的结果；

使用ANY和ALL条件

SELECT empno,ename,sal FROM emp WHERE sal>ANY(3500,4000,4500);

· ALL和ANY不能单独使用，需要配合单行比较操作符>、>=、<、<=一起使用；

  >ANY：大于最小；<ANY：小于最大

  >ALL：大于最大；<ALL：小于最小

查询条件中使用表达式和函数

· 当查询需要对选出的字段进行进一步计算，可以在数字列上使用算术表达式（+、-、*、/）；

· 表达式符合四则运算的默认优先级，如果要改变优先级可以使用括号；

· 算术运算主要是针对数字类型的数据，对日期类型的数据可以做加减操作，表示在一个日期值上加或减一个天数；

使用DISTINCT过滤重复

--查询员工部门编码，去掉重复值
SELECT DISTINCT deptno FROM emp；

排序

使用ORDER BY子句

SELECT ename,deptno FROM emp ORDER BY deptno;

· 必须出现在SELECT中最后一个子句；

ASC和DESC

--不写ASC或DESC,默认是ASC,升序排列
SELECT ename,mgr from emp where deptno=10 ORDER BY mgr;
--降序排列，必须指明
SELECT ename,sal FROM emp ORDER BY sal DESC;

· ASC用来指定升序排列（默认选项），DESC用来指定降序排序；

· NULL值视作最大，则升序排列时，排在最后，降序排列时，排在最前面；

多个列排序

--对职员表中的职员排序，先按照部门编码正序排列，再按照薪水降序排列
SELECT ename,deptno,sal FROM emp ORDER BY deptno ASC ,sal DESC;

· 当以多列作为排序标准时，首先按照第一列进行排序，如果第一列数据相同，再以第二列进行排序，以此类推；

· 多列排序时，不管正序还是倒序，每个列需要单独设置排序方式；

聚合函数

什么是聚合函数

· 将表的全部数据划分为几组数据，每组数据统一一个结果；

· 因为是多行数据参与运算返回一行结果，也称作分组函数、多行函数、集合函数；

MAX和MIN

· 用来获取列或表达式的最大、最小值；

· 可以用来统计任何数据类型，包括数字、字符和日期；

AVG和SUM

· 用来统计列或表达式的平均值和和值；

· 只能操作数字类型；忽略NULL值；

COUNT

· 用来计算表中的记录数；忽略NULL值；

分组查询

GROUP BY子句

select * from emp group by deptno  oeder by deptno;

HAVING子句

select * from emp group by deptno HAVING SAL>400 oeder by deptno;

· HAVING子句用来对分组后的结果进一步限制，比如按部门分组后，得到每个部门的最高薪水，可以继续限制输出结果

· 必须跟在GROUP BY后面，不能单独使用；

查询语句执行顺序

· 查询语句的执行顺序依下列子句次序：

1、from子句：执行顺序为从后往前，从右到左；

    - 数据量较少的表尽量放在后面；

2、where子句：执行顺序自上而下，从右到做；

    - 将能过滤掉最大数量记录的条件写在where子句的最右；

3、group by--执行顺序从左往右分组；

    - 最好在GROUP BY前使用WHERE 将不需要的记录在GROUP BY之前过滤掉；

4、having子句：消耗资源；

    - 尽量避免使用，HAVING会在检索出所有记录之后才对结果集进行过滤，需要排序等操作；

5、select子句：少用*号，尽量取字段名称；

    - ORACLE在解析的过程中，通过查询数据字典将*号依次转换成所有的列名，消耗时间；

6、order by子句：执行顺序为从左到右排序，消耗资源；

7、where -> group by -> having -> order by

SQL（关联查询）

关联基础

关联的概念

select table1.cloumn as t1,table2.cloumn as t2 from table1,table2 where t1=t2;

· 这种查询两个或两个以上数据表或试图的查询叫做连接查询；

· 连接查询通常建立在存在相互关系的父子表之间；

笛卡尔积

select count(*) from emp;--14条
select count(*) from dept;--4条
select emp.ename,dept.dname from emp,dept;--56条

· 笛卡尔积指做关联操作的每一个的每一行和其他表的每一行做组合，假设两个表的记录数分别是X和Y，笛卡尔积将返回X*Y条记录；

· 关联查询时不指定连接条件就会出现笛卡尔积，实际开发要避免这种情况，通常笛卡尔积是没有意义的；

等值连接

· 连接查询中最常见的一种，通常是在有主外键关联关系的表间建立，并将连接条件设定为有关系的列，使用“=”连接相关的表；

select e.ename,e.job,d.dname,d.loc from emp e,dept d where e.deptno = d.deptno

关联查询

1、内连接：返回满足条件的数据

select e.ename,e.job,d.dname,d.loc from emp e,dept d where e.deptno = d.deptno

select e.ename,e.job,d.dname,d.loc from emp e JOIN dept d ON(e.deptno = d.deptno);

2、外连接

· 内连接返回满足连接条件的数据记录；

· 有些情况下，需要返回那些不满足连接条件的记录，需要使用外连接；

· 外联接不仅返回满足连接条件的记录，还将返回不满足连接条件的记录；

· 驱动表概念；

select table1.column,table2.column from
table LEFT|RIGHT|FULL [OUTER] JOIN table2 ON table1.column = table2.column;

（1）左外链接：以左边的表作为驱动表,即emp为驱动表

select e.ename,d.dname from emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno;

（2）右外连接：以右边的表作为驱动表，即以dept作为驱动表

select e.ename,d.dname from emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno;

3、全外连接

· 全外连接是指，除了返回满足连接条件的记录，还会返回不满足连接条件的所有其他行；

· 是左外连接和右外连接查询结果的总和；

select e.ename,d.dname from emp e FULL OUTER JOIN dept d ON e.deptno = d.deptno;

4、自连接

· 自连接是一种特殊的连接查询，数据的来源是一个表，即关联关系来自与表单中的多个列；

· 表中的列表参照同一个表中的其他列的情况称作自参照表；

· 自连接是通过将表用别名虚拟成两个表的方式实现，可以是等值或不等值连接；

· 举例：

--查询每个职员的经理名字，以及他们的职员编号
select worker.empno w_empno,worker.ename w_ename,manager.empno m_empno,maneger.ename m_ename 
From emp worker join emp manager ON worker.mgr = manager.empno