30000字學完MySql資料庫(安裝、SQL、索引、事務等)

1. 資料庫基本介紹

1.1 什麼是資料庫

資料庫: 用於儲存資料的倉庫. 本質上就是一個檔案系統, 只是需要訪問這個檔案系統需要使用特定的語句(sql)進行操作.

1.2 資料庫的作用

我們開發應用程式的時候，程式中的所有資料，最後都需要儲存到專業軟體中。這些專業的儲存資料的軟體我們稱為資料庫。

我們學習資料庫，並不是學習如何去開發一個資料庫軟體，我們學習的是如何使用資料庫以及資料庫中的資料記錄的操作。而資料庫軟體是由第三方公司研發。

1.3 常見的資料庫

資料庫一般被分為兩大類:  關係型 、非關係型

常見的關係型資料庫軟體:

• Oracle:  是Oracle公司的大型關係型資料庫, 收費的

• DB2: IBM公司提供的資料庫產品, 收費的

• SqlServer: 微軟提供的資料庫產品, 收費的

• Sybase: Sybase公司的 , 目前已經過時了, 但其資料庫建模工具(PowerDesign)使用較為廣泛

• MySQL: 早期瑞典一個公司發明，後期被sun公司收購，後期被Oracle。

java 中常用的資料庫產品:

MySQL(5.x) 、Oracle、DB2

1.4 什麼是關係型資料庫

在開發軟體的時候，軟體中的資料之間必然會有一定的關係存在，需要把這些資料儲存在資料庫中，同時也要維護資料之間的關係，這時就可以直接使用上述的那些資料庫。而上述的所有資料庫都屬於關係型資料庫。

描述資料之間的關係，並儲存在資料庫中，同時學習如果根據這些關係查詢資料庫中的資料，

關係型資料：設計資料庫的時候，需要使用E-R圖來描述。

E-R：實體關係圖。

實體：可以理解成我們Java程式中的一個物件。在E-R圖中使用 矩形(長方形) 表示。

針對一個實體中的屬性，我們稱為這個實體的資料，在E-R圖中使用 橢圓表示。

實體和實體之間的關係：在E-R圖中使用菱形表示。

ER圖示:

矩形用來表示實體
橢圓用來表示實體的屬性
菱形用來表示之間的關係

2. mysql如何在linux安裝

2.1 vmware中安裝完linux注意事項

2.1.1 防火牆的問題

service iptables stop  (立即生效,重啟失效)
chkconfig iptables off (重啟生效,永久關閉防火牆)

2.1.2 軟體安裝統一的管理目錄

安裝包存放位置:  mkdir -p /export/software
軟體安裝位置: mkdir -p /export/servers
資料儲存的位置: mkdir -p /export/data
日誌儲存的位置: mkdir -p /export/logs

2.1.3 軟體環境

vmware(12以上)、CRT(連線linux工具)、centos6.9

2.1.4 安裝環境

1、VMware軟體安裝

2、構建虛擬機器

3、需要配置Linux（ip,mac地址，hostname，防火牆），就可以透過crt這個客戶端連線進行操作

4、在linux作業系統進行安裝msyql-5.6
 
 說明：因為在linux作業系統中，安裝軟體的方式主要有3種：
 1、原始碼安裝（redis）
 2、rpm安裝   
 3、yum線上安裝 ---linux聯網（）

2.2 虛擬機器中安裝MySQL

1. 檢測是否自帶的MySQL

rpm -qa |grep mysql

1. 解除安裝自帶的MySQL

rpm -e --nodeps mysql-libs-5.1.73-8.el6_8.x86_64

1. 上傳mysql的安裝包

建議使用rz進行上傳即可:  
 下載上傳外掛(下載一次即可): yum -y install lrzsz
 使用 rz 命令上傳即可或者直接拖
 
上傳目錄:  /export/software

1. 安裝即可

rpm -ivh *.rpm

1. 檢視初始化密碼:

安裝成功後, 會產生一個隨機密碼, 隨機密碼放置的位置: /root/.mysql_secret

cat /root/.mysql_secret

1. 啟動MySQL並登陸

啟動mysql服務:
 service mysql start
 
登陸mysql:
 mysql -uroot -p密碼
  (密碼即為檢視到的隨機密碼)

1. 修改密碼

set PASSWORD=PASSWORD('123456');

1. 退出MySQL客戶端

quit;

1. 用新密碼進行登入即可

mysql -uroot -p(新密碼)

1. 遠端授權

grant all privileges on *.* to 'root' @'%' identified by '123456'; 
flush privileges;

1. 驗證遠端授權是否成功

透過Windows的mysql連線工具連線遠端linux上的mysql, 如果正常連線成功說明授權成功

3. MySQL的基礎操作篇

3.1 登入與退出

登入:
mysql -uroot -p密碼

退出:
 quit

3.2 輸入查詢

1. 檢視當前mysql的版本號及當前時間

SELECT VERSION(),CURRENT_DATE();

注意:

1) SQL語句不區分大小寫, 只要單詞寫對了, 即可查詢

1. 使用SQL 可以進行簡單的運算

SELECT SIN(PI()/4), (4+1)*5; 

注意:

1) PI()   表示的圓周率 3.141593

2) 計算時如果有小括號, 會先計算小括號裡面的內容

3) SQL語句以分號結尾, 一個分號就代表是一句SQL的結束

select SIN(PI()/4) ; select (4+1)*5;   這是兩句SQL

4) 在進行書寫的時候,如果一條SQL太長, 可以分為多行書寫, 只要不輸入分號, 就不會認為SQL已經結束

5) 寫到一半, 如果不想執行SQL了, 在SQL後面加上\c 然後回車即可

3.3 建立和使用資料庫

1. 檢視當前有那些資料庫

show databases ; 

1. 建立資料庫

create databases 資料庫名稱;

1. 使用和切換資料庫

use 資料庫名稱 ; 

3.4 建立表及使用

1. 檢視當前資料庫中有那些表

show tables ;

1. 建立一個表

create table 表名 (
 欄位名稱(長度) 資料型別  [約束] ,
 欄位名稱(長度) 資料型別  [約束] ,
 欄位名稱(長度) 資料型別  [約束] 
)


