第11周

本章內容

?關系型數據庫基礎

?MySQL架構

?存儲引擎

?安裝MySQL

?服務器選項，系統和狀態變量

?管理數據庫和表

?用戶和權限管理

?函數和存儲過程

?優化查詢和索引管理

?鎖和事務管理

?日志管理

?備份還原

?MySQL集群?

數據的時代

?涉及的數據量大

?數據不隨程序的結束而消失

?數據被多個應用程序共享

?大數據

數據庫的發展史

?萌芽階段—–文件系統

使用磁盤文件來存儲數據

?初級階段—–第一代數據庫

出現了網狀模型、層次模型的數據庫

?中級階段—–第二代數據庫

關系型數據庫和結構化查詢語言 ? ?（類似于橫行縱列的表格）

?高級階段——新一代數據庫

“關系-對象”型數據庫

文件管理系統的缺點

?編寫應用程序不方便

?數據冗余不可避免

?應用程序依賴性

?不支持對文件的并發訪問

?數據間聯系弱

?難以按用戶視圖表示數據

?無安全控制功能

數據庫管理系統（DBMS）的優點

?相互關聯的數據的集合

?較少的數據冗余

?程序與數據相互獨立 ? 存儲引擎就是數據存貯的格式

?保證數據的安全、可靠（可精確到某個列可以訪問）

?最大限度地保證數據的正確性（比如年齡可以限制范圍1-100等）

?數據可以并發使用并能同時保證一致性

其實就是數據按照數據管理的格式存放在硬盤上，訪問時需要通過數據庫管理軟件來訪問

數據庫管理系統

?數據庫是數據的匯集，它以一定的組織形式存于存儲介質上（磁盤或者內存中（memcache））

?DBMS是管理數據庫的系統軟件，它實現數據庫系統的各種功能。是數據庫系

統的核心

?DBA：負責數據庫的規劃、設計、協調、維護和管理等工作

?應用程序指以數據庫為基礎的應用程序

raw文件系統，就是沒有文件系統的磁盤空間，就是裸磁盤。可以存放數據但是存放的是0101

數據庫管理系統也可以訪問這種系統

數據庫管理系統的基本功能

?數據定義（定義列是什么類型比如是整數或者字符等）

?數據處理（增刪改查等）

?數據安全（加權限控制）

?數據備份（必須會）

數據庫系統的架構

?單機架構 ? ? ? ? ? ? ? （單個人使用如財務軟件）

?大型主機/終端架構 ?（一個主機多個終端）

?主從式架構（C/S）（MYSQL等數據庫都是這種架構）性能上有瓶頸

?分布式架構 ? ?將數據分散到多個服務器上提高性能

關系型數據庫（性能不好）

不是所有的數據庫都是關系型的

NO SQL

not only sql

memcached redis （k/v）mongodb(文檔)

?關系：關系就是二維表。并滿足如下性質：

表中的行、列次序并不重要

?行row：表中的每一行，又稱為一條記錄

?列column：表中的每一列，稱為屬性，字段

?主鍵（Primary key）：用于惟一確定一個記錄（record）的字段（PK）這個字段所有記錄的內容不能重復

?域domain：屬性的取值范圍，如，性別只能是‘男’和‘女’兩個值

主鍵（班級編號）學員 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?課程 ? ? ? ? ? ? ? ? ? 課程編號 ? 課程

關系數據庫

?RDBMS：

MySQL: MySQL, MariaDB, Percona Server

PostgreSQL: 簡稱為pgsql，EnterpriseDB

Oracle：

MSSQL：

DB2:

?事務transaction：多個操作被當作一個整體對待（5個操作如果在一個事務里要么都做要么都不做）

ACID:

A: 原子性 ? ?比如取錢 ? （如果事務做到一半斷電了，那么系統恢復后不會繼續任務而是依據log回滾到沒做事務之前的狀態）

C：一致性（列如倆個銀行卡之間匯錢，一個減少一個增多但是總的不變）

I: 隔離性 ? （B的事務沒有結束之前A只能看到110）有一定得隔離級別

D：持久性 ?事務結束，數據永久生效保存在磁盤中

隔離性如圖

關系型數據庫相關概念介紹

（1）概念：

關系型數據庫模型是把復雜的數據結構歸結為簡單的二元關系（即二維表格形式）。在關系型數據庫中，對數據的操作幾乎全部建立在一個或多個關系表格上，通過對這些關聯的表格分類、合并、連接或選取等運算來實現數據庫的管理。

（2）關系：

關系就是指存儲數據的二維表，表種的行和列的存貯不注重次序，因為展示可以通過相關操作來實現次序的需求。

（3）行row：

表中的每一行，又稱為一條記錄record

（4）列column：

表中的每一列，稱為屬性，字段，域（field）；這個域和domain這個域的含義不同。前者指列的一種稱呼，后者指列的取值范圍。

（5）事務（transaction）：

事務(Transaction)，一般是指要做的或所做的事情。在計算機術語中是指訪問并可能更新數據庫中各種數據項的一個程序執行單元(unit)，通常就是指數據庫事務。

事務的特性：ACID

原子性（Atomicity）：事務作為一個整體被執行，包含在其中的對數據庫的操作要么全部被執行，要么都不執行。

一致性（Consistency）：事務應確保數據庫的狀態從一個一致狀態轉變為另一個一致狀態。一致狀態的含義是數據庫中的數據應滿足完整性約束。

隔離性（Isolation）：多個事務并發執行時，一個事務的執行不應影響其他事務的執行。

持久性（Durability）：一個事務一旦提交，他對數據庫的修改應該永久保存在數據庫中。

實體-聯系模型E-R

?實體Entity

? 客觀存在并可以相互區分的客觀事物或抽象事件稱為實體。

? 在E-R圖中用矩形框表示實體，把實體名寫在框內

?屬性

? 實體所具有的特征或性質

?聯系

?聯系是數據之間的關聯集合，是客觀存在的應用語義鏈

? 實體內部的聯系：指組成實體的各屬性之間的聯系。如職工實體中，職工號和

部門經理號之間有一種關聯關系

? 實體之間的聯系：指不同實體之間聯系。例學生選課實體和學生基本信息實體

之間

? 實體之間的聯系用菱形框表示

聯系類型

?聯系的類型

?一對一聯系(1:1)

?一對多聯系(1:n)

?多對多聯系(m:n)

一對多如圖

? ? ? ? ? ? ? ? ? ? ? ?員工 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?部門

foreign key ? ?外鍵

多對多如圖

? ? ? ? ? ? ? ? ? 學員編號 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 課程

5.數據三要素

（1）數據結構：

包括兩類，一類是與數據類型、內容、性質有關的對象，比如關系模型中的域、屬性和關系等；另一類是與數據之間聯系有關的對象，它從數據組織層表達數據記錄與字段的結構。

（2）數據的操作：

數據提取（SELECT）在數據集合中提取感興趣的內容。

數據更新（INSERT、DELETE、 UPDATE ）變更數據庫中的數據。

（3）數據的約束條件：

是一組完整性規則的集合。

實體（行）完整性 Entity integrity

值行的唯一性，列和列總是不同的，利用主鍵實現（唯一鍵UK（作用在字段上表示這個字段不能有重復的但是可以是空值（null），主鍵只有一個且不能是空，唯一鍵可以有多個））

域（列）完整性 Domain Integrity

指有一個合理的取值范圍

參考完整性 Referential Integrity（就是外鍵兩個表之間的。）

簡易數據規劃流程

?第一階段：收集數據，得到字段

? 收集必要且完整的數據項

? 轉換成數據表的字段

?第二階段：把字段分類，歸入表，建立表的關聯

? 關聯：表和表間的關系

? 分割數據表并建立關聯的優點

? 節省空間

? 減少輸入錯誤

? 方便數據修改

?第三階段：

? 規范化數據庫

數據庫的正規化分析

?RDMBS設計范式基礎概念

設計關系數據庫時，遵從不同的規范要求，設計出合理的關系型數據庫，這些不

同的規范要求被稱為不同的范式，各種范式呈遞次規范，越高的范式數據庫冗余越

小

?目前關系數據庫有六種范式：第一范式（1NF）、第二范式（2NF）、第三范式

