目錄

一、概述

數據庫概念

數據庫的類型

關系型數據庫模型

關系數據庫相關概念

二、安裝

1、mariadb安裝

2、mysql安裝

3、啟動并開機自啟

4、本地連接（本地登錄）

三、mysqld數據庫配置與命令

yum安裝后生成的目錄

mysqld服務器的啟動腳本

數據存儲目錄的權限

服務啟動時讀取的默認配置文件

mysqld操作命令

mysql命令

mysqladmin命令

四、默認數據庫

五、 SQL語言（背會）

六、mysqld數據類型

1、整型

2、浮點型

3、BIT類型（了解）

4、定點型

5、日期時間類型

YEAR類型

DATE類型

TIME類型

DATETIME類型

TIMESTAMP類型

6、字符串型

CHAR和VARCHAR類型

TEXT類型

ENUM類型

SET類型

BINARY和VARBINARY類型

BLOB類型

JSON數據類型

7、特殊的NULL類型

七、數據庫操作命令

庫操作命令

表結構操作命令

alter命令用法

表數據操作

插入數據

where字句運算符

更新數據

刪除數據

查詢數據

范式違反對比總結

實戰演練

八、mysqld用戶權限管理

MySQL數據庫權限分類

創建遠程管理用戶

創建遠程web服務數據庫的操作用戶

查看用戶權限

刪除用戶權限

案例

九、mysqld數據庫備份

9.1 數據備份的重要性

9.2 造成數據丟失的原因

9.2 備份需要考慮的問題

9.4 備份類型

1、根據是否需要數據庫離線

1.1、常用備份工具

2、根據要備份的數據集合的范圍

建議的恢復策略

3、根據備份數據或文件

9.5 常見的備份方法

1、物理冷備（完全備份）

2、專用備份工具mysqldump或mysqlhotcopy （完全備份，邏輯備份）

3、第三方工具備份

9.6 備份案例

1、完整備份

所有數據庫的備份

指定數據庫的備份

指定數據表的備份

2、增量備份

配置過程

查看日志文件內容

二進制備份的恢復

模擬數據丟失流程

數據恢復

XtraBackup 8.0.35-33 使用指南

簡介

安裝

在基于 RPM 的系統上安裝

在基于 Debian 的系統上安裝

基本使用

1. 完整備份

2. 準備備份（應用日志）

3. 恢復備份

高級使用案例

案例1：增量備份

案例2：壓縮備份

案例3：并行備份和恢復

案例4：加密備份

案例5：流式備份

常用選項說明

注意事項

最佳實踐

MyDumper 詳細使用指南

安裝 MyDumper

Ubuntu/Debian 系統

CentOS/RHEL 系統

從源碼編譯

基本使用

1. 完整備份數據庫

2. 恢復數據庫

常用參數說明

使用案例

案例1：備份單個數據庫

案例2：備份多個特定表

案例3：多線程備份（8個線程）

案例4：壓縮備份

案例5：按100萬行分割表數據

案例6：備份數據庫結構（不備份數據）

案例8：只恢復特定表

高級功能

1. 一致性快照備份

2. 正則表達式過濾表

3. 備份時排除某些表

4. 長查詢超時設置

5. 只備份數據不備份結構

實際應用場景

場景2：大數據表部分恢復

場景3：跨服務器遷移數據庫

注意事項

性能優化建議

mysqlhotcopy 使用指南

安裝與準備

檢查是否安裝

確保依賴安裝（Perl 模塊）

基本語法

常用選項

使用案例

案例1：備份單個數據庫

案例2：備份多個數據庫

案例3：使用正則表達式備份匹配的數據庫

案例4：保留舊備份

案例5：遠程備份到其他服務器

案例6：不備份索引文件

案例7：備份后刷新日志

案例8：模擬運行（不實際備份）

實際應用場景

場景1：生產環境每日備份腳本

場景2：備份特定表

場景3：增量備份策略

恢復數據庫

注意事項

性能優化建議

替代方案

附錄 A mysqld配置文件

附錄 B mysql常見內置函數

一、字符串函數

二、日期時間函數

三、數學函數

四、邏輯函數

五、加密函數

---

一、概述

數據庫概念

數據庫(Database)

簡稱DB，按照一定格式存儲數據的一些文件的組合，顧名思義就是存儲數據的倉庫，實際上就是一堆文件。這些文件中存儲了具有特定格式的數據 。數據庫管理數據有諸多優點，如降低存儲數據的冗余度，存儲的數據可以共享，便于維護數據完整性，能夠實現數據的安全性等。數據庫的發展經歷了萌芽、初級、中級、和高級階段。

數據庫管理系統（DateBase Management System）

簡稱DBMS，數據庫管理系統是專門用來管理數據庫中的數據的，可以對數據庫當中的數據進行增、刪、改、查 等操作，常見的數據庫管理系統：MySql、Oracle、MS SQLServer、DB2、sysbase等…

SQL（Structured Query Language）

結構化查詢語言，針對關系型數據庫的一種語言；SQL 是一種操作數據庫的語言，包括創建數據庫、刪除數據庫、查詢記錄、修改記錄、添加字段等。SQL在MySql中可以使用，同時在Oracle中也可以使用，在DB2中也可以使用 。

數據庫的類型

關系型數據庫

• MariaDB/MySQL

• Oracle

• DB2

• MS SQLServer，只能用于windows系統

  國產數據庫

• 達夢

• 人大金倉

• TiDB，分布式關系型數據庫

NOSQL數據庫

• Redis，緩存數據庫

• MongoDB，文檔型數據庫

關系型數據庫模型

• 層次模型

• 網狀模型

• 關系模型

關系模型的相關概念：

1. 實體（Entity） ：實體是指現實世界中可以區分的對象，它可以是具體的人、事物或概念，也可以是抽象的物體。在數據庫中，實體通常對應于表中的行，而表則實體的一種集合。例如，在一個學生信息管理系統中，學生、課程和教師都可以被視為實體。

2. 屬性（Attribute）： 屬性是實體的特征或性質，它提供了關于實體的具體信息。屬性可以是簡單的數據類型，如姓名、年齡或地址，也可以是更復雜的結構，如電話號碼或電子郵件地址。在數據庫中，屬性通常對應于表中的列，每列代表實體的一種屬性。例如，學生的姓名、學號和年齡都是學生的屬性。

3. 聯系（Relationship）： 聯系描述了不同實體之間的關系。在現實世界中，實體之間往往存在各種形式的關聯。在數據庫中，聯系通過關系表來實現，這個表通常包含兩個或更多的實體作為表的列。例如，學生選修課程的關系表就包含了學生實體和課程實體的相關信息，描述了哪位學生選修了哪門課程。

在關系模型中，實體和聯系通常通過以下三種類型來進一步分類：

• 一對一（1:1）：這種聯系表示兩個實體之間存在一對一的關系，即每個實體只有一個相關的實體，反之亦然。例如，一個部門與一個負責人之間可能存在一對一的聯系。

• 一對多（1:N）：這種聯系表示一個實體與多個實體相關，但每個相關實體只與一個實體相關。例如，一個教師可以教授多門課程，但每門課程只能由一個教師教授。

• 多對多（M:N）：這種聯系表示兩個實體之間存在多對多的關系，即每個實體可以與多個實體相關，同時每個相關實體也可以與多個實體相關。例如，學生可以選修多門課程，同時每門課程也可以被多個選修。

在設計數據庫時，正確地識別實體、屬性和聯系，以及它們之間的類型，對于創建一個結構良好、能夠有效存儲和檢索數據的數據庫至關重要。

關系數據庫相關概念

1. 庫（Database）： 庫是一個存儲數據的容器，它可以包含多個數據庫。在某些管理系統中，庫等同于數據庫。

2. 表（Table）： 表是數據庫中的一個表格，由行和列組成。表是存儲數據的主要結構，每個表通常對應一個實體類型。表的列名稱為屬性，而表的行名稱為記錄。

3. 行（Record）： 行也稱為記錄，它是表中的一個單元，代表表中的一個具體實例。每一行包含了一組屬性值，這些值共同描述了一個實體的狀態。

4. 列（Column）： 列是表中的一列，它代表了表的一個屬性。每列都有一個數據類型，用于定義存儲在其中的數據的種類和格式。

5. 字段（Field）： 字段通常指的是表中的行與列的交叉點，它存儲了單個數據項。在數據庫中，每個字段都有其特定的數據類型和用途。

6. 數據（Data）： 數據是存儲在數據庫中的信息。它可以是文本、數字、日期、圖像、聲音等各種形式。數據是數據庫管理和操作的核心。

二、安裝

默認監聽端口號：3306/tcp

1、mariadb安裝

服務端程序： mariadb-server；客戶端程序：mariadb

yum install -y mariadb-server mariadb

2、mysql安裝

服務端程序：mysql-server；客戶端程序：mysql；

[root@mysql ~]# yum install -y  mysqld-server mysql 
##編譯安裝，需要cmake環境

3、啟動并開機自啟

[root@mysql ~]# systemctl enable --now mysqld.service 

4、本地連接（本地登錄）

[root@mysql ~]# mysql
Welcome to the mysqld monitor.  Commands end with ; or \g.
Your mysqld connection id is 2
Server version: 5.5.68-mysqld mysqld Server
?
Copyright (c) 2000, 2018, Oracle, mysqld Corporation Ab and others.
?
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
?
mysqld [(none)]> 
##[(none)]：表示當前登錄用戶選擇的數據庫的“空”，沒有在任何庫中
##使用“exit”命令可以退出數據庫登錄

注意：mysqld默認的管理用戶是“root”，與操作系統的“root”沒有任何關系！！！root用戶默認沒有密碼！！

三、mysqld數據庫配置與命令

yum安裝后生成的目錄

[root@mysql mysql]# rpm -ql mysqld-server
/etc/logrotate.d/mysqld
/etc/my.cnf.d/server.cnf
/usr/bin/innochecksum
/usr/bin/myisam_ftdump
/usr/bin/myisamchk
/usr/bin/myisamlog
/usr/bin/myisampack
/usr/bin/mysql_convert_table_format
/usr/bin/mysql_fix_extensions
/usr/bin/mysql_install_db
/usr/bin/mysql_plugin
/usr/bin/mysql_secure_installation
/usr/bin/mysql_setpermission
/usr/bin/mysql_tzinfo_to_sql
/usr/bin/mysql_upgrade
/usr/bin/mysql_zap
/usr/bin/mysqlbug
/usr/bin/mysqld_multi
/usr/bin/mysqld_safe
/usr/bin/mysqld_safe_helper
/usr/bin/mysqldumpslow
/usr/bin/mysqlhotcopy
/usr/bin/mysqltest
/usr/bin/perror
/usr/bin/replace
/usr/bin/resolve_stack_dump
/usr/bin/resolveip
/usr/lib/systemd/system/mysqld.service
/usr/lib/tmpfiles.d/mysqld.conf
/usr/lib64/mysql/INFO_BIN
/usr/lib64/mysql/INFO_SRC
/usr/lib64/mysql/mysqlbug
/usr/lib64/mysql/plugin
/usr/lib64/mysql/plugin/adt_null.so
/usr/lib64/mysql/plugin/auth_0x0100.so
/usr/lib64/mysql/plugin/auth_pam.so
/usr/lib64/mysql/plugin/auth_socket.so
/usr/lib64/mysql/plugin/auth_test_plugin.so
/usr/lib64/mysql/plugin/daemon_example.ini
/usr/lib64/mysql/plugin/dialog_examples.so
/usr/lib64/mysql/plugin/ha_innodb.so
/usr/lib64/mysql/plugin/ha_sphinx.so
/usr/lib64/mysql/plugin/handlersocket.so
/usr/lib64/mysql/plugin/libdaemon_example.so
/usr/lib64/mysql/plugin/mypluglib.so
/usr/lib64/mysql/plugin/qa_auth_client.so
/usr/lib64/mysql/plugin/qa_auth_interface.so
/usr/lib64/mysql/plugin/qa_auth_server.so
/usr/lib64/mysql/plugin/query_cache_info.so
/usr/lib64/mysql/plugin/semisync_master.so
/usr/lib64/mysql/plugin/semisync_slave.so
/usr/lib64/mysql/plugin/server_audit.so
/usr/lib64/mysql/plugin/sphinx.so
/usr/lib64/mysql/plugin/sql_errlog.so
/usr/libexec/mysqld-prepare-db-dir
/usr/libexec/mysqld-wait-ready
/usr/libexec/mysqld
/usr/share/man/man1/innochecksum.1.gz
/usr/share/man/man1/msql2mysql.1.gz
/usr/share/man/man1/myisam_ftdump.1.gz
/usr/share/man/man1/myisamchk.1.gz
/usr/share/man/man1/myisamlog.1.gz
/usr/share/man/man1/myisampack.1.gz
/usr/share/man/man1/mysql.server.1.gz
/usr/share/man/man1/mysql_convert_table_format.1.gz
/usr/share/man/man1/mysql_fix_extensions.1.gz
/usr/share/man/man1/mysql_install_db.1.gz
/usr/share/man/man1/mysql_plugin.1.gz
/usr/share/man/man1/mysql_secure_installation.1.gz
/usr/share/man/man1/mysql_setpermission.1.gz
/usr/share/man/man1/mysql_tzinfo_to_sql.1.gz
/usr/share/man/man1/mysql_upgrade.1.gz
/usr/share/man/man1/mysql_zap.1.gz
/usr/share/man/man1/mysqlbinlog.1.gz
/usr/share/man/man1/mysqlbug.1.gz
/usr/share/man/man1/mysqlcheck.1.gz
/usr/share/man/man1/mysqld_multi.1.gz
/usr/share/man/man1/mysqld_safe.1.gz
/usr/share/man/man1/mysqldumpslow.1.gz
/usr/share/man/man1/mysqlhotcopy.1.gz
/usr/share/man/man1/mysqlimport.1.gz
/usr/share/man/man1/mysqltest.1.gz
/usr/share/man/man1/perror.1.gz
/usr/share/man/man1/replace.1.gz
/usr/share/man/man1/resolve_stack_dump.1.gz
/usr/share/man/man1/resolveip.1.gz
/usr/share/man/man8/mysqld.8.gz
/usr/share/mysql/README.mysql-cnf
/usr/share/mysql/errmsg-utf8.txt
/usr/share/mysql/fill_help_tables.sql
/usr/share/mysql/my-huge.cnf
/usr/share/mysql/my-innodb-heavy-4G.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-small.cnf
/usr/share/mysql/mysql_performance_tables.sql
/usr/share/mysql/mysql_system_tables.sql
/usr/share/mysql/mysql_system_tables_data.sql
/usr/share/mysql/mysql_test_data_timezone.sql
/var/lib/mysql ?##mysqld數據庫的數據存儲目錄
/var/log/mysqld
/var/log/mysqld/mysqld.log
/var/run/mysqld

