数据操作语句只适用于表，而无法作用于数据库，所以无需在操作关键词后添加 TABLE 关键字。

INSERT

INSERT 语句有三种语法形式：
INSERT...VALUES语句 

1
2
INSERT [INTO] <表名> [ <列名1> [ , … <列名n>] ]
VALUES ({expr|DEFAULT},...),(...),...

示例：

1
2
3
4
5
6
7
8
9
mysql> CREATE TABLE demo_0(
    -> id       CHAR(12),
    -> `name`   VARCHAR(32),
    -> age      TINYINT,
    -> addr     VARCHAR(32));
mysql> INSERT INTO demo_0
    -> (id, `name`, age, addr)
    -> VALUE('12336', 'QuanHa', 20, '四川');
Query OK, 1 row affected (0.01 sec)

  • 一次可插入多条记录：

    1
    2
    3
    4
    5
    6
    mysql> INSERT INTO demo_0
        -> (id, `name`, age, addr) VALUES
        -> ('13445', 'Jack', 19, '南京'),
        -> ('11234','Mike', 21, '北京');
    Query OK, 2 rows affected (0.01 sec)
    Records: 2  Duplicates: 0  Warnings: 0

  • 如果只向某些列插入值，那么其他未手动插入值的列被赋值为 NULL(若可以) 或 默认值。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    mysql> INSERT INTO demo_0 (id,age,addr)
        -> VALUES('12345', 18, '成都');
    Query OK, 1 row affected (0.01 sec)

    mysql> SELECT * FROM demo_0;
    +-------+--------+------+--------+
    | id    | name   | age  | addr   |
    +-------+--------+------+--------+
    | 12336 | QuanHa |   20 | 四川   |
    | 12345 | NULL   |   18 | 成都   |
    +-------+--------+------+--------+

  • 当为所有列全部赋值时，可不指定列名，但 VALUES 中的列顺序必须和表定义时的列顺序相同 ；如果不知道表的结构，使用 DESC 查看。

    1
    2
    mysql> INSERT INTO demo_0 VALUES('111111','XUAN',19,'重庆');
    Query OK, 1 row affected (0.01 sec)

INSERT...SET语句 

1
2
3
4
5
6
7
8
9
mysql> INSERT INTO demo_0
    -> SET id='12321', name='Han', age=20, addr='天津';
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM demo_0;
+--------+--------+------+--------+
| id     | name   | age  | addr   |
+--------+--------+------+--------+
| 12321  | Han    |   20 | 天津   |
+--------+--------+------+--------+

INSERT...SELECT语句

快速地从一个或多个表中取出数据，并将这些数据作为行数据插入另一个表中。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> INSERT INTO demo_1(id, name, age, addr)
    -> SELECT id, name , age, addr
    -> FROM demo_0
    -> WHERE age=20;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM demo_1;
+-------+--------+------+--------+
| id    | name   | age  | addr   |
+-------+--------+------+--------+
| 12336 | QuanHa |   20 | 四川   |
| 12321 | Han    |   20 | 天津   |
+-------+--------+------+--------+

另外，还有 INSERT…DELAYDE 语句，其用法较复杂，笔者将在后续文章中详细阐述。INSERT…SELECT 语句也有相当多注意事项，详细请参考 MySQL 手册和博主后续其他文章。

蠕虫复制
有时为了测试某条语句的效率，我们需要海量的数据，蠕虫复制就是利用少量已有的数据进行自我复制，只需几次操作就能获得百万级的数据表。操作如下：

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
mysql> SELECT * FROM emp;
+------+---------+------+------------+
| id   | name    | mgr  | department |
+------+---------+------+------------+
|    1 | Han     | NULL | 10         |
|    2 | Jack    | 1    | 12         |
|    3 | Tom     | 1    | 12         |
|    4 | Cling   | 2    | 20         |
|    5 | Black   | 3    | 20         |
|    6 | JayChou | 1    | 12         |
|    1 | Han     | NULL | 10         |
|    2 | Jack    | 1    | 12         |
|    3 | Tom     | 1    | 12         |
|    4 | Cling   | 2    | 20         |
|    5 | Black   | 3    | 20         |
|    6 | JayChou | 1    | 12         |
+------+---------+------+------------+
12 rows in set (0.00 sec)

mysql> INSERT INTO emp^C
mysql> INSERT INTO emp
    -> SELECT * FROM emp;
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> INSERT INTO emp
    -> SELECT * FROM emp;
Query OK, 24 rows affected (0.00 sec)
Records: 24  Duplicates: 0  Warnings: 0
###

重复以上复制十几次就可以达到百万行记录。

UPDATE

使用 UPDATE 语句修改单个表，语法格式为：

1
2
UPDATE <表名> SET 字段1=值1 [,字段2=值2… ] [WHERE子句]
[ORDER BY子句] [LIMIT子句]

示例一，修改某行的多个字段：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> SELECT * FROM demo_0;
+--------+--------+------+--------+
| id     | name   | age  | addr   |
+--------+--------+------+--------+
| 12345  | NULL   |   18 | 成都   |
| 111111 | XUAN   |   19 | 成都   |
+--------+--------+------+--------+
6 rows in set (0.00 sec)

mysql> UPDATE demo_0
    -> SET id=12213, name=NULL #修改某行的多个字段
    -> WHERE id=111111;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM demo_0;
+-------+--------+------+--------+
| id    | name   | age  | addr   |
+-------+--------+------+--------+
| 12345 | NULL   |   18 | 成都   |
| 12213 | NULL   |   19 | 成都   |
+-------+--------+------+--------+

示例二，统一修改所有行的某个字段：