（3NF）、巴德斯科范式（BCNF）、第四范式(4NF）和第五范式（5NF，又稱

完美范式）。滿足最低要求的范式是第一范式（1NF）。在第一范式的基礎上

進一步滿足更多規范要求的稱為第二范式（2NF），其余范式以次類推。一般

說來，數據庫只需滿足第三范式(3NF）即可

范式是可以違反的。

范式

?1NF：無重復的列，每一列都是不可分割的基本數據項，同一列中不能有多個

值，即實體中的某個屬性不能有多個值或者不能有重復的屬性。除去同類型的

字段，就是無重復的列

說明：第一范式（1NF）是對關系模式的基本要求，不滿足第一范式（1NF）的

數據庫就不是關系數據庫

?2NF：屬性完全依賴于主鍵，第二范式必須先滿足第一范式，要求表中的每個

行必須可以被唯一地區分。通常為表加上一個列，以存儲各個實例的唯一標識

PK，非PK的字段需要與整個PK有直接相關性

?3NF：屬性不依賴于其它非主屬性，滿足第三范式必須先滿足第二范式。第三

范式要求一個數據庫表中不包含已在其它表中已包含的非主關鍵字信息，非PK

的字段間不能有從屬關系

第一范式

第二范式：

城市編號和人名沒有直接的依賴性其他都符合第二范式。

單獨設置一張表

城市編號 ? ? ? ? ? ? ? ? ? ? ? ? ?城市名稱 ? ? ? ? ? ? ? ? ? ? ?城市電話區號

復合主鍵

約束

7.數據庫約束

（1）主鍵

關系型數據庫中的一條記錄中有若干個屬性，若其中某一個屬性組(注意是組)能唯一標識一條記錄，該屬性組就可以成為一個主鍵。

要求：必須提供數據，即字段不為空（null）；字段不重復，即唯一標識本行；一張表只能有一個主鍵。

目的：確定一條唯一的記錄。

（2）復合主鍵

表中一個屬性無法唯一標識一條記錄，用兩條屬性組才可以唯一標識一條記錄，那么兩條屬性主就組成了符合主鍵。單獨的每列的字段可重復，但是兩個列組成主鍵后總字段是不重復的。

（3）唯一鍵

一個或多個字段的組合，填入的數據必須能在本表唯一標識本行。和主鍵不同之處：在表中可以有多個；允許為空值

（4）外鍵

一個表中的某字段可填入的數據取決于另一個表的主鍵或唯一鍵已有的數據，鍵表示了兩個表之間的相關聯系。一個表的主鍵是另外一張表的外鍵（唯一鍵也可以做外鍵）

檢查：字段值在一定范圍內

SQL概念

?SQL: Structure Query Language

結構化查詢語言 ? ?（只關注數據是什么而不關注在磁盤上的格式）

SQL解釋器：

數據存儲協議：應用層協議，C/S

?S：server, 監聽于套接字，接收并處理客戶端的應用請求標準3306端口號

?C：Client

程序接口

CLI

GUI

應用編程接口 ? ?程序要和數據庫連接要符合數據庫接口

ODBC：Open Database Connectivity

JDBC：Java Data Base Connectivity

基本概念

?索引：將表中的一個或多個字段中的數據復制一份另存，并且此些需要按特定 ? （例如書的目錄）

次序排序存儲（如果添加新的章節那么需要更新整個目錄和頁數）如果用于查看則使用索引如果改的多讀的少則看情況添加索引

?關系運算：

選擇：挑選出符合條件的行 ? 挑行 ? ?（什么手機）

投影：挑選出需要的字段 ? ? 挑列（什么cpu）

連接：表間字段的關聯 ? ? ? ? ? （外鍵）

數據模型

?數據抽象：

?物理層：數據存儲格式，即RDBMS在磁盤上如何組織文件（存放文件的格式不同的引擎不同的格式）為了優化性能，將文件（數據和日志）放在不同的位置

?邏輯層：DBA角度，描述存儲什么數據，以及數據間存在什么樣的關系（約束，第一范式等）

?視圖層：用戶角度，描述DB中的部分數據（列如商品中的一些屬性價格等）（進貨價格不讓查看）

?關系模型的分類：

?關系模型

?基于對象的關系模型

?半結構化的關系模型：XML數據（擴展的標記語言）

在centos6中/etc/gconf/gconf.xml.defaults/%gconf-tree.xml就是XML格式的

MySQL歷史

?1979年：TcX公司 Monty Widenius，Unireg數據庫引擎

?1996年：發布MySQL1.0，Solaris版本，Linux版本

?1999年：MySQL AB公司，瑞典

?2003年：MySQL 5.0版本，提供視圖、存儲過程等功能

?2008年：sun 收購

?2009年：oracle收購sun

?2009年：Monty成立MariaDB

MySQL和MariaDB

?官方網址：

https://www.mysql.com/

http://mariadb.org/

?官方文檔

https://dev.mysql.com/doc/

https://mariadb.com/kb/en/

?版本演變：

MySQL：5.1 –> 5.5 –> 5.6 –> 5.7

MariaDB：5.5 –>10.0–> 10.1 –> 10.2 –> 10.3

MariaDB的特性

?插件式存儲引擎：也稱為“表類型”，存儲管理器有多種實現版本，功能和特

性可能均略有差別；用戶可根據需要靈活選擇,Mysql5.5.5開始innoDB引擎是

MYSQL默認引擎

MyISAM ==> Aria

InnoDB ==> XtraDB

存儲引擎比較：

https://docs.oracle.com/cd/E17952_01/mysql-5.5-en/storage-

engines.html

?單進程，多線程

?諸多擴展和新特性

?提供了較多測試組件

?開源

安裝 Mariadb

?Mariadb安裝方式：

?1、源代碼：編譯安裝

?2、二進制格式的程序包：展開至特定路徑，并經過簡單配置后即可使用

?3、程序包管理器管理的程序包

?CentOS安裝光盤

?項目官方：

https://downloads.mariadb.org/mariadb/repositories/

[root@centos ~]# yum install mysql-server

/etc/rc.d/init.d/mysqld ? ? ?服務程序

/usr/libexec/mysqld ? ?二進制主程序

/var/lib/mysql ? ? ? ? ?用戶數據存放位置

[root@centos ~]# service mysqld start ? ? 監聽端口是3306

[root@centos ~]# ll !*

ll /var/lib/mysql

total 20488

-rw-rw—- 1 mysql mysql 10485760 Jun ?5 11:37 ibdata1

-rw-rw—- 1 mysql mysql ?5242880 Jun ?5 11:37 ib_logfile0

-rw-rw—- 1 mysql mysql ?5242880 Jun ?5 11:37 ib_logfile1

drwx—— 2 mysql mysql ? ? 4096 Jun ?5 11:37 mysql

srwxrwxrwx 1 mysql mysql ? ? ? ?0 Jun ?5 11:37 mysql.sock ? ? 本地連接

drwx—— 2 mysql mysql ? ? 4096 Jun ?5 11:37 test

[root@centos ~]# which mysql

/usr/bin/mysql

[root@centos ~]# rpm -qf /usr/bin/mysql

mysql-5.1.73-8.el6_8.x86_64

[root@centos ~]# mysql ? ? 可以直接連接數據庫

mysql> \h

? ? ? ? ? (\?) Synonym for `help’.

clear ? ? (\c) Clear the current input statement.

connect ? (\r) Reconnect to the server. Optional arguments are db and host.

delimiter (\d) Set statement delimiter.

edit ? ? ?(\e) Edit command with $EDITOR.

ego ? ? ? (\G) Send command to mysql server, display result vertically.

exit ? ? ?(\q) Exit mysql. Same as quit.

go ? ? ? ?(\g) Send command to mysql server.

help ? ? ?(\h) Display this help.

nopager ? (\n) Disable pager, print to stdout.

notee ? ? (\t) Don’t write into outfile.

pager ? ? (\P) Set PAGER [to_pager]. Print the query results via PAGER.

print ? ? (\p) Print current command.

prompt ? ?(\R) Change your mysql prompt.

quit ? ? ?(\q) Quit mysql.

rehash ? ?(\#) Rebuild completion hash.

source ? ?(\.) Execute an SQL script file. Takes a file name as an argument.

status ? ?(\s) Get status information from the server.

system ? ?(\!) Execute a system shell command.

tee ? ? ? (\T) Set outfile [to_outfile]. Append everything into given outfile.

use ? ? ? (\u) Use another database. Takes database name as argument.

charset ? (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.

warnings ?(\W) Show warnings after every statement.

nowarning (\w) Don’t show warnings after every statement.

mysql> show databases; ? ?必須加；

+——————–+

| Database ? ? ? ? ? |

+——————–+

| information_schema |

| mysql ? ? ? ? ? ? ?|

| test ? ? ? ? ? ? ? |

+——————–+

3 rows in set (0.00 sec)

mysql> \! hostname ? 執行linux命令

centos.localdomain

mysql> status

————–

mysql ?Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

Connection id: ?5

Current database:

Current user: ?root@localhost

SSL: ? Not in use

Current pager: ?stdout

Using outfile: ?”

Using delimiter: ;

Server version: ?5.1.73 Source distribution

Protocol version: 10

Connection: ?Localhost via UNIX socket

Server characterset: latin1

Db ? ? characterset: latin1

Client characterset: latin1

Conn. ?characterset: latin1

UNIX socket: ?/var/lib/mysql/mysql.sock

Uptime: ? 10 min 18 sec

Threads: 1 ?Questions: 12 ?Slow queries: 0 ?Opens: 15 ?Flush tables: 1 ?Open tables: 8 ?Queries per second avg: 0.19

mysql> use mysql ? ? ? ? ?切換數據庫

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

mysql> status

————–

mysql ?Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

Connection id: ?5

Current database: mysql

Current user: ?root@localhost ? ? mysql自身的用戶賬號

SSL: ? Not in use

Current pager: ?stdout

Using outfile: ?”

Using delimiter: ;

Server version: ?5.1.73 Source distribution

Protocol version: 10

Connection: ?Localhost via UNIX socket

Server characterset: latin1

Db ? ? characterset: latin1

Client characterset: latin1

Conn. ?characterset: latin1

UNIX socket: ?/var/lib/mysql/mysql.sock

Uptime: ? 11 min 59 sec

Threads: 1 ?Questions: 41 ?Slow queries: 0 ?Opens: 30 ?Flush tables: 1 ?Open tables: 23 ?Queries per second avg: 0.57

————–

[root@CENTOS7 ~]# yum install mariadb-server

[root@CENTOS7 ~]# systemctl start mariadb

[root@CENTOS7 ~]# ss -ntl

LISTEN ? ? 0 ? ? ?50 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?*:3306 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?*:*

[root@CENTOS7 ~]# pstree -p ? 多線程

├─mysqld_safe(2885)───mysqld(3047)─┬─{mysqld}(3051)

? ? ? ? ? ?│ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?├─{mysqld}(3052)

? ? ? ? ? ?│ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?├─{mysqld}(3053)

? ? ? ? ? ?│ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?├─{mysqld}(3054)

? ? ? ? ? ?│ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?├─{mysqld}(3055)

? ? ? ? ? ?│ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?├─{mysqld}(3056)

? ? ? ? ? ?│ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?├─{mysqld}(3057)

? ? ? ? ? ?│ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?├─{mysqld}(3058)

? ? ? ? ? ?│ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?├─{mysqld}(3059)

? ? ? ? ? ?│ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?├─{mysqld}(3060)

? ? ? ? ? ?│ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?├─{mysqld}(3061)

? ? ? ? ? ?│ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?├─{mysqld}(3062)

? ? ? ? ? ?│ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?├─{mysqld}(3063)

? ? ? ? ? ?│ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?├─{mysqld}(3064)

? ? ? ? ? ?│ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?├─{mysqld}(3065)

? ? ? ? ? ?│ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?├─{mysqld}(3066)

? ? ? ? ? ?│ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?├─{mysqld}(3074)

? ? ? ? ? ?│ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?└─{mysqld}(3075)

[root@CENTOS7 ~]# ll /var/lib/mysql ? ? ? ? ? ?啟動mariadb后自動生成的文件，所有的文件及文件夾都是masql所擁有

total 28700

-rw-rw—- 1 mysql mysql ? ?16384 Jun ?5 11:59 aria_log.00000001

-rw-rw—- 1 mysql mysql ? ? ? 52 Jun ?5 11:59 aria_log_control

-rw-rw—- 1 mysql mysql 18874368 Jun ?5 11:59 ibdata1

-rw-rw—- 1 mysql mysql ?5242880 Jun ?5 11:59 ib_logfile0

-rw-rw—- 1 mysql mysql ?5242880 Jun ?5 11:59 ib_logfile1

drwx—— 2 mysql mysql ? ? 4096 Jun ?5 11:59 mysql

srwxrwxrwx 1 mysql mysql ? ? ? ?0 Jun ?5 11:59 mysql.sock

drwx—— 2 mysql mysql ? ? 4096 Jun ?5 11:59 performance_schema

drwx—— 2 mysql mysql ? ? ? ?6 Jun ?5 11:59 test

mysql ? ? performance_schema ? ? ?test ? ?這3個文件夾對應的就是3個mysql數據庫

[root@CENTOS7 ~]# mysql

MariaDB [(none)]> show databases;

+——————–+

| Database ? ? ? ? ? |

+——————–+

| information_schema | ? ? ? ? ? ? ? ? ? ? ?內存中虛擬的數據庫

| mysql ? ? ? ? ? ? ?|

| performance_schema |

| test ? ? ? ? ? ? ? |

+——————–+

4 rows in set (0.00 sec)

[root@CENTOS7 mysql]# cp -r mysql mysqlcron

[root@CENTOS7 mysql]# ls

aria_log.00000001 ?ibdata1 ? ? ?ib_logfile1 ?mysqlcron ? performance_schema

aria_log_control ? ib_logfile0 ?mysql ? ? ? ?mysql.sock ?test

[root@CENTOS7 mysql]# mysql

Welcome to the MariaDB monitor. ?Commands end with ; or \g.

Your MariaDB connection id is 4

Server version: 5.5.56-MariaDB MariaDB Server

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]> show databases

? ? -> ;

+——————–+

| Database ? ? ? ? ? |

+——————–+

| information_schema |

| mysql ? ? ? ? ? ? ?|

| mysqlcron ? ? ? ? ?|

| performance_schema |

| test ? ? ? ? ? ? ? |

+——————–+

5 rows in set (0.00 sec)

如果想備份數據庫也可以備份文件夾，但是文件夾過大可能導致在備份的過程中使其中的文件發生改變，（備份的文件時間必須一致），在備份是可以停止數據庫服務后再備份。（不建議使用此方法）

[wang@CENTOS7 ~]$ mysql

MariaDB [mysqlcron]> use mysql

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

MariaDB [mysql]> show tables ? ?查看數據庫mysql內容

? ? -> ;

+—————————+

| Tables_in_mysql ? ? ? ? ? |

+—————————+

| columns_priv ? ? ? ? ? ? ?|

| db ? ? ? ? ? ? ? ? ? ? ? ?|

| event ? ? ? ? ? ? ? ? ? ? |

| func ? ? ? ? ? ? ? ? ? ? ?|

| general_log ? ? ? ? ? ? ? |

| help_category ? ? ? ? ? ? |

| help_keyword ? ? ? ? ? ? ?|

| help_relation ? ? ? ? ? ? |

| help_topic ? ? ? ? ? ? ? ?|

| host ? ? ? ? ? ? ? ? ? ? ?|

| ndb_binlog_index ? ? ? ? ?|

| plugin ? ? ? ? ? ? ? ? ? ?|

| proc ? ? ? ? ? ? ? ? ? ? ?|

| procs_priv ? ? ? ? ? ? ? ?|

| proxies_priv ? ? ? ? ? ? ?|

| servers ? ? ? ? ? ? ? ? ? |

| slow_log ? ? ? ? ? ? ? ? ?|

| tables_priv ? ? ? ? ? ? ? |

| time_zone ? ? ? ? ? ? ? ? |

| time_zone_leap_second ? ? |

| time_zone_name ? ? ? ? ? ?|

| time_zone_transition ? ? ?|

| time_zone_transition_type |

| user ? ? ? ? ? ? ? ? ? ? ?|

+—————————+

24 rows in set (0.00 sec)

MariaDB [mysql]> select * from user ? ?查看user表的內容

? ? -> ;

MariaDB [mysql]> select * from user\G ? ? ? ? ? ? ? ? ? 豎行顯示

*************************** 1. row ***************************

? ? ? ? ? ? ? ? ? Host: localhost

? ? ? ? ? ? ? ? ? User: root

? ? ? ? ? ? ? Password:

? ? ? ? ? ?Select_priv: Y

? ? ? ? ? ?Insert_priv: Y

? ? ? ? ? ?Update_priv: Y

? ? ? ? ? ?Delete_priv: Y

? ? ? ? ? ?Create_priv: Y

? ? ? ? ? ? ?Drop_priv: Y

? ? ? ? ? ?Reload_priv: Y

? ? ? ? ?Shutdown_priv: Y

? ? ? ? ? Process_priv: Y

? ? ? ? ? ? ?File_priv: Y

? ? ? ? ? ? Grant_priv: Y

? ? ? ?References_priv: Y

? ? ? ? ? ? Index_priv: Y

? ? ? ? ? ? Alter_priv: Y

? ? ? ? ? Show_db_priv: Y

? ? ? ? ? ? Super_priv: Y

?Create_tmp_table_priv: Y

? ? ? Lock_tables_priv: Y

? ? ? ? ? Execute_priv: Y

? ? ? ?Repl_slave_priv: Y

? ? ? Repl_client_priv: Y

? ? ? Create_view_priv: Y

? ? ? ? Show_view_priv: Y

? ?Create_routine_priv: Y

? ? Alter_routine_priv: Y

? ? ? Create_user_priv: Y

? ? ? ? ? ? Event_priv: Y

? ? ? ? ? Trigger_priv: Y

Create_tablespace_priv: Y

? ? ? ? ? ? ? ssl_type:

? ? ? ? ? ? ssl_cipher:

? ? ? ? ? ?x509_issuer:

? ? ? ? ? x509_subject:

? ? ? ? ?max_questions: 0

? ? ? ? ? ?max_updates: 0

? ? ? ?max_connections: 0

? max_user_connections: 0

? ? ? ? ? ? ? ? plugin:

?authentication_string:

*************************** 2. row ***************************

? ? ? ? ? ? ? ? ? Host: centos7.localdomain

? ? ? ? ? ? ? ? ? User: root

? ? ? ? ? ? ? Password:

? ? ? ? ? ?Select_priv: Y

? ? ? ? ? ?Insert_priv: Y

? ? ? ? ? ?Update_priv: Y

? ? ? ? ? ?Delete_priv: Y

? ? ? ? ? ?Create_priv: Y

? ? ? ? ? ? ?Drop_priv: Y

? ? ? ? ? ?Reload_priv: Y

? ? ? ? ?Shutdown_priv: Y

? ? ? ? ? Process_priv: Y

? ? ? ? ? ? ?File_priv: Y

? ? ? ? ? ? Grant_priv: Y

? ? ? ?References_priv: Y

? ? ? ? ? ? Index_priv: Y

? ? ? ? ? ? Alter_priv: Y

? ? ? ? ? Show_db_priv: Y

? ? ? ? ? ? Super_priv: Y

?Create_tmp_table_priv: Y

? ? ? Lock_tables_priv: Y

? ? ? ? ? Execute_priv: Y

? ? ? ?Repl_slave_priv: Y

? ? ? Repl_client_priv: Y

? ? ? Create_view_priv: Y

? ? ? ? Show_view_priv: Y

? ?Create_routine_priv: Y

? ? Alter_routine_priv: Y

? ? ? Create_user_priv: Y

? ? ? ? ? ? Event_priv: Y

? ? ? ? ? Trigger_priv: Y

Create_tablespace_priv: Y

? ? ? ? ? ? ? ssl_type:

? ? ? ? ? ? ssl_cipher:

? ? ? ? ? ?x509_issuer:

? ? ? ? ? x509_subject:

? ? ? ? ?max_questions: 0

? ? ? ? ? ?max_updates: 0

? ? ? ?max_connections: 0

? max_user_connections: 0

? ? ? ? ? ? ? ? plugin:

?authentication_string:

*************************** 3. row ***************************

? ? ? ? ? ? ? ? ? Host: 127.0.0.1

? ? ? ? ? ? ? ? ? User: root

? ? ? ? ? ? ? Password:

? ? ? ? ? ?Select_priv: Y

? ? ? ? ? ?Insert_priv: Y

? ? ? ? ? ?Update_priv: Y

? ? ? ? ? ?Delete_priv: Y

? ? ? ? ? ?Create_priv: Y

? ? ? ? ? ? ?Drop_priv: Y

? ? ? ? ? ?Reload_priv: Y

? ? ? ? ?Shutdown_priv: Y

? ? ? ? ? Process_priv: Y

? ? ? ? ? ? ?File_priv: Y

? ? ? ? ? ? Grant_priv: Y

? ? ? ?References_priv: Y

? ? ? ? ? ? Index_priv: Y

? ? ? ? ? ? Alter_priv: Y

? ? ? ? ? Show_db_priv: Y

? ? ? ? ? ? Super_priv: Y

?Create_tmp_table_priv: Y

? ? ? Lock_tables_priv: Y

? ? ? ? ? Execute_priv: Y

? ? ? ?Repl_slave_priv: Y

? ? ? Repl_client_priv: Y

? ? ? Create_view_priv: Y

? ? ? ? Show_view_priv: Y

? ?Create_routine_priv: Y

? ? Alter_routine_priv: Y

? ? ? Create_user_priv: Y

? ? ? ? ? ? Event_priv: Y

? ? ? ? ? Trigger_priv: Y

Create_tablespace_priv: Y

? ? ? ? ? ? ? ssl_type:

? ? ? ? ? ? ssl_cipher:

? ? ? ? ? ?x509_issuer:

? ? ? ? ? x509_subject:

? ? ? ? ?max_questions: 0

? ? ? ? ? ?max_updates: 0

? ? ? ?max_connections: 0

? max_user_connections: 0

? ? ? ? ? ? ? ? plugin:

?authentication_string:

*************************** 4. row ***************************

? ? ? ? ? ? ? ? ? Host: ::1

? ? ? ? ? ? ? ? ? User: root

? ? ? ? ? ? ? Password:

? ? ? ? ? ?Select_priv: Y

? ? ? ? ? ?Insert_priv: Y

? ? ? ? ? ?Update_priv: Y

? ? ? ? ? ?Delete_priv: Y

? ? ? ? ? ?Create_priv: Y

? ? ? ? ? ? ?Drop_priv: Y

? ? ? ? ? ?Reload_priv: Y

? ? ? ? ?Shutdown_priv: Y

? ? ? ? ? Process_priv: Y

? ? ? ? ? ? ?File_priv: Y

? ? ? ? ? ? Grant_priv: Y

? ? ? ?References_priv: Y

? ? ? ? ? ? Index_priv: Y

? ? ? ? ? ? Alter_priv: Y

? ? ? ? ? Show_db_priv: Y

? ? ? ? ? ? Super_priv: Y

?Create_tmp_table_priv: Y

? ? ? Lock_tables_priv: Y

? ? ? ? ? Execute_priv: Y

? ? ? ?Repl_slave_priv: Y

? ? ? Repl_client_priv: Y

? ? ? Create_view_priv: Y

? ? ? ? Show_view_priv: Y

? ?Create_routine_priv: Y

? ? Alter_routine_priv: Y

? ? ? Create_user_priv: Y

? ? ? ? ? ? Event_priv: Y

? ? ? ? ? Trigger_priv: Y

Create_tablespace_priv: Y

? ? ? ? ? ? ? ssl_type:

? ? ? ? ? ? ssl_cipher:

? ? ? ? ? ?x509_issuer:

? ? ? ? ? x509_subject:

? ? ? ? ?max_questions: 0

? ? ? ? ? ?max_updates: 0

? ? ? ?max_connections: 0

? max_user_connections: 0

? ? ? ? ? ? ? ? plugin:

?authentication_string:

*************************** 5. row ***************************

? ? ? ? ? ? ? ? ? Host: localhost

? ? ? ? ? ? ? ? ? User:

? ? ? ? ? ? ? Password:

? ? ? ? ? ?Select_priv: N

? ? ? ? ? ?Insert_priv: N

? ? ? ? ? ?Update_priv: N

? ? ? ? ? ?Delete_priv: N

? ? ? ? ? ?Create_priv: N

? ? ? ? ? ? ?Drop_priv: N

? ? ? ? ? ?Reload_priv: N

? ? ? ? ?Shutdown_priv: N

? ? ? ? ? Process_priv: N

? ? ? ? ? ? ?File_priv: N

? ? ? ? ? ? Grant_priv: N

? ? ? ?References_priv: N

? ? ? ? ? ? Index_priv: N

? ? ? ? ? ? Alter_priv: N

? ? ? ? ? Show_db_priv: N

? ? ? ? ? ? Super_priv: N

?Create_tmp_table_priv: N

? ? ? Lock_tables_priv: N

? ? ? ? ? Execute_priv: N

? ? ? ?Repl_slave_priv: N

? ? ? Repl_client_priv: N

? ? ? Create_view_priv: N

? ? ? ? Show_view_priv: N

? ?Create_routine_priv: N

? ? Alter_routine_priv: N

? ? ? Create_user_priv: N

? ? ? ? ? ? Event_priv: N

? ? ? ? ? Trigger_priv: N

Create_tablespace_priv: N

? ? ? ? ? ? ? ssl_type:

? ? ? ? ? ? ssl_cipher:

? ? ? ? ? ?x509_issuer:

? ? ? ? ? x509_subject:

? ? ? ? ?max_questions: 0

? ? ? ? ? ?max_updates: 0

? ? ? ?max_connections: 0

? max_user_connections: 0

? ? ? ? ? ? ? ? plugin:

?authentication_string:

*************************** 6. row ***************************

? ? ? ? ? ? ? ? ? Host: centos7.localdomain

? ? ? ? ? ? ? ? ? User:

? ? ? ? ? ? ? Password:

? ? ? ? ? ?Select_priv: N

? ? ? ? ? ?Insert_priv: N

? ? ? ? ? ?Update_priv: N

? ? ? ? ? ?Delete_priv: N

? ? ? ? ? ?Create_priv: N

? ? ? ? ? ? ?Drop_priv: N

? ? ? ? ? ?Reload_priv: N

? ? ? ? ?Shutdown_priv: N

? ? ? ? ? Process_priv: N

? ? ? ? ? ? ?File_priv: N

? ? ? ? ? ? Grant_priv: N

? ? ? ?References_priv: N

? ? ? ? ? ? Index_priv: N

? ? ? ? ? ? Alter_priv: N

? ? ? ? ? Show_db_priv: N

? ? ? ? ? ? Super_priv: N

?Create_tmp_table_priv: N

? ? ? Lock_tables_priv: N

? ? ? ? ? Execute_priv: N

? ? ? ?Repl_slave_priv: N

? ? ? Repl_client_priv: N

? ? ? Create_view_priv: N

? ? ? ? Show_view_priv: N

? ?Create_routine_priv: N

? ? Alter_routine_priv: N

? ? ? Create_user_priv: N

? ? ? ? ? ? Event_priv: N

? ? ? ? ? Trigger_priv: N

Create_tablespace_priv: N

? ? ? ? ? ? ? ssl_type:

? ? ? ? ? ? ssl_cipher:

? ? ? ? ? ?x509_issuer:

? ? ? ? ? x509_subject:

? ? ? ? ?max_questions: 0

? ? ? ? ? ?max_updates: 0

? ? ? ?max_connections: 0

? max_user_connections: 0

? ? ? ? ? ? ? ? plugin:

?authentication_string:

6 rows in set (0.00 sec)

由于任何人都可以隨意的訪問數據庫所以不安全

[root@CENTOS7 mysql]# chown mysql.mysql mysqlcron ? ? ?修改文件或文件夾的所屬組和所屬者

MariaDB [(none)]> select user();

+—————-+

| user() ? ? ? ? |

+—————-+

| root@localhost |

+—————-+

1 row in set (0.00 sec)

MariaDB [mysql]> select user,password,host from user;

+——+———-+———————+

| user | password | host ? ? ? ? ? ? ? ?|

+——+———-+———————+

| root | ? ? ? ? ?| localhost ? ? ? ? ? |

| root | ? ? ? ? ?| centos7.localdomain |

| root | ? ? ? ? ?| 127.0.0.1 ? ? ? ? ? |

| root | ? ? ? ? ?| ::1 ? ? ? ? ? ? ? ? |

| ? ? ?| ? ? ? ? ?| localhost ? ? ? ? ? |

| ? ? ?| ? ? ? ? ?| centos7.localdomain |

+——+———-+———————+

6 rows in set (0.00 sec)

Rpm安裝mariadb

?安裝和使用MariaDB

?Rpm包安裝

CentOS 7：安裝光盤直接提供

mariadb-server 服務器包

mariadb ?客戶端工具包

CentOS 6

?提高安全性

mysql_secure_installation

?設置數據庫管理員root口令

?禁止root遠程登錄

?刪除anonymous用戶帳號

?刪除test數據庫

設置口令

[wang@CENTOS7 ~]$ mysql_secure_installation

[wang@CENTOS7 ~]$ mysql

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)

[wang@CENTOS7 ~]$ mysql -uroot -p

Enter password:

Welcome to the MariaDB monitor. ?Commands end with ; or \g.

Your MariaDB connection id is 17

Server version: 5.5.56-MariaDB MariaDB Server

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]>

MariaDB [(none)]> select user();

+—————-+

| user() ? ? ? ? |

+—————-+

| root@localhost |

+—————-+

1 row in set (0.00 sec)

MariaDB [mysql]> select user,password,host from user;

+——+——————————————-+———————+

| user | password ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?| host ? ? ? ? ? ? ? ?|

+——+——————————————-+———————+

| root | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 | localhost ? ? ? ? ? |

| root | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 | centos7.localdomain |

| root | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 | 127.0.0.1 ? ? ? ? ? |

| root | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 | ::1 ? ? ? ? ? ? ? ? |

+——+——————————————-+———————+

4 rows in set (0.00 sec)

這是4個不同的賬號賬號分別是root localhost

[root@centos ~]# mysql -uroot -pmagedu -h192.168.30.100

ERROR 1130 (HY000): Host ‘192.168.30.99’ is not allowed to connect to this MariaDB server

MariaDB程序

? 客戶端程序：

mysql: 交互式的CLI工具

mysqldump: 備份工具，基于mysql協議向mysqld發起查詢請求，并將查得的所

有數據轉換成insert等寫操作語句保存文本文件中

mysqladmin：基于mysql協議管理mysqld

mysqlimport: 數據導入工具

? MyISAM存儲引擎的管理工具：

myisamchk：檢查MyISAM庫

myisampack：打包MyISAM表，只讀

?服務器端程序

mysqld_safe

mysqld 獲取默認設置： mysqld –print-defaults

mysqld_multi：多實例，示例：mysqld_multi –example ?就是一個服務器上跑多份，一個mysql就是一個實例

[root@CENTOS7 ~]# getent passwd mysql ? ? 賬號中設置的家目錄就是數據庫存放的路徑

mysql:x:27:27:MariaDB Server:/var/lib/mysql:/sbin/nologin

安裝mariadb-server時自動跑的安裝前腳本

[root@CENTOS7 ~]# rpm -q –scripts mariadb-server

preinstall scriptlet (using /bin/sh):

/usr/sbin/groupadd -g 27 -o -r mysql >/dev/null 2>&1 || :

/usr/sbin/useradd -M -N -g mysql -o -r -d /var/lib/mysql -s /sbin/nologin \

-c “MariaDB Server” -u 27 mysql >/dev/null 2>&1 || :

useradd -o ? 可以忽略id號唯一性

多實例如圖

用戶賬號

?mysql用戶賬號由兩部分組成：

‘USERNAME’@’HOST‘

?說明：

HOST限制此用戶可通過哪些遠程主機連接mysql服務器

支持使用通配符：

% 匹配任意長度的任意字符

172.16.0.0/255.255.0.0 或 172.16.%.%

_ 匹配任意單個字符

Mysql 客戶端

? mysql使用模式：

? 交互式模式：

可運行命令有兩類：

客戶端命令：

\h, help

\u，use

\s，status

\!，system

服務器端命令：

SQL, 需要語句結束符；

? 腳本模式：

mysql –uUSERNAME -pPASSWORD < /path/somefile.sql

mysql> source /path/from/somefile.sql

MariaDB [(none)]> create database testds ? ? ?創建一個數據庫

? ? -> ;

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;

+——————–+

| Database ? ? ? ? ? |

+——————–+

| information_schema |

| mysql ? ? ? ? ? ? ?|

| mysqlcron ? ? ? ? ?|

| performance_schema |

| test ? ? ? ? ? ? ? |

| testds ? ? ? ? ? ? |

+——————–+

6 rows in set (0.00 sec)

[root@CENTOS7 ~]# cat test.sql

create database testdb2;

show databases;

[root@CENTOS7 ~]# mysql -uroot -pmagedu < test.sql

Database

information_schema

mysql

mysqlcron

performance_schema

test

testdb2

testds

MariaDB [(none)]> drop database testdb2; ? ?刪除

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> sorce test.sql

? ? -> ;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘sorce test.sql’ at line 1

MariaDB [(none)]> source test.sql

Query OK, 1 row affected (0.00 sec)

+——————–+

| Database ? ? ? ? ? |

+——————–+

| information_schema |

| mysql ? ? ? ? ? ? ?|

| mysqlcron ? ? ? ? ?|

| performance_schema |

| testdb2 ? ? ? ? ? ?|

+——————–+

5 rows in set (0.00 sec)

服務器端命令需要加；客戶端命令不需要加；

prompt ? ?(\R) Change your mysql prompt.修改mysql的提示符

MariaDB [(none)]> prompt \u@[\D] \r:\m:\s->

PROMPT set to ‘\u@[\D] \r:\m:\s->’

root@[Tue Jun ?5 14:39:13 2018] 02:39:13->

[root@CENTOS7 ~]# vim /etc/my.cnf.d/mysql-clients.cnf

# These groups are read by MariaDB command-line tools

# Use it for options that affect only one utility

[mysql]

# 在此可以添加修改提示符的信息

3中修改提示符的方式

Use an environment variable. ?You can set the MYSQL_PS1 environment variable to a prompt string. For example:

? ? ? ? ? ? ? ?shell> export MYSQL_PS1=”(\u@\h) [\d]> “

? ? ? ?? ? Use a command-line option. ?You can set the –prompt option on the command line to mysql. For example:

? ? ? ? ? ? ? ?shell> mysql –prompt=”(\u@\h) [\d]> “

? ? ? ? ? ? ? ?(user@host) [database]>

? ? ? ?? ? Use an option file. ?You can set the prompt option in the [mysql] group of any MySQL option file, such as /etc/my.cnf or the .my.cnf

? ? ? ? ? ?file in your home directory. For example:

? ? ? ? ? ? ? ?[mysql]

? ? ? ? ? ? ? ?prompt=(\\u@\\h) [\\d]>\\_

Mysql客戶端

?mysql客戶端可用選項：

-A, –no-auto-rehash 禁止補全

-u, –user= ?用戶名,默認為root

-h, –host= ?服務器主機,默認為localhost

-p, –passowrd= ?用戶密碼,建議使用-p,默認為空密碼

-P, –port= ?服務器端口

-S, –socket= 指定連接socket文件路徑

-D, –database= 指定默認數據庫

-C, –compress ?啟用壓縮

-e “SQL“ 執行SQL命令

-V, –version ?顯示版本

-v –verbose 顯示詳細信息

–print-defaults 獲取程序默認使用的配置

[root@CENTOS7 ~]# mysql -uroot -pmagedu -e “show databases;”

+——————–+

| Database ? ? ? ? ? |

+——————–+

| information_schema |

| mysql ? ? ? ? ? ? ?|

| mysqlcron ? ? ? ? ?|

| performance_schema |

| testdb2 ? ? ? ? ? ?|

+——————–+

[root@CENTOS7 ~]# mysql -uroot -pmagedu -e “drop database testdb2;show databases;”

+——————–+

| Database ? ? ? ? ? |

+——————–+

| information_schema |

| mysql ? ? ? ? ? ? ?|

| mysqlcron ? ? ? ? ?|

| performance_schema |

+——————–+

socket地址

?服務器監聽的兩種socket地址：

ip socket: 監聽在tcp的3306端口，支持遠程通信

unix sock: 監聽在sock文件上，僅支持本機通信

如：/var/lib/mysql/mysql.sock)

說明：host為localhost,127.0.0.1時自動使用unix sock

本機是 socket通訊遠程tcp通訊

執行命令

? 運行mysql命令：默認空密碼登錄

mysql>use mysql

mysql>select user();查看當前用戶

mysql>SELECT User,Host,Password FROM user;

? 登錄系統：mysql –uroot –p

? 客戶端命令：本地執行

mysql> help

每個命令都完整形式和簡寫格式

mysql> status 或 \s

? 服務端命令：通過mysql協議發往服務器執行并取回結果

每個命令都必須命令結束符號；默認為分號

SELECT VERSION();

服務器端配置

? 服務器端(mysqld)：工作特性有多種配置方式

?1、命令行選項：

?2、配置文件：類ini格式

集中式的配置，能夠為mysql的各應用程序提供配置信息

[mysqld]

[mysqld_safe]

[mysqld_multi]

[mysql]

[mysqldump]

[server]

[client]

格式：parameter = value

說明：_和- 相同

0，OFF，FALSE意義相同，1，ON，TRUE意義相同

服務器端配置

這些都是mysql的配置文件

[root@CENTOS7 ~]# cd /etc/my.cnf.d/

[root@CENTOS7 my.cnf.d]# ls

client.cnf ?mysql-clients.cnf ?server.cnf

[root@CENTOS7 ~]# 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 mariadb according to the

# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]

log-error=/var/log/mariadb/mariadb.log

pid-file=/var/run/mariadb/mariadb.pid

# include all files from the config directory

!includedir /etc/my.cnf.d

配置文件

?配置文件：

后面覆蓋前面的配置文件，順序如下：

? /etc/my.cnf Global選項

? /etc/mysql/my.cnf ?Global全局選項

? SYSCONFDIR/my.cnf ?Global選項

? $MYSQL_HOME/my.cnf Server-specific 選項

? –defaults-extra-file=path

? ~/.my.cnf User-specific 選項

? 獲取可用參數列表：

mysqld –help –verbose

查看服務器默認配置

[root@CENTOS7 ~]# /usr/libexec/mysqld –print-defaults

/usr/libexec/mysqld would have been started with the following arguments:

–datadir=/var/lib/mysql –socket=/var/lib/mysql/mysql.sock –symbolic-links=0

MairaDB配置

偵聽3306/tcp端口可以在綁定有一個或全部接口IP上

?vim /etc/my.cnf

[mysqld]

skip-networking=1 關閉網絡連接，只偵聽本地客戶端，所有和服務器的交互

都通過一個socket實現，socket的配置存放在/var/lib/mysql/mysql.sock）可在

/etc/my.cnf修改

可以在維護時使用，臨時禁用遠程連接

在網站https://downloads.mariadb.org/mariadb/repositories/#mirror=tuna&distro=CentOS&distro_release=centos7-amd64–centos7&version=10.2中復制yum配置路徑

[root@CENTOS7 ~]# vim /etc/yum.repos.d/base.repo

[mariadb]

name = MariaDB

baseurl = http://yum.mariadb.org/10.2/centos7-amd64

gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

gpgcheck=1

通用二進制格式安裝過程

?二進制格式安裝過程

?(1) 準備用戶

?groupadd -r -g 306 mysql

?useradd -r -g 306 -u 306 –m –d /app/data mysql

?(2) 準備數據目錄

以/app/data為例,建議使用邏輯卷

?chown mysql:mysql /app/data

?(3) 準備二進制程序

?tar xf mariadb-VERSION-linux-x86_64.tar.gz -C /usr/local

?cd /usr/local;ln -sv mariadb-VERSION mysql

?chown -R root:mysql /usr/local/mysql/

通用二進制格式安裝過程

? (4) 準備配置文件

mkdir /etc/mysql/

cp support-files/my-large.cnf /etc/mysql/my.cnf

[mysqld]中添加三個選項：

datadir = /app/data

innodb_file_per_table = on

skip_name_resolve = on 禁止主機名解析，建議使用

通用二進制格式安裝過程

? (5)創建數據庫文件

cd /usr/local/mysql/

./scripts/mysql_install_db –datadir=/app/data –user=mysql

? (6)準備日志文件

touch /var/log/mysqld.log

chown mysqld /var/log/mysqld.log

? (7)準備服務腳本，并啟動服務

cp ./support-files/mysql.server /etc/rc.d/init.d/mysqld

chkconfig –add mysqld

service mysqld start

? (8)安全初始化

/user/local/mysql/bin/mysql_secure_installation

在centos7上用二進制安裝10.2.15版本的mariadb

1.檢查環境

? ?iptables

?selinux

有沒有安裝相關的mariadb的別的版本

用戶是否創建

下載二進制包

rz 將二進制包導入/root/下

創建mysql賬號。

[root@CENTOS7 ~]# useradd -r -d /data/mysqldb -s /sbin/nologin mysql

[root@CENTOS7 ~]# getent passwd mysql

mysql:x:991:986::/data/mysqldb:/sbin/nologin

[root@CENTOS7 ~]# id mysql

uid=991(mysql) gid=986(mysql) groups=986(mysql)

4.創建安裝路徑

tar -xvf mariadb-10.2.15-linux-x86_64.tar.gz -C /usr/local/

[root@CENTOS7 local]# ls

bin ?etc ?games ?include ?lib ?lib64 ?libexec ?mariadb-10.2.15-linux-x86_64 ?sbin ?share ?src

[root@CENTOS7 local]# ln -s mariadb-10.2.15-linux-x86_64/ mysql

[root@CENTOS7 local]# ll

total 0

drwxr-xr-x. ?2 root root ? 6 Nov ?5 ?2016 bin

drwxr-xr-x. ?2 root root ? 6 Nov ?5 ?2016 etc

drwxr-xr-x. ?2 root root ? 6 Nov ?5 ?2016 games

drwxr-xr-x. ?2 root root ? 6 Nov ?5 ?2016 include

drwxr-xr-x. ?2 root root ? 6 Nov ?5 ?2016 lib

drwxr-xr-x. ?2 root root ? 6 Nov ?5 ?2016 lib64

drwxr-xr-x. ?2 root root ? 6 Nov ?5 ?2016 libexec

drwxr-xr-x ?12 root root 290 Jun ?5 16:13 mariadb-10.2.15-linux-x86_64

lrwxrwxrwx ? 1 root root ?29 Jun ?5 16:15 mysql -> mariadb-10.2.15-linux-x86_64/

[root@CENTOS7 local]# ll mysql/ ? ? ?其內的文件所屬組所有者有問題

total 176

drwxr-xr-x ?2 root root ?4096 Jun ?5 16:13 bin

-rw-r–r– ?1 ?500 ?500 17987 May 17 05:31 COPYING

-rw-r–r– ?1 ?500 ?500 86263 May 17 05:31 COPYING.thirdparty

-rw-r–r– ?1 ?500 ?500 ?2268 May 17 05:31 CREDITS

drwxr-xr-x ?3 root root ? ?18 Jun ?5 16:13 data

-rw-r–r– ?1 ?500 ?500 ?8245 May 17 05:31 EXCEPTIONS-CLIENT

drwxr-xr-x ?3 root root ? ?19 Jun ?5 16:12 include

-rw-r–r– ?1 ?500 ?500 ?8694 May 17 05:31 INSTALL-BINARY

drwxr-xr-x ?3 root root ? 280 Jun ?5 16:12 lib

drwxr-xr-x ?4 root root ? ?30 Jun ?5 16:13 man

drwxr-xr-x 11 root root ?4096 Jun ?5 16:13 mysql-test

-rw-r–r– ?1 ?500 ?500 ?2374 May 17 05:31 README.md

-rw-r–r– ?1 ?500 ?500 19510 May 17 05:31 README-wsrep

drwxr-xr-x ?2 root root ? ?30 Jun ?5 16:13 scripts

drwxr-xr-x 32 root root ?4096 Jun ?5 16:13 share

drwxr-xr-x ?4 root root ?4096 Jun ?5 16:13 sql-bench

drwxr-xr-x ?3 root root ? 275 Jun ?5 16:13 support-files

[root@CENTOS7 local]# chown -R root: mysql/

[root@CENTOS7 local]# ll

total 0

drwxr-xr-x. ?2 root root ? 6 Nov ?5 ?2016 bin

drwxr-xr-x. ?2 root root ? 6 Nov ?5 ?2016 etc

drwxr-xr-x. ?2 root root ? 6 Nov ?5 ?2016 games

drwxr-xr-x. ?2 root root ? 6 Nov ?5 ?2016 include

drwxr-xr-x. ?2 root root ? 6 Nov ?5 ?2016 lib

drwxr-xr-x. ?2 root root ? 6 Nov ?5 ?2016 lib64

drwxr-xr-x. ?2 root root ? 6 Nov ?5 ?2016 libexec

drwxr-xr-x ?12 root root 290 Jun ?5 16:13 mariadb-10.2.15-linux-x86_64

lrwxrwxrwx ? 1 root root ?29 Jun ?5 16:15 mysql -> mariadb-10.2.15-linux-x86_64/

drwxr-xr-x. ?2 root root ? 6 Nov ?5 ?2016 sbin

drwxr-xr-x. ?5 root root ?49 Mar 27 17:54 share

drwxr-xr-x. ?2 root root ? 6 Nov ?5 ?2016 src

[root@CENTOS7 bin]# ls /usr/local/mysql/bin

aria_chk ? ? ? ?myisamchk ? ? ? ? ?mysql_client_test ? ? ? ? ? mysql_embedded ? ? ? ? ? ? mysqltest ? ? ? ? ? ?wsrep_sst_common

aria_dump_log ? myisam_ftdump ? ? ?mysql_client_test_embedded ?mysql_find_rows ? ? ? ? ? ?mysqltest_embedded ? wsrep_sst_mariabackup

aria_ftdump ? ? myisamlog ? ? ? ? ?mysql_config ? ? ? ? ? ? ? ?mysql_fix_extensions ? ? ? mysql_tzinfo_to_sql ?wsrep_sst_mysqldump

aria_pack ? ? ? myisampack ? ? ? ? mysql_convert_table_format ?mysqlhotcopy ? ? ? ? ? ? ? mysql_upgrade ? ? ? ?wsrep_sst_rsync

aria_read_log ? my_print_defaults ?mysqld ? ? ? ? ? ? ? ? ? ? ?mysqlimport ? ? ? ? ? ? ? ?mysql_waitpid ? ? ? ?wsrep_sst_xtrabackup

innochecksum ? ?mysql ? ? ? ? ? ? ?mysqld_multi ? ? ? ? ? ? ? ?mysql_plugin ? ? ? ? ? ? ? mytop ? ? ? ? ? ? ? ?wsrep_sst_xtrabackup-v2

mariabackup ? ? mysqlaccess ? ? ? ?mysqld_safe ? ? ? ? ? ? ? ? mysql_secure_installation ?perror

mariadb_config ?mysqladmin ? ? ? ? mysqld_safe_helper ? ? ? ? ?mysql_setpermission ? ? ? ?replace

mbstream ? ? ? ?mysqlbinlog ? ? ? ?mysqldump ? ? ? ? ? ? ? ? ? mysqlshow ? ? ? ? ? ? ? ? ?resolveip

msql2mysql ? ? ?mysqlcheck ? ? ? ? mysqldumpslow ? ? ? ? ? ? ? mysqlslap ? ? ? ? ? ? ? ? ?resolve_stack_dump

[root@CENTOS7 bin]# echo PATH=/usr/local/mysql/bin:$PATH > /etc/profile.d/mysql.sh

[root@CENTOS7 bin]# . /etc/profile.d/mysql.sh

[root@CENTOS7 bin]# echo $PATH

/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

添加一個新的硬盤劃分成邏輯卷用于存放用戶數據

[root@CENTOS7 bin]# echo ” – – – ” > /sys/class/scsi_host/host2/scan

[root@CENTOS7 bin]# pvcreate /dev/sdb

? Physical volume “/dev/sdb” successfully created.

[root@CENTOS7 bin]# vgcreate vg0 /dev/sdb

? Volume group “vg0” successfully created

[root@CENTOS7 bin]# lvcreate -n lv_mysql -l 100%FREE vg0

? Logical volume “lv_mysql” created.

[root@CENTOS7 bin]# lvs

? LV ? ? ? VG ?Attr ? ? ? LSize ? ?Pool Origin Data% ?Meta% ?Move Log Cpy%Sync Convert

? lv_mysql vg0 -wi-a—– <200.00g

[root@CENTOS7 bin]# mkfs.xfs /dev/vg0/lv_mysql

meta-data=/dev/vg0/lv_mysql ? ? ?isize=512 ? ?agcount=4, agsize=13106944 blks

? ? ? ? ?= ? ? ? ? ? ? ? ? ? ? ? sectsz=512 ? attr=2, projid32bit=1

? ? ? ? ?= ? ? ? ? ? ? ? ? ? ? ? crc=1 ? ? ? ?finobt=0, sparse=0

data ? ? = ? ? ? ? ? ? ? ? ? ? ? bsize=4096 ? blocks=52427776, imaxpct=25

? ? ? ? ?= ? ? ? ? ? ? ? ? ? ? ? sunit=0 ? ? ?swidth=0 blks

naming ? =version 2 ? ? ? ? ? ? ?bsize=4096 ? ascii-ci=0 ftype=1

log ? ? ?=internal log ? ? ? ? ? bsize=4096 ? blocks=25599, version=2

? ? ? ? ?= ? ? ? ? ? ? ? ? ? ? ? sectsz=512 ? sunit=0 blks, lazy-count=1

realtime =none ? ? ? ? ? ? ? ? ? extsz=4096 ? blocks=0, rtextents=0

[root@CENTOS7 bin]# blkid

/dev/sdb: UUID=”mEFJk7-R0yE-lVHd-tlcc-uMGX-f0K6-Nh4BNj” TYPE=”LVM2_member”

[root@CENTOS7 ~]# vim /etc/fstab

UUID=758a4a10-ea77-4b17-8126-14914075c86c /app ? ? ? ? ? ? ? ? ?xfs ? ? defaults ? ? ? ?0 0

[root@CENTOS7 ~]# mount -a

[root@CENTOS7 ~]# df -h

/dev/mapper/vg0-lv_mysql ?200G ? 33M ?200G ? 1% /app

[root@CENTOS7 ~]# mkdir /app/mysqldb

[root@CENTOS7 ~]# ll -d /app/mysqldb

drwxr-xr-x 2 root root 6 Jun ?5 16:40 /app/mysqldb

[root@CENTOS7 ~]# chown mysql.mysql /app/mysqldb

[root@CENTOS7 ~]# ll -d /app/mysqldb

drwxr-xr-x 2 mysql mysql 6 Jun ?5 16:40 /app/mysqldb

[root@CENTOS7 ~]# chmod 770 /app/mysqldb

[root@CENTOS7 ~]# ll -d /app/mysqldb

drwxrwx— 2 mysql mysql 6 Jun ?5 16:40 /app/mysqldb

[root@CENTOS7 ~]# cd /usr/local/mysql

[root@CENTOS7 mysql]# ls

bin ? ? ?COPYING.thirdparty ?data ? ? ? ? ? ? ? include ? ? ? ? lib ?mysql-test ?README-wsrep ?share ? ? ?support-files

COPYING ?CREDITS ? ? ? ? ? ? EXCEPTIONS-CLIENT ?INSTALL-BINARY ?man ?README.md ? scripts ? ? ? sql-bench

[root@CENTOS7 scripts]# ls

mysql_install_db

這個程序必須在這個目錄下執行

[root@CENTOS7 mysql]# pwd

/usr/local/mysql

執行這個腳本生成用戶數據庫基本文件

[root@CENTOS7 mysql]# scripts/mysql_install_db –datadir=/app/mysqldb –user=mysql

[root@CENTOS7 mysql]# ll /app/mysqldb

total 110620

-rw-rw—- 1 mysql mysql ? ?16384 Jun ?5 16:49 aria_log.00000001

-rw-rw—- 1 mysql mysql ? ? ? 52 Jun ?5 16:49 aria_log_control

-rw-rw—- 1 mysql mysql ? ? ?938 Jun ?5 16:49 ib_buffer_pool

-rw-rw—- 1 mysql mysql 12582912 Jun ?5 16:49 ibdata1

-rw-rw—- 1 mysql mysql 50331648 Jun ?5 16:49 ib_logfile0

-rw-rw—- 1 mysql mysql 50331648 Jun ?5 16:49 ib_logfile1

drwx—— 2 mysql root ? ? ?4096 Jun ?5 16:49 mysql

drwx—— 2 mysql mysql ? ? ? 20 Jun ?5 16:49 performance_schema

drwx—— 2 mysql root ? ? ? ? 6 Jun ?5 16:49 test

配置配置文件

[root@CENTOS7 mysql]# cd support-files/

[root@CENTOS7 support-files]# ls

binary-configure ?my-huge.cnf ? ? ? ? ? ? my-large.cnf ? my-small.cnf ? ? ? ? mysql-log-rotate ?policy ? ? wsrep_notify

magic ? ? ? ? ? ? my-innodb-heavy-4G.cnf ?my-medium.cnf ?mysqld_multi.server ?mysql.server ? ? ?wsrep.cnf

[root@CENTOS7 support-files]# cp /etc/my.cnf{,.bak}

[root@CENTOS7 support-files]# cp my-huge.cnf /etc/my.cnf

[root@CENTOS7 support-files]# vim /etc/my.cnf

datadir ? ? ? ? =/app/mysqldb

[root@CENTOS7 support-files]# cp mysql.server /etc/init.d/mysqld

[root@CENTOS7 support-files]# chkconfig –list

[root@CENTOS7 support-files]# chkconfig –add mysqld

[root@CENTOS7 support-files]# chkconfig –list

[root@CENTOS7 support-files]# service mysqld start

Starting mysqld (via systemctl): ? ? ? ? ? ? ? ? ? ? ? ? ? [ ?OK ?]

[root@CENTOS7 ~]# /usr/local/mysql/bin/mysqld –print-defaults

/usr/local/mysql/bin/mysqld would have been started with the following arguments:

–port=3306 –datadir=/app/mysqldb –socket=/tmp/mysql.sock –skip-external-locking –key_buffer_size=384M –max_allowed_packet=1M –table_open_cache=512 –sort_buffer_size=2M –read_buffer_size=2M –read_rnd_buffer_size=8M –myisam_sort_buffer_size=64M –thread_cache_size=8 –query_cache_size=32M –thread_concurrency=8 –log-bin=mysql-bin –server-id=1

實驗：centos7.4二進制安裝mariadb-10.2.15-linux-x86_64.tar.gz

1 檢查環境

iptables

selinux

mariadb-server

2 下載二進制包

3 useradd -r -d /data/mysqldb -s /sbin/nologin mysql

4 tar xvf ?mariadb-10.2.15-linux-x86_64.tar.gz ?-C /usr/local/

?cd /usr/local

?ln -s mariadb-10.2.15-linux-x86_64/ mysql

?chown -R root:root mysql/

5 echo PATH=/usr/local/mysql/bin:$PATH > /etc/profile.d/mysql.sh

. /etc/profile.d/mysql.sh

6 mkdir -pv /data/mysqldb

chown mysql.mysql /data/mysqldb

chmod 770 /data/mysqldb

cd /usr/local/mysql

scripts/mysql_install_db ?–datadir=/data/mysqldb –user=mysql

cd /usr/local/mysql

cp support-files/my-huge.cnf ?/etc/my.cnf

vim /etc/my.cnf

[mysqld]

datadir=/data/mysqldb 加此行

cd /usr/local/mysql

cp support-files/mysql.server ?/etc/init.d/mysqld

chkconfig –add mysqld

service mysqld start

10 安全加固

?mysql_secure_installation

源碼編譯安裝mariadb

? 安裝包

yum install bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-

devel gcc gcc-c++ cmake libevent-devel gnutls-devel libaio-devel openssl-

devel ncurses-devel libxml2-devel

? 做準備用戶和數據目錄

mkdir /data

useradd –r –s /bin/false –m –d /data/mysqldb/ mysql

tar xvf mariadb-10.2.12.tar.gz

? cmake 編譯安裝

cd mariadb-10.2.12/

編譯選項:

https://dev.mysql.com/doc/refman/5.7/en/source-configuration-options.html

源碼編譯安裝mariadb

cmake . \

-DCMAKE_INSTALL_PREFIX=/app/mysql \

-DMYSQL_DATADIR=/data/mysqldb/ \

-DSYSCONFDIR=/etc \

-DMYSQL_USER=mysql \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DWITH_ARCHIVE_STORAGE_ENGINE=1 \

-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \

-DWITH_PARTITION_STORAGE_ENGINE=1 \

-DWITHOUT_MROONGA_STORAGE_ENGINE=1 \

-DWITH_DEBUG=0 \

-DWITH_READLINE=1 \

-DWITH_SSL=system \

-DWITH_ZLIB=system \

-DWITH_LIBWRAP=0 \

-DENABLED_LOCAL_INFILE=1 \

-DMYSQL_UNIX_ADDR=/app/mysql/mysql.sock \

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci

make && make install

源碼編譯安裝mariadb

? 準備環境變量

echo ‘PATH=/app/mysql/bin:$PATH’ > ?/etc/profile.d/mysql.sh

. /etc/profile.d/mysql.sh

? 生成數據庫文件

cd /app/mysql/

scripts/mysql_install_db –datadir=/data/mysqldb/ –user=mysql

? 準備配置文件

cp /app/mysql/support-files/my-huge.cnf /etc/my.cnf

? 準備啟動腳本

cp /app/mysql/support-files/mysql.server /etc/init.d/mysqld

? 啟動服務

chkconfig –add mysqld ;service mysqld start

安裝完成后也可以修改數據存放路徑

vim /etc/my.cnf.d/server.cnf

[mysqld]

datadir=/data/mysql

創建文件夾

mkdir /data/mysql -p

chown mysql.mysql /data/mysql

mysql_install_db –datadir=/data/mysql –user=mysql

sysctmctl restart mariadb

源碼安裝mariadb-server；

? 安裝包

yum install bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-

devel gcc gcc-c++ cmake libevent-devel gnutls-devel libaio-devel openssl-

devel ncurses-devel libxml2-devel

tar xvf mariadb-10.2.12.tar.gz

[root@CENTOS7 mariadb-10.3.7]# mkdir -pv /data/mysqldb

mkdir: created directory ‘/data/mysqldb’

[root@CENTOS7 mariadb-10.3.7]# chown mysql.mysql /data/mysqldb/

[root@CENTOS7 mariadb-10.3.7]# ll /data/mysqldb/

total 0

[root@CENTOS7 mariadb-10.3.7]# ll -d /data/mysqldb/

drwxr-xr-x. 2 mysql mysql 6 Jun ?5 19:05 /data/mysqldb/

源碼編譯安裝mariadb

cmake . \

-DCMAKE_INSTALL_PREFIX=/app/mysql \

-DMYSQL_DATADIR=/data/mysqldb/ \

-DSYSCONFDIR=/etc \

-DMYSQL_USER=mysql \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DWITH_ARCHIVE_STORAGE_ENGINE=1 \

-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \

-DWITH_PARTITION_STORAGE_ENGINE=1 \

-DWITHOUT_MROONGA_STORAGE_ENGINE=1 \

-DWITH_DEBUG=0 \

-DWITH_READLINE=1 \

-DWITH_SSL=system \

-DWITH_ZLIB=system \

-DWITH_LIBWRAP=0 \

-DENABLED_LOCAL_INFILE=1 \

-DMYSQL_UNIX_ADDR=/app/mysql/mysql.sock \

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci

make && make install

[root@CENTOS7 mariadb-10.3.7]# ls /app/mysql/

bin ? ? ?COPYING.thirdparty ?data ?EXCEPTIONS-CLIENT ?INSTALL-BINARY ?man ? ? ? ? README.md ? ? scripts ?sql-bench

COPYING ?CREDITS ? ? ? ? ? ? docs ?include ? ? ? ? ? ?lib ? ? ? ? ? ? mysql-test ?README-wsrep ?share ? ?support-files

[root@CENTOS7 mariadb-10.3.7]# ls /app/mysql//bin

aria_chk ? ? ? ?myisamchk ? ? ? ? ?mysqlcheck ? ? ? ? ? ? ? ? ?mysql_find_rows ? ? ? ? ? ?mysqltest ? ? ? ? ? ?tokuftdump

aria_dump_log ? myisam_ftdump ? ? ?mysql_client_test ? ? ? ? ? mysql_fix_extensions ? ? ? mysql_tzinfo_to_sql ?tokuft_logprint

aria_ftdump ? ? myisamlog ? ? ? ? ?mysql_config ? ? ? ? ? ? ? ?mysqlhotcopy ? ? ? ? ? ? ? mysql_upgrade ? ? ? ?wsrep_sst_common

aria_pack ? ? ? myisampack ? ? ? ? mysql_convert_table_format ?mysqlimport ? ? ? ? ? ? ? ?mysql_waitpid ? ? ? ?wsrep_sst_mariabackup

aria_read_log ? my_print_defaults ?mysqld ? ? ? ? ? ? ? ? ? ? ?mysql_ldb ? ? ? ? ? ? ? ? ?mytop ? ? ? ? ? ? ? ?wsrep_sst_mysqldump

innochecksum ? ?myrocks_hotbackup ?mysqld_multi ? ? ? ? ? ? ? ?mysql_plugin ? ? ? ? ? ? ? perror ? ? ? ? ? ? ? wsrep_sst_rsync

mariabackup ? ? mysql ? ? ? ? ? ? ?mysqld_safe ? ? ? ? ? ? ? ? mysql_secure_installation ?replace ? ? ? ? ? ? ?wsrep_sst_xtrabackup

mariadb_config ?mysqlaccess ? ? ? ?mysqld_safe_helper ? ? ? ? ?mysql_setpermission ? ? ? ?resolveip ? ? ? ? ? ?wsrep_sst_xtrabackup-v2

mbstream ? ? ? ?mysqladmin ? ? ? ? mysqldump ? ? ? ? ? ? ? ? ? mysqlshow ? ? ? ? ? ? ? ? ?resolve_stack_dump

msql2mysql ? ? ?mysqlbinlog ? ? ? ?mysqldumpslow ? ? ? ? ? ? ? mysqlslap ? ? ? ? ? ? ? ? ?sst_dump

[root@CENTOS7 mariadb-10.3.7]# echo PATH=/app/mysql/bin/:$PATH > /etc/profile.d/mysql.sh

[root@CENTOS7 mariadb-10.3.7]# . /etc/profile.d/mysql.sh

[root@CENTOS7 mariadb-10.3.7]# cd /app/mysql/

[root@CENTOS7 mysql]# scripts/mysql_install_db –datadir=/data/mysqldb/ –user=mysql –basedir=/app/mysql

[root@CENTOS7 mysql]# ls /data/mysqldb/ -l

total 110620

-rw-rw—-. 1 mysql mysql ? ?16384 Jun ?5 21:07 aria_log.00000001

-rw-rw—-. 1 mysql mysql ? ? ? 52 Jun ?5 21:07 aria_log_control

-rw-rw—-. 1 mysql mysql ? ? ?972 Jun ?5 21:07 ib_buffer_pool

-rw-rw—-. 1 mysql mysql 12582912 Jun ?5 21:07 ibdata1

-rw-rw—-. 1 mysql mysql 50331648 Jun ?5 21:07 ib_logfile0

-rw-rw—-. 1 mysql mysql 50331648 Jun ?5 21:07 ib_logfile1

drwx——. 2 mysql root ? ? ?4096 Jun ?5 21:07 mysql

drwx——. 2 mysql mysql ? ? ? 20 Jun ?5 21:07 performance_schema

drwx——. 2 mysql mysql ? ? ? 20 Jun ?5 21:07 test

[root@CENTOS7 support-files]# cp my-huge.cnf /etc/my.cnf

vim /etc/my.cnf

[root@CENTOS7 support-files]# cp mysql.server /etc/init.d/mysqld

setfacl -R -m -u:mysql:rwx /app/mysql

service mysqld start

實現多實例；

/usr/bin/mysqld_multi這個就可以實現多實例，但是不能應用在不同的版本上。

?mkdir /mysqldb/{3306,3307,3308}/{etc,socket,pid,log,data} -pv

[root@CENTOS7 ~]# tree /mysqldb/

/mysqldb/

├── 3306

│ ? ├── data

│ ? ├── etc

│ ? ├── log

│ ? ├── pid

│ ? └── socket

├── 3307

│ ? ├── data

│ ? ├── etc

│ ? ├── log

│ ? ├── pid

│ ? └── socket

└── 3308

? ? ├── data

? ? ├── etc

? ? ├── log

? ? ├── pid

? ? └── socket

[root@CENTOS7 ~]# chown -R mysql.mysql /mysqldb/

mysql> show variables like ‘%basedir%’; ? 查找basedir的路徑 ?如果是二進制安裝就不許指定basedur

+—————+——-+

| Variable_name | Value |

+—————+——-+

| basedir ? ? ? | /usr/ |

+—————+——-+

1 row in set (0.00 sec)

[root@CENTOS7 ~]# mysql_install_db –datadir=/mysqldb/3306/data –user=mysql –basedir=/uer/

[root@CENTOS7 ~]# mysql_install_db –datadir=/mysqldb/3306/data –user=mysql

[root@CENTOS7 ~]# mysql_install_db –datadir=/mysqldb/3307/data –user=mysql

[root@CENTOS7 ~]# mysql_install_db –datadir=/mysqldb/3308/data –user=mysql

[root@CENTOS7 ~]# cp /etc/my.cnf /mysqldb/3306//etc/

[root@CENTOS7 ~]# vim /mysqldb/3306/etc/my.cnf

[root@CENTOS7 ~]# cp /mysqldb/3306/etc/my.cnf /mysqldb/3307/etc/my.cnf

[root@CENTOS7 ~]# cp /mysqldb/3306/etc/my.cnf /mysqldb/3308/etc/my.cnf

[root@CENTOS7 ~]# cat /mysqldb/3306/etc/my.cnf /mysqldb/3307/etc/my.cnf /mysqldb/3308/etc/my.cnf

[mysqld]

port=3306

datadir=/mysqldb/3306/data

socket=/mysqldb/3306/socket/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 mariadb according to the

# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]

log-error=/mysqldb/3306/log/mariadb.log

pid-file=/mysqldb/3306/pid/mariadb.pid

# include all files from the config directory

#!includedir /etc/my.cnf.d

[mysqld]

port=3307

datadir=/mysqldb/3307/data

socket=/mysqldb/3307/socket/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 mariadb according to the

# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]

log-error=/mysqldb/3307/log/mariadb.log

pid-file=/mysqldb/3307/pid/mariadb.pid

# include all files from the config directory

#!includedir /etc/my.cnf.d

[mysqld]

port=3308

datadir=/mysqldb/3308/data

socket=/mysqldb/3308/socket/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 mariadb according to the

# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]

log-error=/mysqldb/3308/log/mariadb.log

pid-file=/mysqldb/3308/pid/mariadb.pid

# include all files from the config directory

#!includedir /etc/my.cnf.d

需要啟動腳本

導入預先準備好的啟動腳本

[root@CENTOS7 ~]# vim mysqld

#!/bin/bash

port=3306

mysql_user=”root”

mysql_pwd=””

cmd_path=”/usr/bin”

mysql_basedir=”/mysqldb”

mysql_sock=”${mysql_basedir}/${port}/socket/mysql.sock”

function_start_mysql()

{

? ? if [ ! -e “$mysql_sock” ];then

? ? ? printf “Starting MySQL…\n”

? ? ? ${cmd_path}/mysqld_safe –defaults-file=${mysql_basedir}/${port}/etc/my.cnf ?&> /dev/null ?&

? ? else

? ? ? printf “MySQL is running…\n”

? ? ? exit

? ? fi

}

function_stop_mysql()

{

? ? if [ ! -e “$mysql_sock” ];then

? ? ? ?printf “MySQL is stopped…\n”

? ? ? ?exit

? ? else

? ? ? ?printf “Stoping MySQL…\n”

? ? ? ?${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown

? ?fi

}

function_restart_mysql()

{

? ? printf “Restarting MySQL…\n”

? ? function_stop_mysql

? ? sleep 2

? ? function_start_mysql

}

case $1 in

start)

? ? function_start_mysql

;;

stop)

? ? function_stop_mysql

;;

restart)

? ? function_restart_mysql

;;

? ? printf “Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n”

esac

[root@CENTOS7 ~]# cp mysqld /mysqldb/3306

[root@CENTOS7 ~]# cp mysqld /mysqldb/3307

[root@CENTOS7 ~]# cp mysqld /mysqldb/3308

[root@CENTOS7 ~]# vim /mysqldb/3308/mysqld

#!/bin/bash

port=3308

[root@CENTOS7 ~]# vim /mysqldb/3307/mysqld

#!/bin/bash

port=3307

[root@CENTOS7 ~]# chmod /mysqldb/3308/

data/ ? etc/ ? ?log/ ? ?mysqld ?pid/ ? ?socket/

[root@CENTOS7 ~]# chmod ?+x /mysqldb/3308/mysqld

[root@CENTOS7 ~]# chmod 700 /mysqldb/3308/mysqld

[root@CENTOS7 ~]# chmod ?+x /mysqldb/3308/mysqld

[root@CENTOS7 ~]# chmod 700 /mysqldb/3308/mysqld

[root@CENTOS7 ~]# chmod 700 /mysqldb/3307/mysqld

[root@CENTOS7 ~]# chmod 700 /mysqldb/3306/mysqld

[root@CENTOS7 ~]# chmod ?+x /mysqldb/3307/mysqld

[root@CENTOS7 ~]# chmod ?+x /mysqldb/3306/mysqld

[root@CENTOS7 ~]# /mysqldb//3308/mysqld start

Starting MySQL…

[root@CENTOS7 ~]# ss -ntl

State ? ? ? Recv-Q Send-Q ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? Local Address:Port ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?Peer Address:Port

LISTEN ? ? ?0 ? ? ?50 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? *:3308

[root@CENTOS7 ~]# /mysqldb//3307/mysqld start

Starting MySQL…

[root@CENTOS7 ~]# /mysqldb//3306/mysqld start

Starting MySQL…

[root@CENTOS7 ~]# ss -ntl

State ? ? ? Recv-Q Send-Q ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? Local Address:Port ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?Peer Address:Port

LISTEN ? ? ?0 ? ? ?50 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? *:3307 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? *:*

LISTEN ? ? ?0 ? ? ?50 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? *:3308 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? *:*

LISTEN ? ? ?0 ? ? ?50 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? *:3306 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? *:*

[root@CENTOS7 ~]# mysql -S /mysqldb/3308/socket/mysql.sock

Welcome to the MariaDB monitor. ?Commands end with ; or \g.

Your MariaDB connection id is 1

Server version: 5.5.56-MariaDB MariaDB Server

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]>

MariaDB [(none)]> show variables like ‘%potr%’;

Empty set (0.00 sec)

MariaDB [(none)]> show variables like ‘%port%’;

+————————————-+——-+

| Variable_name ? ? ? ? ? ? ? ? ? ? ? | Value |

+————————————-+——-+

| extra_port ? ? ? ? ? ? ? ? ? ? ? ? ?| 0 ? ? |

| innodb_import_table_from_xtrabackup | 0 ? ? |

| innodb_support_xa ? ? ? ? ? ? ? ? ? | ON ? ?|

| large_files_support ? ? ? ? ? ? ? ? | ON ? ?|

| port ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?| 3308 ?|

| progress_report_time ? ? ? ? ? ? ? ?| 5 ? ? |

| report_host ? ? ? ? ? ? ? ? ? ? ? ? | ? ? ? |

| report_password ? ? ? ? ? ? ? ? ? ? | ? ? ? |

| report_port ? ? ? ? ? ? ? ? ? ? ? ? | 3308 ?|

| report_user ? ? ? ? ? ? ? ? ? ? ? ? | ? ? ? |

+————————————-+——-+

10 rows in set (0.00 sec)

[root@CENTOS7 ~]# mysql -S /mysqldb/3307/socket/mysql.sock

MariaDB [(none)]> show variables like ‘%port%’;

+————————————-+——-+

| Variable_name ? ? ? ? ? ? ? ? ? ? ? | Value |

+————————————-+——-+

| extra_port ? ? ? ? ? ? ? ? ? ? ? ? ?| 0 ? ? |

| innodb_import_table_from_xtrabackup | 0 ? ? |

| innodb_support_xa ? ? ? ? ? ? ? ? ? | ON ? ?|

| large_files_support ? ? ? ? ? ? ? ? | ON ? ?|

| port ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?| 3307 ?|

| progress_report_time ? ? ? ? ? ? ? ?| 5 ? ? |

| report_host ? ? ? ? ? ? ? ? ? ? ? ? | ? ? ? |

| report_password ? ? ? ? ? ? ? ? ? ? | ? ? ? |

| report_port ? ? ? ? ? ? ? ? ? ? ? ? | 3307 ?|

| report_user ? ? ? ? ? ? ? ? ? ? ? ? | ? ? ? |

+————————————-+——-+

MariaDB [(none)]> create database db3307;

Query OK, 1 row affected (0.00 sec)

[root@CENTOS7 ~]# ls /mysqldb//3307/data/

aria_log.00000001 ?aria_log_control ?db3307 ?ibdata1 ?ib_logfile0 ?ib_logfile1 ?mysql ?performance_schema ?test

實驗：centos7.4 yum 安裝包的實現多實例

1 yum install mariadb-server

2 mkdir /mysqldb/{3306,3307,3308}/{etc,socket,pid,log,data} -pv

?chown -R mysql.mysql /mysqldb/

3 mysql_install_db –datadir=/mysqldb/3306/data –user=mysql

? mysql_install_db –datadir=/mysqldb/3307/data –user=mysql

?mysql_install_db –datadir=/mysqldb/3308/data –user=mysql

cp /etc/my.cnf /mysqldb/3306/etc/

vim /mysqldb/3306/etc/my.cnf

[mysqld]

port=3306

datadir=/mysqldb/3306/data

socket=/mysqldb/3306/socket/mysql.sock

[mysqld_safe]

log-error=/mysqldb/3306/log/mariadb.log

pid-file=/mysqldb/3306/pid/mariadb.pid

#!includedir /etc/my.cnf.d

cp /mysqldb/3306/etc/my.cnf ?/mysqldb/3307/etc/my.cnf

cp /mysqldb/3306/etc/my.cnf ?/mysqldb/3308/etc/my.cnf

vim /mysqldb/3308/etc/my.cnf

vim /mysqldb/3307/etc/my.cnf

cp mysqld /mysqldb/3306/

cp mysqld /mysqldb/3307/

cp mysqld /mysqldb/3308/

chmod 700 /mysqldb/3308/mysqld

chmod 700 /mysqldb/3307/mysqld

chmod 700 /mysqldb/3306/mysqld

vim ?/mysqldb/3306/mysqld

vim ?/mysqldb/3307/mysqld

vim ?/mysqldb/3308/mysqld

6 /mysqldb/3308/mysqld start

?/mysqldb/3307/mysqld start

?/mysqldb/3306/mysqld start

mysql ?-S /mysqldb/3308/socket/mysql.sock

mysql ?-S /mysqldb/3307/socket/mysql.sock

mysql ?-S /mysqldb/3306/socket/mysql.sock

[root@CENTOS7 ~]# /mysqldb/3308/mysqld stop ? 停止程序

Stoping MySQL…

Enter password:

[root@CENTOS7 ~]# mysql -S /mysqldb/3307/socket/mysql.sock

MariaDB [(none)]> select user,host,password from mysql.user;

+——+———————+———-+

| user | host ? ? ? ? ? ? ? ?| password |

+——+———————+———-+

| root | localhost ? ? ? ? ? | ? ? ? ? ?|

| root | centos7.localdomain | ? ? ? ? ?|

| root | 127.0.0.1 ? ? ? ? ? | ? ? ? ? ?|

| root | ::1 ? ? ? ? ? ? ? ? | ? ? ? ? ?|

| ? ? ?| localhost ? ? ? ? ? | ? ? ? ? ?|

| ? ? ?| centos7.localdomain | ? ? ? ? ?|

+——+———————+———-+

6 rows in set (0.00 sec)

MariaDB [(none)]> update mysql.user set password=password(“centos”) where user=’root’;

Query OK, 4 rows affected (0.00 sec)

Rows matched: 4 ?Changed: 4 ?Warnings: 0

MariaDB [(none)]> select user,host,password from mysql.user;

+——+———————+——————————————-+

| user | host ? ? ? ? ? ? ? ?| password ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?|

+——+———————+——————————————-+

| root | localhost ? ? ? ? ? | *128977E278358FF80A246B5046F51043A2B1FCED |

| root | centos7.localdomain | *128977E278358FF80A246B5046F51043A2B1FCED |

| root | 127.0.0.1 ? ? ? ? ? | *128977E278358FF80A246B5046F51043A2B1FCED |

| root | ::1 ? ? ? ? ? ? ? ? | *128977E278358FF80A246B5046F51043A2B1FCED |

因為腳本文件沒有修改 ?修改完成后配置文件沒有生效

[root@CENTOS7 ~]# /mysqldb/3307/mysqld stop

Stoping MySQL…

Enter password:

/usr/bin/mysqladmin: connect to server at ‘localhost’ failed

error: ‘Access denied for user ‘root’@’localhost’ (using password: YES)’

使權限生效

[root@CENTOS7 ~]# mysql -S /mysqldb/3307/socket/mysql.sock

MariaDB [(none)]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

[root@CENTOS7 ~]# /mysqldb/3307/mysqld stop

Stoping MySQL…

[root@CENTOS7 ~]# ss -ntl

關系型數據庫的常見組件

?數據庫：database

?表：table

行：row

列：column

?索引：index

?視圖：view

?用戶：user

?權限：privilege

?存儲過程：procedure，無返回值

?存儲函數：function，有返回值

?觸發器：trigger

?事件調度器：event scheduler，任務計劃

SQL語言的興起與語法標準

?20世紀70年代，IBM開發出SQL，用于DB2

?1981年，IBM推出SQL/DS數據庫

?業內標準微軟和Sybase的T-SQL，Oracle的PL/SQL

?SQL作為關系型數據庫所使用的標準語言，最初是基于IBM的實現在1986年被

批準的。1987年，“國際標準化組織(ISO)”把ANSI(美國國家標準化組織)

SQL作為國際標準。

?SQL：ANSI SQL

SQL-86, SQL-89, SQL-92, SQL-99, SQL-03

SQL語言規范

?在數據庫系統中，SQL語句不區分大小寫(建議用大寫)

?但字符串常量區分大小寫

?SQL語句可單行或多行書寫，以“;”結尾

?關鍵詞不能跨多行或簡寫

?用空格和縮進來提高語句的可讀性

?子句通常位于獨立行，便于編輯，提高可讀性

?注釋：

?SQL標準：

/*注釋內容*/ 多行注釋

aaa

bbb

cccc

select user，host，/*注釋*/password，from user；

— 注釋內容單行注釋，注意有空格 ? ? 單行注釋要么自己一行要么寫在命令的后面。

?MySQL注釋：

數據庫對象

?數據庫的組件(對象)：

數據庫、表、索引、視圖、用戶、存儲過程、函數、觸發器、事件調度器等

?命名規則：

?必須以字母開頭

?可包括數字和三個特殊字符（# _ $）

?不要使用MySQL的保留字 ?列如select

?同一database(Schema)下的對象不能同名（即使是不同類型的也不能同名）

在同一個實例（instance）

SQL語句分類

?SQL語句分類：

?DDL: Data Defination Language 數據定義語言

CREATE, DROP, ALTER （增刪改）表的結構，數據庫的名稱等，修改數據庫中的對象

?DML: Data Manipulation Language數據的操作語言

INSERT, DELETE, UPDATE（增刪改）對數據表增刪改，修改數據的

?DCL：Data Control Language數據的控制語言，主要針對權限

GRANT, REVOKE（授權，撤銷）

?DQL：Data Query Language數據的查詢語言

SELECT

SQL語句構成

?SQL語句構成：

Keyword組成clause，關鍵字組成子句

多條clause組成語句

?示例：

SELECT * SELECT子句

FROM products FROM子句

WHERE price>400 WHERE子句

說明：此為一組SQL語句，由三個子句構成，SELECT,FROM和WHERE是關鍵字

數據庫操作

? 創建數據庫：

CREATE DATABASE|SCHEMA [IF NOT EXISTS] ‘DB_NAME’;

CHARACTER SET ‘character set name’ ? ?字符集

COLLATE ‘collate name’ ? 排序規則

? 刪除數據庫

DROP DATABASE|SCHEMA [IF EXISTS] ‘DB_NAME’;

? 查看支持所有字符集：SHOW CHARACTER SET;

? 查看支持所有排序規則：SHOW COLLATION;

? 獲取命令使用幫助：

mysql> HELP KEYWORD;

? 查看數據庫列表：

mysql> SHOW DATABASES;

[root@CENTOS7 ~]# systemctl start mariadb

[root@CENTOS7 ~]# mysql

Welcome to the MariaDB monitor. ?Commands end with ; or \g.

Your MariaDB connection id is 2

Server version: 5.5.56-MariaDB MariaDB Server

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]>

MariaDB [(none)]> show databases; ? ?查看數據庫列表

+——————–+

| Database ? ? ? ? ? |

+——————–+

| information_schema |

| mysql ? ? ? ? ? ? ?|

| performance_schema |

| test ? ? ? ? ? ? ? |

+——————–+

4 rows in set (0.00 sec)

MariaDB [(none)]> \! ls /var/lib/mysql

aria_log.00000001 ?aria_log_control ?ibdata1 ?ib_logfile0 ?ib_logfile1 mysql ?mysql.sock ?performance_schema ?test

在硬盤上只能看到performance_schema ?test mysql ?因為 information_schema 是一個虛擬的放在內存中。

MariaDB [(none)]> create database db1; ? 創建一個數據庫（名字最好是代表這個數據庫是存什么的）

MariaDB [(none)]> \! cat /var/lib/mysql/db1/db.opt ? 自己創建的數據庫中存放的文件

default-character-set=latin1 ? ? ? ?默認的字符集是拉丁這個支持ABC如果想支持別的語言最好用utf-8

default-collation=latin1_swedish_ci ? ? 默認的排序方式是拉丁

MariaDB [(none)]> drop database db1; ? 刪除數據庫

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show character set; ?查看系統支持的所有字符集

utf8 ? ? | UTF-8 Unicode ? ? ? ? ? ? ? | utf8_general_ci ? ? | ? ? ?3 ? ?支持全世界的語言

MariaDB [(none)]> show collation;查看所有的排序規則

MariaDB [(none)]> help create; 查看create命令如何使用

MariaDB [(none)]> help create database;

http://dev.mysql.com/doc/refman/5.5/en/create-database.html ?詳細查詢

MariaDB [(none)]> use db1 ? 指定當前數據庫是誰

Database changed

MariaDB [db1]>

MariaDB [db1]> show tables; ?查看數據庫中的表

Empty set (0.00 sec)

表

?表：二維關系

?設計表：遵循規范

?定義：字段，索引

字段：字段名，字段數據類型，修飾符

約束，索引：應該創建在經常用作查詢條件的字段上

創建表

?創建表：CREATE TABLE

?(1) 直接創建

?(2) 通過查詢現存表創建；新表會被直接插入查詢而來的數據

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

[(create_definition,…)] [table_options]

[partition_options] select_statement

?(3) 通過復制現存的表的表結構創建，但不復制數據

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE

old_tbl_name | (LIKE old_tbl_name) }

?注意：

? Storage Engine是指表類型，也即在表創建時指明其使用的存儲引擎，同一庫中不同

表可以使用不同的存儲引擎

? 同一個庫中表建議要使用同一種存儲引擎類型

MariaDB [db1]> help create table; ? 查詢create table用法

共3中創建方法

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name當在腳本中運行時需要加IF NOT EXISTS

? ? (create_definition,…)

? ? [table_options]

? ? [partition_options]

Or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

? ? [(create_definition,…)]

? ? [table_options]

? ? [partition_options]

? ? select_statement

Or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

? ? { LIKE old_tbl_name | (LIKE old_tbl_name) }

數據類型

?數據類型：

? 數據長什么樣？

? 數據需要多少空間來存放？

?系統內置數據類型和用戶定義數據類型

?MySql支持多種列類型：

? 數值類型

? 日期/時間類型

? 字符串(字符)類型

?選擇正確的數據類型對于獲得高性能至關重要，三大原則：

? 更小的通常更好，盡量使用可正確存儲數據的最小數據類型

? 簡單就好，簡單數據類型的操作通常需要更少的CPU周期

? 盡量避免NULL，包含為NULL的列，對MySQL更難優化

數據類型

? 1、整型

? tinyint(m) ?1個字節范圍(-128~127)

? smallint(m) ?2個字節范圍(-32768~32767)

? mediumint(m) ?3個字節范圍(-8388608~8388607)

? int(m) ?4個字節范圍(-2147483648~2147483647)

? bigint(m) 8個字節范圍(+-9.22*10的18次方)

取值范圍如果加了unsigned，則最大值翻倍，如tinyint unsigned的取值范圍為(0~255)

int(m)里的m是表示SELECT查詢結果集中的顯示寬度，并不影響實際的取值范圍，規定

了MySQL的一些交互工具（例如MySQL命令行客戶端）用來顯示字符的個數。對于存儲

和計算來說，Int(1)和Int(20)是相同的

?BOOL，BOOLEAN：布爾型，是TINYINT(1)的同義詞。zero值被視為假。非zero值視

為真

數據類型

?2、浮點型(float和double)，近似值

? float(m,d) 單精度浮點型 8位精度(4字節) m總個數，d小數位

? double(m,d) 雙精度浮點型16位精度(8字節) m總個數，d小數位

? 設一個字段定義為float(6,3)，如果插入一個數123.45678,實際數據庫里存的是

123.457，但總個數還以實際為準，即6位

數據類型

?3、定點數

? 在數據庫中存放的是精確值,存為十進制

? decimal(m,d) 參數m<65 是總個數，d<30且 d<m 是小數位

? MySQL5.0和更高版本將數字打包保存到一個二進制字符串中（每4個字節存9個數

字）。例如，decimal(18,9)小數點兩邊將各存儲9個數字，一共使用9個字節：小數點

前的數字用4個字節，小數點后的數字用4個字節，小數點本身占1個字節

? 浮點類型在存儲同樣范圍的值時，通常比decimal使用更少的空間。float使用4個字節

存儲。double占用8個字節

? 因為需要額外的空間和計算開銷，所以應該盡量只在對小數進行精確計算時才使用

decimal——例如存儲財務數據。但在數據量比較大的時候，可以考慮使用bigint代替

decimal

數據類型

?4、字符串(char,varchar,_text)

? char(n) 固定長度，最多255個字符

? varchar(n)可變長度，最多65535個字符

? tinytext 可變長度，最多255個字符

? text ?可變長度，最多65535個字符

? mediumtext 可變長度，最多2的24次方-1個字符

? longtext 可變長度，最多2的32次方-1個字符

? BINARY(M) 固定長度，可存二進制或字符，長度為0-M字節

? VARBINARY(M) 可變長度，可存二進制或字符，允許長度為0-M字節

? 內建類型：ENUM枚舉, SET集合

數據類型

? char和varchar：

? 1.char(n) 若存入字符數小于n，則以空格補于其后，查詢之時再將空格去掉。所以

char類型存儲的字符串末尾不能有空格，varchar不限于此。

? 2.char(n) 固定長度，char(4)不管是存入幾個字符，都將占用4個字節，varchar是存入

的實際字符數+1個字節（n< n>255)，所以varchar(4),存入3個字符將占用4個字節。

? 3.char類型的字符串檢索速度要比varchar類型的快

? varchar和text：

? 1.varchar可指定n，text不能指定，內部存儲varchar是存入的實際字符數+1個字節

（n< n>255)，text是實際字符數+2個字節。

? 2.text類型不能有默認值

? 3.varchar可直接創建索引，text創建索引要指定前多少個字符。varchar查詢速度快于

text

數據類型

? 5.二進制數據：BLOB

? BLOB和text存儲方式不同，TEXT以文本方式存儲，英文存儲區分大小寫，

而Blob是以二進制方式存儲，不分大小寫

? BLOB存儲的數據只能整體讀出

? TEXT可以指定字符集，BLOB不用指定字符集

? 6.日期時間類型

? date ?日期 ‘2008-12-2’

? time ?時間 ’12:25:36′

? datetime ?日期時間 ‘2008-12-2 22:06:44’

? timestamp ?自動存儲記錄修改時間，好處假設銀行賬戶中存放1億，如果有時間戳那么一但挪用這筆錢，就會導致時間戳不一樣，自動修改

? YEAR(2), YEAR(4)：年份

timestamp字段里的時間數據會隨其他字段修改的時候自動刷新，這個數據類型的字

段可以存放這條記錄最后被修改的時間

修飾符

?所有類型：

? NULL ?數據列可包含NULL值

? NOT NULL ?數據列不允許包含NULL值

? DEFAULT 默認值

? PRIMARY KEY ?主鍵

? UNIQUE KEY 唯一鍵

? CHARACTER SET name ?指定一個字符集

?數值型

? AUTO_INCREMENT ?自動遞增，適用于整數類型

? UNSIGNED ?無符號

salary decimal(5,2)表示共5位精確到小數點后兩位

char是比較占空間的，但是在內存中存放是固定長度的，查詢更快

創建一個students表

MariaDB [db1]> create table students ( id tinyint unsigned not null primary key,name char(10) not null,phone char(11),sex char(1) );

MariaDB [db1]> show tables;

+—————+

| Tables_in_db1 |

+—————+

| students ? ? ?|

+—————+

1 row in set (0.00 sec)

MariaDB [db1]> desc students; ? ?查看這個表的定義

+——-+———————+——+—–+———+——-+

+——-+———————+——+—–+———+——-+

| id ? ?| tinyint(3) unsigned | NO ? | PRI | NULL ? ?| ? ? ? |

| name ?| char(10) ? ? ? ? ? ?| NO ? | ? ? | NULL ? ?| ? ? ? |

| phone | char(11) ? ? ? ? ? ?| YES ?| ? ? | NULL ? ?| ? ? ? |

| sex ? | char(1) ? ? ? ? ? ? | YES ?| ? ? | NULL ? ?| ? ? ? |

+——-+———————+——+—–+———+——-+

MariaDB [db1]> show table status like ‘students’\G

*************************** 1. row ***************************

? ? ? ? ? ?Name: students ? ?表明

? ? ? ? ?Engine: InnoDB ? 存儲引擎 ? ?在centos6上適應的是myisam

? ? ? ? Version: 10

? ? ?Row_format: Compact

? ? ? ? ? ?Rows: 0

?Avg_row_length: 0

? ? Data_length: 16384

Max_data_length: 0

? ?Index_length: 0

? ? ? Data_free: 10485760

?Auto_increment: NULL

? ? Create_time: 2018-06-06 20:41:40

? ? Update_time: NULL

? ? ?Check_time: NULL

? ? ? Collation: latin1_swedish_ci

? ? ? ?Checksum: NULL

?Create_options:

? ? ? ? Comment:

1 row in set (0.00 sec)

創建表

? CREATE TABLE [IF NOT EXISTS] ‘tbl_name’ (col1 type1 修飾符, col2 type2 修飾

符, …)

? 字段信息

? col type1

? PRIMARY KEY(col1,…)

? INDEX(col1, …)

? UNIQUE KEY(col1, …)

? 表選項：

? ENGINE [=] engine_name

SHOW ENGINES;查看支持的engine類型

? ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

? 獲取幫助：mysql> HELP CREATE TABLE;

表操作

?查看所有的引擎：SHOW ENGINES

?查看表：SHOW TABLES [FROM db_name]

?查看表結構：DESC [db_name.]tb_name

?刪除表：DROP TABLE [IF EXISTS] tb_name

?查看表創建命令：SHOW CREATE TABLE tbl_name

?查看表狀態：SHOW TABLE STATUS LIKE ‘tbl_name’

?查看庫中所有表狀態： SHOW TABLE STATUS FROM ?db_name

MariaDB [db1]> show table status from db1\G ? ?查看數據庫中有幾個表

*************************** 1. row ***************************

? ? ? ? ? ?Name: students

? ? ? ? ?Engine: InnoDB

? ? ? ? Version: 10

? ? ?Row_format: Compact

? ? ? ? ? ?Rows: 0

?Avg_row_length: 0

? ? Data_length: 16384

Max_data_length: 0

? ?Index_length: 0

? ? ? Data_free: 10485760

?Auto_increment: NULL

? ? Create_time: 2018-06-06 20:41:40

? ? Update_time: NULL

? ? ?Check_time: NULL

? ? ? Collation: latin1_swedish_ci

? ? ? ?Checksum: NULL

?Create_options:

? ? ? ? Comment:

1 row in set (0.00 sec)

創建emp表格，定義其相關屬性

MariaDB [db1]> create table emp ?( id int unsigned primary key ?auto_increment, name varchar(30) not null, sex char(1) default ‘m’, address varchar(100) ) engine=innodb ?charset=utf8;

Query OK, 0 rows affected (0.01 sec)

MariaDB [db1]> desc emp;

+———+——————+——+—–+———+—————-+

+———+——————+——+—–+———+—————-+

| id ? ? ?| int(10) unsigned | NO ? | PRI | NULL ? ?| auto_increment |

| name ? ?| varchar(30) ? ? ?| NO ? | ? ? | NULL ? ?| ? ? ? ? ? ? ? ?|

| sex ? ? | char(1) ? ? ? ? ?| YES ?| ? ? | m ? ? ? | ? ? ? ? ? ? ? ?|

| address | varchar(100) ? ? | YES ?| ? ? | NULL ? ?| ? ? ? ? ? ? ? ?|

+———+——————+——+—–+———+—————-+

4 rows in set (0.01 sec)

MariaDB [db1]> show table status like ’emp’\G

*************************** 1. row ***************************

? ? ? ? ? ?Name: emp

? ? ? ? ?Engine: InnoDB

? ? ? ? Version: 10

? ? ?Row_format: Compact

? ? ? ? ? ?Rows: 0

?Avg_row_length: 0

? ? Data_length: 16384

Max_data_length: 0

? ?Index_length: 0

? ? ? Data_free: 10485760

?Auto_increment: 1

? ? Create_time: 2018-06-06 21:06:40

? ? Update_time: NULL

? ? ?Check_time: NULL

? ? ? Collation: utf8_general_ci

? ? ? ?Checksum: NULL

?Create_options:

? ? ? ? Comment:

1 row in set (0.00 sec)

MariaDB [db1]> show table status from db1\G

*************************** 1. row ***************************

? ? ? ? ? ?Name: emp

? ? ? ? ?Engine: InnoDB

? ? ? ? Version: 10

? ? ?Row_format: Compact

? ? ? ? ? ?Rows: 0

?Avg_row_length: 0

? ? Data_length: 16384

Max_data_length: 0

? ?Index_length: 0

? ? ? Data_free: 10485760

?Auto_increment: 1

? ? Create_time: 2018-06-06 21:06:40

? ? Update_time: NULL

? ? ?Check_time: NULL

? ? ? Collation: utf8_general_ci

? ? ? ?Checksum: NULL

?Create_options:

? ? ? ? Comment:

*************************** 2. row ***************************

? ? ? ? ? ?Name: students

? ? ? ? ?Engine: InnoDB

? ? ? ? Version: 10

? ? ?Row_format: Compact

? ? ? ? ? ?Rows: 0

?Avg_row_length: 0

? ? Data_length: 16384

Max_data_length: 0

? ?Index_length: 0

? ? ? Data_free: 10485760

?Auto_increment: NULL

? ? Create_time: 2018-06-06 20:41:40

? ? Update_time: NULL

? ? ?Check_time: NULL

? ? ? Collation: latin1_swedish_ci

? ? ? ?Checksum: NULL

?Create_options:

? ? ? ? Comment:

2 rows in set (0.00 sec)

MariaDB [db1]> show create table emp\G ? ? 查看一張舊的表示如何定義的

*************************** 1. row ***************************

? ? ? ?Table: emp

Create Table: CREATE TABLE `emp` (

? `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

? `name` varchar(30) NOT NULL,

? `sex` char(1) DEFAULT ‘m’,

? `address` varchar(100) DEFAULT NULL,

? PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.01 sec)

第二種方法創建表

利用以前的表創建新的表

MariaDB [db1]> create table user select user,host,password from mysql.user;

MariaDB [db1]> desc user;

+———-+———-+——+—–+———+——-+

+———-+———-+——+—–+———+——-+

| user ? ? | char(16) | NO ? | ? ? | ? ? ? ? | ? ? ? |

| host ? ? | char(60) | NO ? | ? ? | ? ? ? ? | ? ? ? |

| password | char(41) | NO ? | ? ? | ? ? ? ? | ? ? ? |

+———-+———-+——+—–+———+——-+

不光數據表的結構復制了，數據也復制了

MariaDB [db1]> select * from user

? ? -> ;

+——+———————+———-+

| user | host ? ? ? ? ? ? ? ?| password |

+——+———————+———-+

| root | localhost ? ? ? ? ? | ? ? ? ? ?|

| root | centos7.localdomain | ? ? ? ? ?|

| root | 127.0.0.1 ? ? ? ? ? | ? ? ? ? ?|

| root | ::1 ? ? ? ? ? ? ? ? | ? ? ? ? ?|

| ? ? ?| localhost ? ? ? ? ? | ? ? ? ? ?|

| ? ? ?| centos7.localdomain | ? ? ? ? ?|

+——+———————+———-+

只要表的數據結構，不要表的數據內容

MariaDB [db1]> create table user2 select user,host,password from mysql.user where 1 = 0 ;

MariaDB [db1]> desc user2;

+———-+———-+——+—–+———+——-+

+———-+———-+——+—–+———+——-+

| user ? ? | char(16) | NO ? | ? ? | ? ? ? ? | ? ? ? |

| host ? ? | char(60) | NO ? | ? ? | ? ? ? ? | ? ? ? |

| password | char(41) | NO ? | ? ? | ? ? ? ? | ? ? ? |

+———-+———-+——+—–+———+——-+

MariaDB [db1]> select * from user2;

Empty set (0.00 sec)

第三種方法創建表

MariaDB [db1]> create table user3 like mysql.user;

MariaDB [db1]> desc user3; ?查看表的數據結構，這種方法創建的表是把舊的表的數據結構完全復制，但是不復制表的數據內容

MariaDB [db1]> select * from user3;

Empty set (0.00 sec)

MariaDB [db1]> show columns from user2; ? ? 等價于desc user2；

+———-+———-+——+—–+———+——-+

+———-+———-+——+—–+———+——-+

| user ? ? | char(16) | NO ? | ? ? | ? ? ? ? | ? ? ? |

| host ? ? | char(60) | NO ? | ? ? | ? ? ? ? | ? ? ? |

| password | char(41) | NO ? | ? ? | ? ? ? ? | ? ? ? |

+———-+———-+——+—–+———+——-+

表操作，修改表的數據結構，但是不建議修改

? DROP TABLE [IF EXISTS] ‘tbl_name’;

? ALTER TABLE ‘tbl_name’

字段：

添加字段：add

ADD col1 data_type [FIRST|AFTER col_name]

刪除字段：drop

修改字段：

alter（默認值）, change（字段名）, modify（字段屬性）

索引:

添加索引：add index

刪除索引: drop index

表選項

修改:

? 查看表上的索引：SHOW INDEXES FROM [db_name.]tbl_name;

? 查看幫助：Help ALTER TABLE

數據存放在表中，表中的字段的順序不是特別重要，因為在查詢的時候是可以自己指定的，記錄的順序也不是特別重要

當想修改name的類型時，將varchar（30）增大問題不大，但是一旦變小就有可能使以前存的數據遭到破壞

MariaDB [db1]> desc emp;

MariaDB [db1]> help drop table

Name: ‘DROP TABLE’

Description:

Syntax:

DROP [TEMPORARY] TABLE [IF EXISTS]

? ? tbl_name [, tbl_name] …

? ? [RESTRICT | CASCADE] ? ? ?級聯刪除，如果倆個表之前存在1依賴2，那么刪除表2也會刪除表1

student ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? teacher

id ? ?name ? ? ?teacherid ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?id ? ? ? ? ? ? ? ? ? ?name

修改表示例

?ALTER TABLE students RENAME s1;

?ALTER TABLE s1 ADD phone varchar(11) AFTER name;

?ALTER TABLE s1 MODIFY phone int;

?ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);

