MySQL 基本操作2
时间：2021-07-01 10:21:17
帮助过：8人阅读
1.向表中增加数据
insert into 表名 (字段1，字段2……)
values (值1，值2……）；
mysql> select * from linlin;
Empty set (
0.00 sec)
mysql> desc linlin;
+-------+-------------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra
|
+-------+-------------+------+-----+---------+-------+
| score
| float | YES
| | NULL | |
| name
| varchar(
20)
| YES
| | NULL | |
| id
| int(
20)
| YES
| | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows
in set (
0.00 sec)
mysql> insert into linlin (score, name, id)
values (
99,
‘A‘,
1);
Query OK, 1 row affected (
0.01 sec)
mysql> select * from linlin;
+-------+------+------+
| score
| name
| id
|
+-------+------+------+
| 99 | A
| 1 |
+-------+------+------+
1 row
in set (
0.00 sec)
也可以一次增加多条数据 insert into 表名 (字段1，字段2……)
values (值1，值2……），(值1，值2……），……；
mysql> insert into linlin (score, name, id)
values (
98,
‘B‘,
2), (
97,
‘C‘,
3);
Query OK, 2 rows affected (
0.01 sec)
Records: 2 Duplicates:
0 Warnings:
0
mysql> select * from linlin;
+-------+------+------+
| score
| name
| id
|
+-------+------+------+
| 99 | A
| 1 |
| 98 | B
| 2 |
| 97 | C
| 3 |
+-------+------+------+
3 rows
in set (
0.00 sec)
当插入语句中(字段1，字段2……)省略时，系统会按照表中字段的排列顺序插入
insert into 表名
values (值1，值2……）；
mysql> insert into linlin
values (
96,
‘D‘,
4);
Query OK, 1 row affected (
0.02 sec)
mysql> select * from linlin;
+-------+------+------+
| score
| name
| id
|
+-------+------+------+
| 99 | A
| 1 |
| 98 | B
| 2 |
| 97 | C
| 3 |
| 96 | D
| 4 |
+-------+------+------+
4 rows
in set (
0.00 sec)
insert语句的另一种写法：
insert into 表名
set 字段1
= 值1，字段2
= 值2，……；
mysql> insert into linlin
set score
= 95, name
= ‘E‘, id
= 5;
Query OK, 1 row affected (
0.02 sec)
mysql> select * from linlin;
+-------+------+------+
| score
| name
| id
|
+-------+------+------+
| 99 | A
| 1 |
| 98 | B
| 2 |
| 97 | C
| 3 |
| 96 | D
| 4 |
| 95 | E
| 5 |
+-------+------+------+
5 rows
in set (
0.00 sec)
2.对表中已存在的数据进行修改
update 表名
set 字段1
= 值1，字段2
= 值2，……
where 条件；
mysql> select * from linlin
where id
= 1;
+-------+------+------+
| score
| name
| id
|
+-------+------+------+
| 100 | A
| 1 |
+-------+------+------+
1 row
in set (
0.00 sec)
mysql> update linlin
set score
= 99 where id
= 1;
Query OK, 1 row affected (
0.01 sec)
Rows matched: 1 Changed:
1 Warnings:
0
mysql> select * from linlin
where id
= 1;
+-------+------+------+
| score
| name
| id
|
+-------+------+------+
| 99 | A
| 1 |
+-------+------+------+
1 row
in set (
0.00 sec)
如果需要更新全部数据时，则不需要where条件
3. 删除表中的记录
delete from 表名
where 表达式；
mysql> select * from linlin;
+-------+------+------+
| score
| name
| id
|
+-------+------+------+
| 99 | A
| 1 |
| 98 | B
| 2 |
| 97 | C
| 3 |
| 96 | D
| 4 |
| 95 | E
| 5 |
+-------+------+------+
5 rows
in set (
0.00 sec)
mysql> delete from linlin
where id
= 5;
Query OK, 1 row affected (
0.01 sec)
mysql> select * from linlin;
+-------+------+------+
| score
| name
| id
|
+-------+------+------+
| 99 | A
| 1 |
| 98 | B
| 2 |
| 97 | C
| 3 |
| 96 | D
| 4 |
+-------+------+------+
4 rows
in set (
0.00 sec)
如果需要删除全部数据，则不必加上where条件
另外，还可以使用 truncate 表名 删除全部数据
truncate 表名 删除全部数据 与
delete from 表名 删除全部数据的异同
它们都可以删除全部数据，但对于自动增加字段的值，truncate 表名 删除
全部数据之后再向表中添加数据时，自动增加字段的默认值时从1开始，而
对于 delete from 表名 删除全部数据，自动增加字段的默认值是从未删除
时该字段的值加1开始。
MySQL 基本操作2
标签：varchar char 0.00 desc 开始 ext 记录 排列 删除
