第六章、MySQL資料庫入門

標籤：

 

1、資料庫的概念

資料庫是儲存資料的倉庫，可以方便的把資料放進去，並且把資料根據各種需求取出來。

 

資料庫管理系統（Database Management System，DBMS）是對資料庫進行管理（增刪改查）的軟體，常用的有DBMS有MYSQL、Oracle、DB2、MSSQLServer等。MYSQL是免費、開源的，因此應用最廣泛。

不同的DBMS的用法大同小異，掌握一個也就基本等於掌握了其它的DBMS。

 

2、資料庫、表、列

Table(表):書都放到書架上，碗放到櫥櫃中，不同類型的資料放到不同的“格子”中，將這種地區叫做“表”(Table)。不同的表根據放的資料不同進行空間的最佳化，找起來也方便。

 

兩種叫法：列(Colum)/欄位(Field)

 

 

3、主鍵

主鍵有兩種選用策略：業務主鍵和邏輯主鍵。業務主鍵是使用有意義的欄位做主鍵，比如社會安全號碼、銀行帳號等；邏輯主鍵是使用沒有任何意義的欄位做主鍵，完全給程式看的，業務人員不會看的資料。

 

表間關聯、外鍵（ForeignKey）

 

 

4、MYSQL常用資料類型文本：

 

CHAR(*)：最多255個位元組的定長字串，它的長度必須在建立時指定

VARCHAR(*)：最多255個位元組的可變長度字串，它的長度必須在建立時指定

TEXT：最大長度為64K字元的變長文本

TINYTEXT：最大長度為255字元的變長文本

MEDUIMTEXT：最大長度為16K字串的變長文本

LONGTXT：最大長度為4GB字元的變長文本

 

整數(考慮資料取值後選擇儘可能小的類型)

tinyint：1位元組。有符號值：-128 到127；無符號值：0到255

smallint：2位元組。有符號值：-32768 到32767；無符號值：0到65535

mediumint：3位元組。

int：4位元組

bigint：8位元組

 

小數(需要指定長度和小數點，也就是顯示寬度和小數位元)：

decimal：精確儲存的小數，在內部用字串儲存，適合金額等要求精確的類型。別名：NUMERIC

 

float：4位元組，單精確度。會近似儲存(*)，效率比decimal高。

double：8位元組，雙精確度。會近似儲存(*)，效率比decimal高。

 

日期時間：

DATE：4位元組。範圍：1000-01-01——9999-12-31

 

TIME：3位元組。範圍：-838:59:59——838:59:59

DATETIME：8位元組。範圍：1000-01-01 00:00:00——9999-12-31 23:59:59

 

 

位元據：

TITYBLOB：最大長度為255位元組

 

BLOB：最大長度為64KB

MEDIUMBLOB：最大長度為16MB

LONGBLOB：最大長度為4GB

 

5、SQL語句入門

(1)SQL語句中字串一般用單引號；

 

(2)SQL語句是大小寫不敏感的

(3)最簡單的SQL語句：查看一個表的全部資料：select * from T_Students

(4)簡單的插入資料庫的SQL語句：Insert Into T_Students(Id,Name,Age,Gender) Values(1,‘汪峰‘,18,1)

補充：如果只想執行NavCat中編寫的一部分代碼，而不是執行全部代碼，只要選中要執行的代碼，點擊滑鼠右鍵，選擇“運行已經選擇的”即可。Insert語句可以省略表名後的列名，但是強烈不推薦

(5)如果插入的行中有些欄位的值不確定，那麼 Insert 的時候不指定那些列即可。不“允許為空白”的列在插入時不能省略

(6)自動遞增/自增(Auto Increment)：欄位自增可以避免並發等問題，不要程式員代碼控制自增。用自增欄位在Insert的時候不用指定值。

 

Update語句

1、更新一個列Update T_Students Set Age=30,Name=‘tom‘

 

2、更新多個列： UPDATE T_Students Set Age=30,Name=‘tom‘

3、還可以使用運算式：UPDATE T_Students Set Age=Age+1

4、更新一部分資料：UPDATE T_Students Set Age=10 Where Name=‘tom‘

5、Where中還可以使用複雜的邏輯判斷：Update T_Students Set Age=30 Where Name=‘tom‘ or Age<25      還有where (Age>20 and Age<30) or(Age=80)等

6、Where中可以使用的其他邏輯運算子：or、and、not、<、>、>=、<=、!=（或<>）等

 

Delete資料刪除

刪除表中全部資料：Delete From T_Students

 

Delete也可以where子句來刪除一部分資料：Delete From T_Students Where Age>20

Delete只是刪除資料，表還在。

Select文法

1、簡單的資料檢索 ：SELECT * FROM T_Employees

 

2、只檢索需要的列 ：SELECT Number FROM T_Employees 、SELECT Name,Age FROM T_Employees

3、列別名：SELECT Number AS 編號,Name AS 姓名,Age AS Age111 FROM T_Employees  寫不寫AS都行

5、使用where檢索合格資料：SELECT Name FROM T_Employees WHERE Salary<5000。

6、還可以檢索不與任何錶關聯的資料：select 1+1;select now();

 

彙總函式

1、SQL彙總函式：MAX（最大值）、MIN（最小值）、AVG （平均值）、SUM （和）、COUNT（數量）

 

2、大於25歲的員工的最高工資 ：SELECT MAX(Salary) FROM T_Employees WHERE Age>25 

3、最低工資和最高工資：SELECT MIN(Salary),MAX(Salary) FROM  T_Employees

4、大於25歲的員工人數：SELECT COUNT(*) FROM  T_Employees  WHERE Age>25 

5、全體員工的工資總和平均工資：SELECT SUM(Salary),AVG (Salary) FROM  T_Employees

 

