MySQL数据库操作

命令行操作

windows环境下

1. 找到安装路劲下的bin文件夹（C:\mysql-8.0.31-winx64\bin），在bin文件夹下按住shift键+点击鼠标右键，选择在此处打开命令窗口(W)
2. 输入命令：mysql -u root -p，随后输入登陆密码

   C:\mysql-8.0.31-winx64\bin>mysql -u root -p
   Enter password: ********
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 257816
   Server version: 8.0.31 MySQL Community Server - GPL
   
   Copyright (c) 2000, 2022, Oracle and/or its affiliates.
   
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   ```
   

3. mysql操作语句

• 比如查询表格行数：SELECT COUNT(*) FROM client_gaodeli_io;，返回表格client_gaodeli_io的行数总共101037行，耗时1.40秒，注意命令行后面需要加分号;。

  mysql> SELECT COUNT(*) FROM client_gaodeli_io;
  +----------+
  | COUNT(*) |
  +----------+
  |   101037 |
  +----------+
  1 row in set (1.40 sec)
  

  查询表格行数不建议用这个语句，太耗时，可以在表中设计个自增字段，通过查询这个字段的最大值来的更有效率，如下，耗时0.00秒

  mysql> SELECT MAX(ID) FROM client_gaodeli_io;
  +---------+
  | MAX(ID) |
  +---------+
  |  103529 |
  +---------+
  1 row in set (0.00 sec)
  

ubantu环境下

如果需要外网连接

打开文件\etc\mysql\mysql.conf.d\mysqld.cnf，把以下几行配置全部屏蔽掉

# sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
# bind-address = 0.0.0.0
# mysqlx-bind-address	= 0.0.0.0
# skip_grant_tables

不屏蔽会有以下情况，这就直接导致外网无法连接

• 跳过网络连接打开

  mysql> show variables like 'skip_networking';
  +-----------------+-------+
  | Variable_name   | Value |
  +-----------------+-------+
  | skip_networking | ON    |
  +-----------------+-------+
  1 row in set (0.00 sec)
  

• 端口号为0

  mysql> show global variables like 'port';
  +---------------+-------+
  | Variable_name | Value |
  +---------------+-------+
  | port          | 0     |
  +---------------+-------+
  1 row in set (0.02 sec)