03. DQL 语言学习

DQL: DataBase Query Language（SQL 分支：数据库查询语言）

1. 基础查询

1.0 基本语法

前置准备：myemployees 中各张表的数据解释

myemployees/employees  员工表
  - employee_id        员工编号
  - first_name         名
  - last_name          姓
  - email              邮箱
  - phone_number       电话号码
  - job_id             工种编号
  - salary             月薪
  - commission_pct     奖金率
  - manager_id         上级领导的员工编号
  - department_id      部门编号
  - hiredate           入职日期

myemployees/departments  部门表
  - department_id      部门编号
  - department_name    部门名称
  - manager_id         部门领导的员工编号
  - location_id          位置编号

myemployees/locations   位置表
  - location_id   位置编号
  - street_address      街道
  - postal_code   邮编
  - city       城市
  - state_province  州
  - country_id   国家编号

myemployees/jobs  工种
  - job_id    工种编号
  - job_title           工种名称
  - min_salary   最低工资
  - max_salary   最高工资

基本查询语法：

着重号 `` ，当字段名与已知 SQL 关键字重复，则可使用。一般情况也可使用。

语法：

select 查询列表 from 表名;

特点：

1. 查询列表可以是：表中的字段、常量值、表达式、函数
2. 查询的结果是一个虚拟的表格

当前操作切换到 myemployees 表：

USE myemployees;

1.1 查询表中的单个字段

SELECT
    last_name
FROM
    employees ;

1.2 查询表中的多个字段

SELECT
    last_name,
    salary,
    email
FROM
    employees ;

1.3 查询表中的所有字段

方式一：

SELECT
    `employee_id`,
    `first_name`,
    `phone_number`,
    `commission_pct`,
    `manager_id`,
    `department_id`,
    `hiredate`
FROM
    employees ;

方式二：

SELECT * FROM employees;

1.4 查询常量值

SELECT 100;
SELECT 'Murphy';

1.5 查询表达式

SELECT 100 % 98;

1.6 查询函数

SELECT VERSION();

1.7 起别名

关键字：AS 优点：

• 便于理解
• 如果要查询的字段有重名的情况，使用别名可以区分开来

方式一：

SELECT 100%98 AS 结果;
SELECT last_name AS 姓,first_name AS 名 FROM employees;

方式二

SELECT last_name 姓, first_name 名 FROM employees;

案例：查询 salary，显示结果为 out put

SELECT salary AS "out put" FROM employees;

1.8 去重(distinct)

#关键字：distinct
#案例：查询员工表中涉及的所有部门编号

SELECT DISTINCT department_id FROM employees;

1.9 + 号的作用

java 中的 + 号：

• 运算符：两个操作数都是数值型
• 连接符，只要有一个为字符串

mysql 中的 + 号： 仅仅只有一个功能：运算符

# 两个操作数都是数值型，则作加法运算
select 100+90; # 190
# 其中有一个为字符型，试图将字符串数值转化成数值型.
# 如果转换成功，则继续做加法运算。
select '123'+90; # 213
# 如果转换失败，则将字符串型转换成 0
select 'abc'+90;  # 90
select null + 10; 只要其中一个为 null,则结果肯定为 null

1. 10. CONCAT 连接符

案例：查询员工名和姓连接成一个字段，并显示为 姓名

SELECT CONCAT('a','b','c') AS 结果; # abc

SELECT
    CONCAT(last_name, " ", first_name) AS 姓名
FROM
    employees;

2. 条件查询

2.0 基本语法

语法：

select
    查询列表
from
    表名
where
    筛选条件

2.1 按条件表达式

条件运算符：>, <, 相等 =, 不等 <>, >=, <= 案例 1：查询工资>12000 的员工信息

USE myemployees;

SELECT
 *
FROM
 employees
WHERE
 salary > 12000;

案例 2：查询部门编号不等于 90 的员工名和部门编号

SELECT
 last_name AS 名字,
 department_id AS 部门编号
FROM
 employees
WHERE
 `department_id` <> 90

2.2 按逻辑表达式筛选

逻辑运算符：

and, or, not
&&, ||, !

案例 1：查询工资在 10000-20000 之间的员工名、工资、奖金

SELECT
 `last_name` AS 名字,
 `salary` AS 工资,
 IFNULL(commission_pct, 0) AS 奖金率
FROM
 employees
WHERE
 salary >= 10000 AND salary <= 20000;

