4-4、数据插入、删除

（1）插入数据

• INSERT [INTO] tab_name [(col_name,…)] {VALUES | VALUE} ({expr | DEFAULT}),(),…
  
• INSERT [INTO] tab_name SET col_name= {expr | DEFAULT},…

  insert user values(null,’tom’,’123’,25,1); insert user values(null,’rose’,’123’,25,1),(null,’rose’,’123’,25,2); insert user set username=’ben’,password=’456’; insert user values(null,’john’,md5(‘123’),25,1); insert user (id,username,password,age) values(null,’tom’,’123456’,24);

mysql> create table user(
    -> id smallint unsigned primary key auto_increment,
    -> username varchar(20) not null,
    -> password varchar(20) not null,
    -> age tinyint unsigned not null default 10,
    -> sex boolean
    -> );
Query OK, 0 rows affected (0.15 sec)
//对于主键并且自增长的列，赋值为NULL或者DEFAULT,它就会自动增大
mysql> insert user values(null,'tom','123',25,1);
Query OK, 1 row affected (0.03 sec)
mysql> insert user values(null,'john','234',25,1);
Query OK, 1 row affected (0.04 sec)
mysql> insert user values(null,'Tom','123',3*9,2);
Query OK, 1 row affected (0.05 sec)
mysql> insert user values(null,'john',md5('123'),25,1);
Query OK, 1 row affected, 1 warning (0.06 sec)
mysql> insert user values(null,'rose','123',25,1),(null,'rose','123',25,2);
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> insert user set username='ben',password='456';
Query OK, 1 row affected (0.04 sec)
mysql> select * from user;
+----+----------+----------------------+-----+------+
| id | username | password             | age | sex  |
+----+----------+----------------------+-----+------+
|  1 | tom      | 123                  |  25 |    1 |
|  2 | john     | 234                  |  25 |    1 |
|  3 | Tom      | 123                  |  27 |    2 |
|  4 | john     | 202cb962ac59075b964b |  25 |    1 |
|  5 | rose     | 123                  |  25 |    1 |
|  6 | rose     | 123                  |  25 |    2 |
|  7 | ben      | 456                  |  10 | NULL |
+----+----------+----------------------+-----+------+
7 rows in set (0.00 sec)

• 从其他表插入数据：INSERT [INTO] ta_name [(col_name,…)] SELECT … 将查询结果插入到指定数据表

  insert username (username) select username from user where age>25;

mysql> create table username(
    -> id smallint unsigned primary key auto_increment,
    -> username varchar(20) not null
    -> );
Query OK, 0 rows affected (0.23 sec)

mysql> insert username (username) select username from user where age>25;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from username;
+----+----------+
| id | username |
+----+----------+
|  1 | Tom      |
+----+----------+
1 row in set (0.00 sec)

（2）更新数据

• UPDATE [LOW_PRIOPITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT} [,col_name1={expr1|DEFAULT} ]… [WHERE where_condition]

  update user set age = age -id,sex=0; update user set age = age + 10 where id%2=0;

mysql> select * from user;
+----+----------+----------------------+-----+------+
| id | username | password             | age | sex  |
+----+----------+----------------------+-----+------+
|  1 | tom      | 123                  |  25 |    1 |
|  2 | john     | 234                  |  25 |    1 |
|  3 | Tom      | 123                  |  27 |    2 |
|  4 | john     | 202cb962ac59075b964b |  25 |    1 |
|  5 | rose     | 123                  |  25 |    1 |
|  6 | rose     | 123                  |  25 |    2 |
|  7 | ben      | 456                  |  10 | NULL |
+----+----------+----------------------+-----+------+
7 rows in set (0.00 sec)

mysql> update user set age = age -id,sex=0;
Query OK, 7 rows affected (0.05 sec)
Rows matched: 7  Changed: 7  Warnings: 0

mysql> select * from user;
+----+----------+----------------------+-----+------+
| id | username | password             | age | sex  |
+----+----------+----------------------+-----+------+
|  1 | tom      | 123                  |  24 |    0 |
|  2 | john     | 234                  |  23 |    0 |
|  3 | Tom      | 123                  |  24 |    0 |
|  4 | john     | 202cb962ac59075b964b |  21 |    0 |
|  5 | rose     | 123                  |  20 |    0 |
|  6 | rose     | 123                  |  19 |    0 |
|  7 | ben      | 456                  |   3 |    0 |
+----+----------+----------------------+-----+------+
7 rows in set (0.00 sec)

mysql> update user set age = age + 10 where id%2=0;
Query OK, 3 rows affected (0.03 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from user;
+----+----------+----------------------+-----+------+
| id | username | password             | age | sex  |
+----+----------+----------------------+-----+------+
|  1 | tom      | 123                  |  24 |    0 |
|  2 | john     | 234                  |  33 |    0 |
|  3 | Tom      | 123                  |  24 |    0 |
|  4 | john     | 202cb962ac59075b964b |  31 |    0 |
|  5 | rose     | 123                  |  20 |    0 |
|  6 | rose     | 123                  |  29 |    0 |
|  7 | ben      | 456                  |   3 |    0 |
+----+----------+----------------------+-----+------+
7 rows in set (0.00 sec)

（3）删除数据

DELETE

• DELETE FROM tb1_name [ WHERE where_condition]
• 不加where条件会删除全部数据

  delete from user where id = 1;

mysql> delete from user where id = 1;
Query OK, 1 row affected (0.09 sec)

TRUNCATE

• 删除老table，再新建一个一模一样的表，但是没有数据

  truncate table bookcopy;