狂神老師的MySQL學習筆記

支持一下狂神老師的學習平臺：www.kuangStudy.com

MySQL01：初識MySQL

為什么學習資料庫

1、崗位技能需求

2、現在的世界,得資料者得天下

3、存盤資料的方法

4、程式,網站中,大量資料如何長久保存?

5、資料庫是幾乎軟體體系中最核心的一個存在，

什么是資料庫

資料庫 ( DataBase , 簡稱DB )

概念 : 長期存放在計算機內,有組織,可共享的大量資料的集合,是一個資料 “倉庫”

作用 : 保存,并能安全管理資料(如:增刪改查等),減少冗余…

資料庫總覽 :

• 關系型資料庫 ( SQL )

• • MySQL , Oracle , SQL Server , SQLite , DB2 , …
  • 關系型資料庫通過外鍵關聯來建立表與表之間的關系

• 非關系型資料庫 ( NOSQL )

• • Redis , MongoDB , …
  • 非關系型資料庫通常指資料以物件的形式存盤在資料庫中，而物件之間的關系通過每個物件自身的屬性來決定

什么是DBMS

資料庫管理系統 ( DataBase Management System )

資料庫管理軟體 , 科學組織和存盤資料 , 高效地獲取和維護資料

為什么要說這個呢?

因為我們要學習的MySQL應該算是一個資料庫管理系統.

MySQL簡介

概念 : 是現在流行的開源的,免費的 關系型資料庫

歷史 : 由瑞典MySQL AB 公司開發，目前屬于 Oracle 旗下產品，

特點 :

• 免費 , 開源資料庫
• 小巧 , 功能齊全
• 使用便捷
• 可運行于Windows或Linux作業系統
• 可適用于中小型甚至大型網站應用

官網 : https://www.mysql.com/

安裝MySQL

這里建議大家使用壓縮版,安裝快,方便.不復雜.

軟體下載

mysql5.7 64位下載地址:

https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-winx64.zip

電腦是64位的就下載使用64位版本的！

安裝步驟

1、下載后得到zip壓縮包.

2、解壓到自己想要安裝到的目錄，本人解壓到的是D:\Environment\mysql-5.7.19

3、添加環境變數：我的電腦->屬性->高級->環境變數

選擇PATH,在其后面添加: 你的mysql 安裝檔案下面的bin檔案夾

4、編輯 my.ini 檔案 ,注意替換路徑位置

[mysqld]
basedir=D:\Program Files\mysql-5.7\
datadir=D:\Program Files\mysql-5.7\data\
port=3306
skip-grant-tables

5、啟動管理員模式下的CMD，并將路徑切換至mysql下的bin目錄，然后輸入mysqld –install (安裝mysql)

6、再輸入 mysqld --initialize-insecure --user=mysql 初始化資料檔案

7、然后再次啟動mysql 然后用命令 mysql –u root –p 進入mysql管理界面（密碼可為空）

8、進入界面后更改root密碼

update mysql.user set authentication_string=password('123456') where user='root'and Host = 'localhost';

9、重繪權限

flush privileges;

10、修改 my.ini檔案洗掉最后一句skip-grant-tables

11、重啟mysql即可正常使用

net stop mysql
net start mysql

12、連接上測驗出現以下結果就安裝好了

一步步去做 , 理論上是沒有任何問題的 .

如果您以前裝過,現在需要重裝,一定要將環境清理干凈 .

好了,到這里大家都裝好了,因為剛接觸,所以我們先不學習命令.

這里給大家推薦一個工具 : SQLyog .

即便有了可視化工具,可是基本的DOS命名大家還是要記住!

SQLyog

可手動操作,管理MySQL資料庫的軟體工具

特點 : 簡潔 , 易用 , 圖形化

使用SQLyog管理工具自己完成以下操作 :

• 連接本地MySQL資料庫

• 新建MySchool資料庫

• • 欄位
  • GradeID : int(11) , Primary Key (pk)
  • GradeName : varchar(50)
  • 資料庫名稱MySchool
  • 新建資料庫表(grade)

在歷史記錄中可以看到相對應的資料庫操作的陳述句 .

連接資料庫

打開MySQL命令視窗

• 在DOS命令列視窗進入 安裝目錄\mysql\bin
• 可設定環境變數，設定了環境變數，可以在任意目錄打開！

連接資料庫陳述句 : mysql -h 服務器主機地址 -u 用戶名 -p 用戶密碼

注意 : -p后面不能加空格,否則會被當做密碼的內容,導致登錄失敗 !

幾個基本的資料庫操作命令 :

update user set password=password('123456')where user='root'; 修改密碼
flush privileges; 重繪資料庫
show databases; 顯示所有資料庫
use dbname；打開某個資料庫
show tables; 顯示資料庫mysql中所有的表
describe user; 顯示表mysql資料庫中user表的列資訊
create database name; 創建資料庫
use databasename; 選擇資料庫

exit; 退出Mysql
? 命令關鍵詞 : 尋求幫助
-- 表示注釋

MySQL02：資料庫操作

結構化查詢陳述句分類

資料庫操作

命令列操作資料庫

創建資料庫 : create database [if not exists] 資料庫名;

洗掉資料庫 : drop database [if exists] 資料庫名;

查看資料庫 : show databases;

使用資料庫 : use 資料庫名;

對比工具操作資料庫

學習方法：

• 對照SQLyog工具自動生成的陳述句學習
• 固定語法中的單詞需要記憶
• 

創建資料表

屬于DDL的一種，語法 :

create table [if not exists] `表名`(
   '欄位名1' 列型別 [屬性][索引][注釋],
   '欄位名2' 列型別 [屬性][索引][注釋],
  #...
   '欄位名n' 列型別 [屬性][索引][注釋]
)[表型別][表字符集][注釋];

說明 : 反引號用于區別MySQL保留字與普通字符而引入的 (鍵盤esc下面的鍵).

資料值和列型別

列型別 : 規定資料庫中該列存放的資料型別

數值型別

字串型別

日期和時間型數值型別

NULL值

• 理解為 “沒有值” 或 “未知值”
• 不要用NULL進行算術運算 , 結果仍為NULL

資料欄位屬性

UnSigned

• 無符號的
• 宣告該資料列不允許負數 .

ZEROFILL

• 0填充的
• 不足位數的用0來填充 , 如int(3),5則為005

Auto_InCrement

• 自動增長的 , 每添加一條資料 , 自動在上一個記錄數上加 1(默認)

• 通常用于設定主鍵 , 且為整數型別

• 可定義起始值和步長

• • 當前表設定步長(AUTO_INCREMENT=100) : 只影響當前表
  • SET @@auto_increment_increment=5 ; 影響所有使用自增的表(全域)

NULL 和 NOT NULL

• 默認為NULL , 即沒有插入該列的數值
• 如果設定為NOT NULL , 則該列必須有值

DEFAULT

• 默認的
• 用于設定默認值
• 例如,性別欄位,默認為"男" , 否則為 “女” ; 若無指定該列的值 , 則默認值為"男"的值

-- 目標 : 創建一個school資料庫
-- 創建學生表(列,欄位)
-- 學號int 登錄密碼varchar(20) 姓名,性別varchar(2),出生日期(datatime),家庭住址,email
-- 創建表之前 , 一定要先選擇資料庫

