MYSQL开发 - 02.简单查询 - 《Java开发在线文档》

2.1结构化查询语言
- 2.1.1 结构化查询语言简介
- 2.1.2 结构化查询语言分类
2.2 SELECT语句
2.3 算术运算符
- 2.3.1算术运算符
- 2.3.2 算术运算符优先级
2.4 空值NULL
- 2.4.1 理解NULL
- 2.4.2 算术表达式中的空值NULL
2.5 列别名
2.6 消除重复行
2.7 显示表结构
2.8 选择限定数据行
2.9 ORDER BY子句
2.10 限制记录的行数

一、单元概述
通过本章的学习能够了解MySQL结构查询语言的概念，掌握SELECT查询语句的基本语法，掌握SELECT查询语句中过滤条件的使用，掌握过滤条件中比较运算符和逻辑运算符的使用，掌握查询结果的排序等
二、重点与难点
重点：

SELECT查询语句的基本语法

难点：

SELECT查询语句中过滤条件中逻辑运算符的混合使用
SELECT查询语句中过滤条件中特殊比较运算符的使用
排序语法的应用

2.1结构化查询语言

2.1.1 结构化查询语言简介
结构化查询语言(Structured Query Language)简称SQL, 是操作和检索关系型数据库的标准语言，20世纪70年代由IBM公司开发，目前应用于各种关系型数据库。

2.1.2 结构化查询语言分类
结构化查询语言可分为5类：
- 数据查询语言（DQL:Data Query Language）：语句主要包括SELECT，用于从表中检索数据。
- 数据操作语言（DML：Data Manipulation Language）：语句主要包括INSERT，UPDATE和DELETE，用于添加，修改和删除表中的行数据。
- 事务处理语言（TPL:Transaction Process Language）：语句主要包括COMMIT和ROLLBACK，用于提交和回滚。
- 数据控制语言（DCL:Data Control Language）：语句主要包括GRANT和REVOKE，用于进行授权和收回权限。
- 数据定义语言（DDL:Data Definition Language）：语句主要包括CREATE、DROP、ALTER，用于定义、销毁、修改数据库对象
  2.2 SELECT语句
  2.2.1基本SELECT语句
基本SELECT语句作用
基本SELECT语句语法 SELECT子句表示所需检索的数据列。 FROM子句表示检索的数据来自哪个表。
```
SELECT     [DISTINCT]{*| column| expression [alias],...}
FROM    table;
```
2.2.2 SQL概念和规则
SQL语句相关概念关键字（Keyword）：SQL语言保留的字符串，例如，SELECT和FROM都是关键字。语句（statement）：一条完整的SQL命令。例如，SELECT - FROM dept 是一条语句。子句（clause）：部分的SQL语句，通常是由关键字加上其它语法元素构成，例如，SELECT - 是一个子句，FROM table也是一个子句。
SQL语句的书写规则不区分大小写，也就是说SELECT，select，Select，执行时效果是一样的。可以单行来书写，也可以书写多行，建议分多行书写，增强代码可读性，通常以子句为单位进行分行。关键字不可以缩写、分开以及跨行书写，如SELECT不可以写成SEL或SELE CT等形式。关键字最好使用大写，其它语法元素（如列名、表名等）小写。 Tab和缩进的使用可以提高程序的可读性

2.2.3 选择列
选择所有列
```
SELECT *  FROM     dept;
```
| DEPTNO | DNAME | LOC | | —- | —- | —- | | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON |
选择所有列
```
SELECT deptno,dname,loc FROM dept;
```
| DEPTNO | DNAME | LOC | | —- | —- | —- | | 10 ACCOUNTING | NEW YORK | | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON |

试比较哪条语句执行效率更高？

选择指定的列
```
SELECT deptno, loc FROM  dept;
```
| DEPTNO | LOC | | —- | —- | | 10 | NEW YORK | | 20 | DALLAS | | 30 | CHICAGO | | 40 | BOSTON |

