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 基础面试应该就没什么问题。后面还会出相关索引方面的资料，