MySQL（八）：MySQL语法-基础

灰太狼 2024-03-17 17:22 49阅读 0赞

#### MySQL语法-基础 ####

*   *  Docker运行MySQL
     *  示例数据库源
     *  SQL 简介
     *  SQL 语法
     *   *  \`SELECT DISTINCT\`
         *  \`WHERE、运算符\`
         *  \`ORDER BY 关键字\`
         *  \`INSERT INTO\`
         *  \`NULL Values（空值）\`
         *  \`UPDATE\`
         *  \`Delete\`

### Docker运行MySQL ###

$ docker run --name local-mysql -p 3306:3306 --privileged -v /path/mysql/conf.d:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=123456 -d mysql

[mysqld]
    
    default-time_zone = '+8:00'

### 示例数据库源 ###

[employee data (large dataset, includes data and test/verification suite)][employee data _large dataset_ includes data and test_verification suite]

### SQL 简介 ###

***什么是空值？***

> 空值不同于零值或包含空格的字段，具有空值的字段是在创建记录时留空的字段

***SQL 约束***

> 用于限制可以进入表中的数据类型，约束可以是列级别，也可以是表级别（列级约束应用于一列；表级约束应用于整个表）

<table> 
 <thead> 
  <tr> 
   <th>约束</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>NOT NULL</td> 
   <td>保证列中数据不能有 NULL 值</td> 
  </tr> 
  <tr> 
   <td>DEFAULT</td> 
   <td>提供该列数据未指定时所采用的默认值</td> 
  </tr> 
  <tr> 
   <td>UNIQUE</td> 
   <td>保证列中的所有数据各不相同</td> 
  </tr> 
  <tr> 
   <td>PRIMARY KEY 主键</td> 
   <td>唯一标识数据表中的行/记录</td> 
  </tr> 
  <tr> 
   <td>FOREIGN KEY 外键</td> 
   <td>唯一标识其他表中的一条行/记录，一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY</td> 
  </tr> 
  <tr> 
   <td>CHECK</td> 
   <td>此约束保证列中的所有值满足某一条件</td> 
  </tr> 
  <tr> 
   <td>INDEX 索引</td> 
   <td>用于在数据库中快速创建或检索数据</td> 
  </tr> 
 </tbody> 
</table>

***数据完整性***

*  实体完整性：表中没有重复行。
 *  域完整性：通过限制值的类型、格式或范围来强制执行给定列的有效条目。
 *  引用完整性：不能删除其他记录使用的行。
 *  用户定义的完整性：强制执行一些不属于实体、域或引用完整性的特定业务规则。

***数据库规范化***

*  消除冗余数据，例如，将相同的数据存储在多个表中。
 *  确保数据依赖关系是有意义的。

### SQL 语法 ###

#### `SELECT DISTINCT` ####

> 用于仅返回不同的（different）值

***示例：***

-- 查询员工分哪些岗位
    SELECT DISTINCT title
    FROM titles;

***示例：***

-- 查询有多少个岗位分工
    SELECT COUNT(DISTINCT title)
    FROM titles;

COUNT(DISTINCT title)|
    ---------------------+
                        7|

***示例：***

-- 查询有多少个岗位分工
    SELECT COUNT(DISTINCT title) as count
    FROM titles;

count|
    -----+
        7|

#### `WHERE、运算符` ####

> WHERE 子句用于提取满足指定标准的记录

***算术运算符***

***比较运算符***

<table> 
 <thead> 
  <tr> 
   <th>运算符</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>=</td> 
   <td>等于</td> 
  </tr> 
  <tr> 
   <td>&lt;&gt;</td> 
   <td>不等于。 注意：在某些版本的 SQL 中，这个操作符可能写成!=</td> 
  </tr> 
  <tr> 
   <td>&lt;</td> 
   <td>大于</td> 
  </tr> 
  <tr> 
   <td>&gt;</td> 
   <td>小于</td> 
  </tr> 
  <tr> 
   <td>&gt;=</td> 
   <td>大于等于</td> 
  </tr> 
  <tr> 
   <td>&lt;=</td> 
   <td>小于等于</td> 
  </tr> 
  <tr> 
   <td>!&lt;</td> 
   <td>不小于</td> 
  </tr> 
  <tr> 
   <td>!&gt;</td> 
   <td>不大于</td> 
  </tr> 
 </tbody> 
</table>

***逻辑运算符***

<table> 
 <thead> 
  <tr> 
   <th>运算符</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>BETWEEN</td> 
   <td>在某个范围内</td> 
  </tr> 
  <tr> 
   <td>LIKE</td> 
   <td>搜索某种模式</td> 
  </tr> 
  <tr> 
   <td>IN</td> 
   <td>为列指定多个可能的值</td> 
  </tr> 
  <tr> 
   <td>OR</td> 
   <td>或</td> 
  </tr> 
  <tr> 
   <td>AND</td> 
   <td>且</td> 
  </tr> 
  <tr> 
   <td>NOT</td> 
   <td>非</td> 
  </tr> 
  <tr> 
   <td>IS NULL</td> 
   <td>用于将值与NULL值进行比较，为空</td> 
  </tr> 
  <tr> 
   <td>IS NOT NULL</td> 
   <td>用于将值与NULL值进行比较，不为空</td> 
  </tr> 
  <tr> 
   <td>ALL</td> 
   <td>用于将值与另一个值集中的所有值进行比较</td> 
  </tr> 
  <tr> 
   <td>ANY</td> 
   <td>用于根据条件将值与列表中的任何适用值进行比较</td> 
  </tr> 
  <tr> 
   <td>EXISTS</td> 
   <td>用于搜索指定表中是否存在满足特定条件的行</td> 
  </tr> 
 </tbody> 
</table>

***示例：***

select 2 * salary as salary
    from salaries;

***示例：***

-- 【=】条件查询(查询岗位为Senior Engineer的数据)
    SELECT *
    FROM titles
    WHERE title = 'Senior Engineer';
    
    -- 【<>、!=】条件查询(查询岗位不为Senior Engineer的数据)
    SELECT *
    FROM titles
    WHERE title <> 'Senior Engineer';
    
    SELECT *
    FROM titles
    WHERE title != 'Senior Engineer';
    
    -- 【>=、<=、AND、BETWEEN】条件查询(查询薪资在 salary >= 140700 AND salary <= 141000 的数据)
    SELECT *
    FROM salaries
    WHERE salary >= 140700
      AND salary <= 141000;
    
    SELECT *
    FROM salaries
    WHERE salary BETWEEN 140700 AND 141000;
    
    -- 【>、<、OR、AND】条件查询(查询薪资为 salary > 158000 OR (salary > 40010 AND salary < 40012) 的数据)
    SELECT *
    FROM salaries
    WHERE salary > 158000
       OR (salary > 40010 AND salary < 40012);
    
    -- 【LIKE】条件查询(查询岗位中后缀带Engineer的岗位数据)
    SELECT DISTINCT title
    FROM titles
    WHERE title LIKE '%Engineer';
    
    -- 【in】条件查询(查询为开发或销售部门的数据)
    SELECT *
    FROM departments
    WHERE dept_name IN ('开发', '销售');
    
    -- 【=、in】条件查询(查询在开发部门的员工管理者姓名)
    SELECT emp_no, first_name, last_name
    FROM employees
    WHERE emp_no in (SELECT emp_no
                     FROM dept_manager
                     WHERE dept_no in (SELECT dept_no
                                       FROM departments
                                       WHERE dept_name = '开发'));
    
    -- 【not】条件查询(查询部门不为开发的数据)
    SELECT *
    FROM departments
    WHERE NOT dept_name = '开发';
    
    -- 【not、in】条件查询(查询部门不为开发和销售的数据)
    SELECT *
    FROM departments
    WHERE NOT dept_name in ('开发', '销售');

***示例(`all`、`in`)：***

-- 查询【salary <= 155000】的【薪资数据】
    select salary
    from salaries
    where salary <= 155000;
    
    -- 查询【salary >= 【all (salary <= 155000)中最大值】】的【员工编号】
    select distinct emp_no
    from salaries
    where salary >= all (select salary from salaries where salary <= 155000);
    
    -- 查询【员工信息】在【【salary >= 【all (salary <= 155000)中最大值】】的【员工编号】】中的【员工信息数据】
    select *
    from employees
    where emp_no in (select emp_no from salaries where salary >= all (select salary from salaries where salary <= 155000));

+--------+
    | salary |
    +--------+
    |  60117 |
    |  62102 |
    |  66074 |
    ......
    
    +--------+
    | emp_no |
    +--------+
    |  43624 |
    |  47978 |
    | 109334 |
    | 253939 |
    | 254466 |
    +--------+
    5 rows in set (0.68 sec)
    
    +--------+------------+------------+-----------+--------+------------+
    | emp_no | birth_date | first_name | last_name | gender | hire_date  |
    +--------+------------+------------+-----------+--------+------------+
    |  43624 | 1953-11-14 | Tokuyasu   | Pesch     | M      | 1985-03-26 |
    |  47978 | 1956-03-24 | Xiahua     | Whitcomb  | M      | 1985-07-18 |
    | 109334 | 1955-08-02 | Tsutomu    | Alameldin | M      | 1985-02-15 |
    | 253939 | 1957-12-03 | Sanjai     | Luders    | M      | 1987-04-15 |
    | 254466 | 1963-05-27 | Honesty    | Mukaidono | M      | 1986-08-08 |
    +--------+------------+------------+-----------+--------+------------+
    5 rows in set (1.12 sec)

***示例(`exists`)：***

-- 查询【员工表】在【【薪资表中存在的数据】并且【薪资大于155000】】的【员工信息数据】 
    select *
    from employees
    where exists (select * from salaries where emp_no = employees.emp_no and salary > 155000);

+--------+------------+------------+-----------+--------+------------+
    | emp_no | birth_date | first_name | last_name | gender | hire_date  |
    +--------+------------+------------+-----------+--------+------------+
    |  43624 | 1953-11-14 | Tokuyasu   | Pesch     | M      | 1985-03-26 |
    |  47978 | 1956-03-24 | Xiahua     | Whitcomb  | M      | 1985-07-18 |
    | 109334 | 1955-08-02 | Tsutomu    | Alameldin | M      | 1985-02-15 |
    | 253939 | 1957-12-03 | Sanjai     | Luders    | M      | 1987-04-15 |
    | 254466 | 1963-05-27 | Honesty    | Mukaidono | M      | 1986-08-08 |
    +--------+------------+------------+-----------+--------+------------+
    5 rows in set (0.38 sec)

***示例(`any`)：***

-- 查询【员工编号 在【薪资大于等于155000的员工编号数据中】】的【员工信息数据】
    select *
    from employees
    where emp_no = any (select distinct emp_no from salaries where salary >= 155000);

#### `ORDER BY 关键字` ####

> 用于按升序或降序对结果集进行排序，默认情况下按升序排序记录（`ASC`升序、`DESC`降序）

***示例(创建表)：***

create table order_table
    (
        id      int auto_increment comment 'ID' primary key,
        date_01 date null comment '日期 01',
        date_02 date null comment '日期 02'
    );
    
    select *
    from order_table;

+----+------------+------------+
    | id | date_01    | date_02    |
    +----+------------+------------+
    |  1 | 2023-07-01 | NULL       |
    |  2 | 2023-07-02 | NULL       |
    |  3 | 2023-07-03 | 2023-07-07 |
    |  4 | 2023-07-03 | 2023-07-09 |
    |  5 | 2023-07-03 | 2023-07-08 |
    |  6 | 2023-07-06 | NULL       |
    |  7 | 2023-07-05 | NULL       |
    |  8 | 2023-07-04 | NULL       |
    +----+------------+------------+
    8 rows in set (0.00 sec)

***示例(排序)：***

-- date_01按正序排列，date_02按倒序排列
    select *
    from order_table
    order by date_01 ASC, date_02 DESC;

+----+------------+------------+
    | id | date_01    | date_02    |
    +----+------------+------------+
    |  1 | 2023-07-01 | NULL       |
    |  2 | 2023-07-02 | NULL       |
    |  4 | 2023-07-03 | 2023-07-09 |
    |  5 | 2023-07-03 | 2023-07-08 |
    |  3 | 2023-07-03 | 2023-07-07 |
    |  8 | 2023-07-04 | NULL       |
    |  7 | 2023-07-05 | NULL       |
    |  6 | 2023-07-06 | NULL       |
    +----+------------+------------+
    8 rows in set (0.00 sec)

#### `INSERT INTO` ####

> 用于向表中插入新的数据行

***示例：***

-- 使用另一个表填充一个表
    insert into order_table (date_01, date_02)
    SELECT from_date, to_date
    FROM dept_manager;
    
    select *
    FROM order_table;

+----+------------+------------+
    | id | date_01    | date_02    |
    +----+------------+------------+
    |  1 | 2023-07-01 | NULL       |
    |  2 | 2023-07-02 | NULL       |
    |  3 | 2023-07-03 | 2023-07-07 |
    |  4 | 2023-07-03 | 2023-07-09 |
    |  5 | 2023-07-03 | 2023-07-08 |
    |  6 | 2023-07-06 | NULL       |
    |  7 | 2023-07-05 | NULL       |
    |  8 | 2023-07-04 | NULL       |
    |  9 | 1985-01-01 | 1991-10-01 |
    | 10 | 1991-10-01 | 9999-01-01 |
    | 11 | 1985-01-01 | 1989-12-17 |
    ......
    | 31 | 1992-09-08 | 1996-01-03 |
    | 32 | 1996-01-03 | 9999-01-01 |
    +----+------------+------------+
    32 rows in set (0.01 sec)

#### `NULL Values（空值）` ####

> 具有NULL值的字段是没有值的字段，NULL 值与 0 或者包含空白（spaces）的字段是不同的，需要使用`IS NULL`和`IS NOT NULL`  
> 运算符来判断比较`null`

***示例：***

select *
    from order_table
    where date_02 is null;

+----+------------+---------+
    | id | date_01    | date_02 |
    +----+------------+---------+
    |  1 | 2023-07-01 | NULL    |
    |  2 | 2023-07-02 | NULL    |
    |  6 | 2023-07-06 | NULL    |
    |  7 | 2023-07-05 | NULL    |
    |  8 | 2023-07-04 | NULL    |
    +----+------------+---------+
    5 rows in set (0.00 sec)

***示例：***

select *
    from order_table
    where date_02 is not null;

+----+------------+------------+
    | id | date_01    | date_02    |
    +----+------------+------------+
    |  3 | 2023-07-03 | 2023-07-07 |
    |  4 | 2023-07-03 | 2023-07-09 |
    |  5 | 2023-07-03 | 2023-07-08 |
    +----+------------+------------+
    3 rows in set (0.00 sec)

#### `UPDATE` ####

> 更新表中已存在的记录

***示例：***

-- 修改Staff为普通员工
    update titles
    set title = '普通员工'
    where title = 'Staff';

+--------+-----------------+------------+------------+
    | emp_no | title           | from_date  | to_date    |
    +--------+-----------------+------------+------------+
    |  10001 | Senior Engineer | 1986-06-26 | 9999-01-01 |
    |  10002 | 普通员工          | 1996-08-03 | 9999-01-01 |
    |  10003 | Senior Engineer | 1995-12-03 | 9999-01-01 |
    |  10004 | Engineer        | 1986-12-01 | 1995-12-01 |
    |  10004 | Senior Engineer | 1995-12-01 | 9999-01-01 |
    |  10005 | Senior Staff    | 1996-09-12 | 9999-01-01 |
    |  10005 | 普通员工          | 1989-09-12 | 1996-09-12 |
    |  10006 | Senior Engineer | 1990-08-05 | 9999-01-01 |
    |  10007 | Senior Staff    | 1996-02-11 | 9999-01-01 |
    |  10007 | 普通员工          | 1989-02-10 | 1996-02-11 |
    ......

#### `Delete` ####

> 用于删除表中现有记录

***示例：***

delete
    from order_table
    where date_02 is null;

[employee data _large dataset_ includes data and test_verification suite]: https://dev.mysql.com/doc/index-other.html