必备表
dept
CREATE TABLE `dept` (
`deptnu` int(11) NOT NULL COMMENT '部门编号',
`dname` varchar(50) DEFAULT NULL COMMENT '部门名称',
`addr` varchar(50) DEFAULT NULL COMMENT '部门地址',
PRIMARY KEY (`deptnu`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
employee
CREATE TABLE `employee` (
`empno` int(11) NOT NULL COMMENT '雇员编号',
`ename` varchar(50) DEFAULT NULL COMMENT '雇员姓名',
`job` varchar(50) DEFAULT NULL COMMENT '雇员职位',
`mgr` int(11) DEFAULT NULL COMMENT '雇员上级编号',
`hiredate` date DEFAULT NULL COMMENT '雇佣日期',
`sal` decimal(7,2) DEFAULT NULL COMMENT '薪资',
`deptnu` int(11) DEFAULT NULL COMMENT '部门编号',
PRIMARY KEY (`empno`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
salgrade
CREATE TABLE `salgrade` (
`grade` int(11) NOT NULL COMMENT '等级',
`lowsal` int(11) DEFAULT NULL COMMENT '最低薪资',
`higsal` int(11) DEFAULT NULL COMMENT '最高薪资',
PRIMARY KEY (`grade`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
题目以及答案:
1.查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数
根据题找信息
employee dept
左连接查询法
select b.deptnu,b.dname,b.addr,a.total from (select deptnu,count(*) as total from employee group by deptnu) a left join dept b on a.deptnu=b.deptnu;
内连接查询法
select b.deptnu,b.dname,b.addr,a.total from (select deptnu,count(*) as total from employee group by deptnu) a,dept b where a.deptnu=b.deptnu;
2.列出薪金比安琪拉高的所有员工。
根据题找信息
employee
select a.ename from employee a where a.sal > (select sal from employee b where b.ename='安琪拉');
3.列出所有员工的姓名及其直接上级的姓名。
根据题找信息
employee
select a.ename,ifnull(b.ename,'BOOS') as leder from employee a left join employee b on a.mgr=b.empno;
4.列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
根据题找信息
employee dept
select a.deptnu,a.ename,c.dname from employee a left join employee b on a.mgr=b.empno left join dept c on a.deptnu=c.deptnu where a.hiredate< b.hiredate;
5.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门。
根据题找信息
employee dept
select a.dname,b.* from dept a left join employee b on a.deptnu=b.deptnu;
6.列出所有文员的姓名及其部门名称,所在部门的总人数。
根据题找信息
employee dept
select deptnu,count(*) from employee group by deptnu;
标准写法
select a.ename,b.dname,c.total from employee a inner join dept b on a.deptnu=b.deptnu inner join (select deptnu,count(*) as total from employee group by deptnu) c on c.deptnu=b.deptnu and a.job='文员';
常用写法
select a.ename,b.dname,c.total from employee a,dept b,(select deptnu,count(*) as total from employee group by deptnu) c where a.deptnu=b.deptnu and a.job='文员' and b.deptnu=c.deptnu;