MySQL数据的增删改
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.
