Made By Herolh

目錄

[TOC]

一、MySQL是什麼：

服務端 客戶端

Mysql資料庫

Mysql是最流行的關係型資料庫管理系統，在 WEB 應用方面MySQL是最好的RDBMS(Relational Database Management System：關係資料庫管理系統)應用軟體之一。 由瑞典MySQL AB公司開發，目前屬於Oracle公司。

什麼是資料庫

資料庫（Database）是按照數據結構來組織、存儲和管理數據的倉庫 我們也可以將數據存儲在文件中，但是在文件中讀寫數據速度相對較慢。 所以，現在我們使用關係型資料庫管理系統(RDBMS)來存儲和管理的大數據量。所謂的關係型資料庫，是建立在關係模型基礎上的資料庫，藉助於集合代數等數學概念和方法來處理資料庫中的數據。 每個資料庫都有一個或多個不同的API用於創建，訪問，管理，搜索和複製所保存的數據。

關係型資料庫的特點

RDBMS即關係資料庫管理系統(Relational Database Management System)的特點：

• 數據以表格的形式出現
• 每行為各種記錄名稱
• 每列為記錄名稱所對應的數據域
• 許多的行和列組成一張表單
• 若幹的表單組成database

-- 關聯資料庫將數據保存在不同的表中，而不是將所有數據放在一個大倉庫內，這樣就增加了速度並提高了靈活性。

MySQL 資料庫的優勢

• Mysql是開源的，所以你不需要支付額外的費用。
• Mysql支持大型的資料庫。可以處理擁有上千萬條記錄的大型資料庫。
• MySQL使用標準的SQL數據語言形式。
• Mysql可以允許於多個系統上，並且對多種語言有很好支持。這些編程語言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和 Tcl 等。
• MySQL支持大型資料庫，支持5000萬條記錄的數據倉庫，32位系統表文件最大可支持4GB，64位系統支持最大的表文件為8TB。
• Mysql是可以定製的，採用了 GPL 協議，你可以修改源碼來開發自己的 Mysql 系統。

技能

安裝

• 源碼安裝/exe
• 初始化
  • 啟動服務端
  • 客戶端連接
    • 發送指令
  • 環境變數

連接：

mysql -u root [-h 主機] -p

# 查看mysql的進程(LINUX下)
ps -ef |grep mysql 	

資料庫操作

資料庫級別操作：

status：	查看預設設置
desc 表名;  查看表結構

數據表操作：

• 數據類型
  • 約束
    • 唯一索引
    • 主鍵約束
    • 外鍵
      • 一對一
      • 一對多
      • 多對多
  • 自增

數據行操作：

• 增刪改查
  • 排序
  • 分組
  • 條件
  • 臨時表
  • 聯表
  • 通配符
  • 分頁
  • 組合

視圖

觸發器

函數

存儲過程

• 游標
• 事務邏輯：

pymysql

• 連接 conect()
  • 操作（游標）
    • 增刪改 ：commit
    • 查 ：fetchone，fetchall,fatchany
  • 存儲過程調用方式
    • callproc("名",參數)
    • select @_存儲過程名稱_0
  • SQL註入
    • 關閉游標
    • 關閉連接

二、MySQL操作

創建用戶

create user ‘username’@'pasword' *identified by* 'password'	

語法代碼

use mysql;
grant create,delete,drop,update,insert,select   # 或者直接寫grant all
	on TUTORIALS.*                  # 所有資料庫.所有表
	to 'username'@'localhost'  
	# 用戶名@ 從哪台機子登陸  '%' 代表任意主機登陸
	IDENTIFIED by 'password'        # 登陸密碼
	;

授權

grant all privileges *on 庫名.表名 to*  'username'@'%' 	

取消授權

revoke al privileges on 庫名.表名 *from* 'username'@'%'

查看用戶許可權

show grants for 用戶名;

字元編碼

charset = GBK;
create 庫名 default charset = utf8;

查看字元集：

