〇、导入一个数据库

首先导入数据库 myemployees,执行 myemployees.sql 文件(图形客户端执行)。

-> 下载该文件

执行SQL

命令行下:

## 先下载文件到本地
wget https://statics.gemo.one/others/mysql/myemployees.sql
## 一步执行命令
mysql -uroot -ppass_word <myemployees.sql

## 或
mysql -uroot -ppass_word
mysql> source xxxx # 文件名

初步认识

导入后我们可以看到数据库中的表的关系如下:

myemployees数据库结构

一、基础查询

1. 语法

select 查询列表 from 表名;

2. 特点

  1. 查询结果是一个虚拟表

  2. select 查询列列表,类似打印

    select` 后面可以有多个部分组成,中间用逗号隔开:

    select 字段1,字段2,表达式 from 表;
  3. 执行顺序

    1. from 子句
    2. select 子句
  4. 查询列表可以是:字段、表达式、常量、函数等

3. 执行

1. 查询常量

SELECT 100;

2. 查询表达式

SELECT 100%3;
## 结果是 1

3. 查询单个字段

SELECT last_name FROM employees;
## 对于sql关键字作为查询字段的时候,需要加着重号`
## SELECT `last_name` FROM `employees`;

结果:

last_name
K_ing
Kochhar
De Haan
……

4. 查询多个字段

SELECT last_name, email, employee_id FROM employees;

结果:

last_name email employee_id
K_ing SKING 100
Kochhar NKOCHHAR 101
De Haan LDEHAAN 102
…… …… ……

5. 查询所有字段

SELECT * FROM employees;
# 字段顺序根据表中定义的顺序输出

# 多行多个字段
SELECt
       last_name,
       first_name,
       commission_pct,
       hiredate,
       salary
FROM
     employees;

结果:

employee_id first_name last_name email phone_number job_id salary commission_pct manager_id department_id hiredate
100 Steven K_ing SKING 515.123.4567 AD_PRES 24000 NULL NULL 90 1992-04-03 00:00:00
…… ……

6. 查询函数(调用函数,获取返回值)

## 查看当前所在的数据库
SELECT DATABASE();
## 查看当前数据库的版本号
SELECT VERSION();
## 获取当前用户信息 返回 user@ip
SELECT USER();

7. 起别名

查询的显示名称可以使用方法修改成其他名字:

# 方式1 使用 as 关键字
SELECT USER() AS 用户名;
SELECT USER() AS "用户名";
SELECT USER() AS '用户名';

SELECT last_name AS "姓 名" FROM employees;

# 方式2 使用空格 直接删除 AS 即可
SELECT USER() 用户名;
SELECT USER() "用户名";
SELECT USER() '用户名';

SELECT last_name "姓 名" FROM employees;

## 语义性上 AS 方法更强

结果:

姓 名
K_ing
……

8. 拼接

+ 号的作用

  1. 两个操作数为数值型,则执行正常的加法运算;
  2. 其中一个操作数为字符型则将字符型转换成数值,如果无法转换,则直接当作 0 处理;
  3. 其中一个操作数为 null ,则结果是 null;

拼接函数 CONCAT

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

结果:

姓 名
Steven K_ing
……

9. DISTINCT 函数

可以用来去重查询,查询后结果会自动去除重复数据。

SELECT DISTINCT department_id FROM employees;

10. 查看表结构

DESC employees;
# aka 
SHOW COLUMNS FROM employees;

## 显示表的字段信息

4. 案例

USE `myemployees`;
# 下面的语句是否可以执行成功
SELECT last_name , job_id , salary AS Sal FROM employees;
# 下面的语句是否可以执行成功
SELECT * FROM employees;
# 找出下面语句中的错误
SELECT employee_id , last_name,
       salary * 12 "ANNUAL SALARY"
