创建表
mysql> create table user(
-> id int primary key auto_increment,
-> name varchar(250),
-> article text,
-> create_time datetime
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc user;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(250) | YES | | NULL | |
| article | text | YES | | NULL | |
| create_time | datetime | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> create table user2(
-> id int primary key auto_increment,
-> name varchar(250),
-> email varchar(200),
-> sex enum('male', 'female')
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> desc user2;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(250) | YES | | NULL | |
| email | varchar(200) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
+-------+-----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
插入数据
使用基本的INSERT语句
使用基本的INSERT语句插入数据要求指定表名称和插入到新记录中的值。语法：INSERT INTO table_name (column_list) VALUES (value_list);。
INSERT语句可以同时向数据表中插入多条记录，插入时指定多个值列表，每个值列表之间用逗号分隔开。使用INSERT同时插入多条记录时，MySQL会返回一些在执行单行插入时没有的额外信息:
- Records：表明插入的记录条数。
- Duplicates：表明插入时被忽略的记录，原因可能是这些记录包含了重复的主键值。
- Warnings：表明有问题的数据值，例如发生数据类型转换。
- 若没有指定的插入列表，值列表为每一个字段列指定插入值，并且这些值的顺序必须和person表中字段定义的顺序相同。
- 如果某些字段没有指定插入值，MySQL将插入该字段定义时的默认值。
mysql> insert into user (name, article, create_time) values
-> ('ronie', 'When all else is lost the future still remains.', now()),
-> ('kelly', 'Keep on going never give up.', now()),
-> ('jack', 'Nurture passes nature.', now()),
-> ('jerry', 'The reason why a great man is great is that he resolves to be a great', now()
-> );
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from user;
+----+-------+-----------------------------------------------------------------------+---------------------+
| id | name | article | create_time |
+----+-------+-----------------------------------------------------------------------+---------------------+
| 1 | ronie | When all else is lost the future still remains. | 2022-10-10 19:48:19 |
| 2 | kelly | Keep on going never give up. | 2022-10-10 19:48:19 |
| 3 | jack | Nurture passes nature. | 2022-10-10 19:48:19 |
| 4 | jerry | The reason why a great man is great is that he resolves to be a great | 2022-10-10 19:48:19 |
+----+-------+-----------------------------------------------------------------------+---------------------+
4 rows in set (0.00 sec)
mysql> insert into user values (5, 'lucy', 'Suffering is the most powerful teacher of life.', now());
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+----+-------+-----------------------------------------------------------------------+---------------------+
| id | name | article | create_time |
+----+-------+-----------------------------------------------------------------------+---------------------+
| 1 | ronie | When all else is lost the future still remains. | 2022-10-10 19:48:19 |
| 2 | kelly | Keep on going never give up. | 2022-10-10 19:48:19 |
| 3 | jack | Nurture passes nature. | 2022-10-10 19:48:19 |
| 4 | jerry | The reason why a great man is great is that he resolves to be a great | 2022-10-10 19:48:19 |
| 5 | lucy | Suffering is the most powerful teacher of life. | 2022-10-10 19:54:19 |
+----+-------+-----------------------------------------------------------------------+---------------------+
5 rows in set (0.00 sec)
mysql> insert into user (name, create_time) values ('mike', now());
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+----+-------+-----------------------------------------------------------------------+---------------------+
| id | name | article | create_time |
+----+-------+-----------------------------------------------------------------------+---------------------+
| 1 | ronie | When all else is lost the future still remains. | 2022-10-10 19:48:19 |
| 2 | kelly | Keep on going never give up. | 2022-10-10 19:48:19 |
| 3 | jack | Nurture passes nature. | 2022-10-10 19:48:19 |
| 4 | jerry | The reason why a great man is great is that he resolves to be a great | 2022-10-10 19:48:19 |
| 5 | lucy | Suffering is the most powerful teacher of life. | 2022-10-10 19:54:19 |
| 6 | mike | NULL | 2022-10-10 19:58:11 |
+----+-------+-----------------------------------------------------------------------+---------------------+
6 rows in set (0.00 sec)
mysql> insert into user2 values (name, email, sex) values
-> ('tom', 'tom@163.com', 'male'),
-> ('frank', 'frank@163.com', 'male');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
将查询结果插入到表中
INSERT还可以将SELECT语句查询的结果插入到表中，只需要使用一条INSERT语句和一条SELECT语句组成的组合语句即可快速地从一个或多个表中向一个表中插入多行。语法：INSERT INTO table_name1 (column_list1) SELECT (column_list2) FROM table_name2 WHERE (condition)，MySQL不关心SELECT返回的列名，它根据列的位置进行插入，SELECT的第1列对应待插入表的第1列，第2列对应待插入表的第2列……即使不同结果的表之间也可以方便地转移数据。
mysql> insert into user (user.name) select user2.name from user2;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from user;
+----+-------+-----------------------------------------------------------------------+---------------------+
| id | name | article | create_time |
+----+-------+-----------------------------------------------------------------------+---------------------+
| 1 | ronie | When all else is lost the future still remains. | 2022-10-10 19:48:19 |
| 2 | kelly | Keep on going never give up. | 2022-10-10 19:48:19 |
| 3 | jack | Nurture passes nature. | 2022-10-10 19:48:19 |
| 4 | jerry | The reason why a great man is great is that he resolves to be a great | 2022-10-10 19:48:19 |
| 5 | lucy | Suffering is the most powerful teacher of life. | 2022-10-10 19:54:19 |
| 6 | mike | NULL | 2022-10-10 19:58:11 |
| 7 | tom | NULL | NULL |
| 8 | frank | NULL | NULL |
+----+-------+-----------------------------------------------------------------------+---------------------+
8 rows in set (0.00 sec)
更新数据
MySQL中使用UPDATE语句更新表中的记录，可以更新特定的行或者同时更新所有的行。语法：UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]。我们通过WHERE子句指定被更新的记录所需要满足的条件，如果忽略WHERE子句，MySQL将更新表中所有的行。
mysql> select * from user;
+----+-------+-----------------------------------------------------------------------+---------------------+
| id | name | article | create_time |
+----+-------+-----------------------------------------------------------------------+---------------------+
| 1 | ronie | When all else is lost the future still remains. | 2022-10-10 19:48:19 |
| 2 | kelly | Keep on going never give up. | 2022-10-10 19:48:19 |
| 3 | jack | Nurture passes nature. | 2022-10-10 19:48:19 |
| 4 | jerry | The reason why a great man is great is that he resolves to be a great | 2022-10-10 19:48:19 |
| 5 | lucy | Suffering is the most powerful teacher of life. | 2022-10-10 19:54:19 |
| 6 | mike | NULL | 2022-10-10 19:58:11 |
| 7 | tom | NULL | NULL |
| 8 | frank | NULL | NULL |
+----+-------+-----------------------------------------------------------------------+---------------------+
8 rows in set (0.00 sec)
mysql> update user set create_time=now() where id in (7, 8) or name='ronie';
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from user;
+----+-------+-----------------------------------------------------------------------+---------------------+
| id | name | article | create_time |
+----+-------+-----------------------------------------------------------------------+---------------------+
| 1 | ronie | When all else is lost the future still remains. | 2022-10-10 20:20:37 |
| 2 | kelly | Keep on going never give up. | 2022-10-10 19:48:19 |
| 3 | jack | Nurture passes nature. | 2022-10-10 19:48:19 |
| 4 | jerry | The reason why a great man is great is that he resolves to be a great | 2022-10-10 19:48:19 |
| 5 | lucy | Suffering is the most powerful teacher of life. | 2022-10-10 19:54:19 |
| 6 | mike | NULL | 2022-10-10 19:58:11 |
| 7 | tom | NULL | 2022-10-10 20:20:37 |
| 8 | frank | NULL | 2022-10-10 20:20:37 |
+----+-------+-----------------------------------------------------------------------+---------------------+
8 rows in set (0.00 sec)
-- 作用于所有行
mysql> update user set create_time=current_date();
Query OK, 8 rows affected (0.01 sec)
Rows matched: 8 Changed: 8 Warnings: 0
mysql> select * from user;
+----+-------+-----------------------------------------------------------------------+---------------------+
| id | name | article | create_time |
+----+-------+-----------------------------------------------------------------------+---------------------+
| 1 | ronie | When all else is lost the future still remains. | 2022-10-10 00:00:00 |
| 2 | kelly | Keep on going never give up. | 2022-10-10 00:00:00 |
| 3 | jack | Nurture passes nature. | 2022-10-10 00:00:00 |
| 4 | jerry | The reason why a great man is great is that he resolves to be a great | 2022-10-10 00:00:00 |
| 5 | lucy | Suffering is the most powerful teacher of life. | 2022-10-10 00:00:00 |
| 6 | mike | NULL | 2022-10-10 00:00:00 |
| 7 | tom | NULL | 2022-10-10 00:00:00 |
| 8 | frank | NULL | 2022-10-10 00:00:00 |
+----+-------+-----------------------------------------------------------------------+---------------------+
8 rows in set (0.00 sec)
mysql>
删除数据
从数据表中删除数据使用DELETE语句，DELETE语句允许WHERE子句指定删除条件。语法：DELETE FROM table_name [WHERE <condition>];。
mysql> delete from user where article is null;
Query OK, 3 rows affected (0.00 sec)
mysql> select * from user;
+----+-------+-----------------------------------------------------------------------+---------------------+
| id | name | article | create_time |
+----+-------+-----------------------------------------------------------------------+---------------------+
| 1 | ronie | When all else is lost the future still remains. | 2022-10-10 00:00:00 |
| 2 | kelly | Keep on going never give up. | 2022-10-10 00:00:00 |
| 3 | jack | Nurture passes nature. | 2022-10-10 00:00:00 |
| 4 | jerry | The reason why a great man is great is that he resolves to be a great | 2022-10-10 00:00:00 |
| 5 | lucy | Suffering is the most powerful teacher of life. | 2022-10-10 00:00:00 |
+----+-------+-----------------------------------------------------------------------+---------------------+
5 rows in set (0.00 sec)
mysql> delete from user;
Query OK, 5 rows affected (0.00 sec)
mysql> select * from user;
Empty set (0.00 sec)
-- 删除表中的所有记录，还可以使用TRUNCATE TABLE语句。
-- TRUNCATE将直接删除原来的表，并重新创建一个表，其语法结构为TRUNCATE TABLE table_name
-- TRUNCATE直接删除表而不是删除记录，因此执行速度比DELETE快。
Comments | NOTHING