CREATE TABLE IF NOT EXISTS `student` (
`id` int(4) NOT NULL AUTO_INCREMENT COMMENT '學號',
`name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密碼',
`sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性別',
`birthday` datetime DEFAULT NULL COMMENT '生日',
`address` varchar(100) DEFAULT NULL COMMENT '地址',
`email` varchar(50) DEFAULT NULL COMMENT '郵箱',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- 查看資料庫的定義
SHOW CREATE DATABASE school;
-- 查看資料表的定義
SHOW CREATE TABLE student;
-- 顯示表結構
DESC student;  -- 設定嚴格檢查模式(不能容錯了)SET sql_mode='STRICT_TRANS_TABLES';

資料表的型別

設定資料表的型別

CREATE TABLE 表名(
   -- 省略一些代碼
   -- Mysql注釋
   -- 1. # 單行注釋
   -- 2. /*...*/ 多行注釋
)ENGINE = MyISAM (or InnoDB)

-- 查看mysql所支持的引擎型別 (表型別)
SHOW ENGINES;

MySQL的資料表的型別 : MyISAM , InnoDB , HEAP , BOB , CSV等…

常見的 MyISAM 與 InnoDB 型別：

經驗 ( 適用場合 ) :

• 適用 MyISAM : 節約空間及相應速度
• 適用 InnoDB : 安全性 , 事務處理及多用戶操作資料表

資料表的存盤位置

• MySQL資料表以檔案方式存放在磁盤中

• • 包括表檔案 , 資料檔案 , 以及資料庫的選項檔案
  • 位置 : Mysql安裝目錄\data\下存放資料表 . 目錄名對應資料庫名 , 該目錄下檔案名對應資料表 .

• 注意 :

• • * . frm – 表結構定義檔案

  • * . MYD – 資料檔案 ( data )

  • * . MYI – 索引檔案 ( index )

  • InnoDB型別資料表只有一個 *.frm檔案 , 以及上一級目錄的ibdata1檔案

  • MyISAM型別資料表對應三個檔案 :

    

設定資料表字符集

我們可為資料庫,資料表,資料列設定不同的字符集，設定方法 :

• 創建時通過命令來設定 , 如 : CREATE TABLE 表名()CHARSET = utf8;
• 如無設定 , 則根據MySQL資料庫組態檔 my.ini 中的引數設定

修改資料庫

修改表 ( ALTER TABLE )

修改表名 :ALTER TABLE 舊表名 RENAME AS 新表名

添加欄位 : ALTER TABLE 表名 ADD欄位名 列屬性[屬性]

修改欄位 :

• ALTER TABLE 表名 MODIFY 欄位名 列型別[屬性]
• ALTER TABLE 表名 CHANGE 舊欄位名 新欄位名 列屬性[屬性]

洗掉欄位 : ALTER TABLE 表名 DROP 欄位名

洗掉資料表

語法：DROP TABLE [IF EXISTS] 表名

• IF EXISTS為可選 , 判斷是否存在該資料表
• 如洗掉不存在的資料表會拋出錯誤

其他

1. 可用反引號（`）為識別符號（庫名、表名、欄位名、索引、別名）包裹，以避免與關鍵字重名！中文也可以作為識別符號！

2. 每個庫目錄存在一個保存當前資料庫的選項檔案db.opt，

3. 注釋：
  單行注釋 # 注釋內容
  多行注釋 /* 注釋內容 */
  單行注釋 -- 注釋內容       (標準SQL注釋風格，要求雙破折號后加一空格符（空格、TAB、換行等）)
   
4. 模式通配符：
  _   任意單個字符
  %   任意多個字符，甚至包括零字符
  單引號需要進行轉義 \'
   
5. CMD命令列內的陳述句結束符可以為 ";", "\G", "\g"，僅影響顯示結果，其他地方還是用分號結束，delimiter 可修改當前對話的陳述句結束符，

6. SQL對大小寫不敏感 （關鍵字）

7. 清除已有陳述句：\c

MySQL03：DML語言

外鍵

外鍵概念

如果公共關鍵字在一個關系中是主關鍵字，那么這個公共關鍵字被稱為另一個關系的外鍵，由此可見，外鍵表示了兩個關系之間的相關聯系，以另一個關系的外鍵作主關鍵字的表被稱為主表，具有此外鍵的表被稱為主表的從表，

在實際操作中，將一個表的值放入第二個表來表示關聯，所使用的值是第一個表的主鍵值(在必要時可包括復合主鍵值)，此時，第二個表中保存這些值的屬性稱為外鍵(foreign key)，

外鍵作用

保持資料一致性，完整性，主要目的是控制存盤在外鍵表中的資料,約束，使兩張表形成關聯，外鍵只能參考外表中的列的值或使用空值，

創建外鍵

建表時指定外鍵約束

-- 創建外鍵的方式一 : 創建子表同時創建外鍵

-- 年級表 (id\年級名稱)
CREATE TABLE `grade` (
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年級ID',
`gradename` VARCHAR(50) NOT NULL COMMENT '年級名稱',
PRIMARY KEY (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 學生資訊表 (學號,姓名,性別,年級,手機,地址,出生日期,郵箱,身份證號)
CREATE TABLE `student` (
`studentno` INT(4) NOT NULL COMMENT '學號',
`studentname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`sex` TINYINT(1) DEFAULT '1' COMMENT '性別',
`gradeid` INT(10) DEFAULT NULL COMMENT '年級',
`phoneNum` VARCHAR(50) NOT NULL COMMENT '手機',
`address` VARCHAR(255) DEFAULT NULL COMMENT '地址',
`borndate` DATETIME DEFAULT NULL COMMENT '生日',
`email` VARCHAR(50) DEFAULT NULL COMMENT '郵箱',
`idCard` VARCHAR(18) DEFAULT NULL COMMENT '身份證號',
PRIMARY KEY (`studentno`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

建表后修改

-- 創建外鍵方式二 : 創建子表完畢后,修改子表添加外鍵
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);

洗掉外鍵

操作：洗掉 grade 表，發現報錯

注意 : 洗掉具有主外鍵關系的表時 , 要先刪子表 , 后刪主表

-- 洗掉外鍵
ALTER TABLE student DROP FOREIGN KEY FK_gradeid;
-- 發現執行完上面的,索引還在,所以還要洗掉索引
-- 注:這個索引是建立外鍵的時候默認生成的
ALTER TABLE student DROP INDEX FK_gradeid;

DML語言

資料庫意義 ： 資料存盤、資料管理

管理資料庫資料方法：

• 通過SQLyog等管理工具管理資料庫資料
• 通過DML陳述句管理資料庫資料

DML語言 ：資料操作語言

• 用于操作資料庫物件中所包含的資料

• 包括 :

• • INSERT (添加資料陳述句)
  • UPDATE (更新資料陳述句)
  • DELETE (洗掉資料陳述句)

添加資料

INSERT命令

語法：

INSERT INTO 表名[(欄位1,欄位2,欄位3,...)] VALUES('值1','值2','值3')

注意 :

• 欄位或值之間用英文逗號隔開 .
• ’ 欄位1,欄位2…’ 該部分可省略 , 但添加的值務必與表結構,資料列,順序相對應,且數量一致 .
• 可同時插入多條資料 , values 后用英文逗號隔開 .

-- 使用陳述句如何增加陳述句?
-- 語法 : INSERT INTO 表名[(欄位1,欄位2,欄位3,...)] VALUES('值1','值2','值3')
INSERT INTO grade(gradename) VALUES ('大一');

-- 主鍵自增,那能否省略呢?
INSERT INTO grade VALUES ('大二');

-- 查詢:INSERT INTO grade VALUE ('大二')錯誤代碼：1136
Column count doesn`t match value count at row 1

-- 結論:'欄位1,欄位2...'該部分可省略 , 但添加的值務必與表結構,資料列,順序相對應,且數量一致.

-- 一次插入多條資料
INSERT INTO grade(gradename) VALUES ('大三'),('大四');

練習題目

自己使用INSERT陳述句為課程表subject添加資料 . 使用到外鍵.

修改資料

update命令

語法：

UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHEREcondition];

注意 :

• column_name 為要更改的資料列
• value 為修改后的資料 , 可以為變數 , 具體指 , 運算式或者嵌套的SELECT結果
• condition 為篩選條件 , 如不指定則修改該表的所有列資料

where條件子句

可以簡單的理解為 : 有條件地從表中篩選資料

測驗：

-- 修改年級資訊
UPDATE grade SET gradename = '高中' WHERE gradeid = 1;

洗掉資料

DELETE命令

語法：

DELETE FROM 表名 [WHERE condition];

注意：condition為篩選條件 , 如不指定則洗掉該表的所有列資料

-- 洗掉最后一個資料
DELETE FROM grade WHERE gradeid = 5

TRUNCATE命令

作用：用于完全清空表資料 , 但表結構 , 索引 , 約束等不變 ;

語法：

TRUNCATE [TABLE] table_name;

-- 清空年級表
TRUNCATE grade

注意：區別于DELETE命令

• 相同 : 都能洗掉資料 , 不洗掉表結構 , 但TRUNCATE速度更快

• 不同 :

• • 使用TRUNCATE TABLE 重新設定AUTO_INCREMENT計數器
  • 使用TRUNCATE TABLE不會對事務有影響 （事務后面會說）

測驗：

-- 創建一個測驗表
CREATE TABLE `test` (
`id` INT(4) NOT NULL AUTO_INCREMENT,
`coll` VARCHAR(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 插入幾個測驗資料
INSERT INTO test(coll) VALUES('row1'),('row2'),('row3');

-- 洗掉表資料(不帶where條件的delete)
DELETE FROM test;
-- 結論:如不指定Where則洗掉該表的所有列資料,自增當前值依然從原來基礎上進行,會記錄日志.

-- 洗掉表資料(truncate)
TRUNCATE TABLE test;
-- 結論:truncate洗掉資料,自增當前值會恢復到初始值重新開始;不會記錄日志.

-- 同樣使用DELETE清空不同引擎的資料庫表資料.重啟資料庫服務后
-- InnoDB : 自增列從初始值重新開始 (因為是存盤在記憶體中,斷電即失)
-- MyISAM : 自增列依然從上一個自增資料基礎上開始 (存在檔案中,不會丟失)

MySQL04：使用DQL查詢資料

DQL語言

DQL( Data Query Language 資料查詢語言 )

• 查詢資料庫資料 , 如SELECT陳述句
• 簡單的單表查詢或多表的復雜查詢和嵌套查詢
• 是資料庫語言中最核心,最重要的陳述句
• 使用頻率最高的陳述句

SELECT語法

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
  [left | right | inner join table_name2]  -- 聯合查詢
  [WHERE ...]  -- 指定結果需滿足的條件
  [GROUP BY ...]  -- 指定結果按照哪幾個欄位來分組
  [HAVING]  -- 過濾分組的記錄必須滿足的次要條件
  [ORDER BY ...]  -- 指定查詢記錄按一個或多個條件排序
  [LIMIT {[offset,]row_count | row_countOFFSET offset}];
   -- 指定查詢的記錄從哪條至哪條

注意 : [ ] 括號代表可選的 , { }括號代表必選得

指定查詢欄位

-- 查詢表中所有的資料列結果 , 采用 **" \* "** 符號; 但是效率低，不推薦 .

-- 查詢所有學生資訊
SELECT * FROM student;

-- 查詢指定列(學號 , 姓名)
SELECT studentno,studentname FROM student;

AS 子句作為別名

作用：

• 可給資料列取一個新別名
• 可給表取一個新別名
• 可把經計算或總結的結果用另一個新名稱來代替

-- 這里是為列取別名(當然as關鍵詞可以省略)
SELECT studentno AS 學號,studentname AS 姓名 FROM student;

-- 使用as也可以為表取別名
SELECT studentno AS 學號,studentname AS 姓名 FROM student AS s;

-- 使用as,為查詢結果取一個新名字
-- CONCAT()函式拼接字串
SELECT CONCAT('姓名:',studentname) AS 新姓名 FROM student;

DISTINCT關鍵字的使用

作用 : 去掉SELECT查詢回傳的記錄結果中重復的記錄 ( 回傳所有列的值都相同 ) , 只回傳一條

-- # 查看哪些同學參加了考試(學號) 去除重復項
SELECT * FROM result; -- 查看考試成績
SELECT studentno FROM result; -- 查看哪些同學參加了考試
SELECT DISTINCT studentno FROM result; -- 了解:DISTINCT 去除重復項 , (默認是ALL)

使用運算式的列

資料庫中的運算式 : 一般由文本值 , 列值 , NULL , 函式和運算子等組成

應用場景 :

• SELECT陳述句回傳結果列中使用

• SELECT陳述句中的ORDER BY , HAVING等子句中使用

• DML陳述句中的 where 條件陳述句中使用運算式

  -- selcet查詢中可以使用運算式
  SELECT @@auto_increment_increment; -- 查詢自增步長
  SELECT VERSION(); -- 查詢版本號
  SELECT 100*3-1 AS 計算結果; -- 運算式
  
  -- 學員考試成績集體提分一分查看
  SELECT studentno,StudentResult+1 AS '提分后' FROM result;
  

• 避免SQL回傳結果中包含 ’ . ’ , ’ * ’ 和括號等干擾開發語言程式.

where條件陳述句

作用：用于檢索資料表中 符合條件 的記錄

搜索條件可由一個或多個邏輯運算式組成 , 結果一般為真或假.

邏輯運算子

測驗

-- 滿足條件的查詢(where)
SELECT Studentno,StudentResult FROM result;

-- 查詢考試成績在95-100之間的
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult>=95 AND StudentResult<=100;

-- AND也可以寫成 &&
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult>=95 && StudentResult<=100;

-- 模糊查詢(對應的詞:精確查詢)
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult BETWEEN 95 AND 100;

-- 除了1000號同學,要其他同學的成績
SELECT studentno,studentresult
FROM result
WHERE studentno!=1000;

-- 使用NOT
SELECT studentno,studentresult
FROM result
WHERE NOT studentno=1000;

模糊查詢 ：比較運算子

注意：

• 數值資料型別的記錄之間才能進行算術運算 ;
• 相同資料型別的資料之間才能進行比較 ;

測驗：

-- 模糊查詢 between and \ like \ in \ null

-- =============================================
-- LIKE
-- =============================================
-- 查詢姓劉的同學的學號及姓名
-- like結合使用的通配符 : % (代表0到任意個字符) _ (一個字符)
SELECT studentno,studentname FROM student
WHERE studentname LIKE '劉%';

-- 查詢姓劉的同學,后面只有一個字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '劉_';

-- 查詢姓劉的同學,后面只有兩個字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '劉__';

-- 查詢姓名中含有 嘉 字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '%嘉%';

-- 查詢姓名中含有特殊字符的需要使用轉義符號 '\'
-- 自定義轉義符關鍵字: ESCAPE ':'

-- =============================================
-- IN
-- =============================================
-- 查詢學號為1000,1001,1002的學生姓名
SELECT studentno,studentname FROM student
WHERE studentno IN (1000,1001,1002);

-- 查詢地址在北京,南京,河南洛陽的學生
SELECT studentno,studentname,address FROM student
WHERE address IN ('北京','南京','河南洛陽');

-- =============================================
-- NULL 空
-- =============================================
-- 查詢出生日期沒有填寫的同學
-- 不能直接寫=NULL , 這是代表錯誤的 , 用 is null
SELECT studentname FROM student
WHERE BornDate IS NULL;

-- 查詢出生日期填寫的同學
SELECT studentname FROM student
WHERE BornDate IS NOT NULL;

-- 查詢沒有寫家庭住址的同學(空字串不等于null)
SELECT studentname FROM student
WHERE Address='' OR Address IS NULL;

連接查詢

JOIN 對比

七種Join：

測驗

/*
連接查詢
   如需要多張資料表的資料進行查詢,則可通過連接運算子實作多個查詢
內連接 inner join
   查詢兩個表中的結果集中的交集
外連接 outer join
   左外連接 left join
       (以左表作為基準,右邊表來一一匹配,匹配不上的,回傳左表的記錄,右表以NULL填充)
   右外連接 right join
       (以右表作為基準,左邊表來一一匹配,匹配不上的,回傳右表的記錄,左表以NULL填充)
       
等值連接和非等值連接

自連接
*/

-- 查詢參加了考試的同學資訊(學號,學生姓名,科目編號,分數)
SELECT * FROM student;
SELECT * FROM result;

/*思路:
(1):分析需求,確定查詢的列來源于兩個類,student result,連接查詢
(2):確定使用哪種連接查詢?(內連接)
*/
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno

-- 右連接(也可實作)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno

-- 等值連接
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s , result r
WHERE r.studentno = s.studentno

-- 左連接 (查詢了所有同學,不考試的也會查出來)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno

-- 查一下缺考的同學(左連接應用場景)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
WHERE StudentResult IS NULL

-- 思考題:查詢參加了考試的同學資訊(學號,學生姓名,科目名,分數)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno

自連接

/*
自連接
   資料表與自身進行連接

需求:從一個包含欄目ID , 欄目名稱和父欄目ID的表中
    查詢父欄目名稱和其他子欄目名稱
*/

-- 創建一個表
CREATE TABLE `category` (
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主題id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryName` VARCHAR(50) NOT NULL COMMENT '主題名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

-- 插入資料
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES('2','1','資訊技術'),
('3','1','軟體開發'),
('4','3','資料庫'),
('5','1','美術設計'),
('6','3','web開發'),
('7','5','ps技術'),
('8','2','辦公資訊');

-- 撰寫SQL陳述句,將欄目的父子關系呈現出來 (父欄目名稱,子欄目名稱)
-- 核心思想:把一張表看成兩張一模一樣的表,然后將這兩張表連接查詢(自連接)
SELECT a.categoryName AS '父欄目',b.categoryName AS '子欄目'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`

-- 思考題:查詢參加了考試的同學資訊(學號,學生姓名,科目名,分數)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno

-- 查詢學員及所屬的年級(學號,學生姓名,年級名)
SELECT studentno AS 學號,studentname AS 學生姓名,gradename AS 年級名稱
FROM student s
INNER JOIN grade g
ON s.`GradeId` = g.`GradeID`

-- 查詢科目及所屬的年級(科目名稱,年級名稱)
SELECT subjectname AS 科目名稱,gradename AS 年級名稱
FROM SUBJECT sub
INNER JOIN grade g
ON sub.gradeid = g.gradeid

-- 查詢 資料庫結構-1 的所有考試結果(學號 學生姓名 科目名稱 成績)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='資料庫結構-1'

排序和分頁

測驗

/*============== 排序 ================
語法 : ORDER BY
   ORDER BY 陳述句用于根據指定的列對結果集進行排序，
   ORDER BY 陳述句默認按照ASC升序對記錄進行排序，
   如果您希望按照降序對記錄進行排序，可以使用 DESC 關鍵字，
   
*/

-- 查詢 資料庫結構-1 的所有考試結果(學號 學生姓名 科目名稱 成績)
-- 按成績降序排序
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='資料庫結構-1'
ORDER BY StudentResult DESC

/*============== 分頁 ================
語法 : SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
好處 : (用戶體驗,網路傳輸,查詢壓力)

推導:
   第一頁 : limit 0,5
   第二頁 : limit 5,5
   第三頁 : limit 10,5
   ......
   第N頁 : limit (pageNo-1)*pageSzie,pageSzie
   [pageNo:頁碼,pageSize:單頁面顯示條數]
   
*/

-- 每頁顯示5條資料
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='資料庫結構-1'
ORDER BY StudentResult DESC , studentno
LIMIT 0,5

-- 查詢 JAVA第一學年 課程成績前10名并且分數大于80的學生資訊(學號,姓名,課程名,分數)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='JAVA第一學年'
ORDER BY StudentResult DESC
LIMIT 0,10

子查詢

/*============== 子查詢 ================
什么是子查詢?
   在查詢陳述句中的WHERE條件子句中,又嵌套了另一個查詢陳述句
   嵌套查詢可由多個子查詢組成,求解的方式是由里及外;
   子查詢回傳的結果一般都是集合,故而建議使用IN關鍵字;
*/

-- 查詢 資料庫結構-1 的所有考試結果(學號,科目編號,成績),并且成績降序排列
-- 方法一:使用連接查詢
SELECT studentno,r.subjectno,StudentResult
FROM result r
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE subjectname = '資料庫結構-1'
ORDER BY studentresult DESC;

-- 方法二:使用子查詢(執行順序:由里及外)
SELECT studentno,subjectno,StudentResult
FROM result
WHERE subjectno=(
   SELECT subjectno FROM `subject`
   WHERE subjectname = '資料庫結構-1'
)
ORDER BY studentresult DESC;

-- 查詢課程為 高等數學-2 且分數不小于80分的學生的學號和姓名
-- 方法一:使用連接查詢
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON sub.`SubjectNo` = r.`SubjectNo`
WHERE subjectname = '高等數學-2' AND StudentResult>=80

-- 方法二:使用連接查詢+子查詢
-- 分數不小于80分的學生的學號和姓名
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80

-- 在上面SQL基礎上,添加需求:課程為 高等數學-2
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80 AND subjectno=(
   SELECT subjectno FROM `subject`
   WHERE subjectname = '高等數學-2'
)

-- 方法三:使用子查詢
-- 分步寫簡單sql陳述句,然后將其嵌套起來
SELECT studentno,studentname FROM student WHERE studentno IN(
   SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=(
       SELECT subjectno FROM `subject` WHERE subjectname = '高等數學-2'
  )
)

/*
練習題目:
   查 C語言-1 的前5名學生的成績資訊(學號,姓名,分數)
   使用子查詢,查詢郭靖同學所在的年級名稱
*/

MySQL05：MySQL函式

常用函式

資料函式

 SELECT ABS(-8);  /*絕對值*/
 SELECT CEILING(9.4); /*向上取整*/
 SELECT FLOOR(9.4);   /*向下取整*/
 SELECT RAND();  /*亂數,回傳一個0-1之間的亂數*/
 SELECT SIGN(0); /*符號函式: 負數回傳-1,正數回傳1,0回傳0*/

字串函式

 SELECT CHAR_LENGTH('狂神說堅持就能成功'); /*回傳字串包含的字符數*/
 SELECT CONCAT('我','愛','程式');  /*合并字串,引數可以有多個*/
 SELECT INSERT('我愛編程helloworld',1,2,'超級熱愛');  /*替換字串,從某個位置開始替換某個長度*/
 SELECT LOWER('KuangShen'); /*小寫*/
 SELECT UPPER('KuangShen'); /*大寫*/
 SELECT LEFT('hello,world',5);   /*從左邊截取*/
 SELECT RIGHT('hello,world',5);  /*從右邊截取*/
 SELECT REPLACE('狂神說堅持就能成功','堅持','努力');  /*替換字串*/
 SELECT SUBSTR('狂神說堅持就能成功',4,6); /*截取字串,開始和長度*/
 SELECT REVERSE('狂神說堅持就能成功'); /*反轉
 
 -- 查詢姓周的同學,改成鄒
 SELECT REPLACE(studentname,'周','鄒') AS 新名字
 FROM student WHERE studentname LIKE '周%';

日期和時間函式

 SELECT CURRENT_DATE();   /*獲取當前日期*/
 SELECT CURDATE();   /*獲取當前日期*/
 SELECT NOW();   /*獲取當前日期和時間*/
 SELECT LOCALTIME();   /*獲取當前日期和時間*/
 SELECT SYSDATE();   /*獲取當前日期和時間*/
 
 -- 獲取年月日,時分秒
 SELECT YEAR(NOW());
 SELECT MONTH(NOW());
 SELECT DAY(NOW());
 SELECT HOUR(NOW());
 SELECT MINUTE(NOW());
 SELECT SECOND(NOW());

系統資訊函式

 SELECT VERSION();  /*版本*/
 SELECT USER();     /*用戶*/
 
 

聚合函式

函式名稱	描述
COUNT()	回傳滿足Select條件的記錄總和數，如 select count(*) 【不建議使用 *，效率低】
SUM()	回傳數字欄位或運算式列作統計，回傳一列的總和，
AVG()	通常為數值欄位或表達列作統計，回傳一列的平均值
MAX()	可以為數值欄位，字符欄位或運算式列作統計，回傳最大的值，
MIN()	可以為數值欄位，字符欄位或運算式列作統計，回傳最小的值，

 -- 聚合函式
 /*COUNT:非空的*/
 SELECT COUNT(studentname) FROM student;
 SELECT COUNT(*) FROM student;
 SELECT COUNT(1) FROM student;  /*推薦*/
 
 -- 從含義上講，count(1) 與 count(*) 都表示對全部資料行的查詢，
 -- count(欄位) 會統計該欄位在表中出現的次數，忽略欄位為null 的情況，即不統計欄位為null 的記錄，
 -- count(*) 包括了所有的列，相當于行數，在統計結果的時候，包含欄位為null 的記錄；
 -- count(1) 用1代表代碼行，在統計結果的時候，包含欄位為null 的記錄 ，
 /*
 很多人認為count(1)執行的效率會比count(*)高，原因是count(*)會存在全表掃描，而count(1)可以針對一個欄位進行查詢，其實不然，count(1)和count(*)都會對全表進行掃描，統計所有記錄的條數，包括那些為null的記錄，因此，它們的效率可以說是相差無幾，而count(欄位)則與前兩者不同，它會統計該欄位不為null的記錄條數，
 
 下面它們之間的一些對比：
 
 1）在表沒有主鍵時，count(1)比count(*)快
 2）有主鍵時，主鍵作為計算條件，count(主鍵)效率最高；
 3）若表格只有一個欄位，則count(*)效率較高，
 */
 
 SELECT SUM(StudentResult) AS 總和 FROM result;
 SELECT AVG(StudentResult) AS 平均分 FROM result;
 SELECT MAX(StudentResult) AS 最高分 FROM result;
 SELECT MIN(StudentResult) AS 最低分 FROM result;

題目：

 -- 查詢不同課程的平均分,最高分,最低分
 -- 前提:根據不同的課程進行分組
 
 SELECT subjectname,AVG(studentresult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
 FROM result AS r
 INNER JOIN `subject` AS s
 ON r.subjectno = s.subjectno
 GROUP BY r.subjectno
 HAVING 平均分>80;
 
 /*
 where寫在group by前面.
 要是放在分組后面的篩選
 要使用HAVING..
 因為having是從前面篩選的欄位再篩選，而where是從資料表中的>欄位直接進行的篩選的
 */

MD5 加密

一、MD5簡介

MD5即Message-Digest Algorithm 5（資訊-摘要演算法5），用于確保資訊傳輸完整一致，是計算機廣泛使用的雜湊演算法之一（又譯摘要演算法、哈希演算法），主流編程語言普遍已有MD5實作，將資料（如漢字）運算為另一固定長度值，是雜湊演算法的基礎原理，MD5的前身有MD2、MD3和MD4，

二、實作資料加密

新建一個表 testmd5

 CREATE TABLE `testmd5` (
  `id` INT(4) NOT NULL,
  `name` VARCHAR(20) NOT NULL,
  `pwd` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`)
 ) ENGINE=INNODB DEFAULT CHARSET=utf8