?ALTER TABLE s1 DROP COLUMN mobile;

?Help ALTER TABLE 查看幫助

修改表示例

?ALTER TABLE students ADD gender ENUM(‘m’,’f’)

?ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL

PRIMARY KEY;

?ALTER TABLE students ADD UNIQUE KEY(name); ? ? 唯一鍵

?ALTER TABLE students ADD INDEX(age);

?DESC students;

?SHOW INDEXES FROM students;

?ALTER TABLE students DROP age;

set ? ? ?多選，其中的字符可以選一個兩個及多個

enmu ? 多選一 ?最多65535個字符中選取一個

創建復合主鍵

MariaDB [db1]> create table t1 ( name char(30),city char(30),sex char(1),primary key(name,city) );

MariaDB [db1]> desc t1;

+——-+———-+——+—–+———+——-+

+——-+———-+——+—–+———+——-+

| name ?| char(30) | NO ? | PRI | ? ? ? ? | ? ? ? |

| city ?| char(30) | NO ? | PRI | ? ? ? ? | ? ? ? |

| sex ? | char(1) ?| YES ?| ? ? | NULL ? ?| ? ? ? |

+——-+———-+——+—–+———+——-+

DML語句 ? ，表數據的操作

? DML:

? INSERT, DELETE, UPDATE, SELECT

