MySQL基础知识点总结代码

drop database if exists SchoolManagesystemDB;
create database if not exists SchoolManageSystemDB;
use SchoolManageSystemDB;

drop table if exists OrganizeInfo;
create table if not exists OrganizeInfo
(
OrganizeID int primary key auto_increment comment'组织唯一编码',
KindOf enum('1', '2', '3') not null default '3' comment'类别：1、学院，2、教研组，3、班级',
`Name` varchar(50) not null comment'名称',
MasterID int comment'主管唯一编码'
)engine=innodb default charset=utf8;

drop table if exists RoomInfo;
create table if not exists RoomInfo
(
RoomID int primary key auto_increment comment'住宿唯一编码',
TypeOf enum('1', '2') not null default '1' comment'类别：1、教职员工宿舍，2、学生宿舍',
`Name` varchar(50) not null comment'名称',
MaxCount int not null default 6 comment'最大额定人数'
)engine=innodb default charset=utf8;

drop table if exists UserInfo;
create table if not exists UserInfo
(
UserID int primary key auto_increment comment'人员唯一编码',
OrganizeID int not null comment'隶属组织唯一编码',
UserUniqueID varchar(50) not null comment'教员导员为工号，学生为学号',
`Name` varchar(50) not null comment'名称',
RoleOf enum('1','2','3','4') not null default'3' comment'身份角色：1、教员，2、导员，3、学生，4、学生管理员',
RoomID int not null comment'住宿信息唯一编码',
foreign key(OrganizeID) references OrganizeInfo(OrganizeID),
foreign key(RoomID) references RoomInfo(RoomID)
)engine=innodb default charset=utf8;

alter table OrganizeInfo add constraint FK_OrganizeInfo_UserInfo_MasterID foreign key(MasterID) references UserInfo(UserID);

insert into OrganizeInfo(KindOf, `Name`)
values
('1', '电子信息学院'),
('2', '教员组'),
('2', '导员组'),
('3', '一班'),
('3', '二班'),
('3', '三班');

insert into RoomInfo(TypeOf, `Name`, MaxCount)
values
('1', '教师公寓101室', 4),
('1', '教师公寓202室', 4),
('2', '学生公寓101室', 6),
('2', '学生公寓202室', 8),
('2', '学生公寓303室', 8),
('2', '学生公寓404室', 8),
('2', '学生公寓505室', 8);

insert into UserInfo(OrganizeID, UserUniqueID, `Name`, RoleOf, RoomID)
select a.OrganizeID, '20191111', '张一', '1', b.RoomID from OrganizeInfo a, RoomInfo b where a.`Name`='教员组' and b.`Name`='教师公寓101室'
union
select a.OrganizeID, '20192222', '张二', '2', b.RoomID from OrganizeInfo a, RoomInfo b where a.`Name`='导员组' and b.`Name`='教师公寓202室'
union
select a.OrganizeID, '20193333', '张三', '3', b.RoomID from OrganizeInfo a, RoomInfo b where a.`Name`='三班' and b.`Name`='学生公寓303室'
union
select a.OrganizeID, '20194444', '张四', '3', b.RoomID from OrganizeInfo a, RoomInfo b where a.`Name`='三班' and b.`Name`='学生公寓404室'
union
select a.OrganizeID, '20195555', '张五', '3', b.RoomID from OrganizeInfo a, RoomInfo b where a.`Name`='三班' and b.`Name`='学生公寓505室'
union
select a.OrganizeID, '20196666', '张六', '4', b.RoomID from OrganizeInfo a, RoomInfo b where a.`Name`='三班' and b.`Name`='学生公寓606室';

select * from UserInfo where UserUniqueID='20191111';
select * from UserInfo where UserUniqueID='20193333';

select b.`Name` from RoomInfo a inner join UserInfo b on a.RoomID=b.RoomID where a.`Name`='学生公寓505室';

select a.`Name`,
case when a.RoleOf='1' then '教员' else
	case when a.RoleOf='2' then '导员' else
		case when a.RoleOf='3' then '学生' else '学生管理员'
		end
	end
end as '身份',
b.`Name` as '隶属组织名称', c.`Name` as '主管姓名'
from UserInfo a inner join OrganizeInfo b on a.OrganizeID=b.OrganizeID
left join UserInfo c on b.MasterID=c.UserID
where a.RoleOf in ('1', '2');


select a.`Name`,
case when a.RoleOf='1' then '教员' else
	case when a.ROleOf='2' then '导员' else
		case when a.RoleOf='3' then '学生' else '学生管理员'
		end
	end
end as '身份',
b.`Name` as '班级名称', c.`Name` as '教员姓名', d.`Name` as '宿舍名称'
from UserInfo a inner join OrganizeInfo b on a.OrganizeID=b.OrganizeID
left join UserInfo c on b.MasterID=c.UserID
inner join RoomInfo d on a.RoomID=d.RoomID
where b.`Name`='三班';