Python全棧之學習MySQL(1)

1. mysql約束

# ### char varchar (補充)
char    字符長度   255個
varchar 字符長度 21845個
# ### part1  時間類型
date  YYYY-MM-DD 年月日 (節假日,紀念日)
time  HH:MM:SS   時分秒 (體育競賽,記錄時間)
year  YYYY       年份   (歷史,酒的年份)
datetime  YYYY-MM-DD HH:MM:SS  年月日 時分秒 (上線時間,下單時間)
	create table t1(d date, t time , y year , dt datetime);
	insert into t1 values("2020-11-3","9:19:30","2020","2020-11-3 9:19:30");
	insert into t1 values(now(),now(),now(),now());
timestamp YYYYMMDDHHMMSS(時間戳)  自動更新時間 (不需要手動寫入,自動實現更新記錄,[用作記錄修改的時間])
	create table t2(dt datetime , ts timestamp);
	insert into t2 values(20201103092530 , 20201103092530);
	insert into t2 values(null,null); # 區別 timestamp 自動更新時間(以當前時間戳) datetime沒有
	insert into t2 values(20390102101010 , 20390102101010); error # 超越2038 
# ### part2 約束 : 對編輯的數據進行類型的限制,不滿足約束條件的報錯
	unsigned   :    無符號
	not null   :    不為空
	default    :    默認值
	unique     :    唯一值,加入唯一索引
	(索引相當於字典目錄,索引的提出是為瞭加快速度,一味地亂加索引不會提高查詢效率)
	primary key:    主鍵
	auto_increment: 自增加一
	zerofill   :    零填充
	foreign key:    外鍵
# unsigned 無符號
	create table t3(id int unsigned);
	insert into t3 values(-1); error
	insert into t3 values(4000000000); success
	
# not null   :    不為空
	create table t4(id int not null , name varchar(11));
	insert into t4 values(1,"張宇");
	insert into t4 values(null,"張宇"); error
	insert into t4(name) values("李四"); error
	
# default    :    默認值
	create table t5(id int not null  , name varchar(11) default "沈思雨" );
	insert into t5 values(1,null);
	insert into t5(id) values(2);
	create table t5_2(id int not null  default "1111" , name varchar(11) default "沈思雨" );
	insert into t5_2 values(); # 在values裡面不寫值,默認使用默認值;
	
# unique     :    唯一值,加入唯一索引(索引的提出是為瞭加快速度,一味地亂加索引不會提高查詢效率)
	# 唯一 可為null  標記成: UNI
	create table t6(id int unique , name char(10) default "趙萬裡" );
	insert into t6(id) values(1);
	insert into t6(id) values(1); error
	insert into t6(id) values(null);
	insert into t6(id) values(null); # id變成瞭多個null
# primary key:    主鍵 [ 唯一 + 不為null ]   PRI 標記數據的唯一特征
	"""一個表中,隻能設置一個字段為一個主鍵,unique唯一約束可以設置多個"""
	# 創建主鍵
	create table t7(id int primary key , name varchar(10) default "趙沈陽");
	insert into t7(id) values(1);
	insert into t7(id) values(1); error 
	insert into t7(id) values(null); error
	# unique + not null => PRI
	create table t8(id int unique not null ,  name varchar(10) default "趙沈陽" );
	# primary key  / unique + not null  => 優先把primary key 作為主鍵;
	create table t9(id1 int unique not null ,  id2 int primary key );
	# 一個表隻能設置單個字段為一個主鍵;
	create table t10(id1 int  primary key  ,  id2 int primary key ); error
	
# auto_increment: 自增加一 (一般配合 主鍵或者unique 使用)
	create table t11(id int primary key auto_increment , name varchar(255) default "敬文棟");
	insert into t11 values(1,"張三");
	insert into t11 values(null,"李四");
	insert into t11(id) values(null);
	# 使用默認值或者自增插入數據
	insert into t11 values();
	# 刪除數據
	delete from t11;
	# 刪除數據 + 重置id
	truncate table t11;

# zerofill   :    零填充 (配合int使用,不夠5位拿0來填充)
	create table t12(id int(5) zerofill);
	insert into t12 values(1234567);
	insert into t12 values(12);

2. 外鍵_聯合主鍵_唯一索引

