MySQL/MariaDB基礎性知識及DDL操作詳解

前言

MySQL/MariaDB是一個開放源碼的小型關聯式數據庫管理系統，由于其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點，許多中小型網站為了降低網站總體擁有成本而選擇了MySQL/MariaDB作為網站數據庫。

基礎架構

MySQL核心組件

連接池：認證、線程重用、連接數限制、內存檢查、緩存

SQL接口：DDL, DML, 關系型數據庫的基本抽象

parser: 查詢轉換、對象權限檢查

優化器：訪問路徑，性能相關的統計數據

caches和buffers：與存儲引擎自身相關的I/O性能提升工具

存儲引擎：MyISAM、InnoDB(變種：XtraDB)、Memory、Merge、Federated、CSV、Archive、Blackholl、Aria、SphinxSE、TokuDB

內部結構

程序類別及命令選項

服務器端程序：啟動并監聽于套接字上；mysqld, mysqld_safe, mysqld_multi

客戶端程序：可通過mysql協議連入服務器并發出請求的；mysql, mysqlbinlog, mysqladmin, mysqldump等

工具程序：運行于服務器進程所在的主機，實現一些管理或維護操作，myisamchk

客戶端程序通用選項

-u,--user #指定登陸用戶
-h,--host #指定登陸主機
-p,--password #指定登陸密碼
--protocol={tcp|socket|memory|pipe} #指定協議
-P,--port #指定端口，默認監聽端口：tcp/3306
--socket #指定本地連接的sock文件，相當于--protocol socket
--compress #數據傳輸采用壓縮格式
-D,--database #指定連接后默認使用的數據庫
-H,--html #指定產生html輸出
-X,--xml #指定產生xml輸出
--safe-updates #拒絕使用無where子句的update或delete命令

客戶端程序命令

交互式模式：
#客戶端命令
   mysql> help #列出所有命令
   mysql> \? #相當于help
   mysql> \c #取消命令執行
   mysql> \g #發送命令至服務器端
   mysql> \G #發送命令至服務器端，垂直顯示結果
   mysql> \q #退出
   mysql> \! #運行shell命令
   mysql> \s #顯示服務器端狀態信息
   mysql> \. /path/to/somefile.sql #批量執行sql
   mysql> \u #將指定的庫設為默認庫
#服務器端命令
   mysql> help KEYWORD 獲取關鍵字的幫助信息
批模式：
mysql < /path/from/somefile.sql

管理工具mysqladmin

格式：mysqladmin [options] command [arg] [command [arg]] ...
#常用命令
create DB_Name：創建數據庫
drop DB_Name：刪除數據庫
debug：打開調試日志并記錄于error log中
status：顯示簡要狀態信息
        --sleep #間隔秒數
        --count #顯示的次數
extend-status: 顯示mysqld的所有服務器狀態變量
flush-privileges: 刷新授權表，相當于reload命令
flush-hosts: 清除dns緩存及被拒絕的客戶端列表緩存
flush-logs: 滾動日志, 二進制日志和中繼日志
flush-status: 重置各狀態變量
flush-tables: 關閉當前打開的所有的表文件句柄；
flush-treads: 重置線程緩存；
password: 設置密碼
ping: 測試服務器是否在線
processlist: 顯示當前服務器上的所有線程
refresh: 相當于執行flush-hosts和flush-logs
shutdown: 關閉服務器進程 ；
start-slave, stop-slave: 啟動、關閉從服務器線程；
variables: 顯示服務器變量

功能特性補充

命令行編輯功能

Ctrl+a: 快速移動光標至行首
Ctrl+e: 快速移動光標至行尾
Ctrl+w: 刪除光標之前的單詞
Ctrl+u: 刪除行首至光標處的所有內容
Ctrl+y: 粘貼使用Ctrl+w或Ctrl+u刪除的內容

提示符