FROM employees;
# 显示表 departments 的结构,并查询其中的全部数据
DESC departments;
SELECT * FROM departments;
# 显示出表 employees 中的全部 job_id(不能重复)
SELECT DISTINCT job_id FROM employees;
# 显示出表 employees 的全部列,各个列之间用逗号连接,列头显示成 OUT_PUT
SELECT CONCAT(employee_id, ',',
              first_name, ',',
              last_name, ',',
              email, ',',
              phone_number, ',',
              job_id, ',',
              salary, ',',
              IFNULL(commission_pct, ''), ',',
              IFNULL(manager_id, ''), ',',
              IFNULL(department_id, ''), ',',
              hiredate
           ) AS "OUT_PUT" FROM employees;

## IFNULL(1, 2)
-- 如果 1 是 NULL ,则显示2;否则1

二、条件查询

1. 语法

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

执行顺序:

  1. from
  2. where
  3. select
SELECT last_name, first_name FROM employees where salary>20000;

2. 特点

  1. 按关系表达式筛选

    关系运算符 >大于 <小于  >=大于等于 <=小于等于 =等于 <>不等于
        可以使用 != 但不建议
  2. 按逻辑表达式筛选

    逻辑运算符: and or not
        可以 && || ! ,但不建议
  3. 模糊查询

    like
    in 
    between and
    is null

3. 执行

1. 按关系表达式筛选

# 案例1: 查询部门编号不是100的员工信息
SELECT *
FROM employees
WHERE department_id <> 100;

# 案例2: 查询工资<15000 的姓名,工资
SELECT CONCAT(first_name, ' ', last_name) AS '姓名',
       salary AS '工资'
FROM employees
WHERE  salary <15000;

2. 按逻辑表达式筛选

# 案例1: 查询部门编号不是50-100之间员工的姓名、部门编号、邮箱
# 方式1
SELECT CONCAT(first_name, ' ', last_name) AS '姓名',
       department_id AS '部门编号',
       email AS '邮箱'
FROM employees
WHERE department_id <50 OR department_id >100;

# 方式2
SELECT CONCAT(first_name, ' ', last_name) AS '姓名',
       department_id AS '部门编号',
       email AS '邮箱'
FROM employees
WHERE NOT(department_id >= 50 AND department_id <=100);

# 语义清晰还是使用模糊查询 但本小结是逻辑表达式
SELECT CONCAT(first_name, ' ', last_name) AS '姓名',
       department_id AS '部门编号',
       email AS '邮箱'
FROM employees
WHERE department_id NOT BETWEEN 50 AND 100;


# 案例2: 查询奖金率>0.03 或者 员工编号在60-110 之间的员工信息
SELECT *
FROM employees
WHERE commission_pct>0.03 or (employee_id >= 60 AND employee_id <= 100)

3. 模糊查询

1. like 语句

一般用于字符型字段。

一般和通配符搭配使用,对字符型数据进行部分匹配查询。

常见的通配符:

  • _ 任意单个字符
  • % 任意多个字符(0-多个)
# 案例1: 查询姓名中包含字符a的员工信息
SELECT *
FROM employees
WHERE last_name LIKE '%a%'; 

# 案例2: 查询姓名中最后一个字符为e的员工信息
SELECT *
FROM employees
WHERE last_name LIKE '%e';

# 案例3: 查询姓名中第一个字符为e的员工信息
SELECT *
FROM employees
WHERE last_name LIKE 'e%';

# 案例4: 查询姓名中第三个字符为x的员工信息
SELECT *
FROM employees
WHERE last_name LIKE '__x%';

# 案例5: 查询姓名中第二个字符为_的员工信息
# 使用转义字符
SELECT *
FROM employees
WHERE last_name LIKE '_$_%' ESCAPE '$';
## 可以使用其他语言中 的 '\' 作为转义字符,但不建议
## ESCAPE 可以定义前面作为转义字符的 字符
## 该语句中定义率 '$' 作为转义字符

2. in 语句

用于查询某字段的值是否属于指定的列表之内