# ### part3"""主鍵索引 : PRI    [primary key]唯一索引 : UNI    [unique]普通索引 : MUL    [index]"""# 1.聯合唯一索引"""unique(字段1,字段2,字段3 ..... )  合在一起,該數據不能重復"""# unique + not nullcreate table t1_server(id int , server_name varchar(10)  not null , ip varchar(15) not null , port int not null , unique(ip,port) );insert into t1_server values(1,"阿裡","192.168.11.251",3306);insert into t1_server values(1,"阿裡","192.168.11.251",80);insert into t1_server values(1,"阿裡","192.168.11.252",80);insert into t1_server values(1,"阿裡","192.168.11.252",80); error# unique : 有可能出現多個空值的情況要註意;create table t2_server(id int , server_name varchar(10)  not null , ip varchar(15) , port int , unique(ip,port) );insert into t2_server values(1,"騰訊","192.168.11.251",3306);insert into t2_server values(1,"騰訊","192.168.11.251",3306); errorinsert into t2_server values(1,"騰訊",null,null); # 註意點: 允許插入多個空值;+------+-------------+----------------+------+| id   | server_name | ip             | port |+------+-------------+----------------+------+|    1 | 騰訊        | 192.168.11.251 | 3306 ||    1 | 騰訊        | NULL           | NULL ||    1 | 騰訊        | NULL           | NULL ||    1 | 騰訊        | NULL           | NULL ||    1 | 騰訊        | NULL           | NULL |+------+-------------+----------------+------+# 2.聯合唯一主鍵create table t3_server(id int ,server_name varchar(10)  not null , ip varchar(15) , port int  , primary key(ip,port) );insert into t3_server values(1,"華為","192.168.11.251",3306);insert into t3_server values(1,"華為","192.168.11.251",3307);"""總結:primary key(字段1,字段2 ... )   聯合唯一主鍵 , 單個字段情況,可以設置一個主鍵,如果是多個字段隻能設置成聯合主鍵,合在一起表達一個主鍵概念;unique(字段1,字段2 ... )    聯合唯一索引index(字段1,字段2 ... )    聯合普通索引"""# 3.foreign key:    外鍵,把多張表通過一個關聯字段聯合在一起 (該字段可以設置成外鍵,作用是可以聯級更新或者聯級刪除)"""  語法:foreign key(classid) references class1(id)  條件:被關聯的字段,必須具備唯一屬性;"""student1:id  name          age    classid      1  wangtongpei   58     12   liuyifeng     85     13   wangwen       18     2class1:id classname 1  python322  python33# 創建class1create table class1(id int , classname varchar(255));# 添加唯一索引alter table class1 add unique(id);# 刪除索引create table class222(id int unique, classname varchar(255));alter table class1 drop index id;# 創建student1create table student1(id int primary key auto_increment,name varchar(255),age int,classid int,foreign key(classid) references class1(id));# 添加數據insert into class1 values(1,"python32");insert into class1 values(2,"python33");insert into class1 values(3,"python34");insert into student1 values(null,"wangtongpei",58,1);insert into student1 values(null,"liuyifeng",85,1);insert into student1 values(null,"wangwen",18,2);# 沒有關聯的數據可以直接刪除delete from class1 where id = 1;# 有關聯的數據不能直接刪除,要先把關聯的數據刪掉之後再刪除delete from student1 where id = 3;delete from class1 where id = 2;# 聯級更新 , 聯級刪除 ( 謹慎使用 )"""聯級刪除 on delete cascade聯級更新 on update cascade"""# 創建class2create table class2(id int primary key auto_increment, classname varchar(255));# 創建student2create table student2(id int primary key auto_increment,name varchar(255),age int,classid int,foreign key(classid) references class2(id) on delete cascade on update cascade #區別);# 添加數據insert into class2 values(1,"python32");insert into class2 values(2,"python33");insert into class2 values(3,"python34");insert into student2 values(null,"wangtongpei",58,1);insert into student2 values(null,"liuyifeng",85,1);insert into student2 values(null,"wangwen",18,2);# 聯級刪除 (把所有關聯數據全部刪除,謹慎;)delete from class2 where id = 1;# 聯級更新 (把所有關聯數據全部更新,謹慎;)update class2 set id = 100 where classname="python33# ### part3
"""
主鍵索引 : PRI    [primary key]
唯一索引 : UNI    [unique]
普通索引 : MUL    [index]
"""
# 1.聯合唯一索引
	"""unique(字段1,字段2,字段3 ..... )  合在一起,該數據不能重復"""
	# unique + not null
	create table t1_server(id int , server_name varchar(10)  not null , ip varchar(15) not null , port int not null , unique(ip,port) );
	insert into t1_server values(1,"阿裡","192.168.11.251",3306);
	insert into t1_server values(1,"阿裡","192.168.11.251",80);
	insert into t1_server values(1,"阿裡","192.168.11.252",80);
	insert into t1_server values(1,"阿裡","192.168.11.252",80); error
	# unique : 有可能出現多個空值的情況要註意;
	create table t2_server(id int , server_name varchar(10)  not null , ip varchar(15) , port int , unique(ip,port) );
	insert into t2_server values(1,"騰訊","192.168.11.251",3306);
	insert into t2_server values(1,"騰訊","192.168.11.251",3306); error
	insert into t2_server values(1,"騰訊",null,null); # 註意點: 允許插入多個空值;
	+------+-------------+----------------+------+
	| id   | server_name | ip             | port |
	+------+-------------+----------------+------+
	|    1 | 騰訊        | 192.168.11.251 | 3306 |
	|    1 | 騰訊        | NULL           | NULL |
	|    1 | 騰訊        | NULL           | NULL |
	|    1 | 騰訊        | NULL           | NULL |
	|    1 | 騰訊        | NULL           | NULL |
	+------+-------------+----------------+------+
	
