國家 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資料庫