例如: 
  CREATE TABLE pet (
    name VARCHAR(20),   
    owner VARCHAR(20),
    species VARCHAR(20), 
    sex CHAR(1), 
    birth DATE, 
    death DATE
  );

1. 檢視錶的結構

show create table 表名  ; 
desc 表名 ;

• 
  
• 第一種：將以上資料整理成SQL語句，insert into pet....
• 第二種：透過載入檔案的方式將資料匯入到表中
• 在表中匯入資料的方式有兩種

1. 在表建立後, 匯入資料

第二種: 在本地建立一個pet.txt檔案, 名稱可以自定義 （注：每個欄位中用tab鍵隔開，欄位沒有值得記錄用\N代替）

檔案的內容:
Fluffy  Harold  cat     f       1993-02-04
Claws   Gwen    cat     m       1994-03-17
Buffy   Harold  dog     f       1989-05-13
Fang    Benny   dog     m       1990-08-27
Bowser  Diane   dog     m       1979-08-31      1995-07-29
Chirpy  Gwen    bird    f       1998-09-11
Whistler        Gwen    bird    \N      1997-12-09      \N
Slim    Benny   snake   m       1996-04-29

執行載入檔案的SQL將其匯入表中:
 load data local infile '本地路徑' into table 表名 ;
 範例:
  load data local infile '/root/pet.txt' into table pet;
 
檢驗是否已經匯入成功:
 select * from 表名 ; 
注意:
 put -a 檔案路徑 //將指定檔案上傳的伺服器.
  示例: put -a D:\compile\pet.txt
 rm -rf 檔名   //移除指定的檔案.
  示例: rm -rf test01.sql

3.5 資料查詢部分

1. 查詢全部資料

select * from 表名 ; 

1. 刪除表中全部資料

delete from 表名 ;

1. 更新表中指定記錄的資料

update 表名 set 欄位名稱1 = 值1,欄位名稱2 = 值2,欄位名稱3 = 值3 where 條件 ; 

注意: 如果沒有where條件, 就會對錶中所有的資料進行對應修改

1. 查詢特定的行

select * from 表名 where 欄位名稱 = '值' ; 

1. 查詢某個範圍的資料

select * from 表名 where 欄位名稱 >|>=|<|<= 值 ;
select * from 表名 where 欄位名稱 between  min  and  max ;

1. 多條件查詢:  and  和 or

select * from 表名 where 條件1 and|or  條件2 and|or (條件3  and|or 條件4) ....;

and:  並列關係, 結果必須是and條件內的資料
or:  或者關係 , 結果如果有展示, 沒有就不展示
如果有括號的, 最佳化執行括號內的資料

1. 查詢返回指定列的資料

select 欄位名稱1,欄位名稱2... from  表名 ; 

1. 如果表中有重複資料, 可以將其去重 :  distinct

select distinct 欄位名稱1,欄位名稱2... from  表名 ;

1. 排序操作: order by

SELECT *|欄位列表 FROM 表名 ORDER BY 排序欄位1 [排序方式],排序欄位2 [排序方式];


排序方式:  desc  和 asc  , 預設為升序排序

如果是多個欄位排序, 會先按照第一個欄位進行排序,如果第一個欄位有相同的, 然後在按照第二個欄位排序

1. 日期計算

查詢當前的日期:
 select curdate() from pet;
獲取當年的年 :
 select YEAR('2018-02-05') AS YEARS from pet;
獲取當年的月
 select month('2018-02-05') AS month from pet;
獲取當年的日
 select day('2018-02-05') AS day from pet; 
 
計算年齡:
 SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet;

1. is null和 is not null值

SELECT name, birth, death, TIMESTAMPDIFF(YEAR,birth,death) AS age FROM pet WHERE death IS NOT NULL ORDER BY age;

格式:  欄位  is null   篩選這個欄位為null的資料
   欄位  is not null 篩選這個欄位不為null的資料

4. 案例

1. 建立表和匯入資料

CREATE TABLE shop (
 article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
 dealer CHAR(20) DEFAULT '' NOT NULL,
 price DOUBLE(16,2) DEFAULT '0.00' NOT NULL,
 PRIMARY KEY(article, dealer)
);


INSERT INTO shop VALUES
(1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
(3,'C',1.69),(3,'D',1.25),(4,'D',19.95);

1. 檢索表中的全部資料

select * from shop;

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  3.45 |
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | B      |  1.45 |
|    0003 | C      |  1.69 |
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

1. 求某一列的最大值或者最小值

SELECT MAX(article) AS article FROM shop;

+---------+
| article |
+---------+
|       4 |
+---------+

 //求某一列的最小值
select min(price) as article from shop; 
 
+---------+
| article |
+---------+
|    1.25 |
+---------+

1. 過濾出某個欄位值最大的整條記錄資料

使用子查詢:
 SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop);

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0004 | D      | 19.95 |
+---------+--------+-------+

使用自關聯查詢:
 SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.price < s2.price WHERE s2.article IS NULL;
 
 SELECT article, dealer, price FROM shop ORDER BY price DESC LIMIT 1;

1. 求出每一列的最大值，並且根據某一個欄位進行分組--分組topn求法

SELECT article, MAX(price) AS price FROM shop GROUP BY article;

+---------+-------+
| article | price |
+---------+-------+
|    0001 |  3.99 |
|    0002 | 10.99 |
|    0003 |  1.69 |
|    0004 | 19.95 |
+---------+-------+

5. SQL中的聚合函式

SQL語言中定義了部分的函式，可以幫助我們完成對查詢結果的計算操作：

1. count 統計個數（行數）

1. sum函式：求和

1. avg函式：求平均值

1. max、min 求最大值和最小值

5.1 count函式

語法: select count(*|列名) from 表名;

注意：count在根據指定的列統計的時候，如果這一列中有null 不會被統計在其中。

mysql> select * from pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
+----------+--------+---------+------+------------+------------+
8 rows in set (0.00 sec)

mysql> select count(sex) from pet; 
+------------+
| count(sex) |
+------------+
|          7 |
+------------+
1 row in set (0.00 sec)

