雲端教學課程

MYSQL基礎教學

先到的同學先把WAMP、Navicat 安裝起來。

你的資料庫

其實已經架好了

我們來用phpmyadmin看

http://localhost/phpmyadmin/

資料庫基本概念

我們拿Excel來舉例，這資料各位應該都看過。

資料庫基本概念

所以各位都很優秀，入學前就會資料庫了。

資料庫基本概念

索引

主要且通常不重複的資料欄位( EX : 學號、身份證字號)

用意是為了加速搜尋，

所以每個資料表都會至少有一個索引欄位。

通常主鍵(PRIMARY Key)會讓資料庫自動編號；

如果網站有提供會員暱稱，也會把會員暱稱設為索引。

http://miggo.pixnet.net/blog/post/30862194

建立資料庫結構

開啟 phpmyadmin

http://localhost/phpmyadmin/

先把語言設成中文

建立資料庫結構

建立資料庫

名稱 : blog
編碼 : utf8_unicode_ci

建立資料庫結構

建立資料表

SQL操作資料表

新增 insrt
修改 updata
刪除 delete
查詢 select

SQL - 新增資料

INSERT

　INSERT INTO `表格名`(　`欄位1`,　`欄位2`,　...)VALUES(　'值1',　'值2',　...);

欄位名稱跟資料表名稱要用 ` 這是一個欄位 ` 框起來
字串要用 ' 這是一個字串 ' 框起來

SQL - 新增資料

INSERT

新增一筆資料到message資料表

INSERT INTO  `message` (
　`time` ,
　`message`
)
VALUES (
　NOW( ) , 'Hello message!'
)

NOW( ) 是一個回傳現在時間點的函數，

我們可以直接插入現在的時間點。

sql - 新增資料

INSERT

新增多筆資料到 message

INSERT INTO  `message` (
　`time` ,
　`message`
)
VALUES (
　NOW( ) ,  'Hello message \'A\' !'
), (
　NOW( ) ,  'Hello message \'B\' !'
)

資料裡如果有 ' 要用 \' 來跳脫。

這裡有全部的跳脫字元。

http://dev.mysql.com/doc/refman/4.1/en/string-literals.html

SQL - 修改資料

UPDATE

UPDATE　`表格名`SET 　`欄位1` = 新值1,　`欄位2` = 新值2WHERE　‵欄位3‵ = 1;

SQL - 修改資料

UPDATE

修改message資料表index=1的資料

UPDATE　`message`SET 　`time` = NOW(),　`message` = '修改留言!!'WHERE　`index`=1;

SQL - 刪除資料

DELETE

DELETE FROM　‵表格名‵
WHERE　{條件};

SQL - 刪除資料

DELETE

從message資料表刪除index=1的資料

DELETE FROM
　`message`
WHERE
　`index`=1;

SQL - 查詢資料

SELECT

SELECT　`欄位1`,　`欄位2`,　...FROM　"表格名";

查詢資料

SELECT - *

SELECT　*FROM　`message`

* 表示把所有欄位都查出來

查詢資料

SELECT - where

SELECT　*FROM　`message`WHERE　`index` = 3

用 where 指定查詢資料

查詢資料

SELECT - and

SELECT　*FROM　`message`WHERE　　　　`index` > 1　AND　`time` >= '2013-07-22 19:45:00'

用 AND / OR/LIKE指定查詢資料，

或是用BETWEEN來查詢區間資料。

http://www.1keydata.com/tw/sql/sqlbetween.html

查詢資料

SELECT - ORDER BY

SELECT　*FROM　`message`WHERE　　　　`index` > 1　AND　`time` => '2013-07-22 19:45:00'ORDER BY　`index` DESC

ASC是由小到大排序，DESC是由大到小排序

 ORDER BY `欄位一` [ASC / DESC], `欄位二` [ASC / DESC]

查詢資料

SELECT - 函數

SELECT　COUNT(`message`)FROM　`message`

COUNT(`message`)

