MySQL(一)：基本原理

一、資料庫定義

資料庫，簡單來說是本身可視為電子化的檔案櫃——存儲電子檔案的處所，使用者可以對檔案中的資料運作新增、截取、更新、删除等操作。

資料庫指的是以一定方式儲存在一起、能為多個使用者共享、具有盡可能小的備援度、與應用程式彼此獨立的資料集合。

資料庫的組織模式：

層次型資料結構

網狀型資料結構

關系型資料結構(MySQL)

非關系型資料結構(NoSQL)

關系型資料結構圖示

上圖描述了從使用者層到實體層的工作結構。

二、資料庫基礎知識

SQL：structure query language；一種介于關系代數與關系演算之間的結構化查詢語言，是一個通用的、功能極強的關系性資料庫語言。

SQL語言組成部分：

資料定義語言(DDL:Data Definition Language)：CREATE, DROP, ALTER

資料操縱語言(DML:Data Manipulation Language)：SELECT, INSERT, UPDATE, DELETE

資料控制語言(DCL:Data Control Language)：GRANT, REVOKE

事務(transaction)：事務就是一組原子性的查詢語句；也即将多個查詢當作一個獨立的工作單元。

ACID測試：能滿足ACID測試就表示其支援事務；或相容事務。

原子性（Atomic）:事務作為一個整體被執行，包含在其中的對資料庫的操作要麼全部被執行，要麼都不執行。

一緻性（Consistency）:事務應確定資料庫的狀态從一個一緻狀态轉變為另一個一緻狀态。一緻狀态的含義是資料庫中的資料應滿足完整性限制。

隔離性（Isolation）:多個事務并發執行時，一個事務所做的操作在送出之前對其他事務是不可見的。

持久性（Durability）:已被送出的事務對資料庫的修改應該永久儲存在資料庫中。

三、MySQL基礎特性

鍵：key(字段)

主鍵：能夠唯一辨別表中每一個記錄的字段或字段的組合。

候選鍵：能夠作為唯一辨別表中每一個記錄的字段。

外鍵：依賴别的表當做屬性以建立彼此的關系；而這個屬性就是外鍵。

唯一鍵：可以确定一條記錄的字段(或字段組合)，一張表可以有多個唯一鍵。

限制：constraint

主鍵限制：用于定義基本表的主鍵，起惟一辨別作用；對一張表來說；主鍵隻能有一個；不能為空；不能重複。

外鍵限制：定義了一個表中資料與另一個表中的資料的聯系；對應其他表中的字段；在自己表中沒有的不能填入。

唯一鍵限制：用于指明建立惟一限制的列上的取值必須惟一；可以為空；不能重複；可以有多個。

檢查式限制：使用者自定義有效取值範圍；通常為布爾表達式。

非空限制：隻用于定義列限制。

mysql中的比較方式、存儲空間的取值範圍以及參與的運算等都取決于mysql的資料類型；是以資料類型非常重要；具體的字段的資料類型：

字元型