mysqld服務器的啟動腳本

[root@mysql ~]# cat /usr/lib/systemd/system/mysqld.service
?
[Unit]
Description=mysqld database server
After=syslog.target
After=network.target
?
[Service]
Type=simple
User=mysql ?##程序運行用戶
Group=mysql
?
ExecStartPre=/usr/libexec/mysqld-prepare-db-dir %n
# Note: we set --basedir to prevent probes that might trigger SELinux alarms,
# per bug #547485
ExecStart=/usr/bin/mysqld_safe --basedir=/usr
ExecStartPost=/usr/libexec/mysqld-wait-ready $MAINPID
"/usr/lib/systemd/system/mysqld.service" 48L, 1697C ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?39,1 ? ? ? ? ?55%
# http://fedoraproject.org/wiki/Systemd#How_do_I_customize_a_unit_file.2F_add_a_custom_unit_file.3F
?
# For example, if you want to increase mysqld's open-files-limit to 10000,
# you need to increase systemd's LimitNOFILE setting, so create a file named
# "/etc/systemd/system/mysqld.service.d/limits.conf" containing:
# ? ? ? [Service]
# ? ? ? LimitNOFILE=10000
?
# Note: /usr/lib/... is recommended in the .include line though /lib/... 
# still works.
# Don't forget to reload systemd daemon after you change unit configuration:
# root> systemctl --system daemon-reload
?
[Unit]
Description=mysqld database server
After=syslog.target
After=network.target
?
[Service]
Type=simple
User=mysql
Group=mysql
?
ExecStartPre=/usr/libexec/mysqld-prepare-db-dir %n
# Note: we set --basedir to prevent probes that might trigger SELinux alarms,
# per bug #547485
ExecStart=/usr/bin/mysqld_safe --basedir=/usr
ExecStartPost=/usr/libexec/mysqld-wait-ready $MAINPID
?
# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300
?
# Place temp files in a secure directory, not /tmp
PrivateTmp=true
?
[Install]
WantedBy=multi-user.target

數據存儲目錄的權限

[root@mysql mysql]# ls -ld /var/lib/mysql/
drwxr-xr-x 5 mysql mysql 177 1月 ?31 10:16 /var/lib/mysql/

服務啟動時讀取的默認配置文件

[root@mysql mysql]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql ?##定義數據存儲目錄
socket=/var/lib/mysql/mysql.sock ?##定義連接的網絡接口文件
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
?
[mysqld_safe]
log-error=/var/log/mysqld/mysqld.log ?##程序運行的錯誤日志
pid-file=/var/run/mysqld/mysqld.pid ?##程序運行的PID文件
?
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

mysqld操作命令

[root@mysql ~]# ls -l /usr/bin/mysql*
-rwxr-xr-x 1 root root 3543584 10月 ?2 2020 /usr/bin/mysql
-rwxr-xr-x 1 root root ?111971 10月 ?2 2020 /usr/bin/mysqlaccess
-rwxr-xr-x 1 root root 3096720 10月 ?2 2020 /usr/bin/mysqladmin
-rwxr-xr-x 1 root root 3259880 10月 ?2 2020 /usr/bin/mysqlbinlog
lrwxrwxrwx 1 root root ? ? ?26 1月 ?31 10:15 /usr/bin/mysqlbug -> /etc/alternatives/mysqlbug
-rwxr-xr-x 1 root root 3094016 10月 ?2 2020 /usr/bin/mysqlcheck
-rwxr-xr-x 1 root root ? ?4215 10月 ?2 2020 /usr/bin/mysql_convert_table_format
-rwxr-xr-x 1 root root ? 24116 10月 ?2 2020 /usr/bin/mysqld_multi
-rwxr-xr-x 1 root root ? 27105 10月 ?2 2020 /usr/bin/mysqld_safe
-rwxr-xr-x 1 root root 2887144 10月 ?2 2020 /usr/bin/mysqld_safe_helper
-rwxr-xr-x 1 root root 3176704 10月 ?2 2020 /usr/bin/mysqldump
-rwxr-xr-x 1 root root ? ?7876 10月 ?2 2020 /usr/bin/mysqldumpslow
-rwxr-xr-x 1 root root ? ?3288 10月 ?2 2020 /usr/bin/mysql_find_rows
-rwxr-xr-x 1 root root ? ?1246 10月 ?2 2020 /usr/bin/mysql_fix_extensions
-rwxr-xr-x 1 root root ? 34942 10月 ?2 2020 /usr/bin/mysqlhotcopy
-rwxr-xr-x 1 root root 3088968 10月 ?2 2020 /usr/bin/mysqlimport
-rwxr-xr-x 1 root root ? 16701 10月 ?2 2020 /usr/bin/mysql_install_db
-rwxr-xr-x 1 root root 2926112 10月 ?2 2020 /usr/bin/mysql_plugin
-rwxr-xr-x 1 root root ? 12126 10月 ?2 2020 /usr/bin/mysql_secure_installation
-rwxr-xr-x 1 root root ? 17464 10月 ?2 2020 /usr/bin/mysql_setpermission
-rwxr-xr-x 1 root root 3087504 10月 ?2 2020 /usr/bin/mysqlshow
-rwxr-xr-x 1 root root 3106896 10月 ?2 2020 /usr/bin/mysqlslap
-rwxr-xr-x 1 root root 3449016 10月 ?2 2020 /usr/bin/mysqltest
-rwxr-xr-x 1 root root 2921448 10月 ?2 2020 /usr/bin/mysql_tzinfo_to_sql
-rwxr-xr-x 1 root root 2998448 10月 ?2 2020 /usr/bin/mysql_upgrade
-rwxr-xr-x 1 root root 2912968 10月 ?2 2020 /usr/bin/mysql_waitpid
-rwxr-xr-x 1 root root ? ?3856 10月 ?2 2020 /usr/bin/mysql_zap

mysql命令

mysql數據庫登錄的命令行工具，本地登錄時且root用戶沒有密碼，輸入mysql，等效于”mysql -uroot -hlocalhost -P3306“

語法

 mysql [options] db_name

常用選項

-u：指定登錄用戶
-p：指定用戶密碼
-h：指定登錄數據庫的IP或者域名
-P：指定登錄數據庫的端口號
-e：能夠在終端執行數據庫指令

使用案例

[root@mysql ~]# mysql -uroot -hlocalhost -P3306
[root@mysql ~]# mysql -p123.com -e "show databases" ? ##直接在外部執行查看數據庫的命令
+--------------------+
| Database ? ? ? ? ? |
+--------------------+
| information_schema |
| mysql ? ? ? ? ? ?  |
| performance_schema |
| test ? ? ? ? ? ? ? |
+--------------------+
?
##指定登錄的數據庫，只能是一個
[root@mysql mysql]# mysql -p123.com mysql ? 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
?
Welcome to the mysqld monitor.  Commands end with ; or \g.
Your mysqld connection id is 14
Server version: 5.5.68-mysqld mysqld Server
?
Copyright (c) 2000, 2018, Oracle, mysqld Corporation Ab and others.
?
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
?
mysqld [mysql]> 

mysqladmin命令

超級管理命令

語法

mysqladmin [options] command [command-arg] [command [command-arg]] ...

設置root用戶密碼

[root@mysql ~]# mysqladmin -uroot password '123.com'

使用密碼登錄

[root@mysql ~]# mysql -uroot -p123.com -h127.0.0.1 -P3306

四、默認數據庫

• information_schema ：信息數據庫，存儲所有的庫、表、列的名稱，任意可登錄數據庫的用戶都可讀；sql注入

表名	作用
CHARACTER_SETS	字符集信息表。用于查看和管理MySQL數據庫中的字符集信息。
CLIENT_STATISTICS	客戶端統計信息表。用于查看和管理客戶端的統計信息，例如連接數、請求次數等。
COLLATIONS	排序規則信息表。用于查看和管理MySQL數據庫中的排序規則信息。
COLLATION_CHARACTER_SET_APPLICABILITY	字符集和排序規則的適用性表。用于查看和管理字符集和排序規則之間的適用關系。
COLUMNS	數據表的列信息表。用于查看和管理MySQL數據庫中數據表的列信息。
COLUMN_PRIVILEGES	列級別的權限信息表。用于查看和管理列級別的權限信息，例如用戶對各個列的SELECT、INSERT、UPDATE等權限。
ENGINES	存儲引擎信息表。用于查看和管理MySQL數據庫中支持的存儲引擎信息。
EVENTS	定時事件信息表。用于查看和管理MySQL數據庫中的定時事件信息。
FILES	文件信息表。用于查看和管理MySQL數據庫服務器上文件的信息。
GLOBAL_STATUS	全局狀態信息表。用于查看和管理MySQL數據庫服務器的全局狀態信息。
GLOBAL_VARIABLES	全局變量信息表。用于查看和管理MySQL數據庫服務器的全局變量信息。
INDEX_STATISTICS	索引統計信息表。用于查看和管理MySQL數據庫中索引的統計信息。
KEY_CACHES	鍵緩存表。用于查看和管理MySQL數據庫中的鍵緩存信息。
KEY_COLUMN_USAGE	鍵列使用表。用于查看和管理鍵列的使用情況。
PARAMETERS	參數表。用于查看和管理MySQL數據庫的參數信息，例如連接超時時間、最大連接數等。
PARTITIONS	分區表。用于查看和管理MySQL數據庫中的分區信息。
PLUGINS	插件表。用于查看和管理MySQL數據庫中的插件信息。
PROCESSLIST	進程列表表。用于查看當前正在運行的MySQL進程，包括連接的客戶端和正在執行的查詢等。
PROFILING	性能分析表。用于啟用性能分析，記錄和分析SQL查詢的性能數據。
REFERENTIAL_CONSTRAINTS	外鍵約束表。用于查看和管理外鍵約束的信息，包括外鍵列和參考列等。
ROUTINES	存儲過程和函數表。用于查看和管理MySQL數據庫中的存儲過程和函數的信息，包括創建時間、修改時間、函數名等。
SCHEMATA	架構表。用于查看和管理MySQL數據庫中的架構信息，包括架構名、架構下的表名等。
SCHEMA_PRIVILEGES	架構權限表。用于查看和管理架構下的權限信息，包括用戶對架構下表的SELECT、INSERT、UPDATE等權限。
SESSION_STATUS	會話狀態信息表。用于查看和管理當前會話的狀態信息，例如會話的連接時間、查詢時間等。
SESSION_VARIABLES	會話變量表。用于查看和管理當前會話的變量信息，例如會話的最大連接數、最大內存使用量等。
STATISTICS	統計信息表。用于查看和管理MySQL數據庫中表的統計信息，包括表的行數、平均行大小等。
TABLES	數據表信息表。用于查看和管理MySQL數據庫中的數據表信息，包括表名、引擎類型等。
TABLESPACES	存儲空間表。用于查看和管理MySQL數據庫中的存儲空間信息，包括存儲空間名、大小等。
TABLE_CONSTRAINTS	表約束信息表。用于查看和管理表的約束信息，包括主鍵約束、外鍵約束等。
TABLE_PRIVILEGES	表級別的權限信息表。用于查看和管理表級別的權限信息，例如用戶對各個表的SELECT、INSERT、UPDATE等權限。
INNODB_CMPMEM_RESET	InnoDB 內存比較器重置表。此表用于記錄 InnoDB 內存比較器（comparison memory）的清除操作。
INNODB_RSEG	InnoDB 重做段表此表用于記錄 InnoDB 數據文件的重做段信息。
INNODB_UNDO_LOGS	InnoDB 撤銷日志表。此表用于記錄 InnoDB 撤銷操作的日志信息。
INNODB_CMPMEM	InnoDB 內存比較器表。此表用于記錄 InnoDB 內存比較器的分配和使用情況。
INNODB_SYS_TABLESTATS	InnoDB 系統表統計信息表。此表用于記錄 InnoDB 系統表的統計信息，如數據量、碎片率等。
INNODB_LOCK_WAITS	InnoDB 鎖等待信息表。此表記錄 InnoDB 鎖等待的情況，包括等待鎖的線程 ID、等待時間等。
INNODB_INDEX_STATS	InnoDB 索引統計信息表。此表用于記錄 InnoDB 索引的統計信息，如索引大小、索引列的數據類型等。
INNODB_CMP	InnoDB 比較器表。此表用于記錄 InnoDB 比較器的分配和使用情況。
INNODB_CHANGED_PAGES	InnoDB 更改頁表。此表用于記錄 InnoDB 數據文件中已更改的頁的信息。
INNODB_BUFFER_POOL_PAGES	InnoDB 緩沖池頁表。此表用于記錄 InnoDB 緩沖池中每個頁的信息，包括頁的類型、頁的狀態等。
INNODB_TRX	InnoDB 事務表。此表用于記錄 InnoDB 事務的信息，如事務 ID、事務狀態等。
INNODB_BUFFER_POOL_PAGES_INDEX	InnoDB 緩沖池頁索引表。此表用于記錄 InnoDB 緩沖池中每個頁的索引信息。
INNODB_LOCKS	InnoDB 鎖表。此表用于記錄 InnoDB 鎖的信息，如鎖的類型、鎖的持有者等。
INNODB_BUFFER_PAGE_LRU	InnoDB 緩沖池頁 LRU 表。此表用于記錄 InnoDB 緩沖池中每個頁的最近最少使用（Least Recently Used，LRU）信息。
INNODB_SYS_TABLES	InnoDB 系統表信息表。此表用于記錄 InnoDB 系統表的信息，如表名、表狀態等。
INNODB_SYS_FIELDS	InnoDB 系統字段信息表。此表用于記錄 InnoDB 系統表中每個字段的信息，如字段名、字段類型等。
INNODB_SYS_COLUMNS	InnoDB 系統列信息表。此表用于記錄 InnoDB 系統表中每個列的信息，如列名、列類型、列長度等。
INNODB_SYS_STATS	InnoDB 系統統計信息表。此表用于記錄 InnoDB 系統統計信息，如表數量、數據量等。
INNODB_SYS_FOREIGN	InnoDB 系統外鍵信息表。此表用于記錄 InnoDB 系統表中外鍵的信息，如外鍵約束條件等。
INNODB_SYS_INDEXES	InnoDB 系統索引信息表。此表用于記錄 InnoDB 系統表中索引的信息，如索引名、索引類型等。