a in (常量值1, 2, 3, ……)

# not
a not in (1, 2, 3, ……)
# 案例1: 查询部门编号是30/50/90的员工名、部门编号
SELECT CONCAT(first_name, ' ', last_name) AS '员工姓名',
       department_id AS '部门ID'
FROM employees
WHERE department_id in (30, 50, 90);

# 逻辑表达式
SELECT CONCAT(first_name, ' ', last_name) AS '员工姓名',
       department_id AS '部门ID'
FROM employees
WHERE department_id = 30
OR department_id = 50
OR department_id = 90
## 两种方式的效率是一致的,仅仅是语义上的区别

# 案例2 查询工种编号不是 SH_CLERK 或 IT_PROG 的员工信息
SELECT *
FROM employees
WHERE job_id NOT IN ('SH_CLERK', 'IT_PROG');
## 数值型不需要引号,其他需要

3. between and 语句

判断某个字段的值是否介于xx之间

between and

not between and
# 案例1 查询部门编号在30-90之间的部门编号、员工姓名
SELECT department_id AS '部门ID',
       CONCAT(first_name, ' ', last_name) AS '姓名'
FROM employees
WHERE department_id BETWEEN 30 AND 90;

# 案例2 查询年薪不是100000-200000之间的员工姓名、工资、年薪
SELECT CONCAT(first_name, ' ', last_name) AS '员工姓名',
       salary AS '月工资',
       salary * 12 * (1 + IFNULL(commission_pct, 0)) AS '年薪'
FROM employees
WHERE salary * 12 * (1 + IFNULL(commission_pct, 0)) NOT BETWEEN 100000 AND 200000;

4. is null / is not null

# 案例1 查询没有奖金的员工信息
SELECT *
FROM employees
WHERE commission_pct IS NULL;

## = 只能判断普通类型
## IS 只能判断NULL值
## <=> 安全等于,既能判断普通内容,又能判断NULL值 --> 语义性不好

4. 案例

# 查询工资大于 12000 的员工姓名和工资
SELECT CONCAT(first_name, ' ', last_name) AS '员工姓名',
       salary AS '工资'
FROM employees
WHERE salary > 12000;

# 查询员工号为 176 的员工的姓名和部门号和年薪
SELECT CONCAT(first_name, ' ', last_name) AS '员工姓名',
       department_id AS '部门号',
       salary * 12 * (1 + IFNULL(commission_pct, 0)) AS '年薪'
FROM employees
WHERE employee_id = 176;

# 选择工资不在 5000 到 12000 的员工的姓名和工资
SELECT CONCAT(first_name, ' ', last_name) AS '员工姓名',
       salary AS '工资'
FROM employees
WHERE salary NOT BETWEEN 5000 AND 12000;

# 选择在 20 或 50 号部门工作的员工姓名和部门号
SELECT CONCAT(first_name, ' ', last_name) AS '员工姓名',
       department_id AS '部门号'
FROM employees
WHERE department_id IN (20, 50);

# 选择公司中没有管理者的员工姓名及 job_id
SELECT CONCAT(first_name, ' ', last_name) AS '员工姓名',
       job_id
FROM employees
WHERE manager_id IS NULL;

# 选择公司中有奖金的员工姓名,工资和奖金级别
SELECT CONCAT(first_name, ' ', last_name) AS '员工姓名',
       salary AS '工资',
       commission_pct AS '奖金级别'
FROM employees
WHERE commission_pct IS NOT NULL;

# 选择员工姓名的第三个字母是 a 的员工姓名
SELECT last_name AS '名字'
FROM employees
WHERE last_name LIKE '__a%';

# 选择姓名中有字母 a 和 e 的员工姓名
SELECT last_name AS '名字'
FROM employees
WHERE last_name LIKE '%a%'
  AND last_name LIKE '%e%';

# 显示出表 employees 表中 first_name 以 'e'结尾的员工信息
SELECT *
FROM employees
WHERE first_name LIKE '%e';