char(#)

用于儲存定長(size)位元組的字元串資料。編碼時不區分字元大小寫。最大長度為255位元組.

varchar(#)

用于儲存變長的字元串資料。其中最大位元組長度由(size)指定。每行長度可變，最大長度為65535位元組。資料長度預設為1 ；編碼時區不分字元大小寫。

binary(#)

固定長度的二進制字元串；每行定長(不足部分補為空格)；最大長度為255位元組，預設值為每行1位元組。編碼時區分字元大小寫。

varbinary(#)

可變長度的長度的二進制字元串。每行長度可變，最大長度為65535位元組。資料長度預設為1。編碼時區分字元大小寫。

text

一個BLOB或TEXT列，最大長度為65535(2^16-1)個字元；編碼時不區分字元大小寫

blob

二進制的大對象；一個BLOB或TEXT列，最大長度為65535(2^16-1)個字元；編碼時區分字元大小寫。

數值型

精确數值型

tinyint

儲存整型資料，範圍為0到255。最大長度為1位元組。

smallint

儲存整型資料，範圍為-32767到+32767最,大長度為2位元組。

mediumint

儲存整型資料，範圍為-8388608至8388607或0至16777215。最大長度為3位元組。

int

儲存整型資料，範圍為-2147483647到+2147483647。最大長度為4位元組。

bigint

儲存整型資料，範圍為-9223372036854775808至9223372036854775807或0至18446744073709551615。最大長度為8位元組

decimal

儲存精确數值資料類型，範圍為-10E-38到+10E-38,最大長度為2~17位元組。

近似數值型

float

最小非零值：±1.175494351E – 38，同double一樣适用于精度要求高的場合

double

最小非零值：±2.2250738585072014E - 308

日期時間型

date

1000-01-01~9999-12-31 3位元組

time

-838:59:59~838:59:59 3位元組

datetime

1000-01-01 00:00:00~9999-12-31 23:59:59 8位元組

timestamp

19700101000000~指定時間的某個時刻 4位元組

year

YYYY 格式表示的年份值

MySQL内置類型

enum

枚舉類型(指定範圍内中某一種)

set

集合型(指定範圍内任意組合)

布爾型：tinyint{0|1}

NULL

字段或字段類型的修飾符

not null

非空

null

空

unsigned

無符号整型(隻能用于數值)

default [value]

字元需要加引号；資料絕對不能加引号

auto_increment

自動增長類型的字段必須為主鍵或唯一鍵

primary key

定義主鍵

unique key

定義唯一鍵

Example:

<code>mysql> create table stu1 (</code><code>Id</code> <code>int</code> <code>unsigned auto_increment unique key,Name char(</code><code>30</code><code>) </code><code>not</code> <code>null,Age tinyint unsigned </code><code>not</code> <code>null,Gender enum(</code><code>'F'</code><code>,</code><code>'M'</code><code>) </code><code>not</code> <code>null default </code><code>'M'</code><code>);</code>

<code>Query OK, </code><code>0</code> <code>rows affected (</code><code>0.04</code> <code>sec)</code>

<code>mysql> desc stu1;</code>

<code>mysql></code>

<code>mysql> insert into stu1 (Name,Age,Gender) values (</code><code>'Tom'</code><code>,</code><code>20</code><code>,</code><code>'M'</code><code>),</code>

<code> </code><code>-</code><code>> (</code><code>'Pipi'</code><code>,</code><code>20</code><code>,</code><code>'M'</code><code>), </code><code>#這裡插入資料都沒有插入Id号；因為上述定義的是自增長</code>

<code> </code><code>-</code><code>> (</code><code>'Jerry'</code><code>,</code><code>18</code><code>,</code><code>'F'</code><code>),</code>

<code>Query OK, </code><code>4</code> <code>rows affected (</code><code>0.00</code> <code>sec)</code>

<code>Records: </code><code>4</code> <code>Duplicates: </code><code>0</code> <code>Warnings: </code><code>0</code>

<code>mysql> select </code><code>*</code> <code>from</code> <code>stu1; </code><code>#顯示表字段資訊</code>

SQL語句可以直接在mysql裡面幫助擷取：幫助資訊顯示的也很詳細

<code>mysql> </code><code>help</code> <code>insert</code>

<code>#[]括号内的都是可省的；{}内是必選的；</code>

<code>Name: </code><code>'INSERT'</code>

<code>Description:</code>

<code>Syntax:</code>

<code>INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]</code>

<code> </code><code>{VALUES | VALUE} ({expr | DEFAULT},...),(...),...</code>

<code> </code><code>[ ON DUPLICATE KEY UPDATE</code>

<code>mysql> </code><code>help</code> <code>select</code>

<code>Name: </code><code>'SELECT'</code>

<code>SELECT</code>

<code> </code><code>[</code><code>ALL</code> <code>| DISTINCT | DISTINCTROW ]</code>

<code> </code><code>[HIGH_PRIORITY]</code>

<code> </code><code>[STRAIGHT_JOIN]</code>

<code> </code><code>[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]</code>

<code> </code><code>[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]</code>

<code> </code><code>select_expr [, select_expr ...]</code>

<code> </code><code>[FROM table_references</code>

<code> </code><code>[WHERE where_condition]</code>

<code> </code><code>[GROUP BY {col_name | expr | position}</code>

<code> </code><code>[ASC | DESC], ... [WITH ROLLUP]]</code>

<code> </code><code>[HAVING where_condition]</code>

<code> </code><code>[ORDER BY {col_name | expr | position}</code>

<code> </code><code>[LIMIT {[offset,] row_count | row_count OFFSET offset}]</code>

<code> </code><code>[PROCEDURE procedure_name(argument_list)]</code>

<code> </code><code>[INTO OUTFILE </code><code>'file_name'</code>

<code> </code><code>[CHARACTER </code><code>SET</code> <code>charset_name]</code>

<code> </code><code>export_options</code>

<code> </code><code>| INTO DUMPFILE </code><code>'file_name'</code>

<code> </code><code>[FOR UPDATE | LOCK IN SHARE MODE]]</code>

四、布爾條件表達式操作符

equal；等于

<=>

null sale equal to；與空值比較的安全方式

<> | !=

not equal to；不等于

less；小于

less than or equal to；小于等于

greater；大于

greater than or equal to；大于等于

is null

為空

is not null

不為空

通配；%(任意長度的任意字元), _(任意單個字元)

regexp | rlike

支援使用正規表達式

判斷指定字段是否在給定的清單中

between... and...

位于指定的範圍之間

組合條件測試

not | !

非

and | &&

與

or | ||

或

聚合函數

sum()

求和

avg()

求平均值

max()

最大值

min()

最小值

count()

計數

根據資料庫操作語言(DML)列出幾個簡單的例子；後續作詳細說明

Examples:

select：

<code>mysql> select </code><code>*</code> <code>from</code> <code>students;</code>

<code>mysql> select Name,Age </code><code>from</code> <code>students where Age > </code><code>20</code><code>;</code>

<code>#查找年齡大于20的</code>

<code>mysql> select Name,Gender </code><code>from</code> <code>students where Gender </code><code>=</code> <code>'F'</code><code>;</code>

<code>#查找性别為女的</code>

<code>| Name | Gender |</code>

<code>| Xue Baochai | F |</code>

<code>mysql> select Name,Age,Gender </code><code>from</code> <code>students where Age > </code><code>20</code> <code>and</code> <code>Gender </code><code>=</code> <code>'F'</code><code>;</code>

<code>| Name | Age | Gender |</code>

delete：

<code>mysql> delete </code><code>from</code> <code>students where Name like </code><code>'X%'</code><code>;</code>

<code>Query OK, </code><code>2</code> <code>rows affected (</code><code>0.00</code> <code>sec)</code>

update：

<code>mysql> update students </code><code>set</code> <code>Age </code><code>=</code> <code>40</code> <code>where Name </code><code>=</code> <code>'Wu yong'</code><code>;</code>

<code>Query OK, </code><code>1</code> <code>row affected (</code><code>0.01</code> <code>sec)</code>

<code>Rows matched: </code><code>1</code> <code>Changed: </code><code>1</code> <code>Warnings: </code><code>0</code>

幾個簡單show指令的用法

<code>mysql> show create table stu1\G;</code>

<code>#檢視建立表所使用的指令。</code>

<code>#\G為用戶端指令；可以使用\?來檢視。</code>

<code> </code><code>Table: stu1</code>

<code>Create Table: CREATE TABLE `stu1` (</code>

<code> </code><code>`</code><code>Id</code><code>` </code><code>int</code><code>(</code><code>10</code><code>) unsigned NOT NULL AUTO_INCREMENT,</code>

<code> </code><code>`Age` tinyint(</code><code>3</code><code>) unsigned NOT NULL,</code>

<code> </code><code>`Gender` enum(</code><code>'F'</code><code>,</code><code>'M'</code><code>) NOT NULL DEFAULT </code><code>'M'</code><code>,</code>

<code> </code><code>UNIQUE KEY `</code><code>Id</code><code>` (`</code><code>Id</code><code>`)</code>

<code>) ENGINE</code><code>=</code><code>MyISAM AUTO_INCREMENT</code><code>=</code><code>9</code> <code>DEFAULT CHARSET</code><code>=</code><code>latin1</code>

<code>ERROR:</code>

<code>No query specified</code>

<code>mysql> show engines\G;</code>

<code>#檢視目前資料庫支援的存儲引擎</code>

<code> </code><code>Engine: MRG_MYISAM</code>

<code> </code><code>Support: YES </code><code>#是否支援</code>

<code> </code><code>Comment: Collection of identical MyISAM tables</code>

<code>Transactions: NO </code><code>#是否支援事務</code>

<code> </code><code>XA: NO </code><code>#是否支援分布式事務</code>

<code> </code><code>Savepoints: NO </code><code>#是否支援儲存點</code>

<code> </code><code>Engine: CSV</code>

<code> </code><code>Support: YES</code>

<code> </code><code>Comment: CSV storage engine</code>

<code>Transactions: NO</code>

<code> </code><code>Savepoints: NO</code>

<code> </code><code>Engine: MyISAM</code>

<code> </code><code>Support: DEFAULT</code>

<code> </code><code>Comment: Default engine as of MySQL </code><code>3.23</code> <code>with great performance</code>

<code> </code><code>Engine: InnoDB</code>

<code> </code><code>Comment: Supports transactions, row</code><code>-</code><code>level locking, </code><code>and</code> <code>foreign keys</code>

<code>Transactions: YES</code>

<code> </code><code>Savepoints: YES</code>

<code> </code><code>Engine: MEMORY</code>

<code> </code><code>Comment: </code><code>Hash</code> <code>based, stored </code><code>in</code> <code>memory, useful </code><code>for</code> <code>temporary tables</code>

<code>mysql> show character </code><code>set</code><code>;</code>

<code>#顯示字元集</code>

<code>mysql> show collation;</code>

<code>#檢視show的幫助都有詳細說明</code>

<code>mysql> </code><code>help</code> <code>show</code>

<code>SHOW has many forms that provide information about databases, tables,</code>

<code>columns, </code><code>or</code> <code>status information about the server. This section describes</code>

<code>those following:</code>

<code>SHOW AUTHORS</code>

<code>SHOW {BINARY | MASTER} LOGS</code>

<code>SHOW BINLOG EVENTS [IN </code><code>'log_name'</code><code>] [FROM pos] [LIMIT [offset,] row_count]</code>

<code>SHOW CHARACTER </code><code>SET</code> <code>[like_or_where]</code>

<code>SHOW COLLATION [like_or_where]</code>

<code>SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]</code>

<code>SHOW CONTRIBUTORS</code>

<code>SHOW CREATE DATABASE db_name</code>

<code>SHOW CREATE EVENT event_name</code>

<code>SHOW CREATE FUNCTION func_name</code>

<code>SHOW CREATE PROCEDURE proc_name</code>

<code>SHOW CREATE TABLE tbl_name</code>

<code>SHOW CREATE TRIGGER trigger_name</code>

關系型資料庫的基本用法到此，由于資料庫理論等方面内容很多；無法總結的很全面；可能存在很多遺漏的地方。後續介紹詳細用法。

本文轉自Mr_陳 51CTO部落格，原文連結：http://blog.51cto.com/chenpipi/1392761，如需轉載請自行聯系原作者

MySQL(一)：基本原理

繼續閱讀

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述