「这是我参与11月更文挑战的第16天，活动详情查看：2021最后一次更文挑战」
1.基础查询
1.select 查询列表 from 表名
``是为了区分是字段还是关键字
#2.查询常量值
select 100;
select 'a';
#3.查询函数
select version();
#4.查询表达式
select 100%98 ;
#5.起别名1
select last_name as 姓 from employees;
#起别名2
select last_name 姓 from employees;
#6.去重复
select DISTINCT job_id 员工 from employees;
#7.拼接
select CONCAT(last_name,first_name) 姓名 from employees;
#8.为空的判断拼接 IFNULL(expr1,expr2)
select CONCAT(IFNULL(commission_pct,0),'------',salary) from employees;
2.条件查询
1.按条件表达式筛选
<>=<>
2.逻辑表达式筛选
&& || !
and or not
3.模糊查询
like
between and
int
is null
安全等于
<=> 可以判断普通和null
#查询表达式
select 100%98 ;
#起别名1
select last_name as 姓 from employees;
#起别名2
select last_name 姓 from employees;
#去重复
select DISTINCT job_id 员工 from employees;
#拼接
select CONCAT(last_name,first_name) 姓名 from employees;
#为空的判断拼接 IFNULL(expr1,expr2)
select CONCAT(IFNULL(commission_pct,0),'------',salary) from employees;
#>= <>
select salary from employees where salary<>12000;
select * from employees where salary>=12000;
select * from employees where salary BETWEEN 12000 and 30000;
#%表示通配符
select * from employees where first_name like "%a%";
#_表示占一个位置
select * from employees where first_name like "_a%";
#表示转义字符
select * from employees where first_name like "_$_a%" ESCAPE '$';
#or NOT
select * from employees where salary=12000
select * from employees where not salary =12000
#in
select * from employees where salary in(12000,17000);
# is NULL
select * from employees where commission_pct is null;
# 安全等于 <=>
select * from employees where commission_pct <=> null or commission_pct <=> 0.1;
3.排序查询
1、按单个字段排序
select * from employees ORDER BY salary DESC;
2、添加筛选条件再排序
select * from employees where salary > 12000 ORDER BY salary ASC;
#案例：查询部门编号>=90的员工信息，并按员工编号降序
select * FROM employees where department_id >= 90 ORDER BY employee_id ASC;
3、按表达式排序 #案例：查询员工信息 按年薪降序
select * ,12*salary*(IFNULL(commission_pct,0)+1) 年薪 from employees ORDER BY 12*salary*(IFNULL(commission_pct,0)+1) DESC;
4、按别名排序 #案例：查询员工信息 按年薪升序
SELECT * ,12*salary*(IFNULL(commission_pct,0)+1) 年薪 from employees ORDER BY 年薪 DESC;
5、按函数排序 #案例：查询员工名，并且按名字的长度降序
select last_name from employees order by LENGTH(last_name) DESC;
6、按多个字段排序
#案例：查询员工信息，要求先按工资降序，再按employee_id升序
select * from employees order by salary DESC,employee_id ASC;
