1. 背景
* CURD 操作通常是使用关系型数据库系统中的结构化查询语言（Structured Query Language，SQL）完成的
* CURD 定义了用于处理数据的基本原子操作
* CURD 代表创建（Create）、更新（Update）、读取（Retrieve）和删除（Delete）操作。
2. 创建表操作
* 创建数据库(DB) mytest
CHARACTER SET: 设置字符集
|
1
2
|
mysql> CREATE DATABASE mytest CHARACTER SET utf8mb4;
Query OK, 1 row affected (0.00 sec) |
* 在数据库中创建表(table)
ENGINE=INNODB 指定Innodb 存储引擎
CHARSET=utf8mb4 设置表字符集
|
1
2
3
4
5
6
7
|
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 插入单条数据
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> INSERT INTO users SELECT NULL, 'tom', 'M', 29;
Query OK, 1 row affected (0.01 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> select * from users;
+----+------+-----+-----+| id | name | sex | age |
+----+------+-----+-----+| 1 | tom | M | 29 |+----+------+-----+-----+1 row in set (0.00 sec)
|
* values 插入单条数据
|
1
2
3
4
5
6
7
8
9
10
11
|
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列会自增 ]
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
mysql> INSERT INTO users(name, sex, age) SELECT 'sea', 'M', '26';
Query OK, 1 row affected (0.01 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> 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 指定列插入
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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 插入多条数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
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: 0mysql> 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 指定列插入多条数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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: 0mysql> 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 条件语句 (没有写条件语句会修改表中所有数据)
* 一次修改单列数据操作
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
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: 0mysql> select * from users where id = 1;
+----+-------+-----+-----+| id | name | sex | age |
+----+-------+-----+-----+| 1 | lisea | M | 29 |+----+-------+-----+-----+1 row in set (0.00 sec)
|
* 一次修改多列数据操作
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
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: 0mysql> 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 条件语句 [ 没有写条件语句会修改表中所有数据 ]
|
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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
|
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 set (0.00 sec)
|
6. 总结
以需求驱动技术，技术本身没有优略之分，只有业务之分。
本文转自asd1123509133 51CTO博客，原文链接：http://blog.51cto.com/lisea/1943701，如需转载请自行联系原作者