插入一些資料

 INSERT INTO testmd5 VALUES(1,'kuangshen','123456'),(2,'qinjiang','456789')

如果我們要對pwd這一列資料進行加密，語法是：

 update testmd5 set pwd = md5(pwd);

如果單獨對某個用戶(如kuangshen)的密碼加密：

 INSERT INTO testmd5 VALUES(3,'kuangshen2','123456')
 update testmd5 set pwd = md5(pwd) where name = 'kuangshen2';

插入新的資料自動加密

 INSERT INTO testmd5 VALUES(4,'kuangshen3',md5('123456'));

查詢登錄用戶資訊（md5對比使用，查看用戶輸入加密后的密碼進行比對）

 SELECT * FROM testmd5 WHERE `name`='kuangshen' AND pwd=MD5('123456');

小結

 -- ================ 內置函式 ================
 -- 數值函式
 abs(x)            -- 絕對值 abs(-10.9) = 10
 format(x, d)    -- 格式化千分位數值 format(1234567.456, 2) = 1,234,567.46
 ceil(x)            -- 向上取整 ceil(10.1) = 11
 floor(x)        -- 向下取整 floor (10.1) = 10
 round(x)        -- 四舍五入去整
 mod(m, n)        -- m%n m mod n 求余 10%3=1
 pi()            -- 獲得圓周率
 pow(m, n)        -- m^n
 sqrt(x)            -- 算術平方根
 rand()            -- 亂數
 truncate(x, d)    -- 截取d位小數
 
 -- 時間日期函式
 now(), current_timestamp();     -- 當前日期時間
 current_date();                    -- 當前日期
 current_time();                    -- 當前時間
 date('yyyy-mm-dd hh:ii:ss');    -- 獲取日期部分
 time('yyyy-mm-dd hh:ii:ss');    -- 獲取時間部分
 date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j');    -- 格式化時間
 unix_timestamp();                -- 獲得unix時間戳
 from_unixtime();                -- 從時間戳獲得時間
 
 -- 字串函式
 length(string)            -- string長度，位元組
 char_length(string)        -- string的字符個數
 substring(str, position [,length])        -- 從str的position開始,取length個字符
 replace(str ,search_str ,replace_str)    -- 在str中用replace_str替換search_str
 instr(string ,substring)    -- 回傳substring首次在string中出現的位置
 concat(string [,...])    -- 連接字串
 charset(str)            -- 回傳字串字符集
 lcase(string)            -- 轉換成小寫
 left(string, length)    -- 從string2中的左邊起取length個字符
 load_file(file_name)    -- 從檔案讀取內容
 locate(substring, string [,start_position])    -- 同instr,但可指定開始位置
 lpad(string, length, pad)    -- 重復用pad加在string開頭,直到字串長度為length
 ltrim(string)            -- 去除前端空格
 repeat(string, count)    -- 重復count次
 rpad(string, length, pad)    --在str后用pad補充,直到長度為length
 rtrim(string)            -- 去除后端空格
 strcmp(string1 ,string2)    -- 逐字符比較兩字串大小
 
 -- 聚合函式
 count()
 sum();
 max();
 min();
 avg();
 group_concat()
 
 -- 其他常用函式
 md5();
 default();