Order By排序

1、ORDER BY子句位於SELECT語句的末尾，它允許指定按照一個列或者多個列進行排序，還可以指定排序方式是升序(從小到大排列，ASC)還是降序(從大到小排列，DESC) 

 

2、按照年齡升序排序所有員工資訊的列表：SELECT * FROM  T_Employees ORDER BY Age ASC 

3、按照年齡從大到小排序，如果年齡相同則按照工資從大到小排序 ：SELECT * FROM  T_Employees ORDER BY Age DESC,Salary DESC

4、ORDER BY子句要放到WHERE子句之後 ：SELECT * FROM T_Employees WHERE Age>23 ORDER BY Age DESC,Salary DESC 

 

萬用字元過濾使用LIKE

1、單字匹配的萬用字元為半形底線"_" ，它匹配單個出現的字元。以任一字元開頭，剩餘部分為"erry" :Select * From T_Employees Where Name Like ‘_erry‘

 

2、多字元匹配的萬用字元為半形百分比符號"%" :它匹配任意次數(零或多個)出現的任一字元。"k%" 匹配以 "k" 開頭、任意長度的字串。檢索姓名中包含字母 ‘n‘ 的員工資訊：Select * From T_Employees Where Name Like ‘n%n‘

3、Like效能較差，很容易造成全表掃描，謹慎使用。

 

null不知道

1、資料庫中，一個列如果沒有指定值，那麼值就為null，資料庫中的null表示“不知道”，而不是表示沒有。因此select null+1結果是null，因為“不知道”加1的結果還是“不知道”。

 

2、SELECT * FROM T_Employees WHERE NAME=null ； SELECT * FROM T_Employees WHERE NAME!=null ；

都沒有任何返回結果，因為資料庫也“不知道”。

3、Select Name+"a" FROM T_Employees 

4、SQL中使用is null、is not null來進行空值判斷： 

(1)    SELECT * FROM T_Employees WHERE NAME is null ； 

(2)    SELECT * FROM T_Employees WHERE NAME is not null ；

 

 

LIMIT擷取部分結果集

1、LIMIT關鍵字用來限制返回的結果集， LIMIT放在SELECT語句的最後位置，文法為“LIMIT 首行行號，要返回的結果集的最大數目” 。

 

比如下面的SQL語句將返回Name不為空白的、按照工資降序排列的從第二行開始（行號從0開始）的最多五條記錄：
SELECT * FROM T_Employees where Name is not null ORDER BY Salary DESC LIMIT 2,5

2、limit一定要放到所有的語句的最後。

GROUP BY 分組查詢

1、資料分組用來將資料分為多個邏輯組，從而可以對每個組進行彙總運算。SQL語句中使用GROUP BY子句進行分組，使用方式為“GROUP BY  分組欄位”。分組語句一般和彙總函式一起使用，GROUP BY子句負責將資料分成邏輯組，而彙總函式則對每一個組進行統計計算。

 

2、查看公司員工有哪些年齡段的：SELECT Age FROM T_Employees GROUP BY Age 

    將Age相同的資料行放到一組，分組後的資料可以看作一個臨時的結果集，而SELECT  Age語句則取出每組的Age欄位的值，這樣我們就得到上表的員工年齡段表了。

 

3、如果SELECT語句有WHERE子句，則GROUP BY子句必須放到WHERE語句的之後。

4、GROUP BY子句將檢索結果劃分為多個組，每個組是所有記錄的一個子集。上面的SQL例子在執行的時候資料庫系統將資料分成了下面的分組：

5、分組後就可以對組內的資料採用彙總函式進行統計了：

1）計算每個分組中的員工平均工資
SELECT Age,AVG(Salary) FROM T_Employees GROUP BY Age

2）查看每個年齡段的員工的人數：
SELECT Age,COUNT(*)  FROM T_Employees GROUP BY Age

Left Join聯集查詢

1、查詢每張訂單的訂單號、價格、對應的客戶姓名以及客戶年齡

 

SELECT o.Number,o.Price,c.Name,c.Age FROM T_Orders o LEFT JOIN T_Customers c ON o.CustomerId=c.Id

2、添加where語句(顯示價格>=150元的訂單)

SELECT o.Number,o.Price,o.CustomerId,c.Name,c.Age  FROM T_Orders o LEFT JOIN T_Customers c ON o.CustomerId WHERE o.Price>=150

3、可以join多張表：

SELECT o.Number 訂單號,o.Price 價格, c.Name 客戶姓名,c.Age 客戶年齡,t.Name 訂單類型  FROM T_Orders o

LEFT JOIN T_Customers c  ON o.CustomerId

LEFT JOIN T_OrderTypes t ON o.TypeId

 

 

外鍵約束

1、如果刪除/更新T_Customers一行記錄，那麼就可能會導致T_Orders中存在CustomerId為非法值的資料，使得程式邏輯錯誤。一般不會更新主鍵Id的值，所以談外鍵約束的時候只談“刪除T_Customers時”。

 

2、外鍵約束：當刪除T_Customers中一條資料的時候，如何處理T_Orders等存在指向T_Customers外鍵的行。外鍵約束建立在外鍵欄位***Id的表上(t_orders)。

3、建外鍵約束的方法：建立或者修改表的時候“外鍵”→“添加外鍵”。名：自動命名即可；欄位名：CustomerId；參考表：t_customers；外欄位名：Id；刪除時、更新時：一般預設RESTRICT(CASCADE:刪除T_Customers一行時把它的訂單也刪除了；SET NULL:刪除T_Customers一行時把它的訂單CustomerId設定為NULL；NO ACTION/RESTRICT：拒絕刪除)。

 

來自為知筆記(Wiz)

第六章、MySQL資料庫入門