? INSERT：

一次插入一行或多行數據

語法

? INSERT [L OW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [(col_name,…)]

{VALUES | VALUE} ({expr | DEFAULT},…),(…),…

[ ON DUPLICATE KEY UPDATE 如果重復更新之

col_name=expr

[, col_name=expr] … ]

簡化寫法：

INSERT tbl_name [(col1,…)] VALUES (val1,…), (val21,…)

?INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name

SET col_name={expr | DEFAULT}, …

[ ON DUPLICATE KEY UPDATE

col_name=expr

[, col_name=expr] … ]

? INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [(col_name,…)]

SELECT …

[ ON DUPLICATE KEY UPDATE

col_name=expr

[, col_name=expr] … ]

MariaDB [db1]> help insert

Name: ‘INSERT’

Description:

Syntax:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

? ? [INTO] tbl_name [(col_name,…)] ? 如果這里不寫字段的名字，那么久必須按照表的數據結構按順序每個字段都賦值。當寫字段名時后面的value就必須與字段對應，沒有寫的字段則是空或默認值

? ? {VALUES | VALUE} ({expr | DEFAULT},…),(…),…

? ? [ ON DUPLICATE KEY UPDATE

? ? ? col_name=expr

? ? ? ? [, col_name=expr] … ]

Or:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

? ? [INTO] tbl_name

? ? SET col_name={expr | DEFAULT}, …

? ? [ ON DUPLICATE KEY UPDATE

? ? ? col_name=expr

? ? ? ? [, col_name=expr] … ]

Or:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]

? ? [INTO] tbl_name [(col_name,…)]

? ? SELECT …

? ? [ ON DUPLICATE KEY UPDATE

? ? ? col_name=expr

? ? ? ? [, col_name=expr] … ]

在學生表中加入一行數據

MariaDB [db1]> insert students values(1,’bai’,’10086′,’m’);

MariaDB [db1]> select * from students;

+—-+——+——-+——+

| id | name | phone | sex ?|

+—-+——+——-+——+

| ?1 | bai ?| 10086 | m ? ?|

+—-+——+——-+——+

帶字段名后，values值必須與字段名一一對應

MariaDB [db1]> insert students (name,id) values (‘wang’,70);

MariaDB [db1]> select * from students;

+—-+——+——-+——+

| id | name | phone | sex ?|

+—-+——+——-+——+

| ?1 | bai ?| 10086 | m ? ?|

+—-+——+——-+——+

一次增加兩條記錄

MariaDB [db1]> insert students (id,name,sex) values (2,’liu’,’m’),(3,’lin’,’f’);

Query OK, 2 rows affected (0.00 sec)

Records: 2 ?Duplicates: 0 ?Warnings: 0

MariaDB [db1]> select * from students

? ? -> ;

+—-+——+——-+——+

| id | name | phone | sex ?|

+—-+——+——-+——+

| ?1 | bai ?| 10086 | m ? ?|

| ?2 | liu ?| NULL ?| m ? ?|

| ?3 | lin ?| NULL ?| f ? ?|

MariaDB [db1]> insert students set id=4,name=’zhao’ ;

MariaDB [db1]> select * from students

? ? -> ;

+—-+——+——-+——+

| id | name | phone | sex ?|

+—-+——+——-+——+

| ?1 | bai ?| 10086 | m ? ?|

| ?2 | liu ?| NULL ?| m ? ?|

| ?3 | lin ?| NULL ?| f ? ?|

MariaDB [db1]> insert emp (name,address) select user,host from user;

Query OK, 6 rows affected (0.00 sec)

Records: 6 ?Duplicates: 0 ?Warnings: 0

MariaDB [db1]> select * from emp;

+—-+——+——+———————+

| id | name | sex ?| address ? ? ? ? ? ? |

+—-+——+——+———————+

| ?1 | root | m ? ?| localhost ? ? ? ? ? |

| ?2 | root | m ? ?| centos7.localdomain |

| ?3 | root | m ? ?| 127.0.0.1 ? ? ? ? ? |

| ?4 | root | m ? ?| ::1 ? ? ? ? ? ? ? ? |

| ?5 | ? ? ?| m ? ?| localhost ? ? ? ? ? |

| ?6 | ? ? ?| m ? ?| centos7.localdomain |

+—-+——+——+———————+

MariaDB [db1]> show create table emp

? ? -> ;

+——-+—————————————————————————————————————————————————————————————————————————————————–+

| Table | Create Table ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?|