• 查看查看MySQL資料庫伺服器和資料庫MySQL字元集。

  show variables like '%char%'; 
  

  # 輸出結果
  +--------------------------+----------------------------+
  | 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/ |
  +--------------------------+----------------------------+
  

• 查看MySQL數據表（table）的MySQL字元集。

  show table status from sqlstudy_db like '%countries%'; 
  

  # 輸出結果
  +---------+--------+--------+------------+------+-----------------+ 
  | Name    | Engine | Version| Row_format | Rows | Collation       | 
  +---------+--------+--------+------------+------+-----------------+
  |countries| InnoDB |     10 | Compact    |   11 | utf8_general_ci | 
  +---------+--------+--------+------------+------+-----------------+
  

• 查看MySQL數據列（column）的MySQL字元集。

  show full columns from countries;  
  

  +----------------------+-------------+-----------------+
  | Field                | Type        | Collation       | 
  +----------------------+-------------+-----------------+ 
  | countries_id         | int(11)     | NULL            | 
  | countries_name       | varchar(64) | utf8_general_ci | 
  | countries_iso_code_2 | char(2)     | utf8_general_ci |
  | countries_iso_code_3 | char(3)     | utf8_general_ci |  
  | address_format_id    | int(11)     | NULL            | 
  +----------------------+-------------+-----------------+
  

修改字元集

暫時修改

修改全局字元集

/*建立連接使用的編碼*/
set character_set_connection=utf8;
/*資料庫的編碼*/
set character_set_database=utf8;
/*結果集的編碼*/
set character_set_results=utf8;
/*資料庫伺服器的編碼*/
set character_set_server=utf8;

修改庫的字元集

alter database 庫名 default character set 字元集;

修改表的字元集

alter table 表名 convert to character set 字元集;

修改欄位的字元集

alter table 表名 modify 欄位名 欄位屬性 character set gbk；

永久修改

• 修改 mysql 配置文件

  sudo vi /etc/my.cnf
  

• 在 [mysqld] 上方添加以下設置：

  default-character-set=utf8
  

• 在[mysqld]下方添加以下設置：

  character-set-server=utf8
  collation-server=utf8_general_ci
  

原子性操作

engine = inodb

資料庫操作

查看資料庫

show databases;

創建資料庫

create database 庫名;         # 這麼寫預設不能處理中文
create database 庫名 charset utf8;	# 解決中文字元處理問題：

刪除資料庫

drop database 庫名;

進入資料庫

use 資料庫名;

查看所有表

show tables;

查看表結構

desc 表名;
-- 或
show colums from 表名;

增刪改查

創建數據表

CREATE TABLE table_name (column_name column_type);

插入數據

INSERT INTO table_name ( field1, field2,...fieldN )
VALUES ( value1, value2,...valueN );

查詢數據

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[OFFSET M ][LIMIT N]

UPDATE 更新數據

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

DELETE 語句

DELETE FROM table_name [WHERE Clause]

數據類型

int decimal datatime char，text

性別 enum（ ‘','’ ） username char
set()

String類型：

Number類型

Date 類型

其他數據類型

約束

• 唯一約束 unique
• 主鍵約束 primary key
• 外鍵約束 constraint 外鍵名 foreign key（ 列名 ） references 外表（ 列名 ）

自動遞增

*auto_increment

通配符

• %

• _

算數、邏輯、比較運算符

排序( order by )

• asc
• desc

分組( group by )

• 多表連接

• 左右聯表 : join

• 上下聯表 : union

  • 自動去重：

    select * from 表1		（假設十條）
    union
    select * from 表2		（假設十條）
    (返回二十條)
    

  • 不去重

    select * from 表1		（假設十條）
    union
    select * from 表1		
    (返回二十條)
    

分頁(limit)

select * from 表名 limit 0,10;

（ 翻的頁越大越慢 ）---> 加速方法：只對索引進行掃描

加速方式一：索引加速

# 覆蓋索引，但是快不了多少
select * from userinfo3 where uid in ( select uid from limit 20000,10)

最佳方法加速方式：

記錄當前頁最大值最小值ID：