• mysql ：主數據庫，mysqld運行的必須數據庫，用戶與配置信息

表名稱	作用
columns_priv	保存了每個表的列級別的權限信息，包括用戶對各個列的SELECT、INSERT、UPDATE、REFERENCES等權限。
db	保存了每個數據庫的權限信息，包括用戶對每個數據庫的CREATE、ALTER、DROP等權限。
event	保存了MySQL中的事件信息，包括事件的名稱、執行時間、執行語句等。
func	保存了用戶定義的存儲函數的信息，包括函數的名稱、參數、返回類型等。
general_log	記錄了MySQL服務器上所有的日志操作，包括查詢、連接、錯誤日志等。
help_category	保存了MySQL幫助文檔中的分類信息，用于幫助查找和瀏覽文檔。
help_keyword	保存了MySQL幫助文檔的關鍵字信息，用于快速搜索和查找文檔。
help_relation	保存了MySQL幫助文檔中關鍵字之間的關系信息，用于幫助構建文檔的結構。
help_topic	保存了MySQL幫助文檔的具體內容信息，包括每個主題的標題、內容等。
host	保存了MySQL服務器上的主機信息，包括主機名、IP地址、連接權限等。
ndb_binlog_index	保存了使用NDB存儲引擎的MySQL服務器上的二進制日志索引信息。
plugin	保存了MySQL服務器上安裝的插件信息。
proc	保存了用戶定義的存儲過程的信息，包括過程的名稱、參數、語句等。
procs_priv	保存了用戶對存儲過程的訪問權限信息。
proxies_priv	保存了MySQL服務器上的代理用戶的權限信息。
servers	實驗性表，保存了MySQL服務器的外部服務器和復制配置信息。
slow_log	記錄了MySQL服務器上執行時間超過默認閾值的慢查詢日志。
tables_priv	保存了用戶對表的訪問權限信息。
time_zone	保存了MySQL服務器上的時區信息。
time_zone_leap_second	保存了時區閏秒的信息。
time_zone_name	保存了時區的名稱和相關信息。
time_zone_transition	保存了時區的變化規則和信息。
time_zone_transition_type	保存了時區變化類型的信息。
user	保存了MySQL服務器上的用戶賬號信息，包括用戶名、密碼、權限等。

• performance_schema：性能數據庫，存儲mysqld的資源使用、安全策略權限配置信息

表名	作用
cond_instances	條件實例表，該表用于存儲各種條件或鎖定的實例信息
events_waits_current	當前等待事件表，記錄了當前正在等待某個事件發生的線程或會話信息
events_waits_history	歷史等待事件表，記錄了過去一段時間內等待事件的信息，包括等待事件的類型、等待時間等
events_waits_history_long	長期等待事件表，記錄了長時間等待事件的信息，包括等待事件的類型、等待時間、等待時長等
events_waits_summary_by_instance	按實例總結等待事件表，提供了按實例總結的等待事件統計信息
events_waits_summary_by_thread_by_event_name	按線程和事件總結等待事件表，提供了按線程和特定事件總結的等待事件統計信息
events_waits_summary_global_by_event_name	按全局和事件總結等待事件表，提供了按全局和特定事件總結的等待事件統計信息
file_instances	文件實例表，記錄了數據庫中各個文件的信息，包括文件路徑、文件大小等
file_summary_by_event_name	按事件名總結文件表，提供了按特定事件名總結的文件統計信息
file_summary_by_instance	按實例總結文件表，提供了按實例總結的文件統計信息
mutex_instances	互斥體實例表，記錄了數據庫中各個互斥體的信息，包括互斥體的名稱、狀態等
performance_timers	性能計時器表，提供了數據庫性能的計時信息，包括執行時間、資源使用情況等
rwlock_instances	讀寫鎖實例表，記錄了數據庫中各個讀寫鎖的信息
setup_consumers	設置消費者表，記錄了數據庫設置的各種消費者信息
setup_instruments	設置儀器表，記錄了數據庫設置的各類性能指標信息
setup_timers	設置定時器表，記錄了數據庫設置的各類定時器信息
threads	線程表，記錄了數據庫中的各個線程信息，包括線程ID、線程狀態等

• sys庫

五、 SQL語言（背會）

• DDL：數據定義語言，對數據庫結構操作

create：創建（用戶，庫，表）

alter：改變

drop：刪除

• DML：數據操作語言，對數據表的操作

insert：插入

update：更新

delete：刪除數據

• DCL：數據控制語言，針對用戶權限設置

grant：用戶賦權

revoke：移除用戶權限

• DQL：數據查詢語言，對數據表的操作

select：查詢

六、mysqld數據類型

常用的數據類型有：

• 整型

• 浮點型

• BIT類型

• 定點數

• 日期時間類型

• 字符串

• NULL類型

1、整型

整數類型	占用字節	無符號數的取值范圍	有符號數的取值范圍
TINYINT	1	0~255	-128~127
SMALLINT	2	0~65535	-32768~32767
MEDIUMINT	3	0~16777215	-8388608~8388607
INT	4	0~4294967295	-2147483648~2147483647
BIGINT	8	0~18446744073709551615	-9223372036854774808~9223372036854774807

整數列的可選屬性有三個：

• M: 寬度(在0填充的時候才有意義，否則不需要指定)

• unsigned: 無符號類型(非負)

• zerofill: 0填充,(如果某列是zerofill，那么默認就是無符號)，如果指定了zerofill只是表示不夠M位時，用0在左邊填充，如果超過M位，只要不超過數據存儲范圍即可

在 int(M) 中，M 的值跟 int(M) 所占多少存儲空間并無任何關系。 int(3)、int(4)、int(8) 在磁盤上都是占用 4 bytes 的存儲空間。

各整數數據類型的使用場所

數據類型	應用場景
TINYINT	一般用于枚舉數據，比如系統設定取值范圍很小且固定的場景。
SMALLINT	可以用于較小范圍的統計數據，比如統計工廠的固定資產庫存數量等。
MEDIUMINT	用于較大整數的計算，比如車站每日的客流量等。
INT、INTEGER	取值范圍足夠大，一般情況下不用考慮超限問題，用得最多。比如商品編號。
BIGINT	只有當你處理特別巨大的整數時才會用到。比如雙十一的交易量、大型門戶網站點擊量、證券公司衍生產品持倉等。

2、浮點型

數據類型	字節數	取值范圍
FLOAT	4	-2^128~2 ^128，即-3.40E+38~+3.40E+38
DOUBLE	8	-2^1024~ 2^1024，即-1.79E+308~1.79E+308

• 當浮點數類型使用unsigned修飾無符號時，取值范圍將不包含負數。

• 浮點數的取值范圍是理論上的極限值，但根據不同的硬件或操作系統，實際范圍可能會小。

• 浮點數雖然取值范圍很大，但精度并不高。float類型的精度為6位或7位，double類型的精度大約為15位。

• 如果給定的數值超出精度，可能會導致給定的數值與實際保存的數值不一致，發生精度損失。

• 當一個數字的整數部分和小數部分加起來達到7位時，第7位就會進行四舍五入操作。

• 要避免使用“=”來判斷兩個浮點數是否相等，因為浮點數是不準確的，存在精度損失。

3、BIT類型（了解）

函數	函數用途
BIT(M)	存儲二進制數據
ASCll(M)	獲取M的ASCll值
BIN(M)	獲取M的二進制值
LENGTH(M)	獲取M的數字長度

• BIT數據類型可用來保存位字段值。BIT(M)類型允許存儲M位值。M范圍為1~64，默認為1。

• BIT其實就是存入二進制的值，類似010110。如果存入一個BIT類型的值，位數少于M值，則左補0。如果存入一個BIT類型的值，位數多于M值，MySQL的操作取決于此時有效的SQL模式：如果模式未設置，MySQL將值裁剪到范圍的相應端點，并保存裁減好的值。如果模式設置為traditional(“嚴格模式”)，超出范圍的值將被拒絕并提示錯誤，并且根據SQL標準插入會失敗。

• 對于位字段，直接使用SELECT命令將不會看到結果，可以用bin()或hex()函數進行讀取。

4、定點型

• DECIMAL在MySQL內部以字符串形式存放，比浮點數更精確。定點類型占M+2個字節

• DECIMAL(M,D)與浮點型一樣處理規則。M的取值范圍為0~65，D的取值范圍為0~30，而且必須<=M，超出范圍會報錯。

• DECIMAL如果指定精度時，默認的整數位是10，默認的小數位為0。

• NUMERIC等價于DECIMAL。

• 例如，DECIMAL(5,2)表示的取值范圍為-999.99~999.99。

5、日期時間類型

數據類型	字節	取值范圍	日期格式	零值
YEAR	1	1901~2155	YYYY	0000
DATE	3	1000-01-01~9999-12-31	YYYY-MM-DD	0000-00-00
TIME	3	-838:59:59~838:59:59	HH:MM:SS	00:00:00
DATETIME	8	1000-01-01 00:00:00~9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	0000-00-00 00:00:00
TIMESTAMP	4	1970-01-01 00:00:01~2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	0000-00-00 00:00:00

YEAR類型

YEAR類型用來表示年份，在所有的日期時間類型中所占用的存儲空間最小，只需要1個字節的存儲空間，格式為YYYY。

在MySQL中，可使用以下3種格式指定TEAR類型的值：

使用4位字符串或數字表示，為1901'2155或19012155。

例如，輸入2022或2022，插入到數據庫中的值均為2022.

使用兩位字符串表示，為00~99。

00 ~ '69的值會被轉換為2000~2069的YEAR值

70 ~ '99的值會被自動轉換為1970~1999的YEAR值

例如，輸入22，插入到數據表中的值為2022。

使用兩位數字表示，為1~99。

1 ~ 69的值會被轉換為2001~2069的YEAR值

70 ~ 99的值會被自動轉換為1970~1999的YEAR值

例如，輸入22，插入到數據表中的值為2022。

注意：

當使用YEAR類型時，一定要區分0和0。

數字格式的0表示的YEAR值為0000

字符串格式的0表示的YEAR值為2000

DATE類型

DATE類型用來表示日期值，不包含時間部分，需要 3個字節 的存儲空間，且其格式為 YYYY-MM-DD 。其中，YYYY表示年份，MM表示月份，DD表示日期。

在MySQL中，可以使用以下4種格式指定DATE類型的值：

以YYYY-MM-DD或者YYYYMMDD字符串格式表示。

以YY-MM-DD或者YYMMDD字符串格式表示。

以YY-MM-DD或者YYMMDD數字格式表示。

使用CURRENT_DATE或者NOW()輸入當前系統日期。

注意：

通過"SELECT CURRENT_DATE;"或者"SELECT NOW();"可查詢當前日期。

日期中的分隔符"-“，還可以使用”.“”,“”/"等符號來表示。

TIME類型

TIME類型用于表示時間值，它的顯示形式一般為HH:MM:SS，其中HH表示小時，MM表示分，SS表示秒。

在MySQL中，可以使用以下3種格式指定TIME類型的值：

以HHMMSS字符串或者HHMMSS數學格式表示。

以HH:MM:SS字符串格式表示。

使用CURRENT_TIME或NOW()輸入當前系統時間。

DATETIME類型

DATETIME類型在所有的日期時間類型中占用的存儲空間最大，總共需要 8 個字節的存儲空間，用來表示日期和時間，它的顯示形式為YYYY-MM-DD HH:MM:SS。

在MySQL中，可以使用以下4種格式指定DATETIME類型的值：

以YYYY-MM-DD HH:MM:SS或YYYYMMDDHHMMSS字符串格式表示的日期和時間，取值范圍為1000-01-01 00:00:00~9999-12-31-23-59-59。

以YY-MM-DD HH:MM:SS或YYMMDDHHMMSS字符串格式表示的日期和時間，

以YYYYMMDDHHMMSS或YYMMDDHHMMSS數字格式表示的日期和時間，

使用NOW()來輸入當前系統的日期和時間。

TIMESTAMP類型

TIMESTAMP（時間戳）類型用于表示日期和時間，需要4個字節的存儲空間，它的顯示形式與DATETIME類型的相同，但取值范圍比DATETIME類型的小。

TIMESTAMP類型與DATETIME類型的不同形式：

使用CURRENT_TIMESTAMP來輸入系統當前的日期和時間。

無任何輸入，或輸入NULL時，實際保存的是系統當前日期和時間。

6、字符串型

數據類型	類型說明
CHAR	固定長度字符串
VARCHAR	可變長度字符串
TEXT	大文本數據
ENUM	枚舉類型
SET	字符串類型
BINARY	固定長度的二進制數據
VARBINARY	可變長度是二進制數據
BLOB	二進制大對象

CHAR和VARCHAR類型

CHAR和VARCHAR類型都是用來保存字符串數據，兩者不同的是，VARCHAR可以存儲可變長度的字符串數據。

字符串類型	特點	長度	長度范圍	占用的存儲空間
CHAR(M)	固定長度	M	0<=M<=255	M個字節
VARCHAR(M)	可變長度	M	0<=M<=65535	(實際長度 + 1) 個字節

TEXT類型

TEXT類型用于保存大文本數據，例如，文章內容，評論等比較長的文本。

