数据库|（三）DQL语言学习

1. 基础查询

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

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 表：

```sql
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
案例：查询员工表中涉及的所有部门编号
```sql
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;