mysql> select count(owner) from pet;   
+--------------+
| count(owner) |
+--------------+
|            8 |
+--------------+
1 row in set (0.00 sec)

mysql> select count(death) from pet;     
+--------------+
| count(death) |
+--------------+
|            1 |
+--------------+

5.2 sum 函式

語法: select sum(列名) from 表名;

注意事項：

1、如果使用sum 多列進行求和的時候，如果某一列中的有null，這一列所在的行中的其他資料不會被加到總和。

2、可以使用mysql 資料庫提供的函式 ifnull(列名,值)

3、在資料庫中定義double型別資料，是一個近似值，需要確定準確的位數，這時可以把這一列設計成numeric型別。numeric(資料的總列數,小數位數)

mysql> select sum(price) from shop;  
+------------+
| sum(price) |
+------------+
|      42.77 |
+------------+

5.3 avg 函式

語法：select avg(列名) from 表名;

mysql> select avg(price) from shop;   
+------------+
| avg(price) |
+------------+
|   6.110000 |
+------------+

5.4 max函式

語法：select max(列名) from 表名;

mysql> select max(price) from shop;   
+------------+
| max(price) |
+------------+
|      19.95 |
+------------+

5.5 min函式

語法：select min(列名) from 表名;

mysql> select min(price) from shop;   
+------------+
| min(price) |
+------------+
|       1.25 |
+------------+

6. SQL分類

6.1 DDL語句 (資料庫定義語言)

對資料庫 表 列 進行操作

檢視資料庫:show database;
建立資料庫 create database 資料庫名;
          create database 資料庫名 character set 需要使用的編碼;
查詢建立資料庫語句:show create database 資料庫名;
刪除資料庫 drop database 需要刪除的資料庫名;
檢視當前正在使用的資料庫 select database();
使用資料庫: use 資料庫名


建立表: creat table 表名(欄位名 型別(長度) [約束],欄位名 型別(長度) [約束]..);
檢視錶: show tables;
檢視建立表的語句: show creat table 表名;
檢視錶結構: desc 表名;
刪除表: drop table 表名;
向表中新增欄位: alter table 表名 add 欄位名 型別 [約束];
修改 對欄位進行修改 但不修改欄位名: alter table 表名 modify 欄位名 型別 [約束];
改變 對欄位進行修改 可以修改欄位名: alter table 表名 change 舊欄位名 新欄位名 型別 [約束];
刪除表中的列: alter table 表名 欄位;
修改表名: rename table 舊錶名 to 新表名;

6.2 DML語句(資料庫操作語言)

對資料庫裡的資料進行操作  增 刪 改

如 update， insert， delete

insert into 表名(欄位名,欄位名..) values(值,值,..);
insert into 表名 values(值,值,..);
update 表名 set 欄位=值,欄位=值..where 條件 ;
delete from 表名 where 條件 ;

truncate delete區別: 
    delete 刪除資料時 自動增長不會清除
    truncate刪除資料時 清除自動增長 重寫計數

6.3 DCL語句(資料庫控制語言)

用於設定使用者許可權和控制事務語句

如 grant......

6.4 DQL語句(資料庫查詢語言)

對資料庫進行查詢

 select distinct * from 表名 where 條件 
     group by 分組欄位 having分組完接條件篩選條件
     order by 排序
     distinct  去掉重複

7. 資料庫的備份與恢復

7.1 備份命令

在mysql的安裝目錄的bin目錄下有mysqldump命令，可以完成對資料庫的備份。

語法：mysqldump -u 使用者名稱 -p 資料庫名> 磁碟SQL檔案路徑

由於mysqldump命令不是sql命令，需要在dos視窗下使用。

注意：在備份資料的時候，資料庫不會被刪除。可以手動刪除資料庫。同時在恢復資料的時候，不會

自動的給我們建立資料庫，僅僅只會恢復資料庫中的表和表中的資料。

例如: mysqldump -uroot -p123456 test01 >/root/data/test01.sql
其中: test01是資料庫的名字

7.2 恢復命令

恢復資料庫，需要手動的先建立資料庫：

create database heima2;

語法：mysql -u 使用者名稱-p 匯入庫名< 硬碟SQL檔案絕對路徑

//恢復命令
mysql -uroot -p123456 itcast</root/data/menagerie.sql

8. 多表查詢

8.1 笛卡爾積介紹

笛卡爾乘積是指在數學中，兩個集合X和Y的笛卡尓積（Cartesian product），又稱直積，表示為X × Y，第一個物件是X的成員而第二個物件是Y的所有可能有序對的其中一個成員

準備資料:

create table A(
  A_ID int primary key auto_increment,
  A_NAME varchar(20) not null
);
insert into A values(1,'apple');
insert into A values(2,'orange');
insert into A values(3,'banana');

create table B( 
   A_ID int primary key auto_increment,
   B_PRICE double
);
insert into B values(1,2.30);
insert into B values(2,3.50);
insert into B values(4,null);

展示效果：

mysql> select * from A,B;
+------+--------+------+---------+
| A_ID | A_NAME | A_ID | B_PRICE |
+------+--------+------+---------+
|    1 | apple  |    1 |     2.3 |
|    2 | orange |    1 |     2.3 |
|    3 | banana |    1 |     2.3 |
|    1 | apple  |    2 |     3.5 |
|    2 | orange |    2 |     3.5 |
|    3 | banana |    2 |     3.5 |
|    1 | apple  |    4 |    NULL |
|    2 | orange |    4 |    NULL |
|    3 | banana |    4 |    NULL |
+------+--------+------+---------+

作用：笛卡爾積的資料，對程式是沒有意義的，我們需要對笛卡爾積中的資料再次進行過濾。

對於多表查詢操作，需要過濾出滿足條件的資料，需要把多個表進行連線，連線之後需要加上過濾的條件。

8.2 內連線:

• 顯示內連線:

語法: select * from 表名1  inner join 表名2 on 關聯條件 ; 

• 隱式內連線:

select 列名 , 列名 .... from 表名1,表名2 where 關聯條件;

8.3 外連線:

• 左外連線:
• 用左邊表去右邊表中查詢對應記錄，不管是否找到，都將顯示左邊表中全部記錄。

