ape_li

drop table comment;
create table comment(
id int(7) primary key,
content varchar(20),
comment_date date,
article_id int(7),
constraint com_fk foreign key(article_id) references article(id)
);

drop table article;
create table article(
id int(7) primary key,
title varchar(20),
author varchar(20),
content varchar(20),
post_date date
);




insert into article(id,title,author,content,post_date) values(10000,'Title','admin',null,'2022-08-13');



drop  table department;
create table department(
depid int(7) primary key,
depname varchar(20),
depnote varchar(20)
);

drop table employee;
create table employee(
empid int(7) primary key,
empname varchar(20),
empsex int(2),
depart int(7),
salary double(10,2),
position varchar(20)
);

insert into department values(1,'软件研发部',null),(2,'系统集成部',null);

insert into employee values(2017001,'张三',1,1,8000.00,'职员'),(2017002,'李四',1,1,12000.00,null),(2017003,'王五',1,2,3500.00,'职员'),(2017004,'赵六',2,2,8500.00,'职员');

select empid,empname, case empsex when '1' then '男' else '女' end as empsex,
case depart when '1' then '软件研发部' else '系统集成部' end as dapartment,salary,position from employee ;


select empid,empname, case empsex when '1' then '男' else '女' end as empsex,depart,salary,position from employee ;

-- case depart when '1' then '软件研发部' else '系统集成部' end as dapartment


***case cloumn_name when 'value' then 'your vlaue' else 'your value' end as alias*** 

-- select * from employee e1  where exists (select 1 from employee e2 where e2.salary>e1.salary  having count(empid)=0) ;


-- select * from employee e1 where exists (select 1 from employee e2 where e2.salary<e1.salary having count(*)=0);

update employee set position='试用期' where position is null or salary<5000;

-- method one:
update employee e set e.salary=e.salary*1.1 where depart=(select depid from department where depname='软件研发部');

-- method two:
update (employee e join department d on e.depart=d.depid) set e.salary=e.salary*1.1 where d.depname='软件研发部';

/
*题2
*
/
topic two

drop table table1;
create table table1(
year int(7),
month int(7),
amount double(3,1)
);

insert into table1 values(1991,1,1.1),(1991,2,1.2),(1991,3,1.3),(1991,4,1.4),(1992,1,2.1),(1992,2,2.2),(1992,3,2.3),(1992,4,2.4);

select year, (select t.month from table1  t where month=1  ) m1 from table1;

select year,
   sum(case month when '1' then amount else 0 end)  as m1,sum(case month when '2' then amount else 0 end)  as m2,sum(case month when '3' then amount else 0 end)  as m3,sum(case month when '4' then amount else 0 end) as m4 from table1 group by year;
   
 -- 解释 
 分组后month有4行记录，只有1月的能得到他的aomunt,也就是1.1，其余都是零，故，合并之后应该求和
+------+------+------+------+------+
| year | m1   | m2   | m3   | m4   |
+------+------+------+------+------+
| 1991 |  1.1 |  1.2 |  1.3 |  1.4 |
| 1992 |  2.1 |  2.2 |  2.3 |  2.4 |
+------+------+------+------+------+


/**
题3
**/
topic there

drop table student;
create table student(
sno int(7) primary key not null unique,
sname varchar(20) ,
sex varchar(2),
sage int(7),
sdept varchar(20)
);


drop table course;
create table course(
cno int(7) auto_increment not null unique,
cname varchar(20),
cpno int(7),
credit int (7)
);


drop table sc;
create table sc(
sno int(7) ,
cno int(7),
grade int(5),
constraint sc_pk primary key(sno,cno),
constraint sc_fk foreign key(sno) references student(sno),
constraint sc_fk2 foreign key(cno) references 
course(cno)
);

insert into student values(95001,'李勇','男',20,'cs'),(95002,'刘晨','女',19,'is'),(95003,'王明','女',18,'ma'),(95004,'张立','男',19,'is');

insert into course values(1,'数据库',5,4),(2,'数学',null,2),(3,'信息系统',1,4),(4,'操作系统',6,3),(5,'数据结构',7,4),(6,'数据处理',0,3),(7,'PASCAL',6,4);

insert into sc values(95001,1,92),(95001,2,85),(95001,3,89),(95002,2,90),(95003,3,80);

-- **copy table to other database** comment 'same database as well'
CREATE TABLE new_table LIKE old_database.old_table;
INSERT new_table SELECT * FROM old_database.old_tablel;
for example:
create table course like review.course;
insert course select *from review.course;


mysql add drop alter
ALTER TABLE table
Grammer:ADD [COLUMN] column_name_1 column_1_definition

for example:
alter table student add column nation varchar(10);
 
alter table student drop column nation;
 
alter table student modify column nation varchar(20);


alter table student add column nation varchar(20)
update student set nation='汉族' where sname='王明';

update:
UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ] [ORDER BY 子句] [LIMIT 子句]


select sname,sex from student where sdept='is' or sdept='cs';

select c.cno,c.cname,count(s.sno) from course c left join sc s on s.cno=c.cno group by c.cno;

select c.cno,c.cname from course c join sc s on
s.cno=c.cno where exists (select 1 from sc s2 where c.cno=s2.cno having avg(s2.grade)>88);


delete from student where sno=95003;

ALTER TABLE table_name DROP PRIMARY KEY;

ALTER TABLE table_name DROP INDEX index_name;

ALTER TABLE table_name DROP FOREIGN KEY （fk_symbol）
 
for example:
alter table sc drop foreign key sc_fk;



/**
题4
**/
-- topic 4

select s1.* from scores s1
where exists (select 1 from scores s2 where s1.subject=s2.subject and s1.score<s2.score having count(*)<3)
order by s1.subject,s1.score desc;


create table score(
sid int(7) primary key,
sno int(7),
cno int(7),
grade double(5,2)
);

insert into score values(1,95001,1,100),(2,95002,2,95),(3,95003,2,85),(4,95006,3,86),(5,95002,4,90),(6,95005,5,89),(7,95005,3,200),(8,95003,1,100),(9,95004,7,40),(10,95005,4,50),(11,95003,2,100),(12,95003,6,100);


select s.sname,c.cname,sc.grade from student s join score sc on s.sno=sc.sno join course c on c.cno=sc.cno where exists( select 1 from score sc2 where sc2.grade>sc.grade and sc2.cno=sc.cno group by sc2.grade,sc2.cno having count(*)<2) order by cname,grade;


select s1.name,s1.subject,s1.score from scores s1
group by s1.name,s1.subject,s1.score
having count(1)<3
order by subject,score desc;

/**
题5
**/

topic 5


create table student (
sno int(7) primary key,
sname varchar(20),
sage int(10),
sex varchar(2),
hlocation varchar(50),
phone varchar(20)
);

alter table  student add column degree varchar(20);
alter table  student drop column hlocation;


insert into student values(1,'A',22,'男','123456','小学'),(2,'B',21,'男','119','中学'),(3,'C',23,'男','110','高中'),(4,'D',18,'女','114','大学');

update student set degree='大专' where phone like "11%";

delete from student where sname like 'C%' and sex='男';

select sname,sno from student where sage<22 and degree='大专';