MySQL基礎知識

1、SQL：結構化查詢語言(Structured Query Language)：

DDL：Data?Definition?Language（數據定義語言）：?其語句包括動詞CREATE和DROP。在數據庫中創建新表或刪除表（CREAT TABLE?或?DROP TABLE）；為表加入索引等。DDL包括許多與人數據庫目錄中獲得數據有關的保留字。它也是動作查詢的一部分。

DML：Data Manipulation Language（數據操作語言） ：其語句包括動詞?INSERT，?UPDATE和?DELETE。它們分別用于添加，修改和刪除表中的行。也稱為動作查詢語言。

編程接口

存儲過程：procedure

存儲函數：function

觸發器：trigger

事件調度器：event scheduler

2、三層模型

物理層：決定數據的存儲格式，即如何將數據組織成為物理文件；

邏輯層：描述DB存儲什么數據，以及數據間存在什么樣的關系；

視圖層：描述DB中的部分數據

3、MySQL客戶端交互式CLI工具：mysql

常用選項：

-u, –user=username：用戶名，默認為root

-h, –host=hostname：遠程主機（即mysql服務器）地址，默認為localhost;?客戶端連接服務端，服務器會反解客戶的IP為主機名，關閉此功能(skip_name_resolve=ON)

-p, –password[=PASSWORD]：USERNAME所表示的用戶的密碼； 默認為空

注意：mysql的用戶賬號由兩部分組成：‘USERNAME’@’HOST’;?其中HOST用于限制此用戶可通過哪些遠程主機連接當前的mysql服務

HOST的表示方式，支持使用通配符

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

172.16.%.%, ?172.16.0.0/16

_：匹配任意單個字符

-P, –port=#：mysql服務器監聽的端口；默認為3306/tcp

-S, –socket=/PATH/TO/mysql.sock：套按字文件路徑

-D, –database=DB_name：連接到服務器端之后，設定其處指明的數據庫為默認數據庫

-e, –execute=’SQL STATEMENT’：連接至服務器并讓其執行此命令后直接返回

4、MySQL數據類型：

• 字符型：

? ? ? ? ? ? ? ? 定長字符型：