計算出所有 message欄位不是空值的筆數，

當然也可以用 COUNT(*)，就會計算出所有查到的筆數。

AVG (平均)、COUNT (計數)、

MAX (最大值)、MIN (最小值)、SUM (總合)

查詢資料

SELECT - GROUP BY

SELECT　*,count(*)FROM　`message`GROUP BY　`time`

把資料群組起來 (顯示第一筆)，

再用函數去計算每個群組的筆數。

http://www.1keydata.com/tw/sql/sqlgroupby.html

查詢資料

SELECT - HAVING

SELECT　*,count(*)FROM　`message`GROUP BY　`time`
HAVING
　count(*) >  3

如果我們把函數放在WHERE裡會出現 :

"Invalid use of group function "

這時我們就要把他放在HAVING裡。

查詢資料

SELECT - 欄位別名 AS

SELECT　COUNT(`message`) AS `C`FROM　`message`

資料庫基本概念

關聯

建立一個外連鍵，專門用來連結其他資料表的，

在查詢時把對應的欄位連起來。

資料庫基本概念

正規化 : 減少資料內容重複

( 原本資料表 )

資料庫基本概念

正規化

資料庫基本概念

正規化

mYSQL CLIENT

在開始之前，先下載這工具來連接你的資料庫

Navicat for MySQL

http://www.navicat.com/cht/download

MYSQL CLIENT

主機名稱 : localhost
使用者名稱 : root
密碼 : [空]

MYSQL CLIENT

匯入匯出

http://nens.no-ip.org/teach.sql

資料表連接

資料表連接

Inner Join ( 聯集 )

只有對應到的資料才會被查詢出來。

( 把對應的欄位連結起來 )

資料表連接

Inner Join

( 查詢結果 )

資料表連接

Inner Join

建立資料表 ( 記得主鍵要勾自動遞增 )

student

score

資料表連接

Inner Join

建立測試資料 ( 可以複製貼上 )

( 學生資料表 )

( 成績資料表 )

資料表連接

Inner Join 語法

SELECT
　`資料表1`.`欄位2`,
　`資料表2`.`欄位3`,
　`資料表2`.`欄位4` 
FROM
　`資料表1`
　Inner Join 
　　`資料表2`
　　ON
　　　　/* ON後面接 {聯集條件} */
　　　　　　`資料表1`.`欄位1` = `資料表2`.`欄位2`
　　　　AND `資料表1`.`欄位2` = `資料表2`.`欄位3`

SQL註解方式

單行註解 #註解內容、 -- 註解
區間註解 /* 註解的內容 */

資料表連接

Inner Join

SELECT
　student.student_name,
　score.score,
　score.`subject`
FROM
　student
　Inner Join
　 　　score
　　　ON 
　　　　student.student_id = score.student_id

資料表連接

Inner Join ( 命名 )

SELECT
　`學生`.`student_id` AS `編號`,
　`學生`.`student_name` AS `名字`,
　`成績`.`subject` AS `科目`,
　`成績`.`score` AS `得分`
FROM
　`student` AS `學生`
　Inner Join
　 　　`score` AS `成績`
　　　ON 
　　　　`學生`.`student_id` = `成績`.`student_id`

資料表連接

INNER JOIN

( 查詢結果 )

資料表連接

正規化

+

=

成績 (原始)

成績 ( 正規化後 )

( 科目 )

資料表連接

正規化

SELECT
　`學生`.`student_id` AS `編號`,
　`學生`.`student_name` AS `名字`,
　`科目`.`subject` AS `科目`,
　`成績`.`score` AS `得分`
FROM
　`student` AS `學生`
　Inner Join
　 　　`score` AS `成績`
　　　ON 
　　　　`學生`.`student_id` = `成績`.`student_id`
　Inner Join
　 　　`subject` AS `科目`
　　　ON 
　　　　`成績`.`subject` = `科目`.`subject_id`

資料表連接

Left Join / Right Join

