MySQL学习之旅

MySQL 学习之旅

MySQL 基础操作

数据类型

  • 数值类型

    类型 大小 范围（有符号） 范围（无符号） 用途
    tinyint 1 Byte [-2^7, 2^7 − 1] [0, 2^8 − 1] 小整数值
    smallint 2 Bytes [-2^15, 2^15 − 1] [0, 2^16 − 1] 大整数值
    mediumint 3 Bytes [-2^23, 2^23 − 1] [0, 2^24 − 1] 大整数值
    int / integer 4 Bytes [-2^31, 2^31 − 1] [0, 2^32 − 1] 大整数值
    bigint 8 Bytes [-2^63, 2^63 − 1] [0, 2^64 − 1] 极大整数值
    float 4 Bytes [−(2 − 2^−23)·2^127, (2 − 2^−23)·2^127]；最小正正规数：2^−126 [0, (2 − 2^−23)·2^127]；最小正正规数：2^−126 单精度 浮点数值
    double 8 Bytes [−(2 − 2^−52)·2^1023, (2 − 2^−52)·2^1023]；最小正正规数：2^−1022 [0, (2 − 2^−52)·2^1023]；最小正正规数：2^−1022 双精度 浮点数值
    decimal (M,D) 依 DECIMAL(M,D) 取决于 M 与 D（十进制定点，非 2^k） 取决于 M 与 D（十进制定点，非 2^k） 小数值

    注：

    • 对整数类型，签名范围通式：有符号 [-2^(n−1), 2^(n−1)−1]，无符号 [0, 2^n−1]，其中 n 为位数（= 字节数 ×8）。
    • 对浮点类型，给出了 IEEE-754 的 2^ 指数表示；在 MySQL 中加 UNSIGNED 仅禁止负值，最大值不变。

  • 日期类型

    类型 大小 (bytes) 范围 格式 用途
    date 3 1000-01-01 / 9999-12-31 YYYY-MM-DD 日期值
    time 3 ‘-838:59:59’ / ‘838:59:59’ HH:MM:SS 时间值或持续时间
    year 1 1901 / 2155 YYYY 年份值
    datetime 8 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’ YYYY-MM-DD hh:mm:ss 混合日期和时间值
    timestamp 4 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC
    结束时间是第 2147483647 秒；北京时间 2038-01-19 11:14:07；格林尼治时间 2038-01-19 03:14:07    		 YYYY-MM-DD hh:mm:ss 混合日期和时间值、时间戳

  • 字符串类型

    类型 大小 用途
    char 0–255 bytes 定长字符串
    varchar 0–65535 bytes 变长字符串
    tinyblob 0–255 bytes 不超过 255 个字符的二进制字符串
    tinyblob 0–255 bytes 短文本字符串
    blob 0–65535 bytes 二进制形式的长文本数据
    text 0–65535 bytes 长文本数据
    mediumblob 0–16777215 bytes 二进制形式的中等长度文本数据
    mediumtext 0–16777215 bytes 中等长度文本数据
    longblob 0–4294967295 bytes 二进制形式的极大文本数据
    longtext 0–4294967295 bytes 极大文本数据

    注意：

    • char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

    • CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。
      它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

    • BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，
      不同的是它们包含二进制字符串而不要非二进制字符串。
      它们包含字节字符串而不是字符字符串。
      这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

    • 有 4 种 BLOB 类型：
      TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。
      它们区别在于可容纳存储范围不同。

    • 有 4 种 TEXT 类型：
      TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。
      对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

字段属性

  • 列属性

    在创建及修改表格时给指定列数据赋予的属性

    关键字 含义
    NULL 数据列允许包含 NULL 值
    NOT NULL 数据列不允许包含 NULL 值
    DEFAULT 默认值
    PRIMARY KEY 主键
    AUTO_INCREMENT 自动递增，适用于整数类型
    UNSIGNED 无符号
    CHARACTER SET name 指定一个字符集

