记录开始学习AS400（01）

2018-12-10 AS400的第一天
sql基础：

/**创建班级表*/
create table cla(
	class_id int(9) NOT NULL AUTO_INCREMENT,
	classes varchar(255) not null,
	PRIMARY key (class_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

/**创建学生表*/
ccreate table stu(
	id int(9) not null AUTO_INCREMENT,
	name varchar(255) not null,
	gender enum("男","女"),
	birth date not null,
	class_id int(9) not null, 
	PRIMARY key(id),
	foreign key(class_id)  REFERENCES cla(class_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

/**向班级表中插入数据*/
insert into cla(classes) value("一班");
insert into cla(classes) value("二班");
insert into cla(classes) value("三班");

/**向学生表中插入数据*/
insert into stu(name,gender,birth,class_id) value("李磊","男","20000127",1);
insert into stu(name,gender,birth,class_id) value("韩梅梅","女","19990301",1);
insert into stu(name,gender,birth,class_id) value("韩冬","男","20000506",1);

insert into stu(name,gender,birth,class_id) value("马冬梅","女","19980821",2);
insert into stu(name,gender,birth,class_id) value("小青","女","19980801",2);
insert into stu(name,gender,birth,class_id) value("小白","男","19990401",2);
insert into stu(name,gender,birth,class_id) value("齐德隆","男","19990620",2);
insert into stu(name,gender,birth,class_id) value("齐东强","男","19990620",2);

insert into stu(name,gender,birth,class_id) value("小兰","女","20000127",3);
insert into stu(name,gender,birth,class_id) value("红红","女","19990301",3);
insert into stu(name,gender,birth,class_id) value("萨","女","20000506",3);
insert into stu(name,gender,birth,class_id) value("熊大","男","20000506",3);

/**按班级查找班级总人数*/
select c.classes,count(*) from stu s,cla c where s.class_id = c.class_id group by s.class_id;

/**从学生表和班级表按学生出生时间降序查找学生所有信息*/
select * from stu s,cla c where s.class_id = c.class_id order by birth DESC;

/**删除id为12的学生信息*/
delete from stu where id = 12;

/**更新id为11的学生姓名为飒飒*/
update stu set name="飒飒" where id = 11;