练习

使用两种方式查询所有员工(EMP)信息
查询(EMP)员工编号、员工姓名、员工职位、员工月薪、工作部门编号。
2.3 算术运算符
2.3.1算术运算符

可以在SELECT语句中使用算术运算符，改变输出结果。 | 运算符 | 描述 | | —- | —- | | + | 加 | | - | 减 | | * | 乘 | | / | 除 |
算术运算符的使用
```
SELECT ename, sal, sal+300  FROM    emp;
```
| ENAME | SAL | SAL+300 | | —- | —- | —- | | KING | 5000 | 5300 | | BLAKE | 2850 | 3150 | | CLARK | 2450 | 2750 | | JONES | 2975 | 3275 | | MARTIN | 1250 | 1550 | | ALLEN | 1600 | 1900 |

2.3.2 算术运算符优先级

乘除优先于加减
相同优先权的表达式按照从左至右的顺序依次计算
括弧可以提高优先权，并使表达式的描述更为清晰
```
SELECT ename, sal, 12*sal+100
FROM   emp;
```
| ENAME | SAL | 12*SAL+100 | | —- | —- | —- | | KING | 5000 | 60100 | | BLAKE | 2850 | 34300 | | CLARK | 2450 | 29500 | | JONES | 2975 | 35800 | | MARTIN | 1250 | 15100 | | ALLEN | 1600 | 19300 |
使用括号改变优先级
```
SELECT ename, sal, 12*(sal+100)
FROM   emp;
```
| ENAME | SAL | 12*(SAL+100) | | —- | —- | —- | | KING | 5000 | 61200 | | BLAKE | 2850 | 35400 | | CLARK | 2450 | 30600 | | JONES | 2975 | 36900 | | MARTIN | 1250 | 16200 |

练习

员工转正后，月薪上调20%，请查询出所有员工转正后的月薪。
员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的年薪所得（不考虑奖金部分,年薪的试用期6个月的月薪+转正后6个月的月薪)
2.4 空值NULL
2.4.1 理解NULL
空值是指一种无效的、未赋值、未知的或不可用的值。空值不同于零或者空格。

NULL体现
```
SELECT ename, job, sal, comm
FROM   emp;
```
| ENAME | JOB | SAL | COMM | | —- | —- | —- | —- | | KING | PRESIDENT | 5000 | | | BLAKE | MANAGER | 2850 | | | TURNER | SALESMAN | 1500 | 0 |

2.4.2 算术表达式中的空值NULL

任何包含空值的算术表达式运算后的结果都为空值NULL。

select ename, 12*sal+comm 
from   emp

ENAME	12*SAL+COMM
KING

2.5 列别名

列别名用来重新命名列的显示标题如果SELECT语句中包含计算列，通常使用列别名来重新定义列标题。
使用列别名的方法
- 方式1：列名列别名
- 方式2：列名 AS 列别名
以下三种情况列别名两侧需要添加双引号
列别名中包含有空格
列别名中要求区分大小写(ORACLE中)
列别名中包含有特殊字符

列别名使用

SELECT ename AS name, sal salary
FROM   emp;

| NAME | SALARY| | ——————- | ————-| | xxxx| xxxx|
```
SELECT ename  "Name",
sal*12 "Annual Salary"
FROM   emp;
```
| Name | Annual Salary | | —- | —- | | xxxx | xxxx |

练习

员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的所有收入，要求显示列标题为员工姓名，工资收入，奖金收入，总收入。
2.6 消除重复行

重复行以下查询的结果默认输出所有行，其中包含了重复行
```
SELECT deptno
FROM   emp;
```
| DEPTNO | | —- | | 10 | | 30 | | 10 | | 20 | | … |
消除重复行在SELECT字句中使用关键字DISTINCT可消除重复行。
```
SELECT DISTINCT deptno
FROM   emp;
```
| DEPTNO | | —- | | 10 | | 20 | | 30 |

