MySQL 入门(一)——基本操作

MySQL 是最流行的关系型数据库管理系统,应用广泛。

数据库

  • 查看所有数据库
    1
    SHOW DATABASES;
  • 创建数据库

    1
    CREATE DATABASE database_name;
  • 删除数据库

    1
    DROP DATABASE database_name;
  • 选择数据库

    1
    USE database_name;

数据类型

MySQL 支持多种数据类型,大致可以分为四类:数值类型、日期和时间类型、字符串类型和 JSON 类型。

数值类型

整型

类型 存储大小(字节) 范围 范围(无符号) 备注
TINYINT 1 $(-128,127)$ $(0,255)$
SMALLINT 2 $(-32768,32767)$ $(0,65535)$
MEDIUMINT 3 $(-8388608,8388607)$ $(0,16777215)$
INT 4 $(-2147483648,2147483647)$ $(0,4294967295)$
BIGINT 8 $(-2^{63},2^{63}-1)$ $(0,2^{64}-1)$
DECIMAL(M, D) M 表示总的位数,D 表示小数位数。如 DECIMAL(5, 2) 能表示 $[-999.99,999.99]$ 之内的任意数字。缺省情况下 M=10,D=0

浮点型

类型 备注
FLOAT 单精度浮点数
DOUBLE 双精度浮点数

在 MySQL 8.0.17 之后,推荐直接使用 FLOATDOUBLE 关键字,无需指定精度。

日期和时间类型

类型 存储大小(字节) 范围 格式
DATE 3 [0000-01-01, 9999-12-31] YYYY-MM-DD
TIME 3+0/1/2/3 [-838:59:59, 838:59:59] HH:MM:SS
YEAR 1 [1901, 2155] YYYY
DATETIME 5+0/1/2/3 [0000-01-01 00:00:00, 9999-12-31 23:59:59] YYYY-MM-DD HH:MM:SS
TIMESTAMP 4+0/1/2/3 [1970-01-01 00:00:00, 2038-01-19 11:14:07] YYYY-MM-DD HH:MM:SS

其中,与时间有关的类型,存储所需字节数取决于定义数据表时该字段的设置。可在对应的类型后面添加括号来设置秒数可以精确到多少位,默认为 0。而对应位数所需占用的字节数如下:

秒数精度 存储大小(字节)
0 0
1/2 1
3/4 2
5/6 3

注:DATATIMETIMESTAMP 的区别不仅限于表示范围的不同,TIMESTAMP 会自动将客户端插入的时间转换为 UTC(世界标准时间)进行存储,查询时又转换为当前的时区(即不同时区拿出的时间可能会不同)。

字符串类型

类型 存储大小(字节) 备注
CHAR(M) 定长字符串,M 可取值 $[0,255]$,表示最大可存储的字符数。若存储的内容长度小于 M,则补空格。其占用字节由编码决定
VARCHAR(M) [0, 65535] 变长字符串,M 可取值由编码决定。如 utf8 编码下,M 最大可取 21844
TINYBLOB/TINYTEXT [0, 255]
BLOB/TEXT [0, 65535] 约 64KB
MEDIUMBLOB/MEDIUMTEXT [0, 16777215] 约 16MB
LONGBLOB/LONGTEXT [0, 4294967295] 约 4GB

注:

  • 汉字所占的字节与编码有关,utf8 编码下一个汉字占 3 个字节,gbk 编码下一个汉字占 2 个字节。
  • BLOB 为二进制字符串。

JSON 类型

MySQL 支持 JSON 数组与 JSON 对象,分别使用如下方式插入数据:

  • JSON 数组
    1
    2
    3
    INSERT INTO table_name VALUES('["Tom", 18, "male"]');
    # 或者
    INSERT INTO table_name VALUES(JSON_ARRAY("Tom", 18, "male"));
  • JSON 对象
    1
    2
    3
    INSERT INTO table_name VALUES('{"name": "Tom", "age": 18, "gender": "male"}');
    # 或者
    INSERT INTO table_name VALUES(JSON_OBJECT("name", "Tom", "age", 18, "gender", "male"));

数据表