mysql> #等待輸入命令
->   #續行
‘>  #還需補全后半部單引號
“>  #還需補全后半部雙引號
`>   #還需補全后半部反引號
/*>  #注釋，不執行，需以*/結束注釋

SQL語言的組成部分

DDL：數據定義語言

DCL：數據控制語言，如授權

DML：數據操作語言

完整性定義語言：DDL功能性約束（主鍵、外鍵、惟一鍵、條件、非空、事務）

視圖定義：虛表，存儲下來的select語句

事務控制

數據字典

數據字典：系統編目（system catalog）

保存了數據庫服務器上的元數據

元數據

關系的名稱
每個關系中各字段的名稱
各字段的類型和長度
約束
每個關系上的視圖的名字及視圖的定義
授權的用戶名字
用戶的授權和帳戶信息
統計類數據
	每個關系中字段數；
	每個關系中行數；
	每個關系的存儲方法；

保存元數據的數據庫

infomation_schema
mysql
performance_schema

數據類型及屬性修飾符

數據類型的功用

①存儲的值類型

②占據的最大存儲空間

③定長、變長

④如何被索引和排序

⑤是否能夠被索引

數據類型
字符型

CHAR、VARCARH、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT

BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOG、LONGBLOB

修飾符：
NULL：允許為空
NOT NULL：不允許為空
DEFAULT 'string'：默認值，不適用于TEXT
CHARACTER SET 'set'：設定字符集
    SHOW CHARACTER SET;查看可用字符集
COLLATION 'collation'：設定字符集排序規則
    SHOW COLLATION;查看可用排序規則
DEFAULT：不用于BLOB類型
字符有通配符：
	%: 匹配任意長度的任意字符
	_: 匹配任意單個字符；

整型

TINYINT、SMALLINT、MEDIUMINT 、INT、BININT

修飾符：
UNSIGNED：無符號
NULL
NOT NULL
DEFAULT
AUTO_INCREMENT: 自動增長
#特殊要求：非空，且必須是主鍵或惟一鍵

浮點型

FLOAT、DOUBLE

修飾符：
NOT NULL
NULL
DEFAULT
UNSIGNED

布爾型

沒有專用布爾型，其是TINYINT(1)的別名

日期時間型

DATE、TIME、DATETIME、TIMESTAMP、YEAR(2)、YEAR(4)

修飾符：
NULL
NOT NULL
DEFAULT VALUE

內建類型

ENUM：枚舉，表示僅能從給出的選項選擇其中一個
      ENUM('string1','string2')
SET：集合, 表示能使用給出的元素組合成字符串
      SET('a','b','c')
      
修飾符：
NULL
NOT NULL
DEFAULT ''

SQL模式及服務器變量

sql模式：用來限定mysqld的工作特性

TRADITIONAL：傳統模式

STRICT_TRANS_TABLES：對支持事務的表使用嚴格模式

STRICT_ALL_TABLES：對所有表使用嚴格模式

服務器變量的類型

全局：對所有會話都生效

所有的會話在建立時都從全局繼承，但繼承完成后每個會話獨立維護自己會話級變量

修改全局的服務器變量僅對之后建立的會話生效

要求有管理權限

會話：僅對當前會話有效

修改即刻生效

不要求管理權限

修改方式

動態修改: 會話級別，立即生效；全局級別，新建立的會話有效，重啟服務會失效

靜態修改：要修改配置文件，或修改傳遞給mysqld的選項的值，重啟后有效

注：并非所有的服務器變量都支持動態修改

查看服務器變量

mysql> SHOW {GLOBAL|SESSION} VARIABLES [LIKE clause];

mysql> SELECT @@{GLOBAL|SESSION}.VARIABLE_NAME;

mysql> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='';

mysql> SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME='';

修改服務器變量

動態修改變量的值：
mysql> SET {GLOBAL|SESSION} VARIABLE_NAME='VALUE'
讓設置永久有效的方式：
[mysqld]
sql_mode = 'STRICT_ALL_TABLES'

語句書寫大小寫說明

①SQL關鍵字及函數名不區字符大小寫

②數據庫、表、索引及視圖的名稱是否區分大小寫取決于低層的OS及FS

③存儲過程、存儲函數及事件調度器不區分字符大小寫，但觸發器區分

④表別名不區分大小寫

⑤字段中字符數據，類型為binary、blog、varbinary時區分大小寫，其它的不區分

DDL操作

數據庫操作

創建數據庫
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
刪除數據庫
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
修改數據庫
ALTER {DATABASE | SCHEMA} [IF EXISTS] db_name

表操作

創建表
CREATE TABLE [IF NOT EXISTS] tb_name
(col1_def,col2_def,PRIMARY KEY(col_name,...),UNIQUE (col1,...),INDEX (col1,...))
[table_option]
table_option:
	ENGINE [=] engine_name
	COMMENT [=] 'string'
	ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
	TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name   #復制表數據
    [(create_definition,...)]
    [table_options]
    select_statement
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name   #復制表結構
    { LIKE old_tbl_name | (LIKE old_tbl_name) }
刪除表
DROP [TEMPORARY] TABLE [IF EXISTS]  tbl_name [, tbl_name] ...
[RESTRICT | CASCADE]  #cascade會將有依賴關聯的表一并刪除
修改表
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
    [alter_specification [, alter_specification] ...]
alter_specification:
#插入新字段
ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ]
#刪除字段
DROP [COLUMN] col_name
#修改字段屬性
ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} 
#修改字段名
CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
#修改字段類型及屬性等
MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
修改表名
ALTER TABLE tb_name RENAME TO new_tb_name;
RENAME TABLE old_name TO new_name;
#指定排序字段
ORDER BY col_name [, col_name] ...
#轉換字符集及排序規則
CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]

The end

MySQL/MariaDB的知識就先說到這里了，后續文章還會繼續講解MySQL/MariaDB的相關知識，有興趣可以繼續關注。以上僅為個人學習整理，如有錯漏，大神勿噴~~~