MySQL（十）：MySQL语法-进阶

╰+攻爆jí腚メ 2024-03-17 21:10 23阅读 0赞

#### MySQL语法-进阶 ####

*   *  数据类型
     *   *  Text 类型
         *  Number 类型
         *  Date 类型
     *  \`AS\`
     *  \`ALTER TABLE\`
     *  \`concat\`、\`group\_concat\`
     *  SQL注入
     *  阻止SQL注入
     *   *  方案一
         *  方案二
         *  方案三
     *  \`HAVING 子句\`
     *  临时表
     *  正则表达式
     *  获取服务器元数据
     *  事务
     *  导出数据
     *   *  导出数据
         *  导出表作为原始数
         *  导出SQL格式的数据
     *  导入数据
     *   *  解决无法导入问题
         *  使用 LOAD DATA 导入数据

### 数据类型 ###

> 三种主要的类型：`Text（文本）`、`Number（数字）` 和 `Date/Time（日期/时间）`类型

#### Text 类型 ####

<table> 
 <thead> 
  <tr> 
   <th>数据类型</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>CHAR(size)</td> 
   <td>保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多 255 个字符。</td> 
  </tr> 
  <tr> 
   <td>VARCHAR(size)</td> 
   <td>保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多 255 个字符。注释：如果值的长度大于 255，则被转换为 TEXT 类型。</td> 
  </tr> 
  <tr> 
   <td>TINYTEXT</td> 
   <td>存放最大长度为 255 个字符的字符串。</td> 
  </tr> 
  <tr> 
   <td>TEXT</td> 
   <td>存放最大长度为 65,535 个字符的字符串。</td> 
  </tr> 
  <tr> 
   <td>BLOB</td> 
   <td>用于 BLOBs（Binary Large OBjects）。存放最多 65,535 字节的数据。</td> 
  </tr> 
  <tr> 
   <td>MEDIUMTEXT</td> 
   <td>存放最大长度为 16,777,215 个字符的字符串。</td> 
  </tr> 
  <tr> 
   <td>MEDIUMBLOB</td> 
   <td>用于 BLOBs（Binary Large OBjects）。存放最多 16,777,215 字节的数据。</td> 
  </tr> 
  <tr> 
   <td>LONGTEXT</td> 
   <td>存放最大长度为 4,294,967,295 个字符的字符串。</td> 
  </tr> 
  <tr> 
   <td>LONGBLOB</td> 
   <td>用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。</td> 
  </tr> 
  <tr> 
   <td>ENUM(x,y,z,etc.)</td> 
   <td>允许您输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值，则插入空值。注释：这些值是按照您输入的顺序排序的。可以按照此格式输入可能的值： ENUM(‘X’,‘Y’,‘Z’)</td> 
  </tr> 
  <tr> 
   <td>SET</td> 
   <td>与 ENUM 类似，不同的是，SET 最多只能包含 64 个列表项且 SET 可存储一个以上的选择。</td> 
  </tr> 
 </tbody> 
</table>

#### Number 类型 ####

> 注意：这些整数类型拥有额外的选项 UNSIGNED。通常，整数可以是负数或正数。如果添加 UNSIGNED 属性，那么范围将从 0 开始，而不是某个负数。

<table> 
 <thead> 
  <tr> 
   <th>数据类型</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>TINYINT(size)</td> 
   <td>-128 到 127 常规。0 到 255 无符号*。在括号中规定最大位数。</td> 
  </tr> 
  <tr> 
   <td>SMALLINT(size)</td> 
   <td>-32768 到 32767 常规。0 到 65535 无符号*。在括号中规定最大位数。</td> 
  </tr> 
  <tr> 
   <td>MEDIUMINT(size)</td> 
   <td>-8388608 到 8388607 普通。0 to 16777215 无符号*。在括号中规定最大位数。</td> 
  </tr> 
  <tr> 
   <td>INT(size)</td> 
   <td>-2147483648 到 2147483647 常规。0 到 4294967295 无符号*。在括号中规定最大位数。</td> 
  </tr> 
  <tr> 
   <td>BIGINT(size)</td> 
   <td>-9223372036854775808 到 9223372036854775807 常规。0 到 18446744073709551615 无符号*。在括号中规定最大位数。</td> 
  </tr> 
  <tr> 
   <td>FLOAT(size,d)</td> 
   <td>带有浮动小数点的小数字。在 size 参数中规定最大位数。在 d 参数中规定小数点右侧的最大位数。</td> 
  </tr> 
  <tr> 
   <td>DOUBLE(size,d)</td> 
   <td>带有浮动小数点的大数字。在 size 参数中规定最大位数。在 d 参数中规定小数点右侧的最大位数。</td> 
  </tr> 
  <tr> 
   <td>DECIMAL(size,d)</td> 
   <td>作为字符串存储的 DOUBLE 类型，允许固定的小数点。在 size 参数中规定最大位数。在 d 参数中规定小数点右侧的最大位数。</td> 
  </tr> 
 </tbody> 
</table>

#### Date 类型 ####

> 注意：即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。在 INSERT 或 UPDATE 查询中，TIMESTAMP  
> 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。

<table> 
 <thead> 
  <tr> 
   <th>数据类型</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>DATE()</td> 
   <td>日期。格式：YYYY-MM-DD 注释：支持的范围是从 ‘1000-01-01’ 到 ‘9999-12-31’</td> 
  </tr> 
  <tr> 
   <td>DATETIME()</td> 
   <td>*日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS 注释：支持的范围是从 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’</td> 
  </tr> 
  <tr> 
   <td>TIMESTAMP()</td> 
   <td>*时间戳。TIMESTAMP 值使用 Unix 纪元(‘1970-01-01 00:00:00’ UTC) 至今的秒数来存储。格式：YYYY-MM-DD HH:MM:SS 注释：支持的范围是从 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-09 03:14:07’ UTC</td> 
  </tr> 
  <tr> 
   <td>TIME()</td> 
   <td>时间。格式：HH:MM:SS 注释：支持的范围是从 ‘-838:59:59’ 到 ‘838:59:59’</td> 
  </tr> 
  <tr> 
   <td>YEAR()</td> 
   <td>2 位或 4 位格式的年。 注释：4 位格式所允许的值：1901 到 2155。2 位格式所允许的值：70 到 69，表示从 1970 到 2069。</td> 
  </tr> 
 </tbody> 
</table>

### `AS` ###

> 别名

***示例：***

select emp_no, concat(emp.last_name, ' ', emp.first_name) as name
    from employees as emp;

+---------+----------------+
    | emp_no  | name           |
    +---------+----------------+
    |  10001  | Facello Georgi |
    |  10002  | Simmel Bezalel |
    ......
    |  499999 | Tsukuda Sachin  |
    +---------+----------------+
    300024 rows in set (0.25 sec)

***示例：***

-- 查询管理者的姓名、所在部门
    select a.emp_no, a.dept_no, c.dept_name, concat(b.last_name, ' ', b.first_name) as name
    from dept_manager as a,
         employees as b,
         departments as c
    where a.emp_no = b.emp_no
      and a.dept_no = c.dept_no
    order by emp_no;

+--------+---------+------------+-----------------------+
    | emp_no | dept_no | dept_name  | name                  |
    +--------+---------+------------+-----------------------+
    | 110022 | d001    | 销售        | Markovitch Margareta  |
    | 110039 | d001    | 销售        | Minakawa Vishwani     |
    | 110085 | d002    | 财务        | Alpin Ebru            |
    | 110114 | d002    | 财务        | Legleitner Isamu      |
    | 110183 | d003    | 开发        | Ossenbruggen Shirish  |
    | 110228 | d003    | 开发        | Sigstam Karsten       |
    | 110303 | d004    | 生产        | Wegerle Krassimir     |
    | 110344 | d004    | 生产        | Cools Rosine          |
    | 110386 | d004    | 生产        | Kieras Shem           |
    | 110420 | d004    | 生产        | Ghazalie Oscar        |
    | 110511 | d005    | 客户服务     | Hagimont DeForest     |
    | 110567 | d005    | 客户服务     | DasSarma Leon         |
    | 110725 | d006    | 调查        | Onuegbe Peternela     |
    | 110765 | d006    | 调查        | Hofmeyr Rutger        |
    | 110800 | d006    | 调查        | Quadeer Sanjoy        |
    | 110854 | d006    | 调查        | Pesch Dung            |
    | 111035 | d007    | 营销        | Kaelbling Przemyslawa |
    | 111133 | d007    | 营销        | Zhang Hauke           |
    | 111400 | d008    | 质量管理     | Staelin Arie          |
    | 111534 | d008    | 质量管理     | Kambil Hilary         |
    | 111692 | d009    | 人力资源     | Butterworth Tonny     |
    | 111784 | d009    | 人力资源     | Giarratana Marjo      |
    | 111877 | d009    | 人力资源     | Spinelli Xiaobin      |
    | 111939 | d009    | 人力资源     | Weedman Yuchang       |
    +--------+---------+------------+-----------------------+
    24 rows in set (0.00 sec)

### `ALTER TABLE` ###

> 用于添加、删除或者更改现有数据表中的列，添加或者删除现有数据表上的约束

***示例：***

-- 添加列
    alter table employees
        add name int;
    
    -- 修改列并添加约束
    alter table employees modify column name varchar (50) null;
    
    -- 修改数据
    update employees
    set name = concat(last_name, ' ', first_name)
    where name is null;
    
    -- 删除列
    alter table employees drop column name;

### `concat`、`group_concat` ###

> concat列拼接，group\_concat行拼接

***示例：***

-- 列拼接，行拼接
    select emp_no,
           group_concat(
                   concat('(', from_date, '=', to_date, ' $', salary, ')') separator ';'
               ) as date_salary
    from salaries
    group by emp_no limit 3;

+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | emp_no | date_salary                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
    +--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    |  10001 | (1986-06-26=1987-06-26 $60117);(1987-06-26=1988-06-25 $62102);(1988-06-25=1989-06-25 $66074);(1989-06-25=1990-06-25 $66596);(1990-06-25=1991-06-25 $66961);(1991-06-25=1992-06-24 $71046);(1992-06-24=1993-06-24 $74333);(1993-06-24=1994-06-24 $75286);(1994-06-24=1995-06-24 $75994);(1995-06-24=1996-06-23 $76884);(1996-06-23=1997-06-23 $80013);(1997-06-23=1998-06-23 $81025);(1998-06-23=1999-06-23 $81097);(1999-06-23=2000-06-22 $84917);(2000-06-22=2001-06-22 $85112);(2001-06-22=2002-06-22 $85097);(2002-06-22=9999-01-01 $88958) |
    |  10002 | (1996-08-03=1997-08-03 $65828);(1997-08-03=1998-08-03 $65909);(1998-08-03=1999-08-03 $67534);(1999-08-03=2000-08-02 $69366);(2000-08-02=2001-08-02 $71963);(2001-08-02=9999-01-01 $72527)                                                                                                                                                                                                                                                                                                                                                      |
    |  10003 | (1995-12-03=1996-12-02 $40006);(1996-12-02=1997-12-02 $43616);(1997-12-02=1998-12-02 $43466);(1998-12-02=1999-12-02 $43636);(1999-12-02=2000-12-01 $43478);(2000-12-01=2001-12-01 $43699);(2001-12-01=9999-01-01 $43311)                                                                                                                                                                                                                                                                                                                       |
    +--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    3 rows in set (0.00 sec)

### SQL注入 ###

***服务器代码***

***SQL注入***

![在这里插入图片描述][36feea452a7a434284ceba7c5bbf8128.png_pic_center]

### 阻止SQL注入 ###

#### 方案一 ####

> 对传入参数进行编码

// select * from employees where emp_no = '10001 or emp_no=10002;'
    const sql = `select * from employees where emp_no = ${
          mysql.escape(emp_no)}`;
    const sql = `select * from employees where emp_no = ${
          connection.escape(emp_no)}`;

#### 方案二 ####

> 使用 ? 做为查询参数占位符

const sql = `select * from employees where emp_no = ?`;
    
    // select * from employees where emp_no = ?
    console.log(sql);
    
    connection.query(sql, [emp_no], function (error, results, fields) {
        
        if (error) throw error;
        console.log(fields);
        res.end(JSON.stringify(results));
    });

#### 方案三 ####

> 转义格式化函数

// select * from employees where emp_no = '10001 or emp_no=10002;'
    const sql = mysql.format(`select * from employees where emp_no = ?`, [emp_no]);

### `HAVING 子句` ###

> 指定过滤条件

***示例：***

-- 查询薪资总和大于等于2300000的员工编号，并列出薪资和薪资总和
    select emp_no, group_concat(salary separator ';'), sum(salary)
    from salaries
    group by emp_no
    having sum(salary) >= 2300000
    order by emp_no;

+--------+-------------------------------------------------------------------------------------------------------------------------------+-------------+
    | emp_no | group_concat(salary separator ';')                                                                                            | sum(salary) |
    +--------+-------------------------------------------------------------------------------------------------------------------------------+-------------+
    |  43624 | 116058;119115;123270;125780;125557;127816;132136;135281;138616;138597;141585;145711;149571;151115;153166;153458;157821;158220 |     2492873 |
    |  47978 | 112551;114069;116122;117859;120009;124461;126018;126973;130037;131942;133699;137529;137928;141563;145940;149686;151929;155709 |     2374024 |
    |  66793 | 108972;109838;114157;118579;120271;121549;125603;128468;132283;136755;140532;143454;143697;146281;147282;147702;148448;150052 |     2383923 |
    |  68086 | 108929;112101;116446;119066;122737;124049;124900;125150;127769;127864;131848;135634;136750;137228;137918;137494;139708;139760 |     2305351 |
    |  80823 | 108686;110351;114777;117238;121133;122001;123532;124276;128724;132964;136836;138548;142438;145077;146222;149140;151768;154459 |     2368170 |
    | 109334 | 123668;126169;129434;132511;132196;134572;134624;138817;140625;143182;146531;149208;149675;151484;154885;155377;154888;155190 |     2553036 |
    | 237542 | 108963;112701;115269;118341;120344;124625;127976;128919;133402;136116;137549;140469;140199;140097;144526;147942;150994;152687 |     2381119 |
    +--------+-------------------------------------------------------------------------------------------------------------------------------+-------------+
    7 rows in set (1.35 sec)

### 临时表 ###

> 临时表保存临时数据，当终端会话结束后被删除

***示例：***

-- 创建临时表
    CREATE
    TEMPORARY TABLE SALESSUMMARY
    (
        product_name     VARCHAR(50)    NOT NULL,
        total_sales      DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
        avg_unit_price   DECIMAL(7, 2)  NOT NULL DEFAULT 0.00,
        total_units_sold INT UNSIGNED   NOT NULL DEFAULT 0
    );
    
    -- 插入数据
    INSERT INTO SALESSUMMARY
        (product_name, total_sales, avg_unit_price, total_units_sold)
    VALUES ('cucumber', 100.25, 90, 2);
    
    -- 查询数据
    SELECT *
    FROM SALESSUMMARY;
    
    -- 删除临时表
    DROP TABLE SALESSUMMARY;

+--------------+-------------+----------------+------------------+
    | product_name | total_sales | avg_unit_price | total_units_sold |
    +--------------+-------------+----------------+------------------+
    | cucumber     |      100.25 |          90.00 |                2 |
    +--------------+-------------+----------------+------------------+
    1 row in set (0.00 sec)

### 正则表达式 ###

***示例：***

select emp_no, concat(last_name, ' ', first_name) as name
    from employees
    where concat(last_name, ' ', first_name) regexp '^Awdeh Sh'

+--------+-----------------+
    | emp_no | name            |
    +--------+-----------------+
    |  28835 | Awdeh Shrikanth |
    |  28867 | Awdeh Shahar    |
    |  43350 | Awdeh Sham      |
    |  55280 | Awdeh Sham      |
    |  94678 | Awdeh Shigeaki  |
    | 108457 | Awdeh Shaowei   |
    | 203619 | Awdeh Shaibal   |
    | 209529 | Awdeh Shounak   |
    | 224996 | Awdeh Shan      |
    | 259389 | Awdeh Shigeaki  |
    | 419508 | Awdeh Shuky     |
    | 435166 | Awdeh Shahid    |
    | 446799 | Awdeh Shigeu    |
    +--------+-----------------+
    13 rows in set (0.22 sec)

### 获取服务器元数据 ###

<table> 
 <thead> 
  <tr> 
   <th>命令</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>SELECT VERSION()</td> 
   <td>服务器版本信息</td> 
  </tr> 
  <tr> 
   <td>SELECT DATABASE()</td> 
   <td>当前数据库名 (或者返回空)</td> 
  </tr> 
  <tr> 
   <td>SELECT USER()</td> 
   <td>当前用户名</td> 
  </tr> 
  <tr> 
   <td>SHOW STATUS</td> 
   <td>服务器状态</td> 
  </tr> 
  <tr> 
   <td>SHOW VARIABLES</td> 
   <td>服务器配置变量</td> 
  </tr> 
 </tbody> 
</table>

***示例：***

select VERSION(), DATABASE(), USER();

***示例：***

SHOW
    STATUS;
    show
    VARIABLES;

### 事务 ###

> 用于处理操作量大，复杂度高的数据。如：在人员管理系统中，删除一个人员，即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等。因此，这些数据库操作语句就构成一个事务！

*  一般来说，事务是必须满足4个条件（`ACID`）：
    
     *  `Atomicity（原子性或不可分割性）`
        
         *  一组事务，要么成功；要么撤回，即事务在执行过程中出错会回滚到事务开始前的状态。
     *  `Consistency（一致性）`
        
         *  一个事务不论是开始前还是结束后，数据库的完整性都没有被破坏。因此写入的数据必须完全符合所有预设规则（资料精确度、串联性以及后续数据库能够自发完成预定工作）。
     *  `Isolation（隔离性或独立性）`
        
         *  数据库允许多个事务并发的同时对其数据进行读写修改等操作，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
         *  事务隔离可分为：
            
             *  `Read uncommitted（读未提交）`
             *  `Read committed（读提交）`
             *  `Repeatable read（可重复读）`
             *  `Serializable（串行化）`
     *  `Durability（持久性）`
        
         *  事务在处理结束后对数据做出的修改是永久的，无法丢失。

***示例（`begin`、`rollback`、`commit`）：***

set
    autocommit = 0; -- 禁止自动提交
    
    begin; -- 开始事务
    
    insert into departments (dept_no, dept_name)
    values ('d010', '部门10');
    insert into departments (dept_no, dept_name)
    values ('d011', '部门11');
    insert into departments (dept_no, dept_name)
    values ('d012', '部门12');
    insert into departments (dept_no, dept_name)
    values ('d013', '部门13');
    
    commit; -- 提交事务（成功）
    
    rollback; -- 回滚（失败）
    
    set
    autocommit = 1; -- 开启自动提交

### 导出数据 ###

#### 导出数据 ####

***查看可导出到的文件夹***

-- 查看可导出到的文件夹
    show
    variables where Variable_name like '%secure_file_priv%';

***示例（导出数据）：***

select *
    from departments into outfile '/var/lib/mysql-files/departments.txt';

$ cat /var/lib/mysql-files/departments.txt

d009    Customer Service
    d005    Development
    d002    Finance
    d003    Human Resources
    d001    Marketing
    d004    Production
    d006    Quality Management
    d008    Research
    d007    Sales

***示例（导出数据）：***

select *
    from departments into outfile '/var/lib/mysql-files/departments.csv'
        fields terminated by ','
        enclosed by '"'
        lines terminated by '\r\n';

$ cat /var/lib/mysql-files/departments.csv

"d009","Customer Service"
    "d005","Development"
    "d002","Finance"
    "d003","Human Resources"
    "d001","Marketing"
    "d004","Production"
    "d006","Quality Management"
    "d008","Research"
    "d007","Sales"

***示例（导出数据）：***

select dept_no, dept_name, concat(dept_no, ' ', dept_name)
    into outfile '/var/lib/mysql-files/departments.csv'
        fields terminated by ','
        optionally enclosed by '"'
        lines terminated by '\n'
    from departments;

$ cat /var/lib/mysql-files/departments.csv

"d009","Customer Service","d009 Customer Service"
    "d005","Development","d005 Development"
    "d002","Finance","d002 Finance"
    "d003","Human Resources","d003 Human Resources"
    "d001","Marketing","d001 Marketing"
    "d004","Production","d004 Production"
    "d006","Quality Management","d006 Quality Management"
    "d008","Research","d008 Research"
    "d007","Sales","d007 Sales"

#### 导出表作为原始数 ####

***示例：***

$ mysqldump -u root -p --no-create-info --tab=/var/lib/mysql-files employees departments
    Enter password: ******

$ cat /var/lib/mysql-files/departments.sql

$ cat /var/lib/mysql-files/departments.txt

***示例：***

$ mysqldump -u root -p --no-create-info --tab=/var/lib/mysql-files employees
    Enter password: ******

$ cd /var/lib/mysql-files && ls
    current_dept_emp.sql  departments.txt  dept_emp.txt              dept_manager.sql  employees.sql  salaries.sql  titles.sql
    departments.sql       dept_emp.sql     dept_emp_latest_date.sql  dept_manager.txt  employees.txt  salaries.txt  titles.txt

#### 导出SQL格式的数据 ####

***示例：***

$ mysqldump -u root -p employees departments > /var/lib/mysql-files/departments.sql
    Enter password: ******
    
    $ cat /var/lib/mysql-files/departments.sql

-- MySQL dump 10.13  Distrib 8.0.33, for Linux (x86_64)
    --
    -- Host: localhost    Database: employees
    -- ------------------------------------------------------
    -- Server version       8.0.33
    
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!50503 SET NAMES utf8mb4 */;
    /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
    /*!40103 SET TIME_ZONE='+00:00' */;
    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
    
    --
    -- Table structure for table `departments`
    --
    
    DROP TABLE IF EXISTS `departments`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!50503 SET character_set_client = utf8mb4 */;
    CREATE TABLE `departments`
    (
        `dept_no`   char(4)     NOT NULL,
        `dept_name` varchar(40) NOT NULL,
        PRIMARY KEY (`dept_no`),
        UNIQUE KEY `dept_name` (`dept_name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Dumping data for table `departments`
    --
    
    LOCK
    TABLES `departments` WRITE;
    /*!40000 ALTER TABLE `departments` DISABLE KEYS */;
    INSERT INTO `departments`
    VALUES ('d009', 'Customer Service'),
           ('d005', 'Development'),
           ('d002', 'Finance'),
           ('d003', 'Human Resources'),
           ('d001', 'Marketing'),
           ('d004', 'Production'),
           ('d006', 'Quality Management'),
           ('d008', 'Research'),
           ('d007', 'Sales');
    /*!40000 ALTER TABLE `departments` ENABLE KEYS */;
    UNLOCK
    TABLES;
    /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
    
    /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
    /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
    /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
    
    -- Dump completed on 2023-07-17  8:40:55