MySQL06：事務和索引

事務

什么是事務

• 事務就是將一組SQL陳述句放在同一批次內去執行
• 如果一個SQL陳述句出錯,則該批次內的所有SQL都將被取消執行
• MySQL事務處理只支持InnoDB和BDB資料表型別

事務的ACID原則 百度 ACID

原子性(Atomic)

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

一致性(Consist)

• 一個事務可以封裝狀態改變（除非它是一個只讀的），事務必須始終保持系統處于一致的狀態，不管在任何給定的時間并發事務有多少，也就是說：如果事務是并發多個，系統也必須如同串行事務一樣操作，其主要特征是保護性和不變性(Preserving an Invariant)，以轉賬案例為例，假設有五個賬戶，每個賬戶余額是100元，那么五個賬戶總額是500元，如果在這個5個賬戶之間同時發生多個轉賬，無論并發多少個，比如在A與B賬戶之間轉賬5元，在C與D賬戶之間轉賬10元，在B與E之間轉賬15元，五個賬戶總額也應該還是500元，這就是保護性和不變性，

隔離性(Isolated)

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

持久性(Durable)

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

基本語法

-- 使用set陳述句來改變自動提交模式
SET autocommit = 0;   /*關閉*/
SET autocommit = 1;   /*開啟*/

