11. 臨時表

-- 查詢5大洲國家總數
SELECT continent,COUNT(*) FROM country GROUP BY continent;-- 演示臨時表 
CREATE TEMPORARY TABLE tmp_table
( 
continent VARCHAR(20),
COUNT INT
);INSERT INTO tmp_table SELECT 'Asia' AS 'continent',COUNT(*) FROM country WHERE continent = 'Asia';
INSERT INTO tmp_table SELECT 'North America' AS 'continent',COUNT(*) FROM country WHERE continent = 'North America';
INSERT INTO tmp_table SELECT 'Europe' AS 'continent',COUNT(*) FROM country WHERE continent = 'Europe';
INSERT INTO tmp_table SELECT 'South America' AS 'continent',COUNT(*) FROM country WHERE continent = 'South America';
INSERT INTO tmp_table SELECT 'Oceania' AS 'continent',COUNT(*) FROM country WHERE continent = 'Oceania';
INSERT INTO tmp_table SELECT 'Antarctia' AS 'continent',COUNT(*) FROM country WHERE continent = 'Antarctia';-- 給學生表添加一列 密碼
ALTER TABLE student ADD PASSWORD CHAR(32) NOT NULL AFTER student_no;DROP TEMPORARY TABLE passwd_temp;
CREATE TEMPORARY TABLE passwd_temp SELECT student_no s_no,MD5(student_no) pwd FROM student;
SELECT * FROM passwd_temp;UPDATE student SET PASSWORD = (SELECT pwd FROM passwd_temp WHERE student_no = s_no);SELECT * FROM student;-- 作業:
-- 不用臨時表 嘗試實現同樣功能