MySQL常用命令

最新推荐文章于 2021-09-04 01:20:05 发布

风钤-♡︎

最新推荐文章于 2021-09-04 01:20:05 发布

阅读量170

点赞数

文章标签： mysql linux sql

本文链接：https://blog.csdn.net/qq_50731373/article/details/113536536

版权

常用命令

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:

风钤-♡︎

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL常用命令

常用命令1.查询库：mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test |+--------------------+4 rows in set (0.00 sec)2.切换库my
复制链接

扫一扫