SELECT
　*
FROM
　`TABLE_A` AS `LEFT_TABLE`
　Left Join
　 　　`TABLE_B` AS `RIGHT_TABLE`
　　　ON 
　　　　`LEFT_TABLE`.`student_id` = `RIGHT_TABLE`.`student_id`

資料表連接

Left Join / Right Join

先在student資料表新增性別(sex_id)欄位
新增一個性別(sex)資料表，記得主鍵要勾自動遞增

資料表連接

Left Join / Right Join

填入資料到性別(sex)資料表
把資料表連起來

資料表連接

Left Join / Right Join

SELECT
　`學生`.`student_id` AS `編號`,
　`學生`.`student_name` AS `名字`,
　`性別`.‵sex‵ AS `性別`,
　`科目`.`subject` AS `科目`,
　`成績`.`score` AS `得分`　FROM
　`student` AS `學生`
　Inner Join
　 　　`score` AS `成績`
　　　ON 
　　　　`學生`.`student_id` = `成績`.`student_id`
　Inner Join
　 　　`subject` AS `科目`
　　　ON 
　　　　`成績`.`subject` = `科目`.`subject_id`
　Left Join
　 　　`sex` AS `性別`
　　　ON 
　　　　`性別`.sex_id = `學生`.`sex_id`

資料表連接

Left Join / Right Join

因為是Left Join，沒資料時就會直接顯示(Null)，

可以比較一下 Inner Join 跟 Left Join 的差別，再輸入資料。

資料表連接

判斷式 IF( {判斷內容} , { True } , { False } )

SELECT
　`學生`.`student_id` AS `編號`,
　`學生`.`student_name` AS `名字`,
　IF(`性別`.sex Is Null,'未輸入',`性別`.sex ) AS `性別`,
　`科目`.`subject` AS `科目`,
　`成績`.`score` AS `得分`　FROM
　`student` AS `學生`
　Inner Join
　 　　`score` AS `成績`
　　　ON 
　　　　`學生`.`student_id` = `成績`.`student_id`
　Inner Join
　 　　`subject` AS `科目`
　　　ON 
　　　　`成績`.`subject` = `科目`.`subject_id`
　Left Join
　 　　`sex` AS `性別`
　　　ON 
　　　　`性別`.sex_id = `學生`.`sex_id`

SELECT語法順序

http://dev.mysql.com/doc/refman/5.0/en/select.html

SQLECT語法順序

剛那是給高手看的...實際上只要記得這順序就行。

SELECT　{要查詢的欄位}
FROM　{要查詢的資料表}
WHERE　{查詢條件}
GROUP BY　{以...為群組}
HAVING　{函數條件}
ORDER BY　{以...欄位排序}
LIMIT
　開始位置,查詢數量

子查詢

Subquery

http://www.zentut.com/sql-tutorial/sql-subquery/

子查詢

Subquery

