DQL---基础/条件/排序

DQL 语言—基础/条件/排序

1. 基础查询

/*
select 查询列表 from 表名;
特点：
1. 查询列表可以是：字段、常量值、表达式、函数
2. 查询结果是虚拟表格
*/
#1. 查询单个字段
SELECT last_name FROM employees;
#2. 查询多个字段
SELECT last_name,salary FROM employees ;
#3. 查询所有字段
SELECT * FROM employees;
#4. 查询常量值
SELECT 100;
SELECT 'john';
#5. 查询表达式
SELECT 100%98;
#6. 查询函数
SELECT VERSION();
#7. 别名(as or 空格)
SELECT 100%98 AS result;
SELECT last_name AS 姓,first_name AS 名 FROM employees;
#8. 去重(distinct)
SELECT DISTINCT department_id FROM departments;
#9. +的作用
/*
java中+作用
1. 运算符（数值型）
2. 连接符（字符型）
mysql：
只有运算符的功能 select 100+90；
当遇到字符型时，尝试转化为数值做运算。转化不成功则为0。select ‘100’+90；
只要其中一方为null，则结果为null. select null+10；
*/
#10. 字符串拼接concat
SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees ;

2. 条件查询

/*
select 查询列表 from 表名 where 条件;
执行顺序：首先查表名，再判断条件，再选择列表输出
分类：
1. 按条件表达式筛选 > < = != <> >= <=(条件运算符)
2. 按逻辑表达式筛选 && || ! and or not(逻辑运算符)
3. 模糊查询 like、 between and 、in 、is null 
*/
# 条件表达式
##查询工资大于12000的员工信息
SELECT * FROM employees WHERE salary>12000;
##查询部门编号不等于90的员工名和部门编号
SELECT `last_name`,`department_id` FROM `employees` WHERE `department_id`!=90;
# 逻辑表达式
## 查询工资在10000和20000之间的员工名，工资和奖金
SELECT `last_name`,`salary`,`commission_pct` FROM `employees` WHERE salary>=10000 AND salary<=20000;
## 查询部门编号不在90-110之间或者工资高于15000的员工信息
SELECT * FROM employees WHERE NOT(`department_id`>=90 AND `department_id`<=110) OR salary >=15000;
# 模糊查询
/*
1. like: 一般与通配符配合使用
     通配符有-->% 指的是匹配0个或多个字符；_ 匹配单个字符；
*/
## 查询员工名称包含字符a的信息
SELECT * FROM employees WHERE last_name LIKE '%a%';
SELECT * FROM employees WHERE last_name LIKE '__m%';
SELECT * FROM employees WHERE last_name LIKE '_\_%';
SELECT * FROM employees WHERE last_name LIKE '_&_%' ESCAPE '&';#表示转义
/*
2. between and 
*/
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 200;
/*
3. in
判断某字段值是否在in列表中的某一项
特点： in 列表中的值统一类型
*/
SELECT last_name,job_id FROM employees WHERE job_id IN ('AD_VP','IT_PROG');

/*
4. is null/is not null
= 和<>不能判断null值
*/
SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL;
/*
安全等于 <=>
    IS NULL 只可以判断NULL值，可读性高
    <=> 既可以判断NULL值，也可以判断普通数值，可读性低
*/
SELECT last_name,commission_pct FROM employees WHERE commission_pct <=> NULL;
SELECT last_name,salary FROM employees WHERE salary <=> 12000;

3. 排序查询

# 排序查询
/*
select 查询列表 from 表名 where 筛选条件 order by 排序列表 【asc / desc】
特点： 
     1. 默认升序asc
     2. order by 一般放在查询语句的后面，limit子句例外
*/
SELECT * FROM employees ORDER BY salary DESC;
## 按字段排序
SELECT * FROM employees WHERE department_id>=90 ORDER BY hiredate;
## 按表达式排序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
## 按别名排序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees ORDER BY 年薪 DESC;
## 按照函数排序
SELECT last_name,salary FROM employees ORDER BY LENGTH(last_name);
## 按多字段排序
SELECT * FROM employees ORDER BY salary ,employee_id DESC;
##test
SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees ORDER BY 年薪 DESC,last_name ASC;
SELECT last_name,salary FROM employees WHERE NOT(salary BETWEEN 8000 AND 17000) ORDER BY salary DESC;
SELECT *,LENGTH(email) FROM employees WHERE email LIKE '%e%' ORDER BY LENGTH(email) DESC,department_id;