# 显示出表 employees 部门编号在 80-100 之间 的姓名、职位
SELECT CONCAT(first_name, ' ', last_name) AS '姓名',
       job_id AS '职位'
FROM employees
WHERE department_id BETWEEN 80 AND 100;

# 显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、职位
SELECT CONCAT(first_name, ' ', last_name) AS '姓名',
       job_id AS '职位'
FROM employees
WHERE manager_id IN (100, 101, 110);

三、排序查询

1. 语法

select 查询列表
from 表名
[where 筛选条件]
order by 排序列表

执行循序:

  1. from
  2. where
  3. select
  4. order by
select last_name, salary
form employees
where salary > 10000
order by salary;

2. 特点

  1. 排序列表可以是单个字段、多个字段、表达式、函数、以及以上的组合

  2. 升序:通过 asc默认

    降序:通过 desc

3. 执行

1. 按单个字段排序

# 案例1  将员工编号>120的员工信息进行工资的升序
SELECT *
FROM employees
WHERE employee_id > 120
ORDER BY salary;

2. 按表达式排序

# 案例1 对有奖金的员工,按年薪降序
SELECT *,
       salary * 12 * (1 + IFNULL(commission_pct, 0)) AS '年薪'
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary * 12 * (1 + IFNULL(commission_pct, 0)) DESC;

## 先执行 WHERE 语句, 于是 在计算的过程中 不需要使用 IFNULL
## 由于 WHERE 先执行 不能用 SELECT 起的别名,但 ORDER BY 是 SELECT 后执行的,可以使用别名
SELECT *,
       salary * 12 * (1 + IFNULL(commission_pct, 0)) AS 年薪
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY 年薪 DESC;
## 注意 ORDER BY 后面不能接 '年薪',而应该接 年薪。
## 加 '' 引号的内容 ORDER BY 会自动忽略

3. 按函数的结果排序

# 案例1 按姓名的字数长度进行升序
SELECT LENGTH(last_name), last_name
FROM employees
ORDER BY LENGTH(last_name)

4. 按多个字段进行排序

# 案例1 查询员工的姓名、工资、部门编号,按工资升序、再按部门编号降序
SELECT CONCAT(first_name, ' ', last_name),
       salary,
       department_id
FROM employees
ORDER BY salary,
         department_id DESC ;

5. 按列数排序

SELECT *
FROM employees
ORDER BY 2;

## 直接列数 将按照表中的第二个属性字段进行排序
## 阅读性差

总结

本文主要完成 SQL 中的查询操作,即 DQL(Data Query Language)数据查询语言,其中包括基础查询以及扩展的条件查询和数据排序。

其中,所有查询都是基于基础查询进行操作的:

SELECT KEYWORD1,KEYWORD2
FROM TABLE_S_NAME

具体实现上是先执行 FROM 语句再执行 SELECT ,即先获取表,再在表中获取对应的字段数据。同时在 SELETE 后我们还可以添加 DISTINCT关键字实现去重查询,即查询结果中我们不会看到重复的数据。

在基础查询的基础上,我们可以给予其一些限定条件,使用 WHERE 实现操作。

SELECT KEYWORD
FROM TABLE_S_NAME
WHERE XXX

条件语句是我们编程中熟悉的语句,其中不等号在 SQL 中是 <> 而非 != ,但 != 是可用的。且或非使用对应的英文替代,当然符号也支持。

特有的是模糊查询,有些类似于正则表达式,但有所不同。

LIKE 关键字后可以使用 _ %_ 代表任意单个字符,% 代表任意多个字符。

还有 INBETWEEN AND IS NULL 等,都是对应的语义。

而排序查询控制升序的 ASC ,降序的是 DESC。查询若有排序字段,默认是升序,不需要添加对应的关键字。

在写 SQL 语句时应当使用其原生的语句,而且设计时应当使得语句有强的可读性。