常用命令

  • 连接数据库

    1
    mysql -h <host address> -P <port> -u <user name> －p <user password>

  • 创建数据库

    1
    create database <SQL name>;

  • 显示数据库

    1
    show databases

  • 删除数据库

    1
    drop database <SQL name>;

  • 连接数据库（设该数据库为默认数据库并对其进行操作）

    1
    use <SQL name>;

  • 创建表格

    1
    2
    3
    4
    5
    create table <table_name> (
      <column1> <datatype> <属性（可无）>,
      <column2> <datatype> <属性（可无）>,
      ...
    );

    column: 列名
    datatype: 每列的数据类型，参考数据类型部分
    属性: 数据的属性，参考字符属性部分

  • 删除表格

    删除整个表格：

    1
    2
    DROP TABLE <table_name>;     -- 直接删除表，不检查是否存在
    DROP TABLE IF EXISTS <table_name>;  -- 会检查是否存在，如果存在则删除

    仅删除表格内的数据，不删除表格结构：

    1
    TRUNCATE TABLE <table_name>;

  • 插入数据

    通用：

    1
    2
    INSERT INTO <table_name> (column1, column2, column3, ...)
    VALUES (value1, value2, value3, ...);

    column: 列名
    value1: 要插入的具体数值（和列的数据类型对应）

    如果数据是字符型，必须使用单引号 ‘ 或者双引号 “
    如： ‘value’, “value”

    如果要插入所有列的数据，可以省略列名：

    1
    2
    INSERT INTO <table_name>
    VALUES (value1, value2, value3, ...);

    如果要插入多行数据，可以在 VALUES 子句中指定多组数值：

    1
    2
    3
    4
    5
    INSERT INTO <table_name> (username, email, birthdate, is_active)
    VALUES
      ('test1', 'test1@runoob.com', '1985-07-10', true),
      ('test2', 'test2@runoob.com', '1988-11-25', false),
      ('test3', 'test3@runoob.com', '1993-05-03', true);

  • 查询数据

    1
    2
    3
    4
    5
    SELECT column1, column2, ...
    FROM <table_name>
    [WHERE <condition>]                       -- 可选，用于指定过滤条件，只返回符合条件的行
    [ORDER BY <column_name> [<ASC / DESC>]]     -- 可选，用于指定结果集的排序顺序，默认是升序（ASC）
    [LIMIT <number>];                         -- 可选，用于限制返回的行数

    示例：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    -- 选择所有列的所有行
    SELECT * FROM users;

    -- 选择特定列的所有行
    SELECT username, email FROM users;

    -- 添加 WHERE 子句，选择满足条件的行
    SELECT * FROM users WHERE is_active = TRUE;

    -- 添加 ORDER BY 子句，按照某列的升序排序
    SELECT * FROM users ORDER BY birthdate;

    -- 添加 ORDER BY 子句，按照某列的降序排序
    SELECT * FROM users ORDER BY birthdate DESC;

    -- 添加 LIMIT 子句，限制返回的行数
    SELECT * FROM users LIMIT 10;

  • 更新\修改表格

    1
    2
    3
    UPDATE <table_name>
    SET column1 = value1, column2 = value2, ...
    [WHERE <condition>];

    WHERE condition 是用于指定过滤条件的子句
    如: WHERE id = 1;

  • 删除行

    删除指定行：

    1
    2
    DELETE FROM <table_name>
    WHERE condition;

    删除所有行：

    1
    DELETE FROM <table_name>;

    使用子查询删除符合条件的行：

    1
    2
    3
    4
    5
    6
    DELETE FROM customers
    WHERE customer_id IN (
      SELECT customer_id
      FROM orders
      WHERE order_date < '2023-01-01'
    );

  • 添加列

    1
    2
    ALTER TABLE <table_name>
    ADD COLUMN <new column_name> <datatype>;

  • 修改列的数据类型

    1
    2
    ALTER TABLE <table_name>
    MODIFY COLUMN <column_name> <new datatype>;

  • 修改列名

    1
    2
    ALTER TABLE <table_name>
    CHANGE COLUMN <old column_name> <new column_name> <datatype>;

  • 删除列

    1
    2
    ALTER TABLE <table_name>
    DROP COLUMN <column_name>;

  • 添加主键

    1
    2
    ALTER TABLE <table_name>
    ADD PRIMARY KEY (<column_name>);

  • 修改表名

    1
    2
    ALTER TABLE <old table_name>
    RENAME TO <new table_name>;