-- 注意:
--- 1.MySQL中默認是自動提交
--- 2.使用事務時應先關閉自動提交

-- 開始一個事務,標記事務的起始點
START TRANSACTION  

-- 提交一個事務給資料庫
COMMIT

-- 將事務回滾,資料回到本次事務的初始狀態
ROLLBACK

-- 還原MySQL資料庫的自動提交
SET autocommit =1;

-- 保存點
SAVEPOINT 保存點名稱 -- 設定一個事務保存點
ROLLBACK TO SAVEPOINT 保存點名稱 -- 回滾到保存點
RELEASE SAVEPOINT 保存點名稱 -- 洗掉保存點

測驗

/*
課堂測驗題目

A在線買一款價格為500元商品,網上銀行轉賬.
A的銀行卡余額為2000,然后給商家B支付500.
商家B一開始的銀行卡余額為10000

創建資料庫shop和創建表account并插入2條資料
*/

CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;

CREATE TABLE `account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`cash` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO account (`name`,`cash`)
VALUES('A',2000.00),('B',10000.00)

-- 轉賬實作
SET autocommit = 0; -- 關閉自動提交
START TRANSACTION;  -- 開始一個事務,標記事務的起始點
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- 提交事務
# rollback;
SET autocommit = 1; -- 恢復自動提交

索引

索引的作用

• 提高查詢速度
• 確保資料的唯一性
• 可以加速表和表之間的連接 , 實作表與表之間的參照完整性
• 使用分組和排序子句進行資料檢索時 , 可以顯著減少分組和排序的時間
• 全文檢索欄位進行搜索優化.

分類

• 主鍵索引 (Primary Key)
• 唯一索引 (Unique)
• 常規索引 (Index)
• 全文索引 (FullText)

主鍵索引

主鍵 : 某一個屬性組能唯一標識一條記錄

特點 :

• 最常見的索引型別
• 確保資料記錄的唯一性
• 確定特定資料記錄在資料庫中的位置

唯一索引

作用 : 避免同一個表中某資料列中的值重復

與主鍵索引的區別

• 主鍵索引只能有一個
• 唯一索引可能有多個

CREATE TABLE `Grade`(
  `GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,
  `GradeName` VARCHAR(32) NOT NULL UNIQUE
   -- 或 UNIQUE KEY `GradeID` (`GradeID`)
)