數據類型	存儲范圍	存儲空間占用量	特點
TINYTEXT(tinytext)	0~2^8-1	文本長度+2	小文本，可變長度
TEXT(text)	0~2^16-1	文本長度+2	文本，可變長度
MEDIUMTEXT(mediumtext)	0~2^24-1	文本長度+3	中等文本，可變長度
LONGTEXT(longtext)	0~2^32-1	文本長度+4	大文本，可變長度

ENUM類型

ENUM類型又稱為枚舉類型，其定義格式為：

ENUM('值1','值2','值3','值4','值5',....,'值n')

1. ENUM類型的取值范圍需要在定義字段時進行指定。

2. 設置字段值時，ENUM類型只允許從成員中選取單個值，不能一次選取多個值。

3. 其所需要的存儲空間由定義ENUM類型時指定的成員個數決定。

   • 當ENUM類型包含1~255個成員時，需要1個字節的存儲空間

   • 當ENUM類型包含256~65535個成員時，需要2個字節的存儲空間

   • ENUM類型的成員個數的上限為65535個

SET類型

SET類型用于保存字符串對象，其定義格式與ENUM類型相似。

SET('值1','值2','值3','值4','值5',....,'值n')

1. 其所需要的存儲空間由定SET類型時指定的成員個數決定。

   SET類型包含的成員數	存儲空間
   1~8	1個字節
   9~16	2個字節
   17~24	3個字節
   25~32	4個字節
   33~64	8個字節

2. SET類型在存儲數據時成員個數越多，其占用的存儲空間越大。

3. SET類型在選取成員時，與ENUM類型不同，其可以一次選擇多個成員。

BINARY和VARBINARY類型

BINARY和VARBINARY類型類似于CHAR和VARCHAR，不同的是，它們所表示的是二進制數據。

類型	特點	長度	長度范圍	占用的存儲空間
BINARY(M)	固定長度	M	0<=M<=255	M個字節
VARBINARY(M)	可變長度	M	0<=M<=65535	(M+ 1) 個字節

BLOB類型

BLOB類型用于保存數據量比較大的二進制數據，如圖片，PDF文檔等。

數據類型	存儲范圍	占用空間
TINYBLOB	0~2^8 -1字節	len+1個字節
BLOB	0~2^16 -1字節（相當于64KB）	len+2個字節
MEDIUMBLOB	0~2^24 -1字節（相當于16MB）	len+3個字節
LONGBLOB	0~2^32 -1字節（相當于4GB）	len+4個字節

JSON數據類型

JSON（JavaScript Object Notation）是一種輕量級的數據交換格式 ，簡潔和清晰的層次結構使得 JSON 成為理想的數據交換語言。它易于人閱讀和編寫，同時也易于機器解析和生成，并有效地提升網絡傳輸效率。

JSON 可以將 JavaScript 對象中表示的一組數據轉換為字符串，然后就可以在網絡或者程序之間輕松地傳遞這個字符串，并在需要的時候將它還原為各編程語言所支持的數據格式。

在MySQL 5.7中，就已經支持JSON數據類型。在MySQL中，JSON類型常見的表示方式有2種，分別為JSON數組和JSON對象。

#JSON數組
['abc',10,null,true]
#JSON對象
{"k1":"value","k2":10}

7、特殊的NULL類型

• 所有的類型的值都可以是null，包括int、float等數據類型

• 空字符串””，不等于null，0也不等于null，false也不等于null

• 任何運算符,判斷符碰到NULL,都得NULL

• NULL的判斷只能用is null,is not null

• NULL 影響查詢速度,一般避免使值為NULL

七、數據庫操作命令

庫操作命令

• 查看數據庫

show databases;

?

• 查看所在數據庫

select database();

?

• 查看當前登錄用戶

select user();

?

• 查看當前數據庫版本

select version();

?

• 查看用戶權限列表

show privileges\G;

?

• 查看指定用戶的權限

show grants for root@localhost\G;

?

• 切換數據庫

use databaseName;

?

• 創建數據庫

create database databaseName  [character set utf8];

?

• 刪除數據庫

drop database databaseName;

?

表結構操作命令

• 查看數據表

show tables;

?

• 查看表結構

desc[describe] tableName;
[root@mysqld ~]# mysql -e "desc mysql.user"
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field ? ? ? ? ? ? ? ? ?  | Type ? ? ? ? ? ? ? ? ? ? ? ? ? ?  | Null | Key | Default ? ? ? ? ? ? ? | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host ? ? ? ? ? ? ? ? ? ? | char(255) ? ? ? ? ? ? ? ? ? ? ? ? | NO ? | PRI | ? ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| User ? ? ? ? ? ? ? ? ? ? | char(32) ? ? ? ? ? ? ? ? ? ? ? ?  | NO ? | PRI | ? ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| Select_priv ? ? ? ? ? ?  | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| Insert_priv ? ? ? ? ? ?  | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| Update_priv ? ? ? ? ? ?  | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| Delete_priv ? ? ? ? ? ?  | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| Create_priv ? ? ? ? ? ?  | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| Drop_priv ? ? ? ? ? ? ?  | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| Reload_priv ? ? ? ? ? ?  | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| Shutdown_priv ? ? ? ? ?  | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| Process_priv ? ? ? ? ? ? | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| File_priv ? ? ? ? ? ? ?  | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| Grant_priv ? ? ? ? ? ? ? | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| References_priv ? ? ? ?  | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| Index_priv ? ? ? ? ? ? ? | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| Alter_priv ? ? ? ? ? ? ? | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| Show_db_priv ? ? ? ? ? ? | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| Super_priv ? ? ? ? ? ? ? | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| Create_tmp_table_priv ?  | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| Lock_tables_priv ? ? ? ? | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| Execute_priv ? ? ? ? ? ? | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| Repl_slave_priv ? ? ? ?  | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| Repl_client_priv ? ? ? ? | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| Create_view_priv ? ? ? ? | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| Show_view_priv ? ? ? ? ? | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| Create_routine_priv ? ?  | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| Alter_routine_priv ? ? ? | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| Create_user_priv ? ? ? ? | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| Event_priv ? ? ? ? ? ? ? | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| Trigger_priv ? ? ? ? ? ? | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| Create_tablespace_priv ? | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| ssl_type ? ? ? ? ? ? ? ? | enum('','ANY','X509','SPECIFIED') | NO ? | ? ? | ? ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| ssl_cipher ? ? ? ? ? ? ? | blob ? ? ? ? ? ? ? ? ? ? ? ? ? ?  | NO ? | ? ? | NULL ? ? ? ? ? ? ? ?  | ? ? ? |
| x509_issuer ? ? ? ? ? ?  | blob ? ? ? ? ? ? ? ? ? ? ? ? ? ?  | NO ? | ? ? | NULL ? ? ? ? ? ? ? ?  | ? ? ? |
| x509_subject ? ? ? ? ? ? | blob ? ? ? ? ? ? ? ? ? ? ? ? ? ?  | NO ? | ? ? | NULL ? ? ? ? ? ? ? ?  | ? ? ? |
| max_questions ? ? ? ? ?  | int unsigned ? ? ? ? ? ? ? ? ? ?  | NO ? | ? ? | 0 ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| max_updates ? ? ? ? ? ?  | int unsigned ? ? ? ? ? ? ? ? ? ?  | NO ? | ? ? | 0 ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| max_connections ? ? ? ?  | int unsigned ? ? ? ? ? ? ? ? ? ?  | NO ? | ? ? | 0 ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| max_user_connections ? ? | int unsigned ? ? ? ? ? ? ? ? ? ?  | NO ? | ? ? | 0 ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| plugin ? ? ? ? ? ? ? ? ? | char(64) ? ? ? ? ? ? ? ? ? ? ? ?  | NO ? | ? ? | caching_sha2_password | ? ? ? |
| authentication_string ?  | text ? ? ? ? ? ? ? ? ? ? ? ? ? ?  | YES  | ? ? | NULL ? ? ? ? ? ? ? ?  | ? ? ? |
| password_expired ? ? ? ? | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| password_last_changed ?  | timestamp ? ? ? ? ? ? ? ? ? ? ? ? | YES  | ? ? | NULL ? ? ? ? ? ? ? ?  | ? ? ? |
| password_lifetime ? ? ?  | smallint unsigned ? ? ? ? ? ? ? ? | YES  | ? ? | NULL ? ? ? ? ? ? ? ?  | ? ? ? |
| account_locked ? ? ? ? ? | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| Create_role_priv ? ? ? ? | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| Drop_role_priv ? ? ? ? ? | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | NO ? | ? ? | N ? ? ? ? ? ? ? ? ? ? | ? ? ? |
| Password_reuse_history ? | smallint unsigned ? ? ? ? ? ? ? ? | YES  | ? ? | NULL ? ? ? ? ? ? ? ?  | ? ? ? |
| Password_reuse_time ? ?  | smallint unsigned ? ? ? ? ? ? ? ? | YES  | ? ? | NULL ? ? ? ? ? ? ? ?  | ? ? ? |
| Password_require_current | enum('N','Y') ? ? ? ? ? ? ? ? ? ? | YES  | ? ? | NULL ? ? ? ? ? ? ? ?  | ? ? ? |
| User_attributes ? ? ? ?  | json ? ? ? ? ? ? ? ? ? ? ? ? ? ?  | YES  | ? ? | NULL ? ? ? ? ? ? ? ?  | ? ? ? |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+

?

• 創建數據表

create table tableName(columnName（列名稱） dataType（數據類型）, ............);

?

• 刪除數據表

drop table tableName;

?

alter命令用法

語法

ALTER TABLE <表名> [修改選項]

常見用法

| ADD COLUMN <列名> <類型>
| CHANGE COLUMN <舊列名> <新列名> <新列類型>
| ALTER COLUMN <列名> { SET DEFAULT <默認值> | DROP DEFAULT }
| MODIFY COLUMN <列名> <類型>
| DROP COLUMN <列名>
| RENAME TO <新表名>
| CHARACTER SET <字符集名>
| COLLATE <校對規則名> 

案例

• 創建測試表

create table stu_score(id tinyint, name varchar(50), score decimal(4,2), object varchar(50));

• 修改表名

#alter table tab_name rename to tab_new;
#修改表名
alter table stu_score rename to stuS;

?

• 修改字段類型(修改字段相對位置)

#alter table tab_name modify field type (first/before/after field1);
alter table stuS modify id int;

?

• 修改字段默認值/是否為空/自動增長

#alter table tab_name modify field type not null/default ="未知"/auto_increment;
#自動增長
alter table stuS modify id int primary key;
alter table stuS modify id int auto_increment;

?

?

• 修改字段名/字段類型

#alter table tab_name change field  newfield newtype;
#修改字段類型
alter table stuS modify id int;
#修改字段名及類型
alter table stuS change column object subject varchar(50);
#通過alter modify 修改字段是否為null或者默認值!
alter table  stuS modify column name varchar(50) not null default "未知";

?

• 添加字段

#alter table tab_name add field type (first/before/after field1);
alter table stuS add column extra text;
#添加到第一列
alter table stuS add i int first; 
#添加到某一字段后面!
alter table stuS add sex varchar(10) after name; 

?

• 刪除字段

#alter table tab_name drop field;
alter table stuS drop id;

?

表數據操作

插入數據

• 單行插入

insert [into]  tableName[(columnName,.........)] value('value1',value2,.......);

• 批量插入

insert into stuS value('張三', 22, '11011011011', '男'),('王無',26 , '12011011011', '女'),('李四',27 ,'12011011099', '男');

注意：只書寫表名稱，表示全表插入，即，全字段插入！ 插入的數據數量與字段數量一定要相等！

where字句運算符

比較運算符

運算符	說明
>, >=, <, <=	大于，大于等于，小于，小于等于
=	等于，NULL 不安全，例如 NULL = NULL 的結果是 NULL
<=>	等于，NULL 安全，例如 NULL <=> NULL 的結果是 TRUE(1)
!=, <>	不等于
BETWEEN a0 AND a1	范圍匹配，[a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1)
IN (option, …)	如果是 option 中的任意一個，返回 TRUE(1)
IS NULL	是 NULL
IS NOT NULL	不是 NULL
LIKE	模糊匹配。% 表示任意多個（包括 0 個）任意字符；_ 表示任意一個字符

邏輯運算符

運算符	說明
AND （&&）	多個條件必須都為 TRUE(1)，結果才是 TRUE(1)
OR （||）	任意一個條件為 TRUE(1), 結果為 TRUE(1)
NOT （！）	條件為 TRUE(1)，結果為 FALSE(0)

更新數據

• 全表更新

update tableName set columnName=newValue;

?

• 條件更新

update tableName set  columnName=newValue where columnName=currentValue;
mysql> update stuS set extra='中國武術' where name='老六' and subject='11011011011';

刪除數據

• 全表刪除

delete from tableName;

?

• 條件刪除

delete from tableName where columnName=currentValue;
mysql> delete from stuS where extra='中國武術';

?

查詢數據

生成測試表

