資料庫系統_答案 Database System Week3
練習 列出所有手機不是NULL的學生的手機與姓名 查詢學生的手機及姓名，但是將手機前面的0以+886取代(請排除NULL的學生) (使用Relpace有問題，試試其他函數) 列出所有在星期六出生的學生 將學生的身分證字號以MD5雜湊函數顯示出來 計算出學生的生日跟現在的時間平均差距幾天
練習題答案 SELECT Name,Phone FROM databasesystem1061.student WHERE Phone IS NOT NULL; SELECT Name,CONCAT('+886',TRIM(LEADING '0' FROM phone)) as '手機號碼' FROM databasesystem1061.student WHERE Phone IS NOT NULL; SELECT Name FROM databasesystem1061.student WHERE DAYOFWEEK(Birthday)=7; SELECT MD5(IDCardNum) as '雜湊函數' FROM databasesystem1061.student ; SELECT AVG(datediff(NOW(),Birthday)) as '跟現在平均相差天數' FROM databasesystem1061.student ;
模擬考題目 請列出使用者帳號GT1fO9L61v的好友帳號列表(僅顯示friendid) 請列出發送訊息的時間介於2013-01-01至2013-12-31的訊息id(僅顯示messageid) 請列出所有貼文中按讚數量大於20的貼文，並將按讚數量的欄位名稱改成LikeNumber(僅顯示 postid,LikeNumber) 請查看目前”沒有”按”任何貼文”讚的帳號(userid) 請列出社團id為7的成員中”電話號碼與生日都有填”的”名單及資料”並以”加入時間”由早到晚做 排序(userid,datetime,firstname,lastname,phone,location,birthday,gender)
題目範圍 WHERE 條件查詢 Between AND Group by , Having , Count() , 比較運算子 , AS NOT IN 與 Subquery JOIN , AND , ORDER BY , NULL , NOT
模擬考答案 SELECT friendid FROM communitywebsite.friendsof WHERE userid = 'GT1fO9L61v'; SELECT messageid FROM communitywebsite.messqgerecord where datetime between '2013-01-01' AND '2013-12-31' ; SELECT postid,COUNT(postid) FROM communitywebsite.postlike group by postid having count(postid)>20;
模擬考答案 SELECT userid FROM users WHERE userid NOT IN ( SELECT userid FROM pagelike );
模擬考答案 SELECT groupmember.userid,datetime,firstname,lastname,phone,location, birthday , gender FROM groupmember INNER JOIN information ON (groupmember.userid = information.userid AND groupmember.groupid=7 AND phone IS NOT NULL AND birthday IS NOT null )ORDER BY datetime ASC;