案例 2：查询部门编号不是在 90 到 110 之间，或者工资高于 15000 的员工信息

USE myemployees;

SELECT
 *
FROM
 employees
WHERE
 department_id < 90 OR department_id > 110 OR salary > 15000

2.3 模糊查询

1. LIKE

• 一般与通配符搭配使用
• 通配符： % 任意多个字符，包含 0 个字符 _ 匹配任意单个字符
• 转义字符 escape <转义字符> 将指定字符设置为转义符号

案例 1：筛选员工名中包含字符 a 的员工信息

SELECT
 *
FROM
 employees
WHERE
 last_name LIKE "%a%";

案例 2：查询员工名中第三个字符为 n，第五个字符为 l 的员工名和工资

SELECT
 last_name AS 员工名,
 salary AS 工资
FROM
 employees
WHERE
 last_name LIKE "__n_l%";

案例 3：查询员工名中第二个字符为_的员工名

SELECT
 last_name
FROM
 employees
WHERE
 last_NAME LIKE "_$_%" ESCAPE "$";

2. BETWEEN AND

• 提高简洁度
• between a and b 包含[a,b]

案例 1：查询员工编号在 100-120 之间的员工信息

USE myemployees;

SELECT
 *
FROM
 employees
WHERE
 # employee_id >=100 and employee_id <= 120;
 employee_id BETWEEN 100 AND 120;

3. IN 关键字

含义：判断某个字段是否属于 in 列表中的某一项 特点：

• 使用 in 提高简洁度
• in 列表的值类型必须统一或兼容（'123', 123）

案例 1：查询员工的工种编号 IT_PROT, AD_VP, AD_PRES 中的一个员工名和工种编号

SELECT
    last_name,
    job_id
FROM
    employees
/*
where job_id = 'IT_PROT'
    or job_id = 'AD_VP'
    OR job_id = 'AD_PRES' ;
*/

WHERE
 job_id IN ('IT_PRO', 'AD_VP', 'AD_PRES')

4. IS NULL / IS NOT NULL

案例 1：查询没有奖金的员工名和奖金率

• =或<>不能判断等于 null
• is null, is not null 可以

SELECT
 last_name,
 commission_pct
FROM
 employees
WHERE
 commission_pct IS NOT NULL;

5. 安全等于 <=>

SELECT
 last_name,
 commission_pct
FROM
 employees
WHERE
 commission_pct <=> NULL;

案例：查询工资为 12000 的员工信息

SELECT
 last_name,
 salary
FROM
 employees
WHERE
 salary <=> 12000;

6. is null 和 <=>

• is null: 仅仅可以判断 null 值，可读性高，建议使用
• <=> : 既可以判断 null 值，也可以判断普通数值，可读性差

3. 排序查询

3.0 基本语法

USE myemployees;

SELECT
 查询列表
FROM
 表
ORDER BY 排序列表 [ASC|DESC];

含义：

• asc 代表升序，desc 代表降序，默认为升序
• order by 子句中可以是单个字段、多个字段、表达式、函数、别名
• order by 子句一般是放在查询语句最后面，但 limit 子句除外。

3.1 一般升/降序排序

案例 1：查询员工信息，要求工资从高到底排序

SELECT * FROM employees ORDER BY salary DESC;
SELECT * FROM employees ORDER BY salary ASC;

案例 2：查询部门编号>=90 的员工信息，按入职时间的先后进行排序

SELECT *
FROM employees
WHERE department_id >= 90
ORDER BY hiredate ASC;

3.2 按表达式排序

案例 3：按年薪的高低显示员工的信息和年薪

SELECT *, salary * 12*(1+ IFNULL(commission_pct, 0)) AS 年薪
FROM employees;
ORDER BY salary * 12*(1+ IFNULL(commission_pct, 0)) DESC;

3.3 按别名排序

案例 4：按年薪的高低显示员工的信息和年薪

SELECT *, salary * 12*(1+ IFNULL(commission_pct, 0)) AS 年薪
FROM employees;
ORDER BY 年薪 DESC;

3.4 按函数排序

案例 5：按姓名的长度显示姓名和工资

SELECT LENGTH(last_name) 字节长度, last_name, salary
FROM employees
ORDER BY LENGTH(last_name) DESC;

3.5 按多个字段排序

案例 6：查询员工信息，要求先按工资升序，再按员工编号降序

SELECT *
FROM employees
ORDER BY salary ASC, employee_id DESC;