第1章 MysQL客户端命令介绍

mysql：用于数据库连接管理并将用户SQL语句发送到服务器。
mysqladmin：命令行管理工具。
mysqldump：备份数据库和表的内容。

1.1 mysql命令

mysql命令使用以下命令管理数据库：

命令接口自带命令
DDL：数据定义语言
DCL：数据控制语言
DML：数据操作语言

1.1.1 接口自带功能

mysql> \h

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'. # 查看命令帮助
# 结束一条命令的输入并且不执行，多用于输入错误命令后，也可使用CTRL+C
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.# 执行外部SQL脚本
status (\s) Get status information from the server. # 查看当前数据库状态
system (\!) Execute a system shell command. # 执行linux命令
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'

1.1.1.1 source命令使用

source在mysql中处理输入文件，如果这些文件包含SQL语句，则被称为“脚本文件”或“批处理文件”。

mysql> source world.sql # 建议用法
# 或
[root@db02 ~]# mysql -uroot -p12345678 < world.sql

1.2 mysqladmin命令

mysqladmin是DBA的命令行客户端工具。

1.2.1 mysqladmin的功能

“强制回应 (Ping)”服务器。
关闭服务器。
创建和删除数据库。
显示服务器和版本信息。
显示或重置服务器状态变量。
设置口令。
重新刷新授权表。
刷新日志文件和高速缓存。
启动和停止复制。
显示客户机信息。

1.2.2 mysqladmin命令帮助及基本语法

