如鵬網學習筆記（五）MySql基礎

標籤：過程   ima   說明   多對多   table   執行   mysql   運算子   整數   

MySQL基礎

一、資料庫概念
　　1，網友裝備資訊、論壇文章資訊、QQ好友關係資訊、學籍管理系統中的學生資訊等都要“持久化”的儲存到一個地方，
　　　　如果通過IO寫到檔案中，那麼會非常麻煩，而且不利於多人共用資料

　　2，我們開發大部分軟體、網站都要大量用到書庫，甚至開發遊戲、手機App也要用到資料庫，公司面試的時候資料庫的考核在三分之一以上

　　3，資料庫（DataBase）是儲存資料的倉庫，可以方便的把資料放進去，並且把資料根據各種需要取出來。
　　　　資料庫管理系統（Database Management SYSTEM,DBMS）是對資料庫進行管理（增刪改查等）的軟體，
　　　　常用的DBMS有MYSQL、Oracle、DB2、MSSQLServer等。
　　　　MYSQL是開源、免費的，因此應用最廣泛

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

二、資料庫、表、列

　　1，Table（表）:存放資料的“書架的格子”

　　2，兩種叫法：列（column）/欄位（Field）

三、MYSQL環境安裝

　　1，下載
　　　　http://www.mysql.com/downloads/ MySQL Enterprise Edition (commercial)、MySQL Cluster CGE (commercial)都是收費版；MySQL Community Edition 是開源免費版。

　　2，添加系統服務
　　　　以管理員身份運行命令列，cd到mysql的bin檔案夾，執行“mysqlld -install”

　　　　卸載服務：mysql -remove


四、MYSQL管理工具

　　1，MYSQL管理工具用於對DBMS進行資料庫建立、表建立等管理。開發人員必裝。管理工具不是DBMS，DBMS不可少

　　2，管理工具有很多：Navicat、Workbench(官方)、phpMyAdmin、SQLyog、MySQL-Front，用法大同小異。
　　　　NaviCat lite是免費版，功能學習夠用了。

　　3，安裝後在開始菜單的“PremiumSoft”中。
　　　　主菜單“檔案”→“建立串連”→“MYSQL”，“串連名”隨意，“主機名稱或IP地址”填MYSQLServer的IP地址或者主機名稱，

　　　　裝在本機填127.0.0.1(本地迴環地址)，使用者名稱密碼都是root(生產環境中密碼要改的複雜一點)。

　　4，如果串連不報錯，說明MYSQLServer和管理工具都安裝沒有問題

五、主鍵（PrimaryKey）

　　1，主鍵就是資料行的唯一標識。不會重複的列才能當主鍵。一個表可以沒有主鍵，但是會非常難以處理，
　　　　因此沒有特殊理由表都要設定主鍵

　　2，主鍵有兩種選用策略：
　　　　業務主鍵和邏輯主鍵

　　　　業務主鍵就是使用有業務意義的欄位做主鍵，比如社會安全號碼、銀行帳號等

　　　　邏輯主鍵是使用沒有任務業務意義的欄位做主鍵，完全是給程式看的，業務人員不會看的資料。

　　　　因為很難保證業務主鍵不會重複（社會安全號碼重複）、不會變化（帳號升位），因此推薦用邏輯主鍵


六、表間關聯、外鍵（ForeignKey）

　　1，一張表中的一個欄位是指向了另一張表的主鍵，這樣就將兩張表的資訊串連在一起了

　　2，可以多張表“串聯”，也可以“多對多”的在兩張表之間存在一張關係表


七、建庫建表

　　1，根節點點右鍵，建立資料庫，資料庫名字取得有意義，比如“study1”，字元集建議用UTF-8。

　　2，在study1下的“表”節點下右鍵“建立表”，“欄位”其實指的就是列。

　　　　Id（主鍵，潛規則的名稱，int、不允許為空白、點右鍵“主鍵”）
　　　　Name（nvarchar，長度為10，不允許為空白）
　　　　Gender（bit，不允許為空白）
　　　　儲存為“T_Persons”

　　3，建表常見錯誤：

　　　　列名/表名不要用可能的關鍵字，不要有空格（包括前後）、不要有特殊字元

　　4，MYSQL的儲存引擎有很多，最常用的是InnoDB和MyISAM，MyISAM效率較高，但是不支援事務、外鍵約束等特性，
　　　　因此一般建議用InnoDB，新版本預設也是InnoDB。

　　　　怎麼設：建表時“選項”→“引擎”。建好了如何看引擎：表上點右鍵“對象資訊”。

　　5，常用的資料類型
　　　　文本：
　　　　CHAR(*)：最多255個位元組的定長字串，它的長度必須在建立時指定
　　　　VARCHAR(*)：最多255個位元組的可變長度字串，它的長度必須在建立時指定
　　　　TEXT：最大長度為64K字元的變長文本
　　　　TINYTEXT：最大長度為255字元的變長文本
　　　　MEDUIMTEXT：最大長度為16K字元的變長文本
　　　　LONGTEXT：最大長度為4GB字元的變長文本

 

 

　　　　整數：（考慮資料取值後選擇儘可能小的類型）
　　　　tinyint：1位元組。有符號值：-128 到127；無符號值：0到255
　　　　smallint：1位元組。有符號值：-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

八、SQL語句入門

　　1，不能總是用滑鼠點來點去手動管理資料，要能夠通過語言和資料庫自動交流，SQL語句就是和資料庫“交談”專用的語句

　　2，SQL語句中字串一般用單引號

