数据库基本查询

一、基本SELECT语句

#1.1.查询员工12个月的工资总和，并起别名为ANNUAL SALARY

select salary*12 As ANNUALSALARY from company.employees

#1.2.查询employees表中去除重复的job_id以后的数据

select distinct job_id from company.employees

#1.3.查询工资大于12000的员工姓名和工资

select first_name,last_name,salary from company.employees where salary > 12000

#1.4.查询员工号为176的员工的姓名和部门号

select first_name,last_name,department_id from company.employees where employee_id=176

#1.5.显示表 departments 的结构，并查询其中的全部数据

SELECT * FROM company.departments;

二、运算符

#2.1.选择工资不在5000到12000的员工的姓名和工资

select first_name,last_name,salary from company.employees where salary not between 5000 and 12000;

#2.2.选择在20或50号部门工作的员工姓名和部门号（!!!or,用in)

select first_name,last_name,department_id from company.employees where department_id in (20,50)

#2.3.选择公司中没有管理者的员工姓名及job_id

select first_name,last_name,job_id from company.employees where manager_id is NULL

#2.4.选择公司中有奖金的员工姓名，工资和奖金级别

select first_name,last_name,salary,commission_pct from company.employees where commission_pct is not NULL

#2.5.选择员工姓名的第三个字母是a的员工姓名

select first_name,last_name from company.employees where first_name like '__a%'

#2.6.选择last_name中有字母a和k的员工姓名

select first_name,last_name from company.employees where last_name like '%a%k%' or '%k%a%'

#2.7.显示出表 employees 表中 first_name 以 'e’结尾的员工信息

select first_name,last_name from company.employees where first_name like '%e'

#2.8.显示出表 employees 部门编号在 80-100 之间的姓名、工种–???

select first_name,last_name,job_id from company.employees where department_id between 80 and 100

#2.9.显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、工资、管理者id

select first_name,last_name,salary,manager_id from company.employees where manager_id in (100,101,110)

三、排序和分页

#3.1. 查询员工的姓名和部门号和年薪，按年薪降序,按姓名升序显示

SELECT * FROM company.employees;
select first_name,last_name,department_id,salary*12 AS lianxing
from company.employees
order by lianxing DESC,first_name,last_name ASC

#3.2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序，显示第21到40位置的数据//！！！！！！！

select first_name,last_name,salary
from company.employees
group by salary
order by salary desc
limit 20,20  //显示第21到40位置的数据

#3.3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序

select * from company.employees
where email like "%e%"
order by LENGTH(email) desc ,department_id asc