python資料庫-MySQL資料庫高階查詢操作(51)

Se7eN_HOU發表於2019-07-11
Python資料庫MySql

一、什麼是關係？

1、分析：有這麼一組資料關於學生的資料

學號、姓名、年齡、住址、成績、學科、學科（語文、數學、英語）

我們應該怎麼去設計儲存這些資料呢？

2、先考慮第一正規化：列不可在拆分原則

　　這裡面學科包含了三個學科，所以學科拆分為：語文學科、數學學科、英語學科，同樣的成績也要拆分為語文成績、數學成績、英語成績。這樣既滿足了第一正規化，各列可以設計為：

學號、姓名、年齡、住址、語文學科、數學學科、英語學科、語文成績、數學成績、英語成績

3、在考慮第二正規化：唯一標識

　　也就是說在1NF的基礎上，非Key屬性必須完全依賴於主鍵，第二正規化（2NF）要求資料庫表中的每個例項或記錄必須可以被唯一地區分。選取一個能區分每個實體的屬性或屬性組，作為實體的唯一標識。即確定主鍵，我們可以選取學號為主鍵

4、接著在考慮第三正規化：

　　確保表中各列與主鍵列直接相關，而不是間接相關。即各列與主鍵列都是一種直接依賴關係，則滿足第三正規化。　

　　不難發現在這麼多列中，年齡、住址和成績、學科沒有直接關係，也就是說我考多少分，和年齡及住址都無關，他們都是學生的資訊，但都是不相關的資訊，所以根據第三正規化，我們需要將這些資料根據其相關性拆分為多個表。

5、表與表之間的關係

　　如果我們要想找到張三的語文成績，那麼我們就必須去成績表中查詢，因為成績表中儲存的所有人的所有學科成績。但是在成績表中查詢的時候，需要從省標中查到張三的學號（stu_id）和從學科表中查到語文學科(sub_id)的編號,這個時候這三張表就發生了關係，這也就是關係型資料庫的精髓，而根據這種表與表之間的關係也會衍生出很多的查詢的高階操作

 

二、外來鍵(foreign key)

外來鍵約束:用於限制主表與從表資料完整性。

alter table scores  add constraint 'stu_score_fk' foreign key(stu_id) references students(stu_id);
  • 將scores表的stu_id外來鍵關聯到students表的stu_id欄位（說明：這裡scores表裡面欄位stu_id和students表裡的stu_id重名了，最好避免重名）
  • 每個外來鍵都有一個名字，可以通過constraint指定
  • 存在外來鍵的表，稱之為從表（子表），外來鍵指向的表，稱之為主表（父表）。
  • 作用：保持資料一致性，完整性，主要目的是控制儲存在外來鍵表（從表）中的資料。例如，此時在從表插入或者修改資料時，如果stu_id的值在students表中不存在則會報錯
  • 外來鍵也可以在建立表時可以直接建立約束

