MySQL DML操作实例讲解

1. 背景

   * CURD 操作通常是使用关系型数据库系统中的结构化查询语言（Structured Query Language，SQL）完成的

   * CURD 定义了用于处理数据的基本原子操作

   * CURD 代表创建（Create）、更新（Update）、读取（Retrieve）和删除（Delete）操作。

2. 创建表操作

   * 创建数据库(DB)  mytest

     CHARACTER SET: 设置字符集

mysql> CREATE DATABASE mytest CHARACTER SET utf8mb4;

Query OK, 1 row affected (0.00 sec)

   * 在数据库中创建表(table)

　　 ENGINE=INNODB 指定Innodb 存储引擎

     CHARSET=utf8mb4 设置表字符集

mysql> CREATE TABLE users(

    -> id BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,

    -> name VARCHAR(32) NOT NULL,

    -> sex ENUM('M', 'F') NOT NULL,

    -> age INT NOT NULL

    -> )ENGINE=INNODB CHARSET=utf8mb4;

Query OK, 0 rows affected (0.03 sec)

3. 插入数据操作

　 * select 插入单条数据

mysql> INSERT INTO users SELECT NULL, 'tom', 'M', 29;

Query OK, 1 row affected (0.01 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from users;

+----+------+-----+-----+

| id | name | sex | age |

+----+------+-----+-----+

|  1 | tom  | M   |  29 |

+----+------+-----+-----+

1 row in set (0.00 sec)

   * values 插入单条数据

mysql> INSERT INTO users VALUES (NULL, 'jak', 'F', 33);

Query OK, 1 row affected (0.01 sec)

mysql> select * from users;

+----+------+-----+-----+

| id | name | sex | age |

+----+------+-----+-----+

|  1 | tom  | M   |  29 |

|  2 | jak  | F   |  33 |

+----+------+-----+-----+

2 rows in set (0.00 sec)

   * select 指定列插入 [ id列会自增 ]

mysql> INSERT INTO users(name, sex, age) SELECT 'sea', 'M', '26';

Query OK, 1 row affected (0.01 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from users;

+----+------+-----+-----+

| id | name | sex | age |

+----+------+-----+-----+

|  1 | tom  | M   |  29 |

|  2 | jak  | F   |  33 |

|  3 | sea  | M   |  26 |

+----+------+-----+-----+

3 rows in set (0.01 sec)

   * values 指定列插入

mysql> INSERT INTO users(name, sex, age) VALUES ('hai', 'F', '18');

Query OK, 1 row affected (0.02 sec)

mysql> select * from users;

+----+------+-----+-----+

| id | name | sex | age |

+----+------+-----+-----+

|  1 | tom  | M   |  29 |

|  2 | jak  | F   |  33 |

|  3 | sea  | M   |  26 |

|  4 | hai  | F   |  18 |

+----+------+-----+-----+

4 rows in set (0.00 sec)

   * values 插入多条数据

mysql> INSERT INTO users VALUES (null, 'test1', 'F', 23), (null, 'test2', 'M', 34);

Query OK, 2 rows affected (0.01 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from users;

+----+-------+-----+-----+

| id | name  | sex | age |

+----+-------+-----+-----+

|  1 | tom   | M   |  29 |

|  2 | jak   | F   |  33 |

|  3 | sea   | M   |  26 |

|  4 | hai   | F   |  18 |

|  5 | test1 | F   |  23 |

|  6 | test2 | M   |  34 |

+----+-------+-----+-----+

6 rows in set (0.00 sec)

   * values 指定列插入多条数据

mysql> INSERT INTO users(name, sex, age) VALUES ('user1', 'F', 23), ('user2', 'M', 34);

Query OK, 2 rows affected (0.01 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from users;

+----+-------+-----+-----+

| id | name  | sex | age |

+----+-------+-----+-----+

|  1 | tom   | M   |  29 |

|  2 | jak   | F   |  33 |

|  3 | sea   | M   |  26 |

|  4 | hai   | F   |  18 |

|  5 | test1 | F   |  23 |

|  6 | test2 | M   |  34 |

|  7 | user1 | F   |  23 |

|  8 | user2 | M   |  34 |

+----+-------+-----+-----+

8 rows in set (0.00 sec)

4. 修改数据操作   

        update <table_name> 

     set column = val, ....

     where 条件语句　(没有写条件语句会修改表中所有数据)

   * 一次修改单列数据操作

mysql> select * from users where id = 1;

+----+------+-----+-----+

| id | name | sex | age |

+----+------+-----+-----+

|  1 | tom  | M   |  29 |

+----+------+-----+-----+

1 row in set (0.00 sec)

mysql> UPDATE users set name='lisea' where id = 1;

Query OK, 1 row affected (0.02 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from users where id = 1;

+----+-------+-----+-----+

| id | name  | sex | age |

+----+-------+-----+-----+

|  1 | lisea | M   |  29 |

+----+-------+-----+-----+

1 row in set (0.00 sec)

   * 一次修改多列数据操作

mysql> select * from users where id = 1;

+----+-------+-----+-----+

| id | name  | sex | age |

+----+-------+-----+-----+

|  1 | lisea | M   |  29 |

+----+-------+-----+-----+

1 row in set (0.01 sec)

mysql> UPDATE users set sex='F',age=33 where id = 1;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from users where id = 1;

+----+-------+-----+-----+

| id | name  | sex | age |

+----+-------+-----+-----+

|  1 | lisea | F   |  33 |

+----+-------+-----+-----+

1 row in set (0.00 sec)

5. 删除数据操作

   delete from <table_name> 

　 where 条件语句 [ 没有写条件语句会修改表中所有数据 ]

mysql> select * from users;

+----+-------+-----+-----+

| id | name  | sex | age |

+----+-------+-----+-----+

|  1 | lisea | F   |  33 |

|  2 | jak   | F   |  33 |

|  3 | sea   | M   |  26 |

|  4 | hai   | F   |  18 |

|  5 | test1 | F   |  23 |

|  6 | test2 | M   |  34 |

|  7 | user1 | F   |  23 |

|  8 | user2 | M   |  34 |

+----+-------+-----+-----+

8 rows in set (0.00 sec)

mysql> DELETE FROM users WHERE id = 1;

Query OK, 1 row affected (0.02 sec)

mysql> select * from users;

+----+-------+-----+-----+

| id | name  | sex | age |

+----+-------+-----+-----+

|  2 | jak   | F   |  33 |

|  3 | sea   | M   |  26 |

|  4 | hai   | F   |  18 |

|  5 | test1 | F   |  23 |

|  6 | test2 | M   |  34 |

|  7 | user1 | F   |  23 |

|  8 | user2 | M   |  34 |

+----+-------+-----+-----+

7 rows in set (0.00 sec)

mysql> DELETE FROM users WHERE id in (2, 4);

Query OK, 2 rows affected (0.01 sec)

mysql> select * from users;

+----+-------+-----+-----+

| id | name  | sex | age |

+----+-------+-----+-----+

|  3 | sea   | M   |  26 |

|  5 | test1 | F   |  23 |

|  6 | test2 | M   |  34 |

|  7 | user1 | F   |  23 |

|  8 | user2 | M   |  34 |

+----+-------+-----+-----+

5 rows in set (0.00 sec)

mysql> DELETE FROM users WHERE id >= 7;

Query OK, 2 rows affected (0.01 sec)

mysql> select * from users;

+----+-------+-----+-----+

| id | name  | sex | age |

+----+-------+-----+-----+

|  3 | sea   | M   |  26 |

|  5 | test1 | F   |  23 |

|  6 | test2 | M   |  34 |

+----+-------+-----+-----+

3 rows in