國家 1+X 認證 啟動試點

從2019年開始，在職業院校、各本科高校啟動“學歷證照+若干職業技能等級正式”制度試點工作，職業技能等級證照是1+x證照制度設計的重要內容，是一種新型證照，也是畢業生、社會成員職業技能水平的憑證，是對學習成果的認定。證照體現崗位能力要求，反應職業活動和個人職業生涯發展所需要的職業能力，全面涵蓋專業知識、職業素養和技能操作。

# 1+X Web 前端開發等級考證
1+X Web前端開發便是其中認證方向. 1+X Web前端開發職業技能分為初、中、高三個等級，其中高階證照持有者具有複雜網頁設計開發能力和網站架構設計規劃能力;中級證照持有者具有動態網頁設計開發能力;初級證照持有者具有靜態網頁開發能力。(由於該認證是2019年剛提出來的,還在各大職院校(包括本科)試點中,目前知名度不高,很多都不知道,以後辦的怎麼樣,就不得而知了…)

# 考核方式
Web 前端開發職業技能初、中、高三個級別的考核方式為閉卷考試，採用上機考試形式。考試包括理論考試和實操考試兩部分。理論考試試卷滿分 100 分，共 50 道試題，其中單選題 30 道，多選題 15 道，判斷題 5 道；實操考試試卷滿分 100 分，試卷含 3-5 道實踐性試題，試題形式包括案例分析、軟體程式碼編碼或是網頁效果呈現等。理論考試與實操考試合格標準均為 60 分，兩部分考試成績均合格的學員可以獲得相應級別的職業技能等級證照。(不過目前考的都很簡單,基本的基礎知識掌握了,基本能過)

三個等級考核的職業核心技能介紹如下:

其中中級的考核是在初級的基礎上,重點考察下面四個部分

• 深入淺出MySQL資料庫
• PHP 技術與應用(包含PHP Laravel 框架應用)
• Web前後端互動技術
• 響應式開發技術

更多詳細資訊看這裡:1+X 職業技能等級證照資訊管理服務平臺
1+X Web 前端開發職業技能等級標準pdf

下面是 MySQL 的基礎知識,考的知識基本都在裡面了,12月要考web中級的童鞋來~ 看一遍~

---

一. MySQL 介紹

1.1 MySQL 簡介

Michael Widenius "Monty"是一位程式設計天才。19 歲的時候，他從赫爾辛基理工大學輟學開始全職工作，因為大學已經沒有什麼東西可以教他了。33 歲時，他釋出了MySQL，成為了全世界最流行的開源資料庫。並以 10億美元的價格，將自己建立的公司 MySQL AB賣給了 SUN，此後，Oracle 收購了 SUN。
Widenius 離開了 Sun 之後，覺得依靠 Sun/Oracle 來發展 MySQL，實在很不靠譜，於是決定另開分支，這個分支的名字叫做 MariaDB。MariaDB名稱來自他的女兒Maria的名字。
MariaDB跟 MySQL在絕大多數方面是相容的，對於開發者來說，幾乎感覺不到任何不同。目前 MariaDB 是發展最快的 MySQL 分支版本，新版本釋出速度已經超過了 Oracle官方的 MySQL版本。

1.2 關係型資料庫

關係型資料庫以行和列的形式儲存資料，這一系列的行和列被稱為表，一組表組成了資料庫。
表與表之間的資料記錄有關係。
資料儲存在表內，行（記錄）用於記錄資料，列（欄位）用於規定資料格式

二. MySQL 管理

2.1 MySQL 資料庫管理

檢視資料庫

show databases;

建立資料庫

# 如果沒有修改 my.ini 配置檔案的預設字符集，在建立資料庫時，指定字符集
create database db_name character set 'utf8';

# 特殊字元(關鍵字)用反引號
create database `create`;

MySQL\data 目錄下將自動生成一個對應名稱的目錄，目錄內部有一個 db.opt 文 文
件

顯示資料庫建立資訊

show create database db_name;   # db_name 即你的資料庫名

刪除資料庫

drop database db_name;   # db_name 即你的資料庫名

進入/使用資料庫

use database;

顯示當前開啟的資料庫

select database();

2.2 MySQL 表結構管理

建立資料表

create table 表名(欄位 欄位型別,…)

MySQL\data目錄下的資料庫目錄中將自動生成一個對應名稱的.frm檔案

刪除資料表

drop table 表名;

檢視資料表

show tables; 

# 檢視字母是'l'開頭的表
show tables like 'l%'; # %是萬用字元

檢視錶建立資訊

show create table 表名;

檢視資料表結構

desc 表名;

2.3 MySQL 使用者管理

登入

MySQL 是基於C/S 架構,必須在客戶端通過終端視窗,連線MySQL 伺服器進行操作

mysql -h host -u user -p
Enter password:*********

