前端小册

Appearance

On this page

数据的增删改 ​

创建新的表 ​

sql
mysql> create table first_table (
    -> first_column int,
    -> second_column varchar(100)
    -> );
Query OK, 0 rows affected (0.94 sec)

mysql>

插入数据 ​

插入完成的数据 ​

sql
INSERT INTO 表名 VALUES(列1的值，列2的值, ..., 列n的值);
sql
mysql> insert into first_table values(1, 'aaa');
Query OK, 1 row affected (0.50 sec)

mysql>
sql
mysql> insert into first_table values(1, null);
Query OK, 1 row affected (0.09 sec)

mysql> select * from first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | aaa           |
|            1 | NULL          |
+--------------+---------------+
2 rows in set (0.00 sec)

mysql>

也可以指定列的顺序

sql
mysql> insert into first_table (second_column, first_column) values ('ddd', 4);
Query OK, 1 row affected (0.09 sec)

mysql> select * from first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | aaa           |
|            1 | NULL          |
|            4 | ddd           |
+--------------+---------------+
3 rows in set (0.00 sec)

mysql>

插入部分记录 ​

插入记录的某些列的值可以省略，需要满足的条件

  • 该列可以设置 null 值
  • 该列给了默认值，default 属性
sql
mysql> insert into first_table(first_column) values (5);
Query OK, 1 row affected (2.53 sec)

mysql> select * from first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | aaa           |
|            1 | NULL          |
|            4 | ddd           |
|            5 | NULL          |
+--------------+---------------+
4 rows in set (0.00 sec)

mysql>

批量插入数据 ​

sql
mysql> insert into first_table values (7, 'ggg'), (8, 'hhh');
Query OK, 2 rows affected (2.27 sec)
mysql> select * from first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | aaa           |
|            1 | NULL          |
|            4 | ddd           |
|            5 | NULL          |
|            7 | ggg           |
|            8 | hhh           |
+--------------+---------------+
6 rows in set (0.00 sec)

mysql>

将某个查询结果集插入表中 ​

先创建一个表

sql
mysql> create table second_table(
    -> s varchar(200),
    -> i int);
Query OK, 0 rows affected (2.96 sec)

mysql>

将表 first_table 查询的结果集插入表 second_table 中

sql
mysql> insert into second_table(s,i) select second_column, first_column from first_table where first_column<5;
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from second_table;
+------+------+
| s    | i    |
+------+------+
| aaa  |    1 |
| NULL |    1 |
| ddd  |    4 |
+------+------+
3 rows in set (0.00 sec)

mysql>

insert ignore ​

对于一些主键或者具有 unique 约束的列或列的组合来书，它们不允许重复的值的出现。

sql
mysql> alter table first_table modify column first_column int unique;
Query OK, 0 rows affected (0.79 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

先将 first_column 设置了约束，如再插入

sql
mysql> insert into first_table(first_column,second_column) values (4, "哈哈");
ERROR 1062 (23000): Duplicate entry '4' for key 'first_table.first_column'
mysql>

可以看到插入的时候报了异常，我们不能再插入已有的记录，使用 insert ignore into

sql
mysql> insert ignore into first_table values(4, "haha");
Query OK, 0 rows affected, 1 warning (2.39 sec)

mysql> select * from first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            4 | ddd           |
|            5 | NULL          |
|            7 | ggg           |
|            8 | hhh           |
|            1 | 哈哈          |
+--------------+---------------+
5 rows in set (0.00 sec)

mysql>

可以看到没有报错了

insert on duplicate key update ​

sql
mysql> insert into first_table values (4, "ddd") on duplicate key update second_column="改变";
Query OK, 2 rows affected (2.56 sec)

mysql> select * from first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            4 | 改变          |
|            5 | NULL          |
|            7 | ggg           |
|            8 | hhh           |
|            1 | 哈哈          |
+--------------+---------------+
5 rows in set (0.00 sec)

mysql>

批量插入

sql
mysql> insert into first_table values (4,"红牛"),(5,"雪碧") on duplicate key update second_column= values(second_column);
Query OK, 4 rows affected, 1 warning (2.52 sec)
Records: 2  Duplicates: 2  Warnings: 1

mysql> select * from first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            4 | 红牛          |
|            5 | 雪碧          |
|            7 | ggg           |
|            8 | hhh           |
|            1 | 哈哈          |
|            2 | 红牛          |
|            3 | 雪碧          |
+--------------+---------------+
7 rows in set (0.00 sec)

mysql>

删除数据 ​

sql
DELETE FROM 表名 [WHERE 表达式];
sql
mysql> delete from first_table where first_column <=3
    -> ;
Query OK, 3 rows affected (0.49 sec)

mysql> select * from first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            4 | 红牛          |
|            5 | 雪碧          |
|            7 | ggg           |
|            8 | hhh           |
+--------------+---------------+
4 rows in set (0.00 sec)

mysql>

删除表中 first_column 值最大的一列

sql
mysql> delete from first_table order by first_column desc limit 1;
Query OK, 1 row affected (2.60 sec)

mysql> select * from first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            4 | 红牛          |
|            5 | 雪碧          |
|            7 | ggg           |
+--------------+---------------+
3 rows in set (0.00 sec)

mysql>

删除表全部数据，其实就是没有 where 条件

sql
mysql> delete from second_table;
Query OK, 3 rows affected (0.10 sec)

mysql> select * from second_table;
Empty set (0.00 sec)

mysql>

更新数据 ​

sql
UPDATE 表名 SET 列1=值1, 列2=值2, ...,  列n=值n [WHERE 布尔表达式];
sql
mysql> update first_table set first_column = 5,second_column="美年达" where first_column=5;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            4 | 红牛          |
|            5 | 美年达        |
|            7 | ggg           |
+--------------+---------------+
3 rows in set (0.00 sec)

mysql>

更新 first_column 值最大的一列

sql
mysql> update first_table set second_column="哇哈哈" order by first_column desc limit 1;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            4 | 红牛          |
|            5 | 美年达        |
|            7 | 哇哈哈        |
+--------------+---------------+
3 rows in set (0.00 sec)

mysql>