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;


-- 作业:
-- 不用临时表 尝试实现同样功能