語法：select * from 表1 left outer join 表2 on 條件;

• 右外連線:
• 用右邊表去左邊表查詢對應記錄，不管是否找到，右邊表全部記錄都將顯示。

語法：select * from 表1 right outer join 表2 on 條件;

• 全外連線
• 左外連線和右外連線的結果合併，單會去掉重複的記錄。

語法: select * from a full outer join b on a.A_ID = b.A_ID; 

注意: 但是mysql資料庫不支援此語法。

8.4 關聯子查詢

子查詢：把一個sql的查詢結果作為另外一個查詢的引數存在。

1. in和exist關鍵詞的用法

關聯子查詢其他的關鍵字使用：

回憶：age=23 or age=24  等價於 age in (23,24)

in 表示條件應該是在多個列值中。

in：使用在where後面，經常表示是一個列表中的資料，只要被查詢的資料在這個列表中存在即可。

mysql> select * from A where A_ID in(1,2,3);
+------+--------+
| A_ID | A_NAME |
+------+--------+
|    1 | apple  |
|    2 | orange |
|    3 | banana |
+------+--------+
3 rows in set (0.00 sec)

mysql> select * from A where A_ID =1 or A_ID =2 or A_ID =3;
+------+--------+
| A_ID | A_NAME |
+------+--------+
|    1 | apple  |
|    2 | orange |
|    3 | banana |
+------+--------+
//not in
mysql> select * from A where A_ID not in (1,2,3,4);
Empty set (0.00 sec)

mysql> select * from A where A_ID not in (3,4);    
+------+--------+
| A_ID | A_NAME |
+------+--------+
|    1 | apple  |
|    2 | orange |
+------+--------+
2 rows in set (0.00 sec)

exists：表示存在，當子查詢的結果存在，就會顯示主查詢中的所有資料。

mysql> select * from A where exists(select A_ID from B);
+------+--------+
| A_ID | A_NAME |
+------+--------+
|    1 | apple  |
|    2 | orange |
|    3 | banana |
+------+--------+

mysql> select * from A where not exists(select A_ID from B);
Empty set (0.00 sec)

• union 和union  all使用法

UNION 語句：用於將不同表中相同列中查詢的資料展示出來；（不包括重複資料）

UNION ALL 語句：用於將不同表中相同列中查詢的資料展示出來；（包括重複資料）

mysql> select * from A  union   select * from B;   
+------+--------+
| A_ID | A_NAME |
+------+--------+
|    1 | apple  |
|    2 | orange |
|    3 | banana |
|    1 | 2.3    |
|    2 | 3.5    |
|    4 | NULL   |
+------+--------+
6 rows in set (0.00 sec)

mysql> select * from A  union all  select * from B;
+------+--------+
| A_ID | A_NAME |
+------+--------+
|    1 | apple  |
|    2 | orange |
|    3 | banana |
|    1 | 2.3    |
|    2 | 3.5    |
|    4 | NULL   |
+------+--------+

• case when 語句

case when 語句語法結構：

CASE sex  
WHEN '1' THEN '男'  
WHEN '2' THEN '女'  
ELSE '其他' END 

準備資料:

//建立表
create table employee(
empid                   int  ,                                       
deptid                  int   ,                                      
sex                     varchar(20)  ,                                    
salary                  double 
);

//載入資料
1 10 female 5500.0
2 10 male 4500.0
3 20 female 1900.0
4 20 male 4800.0
5 40 female 6500.0
6 40 female 14500.0
7 40 male 44500.0
8 50 male 6500.0
9 50 male 7500.0

load data local infile '/root/data/emp.txt' into table employee ;

select *,
case 
when salary < 5000 then "低等收入" 
when salary>= 5000 and salary < 10000 then "中等收入"
when salary > 10000 then "高等收入"  
end  as level,
case sex
when "female" then 1 
when "male" then 0
end as flag 
from employee;

如下程式碼為寵物表的程式碼:
 

9. mysql資料型別

MySQL中定義資料欄位的型別對你資料庫的最佳化是非常重要的。

MySQL支援多種型別，大致可以分為三類：數值、日期/時間和字串(字元)型別。

9.1 數值型別

MySQL支援所有標準SQL數值資料型別。