# 當前頁後十條
select * from userinfo3 where uid > 20000 limit 10;					
# 當前頁前十條
select * from userinfo3 where uid < 20000 order by id desc limit 10;

分頁方式：

頁面只有上一頁和下一頁：

# 上一頁：
select * from userinfo3 where uid > max_id limit 10;
# 下一頁：
select * from userinfo3 where uid < min_id order by id desc limit 10;

上一頁 192 193 [196] 197 198 199 下一頁

# 上一頁：
select * from userinfo3 where id in (
select id from (select id from userinfo3 where id > max_id limit 30) as N order by N.id desc limit 10
)

三、MySQL 高級編程

視圖

創建視圖( 臨時表的反覆使用 )

create views 視圖名稱 as SQL語句( select ....)				# 從真實表動態獲取數據

使用視圖時，將其當作表進行操作即可，由於視圖是虛擬表，所以無法使用其對真實表進行創建、更新和刪除操作，僅能做查詢用。

修改視圖：

alter views 視圖名稱 as SQL語句( select ....)

刪除視圖：

 drop views 視圖名稱

觸發器( 資料庫級別操作 )

當對某張表做增刪改操作時，可以使用觸發器自定義關聯行為

創建觸發器

create trigger 觸發器名稱 before/after (SQL行為語句) ON 表、庫

eg:

create trigger tri_before_insert before insert ON tb1 [For EACH ROW 每插入一行就會觸發 ]
begin
	...;
end

create trigger tri_before_insert after insert ON tb1 For EACH ROW
begin
	...;
end

註意:

# 查詢不會觸發觸發器

函數( 性能低 )

執行函數：

select 函數名();

內置函數：

聚合函數

select CURDATE();			# 2018-05-15				年-月-日
select CURRENT_TIMESTAMP();	#  2018-05-15 20:53:36		年-月-日 時:分:秒
select CHAR_LENGTH(str);	# 字元串str長度
select CONCAT(str1,str2)	# 字元串拼接

自定義函數：

delimiter //
create function f1(
	i1 int,		--傳入參數
	i2 int 
)
return int
begin
	-- 不能寫 select * from 表名 等SQL行為
	declare num int default 0;	# 定義臨時變數
	set num = i1 + i2;
	return(num);
END//
delimiter ;

存儲過程( 更為重要 )

mysql 前幾年還沒有這個 保存在 MySQL 上的一個別名 => 一坨SQL語句

1.創建存儲過程

無參存儲過程

delimiter //
create procedure p1()
begin
	INSERT INTO userinfo(username,password) VALUES("user5","pd5");
	SELECT *  FROM userinfo;
end//
delimiter ;

有參數存儲過程：( 關鍵字：in\out\inout )

delimiter //
create procedure p2(
	in n1 int,
	in n2 int
)
begin
	select * from userinfo where id > n1;
end//
delimiter ;

• 存儲過程沒有返回值的概念，out 是傳進一個變數，然後你可以在存儲過程中改變該變數的值

• out 不往裡面傳值，也就是雖然在外面 @var=1，實際上 p3 里使用不了 @var 的值

  delimiter //
  create procedure p3(
  	in n1 int,
  	out n2 int
  )
  begin
  	set n2 = 123123;
  	select * from userinfo where id > n1;
  end//
  delimiter ;
  
  set @var = 1;			# 定義一個變數@var = 0
  call p3(8,@var );
  select @var				# @var = 123123
  

• inout：即可往存儲過程裡面傳值，也可以往外傳值

！註意：

• 為什麼要有結果集，又要有out偽造的的返回值？

  out用於設置一個值，標識存儲過程的執行結果

2.調用存儲過程：

call p1();

存儲過程的優缺點

好處：

網路傳的數據就少了

壞處：

存儲過程如果改了，程式就GG了

以後工作的方式：

方式一：存儲過程

MySQL: 存儲過程 程式：調用存儲過程

方式二：SQL語句

MySQL：。。 程式：SQL語句

方式三：ORM 框架