SELECT
　`學生`.`student_id` AS `編號`,
　`學生`.`student_name` AS `名字`,
　IF(`性別`.sex Is Null,'未輸入',`性別`.sex ) AS `性別`,
　`科目`.`subject` AS `科目`,
　`成績`.`score` AS `得分`　FROM
　`student` AS `學生`
　Inner Join `score` AS `成績`ON `學生`.`student_id` =`成績`.`student_id`
　Inner Join `subject` AS `科目` ON `成績`.`subject` = `科目`.`subject_id`
　Left Join `sex` AS `性別` ON `性別`.sex_id = `學生`.`sex_id`
WHERE
　`成績`.‵score‵>(SELECT　　　　　　　　　
　　　　　　　　　 　AVG(‵score`.`score`)　
　　　　　　　　　 FROM　　　　　　　　　　
　　　　　　　　　 　`score`　　　　　　　
　　　　　　　　　)

子查詢

Subquery + 變數

SELECT
　`學生`.`student_id` AS `編號`,
　`學生`.`student_name` AS `名字`,
　IF(`性別`.sex Is Null,'未輸入',`性別`.sex ) AS `性別`,
　`科目`.`subject` AS `科目`,
　`成績`.`score` AS `得分`,
　IF(`成績`.`score` = (SELECT　　　　　　　　　　　　　　　　　　　　　　
　　　　　　　　　 　　　　MAX(‵score`.`score`)　　　　　　　　　　　　　　
　　　　　　　　　 　　　FROM　　　　　　　　　　　　　　　　　　　　　　　
　　　　　　　　　 　　　　`score`　　　　　　　　　　　　　　　　　　　　　
　　　　　　　　　 　　　WHERE　　　　　　　　　　　　　　　　　　　　　　　
　　　　　　　　　 　　　　`學生`.`student_id` =`score`.`student_id`　
　　　　　　　　　　　　　),'是','否') AS `是否為最高分科目`　FROM
　`student` AS `學生`
　Inner Join `score` AS `成績`ON `學生`.`student_id` =`成績`.`student_id`
　Inner Join `subject` AS `科目` ON `成績`.`subject` = `科目`.`subject_id`
　Left Join `sex` AS `性別` ON `性別`.sex_id = `學生`.`sex_id`

子查詢

Subquery

當然也能放在JOIN的地方，

還有更多範例，就交給各位同學回去複習了！

Subqueries in MySQL

http://dev.mysql.com/tech-resources/articles/subqueries_part_1.html

MySQL Subquery

http://www.mysqltutorial.org/mysql-subquery/

檢視表

把常用的的查詢子句寫成View，

可以減少每次查詢的時間，也提升寫程式效率。

http://msdn.microsoft.com/en-us/library/aa933143(v=sql.80).aspx

檢視表

View

很簡單，不過Mysql有個限制，

就是檢視表裡不能有子查詢。

但這問題能用檢視表包檢視表的方式解決，

命名規則要處理好。

檢視表

把這段很眼熟的SQL貼到檢視表後按儲存(v_student)就行。

SELECT
　`學生`.`student_id` AS `編號`,
　`學生`.`student_name` AS `名字`,
　IF(`性別`.sex Is Null,'未輸入',`性別`.sex ) AS `性別`,
　`科目`.`subject` AS `科目`,
　`成績`.`score` AS `得分`　FROM
　`student` AS `學生`
　Inner Join
　 　　`score` AS `成績`
　　　ON 
　　　　`學生`.`student_id` = `成績`.`student_id`
　Inner Join
　 　　`subject` AS `科目`
　　　ON 
　　　　`成績`.`subject` = `科目`.`subject_id`
　Left Join
　 　　`sex` AS `性別`
　　　ON 
　　　　`性別`.sex_id = `學生`.`sex_id`

檢視表

View

建立student_max_score

select 
　`student_id` AS `student_id`,
　max(`score`) AS `MAX` 
from 
　`score` 
group by 
　`student_id`

檢視表

View

把v_student修改為

SELECT
　`學生`.`student_id` AS `編號`,
　`學生`.`student_name` AS `名字`,
　IF(`性別`.sex Is Null,'未輸入',`性別`.sex ) AS `性別`,
　`科目`.`subject` AS `科目`,
　`成績`.`score` AS `得分`　
　IF(`成績`.`score` =`最高分科目分數`.`MAX`,'是','否') AS `是否為最高分科目`FROM
　`student` AS `學生`
　Inner Join `score` AS `成績` ON `學生`.`student_id` =`成績`.`student_id`
　Inner Join `subject` AS `科目` ON `成績`.`subject` = `科目`.`subject_id`
　Left Join `sex` AS `性別` ON `性別`.sex_id = `學生`.`sex_id`
　Inner Join `student_max_score`  AS `最高分科目分數`
　　　　　　　　　ON `最高分科目分數`.`student_id` = `學生`.`student_id`

最後

資料庫其實還有很多東西，交給同學回去研究了。

預存程序
事件
...

mysql 讓一個預存程序定時作業

只談MySQL (第N天)