13.5-13.6 常用sql语句与mysql恢复备份
查询
mysql> select count(*) from t1; //查询某个表的行数
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from mysql.user;
+----------+
| count(*) |
+----------+
| 15 |
+----------+
1 row in set (0.01 sec)
mysql> select * from mysql.user\G //查看某个表的所有数据
*********** 1. row ***********
Host: localhost
User: root
Password: *5CE29D9A76C0BB86C8B4CE8B06D050D817316437
Select_priv: Y
Insert_priv: Y
mysql> select host,user,password from mysql.user; //查看指定的字段
+----------------+---------+-------------------------------------------+
| host | user | password |
+----------------+---------+-------------------------------------------+
| localhost | root | *5CE29D9A76C0BB86C8B4CE8B06D050D817316437 |
| cent01 | root | *5CE29D9A76C0BB86C8B4CE8B06D050D817316437 |
| 127.0.0.1 | root | *5CE29D9A76C0BB86C8B4CE8B06D050D817316437 |
| ::1 | root | *5CE29D9A76C0BB86C8B4CE8B06D050D817316437 |
| localhost | | |
| cent01 | | |
| 127.0.0.1 | user1 | *B012E8731FF1DF44F3D8B26837708985278C3CED |
| localhost | user2 | *B012E8731FF1DF44F3D8B26837708985278C3CED |
| localhost | user1 | *B012E8731FF1DF44F3D8B26837708985278C3CED |
mysql> select * from mysql.user where host like '192.168%'\G //使用%进行模糊查询
**************** 1. row **************
Host: 192.168.133.1
User: user2
Password: *59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0
Select_priv: N
Insert_priv: N
Update_priv: N
插入
mysql> desc t1; //先查看需要更改的表有多少个字段以及字段类型
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| name | char(40) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> insert db3.t1 values(1,'abc'); //按对应的字段输入值
Query OK, 1 row affected (0.01 sec)
mysql> select * from db3.t1; //查看输入后表的变化
+------+------+
| id | name |
+------+------+
| 1 | abc |
+------+------+
1 row in set (0.00 sec)
mysql> insert db3.t1 values (1,2,'c'); //如果字段数不符,报错
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert db3.t1 values (2,c); //字段类型不符,报错
ERROR 1054 (42S22): Unknown column 'c' in 'field list'
更改某一行的值
mysql> select * from db3.t1; //查看原值
+------+------+
| id | name |
+------+------+
| 1 | abc |
| 1 | 2 |
| 2 | c |
+------+------+
3 rows in set (0.00 sec)
mysql> update db3.t1 set name='wewewe' where id='1'; //更改值
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from db3.t1; //更改后的值
+------+--------+
| id | name |
+------+--------+
| 1 | wewewe |
| 1 | wewewe |
| 2 | c |
+------+--------+
3 rows in set (0.00 sec)
清空,删除表
truncate table db3.t1 //清空表的数据,但结构和字段还在
drop table db3.t1 //直接删除表
drop database db3 //直接删除数据库
数据库备份与恢复
mysql备份用mysqldump命令,恢复直接用mysql命令。
[root@cent01 tmp]# mysqldump -uroot -paming db2>/tmp/db2.sql //备份用mysqldump
Warning: Using a password on the command line interface can be insecure.
[root@cent01 tmp]# mysql -uroot -paming db2</tmp/mysql.sql //恢复直接用mysql
Warning: Using a password on the command line interface can be insecure.
mysql> show tables; //对比发现恢复时,不会覆盖备份文件中没有的字段。例如t1表。
+---------------------------+
| Tables_in_db2 |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| t1 |
备份表 mysqldump -uroot -p123456 mysql user > /tmp/user.sql
恢复表 mysql -uroot -p123456 mysql < /tmp/user.sql
备份所有库 mysqldump -uroot -p -A >/tmp/123.sql
只备份表结构 mysqldump -uroot -p123456 -d mysql > /tmp/mysql.sql