-- 學院表
CREATE TABLE college (college_id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL
);-- 專業表
CREATE TABLE major (major_id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL,college_id INT NOT NULL,FOREIGN KEY (college_id) REFERENCES college(college_id)
);-- 班級表
CREATE TABLE class (class_id INT PRIMARY KEY AUTO_INCREMENT,class_name VARCHAR(20) NOT NULL,grade INT NOT NULL,major_id INT NOT NULL,FOREIGN KEY (major_id) REFERENCES major(major_id)
);-- 學生表
CREATE TABLE student (student_id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL,gender ENUM('男','女') NOT NULL,class_id INT NOT NULL,FOREIGN KEY (class_id) REFERENCES class(class_id)
);-- 課程表
CREATE TABLE course (course_id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL,credit FLOAT NOT NULL
);-- 教師表
CREATE TABLE teacher (teacher_id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL,college_id INT NOT NULL,FOREIGN KEY (college_id) REFERENCES college(college_id)
);-- 專業-課程關聯表
CREATE TABLE major_course (major_id INT NOT NULL,course_id INT NOT NULL,PRIMARY KEY (major_id, course_id),FOREIGN KEY (major_id) REFERENCES major(major_id),FOREIGN KEY (course_id) REFERENCES course(course_id)
);-- 課程-教師關聯表
CREATE TABLE course_teacher (course_id INT NOT NULL,teacher_id INT NOT NULL,PRIMARY KEY (course_id, teacher_id),FOREIGN KEY (course_id) REFERENCES course(course_id),FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id)
);-- 學生選課表
CREATE TABLE student_course (student_id INT NOT NULL,course_id INT NOT NULL,score FLOAT,PRIMARY KEY (student_id, course_id),FOREIGN KEY (student_id) REFERENCES student(student_id),FOREIGN KEY (course_id) REFERENCES course(course_id)
);-- 插入3個學院
INSERT INTO college (name) VALUES 
('計算機學院'),
('經濟管理學院'),
('機械工程學院');-- 每個學院插入3個專業 (共9個專業)
INSERT INTO major (name, college_id) VALUES
('計算機科學與技術', 1),('軟件工程', 1),('人工智能', 1),
('金融學', 2),('會計學', 2),('市場營銷', 2),
('機械工程', 3),('車輛工程', 3),('材料成型', 3);-- 每個專業插入3個年級，每個年級3個班 (共81個班級)
INSERT INTO class (class_name, grade, major_id)
SELECT CONCAT(grade, '級', c.class_no, '班') AS class_name,grade,major_id
FROM (SELECT major_id FROM major
) m
CROSS JOIN (SELECT 2021 AS grade UNION SELECT 2022 UNION SELECT 2023
) g
CROSS JOIN (SELECT '1' AS class_no UNION SELECT '2' UNION SELECT '3'
) c;-- 每個班插入50名學生 (共4050名學生)
INSERT INTO student (name, gender, class_id)
SELECT CONCAT('學生', class_id, '_', s) AS name,IF(RAND() > 0.5, '男', '女') AS gender,class_id
FROM class
CROSS JOIN (SELECT 1 AS s UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNIONSELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20 UNIONSELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24 UNION SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29 UNION SELECT 30 UNIONSELECT 31 UNION SELECT 32 UNION SELECT 33 UNION SELECT 34 UNION SELECT 35 UNION SELECT 36 UNION SELECT 37 UNION SELECT 38 UNION SELECT 39 UNION SELECT 40 UNIONSELECT 41 UNION SELECT 42 UNION SELECT 43 UNION SELECT 44 UNION SELECT 45 UNION SELECT 46 UNION SELECT 47 UNION SELECT 48 UNION SELECT 49 UNION SELECT 50
) seq;-- 每個專業插入6門課程 (共54門課程)
INSERT INTO course (name, credit)
SELECT CONCAT(m.name, '課程', c) AS name,ROUND(1 + RAND() * 3, 1) AS credit
FROM major m
CROSS JOIN (SELECT 1 AS c UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
) c;-- 建立專業-課程關聯 (共54條記錄)
INSERT INTO major_course (major_id, course_id)
SELECT FLOOR((course_id - 1) / 6) + 1 AS major_id,course_id
FROM course;-- 每個學院插入20名教師 (共60名教師)
INSERT INTO teacher (name, college_id)
SELECT CONCAT('教師', c.college_id, '_', t) AS name,c.college_id
FROM college c
CROSS JOIN (SELECT 1 AS t UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNIONSELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20
) t;-- 每門課程關聯3名教師 (共162條記錄)
INSERT INTO course_teacher (course_id, teacher_id)
WITH course_teacher_random AS (SELECT c.course_id,t.teacher_id,ROW_NUMBER() OVER (PARTITION BY c.course_id ORDER BY RAND()) AS rnFROM course cCROSS JOIN teacher t
)
SELECT course_id, teacher_id
FROM course_teacher_random
WHERE rn <= 3;-- 插入學生選課記錄 (每個學生選修3-6門本專業的課程)
INSERT INTO student_course (student_id, course_id, score)
SELECT s.student_id,mc.course_id,ROUND(50 + RAND() * 50, 1) AS score  -- 隨機生成50-100分的成績
FROM student s
JOIN class c ON s.class_id = c.class_id
JOIN major_course mc ON c.major_id = mc.major_id
WHERE NOT EXISTS (SELECT 1 FROM student_course sc WHERE sc.student_id = s.student_id AND sc.course_id = mc.course_id
)  -- 確保不重復選課
GROUP BY s.student_id, mc.course_id
HAVING RAND() < 0.7  -- 70%的概率選擇這門課
ORDER BY RAND()
LIMIT 20000;  -- 大約20000條選課記錄-- 驗證各表記錄數
SELECT (SELECT COUNT(*) FROM college) AS college_count,(SELECT COUNT(*) FROM major) AS major_count,(SELECT COUNT(*) FROM class) AS class_count,(SELECT COUNT(*) FROM student) AS student_count,(SELECT COUNT(*) FROM course) AS course_count,(SELECT COUNT(*) FROM teacher) AS teacher_count,(SELECT COUNT(*) FROM major_course) AS major_course_count,(SELECT COUNT(*) FROM course_teacher) AS course_teacher_count,(SELECT COUNT(*) FROM student_course) AS student_course_count;

• 全表查詢

select * from tableName;
select * from class;

• 去重查詢

#distinct
?
select distinct grade from class;

• 統計查詢，非空數據

#count()
?
select count(distinct class) from stu;

?

• 重新定義查詢結果表中的列名稱

#as
?
select count(*) [as] count from stu;
#改變結果表中的字段名稱
select class_name '2021級3班', grade '2021', major_id '1' from class;

• 聚合函數查詢

#sum()：計算和值
select sum(score) 總成績 from sc;
+-----------+
| 總成績 ?  |
+-----------+
| ? 1422.00 |
+-----------+
?
#avg()：計算平均值
mysqld [jx2409]> select avg(score) 平均成績 from sc;
+--------------+
| 平均成績 ? ? |
+--------------+
| ? ?50.785714 |
+--------------+
?
#max()：最大值
mysqld [jx2409]> select max(score) 最高成績 from sc;
+--------------+
| 最高成績 ? ? |
+--------------+
| ? ? ? ?99.00 |
?
#min()：最小值
select min(score) 最低成績 from sc;
+--------------+
| 最低成績 ? ? |
+--------------+
| ? ? ? ? 0.00 |
+--------------+

?

• 分組查詢

#group by
##每個班的總成績
select class,sum(score) from sc group by class;
+-------+------------+
| class | sum(score) |
+-------+------------+
| ? ? 1 | ? ? 151.00 |
| ? ? 2 | ? ? 201.00 |
| ? ? 3 | ? ? 261.00 |
| ? ? 4 | ? ? 199.00 |
| ? ? 5 | ? ? 174.00 |
| ? ? 6 | ? ? 149.00 |
| ? ? 7 | ? ? 287.00 |
+-------+------------+
##排除2班的每個班的總成績
select class,sum(score) from sc where class!=2 group by class;
+-------+------------+
| class | sum(score) |
+-------+------------+
| ? ? 1 | ? ? 151.00 |
| ? ? 3 | ? ? 261.00 |
| ? ? 4 | ? ? 199.00 |
| ? ? 5 | ? ? 174.00 |
| ? ? 6 | ? ? 149.00 |
| ? ? 7 | ? ? 287.00 |
+-------+------------+

#分組后的條件：having

##查詢總成績大于200分的班級
select class,sum(score) from sc group by class having sum(score) > 200.00;
+-------+------------+
| class | sum(score) |
+-------+------------+
| ? ? 3 | ? ? 261.00 |
| ? ? 7 | ? ? 287.00 |
+-------+------------+

• 排序查詢

#order by
#升序
#select * from stu order by age [asc];
#查詢總成績的升序排列結果
select class,sum(score) from sc group by class order by sum(score);
+-------+------------+
| class | sum(score) |
+-------+------------+
| ? ? 6 | ? ? 149.00 |
| ? ? 1 | ? ? 151.00 |
| ? ? 5 | ? ? 174.00 |
| ? ? 4 | ? ? 199.00 |
| ? ? 2 | ? ? 201.00 |
| ? ? 3 | ? ? 261.00 |
| ? ? 7 | ? ? 287.00 |
+-------+------------+
#降序
#select * from stu order by age desc;
#查詢總成績的降序排列結果
select class,sum(score) from sc group by class order by sum(score) desc;
+-------+------------+
| class | sum(score) |
+-------+------------+
| ? ? 7 | ? ? 287.00 |
| ? ? 3 | ? ? 261.00 |
| ? ? 2 | ? ? 201.00 |
| ? ? 4 | ? ? 199.00 |
| ? ? 5 | ? ? 174.00 |
| ? ? 1 | ? ? 151.00 |
| ? ? 6 | ? ? 149.00 |
+-------+------------+

• 分頁查詢

#limit  number
?
#顯示前number行
?
#select * from stu limit 2;
##查詢總成績第一名
select class,sum(score) from sc group by class order by sum(score) desc limit 1;
+-------+------------+
| class | sum(score) |
+-------+------------+
| ? ? 7 | ? ? 287.00 |
+-------+------------+
?
?
#limit [startNumber,顯示的數據行數]
?
#select * from stu limit 2,1;
#只查看總成績的第二名
select class,sum(score) from sc group by class order by sum(score) desc limit 1,1;
+-------+------------+
| class | sum(score) |
+-------+------------+
| ? ? 3 | ? ? 261.00 |
+-------+------------+

• 子查詢

#select name,score from stu where name in (select name from info);
?
select name,score from sc where name in (select name from class2);
+-----------+-------+
| name ? ?  | score |
+-----------+-------+
| 魏嘉辰 ?  | 98.00 |
| 丁睿 ? ?  | 89.00 |
| 張毅 ? ?  | 14.00 |
+-----------+-------+

• union聯合查詢

####字段個數必須相同
mysql> select * from sc ?union select 1,2,3;
+-----------+-------+-------+
| name ? ? ?| class | score |
+-----------+-------+-------+
| 俞亮 ? ? ?| ? ? 1 | 70.00 |
| 劉博實 ? ?| ? ? 4 | 54.00 |
| 劉振堯 ? ?| ? ? 6 | 73.00 |
| 張霖杰 ? ?| ? ? 6 | 16.00 |
| 陳金招 ? ?| ? ? 5 | 31.00 |
| 武光斌 ? ?| ? ? 3 | 99.00 |
| 張浩 ? ? ?| ? ? 1 | 70.00 |
| 李松旺 ? ?| ? ? 3 | 43.00 |
| 潘圳航 ? ?| ? ? 5 | 28.00 |
| 米博宇 ? ?| ? ? 7 | 88.00 |
| 李振輝 ? ?| ? ? 4 | ?0.00 |
| 王晨宇 ? ?| ? ? 5 | 57.00 |
| 謝輝 ? ? ?| ? ? 2 | 84.00 |
| 1 ? ? ? ? | ? ? 2 | ?3.00 |
+-----------+-------+-------+
14 rows in set (0.01 sec)
?
?
###
mysql> select * from sc ?union select '鐵錘',2,45;
+-----------+-------+-------+
| name ? ? ?| class | score |
+-----------+-------+-------+
| 俞亮 ? ? ?| ? ? 1 | 70.00 |
| 劉博實 ? ?| ? ? 4 | 54.00 |
| 劉振堯 ? ?| ? ? 6 | 73.00 |
| 張霖杰 ? ?| ? ? 6 | 16.00 |
| 陳金招 ? ?| ? ? 5 | 31.00 |
| 武光斌 ? ?| ? ? 3 | 99.00 |
| 張浩 ? ? ?| ? ? 1 | 70.00 |
| 李松旺 ? ?| ? ? 3 | 43.00 |
| 潘圳航 ? ?| ? ? 5 | 28.00 |
| 米博宇 ? ?| ? ? 7 | 88.00 |
| 李振輝 ? ?| ? ? 4 | ?0.00 |
| 王晨宇 ? ?| ? ? 5 | 57.00 |
| 謝輝 ? ? ?| ? ? 2 | 84.00 |
| 鐵錘 ? ? ?| ? ? 2 | 45.00 |
+-----------+-------+-------+
14 rows in set (0.00 sec)
?
###
select * from sc union (select 1,name,3 from info limit 1);

?

• 多表查詢

內連接查詢

#select A.name as name, A.school, B.job from B,A where A.name=B.name;
?
#select A.name as name, A.school, B.job from B [inner] join A on A.name=B.name;
?
select class2.id, class2.name, sc.class, sc.score from sc  join class2 on sc.name=class2.name;
+------+-----------+-------+-------+
| id ? | name ? ?  | class | score |
+------+-----------+-------+-------+
| ? ?1 | 魏嘉辰 ?  | ? ? 2 | 98.00 |
| ? ?2 | 丁睿 ? ?  | ? ? 2 | 89.00 |
| ? ?3 | 張毅 ? ?  | ? ? 2 | 14.00 |
+------+-----------+-------+-------+

外連接查詢

• 左外連接

#select A.name as nameA, A.school, B.name as nameB, B.job from B  left join  A on A.name=B.name;
select class2.id, class2.name, sc.class, sc.score from sc left join class2 on sc.name=class2.name;
+------+-----------+-------+-------+
| id ? | name ? ?  | class | score |
+------+-----------+-------+-------+
| ? ?1 | 魏嘉辰 ?  | ? ? 2 | 98.00 |
| ? ?2 | 丁睿 ? ?  | ? ? 2 | 89.00 |
| ? ?3 | 張毅 ? ?  | ? ? 2 | 14.00 |
| NULL | NULL ? ?  | ? ? 7 | 64.00 |
| NULL | NULL ? ?  | ? ? 1 | 52.00 |
| NULL | NULL ? ?  | ? ? 7 | 99.00 |
| NULL | NULL ? ?  | ? ? 7 | 28.00 |
| NULL | NULL ? ?  | ? ? 5 | 91.00 |
| NULL | NULL ? ?  | ? ? 1 | 38.00 |
| NULL | NULL ? ?  | ? ? 4 | 29.00 |
| NULL | NULL ? ?  | ? ? 6 | ?1.00 |
| NULL | NULL ? ?  | ? ? 3 | 74.00 |
| NULL | NULL ? ?  | ? ? 6 | ?0.00 |
| NULL | NULL ? ?  | ? ? 3 | 25.00 |
| NULL | NULL ? ?  | ? ? 6 | 84.00 |
| NULL | NULL ? ?  | ? ? 1 | ?1.00 |
| NULL | NULL ? ?  | ? ? 1 | 53.00 |
| NULL | NULL ? ?  | ? ? 4 | 84.00 |
| NULL | NULL ? ?  | ? ? 3 | 79.00 |
| NULL | NULL ? ?  | ? ? 3 | 30.00 |
| NULL | NULL ? ?  | ? ? 7 | 96.00 |
| NULL | NULL ? ?  | ? ? 6 | 64.00 |
| NULL | NULL ? ?  | ? ? 3 | 12.00 |
| NULL | NULL ? ?  | ? ? 5 | 78.00 |
| NULL | NULL ? ?  | ? ? 1 | ?7.00 |
| NULL | NULL ? ?  | ? ? 5 | ?5.00 |
| NULL | NULL ? ?  | ? ? 4 | 86.00 |
| NULL | NULL ? ?  | ? ? 3 | 41.00 |
+------+-----------+-------+-------+

