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: 0 mysql> 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: 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 指定列插入
| 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: 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 指定列插入多条数据
| 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: 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 条件语句 (没有写条件语句会修改表中所有数据)
* 一次修改单列数据操作
| 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: 0 mysql> 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: 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 条件语句 [ 没有写条件语句会修改表中所有数据 ]
| 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 ，如需转载请自行联系原作者