练习

查询员工表中一共有哪几种岗位类型
2.7 显示表结构

可以使用DESCRIBE 命令来查看表结构
DESC[RIBE] tablename

练习

分别查看员工表、部门表、薪资等级表的表结构。
2.8 选择限定数据行
2.8.1 WHERE条件过滤

使用WHERE子句可以返回限定的数据行,语法如下：
- WHERE子句紧跟在FROM子句之后
- condition(s)表示条件表达式，
  - 通常格式为：列名比较操作符要比较的值
```
SELECT [DISTINCT] {*|  column |  expression [alias], ...}
FROM   table
[WHERE condition(s)];
```
    2.8.2 比较操作符
    | 操作符 | 含义 | | —- | —- | | = | 等于 | | > | 大于 | | >= | 大于或等于 | | < | 小于 | | <= | 小于或等于 | | <> | 不等于 |

2.8.3 使用where子句

比较数值型数据

SELECT ename, job, deptno
FROM   emp
WHERE  deptno=20;

| ENAME | JOB | DEPTNO | | —- | —- | —- | | JAMES | CLERK | 20 | | SMITH | MANAGER | 20 | | ADAMS | ANALYST | 20 | | MILLER | CLERK | 20 |
比较字符型数据
```
SELECT ename, job, deptno
FROM   emp
WHERE  job='CLERK';
```
| ENAME | JOB | DEPTNO | | —- | —- | —- | | JAMES | CLERK | 30 | | SMITH | CLERK | 20 | | ADAMS | CLERK | 20 | | MILLER | CLERK | 10 |

- 字符型数据作为被比较的值时，必须用单引号引起来
- 字符型数值区分大小写(MySQL不区分，Oracle区分)

比较日期型数据
```
SELECT ename, hiredate, deptno
FROM   emp
WHERE  hiredate > '1985-01-01';
```
| ENAME | HIREDATE | DEPTNO | | —- | —- | —- | | SCOTT | 1987-4-19 | 20 | | ADAMS | 1987-5-23 | 20 |
日期型数值作为被比较的值时，必须用单引号引起来。

练习

查询职位为SALESMAN的员工编号、职位、入职日期。
查询1985年12月31日之前入职的员工姓名及入职日期。
查询部门编号不在10部门的员工姓名、部门编号。
2.8.4 特殊比较运算符

特殊比较运算符 | 运算符 | 含义 | | —- | —- | | BETWEEN…AND… | 判断要比较的值是否在某个范围内。 | | IN（集合列表） | 判断要比较的值是否和集合列表中的任何一个值相等。 | | LIKE | 判断要比较的值是否满足部分匹配。 | | IS NULL | 判断要比较的值是否为空值NULL |
BETWEEN..AND..
- 使用BETWEEN .. AND.. 运算符来判断要比较的值是否在某个范围内。
```
SELECT    ename, sal
FROM     emp
WHERE    sal BETWEEN 1000 AND 1500;
```
  | ENAME | SAL | | —- | —- | | MARTIN | 1250 | | TURNER | 1500 | | WARD | 1250 | | ADAMS | 1100 | | MILLER | 1300 |
IN运算符
- 使用IN运算符判断要比较的值是否和集合列表中的任何一个值相等。
```
SELECT    empno, ename, sal, mgr
FROM     emp
WHERE    mgr IN (7902, 7566, 7788);
```
  | EMPNO | ENAME | SAL | MGR | | —- | —- | —- | —- | | 7902 | FORD | 3000 | 7566 | | 7369 | SMITH | 800 | 7902 | | 7788 | SCOTT | 3000 | 7566 | | 7876 | ADAMS | 1100 | 7788 |

练习

查询入职日期在82年至85年的员工姓名，入职日期。
查询月薪在3000到5000的员工姓名，月薪。
查询部门编号为10或者20的员工姓名，部门编号。
查询经理编号为7902, 7566, 7788的员工姓名，经理编号

