您好, 欢迎来到 !    登录 | 注册 | | 设为首页 | 收藏本站
首页- 博文- mysql- MySQL之数据的增删改

MySQL之数据的增删改

bubuko 2022/1/25 20:08:33 mysql 字数 24372 阅读 827 来源 http://www.bubuko.com/infolist-5-1.html

#1.insert插入数据 #命令语法：Insert into <表名> [(<字段名1>[…<字段名n>] )] values (值1)[，(值n)] #查看帮助：help insert #例子：创建一个表作为测试 #首先创建一个表 mysql> create table test ( -> id ...

#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]
View Code

#例子：将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)
View Code

 

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

#方法：定义一个别名

[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.

 

MySQL之数据的增删改

原文：https://www.cnblogs.com/guoke-boy/p/12392465.html


如果您也喜欢它,动动您的小指点个赞吧

除非注明，文章均由 laddyq.com 整理发布，欢迎转载。

转载请注明：
链接：http://laddyq.com
来源：laddyq.com
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

联系我

联系我

602392714

清零编程清零编程群

置顶