CHAR(#)：不區分字符大小寫（0-255字節）

BINARY(#)：區分字符大小寫

? ? ? ? ? ? ? ? ?變長字符型：

VARCHAR(#)：多占一個或兩個字符空間（最后要有一個字符串結束標記）不區分字符大小寫（0-65535?字節）

VARBINARY(#)：區分字符大小寫

? ? ? ? ? ? ? ? 對象存儲：

TEXT：不區分字符大小寫；TINYTEXT、SMALLTEXT、MEDIUMTEXT、TEXT、LONGTEXT

BLOB：Binary Large OBject，區分字符大小寫；TINYBLOB，SMALLBLOB，MEDIUMBLOB，BLOB，LONGBLOB；

• 數值型：

? ? ? ? ? ? ? 精確數值型：

? ? ? ? ? ? ? 近似數值型：

? ? ? ? ? ? ? ? ? ? ? ? ? ? FLOAT

? ? ? ? ? ? ? ? ? ? ? ? ? ? DOBULE

• 日期時間型：

?

• 字符類型修飾符：

? ? ? ? ? ? ? NOT NULL：非空約束

? ? ? ? ? ? ??NULL：允許為空

? ? ? ? ? ? ? DEFAULT value：指定默認值

CHARACTER SET：使用的字符集

? ? ? ? ? ? ? COLLATION：使用的排序規則

AUTO_INCREMENT：自動增長

• 整型數據修飾符

? ? ? ? ? ? ? NOT NULL：非空約束

? ? ? ? ? ? ? DEFAULT NUMBER：指定默認值

? ? ? ? ? ? ? PRIMARY KEY：主鍵

? ? ? ? ? ? ? UNIQUE KEY：惟一鍵

AUTO_INCREMENT：自動增長

5、數據定義語言（DDL）：

（1）數據庫

創建：

CREATE ?{DATABASE | SCHEMA} ?[IF NOT EXISTS] ?db_name;

CREATE ?{DATABASE 或SCHEMA}后面指明數據庫名稱

[DEFAULT] ?CHARACTER SET [=] charset_name

定義默認字符集

[DEFAULT] ?COLLATE [=] collation_name

定義排序規則

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

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

修改：

ALTER {DATABASE | SCHEMA} ?[db_name]

[DEFAULT] ?CHARACTER SET [=] charset_name

[DEFAULT] ?COLLATE [=] collation_name

刪除：

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

查看：

SHOW DATABASES LIKE ‘??’;

（2）表

創建：

CREATE TABLE [IF NOT EXISTS] [db_name.]tbl_name (create_defination) [table_options]

字段：col_name ?data_type

添加：ADD ?[COLUMN] ?col_name ?data_type ?[FIRST | AFTER col_name ]

刪除：DROP ?[COLUMN] col_name

修改：

CHANGE [COLUMN] old_col_name new_col_name column_definition ?[FIRST|AFTER col_name]

MODIFY [COLUMN] col_name column_definition ?[FIRST | AFTER col_name]

鍵：

PRIMARY KEY (col1, col2, …)

UNIQUE KEY ?(col1, col2,…)

FOREIGN KEY (column)

添加：ADD ?{PRIMARY|UNIQUE|FOREIGN} ?KEY (col1, col2,…)

刪除：

主鍵：DROP PRIMARY KEY

外鍵：DROP FOREIGN KEY fk_symbol

查看數據庫支持的所有存儲引擎類型

mysql> SHOW ?ENGINES；

查看某表的狀態信息：

mysql> SHOW ?TABLES ?STATUS ?[LIKE ?‘tbl_name’][WHERE clause]

修改：

ALTER [ONLINE|OFFLINE] [IGNORE] TABLE tbl_name [alter_specification [, alter_specification] …]

刪除：

DROP ?TABLE ?[IF EXISTS] ??tbl_name [, tbl_name] …????（不推薦使用）

表的引用方式：

tbl_name

db_name.tbl_name

（3）索引管理：

索引是特殊的數據結構；

索引：要有索引名稱；

創建：

CREATE ?[UNIQUE|FULLTEXT|SPATIAL] INDEX ?index_name ?[BTREE|HASH] ?ON tbl_name (col1, col2,,…)

刪除：

DROP ?INDEX index_name ON tbl_name

查看表上的索引的信息：

mysql> SHOW INDEXES FROM tbl_name;

（4）視圖

創建

CREATE VIEW view_name AS select_statement;

刪除

DROP VIEW view_name;

注意：視圖不能插入數據，受基表（創建視圖用到的表）限制，視圖的數據事實上存儲與”基表“中

6、數據操作語言（DML）：

【 INSERT、DELETE、SELECT、UPDATE】

? ? ? 1.INSERT語句：一次插入一行或多行
 ?insert into table_name[(column[column…])]values(value[,value…]);默認情況下，一次插入操作只插入一行
 ?一次性插入多條記錄：
 ?? insert into table_name[(column[,column…])]values(value[,value…]),(value[,value…]);
 ?如果為每列都指定值，則表名后不需要列出插入的列名
 ?如果不想在表名后列出列名，可以為那些無法指定的值插入NUll
 ?如果需要插入其他特殊字符，應該采用\轉義字符做前綴

? ? ? 2.DELETE語句
 ?語法：delete [from] table_name [where condition];
 ?刪除不需要指定列名，因為刪除總是整行整行刪除，where子句是一個條件表達式，只有符合該條件的行才會被刪除。沒有where子句意味著刪除所有數據
 ?刪除可以一次刪除多行，刪除哪些行采用where子句限定

? ? ? 3.UPDATE語句
 ?語法：update table_name set column = value[,column = value]…[where condition];
 ?? 修改可以一次修改多行數據，修改的數據可用where子句限定，where子句是一個條件表達式，只有符合該條件的行才會被修改。沒有where子句意味著where子句表達式值為true
 ?? 也可以同時修改多列，多列的修改中采用逗號隔開

? ? ? 4.SELECT語句
  語法：select_expr [, select_expr ...] [FROM table_references [WHERE where_condition]
  where子句：指明過濾條件以實現選擇的功能
     where子句限定返回的記錄，where子句在from子句后
     where中的字符串和日期值，字符串和日期要用單引號括起來，字符串是大小寫敏感的，日期值是格式敏感的
     比較操作符：>, >=, <, <=, =, <>, BETWEEN min_num AND max_num,
               in(element1,...)：匹配所有列出的值
 ???           like:模糊匹配（%：任意值，_:任意單個字符
               IS NULL是空值
     邏輯運算符：ONT, AND, OR, XOR（相同為假，不同為真）
  group by子句
 ???? group by子句的真正作用在于與各種聚合函數配合使用，它用來對查詢出來的數據進行分組
 ???? 分組的含義是：把該列具有相同值的多條記錄當成一組記錄處理，最后只輸出一條記錄
 ???? 分組函數忽略空值
 ???? 結果集隱式按升序排列，如果需要改變排序方式可以使用order by子句
      count(*|列名)??統計行數 avg(數值類型列名)??平均值 sum(數值類型列名)??求和
 ???  max(列名)???最大值      min(列名)???最小值
?     having子句用來對分組后的結果再進行條件過濾
  在select語句最后可以用limit來限定查詢結果返回的起始記錄和總數量
  對查詢結果中的數據請求施加鎖
       for update：寫鎖，獨占鎖
       lock in share mode：讀鎖，共享鎖

7、數據庫的設計
-當數據庫比較復雜時我們需要設計數據庫
-良好的數據庫設計
-節約數據的存儲空間
-能夠保證數據的完整性
-方便進行數據庫應用系統的開發

☆數據庫設計規范化
-僅有好的DBMS并不足以避免數據冗余，必須在數據庫的設計中創建好的表結構
-數據庫規范化的三個級別，范式是具有最小冗余的表結構，如下：
-第一范式：(1stNF first normal formate)
-第二范式：(2stNF second normal formate)
-第三范式：(3stNF third normal formate)

◆第一范式(1NF)（第一范式必須有主鍵）
-在關系模式R中的每一個具體關系r中，必須要有主鍵，并且每個屬性都是不可再分的最小數據單位

◆第二范式(2NF)
-如果關系模式R中的所有非主屬性都完全依賴于主關鍵字，則稱關系R是屬于第二范式

◆第三范式(3NF)
-關系模式R中的非主關鍵字不能依賴于其他非主關鍵，即非主關鍵字之間不能有函數(傳遞)依賴關系，則稱關系R是屬于第三范式

8、一個存儲系統是否支持事務，測試標準：

ACID：

ACID是數據庫事務正確執行的四個基本要素的縮寫，即原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）、持久性（Durability）。一個支持事務（Transaction）的數據庫系統，必需要具有ACID特性，否則在事務過程（Transaction processing）當中無法保證數據的正確性，交易過程極可能達不到交易方的要求。

A：原子性

整個事務中的所有操作，要么全部完成，要么全部不完成，不可能停滯在中間某個環節。事務在執行過程中發生錯誤，會被回滾（Rollback）到事務開始前的狀態，就像這個事務從來沒有執行過一樣。

C：一致性

一個事務可以封裝狀態改變（除非它是一個只讀的）。事務必須始終保持系統處于一致的狀態，不管在任何給定的時間并發事務有多少。

也就是說：如果事務是并發多個，系統也必須如同串行事務一樣操作。其主要特征是保護性和不變性(Preserving an Invariant)，以轉賬案例為例，假設有五個賬戶，每個賬戶余額是100元，那么五個賬戶總額是500元，如果在這個5個賬戶之間同時發生多個轉賬，無論并發多少個，比如在A與B賬戶之間轉賬5元，在C與D賬戶之間轉賬10元，在B與E之間轉賬15元，五個賬戶總額也應該還是500元，這就是保護性和不變性

I：隔離性

隔離狀態執行事務，使它們好像是系統在給定時間內執行的唯一操作。如果有兩個事務，運行在相同的時間內，執行相同的功能，事務的隔離性將確保每一事務在系統中認為只有該事務在使用系統。這種屬性有時稱為串行化，為了防止事務操作間的混淆，必須串行化或序列化請求，使得在同一時間僅有一個請求用于同一數據

D：持久性

在事務完成以后，該事務對數據庫所作的更改持久的保存在數據庫之中，并不會被回滾。

由于一項操作通常會包含許多子操作，而這些子操作可能會因為硬件的損壞或其他因素產生問題，要正確實現ACID并不容易。ACID建議數據庫將所有需要更新以及修改的資料一次操作完畢，但實際上并不可行。 目前主要有兩種方式實現ACID：第一種是Write ahead logging，也就是日志式的方式(現代數據庫均基于這種方式)。第二種是Shadow paging。

9、編譯安裝mariadb-5.5.44

# yum install cmake
# yum groupinstall "Development Tools" "Server Platform Development"
# groupadd -r -g 306 mysql
# useradd -r -g 306 mysql -u 306 mysql
# tar xf mariadb-5.5.44.tar.gz
# cd maeradb-5.5.44
# cmake
DCMAKE_INSTALL_PREFIX=/usr/local/mariadb-5.5.44\    安裝路徑
DMYSQL_DATADIR=/mydata/data\                        數據目錄
DSYSCONFDIR=/etc                                    指定安裝文件的安裝路徑
DMYSQL_UNIX_ADDR=/tmp/mysqld.sock\                  sock文件
DDEFAULT_CHARSET=utf8\                              默認字符集
DDEFAULT_COLLATION=utf8_general_ci\                 默認排序規則
DWITH_INNOBASE_STORAGE_ENGINE=1\                    默認編譯的存儲引擎
DWITH_ARCHIVE_STORAGE_ENGINE=1\
DWITH_BLACKHOLE_STORAGE_ENGINE=1\
DWITHOUT_READLINE=1\
DWITH_ZLIB=system\
DWITH_SSL=system\
DWITH_LIBWRAP=0\
# make
# make install
# cd /usr/local
# ln -sv mariadb-5.5.44 mysql
# chmod -R root.mysql ./*
# ll
# scrips /mysql_install_db --user=mysql --datadir=/mydata/data 
# cp support-file/my-large.cnf /etc/mysql/my.cnf
# vim /etc/mysql/my.cnf
     datadir=/mydata/data
     innodb_file_per_table = ON
     skip_name_resolve = ON
# cp support-file/mysql.server /etc/rc.d/init.d/mysqld
# chmod +x /etc/rc.d/init.d/mysqld
# chkconfig --add mysqld
# service mysqld start
# /usr/local/mysql/bin/mysql_secure_installation     給所有用戶設定密碼，刪除匿名用戶
# /usr/local/mysql/bin/mysql

 
?