MySQL使用教程【九】：数据的操作

1、插入数据记录

（1）插入完整数据记录

【语法】

insert into table_name(field1,field2……fieldn) values(value1,value2……valuen);
insert into table_name values(value1,value2……valuen);

【实例】

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> desc t_dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(11)     | YES  |     | NULL    |       |
| dname  | varchar(20) | YES  |     | NULL    |       |
| loc    | varchar(40) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.05 sec)

mysql> insert into t_dept(deptno,dname,loc) values(1,'test4','zhongxi');
Query OK, 1 row affected (0.16 sec)

mysql> select * from t_dept;
+--------+-------+---------+
| deptno | dname | loc     |
+--------+-------+---------+
|      1 | test  | shangxi |
|      2 | test  | shangxi |
|      2 | test1 | shangxi |
|      1 | test2 | shangxi |
|      1 | test4 | zhongxi |
+--------+-------+---------+
5 rows in set (0.00 sec)

mysql> insert into t_dept values(33,'test5','zhongxi');
Query OK, 1 row affected (0.03 sec)

mysql> select * from t_dept;
+--------+-------+---------+
| deptno | dname | loc     |
+--------+-------+---------+
|      1 | test  | shangxi |
|      2 | test  | shangxi |
|      2 | test1 | shangxi |
|      1 | test2 | shangxi |
|      1 | test4 | zhongxi |
|     33 | test5 | zhongxi |
+--------+-------+---------+
6 rows in set (0.01 sec)

（2）插入数据记录的一部分

【语法】

insert into table_name(field1,field2……fieldn) values(value1,value2……valuen);

【实例】

mysql> insert into t_diary(tablename,diarytime) values('t_dept',now());
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_diary;
+---------+-----------+---------------------+
| diaryno | tablename | diarytime           |
+---------+-----------+---------------------+
|       1 | t_dept    | 2019-03-07 15:07:24 |
|       2 | t_dept    | 2019-03-07 15:38:37 |
|       3 | t_dept    | 2019-03-07 15:38:37 |
|       4 | t_dept    | 2019-03-07 15:38:37 |
|       5 | t_dept    | 2019-03-07 15:46:53 |
|       6 | t_dept    | 2019-03-07 15:46:53 |
|       7 | t_dept    | 2019-03-07 15:46:53 |
|       8 | t_dept    | 2019-03-07 15:47:08 |
|       9 | t_dept    | 2019-03-07 15:47:08 |
|      10 | t_dept    | 2019-03-07 15:47:08 |
|      11 | t_dept    | 2019-03-08 10:37:00 |
|      12 | t_dept    | 2019-03-08 10:37:00 |
|      13 | t_dept    | 2019-03-08 10:38:33 |
|      14 | t_dept    | 2019-03-08 10:38:33 |
|      15 | t_dept    | 2019-03-08 11:24:19 |
+---------+-----------+---------------------+
15 rows in set (0.00 sec)

（3）插入多条数据记录
【语法】

insert into table_name(field1,field2……fieldn)
               values(value1,value2……valuen),
               values(value11,value12……value1n),
               values(value21,value22……value2n),
               …………
               values(valuem1,valuem2……valuemn);
               
insert into table_name
               values(value1,value2……valuen),
               values(value11,value12……value1n),
               values(value21,value22……value2n),
               …………
               values(valuem1,valuem2……valuemn);

【实例】

mysql> insert into t_dept
    ->   values(2,'test5','beiying'),
    ->         (4,'test6','zhongxi');
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t_diary(tablename,diarytime)
    ->   values('t_dept',now()),
    ->         ('t_dept',now());
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

（3）插入查询结果

【语法】

insert into table_name1(field11,field12……field1n)
 select (field21,field22……field2n)
   from table_name2 where ……

【实例】

mysql> insert into t_dept(dname,loc) select dname,loc from t_loader;

2、更新数据记录

（1）更新特定数据记录

【语法】

update table_name set field1=values1,field2=values2 where 条件语句;

【实例】

mysql> update t_dept set loc='beiying' where dname='dept4';
Query OK, 1 row affected (0.13 sec)
Rows matched: 1  Changed: 1  Warnings: 0

（2）更新所有数据记录

【语法】

update table_name set field1=values1,field2=values2 where 条件语句;
update table_name set field1=values1,field2=values2;

【实例】

mysql> update t_dept set loc='zhongxi' where deptno<4;
Query OK, 3 rows affected (0.10 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from t_dept;
+--------+-------+---------+
| deptno | dname | loc     |
+--------+-------+---------+
|      1 | dept1 | zhongxi |
|      2 | dept2 | zhongxi |
|      3 | dept4 | zhongxi |
+--------+-------+---------+

3 rows in set (0.01 sec)

mysql> update t_dept set loc='shangxi';
Query OK, 3 rows affected (0.08 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from t_dept;
+--------+-------+---------+
| deptno | dname | loc     |
+--------+-------+---------+
|      1 | dept1 | shangxi |
|      2 | dept2 | shangxi |
|      3 | dept4 | shangxi |
+--------+-------+---------+
3 rows in set (0.00 sec)

3、删除数据记录

（1）删除特定数据记录

【语法】

delete from 表名 where 条件语句;

【实例】

mysql> delete from t_dept where dname='dept';
Query OK, 1 row affected (0.07 sec)

（2）删除所有数据记录

【语法】

delete from 表名 where 条件语句;

【实例】

mysql> delete from t_dept where deptno<4;
Query OK, 2 rows affected (0.07 sec)