• 右外連接

#select A.name as nameA, A.school, B.name as nameB, B.job from B  right join  A on A.name=B.name;
select class2.id, class2.name, sc.class, sc.score from sc right join class2 on sc.name=class2.name;
+------+-----------+-------+-------+
| id ? | name ? ?  | class | score |
+------+-----------+-------+-------+
| ? ?1 | 魏嘉辰 ?  | ? ? 2 | 98.00 |
| ? ?2 | 丁睿 ? ?  | ? ? 2 | 89.00 |
| ? ?3 | 張毅 ? ?  | ? ? 2 | 14.00 |
| ? ?4 | Rose ? ?  |  NULL |  NULL |
+------+-----------+-------+-------+

范式違反對比總結

范式級別	核心問題	示例缺陷	后果
違反1NF	列值非原子	多值字段（如逗號分隔列表）	數據無法直接查詢/更新
違反2NF	部分依賴	非主屬性依賴復合主鍵的部分字段	數據冗余/更新異常
違反3NF	傳遞依賴	非主屬性間存在依賴（如A→B→C）	冗余和修改不一致風險

實戰演練

統計每個學院老師數量

統計每個學院專業數量

統計每個學院年級數量

?統計每個年級班級數量

2021級計算機科學與技術成績最高分姓名

2021級計算機科學與技術各個班的平均分

2023級會計班每個班平均分

機械工程學院2023級總分第一名

八、mysqld用戶權限管理

MySQL數據庫權限分類

Privilege	Context	Comment
Alter	Tables	To alter the table
Alter routine	Functions,Procedures	To alter or drop stored functions/procedures
Create	Databases,Tables,Indexes	To create new databases and tables
Create routine	Databases	To use CREATE FUNCTION/PROCEDURE
Create temporary tables	Databases	To use CREATE TEMPORARY TABLE
Create view	Tables	To create new views
Create user	Server Admin	To create new users
Delete	Tables	To delete existing rows
Drop	Databases,Tables	To drop databases, tables, and views
Event	Server Admin	To create, alter, drop and execute events
Execute	Functions,Procedures	To execute stored routines
File	File access on server	To read and write files on the server
Grant option	Databases,Tables,Functions,Procedures	To give to other users those privileges you possess
Index	Tables	To create or drop indexes
Insert	Tables	To insert data into tables
Lock tables	Databases	To use LOCK TABLES (together with SELECT privilege)
Process	Server Admin	To view the plain text of currently executing queries
Proxy	Server Admin	To make proxy user possible
References	Databases,Tables	To have references on tables
Reload	Server Admin	To reload or refresh tables, logs and privileges
Replication client	Server Admin	To ask where the slave or master servers are
Replication slave	Server Admin	To read binary log events from the master
Select	Tables	To retrieve rows from table
Show databases	Server Admin	To see all databases with SHOW DATABASES
Show view	Tables	To see views with SHOW CREATE VIEW
Shutdown	Server Admin	To shut down the server
Super	Server Admin	To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.
Trigger	Tables	To use triggers
Create tablespace	Server Admin	To create/alter/drop tablespaces
Update	Tables	To update existing rows
Usage	Server Admin	No privileges - allow connect only

中文釋義

在MySQL中，權限控制是通過Grant和Revoke命令來管理用戶和角色的權限的。
- Alter：修改。允許用戶修改數據庫中的表結構。
- Alter routine：修改存儲過程或函數。允許用戶修改已創建的存儲過程或函數。
- Create：創建。允許用戶創建新的數據庫對象，如表、視圖、存儲過程等。
- Create routine：創建存儲過程或函數。允許用戶創建新的存儲過程或函數。
- Create temporary tables：創建臨時表。允許用戶創建臨時表，這些表在會話結束時會自動刪除。
- Create view：創建視圖。允許用戶創建新的視圖。
- Create user：創建用戶。允許用戶創建新的數據庫用戶賬戶。
- Delete：刪除。允許用戶刪除表中的數據。
- Drop：刪除。允許用戶刪除數據庫中的對象，如表、視圖等。
- Event：事件。允許用戶管理和修改事件調度器中的事件。
- Execute：執行。允許用戶執行存儲過程或函數。
- File：文件。允許用戶訪問數據庫服務器的文件系統。
- Grant option：授予選項。允許用戶將權限授予其他用戶。
- Index：索引。允許用戶在表上創建、修改或刪除索引。
- Insert：插入。允許用戶向表中插入數據。
- Lock tables：鎖定表。允許用戶鎖定數據庫中的表，以防止其他用戶同時訪問這些表。
- Process：進程。允許查看或殺死數據庫服務器上的進程。
- Proxy：代理。允許用戶作為代理登錄，代表其他用戶執行操作。
- References：引用。允許用戶創建外鍵約束。
- Reload：重新加載。允許用戶重新加載配置文件或刷新權限表。
- Replication client：復制客戶端。允許用戶從主服務器接收數據以進行復制。
- Replication slave：復制從機。允許用戶作為數據復制的從服務器。
- Select：選擇。允許用戶查詢表中的數據。
- Show databases：顯示數據庫。允許用戶查看用的數據庫列表。
- Show view：顯示視圖。允許用戶查看數據庫中的視圖定義。
- Shutdown：關閉。允許用戶關閉數據庫服務器。
- Super：超級。允許用戶所有的權限，并且可以不受限制地執行許多系統命令。
- Trigger：觸發器。允許用戶創建觸發器，以在特定事件發生時自動執行代碼。
- Create tablespace：創建表空間。允許用戶創建新的表空間，用于存儲數據庫對象。
- Update：更新。允許用戶更新表中的數據。
- Usage：使用。這個權限通常用于限制用戶只能查看自己的權限，不能進行任何實際的數據庫操作。

user_name	host_name	說明
zhx	198.51.100.166	zhx，只能從此ip連接
zhx	198.51.100.%	zhx，從192.168.115子網中的任何主機
zhx	%	zhx，任何主機可連

創建遠程管理用戶

create user webuser@'192.168.115.%' identified by '123.com'; ?-- 只創建的用戶默認具有USAGE權限！
-- 更改密碼
alter user jx@192.168.166.9 identified by '123';
-- 在mysql8之前的數據庫，grant命令運行的時候，發現用戶不存在，則隱式創建！
grant all on *.* to 'root'@'192.168.115.130';
flush privileges;

創建遠程web服務數據庫的操作用戶

grant all on jx.* to 'webuser'@'192.168.115.%' identified by '123.com';
flush privileges;

查看用戶權限

show grants for ?'testuser'@'192.168.115.%';
mysqld [mysql]> show grants for webuser@'192.168.115.%';
+--------------------------------------------------------------------------------------------------------------------+
| Grants for webuser@192.168.115.% ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'webuser'@'192.168.115.%' IDENTIFIED BY PASSWORD '*AC241830FFDDC8943AB31CBD47D758E79F7953EA' |
+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

刪除用戶權限

revoke all on 庫.表 ?from 'username'@'ipaddress';
flush privileges;

案例

-- 刪除用戶的連接權限
revoke usage on *.* from 'username'@'ipaddress';
--無法直接刪除，只能刪除賬戶
drop user sluseralocalhost;

九、mysqld數據庫備份

9.1 數據備份的重要性

• 備份的主要目的是災難恢復。

• 在生產環境中，數據的安全性至關重要。

• 任何數據的丟失都可能產生嚴重的后果。

9.2 造成數據丟失的原因

• 程序錯誤

• 人為操作錯誤

• 運算錯誤

• 磁盤故障

• 災難（如火災、地震）和盜竊

9.2 備份需要考慮的問題

1. 可以容忍丟失多長時間的數據；

2. 恢復數據要在多長時間內完成；

3. 恢復的時候是否需要持續提供服務；

4. 恢復的對象，是整個庫，多個表，還是單個庫，單個表。

9.4 備份類型

1、根據是否需要數據庫離線

• 冷備（cold backup）：需要關mysql服務，讀寫請求均不允許狀態下進行；

• 溫備（warm backup）：服務在線，但僅支持讀請求，不允許寫請求；

• 熱備（hot backup）：備份的同時，業務不受影響。

注意：

這種類型的備份，取決于業務的需求，而不是備份工具

MyISAM不支持熱備，InnoDB支持熱備，但是需要專門的工具

1.1、常用備份工具

商業工具

• Navicat for MySQL

  • 功能特點：它提供了直觀的圖形化界面，方便用戶輕松地創建備份任務、設置備份計劃以及恢復數據。支持多種備份類型，如完整備份、增量備份等，還能對備份文件進行加密和壓縮，以確保數據的安全性和減小備份文件的大小。

  • 適用場景：適用于各種規模的企業和開發團隊，尤其是那些需要頻繁進行數據庫備份和恢復操作，且對操作的便捷性和可視化有較高要求的用戶。

• SQLyog Ultimate

  • 功能特點：具有強大的備份和恢復功能，支持定時備份，可以按照設定的時間周期自動執行備份任務。同時，它還提供了數據同步功能，能夠在不同的 MySQL 數據庫之間進行數據同步，方便數據遷移和數據一致性維護。

  • 適用場景：受 MySQL 開發者和數據庫管理員的歡迎，常用于開發環境和生產環境中的數據庫管理與備份工作，對于需要進行數據庫版本控制和數據遷移的場景也非常實用。

開源工具

• Percona XtraBackup

  • 功能特點：它是一款開源的熱備份工具，能夠在不停止 MySQL 服務的情況下進行備份，支持對 InnoDB 和 XtraDB 存儲引擎的備份和恢復，備份過程中不會阻塞數據庫的正常讀寫操作，從而保證了業務的連續性。此外，它還提供了增量備份和部分備份功能，可有效減少備份時間和備份文件的大小。

  • 適用場景：因其高效、靈活的特點，廣泛應用于各種對業務連續性要求較高的生產環境，特別適合大型數據庫和高并發應用場景下的備份需求 。

• MyDumper

  • 功能特點：是一款多線程的備份和恢復工具，能夠充分利用系統資源，大大提高備份和恢復的速度。它支持將數據庫備份為多個文件，便于在恢復時進行靈活的選擇和操作，同時還可以對備份文件進行壓縮，節省存儲空間。

  • 適用場景：適用于需要快速備份和恢復大型 MySQL 數據庫的場景，如互聯網公司的大規模數據存儲和處理環境，以及對備份效率有較高要求的企業級應用。

2、根據要備份的數據集合的范圍

• 完全備份：full backup，備份全部字符集（全部數據庫），每次備份都會進行完全備份，會導致備份文件占用大量的磁盤空間，并且有大量的重復數據，只適合第一次備份，不常用。

• 差異備份: incremental backup，要先進行一次完全備份，每次差異備份都會備份上一次完全備份后的數據，可能會出現備份的重復數據，導致占用大量的磁盤空間；

• 增量備份：differential backup，要先執行一次完全備份，每一次增量備份的數據都是備份在上一次完全備份或者上一次增量備份后的數據，不會出現重復數據，也不會占用額外的磁盤空間

建議的恢復策略

• 完全+增量+二進制日志

• 完全+差異+二進制日志

3、根據備份數據或文件

物理備份：直接備份數據文件

• 優點：備份和恢復操作都比較簡單，能夠支持兼容的mysql版本；恢復速度快，屬于文件系統級別的。

• 建議：不要假設備份一定可用，要測試 mysql>check tables；檢測表是否可用。

邏輯備份: 備份表中的數據和代碼

• 優點：恢復簡單；備份的結果為ASCII文件，可以編輯；與存儲引擎無關；可以通過網絡備份和恢復；

• 缺點：備份或恢復都需要mysql服務器進程參與；備份結果占據更多的空間；浮點數可能會丟失精度；還原之后，縮影需要重建

9.5 常見的備份方法

1、物理冷備（完全備份）

備份時數據庫處于關閉狀態，直接打包數據庫文件

備份速度快，恢復時也是最簡單的

2、專用備份工具mysqldump或mysqlhotcopy （完全備份，邏輯備份）

mysqldump常用的邏輯備份工具 （導出為sql腳本）

mysqlhotcopy僅擁有備份MyISAM和ARCHIVE表

啟用二進制日志進行增量備份 （增量備份）

進行增量備份，需要刷新二進制日志

3、第三方工具備份

免費的MySQL熱備份軟件Percona XtraBackup（阿里云的工具：dts，支持熱遷移）

9.6 備份案例

mysqldump工具

1、完整備份

所有數據庫的備份

備份

mysqldump --all-databases -uroot > all.sql

恢復

mysql -uroot < all.sql

指定數據庫的備份

備份

mysqldump --databases jx -uroot > jx.sql

恢復

mysql -uroot < jx.sql

指定數據表的備份

備份

mysqldump  jx stu > jx.stu.sql

恢復

mysql -uroot jx< jx.sql

2、增量備份

二進制備份

配置過程

vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format="statement"
systemctl restart mysqld

查看日志文件內容

##在數據庫中查看日志文件及書寫位置
show master status;
######
mysqlbinlog mysql-bin.000001 |less

二進制備份的恢復

模擬數據丟失流程

flush logs; ###刷新日志文件，產生新的日志文件
?
-- 創建數據表及插入數據
?
flush logs;
?
-- 執行刪除操作

