查询表记录

基础查询
15.查询emp中所有员工，显示姓名、薪资、奖金

select name,sal,bonus from emp;

16.查询emp表中所有部门和职业

select dept,job from emp;

17.查询emp表中薪资大于3000的所有员工，显示员工姓名、薪资

select name,sal from emp where sal>3000;

查询emp中总薪资大于3500，显示员工姓名、总薪资

select name,sal+bonus from emp where sal+bonus>3500;

–if null(l列，值)函数：判断指定的列是否包含null值，如果有null，用第二个值替换null

select name,sal+ifnull(bonus,0) from emp where sal+ifnull(bonus,0)>3500;

18.修改表头改为总薪资

select name as 姓名,sal+ifnull(bonus,0) as 总薪资 from emp where sal+ifnull(bonus,0)>3500;

19.查询emp表中薪资在3000和4500之间的员工，显示员工的姓名和薪资

select name,sal from emp where sal>=3000 and sal<=4500;

t提示：between…and…

select name,sal from emp where sal between 3000 and 4500; 

20.查询emp表中薪资为1400、1600、1800的员工，显示员工姓名和薪资

select name,sal from emp where sal=1400 or sal=1600 or sal=1800;
--或
select name,sal from emp where sal in(1400,1600,1800);
--删除时
delete from emp where id in(1,3,5,7);

21.查询薪资不为1400,1600,1800的员工

select name,sal from emp where not(sal=1400 or sal=1600 or sal=1800);

22.查询表中薪资大于4000和薪资小于2000的员工，显示员工姓名、薪资

select name,sal from emp where sal>4000 or sal<2000;

23.查询emp表中薪资大于3000并且小于600的员工，显示员工姓名、薪资、奖金

select name,sal,bonus from emp where sal>3000 and ifnull(bonus,0)<600;

24.查询没有部门的员工

select *from emp where dept is null;
select *from emp where not dept is null;//查询有部门的