語法：

 foreign key (外來鍵欄位） references 主表名 (關聯欄位)
例如：
create table scores(
id int primary key auto_increment,
stu_id int,
sub_id int,
score decimal(5,2),
foreign key(stuid) references students(id),
foreign key(subid) references subjects(id)
);

[主表記錄刪除時的動作] [主表記錄更新時的動作]，此時需要檢測一個從表的外來鍵需要約束為主表的已存在的值。外來鍵在沒有關聯的情況下，可以設定為null.前提是該外來鍵列，沒有not null。

三、外來鍵的級聯操作

  • 在刪除或者修改students表的資料時，如果這個stu_id值在scores中已經存在，則會拋異常
  • 推薦使用邏輯刪除，還可以解決這個問題
  • 可以建立表時指定級聯操作，也可以在建立表後再修改外來鍵的級聯操作
alter table scores add constraint stu_sco foreign key(stu_id) references students(stu_id) on delete cascade;

除了on delete還有on update都要注意級聯操作

級聯操作的型別包括：

  • restrict（限制）：預設值，拋異常，拒絕父表刪除或者更新
  • cascade（級聯）：如果主表的記錄刪掉，則從表中相關聯的記錄都將被刪除，如果主表修改記錄，則從表記錄也將被修改
  • set null：將外來鍵設定為空
  • no action：什麼都不做

 

四、連結查詢

在講解第一個問題關係的時候，我們提到了，如果要查詢張三的語文成績，需要用到三個表，當我們查詢結果來源於多張表的時候就需要使用連線查詢

連結查詢關鍵：找到表間的關係，當前的關係是

  • students表的stu_id---scores表的stu_id
  • subjects表的sub_id---scores表的sub_id

select students.stu_name,subjects.sub_name,scores.score 
from scores
inner join students on scores.stu_id=students.stu_id 
inner join subjects on scores.sub_id=subjects.sub_id 
where students.stu_name='張三' and subjects.sub_name='語文';
+----------+----------+-------+
| stu_name | sub_name | score |
+----------+----------+-------+
| 張三      | 語文     |    80 |
+----------+----------+-------+
  • 結論：當需要對有關係的多張表進行查詢時，需要使用連線join
    • 連線查詢分類如下：
    • 表A inner join 表B：表A與表B匹配的行會出現在結果中
    • 表A left join 表B：表A與表B匹配的行會出現在結果中，外加表A中獨有的資料，未對應的資料使用null填充
    • 表A right join 表B：表A與表B匹配的行會出現在結果中，外加表B中獨有的資料，未對應的資料使用null填充
    • 在查詢或條件中推薦使用“表名.列名”的語法
    • 如果多個表中列名不重複可以省略“表名.”部分
    • 如果表的名稱太長，可以在表名後面使用' as 簡寫名'或' 簡寫名'，為表起個臨時的簡寫名稱

 

五、檢視

　　檢視就像我們python裡面的函式一樣，對SQL語言程式碼塊的封裝

    • 對於複雜的查詢，在多次使用後，維護是一件非常麻煩的事情
    • 解決：定義檢視
    • 檢視本質就是對查詢的一個封裝
    • 定義檢視
create view stuscore as select students.stu_name,subjects.sub_name,scores.score from scores inner join students on scores.stu_id=students.stu_id inner join subjects on scores.sub_id=subjects.sub_id;

這句話的意思就是用stuscore就相當於後面紅色很長的一段SQL語句：

mysql> select *from stuscore;
+----------+----------+-------+
| stu_name | sub_name | score |
+----------+----------+-------+
| 張三     | 語文     |    80 |
| 張三     | 數學     |    85 |
| 張三     | 英語     |    88 |
| 李四     | 語文     |    83 |
| 李四     | 數學     |    84 |
| 李四     | 英語     |    87 |
| 王五     | 語文     |    81 |
| 王五     | 數學     |    85 |
| 王五     | 英語     |    85 |
| 趙六     | 語文     |    88 |
| 趙六     | 數學     |    88 |
| 趙六     | 英語     |    87 |
| 侯七     | 語文     |    84 |
| 侯七     | 數學     |    83 |
| 侯七     | 英語     |    82 |
+----------+----------+-------+

 

 六、自連線查詢

　　有時在資訊查詢時需要進行對自身連線（自連線）自連線是單邊操作，所以我們需要為表定義別名。舉例說明，下面是一個新的學生成績表，在下表中我們要找到比張三成績高的。

+--------+----------+-----------+
| stu_id | stu_name | stu_score |
+--------+----------+-----------+
|      1 | 張三     |        90 |
|      2 | 李四     |        85 |
|      3 | 王五     |        80 |
|      4 | 趙六     |        95 |
|      5 | 侯七     |       100 |
+--------+----------+-----------+

　　一般情況我們看到這張表我們第一時間用語句進行操作：

select * from Score where stu_score>90;

　　可想而知，這是有多麼簡單，假設你並不知道資料庫中張三的成績是90或者資料量相當龐大呢？作為一個資料庫管理員，我們就要用別的方式迅速找出所需要的資料。

 

方式一：分步查詢：最簡單的一種方式，也是最容易想到的操作“

select stu_score from Score where stu_name='張三';//得出張三的成績
select *from Score where stu_score>90;

　　與採用自連線的方式相比，這種方法需要對中間結果進行人工干預，顯然不利於程式中的自動處理操作。

方式二：自連線方式：

select * from Score as a,Score as b where a.stu_name='張三' and a.stu_score<b.stu_score;
+--------+----------+-----------+--------+----------+-----------+
| stu_id | stu_name | stu_score | stu_id | stu_name | stu_score |
+--------+----------+-----------+--------+----------+-----------+
|      1 | 張三      |        90 |      4 | 趙六     |        95 |
|      1 | 張三      |        90 |      5 | 侯七     |       100 |
+--------+----------+-----------+--------+----------+-----------+

如果不想把張三的資訊列印出來我們可以調整SQL語句為：

select b.* from Stu_score as a,Stu_score as b where a.stu_name='張三' and a.stu_score<b.stu_score;
+--------+----------+-----------+
| stu_id | stu_name | stu_score |
+--------+----------+-----------+
|      4 | 趙六      |        95 |
|      5 | 侯七      |       100 |
+--------+----------+-----------+

注意：別名 a,b雖然名稱不同，但是同一個表，定義別名的目的是更方便在自身進行刪選。
執行select通過（中間表）所得到的b.*，，就是最終結果。

 

七、子查詢

子查詢也是常用的一種方式，就是在select裡巢狀select。還是上面的例子，我們用子查詢實現如下：

select * from Stu_score where stu_score>(select stu_score from Stu_score where stu_name='張三'); 
+--------+----------+-----------+
| stu_id | stu_name | stu_score |
+--------+----------+-----------+
|      4 | 趙六      |        95 |
|      5 | 侯七      |       100 |
+--------+----------+-----------+

 

八、內建函式

1、字串函式

1、檢視字元的ascii碼值ascii(str)，str是空串時返回0

mysql> select ascii('a');
+------------+
| ascii('a') |
+------------+
|         97 |
+------------+

2、檢視ascii碼值對應的字元char(數字)

mysql> select char(97);
+----------+
| char(97) |
+----------+
| a        |
+----------+

3、拼接字串concat(str1,str2...)

select concat(12,34,'ab');
+--------------------+
| concat(12,34,'ab') |
+--------------------+
| 1234ab             |
+--------------------+

4、包含字元個數length(str)

mysql> select length('abc');
+---------------+
| length('abc') |
+---------------+
|             3 |
+---------------+

5、擷取字串

  • left(str,len)返回字串str的左端len個字元
  • right(str,len)返回字串str的右端len個字元
  • substring(str,pos,len)返回字串str的位置pos起len個字元
mysql> select substring('abc123',2,3);
+-------------------------+
| substring('abc123',2,3) |
+-------------------------+
| bc1                     |
+-------------------------+

6、去除空格

  • ltrim(str)返回刪除了左空格的字串str
  • rtrim(str)返回刪除了右空格的字串str
  • trim([方向 remstr from str)返回從某側刪除remstr後的字串str，方向詞包括both、leading、trailing，表示兩側、左、右
mysql> select trim('  bar   ');
+------------------+
| trim('  bar   ') |
+------------------+
| bar              |
+------------------+
mysql> select trim(leading 'x' FROM 'xxxbarxxx');
+------------------------------------+
| trim(leading 'x' FROM 'xxxbarxxx') |
+------------------------------------+
| barxxx                             |
+------------------------------------+
mysql> select trim(trailing 'x' FROM 'xxxbarxxx');
+-------------------------------------+
| trim(trailing 'x' FROM 'xxxbarxxx') |
+-------------------------------------+
| xxxbar                              |
+-------------------------------------+
mysql> select trim(both 'x' FROM 'xxxbarxxx');
+---------------------------------+
| trim(both 'x' FROM 'xxxbarxxx') |
+---------------------------------+
| bar                             |
+---------------------------------+

7、替換字串replace(str,from_str,to_str)

mysql> select replace('abc123','123','def');
+-------------------------------+
| replace('abc123','123','def') |
+-------------------------------+
| abcdef                        |
+-------------------------------+

8、大小寫轉換，函式如下

  • lower(str)
  • upper(str)
mysql> select lower('aBcD');
+---------------+
| lower('aBcD') |
+---------------+
| abcd          |
+---------------+

2、數學函式

1、求絕對值abs(n)

mysql> select abs(-32);
+----------+
| abs(-32) |
+----------+
|       32 |
+----------+

2、求m除以n的餘數mod(m,n)，同運算子%

mysql> select mod(10,3);
+-----------+
| mod(10,3) |
+-----------+
|         1 |
+-----------+

mysql> select 10%3;
+------+
| 10%3 |
+------+
|    1 |
+------+

3、floor(n)，表示不大於n的最大整數

mysql> select floor(2.3);
+------------+
| floor(2.3) |
+------------+
|          2 |
+------------+

4、ceiling(n)，表示不小於n的最大整數

mysql> select ceiling(2.3);
+--------------+
| ceiling(2.3) |
+--------------+
|            3 |
+--------------+

5、求四捨五入值round(n,d)，n表示原數，d表示小數位置，預設為0

mysql> select round(1.6);
+------------+
| round(1.6) |
+------------+
|          2 |
+------------+

6、求x的y次冪pow(x,y)

mysql> select pow(2,3);
+----------+
| pow(2,3) |
+----------+
|        8 |
+----------+

7、隨機數rand()，值為0-1.0的浮點數

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.0713081630610937 |
+--------------------+

3、日期時間函式

  • 獲取子值，語法如下
    • year(date)返回date的年份(範圍在1000到9999)
    • month(date)返回date中的月份數值
    • day(date)返回date中的日期數值
    • hour(time)返回time的小時數(範圍是0到23)
    • minute(time)返回time的分鐘數(範圍是0到59)
    • second(time)返回time的秒數(範圍是0到59)
mysql> select year('2019-7-11');
+-------------------+
| year('2019-7-11') |
+-------------------+
|              2019 |
+-------------------+
  • 日期計算，使用+-運算子，數字後面的關鍵字為year、month、day、hour、minute、second
mysql> select '2019-7-11'+interval 1 day;
+----------------------------+
| '2019-7-11'+interval 1 day |
+----------------------------+
| 2019-07-12                 |
+----------------------------+

日期格式化date_format(date,format)，format引數可用的值如下

  • 獲取年%Y，返回4位的整數

    *　獲取年%y，返回2位的整數

    *　獲取月%m，值為1-12的整數

  • 獲取日%d，返回整數

    *　獲取時%H，值為0-23的整數

    *　獲取時%h，值為1-12的整數

    *　獲取分%i，值為0-59的整數

    *　獲取秒%s，值為0-59的整數

 

九、事物

  • 當一個業務邏輯需要多個sql語句完成時，如果其中某條sql語句出錯，則希望整個操作都退回
  • 使用事務可以完成退回的功能，保證業務邏輯的正確性
  • 事務四大特性(簡稱ACID)
    • 原子性(Atomicity)：事務中的全部操作在資料庫中是不可分割的，要麼全部完成，要麼均不執行
    • 一致性(Consistency)：幾個並行執行的事務，其執行結果必須與按某一順序序列執行的結果相一致
    • 隔離性(Isolation)：事務的執行不受其他事務的干擾，事務執行的中間結果對其他事務必須是透明的
    • 永續性(Durability)：對於任意已提交事務，系統必須保證該事務對資料庫的改變不被丟失，即使資料庫出現故障
  • 要求：表的型別必須是innodb或bdb型別，才可以對此表使用事務
  • 檢視錶的建立語句
show create table Stu_score;
  • 修改表的型別：alter table '表名' engine=innodb;
alter table Stu_score engine=innodb;
  • 事務語句
開啟begin;
提交commit;
回滾rollback;

　　在begin；後面寫我們要操作的SQL語句組合也就是我們所謂的要執行的事物，但是這個時候寫好的SQL語句就算我們回車之後，也不會執行，知道commit;執行之後才會被執行到資料庫中，rollback回滾是我們begin;之後發現我們寫錯了，或者不想執行了，都可以在commit；之前回滾到上一次commit；的狀態，很像版本控制器SVN和GIT一樣

相關文章