[root@db02 ~]# mysqladmin --help
Usage: mysqladmin [OPTIONS] command command....
--bind-address=name IP address to bind to.
-c, --count=# Number of iterations to make. This works with -i
(--sleep) only.
--debug-check Check memory and open file usage at exit.
--debug-info Print some debug info at exit.
-f, --force Don't ask for confirmation on drop database; with
multiple commands, continue even if an error occurs.
-C, --compress Use compression in server/client protocol.
--character-sets-dir=name
Directory for character set files.
--default-character-set=name
Set the default character set.
- , --help Display this help and exit.
-h, --host=name Connect to host.
-b, --no-beep Turn off beep on error.
-p, --password[=name]
Password to use when connecting to server. If password is
not given it's asked from the tty.
-P, --port=# Port number to use for connection or 0 for default to, in
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/services, built-in default (3306).
--protocol=name The protocol to use for connection (tcp, socket, pipe,
memory).
-r, --relative Show difference between current and previous values when
used with -i. Currently only works with extended-status.
--secure-auth Refuse client connecting to server if it uses old
(pre-4.1.1) protocol.
(Defaults to on; use --skip-secure-auth to disable.)
-s, --silent Silently exit if one can't connect to server.
-S, --socket=name The socket file to use for connection.
-i, --sleep=# Execute commands repeatedly with a sleep between.
--ssl Enable SSL for connection (automatically enabled with
other flags).
--ssl-ca=name CA file in PEM format (check OpenSSL docs, implies
--ssl).
--ssl-capath=name CA directory (check OpenSSL docs, implies --ssl).
--ssl-cert=name X509 cert in PEM format (implies --ssl).
--ssl-cipher=name SSL cipher to use (implies --ssl).
--ssl-key=name X509 key in PEM format (implies --ssl).
--ssl-crl=name Certificate revocation list (implies --ssl).
--ssl-crlpath=name Certificate revocation list path (implies --ssl).
--ssl-verify-server-cert
Verify server's "Common Name" in its cert against
hostname used when connecting. This option is disabled by
default.
--ssl-mode=name SSL connection mode.
-u, --user=name User for login if not current user.
-v, --verbose Write more information.
-V, --version Output version information and exit.
-E, --vertical Print output vertically. Is similar to --relative, but
prints output vertically.
-w, --wait[=#] Wait and retry if connection is down.
--connect-timeout=#
--shutdown-timeout=#
--plugin-dir=name Directory for client-side plugins.
--default-auth=name Default authentication client-side plugin to use.
--enable-cleartext-plugin
Enable/disable the clear text authentication plugin.

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql-5.6.36/etc/my.cnf ~/.my.cnf

The following options may be given as the first argument:
--print-defaults Print the program argument list and exit.
--no-defaults Don't read default options from any option file,except for login file.
--defaults-file=# Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
--defaults-group-suffix=#
Also read groups with concat(group, suffix)
--login-path=# Read this path from the login file.

Where command is a one or more of: (Commands may be shortened)
create databasename Create a new database
debug Instruct server to write debug information to log
drop databasename Delete a database and all its tables
extended-status Gives an extended status message from the server
flush-hosts Flush all cached hosts
flush-logs Flush all logs
flush-status Clear status variables
flush-tables Flush all tables
flush-threads Flush the thread cache
flush-privileges Reload grant tables (same as reload)
kill id,id,... Kill mysql threads
password [new-password] Change old password to new-password in current format
old-password [new-password] Change old password to new-password in old format
ping Check if mysqld is alive
processlist Show list of active threads in server
reload Reload grant tables
refresh Flush all tables and close and open logfiles
shutdown Take server down
status Gives a short status message from the server
start-slave Start slave
stop-slave Stop slave
variables Prints variables available
version Get version info from server

1.2.3 mysqladmin示例

version

[root@db02 ~]# mysqladmin -uroot -p12345678 version
Warning: Using a password on the command line interface can be insecure.
mysqladmin Ver 8.42 Distrib 5.6.36, for Linux on x86_64
Copyright (c) 2000, 2017, 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.

Server version 5.6.36
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /usr/local/mysql-5.6.36/tmp/mysql.sock
Uptime: 2 hours 16 min 48 sec

Threads: 1 Questions: 10862 Slow queries: 0 Opens: 79 Flush tables: 1 Open tables: 69 Queries per second avg: 1.323

status

[root@db02 ~]# mysqladmin -uroot -p12345678 status
Warning: Using a password on the command line interface can be insecure.
Uptime: 8216 Threads: 1 Questions: 10864 Slow queries: 0 Opens: 79 Flush tables: 1 Open tables: 69 Queries per second avg: 1.322

ping

[root@db02 ~]# mysqladmin -uroot -p12345678 ping
Warning: Using a password on the command line interface can be insecure.

processlist

[root@db02 ~]# mysqladmin -uroot -p12345678 processlist
Warning: Using a password on the command line interface can be insecure.
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----+---------+------+-------+------------------+
| 29 | root | localhost | | Query | 0 | init | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

shutdown

[root@db02 ~]# mysqladmin -uroot -p12345678 shutdown
Warning: Using a password on the command line interface can be insecure.
[root@db02 ~]# netstat -lntup | grep 3306
[root@db02 ~]#

1.3 mysqldump命令

mysqldump数据库备份工具。详细使用方式，见备份恢复章节内容。

第2章 MySQL获取帮助的方法细讲

MySQL已经将详细的帮助方法内嵌如MySQL程序中，所以只要执行help命令即可查看到完整的帮助信息。

2.1 help命令使用

2.1.1 查看完整的 SQL 类别列表

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

2.1.2 有关特定 SQL 类别或语句的帮助

mysql> help Account Management
You asked for help about help category: "Account Management"
For more information, type 'help <item>', where <item> is one of the following
topics:
ALTER USER
CREATE USER
DROP USER
GRANT
RENAME USER
REVOKE
SET PASSWORD

2.1.3 有关与状态相关的 SQL 语句的帮助

mysql> help status;
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
SHOW
SHOW ENGINE
SHOW FUNCTION STATUS
SHOW MASTER STATUS
SHOW PROCEDURE STATUS
SHOW SLAVE STATUS
SHOW STATUS
SHOW TABLE STATUS

2.1.4 查看命令使用方法

mysql> help GRANTS
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
SHOW
SHOW GRANTS

mysql> help GRANT
Name: 'GRANT'
Description:
Syntax:
GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user [auth_option] [, user [auth_option]] ...
[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
[WITH {GRANT OPTION | resource_option} ...]

GRANT PROXY ON user
TO user [, user] ...
[WITH GRANT OPTION]

object_type: {
TABLE
| FUNCTION
| PROCEDURE
}

priv_level: {
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
}

user:
(see http://dev.mysql.com/doc/refman/5.6/en/account-names.html)

auth_option: {
IDENTIFIED BY 'auth_string'
| IDENTIFIED BY PASSWORD 'hash_string'
| IDENTIFIED WITH auth_plugin
| IDENTIFIED WITH auth_plugin AS 'hash_string'
}

tls_option: {
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'
}

resource_option: {
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
}
...省略部分输出内容...

第3章 DDL语句之管理数据库

3.1 查看数据库

【语法】：

SHOW DATABASES;

【示例】：

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| leon |
| mysql |
| performance_schema |
| test |
| world |
+--------------------+

6 rows in set (0.00 sec)

mysql> show databases like '%le%';
+-----------------+
| Database (%le%) |
+-----------------+
| leon |
+-----------------+
1 row in set (0.00 sec)

3.2 获取命令帮助

【语法】：

 命令

help 命令

【示例】：

mysql> show databases;
Name: 'SHOW DATABASES'
Description:
Syntax:
SHOW {DATABASES | SCHEMAS}
[LIKE 'pattern' | WHERE expr]

3.3 创建数据库

【语法】：

CREATE DATABASE 数据库名;

【示例】：

mysql> create database 39team;

3.4 查看建库的语句

【语法】：

SHOW DATABASE 数据库名;

【示例】：

mysql> show create database 39team;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| 39team | CREATE DATABASE `39team` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

3.5 查找字符集和校对规则.

【语法】：

SHOW CHARACTER SET;

【示例】：

mysql> show character set;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+----------+-----------------------------+---------------------+--------+
40 rows in set (0.00 sec)

3.6 指定字符集建库

【语法】：

CREATE DATABASE 数据库名 CHARACTER SET 字符集名 COLLATE 字符集校对规则
CREATE DATABASE 数据库名 CHARSET 字符集名 COLLATE 字符集校对规则

【示例】：

mysql> create database 39team character set utf8 collate utf8_general_ci;

3.7 更改库的字符集

【语法】：

ALTER DATABASE 数据库名 CHARSET 字符集 COLLATE 字符集校对规则

【示例】：

mysql> alter database 39team charset gbk collate gbk_chinese_ci;

3.8 删除数据库

【语法】：

DROP DATABASE 数据库名;

【示例】：

mysql> drop database 39team;

3.9 进入数据库

【语法】：

USE 数据库名;

【示例】：

mysql> use mysql;

3.10 查看当前所在的库

【语法】：

SELECT DATABASE() FROM DUAL;
SELECT DATABASE();

【示例】：

mysql> mysql> select database() from dual;
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)

第4章 DDL语句之管理用户

4.1 创建用户

【语法】：

CREATE USER '用户'@'主机域' IDENTIFIED BY '密码';

【示例】：此时创建的用户只有连接权限

mysql> create user 'leon'@'10.0.0.%' identified by '123456';

提示：企业里创建用户一般是授权一个内网网段登录，最常见的网段写法有两种：

方法1：172.16.1.%（%为通配符，匹配所有内容）。

方法2：172.16.1.0/255.255.255.0，但是不能使用172.16.1.0/24

4.2 查看当前用户信息

【语法】：

SELECT 用户信息 FROM MYSQL.USER;

【示例】：

mysql> select user,host,password from mysql.user;
+------+-----------+-------------------------------------------+
| user | host | password |
+------+-----------+-------------------------------------------+
| root | localhost | *84AAC12F54AB666ECFC2A83C676908C8BBC381B1 |
| leon | 10.0.0.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

4.3 查看当前登录的用户

【语法】：

SELECT USER() FROM DUAL;
SELECT USER();

【示例】：

mysql> select user() from dual;
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

4.4 删除用户

【语法】：

DROP USER '用户名'@'主机域'; # 推荐使用方法
DELETE FROM MYSQL.USER WHERE USER='用户名' AND HOST='主机域';

【示例】：

mysql> drop user 'leon'@'10.0.0.%';
# 或
mysql> delete from mysql.user where user='leon' and host='10.0.0.%';
mysql> flush privileges; # 使用delete删除完后需要刷新一下权限

提示：delete操作是直接从表中删除数据，相对来说不安全，所以推荐使用MySQL提供的drop方式进行删除。

4.5 查看用户对应的权限

【语法】：

SHOW GRANTS FOR '用户名'@'主机域';

【示例】：

mysql> mysql> show grants for 'leon'@'10.0.0.%';
+---------------------------------------------------------------------------------------------+
| Grants for [email protected].% |
+---------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'leon'@'10.0.0.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DD' |
+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

提示：USAGE权限代表只能登陆，其他操作都不可以。

4.6 给用户授权

【语法】：

GRANT 权限 ON 库名.表名 TO '用户名'@'主机域';

【示例】：

mysql> grant all on leon.* to 'leon'@'172.16.1.%';

4.7 查看授权权限

【语法】：

SHOW GRANTS FOR '用户名'@'主机域';

【示例】：

mysql> show grants for 'leon'@'172.16.1.%';
+----------------------------------------------------------------------------------------------+
| Grants for [email protected].% |
+----------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'leon'@'172.16.1.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DD'|
| GRANT ALL PRIVILEGES ON `leon`.* TO 'leon'@'172.16.1.%' |
+----------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

4.8 创建用户的同时授权

【语法】：

GRANT 权限 ON 库名.表名 TO '用户名'@'主机域' IDENTIFIED BY '密码';

【示例】：

mysql> grant all on leon.* to 'shadow'@'172.16.1.4%' identified by '123456';

4.9 授权和root用户一样的权限

【语法】：

GRANT ALL ON *.* TO '用户名'@'LOCALHOST' IDENTIFIED BY '密码' WITH GRANT OPTION;

【示例】：

mysql> grant all on *.* to 'system'@'localhost' identified by '123456' with grant option;

4.10 回收权限

【语法】：

REVOKE 权限 ON 库名.表名 FROM '用户名'@'主机域';

【示例】：

mysql> revoke all on leon.* from leon@'172.16.1.%';

提示：可以授权的用户权限包括：

INSERT,SELECT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE

4.11 BLOG授权实例

# 创建BLOG时的授权
mysql> grant select,insert,update,delete,create,drop on blog.* to 'blog'@'172.16.1.%' identified by '123456';
# BLOG创建好后收回权限
mysql> revoke create,drop on blog.* from 'blog'@'172.16.1.%';
# 实际权限
mysql> show grants for 'blog'@'172.16.1.%';
+----------------------------------------------------------------------------------------------+
| Grants for [email protected].% |
+----------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'blog'@'172.16.1.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DD'|
| GRANT SELECT, INSERT, UPDATE, DELETE ON `leon`.* TO 'blog'@'172.16.1.%' |
+----------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

提示：拥有多条权限用户的最终权限是所有权限集合后的并集。

第5章 DDL语句之管理表与案例介绍

表的属性包括：字段、数据类型、索引、默认字符集、默认引擎等。

5.1 查看库中的表

【语法】：

SHOW TABLES;

【示例】：

mysql> use test;
Database changed

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
| t2 |
+----------------+
2 rows in set (0.00 sec)

5.2 创建表

【语法】：

CREATE TABLE 表名（<字段名1> <类型1> ,…<字段名n> <类型n>）ENGINE=引擎名

【示例】：

mysql> create table t1(id int);

5.3 查看建表语句

【语法】：

SHOW CREATE TABLE 表名;

【示例】：

mysql> show create table t1;
+-------+--------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

5.4 查看表结构

【语法】：

DESC 表名;

【示例】：

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

5.5 更改表名

【语法】：

RENAME TABLE 原表名 TO 新表名;
ALTER TABLE 原表名 RENAME TO 新表名;

【示例】：

mysql> rename table t1 to table1;

5.6 修改表结构

5.6.1 原表结构

mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(20) | NO | | NULL | |
| name | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

5.6.2 在结尾增加列

【语法】：

ALTER TABLE 表名 ADD <字段名1> <类型1>;

【示例】：

mysql> alter table t2 add sex char(10) not null;

mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(20) | NO | | NULL | |
| name | varchar(20) | NO | | NULL | |
| sex | char(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

5.6.3 在某一列后增加列

【语法】：

ALTER TABLE 表名 ADD <新字段名> <类型> AFTER <原字段>;

【示例】：

mysql> alter table t2 add age int(10) after name;

mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(20) | NO | | NULL | |
| name | varchar(20) | NO | | NULL | |
| age | int(10) | YES | | NULL | |
| sex | char(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

5.6.4 在首列前增加

【语法】：

ALTER TABLE 表名 ADD <新字段名> <类型> FIRST;

【示例】：

mysql> alter table t2 add address char(100) first;

mysql> desc t2;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| address | char(100) | YES | | NULL | |
| id | int(20) | NO | | NULL | |
| name | varchar(20) | NO | | NULL | |
| age | int(10) | YES | | NULL | |
| sex | char(10) | NO | | NULL | |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

5.6.5 删除表内某一列

【语法】：

ALTER TABLE 表名 DROP <字段名>;

【示例】：

mysql> alter table t2 drop address;

mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(20) | NO | | NULL | |
| name | varchar(20) | NO | | NULL | |
| age | int(10) | YES | | NULL | |
| sex | char(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

5.6.6 同时增加多列

【语法】：

ALTER TABLE 表名 ADD <字段名1> <类型1>,ADD <字段名2> <类型2>...;

【示例】：

mysql> alter table t2 add qq int(10) after age, add mail char(20) first;

mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| mail | char(20) | YES | | NULL | |
| id | int(20) | NO | | NULL | |
| name | varchar(20) | NO | | NULL | |
| age | int(10) | YES | | NULL | |
| qq | int(10) | YES | | NULL | |
| sex | char(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

5.6.7 更改某一字段的类型

【语法】：

ALTER TABLE 表名 MODIFY <字段名> <新类型>;

【示例】：

mysql> alter table t2 modify qq char(20) after age;

mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| mail | char(20) | YES | | NULL | |
| id | int(20) | NO | | NULL | |
| name | varchar(20) | NO | | NULL | |
| age | int(10) | YES | | NULL | |
| qq | char(20) | YES | | NULL | |
| sex | char(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

5.6.8 更改某一字段名和类型

【语法】：

ALTER TABLE 表名 CHANGE <原字段名> <新字段名> <类型>;

【示例】：

mysql> alter table t2 change qq msn char(10) after name;

mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| mail | char(20) | YES | | NULL | |
| id | int(20) | NO | | NULL | |
| name | varchar(20) | NO | | NULL | |
| msn | char(10) | YES | | NULL | |
| age | int(10) | YES | | NULL | |
| sex | char(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

第6章 DML语句之管理表中的数据

6.1 创建测试环境

[root@db02 ~]# mysql -uroot -p12345678

mysql> create database leon;
Query OK, 1 row affected (0.00 sec)

mysql> use leon;
Database changed
mysql> CREATE TABLE test (
-> id int(4) NOT NULL AUTO_INCREMENT,
-> name char(20) NOT NULL,
-> PRIMARY KEY(id)
-> );
Query OK, 0 rows affected (0.20 sec)

mysql> show create table test\G;
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> desc test;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | NULL | |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

6.2 插入数据

【语法】：

INSERT INTO <表名> [( <字段名1>[,..<字段名n > ])] VALUES ( 值1 )[, ( 值n )];

【示例】：

mysql> insert into test(id,name) values(1,'leon');
Query OK, 1 row affected (1.88 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | leon |
+----+------+
1 row in set (0.00 sec)

mysql> insert into test(name) values('shadow');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+----+--------+
| id | name |
+----+--------+
| 1 | leon |
| 2 | shadow |
+----+--------+
2 rows in set (0.00 sec)

mysql> insert into test values(3,'shuaige');
Query OK, 1 row affected (1.90 sec)

mysql> insert into test values(4,'meinv'),(5,'wangzi');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from test;
+----+---------+
| id | name |
+----+---------+
| 1 | leon |
| 2 | shadow |
| 3 | shuaige |
| 4 | meinv |
| 5 | wangzi |
+----+---------+
5 rows in set (0.00 sec)

# 批量添加数据
mysql> INSERT INTO test VALUES(1,'leon'),(2,'shadow'),(3,'shuaige'),(4,'meinv'),(5,'wangzi');
Query OK, 5 rows affected (1.90 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from test;
+----+---------+
| id | name |
+----+---------+
| 1 | leon |
| 2 | shadow |
| 3 | shuaige |
| 4 | meinv |
| 5 | wangzi |
+----+---------+
5 rows in set (0.00 sec)

6.3 删除数据

【语法】：

DELETE FROM 表名 [WHERE <字段>=（值）]; # 逻辑删除，一行一行删
TRUNCATE TABLE 表名; # 物理删除，pages(block)，效率高

【示例】：

mysql> delete from test where id=3;
Query OK, 1 row affected (1.65 sec)

mysql> select * from test;
+----+--------+
| id | name |
+----+--------+
| 1 | leon |
| 2 | shadow |
| 4 | meinv |
| 5 | wangzi |
+----+--------+
4 rows in set (0.00 sec)

mysql> delete from test;
Query OK, 5 rows affected (0.00 sec)

mysql> select * from test;
Empty set (0.00 sec)

6.4 修改数据

【语法】：

UPDATE 表名 SET <字段>=（值）[WHERE <字段>=(值)];

【示例】：

mysql> update test set name='guowang' where id=5;
Query OK, 1 row affected (2.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from test;
+----+---------+
| id | name |
+----+---------+
| 1 | leon |
| 2 | shadow |
| 3 | shuaige |
| 4 | meinv |
| 5 | guowang |
+----+---------+
5 rows in set (0.00 sec)

6.5 严格条件

开启严格模式，此时delete、update必须使用where确定条件，否则执行会报错，这样可以防止不加条件误删或误更改操作，开发过程中必须使用where语句进行位置确定。

【语法】：

[root@db02 ~]# mysql -U

【示例】：

[root@db02 ~]# mysql -uroot -p12345678 -U

mysql> use leon;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

# 不使用where语句会报错，无法执行
mysql> update test set name='guowang';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

# 使用where语句后正常执行
mysql> update test set name='wangzi' where id=5;
Query OK, 1 row affected (1.90 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from test;
+----+---------+
| id | name |
+----+---------+
| 1 | leon |
| 2 | shadow |
| 3 | shuaige |
| 4 | meinv |
| 5 | wangzi |
+----+---------+
5 rows in set (0.00 sec)

提示：至于防止误操作导致数据库故障案例：http://oldboy.blog.51cto.com/2561410/1321061

6.6 伪删除

伪删除的意思是不删除数据，而是给数据增加一个状态列，只有两个值：1标记为存在的表；0标记为删除的表，当想删除表时只要将相应行的状态列值置为0即可，其他操作（增加、修改、查询）时只要条件增加’where state=1’即可。

# 增加状态列
mysql> alter table test add state tinyint(2) not null default 1;
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from test;
+----+---------+-------+
| id | name | state |
+----+---------+-------+
| 1 | leon | 1 |
| 2 | shadow | 1 |
| 3 | shuaige | 1 |
| 4 | meinv | 1 |
| 5 | wangzi | 1 |
+----+---------+-------+
5 rows in set (0.00 sec)

# 模拟删除表列
mysql> update test set state=0 where id=5;
Query OK, 1 row affected (1.90 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from test;
+----+---------+-------+
| id | name | state |
+----+---------+-------+
| 1 | leon | 1 |
| 2 | shadow | 1 |
| 3 | shuaige | 1 |
| 4 | meinv | 1 |
| 5 | wangzi | 0 |
+----+---------+-------+
5 rows in set (0.00 sec)

# 模拟查询存在的表列
mysql> select * from test where state=1;
+----+---------+-------+
| id | name | state |
+----+---------+-------+
| 1 | leon | 1 |
| 2 | shadow | 1 |
| 3 | shuaige | 1 |
| 4 | meinv | 1 |
+----+---------+-------+
4 rows in set (0.00 sec)

第7章 SELECT检索数据

【语法】：

SELECT <字段1，字段2，...> FROM < 表名 > WHERE < 表达式 > AND <表达式 >;
# 其中，select、from、where是不能随便改的，是关键字，支持大小写

7.1 常规查询

mysql> select id from test;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+----+
5 rows in set (0.00 sec)

mysql> select id,state from test;
+----+-------+
| id | state |
+----+-------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 0 |
+----+-------+
5 rows in set (0.00 sec)

mysql> select * from test;
+----+---------+-------+
| id | name | state |
+----+---------+-------+
| 1 | leon | 1 |
| 2 | shadow | 1 |
| 3 | shuaige | 1 |
| 4 | meinv | 1 |
| 5 | wangzi | 0 |
+----+---------+-------+
5 rows in set (0.00 sec)

7.2 带条件语句查询

mysql> select * from test where id=1;
+----+------+-------+
| id | name | state |
+----+------+-------+
| 1 | leon | 1 |
+----+------+-------+
1 row in set (0.00 sec)

mysql> select * from test where id = 1 or state = 1;
+----+---------+-------+
| id | name | state |
+----+---------+-------+
| 1 | leon | 1 |
| 2 | shadow | 1 |
| 3 | shuaige | 1 |
| 4 | meinv | 1 |
+----+---------+-------+
4 rows in set (0.00 sec)

mysql> select * from test where id=1 and state=1;
+----+------+-------+
| id | name | state |
+----+------+-------+
| 1 | leon | 1 |
+----+------+-------+
1 row in set (0.00 sec)

mysql> select * from test where id=1 and state=0;
Empty set (0.00 sec)

7.3 = <> != > <查询

mysql> select * from test where id<>1;
+----+---------+-------+
| id | name | state |
+----+---------+-------+
| 2 | shadow | 1 |
| 3 | shuaige | 1 |
| 4 | meinv | 1 |
| 5 | wangzi | 0 |
+----+---------+-------+
4 rows in set (0.01 sec)

mysql> select * from test where id != 1 and state = 1;
+----+---------+-------+
| id | name | state |
+----+---------+-------+
| 2 | shadow | 1 |
| 3 | shuaige | 1 |
| 4 | meinv | 1 |
+----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from test where id>1 and id <4;
+----+---------+-------+
| id | name | state |
+----+---------+-------+
| 2 | shadow | 1 |
| 3 | shuaige | 1 |
+----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from test where id>4 or id <2;
+----+--------+-------+
| id | name | state |
+----+--------+-------+
| 1 | leon | 1 |
| 5 | wangzi | 0 |
+----+--------+-------+
2 rows in set (0.00 sec)

7.4 排序查询

mysql> select * from test where state=1 order by name;
+----+---------+-------+
| id | name | state |
+----+---------+-------+
| 1 | leon | 1 |
| 4 | meinv | 1 |
| 2 | shadow | 1 |
| 3 | shuaige | 1 |
+----+---------+-------+
4 rows in set (0.00 sec)

mysql> select * from test where state=1 order by name asc;
+----+---------+-------+
| id | name | state |
+----+---------+-------+
| 1 | leon | 1 |
| 4 | meinv | 1 |
| 2 | shadow | 1 |
| 3 | shuaige | 1 |
+----+---------+-------+
4 rows in set (0.00 sec)

mysql> select * from test where state=1 order by name desc;
+----+---------+-------+
| id | name | state |
+----+---------+-------+
| 3 | shuaige | 1 |
| 2 | shadow | 1 |
| 4 | meinv | 1 |
| 1 | leon | 1 |
+----+---------+-------+
4 rows in set (0.00 sec)

7.5 limit查询

mysql> select * from test limit 1,3;
+----+---------+-------+
| id | name | state |
+----+---------+-------+
| 2 | shadow | 1 |
| 3 | shuaige | 1 |
| 4 | meinv | 1 |
+----+---------+-------+
3 rows in set (0.01 sec)

7.6 模糊查询

mysql> select id,name from test where name like 'sh%';
+----+---------+
| id | name |
+----+---------+
| 2 | shadow |
| 3 | shuaige |
+----+---------+
2 rows in set (0.00 sec)

7.7 select查询小结

列名的位置能不用*就不用*，尽量将显示的列名全部写出来
WHERE条件子句中，能不用判断就不用判断，可以使用“=”配合其他条件进行精确定位
SELECT查询时能用WHERE条件子句就一定要加上
企业生产中使用SELECT和UPDATE时必须使用WHERE子句进行定位后再操作数据

温馨提示：本文最后更新于2022-12-20 20:57:52，已超过494天没有更新。某些文章具有时效性，若文章内容或图片资源有错误或已失效，请联系站长。谢谢！
转载请注明本文链接：https://blog.leonshadow.cn/763482/651.html

1. 本网站名称：Leon的博客
2. 本站永久网址：https://blog.leonshadow.cn
3. 本网站的文章部分内容可能来源于网络，仅供大家学习与参考，如有侵权，请联系站长QQ632113590进行删除处理。
4. 本站一切资源不代表本站立场，并不代表本站赞同其观点和对其真实性负责。
5. 本站一律禁止以任何方式发布或转载任何违法的相关信息，访客发现请向站长举报
6. 本站资源大多存储在云盘，如发现链接失效，请联系我们我们会第一时间更新。

THE END