數據恢復

基于位置

#基于開始位置
?
mysqlbinlog --start-position="245"  mysql-bin.000003 | mysql -uroot
?
#基于結束位置
?
mysqlbinlog --stop-position="632"  mysql-bin.000003 | mysql -uroot
?
#基于中間位置
?
mysqlbinlog --start-position="245" --stop-position="632"  mysql-bin.000003 | mysql -uroot

基于時間

#基于開始時間
?
mysqlbinlog --start-datetime="2022-12-23 16:17:59" mysql-bin.000003 | mysql -uroot
?
#基于結束時間
?
mysqlbinlog --stop-datetime="2022-12-23 16:17:59" mysql-bin.000003 | mysql -uroot
?
#基于中間時間
?
mysqlbinlog --start-datetime="2022-12-23 16:17:59" --stop-datetime="2022-12-23 16:17:59" mysql-bin.000003 | mysql -uroot

XtraBackup 8.0.35-33 使用指南

簡介

XtraBackup 8.0.35-33 是 Percona 提供的一個開源熱備份工具，用于 MySQL 8.0 數據庫的物理備份和恢復。它是 MySQL 企業級備份解決方案的重要組成部分。

安裝

在基于 RPM 的系統上安裝

sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
sudo percona-release enable-only tools release
sudo yum install percona-xtrabackup-80

在基于 Debian 的系統上安裝

wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
sudo percona-release enable-only tools release
sudo apt-get update
sudo apt-get install percona-xtrabackup-80

xtrabackup --prepare --target-dir=/path/to/backup

基本使用

1. 完整備份

xtrabackup --backup --target-dir=/path/to/backup --user=username --password=password

2. 準備備份（應用日志）

xtrabackup --prepare --target-dir=/path/to/backup

3. 恢復備份

xtrabackup --copy-back --target-dir=/path/to/backup

高級使用案例

案例1：增量備份

1. 首先進行完整備份：

xtrabackup --backup --target-dir=/backups/full --user=root --password=yourpassword

1. 進行第一次增量備份：

xtrabackup --backup --target-dir=/backups/inc1 --incremental-basedir=/backups/full --user=root --password=yourpassword

1. 進行第二次增量備份：

xtrabackup --backup --target-dir=/backups/inc2 --incremental-basedir=/backups/inc1 --user=root --password=yourpassword

1. 準備完整備份：

xtrabackup --prepare --apply-log-only --target-dir=/backups/full

1. 應用第一個增量備份：

xtrabackup --prepare --apply-log-only --target-dir=/backups/full --incremental-dir=/backups/inc1

1. 應用第二個增量備份：

xtrabackup --prepare --target-dir=/backups/full --incremental-dir=/backups/inc2

案例2：壓縮備份

xtrabackup --backup --compress --target-dir=/backups/compressed --user=root --password=yourpassword

解壓并準備備份：

xtrabackup --decompress --target-dir=/backups/compressed
xtrabackup --prepare --target-dir=/backups/compressed

案例3：并行備份和恢復

xtrabackup --backup --parallel=4 --target-dir=/backups/parallel --user=root --password=yourpassword
并行壓縮：

xtrabackup --backup --compress --compress-threads=4 --parallel=4 --target-dir=/backups/parallel_compressed --user=root --password=yourpassword

案例4：加密備份

1. 生成加密密鑰：

openssl rand -base64 24 > /backups/encryption_key

1. 創建加密備份：

xtrabackup --backup --target-dir=/backups/encrypted --encrypt=AES256 --encrypt-key-file=/backups/encryption_key --user=root --password=yourpassword

1. 解密備份：

xtrabackup --decrypt=AES256 --encrypt-key-file=/backups/encryption_key --target-dir=/backups/encrypted

案例5：流式備份

備份到單個壓縮文件：

xtrabackup --backup --stream=xbstream --user=root --password=yourpassword | gzip > /backups/backup.xbstream.gz

從流式備份恢復：

gunzip -c /backups/backup.xbstream.gz | xbstream -x -C /var/lib/mysql
xtrabackup --prepare --target-dir=/var/lib/mysql

常用選項說明

選項	描述
--backup	執行備份操作
--prepare	準備備份以進行恢復
--copy-back	將備份復制回原始位置
--target-dir	指定備份目標目錄
--user	MySQL 用戶名
--password	MySQL 密碼
--host	MySQL 主機地址
--port	MySQL 端口
--parallel	并行線程數
--compress	啟用壓縮
--compress-threads	壓縮線程數
--encrypt	加密算法
--encrypt-key-file	加密密鑰文件
--incremental-basedir	增量備份的基礎目錄
--incremental-dir	增量備份目錄
--stream	流式備份格式 (xbstream)

注意事項

1. 確保有足夠的磁盤空間存放備份文件

2. 備份期間數據庫負載可能會增加

3. 恢復前確保 MySQL 服務已停止

4. 恢復后可能需要調整文件權限

5. 定期測試備份的可用性

最佳實踐

1. 定期進行完整備份和增量備份

2. 備份文件存儲在不同于數據庫服務器的位置

3. 實施備份驗證流程

4. 記錄備份和恢復過程

5. 監控備份作業的狀態和持續時間

通過以上指南，您可以充分利用 XtraBackup 8.0.35-33 的強大功能來保護您的 MySQL 數據。

MyDumper 詳細使用指南

MyDumper 是一個高性能的 MySQL 邏輯備份工具，相比傳統的 mysqldump 具有并行備份、快照一致性等優勢。以下是 MyDumper 的詳細使用方式和案例。

安裝 MyDumper

Ubuntu/Debian 系統

sudo apt-get install mydumper

CentOS/RHEL 系統

sudo yum install mydumper

從源碼編譯

git clone https://github.com/mydumper/mydumper.git
cd mydumper
mkdir build
cd build
cmake ..
make
make install

基本使用

1. 完整備份數據庫

mydumper -u [username] -p [password] -h [host] -P [port] -o /backup/directory

2. 恢復數據庫

myloader -u [username] -p [password] -h [host] -P [port] -d /backup/directory

常用參數說明

參數	描述	示例
-u	用戶名	-u root
-p	密碼	-p secret
-h	主機地址	-h 127.0.0.1
-P	端口號	-P 3306
-o	輸出目錄	-o /backups
-d	恢復時指定備份目錄	-d /backups
-B	指定備份的數據庫	-B db1,db2
-T	指定備份的表	-T db1.table1
-t	線程數	-t 8
-c	壓縮輸出	-c
-v	詳細輸出	-v 3
-C	壓縮備份文件	-C
-e	備份表結構	-e
-r	分割表的行數	-r 100000
-F	按大小分割備份文件(MB)	-F 256
-s	一致性快照	-s

使用案例

案例1：備份單個數據庫

mydumper -u root -p password -h localhost -B mydatabase -o /backups/mydatabase

案例2：備份多個特定表

mydumper -u root -p password -h localhost -T db1.table1,db1.table2 -o /backups/tables

案例3：多線程備份（8個線程）

mydumper -u root -p password -h localhost -t 8 -o /backups/full

案例4：壓縮備份

mydumper -u root -p password -h localhost -c -o /backups/compressed

案例5：按100萬行分割表數據

mydumper -u root -p password -h localhost -r 1000000 -o /backups/split

案例6：備份數據庫結構（不備份數據）

mydumper -u root -p password -h localhost -e -o /backups/schema

myloader -u root -p password -h localhost -d /backups/full -B new_db_name

案例8：只恢復特定表

myloader -u root -p password -h localhost -d /backups/full -T db1.table1

高級功能

1. 一致性快照備份

mydumper -u root -p password -h localhost -s -o /backups/snapshot

2. 正則表達式過濾表

mydumper -u root -p password -h localhost -x '^sakila\.(actor|film)' -o /backups/regex

3. 備份時排除某些表

mydumper -u root -p password -h localhost -B sakila -X '^sakila\.film_text' -o /backups/exclude

4. 長查詢超時設置

mydumper -u root -p password -h localhost --long-query-retries=10 --long-query-retry-interval=30 -o /backups/timeout

5. 只備份數據不備份結構

mydumper -u root -p password -h localhost --no-schemas -o /backups/data_only

實際應用場景

# 備份腳本
#!/bin/bash
DATE=$(date +%Y%m%d)
BACKUP_DIR="/backups/mysql/$DATE"
LOG_FILE="/var/log/mydumper_${DATE}.log"
?
mydumper -u backup_user -p backup_pass -h 10.0.0.1 -P 3306 -t 4 -c -v 3 -o $BACKUP_DIR > $LOG_FILE 2>&1
?
# 保留7天備份
find /backups/mysql/ -type d -mtime +7 -exec rm -rf {} \;

場景2：大數據表部分恢復

# 只恢復用戶表的前100萬條數據
myloader -u root -p password -h localhost -d /backups/full -T db.users --rows=1000000

場景3：跨服務器遷移數據庫

# 源服務器
mydumper -u root -p password -h source_host -B db_to_migrate -c -o /tmp/db_backup
?
# 目標服務器
myloader -u root -p password -h target_host -d /tmp/db_backup

注意事項

1. 確保備份用戶有足夠的權限（至少需要 SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT 權限）

2. 大數據庫備份時考慮磁盤空間

3. 備份期間可能會對生產數據庫性能產生影響

4. 恢復前最好在測試環境驗證備份文件

5. 考慮使用--no-locks選項減少鎖表時間（但可能影響一致性）

性能優化建議

1. 根據服務器CPU核心數設置適當的線程數(-t)

2. 對大表使用-r參數分割備份文件

3. 使用-c或-C參數壓縮備份減少存儲空間

4. 考慮使用--no-locks選項減少鎖表時間（如果允許不一致）

5. 對于InnoDB表，使用-s參數獲取一致性快照

通過合理配置MyDumper，您可以高效地完成MySQL數據庫的備份和恢復工作，滿足各種業務場景的需求。

mysqlhotcopy 使用指南

mysqlhotcopy 是 MySQL 提供的一個 Perl 腳本工具，用于快速備份 MyISAM 和 ARCHIVE 表。它通過直接復制數據庫文件來實現快速備份，比邏輯備份工具如 mysqldump 更快，但只適用于特定存儲引擎。

安裝與準備

mysqlhotcopy 通常隨 MySQL 客戶端一起安裝，位于 MySQL 的 bin 目錄下。

檢查是否安裝

which mysqlhotcopy

確保依賴安裝（Perl 模塊）

sudo apt-get install perl-dbi perl-dbd-mysql ?# Debian/Ubuntu
sudo yum install perl-DBI perl-DBD-Mysql ? ? # CentOS/RHEL

基本語法

mysqlhotcopy [options] db_name [/path/to/backup/directory]

常用選項

選項	描述
--user=	MySQL 用戶名
--password=	MySQL 密碼
--host=	MySQL 主機
--port=	MySQL 端口
--socket=	MySQL socket 文件
--regexp=	使用正則表達式匹配數據庫
--allowold	不覆蓋現有備份，添加 _old 后綴
--keepold	不刪除被覆蓋的備份
--noindices	不備份索引文件
--method=	復制方法 (cp 或 scp)
--flushlog	備份后刷新日志
--resetmaster	備份后重置二進制日志
--resetslave	備份后重置從庫信息
--addtodest	添加而不是替換目標目錄
--dryrun	模擬執行，不實際復制

使用案例

案例1：備份單個數據庫

mysqlhotcopy --user=root --password=yourpassword mydatabase /backup/mysql

案例2：備份多個數據庫

mysqlhotcopy --user=root --password=yourpassword db1 db2 db3 /backup/mysql

案例3：使用正則表達式備份匹配的數據庫

mysqlhotcopy --user=root --password=yourpassword --regexp='^test_' /backup/mysql

案例4：保留舊備份

mysqlhotcopy --user=root --password=yourpassword --allowold --keepold mydatabase /backup/mysql

案例5：遠程備份到其他服務器

mysqlhotcopy --user=root --password=yourpassword --method=scp mydatabase user@remotehost:/remote/backup/dir

案例6：不備份索引文件

mysqlhotcopy --user=root --password=yourpassword --noindices mydatabase /backup/mysql

案例7：備份后刷新日志

mysqlhotcopy --user=root --password=yourpassword --flushlog mydatabase /backup/mysql

案例8：模擬運行（不實際備份）

mysqlhotcopy --user=root --password=yourpassword --dryrun mydatabase /backup/mysql

實際應用場景

場景1：生產環境每日備份腳本

#!/bin/bash
DATE=$(date +%Y%m%d)
BACKUP_DIR="/backup/mysql/$DATE"
LOG_FILE="/var/log/mysqlhotcopy_${DATE}.log"
?
# 確保備份目錄存在
mkdir -p $BACKUP_DIR
?
# 備份所有數據庫
mysqlhotcopy --user=backup_user --password=backup_pass \--allowold --keepold \--flushlog \--regexp='.*' \$BACKUP_DIR > $LOG_FILE 2>&1
?
# 刪除7天前的備份
find /backup/mysql/ -type d -mtime +7 -exec rm -rf {} \;

場景2：備份特定表

# 備份 mydatabase 中的 table1 和 table2
mysqlhotcopy --user=root --password=yourpassword \mydatabase.table1 mydatabase.table2 \/backup/mysql

場景3：增量備份策略

#!/bin/bash
DATE=$(date +%Y%m%d)
FULL_BACKUP_DIR="/backup/mysql/full"
INC_BACKUP_DIR="/backup/mysql/inc_$DATE"
?
# 每周日做完整備份
if [ $(date +%u) -eq 7 ]; thenmysqlhotcopy --user=backup_user --password=backup_pass \--allowold --keepold \--flushlog \--regexp='.*' \$FULL_BACKUP_DIR
else# 其他日子做增量備份mysqlhotcopy --user=backup_user --password=backup_pass \--addtodest \--regexp='.*' \$INC_BACKUP_DIR
fi

恢復數據庫

mysqlhotcopy 的恢復是通過直接復制文件回原始位置實現的：

1. 停止 MySQL 服務

systemctl stop mysql

1. 復制備份文件到 MySQL 數據目錄

cp -R /backup/mysql/mydatabase /var/lib/mysql/

1. 確保文件權限正確

