資料管理: 資料邏輯 -> 資料庫建置by SQL -> 實戰SQL -> 進階SQL

  • 0
資料管理: 資料邏輯 -> 資料庫建置by SQL -> 實戰SQL -> 進階SQL by TonyQ @ iT邦
摘要

1.建立資料庫
2.建 messageboard 資料表
3.user 使用者表格
4.MessageCategory 留言類別表格
5. 新增類別

CRUD = Create, Read, Update and Delete （建立、讀取、修改、刪除）
= Insert, Select, Update, Delete
Left join, Alias
View, Store Procedure, trigger, count, sum, group by
--

* 資料庫建置by SQL

1.建立資料庫 
create database testdatabase;  

2.建 messageboard 資料表
use testdatabase; -- 指定資料庫  

CREATE TABLE `messageboard` (  
 `messageID` bigint(20) NOT NULL AUTO_INCREMENT,  
 `MessageCategory` bigint(20) DEFAULT NULL,  
 `Author` varchar(100) NOT NULL,  
 `UserId` bigint(20) NOT NULL,  
 `Content` text NOT NULL,  
 `postDate` date DEFAULT NULL,  
 PRIMARY KEY (`messageID`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;  

3.user 使用者表格
CREATE TABLE `user` (  
 `UserId` bigint(20) NOT NULL AUTO_INCREMENT,  -- 使用者 ID  
 `Name` varchar(30) NOT NULL,  -- 姓名  
 `Email` varchar(150) NOT NULL,    
 PRIMARY KEY (`UserId`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;  

4.MessageCategory 留言類別表格
CREATE TABLE `messagecategory` (  
 `messageCategoryID` bigint(20) NOT NULL AUTO_INCREMENT, -- 類別ID  
 `Name` varchar(50) NOT NULL,     -- 類別名稱  
 PRIMARY KEY (`messageCategoryID`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;  

5. 新增類別
1 生活
2 工作
3 聚會
insert into missioncategory values ('生活');  
insert into missioncategory values ('工作');  
insert into missioncategory values ('聚會');  



CRUD = Create, Read, Update and Delete （建立、讀取、修改、刪除）

1. Insert Into <資料表名稱>(<要新增資料表欄位>) Values(<要新增的資料>);  
Ex: insert into messageboard(messageID,MessageCategory,  
          Author,UserId ,Content,postDate )  
    values (null,3,'小王',3,'晚上有沒有空？一起去吃飯？','2012/10/2 17:00:00');  

    insert into messagecategory values (1,'生活');  
    = insert into messagecategory(messageCategoryID,Name) values (1,'生活');  

2. Select <欄位> from <資料表> where <條件式>
Ex: select * from messageboard where messageid = 2 
select * from messageboard where messagecategory =3 and author ='小王'

Select <欄位> from <資料表> <where 條件，如果有的話> Order by <欄位名稱> <ASC,DESC> (從小到大或從大到小)

3. Update <資料表> set <欄位名稱一> = <欄位一資料> , <欄位名稱二> = <欄位二資料> <where 條件判斷>
Ex: update user set name ='test' , email ='test@tonyq.org'
update user set name ='test' , email ='test@tonyq.org' where userid = 2;

4. Delete from <資料表> <where 條件>

--------------------------------------------------------------------------------


select <資料表>.<欄位> , <資料表>.<欄位> ,<資料表>.<欄位> 
from <資料表> left join <資料表> on <資料表關聯條件>
以 Messageboard 資料表為主體(Left)，希望透過這個資料表查詢與其相關的 User 資料表（Right）上的資料。

Ex1. messageboard 額外知道使用者 email 的範例
select MessageBoard.*,User.email 
from Messageboard left join User 
on Messageboard.userid = user.userid ;
-> Messageboard 的資料去查詢 User 的資料表，如果這一筆 Messageboard 的資料在 User 資料表有出現，那就把 User 資料表的 email 也一併顯示

Ex2. 不只希望讀取 email ，還希望顯示類別
select MessageBoard.*,User.email,messagecategory.name
from Messageboard 
left join User 
on Messageboard.userid = user.userid 
left join messagecategory
on Messageboard.MessageCategory = 
messagecategory.messageCategoryID;

-- Alias: 在資料表名稱後面空一格，輸入你希望的別名
select msg.*,u.email,mg.name
from Messageboard msg
left join User u
on msg.userid = u.userid 
left join messagecategory mg
on msg.MessageCategory = 
mg.messageCategoryID;
其中 msg , u , mg 是三個不同資料表的別名

Ex3. 不同資料表的欄位名稱重複？
希望使用 user 資料表的 Name 取代 author 欄位，但是我們上面的查詢已經有一個 name 欄位
select msg.*,u.email,u.name,mg.name as categoryName
from Messageboard msg
left join User u
on msg.userid = u.userid 
left join messagecategory mg
on msg.MessageCategory = 
mg.messageCategoryID;

* View 、 Store Procedure 、 trigger
View 是用來將一些我們常用的查詢預先定義起來的作法。
Store Procedure 是進行定義一些複雜邏輯操作 (if-else ..etc)用的。
trigger 可以幫助我們在資料庫進行資料異動（增刪）時自動進行一些我們設定的行為。
Others - count , sum , group by

沒有留言 :

張貼留言

訂閱： 張貼留言 ( Atom )