Made By Herolh

目錄 {#index}

• 目錄 {#index}
• 一、MySQL是什么：
  • Mysql資料庫
    • 什么是資料庫
    • 關系型資料庫的特點
    • MySQL 資料庫的優勢
  • 技能
    • 安裝
  • 連接：
  • 資料庫操作
    • 資料庫級別操作：
    • 資料表操作：
    • 資料行操作：
    • 視圖
    • 觸發器
    • 函式
    • 存盤程序
    • pymysql
• 二、MySQL操作
  • 創建用戶
    • 語法代碼
    • 授權
    • 取消授權
    • 查看用戶權限
  • 字符編碼
    • 查看字符集：
    • 修改字符集
      • 暫時修改
        • 修改全域字符集
        • 修改庫的字符集
        • 修改表的字符集
        • 修改欄位的字符集
      • 永久修改
  • 原子性操作
  • 資料庫操作
    • 查看資料庫
    • 創建資料庫
    • 洗掉資料庫
    • 進入資料庫
    • 查看所有表
    • 查看表結構
  • 增刪改查
    • 創建資料表
    • 插入資料
    • 查詢資料
    • UPDATE 更新資料
    • DELETE 陳述句
  • 資料型別
    • String型別：
    • Number型別
    • Date 型別
    • 其他資料型別
  • 約束
  • 自動遞增
  • 通配符
  • 算數、邏輯、比較運算子
  • 排序( order by )
  • 分組( group by )
  • 分頁(limit)
    • 加速方式一：索引加速
    • 最佳方法加速方式：
    • 分頁方式：
      • 頁面只有上一頁和下一頁：
      • 上一頁 192 193 [196] 197 198 199 下一頁
• 三、MySQL 高級編程
  • 視圖
    • 創建視圖( 臨時表的反復使用 )
    • 修改視圖：
  • 觸發器( 資料庫級別操作 )
    • 創建觸發器
    • 注意:
  • 函式( 性能低 )
    • 執行函式：
    • 內置函式：
      • 聚合函式
      • 自定義函式：
  • 存盤程序( 更為重要 )
    • 1.創建存盤程序
      • 無參存盤程序
      • 有引數存盤程序：( 關鍵字：in\out\inout )
      • ！注意：
    • 2.呼叫存盤程序：
    • 存盤程序的優缺點
      • 好處：
      • 壞處：
      • 以后作業的方式：
        • 方式一：存盤程序
        • 方式二：SQL陳述句
        • 方式三：ORM 框架
  • 事務（ 性能不高 ）
    • 事務邏輯：
  • 游標( 性能不高 )
    • 創建游標
    • 使用游標
    • 一個回圈的例子：
  • 動態執行SQL陳述句：( 防SQL注入 )
    • 偽代碼：
    • 語法代碼
  • 索引( 加速查找 )
    • 作用：
      • 約束
      • 加速查找
        • 慢速查找：
      • 無索引：
      • 有索引：
    • 索引類別
      • 按作用分類
        • 特殊
      • 按實作方法
        • hash索引
        • btree索引（ 默認下 ）
    • 索引的創建
      • 普通索引：
      • 唯一索引：
      • 聯合索引：
        • 最左前綴匹配：
      • 短索引：
  • ORM 框架操作 ( 關系物件映射 )
    • 作用
    • ORM框架類別：
    • 注意事項
      • 不用 like
      • 避免使用函式
      • 避免使用 or
      • 型別要一致，型別不一致也會使用型別轉化函式
      • 普通索引不走以下語法
        • !=：
        • >：
        • order by
• 四、MySQL 注意事項
• 五、DBA作業
  • 慢日志
    • 配置：
      • 方式一：記憶體
      • 方式二：指定組態檔
      • 方式三：Mysql的組態檔里修改

一、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的組態檔里修改

標籤：MySQL

上一篇：MySQL Online DDL與DML并發阻塞關系總結

下一篇：MySQL執行SQL腳本問題 ：錯誤代碼2006、1153