常規索引

作用 : 快速定位特定資料

注意 :

• index 和 key 關鍵字都可以設定常規索引
• 應加在查詢找條件的欄位
• 不宜添加太多常規索引,影響資料的插入,洗掉和修改操作

CREATE TABLE `result`(
   -- 省略一些代碼
  INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- 創建表時添加
)
-- 創建后添加
ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);

全文索引

百度搜索：全文索引

作用 : 快速定位特定資料

注意 :

• 只能用于MyISAM型別的資料表
• 只能用于CHAR , VARCHAR , TEXT資料列型別
• 適合大型資料集

/*
#方法一：創建表時
  　　CREATE TABLE 表名 (
               欄位名1 資料型別 [完整性約束條件…],
               欄位名2 資料型別 [完整性約束條件…],
               [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
               [索引名] (欄位名[(長度)] [ASC |DESC])
               );


#方法二：CREATE在已存在的表上創建索引
       CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
                    ON 表名 (欄位名[(長度)] [ASC |DESC]) ;


#方法三：ALTER TABLE在已存在的表上創建索引
       ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                            索引名 (欄位名[(長度)] [ASC |DESC]) ;
                           
                           
#洗掉索引：DROP INDEX 索引名 ON 表名字;
#洗掉主鍵索引: ALTER TABLE 表名 DROP PRIMARY KEY;


#顯示索引資訊: SHOW INDEX FROM student;
*/

/*增加全文索引*/
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`StudentName`);

/*EXPLAIN : 分析SQL陳述句執行性能*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';

/*使用全文索引*/
-- 全文搜索通過 MATCH() 函式完成，
-- 搜索字串作為 against() 的引數被給定，搜索以忽略字母大小寫的方式執行，對于表中的每個記錄行，MATCH() 回傳一個相關性值，即，在搜索字串與記錄行在 MATCH() 串列中指定的列的文本之間的相似性尺度，
EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');

