create database stu charset utf8;
use stu
drop table if exists students;
create table students(id int primary key auto_increment,name varchar(20),gender char(1),birth date);
insert into students values(null,'zhangsan','M','2017-10-10');

insert into students(birth,name,gender)
values('2013-12-1','lisi','F'),
('2013-12-3','wangwu','M'),
('2013-8-17','zhaoliu','F'),
('2013-9-25','qianqi','M');

insert into students(name,gender,birth) 
select name,gender,birth from students;

create table xueshengs as select id,gender,name,birth from students;

alter table xueshengs add primary key(id);

drop table if exists lianxi;
create table lianxi
(
 xs_id int primary key,
tel varchar(100),
qq varchar(20),
email varchar(50),
foreign key(xs_id) references xueshengs(id)
);

insert into lianxi(xs_id,tel,qq,email)
values
(1,'83553761','1176878091','1176878091@qq.com'),
(2,'83553762','1176878092','1176878092@qq.com'),
(3,'83553763','1176878093','1176878093@qq.com'),
(4,'83553764','1176878094','1176878094@qq.com'),
(5,'83553765','1176878095','1176878095@qq.com');

alter table xueshengs modify id int auto_increment;

update xueshengs set birth='1995-08-04' where id=5;

update xueshengs set name='xiaoming',gender='M',birth='1998-09-14' 
where id in(6,8,10,13);

-- 用所有qianqi的id过滤，删除联系方式
delete from lianxi where xs_id in
(
select id from xueshengs where name='qianqi'
);

delete from xueshengs where name='qianqi';

drop table if exists banji;
create table banji
(
id int primary key auto_increment,
name varchar(20)
);
alter table xueshengs add banji_id int;

alter table xueshengs
add foreign key(banji_id)
references banji(id);

insert into banji(name) values('maleclass');
insert into banji(name) values('femaleclass');
insert into banji(name) values('animalclass');

update xueshengs set banji_id=3
where gender is null;

update xueshengs set banji_id=1
where gender='M';
update xueshengs set banji_id=2
where gender='F';

select * from xueshengs where banji_id=1 limit 5;

select 
x.id,x.name,
b.id banid,b.name banname
from
xueshengs x,banji b
where 
x.banji_id=b.id
limit 20;

drop table if exists kecheng;
create table kecheng
(
id int primary key auto_increment,
name varchar(20)
);

-- 多对多关系中间表
drop table if exists xs_kc_link;
create table xs_kc_link
(
xs_id int not null,
kc_id int not null,
foreign key(xs_id) references xueshengs(id),
foreign key(kc_id) references kecheng(id),
unique key(xs_id,kc_id)
);
-- 添加课程学生选课
insert into kecheng(name) values('chinese'),('math'),('english');
insert into xs_kc_link values(1,1),(1,2),(2,1),(2,3),(3,2),(3,3);
-- 查询谁选了什么课程
select 
  x.name,
  k.name kecheng
from
  xueshengs x,xs_kc_link l,kecheng k
where 
  x.id=l.xs_id and
  k.id=l.kc_id;