使用者管理

• 超級使用者 root
• 修改賬號密碼
  例如:

## DOS命令下修改，將 root 賬號密碼修改為 123456
mysqladmin -u root password 1234 ##語句最後不要加分號，否則密碼就是 “123456；”

##mysql 命令
set password for 'root'@'localhost'= password('123456');

• 建立使用者
  使用 create語句進行建立使用者，語句格式如下：

create user 'username'@'host' identified by 'password';

引數說明：

1. username: 表示要建立的使用者名稱；
2. host: 表示指定該使用者在哪個主機上可以登陸，如果是本地使用者可用 localhost，如果想讓該使用者可以從任意遠端主機登陸，可以使用萬用字元%；
3. password: 表示該使用者的登陸密碼，密碼可以為空，如果為空則該使用者可以不需要密碼登陸伺服器。例：

create user 'zhangsan'@'localhost' identified by '123456';

• 刪除使用者
  刪除使用者使用drop 語句,語法格式為:

drop user 'username'@'host';

修改配置檔案 my.ini

字符集
MySQL預設字符集是 latin( 拉丁 )，改變為 utf8 才能正確顯示中文
[mysqld] 下新增
character-set-server=utf8
init-connect='\set NAMES utf8'
修改預設引擎
InnoDB 優於 MYISAM
default-storage-engine=MYISAM 修改為 default-storage-engine=InnoDB
化 個性化 mysql  提示符
MySQL預設提示符是” mysql>“ ，可以個性化定製，例如："mysql(資料庫)>"
[mysql]下新增
prompt="mysql(\d)>"

2.4 預設資料庫

• information_schema
  提供了訪問資料庫後設資料的方式。什麼是後設資料呢？後設資料是關於資料的資料，如資料庫名或表名，列的資料型別，或訪問許可權等。有些時候用於表述該資訊的其他術語包括“資料詞典”和“系統目錄”。
• performance_schema
  mysql 5.5 版本 新增了一個效能優化的引擎
• mysql
  這個是 MySQL的核心資料庫，主要負責儲存資料庫的使用者、許可權設定、關鍵字等 MySQL自己需要使用的控制和管理資訊。不可以刪除，也不要輕易修改這個資料庫裡面的表資訊。
• test
  安裝時候建立的一個測試用資料庫，空資料庫，沒有任何表，可以刪除（新版mysql 已取消）。

三. SQL 基礎語法

3.1 SQL 語句簡介

SQL 語言

SQL(Structured Query Language)是用於訪問和處理資料庫的 標準計算機語言。使用SQL 訪問和處理資料系統中的資料，這類資料庫包括：Oracle,mysql,Sybase, SQLServer, DB2, Access 等等。

基本規範

• SQL 對大小寫不敏感，一般資料庫名稱、表名稱、欄位名稱全部小寫
• MySQL要求在每條 SQL 命令的末端使用分號（MS Access 和 SQL Server2000，則不必在每條 SQL 語句之後使用分號）。

註釋

mysql> select 1+1; # 這個註釋直到該行結束
mysql> select 1+1; -- 這個註釋直到該行結束
mysql> select 1 /* 這是一個在行中間的註釋 */ + 1;
mysql> select 1+
/*
這是一個
多行註釋的形式
*/
1;

3.2 MySQL 基本資料型別

欄位型別

資料型別是指列、儲存過程引數、表示式和區域性變數的資料特徵，它決定了資料的儲存方式，代表了不同的資訊型別。不同的資料庫，資料型別有所不同，MySQL資料庫有以下幾種資料型別：

字串型

型別	位元組	大小	說明
char	1	0-255 字元 (2^8)	定長字串
varchar	2	0-65 535 字元 (2^16)	變長字串
tinytext	1	0-255 字元 (2^8)	短文字（與 char儲存形式不同）
text	2	0-65 535 字元(2^16)	文字
mediumtext	3	0-16 777 215字元 (2^24)	中等長度文字
longtext	4	0-4 294 967 295字元(2^32)	極大文字

注意：char 和 和 varchar 需要指定長度，例如：char（10）

整數型

型別	位元組	範圍（有符號）	範圍（無符號）	用途
tinyint	1	(-128，127)	(0，255)	很小整數值
smallint	2	(-32 768，32 767)	(0，65 535)	小整數值
mediumint	3	(-8 388 608，8 388 607)	(0，16 777 215) 中整數值
int 或integer	4	(-2 147 483 648，2 147 483647)	(0，4 294 967 295)	整數值
bigint	8	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744073 709 551 615)	很大的整數值

很多人喜歡在定義資料時，這樣寫：

create table tbl_name(
age int(10)
);

