【MySQL】mysql常用命令

mysql默认端口3306

服务器登录

mysql -uroot -p

输入密码登录

[mysql@yyq mysql]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 73
Server version: 8.0.28-commercial MySQL Enterprise Server - Commercial

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.

mysql> 

查看数据库列表

show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| mysql              |
| sys                |
+--------------------+
2 rows in set (0.01 sec)

mysql> 

切换数据库

use mysql

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> 

查看所有表

show tables;

mysql> show tables;
+--------------------+
| Tables_in_ibase    |
+--------------------+
| a_group            |
| a_info             |
| a_log              |
| a_report           |
+--------------------+
4 rows in set (0.00 sec)

mysql> 

sql文件导入

无需指定数据库，初始化导入

123456是密码，init_schema.sql是导入的文件，./指当前目录下

mysql -uroot -p123456 < ./init_schema.sql

指定数据库导入

123456是密码，yyqdb是导入的数据库，yyqdb.sql为导入的文件

mysql -uroot -p123456 yyqdb < ./yyqdb.sql

数据库下导入

source ./yyqdb.sql

需要先登录数据库，然后source

[mysql@yyq mysql]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 73
Server version: 8.0.28-commercial MySQL Enterprise Server - Commercial

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.

mysql> use yyqdb;
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> source ./yyqdb.sql

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

退出

quit与exit均可

mysql> quit
Bye
[mysql@yyq mysql]#

mysql> exit
Bye
[mysql@yyq mysql]# 

查询所有用户

SELECT User, Host FROM mysql.user;

新建用户

CREATE USER 'test'@'%' IDENTIFIED BY 'test@*20#23';
ALTER USER 'test'@'%' IDENTIFIED WITH mysql_native_password BY 'test@*20#23';
grant select, insert, update, delete on abc_base.* to 'test'@'%';
flush privileges;