常用子句

  • WHERE 子句

    WHERE <condition>; 用于指定过滤条件的子句，condition 为条件

    以下将会列出与其他编程语言不同的条件：

    • 相等条件

      1
      WHERE username = 'test';

      与其他编程语言不同，**’ = ‘** 即代表判断是否相等，而非 ‘ == ‘

    • IN 条件

      1
      WHERE country_code IN ('US', 'CA', 'MX');

    • BETWEEN 条件

      1
      WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

    • 模糊匹配条件（LIKE）:

      1
      WHERE first_name LIKE 'J%';

      该条件仅为举例，具体请看 LIKE 语句部分

  • LIKE 子句

    LIKE 子句是用于 WHERE 子句中进行模糊匹配的关键字。
    它通常与通配符一起使用，用于搜索符合某种模式的字符串。
    LIKE 子句中使用百分号 % 字符来表示任意字符，类似于 UNIX 或正则表达式中的星号 *。
    如果没有使用百分号 % , LIKE 子句与等号 = 的效果是一样的。

    • 可以在 WHERE 子句中指定任何条件。

    • 可以在 WHERE 子句中使用 LIKE 子句。

    • 可以使用 LIKE 子句代替等号 =。

    • LIKE 通常与 % 一同使用，类似于一个元字符的搜索。

    • 可以使用 AND 或者 OR 指定一个或多个条件。

    • 可以在 DELETE 或 UPDATE 命令中使用 WHERE…LIKE 子句来指定条件。

    示例：

    • 百分号通配符 %：

      % 通配符表示零个或多个字符。

      例如，’S%’ 匹配以字母 ‘S’ 开头的任何字符串。

      1
      SELECT * FROM customers WHERE last_name LIKE 'S%';

    • 下划线通配符 _：

      _ 通配符表示一个字符。

      例如，’_r%’ 匹配第二个字母为 ‘r’ 的任何字符串。

      1
      SELECT * FROM products WHERE product_name LIKE '_r%';

    • 组合使用 % 和 _：

      匹配以字母 ‘a’ 开头，然后是零个或多个字符，接着是 ‘o’，最后是一个任意字符的字符串，
      即以字母 ‘a’ 开头， 倒数第二个字符是 ‘o’，最后是一个任意字符的字符串，如 ‘aaron’、’apol’。

      1
      SELECT * FROM users WHERE username LIKE 'a%o_';

    • 不区分大小写：

      选择姓氏以 ‘smi’ 开头的所有字符串，不区分大小写。

      1
      SELECT * FROM employees WHERE last_name LIKE 'smi%' COLLATE utf8mb4_general_ci;

  • UPDATE 子句

    用于更新或修改表格中的数据

    1
    2
    3
    4
    5
    UPDATE <table_name>
    SET column1 = value1,
        column2 = value2,
        ...
    WHERE <condition>;

    可以直接赋值修改，也可以使用表达式修改，如：

    1
    2
    3
    UPDATE products
    SET price = price * 1.1
    WHERE category = 'Electronics';

    这里就将 ‘Electronics’ 类别的 ‘price’ 属性全部变为了 1.1 倍大

  • ORDER BY(排序) 语句

    按照一个或多个列的值进行升序（ASC）或降序（DESC）排序

    1
    2
    3
    4
    5
    SELECT column1, column2, ...
    FROM <table_name>
    ORDER BY column1 [ASC | DESC],
             column2 [ASC | DESC],
             ...;

  • UNION 操作符

    用于连接两个以上的 SELECT 语句的结果组合到一个结果集合，
    必须由两个或多个 SELECT 语句组成，每个 SELECT 语句的列数和对应位置的数据类型必须相同。

    1
    2
    3
    4
    5
    6
    7
    8
    SELECT column1, column2, ...
    FROM table1
    WHERE condition1
    UNION
    SELECT column1, column2, ...
    FROM table2
    WHERE condition2
    [ORDER BY column1, column2, ...];

Python 联合使用 MySQL