MySQL数据的增删改

老油条IT记

发布于 2020-03-20 11:09:51

66200

代码可运行

文章被收录于专栏：用户7095785的专栏用户7095785的专栏

运行总次数：0

代码可运行

1.insert插入数据

#命令语法：Insert into <表名> [(<字段名1>[…<字段名n>] )] values (值1)[，(值n)]

#查看帮助：help insert

#例子：创建一个表作为测试

#首先创建一个表
mysql> create table test (
    -> id int(4) not null auto_increment,
    -> name char(20) not null,
    -> age varchar(14) not null,
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc test;  #查看表结构
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(4)      | NO   | PRI | NULL    | auto_increment |
| name  | char(20)    | NO   |     | NULL    |                |
| age   | varchar(14) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> select * from test; #没有数据
Empty set (0.00 sec)

#1.指定所有列名，并且每列都插入值

mysql> insert into test(id,name,age) values (1,'guo',18);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | guo  | 18  |
+----+------+-----+
1 row in set (0.00 sec)

#2.由于id列设置了主键，id为自增，所以可以只在name和age列插入值

mysql> insert into test(name,age) values ('ke',19);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | guo  | 18  |
|  2 | ke   | 19  |
+----+------+-----+
2 rows in set (0.00 sec)

#3.如果不指定列，就要按规矩为每列插入适当的值

mysql> insert into test values(3,'liu',20);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | guo  | 18  |
|  2 | ke   | 19  |
|  3 | liu  | 20  |
+----+------+-----+
3 rows in set (0.00 sec)

#4.批量插入数据

mysql> insert into test values (4,'wu',21),(5,'zhang',22),(6,'li',23);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

#5.使用脚本插入数据

#!/bin/bash    
sqlcommd="mysql -utest -p"guoke123" db -e "
for  a  in {1..99}
do 
    $sqlcommd  "insert into account  values ('user$a','guoke123')"
done 
    $sqlcommd  "select * from account "

2.delelte/truncate删除数据

2.1使用delete删除

#命令语法：delete from 表名 where 表达式

#例1：使用id列指定删除

mysql> delete from test where id=1;  #删除test表中id为1的记录
Query OK, 1 row affected (0.00 sec)

#例二：使用name字段指定删除

mysql> delete from test where name='liu'; #删除name='liu'的行
Query OK, 1 row affected (0.00 sec)

#例三：指定范围删除

mysql> delete from test where id>2; #删除大于2的行
Query OK, 3 rows affected (0.00 sec)

#注意点：如果不加条件就是全部删除，非常危险的操作，例如：delete from test就是删除整个表的数据

mysql> delete from test;
Query OK, 2 rows affected (0.00 sec)

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

2.2.使用truncate删除

#命令语法:truncate table 表名

mysql> truncate table test; #删除test表的数据
Query OK, 0 rows affected (0.01 sec)

#delete from test和truncate table test区别

1.truncate table test：更快。清空物理文件
2.delete from test：逻辑清除，按行删

3.update修改表中的数据

#命令语法：update 表名 set 字段=新值 ......where 条件

#查看帮助：help update

mysql> help update
Name: 'UPDATE'
Description:
Syntax:
UPDATE is a DML statement that modifies rows in a table.

Single-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

Multiple-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET assignment_list
    [WHERE where_condition]

#例子：将test表中id为3的行的name改成zhang

mysql> select * from test;                                                                  
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | guo  | 18  |
|  2 | ke   | 19  |
|  3 | liu  | 20  |
|  4 | wu   | 25  |
|  5 | li   | 30  |
+----+------+-----+
5 rows in set (0.00 sec)

mysql> update test set name='zhangsan' where id=3;  #修改
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test;  #再次查看
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | guo      | 18  |
|  2 | ke       | 19  |
|  3 | zhangsan | 20  |
|  4 | wu       | 25  |
|  5 | li       | 30  |
+----+----------+-----+
5 rows in set (0.00 sec)

#特别注意：如果修改的时候不加添加，那么就会将表中的所有数据进行修改

#示例：不加条件更改所有表的记录

mysql> select * from test;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | guo      | 18  |
|  2 | ke       | 19  |
|  3 | zhangsan | 20  |
|  4 | wu       | 25  |
|  5 | li       | 30  |
+----+----------+-----+
5 rows in set (0.00 sec)

mysql> update test set name='boy';  #修改
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> select * from test; #再次查看全部都改了
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | boy  | 18  |
|  2 | boy  | 19  |
|  3 | boy  | 20  |
|  4 | boy  | 25  |
|  5 | boy  | 30  |
+----+------+-----+
5 rows in set (0.00 sec)

#模拟错误操作导致数据丢失再恢复

#1.首先先将数据进行备份
[root@cots3 ~]# mysqldump -utest -p db > /opt/back/db.sql
Enter password: 
[root@cots3 ~]# cd /opt/back/
[root@cots3 back]# ls
db.sql

#2.错误操作:不带条件修改
mysql> use db；

Database changed
mysql> select * from test;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | guo  | 18  |
|  2 | ke   | 19  |
|  3 | liu  | 20  |
|  4 | wu   | 25  |
|  5 | li   | 30  |
+----+------+-----+
5 rows in set (0.00 sec)

mysql> update test set name='zhangsan';  #修改
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> select * from test; #更改了所有记录
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | zhangsan | 18  |
|  2 | zhangsan | 19  |
|  3 | zhangsan | 20  |
|  4 | zhangsan | 25  |
|  5 | zhangsan | 30  |
+----+----------+-----+
5 rows in set (0.00 sec)

#3.恢复数据
#问题：可以会存在丢失数据
[root@cots3 back]# mysql -utest -p db < /opt/back/db.sql 
Enter password: 
[root@cots3 back]# mysql -utest -p
Enter password: 
mysql> use db
Database changed

mysql> select * from test;  #再次查看
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | guo  | 18  |
|  2 | ke   | 19  |
|  3 | liu  | 20  |
|  4 | wu   | 25  |
|  5 | li   | 30  |
+----+------+-----+
5 rows in set (0.00 sec)

#防止误操作修改数据的方法

#方法：定义一个别名

[root@cots3 ~]# alias mysql='mysql -U'

#提示:如果想永久生效，就放到/etc/profile里面

#再次测试修改

mysql> use db;

Database changed
mysql> select * from test;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | guo  | 18  |
|  2 | ke   | 19  |
|  3 | liu  | 20  |
|  4 | wu   | 25  |
|  5 | li   | 30  |
+----+------+-----+
5 rows in set (0.00 sec)

mysql> update test set name='zhangsan';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2020-03-03 ，如有侵权请联系 cloudcommunity@tencent.com 删除

sql

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

sql

登录后参与评论

0 条评论

热度