CPT103-Lecture 2

上一篇文章我们讲了许多MySQL的数据操作语言，但是看起来还是挺混乱的，所以我想将他们归纳一下。

数据定义语言

CREATE DATABASE 语法

我们使用 CREATE DATABASE 语句来创建数据库。以下是 CREATE DATABASE 语句的语法：

1
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] database_name

其中：

  • CREATE DATABASE 和 CREATE SCHEMA 的是一样的。
  • CREATE DATABASE 后指定要创建的数据库的名字。
  • IF NOT EXISTS 表示在指定的数据库不存在的情况下才创建。它是可选的。

DROP DATABASE 语法

我们使用 DROP DATABASE 语句来删除数据库：

1
DROP {DATABASE | SCHEMA} [IF EXISTS] database_name;

其中需要说明的点与 CREATE DATABASE 语句相同。

CREATE TABLE 语法

我们使用 CREATE TABLE 语句在数据库中创建一个新表。 CREATE TABLE 语句的语法如下：

1
2
3
4
5
6
CREATE TABLE [IF NOT EXISTS] table_name (
   column_name data_type [NOT NULL | NULL] [DEFAULT expr],
   column_name data_type [NOT NULL | NULL] [DEFAULT expr],
   ...,
   [table_constraints]
);

其中：

  • CREATE TABLE 语句创建一个给定名字 table_name 的表。
    • 表名可由字母、数字、下划线和美元符号组成，表名长度在 64 个字符以内。
    • 表名在一个数据库中是唯一的。
    • 新建的表会在当前默认的数据库中。如果还没有选择数据库，请使用 db_name.table_name 格式指定要新建的表所在的数据库。
  • IF NOT EXISTS 指示只有给定的表不存在的时候才进行创建。它是可选的。
    • 如果你给定一个已经存在的表名，又没有使用 IF NOT EXISTS 子句，服务器会返回一个错误。
  • column_name data_type [NOT NULL | NULL] [DEFAULT expr] [AUTO_INCREMENT] 定义了表中的一列。多个列使用逗号分隔。
    • column_name 是列的名字。列名可由字母、数字、下划线和美元符号组成，列名长度在 64 个字符以内。列名在一个表中是唯一的。
    • data_type 是数据类型，可以是 CHAR, VARCHAR, INT, DATE, DATETIME, BIT, TEXT, ENUM, JSON, BOLB 等。
    • [NOT NULL | NULL] 指示该列是否可以为 NULL。它是可选的。如果不指定该选项，则此列可以为 NULL。如果设置为 NOT NULL，则插入新行时该列必须有值。
    • [DEFAULT expr] 指示该列的默认值。它是可选的。如果不指定该选项，则此列的默认是 NULL。
    • [AUTO_INCREMENT] 指示该列是否是一个自增列。如果使用了此选项，则该列的值可有服务器自动产生和填充。该列的值从 1 开始，每增加一个行就会加 1。一个表中只能有一个自增列。
  • [table_constraints] 位于列定义之后，它定义了表的约束。它是可选的。表的约束有主键、外键、CHECK、UNIQUE 等。
  • 当表名或者字段名中含有空格或者其他特殊字字符时，可使用 ``` 包围起来。比如： test 1。（非标准语法）

DROP TABLE 语句语法

1
2
DROP TABLE [IF EXISTS]
  table_name [, table_name] ...

说明：

  • DROP TABLE 关键字后面是要删除的表名。如果要删除多个表，请使用逗号分隔表名。
  • IF EXISTS 选项避免了删除不存在的表时发生的错误。它是可选的。
    • 当要删除的表中有不存在的表时：
      • 有 IF EXISTS 选项，不会对不存在的表报错。该语句会删除存在的表，并给出不存在的表的提示。
      • 没有 IF EXISTS 选项，该语句运行失败带有一个指示不能移除不存在的表的错误。该语句不会删除任何表。
  • DROP TABLE 删除表的定义和表中的数据，以及表上触发器。
  • 你需要具有要删除的每一个表的 DROP 权限。

ALTER TABLE 语法

1
2
ALTER TABLE table_name
  [alter_action options], ...

其中 alter_action 是一个修改动作，包括：

  • ADD 关键字可用来添加列、索引、约束等，包括：
    • ADD [COLUMN]: 添加列
    • ADD INDEX: 添加索引
    • ADD PRIMARY KEY: 添加主键
    • ADD FOREIGN KEY: 添加外键
    • ADD UNIQUE INDEX: 添加唯一索引
    • ADD CHECK: 添加检查约束
  • DROP 关键字可用来删除列、索引、约束等，包括：
    • DROP [COLUMN] col_name: 删除列
    • ADD INDEX index_name: 删除索引
    • DROP PRIMARY KEY: 删除主键
    • DROP FOREIGN KEY fk_symbol: 删除外键
    • DROP CHECK symbol: 删除检查约束
  • MODIFY 关键字用来修改列的定义。与 CHANGE 关键字不同，它不能重命名列。例如: MODIFY [COLUMN] col_name column_definition。
  • CHANGE 关键字用来修改列的定义。与 MODIFY 关键字不同，它可以重命名列。例如: CHANGE [COLUMN] old_col_name new_col_name column_definition。
  • RENAME 关键字可以重命名列、索引和表。包括：
    • RENAME COLUMN old_col_name TO new_col_name: 重命名列。
    • RENAME INDEX old_index_name TO new_index_name: 重命名索引。
    • RENAME new_tbl_name: 重命名表。

数据操作语言

MySQL SELECT

1
2
SELECT columns_list
FROM table_name;

说明：

  • 关键字 SELECT 后跟着一个或多个数据表的列。
  • columns_list 可以有多个列，他们之间需要用逗号 , 分隔。
  • 当要检索数据表中的所有列的时候，使用 SELECT * FROM table_name 。
  • 关键字 FROM 后跟着要从中检索数据的表名。
  • 分号 ; 表示语句的结束，它是可选的。如果有两条或更多条语句，则需要使用分号 ; 将它们分开，以便 MySQL 单独执行每条语句。

SELECT是 MySQL 中用于读取数据的语言，相对来说挺重要的。

MySQL WHERE

WHERE 子句允许您为 SELECT 查询指定搜索条件。以下是 WHERE 子句的语法：

1
2
3
4
5
6
SELECT
    columns_list
FROM
    table_name
WHERE
    query_condition;

其中 query_condition 就是查询条件，它的结果是一个布尔值，其值可能为 TRUE， FALSE 或 UNKNOWN。最终， SELECT 语句返回的结果集就是满足查询条件结果为 TRUE 的记录。
查询条件一般用来比较某个字段是否匹配某个值，一般形式为：column_name = value
查询条件也可以是使用 AND , OR 和 NOT 逻辑运算符一个或多个表达式的组合。
除了用在 SELECT 语句之外， WHERE 子句还可以用在 UPDATE 和 DELETE 语句中，用来指定要更新或删除的行。

MySQL INSERT

我们可以使用一个 INSERT 语句插入一行或多行数据。基本插入语法为：

1
2
3
4
5
INSERT INTO table_name (column_1, column_2, ...)
VALUES (value_11, value_12, ...),
       (value_21, value_22, ...),
       ...;

  • 简单来说，就是使用INSERT INTO和VALUES关键字来插入数据。
  • INSERT INTO后跟表名，而后是插入数据的列名列表。列名放在小括号中，多个列表使用逗号分隔。
  • VALUES后跟需要存入的值，每个值列表使用小括号包围。值的数量要和字段的数量相同。值的位置和列的位置一一对应。
  • 当插入多行数据时，需要在除最后一个值列表的小括号后用,分割，最后一个值列表后以;结尾。
  • INSERT语句会返回输入的行数。
    1
    2
    3
    4
    5
    6
    7
    INSERT INTO tablename VALUES
    (12, '小马'),
    (18, '企鹅')；

    INSERT INTO tablename (column2, column1) VALUES
    ('小马', 12),
    ('企鹅', 18);

插入日期字段

上一篇中我们没有提到过关于日期字段的使用方式，所以来完善一下。
我们先创建一个新表：

1
2
3
4
5
6
CREATE TABLE user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT,
    birthday DATE
);

其中的birthday即为日期字段的数据。我们可以使用 YYYY-MM-DD 格式的文本字符串来插入日期，其中：

  • YYYY 代表四位数年份，例如 2020。
  • MM 代表两位数的月份，例如 01、02 和 12。
  • DD 表示两位数的日期，例如 01、02、30、31。
    1
    2
    INSERT INTO user (name, age, birthday)
    VALUES ('迟然', 20, '2004-07-16');
    我们可以使用SELECT * FROM user;这一 SQL 查询 user 表的数据，以验证是否成功插入：
    1
    2
    3
    4
    5
    6
    7
    +----+--------+------+------------+
    | id | name   | age  | birthday   |
    +----+--------+------+------------+
    |  1 | 迟然   |   20 | 2004-07-16 |
    |  2 | 小白   |   21 | 2003-12-24 |
    +----+--------+------+------------+
    2 rows in set (0.01 sec)
    大家可以发现，我并没有传入id这一数据，但是程序会自动为传入的数据编号。这是因为我在创建 table 的时候给 id 添加了AUTO_INCREMENT这一属性，我们叫它自增列。它会自动编号，并且每一次编号的比上一次大 1 。默认是从 1 开始增加，当然你也可以设置，就像这样：
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    DROP TABLE IF EXISTS user;
    CREATE TABLE user (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(100) NOT NULL,
      age INT,
      birthday DATE
    )AUTO_INCREMENT = 5;

    INSERT INTO user (name, age, birthday)
    VALUES ('迟然', 20, '2004-07-16');

    INSERT INTO user (name, age, birthday)
    VALUES ('小白', 21, '2003-12-24');
    我们使用了AUTO_INCREMENT = 5，如此一来，**id **就会从 5 开始增加。
    1
    2
    3
    4
    5
    6
    7
    +----+--------+------+------------+
    | id | name   | age  | birthday   |
    +----+--------+------+------------+
    |  5 | 迟然   |   20 | 2004-07-16 |
    |  6 | 小白   |   21 | 2003-12-24 |
    +----+--------+------+------------+
    2 rows in set (0.00 sec)

MySQL DELETE

使用DELET语句可以从该数据库表中删除记录行：

1
2
3
4
DELETE FROM table_name
[WHERE clause]
[ORDER BY ...]
[LIMIT row_count]

其中：

  • DELETE FROM 后跟的是要从中删除数据的表。
  • WHERE 子句用来过滤需要删除的行。满足条件的行会被删除。
  • WHERE 子句是可选的。没有 WHERE 子句时，DELETE 语句将删除表中的所有行。
  • ORDER BY 子句用来指定删除行的顺序。它是可选的。
  • LIMIT 子句用来指定删除的最大行数。它是可选的。
  • DELETE 语句返回删除的行数。
    1
    2
    DELETE FROM tablename
    WHERE column2 = '企鹅';

MySQL UPDATE

UPDATE 语句可以更新表中的一行或者多行数据，可以更新表中的一个或者多个字段（列）。 以下是 UPDATE 语句的基本语法：

1
2
3
4
5
6
UPDATE [IGNORE] table_name
SET
    column_name1 = value1,
    column_name2 = value2,
    ...
[WHERE clause];

其中：

  • UPDATE 关键字后指定要更新数据的表名。
  • 使用 SET 子句设置字段的新值。多个字段使用逗号分隔。字段的值可以是普通的字面值，也可以是表达式运算，还可以是子查询。
  • 使用 WHERE 子句指定要更新的行。只有符合 WHERE 条件的行才会被更新。
  • WHERE 子句是可选的。如果不指定 WHERE 子句，则更新表中的所有行。

UPDATE 语句中的 WHERE 子句非常重要。除非您特意，否则不要省略 WHERE 子句。

1
2
UPDATE tablename set column1 = 19
WHERE column2 = '小马';

添加约束

我们可以在创建 Table 的时候添加约束。约束可以添加在列名称的下方，也可以当作col-options添加。

1
2
3
4
5
6
7
8
CREATE TABLE name (
    col-name datatype [col-options], 
    :
    col-name datatype [col-options], 
    [constraint-1],
    :
    [constraint-2]
);

约束的基本语法是：CONSTRAINT name TYPE details;
建议创建约束名称，以便以后可以通过引用其名称来删除约束。

  • 如果你不提供一个名称，系统会自动生成一个。

MySQL 提供了以下约束类型：

  • PRIMARY KEY
  • UNIQUE
  • FOREIGN KEY
  • CHECK
  • NOT NULL
  • INDEX

CHECK约束

以CHECK约束为例：

  • 在创建表的时候在列定义中使用 CHECK 约束

    1
    2
    3
    4
    CREATE TABLE user (
        id INT AUTO_INCREMENT PRIMARY KEY,
        age INT NOT NULL CHECK(age > 0)
    );

  • 在创建表的时候在表上定义 CHECK 约束

    1
    2
    3
    4
    5
    CREATE TABLE user (
        id INT AUTO_INCREMENT PRIMARY KEY,
        age INT NOT NULL,
        CONSTRAINT CHECK(age > 0)
    );

  • 使用修改表语句的为表添加 CHECK 约束

    1
    2
    ALTER TABLE user
    ADD CONSTRAINT CHECK(age > 0);

    让我们来举个例子：

    1
    2
    3
    4
    5
    6
    7
    CREATE TABLE user (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(45) NOT NULL,
        login_name VARCHAR(45) NOT NULL CHECK(length(login_name) >= 4),
        password VARCHAR(45) NOT NULL CHECK(length(password) >= 8),
        CONSTRAINT CHECK(login_name <> password)
    );

    这里，在 CREATE TABLE 语句中有 3 个 CHECK 约束：

  • 在 login_name 列定义中的 CHECK(length(login_name) >= 4) 保证登录名的长度不小于 4。

  • 在 password 列定义中的 CHECK(length(password) >= 8) 保证登录名的长度不小于 8。

  • 在表上的约束 CONSTRAINT CHECK(login_name <> password) 保证密码不能和登录名相同。

我们可以使用SHOW CREATE TABLE user语句来查看表 **user **上的约束。

UNIQUE约束

在SQL中，UNIQUE约束是一种数据库表约束，它确保表中的特定列或列组合中的值是唯一的。这意味着对于任何两条不同的记录，这些列中的值都必须不同。
UNIQUE约束与PRIMARY KEY约束类似，但两者之间也有一些关键的区别。

  • PRIMARY KEY约束是唯一性约束的一种特殊类型，它还标识了表中的每条记录。这意味着每个表只能有一个PRIMARY KEY约束，并且该约束不能包含任何NULL值。
  • UNIQUE约束可以应用于表中的任何列或列组合，包括主键列。与PRIMARY KEY约束不同，UNIQUE约束可以包含NULL值。

UNIQUE约束可用于确保数据的完整性和准确性。例如，您可以使用UNIQUE约束来确保：

  • 每个客户都有唯一的客户ID。
  • 每个产品都有唯一的库存号。
  • 每个员工都有唯一的电子邮件地址。

如果尝试插入违反UNIQUE约束的值，则会导致错误。

添加外键

什么是外键

外键相对于主键而言，用来引用其他表。外键通过子表的一个或多个列对应到父表的主键或唯一键值，将子表的行和父表行建立起关联关系。
我们来定义一个外键：

1
2
3
4
CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`)
    REFERENCES `country` (`country_id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE

其中：

  • 位于 CONSTRAINT 关键字之后的 fk_city_country 是外键的名字。它是可选的。
  • 位于 FOREIGN KEY 关键字之后的是作为外键的列名。
  • 位于 REFERENCES 关键字之后的是被引用的表和列。
  • ON DELETE 和 ON UPDATE 指定了删除或更新被引用的表中的数据时要采取的约束策略。你可以使用以下 3 个策略中的一个：
    • CASCADE：如果被引用的表中的一行被删除或更新，该表中匹配行的值会自动删除或更新。
    • SET NULL：如果被引用的表中的一行被删除或更新，该表中匹配行的值设置为 NULL。
    • RESTRICT: 如果被引用的表中的一行在该表中有匹配的行，试图删除或更新被引用的表中行时会引发 MySQL 错误。这是默认的策略。

通常，外键所属的表被称作子表，被外键引用的表被称作父表。