| emp ? | CREATE TABLE `emp` (

? `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

? `name` varchar(30) NOT NULL,

? `sex` char(1) DEFAULT ‘m’,

? `address` varchar(100) DEFAULT NULL,

? PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 |

MariaDB [db1]> desc emp;

+———+——————+——+—–+———+—————-+

+———+——————+——+—–+———+—————-+

| id ? ? ?| int(10) unsigned | NO ? | PRI | NULL ? ?| auto_increment |

| name ? ?| varchar(30) ? ? ?| NO ? | ? ? | NULL ? ?| ? ? ? ? ? ? ? ?|

| sex ? ? | char(1) ? ? ? ? ?| YES ?| ? ? | m ? ? ? | ? ? ? ? ? ? ? ?|

| address | varchar(100) ? ? | YES ?| ? ? | NULL ? ?| ? ? ? ? ? ? ? ?|

+———+——————+——+—–+———+—————-+

4 rows in set (0.00 sec)

MariaDB [db1]> desc user;

+———-+———-+——+—–+———+——-+

+———-+———-+——+—–+———+——-+

| user ? ? | char(16) | NO ? | ? ? | ? ? ? ? | ? ? ? |

| host ? ? | char(60) | NO ? | ? ? | ? ? ? ? | ? ? ? |

| password | char(41) | NO ? | ? ? | ? ? ? ? | ? ? ? |

+———-+———-+——+—–+———+——-+

將舊表的信息直接加入新表，新舊表的字段要相互匹配，且新表要包容舊表（例如新表的char（）要大于舊表的char（））

DML語句

?UPDATE：

?UPDATE [LOW_PRIORITY] [IGNORE] table_reference

SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] …

[WHERE where_condition]

[ORDER BY …]

[LIMIT row_count]

?注意：一定要有限制條件，否則將修改所有行的指定字段

限制條件：

WHERE

LIMIT

?Mysql 選項：–safe-updates| –i-am-a-dummy|-U

MariaDB [db1]> select * from emp

? ? -> ;

+—-+——+——+———————+

| id | name | sex ?| address ? ? ? ? ? ? |

+—-+——+——+———————+

| ?1 | root | m ? ?| localhost ? ? ? ? ? |

| ?2 | root | m ? ?| centos7.localdomain |

| ?3 | root | m ? ?| 127.0.0.1 ? ? ? ? ? |

| ?4 | root | m ? ?| ::1 ? ? ? ? ? ? ? ? |

| ?5 | ? ? ?| m ? ?| localhost ? ? ? ? ? |

| ?6 | ? ? ?| m ? ?| centos7.localdomain |

+—-+——+——+———————+

6 rows in set (0.00 sec)

修改第一行中的name和address

MariaDB [db1]> update emp set name=’admin’,address=’beijing’ where id=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 ?Changed: 1 ?Warnings: 0

MariaDB [db1]> select * from emp;

+—-+——-+——+———————+

| id | name ?| sex ?| address ? ? ? ? ? ? |

+—-+——-+——+———————+

| ?1 | admin | m ? ?| beijing ? ? ? ? ? ? |

| ?2 | root ?| m ? ?| centos7.localdomain |

| ?3 | root ?| m ? ?| 127.0.0.1 ? ? ? ? ? |

| ?4 | root ?| m ? ?| ::1 ? ? ? ? ? ? ? ? |

| ?5 | ? ? ? | m ? ?| localhost ? ? ? ? ? |

| ?6 | ? ? ? | m ? ?| centos7.localdomain |

+—-+——-+——+———————+

6 rows in set (0.00 sec)

?limit限定修改的行數

MariaDB [db1]> update emp set name=’adnim’,address=’beijing’ where name=’root’ limit 2;

MariaDB [db1]> select * from emp;

+—-+——-+——+———————+

| id | name ?| sex ?| address ? ? ? ? ? ? |

+—-+——-+——+———————+

| ?1 | admin | m ? ?| beijing ? ? ? ? ? ? |

| ?2 | adnim | m ? ?| beijing ? ? ? ? ? ? |

| ?3 | adnim | m ? ?| beijing ? ? ? ? ? ? |

| ?4 | root ?| m ? ?| ::1 ? ? ? ? ? ? ? ? |

| ?5 | ? ? ? | m ? ?| localhost ? ? ? ? ? |

| ?6 | ? ? ? | m ? ?| centos7.localdomain

DML語句

?DELETE:

?DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name

[WHERE where_condition]

[ORDER BY …]

[LIMIT row_count]

可先排序再指定刪除的行數

?注意：一定要有限制條件，否則將清空表中的所有數據

限制條件：

WHERE

LIMIT

?TRUNCATE TABLE tbl_name; 清空表

生產中不會真的刪除記錄，都是在表的后面加上一個delete的標記位，標記位為1就相當于是刪除了，這樣就算誤刪除記錄還在。

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?students

id ? ? ? ? ? ? ? name ? ? ? ? ? ? ? ?phone ? ? ? ?teacherid ? ? ? delete

1 ? ? ? ? ? ? ? ?wang ? ? ? ? ? ? ? ? ?10086 ? ? ? ? ? ?1 ? ? ? ? ? ? ? ? ? 1 ? ? ? ?認為刪除

2 ? ? ? ? ? ? ? ? zhang ? ? ? ? ? ? ? ?10010 ? ? ? ? ? ? 1 ? ? ? ? ? ? ? ? ?0 ? ? ? ? 沒有刪除

刪除emp表中的第4行

MariaDB [db1]> delete from emp where id=4; ? ?可以還原

MariaDB [db1]> select * from emp;

+—-+——-+——+———————+

| id | name ?| sex ?| address ? ? ? ? ? ? |

+—-+——-+——+———————+

| ?1 | admin | m ? ?| beijing ? ? ? ? ? ? |

| ?2 | adnim | m ? ?| beijing ? ? ? ? ? ? |

| ?3 | adnim | m ? ?| beijing ? ? ? ? ? ? |

| ?5 | ? ? ? | m ? ?| localhost ? ? ? ? ? |

| ?6 | ? ? ? | m ? ?| centos7.localdomain |

直接刪除表中的所有數據

MariaDB [db1]> truncate table emp; ? ?無法恢復

Query OK, 0 rows affected (0.00 sec)

MariaDB [db1]> select * from emp;

Empty set (0.00 sec)

由于刪除和更新表都是很危險的操作因此可以用下面選項來避免

Mysql 選項：–safe-updates| –i-am-a-dummy|-U

[root@CENTOS7 ~]# mysql –safe-updates ?等價于mysql -U

MariaDB [db1]> update user set user=’admin’;

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

MariaDB [db1]> delete from user ;

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

可以寫到配置文件中

[root@CENTOS7 ~]# vim /etc/my.cnf.d/mysql-clients.cnf

[mysql]

safe-updates

MariaDB [db1]> update user set user=’admin’ ;

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

DQL語句

? SELECT

[ALL | DISTINCT | DISTINCTROW ]

[SQL_CACHE | SQL_NO_CACHE]

select_expr [, select_expr …]

[FROM table_references

[WHERE where_condition]

[GROUP BY {col_name | expr | position}

[ASC | DESC], … [WITH ROLLUP]]

[HAVING where_condition]

[ORDER BY {col_name | expr | position}

[ASC | DESC], …]

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

[FOR UPDATE | LOCK IN SHARE MODE]

SELECT

?字段顯示可以使用別名：

col1 AS alias1, col2 AS alias2, …

?WHERE子句：指明過濾條件以實現“選擇”的功能：

過濾條件：布爾型表達式

算術操作符：+, -, *, /, %

比較操作符：=, !=, <>（不等于）, <=, >, >=, <, <=

BETWEEN min_num AND max_num

IN (element1, element2, …)

IS NULL

IS NOT NULL

SELECT

?LIKE:

%: 任意長度的任意字符

_：任意單個字符

?RLIKE：正則表達式，索引失效，不建議使用

?REGEXP：匹配字符串可用正則表達式書寫模式，同上

?邏輯操作符：

NOT

AND

XOR ? ? ?異或

SELECT

?GROUP：根據指定的條件把查詢結果進行“分組”以用于做“聚合”運算

avg(), max(), min(), count(), sum()

HAVING: 對分組聚合運算后的結果指定過濾條件

?ORDER BY: 根據指定的字段對查詢結果進行排序

升序：ASC

降序：DESC

?LIMIT [[offset,]row_count]：對查詢的結果進行輸出行數數量限制

?對查詢結果中的數據請求施加“鎖”

FOR UPDATE: 寫鎖，獨占或排它鎖，只有一個讀和寫只能自己讀寫

LOCK IN SHARE MODE: 讀鎖，共享鎖，同時多個讀，只能自己寫，可以多個讀

避免多個用戶同時修改一個數據庫而帶來問題

最簡單的查詢

MariaDB [db1]> select * from user;

+——+———————+———-+

| user | host ? ? ? ? ? ? ? ?| password |

+——+———————+———-+

| root | localhost ? ? ? ? ? | ? ? ? ? ?|

| root | centos7.localdomain | ? ? ? ? ?|

| root | 127.0.0.1 ? ? ? ? ? | ? ? ? ? ?|

| root | ::1 ? ? ? ? ? ? ? ? | ? ? ? ? ?|

| ? ? ?| localhost ? ? ? ? ? | ? ? ? ? ?|

| ? ? ?| centos7.localdomain | ? ? ? ? ?|

MariaDB [db1]> select ‘1+2=’,1+2;

+——+—–+

| 1+2= | 1+2 |

+——+—–+

| 1+2= | ? 3 |

+——+—–+

1 row in set (0.00 sec)

MariaDB [db1]> select ‘1+2=’,1+2 from user; ? ?顯示的行數和user的行一樣類似于awk

+——+—–+

| 1+2= | 1+2 |

+——+—–+

| 1+2= | ? 3 |

+——+—–+

MariaDB [db1]> select ‘number’,user,password from user;

+——–+——+———-+

| number | user | password |

+——–+——+———-+

| number | root | ? ? ? ? ?|

| number | ? ? ?| ? ? ? ? ?|

+——–+——+———-+

select語句查看的順序是數據在磁盤上的存放位置，后插入的記錄的磁盤存放位置不一定就在之前的記錄的后面

精確查找

MariaDB [db1]> select * from user where host=’localhost’;

+——+———–+———-+

| user | host ? ? ?| password |

+——+———–+———-+

| root | localhost | ? ? ? ? ?|

| ? ? ?| localhost | ? ? ? ? ?|

+——+———–+———-+

where后面可以跟多個條件

MariaDB [db1]> select * from user where host=’localhost’and user=’root’;

+——+———–+———-+

| user | host ? ? ?| password |

+——+———–+———-+

| root | localhost | ? ? ? ? ?|

+——+———–+———-+

1 row in set (0.01 sec)

MariaDB [db1]> select * from user where host=’localhost’ or user=’root’;

+——+———————+———-+

| user | host ? ? ? ? ? ? ? ?| password | ? ? ? ?password不是空

+——+———————+———-+

| root | localhost ? ? ? ? ? | ? ? ? ? ?|

| root | centos7.localdomain | ? ? ? ? ?|

| root | 127.0.0.1 ? ? ? ? ? | ? ? ? ? ?|

| root | ::1 ? ? ? ? ? ? ? ? | ? ? ? ? ?|

| ? ? ?| localhost ? ? ? ? ? | ? ? ? ? ?|

+——+———————+———-+

5 rows in set (0.00 sec)

MariaDB [db1]> select * from students where sex is null

? ? -> ;

+—-+——+——-+——+

| id | name | phone | sex ?|

+—-+——+——-+——+

+—-+——+——-+——+

2 rows in set (0.00 sec)

MariaDB [db1]> select * from students where sex is not null;

+—-+——+——-+——+

| id | name | phone | sex ?|

+—-+——+——-+——+

| ?1 | bai ?| 10086 | m ? ?|

| ?2 | liu ?| NULL ?| m ? ?|

| ?3 | lin ?| NULL ?| f ? ?|

+—-+——+——-+——+

3 rows in set (0.00 sec)

MariaDB [db1]> insert students values(5,’wang’,’10000′,’m’);

MariaDB [db1]> select * from students where id>=2 and id<=5;

+—-+——+——-+——+

| id | name | phone | sex ?|

+—-+——+——-+——+

| ?2 | liu ?| NULL ?| m ? ?|

| ?3 | lin ?| NULL ?| f ? ?|

| ?5 | wang | 10000 | m ? ?|

+—-+——+——-+——+

MariaDB [db1]> select * from students where id between 2 and 5;

+—-+——+——-+——+

| id | name | phone | sex ?|

+—-+——+——-+——+

| ?2 | liu ?| NULL ?| m ? ?|

| ?3 | lin ?| NULL ?| f ? ?|

| ?5 | wang | 10000 | m ? ?|

in（’f’,’m’）包含f或者m的

MariaDB [db1]> select * from students where sex in (‘m’,’f’);

+—-+——+——-+——+

| id | name | phone | sex ?|

+—-+——+——-+——+

| ?1 | bai ?| 10086 | m ? ?|

| ?2 | liu ?| NULL ?| m ? ?|

| ?3 | lin ?| NULL ?| f ? ?|

| ?5 | wang | 10000 | m ? ?|

+—-+——+——-+——+

4 rows in set (0.00 sec)

MariaDB [db1]> select * from students where sex in (‘m’,’f’) or sex is null;

+—-+——+——-+——+

| id | name | phone | sex ?|

+—-+——+——-+——+

| ?1 | bai ?| 10086 | m ? ?|

| ?2 | liu ?| NULL ?| m ? ?|

| ?3 | lin ?| NULL ?| f ? ?|

| ?5 | wang | 10000 | m ? ?|

字段名稱加別名

MariaDB [db1]> select id as 編號,name as ?姓名 ?from students where sex in (‘m’,’f’) or sex is null;

+——–+——–+

| 編號 ? | 姓名 ? |

+——–+——–+

| ? ? ?1 | bai ? ?|

| ? ? ?2 | liu ? ?|

| ? ? ?3 | lin ? ?|

| ? ? ?4 | zhao ? |

| ? ? ?5 | wang ? |

| ? ? 70 | wang ? |

+——–+——–+

也可以給表起別名，students表別名是st，在多表中使用

MariaDB [db1]> select id as 編號,name as ?姓名 ?from students as st ?where sex in (‘m’,’f’) or sex is null;

MariaDB [db1]> select st.id as 編號,st.name as ?姓名 ?from students as st ?where sex in (‘m’,’f’) or sex is null;

+——–+——–+

| 編號 ? | 姓名 ? |

+——–+——–+

| ? ? ?1 | bai ? ?|

| ? ? ?2 | liu ? ?|

| ? ? ?3 | lin ? ?|

| ? ? ?4 | zhao ? |

| ? ? ?5 | wang ? |

| ? ? 70 | wang ? |

+——–+——–+

6 rows in set (0.00 sec)

select 模糊查詢

以w開頭的

MariaDB [db1]> select * from students where name like ‘w%’ ; ? %相當于*

+—-+——+——-+——+

| id | name | phone | sex ?|

+—-+——+——-+——+

| ?5 | wang | 10000 | m ? ?|

+—-+——+——-+——+

包含i的

MariaDB [db1]> select * from students where name like ‘%i%’ ;

+—-+——+——-+——+

| id | name | phone | sex ?|

+—-+——+——-+——+

| ?1 | bai ?| 10086 | m ? ?|

| ?2 | liu ?| NULL ?| m ? ?|

| ?3 | lin ?| NULL ?| f ? ?|

+—-+——+——-+——+

MariaDB [db1]> select * from students where name like ‘___’ ; ?_ 代表任意一個字符

+—-+——+——-+——+

| id | name | phone | sex ?|

+—-+——+——-+——+

| ?1 | bai ?| 10086 | m ? ?|

| ?2 | liu ?| NULL ?| m ? ?|

| ?3 | lin ?| NULL ?| f ? ?|

正則表達式查詢

MariaDB [db1]> select * from students where name rlike ‘^l’;

+—-+——+——-+——+

| id | name | phone | sex ?|

+—-+——+——-+——+

| ?2 | liu ?| NULL ?| m ? ?|

| ?3 | lin ?| NULL ?| f ? ?|

+—-+——+——-+——+

MariaDB [db1]> select * from students where sex != ‘m’ ;

+—-+——+——-+——+

| id | name | phone | sex ?|

+—-+——+——-+——+

| ?3 | lin ?| NULL ?| f ? ?|

+—-+——+——-+——+

1 row in set (0.00 sec)

MariaDB [db1]> select * from students where not sex != ‘m’ ;

+—-+——+——-+——+

| id | name | phone | sex ?|

+—-+——+——-+——+

| ?1 | bai ?| 10086 | m ? ?|

| ?2 | liu ?| NULL ?| m ? ?|

| ?5 | wang | 10000 | m ? ?|

+—-+——+——-+——+

在students表中添加一個score字段

MariaDB [db1]> alter table students add score tinyint after phone;

MariaDB [db1]> select * from students

? ? -> ;

+—-+——+——-+——-+——+

+—-+——+——-+——-+——+

| ?1 | bai ?| 10086 | ?NULL | m ? ?|

| ?2 | liu ?| NULL ?| ?NULL | m ? ?|

| ?3 | lin ?| NULL ?| ?NULL | f ? ?|

| ?5 | wang | 10000 | ?NULL | m ? ?|

添加成績

MariaDB [db1]> update students set score=90 where id=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 ?Changed: 1 ?Warnings: 0

MariaDB [db1]> update students set score=80 where id=2;

Query OK, 1 row affected (0.04 sec)

Rows matched: 1 ?Changed: 1 ?Warnings: 0

MariaDB [db1]> update students set score=80 where id=3;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 ?Changed: 1 ?Warnings: 0

MariaDB [db1]> update students set score=88 where id=4;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 ?Changed: 1 ?Warnings: 0

MariaDB [db1]> update students set score=77 where id=5;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 ?Changed: 1 ?Warnings: 0

MariaDB [db1]> delete from students where sex is null ;

Query OK, 2 rows affected (0.00 sec)

MariaDB [db1]> insert students values(4,’zhang’,’110′,60,’f’);

Query OK, 1 row affected (0.00 sec)

MariaDB [db1]> select * from students;

+—-+——-+——-+——-+——+

+—-+——-+——-+——-+——+

| ?1 | bai ? | 10086 | ? ?90 | m ? ?|

| ?2 | liu ? | NULL ?| ? ?80 | m ? ?|

| ?3 | lin ? | NULL ?| ? ?80 | f ? ?|

| ?4 | zhang | 110 ? | ? ?60 | f ? ?|

| ?5 | wang ?| 10000 | ? ?77 | m ? ?|

+—-+——-+——-+——-+——+

5 rows in set (0.00 sec)

MariaDB [db1]> insert students values(6,’huang’,’110′,99,’m’);

按性別分組，統計性別有幾組

MariaDB [db1]> select sex from students group by sex

? ? -> ;

+——+

| sex ?|

+——+

| f ? ?|

| m ? ?|

分組后select后跟的值一般是統計匯總的平均值，總和，最大值，最小值等，或者是統計的字段（sex）

MariaDB [db1]> select sex,max(score) from students group by sex;

+——+————+

| sex ?| max(score) |

+——+————+

| f ? ?| ? ? ? ? 80 |

| m ? ?| ? ? ? ? 99 |

+——+————+

MariaDB [db1]> select sex,max(score) as 最好成績 from students group by sex;

+——+————–+

| sex ?| 最好成績 ? ? |

+——+————–+

| f ? ?| ? ? ? ? ? 80 |

| m ? ?| ? ? ? ? ? 99 |

+——+————–+

2 rows in set (0.00 sec)

MariaDB [db1]> select sex,avg(score) as 平均成績 from students group by sex;

+——+————–+

| sex ?| 平均成績 ? ? |

+——+————–+

| f ? ?| ? ? ?70.0000 |

| m ? ?| ? ? ?86.5000 |

+——+————–+

各個班的男生女生的平均成績

MariaDB [db1]> select sex,avg(score) as 平均成績 from students group by sex,class;

添加新的字段class

MariaDB [db1]> alter table students add class tinyint default 1 ;

id大于等于3的class修改成2

MariaDB [db1]> update students set class=2 where id>=3 ;

Query OK, 4 rows affected (0.00 sec)

Rows matched: 4 ?Changed: 4 ?Warnings: 0

MariaDB [db1]> select * from students;

+—-+——-+——-+——-+——+——-+

+—-+——-+——-+——-+——+——-+

| ?1 | bai ? | 10086 | ? ?90 | m ? ?| ? ? 1 |

| ?2 | liu ? | NULL ?| ? ?80 | m ? ?| ? ? 1 |

| ?3 | lin ? | NULL ?| ? ?80 | f ? ?| ? ? 2 |

| ?4 | zhang | 110 ? | ? ?60 | f ? ?| ? ? 2 |

| ?5 | wang ?| 10000 | ? ?77 | m ? ?| ? ? 2 |

| ?6 | huang | 110 ? | ? ?99 | m ? ?| ? ? 2 |

MariaDB [db1]> insert students values(7,’aaa’,’100′,86,’f’,1);

MariaDB [db1]> select * from students;

+—-+——-+——-+——-+——+——-+

+—-+——-+——-+——-+——+——-+

| ?1 | bai ? | 10086 | ? ?90 | m ? ?| ? ? 1 |

| ?2 | liu ? | NULL ?| ? ?80 | m ? ?| ? ? 1 |

| ?3 | lin ? | NULL ?| ? ?80 | f ? ?| ? ? 2 |

| ?4 | zhang | 110 ? | ? ?60 | f ? ?| ? ? 2 |

| ?5 | wang ?| 10000 | ? ?77 | m ? ?| ? ? 2 |

| ?6 | huang | 110 ? | ? ?99 | m ? ?| ? ? 2 |

| ?7 | aaa ? | 100 ? | ? ?86 | f ? ?| ? ? 1 |

MariaDB [db1]> select class,sex,avg(score) as 平均成績 ? ? from students group by class,sex ;

+——-+——+————–+

| class | sex ?| 平均成績 ? ? |

+——-+——+————–+

| ? ? 1 | f ? ?| ? ? ?86.0000 |

| ? ? 1 | m ? ?| ? ? ?85.0000 |

| ? ? 2 | f ? ?| ? ? ?70.0000 |

| ? ? 2 | m ? ?| ? ? ?88.0000 |

+——-+——+————–+

MariaDB [db1]> select class,sex,count(id) as 各班人數 ? ? from students group by class,sex ;（id）也可以用（*）

+——-+——+————–+

| class | sex ?| 各班人數 ? ? |

+——-+——+————–+

| ? ? 1 | f ? ?| ? ? ? ? ? ?1 |

| ? ? 1 | m ? ?| ? ? ? ? ? ?2 |

| ? ? 2 | f ? ?| ? ? ? ? ? ?2 |

| ? ? 2 | m ? ?| ? ? ? ? ? ?2 |

MariaDB [db1]> select class,sex,avg(score) as 平均成績 ? ? from students group by class,sex having 平均成績>80;

+——-+——+————–+

| class | sex ?| 平均成績 ? ? |

+——-+——+————–+

| ? ? 1 | f ? ?| ? ? ?86.0000 |

| ? ? 1 | m ? ?| ? ? ?85.0000 |

| ? ? 2 | m ? ?| ? ? ?88.0000 |

where在分組前做，having在分組后做

MariaDB [db1]> select class,sex,avg(score) as 平均成績 ? ? from students where class=1 ?group by class,sex having 平均成績>80;

+——-+——+————–+

| class | sex ?| 平均成績 ? ? |

+——-+——+————–+

| ? ? 1 | f ? ?| ? ? ?86.0000 |

| ? ? 1 | m ? ?| ? ? ?85.0000 |

+——-+——+————–+

排序

MariaDB [db1]> select * from students order by score;

正序排序

+—-+——-+——-+——-+——+——-+

+—-+——-+——-+——-+——+——-+

| ?4 | zhang | 110 ? | ? ?60 | f ? ?| ? ? 2 |

| ?5 | wang ?| 10000 | ? ?77 | m ? ?| ? ? 2 |

| ?2 | liu ? | NULL ?| ? ?80 | m ? ?| ? ? 1 |

| ?3 | lin ? | NULL ?| ? ?80 | f ? ?| ? ? 2 |

| ?7 | aaa ? | 100 ? | ? ?86 | f ? ?| ? ? 1 |

| ?1 | bai ? | 10086 | ? ?90 | m ? ?| ? ? 1 |

| ?6 | huang | 110 ? | ? ?99 | m ? ?| ? ? 2 |

倒序排序

MariaDB [db1]> select * from students order by score desc;

+—-+——-+——-+——-+——+——-+

+—-+——-+——-+——-+——+——-+

| ?6 | huang | 110 ? | ? ?99 | m ? ?| ? ? 2 |

| ?1 | bai ? | 10086 | ? ?90 | m ? ?| ? ? 1 |

| ?7 | aaa ? | 100 ? | ? ?86 | f ? ?| ? ? 1 |

| ?2 | liu ? | NULL ?| ? ?80 | m ? ?| ? ? 1 |

| ?3 | lin ? | NULL ?| ? ?80 | f ? ?| ? ? 2 |

| ?5 | wang ?| 10000 | ? ?77 | m ? ?| ? ? 2 |

| ?4 | zhang | 110 ? | ? ?60 | f ? ?| ? ? 2 |

+—-+——-+——-+——-+——+——-+

按電話號碼排序

MariaDB [db1]> select * from students order by phone;

+—-+——-+——-+——-+——+——-+

+—-+——-+——-+——-+——+——-+

| ?2 | liu ? | NULL ?| ? ?80 | m ? ?| ? ? 1 |

| ?3 | lin ? | NULL ?| ? ?80 | f ? ?| ? ? 2 |

| ?7 | aaa ? | 100 ? | ? ?86 | f ? ?| ? ? 1 |

| ?5 | wang ?| 10000 | ? ?77 | m ? ?| ? ? 2 |

| ?1 | bai ? | 10086 | ? ?90 | m ? ?| ? ? 1 |

| ?4 | zhang | 110 ? | ? ?60 | f ? ?| ? ? 2 |

| ?6 | huang | 110 ? | ? ?99 | m ? ?| ? ? 2 |

MariaDB [db1]> select * from students order by phone desc;

+—-+——-+——-+——-+——+——-+

+—-+——-+——-+——-+——+——-+

| ?4 | zhang | 110 ? | ? ?60 | f ? ?| ? ? 2 |

| ?6 | huang | 110 ? | ? ?99 | m ? ?| ? ? 2 |

| ?1 | bai ? | 10086 | ? ?90 | m ? ?| ? ? 1 |

| ?5 | wang ?| 10000 | ? ?77 | m ? ?| ? ? 2 |

| ?7 | aaa ? | 100 ? | ? ?86 | f ? ?| ? ? 1 |

| ?2 | liu ? | NULL ?| ? ?80 | m ? ?| ? ? 1 |

| ?3 | lin ? | NULL ?| ? ?80 | f ? ?| ? ? 2 |

MariaDB [db1]> insert students values (8,’ma’,’123′,null,’f’,2);

Query OK, 1 row affected (0.00 sec)

MariaDB [db1]> select * from students

? ? -> ;

+—-+——-+——-+——-+——+——-+

+—-+——-+——-+——-+——+——-+

| ?1 | bai ? | 10086 | ? ?90 | m ? ?| ? ? 1 |

| ?2 | liu ? | NULL ?| ? ?80 | m ? ?| ? ? 1 |

| ?3 | lin ? | NULL ?| ? ?80 | f ? ?| ? ? 2 |

| ?4 | zhang | 110 ? | ? ?60 | f ? ?| ? ? 2 |

| ?5 | wang ?| 10000 | ? ?77 | m ? ?| ? ? 2 |

| ?6 | huang | 110 ? | ? ?99 | m ? ?| ? ? 2 |

| ?7 | aaa ? | 100 ? | ? ?86 | f ? ?| ? ? 1 |

| ?8 | ma ? ?| 123 ? | ?NULL | f ? ?| ? ? 2 |

MariaDB [db1]> select * from students order by score asc;

+—-+——-+——-+——-+——+——-+

+—-+——-+——-+——-+——+——-+

| ?8 | ma ? ?| 123 ? | ?NULL | f ? ?| ? ? 2 |

| ?4 | zhang | 110 ? | ? ?60 | f ? ?| ? ? 2 |

| ?5 | wang ?| 10000 | ? ?77 | m ? ?| ? ? 2 |

| ?2 | liu ? | NULL ?| ? ?80 | m ? ?| ? ? 1 |

| ?3 | lin ? | NULL ?| ? ?80 | f ? ?| ? ? 2 |

| ?7 | aaa ? | 100 ? | ? ?86 | f ? ?| ? ? 1 |

| ?1 | bai ? | 10086 | ? ?90 | m ? ?| ? ? 1 |

| ?6 | huang | 110 ? | ? ?99 | m ? ?| ? ? 2 |

MariaDB [db1]> select * from students order by -score desc; 這里的-只對數字有效

+—-+——-+——-+——-+——+——-+

+—-+——-+——-+——-+——+——-+

| ?4 | zhang | 110 ? | ? ?60 | f ? ?| ? ? 2 |

| ?5 | wang ?| 10000 | ? ?77 | m ? ?| ? ? 2 |

| ?2 | liu ? | NULL ?| ? ?80 | m ? ?| ? ? 1 |

| ?3 | lin ? | NULL ?| ? ?80 | f ? ?| ? ? 2 |

| ?7 | aaa ? | 100 ? | ? ?86 | f ? ?| ? ? 1 |

| ?1 | bai ? | 10086 | ? ?90 | m ? ?| ? ? 1 |

| ?6 | huang | 110 ? | ? ?99 | m ? ?| ? ? 2 |

| ?8 | ma ? ?| 123 ? | ?NULL | f ? ?| ? ? 2 |

limit 只取前幾個

MariaDB [db1]> select * from students order by score asc limit 3;

+—-+——-+——-+——-+——+——-+

+—-+——-+——-+——-+——+——-+

| ?8 | ma ? ?| 123 ? | ?NULL | f ? ?| ? ? 2 |

| ?4 | zhang | 110 ? | ? ?60 | f ? ?| ? ? 2 |

| ?5 | wang ?| 10000 | ? ?77 | m ? ?| ? ? 2 |

mysql中引入腳本

第一種方法

[root@CENTOS7 ~]# mysql < hellodb_innodb.sql

第二種方法

MariaDB [(none)]> source hellodb_innodb.sql

MariaDB [hellodb]> show databases;

+——————–+

| Database ? ? ? ? ? |

+——————–+

| information_schema |

| db1 ? ? ? ? ? ? ? ?|

| hellodb ? ? ? ? ? ?|

| mysql ? ? ? ? ? ? ?|

| performance_schema |

| test ? ? ? ? ? ? ? |

+——————–+

6 rows in set (0.00 sec)

MariaDB [hellodb]> use hellodb

Database changed

MariaDB [hellodb]> show tables;

+——————-+

| Tables_in_hellodb |

+——————-+

| classes ? ? ? ? ? |

| coc ? ? ? ? ? ? ? |

| courses ? ? ? ? ? |

| scores ? ? ? ? ? ?|

| students ? ? ? ? ?|

| teachers ? ? ? ? ?|

| toc ? ? ? ? ? ? ? |

(6) 以年齡排序后，顯示年齡最大的前10位同學的信息

MariaDB [hellodb]> select * from students order by age desc limit 10 ;

+——-+————–+—–+——–+———+———–+

+——-+————–+—–+——–+———+———–+

| ? ?25 | Sun Dasheng ?| 100 | M ? ? ?| ? ?NULL | ? ? ?NULL |

| ? ? 3 | Xie Yanke ? ?| ?53 | M ? ? ?| ? ? ? 2 | ? ? ? ?16 |

| ? ? 6 | Shi Qing ? ? | ?46 | M ? ? ?| ? ? ? 5 | ? ? ?NULL |

| ? ?13 | Tian Boguang | ?33 | M ? ? ?| ? ? ? 2 | ? ? ?NULL |

| ? ? 4 | Ding Dian ? ?| ?32 | M ? ? ?| ? ? ? 4 | ? ? ? ? 4 |

| ? ?24 | Xu Xian ? ? ?| ?27 | M ? ? ?| ? ?NULL | ? ? ?NULL |

| ? ? 5 | Yu Yutong ? ?| ?26 | M ? ? ?| ? ? ? 3 | ? ? ? ? 1 |

| ? ?17 | Lin Chong ? ?| ?25 | M ? ? ?| ? ? ? 4 | ? ? ?NULL |

| ? ?23 | Ma Chao ? ? ?| ?23 | M ? ? ?| ? ? ? 4 | ? ? ?NULL |

| ? ?18 | Hua Rong ? ? | ?23 | M ? ? ?| ? ? ? 7 | ? ? ?NULL |

示例

? DESC students;

? INSERT INTO students VALUES(1,’tom’，’m’),(2,’alice’,’f’);

? INSERT INTO students(id,name) VALUES(3,’jack’),(4,’allen’);

? SELECT * FROM students WHERE id < 3;

? SELECT * FROM students WHERE gender=’m’;

? SELECT * FROM students WHERE gender IS NULL;

? SELECT * FROM students WHERE gender IS NOT NULL;

? SELECT * FROM students ORDER BY name DESC LIMIT 2;

? SELECT * FROM students ORDER BY name DESC LIMIT 1,2;

? SELECT * FROM students WHERE id >=2 and id <=4

? SELECT * FROM students WHERE BETWEEN 2 AND 4

? SELECT * FROM students WHERE name LIKE ‘t%’

? SELECT * FROM students WHERE name RLIKE ‘.*[lo].*’;

? SELECT id stuid,name as stuname FROM students

練習

?導入hellodb.sql生成數據庫

?(1) 在students表中，查詢年齡大于25歲，且為男性的同學的名字和年齡

?(2) 以ClassID為分組依據，顯示每組的平均年齡

?(3) 顯示第2題中平均年齡大于30的分組及平均年齡

?(4) 顯示以L開頭的名字的同學的信息

?(5) 顯示TeacherID非空的同學的相關信息

?(6) 以年齡排序后，顯示年齡最大的前10位同學的信息

?(7) 查詢年齡大于等于20歲，小于等于25歲的同學的信息

多表訪問

多表查詢：學生對應的老師是誰 ? 兩張表取交集。

MariaDB [hellodb]> select s.name as 學生姓名,t.name as 老師姓名 ? ? from students as s,teachers as t where s.teacherid=t.tid;

+————–+—————+

| 學生姓名 ? ? | 老師姓名 ? ? ?|

+————–+—————+

| Yu Yutong ? ?| Song Jiang ? ?|

| Shi Zhongyu ?| Miejue Shitai |

| Ding Dian ? ?| Lin Chaoying ?|

MariaDB [hellodb]> select s.name as 學生姓名,t.name as 老師姓名 ? ? from students as s inner join teachers as t on s.teacherid=t.tid;

| 學生姓名 ? ? | 老師姓名 ? ? ?|

+————–+—————+

| Yu Yutong ? ?| Song Jiang ? ?|

| Shi Zhongyu ?| Miejue Shitai |

| Ding Dian ? ?| Lin Chaoying ?|

兩張表取交集用inner join ，可以更多張表取交集。

MariaDB [hellodb]> select * from students cross join teachers; ? ? 交叉連接，笛卡爾乘積

+——-+—————+—–+——–+———+———–+—–+—————+—–+——–+

+——-+—————+—–+——–+———+———–+—–+—————+—–+——–+

| ? ? 1 | Shi Zhongyu ? | ?22 | M ? ? ?| ? ? ? 2 | ? ? ? ? 3 | ? 1 | Song Jiang ? ?| ?45 | M ? ? ?|

| ? ? 1 | Shi Zhongyu ? | ?22 | M ? ? ?| ? ? ? 2 | ? ? ? ? 3 | ? 2 | Zhang Sanfeng | ?94 | M ? ? ?|

| ? ? 1 | Shi Zhongyu ? | ?22 | M ? ? ?| ? ? ? 2 | ? ? ? ? 3 | ? 3 | Miejue Shitai | ?77 | F ? ? ?|

| ? ? 1 | Shi Zhongyu ? | ?22 | M ? ? ?| ? ? ? 2 | ? ? ? ? 3 | ? 4 | Lin Chaoying ?| ?93 | F ? ? ?|

| ? ? 2 | Shi Potian ? ?| ?22 | M ? ? ?| ? ? ? 1 | ? ? ? ? 7 | ? 1 | Song Jiang ? ?| ?45 | M ? ? ?|

| ? ? 2 | Shi Potian ? ?| ?22 | M ? ? ?| ? ? ? 1 | ? ? ? ? 7 | ? 2 | Zhang Sanfeng | ?94 | M ? ? ?|

| ? ? 2 | Shi Potian ? ?| ?22 | M ? ? ?| ? ? ? 1 | ? ? ? ? 7 | ? 3 | Miejue Shitai | ?77 | F ? ? ?|

| ? ? 2 | Shi Potian ? ?| ?22 | M ? ? ?| ? ? ? 1 | ? ? ? ? 7 | ? 4 | Lin Chaoying ?| ?93 | F ? ? ?|

什么情況下使用交叉連接；

列如

職員表 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 職位表

id ? ? ?name ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?id ? ? ? ? name

1 ? ? ? a ? ? ? ? ? ? ?1 ? ? ? ? ? ceo

2 ? ? ? ?b

2 ? ? ? ? ? coo

左外連接

MariaDB [hellodb]> select s.name as 學生姓名,t.name as 老師姓名 from students as s left outer join teachers as t on s.teacherid=t.tid;

+—————+—————+

| 學生姓名 ? ? ?| 老師姓名 ? ? ?|

+—————+—————+

| Shi Zhongyu ? | Miejue Shitai |

| Shi Potian ? ?| NULL ? ? ? ? ?|

| Xie Yanke ? ? | NULL ? ? ? ? ?|

| Ding Dian ? ? | Lin Chaoying ?|

| Yu Yutong ? ? | Song Jiang ? ?|

| Shi Qing ? ? ?| NULL ? ? ? ? ?|

右外連接

ariaDB [hellodb]> select s.name as 學生姓名, t.name as 老師姓名 from students as s right outer join teachers as t on s.teacherid=t.tid;

+————–+—————+

| 學生姓名 ? ? | 老師姓名 ? ? ?|

+————–+—————+

| Shi Zhongyu ?| Miejue Shitai |

| Ding Dian ? ?| Lin Chaoying ?|

| Yu Yutong ? ?| Song Jiang ? ?|

| NULL ? ? ? ? | Zhang Sanfeng |

+————–+—————+

MariaDB [hellodb]> select s.name as 學生姓名,t.name as 老師姓名 from students as s left outer join teachers as t on s.teacherid=t.tid where t.name is null;

+—————+————–+

| 學生姓名 ? ? ?| 老師姓名 ? ? |

+—————+————–+

| Shi Potian ? ?| NULL ? ? ? ? |

| Xie Yanke ? ? | NULL ? ? ? ? |

| Shi Qing ? ? ?| NULL ? ? ? ? |

MariaDB [hellodb]> select s.name as 學生姓名, t.name as 老師姓名 from students as s right outer join teachers as t on s.teacherid=t.tid where s.name is null;

+————–+—————+

| 學生姓名 ? ? | 老師姓名 ? ? ?|

+————–+—————+

| NULL ? ? ? ? | Zhang Sanfeng |

+————–+—————+

1 row in set (0.00 sec)

需要union ?來實現 ?union就是將兩張表豎著連接起來。

select union select 前后兩個select的數據格式應該一致

MariaDB [hellodb]> select stuid , name ?from students

? ? -> union

? ? -> select tid,name from teachers;

+——-+—————+

| stuid | name ? ? ? ? ?|

+——-+—————+

| ? ? 1 | Shi Zhongyu ? |

| ? ? 2 | Shi Potian ? ?|

| ? ? 3 | Xie Yanke ? ? |

| ? ? 4 | Ding Dian ? ? |

| ? ? 5 | Yu Yutong ? ? |

| ? ? 6 | Shi Qing ? ? ?|

| ? ? 7 | Xi Ren ? ? ? ?|

| ? ? 8 | Lin Daiyu ? ? |

| ? ? 9 | Ren Yingying ?|

| ? ?10 | Yue Lingshan ?|

| ? ?11 | Yuan Chengzhi |

| ? ?12 | Wen Qingqing ?|

| ? ?13 | Tian Boguang ?|

| ? ?14 | Lu Wushuang ? |

| ? ?15 | Duan Yu ? ? ? |

| ? ?16 | Xu Zhu ? ? ? ?|

| ? ?17 | Lin Chong ? ? |

| ? ?18 | Hua Rong ? ? ?|

| ? ?19 | Xue Baochai ? |

| ? ?20 | Diao Chan ? ? |

| ? ?21 | Huang Yueying |

| ? ?22 | Xiao Qiao ? ? |

| ? ?23 | Ma Chao ? ? ? |

| ? ?24 | Xu Xian ? ? ? |

| ? ?25 | Sun Dasheng ? |

| ? ? 1 | Song Jiang ? ?|

| ? ? 2 | Zhang Sanfeng |

| ? ? 3 | Miejue Shitai |

| ? ? 4 | Lin Chaoying ?|

MariaDB [hellodb]> select s.name as 學生姓名,t.name as 老師姓名 from students as s left outer join teachers as t on s.teacherid=t.tid

? ? -> union

? ? -> select s.name as 學生成績,t.name as 老師姓名 from students as s right outer join teachers as t on s.teacherid=t.tid;

+—————+—————+

| 學生姓名 ? ? ?| 老師姓名 ? ? ?|

+—————+—————+

| Shi Zhongyu ? | Miejue Shitai |

| Shi Potian ? ?| NULL ? ? ? ? ?|

| Xie Yanke ? ? | NULL ? ? ? ? ?|

| Ding Dian ? ? | Lin Chaoying ?|

| Yu Yutong ? ? | Song Jiang ? ?|

| Shi Qing ? ? ?| NULL ? ? ? ? ?|

| Xi Ren ? ? ? ?| NULL ? ? ? ? ?|

| Lin Daiyu ? ? | NULL ? ? ? ? ?|

| Ren Yingying ?| NULL ? ? ? ? ?|

| Yue Lingshan ?| NULL ? ? ? ? ?|

| Yuan Chengzhi | NULL ? ? ? ? ?|

| Wen Qingqing ?| NULL ? ? ? ? ?|

| Tian Boguang ?| NULL ? ? ? ? ?|

| Lu Wushuang ? | NULL ? ? ? ? ?|

| Duan Yu ? ? ? | NULL ? ? ? ? ?|

| Xu Zhu ? ? ? ?| NULL ? ? ? ? ?|

| Lin Chong ? ? | NULL ? ? ? ? ?|

| Hua Rong ? ? ?| NULL ? ? ? ? ?|

| Xue Baochai ? | NULL ? ? ? ? ?|

| Diao Chan ? ? | NULL ? ? ? ? ?|

| Huang Yueying | NULL ? ? ? ? ?|

| Xiao Qiao ? ? | NULL ? ? ? ? ?|

| Ma Chao ? ? ? | NULL ? ? ? ? ?|

| Xu Xian ? ? ? | NULL ? ? ? ? ?|

| Sun Dasheng ? | NULL ? ? ? ? ?|

| NULL ? ? ? ? ?| Zhang Sanfeng |

+—————+—————+

MariaDB [hellodb]> select s.name as 學生姓名,t.name as 老師姓名 from students as s left outer join teachers as t on s.teacherid=t.tid where t.name is null

? ? -> union

? ? -> select s.name as 學生姓名, t.name as 老師姓名 from students as s right outer join teachers as t on s.teacherid=t.tid where s.name is null;

還可以寫成

MariaDB [hellodb]> select * from (select s.stuid,s.name as studentname,t.tid,t.name as teachername from students as s left outer join teachers as t on s.teacherid=t.tid union select s.stuid,s.name,t.tid,t.name from students as s right outer join teachers as t on s.teacherid=t.tid) as a ?where a.stuid is null or a.tid is null;

+—————+—————+

| 學生姓名 ? ? ?| 老師姓名 ? ? ?|

+—————+—————+

| Shi Potian ? ?| NULL ? ? ? ? ?|

| Xie Yanke ? ? | NULL ? ? ? ? ?|

| Shi Qing ? ? ?| NULL ? ? ? ? ?|

| Xi Ren ? ? ? ?| NULL ? ? ? ? ?|

| Lin Daiyu ? ? | NULL ? ? ? ? ?|

| Ren Yingying ?| NULL ? ? ? ? ?|

| Yue Lingshan ?| NULL ? ? ? ? ?|

| Yuan Chengzhi | NULL ? ? ? ? ?|

| Wen Qingqing ?| NULL ? ? ? ? ?|

| Tian Boguang ?| NULL ? ? ? ? ?|

| Lu Wushuang ? | NULL ? ? ? ? ?|

| Duan Yu ? ? ? | NULL ? ? ? ? ?|

| Xu Zhu ? ? ? ?| NULL ? ? ? ? ?|

| Lin Chong ? ? | NULL ? ? ? ? ?|

| Hua Rong ? ? ?| NULL ? ? ? ? ?|

| Xue Baochai ? | NULL ? ? ? ? ?|

| Diao Chan ? ? | NULL ? ? ? ? ?|

| Huang Yueying | NULL ? ? ? ? ?|

| Xiao Qiao ? ? | NULL ? ? ? ? ?|

| Ma Chao ? ? ? | NULL ? ? ? ? ?|

| Xu Xian ? ? ? | NULL ? ? ? ? ?|

| Sun Dasheng ? | NULL ? ? ? ? ?|

| NULL ? ? ? ? ?| Zhang Sanfeng |

+—————+—————+

自內連接

MariaDB [hellodb]> select * from students

? ? -> ;

+——-+—————+—–+——–+———+———–+

+——-+—————+—–+——–+———+———–+

| ? ? 1 | Shi Zhongyu ? | ?22 | M ? ? ?| ? ? ? 2 | ? ? ? ? 3 |

| ? ? 2 | Shi Potian ? ?| ?22 | M ? ? ?| ? ? ? 1 | ? ? ? ? 7 |

| ? ? 3 | Xie Yanke ? ? | ?53 | M ? ? ?| ? ? ? 2 | ? ? ? ?16 |

| ? ? 4 | Ding Dian ? ? | ?32 | M ? ? ?| ? ? ? 4 | ? ? ? ? 4 |

| ? ? 5 | Yu Yutong ? ? | ?26 | M ? ? ?| ? ? ? 3 | ? ? ? ? 1 |

| ? ? 6 | Shi Qing ? ? ?| ?46 | M ? ? ?| ? ? ? 5 | ? ? ?NULL |

| ? ? 7 | Xi Ren ? ? ? ?| ?19 | F ? ? ?| ? ? ? 3 | ? ? ?NULL |

| ? ? 8 | Lin Daiyu ? ? | ?17 | F ? ? ?| ? ? ? 7 | ? ? ?NULL |

| ? ? 9 | Ren Yingying ?| ?20 | F ? ? ?| ? ? ? 6 | ? ? ?NULL |

| ? ?10 | Yue Lingshan ?| ?19 | F ? ? ?| ? ? ? 3 | ? ? ?NULL |

| ? ?11 | Yuan Chengzhi | ?23 | M ? ? ?| ? ? ? 6 | ? ? ?NULL |

| ? ?12 | Wen Qingqing ?| ?19 | F ? ? ?| ? ? ? 1 | ? ? ?NULL |

| ? ?13 | Tian Boguang ?| ?33 | M ? ? ?| ? ? ? 2 | ? ? ?NULL |

| ? ?14 | Lu Wushuang ? | ?17 | F ? ? ?| ? ? ? 3 | ? ? ?NULL |

| ? ?15 | Duan Yu ? ? ? | ?19 | M ? ? ?| ? ? ? 4 | ? ? ?NULL |

| ? ?16 | Xu Zhu ? ? ? ?| ?21 | M ? ? ?| ? ? ? 1 | ? ? ?NULL |

| ? ?17 | Lin Chong ? ? | ?25 | M ? ? ?| ? ? ? 4 | ? ? ?NULL |

| ? ?18 | Hua Rong ? ? ?| ?23 | M ? ? ?| ? ? ? 7 | ? ? ?NULL |

| ? ?19 | Xue Baochai ? | ?18 | F ? ? ?| ? ? ? 6 | ? ? ?NULL |

| ? ?20 | Diao Chan ? ? | ?19 | F ? ? ?| ? ? ? 7 | ? ? ?NULL |

| ? ?21 | Huang Yueying | ?22 | F ? ? ?| ? ? ? 6 | ? ? ?NULL |

| ? ?22 | Xiao Qiao ? ? | ?20 | F ? ? ?| ? ? ? 1 | ? ? ?NULL |

| ? ?23 | Ma Chao ? ? ? | ?23 | M ? ? ?| ? ? ? 4 | ? ? ?NULL |

| ? ?24 | Xu Xian ? ? ? | ?27 | M ? ? ?| ? ?NULL | ? ? ?NULL |

| ? ?25 | Sun Dasheng ? | 100 | M ? ? ?| ? ?NULL | ? ? ?NULL |

+——-+—————+—–+——–+———+———–+

25 rows in set (0.00 sec)

MariaDB [hellodb]> select s1.name as 雇員,s2.name as 領導 from students as s1 inner join students as s2 on s1.stuid=s2.teacherid;

+————-+————-+

| 雇員 ? ? ? ?| 領導 ? ? ? ?|

+————-+————-+

| Xie Yanke ? | Shi Zhongyu |

| Xi Ren ? ? ?| Shi Potian ?|

| Xu Zhu ? ? ?| Xie Yanke ? |

| Ding Dian ? | Ding Dian ? |

| Shi Zhongyu | Yu Yutong ? |

+————-+————-+

5 rows in set (0.00 sec)

MariaDB [hellodb]> select s1.name as 雇員,s2.name as 領導 from students as s1 inner join students as s2 on s2.stuid=s1.teacherid;

+————-+————-+

| 雇員 ? ? ? ?| 領導 ? ? ? ?|

+————-+————-+

| Shi Zhongyu | Xie Yanke ? |

| Shi Potian ?| Xi Ren ? ? ?|

| Xie Yanke ? | Xu Zhu ? ? ?|

| Ding Dian ? | Ding Dian ? |

| Yu Yutong ? | Shi Zhongyu |

+————-+————-+

5 rows in set (0.00 sec)

MariaDB [hellodb]> select s1.name as 雇員,s2.name as 領導 from students as s1 left outer join students as s2 on s2.stuid=s1.teacherid;

+—————+————-+

| 雇員 ? ? ? ? ?| 領導 ? ? ? ?|

+—————+————-+

| Shi Zhongyu ? | Xie Yanke ? |

| Shi Potian ? ?| Xi Ren ? ? ?|

| Xie Yanke ? ? | Xu Zhu ? ? ?|

| Ding Dian ? ? | Ding Dian ? |

| Yu Yutong ? ? | Shi Zhongyu |

| Shi Qing ? ? ?| NULL ? ? ? ?|

| Xi Ren ? ? ? ?| NULL ? ? ? ?|

| Lin Daiyu ? ? | NULL ? ? ? ?|

| Ren Yingying ?| NULL ? ? ? ?|

| Yue Lingshan ?| NULL ? ? ? ?|

| Yuan Chengzhi | NULL ? ? ? ?|

| Wen Qingqing ?| NULL ? ? ? ?|

| Tian Boguang ?| NULL ? ? ? ?|

| Lu Wushuang ? | NULL ? ? ? ?|

| Duan Yu ? ? ? | NULL ? ? ? ?|

| Xu Zhu ? ? ? ?| NULL ? ? ? ?|

| Lin Chong ? ? | NULL ? ? ? ?|

| Hua Rong ? ? ?| NULL ? ? ? ?|

| Xue Baochai ? | NULL ? ? ? ?|

| Diao Chan ? ? | NULL ? ? ? ?|

| Huang Yueying | NULL ? ? ? ?|

| Xiao Qiao ? ? | NULL ? ? ? ?|

| Ma Chao ? ? ? | NULL ? ? ? ?|

| Xu Xian ? ? ? | NULL ? ? ? ?|

| Sun Dasheng ? | NULL ? ? ? ?|

+—————+————-+

子查詢：查詢語句嵌套查詢語句

MariaDB [hellodb]> select * from scores;

+—-+——-+———-+——-+

+—-+——-+———-+——-+

| ?1 | ? ? 1 | ? ? ? ?2 | ? ?77 |

| ?2 | ? ? 1 | ? ? ? ?6 | ? ?93 |

| ?3 | ? ? 2 | ? ? ? ?2 | ? ?47 |

| ?4 | ? ? 2 | ? ? ? ?5 | ? ?97 |

| ?5 | ? ? 3 | ? ? ? ?2 | ? ?88 |

| ?6 | ? ? 3 | ? ? ? ?6 | ? ?75 |

| ?7 | ? ? 4 | ? ? ? ?5 | ? ?71 |

| ?8 | ? ? 4 | ? ? ? ?2 | ? ?89 |

| ?9 | ? ? 5 | ? ? ? ?1 | ? ?39 |

| 10 | ? ? 5 | ? ? ? ?7 | ? ?63 |

| 11 | ? ? 6 | ? ? ? ?1 | ? ?96 |

| 12 | ? ? 7 | ? ? ? ?1 | ? ?86 |

| 13 | ? ? 7 | ? ? ? ?7 | ? ?83 |

| 14 | ? ? 8 | ? ? ? ?4 | ? ?57 |

| 15 | ? ? 8 | ? ? ? ?3 | ? ?93 |

+—-+——-+———-+——-+

MariaDB [hellodb]> select avg(score) from scores;

+————+

| avg(score) |

+————+

| ? ?76.9333 |

+————+

1 row in set (0.00 sec)

MariaDB [hellodb]> select stuid,score from scores where score > (select avg(score) from scores);

+——-+——-+

| stuid | score |

+——-+——-+

| ? ? 1 | ? ?77 |

| ? ? 1 | ? ?93 |

| ? ? 2 | ? ?97 |

| ? ? 3 | ? ?88 |

| ? ? 4 | ? ?89 |

| ? ? 6 | ? ?96 |

| ? ? 7 | ? ?86 |

| ? ? 7 | ? ?83 |

| ? ? 8 | ? ?93 |

MariaDB [hellodb]> select st.name,sc.score from students as st inner join scores as sc on st.stuid=sc.stuid;

+————-+——-+

| name ? ? ? ?| score |

+————-+——-+

| Shi Zhongyu | ? ?77 |

| Shi Zhongyu | ? ?93 |

| Shi Potian ?| ? ?47 |

| Shi Potian ?| ? ?97 |

| Xie Yanke ? | ? ?88 |

| Xie Yanke ? | ? ?75 |

| Ding Dian ? | ? ?71 |

| Ding Dian ? | ? ?89 |

| Yu Yutong ? | ? ?39 |

| Yu Yutong ? | ? ?63 |

| Shi Qing ? ?| ? ?96 |

| Xi Ren ? ? ?| ? ?86 |

| Xi Ren ? ? ?| ? ?83 |

| Lin Daiyu ? | ? ?57 |

| Lin Daiyu ? | ? ?93 |

MariaDB [hellodb]> select st.name,sc.score from students as st inner join scores as sc on st.stuid=sc.stuid where score > (select avg(score) from scores);

+————-+——-+

| name ? ? ? ?| score |

+————-+——-+

| Shi Zhongyu | ? ?77 |

| Shi Zhongyu | ? ?93 |

| Shi Potian ?| ? ?97 |

| Xie Yanke ? | ? ?88 |

| Ding Dian ? | ? ?89 |

| Shi Qing ? ?| ? ?96 |

| Xi Ren ? ? ?| ? ?86 |

| Xi Ren ? ? ?| ? ?83 |

| Lin Daiyu ? | ? ?93 |

+————-+——-+

多表查詢

?交叉連接：笛卡爾乘積

?內連接：

等值連接：讓表之間的字段以“等值”建立連接關系；

不等值連接

自然連接:去掉重復列的等值連接

自連接

?外連接：

左外連接：

FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col

右外連接

FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col

多表查詢

?子查詢：在查詢語句嵌套著查詢語句，性能較差

基于某語句的查詢結果再次進行的查詢

?用在WHERE子句中的子查詢：

? 用于比較表達式中的子查詢；子查詢僅能返回單個值

SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM students);

? 用于IN中的子查詢：子查詢應該單鍵查詢并返回一個或多個值從構成列表

SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);

? 用于EXISTS

多表查詢

?用于FROM子句中的子查詢

使用格式：SELECT tb_alias.col1,… FROM (SELECT clause) AS tb_alias

WHERE Clause;

示例：

SELECT s.aage,s.ClassID FROM (SELECT avg(Age) AS aage,ClassID

FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID) AS s

WHERE s.aage>30;

?聯合查詢：UNION

SELECT Name,Age FROM students UNION SELECT Name,Age FROM

teachers;

select 語句的執行順序

練習

? 導入hellodb.sql，以下操作在students表上執行

? 1、以ClassID分組，顯示每班的同學的人數

? 2、以Gender分組，顯示其年齡之和

? 3、以ClassID分組，顯示其平均年齡大于25的班級

? 4、以Gender分組，顯示各組中年齡大于25的學員的年齡之和MariaDB [hellodb]> select gender, MariaDB [hellodb]> select gender, sum(age) from students where age > 25 group by gender ?;

+——–+———-+

| gender | sum(age) |

+——–+———-+

| M ? ? ?| ? ? ?317 |

+——–+———-+

MariaDB [hellodb]> select gender, sum(age) from students where age > 18 group by gender ?;

+——–+———-+

| gender | sum(age) |

+——–+———-+

| F ? ? ?| ? ? ?138 |

| M ? ? ?| ? ? ?495 |

+——–+———-+

這個題無法使用select gender, sum(age) from students group by gender having age > 18 ;因為having后面只能跟 gender 和匯總的數據，別的不行。

? 5、顯示前5位同學的姓名、課程及成績

? 6、顯示其成績高于80的同學的名稱及課程；

? 7、求前8位同學每位同學自己兩門課的平均成績，并按降序排列

? 8、顯示每門課程課程名稱及學習了這門課的同學的個數

? 9、如何顯示其年齡大于平均年齡的同學的名字

? 10、如何顯示其學習的課程為第1、2，4或第7門課的同學的名字

? 11、如何顯示其成員數最少為3個的班級的同學中年齡大于同班同學平均年齡的同學

? 12、統計各班級中年齡大于全校同學平均年齡的同學

MariaDB [hellodb]> select avg(age) from students;

+———-+

| avg(age) |

+———-+

| ?27.4000 |

+———-+

MariaDB [hellodb]> select name,classid,age from students where age > (select avg(age) from students);

+————–+———+—–+

| name ? ? ? ? | classid | age |

+————–+———+—–+

| Xie Yanke ? ?| ? ? ? 2 | ?53 |

| Ding Dian ? ?| ? ? ? 4 | ?32 |

| Shi Qing ? ? | ? ? ? 5 | ?46 |

| Tian Boguang | ? ? ? 2 | ?33 |

| Sun Dasheng ?| ? ?NULL | 100 |

+————–+———+—–+

視圖 ? ? 是一種虛擬的表，只是一種查詢結果，并不存放在磁盤上，類似于shall中的別名。

?視圖：VIEW,虛表，保存有實表的查詢結果

?創建方法：

CREATE VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

?查看視圖定義：SHOW CREATE VIEW view_name

?刪除視圖：

DROP VIEW [IF EXISTS]

view_name [, view_name] …

[RESTRICT | CASCADE] ? ?慎用

?視圖中的數據事實上存儲于“基表”中，因此，其修改操作也會針對基表實現；

其修改操作受基表限制

定義一個視圖

MariaDB [hellodb]> create view wiew_students as select stuid,name from students;

Query OK, 0 rows affected (0.00 sec)

使用視圖

MariaDB [hellodb]> select * from wiew_students;

MariaDB [hellodb]> select name from wiew_students;

+—————+

| name ? ? ? ? ?|

+—————+

| Shi Zhongyu ? |

| Shi Potian ? ?|

| Xie Yanke ? ? |

| Ding Dian ? ? |

MariaDB [hellodb]> desc wiew_students;

+——-+——————+——+—–+———+——-+

+——-+——————+——+—–+———+——-+

| stuid | int(10) unsigned | NO ? | ? ? | 0 ? ? ? | ? ? ? |

| name ?| varchar(50) ? ? ?| NO ? | ? ? | NULL ? ?| ? ? ? |

MariaDB [hellodb]> show table status like ‘wiew_students’\G

*************************** 1. row ***************************

? ? ? ? ? ?Name: wiew_students

? ? ? ? ? ?Comment: VIEW

定義時的命令

MariaDB [hellodb]> show create view wiew_students\G

*************************** 1. row ***************************

? ? ? ? ? ? ? ? View: wiew_students

? ? ? ? ?Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `wiew_students` AS select `students`.`StuID` AS `stuid`,`students`.`Name` AS `name` from `students`

character_set_client: utf8

collation_connection: utf8_general_ci

用視圖可以將保存在物理磁盤的數據隱藏起來，只給用戶看想給用戶看的信息，還能將很長的命令定義成視圖（類似別名）來減少工作量。

修改視圖的數據內容，實際上就是修改了students的數據內容

MariaDB [hellodb]> update wiew_students set name=’qtds’ where stuid=25;

Query OK, 1 row affected (0.04 sec)

Rows matched: 1 ?Changed: 1 ?Warnings: 0

MariaDB [hellodb]> select * from wiew_students;

? ?25 | qtds

MariaDB [hellodb]> select * from students;

?25 | qtds ? ? ? ? ?| 100 | M ? ? ?| ? ?NULL | ? ? ?NULL |

MariaDB [hellodb]> create view view_oldstudents as select * from students where age > ’50’ ;

MariaDB [hellodb]> select * from view_oldstudents;

+——-+———–+—–+——–+———+———–+

+——-+———–+—–+——–+———+———–+

| ? ? 3 | Xie Yanke | ?53 | M ? ? ?| ? ? ? 2 | ? ? ? ?16 |

| ? ?25 | qtds ? ? ?| 100 | M ? ? ?| ? ?NULL | ? ? ?NULL |

+——-+———–+—–+——–+———+———–+

MariaDB [hellodb]> update view_oldstudents set age=48 ;

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2 ?Changed: 2 ?Warnings: 0

MariaDB [hellodb]> select * from view_oldstudents;

Empty set (0.00 sec)

MariaDB [hellodb]> select * from students;

? ?3 | Xie Yanke ? ? | ?48 | M ? ? ?| ? ? ? 2 | ? ? ? ?16

25 | qtds ? ? ? ? ?| ?48 | M ? ? ?| ? ?NULL | ? ? ?NULL

物化視圖：就是將視圖物理化，這時的視圖就是存放在磁盤中的

刪除視圖，不會影響原來的數據。

MariaDB [hellodb]> drop view view_oldstudents;

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> show tables;

+——————-+

| Tables_in_hellodb |

+——————-+

| classes ? ? ? ? ? |

| coc ? ? ? ? ? ? ? |

| courses ? ? ? ? ? |

| scores ? ? ? ? ? ?|

| students ? ? ? ? ?|

| teachers ? ? ? ? ?|

| toc ? ? ? ? ? ? ? |

| wiew_students ? ? |

函數

?函數：系統函數和自定義函數

系統函數 :https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

?自定義函數 (user-defined function UDF)

? 保存在mysql.proc表中

? 創建UDF:

CREATE [AGGREGATE] FUNCTION function_name(parameter_name

type,[parameter_name type,…])

RETURNS {STRING|INTEGER|REAL}

runtime_body

? 說明：

參數可以有多個,也可以沒有參數

必須有且只有一個返回值

系統自帶的函數

MariaDB [hellodb]> select count(*) from students;

+———-+

| count(*) |

+———-+

| ? ? ? 25 |

mysql中的函數是不能單獨使用的，必須嵌入到sql語句中使用。

定義一個函數

MariaDB [hellodb]> create function si() returns varchar(20) return “hello world”;

Query OK, 0 rows affected (0.03 sec)

使用這個函數

MariaDB [hellodb]> select si();

+————-+

| si() ? ? ? ?|

+————-+

| hello world |

查看現有的自定義函數

MariaDB [hellodb]> show function status\G

*************************** 1. row ***************************

? ? ? ? ? ? ? ? ? Db: hellodb

? ? ? ? ? ? ? ? Name: si

? ? ? ? ? ? ? ? Type: FUNCTION

? ? ? ? ? ? ?Definer: root@localhost

? ? ? ? ? ? Modified: 2018-06-07 18:53:13

? ? ? ? ? ? ?Created: 2018-06-07 18:53:13

? ? ? ?Security_type: DEFINER

? ? ? ? ? ? ?Comment:

character_set_client: utf8

collation_connection: utf8_general_ci

? Database Collation: utf8_general_ci

自定義函數

?示例:無參UDF

CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN “Hello

World!“;

?示例：有參數UDF

DELIMITER //

CREATE FUNCTION deleteById(uid SMALLINT UNSIGNED) RETURNS VARCHAR(20)

BEGIN

DELETE FROM students WHERE stuid = uid;

RETURN (SELECT COUNT(uid) FROM students);

END//

DELIMITER ;

自定義函數

? 自定義函數中定義局部變量語法:

? DECLARE 變量1[,變量2,… ]變量類型 [DEFAULT 默認值]

? 說明：局部變量的作用范圍是在BEGIN…END程序中,而且定義局部變量語句必須在

BEGIN…END的第一行定義

? 示例:

DELIMITER //

CREATE FUNCTION addTwoNumber(x SMALLINT UNSIGNED, Y SMALLINT UNSIGNED)

RETURNS SMALLINT

BEGIN

DECLARE a, b SMALLINT UNSIGNED DEFAULT 10;

SET a = x, b = y;

RETURN a+b;

END//

自定義函數

?為變量賦值語法

? SET parameter_name = value[,parameter_name = value…]

? SELECT INTO parameter_name

?示例:

…

DECLARE x int;

SELECT COUNT(id) FROM tdb_name INTO x;

RETURN x;

END//

MariaDB [hellodb]> delimiter // ? 這時表示命令結束就變成了//，而不再是；

MariaDB [hellodb]> create function asum(x smallint unsigned,y smallint unsigned) x，y是形參

? ? -> returns smallint

? ? -> begin

? ? -> declare a,b smallint unsigned default 10; a，b是實參。

? ? -> set a = x,b = y;

? ? -> return a+b;

? ? -> end //

MariaDB [hellodb]> delimiter ;

MariaDB [hellodb]> select asum(10,20);

+————-+

| asum(10,20) |

+————-+

| ? ? ? ? ?30 |

+————-+

1 row in set (0.00 sec)

MariaDB [hellodb]> show function status\G

*************************** 1. row ***************************

? ? ? ? ? ? ? ? ? Db: hellodb

? ? ? ? ? ? ? ? Name: asum

? ? ? ? ? ? ? ? Type: FUNCTION

? ? ? ? ? ? ?Definer: root@localhost

? ? ? ? ? ? Modified: 2018-06-07 19:11:12

? ? ? ? ? ? ?Created: 2018-06-07 19:11:12

? ? ? ?Security_type: DEFINER

? ? ? ? ? ? ?Comment:

character_set_client: utf8

collation_connection: utf8_general_ci

? Database Collation: utf8_general_ci

*************************** 2. row ***************************

? ? ? ? ? ? ? ? ? Db: hellodb

? ? ? ? ? ? ? ? Name: si

? ? ? ? ? ? ? ? Type: FUNCTION

? ? ? ? ? ? ?Definer: root@localhost

? ? ? ? ? ? Modified: 2018-06-07 18:53:13

? ? ? ? ? ? ?Created: 2018-06-07 18:53:13

? ? ? ?Security_type: DEFINER

? ? ? ? ? ? ?Comment:

character_set_client: utf8

collation_connection: utf8_general_ci

? Database Collation: utf8_general_ci

刪除函數

MariaDB [hellodb]> drop function si;

這時只剩一個自定義函數了

MariaDB [hellodb]> show function status\G

*************************** 1. row ***************************

? ? ? ? ? ? ? ? ? Db: hellodb

? ? ? ? ? ? ? ? Name: asum

? ? ? ? ? ? ? ? Type: FUNCTION

? ? ? ? ? ? ?Definer: root@localhost

? ? ? ? ? ? Modified: 2018-06-07 19:11:12

? ? ? ? ? ? ?Created: 2018-06-07 19:11:12

? ? ? ?Security_type: DEFINER

? ? ? ? ? ? ?Comment:

character_set_client: utf8

collation_connection: utf8_general_ci

? Database Collation: utf8_general_ci

存儲過程

?存儲過程：存儲過程保存在mysql.proc表中

?創建存儲過程

CREATE PROCEDURE sp_name ([ proc_parameter [,proc_parameter …]])

routime_body

其中:proc_parameter : [IN|OUT|INOUT] parameter_name type

其中IN表示輸入參數，OUT表示輸出參數，INOUT表示既可以輸入也可以輸出；

param_name表示參數名稱；type表示參數的類型

?查看存儲過程列表

SHOW PROCEDURE STATUS

自定義的函數也保存在mysql.proc數據庫中。

MariaDB [hellodb]> select * from mysql.proc\G

*************************** 1. row ***************************

? ? ? ? ? ? ? ? ? db: hellodb

? ? ? ? ? ? ? ? name: asum

? ? ? ? ? ? ? ? type: FUNCTION

? ? ? ?specific_name: asum

? ? ? ? ? ? language: SQL

? ? ?sql_data_access: CONTAINS_SQL

? ? is_deterministic: NO

? ? ? ?security_type: DEFINER

? ? ? ? ? param_list: x smallint unsigned,y smallint unsigned

? ? ? ? ? ? ?returns: smallint(6)

? ? ? ? ? ? ? ? body: begin

declare a,b smallint unsigned default 10;

set a = x,b = y;

return a+b;

end

? ? ? ? ? ? ?definer: root@localhost

? ? ? ? ? ? ?created: 2018-06-07 19:11:12

? ? ? ? ? ? modified: 2018-06-07 19:11:12

? ? ? ? ? ? sql_mode:

? ? ? ? ? ? ?comment:

character_set_client: utf8

collation_connection: utf8_general_ci

? ? ? ? db_collation: utf8_general_ci

? ? ? ? ? ?body_utf8: begin

declare a,b smallint unsigned default 10;

set a = x,b = y;

return a+b;

end

MariaDB [hellodb]> show databases;

+——————–+

| Database ? ? ? ? ? |

+——————–+

| information_schema |

| db1 ? ? ? ? ? ? ? ?|

| hellodb ? ? ? ? ? ?|

| mysql ? ? ? ? ? ? ?| ? ?系統數據庫，將來備份數據時不光備份用戶數據庫也應備份系統數據庫。

| performance_schema |

| test ? ? ? ? ? ? ? |

MariaDB [hellodb]> select now();

+———————+

| now() ? ? ? ? ? ? ? |

+———————+

| 2018-06-07 19:23:03 |

存儲過程

?查看存儲過程定義

SHOW CREATE PROCEDURE sp_name

?調用存儲過程:

CALL sp_name ([ proc_parameter [,proc_parameter …]])

CALL sp_name

說明:當無參時,可以省略”()”,當有參數時,不可省略”()”

?存儲過程修改:

ALTER語句修改存儲過程只能修改存儲過程的注釋等無關緊要的東西,不能修改

存儲過程體,所以要修改存儲過程,方法就是刪除重建

?刪除存儲過程:

DROP PROCEDURE [IF EXISTS] sp_name

存儲過程示例

?創建無參存儲過程:

delimiter //

CREATE PROCEDURE showTime()

BEGIN

SELECT now();

END//

delimiter ;

CALL showTime;

MariaDB [hellodb]> delimiter //

MariaDB [hellodb]> create procedure showtime()

? ? -> begin

? ? -> select now();

? ? -> end //

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> delimiter ;

MariaDB [hellodb]> call showtime;

+———————+

| now() ? ? ? ? ? ? ? |

+———————+

| 2018-06-07 19:25:30 |

存儲過程示例

?創建含參存儲過程:只有一個IN參數

delimiter //

CREATE PROCEDURE seleById(IN id SMALLINT UNSIGNED)

BEGIN

SELECT * FROM students WHERE stuid = uid;

END//

delimiter ;

call seleById(2);

存儲過程示例

?示例

delimiter //

CREATE PROCEDURE dorepeat(p1 INT)

BEGIN

SET @x = 0;

REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; ? ? repeat相當于for循環

END

delimiter ;

CALL dorepeat(1000);

SELECT @x;

存儲過程示例

?創建含參存儲過程:包含IN參數和OUT參數

delimiter //

CREATE PROCEDURE deleteById(IN id SMALLINT UNSIGNED, OUT num SMALLINT UNSIGNED)

BEGIN

DELETE FROM students WHERE stuid = id;

SELETE row_count() into num; ? ? ? ?row_count()系統自帶函數，顯示刪除的信息。

END//

delimiter ;

call seleById(2,@Line);

SELETE @Line;

?說明:創建存儲過程deleteById,包含一個IN參數和一個OUT參數.調用時,傳入刪

除的ID和保存被修改的行數值的用戶變量@Line,select @Line;輸出被影響行數

存儲過程

?存儲過程優勢:

?存儲過程把經常使用的SQL語句或業務邏輯封裝起來,預編譯保存在數據庫中,

當需要時從數據庫中直接調用,省去了編譯的過程

?提高了運行速度

?同時降低網絡數據傳輸量

?存儲過程與自定義函數的區別:

?存儲過程實現的過程要復雜一些,而函數的針對性較強

?存儲過程可以有多個返回值,而自定義函數只有一個返回值

?存儲過程一般獨立的來執行,而函數往往是作為其他SQL語句的一部分來使用

流程控制

?存儲過程和函數中可以使用流程控制來控制語句的執行

?流程控制：

? IF：用來進行條件判斷。根據是否滿足條件，執行不同語句

? CASE：用來進行條件判斷，可實現比IF語句更復雜的條件判斷

? LOOP：重復執行特定的語句，實現一個簡單的循環

? LEAVE：用于跳出循環控制

? ITERATE：跳出本次循環，然后直接進入下一次循環

? REPEAT：有條件控制的循環語句。當滿足特定條件時，就會跳出循環語句

? WHILE：有條件控制的循環語句

觸發器

? 觸發器的執行不是由程序調用，也不是由手工啟動，而是由事件來觸發、激活從而實現執行

? 創建觸發器

CREATE

[DEFINER = { user | CURRENT_USER }]

TRIGGER trigger_name

trigger_time trigger_event

ON tbl_name FOR EACH ROW

trigger_body

? 說明：

trigger_name：觸發器的名稱

trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后觸發

trigger_event:：{ INSERT |UPDATE | DELETE }，觸發的具體事件

tbl_name：該觸發器作用在表名

查詢表不需要觸發器，修改才需要觸發器。

觸發器示例

CREATE TABLE student_info (

stu_no INT(11) NOT NULL AUTO_INCREMENT,

stu_name VARCHAR(255) DEFAULT NULL,

PRIMARY KEY (stu_no)

);

CREATE TABLE student_count (

student_count INT(11) DEFAULT 0

);

INSERT INTO student_count VALUES(0);

示例：創建觸發器，在向學生表INSERT數據時，學生數增加，DELETE學生時，

學生數減少

CREATE TRIGGER trigger_student_count_insert

AFTER INSERT

ON student_info FOR EACH ROW

UPDATE student_count SET student_count=student_count+1;

CREATE TRIGGER trigger_student_count_delete

AFTER DELETE

ON student_info FOR EACH ROW

UPDATE student_count SET student_count=student_count-1;

觸發器

?查看觸發器

SHOW TRIGGERS

查詢系統表information_schema.triggers的方式指定查詢條件，查看指定的觸發

器信息。

mysql> USE information_schema;

Database changed

mysql> SELECT * FROM triggers WHERE

trigger_name=’trigger_student_count_insert’;

?刪除觸發器

DROP TRIGGER trigger_name;

MySQL用戶和權限管理

?元數據數據庫：mysql

系統授權表：

db, host, user

columns_priv, tables_priv, procs_priv, proxies_priv

?用戶賬號：

‘USERNAME’@’HOST’：

@’HOST’:

主機名；

IP地址或Network;

通配符：

%, _: 172.16.%.%

用戶管理

?創建用戶：CREATE USER

CREATE USER ‘USERNAME’@’HOST’ [IDENTIFIED BY ‘password’]；

默認權限：USAGE ? ?只能連接數據庫，沒有別的權限

?用戶重命名：RENAME USER

RENAME USER old_user_name TO new_user_name

?刪除用戶：

DROP USER ‘USERNAME’@’HOST‘

示例：刪除默認的空用戶

DROP USER ”@’localhost’;

創建一個wang用戶，并制定口令是centos

MariaDB [mysql]> create user ‘wang’@’192.168.30.100’ identified by ‘centos’;

Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> select user,host,password from user;

?wang | 192.168.30.100 ? ? ?| *128977E278358FF80A246B5046F51043A2B1FCED |

刷新權限

MariaDB [mysql]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

[root@CENTOS7 ~]# mysql -uwang -pcentos -h 192.168.30.101

MariaDB [(none)]> status;

Connection: ?192.168.30.101 via TCP/IP

查看當前用戶名

MariaDB [(none)]> select user();

+———————+

| user() ? ? ? ? ? ? ?|

+———————+

| wang@192.168.30.100 |

MariaDB [mysql]> show processlist;

+—-+——+———————-+——-+———+——+——-+——————+———-+

+—-+——+———————-+——-+———+——+——-+——————+———-+

| ?9 | wang | 192.168.30.100:57698 | NULL ?| Sleep ? | ? 56 | ? ? ? | NULL ? ? ? ? ? ? | ? ?0.000 |

MariaDB [(none)]> use test;

Database changed

MariaDB [test]>

MariaDB [mysql]> show processlist;

+—-+——+———————-+——-+———+——+——-+——————+———-+

+—-+——+———————-+——-+———+——+——-+——————+———-+

| ?9 | wang | 192.168.30.100:57698 | test ?| Sleep ? | ? ?4 | ? ? ? | NULL ? ? ? ? ? ? | ? ?0.000 |

限制用戶只能在192.168.30.100的機器上登錄wang賬號

[root@CENTOS7 ~]# mysql -uwang -pcentos -h192.168.30.101

ERROR 1130 (HY000): Host ‘192.168.30.101’ is not allowed to connect to this MariaDB server

MariaDB [(none)]> use mysql

刪除用戶信息。

MariaDB [mysql]> drop user ”@’localhost’;

MariaDB [mysql]> select user,host,password from mysql.user;

可以一次刪除多行

MariaDB [mysql]> delete from user where user=”;

匿名用戶已經不存在。

MariaDB [mysql]> select user,host,password from mysql.user;

+——+———————+——————————————-+

| user | host ? ? ? ? ? ? ? ?| password ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?|

+——+———————+——————————————-+

| root | localhost ? ? ? ? ? | ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |

| root | centos7.localdomain | ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |

| root | 127.0.0.1 ? ? ? ? ? | ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |

| root | ::1 ? ? ? ? ? ? ? ? | ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |

| wang | 192.168.30.100 ? ? ?| *128977E278358FF80A246B5046F51043A2B1FCED |

刷新一下權限，迫使其立即生效。

MariaDB [mysql]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

用戶管理

?修改密碼：

?mysql>SET PASSWORD FOR ‘user’@’host’ = PASSWORD(‘password’);

? mysql>UPDATE mysql.user SET password=PASSWORD(‘your_password’) WHERE clause;

此方法需要執行下面指令才能生效：mysql> FLUSH PRIVILEGES;

? #mysqladmin -u root –poldpass password ‘newpass‘

?忘記管理員密碼的解決辦法：

?啟動mysqld進程時，為其使用如下選項：

–skip-grant-tables –skip-networking

?使用UPDATE命令修改管理員密碼

?關閉mysqld進程，移除上述兩個選項，重啟mysqld

為用戶設置口令。

MariaDB [mysql]> set password for root@’::1’=password(‘centos’);

MariaDB [mysql]> select user,host,password from user;

root | ::1 ? ? ? ? ? ? ? ? | *128977E278358FF80A246B5046F51043A2B1FCED

MariaDB [mysql]> update user set password=password(‘centos’) where password=”;

Query OK, 3 rows affected (0.00 sec)

Rows matched: 3 ?Changed: 3 ?Warnings: 0

MariaDB [mysql]> select user,host,password from user;

+——+———————+——————————————-+

| user | host ? ? ? ? ? ? ? ?| password ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?|

+——+———————+——————————————-+

| root | localhost ? ? ? ? ? | *128977E278358FF80A246B5046F51043A2B1FCED |

| root | centos7.localdomain | *128977E278358FF80A246B5046F51043A2B1FCED |

| root | 127.0.0.1 ? ? ? ? ? | *128977E278358FF80A246B5046F51043A2B1FCED |

| root | ::1 ? ? ? ? ? ? ? ? | *128977E278358FF80A246B5046F51043A2B1FCED |

| wang | 192.168.30.100 ? ? ?| *128977E278358FF80A246B5046F51043A2B1FCED

刷新策略

MariaDB [(none)]> flush privileges;

shell命令，查看mysqladmin用法。

[root@CENTOS7 ~]# mysqladmin –help

測試數據庫是什么狀態。

[root@CENTOS7 ~]# mysqladmin -uroot -pcentos ping

mysqld is alive

刪除數據庫db1.

[root@CENTOS7 ~]# mysqladmin -uroot -pcentos drop db1

Dropping the database is potentially a very bad thing to do.

Any data stored in the database will be destroyed.

Do you really want to drop the ‘db1’ database [y/N] y

Database “db1” dropped

db1數據庫已經被刪除。

[root@CENTOS7 ~]# mysql -uroot -pcentos -e ‘show databases’

+——————–+

| Database ? ? ? ? ? |

+——————–+

| information_schema |

| hellodb ? ? ? ? ? ?|

| mysql ? ? ? ? ? ? ?|

| performance_schema |

| test ? ? ? ? ? ? ? |

一但忘記數據庫賬號的密碼如何破解：

如果是一個剛剛創建裝好mariadb，那么可以停掉數據庫，將/var/lib/mysql/中的文件全部刪除就可以了。

cd /var/lib/mysql

rm -rf mysql/*

所有的用戶賬號就丟失了，如果無法啟動mariadb，那么可以rm -rf * ? ? ? ? ? ?啟動mariadb就行。

修改口令

[root@CENTOS7 ~]# mysqladmin -uroot -pcentos password ‘magedu’

對沒有口令的賬號設置口令

[root@CENTOS7 ~]# mysqladmin password ‘centos’

[root@CENTOS7 ~]# mysql -uroot -pcentos

如果忘記口令

[root@CENTOS7 ~]# vim /etc/my.cnf

[mysqld]

skip-grant-tables ? ? 忽略授權表的意思

[root@CENTOS7 ~]# systemctl restart mariadb 重啟服務后可以直接登陸

MariaDB [(none)]> use mysql

MariaDB [mysql]> update user set password=password(‘centos’) where user=’root’ and host=’localhost’;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 ?Changed: 1 ?Warnings: 0

MariaDB [mysql]> select user,host,password from user;

+——+———————+——————————————-+

| user | host ? ? ? ? ? ? ? ?| password ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?|

+——+———————+——————————————-+

| root | localhost ? ? ? ? ? | *128977E278358FF80A246B5046F51043A2B1FCED |

[root@CENTOS7 ~]# vim /etc/my.cnf

[mysqld]

#skip-grant-tables

[root@CENTOS7 ~]# systemctl restart mariadb

到此已經破解mysql口令。

[root@CENTOS7 ~]# mysql -uroot -pcentos

Welcome to the MariaDB monitor. ?Commands end with ; or \g.

Your MariaDB connection id is 2

Server version: 5.5.56-MariaDB MariaDB Server

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

[root@CENTOS7 ~]# scp -r /var/lib/mysql/hellodb/ 192.168.30.100 /var/lib/mysql

[root@CENTOS7 ~]# ll /var/lib/mysql

drwx——. 2 root ?root ? ? ? 171 Jun ?7 21:04 hellodb

[root@CENTOS7 ~]# chown -R mysql.mysql /var/lib/mysql/hellodb/

[root@CENTOS7 ~]# ll /var/lib/mysql

drwx——. 2 mysql mysql ? ? ?171 Jun ?7 21:04 hellodb

傳數據的時候不光要把文件夾傳過去mariadb10版本之前的版本數據庫的數據放在下面文件中

[root@CENTOS7 ~]# ll /var/lib/mysql/

-rw-rw—- 1 mysql mysql 18874368 Jun ?7 20:59 ibdata1

[root@CENTOS7 ~]# scp /var/lib/mysql/ibdata1 192.168.30.100:/var/lib/mysql/

[root@CENTOS7 ~]# systemctl restart mariadb

MariaDB [(none)]> use hellodb

MariaDB [hellodb]> show tables;

+——————-+

| Tables_in_hellodb |

+——————-+

| classes ? ? ? ? ? |

| coc ? ? ? ? ? ? ? |

| courses ? ? ? ? ? |

| scores ? ? ? ? ? ?|

| students ? ? ? ? ?|

| teachers ? ? ? ? ?|

| toc ? ? ? ? ? ? ? |

| wiew_students ? ? |

已經可以使用拷過來的數據。

MariaDB [hellodb]> select * from coc;

+—-+———+———-+

| ID | ClassID | CourseID |

+—-+———+———-+

| ?1 | ? ? ? 1 | ? ? ? ?2 |

| ?2 | ? ? ? 1 | ? ? ? ?5 |

| ?3 | ? ? ? 2 | ? ? ? ?2 |

| ?4 | ? ? ? 2 | ? ? ? ?6 |

| ?5 | ? ? ? 3 | ? ? ? ?1 |

| ?6 | ? ? ? 3 | ? ? ? ?7 |

| ?7 | ? ? ? 4 | ? ? ? ?5 |

| ?8 | ? ? ? 4 | ? ? ? ?2 |

| ?9 | ? ? ? 5 | ? ? ? ?1 |

| 10 | ? ? ? 5 | ? ? ? ?9 |

| 11 | ? ? ? 6 | ? ? ? ?3 |

| 12 | ? ? ? 6 | ? ? ? ?4 |

| 13 | ? ? ? 7 | ? ? ? ?4 |

| 14 | ? ? ? 7 | ? ? ? ?3 |

MariaDB [hellodb]> select st.stuid,st.name,sc.courseid from students as st inner join scores as sc on st.stuid=sc.stuid;

+——-+————-+———-+

| stuid | name ? ? ? ?| courseid |

+——-+————-+———-+

| ? ? 1 | Shi Zhongyu | ? ? ? ?2 |

| ? ? 1 | Shi Zhongyu | ? ? ? ?6 |

| ? ? 2 | Shi Potian ?| ? ? ? ?2 |

| ? ? 2 | Shi Potian ?| ? ? ? ?5 |

| ? ? 3 | Xie Yanke ? | ? ? ? ?2 |

| ? ? 3 | Xie Yanke ? | ? ? ? ?6 |

| ? ? 4 | Ding Dian ? | ? ? ? ?5 |

| ? ? 4 | Ding Dian ? | ? ? ? ?2 |

| ? ? 5 | Yu Yutong ? | ? ? ? ?1 |

| ? ? 5 | Yu Yutong ? | ? ? ? ?7 |

| ? ? 6 | Shi Qing ? ?| ? ? ? ?1 |

| ? ? 7 | Xi Ren ? ? ?| ? ? ? ?1 |

| ? ? 7 | Xi Ren ? ? ?| ? ? ? ?7 |

| ? ? 8 | Lin Daiyu ? | ? ? ? ?4 |

| ? ? 8 | Lin Daiyu ? | ? ? ? ?3 |

3張表相互連接

MariaDB [hellodb]> select st.stuid,st.name,sc.courseid,co.course from students as st inner join scores as sc on st.stuid=sc.stuid inner join courses as co on sc.courseid=co.courseid;

+——-+————-+———-+—————-+

+——-+————-+———-+—————-+

| ? ? 1 | Shi Zhongyu | ? ? ? ?2 | Kuihua Baodian |

| ? ? 1 | Shi Zhongyu | ? ? ? ?6 | Weituo Zhang ? |

| ? ? 2 | Shi Potian ?| ? ? ? ?2 | Kuihua Baodian |

| ? ? 2 | Shi Potian ?| ? ? ? ?5 | Daiyu Zanghua ?|

| ? ? 3 | Xie Yanke ? | ? ? ? ?2 | Kuihua Baodian |

| ? ? 3 | Xie Yanke ? | ? ? ? ?6 | Weituo Zhang ? |

| ? ? 4 | Ding Dian ? | ? ? ? ?5 | Daiyu Zanghua ?|

| ? ? 4 | Ding Dian ? | ? ? ? ?2 | Kuihua Baodian |

| ? ? 5 | Yu Yutong ? | ? ? ? ?1 | Hamo Gong ? ? ?|

| ? ? 5 | Yu Yutong ? | ? ? ? ?7 | Dagou Bangfa ? |

| ? ? 6 | Shi Qing ? ?| ? ? ? ?1 | Hamo Gong ? ? ?|

| ? ? 7 | Xi Ren ? ? ?| ? ? ? ?1 | Hamo Gong ? ? ?|

| ? ? 7 | Xi Ren ? ? ?| ? ? ? ?7 | Dagou Bangfa ? |

| ? ? 8 | Lin Daiyu ? | ? ? ? ?4 | Taiji Quan ? ? |

| ? ? 8 | Lin Daiyu ? | ? ? ? ?3 | Jinshe Jianfa ?|

MySQL權限管理

?權限類別：

數據庫級別

表級別

字段級別

管理類

程序類 ? ? 函數，存儲過程，觸發器都是程序累得。

MySQL用戶和權限管理

?管理類：

CREATE TEMPORARY TABLES ? 創建臨時表

CREATE USER ? ?創建用戶

FILE

SUPER

SHOW DATABASES ? ? ?查看數據庫

RELOAD

SHUTDOWN ? ? 停止mariadb

REPLICATION SLAVE ? ? 集群復制

REPLICATION CLIENT

LOCK TABLES

PROCESS

MySQL用戶和權限管理

? 程序類： FUNCTION、PROCEDURE、TRIGGER

CREATE

ALTER

DROP

EXCUTE

? 庫和表級別：DATABASE、TABLE

ALTER

CREATE

CREATE VIEW

DROP

INDEX

SHOW VIEW

GRANT OPTION：能將自己獲得的權限轉贈給其他用戶

MySQL用戶和權限管理

?數據操作：

SELECT

INSERT

DELETE

UPDATE

?字段級別：

SELECT(col1,col2,…)

UPDATE(col1,col2,…)

INSERT(col1,col2,…)

?所有權限：ALL PRIVILEGES 或 ALL

授權

? 參考：https://dev.mysql.com/doc/refman/5.7/en/grant.html

? GRANT priv_type [(column_list)],… ON [object_type] priv_level TO ‘user’@’host’

[IDENTIFIED BY ‘password’] [WITH GRANT OPTION];

? priv_type: ALL [PRIVILEGES]

? object_type:TABLE | FUNCTION | PROCEDURE

db_name.routine_name(指定庫的函數,存儲過程,觸發器)

? with_option: ?GRANT OPTION

| MAX_QUERIES_PER_HOUR count

| MAX_UPDATES_PER_HOUR count

| MAX_CONNECTIONS_PER_HOUR count

| MAX_USER_CONNECTIONS count

示例：GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO ‘someuser’@’somehost’;

創建用戶

MariaDB [hellodb]> create user wang@’192.168.30.%’ identified by ‘centos’;

創建用戶并直接賦予權限，

MariaDB [hellodb]> grant all on hellodb.* to wang@’192.168.%.%’ identified by ‘magedu’;

MariaDB [hellodb]> select user,host,password from mysql.user;

wang | 192.168.30.100 ? ? ?| *128977E278358FF80A246B5046F51043A2B1FCED |

| wang | 192.168.30.% ? ? ? ?| *128977E278358FF80A246B5046F51043A2B1FCED |

| wang | 192.168.%.% ? ? ? ? | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664

刪除用戶wang@’192.168.30.100‘

MariaDB [hellodb]> drop user wang@192.168.30.100;

刪除用戶wang@‘192.168.30.%’

MariaDB [hellodb]> drop user wang@’192.168.30.%’;

MariaDB [hellodb]> select user,host,password from mysql.user;

+——+———————+——————————————-+

| user | host ? ? ? ? ? ? ? ?| password ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?|

+——+———————+——————————————-+

| root | localhost ? ? ? ? ? | *128977E278358FF80A246B5046F51043A2B1FCED |

| root | centos7.localdomain | *128977E278358FF80A246B5046F51043A2B1FCED |

| root | 127.0.0.1 ? ? ? ? ? | *128977E278358FF80A246B5046F51043A2B1FCED |

| root | ::1 ? ? ? ? ? ? ? ? | *128977E278358FF80A246B5046F51043A2B1FCED |

| wang | 192.168.%.% ? ? ? ? | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 |

MariaDB [hellodb]> grant select,insert on hellodb.* to mage@’%’ identified by ‘centos’;

MariaDB [hellodb]> select user,host,password from mysql.user;

?mage | % ? ? ? ? ? ? ? ? ? | *128977E278358FF80A246B5046F51043A2B1FCED

[root@CENTOS7 ~]# mysql -umage -pcentos -h192.168.30.101

因為沒有權限所以不能刪除。

MariaDB [hellodb]> delete from students;

ERROR 1142 (42000): DELETE command denied to user ‘mage’@’192.168.30.100’ for table ‘students’

新建laowang用戶并授權只能查看students表中的age和name。

MariaDB [hellodb]> grant select(age,name) on hellodb.students to laowang@’%’ identified by ‘centos’;

[root@CENTOS7 ~]# mysql -ulaowang -pcentos -h192.168.30.101

MariaDB [(none)]> show databases;

+——————–+

| Database ? ? ? ? ? |

+——————–+

| information_schema |

| hellodb ? ? ? ? ? ?|

| test ? ? ? ? ? ? ? |

MariaDB [hellodb]> show tables;

+——————-+

| Tables_in_hellodb |

+——————-+

| students ? ? ? ? ?|

MariaDB [hellodb]> select * from students;

ERROR 1143 (42000): SELECT command denied to user ‘laowang’@’192.168.30.100’ for column ‘StuID’ in table ‘students’

MariaDB [hellodb]> select name,age from students;

+—————+—–+

| name ? ? ? ? ?| age |

+—————+—–+

| Shi Zhongyu ? | ?22 |

| Shi Potian ? ?| ?22 |

MariaDB [(none)]> show grants for wang@’192.168.%.%’;

+—————————————————————————————————————+

| Grants for wang@192.168.%.% ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |

+—————————————————————————————————————+

| GRANT USAGE ON *.* TO ‘wang’@’192.168.%.%’ IDENTIFIED BY PASSWORD ‘*6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664’ |

| GRANT ALL PRIVILEGES ON `hellodb`.* TO ‘wang’@’192.168.%.%’

取消wang@192.168.%.% 的delete權限

MariaDB [(none)]> revoke delete on hellodb.* from wang@’192.168.%.%’;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show grants for wang@’192.168.%.%’;

+———————————————————————————————————————————————————————————————————————————–+

| Grants for wang@192.168.%.% ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |

| GRANT USAGE ON *.* TO ‘wang’@’192.168.%.%’ IDENTIFIED BY PASSWORD ‘*6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664’ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |加上delete就是all的權限。

| GRANT SELECT, INSERT, UPDATE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `hellodb`.* TO ‘wang’@’192.168.%.%’

查看當前登錄的賬號的權限。

MariaDB [(none)]> show grants for current_user();

+—————————————————————————————————————————————-+

| Grants for root@localhost ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?|

+—————————————————————————————————————————————-+

| GRANT ALL PRIVILEGES ON *.* TO ‘root’@’localhost’ IDENTIFIED BY PASSWORD ‘*128977E278358FF80A246B5046F51043A2B1FCED’ WITH GRANT OPTION |

| GRANT PROXY ON ”@” TO ‘root’@’localhost’ WITH GRANT OPTION

這些授權放在什么地方呢？

其中列的授權放在columns_priv ，表權限存放在tables_priv中。

MariaDB [(none)]> show databases;

+——————–+

| Database ? ? ? ? ? |

+——————–+

| information_schema |

| hellodb ? ? ? ? ? ?|

| mysql ? ? ? ? ? ? ?|

MariaDB [(none)]> use mysql

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

MariaDB [mysql]> show tables;

+—————————+

| Tables_in_mysql ? ? ? ? ? |

+—————————+

| columns_priv ? ? ? ? ? ? ?|

| db ? ? ? ? ? ? ? ? ? ? ? ?|

| event ? ? ? ? ? ? ? ? ? ? |

| func ? ? ? ? ? ? ? ? ? ? ?|

| general_log ? ? ? ? ? ? ? |

| help_category ? ? ? ? ? ? |

| help_keyword ? ? ? ? ? ? ?|

| help_relation ? ? ? ? ? ? |

| help_topic ? ? ? ? ? ? ? ?|

| host ? ? ? ? ? ? ? ? ? ? ?|

| ndb_binlog_index ? ? ? ? ?|

| plugin ? ? ? ? ? ? ? ? ? ?|

| proc ? ? ? ? ? ? ? ? ? ? ?|

| procs_priv ? ? ? ? ? ? ? ?|

| proxies_priv ? ? ? ? ? ? ?|

| servers ? ? ? ? ? ? ? ? ? |

| slow_log ? ? ? ? ? ? ? ? ?|

| tables_priv ? ? ? ? ? ? ? |

| time_zone ? ? ? ? ? ? ? ? |

| time_zone_leap_second ? ? |

| time_zone_name ? ? ? ? ? ?|

| time_zone_transition ? ? ?|

| time_zone_transition_type |

| user ? ? ? ? ? ? ? ? ? ? ?|

+—————————+

24 rows in set (0.00 sec)

MariaDB [mysql]> select * from columns_priv ? ?;

+——+———+———+————+————-+———————+————-+

+——+———+———+————+————-+———————+————-+

| % ? ?| hellodb | laowang | students ? | age ? ? ? ? | 2018-06-08 20:48:20 | Select ? ? ?|

+——+———+———+————+————-+———————+————-+

2 rows in set (0.01 sec)

MariaDB [mysql]> select * from columns_priv\G

*************************** 1. row ***************************

? ? ? ?Host: %

? ? ? ? ?Db: hellodb

? ? ? ?User: laowang

?Table_name: students

Column_name: age

? Timestamp: 2018-06-08 20:48:20

Column_priv: Select

*************************** 2. row ***************************

? ? ? ?Host: %

? ? ? ? ?Db: hellodb

? ? ? ?User: laowang

?Table_name: students

Column_name: name

? Timestamp: 2018-06-08 20:48:20

Column_priv: Select

2 rows in set (0.00 sec)

表權限存放的地方

MariaDB [mysql]> select * from tables_priv\G

*************************** 1. row ***************************

? ? ? ?Host: %

? ? ? ? ?Db: hellodb

? ? ? ?User: laowang

?Table_name: students

? ? Grantor: root@localhost

? Timestamp: 2018-06-08 20:48:20

?Table_priv:

Column_priv: Select

這些都存放在musql數據庫中將來備份數據的時候應該將mysql數據庫也同時備份。也可以直接通過修改表的方法添加權限用insert語句

授權

? 回收授權：REVOKE priv_type [(column_list)] [, priv_type

[(column_list)]] … ON [object_type] priv_level FROM user [, user] …

示例：REVOKE DELETE ON testdb.* FROM ‘testuser’@’%‘

? 查看指定用戶獲得的授權：

Help SHOW GRANTS

SHOW GRANTS FOR ‘user’@’host’;

SHOW GRANTS FOR CURRENT_USER[()];

? 注意：MariaDB服務進程啟動時會讀取mysql庫中所有授權表至內存

(1) GRANT或REVOKE等執行權限操作會保存于系統表中，MariaDB的服務進程通常會自

動重讀授權表，使之生效

(2) 對于不能夠或不能及時重讀授權表的命令，可手動讓MariaDB的服務進程重讀授權

表：

mysql> FLUSH PRIVILEGES;

用戶通過連接模塊連接mysql，每連接一個用戶就要占用一個線程，mysql有一個線程池。

transactions （事務）

locking granulanty 鎖級別，如果倆個人同時修改一張表在myisam中實現不了。在innodb可以。表級鎖，影響迸發訪問量

MVCC：多版本迸發控制機制，多個用戶同時修改數據而不產生影響

students表 ? ?后兩個列是系統自動添加的創建版本，刪除版本

id ? ?name ?createverion ? ? deleteversion

1 ? ? ? ?a ? ? ? 事務的編號1 ? ? ? ? ? ? ? ?5

2 ? ? ? ? b ? ? ? ? ? ? ? ?2 ? ? ? ? ? ? ? 事務的編號2

3 ? ? ? ? c ? ? ? ? ? ? ? ?3 ? ? ? ? ? ? ? ? ? ? ? ?3

4 ? ? ? ? ?d ? ? ? ? ? ? ? ?6 ? ? ? ? ? ? ? ? ? ? ? ?4

事務4只能看到1,3，因為4還沒有發生2已經被刪除

full-text search indexes 全文索引

clustered indexes ?聚簇索引。

data caches 數據緩存

froreign key support ? 外鍵

查看系統支持的存儲引擎

MariaDB [(none)]> show engines\G

MariaDB的特性

?插件式存儲引擎：也稱為“表類型”，存儲管理器有多種實現版本，功能和特

性可能均略有差別；用戶可根據需要靈活選擇,Mysql5.5.5開始innoDB引擎是

MYSQL默認引擎

MyISAM ==> Aria

InnoDB ==> XtraDB

存儲引擎比較：

https://docs.oracle.com/cd/E17952_01/mysql-5.5-en/storage-

engines.html

?單進程，多線程

?諸多擴展和新特性

?提供了較多測試組件

?開源

存儲引擎

?MyISAM引擎特點：

?不支持事務

?表級鎖定

?讀寫相互阻塞，寫入不能讀，讀時不能寫

?只緩存索引

?不支持外鍵約束

?不支持聚簇索引

?讀取數據較快，占用資源較少

?不支持MVCC（多版本并發控制機制）高并發

?崩潰恢復性較差

?MySQL5.5.5前默認的數據庫引擎

存儲引擎

?適用場景：只讀（或者寫較少）、表較?。梢越邮荛L時間進行修復操作）

?MyISAM引擎文件：

tbl_name.frm: 表格式定義

tbl_name.MYD: 數據文件

tbl_name.MYI: 索引文件

存儲引擎是和表相關的而不是和庫相關的，創建表的時候需要寫存儲引擎

存儲引擎

?InnoDB引擎特點：

?支持事務，適合處理大量短期事務

?行級鎖

?讀寫阻塞與事務隔離級別相關

?可緩存數據和索引

?支持聚簇索引

?崩潰恢復性更好

?支持MVCC高并發

?從MySQL5.5后支持全文索引

?從MySQL5.5.5開始為默認的數據庫引擎

存儲引擎

?InnoDB數據庫文件

?所有InnoDB表的數據和索引放置于同一個表空間中

表空間文件：datadir定義的目錄下

數據文件：ibddata1, ibddata2, …

?每個表單獨使用一個表空間存儲表的數據和索引

啟用：innodb_file_per_table=ON

兩類文件放在數據庫獨立目錄中

數據文件(存儲數據和索引)：tb_name.ibd

表格式定義：tb_name.frm

每個表單獨使用一個表空間存儲表的數據和索引

[root@centos7 ~]# vim /etc/my.cnf

[mysqld]

innodb_file_per_table=ON

[root@centos7 ~]# vim hellodb_innodb.sql

[root@centos7 ~]# mysql -uroot -pcentos < hellodb_innodb.sql

[root@centos7 ~]# ll /var/lib/mysql/hellodb2

total 1432

-rw-rw—- 1 mysql mysql ?8636 Jun ?9 14:10 classes.frm

-rw-rw—- 1 mysql mysql 98304 Jun ?9 14:10 classes.ibd

-rw-rw—- 1 mysql mysql ?8630 Jun ?9 14:10 coc.frm

-rw-rw—- 1 mysql mysql 98304 Jun ?9 14:10 coc.ibd

查看innodb_file_per_table的狀態

MariaDB [(none)]> show variables like ‘innodb_file%’;

+————————–+———-+

| Variable_name ? ? ? ? ? ?| Value ? ?|

+————————–+———-+

| innodb_file_format ? ? ? | Antelope |

| innodb_file_format_check | ON ? ? ? |

| innodb_file_format_max ? | Antelope |

| innodb_file_per_table ? ?| ON ? ? ? |

其它存儲引擎

?Performance_Schema:Performance_Schema數據庫

?Memory ：將所有數據存儲在RAM中，以便在需要快速查找參考和其他類似

數據的環境中進行快速訪問。適用存放臨時數據。引擎以前被稱為HEAP引擎

?MRG_MyISAM：使MySQL DBA或開發人員能夠對一系列相同的MyISAM表

進行邏輯分組，并將它們作為一個對象引用。適用于VLDB(Very Large Data

Base)環境，如數據倉庫

?Archive ：為存儲和檢索大量很少參考的存檔或安全審核信息，只支持

SELECT和INSERT操作；支持行級鎖和專用緩存區

?Federated聯合：用于訪問其它遠程MySQL服務器一個代理，它通過創建一

個到遠程MySQL服務器的客戶端連接，并將查詢傳輸到遠程服務器執行，而

后完成數據存取，提供鏈接單獨MySQL服務器的能力，以便從多個物理服務

器創建一個邏輯數據庫。非常適合分布式或數據集市環境

其它數據庫引擎

?BDB：可替代InnoDB的事務引擎，支持COMMIT、ROLLBACK和其他事務特性

?Cluster/NDB：MySQL的簇式數據庫引擎，尤其適合于具有高性能查找要求的

應用程序，這類查找需求還要求具有最高的正常工作時間和可用性

?CSV：CSV存儲引擎使用逗號分隔值格式將數據存儲在文本文件中。可以使用

CSV引擎以CSV格式導入和導出其他軟件和應用程序之間的數據交換

?BLACKHOLE ：黑洞存儲引擎接受但不存儲數據，檢索總是返回一個空集。該功

能可用于分布式數據庫設計，數據自動復制，但不是本地存儲

?example：“stub”引擎，它什么都不做?？梢允褂么艘鎰摻ū?，但不能將數

據存儲在其中或從中檢索。目的是作為例子來說明如何開始編寫新的存儲引擎

數據自動復制，從服務器應該使用BLACKHOLE ：黑洞存儲引擎

其它存儲引擎

?MariaDB支持的其它存儲引擎：

?OQGraph

?SphinxSE

?TokuDB

?Cassandra

?CONNECT

?SQUENCE

管理存儲引擎

?查看mysql支持的存儲引擎:

show engines;

?查看當前默認的存儲引擎:

show variables like ‘%storage_engine%’;

?設置默認的存儲引擎：

vim /etc/my.conf

[mysqld]

default_storage_engine= InnoDB;

管理存儲引擎

?查看庫中所有表使用的存儲引擎

Show table status from db_name;

?查看庫中指定表的存儲引擎

show table status like ‘tb_name‘;

show create table tb_name;

?設置表的存儲引擎：

CREATE TABLE tb_name(… ) ENGINE=InnoDB;

ALTER TABLE tb_name ENGINE=InnoDB;

MySQL中的系統數據庫

?mysql數據庫：是mysql的核心數據庫，類似于sql server中的master庫，主要

負責存儲數據庫的用戶、權限設置、關鍵字等mysql自己需要使用的控制和管理

信息

?PERFORMANCE_SCHEMA:MySQL 5.5開始新增的數據庫，主要用于收集數據

庫服務器性能參數,庫里表的存儲引擎均為PERFORMANCE_SCHEMA，用戶不

能創建存儲引擎為PERFORMANCE_SCHEMA的表

?information_schema數據庫:MySQL 5.0之后產生的，一個虛擬數據庫，物理

上并不存在。information_schema數據庫類似與“數據字典”，提供了訪問數

據庫元數據的方式，即數據的數據。比如數據庫名或表名，列類型，訪問權限

（更加細化的訪問方式）

服務器配置

?mysqld選項，服務器系統變量和服務器狀態變量

https://dev.mysql.com/doc/refman/5.7/en/mysqld-option-

tables.html

https://mariadb.com/kb/en/library/full-list-of-mariadb-options-

system-and-status-variables/

?獲取運行中的mysql進程使用各服務器參數及其值

mysql> SHOW GLOBAL VARIABLES;

mysql> SHOW [SESSION] VARIABLES;

?注意：其中有些參數支持運行時修改，會立即生效；有些參數不支持，且只能通

過修改配置文件，并重啟服務器程序生效；有些參數作用域是全局的，且不可改變；

有些可以為每個用戶提供單獨（會話）的設置

查看mysql的多有選項

[root@centos7 ~]# /usr/libexec/mysqld –help –verbose

查看系統變量

MariaDB [(none)]> show variables;

服務器系統變量：分全局和會話兩種

服務器狀態變量：分全局和會話兩種 ? ? 狀態變量就是統計系統狀態的。只讀的。如cmd_delete 是查看刪除次數的。

獲取運行中的mysql進程使用各種服務器參數及其值

show global variables；

show [session]variables;

設置服務器系統變量三種方法：

?在命令行中設置:

shell> ./mysqld_safe –aria_group_commit=”hard“

?在配置文件my.cnf中設置：

aria_group_commit = “hard”

?在mysql客戶端使用SET命令：

SET GLOBAL aria_group_commit=”hard”;

–skip-name-resolve 反向地址解析 ?可以加到文件中。

SET GLOBAL –skip-name-resolve不可以直接設置，只能在文件中更改，然后從新啟動服務生效。

服務器端設置

?修改服務器變量的值：

mysql> help SET

?修改全局變量：僅對修改后新創建的會話有效；對已經建立的會話無效

mysql> SET GLOBAL system_var_name=value;

mysql> SET @@global.system_var_name=value;

?修改會話變量：

mysql> SET [SESSION] system_var_name=value;

mysql> SET @@[session.]system_var_name=value;

?狀態變量（只讀）：用于保存mysqld運行中的統計數據的變量，不可更改

mysql> SHOW GLOBAL STATUS;

mysql> SHOW [SESSION] STATUS;

MariaDB [(none)]> show global variables like ‘skip_name_resolve’;

+——————-+——-+

| Variable_name ? ? | Value |

+——————-+——-+

| skip_name_resolve | OFF ? |

MariaDB [(none)]> show variables like ‘sql_log_bin’;

+—————+——-+

| Variable_name | Value |

+—————+——-+

| sql_log_bin ? | ON ? ?|

MariaDB [(none)]> show variables like ‘datadir’;

+—————+—————–+

| Variable_name | Value ? ? ? ? ? |

+—————+—————–+

| datadir ? ? ? | /var/lib/mysql/ | ? ? 數據的路徑

MariaDB [(none)]> show variables like ‘basedir’;

+—————+——-+

| Variable_name | Value |

+—————+——-+

| basedir ? ? ? | /usr ?| ? ?軟件安裝的路徑

MariaDB [(none)]> set datadir=’/data’;

ERROR 1238 (HY000): Variable ‘datadir’ is a read only variable

SQL_MODE

? 服務器變量SQL_MODE：對其設置可以完成一些約束檢查的工作,可分別進行全局的設置或當前

會話的設置

? 常見MODE:

? NO_AUTO_CREATE_USER

禁止GRANT創建密碼為空的用戶

? NO_AUTO_VALUE_ON_ZERO

在自增長的列中插入0或NULL將不會是下一個自增長值

? NO_BACKSLASH_ESCAPES

反斜杠“\”作為普通字符而非轉義字符

? PAD_CHAR_TO_FULL_LENGTH

啟用后，對于CHAR類型將不會截斷空洞數據

? PAD_CHAR_TO_FULL_LENGTH

啟用后，對于CHAR類型將不會截斷空洞數據

? PIPES_AS_CONCAT

將”||”視為連接操作符而非“或運算符 ”

MariaDB [(none)]> show variables like ‘sql_mode’;

+—————+——-+

| Variable_name | Value |

+—————+——-+

| sql_mode ? ? ?| ? ? ? |

MariaDB [hellodb]> create table test (id int,name char(5));

Query OK, 0 rows affected (0.01 sec)

MariaDB [hellodb]> select * from test;

Empty set (0.00 sec)

MariaDB [hellodb]> insert test values(1,’abc’),(2,’wangxiaocun’);

Query OK, 2 rows affected, 1 warning (0.00 sec)

Records: 2 ?Duplicates: 0 ?Warnings: 1

MariaDB [hellodb]> select * from test;

+——+——-+

| id ? | name ?|

+——+——-+

| ? ?1 | abc ? |

| ? ?2 | wangx |

在報警時趕緊查看，可以看到報警。

MariaDB [hellodb]> show warnings;

MariaDB [hellodb]> set sql_mode=’traditional’;

MariaDB [hellodb]> insert test values(3,’lalaxiaocun’);

ERROR 1406 (22001): Data too long for column ‘name’ at row 1

MariaDB [hellodb]> show variables like ‘sql_mode’;

+—————+——————————————————————————————————————————————————+

| Variable_name | Value ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?|

+—————+——————————————————————————————————————————————————+

| sql_mode ? ? ?| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

mysql的通訊流程

對于不同的語言有不同的接口用來連接mysql

不是任何人都能連接的，有connection pool 連接池找一個空閑的線程為用戶提供服務，如果結束，則會把之前的信息清空，讓后給下一個用戶使用。連接限制：迸發連接數也就在幾百。（瓶頸所在）

在sql interface 用戶執行增刪改查命令時檢查語法正確性。parser 解析器：能不能執行之前的語句要看用戶有沒有權限，如果能執行，就將語言翻譯成sql的二進制語言進行執行，在執行的時候在optimizer 來優化命令的執行，（類似于路由選擇最佳路徑）在執行計劃可以看到。讀取和寫入都有相應的cache和buffers （在不同的引擎中可能不同）通過引擎，引擎發指令給內核，內核將數據存放到磁盤上。

mysql的塊是磁盤塊的整數倍默認是16k

查詢緩存

?查詢緩存（ Query Cache ）原理：

緩存SELECT操作或預處理查詢的結果集和SQL語句，當有新的SELECT語句或預

處理查詢語句請求，先去查詢緩存，判斷是否存在可用的記錄集，判斷標準：與緩

存的SQL語句，是否完全一樣，區分大小寫

?優缺點

不需要對SQL語句做任何解析和執行，當然語法解析必須通過在先，直接從

Query Cache中獲得查詢結果，提高查詢性能

查詢緩存的判斷規則，不夠智能，也即提高了查詢緩存的使用門檻，降低其效率；

查詢緩存的使用，會增加檢查和清理Query Cache中記錄集的開銷

查詢緩存

? 哪些查詢可能不會被緩存

? 查詢語句中加了SQL_NO_CACHE參數 ?如果查詢的表太大適合使用SQL_NO_CACHE可以提高性能

? 查詢語句中含有獲得值的函數，包含自定義函數，如：NOW()

CURDATE()、GET_LOCK()、RAND()、CONVERT_TZ()等

? 對系統數據庫的查詢：mysql、information_schema 查詢語句中使用SESSION級別變量

或存儲過程中的局部變量

? 查詢語句中使用了LOCK IN SHARE MODE、FOR UPDATE的語句查詢語句中類似

SELECT …INTO 導出數據的語句

? 對臨時表的查詢操作CREATE [TEMPORARY] ；存在警告信息的查詢語句；不涉及任何表或視圖的查詢語句；

某用戶只有列級別權限的查詢語句

? 事務隔離級別為Serializable時，所有查詢語句都不能緩存

MariaDB [hellodb]> select now(); 不需要緩存，因為時間在一直改變

+———————+

| now() ? ? ? ? ? ? ? |

+———————+

| 2018-06-09 17:10:39 |

查詢緩存

?查詢緩存相關的服務器變量

? query_cache_min_res_unit: 查詢緩存中內存塊的最小分配單位，默認4k，較小值會減少浪費，

但會導致更頻繁的內存分配操作，較大值會帶來浪費，會導致碎片過多，內存不足

? query_cache_limit：單個查詢結果能緩存的最大值，默認為1M，

對于查詢結果過大而無法緩存的語句，建議使用 SQL_NO_CACHE

? query_cache_size：查詢緩存總共可用的內存空間；單位字節，必須是1024的整數倍，最小值

40KB，低于此值有警報

? query_cache_wlock_invalidate：如果某表被其它的會話鎖定，是否仍然可以從查詢緩存中返

回結果，默認值為OFF，表示可以在表被其它會話鎖定的場景中繼續從緩存返回數據；ON則

表示不允許

? query_cache_type: 取值為ON, OFF, DEMAND

?參看： https://dev.mysql.com/doc/refman/5.7/en/query-cache-configuration.html

MariaDB [(none)]> show variables like ‘query_cache_min_res_unit’;

+————————–+——-+

| Variable_name ? ? ? ? ? ?| Value |

+————————–+——-+

| query_cache_min_res_unit | 4096 ?|

緩存總的可用空間

MariaDB [(none)]> show variables like ‘query_cache_size’;

+——————+——-+

| Variable_name ? ?| Value |

+——————+——-+

| query_cache_size | 0 ? ? |

緩存功能是否開啟

MariaDB [(none)]> show variables like ‘query_cache_type’;

+——————+——-+

| Variable_name ? ?| Value |

+——————+——-+

| query_cache_type | ON ? ?|

查詢緩存

?SELECT語句的緩存控制

?SQL_CACHE: 顯式指定存儲查詢結果于緩存之中

?SQL_NO_CACHE: 顯式查詢結果不予緩存

?query_cache_type參數變量：

?query_cache_type的值為OFF或0時，查詢緩存功能關閉

?query_cache_type的值為ON或1時，查詢緩存功能打開，SELECT的結果符合緩

存條件即會緩存，否則，不予緩存，顯式指定SQL_NO_CACHE，不予緩存，

此為默認值

? query_cache_type的值為DEMAND或2時，查詢緩存功能按需進行，顯式指定

SQL_CACHE的SELECT語句才會緩存；其它均不予緩存

查看官方文檔： https://mariadb.com/kb/en/library/server-system-variables/

#query_cache_type

查詢緩存

? 查詢緩存相關的狀態變量

SHOW GLOBAL STATUS LIKE ‘Qcache%’;

? Qcache_free_blocks：處于空閑狀態 Query Cache中內存 Block 數

? Qcache_free_memory：處于空閑狀態的 Query Cache 內存總量

? Qcache_hits：Query Cache 命中次數

? Qcache_inserts：向 Query Cache 中插入新的 Query Cache 的次數，即沒有命中的次數

? Qcache_lowmem_prunes：當 Query Cache 內存容量不夠，需要刪除老的 Query Cache 以給新

的 Cache 對象使用的次數

? Qcache_not_cached：沒有被 Cache 的 SQL 數，包括無法被 Cache 的 SQL 以及由于

query_cache_type 設置的不會被 Cache 的 SQL語句

? Qcache_queries_in_cache：在 Query Cache 中的 SQL 數量

? Qcache_total_blocks：Query Cache 中總的 Block

查詢和緩存相關的數據

MariaDB [(none)]> show status like ‘Qcache%’\G

*************************** 1. row ***************************

Variable_name: Qcache_free_blocks ? ?可用的塊

? ? ? ? Value: 0

*************************** 2. row ***************************

Variable_name: Qcache_free_memory ? 剩余空間

? ? ? ? Value: 0

*************************** 3. row ***************************

Variable_name: Qcache_hits ? ?命中率

? ? ? ? Value: 0

*************************** 4. row ***************************

Variable_name: Qcache_inserts ? ? ?增加了幾次記錄

? ? ? ? Value: 0

*************************** 5. row ***************************

Variable_name: Qcache_lowmem_prunes

? ? ? ? Value: 0

*************************** 6. row ***************************

Variable_name: Qcache_not_cached

? ? ? ? Value: 0

*************************** 7. row ***************************

Variable_name: Qcache_queries_in_cache

? ? ? ? Value: 0

*************************** 8. row ***************************

Variable_name: Qcache_total_blocks

? ? ? ? Value: 0

命中率

?命中率和內存使用率估算

?query_cache_min_res_unit ≈(query_cache_size – Qcache_free_memory) /

Qcache_queries_in_cache

?查詢緩存命中率 ≈ (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%

?查詢緩存內存使用率 ≈ (query_cache_size – qcache_free_memory) /

query_cache_size * 100%

查詢緩存中內存塊的最小分配單位query-cache_min_res_unit :

(query_cache_size – Qcache_free_memery)/Qcache_queries_in_chche

查詢執行了幾次select命令

MariaDB [hellodb]> show status like ‘com_select%’;

+—————+——-+

| Variable_name | Value |

+—————+——-+

| Com_select ? ?| 3 ? ? |

MariaDB [hellodb]> select * from students;

MariaDB [hellodb]> show status like ‘com_select%’;

+—————+——-+

| Variable_name | Value |

+—————+——-+

| Com_select ? ?| 4 ? ? |

InnoDB存儲引擎

?InnoDB存儲引擎的緩沖池:

通常InnoDB存儲引擎緩沖池的命中不應該小于99%

?查看相關狀態變量：

show global status like ‘innodb%read%’\G

? Innodb_buffer_pool_reads: 表示從物理磁盤讀取頁的次數

? Innodb_buffer_pool_read_ahead: 預讀的次數

? Innodb_buffer_pool_read_ahead_evicted: 預讀頁，但是沒有讀取就從緩沖池中被替換

的頁數量，一般用來判斷預讀的效率

? Innodb_buffer_pool_read_requests: 從緩沖池中讀取頁次數

? Innodb_data_read: 總共讀入的字節數

? Innodb_data_reads: 發起讀取請求的次數，每次讀取可能需要讀取多個頁

innodb 存儲引擎的緩存

MariaDB [hellodb]> show global status like ‘innodb%read%’\G

*************************** 1. row ***************************

Variable_name: Innodb_buffer_pool_read_ahead

? ? ? ? Value: 0

*************************** 2. row ***************************

Variable_name: Innodb_buffer_pool_read_ahead_evicted

? ? ? ? Value: 0

*************************** 3. row ***************************

Variable_name: Innodb_buffer_pool_read_ahead_rnd

? ? ? ? Value: 0

*************************** 4. row ***************************

Variable_name: Innodb_buffer_pool_read_requests

? ? ? ? Value: 2090

*************************** 5. row ***************************

Variable_name: Innodb_buffer_pool_reads

? ? ? ? Value: 213

*************************** 6. row ***************************

Variable_name: Innodb_data_pending_reads

? ? ? ? Value: 0

*************************** 7. row ***************************

Variable_name: Innodb_data_read

? ? ? ? Value: 5673472

*************************** 8. row ***************************

Variable_name: Innodb_data_reads

? ? ? ? Value: 224

*************************** 9. row ***************************

Variable_name: Innodb_master_thread_1_second_loops

? ? ? ? Value: 18

*************************** 10. row ***************************

Variable_name: Innodb_master_thread_10_second_loops

? ? ? ? Value: 1

*************************** 11. row ***************************

Variable_name: Innodb_master_thread_background_loops

? ? ? ? Value: 10

*************************** 12. row ***************************

Variable_name: Innodb_master_thread_main_flush_loops

? ? ? ? Value: 10

*************************** 13. row ***************************

Variable_name: Innodb_master_thread_sleeps

? ? ? ? Value: 18

*************************** 14. row ***************************

Variable_name: Innodb_pages_read

? ? ? ? Value: 212

*************************** 15. row ***************************

Variable_name: Innodb_read_views_memory

? ? ? ? Value: 184

*************************** 16. row ***************************

Variable_name: Innodb_rows_read

? ? ? ? Value: 52

索引

?索引是特殊數據結構：定義在查找時作為查找條件的字段，索引是現在存儲引擎。

?優點：提高查詢速度，索引可以減低服務需要掃描的數據量，減少io的次數，索引可以幫助服務器避免排序和使用臨時表，索引可以幫助將隨機i/o（全表掃描）轉為順序i/o 。缺點：占用額外空間，影響插入速度

?索引實現在存儲引擎

?索引類型：

?聚簇（集）索引、非聚簇索引：數據是否與索引存儲在一起，排序的順序是否一致。

?主鍵索引、輔助索引

?稠密索引、稀疏索引：是否索引了每一個數據項

?B+ TREE、HASH、R TREE

?簡單索引（一個字段上建立所以）、組合索引（多個字段上建立索引）

左前綴索引：取前面的字符做索引

覆蓋索引：從索引中即可取出要查詢的數據，性能高

二級索引是非聚集索引，其表中不存放數據，而是存放著指向的主鍵

B-Tree索引

?B+ Tree索引：順序存儲，每一個葉子節點到根結點的距離是相同的；左

前綴索引，適合查詢范圍類的數據

?可以使用B-Tree索引的查詢類型：

全值匹配：精確所有索引列，如：姓wang，名xiaochun，年齡30

匹配最左前綴：即只使用索引的第一列，如：姓wang

匹配列前綴：只匹配一列值開頭部分，如：姓以w開頭的

匹配范圍值：如：姓ma和姓wang之間

精確匹配某一列并范圍匹配另一列：如：姓wang,名以x開頭的

只訪問索引的查詢

B-Tree索引

?B-Tree索引的限制：

?如果不從最左列開始，則無法使用索引：如：查找名為xiaochun，或姓為g結

尾

?不能跳過索引中的列：如：查找姓wang,年齡30的，只能使用索引第一列

?如果查詢中某個列是為范圍查詢，那么其右側的列都無法再使用索引：如：

姓wang,名x%,年齡30，只能利用姓和名上面的索引

?特別提示：

?索引列的順序和查詢語句的寫法應相匹配，才能更好的利用索引

?為優化性能，可能需要針對相同的列但順序不同創建不同的索引來滿足不同

類型的查詢需求

Hash索引

?Hash索引：基于哈希表實現，只有精確匹配索引中的所有列的查詢才有效，索

引自身只存儲索引列對應的哈希值和數據指針，索引結構緊湊，查詢性能好

?只有Memory存儲引擎支持顯式hash索引

?適用場景：

只支持等值比較查詢，包括=, IN(), <=>

?不適合使用hash索引的場景：

不適用于順序查詢：索引存儲順序的不是值的順序

不支持模糊匹配

不支持范圍查詢

不支持部分索引列匹配查找：如A，B列索引，只查詢A列索引無效

索引

?空間索引（R-Tree）：

MyISAM支持空間索引，可以使用任意維度組合查詢，使用特有的函數訪問，

常用于做地理數據存儲，使用不多

?全文索引(FULLTEXT)：

在文本中查找關鍵詞，而不是直接比較索引中的值，類似搜索引擎

?索引優點：

索引可以降低服務需要掃描的數據量，減少了IO次數

索引可以幫助服務器避免排序和使用臨時表

索引可以幫助將隨機I/O轉為順序I/O

MariaDB [(none)]> show global variables like ‘query_cache_%’;

+——————————+———+

| Variable_name ? ? ? ? ? ? ? ?| Value ? |

+——————————+———+

| query_cache_limit ? ? ? ? ? ?| 1048576 |

| query_cache_min_res_unit ? ? | 4096 ? ?|

| query_cache_size ? ? ? ? ? ? | 0 ? ? ? |

| query_cache_strip_comments ? | OFF ? ? |

| query_cache_type ? ? ? ? ? ? | ON ? ? ?|

| query_cache_wlock_invalidate | OFF ? ? |

| query_cache_size ? ? ? ? ? ? | 0 ? ? ? | ?因為緩存空間是0所以無法緩存。

[root@CENTOS7 ~]# vim /etc/my.cnf

query_cache_size=10M ? ? 給予10M緩存

[root@CENTOS7 ~]# systemctl restart mariadb

查看緩存的性能指標

MariaDB [(none)]> show global status like ‘Qcache%’;

+————————-+———-+

| Variable_name ? ? ? ? ? | Value ? ?|

+————————-+———-+

| Qcache_free_blocks ? ? ?| 1 ? ? ? ?|

| Qcache_free_memory ? ? ?| 10468296 |

| Qcache_hits ? ? ? ? ? ? | 0 ? ? ? ?|

| Qcache_inserts ? ? ? ? ?| 0 ? ? ? ?|

| Qcache_lowmem_prunes ? ?| 0 ? ? ? ?|

| Qcache_not_cached ? ? ? | 1 ? ? ? ?|

| Qcache_queries_in_cache | 0 ? ? ? ?|

| Qcache_total_blocks ? ? | 1 ? ? ? ?|

MaMariaDB [(none)]> use hellodb

riaDB [(none)]> use hellodb

第一次搜索

MariaDB [hellodb]> select * from students;

MariaDB [hellodb]> show global status like ‘Qcache%’;

+————————-+———-+

| Variable_name ? ? ? ? ? | Value ? ?|

+————————-+———-+

| Qcache_free_blocks ? ? ?| 1 ? ? ? ?|

| Qcache_free_memory ? ? ?| 10466192 |

| Qcache_hits ? ? ? ? ? ? | 0 ? ? ? ?|

| Qcache_inserts ? ? ? ? ?| 1 ? ? ? ?|

| Qcache_lowmem_prunes ? ?| 0 ? ? ? ?|

| Qcache_not_cached ? ? ? | 2 ? ? ? ?|

| Qcache_queries_in_cache | 1 ? ? ? ?|

| Qcache_total_blocks ? ? | 4 ? ? ? ?|

第二次搜索

MariaDB [hellodb]> select * from students;

MariaDB [hellodb]> show global status like ‘Qcache%’;

+————————-+———-+

| Variable_name ? ? ? ? ? | Value ? ?|

+————————-+———-+

| Qcache_free_blocks ? ? ?| 1 ? ? ? ?|

| Qcache_free_memory ? ? ?| 10466192 |

| Qcache_hits ? ? ? ? ? ? | 1 ? ? ? ?|

| Qcache_inserts ? ? ? ? ?| 1 ? ? ? ?|

| Qcache_lowmem_prunes ? ?| 0 ? ? ? ?|

| Qcache_not_cached ? ? ? | 2 ? ? ? ?|

| Qcache_queries_in_cache | 1 ? ? ? ?|

| Qcache_total_blocks ? ? | 4 ? ? ? ?|

搜索8次

MariaDB [hellodb]> show global status like ‘Qcache%’;

+————————-+———-+

| Variable_name ? ? ? ? ? | Value ? ?|

+————————-+———-+

| Qcache_free_blocks ? ? ?| 1 ? ? ? ?|

| Qcache_free_memory ? ? ?| 10466192 |

| Qcache_hits ? ? ? ? ? ? | 7 ? ? ? ?|

| Qcache_inserts ? ? ? ? ?| 1 ? ? ? ?|

| Qcache_lowmem_prunes ? ?| 0 ? ? ? ?|

| Qcache_not_cached ? ? ? | 2 ? ? ? ?|

| Qcache_queries_in_cache | 1 ? ? ? ?|

| Qcache_total_blocks ? ? | 4 ? ? ? ?|

雖然是一樣的命令，但是一旦用大寫，那么就是新的緩存。

MariaDB [hellodb]> SELECT * from students;

MariaDB [hellodb]> show global status like ‘Qcache%’;

+————————-+———-+

| Variable_name ? ? ? ? ? | Value ? ?|

+————————-+———-+

| Qcache_free_blocks ? ? ?| 1 ? ? ? ?|

| Qcache_free_memory ? ? ?| 10464600 |

| Qcache_hits ? ? ? ? ? ? | 7 ? ? ? ?|

| Qcache_inserts ? ? ? ? ?| 2 ? ? ? ?|

| Qcache_lowmem_prunes ? ?| 0 ? ? ? ?|

| Qcache_not_cached ? ? ? | 2 ? ? ? ?|

| Qcache_queries_in_cache | 2 ? ? ? ?|

| Qcache_total_blocks ? ? | 6 ? ? ? ?|

索引

?高性能索引策略：

?獨立地使用列：盡量避免其參與運算，獨立的列指索引列不能是表達式的一

部分，也不能是函數的參數，在where條件中，始終將索引列單獨放在比較

符號的一側

?左前綴索引：構建指定索引字段的左側的字符數，要通過索引選擇性來評估

索引選擇性：不重復的索引值和數據表的記錄總數的比值

?多列索引：AND操作時更適合使用多列索引，而非為每個列創建單獨的索引

?選擇合適的索引列順序：無排序和分組時，將選擇性最高放左側

?冗余和重復索引：（A），（A，B）即為冗余索引

不好的索引使用策略，建議擴展索引，而非冗余

myisan的數據和索引是分開存放的

innodb的數據和索引是存放在一起的都放在ibdata1這個文件中

-rw-rw—- 1 mysql mysql 18874368 Jun 10 12:10 ibdata1

主鍵索引

如果經常用名字進行搜索，那么應該用名字建立索引。名字索引是指向主鍵索引的，并不存放數據

復合索引有先后順序，按左側的排序只有當左側相同時在按右側排序指針指向主鍵搜索的時候要使用索引需要搜索左側的。不能搜索后續的。

索引

?高性能索引策略：

?獨立地使用列：盡量避免其參與運算，獨立的列指索引列不能是表達式的一

部分，也不能是函數的參數，在where條件中，始終將索引列單獨放在比較

符號的一側

?左前綴索引：構建指定索引字段的左側的字符數，要通過索引選擇性來評估

索引選擇性：不重復的索引值和數據表的記錄總數的比值

?多列索引：AND操作時更適合使用多列索引，而非為每個列創建單獨的索引

?選擇合適的索引列順序：無排序和分組時，將選擇性最高放左側

?冗余和重復索引：（A），（A，B）即為冗余索引

不好的索引使用策略，建議擴展索引，而非冗余

索引優化建議

?只要列中含有NULL值，就最好不要在此例設置索引，復合索引如果有NULL值，

此列在使用時也不會使用索引

?盡量使用短索引，如果可以，應該制定一個前綴長度

?對于經常在where子句使用的列，最好設置索引

?對于有多個列where或者order by子句，應該建立復合索引

?對于like語句，以%或者‘-’開頭的不會使用索引，以%結尾會使用索引

?盡量不要在列上進行運算（函數操作和表達式操作）

?盡量不要使用not in和<>操作

多表連接時應該小表join大表，a10條記錄b1000條記錄，那么在查詢的時候會循環10次反之1000次

在千萬級分頁時使用limit

對于經常使用的查詢，可以開啟緩存

大部分情況連接效率遠大于子查詢

管理索引

?創建索引：

CREATE INDEX index_name ON tbl_name (index_col_name,…字段);

help CREATE INDEX

?刪除索引：

DROP INDEX index_name ON tbl_name;

?查看索引：

SHOW INDEXES FROM [db_name.]tbl_name;

?優化表空間：

OPTIMIZE TABLE tb_name

查看students表是否有索引。

MariaDB [hellodb]> show indexes from students\G

*************************** 1. row ***************************

? ? ? ? Table: students

? ?Non_unique: 0

? ? ?Key_name: PRIMARY

?Seq_in_index: 1

? Column_name: StuID

? ? Collation: A

? Cardinality: 25

? ? ?Sub_part: NULL

? ? ? ?Packed: NULL

? ? ? ? ?Null:

? ?Index_type: BTREE

? ? ? Comment:

Index_comment:

explain 查看select語句的執行情況rows25表示查詢了25行就是全表掃描

MariaDB [hellodb]> explain select * from students where age = 20\G

*************************** 1. row ***************************

? ? ? ? ? ?id: 1

? select_type: SIMPLE

? ? ? ? table: students

? ? ? ? ?type: ALL

possible_keys: NULL

? ? ? ? ? key: NULL

? ? ? key_len: NULL

? ? ? ? ? ref: NULL

? ? ? ? ?rows: 25

? ? ? ? Extra: Using where

對students表的age列建立索引

MariaDB [hellodb]> create index index_age on students(age);

這時students表有倆個索引

MariaDB [hellodb]> show indexes from students\G

*************************** 1. row ***************************

? ? ? ? Table: students

? ?Non_unique: 0

? ? ?Key_name: PRIMARY

?Seq_in_index: 1

? Column_name: StuID

? ? Collation: A

? Cardinality: 25

? ? ?Sub_part: NULL

? ? ? ?Packed: NULL

? ? ? ? ?Null:

? ?Index_type: BTREE

? ? ? Comment:

Index_comment:

*************************** 2. row ***************************

? ? ? ? Table: students

? ?Non_unique: 1

? ? ?Key_name: index_age

?Seq_in_index: 1

? Column_name: Age

? ? Collation: A

? Cardinality: 25

? ? ?Sub_part: NULL

? ? ? ?Packed: NULL

? ? ? ? ?Null:

? ?Index_type: BTREE

? ? ? Comment:

Index_comment:

這時在搜索只搜索了2行

MariaDB [hellodb]> explain select * from students where age = 20\G

*************************** 1. row ***************************

? ? ? ? ? ?id: 1

? select_type: SIMPLE

? ? ? ? table: students

? ? ? ? ?type: ref

possible_keys: index_age

? ? ? ? ? key: index_age

? ? ? key_len: 1

? ? ? ? ? ref: const

? ? ? ? ?rows: 2

? ? ? ? Extra:

對姓名再次建立索引

MariaDB [hellodb]> create index index_name on students(name);

MariaDB [hellodb]> explain select * from students where name like ‘b%’\G

*************************** 1. row ***************************

? ? ? ? ? ?id: 1

? select_type: SIMPLE

? ? ? ? table: students

? ? ? ? ?type: range

possible_keys: index_name

? ? ? ? ? key: index_name

? ? ? key_len: 152

? ? ? ? ? ref: NULL

? ? ? ? ?rows: 1

? ? ? ? Extra: Using index condition

1 row in set (0.00 sec)

MariaDB [hellodb]> explain select * from students where name like ‘%b’\G ?%b這種寫法無法使用索引

*************************** 1. row ***************************

? ? ? ? ? ?id: 1

? select_type: SIMPLE

? ? ? ? table: students

? ? ? ? ?type: ALL

possible_keys: NULL

? ? ? ? ? key: NULL

? ? ? key_len: NULL

? ? ? ? ? ref: NULL

? ? ? ? ?rows: 25

? ? ? ? Extra: Using where

查看索引的使用

set global userstat =1；

show index_statistics

MariaDB [hellodb]> set global userstat=1

MariaDB [hellodb]> show variables like ‘userstat’;

+—————+——-+

| Variable_name | Value |

+—————+——-+

| userstat ? ? ?| ON ? ?|

MariaDB [hellodb]> select * from students where age=20;

age這個索引被使用了。

MariaDB [hellodb]> show index_statistics;

+————–+————+————+———–+

+————–+————+————+———–+

當刪除表中的一些行時，索引就會變得不緊湊，產生很多磁盤碎片，這時可以使用OPTIMIZE TABLE tb_name 來清理磁盤碎片使索引變得緊湊。

EXPLAIN

?通過EXPLAIN來分析索引的有效性：

?EXPLAIN SELECT clause

獲取查詢執行計劃信息，用來查看查詢優化器如何執行查詢

?輸出信息說明：參考 https://dev.mysql.com/doc/refman/5.7/en/explain-

output.html

?id: 當前查詢語句中，每個SELECT語句的編號id表示有幾個select語句，當有子查詢時有倆條記錄

復雜類型的查詢有三種：

簡單子查詢

用于FROM中的子查詢

聯合查詢：UNION

注意：UNION查詢的分析結果會出現一個額外匿名臨時表

EXPLAIN

?select_type：

簡單查詢為SIMPLE

復雜查詢：

SUBQUERY: 簡單子查詢

PRIMARY:最外面的SELECT

DERIVED: 用于FROM中的子查詢

UNION：UNION語句的第一個之后的SELECT語句

UNION RESULT: 匿名臨時表

?table：SELECT語句關聯到的表

EXPLAIN

?type：關聯類型或訪問類型，即MySQL決定的如何去查詢表中的行的方式，以

下順序，性能從低到高

? ALL: 全表掃描

? index：根據索引的次序進行全表掃描；如果在Extra列出現“Using index”表示了使用

覆蓋索引，而非全表掃描

? range：有范圍限制的根據索引實現范圍掃描；掃描位置始于索引中的某一點，結束

于另一點

? ref: 根據索引返回表中匹配某單個值的所有行

? eq_ref：僅返回一個行，但與需要額外與某個參考值做比較

? const, system: 直接返回單個行

?possible_keys：查詢可能會用到的索引

?key: 查詢中使用到的索引

?key_len: 在索引使用的字節數

EXPLAIN

?ref: 在利用key字段所表示的索引完成查詢時所用的列或某常量值

?rows：MySQL估計為找所有的目標行而需要讀取的行數

?Extra：額外信息

Using index：MySQL將會使用覆蓋索引，以避免訪問表

Using where：MySQL服務器將在存儲引擎檢索后，再進行一次過濾

Using temporary：MySQL對結果排序時會使用臨時表

Using filesort：對結果使用一個外部索引排序

查看students表中有幾個索引

MariaDB [hellodb]> show indexes from students\G

刪除索引

MariaDB [hellodb]> drop index index_name on students;

MariaDB [hellodb]> drop index index_age on students;

創建復合索引

MariaDB [hellodb]> create index index_name_age on students(name,age);

MariaDB [hellodb]> show index from students\G

*************************** 2. row ***************************

? ? ? ? Table: students

? ?Non_unique: 1

? ? ?Key_name: index_name_age

?Seq_in_index: 1

? Column_name: Name

? ? Collation: A

? Cardinality: 25

? ? ?Sub_part: NULL

? ? ? ?Packed: NULL

? ? ? ? ?Null:

? ?Index_type: BTREE

? ? ? Comment:

Index_comment:

*************************** 3. row ***************************

? ? ? ? Table: students

? ?Non_unique: 1

? ? ?Key_name: index_name_age

?Seq_in_index: 2

? Column_name: Age

? ? Collation: A

? Cardinality: 25

? ? ?Sub_part: NULL

? ? ? ?Packed: NULL

? ? ? ? ?Null:

? ?Index_type: BTREE

? ? ? Comment:

Index_comment:

MariaDB [hellodb]> explain select * from students where name like ‘b%’\G

*************************** 1. row ***************************

? ? ? ? ? ?id: 1

? select_type: SIMPLE

? ? ? ? table: students

? ? ? ? ?type: range

possible_keys: index_name_age

? ? ? ? ? key: index_name_age

? ? ? key_len: 152

? ? ? ? ? ref: NULL

? ? ? ? ?rows: 1

? ? ? ? Extra: Using index condition

MariaDB [hellodb]> explain select * from students where name like ‘x%’\G ? ?？？？？？

*************************** 1. row ***************************

? ? ? ? ? ?id: 1

? select_type: SIMPLE

? ? ? ? table: students

? ? ? ? ?type: ALL

possible_keys: index_name_age

? ? ? ? ? key: NULL

? ? ? key_len: NULL

? ? ? ? ? ref: NULL

? ? ? ? ?rows: 25

? ? ? ? Extra: Using where

1 row in set (0.00 sec)

MariaDB [hellodb]> explain select * from students where name like ‘s%’\G

*************************** 1. row ***************************

? ? ? ? ? ?id: 1

? select_type: SIMPLE

? ? ? ? table: students

? ? ? ? ?type: range

possible_keys: index_name_age

? ? ? ? ? key: index_name_age

? ? ? key_len: 152

? ? ? ? ? ref: NULL

? ? ? ? ?rows: 3

? ? ? ? Extra: Using index condition

建立唯一鍵索引。

MariaDB [hellodb]> create unique index uni_index_name on students(name);

MariaDB [hellodb]> show index from students\G

? ? ? ?Table: students

? ?Non_unique: 0

? ? ?Key_name: PRIMARY

?Seq_in_index: 1

? Column_name: StuID

? ? Collation: A

? Cardinality: 25

? ? ?Sub_part: NULL

? ? ? ?Packed: NULL

? ? ? ? ?Null:

? ?Index_type: BTREE

? ? ? Comment:

Index_comment:

限定索引的字符數支取name的倆個字符

MariaDB [hellodb]> create index index_name on students (name(2));

MariaDB [hellodb]> show index from students\G

? ? ? ?Table: students

? ?Non_unique: 1

? ? ?Key_name: index_name

?Seq_in_index: 1

? Column_name: Name

? ? Collation: A

? Cardinality: 25

? ? ?Sub_part: 2 ? ? 兩個字符

? ? ? ?Packed: NULL

? ? ? ? ?Null:

? ?Index_type: BTREE

? ? ? Comment:

Index_comment:

utf8的類型會一個占用4個字節

而我們定義的是name（2）兩個字符，所以是8個字節。

QL語句性能優化

?查詢時，能不要*就不用*，盡量寫全字段名

?大部分情況連接效率遠大于子查詢

?多表連接時，盡量小表驅動大表，即小表 join 大表

?在千萬級分頁時使用limit

?對于經常使用的查詢，可以開啟緩存

?多使用explain和profile分析查詢語句

?查看慢查詢日志，找出執行時間長的sql語句優化

并發控制

?鎖粒度：

表級鎖

行級鎖

?鎖：

讀鎖：共享鎖，只讀不可寫，多個讀互不阻塞，

寫鎖：獨占鎖,排它鎖，一個寫鎖會阻塞其它讀和它鎖

?實現

存儲引擎：自行實現其鎖策略和鎖粒度

服務器級：實現了鎖，表級鎖；用戶可顯式請求

?分類：

隱式鎖：由存儲引擎自動施加鎖

顯式鎖：用戶手動請求

并發控制

?鎖策略：在鎖粒度及數據安全性尋求的平衡機制

?顯示使用鎖

? LOCK TABLES

tbl_name [[AS] alias] lock_type

[, tbl_name [[AS] alias] lock_type] …

lock_type: READ ， WRITE

UNLOCK TABLES 解鎖

? FLUSH TABLES tb_name[,…] [WITH READ LOCK]

關閉正在打開的表（清除查詢緩存），通常在備份前加全局讀鎖

? SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]

查詢時加寫或讀鎖

對students表添加讀鎖，自己能讀不能寫，別的用戶也只能讀不能寫。

MariaDB [hellodb]> lock tables students read;

MariaDB [hellodb]> update students set classid=2 where stuid=24;

ERROR 1099 (HY000): Table ‘students’ was locked with a READ lock and can’t be updated

別的用戶會一直卡在這個界面無法修改，當解除鎖后會立即修改

MariaDB [hellodb]> update students set classid=2 where stuid=24;

解除鎖

MariaDB [hellodb]> unlock tables;

別的用戶的修改已經執行

MariaDB [hellodb]> update students set classid=2 where stuid=24;

Query OK, 1 row affected (1 min 50.52 sec)

Rows matched: 1 ?Changed: 1 ?Warnings: 0

添加寫鎖，自己可以讀不可以寫，別的用戶不能讀也不能寫，如果別的用戶可以查看是因為有緩存。

MariaDB [hellodb]> lock tables students write;

解鎖

MariaDB [hellodb]> unlock tables;

清除緩存

MariaDB [hellodb]> flush tables students;

事務

?事務Transactions：一組原子性的SQL語句，或一個獨立工作單元

?事務日志：記錄事務信息，實現undo,redo等故障恢復功能

?ACID特性：

?A：atomicity原子性；整個事務中的所有操作要么全部成功執行，要么全部失

敗后回滾

?C：consistency一致性；數據庫總是從一個一致性狀態轉換為另一個一致性狀

態

?I：Isolation隔離性；一個事務所做出的操作在提交之前，是不能為其它事務

所見；隔離有多種隔離級別，實現并發

?D：durability持久性；一旦事務提交，其所做的修改會永久保存于數據庫中

對數據增刪改才是事務，查詢不算事務

本文來自投稿，不代表Linux運維部落立場，如若轉載，請注明出處：http://www.www58058.com/100803

相關推薦

Linuxday1——計算機基礎

ansile

Linux發行版之間的聯系和不同

硬連接和軟連接

LVS虛擬服務器相關實驗

linux的基礎命令