Sui Xin's Blog

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

Posted on In Disqus:

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 之后，推荐直接使用 FLOAT 与 DOUBLE 关键字，无需指定精度。

日期和时间类型

类型 存储大小（字节） 范围 格式
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

注：DATATIME 与 TIMESTAMP 的区别不仅限于表示范围的不同，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)
Related Posts