MySQL：。。 程式：類和對象（SQL語句）

事務（ 性能不高 ）

事務邏輯：

delimiter //
create procedure p4(
	out status int
)
BEGIN
	# 聲明如果出現異常則執行
	{
		set status = 1;
		rollback;
	}

	#開始事務
	SQL;
	commit;

	# 結束
	set status = 2;
END //
delimiter ;

語法代碼

delimiter \\
create PROCEDURE p5(
	OUT p_return_code tinyint
)
BEGIN 
	DECLARE exit handler for sqlexception 		--聲明如果出現異常則執行
	BEGIN 
		-- ERROR 
		set p_return_code = 1; 
		rollback; 
	END; 

	START TRANSACTION; 							--開始事務
		DELETE from tb1;
		insert into tb2(name)values('seven');
	COMMIT; 

	-- SUCCESS 
	set p_return_code = 2; 
END\\
delimiter ;

游標( 性能不高 )

創建游標

declare my_cursor CURSOR FOR [SQL語句]select * from A；

使用游標

fetch my_cursor into row_namename,row_psd;

一個迴圈的例子：

delimiter \\
create procedure p6()
BEGIN
	DECLARE row_name varchar(20);		-- 自定義一個變數1
	DECLARE row_psd varchar(50);		-- 自定義一個變數2
	declare done int default False;		-- 自定義迴圈結束標誌

	declare my_cursor CURSOR FOR select username,password from userinfo;
	declare CONTINUE HANDLER FOR NOT FOUND set done = TRUE;				
	-- 當游標為空時改變迴圈結束標誌

	open my_cursor;						-- 打開游標
		SIGN:LOOP						-- 開始迴圈標誌
			fetch my_cursor into row_namename,row_psd;	-- 獲取游標內容
				if done then 				
					leave SIGN;				-- breake
				end if;
				insert into userinfo2( row_namename,row_psd) values( row_name,row_psd);
		end loop SIGN;					-- 關閉迴圈標誌
	close my_cursor;					-- 關閉游標
END\\
delimiter ;

動態執行SQL語句：( 防SQL註入 )

偽代碼：

delimiter \\
create procedure p7(
	in str varchar(255),			# 放要執行的SQL語句
	in arg int
)
begin 
	1. 預檢測某個東西 SQL語句合法性
	2. SQL =格式化 tpl + arg 
	3. 執行SQL語句
end

語法代碼

delimiter \\
CREATE PROCEDURE p7(
	in nid int
)
BEGIN
	set @nid = nid;				-- 因為 execute 只能使用@ 所以要給他賦值
	PREPARE prod FROM 'select * from student where sid > ?';		
	-- 預檢測SQL語句
	EXECUTE prod USING @nid;										
	-- 拼接
	DEALLOCATE prepare prod; 
END\\
delimiter ;

索引( 加速查找 )

對於頻繁查找的列要創建索引

作用：

約束

加速查找

慢速查找：

select * from tb where 列名="...”

快速查找：

select * from tb where id = 65

無索引：

從前往後依次查詢

有索引：

• 創建額外文件( 某種格式存儲)，保存特殊的數據結構
• 查詢快，插入更新刪除慢
• 命中索引( 版本和版本，資料庫和資料庫也有不同標準 )

索引類別

按作用分類

• 主鍵索引：加速查找 + 不能為空 + 不能重覆
• 普通索引：加速查找
• 唯一索引：加速查找 + 不能重覆
• 聯合索引（多列）：
  • 聯合主鍵索引
  • 聯合唯一索引
  • 聯合普通索引

特殊

以下並非真實索引

• 覆蓋索引：

  --在索引文件中直接獲取數據
  select id from userinfo3 where email = ".....";
  

• 索引合併：

  --把多個單列索引合併使用
  select id from userinfo3 where email = "....." and pd = ”...“;	
  

按實現方法

hash索引

• 單值快
• 範圍慢

btree索引（ 預設下 ）

• 按二叉樹查
• 快

索引的創建

普通索引：

