新增SQL語法 (2010/11/26)

SELECT A.CLASS_ID,CLASS_NAME,CLASS_TEACHER,DAY,ROOM FROM courses_new JOIN
(SELECT CLASS_ID FROM schedule WHERE S_ID = '496511126') A
WHERE A.CLASS_ID = courses_new.CLASS_ID;

==>使用"學號" 查詢該學生的課表(選修的課) 可在SELECT調整需要的欄位
    


SELECT CLASS_NAME FROM (SELECT A.CLASS_ID,CLASS_NAME,CLASS_TEACHER,DAY,ROOM,START_TIME,END_TIME FROM courses_new JOIN
(SELECT CLASS_ID FROM schedule WHERE S_ID = '496511126') A
WHERE A.CLASS_ID = courses_new.CLASS_ID ) B
WHERE DAY = DATE_FORMAT( NOW(), '%a' )) AND ( CURTIME() BETWEEN START_TIME AND END_TIME);

==>使用"現在時間(格式:xx:yy:zz)" 來查詢 此時此課該學生要上的課







SELECT CLASS_NAME FROM courses_new JOIN (SELECT CLASS_ID FROM open WHERE T_ID = 1) A
WHERE A.CLASS_ID = courses_new.CLASS_ID;

==>使用 老師的T_ID 來查詢 老師所有開的課程名單





SELECT A.S_ID, DEPARTMENT, STUDENT_NAME FROM student JOIN (SELECT * FROM schedule s WHERE CLASS_ID = 2) A
WHERE A.S_ID = student.S_ID;

==>使用 CLASS_ID 來查詢 修課同學名單



SELECT * FROM sign_new s WHERE S_ID = 496511126 AND ROOM = 'SF648' AND DATE_FORMAT(ENTER_TIME,'%Y-%m-%d-') = DATE_FORMAT(NOW(),'%Y-%m-%d-') AND CLASS_ID = 27;


==>判斷此學生 在今天(日期)與CLASS_ID與學號 去判斷是否已簽到過??




SELECT ENTER_TIME FROM sign_new s WHERE DATE_FORMAT(ENTER_TIME,'%Y-%m-%d') = CURDATE() AND CLASS_ID = 27;

==>

SELECT DISTINCT DATE_FORMAT(ENTER_TIME,'%Y-%m-%d') FROM sign_new WHERE CLASS_ID = 27 ORDER BY ENTER_TIME

==>回傳該堂課的(今天以前的)上課日期

SELECT DEPARTMENT, S_ID, STUDENT_NAME, DATE_FORMAT(ENTER_TIME,'%T'),IS_SIGN FROM sign_new WHERE CLASS_ID = 27 AND DATE_FORMAT(ENTER_TIME,'%Y-%m-%d') = '2010-11-27' AND IS_SIGN = '已簽到' ORDER BY ENTER_TIME

==>回傳 該堂課該日期 的所有已簽到學生



SELECT * FROM (SELECT A.S_ID, DEPARTMENT, STUDENT_NAME FROM student JOIN (SELECT * FROM schedule s WHERE CLASS_ID = 2) A
WHERE A.S_ID = student.S_ID) B
WHERE B.STUDENT_NAME
NOT IN
(SELECT STUDENT_NAME FROM sign_new WHERE CLASS_ID = 2 AND DATE_FORMAT(ENTER_TIME,'%Y-%m-%d') = '2010-11-27' AND IS_SIGN = '已簽到' ORDER BY ENTER_TIME);

==>該堂課該日期 未到名單    修課名單-已到名單



UPDATE sign_new SET IS_SIGN = '未到' WHERE CLASS_ID = 27 AND S_ID = 496511126 AND DATE_FORMAT(ENTER_TIME,'%Y-%m-%d') = '2010-11-27'

取得 此時此刻的時間 & 星期幾,此時

SELECT DATE_FORMAT( NOW(), '%a' ) , DATE_FORMAT( NOW(), '%H' ) ;

回傳：  星期幾&此時

SELECT NOW();
SELECT CURRENT_TIMESTAMP;

回傳：  ex. 2010-11-26 16:44:21

MySQL DATETIME 時間

MySQL Date 函數

下面的表格列出了MySQL 中最重要的內建日期函數：

SQL Date 數據類型

MySQL 使用下列數據類型在數據庫中存儲日期或日期/時間值：

• DATE - 格式YYYY-MM-DD
• DATETIME - 格式: YYYY-MM-DD HH:MM:SS
• TIMESTAMP - 格式: YYYY-MM-DD HH:MM:SS
• YEAR - 格式YYYY 或YY

SQL Server 使用下列數據類型在數據庫中存儲日期或日期/時間值：

• DATE - 格式YYYY-MM-DD
• DATETIME - 格式: YYYY-MM-DD HH:MM:SS
• SMALLDATETIME - 格式: YYYY-MM-DD HH:MM:SS
• TIMESTAMP - 格式: 唯一的數字

例子 1

下面是SELECT 語句：

SELECT NOW(),CURDATE(),CURTIME()

結果類似：

NOW()                                      CURDATE()                      CURTIME()

2008-12-29 16:25:46            2008-12-29                     16:25:46

如果需要INSERT時，系統自動加入時間，要將DATETIME欄位的default value設為NOW();

但是如果這樣執行時會跳出錯誤訊息，因為MySQL的文件上面有說，default value只能設為常數不能設為function。

"The DEFAULT value  clause in a data type specification indicates a default value for a
column. With one exception, the default value must be a constant; it cannot be a function
or an expression. This means, for example, that you cannot set the default for a date
column to be the value of a function such as NOW()  or CURRENT_DATE."

後來爬了爬文，有網友提供另一種和DATETIME幾乎類似的方法

－＞使用TIMESTAMP型態的column，將其default value設定CURRENT_TIMESTAMP。

每次在TABLE裡面新增一筆資料時，系統會自動在該欄位加入目前時間。

這樣在MySQL環境下是可以成功執行的。

參考資料:

http://www.w3school.com.cn/sql/sql_dates.asp

http://bugs.mysql.com/bug.php?id=27645