# 2.聯合唯一主鍵
	create table t3_server(id int ,server_name varchar(10)  not null , ip varchar(15) , port int  , primary key(ip,port) );
	insert into t3_server values(1,"華為","192.168.11.251",3306);
	insert into t3_server values(1,"華為","192.168.11.251",3307);
	"""
	總結:
		primary key(字段1,字段2 ... )   聯合唯一主鍵 , 單個字段情況,可以設置一個主鍵,如果是多個字段隻能設置成聯合主鍵,合在一起表達一個主鍵概念;
		unique(字段1,字段2 ... )	    聯合唯一索引
		index(字段1,字段2 ... )		    聯合普通索引
	"""
	
# 3.foreign key:    外鍵,把多張表通過一個關聯字段聯合在一起 (該字段可以設置成外鍵,作用是可以聯級更新或者聯級刪除)
	"""  
		語法:	foreign key(classid) references class1(id)  
		條件:	被關聯的字段,必須具備唯一屬性;
	"""
	student1:
		id  name          age    classid      
		1  	wangtongpei   58     1
		2   liuyifeng     85     1
		3   wangwen       18     2
	class1:
		id classname 
		1  python32
		2  python33
	
	# 創建class1
	create table class1(id int , classname varchar(255));
	# 添加唯一索引
	alter table class1 add unique(id);
	# 刪除索引
	create table class222(id int unique, classname varchar(255));
	alter table class1 drop index id;
	# 創建student1
	create table student1(
	id int primary key auto_increment,
	name varchar(255),
	age int,
	classid int,
	foreign key(classid) references class1(id)
	);
	# 添加數據
	insert into class1 values(1,"python32");
	insert into class1 values(2,"python33");
	insert into class1 values(3,"python34");
	insert into student1 values(null,"wangtongpei",58,1);
	insert into student1 values(null,"liuyifeng",85,1);
	insert into student1 values(null,"wangwen",18,2);
	# 沒有關聯的數據可以直接刪除
	delete from class1 where id = 1;
	# 有關聯的數據不能直接刪除,要先把關聯的數據刪掉之後再刪除
	delete from student1 where id = 3;
	delete from class1 where id = 2;
	
	# 聯級更新 , 聯級刪除 ( 謹慎使用 )
	"""
	聯級刪除 on delete cascade
	聯級更新 on update cascade
	"""
	# 創建class2
	create table class2(id int primary key auto_increment, classname varchar(255));
	# 創建student2
	create table student2(
	id int primary key auto_increment,
	name varchar(255),
	age int,
	classid int,
	foreign key(classid) references class2(id) on delete cascade on update cascade #區別
	);
	# 添加數據
	insert into class2 values(1,"python32");
	insert into class2 values(2,"python33");
	insert into class2 values(3,"python34");
	insert into student2 values(null,"wangtongpei",58,1);
	insert into student2 values(null,"liuyifeng",85,1);
	insert into student2 values(null,"wangwen",18,2);
	# 聯級刪除 (把所有關聯數據全部刪除,謹慎;)
	delete from class2 where id = 1;
	# 聯級更新 (把所有關聯數據全部更新,謹慎;)
	update class2 set id = 100 where classname="python33";

