MySQL 学习笔记(4):实例查询

MySQL 学习笔记(4):实例查询

  • --1.列出男职工的总数和女职工的总数

select count(*) from employees group by sex;

  • -- 2.列出非党员职工的总数

seelct count(*) from employees where politicalstatus<>'党员';

  • -- 3.列出所有职工工号,姓名以及所在部门名称

select empid,empname,deptname from employees e
inner join departments d on e.deptid=d.deptid;

  • -- 4.列出所有职工工号,姓名和对应工资

select e.empid,empname,salary from employees e
inner join salary s on e.empid=s.empid;

  • -- 5.列出所有领导岗的姓名以及所在部门名称

select empname,deptname from employees e
inner join departments d on e.depid=d.detpid
where leader is null;

  • -- 6.列出职工总人数大于4的部门号和部门名称

select depitd,count(*) from employees group by deptid having count(*)>4;

  • -- 7.列出职工总人数大于4的部门号和部门名称

select e.deptid,deptname from employees e
inner join departments d on e.deptid=d.deptid
group by e.deptid having count(*)>4;

  •  -- 8.列出开发部和测试部的职工号,姓名

select empid,empname from employees e
inner join departments d on e.deptid=d.deptid
where deptname in('开发部','测试部');

  • -- 9.列出市场部所有女职工的姓名和政治面貌

select empname,politicalstatus from employees e
inner join departments d on e.deptid=d.deptid
where deptname='市场部' and sex='女';

  • -- 10.显示所有职工姓名和工资,包括没有工资和职业姓名

select empname,salary from employees e
left join salary s on e.empid=s.empid;

  • -- 11.求不姓孙的所有职工工资总和

select sum(salary) from employees e
inner join salary s on e.empid=s.empid
where not empname like'孙%';

上一篇:MySQL JOIN的使用


下一篇:SpringMVC学前回顾