int 後面()中的數字，不代表佔用空間容量。而代表最小顯示位數。這個東西基本沒有意義，除非你對欄位指定 zerofill。mysql 會自動分配長度：int(11)、tinyint(4)、smallint(6)、mediumint(9)、bigint(20)。所以，建議在使用時，就用這些預設的顯示長度就可以了。不用再去自己填長度（比如：int(10)、tinyint(1)之類的基本沒用）。

浮點型

型別	位元組	範圍	用途
float(M,D)	4	23bit（約 6~7位 10進位制數字）	單精度浮點數 值絕對能保證精度為 6~7位有效數字
double(M,D)	8	52bit（約 15~16位10 進位制數字）	雙精度浮點數值 精度為15~16位有效數字
decimal(M,D)	M+2	依賴於 M 和 D的值	定點型

M（精度），代表總長度（整數位和小數位）限制
D（標度），代表小數位的長度限制。
M 必需大於等於 D

數字的修飾符	功能	說明
unsigned	無符號	非負數
zerofill	前導 0	整形前加 0（自動新增 unsigned）

日期型

型別	位元組	範圍	格式	用途
date	3	1000-01-01—9999-12-31	YYYY-MM-DD	日期值
time	3	-838:59:59—838:59:59	HH:MM:SS	時間值或持續時間
year	1	1901—2155	YYYY	年份值
datetime	8	1000-01-01 00:00:00—9999-12-3123:59:59	YYYY-MM-DDHH:MM:SS	混合日期和時間值
timestamp	4	1970-01-01 00:00:00/2038	結束時間是第 2147483647 秒，北京時間2038-1-19 11:14:07，格林尼治時間2038 年 1 月 19日 凌晨 03:14:07	YYYYMMDDHHMMSS

列舉與列舉

名稱	位元組	說明
set	1、2、3、4或 8	列舉：可以取 SET列表中的一個或多個元素（多選）
enum	1或 2	列舉：可以取 ENUM 列表中的一個元素（單選）

create table students(
id tinyint, # 微小整型
name varchar(10), #變長字元
sex enum('m','w'), #單選
birthday date, # 日期型
tel char(11), # 定長字元
city char(1), # 城市
hobby set('1','2','3','4'), #多選
introduce  text # 個人介紹
);

欄位屬性

屬性	功能	說明
not null	非空	必須有值，不允許為 null
default	預設值	當插入記錄時沒有賦值，自動賦予預設值（允許為null）
primary key	主鍵	惟一標識一行資料的欄位（主鍵自動為 not null）
auto_increment	自動增量	不能單獨使用，必須與 primary key 一起定義
unique(uniquekey)	唯一	記錄不能重複（一張表可以有多個 unique，允許為null）

3.3 資料的增,刪,改

增刪改查(簡稱:CURD)

增(增加/插入資料)

# 方法 1：指定欄位
insert into students(name,age) values('張三','20');

# 方法 2： 省略欄位名，欄位位要一一對應，不能跳過（auto_increment 欄位，可以使用 null 或 default）
insert into students values(null,'張三','20');

# 方法 3：批量增加資料
insert into students(name,age) values('張三','20')，('李四','21')，('王五','22') ……

刪

# 用 delete刪除記錄，一定要加 where條件，否則表資料全部刪除！！
delete from 表名 where xx=xxx;

# 用 truncate刪除記錄，不能加 where條件，直接刪除全部記錄，id索引重新從 1開始
truncate table 表名;

改

單條修改
update 表名 set xx=xx,xxx=xx where xxx=xxx and xxx=xxx;

#多條修改
update students
set name = case id # id 欄位
when 1 then 'zhangsan'
when 2 then 'lisi'
when 3 then 'wangwu'
when 4 then 'zhaoliu'
end,
city = case id
when 1 then '2'
when 2 then '4'
when 3 then '1'
when 4 then '2'
end
where id in (1,2,3,4);

四. 資料的查

SELECT 語句用於從表中選取資料。結果被儲存在一個結果表中（稱為結果集）。

4.1 查詢表示式

# 當前使用的資料庫
select database();
# 檢視當前 MySQL版本
select version();
# 檢視當前使用者
select user();
# 檢視運算結果
select 1+2;

4.2 條件表示式

from 子句

# 欄位用','隔開，至少有一個欄位，最終結果集按照這個這個順序顯示
select 欄位 1,欄位 2... from 表名;
# *代表所有欄位
select * from 表名;

distinct(去重複)

# 去重後的結果，distinct 必須緊接在 select 後面
select distinct 欄位 from 表名;

# 統計不重複的個數
select count(distinct 欄位) from 表名;

where 子句

• where子句適用於對記錄的 刪、改、查 操作
• 對記錄進行過濾，如果沒有指定 where子句，則顯示所有記錄
• 在 where表示式中，可以使用函式或運算子，運算子包括如下表：

型別	運算子
算術	+ - * / %
比較	> < >= <= != =
邏輯	or
提升優先順序	( )