LIKE运算符
- 使用LIKE运算符判断要比较的值是否满足部分匹配，也叫模糊查询。模糊查询中两个通配符：
  - % 代表零或任意更多的字符
  - _ 代表一个字符
```
SELECT    ename
FROM     emp
WHERE    ename LIKE 'S%';
```

LIKE运算符

%与_组合使用

SELECT    ename
FROM     emp
WHERE    ename LIKE '_L%';

LIKE通配符转义
- 您可以使用ESCAPE标识符实现对“%”和 “_”的查找。
- 例：查询 JOB 以“MAN_”开头的雇员信息。
```
SELECT    ename,job
FROM     emp
WHERE    job LIKE 'MAN@_%' ESCAPE '@';
```
IS NULL 运算符
- 使用 IS NULL 运算符来判断要比较的值是否为空值NULL
```
SELECT  ename, mgr
FROM    emp
WHERE   mgr IS NULL;
```
  | ENAME | MGR | | —- | —- | | KING | |

练习

查询员工姓名以W开头的员工姓名。
查询员工姓名倒数第2个字符为T的员工姓名。
查询奖金为空的员工姓名，奖金。
2.8.5 逻辑运算符

逻辑运算符
- 当需要和多个条件表达式进行比较时，需要使用逻辑运算符把多个表达式连接起来，逻辑运算符包括AND、OR、NOT，逻辑表达式的结果为TRUE，FALSE，NULL。 | 运算符 | 含义 | | —- | —- | | AND | 逻辑与，用来连接多个条件表达式。如果每个条件表达式的结果都为TRUE，整个表达式的结果才为TRUE。 | | OR | 逻辑或，用来连接多个条件表达式。只要有1个条件表达式的结果为TRUE，整个表达式的结果就为TRUE | | NOT | 逻辑非，用来对条件表达式取反。TRUE取反为FALSE，FALSE取反为TRUE。 |
逻辑与(AND)：
- AND: 要求两个条件都为真,结果才为真
```
SELECT empno, ename, job, sal
FROM   emp
WHERE  sal>=1100
AND    job='CLERK';
```
  | EMPNO | ENAME | JOB | SAL | | —- | —- | —- | —- | | 7876 | ADAMS | CLERK | 1100 | | 7934 | MILLER | CLERK | 1300 |
逻辑或(OR)：
- OR: 只需要两个条件中的一个为真,结果就返回真
```
SELECT empno, ename, job, sal
FROM   emp
WHERE  sal>=1100
OR     job='CLERK';
```
  | EMPNO | ENAME | JOB | SAL | | —- | —- | —- | —- | | 7839 | KING | PRESIDENT | 5000 | | 7698 | BLAKE | MANAGER | 2850 | | 7782 | CLARK | MANAGER | 2450 | | 7566 | JONES | MANAGER | 2975 | | 7654 | MARTIN | SALESMAN | 1250 | | 7900 | JAMES | CLERK | 950 | | … | | | |
逻辑非(NOT)
```
SELECT ename, job
FROM   emp
WHERE  job NOT IN ('CLERK','MANAGER','ANALYST');
```
| ENAME | JOB | | —- | —- | | KING | PRESIDENT | | MARTIN | SALESMAN | | ALLEN | SALESMAN | | TURNER | SALESMAN | | WARD | SALESMAN |

逻辑非(NOT)

NOT运算符还可以和BETWEEN…AND、LIKE、IS NULL一起使用

... WHERE deptno NOT IN (10, 20)
... WHERE sal NOT BETWEEN 3000 AND 5000
... WHERE ename NOT LIKE 'D%’
... WHERE mgr IS NOT NULL

2.8.6 运算符的优先级