/*
開始之前，先說一下全文索引的版本、存盤引擎、資料型別的支持情況

MySQL 5.6 以前的版本，只有 MyISAM 存盤引擎支持全文索引；
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存盤引擎均支持全文索引;
只有欄位的資料型別為 char、varchar、text 及其系列才可以建全文索引，
測驗或使用全文索引時，要先看一下自己的 MySQL 版本、存盤引擎和資料型別是否支持全文索引，
*/

拓展：測驗索引

建表app_user：

CREATE TABLE `app_user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT '' COMMENT '用戶昵稱',
`email` varchar(50) NOT NULL COMMENT '用戶郵箱',
`phone` varchar(20) DEFAULT '' COMMENT '手機號',
`gender` tinyint(4) unsigned DEFAULT '0' COMMENT '性別（0:男；1：女）',
`password` varchar(100) NOT NULL COMMENT '密碼',
`age` tinyint(4) DEFAULT '0' COMMENT '年齡',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用戶表'

批量插入資料：100w

DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i < num DO
  INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
   VALUES(CONCAT('用戶', i), '24736743@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
  SET i = i + 1;
END WHILE;
RETURN i;
END;
SELECT mock_data();

索引效率測驗

無索引

SELECT * FROM app_user WHERE name = '用戶9999'; -- 查看耗時
SELECT * FROM app_user WHERE name = '用戶9999';
SELECT * FROM app_user WHERE name = '用戶9999';

mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用戶9999'\G
*************************** 1. row ***************************
          id: 1
select_type: SIMPLE
       table: app_user
  partitions: NULL
        type: ALL
possible_keys: NULL
        key: NULL
    key_len: NULL
        ref: NULL
        rows: 992759
    filtered: 10.00
      Extra: Using where
1 row in set, 1 warning (0.00 sec)

創建索引

CREATE INDEX idx_app_user_name ON app_user(name);

測驗普通索引

mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用戶9999'\G
*************************** 1. row ***************************
          id: 1
select_type: SIMPLE
       table: app_user
  partitions: NULL
        type: ref
possible_keys: idx_app_user_name
        key: idx_app_user_name
    key_len: 203
        ref: const
        rows: 1
    filtered: 100.00
      Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = '用戶9999';
1 row in set (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = '用戶9999';
1 row in set (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = '用戶9999';
1 row in set (0.00 sec)

索引準則

• 索引不是越多越好
• 不要對經常變動的資料加索引
• 小資料量的表建議不要加索引
• 索引一般應加在查找條件的欄位

索引的資料結構

-- 我們可以在創建上述索引的時候，為其指定索引型別，分兩類
hash型別的索引：查詢單條快，范圍查詢慢
btree型別的索引：b+樹，層數越多，資料量指數級增長（我們就用它，因為innodb默認支持它）

-- 不同的存盤引擎支持的索引型別也不一樣
InnoDB 支持事務，支持行級別鎖定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事務，支持表級別鎖定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事務，支持表級別鎖定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事務，支持行級別鎖定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事務，支持表級別鎖定，不支持 B-tree、Hash、Full-text 等索引；

MySQL07：權限及如何設計資料庫

用戶管理

使用SQLyog 創建用戶，并授予權限演示

基本命令

/* 用戶和權限管理 */ ------------------
用戶資訊表：mysql.user

-- 重繪權限
FLUSH PRIVILEGES

-- 增加用戶 CREATE USER kuangshen IDENTIFIED BY '123456'
CREATE USER 用戶名 IDENTIFIED BY [PASSWORD] 密碼(字串)
  - 必須擁有mysql資料庫的全域CREATE USER權限，或擁有INSERT權限，
  - 只能創建用戶，不能賦予權限，
  - 用戶名，注意引號：如 'user_name'@'192.168.1.1'
  - 密碼也需引號，純數字密碼也要加引號
  - 要在純文本中指定密碼，需忽略PASSWORD關鍵詞，要把密碼指定為由PASSWORD()函式回傳的混編值，需包含關鍵字PASSWORD

-- 重命名用戶 RENAME USER kuangshen TO kuangshen2
RENAME USER old_user TO new_user

-- 設定密碼
SET PASSWORD = PASSWORD('密碼')    -- 為當前用戶設定密碼
SET PASSWORD FOR 用戶名 = PASSWORD('密碼')    -- 為指定用戶設定密碼

-- 洗掉用戶 DROP USER kuangshen2
DROP USER 用戶名

-- 分配權限/添加用戶
GRANT 權限串列 ON 表名 TO 用戶名 [IDENTIFIED BY [PASSWORD] 'password']
  - all privileges 表示所有權限
  - *.* 表示所有庫的所有表
  - 庫名.表名 表示某庫下面的某表

-- 查看權限   SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR 用戶名
   -- 查看當前用戶權限
  SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR CURRENT_USER();

-- 撤消權限
REVOKE 權限串列 ON 表名 FROM 用戶名
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用戶名    -- 撤銷所有權限

權限解釋

-- 權限串列
ALL [PRIVILEGES]    -- 設定除GRANT OPTION之外的所有簡單權限
ALTER    -- 允許使用ALTER TABLE
ALTER ROUTINE    -- 更改或取消已存盤的子程式
CREATE    -- 允許使用CREATE TABLE
CREATE ROUTINE    -- 創建已存盤的子程式
CREATE TEMPORARY TABLES        -- 允許使用CREATE TEMPORARY TABLE
CREATE USER        -- 允許使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES，
CREATE VIEW        -- 允許使用CREATE VIEW
DELETE    -- 允許使用DELETE
DROP    -- 允許使用DROP TABLE
EXECUTE        -- 允許用戶運行已存盤的子程式
FILE    -- 允許使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX     -- 允許使用CREATE INDEX和DROP INDEX
INSERT    -- 允許使用INSERT
LOCK TABLES        -- 允許對您擁有SELECT權限的表使用LOCK TABLES
PROCESS     -- 允許使用SHOW FULL PROCESSLIST
REFERENCES    -- 未被實施
RELOAD    -- 允許使用FLUSH
REPLICATION CLIENT    -- 允許用戶詢問從屬服務器或主服務器的地址
REPLICATION SLAVE    -- 用于復制型從屬服務器（從主服務器中讀取二進制日志事件）
SELECT    -- 允許使用SELECT
SHOW DATABASES    -- 顯示所有資料庫
SHOW VIEW    -- 允許使用SHOW CREATE VIEW
SHUTDOWN    -- 允許使用mysqladmin shutdown
SUPER    -- 允許使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL陳述句，mysqladmin debug命令；允許您連接（一次），即使已達到max_connections，
UPDATE    -- 允許使用UPDATE
USAGE    -- “無權限”的同義詞
GRANT OPTION    -- 允許授予權限


/* 表維護 */

-- 分析和存盤表的關鍵字分布
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE 表名 ...
-- 檢查一個或多個表是否有錯誤
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
-- 整理資料檔案的碎片
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

MySQL備份

資料庫備份必要性

• 保證重要資料不丟失
• 資料轉移

MySQL資料庫備份方法

• mysqldump備份工具
• 資料庫管理工具,如SQLyog
• 直接拷貝資料庫檔案和相關組態檔

mysqldump客戶端

作用 :

• 轉儲資料庫
• 搜集資料庫進行備份
• 將資料轉移到另一個SQL服務器,不一定是MySQL服務器

-- 匯出
1. 匯出一張表 -- mysqldump -uroot -p123456 school student >D:/a.sql
　　mysqldump -u用戶名 -p密碼 庫名 表名 > 檔案名(D:/a.sql)
2. 匯出多張表 -- mysqldump -uroot -p123456 school student result >D:/a.sql
　　mysqldump -u用戶名 -p密碼 庫名 表1 表2 表3 > 檔案名(D:/a.sql)
3. 匯出所有表 -- mysqldump -uroot -p123456 school >D:/a.sql
　　mysqldump -u用戶名 -p密碼 庫名 > 檔案名(D:/a.sql)
4. 匯出一個庫 -- mysqldump -uroot -p123456 -B school >D:/a.sql
　　mysqldump -u用戶名 -p密碼 -B 庫名 > 檔案名(D:/a.sql)

可以-w攜帶備份條件

-- 匯入
1. 在登錄mysql的情況下：-- source D:/a.sql
　　source 備份檔案
2. 在不登錄的情況下
　　mysql -u用戶名 -p密碼 庫名 < 備份檔案

規范化資料庫設計

為什么需要資料庫設計

當資料庫比較復雜時我們需要設計資料庫

糟糕的資料庫設計 :

• 資料冗余,存盤空間浪費
• 資料更新和插入的例外
• 程式性能差

良好的資料庫設計 :

• 節省資料的存盤空間
• 能夠保證資料的完整性
• 方便進行資料庫應用系統的開發

軟體專案開發周期中資料庫設計 :

• 需求分析階段: 分析客戶的業務和資料處理需求
• 概要設計階段:設計資料庫的E-R模型圖 , 確認需求資訊的正確和完整.

設計資料庫步驟

• 收集資訊

• • 與該系統有關人員進行交流 , 座談 , 充分了解用戶需求 , 理解資料庫需要完成的任務.

• 標識物體[Entity]

• • 標識資料庫要管理的關鍵物件或物體,物體一般是名詞

• 標識每個物體需要存盤的詳細資訊[Attribute]

• 標識物體之間的關系[Relationship]

三大范式

問題 : 為什么需要資料規范化?

不合規范的表設計會導致的問題：

• 資訊重復

• 更新例外

• 插入例外

• • 無法正確表示資訊

• 洗掉例外

• • 丟失有效資訊

三大范式

第一范式 (1st NF)

第一范式的目標是確保每列的原子性,如果每列都是不可再分的最小資料單元,則滿足第一范式

第二范式(2nd NF)

第二范式（2NF）是在第一范式（1NF）的基礎上建立起來的，即滿足第二范式（2NF）必須先滿足第一范式（1NF），

第二范式要求每個表只描述一件事情

第三范式(3rd NF)

如果一個關系滿足第二范式,并且除了主鍵以外的其他列都不傳遞依賴于主鍵列,則滿足第三范式.

第三范式需要確保資料表中的每一列資料都和主鍵直接相關，而不能間接相關，

規范化和性能的關系

為滿足某種商業目標 , 資料庫性能比規范化資料庫更重要

在資料規范化的同時 , 要綜合考慮資料庫的性能

通過在給定的表中添加額外的欄位,以大量減少需要從中搜索資訊所需的時間

e [, tbl_name] … [option] …
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
– 整理資料檔案的碎片
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] …

### MySQL備份

資料庫備份必要性

- 保證重要資料不丟失
- 資料轉移

MySQL資料庫備份方法

- mysqldump備份工具
- 資料庫管理工具,如SQLyog
- 直接拷貝資料庫檔案和相關組態檔

**mysqldump客戶端**

作用 :

- 轉儲資料庫
- 搜集資料庫進行備份
- 將資料轉移到另一個SQL服務器,不一定是MySQL服務器

[外鏈圖片轉存中...(img-kwwnyH4I-1604630594823)]

– 匯出

1. 匯出一張表 – mysqldump -uroot -p123456 school student >D:/a.sql
   　　mysqldump -u用戶名 -p密碼 庫名 表名 > 檔案名(D:/a.sql)
2. 匯出多張表 – mysqldump -uroot -p123456 school student result >D:/a.sql
   　　mysqldump -u用戶名 -p密碼 庫名 表1 表2 表3 > 檔案名(D:/a.sql)
3. 匯出所有表 – mysqldump -uroot -p123456 school >D:/a.sql
   　　mysqldump -u用戶名 -p密碼 庫名 > 檔案名(D:/a.sql)
4. 匯出一個庫 – mysqldump -uroot -p123456 -B school >D:/a.sql
   　　mysqldump -u用戶名 -p密碼 -B 庫名 > 檔案名(D:/a.sql)

可以-w攜帶備份條件

– 匯入

1. 在登錄mysql的情況下：-- source D:/a.sql
   　　source 備份檔案
2. 在不登錄的情況下
   　　mysql -u用戶名 -p密碼 庫名 < 備份檔案

### 規范化資料庫設計

### 為什么需要資料庫設計

**當資料庫比較復雜時我們需要設計資料庫**

**糟糕的資料庫設計 :** 

- 資料冗余,存盤空間浪費
- 資料更新和插入的例外
- 程式性能差

**良好的資料庫設計 :** 

- 節省資料的存盤空間
- 能夠保證資料的完整性
- 方便進行資料庫應用系統的開發

 **軟體專案開發周期中資料庫設計 :**

- 需求分析階段: 分析客戶的業務和資料處理需求
- 概要設計階段:設計資料庫的E-R模型圖 , 確認需求資訊的正確和完整.

**設計資料庫步驟**

- 收集資訊

- - 與該系統有關人員進行交流 , 座談 , 充分了解用戶需求 , 理解資料庫需要完成的任務.

- 標識物體[Entity]

- 

- - 標識資料庫要管理的關鍵物件或物體,物體一般是名詞

- 標識每個物體需要存盤的詳細資訊[Attribute]

- 標識物體之間的關系[Relationship]



### 三大范式

**問題 : 為什么需要資料規范化?**

不合規范的表設計會導致的問題：

- 資訊重復

- 更新例外

- 插入例外

- - 無法正確表示資訊

- 洗掉例外

- - 丟失有效資訊

> 三大范式

**第一范式 (1st NF)**

第一范式的目標是確保每列的原子性,如果每列都是不可再分的最小資料單元,則滿足第一范式

**第二范式(2nd NF)**

第二范式（2NF）是在第一范式（1NF）的基礎上建立起來的，即滿足第二范式（2NF）必須先滿足第一范式（1NF），

第二范式要求每個表只描述一件事情

**第三范式(3rd NF)**

如果一個關系滿足第二范式,并且除了主鍵以外的其他列都不傳遞依賴于主鍵列,則滿足第三范式.

第三范式需要確保資料表中的每一列資料都和主鍵直接相關，而不能間接相關，



**規范化和性能的關系**

為滿足某種商業目標 , 資料庫性能比規范化資料庫更重要

在資料規范化的同時 , 要綜合考慮資料庫的性能

通過在給定的表中添加額外的欄位,以大量減少需要從中搜索資訊所需的時間

通過在給定的表中插入計算列,以方便查詢