SQL查询语句：查询公司员工薪水、岗位等问题

sql查询语句：查询公司员工薪水、岗位等问题

show databases;

use esupermarket;

show tables;

select * from tbl_product limit 2,5;

select id,productno,title,price,sellpoint,status,image1,image2,image3,image4,image5 from tbl_product limit 5,5;

select count(id) from tbl_product;

select
ta.id,ta.`name`,ta.age,tb.source
from
tablea ta, tableb tb
where ta.id = tb.id;


select 
ta.id,ta.`name`,ta.age,tb.source
from tablea ta
left join tableb tb
on ta.id = tb.id;

select 
ta.id,ta.`name`,ta.age,tb.source
from tablea ta
right join tableb tb
on ta.id = tb.id;


/* 查询当前使用数据库版本, */
select database();
/* 显示当前库有哪些表 */
show tables;

show databases;

use test2;

show tables from esupermarket;

desc tablea;

show create table tablea;

select database();

use bjpowernode;

show tables;

desc dept;

show tables;

select empno, ename from emp;

select * from emp;

/* 计算员工年薪 */
select empno, ename, job, sal*12 as '年薪'
from emp;

/* 过滤掉大于1000的员工 */
/* 列出员工的编号，姓名和年薪 */
select empno, ename, job, sal
from emp
where sal < 1000;


/* 条件查询 */
select empno, ename, job,sal
from emp
where sal = 5000;

/* 查询职位为manager的员工 */
select empno,ename,job
from emp
where job = 'manager';

select *
from emp
where sal != 5000;

select *
from emp
where sal != '5000';

select *
from emp
where sal < '3000';

/* 以下显示1981-02-20以后的记录*/
select *
from emp
where hiredate > '1981-02-20';

/* 以下显示1981-02-20以前的记录*/
select *
from emp
where hiredate < '1981-02-20';

/* 查询薪水1600到3000之间的员工 */
select * 
from emp
where sal>1600 and sal < 3000;

select *
from emp
where sal between 1600 and 3000;

select empno, ename
from emp
where ename between 'a' and 'd';

show tables;

desc emp;

/* 查询员工津贴为null的记录 */
/* 因为null类型比较特殊，必须使用 is 来比较 */
select *
from emp
where comm is null;


/* 查询员工津贴不为null的记录 */
select * 
from emp
where comm is not null;

/* 查询工作岗位为“manager”并且薪水大于2500的员工 */
select * 
from emp
where job between 'manager' and sal < 2500;

/* 查询出 job 为 salesman 和 job为manager的员工 */
select * 
from emp
where job = 'manager' or job = 'clerk';

/* 查询薪水大于1800，并且部门编号为20或30的员工 */
select 
    empno,ename,deptno,sal
from 
    emp
where 
    sal > '1800'
and
    (deptno = '20' or deptno = '30');


select
    empno,ename,deptno,sal
from 
    emp
where
    sal > '1800'
having
    deptno
in
    ('20','30');


/* 查询出job为 salesman 和 job为 manager 的员工 */
/* in表示包含的意思，完全可以采用or来表示，采用in会更简洁一些 */
select
    *
from
    emp
where 
    job 
in 
    ('salesman','manager');

/* 查询出薪水为1600和3000的员工 */
select
    empno, ename, sal
from
    emp
where
    sal
in
    ('1600','3000');

select
    empno, ename, sal
from
    emp
where
    sal = '1600' or sal = '3000';


/* 查询出薪水在1600和3000的之间员工 */
select
    empno, ename, sal
from
    emp
where
    sal between '1600' and '3000';

/* 内连接 */
select
    e.empno,e.ename,d.dname,e.comm
from
    emp e ,dept d
where
    e.deptno = d.deptno
having
    e.comm is null;


/* 模糊查询like */
select
    *
from
    emp
where
    ename like '%s%';

/* 查询姓名中第二个字符为a的所有员工 */
select 
    empno, ename
from
    emp
where
    ename like '_a%';

/* 查询ename所有倒数第二个字母为e的 */
select
    empno, ename
from 
    emp
where
    ename like '%e_';

/* 查询姓名中第三个字符为n的所有员工姓名 */
select
    empno,ename
from 
    emp
where
    ename like '__n%';

update
    emp
set
    ename ='ki_ng'
where
    empno = '7839';

update
    emp
set
    ename = 'jo%nn'
where
    empno = '7566';

/* 转义查询包含通配符 escape */
select
    empno,ename,sal
from 
    emp
where
    ename like '%/_%'
escape "/";

select
    empno,ename,sal