括号’()’优先于其他操作符。 | 优先级 | 运算分类 | 运算符举例 | | —- | —- | —- | | 1 | 算术运算符 | *, \, +, - | | 2 | 比较运算符 | =, <>, <, >, <=, >= | | 3 | 特殊比较运算符 | BETWEEN..AND.. ,IN,LIKE,IS NULL | | 4 | 逻辑非 | NOT | | 5 | 逻辑与 | AND | | 6 | 逻辑或 | OR |

运算符的优先级

SELECT ename, job, sal
FROM   emp
WHERE  job='SALESMAN'  OR  job='PRESIDENT'  AND  sal>1500;

| ENAME | JOB | SAL| | ————— | ————-| ————-| | KING | PRESIDENT | 5000| | MARTIN | SALESMAN | 1250| | ALLEN | SALESMAN | 1600| | TURNER | SALESMAN | 1500| | WARD | SALESMAN | 1250|
运算符的优先级
- 使用括号强制改变优先权
```
SELECT   ename, job, sal
FROM     emp
WHERE    (job='SALESMAN'
OR       job='PRESIDENT')
AND      sal>1500;
```
  | ENAME | JOB | SAL | | —- | —- | —- | | KING | PRESIDENT | 5000 | | ALLEN | SALESMAN | 1600 |

练习

查询工资超过2000并且职位是 MANAGER或SALESMAN的员工姓名、职位、工资。
查询部门在10或者20，并且工资在3000到5000之间的员工姓名、部门、工资。
查询入职日期在81年，并且职位不是SALES开头的员工姓名、入职日期、职位。
查询职位为SALESMAN或MANAGER，部门编号为10或者20，姓名包含A的员工姓名、职位、部门编号。
2.9 ORDER BY子句
2.9.1 ORDER BY子句语法

使用ORDER BY子句能对查询结果集进行排序,语法结构如下：

SELECT  [DISTINCT] { *| 列名 | 表达式 [别名][,...] }
FROM    表名
[WHERE  条件]
[ORDER BY  {列名| 表达式| 列别名| 列序号} [ASC| DESC],…];

其中：
- 可以按照列名、表达式、列别名、结果集的列序号排序
- ASC: 升序(默认值), DESC: 降序
- ORDER BY 子句必须写在SELECT语句的最后
  2.9.2 排序规则(以升序为例)
数字升序排列小值在前，大值在后。即按照数字大小顺序由小到大排列。
日期升序排列相对较早的日期在前，较晚的日期在后。
字符升序排列按照字母由小到大的顺序排列。即由A-Z排列；中文升序按照字典顺序排列。
空值在升序排列中排在最前面，在降序排列中排在最后。
按列名升序排序
```
SELECT   ename, job, deptno, hiredate
FROM         emp
ORDER BY hiredate;
```
| ENAME | JOB | DEPTNO | HIREDATE | | —- | —- | —- | —- | | SMITH | CLERK | 20 | 1980-12-17 | | ALLEN | SALESMAN | 30 | 1981-02-20 | | … | | | |
按列名降序排序
```
SELECT       ename, job, deptno, hiredate
FROM         emp
ORDER BY hiredate DESC;
```
| ENAME | JOB | DEPTNO | HIREDATE | | —- | —- | —- | —- | | ADAMS | CLERK | 20 | 1987-05-23 | | SCOTT | ANALYST | 20 | 1987-04-19 | | MILLER | CLERK | 10 | 1982-01-23 | | … | | | |
按列别名排序
```
SELECT   empno, ename, sal*12 annsal
FROM     emp
ORDER BY annsal;
```
| EMPNO | ENAME | ANNSAL | | —- | —- | —- | | 7369 | SMITH | 9600 | | 7900 | JAMES | 11400 | | 7876 | ADAMS | 13200 | | 7654 | MARTIN | 15000 | | 7521 | WARD | 15000 | | 7934 | MILLER | 15600 | | 7844 | TURNER | 18000 | | … | | |

2.9.3 多列参与排序

SELECT     ename, deptno, sal
FROM      emp
ORDER BY     deptno, sal DESC;

