第 3 章 基础知识与 mysql 客户端
3.2 连接到服务器
这种登录方式会提示输入密码
$ mysql -u russell -p
选项 -p 和密码之间不要留空格（这种登录方式不安全，密码会明文显示）
$ mysql -u russell -p666666
登录用户默认与当前系统用户名一致
$ mysql -p
登录后的提示信息
# 命令要以分号（;）或斜线 +g（\g）结尾
Welcome to the MySQL monitor. Commands end with ; or \g.
# 此次连接的标识号
Your MySQL connection id is 15
# 版本号
Server version: 5.6.37 MySQL Community Server (GPL)
...
# help 是帮助命令，\c 是清除当前输入语句的命令
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
此命令会介绍如何使用 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
此命令会将所有可用的数据操作语句显示出来
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
查看具体某个命令的说明
mysql> help DELETE
取消输入到一半的 SQL 语句
mysql> show databases\c
3.3 开始探索数据库
查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
MySQL 不区分关键字（如 SHOW）的大小写，所以你可以用 show 甚至 sHoW。然而，数据库、表和列的名字却可能是区分大小写的。
information_schema 数据库包含服务器的相关信息。mysql 数据库则存储着用户名、密码和权限。
3.3.1 第一条 SQL 语句
创建数据库
mysql> create database test;
切换数据库
mysql> use test
创建表
# 当前数据库就是 test
mysql> create table books(book_id int, title text, status int);
或
# 当前数据库不是 test
mysql> create table test.books(book_id int, title text, status int);
查看表
# 当前数据库就是 test
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| books |
+----------------+
或
# 当前数据库不是 test
mysql> show tables from test;
+----------------+
| Tables_in_test |
+----------------+
| books |
+----------------+
查看表结构
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 插入和操作数据
插入数据
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);
查询数据
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
更新数据
mysql> update books set status = 1 where book_id = 102;
同时更新多个字段
mysql> update books
-> set title = 'The Catcher in the Rye', status = 0
-> where book_id = 101;
3.3.3 再复杂一点
建立另一个表
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 |
+---------+------------------------+-------------+