### 导入数据 ###

#### 解决无法导入问题 ####

show
    variables where Variable_name like '%local_infile%';

+---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | local_infile  | OFF   |
    +---------------+-------+
    1 row in set (0.00 sec)

***修改配置文件`xxx.cnf`***

[mysqld]
    default-time_zone = '+8:00'
    local_infile = 1
    
    [mysql]
    local_infile = 1
    
    [client]
    local_infile = 1

***重启MySQL查看***

show
    variables where Variable_name like '%local_infile%';

+---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | local_infile  | ON    |
    +---------------+-------+
    1 row in set (0.00 sec)

#### 使用 LOAD DATA 导入数据 ####

***示例：***

load
    data local infile '/var/lib/mysql-files/departments.txt' into table departments;
    
    select *
    from departments;

+---------+--------------------+
    | dept_no | dept_name          |
    +---------+--------------------+
    | d009    | Customer Service   |
    | d005    | Development        |
    | d002    | Finance            |
    | d003    | Human Resources    |
    | d001    | Marketing          |
    | d004    | Production         |
    | d006    | Quality Management |
    | d008    | Research           |
    | d007    | Sales              |
    +---------+--------------------+
    9 rows in set (0.00 sec)

***示例：***

load
    data local infile '/var/lib/mysql-files/departments.csv' into table departments
        fields terminated by ','
        enclosed by '"'
        lines terminated by '\r\n';
    
    select *
    from departments;

[36feea452a7a434284ceba7c5bbf8128.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/f924d47edb7645b4a412556eb54e91f0.png