資料庫系統 Database System Week3

Presentation on theme: "資料庫系統 Database System Week3"— Presentation transcript:

1 資料庫系統 Database System Week3

2 本周大綱 第一節 SELECT補充(NOT、IS、時間處理、字串處理) CREATE Table / Database
INSERT、UPDATE、DELEATE 第二節 前言 在程式中連接資料庫(C#、PHP、JAVA、Android) 第三節 考試流程及相關規定 模擬考 題目講解

3 補充-1 NOT運算符 不只可以用在IN 也可以用在其他運算上 IS 運算符 IS NULL 、 IS True 、 IS False
SELECT Name,Phone FROM databasesystem1061.student WHERE Phone IS NULL;

4 補充-2 字串處理 脫逸字元 萬用字元 %、_ 如果要比對有沒有%或_的字元，則要再加上\
SELECT * FROM databasesystem1061.student WHERE phone like "%8%8%"; SELECT * FROM databasesystem1061.student WHERE phone like "%8_8%";

5 補充-3 字串處理 binary 強制大小寫比較 UCASE( ) / LOWER( ) 小寫轉大寫 / 大寫轉小寫
小寫轉大寫 / 大寫轉小寫 SELECT StudentNum FROM databasesystem1061.student WHERE StudentNum like "%m%"; SELECT StudentNum FROM databasesystem1061.student WHERE StudentNum like binary "%m%"; SELECT LOWER( StudentNum) from databasesystem1061.student;

6 補充-3 字串處理 處理函數 LENGTH( ) 取得字串長度 CONCAT( ) 字串連接
TRIM( )、 RTRIM( )、 LTRIM( ) 移除掉一個字串中字頭字尾的空白 SUBSTRING( ) 切割字串 REPLACE( ) 取代字串

7 補充-3 字串處理 LENGTH( ) SELECT LENGTH(phone) FROM databasesystem1061.student; CONCAT( ) SELECT CONCAT(Name,Phone,Gender) AS 'all' FROM databasesystem1061.student ; SUBSTRING( ) SELECT SUBSTRING(phone,3,10) FROM databasesystem1061.student; REPLACE() SELECT replace(phone,‘09’,‘+8869’) FROM databasesystem1061.teacher; 會有問題

8 補充-3 字串處理 TRIM([ {BOTH | LEADING | TRAILING} [String] FROM ] String)
可以選擇同時、去除開頭、去除結尾的特定字串 SELECT TRIM(LEADING '09' FROM phone) from databasesystem1061.student; RTRIM( ) SELECT RTRIM(' HELLO ') ; LTRIM( ) SELECT LENGTH(LTRIM(' HELLO ') ); SELECT LENGTH(' HELLO ‘);

9 補充-3 字串處理 避免明文儲存密碼 在SQL中使用雜湊函數MD5(原本密碼)

10 補充-4 時間處理 DAYOFWEEK( ) 當天為星期幾 1等於星期日 TIMEDIFF( ) 時間差異 DATEDIFF( ) 天數差異
NOW( ) 服務器當前日期時間 CURTIME( ) 服務器當前時間 CURDATE( ) 服務器當前日期 ADDDATE( ) / ADDTIME( ) 回傳增加後的時間 SUBDATE( ) / SUBTIME( ) 回傳減少後的時間

11 補充-4 時間處理 NOW( ) SELECT NOW() ; CURTIME( ) SELECT CURTIME() ;
CURDATE( ) SELECT CURDATE() ; DAYOFWEEK( ) SELECT DAYOFWEEK(student.Birthday) from databasesystem1061.student; TIMEDIFF( ) SELECT TIMEDIFF(NOW(),NOW() - interval 2 minute) ; DATEDIFF( ) SELECT DATEDIFF(NOW(),birthday) from student ;

12 補充-4 時間處理 ADDDATE( ) / ADDTIME( )
SELECT adddate(student.Birthday,interval 30 minute ) from databasesystem1061.student; SELECT addtime(student.Birthday, '00:30:00') from databasesystem1061.student; SUBDATE( ) / SUBTIME( ) SELECT subdate(student.Birthday,interval 30 minute ) from databasesystem1061.student; SELECT subtime(student.Birthday, '00:30:00') from databasesystem1061.student;

13 Create Database create database 資料庫名字; create database school; Server

14 在Create table 之前 常用資料格式: 數字 字串 N：支援UNICODE VAR：可變長度 CHAR NCHAR VARCHAR
INT FLOAT DOUBLE 字串 N：支援UNICODE VAR：可變長度 CHAR NCHAR VARCHAR NVARCHAR

15 在Create table 之前 常用資料格式 時間 布林 Mysql沒有布林專用的欄位，所以通常會使用TINYINT格式(0~255)
TIME DATE DATETIME 布林 Mysql沒有布林專用的欄位，所以通常會使用TINYINT格式(0~255)

16 在Create table 之前 常用資料表欄位選項 PRIMARY KEY NOT NULL Unique UNSIGNED
DEFAULT AUTO INCREMENT

17 Create table CREATE TABLE 表格名 ( 欄位名1 INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 欄位名2 VARCHAR(30) NOT NULL, 欄位名3 VARCHAR(30) NOT NULL DEFAULT '預設值', 欄位名4 VARCHAR(50), 欄位名5 DATETIME FOREIGN KEY (欄位名2) REFERENCES 表格2(該欄位名) )

18 INSERT、UPDATE、DELEATE
INSERT INTO 表格名 (欄位1, 欄位2, 欄位3, ...) VALUES (資料1, 資料2, 資料3, ...); INSERT INTO 表格名 VALUES (欄位1, 欄位2, 欄位3, ...); UPDATE UPDATE 表格名 SET 欄位1  = 資料1, 欄位2 = 資料2, ... WHERE 條件; DELEATE DELETE FROM 表格名 WHERE 條件; DELETE FROM 表格名; DELETE * FROM 表格名;

19 練習 列出所有手機不是NULL的學生的手機與姓名 查詢學生的手機及姓名，但是將手機前面的0以+886取代(請排除NULL的學生)
(使用Relpace有問題，試試其他函數) 列出所有在星期六出生的學生 將學生的身分證字號以MD5雜湊函數顯示出來 計算出學生的生日跟現在的時間平均差距幾天

20 練習 第一題(部分顯示) 第二題(部分顯示) 第三題 第四題(部分顯示) 第五題

21 練習 自行練習 為自己小組做出來的Relation中的每一個欄位規劃Data Type 嘗試看看把自己小組的資料庫/表建出來

22 第二節 程式中連接SQL Server

23 前言 資料庫隱碼攻擊(SQL injection)
strSQL = "SELECT * FROM users WHERE (name = '" + userName + "') and (pw = '"+ passWord +"');" userName = "1' OR '1'='1"; passWord = "1' OR '1'='1"; strSQL = "SELECT * FROM users WHERE (name = '1' OR '1'='1') and (pw = '1' OR '1'='1');" strSQL = "SELECT * FROM users;"

24 C#中連接服務器

25 PHP中連接服務器

26 JAVA中連接服務器

27 Android中連接服務器 由於Anndroid APP由於容易拆解的原因，因此將連接Server的連接資訊就極度不宜放在app中，
因此目前大多數使用的方法都是利用post網頁的方式將需要的資訊傳給網頁，網頁進行查詢後，再將 所查詢的資料回傳給APP。

28 Android中連接服務器 PHP 向 Server 查詢 APP以POST呼叫控制器 呼叫控制器 使用服務 服務回傳 控制器回傳
POST to PHP PHP 向 Server 查詢 Server回傳PHP PHP回傳給程式

29 第三節 考試相關

30 考試 考試時一題最多12分鐘，總共五題，一題滿分20分 3分鐘內答題 20 分 5分鐘內答題 17 分
3分鐘內答題 20 分 分鐘內答題 17 分 7分鐘內答題 14 分 分鐘內答題 12 分 12分鐘內答題 10 分 超過12分鐘0分計 考試當天有監考，答題後舉手，負責學長姐會去確認答案正 確與否