MySQL查询-补充

数据准备：

-- 部门表
create table dept(deptno int primary key,                            -- 部门编号  主键：唯一，非空dname varchar(14), 		 -- 部门名称loc varchar(13)			 -- 部门地址
);insert into dept values	(10,'accounting','new york');
insert into dept values             (20,'research','dallas');
insert into dept values	(30,'sales','chicago');
insert into dept values	(40,'operations','boston');-- 员工表
create table emp(empno int  primary key, 	-- 员工编号ename varchar(10), 			-- 员工姓名job varchar(9), 			-- 员工工作mgr int, 					-- 员工直属领导编号hiredate date, 				-- 入职时间sal double, 				-- 工资comm double, 				-- 奖金deptno int  				-- 所在部门
);
insert into emp values(7369,'smith','职员',7566,"1980-12-17",800,null,20);
insert into emp values(7499,'allen','销售员',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,'ward','销售员',7698,'1981-02-22',1250,500,30);
insert into emp values(7566,'jones','经理',7839,'1981-04-02',2975,null,20);
insert into emp values(7654,'martin','销售员',7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,'blake','经理',7839,'1981-05-01',2850,null,30);
insert into emp values(7782,'clark','经理',7839,'1981-06-09',2450,null,10);
insert into emp values(7788,'scott','职员',7566,'1987-07-03',3000,2000,20);
insert into emp values(7839,'king','董事长',null,'1981-11-17',5000,null,10);
insert into emp values(7844,'turners','销售员',7698,'1981-09-08',1500,50,30);
insert into emp values(7876,'adams','职员',7566,'1987-07-13',1100,null,20);
insert into emp values(7900,'james','职员',7698,'1981-12-03',1250,null,30);
insert into emp values(7902,'ford','销售员',7566,'1981-12-03',3000,null,20);
insert into emp values(7934,'miller','职员',7782,'1981-01-23',1300,null,10);-- 工资等级表
create table salgrade( grade int,-- 等级losal double, -- 最低工资hisal double ); -- 最高工资insert into salgrade values (1,500,1000);
insert into salgrade values (2,1001,1500);
insert into salgrade values (3,1501,2000);
insert into salgrade values (4,2001,3000);
insert into salgrade values (5,3001,9999);

查询员工姓名、职位、薪水、薪资等级

提示：两个表关联不一定是外键列，在该题中关联是一个非等的条件

SELECT e.ename AS "员工姓名",e.job AS "职位",e.sal AS "薪水",sg.grade AS "薪资等级"
FROM emp e
JOIN salgrade sg
ON e.sal BETWEEN sg.losal AND sg.hisal;

计算出员工的年薪，并且以年薪降序排序

selectename ,case when comm is null then sal  else sal +comm  end as '年薪'
from emporder by 年薪 desc;

这里用到一个语法：

case

        when 条件1 then 0

        when 条件2 then 0

        else 值n

        end #记得新起个列名 ，凑则会在查询得到结果后有惊喜

这个还可以有另外一种写法，但是该种写法有特殊限制，就是when后面的条件都是相等关系且等会前面的条件一样

case gender

        when '男' then ‘male’

        when '女' then ‘female’

        else ‘萨瓦迪卡’

        else as new_gender

这个case when then这个还可以与聚合函数配合使用

按工作分类，求出分类下所有员工的总奖金，并按降序排列

select job, sum(case when comm is null then 0 else comm end) as sum_comm from emp group by job order by sum_comm desc;

在这里case when then 可以用if语句代替

语法：if(条件，值1，值2) -- 条件成立返回值1，否则返回值2

select job, sum(if(comm is null, 0 ,comm)) as sum_comm from emp group by job order by sum_comm desc;

基本语法差不多也就这么多，我的这些笔记看完了mysql 基础面试应该就没什么问题。后面还会出相关索引方面的资料，