# 搜尋 id<20的所以資料
select * from students where id<20;
# 搜尋 id 編號為偶數的資料
select * from students where id%2=0;

where 條件關鍵字:

• in : 查詢一個集合的資料

# 搜尋 id 在（1,3,7）之中的資料
select * from students where id=1 || id=3 || id=7;
select * from students where id in(1,3,7);
# 一次刪除多條記錄
delete from students where id=3 || id=15 || id=23;
delete from students where id in(3,15,23);

• between…and…: 查詢一個區間的資料

# 搜尋 id 在 20-40之間的資料
select * from students where id>20 && id<40;
select * from students where id between 20 and 40;
# 刪除 id 在 20-40之間的資料
delete from students where id between 20 and 40;

• not : 排除

# 搜尋 id 除了 20-40之間的資料
select * from students where id not between 30 and 40;

like 子句

用於模糊查詢 %：任意字元長度 _ ：一個字元長度

# 搜尋 name名字以 5 結尾的資料
select * from students where name like '張%';
# 搜尋 name名字包含字母 s 的資料
select * from students where name like '%二%';
# 搜尋 id 以 5結尾的兩位數 資料
select * from students where id like '_5';

limit 子句

控制查詢記錄條數，資料表中的記錄，從 索引從 0 開始

select * from students limit 2 # 返回兩條記錄
select * from students limit 3,4 # 從索引為 3的記錄開始，返回 4條記錄
# php中的分頁功能，偏移值的計算：（當前頁-1） * 每頁記錄數
select name from student limit 4 offset 3
# 還可以使用 offset（偏移）：從索引為 3的記錄開始，返回 4 條

group by (結果分組)

根據 給定資料列的每個成員對查詢結果進行分組統計，最終得到一個分組彙總表利用 group by分組資訊進行統計，常見的是配合 max 等聚合函式篩選資料後分析。

select 指定的欄位要麼作為分組的依據（Group By 語句的後面），要麼就要被包含在聚合函式中。

# 簡單分組
select sex from students group by sex;
+-----+
| sex |
+-----+
| m |
| w |
+-----+
# 聚合函式分組
select count(*),city from students group by city;
+----------+----------+
| count(*) | livecity |
+----------+----------+

# 結果
| 7 | 1 |
| 8 | 2 |
| 1 | 3 |
| 3 | 4 |
| 2 | 5 |
+----------+----------+

order by( 結果排序)

按照給定的欄位進行排序，asc：升序(預設) desc：降序
如果同時選擇多個欄位，先按第一個欄位排序，如果第一個欄位值相等，再嘗試第二個欄位，以此類推

# 預設升序
select * from students order by birthday;
# 降序
select * from students order by birthday desc;

查詢語句的書寫順序

select → 欄位→ from→ 表名→where→group by→order by→limit

別名

4.3 多表查詢

例如這裡有三個表:

從姓名錶中查詢為id為6,成績表中他課程號為4,科目表中id為4的所有資訊:

select a.name,c.name,b.score from students as a,score as b,course as c where b.user_id=6 and b.course_id=4 and a.id=b.user_id and c.id=b.course_id;

表連線

有時為了得到完整的結果，我們需要從兩個或更多的表中獲取結果。我們就需要執行join

內連線

• JOIN: 如果表中有至少一個匹配，則返回行

select a.*,b.name from students as a [inner] join city as b on a.livecity=b.id;

• LEFT JOIN: 即使右表中沒有匹配，也從左表返回所有的行

select a.*,b.name from students as a left join city as b on a.livecity=b.id;

• RIGHT JOIN: 即使左表中沒有匹配，也從右表返回所有的行

select a.*,b.name from students as a right join city as b on a.livecity=b.id;

• FULL JOIN: 只要其中一個表中存在匹配，就返回行（mysql 不支援）

# 帶有連線的分組
select city.name, count(*) from students,city where students.livecity=city.id group by st
udents.livecity；
+--------+----------+
| name | count(*) |
+--------+----------+
| 北京 | 7 |
| 上海 | 8 |
| 杭州 | 1 |
| 深圳 | 3 |
+--------+----------+

4.4 子查詢

子查詢（subquery）是指出現在其他 SQL語句內的 select 子句（巢狀在查詢內部，且必須始終出現在圓括號內）

# 查詢城市名稱是北京的
# 普通方式查詢
select * from students where livecity=1;
# 子查詢方式
select * from students where livecity=(select id from city where name='北京');

• 子查詢可以包含多個關鍵字或條件，如：distinct、group by、order by、limit、函式等
• 子查詢的外層可以是：select，insert，update

五. 檢視與事務

5.1 檢視