from 
    emp
where 
    ename like '%/%%'
escape '/';

/* 按照薪水由小到大排序（系统默认由小到大） */
select
    empno,ename,sal
from
    emp
where
    job = 'manager'
order by 
    sal asc;


/* 多个字段排序 */
/* 按照 job 和薪水倒序排序,首先按照job降序排列.然后在按照sal降序排列 */
select 
    e.empno,e.ename,e.job,e.sal,d.deptno,d.dname
from
    emp e, dept d
where
    d.deptno = '20'
order by
    e.job desc,e.sal asc;

/* 将大写转换成小写 */
select
    empno,lower(ename) as ename,sal
from
    emp;


/* 查询员工姓名中第二个字母为a的所有员工 */
select
    empno,ename
from 
    emp
where
    substr(ename,'2','1') = 'a';


select
    empno,ename,sal,mgr
from 
    emp
where
    substr(mgr,'2','2') = '69';

select
    empno,ename,sal,mgr
from
    emp
where
    mgr like '_69%';

/* 取得员工姓名长度 */
select
    empno,ename,length(ename) as enamelength
from 
    emp;

/* 取得工作岗位为manager的所有员工 */
/* 使用trim()函数,去除"  manager  "前后的空格,可以取得查询结果 */
select
    empno,ename,sal,job
from
    emp
where
    job = trim('manager');

select rand();

select
    empno,ename,sal,job
from 
    emp
order by sal desc;


select
    empno,ename,sal,job
from
    emp
order by sal asc;


select * from emp;

select
    deptno,sum(sal) as sal_total
from 
    emp
group by
    deptno
order by
    sum(sal) asc;

select
    deptno,ename,sal
from 
    emp
group by
    deptno,ename,sal
order by
    deptno,sal asc;

/* 去重关键字 */
select distinct job from emp;

select
    *
from
    emp 
where 
    false;

select
    *
from
    emp
where 
    true;


show databases;

use bjpowernode;

show tables;

/* ifnull  空值处理函数   */
select
    ename,sal,comm,(sal + ifnull(comm,0))*12 as '年收入'
from
    emp;

/* 没有补助的员工 将补助100 计算年薪 */
select
    ename,sal,comm,ifnull(comm,100) as '补助',(sal + ifnull(comm,100))*12 as '年薪'
from 
    emp;

select
    *
from 
    emp
where
    hiredate = '1981-02-20';


/* where过滤 年月日 y要大写 d m 小写 */
select
    ename,hiredate
from 
    emp
where
    hiredate = str_to_date('17-12-1980','%d-%m-%y');

/* 创建表格语句 */
create table t_student(
id  int(4),
name varchar(32),
birth  date
);

select * from t_student;

insert into 
    t_student 
values
    ('1','lisi','1925-12-22');

insert into
    t_student
values
    ('2','zhangsan',str_to_date('12-31-2001','%m-%d-%y'));

insert into
    t_student
values
    ('4','wangwu',str_to_date('31-12-2010','%d-%m-%y'));

/* data_format函数主要用在数据库查询操作中。实际工作中，客户需要日期以特定格式展示的时候，需要使用该函数 */



/* 匹配工作岗位，当为manager时，薪水上调10%，当为salesman时，薪水上调50%，其它岗位薪水不变 */
select
    empno, ename, job, sal, (case job when 'manager' then sal*1.1 when 'salesman' then sal*1.5 else sal end) as 'newsal'
from emp;

select * from emp;

/* 匹配部门列表 当部门编号为10 去20部门 20部门去30部门  其他不变 */
select
    empno,ename,job,sal,deptno,(case deptno when '10' then deptno+10 when '20' then deptno+10 else deptno end) newdeptno
from emp;

select
    count(comm)
from
    emp;

select
    sum(sal+comm)
from
    emp;

select
    sum(sal + ifnull(comm,0)) as 'sal+comm'
from
    emp;

/* 取得最晚入职的员工 */
select
    *
from
    emp
where
    hiredate = (
    select
    max(hiredate)
from
    emp
    );


select 
    distinct 
        e.deptno, d.dname, e.job
from
    emp e, dept d
where
    e.deptno = d.deptno
order by
    e.deptno;

/* 查询该公司有几个工作岗位 */
select 
    count(distinct job) as jobnum
from    
    emp;

/* 按照工作岗位分组，然后对每一组求最大值。sql语句中肯定包含 group by、max(sal)； */
select 
    job,
    max(sal)
from
    emp
group by
    job;

/* 找出每个工作岗位的平均薪水，要求显示平均薪水大于2000的； */