ENAME	DEPTNO	SAL
KING	10	5000
CLARK	10	2450
MILLER	10	1300
FORD	20	3000
…

参与排序的多列都可以指定升序或者降序
ORDER BY子句中可以写没在SELECT列表中出现的列

2.9.4 按结果集列序号排序
ORDER BY子句后列名可以用数字来代替，这个数字是SELECT语句后列的顺序号。
```
SELECT     ename, deptno, sal
FROM      emp
ORDER BY     2, 3 DESC;
```
| ENAME | DEPTNO | SAL | | —- | —- | —- | | KING | 10 | 5000 | | CLARK | 10 | 2450 | | MILLER | 10 | 1300 | | FORD | 20 | 3000 | | …. | | |

练习

查询部门在20或30的员工姓名，部门编号，并按照工资升序排序。
查询工资在2000-3000之间，部门不在10号的员工姓名，部门编号，工资，并按照部门升序，工资降序排序。
查询入职日期在82年至83年之间，职位以SALES或者MAN开头的员工姓名，入职日期，职位，并按照入职日期降序排序。
2.10 限制记录的行数

使用select语句时，经常要返回前几条或者中间某几行记录，可以使用关键字limit。语法格式如下：
```
SELECT 字段列表
FROM 数据源
LIMIT [start,]length;
```
说明： 1．limit接受一个或两个整数参数。start表示从第几行记录开始输出，length表示输出的记录行数。 2．表中第一行记录的start值为0(不是 1)。
查询员工表的前5条记录
```
SELECT     ename, deptno, sal
FROM      emp
LIMIT 0,5;
```
| ENAME | DEPTNO | SAL | | —- | —- | —- | | SMITH | 20 | 800 | | ALLEN | 30 | 1600 | | WARD | 30 | 1250 | | JONES | 20 | 2975 | | MARTIN | 30 | 1250 | | …. | | |

练习

查询入职日期最早的前5名员工姓名，入职日期。
查询20号部门下入职日期最早的前2名员工姓名，入职日期。
按照每页显示5条记录，分别查询第1页，第2页，第3页信息，要求显示员工姓名、入职日期、部门编号。

本章小结

结构化查询语言的作用和分类
基本SELECT语句的作用
空值NULL
列别名
消除重复行的关键字Distinct
限制数据的作用及 WHERE 子句的语法
按列名、列别名、顺序号排序及升序降序排序
限制记录行数

本章作业

查询入职时间在1982-7-9之后，并且不从事SALESMAN工作的员工姓名、入职时间、职位。
查询员工姓名的第三个字母是a的员工姓名。
查询除了10、20号部门以外的员工姓名、部门编号。
查询部门号为30号员工的信息，先按工资降序排序，再按姓名升序排序。
查询没有上级的员工(经理号为空)的员工姓名。
查询工资大于等于4500并且部门为10或者20的员工的姓名\工资、部门编号。

02.简单查询

2.1结构化查询语言

2.1.1 结构化查询语言简介

2.1.2 结构化查询语言分类

2.2 SELECT语句

2.2.1基本SELECT语句

2.2.2 SQL概念和规则

2.2.3 选择列

2.3 算术运算符

2.3.1算术运算符

2.3.2 算术运算符优先级

2.4 空值NULL

2.4.1 理解NULL

2.4.2 算术表达式中的空值NULL

2.5 列别名

2.6 消除重复行

2.7 显示表结构

2.8 选择限定数据行

2.8.1 WHERE条件过滤

2.8.2 比较操作符

2.8.3 使用where子句

2.8.4 特殊比较运算符

2.8.5 逻辑运算符

2.8.6 运算符的优先级

2.9 ORDER BY子句

2.9.1 ORDER BY子句语法

2.9.2 排序规则(以升序为例)

2.9.3 多列参与排序

2.9.4 按结果集列序号排序

2.10 限制记录的行数