MySQL创建和管理表

Posted on Edited on In , Views: Word count in article: 8.6k Reading time ≈ 8 mins.

1、基础知识

1.1 一条数据存储的过程

  • 存储数据是处理数据的第一步。只有正确地把数据存储起来，我们才能进行有效的处理和分析。否则，只能是一团乱麻，无从下手。

  • 那么，怎样才能把用户各种经营相关的、纷繁复杂的数据，有序、高效地存储起来呢？在MySQL中， 一个完整的数据存储过程总共有 4 步，分别是创建数据库、确认字段、创建数据表、插入数据。

  • 从系统架构的层次上看，MySQL数据库系统从大到小依次是数据库服务器、数据库、数据表、数据表的行与列。

1.2 标识符命名规则

  • 数据库名、表名不得超过30个字符，变量名限制为29个。
  • 必须只能包含A–Z,a–z,0–9,_共63个字符。
  • 数据库名、表名、字段名等对象名中间不要包含空格。
  • 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名。
  • 必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使 用`（着重号）引起来。
  • 保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了。

1.3 MySQL中的数据类型

  • 数据类型如下：

    • 其中，常用的几类类型介绍如下：

2、创建和管理数据库

2.1 创建数据库

  • 方式1：创建数据库。

    1
    2
    3
    4
    CREATE DATABASE 数据库名; # 创建的此数据库使用的是默认的字符集，默认的字符集通过show variables like 'character_%';查看

    # 查看创建数据库的结构
    SHOW CREATE DATABASE 数据库名;

  • 方式2：创建数据库并指定字符集。

    1
    CREATE DATABASE 数据库名 CHARACTER SET 字符集;

  • 方式3：判断数据库是否已经存在，不存在则创建数据库（ 推荐 ） 

    1
    CREATE DATABASE IF NOT EXISTS 数据库名;

  • 如果MySQL中已经存在相关的数据库，则忽略创建语句，不再创建数据库。

注意：DATABASE不能改名。一些可视化工具可以改名，它是建新库，把所有表复制到新库，再删旧库完成的。

2.2 使用数据库

  • 查看当前所有的数据库。

    1
    SHOW DATABASES; # 有一个S，代表多个数据库

  • 查看当前正在使用的数据库。

    1
    SELECT DATABASE(); # 使用的一个mysql中的全局函数

  • 查看指定库下所有的表。

    1
    SHOW TABLES FROM 数据库名;

  • 查看数据库的创建信息。

    1
    2
    3
    SHOW CREATE DATABASE 数据库名;
    # 或者
    SHOW CREATE DATABASE 数据库名\G

  • 使用/切换数据库。

    1
    USE 数据库名;

注意：要操作表格和数据之前必须先说明是对哪个数据库进行操作，否则就要对所有对象加上“数据库名.”。

2.3 修改数据库

  • 更改数据库字符集。

    1
    ALTER DATABASE 数据库名 CHARACTER SET 字符集; # 比如：gbk、utf8等

2.4 删除数据库

  • 方式1：删除指定的数据库。

    1
    DROP DATABASE 数据库名;

  • 方式2：删除指定的数据库（ 推荐 ） 

    1
    DROP DATABASE IF EXISTS 数据库名;

3、创建和操作表

3.1 创建表

3.1.1 创建方式1

  • 必须具备：

    • CREATE TABLE权限。
    • 存储空间。

  • 语法格式：

    1
    2
    3
    4
    5
    6
    7
    CREATE TABLE [IF NOT EXISTS] 表名(
    字段1, 数据类型 [约束条件] [默认值],
    字段2, 数据类型 [约束条件] [默认值],
    字段3, 数据类型 [约束条件] [默认值],
    ……
    [表约束条件]
    );

    加上了IF NOT EXISTS关键字，则表示：如果当前数据库中不存在要创建的数据表，则创建数据表； 如果当前数据库中已经存在要创建的数据表，则忽略建表语句，不再创建数据表。

  • 必须指定：

    • 表名。
    • 列名(或字段名)，数据类型，长度。

  • 可选指定：

    • 约束条件。
    • 默认值。

  • 创建表举例：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    CREATE TABLE IF NOT EXISTS myemp1(   # 需要用户具备创建表的权限。
    id INT,
    emp_name VARCHAR(15), # 使用VARCHAR来定义字符串，必须在使用VARCHAR时指明其长度。
    hire_date DATE
    );

    # 查看表结构
    DESC myemp1;

    # 查看创建表的语句结构
    SHOW CREATE TABLE myemp1; # 如果创建表时没有指明使用的字符集，则默认使用表所在的数据库的字符集。

    # 查看表数据
    SELECT * FROM myemp1;

3.1.2 创建方式2

  • 基于现有的表，同时导入数据。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    CREATE TABLE myemp2
    AS
    SELECT employee_id,last_name,salary
    FROM employees;

    DESC myemp2;
    DESC employees;

    SELECT *
    FROM myemp2;
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    # 说明1：查询语句中字段的别名，可以作为新创建的表的字段的名称。
    # 说明2：此时的查询语句可以结构比较丰富，使用前面章节讲过的各种SELECT
    CREATE TABLE myemp3
    AS
    SELECT e.employee_id emp_id,e.last_name lname,d.department_name
    FROM employees e JOIN departments d
    ON e.department_id = d.department_id;

    SELECT *
    FROM myemp3;

    DESC myemp3;
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    # 创建一个表employees_copy，实现对employees表的复制，包括表数据
    CREATE TABLE employees_copy
    AS
    SELECT *
    FROM employees;

    SELECT * FROM employees_copy;

    # 创建一个表employees_blank，实现对employees表的复制，不包括表数据
    CREATE TABLE employees_blank
    AS
    SELECT *
    FROM employees
    # where department_id > 10000;
    WHERE 1 = 2;

    SELECT * FROM employees_blank;

3.2 修改表

3.2.1 追加一个列

  • 语法格式如下：

    1
    ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;

  • 例子：

    1
    2
    3
    4
    5
    6
    7
    8
    ALTER TABLE myemp1
    ADD salary DOUBLE(10,2); # 默认添加到表中的最后一个字段的位置

    ALTER TABLE myemp1
    ADD phone_number VARCHAR(20) FIRST;

    ALTER TABLE myemp1
    ADD email VARCHAR(45) AFTER emp_name;

3.2.2 修改一个列

  • 可以修改列的数据类型，长度、默认值和位置。

  • 修改字段数据类型、长度、默认值、位置的语法格式如下：

    1
    2
    ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名
    2】;

  • 例子：

    1
    2
    3
    4
    5
    ALTER TABLE myemp1
    MODIFY emp_name VARCHAR(25) ;

    ALTER TABLE myemp1
    MODIFY emp_name VARCHAR(35) DEFAULT 'aaa';

3.2.3 重命名一个列

  • 使用CHANGE old_column new_column dataType子句重命名列。语法格式如下：

    1
    ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;

  • 例子：

    1
    2
    3
    4
    5
    ALTER TABLE myemp1
    CHANGE salary monthly_salary DOUBLE(10,2);

    ALTER TABLE myemp1
    CHANGE email my_email VARCHAR(50); # 可以在修改名称的同时修改类型和长度

3.2.4 删除一个列

  • 删除表中某个字段的语法格式如下：

    1
    ALTER TABLE 表名 DROP 【COLUMN】字段名

  • 例子：

    1
    2
    ALTER TABLE myemp1
    DROP COLUMN my_email;

3.3 重命名表

  • 方式一：使用RENAME。

    1
    2
    RENAME TABLE myemp1
    TO myemp11;

  • 方式二：

    1
    2
    ALTER TABLE myemp2
    RENAME TO myemp12; # TO可以省略

3.4 删除表

  • 在MySQL中，当一张数据表没有与其他任何数据表形成关联关系时，可以将当前数据表直接删除。

  • 数据和结构都被删除。

  • 所有正在运行的相关事务被提交。

  • 所有相关索引被删除。

  • 语法格式：

    1
    DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];

  • 例子：

    1
    2
    3
    4
    5
    DROP TABLE IF EXISTS myemp2;

    DROP TABLE IF EXISTS myemp12;

    # DROP TABLE 语句不能回滚

  • 对比TRUNCATE TABLE和DELETE FROM。

    • 相同点：都可以实现对表中所有数据的删除，同时保留表结构。
    • 不同点：
      • TRUNCATE TABLE：一旦执行此操作，表数据全部清除。同时，数据是不可以回滚的。
      • DELETE FROM：一旦执行此操作，表数据可以全部清除（不带WHERE）。同时，数据是可以实现回滚的。

3.5 清空表

  • TRUNCATE TABLE语句：

    • 删除表中所有的数据。
    • 释放表的存储空间。

  • 例子：

    1
    2
    3
    # 清空表，表示清空表中的所有数据，但是表结构保留。
    # TRUNCATE语句不能回滚，而使用 DELETE 语句删除数据，可以回滚。
    TRUNCATE TABLE employees_copy;

4、DCL中COMMIT和ROLLBACK

  • DDL和DML的说明。

    • DDL的操作一旦执行，就不可回滚。指令SET autocommit = FALSE对DDL操作失效。(因为在执行完DDL操作之后，一定会执行一次COMMIT。而此COMMIT操作不受SET autocommit = FALSE影响的)
    • DML的操作默认情况，一旦执行，也是不可回滚的。但是，如果在执行DML之前，执行了SET autocommit = FALSE，则执行的DML操作就可以实现回滚。

  • 例子：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    # DELETE FROM 
    #1)
    COMMIT;
    #2)
    SELECT *
    FROM myemp3;
    #3)
    SET autocommit = FALSE;
    #4)
    DELETE FROM myemp3;
    #5)
    SELECT *
    FROM myemp3;
    #6)
    ROLLBACK;
    #7)
    SELECT *
    FROM myemp3; # 数据成功回滚

    # TRUNCATE TABLE
    #1)
    COMMIT;
    #2)
    SELECT *
    FROM myemp3;
    #3)
    SET autocommit = FALSE;
    #4)
    TRUNCATE TABLE myemp3;
    #5)
    SELECT *
    FROM myemp3;
    #6)
    ROLLBACK;
    #7)
    SELECT *
    FROM myemp3; # 数据回滚失败

阿里开发规范：

【参考】TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无 事务且不触发 TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句。

说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

  • 在MySQL 8.0版本中，InnoDB表的DDL支持事务完整性，即DDL操作要么成功要么回滚。DDL操作回滚日志 写入到data dictionary数据字典表mysql.innodb_ddl_log（该表是隐藏的表，通过show tables无法看到） 中，用于回滚操作。通过设置参数，可将DDL操作日志打印输出到MySQL错误日志中。

    • COMMIT：提交数据。一旦执行COMMIT，则数据就被永久的保存在了数据库中，意味着数据不可以回滚。
    • ROLLBACK：回滚数据。一旦执行ROLLBACK,则可以实现数据的回滚。回滚到最近的一次COMMIT之后。
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    # 测试MySQL8.0的新特性：DDL的原子化
    CREATE DATABASE mytest;

    USE mytest;

    CREATE TABLE book1(
    book_id INT ,rr
    book_name VARCHAR(255)
    );

    SHOW TABLES;

    DROP TABLE book1,book2; # book2不存在

    SHOW TABLES; # 发现book1表仍然存在

5、数据处理之增删改

5.1 插入数据

  • 方式1：VALUES的方式添加。使用这种语法一次只能向表中插入一条数据。

    • 情况1：为表的所有字段按默认顺序插入数据，格式与案例如下：

      1
      2
      3
      # 值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。
      INSERT INTO 表名
      VALUES (value1,value2,....);
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      CREATE TABLE IF NOT EXISTS emp1(
      id INT,
      `name` VARCHAR(15),
      hire_date DATE,
      salary DOUBLE(10,2)
      );

      # 正确的
      INSERT INTO emp1
      VALUES (1,'Tom','2000-12-21',3400); # 注意：一定要按照声明的字段的先后顺序添加
      # 错误的
      INSERT INTO emp1
      VALUES (2,3400,'2000-12-21','Jerry');

    • 情况2：为表的指定字段插入数据，格式与案例如下：

      1
      2
      INSERT INTO 表名(column1 [, column2, …, columnn])
      VALUES (value1 [,value2, …, valuen]);
      1
      2
      3
      4
      5
      INSERT INTO emp1(id,hire_date,salary,`name`)
      VALUES(2,'1999-09-09',4000,'Jerry');
      # 说明：没有进行赋值的hire_date 的值为 null
      INSERT INTO emp1(id,salary,`name`)
      VALUES(3,4500,'shk');

    • 情况3：同时插入多条记录。INSERT语句可以同时向数据表中插入多条记录，插入时指定多个值列表，每个值列表之间用逗号分隔开，基本语法格式与案例如下： 

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      INSERT INTO table_name
      VALUES
      (value1 [,value2, …, valuen]),
      (value1 [,value2, …, valuen]),
      ……
      (value1 [,value2, …, valuen]);
      # 或者
      INSERT INTO table_name(column1 [, column2, …, columnn])
      VALUES
      (value1 [,value2, …, valuen]),
      (value1 [,value2, …, valuen]),
      ……
      (value1 [,value2, …, valuen]);
      1
      2
      3
      4
      INSERT INTO emp1(id,NAME,salary)
      VALUES
      (4,'Jim',5000),
      (5,'张俊杰',5500);

      一个同时插入多行记录的INSERT语句等同于多个单行插入的INSERT语句，但是多行的INSERT语句 在处理过程中效率更高。因为MySQL执行单条INSERT语句插入多行数据比使用多条INSERT语句 快，所以在插入多条记录时最好选择使用单条INSERT语句的方式插入。

      注意：

      • VALUES也可以写成VALUE，但是VALUES是标准写法。

      • 字符和日期型数据应包含在单引号中。

  • 方式2：将查询结果插入到表中。INSERT还可以将SELECT语句查询的结果插入到表中，此时不需要把每一条记录的值一个一个输入，只需要使用一条INSERT语句和一条SELECT语句组成的组合语句即可快速地从一个或多个表中向一个表中插入多行。基本语法格式与案例如下：

    1
    2
    3
    4
    5
    6
    INSERT INTO 目标表名
    (tar_column1 [, tar_column2, …, tar_columnn])
    SELECT
    (src_column1 [, src_column2, …, src_columnn])
    FROM 源表名
    [WHERE condition]
    1
    2
    3
    4
    5
    6
    7
    # 说明：emp1表中要添加数据的字段的长度不能低于employees表中查询的字段的长度。
    # 如果emp1表中要添加数据的字段的长度低于employees表中查询的字段的长度的话，就有添加不成功的风险。
    INSERT INTO emp1(id,NAME,salary,hire_date)
    # 查询语句
    SELECT employee_id,last_name,salary,hire_date  # 查询的字段一定要与添加到的表的字段一一对应
    FROM employees
    WHERE department_id IN (70,60);

5.2 更新数据

  • 使用UPDATE语句更新数据。语法如下： 

    1
    2
    3
    UPDATE table_name
    SET column1=value1, column2=value2, … , column=valuen
    [WHERE condition]
    • 可以一次更新多条数据。
    • 如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;

  • 例子：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    # 修改一条数据的单个字段
    UPDATE emp1
    SET hire_date = CURDATE()
    WHERE id = 5;

    # 同时修改一条数据的多个字段
    UPDATE emp1
    SET hire_date = CURDATE(),salary = 6000
    WHERE id = 4;

    # 将表中姓名中包含字符a的提薪20%
    UPDATE emp1
    SET salary = salary * 1.2
    WHERE NAME LIKE '%a%';

    # 修改数据时，是可能存在不成功的情况的。（可能是由于约束的影响造成的）
    UPDATE employees
    SET department_id = 10000
    WHERE employee_id = 102;

5.3 删除数据

  • 使用DELETE语句从表中删除数据，语法如下：

    1
    DELETE FROM table_name [WHERE <condition>];

  • 例子：

    1
    2
    3
    4
    5
    6
    7
    8
    DELETE FROM emp1
    WHERE id = 1;

    # 在删除数据时，也有可能因为约束的影响，导致删除失败
    DELETE FROM departments
    WHERE department_id = 50;
    # 小结：DML操作默认情况下，执行完以后都会自动提交数据。
    # 如果希望执行完以后不自动提交数据，则需要使用 SET autocommit = FALSE。

6、MySQL8新特性：计算列

  • 什么叫计算列呢？简单来说就是某一列的值是通过别的列计算得来的。例如，a列值为1、b列值为2，c列不需要手动插入，定义a+b的结果为c的值，那么c就是计算列，是通过别的列计算得来的。

  • 在MySQL 8.0中，CREATE TABLE和ALTER TABLE中都支持增加计算列。下面以CREATE TABLE为例进行讲解。 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    # 定义数据表tb1，然后定义字段id、字段a、字段b和字段c，其中字段c为计算列，用于计算a+b的值。
    CREATE TABLE tb1(
    id INT,
    a INT,
    b INT,
    c INT GENERATED ALWAYS AS (a + b) VIRTUAL
    );

    # 插入数据
    INSERT INTO tb1(a,b) VALUES (100,200);

    # 查询数据
    mysql> SELECT * FROM tb1;
    +------+------+------+------+
    | id   | a    | b    | c    |
    +------+------+------+------+
    | NULL |  100 |  200 |  300 |
    +------+------+------+------+
    1 row in set (0.00 sec)

    # 更新数据
    mysql> UPDATE tb1 SET a = 500;
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

    mysql> SELECT * FROM tb1;
    +------+------+------+------+
    | id   | a    | b    | c    |
    +------+------+------+------+
    | NULL |  500 |  200 |  700 |
    +------+------+------+------+
    1 row in set (0.00 sec)

    # 修改计算列会报错
    mysql> UPDATE tb1 SET c = 500;
    ERROR 3105 (HY000): The value specified for generated column 'c' in table 'tb1' is not allowed.