target
掌握select的基本使用（包括别名、去重）
掌握数据排序（升序和降序）以及排序时null值的处理
掌握where关键字的基本使用
掌握模糊查询、分组查询等
掌握子查询的使用
掌握连接查询
1. 查询数据必备select
1.1 select语法
SELECT语句是日常使用最多的语句，它以SELECT开头。其中最主要的部分就是 SELECT和FROM关键字，这两项是査询当中必需的部分，其他子句可以根据实际需求进行变动。SELECT语句的主要语法结构如下：
SELECT
[DISTINCT|ALL]
select_list
FROM table_list
[where_clause]
(group_by_clause]
[HAVING condition]
[order_by_clause]
1.2 获取指定字段的数据
获取表中指定字段的数据，就是指定表中的某几个字段（列），然后利用SELECT语句得 到指定字段的数据，多个字段之间使用逗号分隔。
🌰：要求査询表emp中empno,ename,job,sal的数据
select empno,ename,job,sal from emp;
1.3 获取所有字段的数据
使用“*”通配符可以查出所有字段的数据：
select * from emp;
注意：
虽然使用星号查询数据比较方便，但是不建议这么使用，原因如下：
- 查询明确的列在执行效率上比使用星号要高。
- 只返回必要的列的数据可以减少网络消耗。
- 如果使用星号返回所有的列的数据，当在表中增加新的字段时，有可能引起程序的异常。
1.4 使用别名替代表中的字段名
表中的字段名称通常都是英文的，这会给英文不好的客户査看数据带来不便。SELECT语句中的列名允许我们指定别名，指定别名可以利用AS关键字。
select empno as 员工编号,ename as 员工姓名,job as 工作,sal as 薪水 from emp;
如果不使用AS而是直接在査询的列名后面加上空格 ，然后输入别名也可以达到同样的效果。
select empno 员工编号,ename 员工姓名,job 工作,sal 薪水 from emp;
1.5 使用表达式操作査询的字段
可以针对某个列（字段）使用表达式，这样査询出来的结果就是修改后的数据，但是数据 库里的数据不会被修改。
🌰：查询员工的年薪
select ename 员工姓名,sal 月薪,sal*12 年薪 from emp;
1.6 去除检索数据中的重复记录
当査询数据时有可能遇到重复的记录，这给统计有效数据造成了一定的影响，利用 DISTINCT关键字可以去除重复的数据。
🌰：根据种类（category）去重
SQL> select * from productInfo; --查询所有
PRODUCTID PRODUCTNAME PRODUCTPRICE CATEGORY
---------- -------------------- ------------ ----------
1 华为Mate40Pro 7999 手机
2 华为P40Pro 6999 手机
3 华为荣耀10 1999 手机
4 华为MatePadPro 4999 平板
5 华为智慧屏 8999 电视
6 华为智慧屏2代 9999 电视
7 华为智慧屏2代 9999 电视
8 华为智慧屏2代 10999 电视
已选择8行。
SQL> select category 产品类型 from productInfo; --查询所有种类
产品类型
----------
手机
手机
手机
平板
电视
电视
电视
电视
已选择8行。
SQL> select distinct category 产品类型 from productInfo; --根据种类去重
产品类型
----------
手机
电视
平板
注意：
DISTINCT后面如果是 多个列名，那么DISTINCT把这些列名看成一个整体，来去除重复记录。
SQL> select distinct category,productName from productInfo;
CATEGORY PRODUCTNAME
---------- --------------------
手机 华为荣耀10
电视 华为智慧屏2代
手机 华为Mate40Pro
平板 华为MatePadPro
电视 华为智慧屏
手机 华为P40Pro
已选择6行。
2. 检索出来的数据排序
查询出来海量的数据后，可能需要根据某个字段进行排序，排序分为升序（asc）和降序（desc）。
默认排序方式是升序（asc）。
排序使用order by子句。
2.1 使用升序降序处理数据
(1) 升序
🌰：查询productInfo表，根据productPrice升序排列
SQL> select * from productInfo order by productPrice;
PRODUCTID PRODUCTNAME PRODUCTPRICE CATEGORY
---------- -------------------- ------------ ----------
3 华为荣耀10 1999 手机
4 华为MatePadPro 4999 平板
2 华为P40Pro 6999 手机
1 华为Mate40Pro 7999 手机
5 华为智慧屏 8999 电视
6 华为智慧屏2代 9999 电视
7 华为智慧屏2代 9999 电视
8 华为智慧屏2代 10999 电视
已选择8行。
等价于：
select * from productInfo order by productPrice asc;
(2) 降序
🌰：查询productInfo表，根据productPrice降序排列
SQL> select * from productInfo order by productPrice desc;
PRODUCTID PRODUCTNAME PRODUCTPRICE CATEGORY
---------- -------------------- ------------ ----------
8 华为智慧屏2代 10999 电视
7 华为智慧屏2代 9999 电视
6 华为智慧屏2代 9999 电视
5 华为智慧屏 8999 电视
1 华为Mate40Pro 7999 手机
2 华为P40Pro 6999 手机
4 华为MatePadPro 4999 平板
3 华为荣耀10 1999 手机
已选择8行。
2.2 排序时对NULL值的处理
NULL值在排序过程中是个比较特殊的值类型，默认情况下排序时把它看成最大值。也就是说，当排序的记录中出现NULL值时，默认情况下，升序排列时它在最后，降序排列时它在首位。
其实NULL值在排序时，具体在前还是在后开发人员是可以指定的。
(1) 降序时NULL值在末位
SELECT productname, productPrice FROM productinfo
ORDER BY productPrice desc NULLS LAST;
(2) 升序时NULL值在首位
SELECT productname, productPrice FROM productinfo ORDER BY productPrice NULLS FIRST;
2.3 使用别名作为排序字段
使用列的别名作为排序字段和使用该列作为排序字段效果是一样的。开发人员可根据自己的习惯进行选择，但在被排序的字段有别名的情况下建议使用别名作为排序字段，这样更易于阅读。
select productName 产品名称,productPrice 产品价格 from productInfo
order by 产品价格 desc nulls last;
2.4 使用表达式作为排序字段
🌰：利用产品价格和产品数量的乘积作为排序字段。脚本如下：
SELECT productname,productprice,quantity, productprice*quantity FROM productinfo
ORDER BY productprice*quantity ASC;
注意：NULL值和其他值相乘还是NULL。
2.5 使用字段的位置作为排序字段
排序时允许使用査询列表中字段的位置来作为排序字段，这么做一是为了方便，二是为了防止使用UNION时出现错误。
🌰：査询表PRODUCTINFO中产品名称、产品价格，并利用产品价格所在位置排序。
select productName,productPrice from productInfo
order by 2 desc;
2.6 使用多个字段排序
利用多个字段进行排序，最后査询出来的数据是综合排序后的数据。多个字段排序的操作过程如下：
1）按照第一个字段进行排序。
2）在此基础上按照第二个字段排序。也就是说，当第一个字段的数据相同时，才对这些数据以第二个字段排序。
3）如果还有后面的排序字段，那么这个过程会不断重复，而可以排序的数据范围，都是前面一个字段中重复的数据。
🌰：要求査询PRODUCTINFO表中的产品名称、产品类型编码、产品数量，并利用后面两个字段排序。
select productName,category,quantity from productInfo
order by category asc,quantity desc;
3. 使用WHERE子句设置检索条件
SELECT...FROM是一个基本的査询语句，它会返回所有的值，但这通常不是我们想要的，我们希望检索出来的数据是满足某个甚至某些条件的，而利用WHERE子句可以达到我们的目的。
WHERE子句就像一个筛选器，它对FROM子句返回的结果进行筛选，每条记录都会按照条件进行判断，如果符合条件，则该记录作为査询结果的一部分，如果不符合条件则不会返回。
WHERE条件子句中可以使用的操作符主要有：
① 关系操作符包括：<、<=、>、>=、=、!=、<>（不等于）
② 比较操作符包括：
IS NULL：如果操作数为NULL返回TRUE。
LIKE：模糊比较字符串值。
BETWEEN...AND...：验证值是否在范围之内。
IN：验证操作数在设定的一系列值中。
③ 逻辑操作符包括：
- AND：两个条件都必须得到满足。
- OR：只要满足两个条件中其中的一个.
- NOT：与某个逻辑值取反。
简单的WHERE条件语句一般只有一个限制条件，但是如果单一的限制条件不能满足我们 的业务需求时，开发人员可以使用多个限制条件査询数据，多个限制条件之间可以使用逻辑操 作符相连接。
3.1 査询中使用单一条件限制
🌰：检索表productInfo中产品名称、产品价格、产品数量，列出的记录要求产品数量大于 20,并根据产品数量升序排列
SELECT productName,productPrice, quantity from productinfo
where quantity > 20 order by quantity;
🌰：检索表productInfo中产品名称、产品价格、产品种类，列出的记录要求”产品种类“不为手机
SQL> select productName,productPrice,category from productinfo where category <> '手机';
PRODUCTNAME PRODUCTPRICE CATEGORY
-------------------- ------------ ----------
华为MatePadPro 4999 平板
华为智慧屏 8999 电视
华为智慧屏2代 9999 电视
华为智慧屏2代 9999 电视
华为智慧屏2代 电视
3.2 查询中使用多个条件限制
査询条件中除了单一的条件也可以设置多个条件，但是这些条件需要使用逻辑操作符连接起来。例如，使用AND表示多个条件需要同时满足，其中有一个条件不能满足，那么该记录 就不会返回到査询结果中。而OR表示多个条件中符合其中一个就能返回到査询结果中。
🌰：检索表productinfo中产品ID、产品名称、产品价格、产品数量。返回记录要求产 品价格”在1000 - 7000之间，并且包含这两端价格。
select productId,productName,productPrice,quantity from productinfo
where productPrice >= 1000 and productPrice <=7000;
还可以使用 between...and..实现：
select productId,productName,productPrice,quantity from productinfo
where productPrice between 1000 and 7000;
🌰：检索表PRODUCTINFO中产品ID、产品名称、产品价格、产品数量。返回记录要求产 品价格”低于1000或高于3000,不包含这两个价格本身。
select productId,productName,productPrice,quantity from productinfo
where productPrice < 1000 or productPrice > 3000;
3.3 模糊査询数据
当并不能确切地了解査询条件，而是只了解査询条件中的一部分时，或者想检索出包含特定字符的数据时，可以利用模糊査询。
使用模糊査询的关键字是LIKE,它和两个通配符一起使用，才能实现模糊査询的功能。 用这两个通配符可以替代模糊的部分，它们分别是：
- _：可以替代一个字符。
- %：可以替代多个字符。
🌰：要求检索出"产品名称”字段中包含“智慧”两个字的产品及其价格。
select productName,productPrice from productinfo where productName like '%智慧%';
3.4 查询条件限制在某个列表范围之内
某种情况下，要求査询条件从给定的值中选取，这时就可以利用IN关键字来实现这个功能。 它的语法是in(list),其中list是值列表。
🌰：要求利用in关键字，检索出产品编码为”1“和”3“的产品名 称和产品价格。
SQL> SELECT productId,productname,productprice FROM productinfo where productId IN('1','3');
PRODUCTID PRODUCTNAME PRODUCTPRICE
---------- -------------------- ------------
1 华为Mate40Pro 7999
3 华为荣耀10 1999
如果in前面加一个not关键字，检索出来的数据将和案例相反。
3.5 专门针对NULL值的查询
数据库中的数据不会是完美的，更多的时候，由于种种原因，会存在垃圾数据和NULL数 据。如果要检索NULL数据，将如何操作呢？利用”=“是不行的。需要使用IS NULL就可以达到目的，而利用IS NOT NULL”就可以检索非NULL的数据。
🌰：要求査询产品价格为NULL的数据。
SQL> select productname,productprice from productinfo where productprice is null;
PRODUCTNAME PRODUCTPRICE
-------------------- ------------
华为智慧屏2代
3.6 GROUP BY和HAVING子句
GROUP BY子句和HAVING子句同WHERE不一样，它们两个都用于组的査询。使用分组 査询可以统计数据，例如利用GROUP BY配合分组函数，可以同时査询出每种类型产品的平均价格。
(1) group by子句
🌰：计算出不同类型产品的平均价格
SQL> select category,avg(productPrice) 平均价格 from productInfo group by category;
CATEGORY 平均价格
---------- ----------
手机 5665.66667
电视 9665.66667
平板 4999
🌰：根据不同产地，计算不同类型产品的平均价格
SQL> select category 种类,productPrice 产品价格,origin 产地 from productInfo;
种类 产品价格 产地
---------- ---------- --------------------
手机 7999 郑州
手机 6999 郑州
手机 1999 郑州
平板 4999 四川
电视 8999 四川
电视 9999 郑州
电视 9999 台湾
电视 台湾
已选择8行。
SQL> select category 种类,avg(productPrice) 平均价格,origin 产地 from productInfo
2 group by category,origin;
种类 平均价格 产地
---------- ---------- --------------------
电视 9999 郑州
电视 9999 台湾
平板 4999 四川
电视 8999 四川
手机 5665.66667 郑州
🌰：根据不同产地，计算价格高于3000的、不同类型产品的平均价格。
SQL> select category 种类,avg(productPrice) 平均价格,origin 产地 from productInfo
2 where productPrice > 3000 group by category,origin;
种类 平均价格 产地
---------- ---------- --------------------
电视 9999 郑州
电视 9999 台湾
平板 4999 四川
电视 8999 四川
手机 7499 郑州
(2) having子句
HAVING子句通常和GROUP BY子句一起使用，限制搜索条件。
它和WHERE子句不一样， HAVING子句与组有关，而不与单个的值有关。在GROUP BY子句中，它会作用于GROUP BY创建的组。
🌰：计算出不同类型产品的平均价格，并列出平均价格高于3000的数据。
SQL> SELECT category, AVG (productprice) 平均价格
2 FROM ProductInfo GROUP BY category HAVING AVG (productprice)> 3000;
CATEGORY 平均价格
---------- ----------
手机 5665.66667
电视 9665.66667
平板 4999
4. 使用子查询
什么是子査询？子査询就是嵌套査询，简单说就是当前查询语句的条件是另一条语句的结果。
🌰：查出部门地址在”NEW YORK“的员工信息
SQL> select ename,job,sal from emp where deptno =
(select deptno from dept where loc = 'NEW YORK');
ENAME JOB SAL
---------- --------- ----------
CLARK MANAGER 2450
KING PRESIDENT 5000
MILLER CLERK 1300
🌰：查出部门地址在”NEW YORK“，并且工资高于3000的员工信息
SQL> select ename,job,sal from emp where deptno =
(select deptno from dept where loc = 'NEW YORK') and sal > 3000;
ENAME JOB SAL
---------- --------- ----------
KING PRESIDENT 5000
4.1 关键字in的使用：
🌰：查出部门地址在”NEW YORK“，或者部门名称为”SALES“的员工信息
SQL> select ename,job,sal from emp where deptno in
(select deptno from dept where loc = 'NEW YORK' or dname='SALES');
ENAME JOB SAL
---------- --------- ----------
CLARK MANAGER 2450
KING PRESIDENT 5000
MILLER CLERK 1300
WARD SALESMAN 1250
TURNER SALESMAN 1500
ALLEN SALESMAN 1600
JAMES CLERK 950
BLAKE MANAGER 2850
MARTIN SALESMAN 1250
已选择9行。
4.2 关键字ANY的使用
表示满足子査询结果的任意一个即可。和＜、＜=搭配，表示小于等于列表中的最大值，而和>、＞=配合时表示大于等于列表中的最小值。
🌰：从emp表中查出工资低于指定价格列表中的最大值。指定的价格列表就是部门地址在”DALLAS“的所有员工工资。
SQL> select ename,job,sal from emp where sal <
any(select sal from emp where deptno =
(select deptno from dept where loc='DALLAS'));
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
JAMES CLERK 950
ADAMS CLERK 1100
WARD SALESMAN 1250
MARTIN SALESMAN 1250
MILLER CLERK 1300
TURNER SALESMAN 1500
ALLEN SALESMAN 1600
CLARK MANAGER 2450
BLAKE MANAGER 2850
JONES MANAGER 2975
已选择11行。
4.3 关键字ALL的使用
表示满足子査询结果的所有结果。和＜、＜=搭配，表示小于等于列表中的最小 值，而和>、＞=配合时表示大于等于列表中的最大值。
🌰：从emp表中查出工资低于指定价格列表中的最小值。指定的价格列表就是部门地址在”NEW YORK“的所有员工工资。
SQL> select ename,job,sal from emp where sal <
all(select sal from emp where deptno =
(select deptno from dept where loc='NEW YORK'));
ENAME JOB SAL
---------- --------- ----------
WARD SALESMAN 1250
MARTIN SALESMAN 1250
ADAMS CLERK 1100
JAMES CLERK 950
SMITH CLERK 800
5. 连接查询
关系型数据库中允许表和表之间存在关系，这种关系可以把两个甚至多个表的数据联系在 一起。
连接分为内连接、外连接和全连接，还有一种叫做自连接，其中最常用的是内连接和外连接。
5.1 最简单的连接査询
最简单的连接査询是利用逗号完成的，它利用逗号把FROM后的表名隔开，这就构成了最简单的连接査询。但这么做的意义不大。
🌰：利用表emp表和dept表实现最简单的连接査询。
select * from emp,dept;
利用这种方式査询数据 将得到两个表的笛卡儿积，也就是说得到两个表中记录数的乘积。而这么做显然没有什么意义。
关于笛卡儿积，就是一个表中的毎一行与另一个表中的每一行连接在一起而形成的新表， 也就是査询结果。査询结果的记录数是这两个记录数的乘积。
🌰：验证笛卡儿积
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> select count(*) from dept;
COUNT(*)
----------
4
SQL> select count(*) from dept,emp;
COUNT(*)
----------
56
5.2 内连接
内连接也称为简单连接，它会把两个或多个表进行连接，只能査询出匹配的记录，不匹配的记录将无法査询出来。这种连接查询是平时最常用的査询。
🌰：查询出emp表和dept表中deptno一致的数据
select e.ename,e.job,e.sal,e.deptno,d.dname,d.loc
from emp e,dept d where e.deptno = d.deptno;
等价于：
select e.ename,e.job,e.sal,e.deptno,d.dname,d.loc
from emp e inner join dept d on e.deptno = d.deptno;
注意：inner join关键字可以直接写成join。
5.3 外连接
外连接分为左外连接、右外连接、全外连接。
左外连接（left join）：使用左外连接的査询，返回的结果不仅仅是符合连接条件的行记录，还包含了左边表中的全部记录，也就是说，如果左表的某行记录在右表中没有匹配项，则在返回结果中右表的所有选择列表列均为空。
右外连接（right join）：它与左外连接相反，将右边的表中所有的数据与左表进行匹配，返回的结果除了匹配成功的记录，还包含了右表中未匹配成功的记录，并在其左表对应的列补空值。
全外连接（full join）：返回所有匹配成功的记录，并返回左表未匹配成功的记录，也返回右表未 匹配成功的记录。
select e.ename,e.job,e.sal,e.deptno,d.dname,d.loc
from emp e left join dept d on e.deptno = d.deptno;
5.4 自连接
自连接是指在同一张表的连接查询。
🌰：显示员工的上级领导的姓名，比如显示‘FORD’的上级
第一步：查询出FORD这个人的上级编号：
select mgr from emp where ename='FORD';
第二步：显示上级的信息：
select * from emp where empno=
(select mgr from emp where ename='FORD');
🌰扩展要求：显示各员工的姓名和他的上级领导姓名
思路：把emp表看做两张表（worker，boss）
SQL> select worker.empno 员工编号,worker.ename 员工姓名,boss.empno 领导编号,boss.ename 领导姓名
from emp worker,emp boss
where worker.mgr=boss.empno;
员工编号 员工姓名 领导编号 领导姓名
---------- ---------- ---------- ----------
7902 FORD 7566 JONES
7788 SCOTT 7566 JONES
7844 TURNER 7698 BLAKE
7499 ALLEN 7698 BLAKE
7521 WARD 7698 BLAKE
7900 JAMES 7698 BLAKE
7654 MARTIN 7698 BLAKE
7934 MILLER 7782 CLARK
7876 ADAMS 7788 SCOTT
7698 BLAKE 7839 KING
7566 JONES 7839 KING
员工编号 员工姓名 领导编号 领导姓名
---------- ---------- ---------- ----------
7782 CLARK 7839 KING
7369 SMITH 7902 FORD
已选择13行。
🌰补充：这里我们看到KING没有显示，因为KING没有上级，如果我们希望把没有上级的人也显示出来，则需要使用外连接
select worker.ename,boss.ename from emp worker,emp boss
where worker.mgr=boss.empno(+);
习题
一、填空题
SELECT语句中，利用（ ）可以去除重复记录。
为査询出来的数据进行排序，需要用到（ ）关键字。
排序时，降序关键字是（ ）
模糊査询的关键字是（ ）.
模糊査询中，％表示 （ ）
分组査询利用 （ ）子句.
二、选择题
-
内连接的语法是（）
A. INNER JOIN B. OUTER JOIN
-
外连接分为（ ）
A. 左外连接B. 右外连接
C. 全外连接
D. 自连接
