【MySQL】实操第一课 安装及Workbench和shell等的准备

这个系列的内容是在Windows环境中的，对于Linux环境，除了安装过程不同外，其余部分基本都是一样的。

MySQL安装

这部分很简单就直接略过了；实际上是简单到忘记截图了^_

MySQL Workbench

新连接：

访问：

UI：

检索：

mysql shell

以Windows下本机安装的MySQL 5.7为例：

连接：

# 首先进入MySQL Server 5.7的安装目录，每个人的安装位置可能不一样
PS D:\> cd "C:\Program Files\MySQL\MySQL Server 5.7\bin"
PS C:\Program Files\MySQL\MySQL Server 5.7\bin>

# 登录
PS C:\Program Files\MySQL\MySQL Server 5.7\bin> .\mysql.exe -u root -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.43-log MySQL Community Server (GPL)

Copyright (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>




# 或者；对于远程的服务器指定服务器名或者IP
PS C:\Program Files\MySQL\MySQL Server 5.7\bin> .\mysql.exe -u root -p -h localhost
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.43-log MySQL Community Server (GPL)

Copyright (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指令帮助：

# .\mysql.exe --help或者 .\mysql.exe -?
PS C:\Program Files\MySQL\MySQL Server 5.7\bin> .\mysql.exe --help
C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe  Ver 14.14 Distrib 5.7.43, for Win64 (x86_64)
Copyright (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.

Usage: C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe [OPTIONS] [database]
  -?, --help          Display this help and exit.
  -I, --help          Synonym for -?
  --auto-rehash       Enable automatic rehashing. One doesn't need to use
                      'rehash' to get table and field completion, but startup
                      and reconnecting may take a longer time. Disable with
                      --disable-auto-rehash.
                      (Defaults to on; use --skip-auto-rehash to disable.)
  -A, --no-auto-rehash
                      No automatic rehashing. One has to use 'rehash' to get
...

MySQL语句帮助

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.
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.
notee     (\t) Don't write into outfile.
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.
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.

For server side help, type 'help contents'

mysql>

退出：

# 方法1
mysql> exit;
Bye
PS C:\Program Files\MySQL\MySQL Server 5.7\bin>

# 方法2
mysql> \q;
Bye
PS C:\Program Files\MySQL\MySQL Server 5.7\bin>

指令自动补全：

在windows下的mysql client是无法实现的；具体的解释看这里：

https://stackoverflow.com/questions/269653/autocomplete-in-mysql-under-windows

在windows 10及以后的版本里，可以通过使用WSL来解决这个问题：

https://learn.microsoft.com/en-us/windows/wsl/install-win10

或者使用一些第三方的sql shell工具；应该可以找到解决这个问题的工具，但我没有研究过，这里就不说了，有知道的朋友也可以分享给我，感谢。

在linux下很容易实现：

# 方法1：开启自动补全：rehash (\#) Rebuild completion hash.
mysql> \#
# 或者
mysql> rehash

# 方法2：在连接时指定
mysql --auto-rehash -u root -p

# 方法3：修改配置文件,增加以下配置
# - 对所有用户：/etc/my.cnf
# - 对自己：~/.my.cnf
[mysql]
auto-rehash

mysqlsh

启动：

PS D:\> mysqlsh
MySQL Shell 8.0.34

Copyright (c) 2016, 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 '\?' for help; '\quit' to exit.
 MySQL  JS >

帮助：

# 或者\?
MySQL  SQL > \help
The Shell Help is organized in categories and topics. To get help for a
specific category or topic use: \? <pattern>

The <pattern> argument should be the name of a category or a topic.

The pattern is a filter to identify topics for which help is required, it can
use the following wildcards:

- ? matches any single character.
- * matches any character sequence.

The following are the main help categories:

 - Shell Commands Provides details about the available built-in shell commands.
 - SQL Syntax     Entry point to retrieve syntax help on SQL statements.

The available topics include:

- The available shell commands.
- Any word that is part of an SQL statement.
- Command Line - invoking built-in shell functions without entering interactive
  mode.

SHELL COMMANDS

The shell commands allow executing specific operations including updating the
shell configuration.

The following shell commands are available:

 - \                   Start multi-line input when in SQL mode.
 - \connect    (\c)    Connects the shell to a MySQL server and assigns the
                       global session.
 - \disconnect         Disconnects the global session.
 - \edit       (\e)    Launch a system editor to edit a command to be executed.
 - \exit               Exits the MySQL Shell, same as \quit.
 - \G                  Send command to mysql server, display result vertically.
 - \g                  Send command to mysql server.
 - \help       (\?,\h) Prints help information about a specific topic.
 - \history            View and edit command line history.
 - \js                 Switches to JavaScript processing mode.
 - \nopager            Disables the current pager.
 - \nowarnings (\w)    Don't show warnings after every statement.
 - \option             Allows working with the available shell options.
 - \pager      (\P)    Sets the current pager.
 - \py                 Switches to Python processing mode.
 - \quit       (\q)    Exits the MySQL Shell.
 - \reconnect          Reconnects the global session.
 - \rehash             Refresh the autocompletion cache.
 - \show               Executes the given report with provided options and
                       arguments.
 - \source     (\.)    Loads and executes a script from a file.
 - \sql                Executes SQL statement or switches to SQL processing
                       mode when no statement is given.
 - \status     (\s)    Print information about the current global session.
 - \system     (\!)    Execute a system shell command.
 - \use        (\u)    Sets the active schema.
 - \warnings   (\W)    Show warnings after every statement.
 - \watch              Executes the given report with provided options and
                       arguments in a loop.

EXAMPLES
\? sql syntax
      Displays the main SQL help categories.

\? select
      Displays information about the SELECT SQL statement.
 MySQL  SQL > 

切换模式：

 MySQL  JS > \sql
Switching to SQL mode... Commands end with ;
 MySQL  SQL >

连接：

 MySQL  SQL > \connect root@localhost
 Creating a session to 'root@localhost'
Fetching global names for auto-completion... Press ^C to stop.
Your MySQL connection id is 3
Server version: 5.7.43-log MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.
 MySQL  localhost:3306 ssl  SQL >

输入对应账户的密码即可登录，我这里是是设置了记住之前的密码所以不用重新输入密码。

退出：

 \quit