《SQL高级应用和数据仓库基础（MySQL版）》作业 ·005

问题列表

1.使用如下语句，创建学生表student和班级表class 

create table student( -- 学生表
	xh char(4),         -- 学号
	xm varchar(10),     -- 姓名
	sex char(2),        -- 性别
	birthday date,      -- 出生日期
	sal double(7,2),    -- 奖学金
	studentcid int(2),  -- 学生班级号 
);

create table class( -- 班级表
	classid int(2),   -- 班级编号
	cname varchar(20),-- 班级名称
	ccount int(3)     -- 班级人数
 );

2.基于上述学生表和班级表，完成如下问题 
（1）添加三个班级信息为：
		1, JAVA1班, null
		2, JAVA2班, null
		3, JAVA3班, null
（2）添加学生信息如下：
		'A001', '张三', '男', '01-05-05', 100, 1
（3）添加学生信息如下：
		'A002', 'MIKE', '男', '1905-05-06', 10, 2
（4）插入部分学生信息：
		'A003', 'JOHN', '女'
（5）将A001学生性别修改为'女' 
（6）将A001学生信息修改如下：
		性别为男，生日设置为1980-04-01
（7）将生日为空的学生班级修改为Java3班
（8）请使用一条SQL语句，使用子查询，更新班级表中每个班级的人数 字段

参考题解

/*
* 注：建议在创建表之前，确认当前数据库的字符集是utf8，否则后面插入中文可能会失败。
**/

-- 1.添加三个班级
insert into class
values
	(1, 'JAVA1班', null),
	(2, 'JAVA2班', null),
	(3, 'JAVA3班', null);

-- 2.添加一个学生信息
insert into student
values('A001', '张三', '男', '01-05-05', 100, 1);

-- 3.添加一个学生信息
insert into student
values('A002', 'MIKE', '男', '1905-05-06', 10, 2);

-- 4.插入一个学生部分信息
insert into student(xh, xm, sex)
values('A003', 'JOHN', '女');

-- 5.修改一个学生性别
update student
set sex='女'
where xh='A001';

-- 6.修改一个学生部分信息
update student
set sex='男', birthday='1980-04-01'
where xh='A001';

-- 7.修改所有满足条件的学生的班级
update student
set studentcid=(select classid from class where cname='JAVA3班')
where birthday is null;

-- 8.子查询更新班级人数
delimiter $$ 
	create procedure getStuNum()
	begin
		declare cid_i int(2);
		declare done int default false;
		declare cur cursor for select classid from class;
		declare continue handler for not found set done=true;
		
		open cur;
		fetch cur into cid_i;
		while(not done) do
			update class
			set ccount=(select count(*) from student where studentcid=cid_i)
			where classid=cid_i;
			
			fetch cur into cid_i;
		end while;
		close cur;
	end$$ 
delimiter ;

call getStuNum();

修改标注：
	1、将原题文本部分的建表语句进行了优化
	2、将原题的2中的（2）的日期从'01-5月05'改为了'01-05-05'
	3、将原题中2中的（3）的学生信息加上了其班级编号为2
	（因为原题有区分添加学生信息和插入部分学生信息，就算真的是部分信息也不难实现插入）

注：

第8题的常规写法如下：

update class
set ccount=(select count(*) from student where student.studentcid=class.classid);