　　3，SQL語句是大小寫不敏感的

　　4，NavCat中找到執行SQL語句的地方“查詢”——“建立查詢”，編寫SQL後點擊“運行”執行SQL語句

　　5，最簡單的SQL：

　　　　查看一個表的全部資料：select * from T_Persons

　　6，Insert
　　　　簡單的插入資料的SQL語句：
　　　　insert into T_Persons (Id,Name,Age,Gender) values(5,‘jim‘,20,1)

　　　　Insert語句可以省略表名後的列名，但是強烈不推薦

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

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

　　7，修改表結構的方法：點“設計表”

　　　　1，把“允許為空白”的欄位修改為“不允許為空白”，需要先給舊資料預設值(用Update語句)，
　　　　　　如果之前是測試資料無所謂，可以把舊資料都刪除(增加Height欄位)

　　　　2，修改列的資料類型要注意舊資料能否相容轉換為新類型；修改資料的長度的時候也是如此

　　8，資料更新

　　　　更新一個列：
　　　　update T_Persons Set Age=30

　　　　更新多個列：
　　　　update T_Persons s Set Age=30，Name=‘tom‘

　　　　運算式：UPDATE T_Persons Set Age=Age+1

　　9，條件資料更新

　　　　更新一部分資料： 
　　　　UPDATE T_Persons Set Age=30 where Name=‘tom‘，用where語句表示只更新Name是‘tom‘的行，
　　　　注意SQL中等於判斷用單個=，而不是==。

　　　　Where中還可以使用複雜的邏輯判斷UPDATE T_Persons Set Age=30 where Name=‘tom‘ or Age<25，or相當於Java中的||

　　　　where (Age>20 and Age<30) or(Age=80)

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

　　10，資料刪除
　　　　刪除表中全部資料:
　　　　DELETE from T_Persons

　　　　刪除表中部分資料
　　　　Delete from T_Persons where Age>20

　　　　DELETE只是刪除資料，表還在，

　　11，刪除表
　　　　Drop table T_Persons

九、資料檢索
　　1，簡單的資料檢索：Select * from T_Persons

　　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都行

　　4，計算資料行
　　　　SELECT Number 編號,Name 姓名,Age Age111,Age+10 十年後的年齡,1+1,now() FROM T_Employees

　　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

十一、資料排序

　　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

十二、萬用字元過濾

　　1，萬用字元過濾使用like

　　2，單字元匹配的萬用字元為半形底線“_”，它匹配單個出現的字元。
　　　　以任一字元開頭，剩餘部分為“erry”：
　　　　Select * from T_Employees where Name like ‘_erry‘

　　3，多字元匹配的萬用字元為半形百分比符號“%”，它匹配任意次數（0或者多個）出現的任一字元。
　　　　k% 匹配以k開頭、任意長度的字串
　　　　檢索姓名中包含字母n的員工資訊：
　　　　select * from T_Employees where name like ‘%n%‘

　　4，like效能較差，很容易造成全表掃描，謹慎使用。
　　　　後面會講資料庫最佳化（索引等），項目中做搜尋用全文索引

十三、空值處理

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

　　2，Select * from T_Employees where name = null;
　　　　select * from T_Employees where name!=null;
　　　　都沒有任何返回結果，因為資料庫“也不知道”
　　　　select name+"a" from T_Employees

　　3，SQL中使用is null、is not null 來進行空值判斷
　　　　SELECT * FROM T_Employees WHERE NAME is null ； 
　　　　SELECT * FROM T_Employees WHERE NAME is not null ；

十四、limit

　　limit關鍵字用來限制返回的結果集，limit放在select語句的最後位置，
　　文法為：limit 首行行號，要返回的結果集的最大數目

　　比如下面的SQL語句將返回Name不為空白的、按照工資降序排列的從第二行開始（行號從0開始）的最多五條記錄
　　select 8 from T_Employees
　　where Name is not null
　　order by Salary desc
　　limit 2,5

　　注意：limit一定要放到所有的語句的最後

　　使用情境：開發網站、開發手機程式的時候分頁用的非常多

十五、group by

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

　　2，查看公司員工有哪些年齡段的：
　　　　select Age from T_Employees group by Age

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

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

　　5，group by子句將檢索結果劃分為多個組，每個組是所有記錄的一個子集。

十六、group by與彙總函式

　　1，分組後就可以對組內的資料採用彙總函式進行統計了；

　　　　計算每個分組中員工的平均工資：
　　　　select Age,avg(salary) from T_Employees
　　　　group by Age

　　　　查看每個年齡段的員工的人數：
　　　　select Age,count(*) from T_Employees 
　　　　group by Age

十七、join
　　1，真是的業務系統中，各個表之間都存在這種聯絡，很少存在不與其他表存在關聯關係的表，
　　　　而在實現業務功能的時候也經常需要從多個表中進行資料的檢索，而進行多表檢索最常用的技術就是表串連

　　2，如果沒有表串連，那麼查詢每張訂單的客戶姓名就要先查詢訂單，再去查詢客戶表，麻煩而且效率低

　　3，SQL中使用JOIN關鍵字來進行表串連。
　　　　表串連有多種不同的類型，被主流資料庫系統支援的有交叉串連（CROSS JOIN）、內串連（INNER JOIN）、外串連（OUTTER JOIN）

　　　　外串連分為：left join、right join。

十八、外鍵約束

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

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

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

十九、擴充學習資料
　　1，DDL：

　　2，預存程序、觸發器、約束、子查詢、處理left join之外其他的join

如鵬網學習筆記（五）MySql基礎