DQL语言(查询)
(1) 基础查询
语句:
select 查询列表
from 表名;
特点:
-
查询可以是字段、常量、表达式、函数
-
查询结果是虚拟的表格
字段查询
# 查询单个字段
SELECT last_name FROM employees;
# 查询多个字段
SELECT email, last_name FROM employees;
# 查询所有字段
SELECT * FROM employees;
PS:``是着重号,强调字段名称
运算与函数
# 运算
SELECT 100;
SELECT 'join';
SELECT 100*78;
SELECT VERSION();
别名
# 方式一
SELECT email AS 邮箱, last_name AS 名 FROM employees;
SELECT 100 AS 结果;
SELECT 'join' AS 结果;
# 方式二
SELECT email 邮箱, last_name 名 FROM employees;
注意:别名违反规范需要加双引号或单引号
去重查询
查询员工涉及到的所有部门编号
SELECT DISTINCT department_id FROM employees;
加号的作用
PS:sql中的加号只用运算功能
select '100'+90; 加号尝试str->int,失败未0,有一个为null结果为null
组合显示员工的姓名
SELECT
CONCAT(last_name,first_name) AS 姓名
FROM
employees;
(2) 条件查询
语句:
select
查询列表
from
表名;
where
条件;
分类:
- 按条件表达式筛选
- < > = != <> >= <=
- 按逻辑表达式筛选
- && || !
- and or not
- 模糊查询
- like
- between and
- in
- is null
条件表达式
SELECT
*
FROM
employees
WHERE
salary > 12000;
逻辑表达式
查询部门为90 工资大于12000的人
SELECT
*
FROM
employees
WHERE
salary > 12000
AND
department_id = 90;
查询部门编号不在90-110之间,或者工资高于15000的人
SELECT
*
FROM
employees
WHERE
NOT(department_id>=90 AND department_id<=110) OR salary>15000;
模糊查询
- LIKE
# 查询员工名包含字符a的
SELECT
*
FROM
employees
WHERE
last_name LIKE '%a%'; # %作为通配符
# 查询员工名中第二个字符为下划线的
SELECT
*
FROM
employees
WHERE
last_name LIKE '_\_%';
指定字符串中的某个字符为需要转义的字符
last_name LIKE '_$_' ESCAPE '$';
% 任意多个字符,包含0个字符
_ 任意单个字符
- BETWEEN AND
# 查询员工id为100 到120之间 100<= x <= 120
SELECT
*
FROM
employees
WHERE
employee_id BETWEEN 100 AND 120;
- in
# 查询员工的工种编号时IT_PROG AD_VP AD_PRES中的一种
SELECT
*
FROM
employees
WHERE
job_id IN ('IT_PROG', 'AD_VP', 'AD_PRES') ; #in列表中类型要相同,并且不支持通配符
- is null / is not null
# 查询没有奖金的员工
SELECT
*
FROM
employees
WHERE
commission_pct IS NULL ;
- 安全等于 <=>
# 查询奖金=12000的员工
SELECT
*
FROM
employees
WHERE
salary <=> 12000;
- PS 计算年薪
SELECT
last_name,
department_id,
salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM
employees
(3) 排序查询
语句:
select colm
from tablename
[where -]
order by 排序列表 asc|desc
asc 升序 可以省略
desc 降序
样例
# 普通排序:查询员工按照薪资排序
SELECT
*
FROM
employees
ORDER BY salary ASC;
# 带条件:部门编号大于90 的薪资排序
SELECT
*
FROM
employees
where
department_id >=90
ORDER BY salary ASC;
# 按表达式排序: 按年薪排序
SELECT
last_name,
department_id,
salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM
employees
ORDER BY 年薪 ASC;
# 按函数排序: 按姓名长度显示员工
SELECT
LENGTH(last_name) 长度
last_name,
salary
FROM
employees
ORDER BY LENGTH(last_name) ASC;
# 多重排序:先按工资排序,再按员工编号排序
SELECT
*
FROM
employees
ORDER BY salary ASC, employee_id DESC;