JAVA sql 查询

-- 1.  查询员工表所有数据，并说明使用*的缺点 
SELECT * from employees
-- *号查询效率低

-- 2.  查询所员工的 email 全名,公司 email 统一以 "@qq.com " 结尾. 
SELECT email from employees WHERE email like "[e-mail]"

-- 3.  打印公司里所有的 manager_id(去除重复数据) 
select distinct manager_id from employees

-- 4.  查询职位(JOB_ID)为'ST_CLERK'的员工的工资 
SELECT salary from employees WHERE JOB_ID="ST_CLERK"

-- 5.  查询 50 号部门的员工姓名. 
SELECT last_name from employees WHERE department_id=50

-- 6.  查询 80 号部门工资大于 7000 的员工的全名. 
SELECT first_name,last_name from employees WHERE department_id=50 and salary>7000

-- 7.  查询工资高于 7000 但是没有提成的所有员工. 
SELECT * from employees WHERE commission_pct=null and salary>7000

-- 8.  查询入职日期在 1997-5-1 到 1997-12-31 之间的所有员工信息 
-- SELECT * from employees WHERE hiredate between '1997-5-1' and '1997-12-31'
select *
from employees
where hiredate between str_to_date('1997-5-1','%Y-%m-%d') and str_to_date('1997-12-31','%Y-%m-%d')

-- 9.  显示姓名中没有'L'字的员工的详细信息 
SELECT * from employees WHERE first_name not like "%L%"

-- 10.  查询电话号码以 8 开头的所有员工信息. 
SELECT * from employees WHERE phone_number like "8%"

-- 11.  查询 80 号部门中 last_name 以 n 结尾的所有员工信息 
SELECT * from employees WHERE department_id=80 and last_name like "%n"

-- 12.  查询所有 last_name 由四个以上字母组成的员工信息 
SELECT * from employees WHERE  first_name like '____'
select  * from employees where LENGTH(last_name)>4

-- 13.  查询 first_name 中包含"na"的员工信息. 
SELECT * from employees WHERE  first_name like '%na%'

-- 14.  显示公司里所有员工的工资级别 case when 
select employee_id, salary,
    CASE
        when salary>=15000 then'D'
        when salary>=8001 or salary>=15000 then'B'
        when salary>=5001 or salary>=8000 then'A'
        else 'A'
END as '工资标准'
 from employees
 
 -- 15 根据入职时间打印出员工级别 
 select hiredate,
        case 
            when date_format(hiredate,'%Y')>1990  then '新员工' 
            when date_format(hiredate,'%Y')>=1986 then'普通员工' 
            when date_format(hiredate,'%Y')<=1985  then '资深员工'
end as 员工级别
from employees
 
--  请打印出 1997 年入职的员工
select first_name from employees where date_format(hiredate,'Y')=1978
 
--  把 hiredate 列看做是员工的生日,求本月过生日的员工
select * from employees where date_format(now(),'%m')=date_format(hiredate,'%m')
 
--  查询入职时间超过25年的员工信息
select * from employees where date_format(now(),'%Y')-date_format(hiredate,'%Y')>'25'

--  以  员工id-员工完整姓名-员工工资  的形式查询员工信息
select employee_id,salary,concat(first_name,last_name) from employees
 
--  查询工资高于10000并且岗位id长度小于5的员工信息
select * from employees where salary>10000 and length(job_id)<=5