基础查询
# 显示常量
select 100;
select 'john';
# 显示表达式
select 70*20;
# 显示函数
SELECT length('john');
SELECT VERSION();
# 显示表中字段
select last_name from employees;
#显示多个字段＋ 该别名
SELECT last_name'名字',email'youxiang' from employees;
SELECT last_name as '名字',email as 'youxiang' from employees;
#如何去重
SELECT DISTINCT email from employees;
条件查询
/**
条件拆查询
**/
#案例1：查询月薪>5000的员工信息
select * from employees where salary > 5000
#案例 查询月薪不等于12000的员工信息
SELECT *FROM employees WHERE salary <> 12000
#案例1：查询月薪在5000到12000的员工工资和姓名
SELECT first_name,employee_id from employees WHERE salary between 5000 and 12000
#案例3：查询部门编号=90 或 月薪>10000并且月薪<15000的员工信息
select first_name from employees where employee_id = 90 or salary between 10000 and 15000
基础查询练习
#4. 显示表departments的结构，并查询其中的全部数据 desc 表名;
DESC departments;
# 显示出表employees中的全部job_id（不能重复）distinct
SELECT DISTINCT job_id from employees
#6.显示出表employees的全部列，各个列之间用逗号连接，列头显示成OUT_PUT
SELECT CONCAT(last_name,'||',email) as OUT_PUT from employees
#7.显示出表employees部门编号在80-100之间 的姓名、职位
SELECT * from employees where department_id BETWEEN 80 and 100
SELECT last_name,job_id,department_id FROM employees
WHERE department_id>=80 AND department_id<=100;
#8.显示出表employees的manager_id 是 100,101,110 的员工姓名、职位
SELECT last_name FROM employees where manager_id in(100,101)
/**
模糊查询
**/
#2.查询员工号为176的员工的姓名和部门号和年薪
SELECT first_name,job_id,salary*12*(1+IFNULL(commission_pct,0))
from employees where employee_id = 176
#3.选择工资不在5000到12000的员工的姓名和工资
SELECT first_name,salary from employees where salary not BETWEEN 5000 and 12000
#4.选择在20或50号部门工作的员工姓名和部门号
SELECT first_name,department_id from employees
WHERE department_id = 20 or department_id =50
#5.选择公司中没有管理者的员工姓名及job_id
SELECT first_name,job_id from employees where manager_id is null
#6.选择公司中有奖金的员工姓名，工资和奖金级别
SELECT first_name,salary,commission_pct
from employees WHERE commission_pct is not null
#7.选择员工姓名的第三个字母是a的员工姓名
SELECT first_name from employees where first_name LIKE '__a%'
#8.选择姓名中有字母a和e的员工姓名
SELECT first_name from employees
WHERE first_name like '%a&e%' or first_name like '%e%a%';
#9.显示出表employees表中 first_name 以 'e'结尾的员工信息
SELECT first_name from employees WHERE first_name like '%e'
/**
排序查询
**/
#一、按单个字段进行排序
SELECT * FROM employees ORDER BY salary DESC
#二、按多个字段进行排序
SELECT * from employees ORDER BY salary,employee_id DESC
#三、按表达式排序
#案例：按年薪降序
SELECT *,salary*12*(1+IFNULL(commissiom_pct,0)) 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(commissiom_pct,0)) DESC
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
