MySQL作业2

	1.	列出员工表中每个部门的员工数（员工数必须大于 3）,和部门名称.
	select emp.deptno,dept.dname, count(*) from emp,dept where emp.deptno=dept.deptno group by emp.deptno,dept.dname having count(*)>3;
	2.	找出工资比 jones 多的员工信息。
	select *from emp where sal>(select sal from emp where ename='JONES');
	3.	列出所有员工的姓名和其上级的姓名。
	select works.ename works,boss.ename boss from emp works,emp boss where works.mgr=boss.empno;
	4.	查询大于30号部门平均工资并且不在30号部门的员工信息和部门名称。
	select emp.*,dept.dname from emp,dept where emp.deptno=dept.deptno and sal>(select avg(sal) from emp where deptno=30) and emp.deptno!=30;
	5.	查询每个月工资总数最少的那个部门的部门编号，部门名称，部门位置。
	select dept.*from dept,(select sum(sal),deptno from emp group by deptno order by sum(sal) asc limit 1) a where dept.deptno=a.deptno;
	6.	查询员工编号为7369的员工姓名和所在部门的部门名称。
	select emp.ename,dept.dname from dept,emp where emp.empno=7369 and emp.deptno=dept.deptno;
	7.	查询超过其所在部门平均工资的员工信息。
	select *from emp,(select deptno sadept,avg(sal) sa from emp group by deptno) ag where sal>ag.sa and emp.deptno=ag.sadept;
	8.	查询大于30号部门平均工资并且不在30号部门的员工信息
	select emp.*from emp where sal>(select avg(sal) from emp where deptno=30) and emp.deptno!=30;
	9.	创建存储过程用于根据部门编号查询员工姓名
	delimiter //;
	create procedure work_ename(eno int,out name varchar(20))
	begin
	select ename into name from emp where empno=eno;
	end //;
	call sel_ename(7788,@name);
	select @name;
	10.	创建函数实现根据员工编号查询部门名称
	delimiter //;
	create procedure dept_dname1(eno int,out deptname varchar(20))
	begin
	select dname into deptname from dept where dept.deptno=(select deptno from emp where emp.empno=eno);
	end //;
	call dept_dname1(7788,@name);
	select @name;

News表
中文字段名	英文字段名	数据类型	备注
新闻id		newsID		 int	主键
新闻标题		newsTitle	varchar	N
新闻编者		editer		varchar	N
创建时间		createTime	Date	N
新闻内容		content		varchar	N

Message表
中文字段名	英文字段名	数据类型	备注
评论编号		Msgid		int		主键
评论时间		MsgTime		Datetime	
评论人		MsgWriter	Varchar	
新闻 id		NewsId		int		外键

create table news(
	newsID int primary key auto_increment,
	newsTitle varchar(20),
	editer varchar(50),
	createTime Date,
	content varchar(50)
);
create table message(
	msgid int primary key,
	msgTime Datetime,
	msgwriter varchar(20),
	newsID int,
	foreign key(newsID) references news(newsID)
);
insert into news values(1,'体育','李欣','2019-4-3','中国第一'),(2,'教育','张亮','2018-7-8','好好学习'),(3,'Java','张琴','2019-3-20','编程设计'),(4,'c++','王柳','2017-5-3','后端设计'),(5,'C语言','雷云','2016-7-13','底层设计'),(6,'网络','牛头',now(),'网络编程');
insert into message values(1,'2019-8-17','季布',1),(2,'2018-8-10','韩信',2),(3,'2019-7-17','刘邦',3),(4,'2017-9-23','萧何',4),(5,'2016-11-1','虞姬',5),(6,now(),'萧太后',6);
1：将评论时间是当前月的评论信息，评论人追加上教授两个字
update message set msgwriter=concat(msgwriter,'教授') where year(msgTime)=year(now()) and month(msgTime)=month(now());
2：修改新闻id为1,3,5的评论，将评论时间改为今天
update message set msgTime=now() where newsID in(1,3,5);
3：查询编著新闻最多的两位编者,及其编著数量
select editer,count(*) n from news n group by editer order by n desc limit 2;
4：查询每天的新闻数量
select createtime,count(*) from news group by createtime
5：按评论时间降序显示1号新闻的评论信息
select *from message natural join news where news.newsID in(1) order by msgTime desc ;
select * from message where newsid=1 order by msgtime desc
6：获取评论数最多的评论人
select msgwriter from message group by msgwriter order by count(*) desc limit 1
7：获取每条新闻的新闻标题,创建时间和评价数
select newstitle,createtime,msgCount from
(select newsid,count(*) msgCount from message group by newsid) a,
(select * from news) b
where a.newsid=b.newsid;
8：获取没有评论的新闻信息
select * from news where newsid not in(select newsid from message);
9：获取所有的评论人(去除重复列)
select distinct msgwriter from message;
10：删除没有评论的新闻
delete from news where newsid not in(select newsid from message)