chown -R mysql:mysql /var/lib/mysql/mydatabase

1. 啟動 MySQL 服務

systemctl start mysql

注意事項

1. 存儲引擎限制：mysqlhotcopy 只適用于 MyISAM 和 ARCHIVE 表，不適用于 InnoDB

2. 鎖表：mysqlhotcopy 在備份期間會鎖定表，可能導致應用程序短暫阻塞

3. 備份一致性：對于正在寫入的表，備份可能不一致

4. 權限要求：

   • 執行用戶需要有讀取數據庫文件的權限

   • MySQL 用戶需要 SELECT、RELOAD、LOCK TABLES 權限

5. 備份完整性：備份后建議驗證備份文件的完整性

6. 版本兼容性：確保備份和恢復使用相同版本的 MySQL

性能優化建議

1. 在低峰期執行備份操作

2. 對于大型數據庫，考慮分批備份

3. 使用 --noindices 選項可以加快備份速度（但恢復時需要重建索引）

4. 考慮使用 --method=scp 直接備份到遠程服務器，減少本地磁盤IO

5. 對于頻繁更新的表，考慮結合 FLUSH TABLES 命令確保數據一致性

替代方案

由于 mysqlhotcopy 的局限性，現代 MySQL 環境通常使用以下替代方案：

• 對于 InnoDB 表：使用 Percona XtraBackup

• 邏輯備份：使用 mysqldump 或 mydumper

• 文件系統快照：LVM 快照或存儲設備快照功能

mysqlhotcopy 最適合用于 MyISAM 表的快速備份場景，特別是當數據庫大小適中且可以接受短暫鎖表的情況下。

附錄 A mysqld配置文件

# mysqld配置文件
?
# ----------------- 客戶端配置 -----------------
[client]
port = 3306 # 客戶端連接數據庫的端口，默認為3306
socket = /var/lib/mysql/mysql.sock # 服務器socket文件的路徑，默認為/var/lib/mysql/mysql.sock
?
# ----------------- MySQL客戶端工具配置 -----------------
[mysql]
default-character-set=utf8mb4 # 客戶端連接數據庫時的默認字符集，這里使用utf8mb4
?
# ----------------- 服務器配置 -----------------
[mysqld]
user = mysql # 運行數據庫服務器的系統用戶，默認為mysql
port = 3306 # 服務器監聽的端口，默認為3306
socket = /var/lib/mysql/mysql.sock # 服務器socket文件的路徑，默認為/var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysql.pid # 服務器進程ID保存的文件路徑，默認為/var/lib/mysql/mysql.pid
bind-address = 127.0.0.1 # 服務器綁定的IP地址，默認為本地回環地址
?
# ----------------- 日志配置 -----------------
log-error = /var/log/mysql/error.log # 錯誤日志文件路徑，默認為/var/log/mysql/error.log
slow-query-log = 1 # 是否啟用慢查詢日志，1表示啟用，0表示禁用，默認為禁用
slow-query-log-file = /var/log/mysql/slow.log # 慢查詢日志文件路徑，默認為/var/log/mysql/slow.log
?
# ----------------- 緩存配置 -----------------
query_cache_type = 1 # 查詢緩存類型，1表示啟用，0表示禁用，默認為禁用
query_cache_size = 32M # 查詢緩存大小，默認為32M
query_cache_limit = 2M # 查詢緩存單個查詢的最大緩存大小，默認為2M
?
# ----------------- 字符集配置 -----------------
character-set-server = utf8mb4 # 服務器使用的字符集，默認為utf8mb4
collation-server = utf8mb4_unicode_ci # 服務器使用的字符集排序規則，默認為utf8mb4_unicode_ci
?
# ----------------- 默認存儲引擎 -----------------
default-storage-engine = InnoDB # 默認使用的存儲引擎，默認為InnoDB
?
# ----------------- InnoDB配置 -----------------
innodb_buffer_pool_size = 256M # InnoDB緩沖池大小，默認為256M
innodb_flush_log_at_trx_commit = 2 # 日志刷新策略，2表示每秒刷新，默認為每次事務提交刷新
innodb_log_buffer_size = 8M # InnoDB日志緩沖區大小，默認為8M
innodb_file_per_table = 1 # 是否為每個InnoDB表使用單獨的表空間，1表示啟用，0表示禁用，默認為啟用
innodb_open_files = 400 # InnoDB打開的文件數量，默認為400
?
# ----------------- 網絡和連接配置 -----------------
max_connections = 1000 # 最大并發連接數，默認為1000
max_allowed_packet = 16M # 允許的最大數據包大小，默認為16M
skip_external_locking = 1 # 是否禁用外部鎖定，默認為是
?
# ----------------- 安全性配置 -----------------
secure-file-priv = /var/lib/mysql-files # 加載數據文件的安全目錄，默認為/var/lib/mysql-files
sql-mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES # 服務器的SQL模式，默認為NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

附錄 B mysql常見內置函數

一、字符串函數

-- ASCII(str):返回字符串str的最左面字符的ASCII代碼值。如果str是空字符串，返回0。如果str是NULL，返回NULL
SELECT ASCII("dd");-- 100
SELECT ASCII("dc");-- 100-- CONCAT(str1,str2,...)：返回來自于參數連結的字符串。如果任何參數是NULL，返回NULL。可以有超過2個的參數。一個數字參數被變換為等價的字符串形式
select CONCAT('My', 'S', 'QL'); -- MySQL
select CONCAT('My', NULL, 'QL'); -- NULL
select CONCAT(14.3); -- 14.3-- LENGTH(str):返回字符串str的字節長度
select LENGTH('text'); -- 4
select LENGTH('字符串');-- 9-- CHAR_LENGTH(str):用于獲取字符串長度
select CHAR_LENGTH('text'); -- 4
select CHAR_LENGTH('字符串');-- 3-- LOCATE(substr,str):返回子串substr在字符串str第一個出現的位置，如果substr不是在str里面，返回0
select LOCATE('bar', 'foobarbar'); -- 4
select LOCATE('xbar', 'foobar'); -- 0-- INSTR(str,substr):返回子串substr在字符串str中的第一個出現的位置
select INSTR('foobarbar', 'bar'); -- 4
select INSTR('xbar', 'foobar'); -- 0-- LEFT(str,len)/RIGHT(str,len):返回字符串str的最左/右面len個字符
select LEFT('foobarbar', 5); -- fooba
select RIGHT('foobarbar', 4); -- rbar-- SUBSTRING(str,pos):從字符串str的起始位置pos返回一個子串
select SUBSTRING('foobarbar',5);-- arbar-- TRIM(str):返回字符串str，所有前綴或后綴被刪除了
select TRIM(' xbar ');-- xbar-- LTRIM(str)/RTRIM(str)：返回刪除了其前/后置空格字符的字符串str。
select LTRIM(' xbar');-- xbar
select RTRIM('xbar ');--xbar-- REPLACE(str,from_str,to_str):返回字符串str，其字符串from_str的所有出現由字符串to_str代替
select REPLACE('xbar', 'x', 'bar');-- barbar-- REPEAT(str,count):返回由重復countTimes次的字符串str組成的一個字符串。如果count <= 0，返回一個空字符串。如果str或count是NULL，返回NULL
select REPEAT('bar', 3);-- barbarbar-- REVERSE(str):返回顛倒字符順序的字符串str。
select REVERSE('bar');-- rab-- INSERT(str,pos,len,newstr):返回字符串str，在位置pos起始的子串且len個字符長的子串由字符串newstr代替。 
select INSERT(whatareyou', 5, 3, is');-- whatisyou-- strcmp(str1,str2):用于比較兩個字符串的大小。左大于右時返回1，左等于右時返回0，，左小于于右時返回-1
SELECT strcmp('ab','ac');-- -1-- 大寫：upper(x),ucase(x)；小寫lower(x),lcase(x):字母大小寫轉換函數;
SELECT UPPER("abc");-- ABC
SELECT UCASE("abc");-- ABC
SELECT LOWER("ABC");-- abc
SELECT LCASE("ABC");-- abc-- find_in_set(str1,str2):返回字符串str1在str2中的位置，str2包含若干個以逗號分隔的字符串（可以把str2看出一個列表，元素是多個字符串，查找結果是str1在str2這個列表中的索引位置，從1開始）
SELECT FIND_IN_SET('abc','123,456,abc');-- 3-- field(str,str1,str2,str3…):與find_in_set類似，但str2由一個類似列表的字符串變成了多個字符串，返回str在str1,str2,str3…中的位置。
SELECT FIELD('abc','123','456','abc');-- 3-- elt(index,str1,str2,str3…):獲取指定位置的字符串
SELECT elt(3,'123','456','abc');-- abc

二、日期時間函數

-- curdate()/current_date():獲取當前日期
SELECT curdate();-- 2018-08-09
SELECT current_date();-- 2018-08-09-- curtime()/current_time()：獲取當前時間
SELECT curtime();-- 15:38:54
SELECT current_time();-- 15:38:54-- now():獲取當前日期時間
select now();-- 2018-08-09 15:40:09-- month(date),monthname(date):獲取日期月份
SELECT MONTH (now());-- 8
SELECT monthname(now());-- August-- week(date)：獲取日期周數
select week(now());-- 31-- year(date):獲取日期年數
select year(now());-- 2018-- hour(time):獲取時間時刻
select hour(now());-- 15-- minute(time):獲取時間分鐘數
select minute(now());-- 47-- DAYOFWEEK(date)/DAYNAME(date)/WEEKDAY(date):獲取時間星期數
select DAYOFWEEK(NOW());-- 5
select DAYNAME(now());-- Thursday
select WEEKDAY(now());-- 3-- DATE_ADD(date,INTERVAL expr type)/DATE_SUB(date,INTERVAL expr type):進行日期增加/減少的操作，可以精確到秒
SELECT '2018-08-09'+INTERVAL 1 DAY;-- 2018-08-10
SELECT '2018-08-09'-INTERVAL 1 SECOND;-- 2018-08-08 23:59:59
SELECT DATE_ADD('2018-08-08 23:59:59',INTERVAL 1 SECOND);-- 2018-08-09 00:00:00
SELECT DATE_SUB('2018-08-09 00:01:01',INTERVAL '1:1' MINUTE_SECOND);-- 2018-08-09 00:00:00-- date_format('time','format')/time_format(time,format):日期時間轉換為字符串
select date_format(now(), '%Y%m%d%H%i%s');-- 20180809160315
select time_format(now(),'%H:%i:%s');--16:03:15-- str_to_date(str, format)：字符串轉換為日期
select str_to_date('08.09.2018 16:06:30', '%m.%d.%Y %H:%i:%s');-- 2018-08-09 16:06:30-- makdedate(year,dayofyear)/maketime(hour,minute,second)：拼湊日期、時間函數
select makedate(2001,31); -- '2001-01-31'
select makedate(2001,32); -- '2001-02-01'
select maketime(12,15,30); -- '12:15:30'-- unix_timestamp()/unix_timestamp(date)/from_unixtime(unix_timestamp)/from_unixtime(unix_timestamp,format):Unix 時間戳、日期轉換函數
select unix_timestamp(); -- 1533802315
select unix_timestamp(now());-- 1533802315
select from_unixtime(1533802315); -- 2018-08-09 16:11:55
select from_unixtime(1533802315, '%Y%m%d%H%i%s'); -- 20180809161155

三、數學函數

-- ABS(X):返回X的絕對值
select ABS(-32);-- 32-- MOD(N,M)或%:返回N被M除的余數
select MOD(15,7);-- 1
select 15 % 7; -- 1-- FLOOR(X):返回不大于X的最大整數值
select FLOOR(1.23); -- 2
select FLOOR(-1.23); -- -2-- CEILING(X)/ceil(x):返回不小于X的最小整數值
select CEILING(1.23);-- 2 
select CEILING(-1.23); -- -1-- ROUND(X) :返回參數X的四舍五入的一個整數。 
select ROUND(1.58); -- 2
select ROUND(-1.58); -- -2-- round(x,y):返回數值x帶有y為小數結果的數值（四舍五入）
SELECT round(3.1415926,2);-- 3.14-- rand():返回隨機數
select rand();-- 0.5911854436538978-- truncate(x,y):返回數值x截取y位小數的結果（不四舍五入）
select truncate(3.1415926,4);-- 3.1415

四、邏輯函數

-- CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END：在第一個方案的返回結果中， value=compare-value。而第二個方案的返回結果是第一種情況的真實結果。如果沒有匹配的結果值，則返回結果為ELSE后的結果，如果沒有ELSE 部分，則返回值為 NULL。
SELECTCASE 1
WHEN 1 THEN2
ELSE3
END;-- 2-- IF(expr1,expr2,expr3)：如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，則 IF()的返回值為expr2; 否則返回值則為 expr3。IF() 的返回值為數字值或字符串值，具體情況視其所在語境而定
SELECT IF(1>2,2,3);-- 3-- STRCMP(expr1,expr2)：如果字符串相同，STRCMP()返回0，如果第一參數根據當前的排序次序小于第二個，返回-1，否則返回1
select STRCMP('text', 'text2');-- -1-- SELECT IFNULL(expr1,expr2)：如果expr1為空則返回expr2否則返回expr1
SELECT IFNULL(1,2);-- 2

五、加密函數

-- MD5(str)：函數可以對字符串str進行加密。MD5(str)函數主要對普通的數據進行加密。下面使用MD5(str)函數為字符串“abcd”加密
SELECT MD5('abcd');-- e2fc714c4727ee9395f324cd2e7f331f-- ENCODE(str,pswd_str)：函數可以使用字符串pswd_str來加密字符串str。加密的結果是一個二進制數，必須使用BLOB類型的字段來保存它。
SELECT ENCODE("abcd","evan");-- DECODE(crypt_str,pswd_str)函數可以使用字符串pswd_str來為crypt_str解密。crypt_str是通過ENCODE(str,pswd_str)加密后的二進制數據。字符串pswd_str應該與加密時的字符串pswd_str是相同的。下面使用DECODE(crypt_str,pswd_str)為ENCODE(str,pswd_str)加密的數據解密。
SELECT DECODE(ENCODE("abcd","evan"),"evan");