第 3 章 基礎(chǔ)知識(shí)與 mysql 客戶端

3.2 連接到服務(wù)器

這種登錄方式會(huì)提示輸入密碼

$ mysql -u russell -p

選項(xiàng) -p 和密碼之間不要留空格（這種登錄方式不安全，密碼會(huì)明文顯示）

$ mysql -u russell -p666666

登錄用戶默認(rèn)與當(dāng)前系統(tǒng)用戶名一致

$ mysql -p

登錄后的提示信息

# 命令要以分號(hào)（;）或斜線 +g（\g）結(jié)尾
Welcome to the MySQL monitor.  Commands end with ; or \g.

# 此次連接的標(biāo)識(shí)號(hào)
Your MySQL connection id is 15

# 版本號(hào)
Server version: 5.6.37 MySQL Community Server (GPL)

...

# help 是幫助命令，\c 是清除當(dāng)前輸入語句的命令
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

此命令會(huì)介紹如何使用 mysql

mysql> help

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.

For server side help, type 'help contents'

此命令將 MySQL 或 MariaDB 的各種幫助分門別類地以列表展示出來

mysql> help contents
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
   Account Management
   Administration
   Compound Statements
   Data Definition
   Data Manipulation
   Data Types
   Functions
   Functions and Modifiers for Use with GROUP BY
   Geographic Features
   Help Metadata
   Language Structure
   Plugins
   Procedures
   Storage Engines
   Table Maintenance
   Transactions
   User-Defined Functions
   Utility

此命令會(huì)將所有可用的數(shù)據(jù)操作語句顯示出來

mysql> help Data Manipulation
You asked for help about help category: "Data Manipulation"
For more information, type 'help <item>', where <item> is one of the following
topics:
   CALL
   DELETE
   DO
   DUAL
   HANDLER
   INSERT
   INSERT DELAYED
   INSERT SELECT
   JOIN
   LOAD DATA
   LOAD XML
   REPLACE
   SELECT
   UNION
   UPDATE

查看具體某個(gè)命令的說明

mysql> help DELETE

取消輸入到一半的 SQL 語句

mysql> show databases\c

3.3 開始探索數(shù)據(jù)庫

查看數(shù)據(jù)庫

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

MySQL 不區(qū)分關(guān)鍵字（如 SHOW）的大小寫，所以你可以用 show 甚至 sHoW。然而，數(shù)據(jù)庫、表和列的名字卻可能是區(qū)分大小寫的。

information_schema 數(shù)據(jù)庫包含服務(wù)器的相關(guān)信息。mysql 數(shù)據(jù)庫則存儲(chǔ)著用戶名、密碼和權(quán)限。

3.3.1 第一條 SQL 語句

創(chuàng)建數(shù)據(jù)庫

mysql> create database test;

切換數(shù)據(jù)庫

mysql> use test

創(chuàng)建表

# 當(dāng)前數(shù)據(jù)庫就是 test
mysql> create table books(book_id int, title text, status int);

或

# 當(dāng)前數(shù)據(jù)庫不是 test
mysql> create table test.books(book_id int, title text, status int);

查看表

# 當(dāng)前數(shù)據(jù)庫就是 test
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| books          |
+----------------+

或

# 當(dāng)前數(shù)據(jù)庫不是 test
mysql> show tables from test;
+----------------+
| Tables_in_test |
+----------------+
| books          |
+----------------+

查看表結(jié)構(gòu)

mysql> describe books;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| book_id | int(11) | YES  |     | NULL    |       |
| title   | text    | YES  |     | NULL    |       |
| status  | int(11) | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+

3.3.2 插入和操作數(shù)據(jù)

插入數(shù)據(jù)

mysql> insert into books values(100, 'Heart of Darkness', 0);

mysql> insert into books values(101, 'The Catcher of the Rye', 1);

mysql> insert into books values(102, 'My Antonia', 0);

查詢數(shù)據(jù)

mysql> select * from books;
+---------+------------------------+--------+
| book_id | title                  | status |
+---------+------------------------+--------+
|     100 | Heart of Darkness      |      0 |
|     101 | The Catcher of the Rye |      1 |
|     102 | My Antonia             |      0 |
+---------+------------------------+--------+

WHERE 子句

mysql> select * from books where status = 1;
+---------+------------------------+--------+
| book_id | title                  | status |
+---------+------------------------+--------+
|     101 | The Catcher of the Rye |      1 |
+---------+------------------------+--------+

使每條記錄都分成多行來展示

mysql> select * from books where status = 0 \G
*************************** 1. row ***************************
book_id: 100
  title: Heart of Darkness
 status: 0
*************************** 2. row ***************************
book_id: 102
  title: My Antonia
 status: 0

更新數(shù)據(jù)

mysql> update books set status = 1 where book_id = 102;

同時(shí)更新多個(gè)字段

mysql> update books
    -> set title = 'The Catcher in the Rye', status = 0
    -> where book_id = 101;

3.3.3 再復(fù)雜一點(diǎn)

建立另一個(gè)表

mysql> create table status_names (status_id int, status_name char(8));

mysql> insert into status_names values(0, 'Inactive'), (1, 'Active');

mysql> select * from status_names;
+-----------+-------------+
| status_id | status_name |
+-----------+-------------+
|         0 | Inactive    |
|         1 | Active      |
+-----------+-------------+

表連接

mysql> select book_id, title, status_name
    -> from books join status_names
    -> where status = status_id;
+---------+------------------------+-------------+
| book_id | title                  | status_name |
+---------+------------------------+-------------+
|     100 | Heart of Darkness      | Inactive    |
|     101 | The Catcher in the Rye | Inactive    |
|     102 | My Antonia             | Active      |
+---------+------------------------+-------------+