MySQL資料庫
一、基本使用
cls 清屏(windows)
ctrl + l 清屏（linux）
1、開啟或關閉mysql服務
（1）windows：超級管理員權限下才能使用
? 啟動mysql：net start mysql
? 停止mysql：net stop mysql
（2）linux：直接啟動即可
? 啟動mysql：service mysql start
? 停止mysql：service mysql stop
? 重啟mysql：service mysql restart
2、基本權限操作
登錄操作
? musql -u用戶名 -p密碼 -h地址ip
? （1）登錄到本地mysql，默認用戶root是最高權限賬戶
? mysql -u用戶名 -p密碼 -h地址ip
? （2）退出mysql
? \q 或 exit
? （3）遠程連接mysql服務器
? mysql -uroot -p -h192.168.134.131
用戶操作
? （1）查詢當前登錄的用戶
? select user();
? （2）設定密碼
? set password = password("1324")
? （3）去除密碼
? set password = password("")
權限操作
? ipconfig：windows查詢IP命令
? ifconfig：linux查詢命令
? （1）windows連接遠程linux中的mysql
? create user "用戶名"@"192.168.134.131(網路地址)" identified by "1234(設定的登陸密碼)";
? （2）給具體某個網段下的所有ip設定賬戶 % 代表任意的資料0~255
? create user "用戶名"@"192.168.134.131(網路地址)" identified by "1234(設定的登錄密碼)"
? （3）所有的IP都可登錄
? create user "用戶名"@"%" identified by "1234(設定的登陸密碼)"
"""USAGE 沒有任何權限"""
mysql> show grants for "ceshi12"@"%";
+-------------------------------------+
| Grants for ceshi12@% |
+-------------------------------------+
| GRANT USAGE ON *.* TO 'ceshi12'@'%' |
+-------------------------------------+
1 row in set (0.00 sec)
? （4）grant 權限 on 資料庫.表名 to "用戶名"%"ip地址" identified by "密碼";
"""
# 主要權限
select 查詢資料庫的權限
insert 插入資料庫的權限
update 更新資料庫的權限
delete 洗掉資料庫的權限
* 代表所有
"""
? 查詢權限：grant select on *.* to "用戶名"@"%" identified by "密碼";
? 所有權限：grant all privileges on *.* to "用戶名"@"%" identified by "密碼";
? 查看所有資料庫：show databases;
? 移除權限：revoke all on *.* from "用戶名"@"%";
? 洗掉賬戶：drop user "用戶名"@"%";
? 重繪權限，立刻生效：flush privileges
3、資料庫詳細操作
（1）操作 [ 資料庫 ] 檔案夾操作
? 增：create
? 創建資料庫：create database 資料庫名 charset utf-8;
? 刪：drop
? 洗掉資料庫：drop database 資料庫名;
? 改：alter
? alert database 資料庫 charset gbk;
? 查：show
? 查看所有的資料庫：show databases;
? 查看資料庫的建表陳述句：show create database 資料庫名;
（2）操作 [ 資料庫 ] 檔案操作
? 選擇資料庫
? use 資料庫名
? 增：create
? 增加表：create table 表名(欄位名1 欄位型別2, 欄位名2 欄位型別2);
? 刪：drop
? 洗掉表：drop table 表名
? 改：alter
? modify只改資料型別：alter table 表名 modify name char(5);
? change連欄位和資料型別一起改：alter table 表名 change 欄位名 要更改的欄位名 欄位型別;（可以只更改一項）
? add添加欄位：alter table 表 add 要添加的欄位 欄位型別;（可以使用after在固定的某個欄位后面添加一個欄位：alter table 表 add 要添加的欄位 欄位型別 after 欄位）
? add column添加欄位：add column列（欄位，關鍵字）：alter table 表 add column age;（添加欄位可以不用寫column）
? drop column洗掉欄位：drop column列（欄位，關鍵字）：alter table 表 drop column age;
? rename 更改表名：alter table 表名 rename 新表名;
? 查：show、desc
? 查詢所有資料表：show tables;
? 查看建表的陳述句：show create table 表名 # ( 加上\G 垂直顯示 ,默認橫向顯示)
? 查看表結構：desc 表名；
（3）操作記錄（檔案李里面的資料）
? 注：mysql null 相當于 python None 是一樣的代表為空
? 增：insert
? 一次插入一條資料：insert into 表名(欄位1, 欄位2, ...) value(值1, 值2, ...)
? 一次插入多條資料：insert into 表名(欄位1, 欄位2, ...) values(值1, 值2, ...), (值1, 值2, ...), ...
? 注：value和values是一樣的，區別是插入一條資料value的執行效率快于values的執行效率；
? 當插入多條資料是時，values的執行效率快于value的執行效率
? 不指定具體欄位時，默認把所有欄位查詢一遍：insert into 表 values(值1, 值2, ...)可以使用null占位
? 可以具體指定某個欄位進行插入：insert into 表(欄位) value(值)
? 刪：delete
? 洗掉id為2的某條資料：delete from 表 where id = 2
? 洗掉表中所有資料：delete from 表
? 改：update
? 更改符合條件的資料：update 表名 set 欄位=值 where 條件
? 更改所有資料：update 表名 set 欄位=值
? 查：select
? * 代表所有：select * from 表;
? 把要搜索的欄位寫在select后面：select id, name from 表；
二、資料庫中常用的資料型別
1、整型
? tinyint：1個位元組 有符號（-128~127）無符號（unsigned） 小整型值
? int：4個位元組 有符號（-21億~21億）無符號（0~42億） 大整型值
1.1、布爾型別
? MySQL中的布爾型別是通過tinyint來實作的，tinyint(0) 是布爾型別的False，tinyint(1) 是布爾型別的True，
2、浮點型
? float：（255，30）單精度，float存在四舍五入，小數位默認保留5位
? double：（255，30）雙精度，double存在四舍五入，小數位默認保留16位
? decimal：（65，30）金錢型別，使用字串的形式保存小數，decimal也存在四舍五入，默認保留整數
"""存在四舍五入"""
create table t2(f1 float(5,2) , f2 double(5,2) , f3 decimal(5,2));
insert into t2 values(1.666666666666666666666666666,1.666666666666666666666666666,1.666666666666666666666666666);
"""float 小數位默認保留5位,double小數位默認保留16位,decimal 默認保留整數位,存在四舍五入"""
create table t3(f1 float , f2 double , f3 decimal);
insert into t3 values(1.666666666666666666666666666,1.666666666666666666666666666,1.666666666666666666666666666);
# float(5,2) 5=>代表總長度(整數+小數) 2=> 小數長度,保留2位
create table t6(f1 float(5,2));
insert into t4 values(12.34567);
insert into t5 values(1234.34567);y
3、字串
注：varchar（）開辟空間默認預留2個位元組空間：像varchar（5）存盤的中文實際開辟空間是5+中文（3）+2個位元組的空間
? char：固定開辟空間長度，但開辟空間速度快；犧牲空間換取時間；像手機號、身份證號等，字符長度255個
? varchar：不固定開辟空間長度，可根據存入值開辟對應長度空間，但開辟空間速度慢；
? 犧牲時間，換取空間；例如：小廣告或評論等；字符長度21845個
? text：文本型別，針對于很長的字串，像小說，文章等
4、concat拼接
? 查詢當前用戶：select user()
? concat：把所有的引數拼接在一起
? select concat(引數1, 引數2, ...)
5、列舉和集合
? enum 列舉：從列出來的資料當中選一個（例如：性別）
? set集合：從列出來的資料當中選多個（自動去重）
create table 表(
id int ,
name char(10),
money float(6,2) ,
sex enum("man","woman"),
hobby set("beat_doudou","smoke","drink","tang_head")
)
# 正常寫法
insert into 表(id,name,money,sex,hobby) values(1,"張三",9.66666,"woman","smoke,tang_head");
# 自動去重
insert into 表(id,name,money,sex,hobby) values(1,"張三",9.66666,"woman","beat_doudou,beat_doudou,beat_doudou,beat_doudou");
6、時間資料型別
? date：YYY—MM—DD 年月日 （結婚紀念日，節假日）
? time：HH：MM：SS 時分秒（體育競賽）
? year：YYYY 年份值（歷史，1882年的某某飲品）
? datetime：YYYY—MM—DD HH—MM—SS 年月日 時分秒（登錄時間，下單時間），輸入null就寫入null
? timestemp：YYYYMMDDHHMMSS（時間戳）自動更新時間（不需要手動寫入，修改資料的時候自動更新，記錄最后一次修改的時間），輸入null時寫入當前時間
? mysql內置函式：now 獲取當前時間 select now();
create table t5(d date , t time , y year , dt datetime);
insert into t5 values("2020-06-17","09:15:30","2020","2020-06-17 09:15:30");
insert into t5 values(now(),now(),now(),now());
?
三、約束
? 對編輯的資料進行型別限制，不滿足約束條件的直接報錯
? unsigned：無符號
? not null：不為空
? default：設定默認值
? unique：唯一約束，資料唯一不能重復
? 索引：相當于字典的目錄，通過索引可以加快查詢速度
? UNI 表示唯一索引，允許null空值；
? primary key：主鍵，標記資料的唯一特征（唯一且不為空的資料）
? 注：主鍵非空且唯一，在一個表里面，只能有一個欄位是主鍵
? auto_increment ：自增加一（一般配合主鍵使用或者unique進行自增）
? delete：單純的洗掉資料，資料id號從上一個繼續自增
? truncate：洗掉所有資料，id從開頭開始（充值表）
? zerofill：0 填充（配合int型別使用），int（6），位數不夠，拿0來湊，少了自動擴充
? foreign key：外鍵，把多張表通過一個關聯欄位，聯合在一起
# unsigned
create table t7(id int unsigned);
insert into t7 values(66);
insert into t7 values(-66); error
# not null
create table t8(id int not null , name varchar(255));
insert into t8 values(1,"xll");
insert into t8 values(null,"xll"); error
insert into t8(name) values("xll"); error
# default
create table t9(id int not null , name varchar(255) default "pdd");
insert into t9 values(1,null);
insert into t9(id) values(1);
# unique
create table t10(id int unique , name char(10) default "pdd");
insert into t10(id) values(1);
insert into t10(id) values(1); # error 不能重復
insert into t10(id) values(12);
insert into t10(id) values(null);
insert into t10(id) values(null);
# primary key 主鍵,標記資料的唯一特征(唯一且不為空的資料) [創建表: 欄位 型別 約束 ...]
"""PRI 主鍵 非空且唯一, 在一個表里面,只能有一個欄位是主鍵"""
create table t11(id int not null unique , name char(10) default "pdd");
insert into t11 values(1,"你好");
insert into t11 values(null,"你好啊"); error
# primary key 創建主鍵
create table t12(id int primary key , name char(10) default "pdd");
insert into t12 values(1,"aaa");
# 兩者同時存在 (優先顯示primary key 作為主鍵, 另一個被標記成UNI 唯一索引)
create table t13(id int primary key , name char(10) not null unique);
# 一個表里面只能有一個主鍵
create table t13(id int primary key , name char(10) primary key);
# auto_increment 自增加一(一般配合主鍵使用 或者 unique進行自增)
create table t14(id int primary key auto_increment , name char(10) default "pdd");
insert into t14 values(1,"張三");
insert into t14 values(2,"張三");
insert into t14 values(null,"張三");
insert into t14(id) values(null);
# 使用默認值進行插入
insert into t14 values();
# delete 單純的洗掉資料,資料id號從上一個繼續自增
delete from t14;
# truncate 洗掉所有資料, id從頭開始 (重置表)
truncate table t14
# zerofill 0填充(配合int型別使用) , int(6) , 位數不夠位,拿0來補充
create table t15(id int(6) zerofill );
insert into t15 values(2);
insert into t15 values(2222);
insert into t15 values(2222222222);
四、聯合唯一約束
? unique(欄位1，欄位2，欄位3，...) 把多個欄位拼在一起表達唯一的資料
? MUL：代表普通索引；UNI：代表唯一索引；PRI：主鍵索引
? （1）聯合唯一索引（都為非空的欄位desc顯示的PRI，聯合在一起做的住鍵，不是單個欄位的主鍵）
create table t1_server(id int, server_name char(10) not null , ip char(15) not null , port int not null , unique(ip,port));
insert into t1_server values(1,"aaa","192.168.56.31",5000);
insert into t1_server values(1,"aaa","192.168.56.31",6000);
insert into t1_server values(1,"aaa","192.168.56.40",6000);
? （2）聯合唯一索引（為空的欄位，允許插入null，顯示MUL）
create table t2_server(id int, server_name char(10) not null , ip char(15) , port int , unique(ip,port));
insert into t2_server values(1,"aaa","192.168.56.31",5000);
insert into t2_server values(1,"aaa","192.168.56.31",6000);
insert into t2_server values(1,"aaa","192.168.56.40",6000);
insert into t2_server values(1,"aaa",null,null); # 注意點,可以插入多個空值;
? （3）聯合唯一索引和主鍵同時存在
? primary key 是真正的主鍵，聯合唯一索引就會變成MUL普通索引；
? unique(id,port) 聯合唯一索引
? primary key(id,port) 聯合唯一主鍵
? 二者使用的方式是一樣的，區別是：前者可以繼續添加一個主鍵，而后者不能繼續添加主鍵；
? 主鍵只能是單個欄位，或者聯合主鍵，如果再加就會報錯；
? （4）foreign key：外鍵，把多張表通過一個關聯欄位，聯合在一起
? 外鍵的要求：關聯的欄位必須具有唯一屬性（unique 或者 primary key）
? 為了避免出現過多的欄位，可以采用分表的形式，來提升效率，減少資料的冗余
? （5）聯級操作、聯級更新（謹慎操作）
? 聯級洗掉：on delete cascade
? 聯級更新：on update cascade
student1
id name age address classid
1 wangzhen 80 北京市天安門閣樓里 1
2 xiaolin 90 東北老革命工業基地 1
3 wangwen 18 內蒙古呼和浩特蒙古包 2
class1:
id classname datetime
1 python30 2020-01-01 09:09:09
2 python31 2020-02-01 09:09:09
# 創建class1表
create table class1(id int , classname varchar(255));
# 被關聯的欄位至少需要具有唯一屬性
alter table class1 add unique(id);
# 創建student1學生表
create table student1(
id int primary key auto_increment,
name varchar(255) not null,
age int not null,
classid int,
foreign key(classid) references class1(id)
);
insert into class1 values(1,"python30");
insert into class1 values(2,"python31");
insert into student1 values(null,"wz",80,2);
insert into student1 values(null,"xl", 90,1);
insert into student1 values(null,"ww", 18,2);
# 洗掉class1 如果這條資料在其他表里存在,直接刪會報錯,因為外鍵的關聯限制
delete from class1 where id = 1;
# 先把關聯的資料都刪了之后,才可真正刪掉這條資料
delete from student1 where id = 2;
delete from class1 where id = 1;
# 聯級洗掉 聯級更新 (謹慎操作)
"""
聯級洗掉 on delete cascade
聯級更新 on update cascade
"""
# 創建class2
create table class2(id int unique , classname varchar(255));
# 創建student2
create table student2(
id int primary key auto_increment,
name varchar(255) not null,
age int not null,
classid int,
foreign key(classid) references class2(id) on delete cascade on update cascade);
insert into class2 values(1,"python30");
insert into class2 values(2,"python31");
insert into student2 values(null,"hen",80,2);
insert into student2 values(null,"lin", 90,1);
insert into student2 values(null,"angn", 18,2);
# 聯級洗掉
delete from class2 where id = 2
# 聯級更新
update class2 set id =100 where classname="python30"
五、表與表之間的關系
（1）一對一：表1中的主鍵(唯一索引)關聯表二中的主鍵(唯一索引)欄位形成一對一的關系，
（2）一對多或多對一：一個班級里可以對應多個學生，把學生作為主動關聯的表，設定一個外鍵，去存盤班級表的關聯欄位中的資料
（3）多對多：一個學生可以對應多個學科，一個學科也可以被多個學生學習
xueke (表1)
id name
1 math
2 english
3 wuli
student (表2)
id name
1 wangwen
2 weiyilin
3 wangyingqian
# 表達多對多關系時,需要第三張關系表
relation (表3) 把xid 和 sid 設定成外鍵 關聯xueke的id 和 student的id
xid sid
1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 3
六、存盤引擎
存盤資料的結構方式
查看存盤引擎命令：show engines；
概念：
? 表級鎖：如果有人修改了當前主人個表，會直接上表鎖，其它人無法修改，在編輯資料的時候，速度慢，不能高并發（MyISAM特有）
? 行級鎖：如果有人修改了當前這個表中的一條記錄，當前這個資料記錄會上鎖，其它資料仍然可以正常修改，速度快，允許更高的并發（InnoDB）
事務處理
? 支持事務處理：如果執行sql陳述句，在全部成功之后，再選擇提交資料，有一條失敗，立刻回滾，恢復成原來的狀態，
? begin：開始事務
? commit：提交資料
? rollback：回滾資料
InnoDB：5.6版本后的默認存盤引擎，支持事務處理，行級鎖，外鍵
MyISAM：5.6版本前的默認存盤引擎，支持表級鎖
MEMORY：把資料放在記憶體中，用做快取
BLACKHOLE：黑洞，用來同步主從資料庫中的資料，場景發生在服務器并發集群，用在主從資料庫當中[主資料庫：增刪改操作，從資料庫：查詢操作]
E:\Lenovo wenjian\MySQL\mysql-5.7.25-winx64\ceshi
create table myisam1(id int,name char(10)) engine = myisam;
myisam1.frm 表結構
myisam1.MYD 表資料
myisam1.MYI 表索引
create table innodb1(id int,name char(10)) engine = innodb;
innodb1.frm 表結構
innodb1.ibd 表資料+表索引
create table memory1(id int,name char(10)) engine = memory;
memory1.frm 表結構
# 沒有表資料檔案,因為把資料存放在記憶體中了.
create table blackhole1(id int,name char(10)) engine = blackhole;
blackhole1.frm 表結構
七、單表查詢
select . . . from . . . where . . . group by . . . having . . . order by . . . limit . . .
1、while 條件的使用：
? 對表中資料的篩選和過濾
? （1）判斷的符號：
? = > >= < <= != <>不等于
? （2）拼接條件關鍵字：
? and or not
? （3）查詢區間范圍值：between
? between 小值 and 大值 [小值，大值] 查詢兩者之間這個范圍內所有資料
? （4）查詢具體某個值的范圍：in
? in(1,2,3) 指定范圍
? （5）模糊查詢 like "%" "_" 通配符：
? like "%a"：匹配以a結尾的任意長度的字串
? like "a%"：匹配以a開頭的任意長度的字串
? like "%a%"：匹配含有字母a的任意長度的字串
? like "_a"：個數一共兩個，以a結尾的字串
? like "a_"：個數一共兩個，以a開頭的字串
? （6）distinct 去重
? distinct 欄位
? distinct(欄位)
（1）單條件的查詢：
（2）多條件的查詢
（3）關鍵字between... and...
（4）null 關鍵字：進行判定的時候使用is進行判定，不要用=判定
（5）關鍵字 in 在 ... 之重 查詢
（6）模糊查詢 like "%" "_" 通配符
（7）concat(拼接；可使用as 起別名)
（8）concat_ws(拼接的符號，引數1，引數2，...)
（9）內部可使用四則運算子（+ - * /）
2、group by 子句 分組分類
group by 欄位 對資料進行分類，by后面接什么欄位，select就搜索什么欄位
group_concat 按照分類形式進行欄位的拼接
聚合函式
count 統計總數 * 所有
max 統計最大值
min 統計最小值
avg 統計平均值
sun 統計總合
一般境況下：分組 + 聚合函式 配合使用
3、having 過濾
having 資料在分類分組之后，進行二次資料過濾，一般是配合group by 使用，分組之后再過濾
4、order by 排序
按照欄位進行排序
order by asc 默認升序：從小到大
order by desc 降序：從大到小
5、limit 限制查詢條數（資料分頁）
limit m, n ：m代表從第幾條資料開始查，n代表查詢幾條，m=0資料庫表中的第一條資料
兩個引數，引數1默認從0開始算，0是第一條，引數2是查詢條數
select * from 表 limit 0,5
只查一條資料，默認從第一條
select * from 表 limit 1
找資料庫當中最后一條資料
select * from 表 order by 欄位 desc limit 1
找資料庫當中最后三條資料
select * from 表 order by 欄位 desc limit 3 desc
6、可以使用正則運算式查詢資料（不推薦使用，效率不高）
select * from 表 where 欄位 regexp ".*on$" # 以什么結尾，沒有？，問號不能使用
select * from 表 where 欄位 regexp "^程" 以什么開頭
select * from 表 where 欄位 regexp "^程.*金"
八、多表查詢
1、內連接（行內查詢）：兩個表或者多表滿足條件的所有資料查詢出來（兩表之間共有的資料）
（1）兩表查詢：
select 欄位 from 表1 inner join 表2 on 必要的關聯條件
（2）多表查詢：
select 欄位 from 表1 inner join 表2 on 必要條件1 inner join 表3 on 必要條件3 ...
（3）as 起別名
（4）where 默認實作的就是行內查詢
2、外連接
(1) union: 去掉重復元素
(2) union all 保留重復元素
（1）左連接（左聯查詢 left join）以左表為主，右表為輔，完整查詢左表所有資料，右表沒有的資料補null
select * from employee left join department on employee.dep_id = department.id;
（2）右鏈接（右聯查詢 right join）以右表為主，左表為輔，完整查詢右表所有資料，左表沒有的資料補null
select * from employee right join department on employee.dep_id = department.id;
（3）全連接（全連接 union）所有資料都合并起來，空缺的補null
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id;
select * from employee left join department on employee.dep_id = department.id
union all
select * from employee right join department on employee.dep_id = department.id;
九、子查詢
? 子查詢可以單獨作為一個臨時資料，臨時的表，臨時的欄位，一般用在from where select 字句后面，可以通過查詢出來的臨時資料和另外的表聯合，變成一張更大的表，再做單表查詢查到想要的資料
子查詢：嵌套查詢
? （1）sql陳述句當中又嵌套了另外一條sql陳述句，用（）包起來，表達一個整體
? （2）一般應用在from子句后面表達一張表，where子句后面表達一個條件
? （3）查詢速度從快到慢：單表查詢 -> 聯表查詢 -> 子查詢
（1）普通where寫法
select
d.id,d.name
from
employee as e,department as d
where
e.dep_id = d.id
group by
d.id,d.name
having
avg(e.age) > 25;
（2）inner join
select
d.id,d.name
from
employee as e inner join department as d on e.dep_id = d.id
group by
d.id,d.name
having
avg(e.age) > 25;
（3）子查詢
1.先選出平均年齡大于25歲的部門id
select dep_id from employee group by dep_id having avg(age) > 25;
2.通過部門id,找部門的名字
select name from department where id in (201,202);
3.綜合拼接
select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25);
（4）
1.找技術部門對應id
select id from department where name = "技術";
2.通過id找員工姓名
select name from employee where dep_id = 200;
3.綜合拼接
select name,dep_id from employee where dep_id = (select id from department where name = "技術");
（5）聯表寫法(把null的資料露出來 , 員工的部門dep_id 為null,代表這個部門沒人.)
select
d.id,d.name
from
department as d left join employee as e on d.id = e.dep_id
where
e.dep_id is null
（6）子查詢
1.先查詢,員工在哪些部門 (所有員工的分類分別是 200 201 202 204)
select dep_id from employee group by dep_id
2.把不在部門的資料找出來
select id from department where id not in (200,201,202,204);
3.綜合拼接
select id,name from department where id not in (select dep_id from employee group by dep_id)
十、帶EXISTS關鍵字查詢
exists 關鍵字，表達存在
如果內層sql能夠查詢到資料，回傳True，外層sql執行查詢陳述句
如果內層sql不能查到資料，回傳False，外層sql不執行查詢陳述句
select * from employee where exists (select * from employee where id = 1) # 能
select * from employee where exists (select * from employee where id = 100) # 不能
補充：約束的添加何洗掉
1、添加/洗掉 約束 not null
alter table 表名 modify 欄位名 型別
alert table 表名 modify 欄位 型別 not null
alert table 表名 modify 欄位 型別
2、添加/洗掉 unique 唯一索引
alter table 表名 add unique(欄位)
alter table 表名 drop index 欄位
3、添加/洗掉 primary key
alter table 表名 add primary key(欄位)
alter table 表名 drop primary key
4、添加/洗掉 foreign key 外鍵(先通過desc表找到外鍵名字，然后再刪)
alter table 表 drop foreign key 欄位
alert table 外鍵表 add foreign key(外鍵欄位) reference 關聯表(關聯欄位)
十一、py中操作資料庫
? 在python中操作資料庫需要使用到pymysql模塊，這種形式的資料庫操作主要用沒有現成的框架可以操作資料庫的時候，對資料庫進行手動操作的模塊
import pymysql
# 基本語法
# (1) 創建連接 host user password database 這四個引數必須寫，還有port埠引數，charset編碼引數等等
conn = pymysql.connect(host='127.0.0.1', user="root", password='1234', database='ceshi', port="3306", charset="utf8")
# 注：port一般默認3306埠
# (2) 創建游標物件，該物件可以進行增刪改查操作
cursor = conn.cursor()
# (3) 執行sql陳述句
sql = "select * from t1"
# 回傳的是資料的總條數
res = cursor.execute(sql)
print(res)
# (4) 獲取資料
res = cursor.fetchone() # res的回傳值依具體情況而定
print(res)
# (5) 釋放游標物件
cursor.close()
# (6) 關閉連接
conn.close()
事務處理
? python中通過pymysql模塊操作事務處理，必須通過commit提交資料，才會真正的更新資料，否則rollback回滾，恢復到以前狀態
conn = pymysql.connect(host="127.0.0.1",user="root",password="",database="db0618")
cursor = conn.cursor()
sql1 = "begin"
sql2 = "select * from employee" # 只有查詢得到了回傳值，其它都是無狀態0
sql3 = "update employee set emp_name = '111egon' where id = 1 "
sql4 = "commit"
res1 = cursor.execute(sql1)
res2 = cursor.execute(sql2)
res3 = cursor.execute(sql3)
# res4 = cursor.execute(sql4)
# print(res1,res2,res3,res4)
# print(cursor.fetchone())
cursor.close()
conn.close()
SQL注入問題
? sql的注入問題是因為的執行漏洞，在python中通過使用mysql中的注釋符間接跨過資料庫表的查詢條件，獲得用戶的訪問權限，
# 不安全寫法：就是直接通過格式化字串和format替換去操作資料
import pymysql
user = input("user>>>: ").strip()
pwd = input("password>>>: ").strip()
conn = pymysql.connect(host="127.0.0.1",user="root",password="",database="db0619")
cursor = conn.cursor()
sql = "select * from usr_pwd where username='%s' and password='%s' " % (user,pwd)
print(sql)
res = cursor.execute(sql)
print(res) # 查詢資料的個數
if res:
print("登陸成功")
else:
print("登錄失敗")
cursor.close()
conn.close()
'''
輸入類似: 字串' or 10 = 10 -- 字串
原理是：select * from usr_pwd where username='%s' and password='%s'
輸入：123' or 10 = 10 -- 456 字串執行陳述句變成
select * from usr_pwd where username='123' or 10 = 10 -- 456 and password='%s'
但是mysql中 -- 是注釋，所以陳述句變成了：
select * from usr_pwd where username='123' or 10 = 10
又因為條件 or 一真即真的特點，陳述句會正常執行完畢，因此獲得操作權限
'''
# 安全寫法，也就是解決辦法，在pymysql中已經封裝好了，可以表面絕大多數的sql注入問題
# execute 引數1是一個sql陳述句,如果sql陳述句和里面的引數值分開執行,默認開啟預處理
# execute(sql , (引數1,引數2,引數3))
import pymysql
user = input("user>>>: ").strip()
pwd = input("password>>>: ").strip()
conn = pymysql.connect(host="127.0.0.1",user="root",password="",database="db0619")
cursor = conn.cursor()
sql = "select * from usr_pwd where username=%s and password = %s"
res = cursor.execute(sql, (user,pwd))
print("登錄成功" if res else "登錄失敗")
cursor.close()
conn.close()
python操作mysql增刪改查
? python操作pymysql的時候，默認是開啟事物的，必須在增刪改查之后，統一提交資料，才會對資料庫產生影響，否則默認回滾，回到沒有修改前的狀態，
（1）pymysql中封裝好了提交資料的方法：conn.commit(注：conn是創建資料庫連接，名字不固定，不一定是conn)
（2）pymysql中封裝好了回滾資料的方法：conn.rollback(注：同上一個一樣)
（3）查詢資料,默認是元組,可以設定回傳字典型別：cursor=conn.cursor(cursor = pymysql.cursors.DictCursor)
（4）單條資料操作（針對資料庫陳述句的操作）：游標物件.execute()
（5）多條資料操作（針對資料庫陳述句的操作）：游標物件.executemany()
? 注：插入后的回傳值是第一條資料的id，插入多條陳述句時，默認也是回傳插入的第一條資料的id，但要注意不能使用：insert into 表 value（null，"值1","值2"）這種方式，這種方式默認回傳值是最后一條資料的id
（6）單條資料獲取（獲取資料庫的查詢結果）：游標物件.fetchone()
（7）多條資料獲取（獲取資料庫的查詢結果）：游標物件.fetchmany()
? 注：默認fetchmany()是只獲取一條資料，加引數是獲取多條資料；
? 無論是fetchone()還是fetchmany()，都是默認從上一條資料繼續向下搜索(性質類似于迭代器)
（8）獲取所有資料fetchall()基于上一條資料往下搜索(性質類似于迭代器)；默認查詢所有符合條件的資料
（9）資料滾動：
-
相對滾動：基于相對位置的資料進行滾動
使用方法：游標物件.scroll(滾動條數,mode="relative")
滾動條數為負數時是向后滾動資料，正數是向前滾動資料
當以表中的第一條資料為基礎（相對位置）時向后滾動報錯
向前滾動資料時超出資料范圍（表資料到最后一條了）也會報錯
-
絕對滾動：永遠基于第一條資料的位置滾動
當以表中的第一條資料為基礎（絕對位置）時向后滾動報錯，智能向前滾動資料
向前滾動資料時超出資料范圍（表資料到最后一條了）也會報錯
# 1.創建mysql連接
conn = pymysql.connect(host="127.0.0.1",user="root",password="",database="db0619")
# 查詢資料,默認是元組,可以設定回傳字典型別, pymysql.cursors.DictCursor
cursor = conn.cursor(cursor = pymysql.cursors.DictCursor)
# 增
sql = "insert into t1(first_name , last_name ,age ,sex,money) values(%s,%s,%s,%s,%s)"
# 一次插入一條資料
res = cursor.execute(sql,("王","振",80,0,12000))
print(res)
# 一次插入多條資料
res = cursor.executemany(sql,[("尉","翼麟",18,1,12000),("謝","晨",80,0,200),("主","勝",3,0,9.9)])
print(res)
# 獲取最后插入這條資料的id號(針對于單條資料執行,獲取最后的id,如果多條資料的執行,以第一條資料的id為主)
print(cursor.lastrowid)
# 針對于多條資料最后的id,可以通過倒序查詢,找到id號
# select id from t1 order by id desc limit 1
# 刪
sql = "delete from t1 where id = %s"
res = cursor.execute(sql,(8,))
print(res)
if res:
print("洗掉成功")
else:
print("洗掉失敗")
# 改
sql = "update t1 set first_name = %s where id = %s"
res = cursor.execute(sql,('王',10))
print(res)
if res:
print("更新成功")
else:
print("更新失敗")
# 查
sql = "select * from t1"
res = cursor.execute(sql)
print(res) # 總條數
# (1) 獲取一條資料 fetchone
res = cursor.fetchone()
print(res) # {'id': 9, 'first_name': '王', 'last_name': '翼麟', 'age': 18, 'sex': 1, 'money': 12000.0}
# (2) 獲取多條資料 fetchmany 默認從上一條資料繼續向下搜索(性質類似于迭代器)
data = cursor.fetchmany() # 沒有引數,默認只獲取一條
data = cursor.fetchmany(3)
print(data)
for row in data:
first_name = row["first_name"]
last_name = row["last_name"]
age = row["age"]
if row["sex"] == 0:
sex = "女性"
else:
sex = "男性"
money = row["money"]
print("姓:{},名:{},年齡:{},性別:{},收入:{}".format(first_name,last_name,age,sex,money))
# (3) 獲取所有資料 fetchall 基于上一條資料往下搜索(性質類似于迭代器)
data = cursor.fetchall()
print(data)
# (4) 自定義搜索查詢的位置
sql = "select * from t1 where id >=10"
res = cursor.execute(sql)
res = cursor.fetchone()
print(res)
# 1.相對滾動 (向后)
cursor.scroll(3,mode="relative")
res = cursor.fetchone()
print(res)
# 方向向前 (無論前后,不能超出范圍)
cursor.scroll(-3,mode="relative")
res = cursor.fetchone()
print(res)
# 2.絕對滾動 , 永遠基于第一條資料的位置滾動
cursor.scroll(0,mode="absolute")
print(cursor.fetchone())
cursor.scroll(1,mode="absolute")
print(cursor.fetchone())
cursor.scroll(2,mode="absolute")
print(cursor.fetchone())
# 往前滾沒資料,超出范圍error
cursor.scroll(-1,mode="absolute")
print(cursor.fetchone())
# 在進行增刪改的時候,必須通過commit提交資料,才會對資料庫進行更新,否則默認回滾.
conn.commit()
cursor.close()
conn.close()
十二、SQL陳述句優化
1、SQL陳述句優化
1、mysql的執行流程
客戶端：
? 發送連接請求，然后發送增刪改查sql陳述句執行操作
服務端：
? （1）連接層：提供和客戶端連接的服務，在tcp協議下，提供多執行緒的技術，讓多個用戶登錄到mysql中
? 可使用命令：show processlist; 查看所有登錄到mysql的用戶行程
? （2）服務器：提供各種介面（增刪改查等操作）分析器組件會決議用戶的sql陳述句；
? 如果發現sql陳述句執行效率較低，會提交給優化器組件進行優化，然后執行
? （查詢快取：把上次搜索過的資料，或者提前存盤的資料直接回傳，效率加快）
? （優化器：mysql query optimizer）
? （3）存盤引擎：存盤或者提取資料
? innoDB：支持事務處理，支持行級鎖，支持高并發
? myisam：支持表級鎖，不支持高并發
? （4）日志檔案：產生binlog日志（二進制檔案）
創建表的時候可以在陳述句最后面設定引擎和編碼集，還可以設定起始的id
create table ceshi_table1(
id int primary key auto_increment,
name varchar(255)
)engine = myisam auto_increment=3 charset=utf8;
2、sql卡頓原因
? 硬碟讀寫資料，io延遲高，sql陳述句性能低，導致sql執行的時間漫長；表中的護具沒有索引，并且資料量大，也會造成sql陳述句查詢速度慢
撰寫：select ... from ... join on ... where ... group by ... having ... order by ... limit
決議：from ... join on ... where ... group by ... having ... select ... order by ... limit
3、索引
索引（index）概念：
? 是一個樹狀的資料結構，即（B樹結構，分支節點>2）
? 相當于字典的目錄，功效是加快查詢速度
? 常用樹：B樹（banlance-tree），二叉樹，紅黑樹，hash樹
樹節點的概念：
? 根節點（最頂級的節點）
? 分支節點（兩種狀態，父節點，子節點）
? 葉子節點（最后一層存盤資料的節點）
? 樹的高度（樹的層級）
B樹【b-tree】理想狀態下三級，任何資料最多三次查到，支持百萬級別的資料查詢，追求樹的矮胖結構
B+樹【b+tree】：在相鄰的葉子節點上，加入雙向鏈表（指標），當前葉子節點不但保存了資料，還保存了上下兩個節點的地址【小范圍資料中，加快查詢資料】
B*樹【b*或b++ tree】在相鄰的分支節點（包含葉子節點）上，加入雙向鏈表（指標），當前分支節點（包含葉子節點）不但保存了資料，還保存了上下兩個節點的地址【大范圍資料中，加快查詢資料】
注：磁盤塊 block 資料頁 16k；myisam和innodb都是b+樹結構
4、innodb和myisam的索引結構
（1）聚集索引【innodb存盤的特點，myisam不支持】：
? 如果有主鍵，自動以主鍵創建聚集索引的資料結構（樹狀結構）；
? 如果沒有主鍵，選擇唯一鍵；
? 都沒有，自動生成隱藏的聚集索引，也會分出一個欄位占用6個位元組長整型；
? 葉子節點上面直接存盤真實資料（索引和資料捆綁在一起）
? 分支節點存盤的是索引的最小值，用來劃分范圍
? 在資料量變大的時候，盡量在樹層級高度不變的情況下橫向發展；好處：查詢次數少，提升效率，減少io阻塞
（2）非聚集索引（輔助索引，二級索引，普通索引）
? 先對創建索引的該欄位劃磁區間進行排序，把索引值分布在葉子節點上；
? 存盤的是該欄位的值以及對應映射的主鍵id（primary key），沒有真實資料
? 通過主鍵id，再去從其它檔案中找資料
（3）兩者區別
? myisam和innodb使用的索引結構都是b+樹，但是葉子節點存盤的資料不同
? innodb檔案結構中只有frm，ibd直接把資料存在葉子節點上
? myisam檔案結構中有frm，myi，myd，葉子節點上存盤的所引值，通過索引找到id，通過id查找資料
（4）性能優化：
? 利用索引查詢時，可以增快查詢速度，但是增刪改速度變慢，會改變樹狀結構
? 追求盡量讓葉子節點存盤的資料型別小一點，讓高度變矮，讓資料頁變少
2、索引
1、常用索引
單個欄位索引
? 主鍵索引：primary key （非空且唯一）
? 唯一索引：unique（唯一）
? 普通索引：index （單純的加個索引，為了提升查詢效率）
聯合索引
? primary key（欄位1，欄位2，...）：聯合主鍵索引
? unique（欄位1，欄位2，...）：聯合唯一索引
? index（欄位1，欄位2，...）：聯合普通索引
2、應用場景
編號:int
姓名:varchar(255)
身份證號:char(18)
電話char(11)
地址varchar(255)
備注:text
姓: varchar(10)
名: varchar(10)
編號: 主鍵
姓名: 普通索引(注意在區分度高的欄位上加)
身份證:unique
電話:unique
備注:全文索引 , 借助第三方軟體sphinx來運行
姓和名:聯合索引 , 聯合在一起查,加快速度
3、不同的存盤引擎支持的資料結構
innodb：支持b-tree fulltext 不支持hash型別索引結構
myisam：支持b-tree fulltext 不支持hash型別索引結構
memory：支持b-tree hash型別 不支持fulltext索引
hash型別索引：資料放記憶體中，通過見來獲取到值，單條資料查詢快，一個范圍內的資料慢
b-tree：最理想的三層結構，理論上可支撐百萬條資料的查詢；
4、建立索引
（1）建表的時候，直接船家女索引index索引名（索引欄位）
create table t1()
id int primary key,
name char(10),
index index_name(name)
);
（2）創建表之后，創建索引 create index 索引名on 表名（索引欄位）
create table t2(
id int primary key,
name char(10)
);
create index index_name on t2(name);
（3）改變欄位索引 alter table 表名 add index 索引名（索引欄位）
create table t3(
id int primary key,
name char(10)
);
alter table t3 add index index_name(name);
（4）洗掉索引
drop index index_name on t3;
5、正確使用索引
加索引和不加索引的速度差別巨大，加了索引之后，ibd檔案變大
（1）把頻繁作為搜索條件的欄位作為索引，查詢單條資料，如果查詢的是一個大范圍中的資料，不能命中索引
? 表達范圍的符號：> < >= <= != <> like between and in
select * from s1 where id > 5;
select * from s1 where id < 5; # 表達一個小范圍內的資料可以命中.
（2）選一個區分度較高的作為索引
選區分度低的欄位作為索引，在查詢資料的時候，先走索引建好的樹狀結構，再把資料搜出來
因為樹狀結構中有大量的重復資料，會增加樹的高度，反而速度不快，冗余資料過多
默認系統會把主鍵或者unique標識的約束，自動創建索引，因為區分度較高，沒有冗余資料
create index index_name on s1(name); # 不推薦把區分度不高的欄位加索引
（3）在搜索條件中，不能讓索引欄位參與計算，不能命中索引
select * from s1 where id = 1000;
select * from s1 where id*3 = 3000; # id = 1000
（4）當條件中含有and，sql陳述句會通過優化器進行優化
- 如果有and相連，找到第一個有索引的并且樹的高度最矮的欄位進行優化
select count(*) from s1 where email = "xboyww1000@oldboy"
select count(*) from s1 where email = "xboyww1000@oldboy" and id = 1000;
select count(*) from s1 where email = "xboyww1000@oldboy" and name = "xboyww";
select count(*) from s1 where email = "xboyww1000@oldboy" and name = "xboyww" and id = 1000;
多個and條件依然成立
- 如果有or相連，沒有優化，所有陳述句從左到右執行，讓索引失去意義
select count(*) from s1 where id = 1000 or email = "xboyww1000@oldboy";
（5）聯合索引：遵循最左原則index（欄位1，欄位2，...）
select count(*) from s1 where first_name = "王6" and last_name="文6" # 命中索引
select count(*) from s1 where last_name="文6" and first_name = "王6" # 命中索引
select count(*) from s1 where last_name="文6" # 不能命中索引
select count(*) from s1 where first_name="王6" and gender="man";
select count(*) from s1 where first_name="王6" and gender="man" and name="xboyww";
# 最左前綴原則:被標記成MUL這個欄位,必須存在在搜索條件中,就命中索引
first_name + .... (必須該欄位存在) 聯合索引會更加精確的命中想要的資料.資料結構更合理;
（6）其它
-
資料型別不匹配，不能命中索引
select count(*) from s1 where first_name = 100; # first_name 是字串 -
使用了函式不能命中索引
select count(*) from s1 where reverse(first_name) = "6王"; # 字串反轉函式reverse()
十三、索引樹高度
1、表的資料量
資料量越大，樹的高度就會變高，理論上三層索引樹的告訴最為理想，可以支持百萬級別的資料量
解決：可以使用分表（橫切，豎切），分庫，增加快取，解決資料量大，查詢慢的問題
2、索引鍵值過長
該索引欄位存盤資料太大，每個葉子節點最大存盤16K，超過這個范圍會新增加葉子節點和分支節點
解決：前綴索引（截取前5個長度）
3、資料型別
char（定長） varchar（變長）從開辟空間速度來看，char快
從資料結構上來看，varchar更合理
（1）避免使用select * ，不確定表大小的時候，使用count（*）查一下資料
（2）盡量使用資料型別較小的欄位做索引
（3）重復值少的，區分度高的欄位索引，性別這樣的欄位不要做索引
（4）在多表查詢時使用join，盡量少的使用子查詢
十四、執行計劃分析
desc/explain
執行計化：在一條sql執行之前，制定執行的方案
desc select * from s1；
1、select_type
? simple：代表的是簡單查詢（單表查詢，不包括子查詢，union）
? primary：sql嵌套中的主查尋（最外層）
? subquery：sql嵌套中的子查詢（最里面）
? derived：衍生查詢（把子查詢結果作為一張臨時表）
2、table
? 在多表或者子查詢的時候，通過table分析出出問題的表是誰
3、type
? 顯示執行計劃的型別，優先級從低到高如下，優化時，至少達到range或者ref級別
? all < index < range < ref < eq_ref < const < system
? （1）all全表掃描(不走索引)慢查詢
? 1）在大范圍內查詢 > < >= <= != between and in like
? 2）where條件中有計算，有函式
? 3）資料型別不匹配
? 4）拼接條件使用or
? （2）index全索引掃描
? 掃描整個索引樹，才能獲取到多有資料，這樣的索引失去意義
? desc select count(*) from s1;
? （3）range索引范圍掃描（注意點：范圍太大，不能命中索引）
? 1）普通情況
desc select * from s1 where id < 10; # type = range
desc select * from s1 where id < 1000000; # type = all
desc select * from s1 where id between 1 and 10; # type => range
desc select * from s1 where id between 1 and 1000000; # type => all
desc select * from s1 where email like "%w%"; # type => all
desc select * from s1 where email like "w%"; # type => range (去掉左邊的%)
? 2）對in或or陳述句進行優化
? 優化時：union all 比 union 速度快，union在合并資料之后，多一步去重操作
desc select * from s1 where id = 1
union all
select * from s1 where id = 1;
desc select * from s1 where id = 1
union
select * from s1 where id = 1;
? 優化or條件
desc select * from s1 where id = 10 or name = "aaaaa"
desc select * from s1 where id = 10
union all
select * from s1 where name = 'aaaaa';
? （4）ref普通索引查詢（非唯一）
desc select * from s1 where email = "xboyww10@oldboy";
desc select * from s1 where id = 10; # 此時id設定是普通索引
? （5）eq_ref 唯一性索引（聯表）
? 要求：應用在多表聯查中，被關聯的欄位需要主鍵或者唯一鍵，表之間的關系為一對一并且資料條數相同
desc select student1.age from student1,class1 where student1.class_id = class1.id
alter table class1 add primary key(id);
delete from student1 where id = 3;
? （6）const主鍵或唯一索引（單表）
? 針對于primary key 和 unique 索引等值查詢
desc select * from class1 where id = 1 # type => const
desc select * from class1 where id > 1 # type => range
? （7）system
? 只有一條資料的系統表
4、possible_keys
? 執行sql時，可能用到的索引是誰
5、key
? 執行sql時，實際用到的索引是誰
6、key_len
? 判斷聯合索引覆寫的長度（通過位元組數可以判定出到底觸發了哪些聯合索引欄位）
? 在沒有not null約束的時候，默認預留一個位元組，標記是慷訓者非空
? utf8通常情況下，中文1個字符占用三個位元組，字母占用1個位元組，極個別的生僻字占用4個位元組
? varchar 每次存盤資料的時候，系統底層默認會額外預留2個位元組
有not null(不為空) 沒有not null(可為空)
tinyint 1 1+1
int 4 4+1
char(5) 5*3 5*3+1
varchar(5) 5*3 + 2 5*3+2+1
十五、事務處理的四項特征ACID
A.原子性:
? 同一個事務中執行多條sql陳述句，要么全部成功，要么直接回滾，作為一個完整的整體，不能再繼續分隔得最小個體
C.一致性：
? a，i，d 都是為了保證資料的一致性才提出來的，比如約束，鍵在插入資料時，必須按照要求插入，保證規則上的一致性
? 上升到事務中，如果出現意外導致資料不統一，例如臟讀，幻讀，不可重讀，最終要保證資料是一致的
? 上升到主從資料庫，主資料庫增刪改，從資料庫也要進行同步改變，保證資料的一致性；
I.隔離性：
? lock + isolation鎖，來處理事務的隔離級別
? 一個事務和另外一個事務作業程序中彼此獨立隔離
D.持久性：
? 把資料寫到磁盤上，保證資料持久化存盤不丟失
隔離性：隔離級別
? 臟讀：沒提交的資料被讀出來了
? 不可重讀：前后多次讀取，結果資料內容不一樣（同一個會話里，在不修改的情況下，永遠只看到同樣的一份資料）
? 幻讀：前后多次讀取，結果資料的總量不一樣
RU:讀未提交：臟讀，不可重讀，幻讀READ-UNCOMMITTED
RC:讀已提交：防止臟讀，會出現不可重讀和幻讀 READ-COMMITTED
RR:可重復讀：防止臟讀，不可重讀，可能會出現幻讀 REPEATABLE-READ
SR:可串行化：防止一切（但是會把異步并發的程式變成同步程式，不能并發，性能差）
查詢當前mysql的隔離級別（默認是RR）
select @@tx_isolation;
查詢是否自動提交資料
select @@autocommit；
修改mysql配飾檔案
找到組態檔my.ini檔案修改配置
更改隔離級別
transaction_isolation = READ-UNCOMMITTED
不讓系統自動提交資料
autocommit = 0
重啟mysql
net start mysql
net stop mysql
臟讀
READ-UNCOMMITTED
先去調賬設定，重啟mysql，嘗試在一個視窗里通過事務，更改一條資料，開啟另外一個視窗嘗試讀取，會出現問題
不可重復讀
視窗1
begin；
update t1 set k1="abc" where id = 1;
select * from t1;
commit;
視窗2
select * from t1；資料也跟著改了，這就是不可重讀
幻讀
視窗1
begin；
insert into t1 values(4,'c',50);
select * from t1;
commit;
視窗2
select * from t1; 數量也跟著增加了，這就是幻讀
通過二次提交commit，可以讓多用戶同步資料
commit;
事務應用的計數
（1）RR級別下，解決不可重讀，使用mvcc技術，生成最新的mysql的系統備份（快照），然后讀取快照
（2）RR級別下，解決幻讀，gap間隙鎖 next-lock下一鍵鎖
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/26176.html
標籤：MySQL