MySQL开发手册(七) 数据表增删改

(1)插入操作


  • INSERT [INTO] tab_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT}),(),...
  • INSERT [INTO] tab_name SET col_name= {expr | DEFAULT},...
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 ... 将查询结果插入到指定数据表
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]
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 FROM tb1_name [ WHERE where_condition]
mysql> delete from user where id = 1;
Query OK, 1 row affected (0.09 sec)

笔记资源下载链接 ,支持PDF、ENEX印象笔记、HTML,方便大家整理导入

© 著作权归作者所有
这个作品真棒,我要支持一下!
主要帮助小白掌握MySQL数据库的增删改查操作,最终提供一个工具类语句手册和完善的学习笔记
0条评论
top Created with Sketch.