檢視是從一個或幾個基本表（或檢視）中匯出的虛擬的表。在系統的資料字典中僅存放了檢視的定義，不存放檢視對應的資料。檢視是原始資料庫資料的一種變換，是檢視錶中資料的另外一種方式。可以將檢視看成是一個移動的視窗，通過它可以看到感興趣的資料。 檢視是從一個或多個實際表中獲得的，這些表的資料存放在資料庫中。那些用於產生檢視的表叫做該檢視的基表。一個檢視也可以從另一個檢視中產生。

資料庫中檢視是一個重要的概念，其優勢在於：

• 安全：有的資料是需要保密的，如果直接把表給出來進行操作會造成洩密，那麼可以通過建立檢視把相應檢視的許可權給出來即可保證資料的安全。
• 高效：複雜的連線查詢，每次執行時效率比較低，建立檢視，每次從檢視中獲取，將會提高效率。
• 定製資料：將常用的欄位放置在檢視中。

建立檢視

CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE conditi
on;
引數說明:

• viewname 為欲建立的檢視名
• columnname(s)為查詢的列名
• table_name 為查詢的表名
• condition 為查詢條件。

修改檢視

# ALTER 語句：
ALTER VIEW view_name AS SELECT column_name(s) FROM table_name WHERE c
ondition;
# CREATE OR REPLACE 語句：
ALTER VIEW view_name AS SELECT column_name(s) FROM table_name WHERE c
ondition;

刪除檢視

DROP view_name;

查詢檢視

SHOW TABLES;
SHOW TABLE STATUS;

這兩個命令不僅可以顯示錶名及表資訊，而且會顯示出所有檢視名稱及檢視資訊。除此之外，使用 SHOW CREATE VIEW 命令可以檢視某個檢視的定義，格式如下：

SHOW CREATE VIEW view_name;

關聯式資料庫表是用於儲存和組織資訊的資料結構,資料結構的不同，直接影響運算元據的效率和功能，對於 MySQL來說，它提供了很多種型別的儲存引擎，可以根據對資料處理的需求，選擇不同的儲存引擎，從而最大限度的利用 MySQL強大的功能。

5.2 事務

MyISAM 引擎

MyISAM 表是獨立於作業系統的，這說明可以輕鬆地將其從 Windows 伺服器移植到 Linux 伺服器，建立一個 MyISAM 引擎的 tb_Demo 表，就會生成以下三個檔案：

• tbdemo.frm 儲存表定義
• tbdemo.MYD 儲存資料
• tb_demo.MYI 儲存索引。

MyISAM 無法處理事務 ，特別適合以下幾種情況下使用：

1. 選擇密集型的表。MyISAM 儲存引擎在篩選大量資料時非常迅速，這是它最突出的優點。
2. 插入密集型的表。MyISAM 的併發插入特性允許同時選擇和插入資料。例如：MyISAM 儲存引擎很適合管理郵件或 Web 伺服器日誌資料。

InnoDB 引擎

InnoDB是一個健壯的事務型儲存引擎，InnoDB還引入了外來鍵約束，在以下場合下，使用 InnoDB是最理想的選擇：

1. 更新密集的表。InnoDB儲存引擎特別適合處理多重併發的更新請求。
2. 事務。InnoDB儲存引擎是支援事務的標準 MySQL儲存引擎。
3. 外來鍵約束。MySQL支援外來鍵的儲存引擎只有 InnoD
4. 自動災難恢復。與其它儲存引擎不同，InnoDB表能夠自動從災難中恢復。

事務處理

以銀行轉賬業務為例，張三→李四轉賬 100 元，這是一個完整事務，需要兩步操作：

1. 張三資料表減去 100元
2. 李四資料表增加 100元

如果在 1步完成後，操作出現錯誤（斷電、操作異常等），使 2步沒有完成，此時，張三減去了 100元，而張三卻沒有收到 100 元

為了避免這種情況的發生，就將整個操作定義為一個事務，任何操作步驟出現錯
誤，都會回滾到上一次斷點位置，避免出現其他錯誤。
語法:

# 開始
begin;
update tbl_a set money=money-100 where name='zhangsan';
update tbl_b set money=money+100 where name='lisi';
# 提交
commit;
# 回滾
rollback;

六. 索引約束分割槽

6.1 索引

索引是幫助 MySQL高效獲取資料的資料結構

資料庫在儲存資料之外，還維護著滿足特定查詢演算法的資料結構，這些資料結構以某種方式引用（指向）資料，這樣就可以在這些資料結構上實現高階查詢演算法。這種資料結構，就是索引。索引可以大大提高 MySQL的檢索速度。

在 MySQL中，對於一個 Primary Key的列，MySQL已經自動對其建立了 Unique和 Index。

建立索引

# 建立索引
create table 表名(
id int not null,
username varchar(16) not null,
index(username(length)) ### 用 username欄位作為索引
);

顯示索引

# 顯示索引
show index from 表名;

刪除索引

# 刪除索引
alter table 表名 drop index name;

