python之路-----MySql操作二

標籤：完全   prim   div   主機資訊   2.3   建表   區別   sel   補充   

一.主鍵　　1.每個 表只有一個主鍵　　2.每個主鍵可以由多個列組成.(如果主鍵由多個組成,只要有一行列值不等即可)

CREATE TABLE NAME (    id INT auto_increment,    NAME CHAR (20),    sid int,    PRIMARY KEY (id, sid));插入資料(1,‘a‘,1)，主索引值為：(1,1)  ,插入(1,‘b‘,2)，主鍵為(1,2)，兩個主索引值不完全相等即可

主鍵樣本 二.唯一索引　　唯一索引也是一種約束，約束內容為：　　1.可以為空白，但是不能重複　　2.加快查詢速度

CREATE TABLE name1 (    id INT auto_increment,    NAME CHAR (20),    UNIQUE qu_name (NAME),    INDEX (id))

唯一索引樣本　　唯一索引的值可以為多列，稱為聯合唯一索引　　和主鍵的區別：　　　　1.主鍵不允許為空白，唯一索引可以為空白　　　　2.主鍵可在表外部被調用，唯一索引只能在表內使用  

 三.自增

　　1.修改自增列起始值　　　　1.1.查看錶資料　　　　　　DESC hostinfo;　　　　1.2.查看建立表時的命令:　　　　　　show CREATE TABLE tablename　　　　　　show CREATE TABLE tablename \G 豎著顯示資料