1
2
3
4
mysql> UPDATE demo_0
    -> SET id=id*3, id=id+1;
Query OK, 6 rows affected (0.01 sec)
Rows matched: 6  Changed: 6  Warnings: 0

如第二行所示，UPDATE 从左到右执行赋值操作，id先加 1 ，再乘 3 。

示例三，使用 LIMIT 限定更改的行数：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> UPDATE demo_0
    -> SET age=23
    -> WHERE addr="南京"
    -> LIMIT 3;  #限定修改三条
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM demo_0;
+-------+--------+------+--------+
| id    | name   | age  | addr   |
+-------+--------+------+--------+
| 37009 | QuanHa |   23 | 南京   |
| 40336 | Jack   |   23 | 南京   |
| 33703 | Mike   |   23 | 南京   |
| 37036 | NULL   |   22 | 南京   |
| 36640 | XUAN   |   22 | 成都   |
| 36964 | Han    |   21 | 上海   |
+-------+--------+------+--------+

DELETE

使用 DELETE 语句从单个表中删除数据，语法格式为：

1
DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]
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
mysql> SELECT * FROM demo_0;
+-------+--------+------+--------+
| id    | name   | age  | addr   |
+-------+--------+------+--------+
| 37009 | QuanHa |   23 | 南京   |
| 40336 | Jack   |   23 | 南京   |
| 33703 | Mike   |   23 | 南京   |
| 37036 | NULL   |   22 | 南京   |
| 36640 | XUAN   |   22 | 南京   |
| 36964 | Han    |   22 | 南京   |
+-------+--------+------+--------+
6 rows in set (0.00 sec)

mysql> DELETE FROM demo_0
    -> ORDER BY id DESC
    -> LIMIT 3;
Query OK, 3 rows affected (0.01 sec)

mysql> SELECT * FROM demo_0;
+-------+------+------+--------+
| id    | name | age  | addr   |
+-------+------+------+--------+
| 33703 | Mike |   23 | 南京   |
| 36640 | XUAN |   22 | 南京   |
| 36964 | Han  |   22 | 南京   |
+-------+------+------+--------+
3 rows in set (0.00 sec)

第 15 行中 DESC (descent) 指降序（从大到小），第 16 行的 LIMIT 为 3，故而本指令的含义为：依次删除 id 最大的三个条目。
注意：在不使用 WHERE 或 LIMIT 条件的时候，将删除所有数据。

如何删除表中的重复数据？

  1. 创建一张新表，其结构和旧表相同。
  2. 在旧表中查重
  3. 将查重的内容复制到新表
  4. 删除旧表
  5. 将新表的名改为旧表
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
38
39
40
41
42
43
44
mysql> SELECT * from emp;
+------+---------+------+------------+
| id   | name    | mgr  | department |
+------+---------+------+------------+
|    1 | Han     | NULL | 10         |
|    2 | Jack    | 1    | 12         |
|    3 | Tom     | 1    | 12         |
|    4 | Cling   | 2    | 20         |
|    5 | Black   | 3    | 20         |
|    6 | JayChou | 1    | 12         |
|    1 | Han     | NULL | 10         |
|    2 | Jack    | 1    | 12         |
|    3 | Tom     | 1    | 12         |
|    4 | Cling   | 2    | 20         |
|    5 | Black   | 3    | 20         |
--------------------------------------
48 rows in set (0.00 sec)

mysql>  CREATE TABLE temp LIKE emp;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO temp
    -> SELECT DISTINCT * FROM emp;
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> DROP TABLE emp;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE temp RENAME TO emp;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM emp;
+------+---------+------+------------+
| id   | name    | mgr  | department |
+------+---------+------+------------+
|    1 | Han     | NULL | 10         |
|    2 | Jack    | 1    | 12         |
|    3 | Tom     | 1    | 12         |
|    4 | Cling   | 2    | 20         |
|    5 | Black   | 3    | 20         |
|    6 | JayChou | 1    | 12         |
+------+---------+------+------------+
6 rows in set (0.00 sec)

TRUNCATE

TRUNCATE TABLE 用于完全清空一个表 。从逻辑上说，该语句与用于删除所有行的 DELETE 语句等同，但是在有些情况下，两者在使用上有所不同：

  • 对于InnoDB表，如果有需要引用表的外键限制，则 TRUNCATE TABLE 被映射到 DELETE 上；否则使用快速删减（取消和重新创建表）。使用TRUNCATE TABLE 重新设置 AUTO_INCREMENT 计数器，设置时不考虑是否有外键限制。

  • 对于其它存储引擎，在 MySQL 5.1 中，TRUNCATE TABLE 与 DELETE FROM 有以下几处不同：

    • 删减操作会取消并重新创建表，这比一行一行的删除行要快很多
    • 删减操作不能保证对事务是安全的；在进行事务处理和表锁定的过程中尝试进行删减，会发生错误。
    • 被删除的行的数目没有被返回。
    • 只要表定义文件 tbl_name.frm 是合法的，则可以使用 TRUNCATE TABLE 把表重新创建为一个空表，即使数据或索引文件已经被破坏。
    • 表管理程序不记得最后被使用的 AUTO_INCREMENT 值，但是会从头开始计数。即使对于 MyISAM 和 InnoDB 也是如此。MyISAM 和InnoDB通常不再次使用序列值。
    • 当被用于带分区的表时，TRUNCATE TABLE 会保留分区；即，数据和索引文件被取消并重新创建，同时分区定义（.par）文件不受影响。

TRUNCATE TABLE 是在 MySQL 中采用的一个 Oracle SQL 扩展。

SELECT

SELECT 内容较多，参见另一篇文章：MySQL-SELECT详解