create index 索引名 on 表名( 列名 );

唯一索引：

create unique index 索引名 on 表名( 列名 );

聯合索引：

create unique index 索引名 on 表名( 列名1，列名2 );

最左首碼匹配：

select * from 表名 where 列名1 = xxx and 列名2 = xxx;		-- 走索引
select * from 表名 where 列名2 = xxx;					  -- 不走索引
-- 聯合索引的效率大於索引合併

短索引：

create index 索引名 on 表名( 列名(16) )  -- 對列名1的16個位元組之後的數據建立索引

註意: 在 Mysql 里 TEXT 類型要想建立索引必須建立短索引，否則報錯

ORM 框架操作 ( 關係對象映射 )

如 SQLAlchmy

• 當一類函數公用同樣參數時候，可以轉變成類進行 - 分類

• 面向對象： 數據和邏輯（屬性和行為）組合在一起 函數編程： 數據和邏輯分離

• 模板“約束”

  提取共性 一類事物共同具有：屬性和行為

作用

• 提供簡單的規
• 自動轉換成SQL語句

ORM框架類別：

• DB first:

  graph LR A(手動創建資料庫以及表) --> B(ORM框架 ) B --> C(自動生成類)

• code first：

  SQLAlchmy 屬於該類

  graph LR A(手動創建類和資料庫) -->B(ORM框架) B -->C(以及表)

註意事項

不用 like

不用like,用 like 永遠也命中不了索引

避免使用函數

避免使用 or

特別的：當or條件中有未建立索引的列才失效，以下會走索引

select * from tb1 where nid = 1 or name = 'seven';
select * from tb1 where nid = 1 or name = '[email protected]' and email = 'alex'

類型要一致，類型不一致也會使用類型轉化函數

普通索引不走以下語法

!=：

select * from tb1 where email != '...'
-- 特別的：如果是主鍵，則還是會走索引
select * from tb1 where nid != 123

>：

select * from tb1 where email > 'alex'
-- 特別的：如果是主鍵或索引是整數類型，則還是會走索引
select * from tb1 where nid > 123
select * from tb1 where num > 123	

order by

select name from tb1 order by email desc;( 前後不一致不走索引 )
-- 當根據索引排序時候，選擇的映射如果不是索引，則不走索引
-- 特別的：如果對主鍵排序，則還是走索引：
select * from tb1 order by nid desc;

四、MySQL 註意事項

• 避免使用select *

• count(1)或count(列) 代替 count(*)

• 創建表時儘量時 char 代替 varchar

• 表的欄位順序固定長度的欄位優先

• 組合索引代替多個單列索引（經常使用多個條件查詢時）

• 儘量使用短索引

• 使用連接（JOIN）來代替子查詢(Sub-Queries)

  -- MYSQL里已經沒有區別了
  

• 連表時註意條件類型需一致

• 索引散列值（重覆少）不適合建索引，例：性別不適合

• 因為mysql預設;為語句結束符，所以當執行複合語句內部[begin...end]時會出錯,解決如下：

  delimiter //					修改語句結束符為 //
  create trigger tri_before_insert after insert ON tb1 For EACH ROW
  begin
  ...;
  end//
  delimiter ;						還原，避免其他語句被干擾
  補充：
  -- NEW,代指新數據（添加的時候有）
  insert into tb() values( NEW.user );
  -- OLD,代指老數據( 刪除的時候有 ) 
  

補充：

• NEW,代指新數據（添加的時候有）

  insert into tb() values( NEW.user );
  

• OLD,代指老數據( 刪除的時候有 )

五、DBA工作

慢日誌

• 執行時間 > 10
• 未命中索引
• 日誌文件路徑

配置：

方式一：記憶體

show variables like '%query%'
set global 變數名 = 值
set global slow_query_log = ON;			--開啟慢日誌，預設關閉

方式二：指定配置文件

mysqld --defaults-file='E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\my-default.ini'

my.conf內容：

slow_query_log = ON
slow_query_log_file = D:/....

方式三：Mysql的配置文件里修改