3. 存儲引擎_表關系

# ### part4 表與表之間的關系
(1) 一對一 : id name age sex address guanlian    id userid mother father ....  
(2) 一對多(多對一) : 班級和學生之間的關系 一個班級可以對應多個學生,反過來,多個學生對應一個班級;
(3) 多對多 : 一個學生可以同時學習多個學科,一個學科同時可以被多個學生學習
			 一本書可以被多個作者共同編寫,一個作者可以寫多本書
xueke (表1)
id  name
1   math
2   english
3   wuli 
student (表2)
id  name
1   wangwen
2   wangwei
3   wangtongpei
relation (關系表3)
"""
把 xid 和 sid 這兩個關聯字段設置成外鍵,
關聯xueke表裡的id(對應的xid) , 
關聯student表裡的id(對應的sid)
"""
xid sid
1   1
1   2
1   3
2   1
2   2
2   3

# ### part5 存儲引擎 : 存儲數據的一種結構方式
# 概念:
表級鎖 :  隻要有一個線程執行修改表中的相關操作,就會上鎖,其他線程默認等待;
行級鎖 :  針對於當前表中的這條記錄,這一行進行上鎖,其他數據仍然可以被其他線程修改,實現高並發,高可用;
事務處理: 執行sql語句時,必須所有的操作全部成功,才最終提交數據,有一條失敗,直接回滾,恢復到先前狀態
begin     : 開啟事務
commit    : 提交數據
rollback  : 回滾數據

MyISAM: 表級鎖    			  (5.5版本之前的默認存儲引擎)
InnoDB: 事務處理,行級鎖,外鍵 (5.5版本之後的默認存儲引擎)
MEMORY: 把數據放在內存中,臨時緩存;
BLACKHOLE: anything you write to it disappears
		   一般用於同步主從數據庫;(放在主數據庫和從數據庫之間的一臺服務器;產生binlog日志進行分發,減輕master的壓力)
"""
主數據庫: 增刪改
從數據庫: 查詢
配置: 一主一從 , 一主多從 , 多主多從
"""
"""
show engins;  # 查看存儲引擎
show create table memary1; # 查看創建數據庫的結構
desc + 表名;  # 查看表結構
"""
create table myisam1( id int ) engine=MyISAM;
.frm 表結構
.MYD 表數據
.MYI 表索引
create table innodb1( id int ) engine=InnoDB;
.frm 表結構
.ibd 表數據+表索引
create table memory1( id int ) engine=MEMORY;
.frm 隻有表結構 , 數據存放在內存中
create table blackhole( id int ) engine=BLACKHOLE;
.frm 隻有表結構 , 所有的數據都不會存儲;

# ### 額外補充
# 關於約束的添加和刪除
# 1 添加/刪除 約束 not null
	#alter table 表名 modify 字段名 類型
	alter table t1 modify id int not null
	alter table t1 modify id int
# 2 添加/刪除 unique 唯一索引
	# alter table 表名 add unique(id)
	alter table t1 add unique(id)
	alter table t1 drop index id
# 3 添加/刪除 primary key
	# alter table 表名 add primary key(id);
	alter table t1 add primary key(id);
	alter table t1 drop primary key;
# 4 添加/刪除 foreign key 外鍵 (先通過desc 表 找到外鍵名字,然後再刪)
	alter table student1 drop foreign key student1_ibfk_1; #刪除
	alter table student1 add foreign key(classid) references class1(id) #添加

在這裡插入圖片描述

在這裡插入圖片描述

總結

本篇文章就到這裡瞭,希望能夠給你帶來幫助,也希望您能夠多多關註WalkonNet的更多內容!

推薦閱讀: