linux下终端操作mysql数据库

目录

一．检查mysql是否安装

1.  查看文件安装路径

2.  查询运行文件所在路径(文件夹地址)

二．登录mysql

三．列出mysql全部用户

四．常用指令

１．查看全部数据库

 ２．选择数据库

３．查看该数据库的全部数据表

４．显示表结构

５．运行sql文件

６．显示数据库概况

五．查看帮助指令

六

一．检查mysql是否安装

1.  查看文件安装路径

# gr @ gr-System-Product-Name in ~ [10:05:21] 
$ whereis mysql
mysql: /usr/bin/mysql /usr/lib/mysql /etc/mysql /usr/share/man/man1/mysql.1.gz

2.  查询运行文件所在路径(文件夹地址)

# gr @ gr-System-Product-Name in ~ [10:05:26] 
$ which mysql     
/usr/bin/mysql

二．登录mysql

# gr @ gr-System-Product-Name in ~ [10:36:49] 
$ mysql -u用户名 -p密码
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 55
Server version: 8.0.32 MySQL Community Server - GPLCopyright (c) 2000, 2023, 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> 

三．列出mysql全部用户

必须要用root账户登录，进入mysql环境

# gr @ gr-System-Product-Name in ~ [10:39:50] 
$ mysql -uroot -p密码
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 56
Server version: 8.0.32 MySQL Community Server - GPLCopyright (c) 2000, 2023, 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> 

mysql> SELECT User, Host FROM mysql.user;
+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| ga               | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

四．常用指令

１．查看全部数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| test               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.02 sec)

 ２．选择数据库

mysql> use mysql;
Database changed

３．查看该数据库的全部数据表

mysql> show tables;
+------------------------------------------------------+
| Tables_in_mysql                                      |
+------------------------------------------------------+
| columns_priv                                         |
| component                                            |
| db                                                   |
| default_roles                                        |
| engine_cost                                          |
| func                                                 |
| general_log                                          |
| global_grants                                        |
| user                                                 |
+------------------------------------------------------+
38 rows in set (0.00 sec)

４．显示表结构

mysql> desc user;

５．运行sql文件

mysql> use mysql
Database changed
mysql> source /user/test.sql

６．显示数据库概况

　　当前使用的数据库，当前用户，mysql版本信息

mysql> status
--------------
mysql  Ver 8.0.32 for Linux on x86_64 (MySQL Community Server - GPL)Connection id:		59
Current database:	mysql
Current user:		root@localhost
SSL:			Not in use
Current pager:		less
Using outfile:		''
Using delimiter:	;
Server version:		8.0.32 MySQL Community Server - GPL
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
UNIX socket:		/var/run/mysqld/mysqld.sock
Binary data as:		Hexadecimal
Uptime:			5 days 2 min 4 secThreads: 4  Questions: 2198  Slow queries: 0  Opens: 580  Flush tables: 3  Open tables: 499  Queries per second avg: 0.005
--------------

五．查看帮助指令

mysql> \?For information about MySQL products and services, visit:http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:https://shop.mysql.com/List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
resetconnection(\x) Clean session context.
query_attributes Sets string parameters (name1 value1 name2 value2 ...) for the next query to pick up.
ssl_session_data_print Serializes the current SSL session data to stdout or fileFor server side help, type 'help contents'

六．权限问题

１．查询某用户的权限

mysql> show grants for 'ga'@'%';
+---------------------------------------------------+
| Grants for gauture@%                              |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO `ga`@`%`                    |
| GRANT ALL PRIVILEGES ON `table01`.* TO `ga`@`%`   |
| GRANT ALL PRIVILEGES ON `table01`.* TO `ga`@`%`   |
+---------------------------------------------------+
3 rows in set (0.00 sec)

２．给用户添加权限

mysql> GRANT ALL PRIVILEGES ON test.* TO 'ga'@'%';
Query OK, 0 rows affected (0.01 sec)

七．退出

mysql> exit
Bye