這些型別包括嚴格數值資料型別(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似數值資料型別(FLOAT、REAL和DOUBLE PRECISION)。

關鍵字INT是INTEGER的.同義詞，關鍵字DEC是DECIMAL的同義詞。

BIT資料型別儲存位欄位值，並且支援MyISAM、MEMORY、InnoDB和BDB表。

作為SQL標準的擴充套件，MySQL也支援整數型別TINYINT、MEDIUMINT和BIGINT。下面的表顯示了需要的每個整數型別的儲存和範圍。

M表示: 總的資料個數, D表示: 小數點後的精確位數.

DECIMAL(5, 2), 希望獲得的數字一共有5位,   並要求小數點保留兩位.

11123.54321 -->  11123.54

9.2 日期和時間型別

表示時間值的日期和時間型別為DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每個時間型別有一個有效值範圍和一個"零"值，當指定不合法的MySQL不能表示的值時使用"零"值。

TIMESTAMP型別有專有的自動更新特性，將在後面描述。

9.3 字串型別

字串型別指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。該節描述了這些型別如何工作以及如何在查詢中使用這些型別。

CHAR 和 VARCHAR 型別類似，但它們儲存和檢索的方式不同。它們的最大長度和是否尾部空格被保留等方面也不同。在儲存或檢索過程中不進行大小寫轉換。

BINARY 和 VARBINARY 類似於 CHAR 和 VARCHAR，不同的是它們包含二進位制字串而不要非二進位制字串。也就是說，它們包含位元組字串而不是字元字串。這說明它們沒有字符集，並且排序和比較基於列值位元組的數值值。

BLOB 是一個二進位制大物件，可以容納可變數量的資料。有 4 種 BLOB 型別：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它們區別在於可容納儲存範圍不同。

有 4 種 TEXT 型別：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。對應的這 4 種 BLOB 型別，可儲存的最大長度不同，可根據實際情況選擇。

MySQL 5.0 以上的版本：

1、一個漢字佔多少長度與編碼有關：

UTF－8：一個漢字＝3個位元組

GBK：一個漢字＝2個位元組

2、varchar(n) 表示 n 個字元，無論漢字和英文，Mysql 都能存入 n 個字元，僅是實際位元組長度有所區別

3、MySQL 檢查長度，可用 SQL 語言來檢視：

select LENGTH(fieldname) from tablename

1、整型

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

int(m) 裡的 m 是表示 SELECT 查詢結果集中的顯示寬度，並不影響實際的取值範圍，沒有影響到顯示的寬度，不知道這個 m 有什麼用。

2、浮點型(float 和 double)

設一個欄位定義為 float(5,3)，如果插入一個數 123.45678,實際資料庫裡存的是 123.457，但總個數還以實際為準，即 6 位。

3、定點數

浮點型在資料庫中存放的是近似值，而定點型別在資料庫中存放的是精確值。

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

m: 表示總個數, 要求最大值64.

d: 表示小數位數,

d<30;   小數位數最多保留: 29個.

4、字串(char,varchar,_text)

char 和 varchar：

1. char(n) 若存入字元數小於n，則以空格補於其後，查詢之時再將空格去掉。所以 char 型別儲存的字串末尾不能有空格，varchar 不限於此。

1. char(n) 固定長度，char(4) 不管是存入幾個字元，都將佔用 4 個位元組，varchar 是存入的實際字元數 +1 個位元組（n<=255）或2個位元組(n>255)，所以 varchar(4),存入 3 個字元將佔用 4 個位元組。

1. char 型別的字串檢索速度要比 varchar 型別的快。

varchar 和 text：

1. varchar( 可指定 n，text 不能指定，內部儲存 varchar 是存入的實際字元數 +1 個位元組（n<=255）或 2 個位元組(n>255)，text 是實際字元數 +2 個位元組。

1. text 型別不能有預設值。

1. varchar 可直接建立索引，text 建立索引要指定前多少個字元。varchar 查詢速度快於 text, 在都建立索引的情況下，text 的索引似乎不起作用。

5.二進位制資料(Blob)

1. BLOB和text儲存方式不同，TEXT以文字方式儲存，英文儲存區分大小寫，而Blob是以二進位制方式儲存，不分大小寫。

1. BLOB儲存的資料只能整體讀出。

1. TEXT可以指定字符集，BLOB不用指定字符集。

6.日期時間型別

若定義一個欄位為timestamp，這個欄位裡的時間資料會隨其他欄位修改的時候自動重新整理，所以這個資料型別的欄位可以存放這條記錄最後被修改的時間。

資料型別的屬性

charset

10. MySQL GROUP BY 語句

GROUP BY 語句根據一個或多個列對結果集進行分組。

在分組的列上我們可以使用 COUNT, SUM, AVG,等函式。

語法結構：

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

準備資料：

CREATE TABLE `employee_tbl` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL DEFAULT '',
  `date` datetime NOT NULL,
  `singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登入次數',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

INSERT INTO `employee_tbl` VALUES ('1', '小明', '2016-04-22 15:25:33', '1'), ('2', '小王', '2016-04-20 15:25:47', '3'), ('3', '小麗', '2016-04-19 15:26:02', '2'), ('4', '小王', '2016-04-07 15:26:14', '4'), ('5', '小明', '2016-04-11 15:26:40', '4'), ('6', '小明', '2016-04-04 15:26:54', '2');

mysql> SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;
+--------+----------+
| name   | COUNT(*) |
+--------+----------+
| 小麗 |        1 |
| 小明 |        3 |
| 小王 |        2 |
+--------+----------+

mysql> select * from employee_tbl;
+----+------+---------------------+--------+
| id | name | date                | singin |
+----+------+---------------------+--------+
|  1 | ??   | 2016-04-22 15:25:33 |      1 |
|  2 | ??   | 2016-04-20 15:25:47 |      3 |
|  3 | ??   | 2016-04-19 15:26:02 |      2 |
|  4 | ??   | 2016-04-07 15:26:14 |      4 |
|  5 | ??   | 2016-04-11 15:26:40 |      4 |
|  6 | ??   | 2016-04-04 15:26:54 |      2 |
+----+------+---------------------+--------+

mysql> select * from employee_tbl group by singin;
+----+------+---------------------+--------+
| id | name | date                | singin |
+----+------+---------------------+--------+
|  1 | ??   | 2016-04-22 15:25:33 |      1 |
|  3 | ??   | 2016-04-19 15:26:02 |      2 |
|  2 | ??   | 2016-04-20 15:25:47 |      3 |
|  4 | ??   | 2016-04-07 15:26:14 |      4 |
+----+------+---------------------+--------+

注意：

1、group by 可以實現一個最簡單的去重查詢，假設想看下有哪些員工，除了用 distinct,還可以用：

SELECT name FROM employee_tbl GROUP BY name;

返回的結果集就是所有員工的名字。

2、分組後的條件使用 HAVING 來限定，WHERE 是對原始資料進行條件限制。幾個關鍵字的使用順序為 where 、group by 、having、order by ，例如：

SELECT name ,sum(singin)  FROM employee_tbl WHERE date>'2015-04-07 15:26:14' GROUP BY name  HAVING sum(*)>5 ORDER BY sum(singin) DESC;

11、MySQL LIKE 子句

我們知道在 MySQL 中使用 SQL SELECT 命令來讀取資料， 同時我們可以在 SELECT 語句中使用 WHERE 子句來獲取指定的記錄。

WHERE 子句中可以使用等號 = 來設定獲取資料的條件，如 "company = 'itcast"。

但是有時候我們需要獲取 company 欄位含有 "it" 字元的所有記錄，這時我們就需要在 WHERE 子句中使用 SQL LIKE 子句。

SQL LIKE 子句中使用百分號 %字元來表示任意字元。

如果沒有使用百分號 %, LIKE 子句與等號 = 的效果是一樣的。

語法：

以下是 SQL SELECT 語句使用 LIKE 子句從資料表中讀取資料的通用語法：

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND|OR] filed2 = 'somevalue'

• 你可以在 WHERE 子句中指定任何條件。
• 你可以在 WHERE 子句中使用LIKE子句。
• 你可以使用LIKE子句代替等號 =。
• LIKE 通常與 ' % 和 _ ' 一同使用，類似於一個字元的模糊搜尋。
• 你可以使用 AND 或者 OR 指定一個或多個條件。
• 你可以在 DELETE 或 UPDATE 命令中使用 WHERE...LIKE 子句來指定條件。

mysql> select * from pet where species like '%d%';
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
+----------+--------+---------+------+------------+------------+

12. MySQL NULL 值處理

我們已經知道 MySQL 使用 SQL SELECT 命令及 WHERE 子句來讀取資料表中的資料,但是當提供的查詢條件欄位為 NULL 時，該命令可能就無法正常工作。

為了處理這種情況，MySQL提供了三大運算子:

• IS NULL: 當列的值是 NULL,此運算子返回 true。
• IS NOT NULL: 當列的值不為 NULL, 運算子返回 true。
• <=>: 比較運算子（不同於=運算子），當比較的的兩個值為 NULL 時返回 true。

關於 NULL 的條件比較運算是比較特殊的。你不能使用 = NULL 或 != NULL 在列中查詢 NULL 值 。

在 MySQL 中，NULL 值與任何其它值的比較（即使是 NULL）永遠返回 false，即 NULL = NULL 返回false 。

MySQL 中處理 NULL 使用 IS NULL 和 IS NOT NULL 運算子。

13. MySQL 後設資料

你可能想知道MySQL以下三種資訊：

• 查詢結果資訊： SELECT, UPDATE 或 DELETE語句影響的記錄數。
• 資料庫和資料表的資訊： 包含了資料庫及資料表的結構資訊。
• MySQL伺服器資訊： 包含了資料庫伺服器的當前狀態，版本號等。

在MySQL的命令提示符中，我們可以很容易的獲取以上伺服器資訊。

14 MySQL 函式

MySQL 有很多內建的函式，以下列出了這些函式的說明。

14.1 MySQL 字串函式

14.2 MySQL 數字函式

14.3 MySQL 日期函式

14.4 MySQL 高階函式

15. MySQL 索引

MySQL索引的建立對於MySQL的高效執行是很重要的，索引可以大大提高MySQL的檢索速度。

索引分單列索引和組合索引。單列索引，即一個索引只包含單個列，一個表可以有多個單列索引，但這不是組合索引。組合索引，即一個索引包含多個列。

建立索引時，你需要確保該索引是應用在 SQL 查詢語句的條件(一般作為 WHERE 子句的條件)。

實際上，索引也是一張表，該表儲存了主鍵與索引欄位，並指向實體表的記錄。

上面都在說使用索引的好處，但過多的使用索引將會造成濫用。因此索引也會有它的缺點：雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對錶進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要儲存資料，還要儲存一下索引檔案。

建立索引會佔用磁碟空間的索引檔案。

15.1 普通索引

• 這是最基本的索引，它沒有任何限制。它有以下幾種建立方式：

1. 建立索引:

格式: CREATE INDEX indexName ON mytable(username(length)); 

//建立索引
 create index id on B(A_ID);

如果是CHAR，VARCHAR型別，length可以小於欄位實際長度；如果是BLOB和TEXT型別，必須指定 length。

1. 修改表結構(新增索引)

格式: ALTER table tableName ADD INDEX indexName(columnName)

1. 建立表的時候直接指定

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
INDEX [indexName] (username(length))  
 
);

1. 刪除索引的語法

DROP INDEX [indexName] ON mytable; 

15.2 唯一索引

它與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。它有以下幾種建立方式：

1. 建立索引

CREATE UNIQUE INDEX indexName ON mytable(username(length)) 

1. 修改表結構

ALTER table mytable ADD UNIQUE [indexName] (username(length))

1. 建立表的時候直接指定

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
UNIQUE [indexName] (username(length))  
);  

15.3 使用alter命令新增和刪除索引

有四種方式來新增資料表的索引：

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 該語句新增一個主鍵，這意味著索引值必須是唯一的，且不能為NULL。
• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 這條語句建立索引的值必須是唯一的（除了NULL外，NULL可能會出現多次）。
• ALTER TABLE tbl_name ADD INDEX index_name (column_list): 新增普通索引，索引值可出現多次。
• **ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):**該語句指定了索引為 FULLTEXT ，用於全文索引。

以下例項為在表中新增索引。

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

你還可以在 ALTER 命令中使用 DROP 子句來刪除索引。嘗試以下例項刪除索引:

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

15.4 使用alter命令新增和刪除主鍵

主鍵只能在一個表中新增一個，新增主鍵索引時，你需要確保該主鍵預設不為空（NOT NULL）。例項如下：

mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (itcast);

你也可以使用 ALTER 命令刪除主鍵：

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

刪除主鍵時只需指定PRIMARY KEY，但在刪除索引時，你必須知道索引名。

15.5 顯示索引資訊

你可以使用 SHOW INDEX 命令來列出表中的相關的索引資訊。可以透過新增 \G 來格式化輸出資訊。

嘗試以下例項:

格式: SHOW INDEX FROM table_name; \G



mysql> show index from B;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| B     |          0 | PRIMARY  |            1 | A_ID        | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

16. MySQL 事務

MySQL 事務主要用於處理操作量大，複雜度高的資料。比如說，在人員管理系統中，你刪除一個人員，你即需要刪除人員的基本資料，也要刪除和該人員相關的資訊，如信箱，文章等等，這樣，這些資料庫操作語句就構成一個事務！

• 在 MySQL 中只有使用了 Innodb 資料庫引擎的資料庫或表才支援事務。
• 事務處理可以用來維護資料庫的完整性，保證成批的 SQL 語句要麼全部執行，要麼全部不執行。
• 事務用來管理 insert,update,delete 語句

一般來說，事務是必須滿足4個條件（ACID）：：原子性（Atomicity，或稱不可分割性）、一致性（Consistency）、隔離性（Isolation，又稱獨立性）、永續性（Durability）。

• 原子性：一個事務（transaction）中的所有操作，要麼全部完成，要麼全部不完成，在中間某個環節不會結束。事務在執行過程中發生錯誤，會被回滾（Rollback）到事務開始前的狀態，就像這個事務從來沒有執行過一樣。
• 一致性：在事務開始之前和事務結束以後，資料庫的完整性沒有被破壞。這表示寫入的資料必須完全符合所有的預設規則，這包含資料的精確度、串聯性以及後續資料庫可以自發性地完成預定的工作。
• 隔離性：資料庫允許多個併發事務同時對其資料進行讀寫和修改的能力，隔離性可以防止多個事務併發執行時由於交叉執行而導致資料的不一致。事務隔離分為不同級別，包括讀未提交（Read uncommitted）、讀提交（read committed）、可重複讀（repeatable read）和序列化（Serializable）。
• 永續性：事務處理結束後，對資料的修改就是永久的，即便系統故障也不會丟失。

16.1 事務控制語句：

• BEGIN或START TRANSACTION；顯式地開啟一個事務；
• COMMIT；也可以使用COMMIT WORK，不過二者是等價的。COMMIT會提交事務，並使已對資料庫進行的所有修改成為永久性的；
• ROLLBACK；有可以使用ROLLBACK WORK，不過二者是等價的。回滾會結束使用者的事務，並撤銷正在進行的所有未提交的修改；
• SAVEPOINT identifier；SAVEPOINT允許在事務中建立一個儲存點，一個事務中可以有多個SAVEPOINT；
• RELEASE SAVEPOINT identifier；刪除一個事務的儲存點，當沒有指定的儲存點時，執行該語句會丟擲一個異常；
• ROLLBACK TO identifier；把事務回滾到標記點；
• SET TRANSACTION；用來設定事務的隔離級別。InnoDB儲存引擎提供事務的隔離級別有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

16.2 MYSQL 事務處理主要有兩種方法：

1、用 BEGIN, ROLLBACK, COMMIT來實現

• BEGIN 開始一個事務
• ROLLBACK 事務回滾
• COMMIT 事務確認

2、直接用 SET 來改變 MySQL 的自動提交模式:

• SET AUTOCOMMIT=0 禁止自動提交
• SET AUTOCOMMIT=1 開啟自動提交

16.3、事務併發操作出現幾種問題

所謂事務，是使用者定義的一個資料庫操作序列，是資料庫環境中的邏輯工作單元，是一個不可分割的整體。

事務的這個4個特性簡稱為ACID特性，事務ACID特性可能遭到破壞的因素有：

①多個事務併發執行，不同事務的操作交叉執行；

②事務在執行過程中被強行終止。

如何保證在多個事務併發執行的過程中不發生上述的兩種情況，是資料庫管理系統併發控制的主要責任。

16.4、丟失修改資料

舉例：銀行卡有100元，事務A取10元，事務B取10元，事務AB兩人同時取錢，初始值都是100

16.4.1、讀“髒”資料

資料庫技術中，如果正常提交的事務A使用了事務B未提交的撤銷資料，這種資料成為“髒資料”，會造成資料的髒讀和汙讀。

16.4.2、不一致分析

造成這種資料不一致的主要原因是併發執行的兩個事務中，一個事務在讀取資料時，另一個事務正在修改同一個資料。這樣就可能導致兩個事務的相互干擾及“讀”事務的錯誤執行結果。

16.5、資料庫併發的控制（瞭解部分）

16.5.1、併發排程的可序列化

可序列化準則：多個事務的併發執行時正確的，當且僅當其結果按某一次序序列執行它們時的結果相同，這種排程策略稱為可序列化排程。可序列化是併發事務正確性的準則，一個給定的併發排程，當且僅當它是可序列化的，才認為是正確的。

16.5.2、封鎖

封鎖是實現併發控制的非常重要的技術。封鎖是指某事務在對某資料物件進行操作以前，先請求系統對其加鎖，成功加鎖之後該事務就對該資料物件有了控制權，只有該事務對其進行解鎖之後，其他的事務才能更新它，DBMS有兩種鎖：

①  排它鎖（也稱作X鎖） ：可讀可寫

如果事務T在對某個資料物件實施了X鎖，那麼其他的事務必須要等到T事務接觸對該資料物件的X鎖之後，才能對這個資料進行加鎖。

②  共享鎖（也稱作S鎖） ：只能進行讀取工作

如果事務T在對某個資料物件實施了S鎖，那麼其他的事務也能對該資料物件實施S鎖，但是對這個資料物件施加的所有S鎖都釋放之前不允許任何事務對該資料物件實施X鎖。

16.5.3、死鎖

封鎖技術可以避免一些併發操作引起的不一致錯誤，但也會產生其他的一些問題，活鎖和死鎖。

①  活鎖

如果某個事務處在永遠等待的狀態，得不到封鎖的機會，這種現象為活鎖，避免這種鎖最好的方法就是採用先來先服務的策略。

②  死鎖

兩個或兩個以上的事務都處於等待狀態每個事務都在等待對方事務接觸封鎖，它才能繼續執行下去，這樣任何事務都處於等待狀態而無法繼續執行的現象稱為死鎖

解決死鎖問題方法有兩類：

A、 死鎖的預防

B、 死鎖的診斷與預防

17. 資料庫編碼

1. 檢視mysql編碼

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

• s2) 設定mysql編碼

# vi /etc/my.cnf
如下(少補)：

[mysqld]
character-set-server=utf8 
collation-server=utf8_general_ci 
sql_mode='NO_ENGINE_SUBSTITUTION'

[mysql]
default-character-set=utf8

重啟mysql

# service mysql restart
再次檢視編碼：

# mysql -uroot -p
mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

18. mysql 的目錄及配置檔案

1. /etc/my.cnf  :   這是mysql的主配置檔案

1. /var/lib/mysql : mysql中資料庫及其資料儲存的目錄

1. /var/log/mysql(或者直接在/var/lib/mysql) : 資料庫的日誌輸出存放位置

1. 檢視埠。netstat -nltp 看是否能找到3306的埠

19. 資料庫常用術語

19.1 資料模型

資料模型（Data model）是資料庫結構的基礎，是用來描述資料的一組概念和定義，資料模型主要有三個要素：資料結構、資料操作、資料約束條件。

資料結構：物件型別的集合，是對靜態屬性的描述。

資料操作：是對資料庫中的各種物件性資料，允許執行的操作的集合，如增刪改查等；資料操作是對系統動態熱性的描述。

資料的約束條件：是一組完整性規則的集合，也就是說，對於具體的應用必須先遵循特定的語義約束條件。比如：性別只能取  “男”或者“女”中的之一。考試成績：（滿分100）只能是0-100的數值。

19.2 資料庫

資料庫（database）是長期儲存在計算機外存上有結構，可共享的資料集合；資料庫中的資料按照一定的資料模型描述、組織和儲存，具有較小的冗餘度，較高的資料獨立性和可擴充套件性，並可以為多個使用者共享。

常見資料庫分類：

關係型資料庫：如：MySQL，oracle，sqlserver

非關係型資料庫：如：redis，hbase等

19.3 資料庫管理系統

資料庫管理系統（database managerment system， DBMS）是指資料庫系統中對資料庫進行管理的軟體系統，是資料庫系統的核心組成部分。資料庫的一切操作，如增刪改查以及各種控制，都是透過DBMS進行的。

具有以下4個基本的功能：

• 1、資料定義功能

  使用者可以透過DBMS提供的資料定義語言對資料庫的資料進行定義。

• 2、資料操縱功能

  使用者可以透過資料操縱語言實現對資料庫的增刪改查操作

• 3、資料庫執行管理

  管理資料庫的執行是DBMS執行時的核心工作。所有訪問資料庫的操作都要在DBMS的統一管理下進行，以保證資料的安全性、完整性、一致性以及多使用者對資料庫的併發使用。

• 4、資料庫的建立和維護

  建立資料庫，包括資料庫初始資料的輸入與資料轉換等。維護資料庫，包括資料庫的轉儲與恢復，資料庫的重組織，效能監控和分析。

19.4 資料庫系統相關管理人員

資料庫系統的相關人員是資料庫系統的重要組成部分，具體可以分為以下的三類人員

• 1、資料庫管理員(DBA)

  職責：負責資料庫的建立、使用、維護的專門人員

• 2、應用程式開發人員

  職責：開發資料庫應用程式的人員，可以使用資料庫管理系統的所有功能。

• 3、終端使用者

  職責：一般來說，是透過應用程式使用資料庫的人員，終端使用者無需自己編寫應用程式。

19.5 資料庫系統

資料庫系統（database system DBS）是由硬體系統，資料庫管理系統，資料庫，資料庫應用程式，資料庫系統相關人員構成的人-機系統，是指有資料庫的整個計算機系統。

說明：在許多場合下，資料庫，資料庫管理系統，資料庫系統不做嚴格區分；

20 資料庫設計流程

一般對於整個資料庫的設定分為5大部分

• 
  
• 明確使用者需求，到底做什麼？

1. 需求分析:

• 優點:
• 簡潔明瞭, 描述了各個之間的邏輯關係
• 獨立於計算機與具體的RDBMS無關。

• 該階段是整個資料庫設計的關鍵，它透過對使用者需求進行綜合、歸納與抽象。主要是透過E-R圖表示

  E-R模型的基本元素:
  
  A、實體（Entity） 如：學生
  
  B、屬性（attribute）如：姓名
  
  C、鍵碼（key）如：身份證號碼；
  
  D、關係（relationship）如：兩個實體之間的關係
  
   a)  一對一（1:1）： 一個人一個身份證號碼；一個學校一個校長
  
   b)  一對多（1：n）：學校和老師的關係
  
   c)  多對多（n：n）：學生選課，一個學生可以選擇多門課程，一門課程課被多名學生進行選修。
  
  

1. 概念模型設計:

• 與具體的資料庫相關,反映出業務部門的需求
• 規範化處理，儘可能的消除關係操作過程中的異常情況。

1. 邏輯模型設計: 該階段會涉及到更多的概念，方法，理論。

• 建立資料庫，定義資料庫結構，組織資料入庫，除錯資料庫並進行資料庫的試執行。

1. 資料庫實施:

• 資料庫正式執行之後，對資料庫執行過程中對其進行評價，調整，修改，調優等。

1. 資料庫的執行和維護:

21 資料庫設計遵循的原則

21.1 正規化概念

概念：正規化就是符合某一規範級別的關係模式的集合。共有7種正規化：

1NF ⊃2NF⊃3NF⊃BCNF⊃4NF⊃5NF⊃6NF

21.2 第一正規化(First Normal Form)

如果一個關係模式R的所有屬性都是不可分割的基本資料項，則這個關係屬於第一正規化。

舉例說明：(學生選課：學號，姓名，系別，系部地址，課程名稱，課程成績)

Student(s_no,s_name,s_dept,s_location,s_course_name,s_grade)

注：1NF是關係模式應具備的最起碼的條件，如果資料庫設計不能滿足第一正規化，就不能稱作是關係模式；關聯式資料庫設計研究的關係規範化是在1NF基礎之上進行的。

21.3 第二正規化(Second Normal Form)

定義：若關係模式R屬於第一正規化，且每個非主屬性都是完全函式依賴於主鍵，則R屬於第二正規化。

說明：從2NF的定義可以看出，從2NF開始討論的是主鍵和非主屬性之間的函式依賴關係，所以分析關係模式是屬於2NF，首先指明關係模式的主鍵，然後在討論非主屬性和主鍵之間的函式依賴關係。

例如：選課關係模式

SC（s_no,c_no,score）中，主鍵為（s_no,c_no），而非主屬性score與主鍵之間不存在部分函式依賴關係，所以關係模式SC屬於2NF

21.4 第三正規化(Third Normal Form)

定義：若關係模式R屬於第一正規化，且每個非主屬性都不傳遞函式依賴於主鍵，則R屬於第三正規化。

說明：3NF說明的是非主屬性和主鍵之間的函式依賴關係

例如：選課關係模式

SC（s_no,c_no,score）中,由於除了主鍵之外，只有一個非主屬性score，所以score不可能構成與主鍵之間的傳遞函式依賴，所以SC屬於3NF