选择数据库之后,可以进行该数据库下数据表的操作。

  • 查看所有数据表

    1
    SHOW TABLES;
  • 创建数据表

    1
    2
    3
    4
    5
    6
    CREATE TABLE table_name(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    field1 data_type1 NOT NULL DEFAULT default_value COMMENT="",
    field2 data_type2 NOT NULL COMMENT="",
    PRIMARY KEY(id)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT="";
  • 删除数据表

    1
    DROP TABLE table_name;
  • 查看数据表结构

    1
    2
    3
    DESC table_name;
    # 完整的
    SHOW FULL COLUMNS FROM table_name;
  • 修改数据表
    已有的 test 表结构:

    1
    2
    3
    4
    5
    6
    7
    8
    mysql> DESC test;
    +-------+---------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +-------+---------+------+-----+---------+-------+
    | i | int(11) | YES | | NULL | |
    | c | char(1) | YES | | NULL | |
    +-------+---------+------+-----+---------+-------+
    2 rows in set (0.00 sec)
    • 删除字段

      1
      2
      # 删除字段 c
      ALTER TABLE test DROP c;
    • 添加字段

      1
      2
      3
      4
      5
      6
      # 默认在末尾添加字段
      ALTER TABLE test ADD vc VARCHAR(100) NOT NULL DEFAULT "hh";
      # 使用 FIRST 关键字在开头添加字段
      ALTER TABLE test ADD vc VARCHAR(100) NOT NULL DEFAULT "hh" FIRST;
      # 使用 AFTER 关键字在某个字段后面添加字段
      ALTER TABLE test ADD vc VARCHAR(100) NOT NULL DEFAULT "hh" AFTER i;
    • 修改字段

      1
      2
      3
      4
      5
      6
      7
      8
      # 使用 MODIFY 关键字修改列的属性(类型、约束)
      ALTER TABLE test MODIFY c CHAR(10) NOT NULL DEFAULT "default_c";
      # 使用 CHANGE 关键字重新定义列
      ALTER TABLE test CHANGE c vc VARCHAR(100) NOT NULL DEFAULT "default_vc";
      # 修改字段默认值
      ALTER TABLE test ALTER i SET DEFAULT 10;
      # 删除字段默认值
      ALTER TABLE test ALTER i DROP DEFAULT;
    • 修改表属性

      1
      2
      3
      4
      5
      6
      # 使用 RENAME TO 关键字修改表名
      ALTER TABLE test RENAME TO test_new;
      # 修改表存储引擎
      ALTER TABLE test ENGINE=MyISAM;
      # 修改表编码集
      ALTET TABLE test CHARSET=gbk;

数据

1
2
3
4
5
6
# 插入单条数据
INSERT INTO table_name (field1, field2, field3) VALUES(value1, value2, value3);
# 插入多条数据
INSERT INTO table_name (field1, field2, field3) VALUES(valueA1, valueA2, valueA3),(valueB1, valueB2, valueB3),(valueC1, valueC2, valueC3);
# 不指定字段插入,此时数据与列对应
INSERT INTO table_name VALUES(value1, value2, value3);

1
2
DELETE FROM table_name [WHERE clause];
# 若没有指定 WHERE 子句,则所有数据被删除

1
2
UPDATE table_name SET field1=value1, field2=value2 [WHERE clause];
# 可以配合使用 REPLACE 函数

1
SELECT field1, field2 FROM table_name [WHERE clause] [LIMIT n] [OFFSET m];

注:

  • 可以使用多张表,之间用 , 分隔;
  • 可以使用通配符 * 代替字段名,此时拿出所有字段;
  • LIMIT n 限制返回的最多数据数;
  • OFFSET m 指定开始查询的偏移量,默认为 0,即从表的开头开始查询。

WHERE 子句

WHERE 是 SQL 中的条件子句关键字,相当于其他编程语言中的 if

常用关键字
  • AND
  • OR
  • =
  • !=:不等于
  • <>:不等于
  • >
  • <
  • >=
  • <=
LIKE 子句

LIKE 关键字在 WHERE 子句中提供了模糊查询的功能。有四种使用方式:

  • %:通配符,匹配任意个字符;
  • _:匹配任意单个字符;
  • []:匹配括号内所列的任意一个字符,类似于正则表达式;
  • [^]:匹配不在括号内所列的任意一个字符,类似于正则表达式。

NOT LIKE 将查询相反的结果。

REGEXP 正则表达式

REGEXP 关键字在 WHERE 子句中提供了正则表达式的功能,语法与其他语言类似。

UNION 操作符

使用关键字 UNION 连接两个以上的 SELECT 语句的查询结果。默认会删除重复的数据。使用 UNION ALL 关键字强制不去重。

1
2
3
SELECT field FROM table_name1 [WHERE clause1]
UNION [ALL]
SELECT field FROM table_name2 [WHERE clause2];

排序

使用关键字 ORDER BY 来为查询结果排序,默认按照升序排列。

1
2
SELECT field1, field2 FROM table_name
ORDER BY field1, field2 DESC;

使用关键字 DESC 使其降序排列。

分组

使用关键字 GROUP BY 来对查询结果分组,在分组的字段上可以使用函数。
如,根据 name 列进行分组并计数:

1
SELECT name, COUNT(name) FROM table_name GROUP BY name;

WITH ROLLUP 关键字可以在分组统计的数字上再使用相同的函数(即得到一个汇总):

1
2
3
4
5
6
7
8
9
mysql> SELECT COALESCE(name, "总数") AS name, COUNT(name) FROM table_name GROUP BY name WITH ROLLUP;
+--------+-------------+
| name | COUNT(name) |
+--------+-------------+
| 张三 | 2 |
| 李四 | 1 |
| 总数 | 3 |
+--------+-------------+
3 rows in set, 1 warning (0.00 sec)

其中,COALESCE 函数表示返回第一个非空的表达式。

连接

使用 JOIN 功能在多个表中查询数据。

  • INNER JOIN:可简写为 JOIN,获取两个表中字段匹配关系的数据;
  • LEFT JOIN:获取左表所有记录,即使右表中没有对应的数据;
  • RIGHT JOIN:获取右表所有记录,即使左表中没有对应的数据。

用法举例:
已有的两张表:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> SELECT * FROM test1;
+----+--------+------+--------+--------+
| id | name | age | gender | others |
+----+--------+------+--------+--------+
| 1 | 张三 | 15 | 男 | NULL |
| 2 | 李四 | 44 | 女 | |
| 4 | Shane | 25 | 男 | 0 |
+----+--------+------+--------+--------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM test2;
+----+---------+---------+---------+
| id | name | Chinese | English |
+----+---------+---------+---------+
| 1 | 张三 | 98 | 59 |
| 2 | 李四 | 88 | 96 |
| 3 | Sui Xin | 77 | 81 |
+----+---------+---------+---------+
3 rows in set (0.01 sec)

  • JOIN:查询两张表中都存在的人的语文成绩
    1
    2
    3
    4
    5
    6
    7
    8
    mysql> SELECT a.name, b.Chinese FROM test1 a JOIN test2 b ON a.name=b.name;
    +--------+---------+
    | name | Chinese |
    +--------+---------+
    | 张三 | 98 |
    | 李四 | 88 |
    +--------+---------+
    2 rows in set (0.00 sec)
  • LEFT JOIN:查询表一中所有人的英语成绩(可能为空)
    1
    2
    3
    4
    5
    6
    7
    8
    9
    mysql> SELECT a.name, b.English FROM test1 a LEFT JOIN test2 b ON a.name=b.name;
    +--------+---------+
    | name | English |
    +--------+---------+
    | 张三 | 59 |
    | 李四 | 96 |
    | Shane | NULL |
    +--------+---------+
    3 rows in set (0.00 sec)
  • RIGHT JOIN:查询表二中所有人的年龄(可能为空)
    1
    2
    3
    4
    5
    6
    7
    8
    9
    mysql> SELECT b.name, a.age FROM test1 a RIGHT JOIN test2 b ON a.name=b.name;
    +---------+------+
    | name | age |
    +---------+------+
    | 张三 | 15 |
    | 李四 | 44 |
    | Sui Xin | NULL |
    +---------+------+
    3 rows in set (0.00 sec)