常用命令
1.查询库：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
2.切换库
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
3.查看库里的表
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
28 rows in set (0.00 sec)
4.查看表里的字段
mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
5.查看建表语句：
mysql> show create table user\G;
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
`Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
6.查看当前用户
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
7.查看当前使用的数据库：
mysql>
mysql> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)
8.创建库：
mysql>
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
9.创建表
mysql> use db1; create table t1(`id` int(4), `name` char(40));
Database changed
Query OK, 0 rows affected (0.00 sec)
10.查看当前版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.43 |
+-----------+
1 row in set (0.00 sec)
11.查看数据库状态
mysql> show status;
+-----------------------------------------------+-------------+
| Variable_name | Value |
+-----------------------------------------------+-------------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
12.查看各参数 show variables; show variables like ‘max_connect%’
mysql> show variables like 'max_connect%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 100 |
| max_connections | 151 |
+--------------------+-------+
2 rows in set (0.00 sec)
13.修改参数：
mysql> set global max_connect_errors=1000;
Query OK, 0 rows affected (0.00 sec)
14.查看列队show processlist; show full processlist;
mysql> show processlist; show full processlist;
+----+------+-----------------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+---------+------+-------+------------------+
| 2 | root | localhost | NULL | Sleep | 963 | | NULL |
| 3 | root | localhost:55913 | NULL | Sleep | 949 | | NULL |
| 4 | root | localhost | NULL | Sleep | 934 | | NULL |
| 9 | root | localhost:55917 | db1 | Query | 0 | init | show processlist |
+----+------+-----------------+------+---------+------+-------+------------------+
4 rows in set (0.00 sec)
+----+------+-----------------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+---------+------+-------+-----------------------+
| 2 | root | localhost | NULL | Sleep | 963 | | NULL |
| 3 | root | localhost:55913 | NULL | Sleep | 949 | | NULL |
| 4 | root | localhost | NULL | Sleep | 934 | | NULL |
| 9 | root | localhost:55917 | db1 | Query | 0 | init | show full processlist |
+----+------+-----------------+------+---------+------+-------+-----------------------+
4 rows in set (0.00 sec)
常用语句
1.查看表内行数 select count(*) from mysql.user;
mysql> select count(*) from mysql.user;
+----------+
| count(*) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)
2.查看db表内的内容 select * from mysql.db;
mysql> select * from mysql.db\G
*************************** 1. row ***************************
Host: %
Db: test
User:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: N
Execute_priv: N
Event_priv: Y
Trigger_priv: Y
*************************** 2. row ***************************
Host: %
Db: test\_%
User:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: N
Execute_priv: N
Event_priv: Y
Trigger_priv: Y
*************************** 3. row ***************************
Host: 192.168.133.1
Db: db1
User: user2
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N
*************************** 4. row ***************************
Host: %
Db: db1
User: user3
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Execute_priv: Y
Event_priv: Y
Trigger_priv: Y
*************************** 5. row ***************************
Host: 192.168.200.10
Db: db1
User: user2
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N
5 rows in set (0.00 sec
3.查看 db表内含有 db字段的内容 select db from mysql.db;
mysql>
mysql> select db from mysql.db;
+---------+
| db |
+---------+
| db1 |
| test |
| test\_% |
| db1 |
| db1 |
+---------+
5 rows in set (0.00 sec)
4.搜索查看多个字段 select db,user from mysql.db;
mysql> select db,user from mysql.db;
+---------+-------+
| db | user |
+---------+-------+
| db1 | user3 |
| test | |
| test\_% | |
| db1 | user2 |
| db1 | user2 |
+---------+-------+
5 rows in set (0.00 sec)
搜索多个字段时，字段中间要用“，”隔开
- 查询 t host 为 为 0 127.0 的内容 select * from mysql.db where host like ‘192.168.%’;
// An highlighted block
var foo = 'bar';mysql> select * from mysql.db where host like '192.168.%'\G;
*************************** 1. row ***************************
Host: 192.168.133.1
Db: db1
User: user2
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N
*************************** 2. row ***************************
Host: 192.168.200.10
Db: db1
User: user2
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N
2 rows in set (0.00 sec)
6.向 db1.t1 中插入内容 insert into db1.t1 values (1, ‘qqq’);
mysql> insert into db1.t1 values (1, 'qqq');
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> select * from db1.t1;
+------+------+
| id | name |
+------+------+
| 1 | qqq |
+------+------+
1 row in set (0.00 sec)
7.把 1 id=1 的字段内容更新成 aaa ； update db1.t1 set name=‘aaa’ where id=1;
mysql> update db1.t1 set name='aaa' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from db1.t1;
+------+------+
| id | name |
+------+------+
| 1 | aaa |
+------+------+
1 row in set (0.00 sec
8.清空 1 db1.t1 表内的内容 truncate table db1.t1;
mysql> truncate table db1.t1;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from db1.t1;
Empty set (0.00 sec)
9.删除 1 db1.t1 表内的内容 drop table db1.t1;
mysql> drop table db1.t1;
Query OK, 0 rows affected (0.00 sec)
mysql> desc db1.t1;
ERROR 1146 (42S02): Table 'db1.t1' doesn't exist
mysql>
10.清空 db1.t1 数据库 drop database db1;
mysql> drop database db1;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
数据库的备份与恢复
1.备份库
[root@localhost ~]# mysqldump -uroot -plinux mysql > /tmp/mysql.sql
Warning: Using a password on the command line interface can be insecure.
2.恢复库
[root@localhost ~]# mysql -uroot -plinux mysql < /tmp/mysql.sql
Warning: Using a password on the command line interface can be insecure.
3.备份表
[root@localhost ~]# mysqldump -uroot -plinux mysql user > /tmp/user.sql
Warning: Using a password on the command line interface can be insecure.
4.恢复表
[root@localhost ~]# mysql -uroot -plinux mysql < /tmp/user.sql
Warning: Using a password on the command line interface can be insecure.
5.备份所有库
[root@localhost ~]# mysqldump -uroot -p -A > /tmp/123.sql
Enter password:
[root@localhost ~]#
6.只备份表结构
[root@localhost ~]# mysqldump -uroot -p -d mysql > /tmp/mysql.sql
Enter password:
扫一扫
217
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
