绵阳建设网站,自学网设计,wordpress下载主题博客,php响应式网站-- 1. 查询员工表所有数据#xff0c;并说明使用*的缺点 SELECT * from employees -- *号查询效率低
-- 2. 查询所员工的 email 全名,公司 email 统一以 qq.com 结尾. SELECT email from employees WHERE email like %qq.com
-- 3. 打印公司里…-- 1. 查询员工表所有数据并说明使用*的缺点 SELECT * from employees -- *号查询效率低
-- 2. 查询所员工的 email 全名,公司 email 统一以 qq.com 结尾. SELECT email from employees WHERE email like %qq.com
-- 3. 打印公司里所有的 manager_id(去除重复数据) select distinct manager_id from employees
-- 4. 查询职位(JOB_ID)为ST_CLERK的员工的工资 SELECT salary from employees WHERE JOB_IDST_CLERK
-- 5. 查询 50 号部门的员工姓名. SELECT last_name from employees WHERE department_id50
-- 6. 查询 80 号部门工资大于 7000 的员工的全名. SELECT first_name,last_name from employees WHERE department_id50 and salary7000
-- 7. 查询工资高于 7000 但是没有提成的所有员工. SELECT * from employees WHERE commission_pctnull and salary7000 -- 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_id80 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 salary15000 thenD when salary8001 or salary15000 thenB when salary5001 or salary8000 thenA 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 salary10000 and length(job_id)5
