mysql 数据库服务命令
2021-06-29 本文已影响0人
Geroge1226
Mysql数据库作为客户端/服务端架构，客户端通过命令操作服务端数据库数据。以下记录常见的服务应用相关操作命令。
1、mysql服务器命令操作方式
- 启动MySQL服务:
start
sudo /usr/local/mysql/support-files/mysql.server start
- 停止MySQL服务:
stop
sudo /usr/local/mysql/support-files/mysql.server stop
- 重启MySQL服务:
restart
sudo /usr/local/mysql/support-files/mysql.server restart
2、客户端操作命令
- 登录到数据库:
mysql -h ip -u root -p
[sdc@lsyPro ~ ]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 626
Server version: 5.7.29-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
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是 登录Mysql数据库命令
-h 指定为访问的ip 默认走本地，可远程
-u 指定用户
-p 指定密码
- 展示数据库
show databases
mysql> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| job-center |
| my-famliy |
| mysel_finance |
| mysql |
| performance_schema |
| pousheng_source |
| sys |
| xxl_job |
| yy_sports |
+--------------------+
10 rows in set (0.01 sec)
mysql>
- 选择数据库
use db
mysql> use mysel_finance;
Database changed
- 展示数据库表 ：
show tables
mysql> show tables
-> ;
+-------------------------+
| Tables_in_mysel_finance |
+-------------------------+
| stock_trade_log |
+-------------------------+
1 row in set (0.01 sec)
mysql>
- 展示数据库存储引擎：
show engines
mysql> show engines
-> ;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
mysql>
说明：客户端命令结尾要加“;”或者 “\g”, “\G”，其中“;”与“\g”作用相同，“\G”更加美观
- 查看mysql数据存储路径:
SHOW VARIABLES LIKE 'datadir';
mysql> SHOW VARIABLES LIKE 'datadir';
+---------------+------------------------+
| Variable_name | Value |
+---------------+------------------------+
| datadir | /usr/local/mysql/data/ |
+---------------+------------------------+
1 row in set (0.05 sec)
-
查看数据表基本结构：
describe 表名，简写desc
mysql> desc stock_trade_log;
+------------+--------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------------------+------+-----+---------+----------------+
| id | int(3) unsigned zerofill | NO | PRI | NULL | auto_increment |
| trade_time | datetime | YES | | NULL | |
+------------+--------------------------+------+-----+---------+----------------+
2 rows in set (0.06 sec)
- 查看数据表详细结构：
show create table 表名
show create table stock_trade_log;
+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| stock_trade_log | CREATE TABLE `stock_trade_log` (
`id` int(3) unsigned zerofill NOT NULL AUTO_INCREMENT,
`trade_time` datetime DEFAULT NULL COMMENT '交易时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22334456 DEFAULT CHARSET=utf8 COMMENT='股票交易记录表' |
+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