CREATE TABLE `more2` (`id` int(11) NOT NULL AUTO_INCREMENT,`pc_name` char(20) DEFAULT NULL,`owner_id` int(11) DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `uq_more2` (`owner_id`),CONSTRAINT `one2_more2` FOREIGN KEY (`owner_id`) REFERENCES `one2` (`id`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

　　　　1.3.通過修改表的auto_increment值來進行自增初始值的修改　　　　　　ALTER TABLE tablename auto_increment=9;　　　　　這時候會從9開始自增,新插入的資料自增列值為9.　　2.修改步長　　mysql的步長單位和其他的資料庫軟體不同，部分資料庫的步長為表層級的，可以針對錶設定步長,而mysql的步長修改為會話層級的,一次串連修改的步長僅本次串連有效，如果建立串連，而步長依舊為1.　　2.1 查看步長　　　　show session variables like ‘%auto_inc%‘;     #查看session變數

　　　　　　　　+--------------------------+-------+
　　　　　　　　| Variable_name | Value |
　　　　　　　　+--------------------------+-------+
　　　　　　　　| auto_increment_increment | 1 |   #步長
　　　　　　　　| auto_increment_offset | 1 |       #數量預設起始值
　　　　　　　　+--------------------------+-------+

　　2.2 修改session層級自增變數值　　　　 set session auto_increment_increment=3;   #設定步長為3　　　　set session auto_increment_offset=5;           #設定自增列預設起始值為5　　2.3 查看修改後的值　　　

　　　　　　　　+--------------------------+-------+
　　　　　　　　| Variable_name | Value |
　　　　　　　　+--------------------------+-------+
　　　　　　　　| auto_increment_increment | 3 |
　　　　　　　　| auto_increment_offset | 5 |
　　　　　　　　+--------------------------+-------+

　　　　建立串連,查看自增起始值和步長

　　　　　　　　+--------------------------+-------+
　　　　　　　　| Variable_name | Value |
　　　　　　　　+--------------------------+-------+
　　　　　　　　| auto_increment_increment | 1 |
　　　　　　　　| auto_increment_offset | 1 |
　　　　　　　　+--------------------------+-------+

　　我們發送，通過修改session變數，僅本次串連有效，如果要使修改的值永久有效，需要修改global變數

　　2.4查看global自增相關變數值

　　　　show global variables like ‘%auto_inc%‘;　

　　　　　　　　+--------------------------+-------+
　　　　　　　　| Variable_name | Value |
　　　　　　　　+--------------------------+-------+
　　　　　　　　| auto_increment_increment | 1 |
　　　　　　　　| auto_increment_offset | 1 |
　　　　　　　　+--------------------------+-------+

　　2.5 修改global變數

　　　　set global auto_increment_increment=2;  #修改自增步長

　　　   set global auto_increment_offset=2;      #修改自增列起始值

　　2.6 建立串連查看，配置依舊生效 。但是不建議使用該方法進行修改

四.外鍵　　補充:當主鍵為多個時，外鍵可以關聯多個

 create table morekey(    id int auto_increment,    sid int,    name  char(20),    PRIMARY KEY(id,sid));CREATE table morekey1(    id1 INT,    sid1  INT,    name1 CHAR(20),    CONSTRAINT fk_moremore FOREIGN KEY(id1,sid1) REFERENCES morekey(id,sid) )

關聯多個外鍵　　1.一對多　　　　當我們對兩個表通過外鍵建立約束關係時，此時表A的值對應應表B的多個值時,且B表的值僅對應A表的一個值，則該關係為一對多。例如部門表和員工表。一個員工僅對應一個部門,而一個部門卻可以對應多個員工。

 create table morekey(    id int auto_increment,    sid int,    name  char(20),    PRIMARY KEY(id,sid));CREATE table morekey1(    id1 INT,    sid1  INT,    name1 CHAR(20),    CONSTRAINT fk_moremore FOREIGN KEY(id1,sid1) REFERENCES morekey(id,sid) )

一對多樣本　　2.一對一　　　　當B表的值受A表的約束,且A表的值只能被B表使用一次，這時候A表和B表的值是一對一的。例如公司資產登記，一台pc只能歸一個職工使用。或者是密碼登陸管理，不允許使用者名稱重複。　　　　我們知道，唯一索引的作用就是對錶內的值進行約束，使其在表內是唯一的，不允許重複。我們利用外鍵和唯一索引，即可完成一對一的表格設計

-- pc編號 CREATE TABLE hostinfo(    id int auto_increment PRIMARY KEY,    host_name CHAR(20));-- 使用者資產資訊CREATE TABLE employ(    id int auto_increment PRIMARY KEY,    name char(20),    pc_id int,    UNIQUE uq_hostinfo_employ (pc_id),    CONSTRAINT fk_hostinfo_employ FOREIGN KEY (pc_id) REFERENCES hostinfo(id));

一對一樣本　　3.多對多　　　　當A表的值和B表的值互相對應多個時,即兩表之間有著多對多的關係。例如營運人員對公司伺服器的主機管理，一個人可登陸多台伺服器，一台伺服器也可多人登陸。　　　　我們需要利用第三張表來記錄A表和B表之間多對多的關係。

-- 主機資訊表 CREATE TABLE pcinfo(    id int auto_increment PRIMARY KEY,    host_name char(20));-- 管理員資訊表 CREATE TABLE admininfo(    id int auto_increment PRIMARY KEY,    name char(20));-- 管理員主機對應表CREATE TABLE admin_pc(    id int auto_increment PRIMARY KEY,    pc_id int ,    user_id int,    UNIQUE uq_user_pc (pc_id,user_id),    CONSTRAINT fk_admin_pc FOREIGN KEY(pc_id) REFERENCES pcinfo(id),    CONSTRAINT fk_admin_user FOREIGN KEY(user_id) REFERENCES admininfo(id));

多對多樣本

 

五.動作表內容進階運用補充

　　1.笛卡爾積

　　　　笛卡爾積是關係代數裡的一個概念，表示兩個表中的每一行資料任意組合。例如A表中有三條資料,B表中有三條資料，則使用命令:

　　　　select * from A,B,則會產生九條結果。A表的每個值都會和B表的每個值產生一條對應的值。關係圖如下：

　　　　　　　　　　　　

 

　　2.暫存資料表，將過濾出來的資料臨時當成一張表使用

命令格式：(SELECT 列名 FROM 表) as B應用連表操作:SELECT * FROM (SELECT sid FROM score) as B LEFT JOIN student on student.sid=B.sid;把score表中過濾出來的資料和student表進行連表操作

      3.insert插入過濾後的表內容

INSERT INTO class(caption) SELECT name FROM class2;ps:需要注意的是，插入的列要跟表的列一致

　　4.條件陳述式

　　句型:case when 條件 then 條件為正返回 esle 條件為假返回 end

case when id<10 then 1 else 0 end;   #如果id小於10，則返回1,否則返回0

　　5.利用常量來統計

select count(1) from tablename groub by id;

　　6.插入動態值

SELECT                         student_id,                        (select num from score as s2 where s2.student_id=s1.student_id and course_id = 1) as 語文,                        (select num from score as s2 where s2.student_id=s1.student_id and course_id = 2) as 數學,                        (select num from score as s2 where s2.student_id=s1.student_id and course_id = 3) as 英語                    from score as s1;

　　s1表的值可以再內迴圈中使用。類似於：

for student_id in s1:    for id in s2:        s2=s1

 　　7.三元運算

　　　　if(條件,為真返回，為假返回)

　　　　e.g:if (isnull(xx),0,1)  如果為null，則返回0，否則返回1

　　

python之路-----MySql操作二