6.2 約束

約束保證資料的完整性和一致性，根據約束的欄位數目的多少，約束又分為表級約束和列級約束

• 列級約束：針對某一欄位來使用
• 表級約束：針對兩個或兩個以上的欄位使用

約束型別包括：

• not null(非空約束)
• primary key (主鍵約束)
• unique key (唯一約束)
• default (預設約束)
• foreign key(外來鍵約束)

唯一(unique)約束

• unique 約束唯一標識資料庫表中的每條記錄。
• unique和 primary key約束均為列提供了唯一性的保證。
• primary key 被自動定義為 unique 約束。
  注意: 每個表可以有多個 unique約束，但是每個表只能有一個 primary key 約束。

# 第一種方式
create table persons(
id_p int not null,
address varchar(255),
city varchar(255),
phone varchar(11) unique # 定義欄位的同時，定義約束
);
# 第二種方式
create table persons(
id_p int not null,
address varchar(255),
city varchar(255),
phone varchar(11),
unique phone(phone) # 單獨一行命令，定義約束
# 第三種方式
alter table persons add unique city(city); #修改表

預設(default)約束

用於約束對應列中的值的預設值（除非預設為空值，否則不可插入空值）

create table persons(
id tinyint primary key auto_increment,
name varchar(30),
sex enum('m','w') default 'm', # 定義 sex 預設值為:'m'
)

主鍵(primary key)約束

每張資料表只能存在一個主鍵，主鍵保證記錄的唯一性，主鍵自動為 not null（同時作為表的索引）。

# 為沒有主鍵的表新增主鍵
alter table 表名 add primary key (欄位名)

外來鍵(foreign key) 約束

外來鍵約束是為了保持資料一致性，完整性，實現一對一或一對多關係

• 子表（具有外來鍵列的表）和 父表（子表所參照的表），儲存引擎只能為innoDB。
• 外來鍵列和參照列必須具有相似的資料型別。
  • 如果是數字型別，數字的長度、是否有符號位 必須相同
  • 字元型別的長度則可以不同
• 外來鍵列和參照列必須建立索引（如果外來鍵列不存在索引的話，MySQL將自動建立索引）。

# 先建父表 子表才能建外來鍵 父表和子表必須都是 innodb 引擎
# city父表
create table city(
id tinyint primary key,
name varchar(10) not null
)engine=INNODB;
# students 子表
create table students(
id tinyint primary key auto_increment, #id
# 定義欄位時同時定義
city tinyint, # 外來鍵欄位型別要與主表相同
foreign key(city) references city(id), # city 欄位作為外來鍵 ，引用 city表中的 id
)engine=INNODB;
-----------------------------------------------------------------------------
# 主表的資料可以修改，但不能刪除
# 刪除 city中的記錄
delete from city where id=1;
# 建立外來鍵以後，再刪除 city記錄，就會報錯：
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fail
s (`hxsd`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`city`) REFERE
NCES `city` (`id`))

6.3 刪除約束

刪除 primary key

alter table 表名 drop primary key;

刪除 index

alter table 表名 drop index index_name;

刪除外來鍵約束

alter table 表名 drop foreign key FK_ID;

6.4 索引與約束的關係

索引是面向資料庫本身的，用於查詢優化等操作。約束則更多的是業務上的關係。

通常，建立唯一約束就自動獲取唯一索引，是因為資料庫認為對資料庫進行唯一檢查時，如果該欄位上有索引會很快，所以建立唯一約束就預設建立唯一索引。同樣，常見的主鍵即是唯一性的約束，也是個索引。但對於 not null 這樣的約束，資料庫是不會建立索引的。

6.5 分割槽

如果一張表的資料量太大，不僅查詢資料的效率低下，而且難以找到一塊集中的儲存來存放。為了解決這個問題，資料庫推出了分割槽的功能。MySQL 表分割槽主要有以下四種型別：

• RANGE分割槽：
  RANGE即範圍分割槽，根據區間來判斷位於哪個分割槽。這些區間要連續且不能相互重疊，使用 VALUES LESS THAN 操作符來進行定義。

create table test(
id int DEFAULT null,
name char(30),
datedata date
)
PARTITION BY RANGE (year(datedata)) (
PARTITION part1 VALUES LESS THAN (1990) ,
PARTITION part2 VALUES LESS THAN (1995) ,
PARTITION part3 VALUES LESS THAN (2000) ,
PARTITION part4 VALUES LESS THAN MAXVALUE ); 

• LIST 分割槽:
  LIST 分割槽類似於按 RANGE 分割槽，區別在於 LIST 分割槽是基於列值匹配一個離
  散值集合中的某個值來進行選擇。

create table test1(
id int not null,
name char(30),
career VARCHAR(30)
)
PARTITION BY LIST (id) (
PARTITION part0 VALUES IN (1,5) ,
PARTITION part1 VALUES IN (11,15) ,
PARTITION part2 VALUES IN (6,10) ,
PARTITION part3 VALUES IN (16,20) 
);

• HASH分割槽:
  HASH分割槽基於使用者定義的表示式返回值來選擇分割槽，該表示式對要插入到表的行中列值進行 Hash 計算。

CREATE TABLE employees (
id INT NOT NULL, 
firstname VARCHAR(30), 
lastname VARCHAR(30), 
hired DATE NOT NULL DEFAULT '1970-01-01', 
separated DATE NOT NULL DEFAULT '9999-12-31', 
job_code INT, 
store_id INT
) 
PARTITION BY HASH(store_id) PARTITIONS 4;

• KEY分割槽
  KEY分割槽類似 HASH，但是 HASH允許使用者使用自定義表示式，而 KEY分割槽不允許，它需要使用 MySQL伺服器提供的 HASH 函式，同時 HASH 分割槽只支援整數分割槽，而 KEY分割槽支援除 BLOB 和 TEXT型別外其他列。

CREATE TABLE tk ( 
col1 INT NOT NULL, 
col2 CHAR(5), 
col3 DATE,
PRIMARY KEY(col1)
) 
PARTITION BY KEY (col1) PARTITIONS 3;

七. 儲存過程,觸發器

7.1 儲存過程

儲存過程（Stored Procedure）是一種在資料庫中儲存複雜程式，以便外部程式呼叫的一種資料庫物件。(*)

儲存過程是為了完成特定功能的 SQL語句集，經編譯建立並儲存在資料庫中，使用者可通過指定儲存過程的名字並給定引數(需要時)來呼叫執行。

儲存過程思想上很簡單，就是資料庫 SQL 語言層面的程式碼封裝與重用。

優點:

• 儲存過程可封裝，並隱藏複雜的商業邏輯。
• 儲存過程可以回傳值，並可以接受引數。
• 儲存過程無法使用 SELECT 指令來執行，因為它是子程式，與檢視錶，資料表或使用者定義函式不同。
• 儲存過程可以用在資料檢驗，強制實行商業邏輯等。
  缺點:
• 儲存過程，往往定製化於特定的資料庫上，因為支援的程式語言不同。當切換到其他廠商的資料庫系統時，需要重寫原有的儲存過程。
• 儲存過程的效能調校與撰寫，受限於各種資料庫系統。

儲存過程的建立和呼叫

儲存過程就是具有名字的一段程式碼，用來完成一個特定的功能。
建立的儲存過程儲存在資料庫的資料字典中。
語法:

create procedure 儲存過程名稱(in|out|inout 引數名稱 引數型別,......)
begin
過程體;
end

例項:

create procedure getStudentCount()
begin
select count(*) as num from student where classid=8;
end

查詢,修改與刪除

查詢

檢視所有儲存過程狀態：

SHOW PROCEDURE STATUS;

檢視對應資料庫下所有儲存過程狀態：

SHOW PROCEDURE STATUS WHERE DB='資料庫名';

檢視名稱包含 Student 的儲存過程狀態：

SHOW PROCEDURE STATUS WHERE name LIKE '%Student%';

查詢儲存過程詳細程式碼：

SHOW CREATE PROCEDURE 過程名;

修改

ALTER PROCEDURE 過程名 ([過程引數[,...]]) 過程體;

刪除

DROP PROCEDURE 過程名;

注意：不能在一個儲存過程中刪除另一個儲存過程，只能呼叫另一個儲存過程。

呼叫儲存過程

MySQL儲存過程用 call 和過程名以及一個括號，括號裡面根據需要，加入引數，引數包括輸入引數、輸出引數、輸入輸出引數呼叫，格式如下：

CALL儲存過程名 ([過程引數[,...]])

7.2 觸發器

觸發器（trigger），也叫觸發程式，是與表有關的命名資料庫物件，是 MySQL中提供給程式設計師來保證資料完整性的一種方法，它是與表事件 INSET、UPDATE、DELETE相關的一種特殊的儲存過程，它的執行是由事件來觸發，比如當對一個表
進行 INSET、UPDATE、DELETE 事件時就會啟用它執行。因此，刪除、新增或者修改操作可能都會啟用觸發器，所以不要編寫過於複雜的觸發器，也不要增加過多的觸發器，這樣會對資料的插入、修改或者刪除帶來比較嚴重的影響，同時也會帶來可移植性差的後果，所以在設計觸發器的時候一定要有所考慮。

建立觸發器

CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW tri
gger_stmt

引數說明:

• trigger_name 標識觸發器名稱，由使用者自行指定；
• trigger_time： 標識觸發時機，取值為 BEFORE 或 AFTER；
• trigger_event： 標識觸發事件，取值為 INSERT、UPDATE 或 DELETE；
• tb_name： 標識建立觸發器的表名，即在哪張表上建立觸發器；
• trigger_stmt： 觸發器程式體，可以是一句 SQL語句，
  或者用BEGIN 和 END 包含的多條語句，與儲存過程類似。
• FOR EACHROW: 表示任何一條記錄上的操作滿足觸發事件都會觸發該觸發器。

觸發事件型別:

事件	描述
INSERT型觸發器	插入某一行時啟用觸發器，可能通過 INSERT、LOADDATA、REPLACE 語句觸發
UPDATE型觸發器	更改某一行時啟用觸發器，可能通過 UPDATE 語句觸發
DELETE型觸發器	刪除某一行時啟用觸發器，可能通過 DELETE、REPLACE語句觸發

我們想要使班級表中的班內學生數隨著學生的新增自動更新，則採用觸發器來實現
最為合適，建立觸發器如下：

CREATE trigger tri_stuInsert after insert
on student for each row
BEGIN
declare c int;
set c = (select stuCount from class where classID=new.classID);
update class set stuCount = c + 1 where classID = new.classID;
END

從需求我們可以得知，班內學生數的變化是在插入學生記錄之後發生的，所以建立的觸發器型別為 after insert 型別。

檢視觸發器

SHOW TRIGGERS;

刪除觸發器

DROP TRIGGER [IF EXISTS] trigger_name

觸發器執行順序

日常開發中建立的資料庫通常都是 InnoDB 資料庫，在資料庫上建立的表大都是事務性表，也就是事務安全的，這時觸發器的執行順序主要是：

1. 如果 BEFORE 型別的觸發器執行失敗，SQL 無法正確執行。
2. 如果 SQL 執行失敗時，AFTER 型別的觸發器不會觸發。
3. 如果 AFTER 型別的觸發器執行失敗，資料會回滾。

如果是對資料庫的非事務表進行操作，當觸發器執行順序中的任何一步執行出錯，那麼就無法回滾了，資料可能會出錯。

八. MySQL 函式

8.1 運算函式

• abs(x) : 返回 x 的絕對值
• round(x,y) : 返回引數 x 的四捨五入的有 y位小數的值
• mod(x,y) : 返回 x/y的模（餘數）
• greatest(x1,x2,…,xn) : 返回集合中最大的值
• least(x1,x2,…,xn) : 返回集合中最小的值

8.2 字串函式

• trim(str) :去除字串首尾兩端的空格
• upper(str) : 字串轉大寫
• concat(s1,s2…,sn) : 將 s1,s2…,sn 連線成字串

# concat
insert into tbl_name values( concat('abc','def') );

8.3 日期函式

• year(date) : 返回日期 date的年份(1000~9999)
• month(date) : 返回 date 的月份值(1~12)
• day(date)：返回 date 的日(1~31)
• curdate() : 返回當前的日期
• week(date) : 返回日期 date為一年中第幾周(0~53)
• now() : 返回當前的日期和時間
• curtime() : 返回當前的時間
• hour(time) : 返回 time的小時值(0~23)
• minute(time) : 返回 time的分鐘值(0~59)

8.3 聚合函式

• count(col) : 統計記錄的條數
• sum(col) : 求和
• avg(col) : 求平均值
• max(col): 求最大值
• min(col) : 求最小值

# count 統計總記錄數
select count(*) from tbl_name;
# sum 年齡總和
select sum(age) from tbl_name;
# avg 平均年齡
select avg(age) from tbl_name;
# 最大年齡
select min(birthday) from tbl_name; # 日期最小的

FOUND_ROWS()

FOUND_ROWS 函式配合 SQL_CALC_FOUND_ROWS 用於獲取總記錄數（忽略limit）

select SQL_CALC_FOUND_ROWS * from qa_list limit 3;
select FOUND_ROWS();

第一個 sql 裡面的 SQL_CALC_FOUND_ROWS 不可省略，它表示需要取得結果數，也是後面使用 FOUND_ROWS()函式的鋪墊。
FOUND_ROWS()返回的結果是臨時的。如果程式往後會用到這個數字，必須提前它儲存在一個變數中待用。

FOUND_ROWS()與 count()的區別：
1、當 SQL限制條件太多時， count()的執行效率不是很高，最好使用 FOUND_RO
WS()
2、當 SQL查詢語句沒有 where等條件限制時，使用 count()函式的執行效率較高。

九. 資料庫備份

9.1 備份資料

mysqldump -u [使用者名稱] -p [密碼] [資料庫名] > [path]/[名稱].sql

9.2 恢復資料

mysql>source c:\system.sql

…

一. 1+X Web前端中級必考 | PHP 技術與應用
二. 1+X Web 前端等級考試知識點 | jQuery基礎
1+X Web前端等級考證 | 深入淺出MySQL資料庫