03—— DQL语言基础（01）_dql语句基本-优快云博客

本文链接：https://blog.youkuaiyun.com/Jeffxu_lib/article/details/101722443

一、基础查询

如果查询的列表与关键字重名，或包含特殊字符，需要用单引号或双引号将其括起来。MySQL推荐双引号
null 与任何字段拼接、运算的结果都是 null，如：1+null 的结果为null

/* 1、查询列表可以是：表中的字段、常量值、表达式、函数
   2、select 可以查询多个字段
   3、查询的结果是一个虚拟的表格
*/

# select后的查询内容为要显示的查询内容，表名为从哪张表中去查
select 查询内容 from 表名;

# 起别名，方法一：
SELECT 10+8 AS result;         # result的值为10+8
# num1为firsr_name，num2为last_name
SELECT first_name AS num1,last_name AS num2 FROM [表名]

# 方法二：省略as
SELECT first_name num1,last_name num2 FROM [表名]

# 去重，将查询结果中的重复部分的去掉，如去除id中的重复部分
SELECT DISTINCT id FROM newTable;

# 加号的作用，只是数值运算
SELECT 10+8;

# 将查询的多个结果拼接起来，使用 CONCAT 函数
SELECT CONCAT("first"," name") AS result;


/* 判断是否为空，num01是可能为空的字段；当num01为空时，num02为返回的值，可以自行设定，如设为0
表示当num01为空时，返回0   
*/
SELECT IFNULL(num01,num02);

二、条件查询

1、常用通配符：

% 任意多个字符，包含0个
_ 任意单个字符

2、条件查询的常见用法：

【注】：where 一定放在 from 之后；多个条件限制时，条件间用 and 进行连接。

# 当where后的条件成立，就显示出来。其执行顺序为：先查看表名，再看筛选条件，最后再查询列表
/*
基本语法：
       select  
                查询列表
        from
                表名
        where
                筛选条件;

筛选分类：
        条件运算符（=、>、<、<=、>=、<>、!=）
        逻辑表达式（and or not && || !）
        模糊查询（like 、between and、in、is null、is not null）
*/
*******************************************************************

# 1、按条件表达式筛选出表id中所有num大于88的
SELECT
	*
FROM
	id
WHERE
	num>88;

# 2、按逻辑表达式筛选出age在30到60之间的
SELECT
	first_name,
    age
FROM
	id
WHERE
	age>30 AND age<60;

*******************************************************************

# 3-1、模糊查询like（一般查看是否包含某字段），查询员工名中包含小写字母a的所有员工，百分号表示通配符
SELECT
	*
FROM
	employees
WHERE
	theName LIKE '%a%';

# 3-2、模糊查询like，查询员工名中第4个字符为a的。a前有三个下划线
SELECT
	theName
FROM
	employees
WHERE
	theName LIKE '___a%';


# 3-3、自定义转义字符。查询员工名第二个字符为下划线。如下定义$ 为转义字符，也可以直接用斜线
SELECT
	theName
FROM
	employees
WHERE
	theName LIKE '_$_a%' ESCAPE '$' ;

# 4-1、模糊查询between and，员工编号在10到100之间的，包含临界值。
SELECT
	theNum
FROM
	employees
WHERE
	theNum BETWEEN 10 AND 100;
    # theNum NOT BETWEEN 10 AND 100;   不在10到100之间的



# 5-1、模糊查询in,查询员工工种编号是A、B、C其中之一的。
SELECT
	last_name,
	job_id
FROM
	employees
WHERE
	job_id IN('A','B','C');
# 等价于： job_id='A' or job_id='B' or job_id='C';

# 6-1、模糊查询is null 或is not null，判断员工中奖金为NULL的
SELECT
	last_name,
	bonus
FROM
	employees
WHERE
	bonus IS NULL;

# 6-2、安全等于 <=> ，可以判断一般的值，也可以判读null 值，但是可读性差。如下所示
SELECT
	last_name,
	bonus
FROM
	employees
WHERE
	bonus <=> NULL;

3、查询员工号为176的员工的姓名和部门号和年薪

【注】：因为奖金bonus可能为null，所以要加上ifnull 来进行判断，当bonus为空时，让其为0

SELECT
	last_name,
	department_id,
	salary*12*(1+IFNULL(bonus,0)) AS 年薪
FROM
	employees
WHERE
	number_id=176;

4、注意事项

MySQL中判断不等于可以使用 != ，但一般建议使用 <> 来进行判断
=或<> 不能用于判断 null 值，常使用 is null 或 is not null 来进行判断

三、排序查询

1、语法

select 查询的内容
from 表名
[where  筛选条件]                      # 该项可有可无
order by 待排序的内容  [asc|desc]       # 将待排序的项目升序或降序排列

# asc 为升序，desc 将降序。省略时按默认的升序排序

2、应用

# 1、按工资由高到低的顺序，来显示员工下姓名、工种、薪资
SELECT first_name,job_id,salary 
FROM employees 
ORDER BY salary DESC;

# 2、查询部门编号>=90 的员工信息，按入职时间的先后顺序
SELECT *
FROM employees
WHERE department_id>=90
ORDER BY hiredate ASC;

# 3、按年薪的高低显示员工的信息和年薪
SELECT *, salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 ASC;

# 4、多个字段排序：如先按工资排序，若工资相同，再按员工编号排序。先排序的在前，后排序在后
SELECT *
FROM employees
ORDER BY salary ASC,employee_id DESC;

3、示例

# 查询员工姓名、部门、薪资。并按薪资降序、姓名升序来进行排列
SELECT last_name 姓名,department_id 部门,salary 薪资
FROM employees
ORDER BY salary DESC,last_name ASC;

四、常见函数

1、调用语法

# []中的内容可有可无，具体根据需要来定
select 函数名（实参列表） [from  表名]

2、函数分类：单行函数、分组函数

（1）、单行函数：

一、字符函数

# length 函数获取参数的字节数。在字符集为utf8下，英文占一个字节，中文占3个字节
SELECT LENGTH('hello');

# concat 函数，拼接字符
SELECT CONCAT('a','+','b');

# upper、lower，将字符大写或小写
SELECT UPPER('time')；
SELECT LOWER('TiMe')；

# substr、substring， 截取字符。MySQL中索引从1开始
SELECT SUBSTR('this is just a test',6) output;  #获取第六个字符之后的所有字符，包含第6个
SELECT SUBSTR('this is just a test',1,4) output; # 1为第一个字符，4为字符长度

# instr，返回字串第一次出现的索引。如：返回one在someone中第一次出现的位置
SELECT INSTR('someone','one');

# trim，返回字符串前后的空格，还可以去除字符串前后指定的字符
SELECT TRIM('a' FROM 'aaaaathis is justaaaaa');  #结果为：this is just

# lpad，用指定的字符实现左填充至指定的长度
SELECT LPAD('tom',6,'$');  #输出为 $$$tom ,一共6个字节

# rpad，指定字符填充至指定长度的右填充
SELECT RPAD('tom',6,'$');  #输出为 tom$$$ ,凑足6个字符

# replace，替换。如下将字符串中的like 替换成了do
SELECT REPLACE('hello there is some one who like this','like','do');

********************************************************************
二、数学函数

# round，四舍五入。如下四舍五入并保留两位小数
SELECT ROUND(3.1415926,4);

# ceil，向上取整，返回大于等于该数的值

# floor，向下取整

# truncate，截断。如下保留4位小数，其余的舍弃
SELECT TRUNCATE(3.1415926,4);

# mod，取余数。如下取10的余数
SELECT MOD(10,3);  # 等效于 10%3

********************************************************************
三、日期函数

# now，返回当前系统的日期+时间
SELECT NOW();

# curdate，返回当前系统日期，不包含时间
SELECT CURDATE();

# curtime，返回时间，不含日期

# 可以获取指定的部分，如：年、月、日、时分秒等
SELECT YEAR(NOW());    # 只有年
SELECT MONTH(NOW());   # 只有月
SELECT DAY(NOW());     # 只有天

# str_to_date：将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE('2019-9-30','%Y-%c-%d') AS 'date';

# date_format：将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%Y年%c月%d日');

********************************************************************
四、其它函数

SELECT VERSION();  # 查看版本
SELECT DATABASE(); # 查看当前数据库
SELECT USER();     # 查看当前用户

********************************************************************
五、流程控制函数

# if 函数，相当于if else，如果expt为true，输出up，否则输出down
SELECT IF(expt,'up','down');

/* case，用法一：类似switch case
语法：（注意：then后面为值时不加分号，为语句时要加分号）
case  要判断的字段或表达式
when  常量1  then 要显示的值1或语句1；
when  常量2  then 要显示的值2或语句2； 
..........
else  要显示的值n或语句n；
end
*/
/*  case 应用一：
查询员工工资、部门、及新工资。新工资计算方式如下：
部门为30，则新工资乘1.1
部门为40，则新工资乘1.2
部门为50，则新工资乘1.3
其余的为原工资
*/
SELECT 
	salary,
	department_id,
	CASE department_id
	WHEN 30 THEN salary*1.1
	WHEN 40 THEN salary*1.2
	WHEN 50 THEN salary*1.3
	ELSE salary
	END AS  newSalary
FROM 
	employees;


/* case 用法二：类似多重if
语法：
case
when  条件1  then 要显示的值1
when  条件2  then 要显示的值2
..........
else  要显示的值n或语句n；
end
*/
/* case 应用二：
查询员工工资情况：
如果工资大于20000，显示A级别
如果工资大于15000，显示B级别
如果工资大于10000，显示C级别
否则显示D级别
*/
SELECT  salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 AND salary<20000 THEN 'B'
WHEN salary>10000 AND salary<15000 THEN 'C'
ELSE 'C'
END AS theSalary
FROM employees;

应用实例：

查询有奖金的员工姓名、入职日期、奖金，并按照奖金升序进行排序：

SELECT last_name 姓名,DATE_FORMAT(hiredate,'%Y年%c月%d日') 入职日期,commission_pct 奖金
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY commission_pct ASC;

（2）、分组函数

分组函数主要用于统计使用，又称为统计函数、聚合函数、组函数。
其作用是传进一组值，经过处理后，返回一个值
都会忽略 NULL
可以与 distinct 搭配使用，来去除重复
和分组函数一同查询的字段有限制

# sum 求和
# avg 平均值
# max 最大值
# min 最小值
# count 计数，计算非空值的个数，推荐使用count(*)

/* 对工资进行处理 */
SELECT 
	SUM(salary),
	AVG(salary),
	MAX(salary),
	MIN(salary),
	COUNT(salary)
FROM employees;

运行结果如下

# count 函数的详细介绍

# employees 中所有非空的字段都会被统计，常用于统计行数
select count(*) from employees;

# 统计行数的另一种方式，在count中可以用任何常量，如下使用 1
SELECT COUNT(1) FROM employees

五、分组查询

1、group by 子句将表中的数据分成若干组，

查询的列表必须特殊，要求是分组函数和group by 后出现的字段
注意各个语句的位置
from 的条件为可以直接在原表中进行筛选的字段
having 的条件为根据分组后的结果，再来进行筛选

其语法为：

select  对分组数据的操作，列（要求出现在group by 后面）
from  表名
[where 筛选条件]
group by 分组的列表
[having 根据分组后进行筛选的条件]
[order by 子句]

2、应用：分组前的筛选

（1）、查询每个工种的最高工资，job_id 为工种

SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;

（2）、查询每个位置上的部门个数

SELECT COUNT(*),location_id 
FROM departments
GROUP BY location_id;

（3）、查询邮箱中包含a字符的，每个部门的平均工资

SELECT AVG(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

（4）、查询有奖金的每个领导手下员工的最高工资

SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

3、应用：分组后筛选。根据分组后的结果来进行筛选，条件为语句为： having

（1）、查询哪个部门的员工数大于2

【分析】此时每个部门的员工数需要分组统计后才能知道，所以需要分组后，再根据结果进行筛选

SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;

（2）、查询每个工种有奖金的员工的最高工资大于12000的工种编号和最高工资

SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL         # 查看是否有奖金
GROUP BY job_id                          # 根据工种来进行分组
HAVING MAX(salary)>12000;                # 筛选结果中，最高工资大于12000的

4、按表达式或函数分组

按员工姓名的长度分组，查询每一组员工个数，筛选员工个数大于5的有哪些

SELECT COUNT(*) num,LENGTH(last_name) len_name
FROM employees
GROUP BY len_name
HAVING num>5;

5、按多个字段分组

查询每个部门每个工种的员工的平均工资，并按升序进行排列

SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id
ORDER BY AVG(salary) ASC;

【总结】分组查询中筛选条件分为两类：分组前筛选、分组后筛选

分组前筛选，数据源：原始表、位置：group by 子句前、关键字：where
分组后筛选，数据源：分组后的结果中、位置：group by 子句后、关键字：having
分组函数作为条件的肯定是分组后筛选
能用分组前筛选的优先使用分组前筛选
group by 子句支持单个字段分组，多字段分组（逗号隔开，不分先后顺序）

六、连接查询

1、基本知识

含义：连接查询又称多表查询，当要查询的字段来自于多个表时，使用连接查询
查询的字段一般要加上表名来进行限制，除非该字段只出现在一张表中，不会引起歧义
使用连接查询一般要为表起别名，因为不同表中可能会有同样的字段名，通过别名来添加限制可以让代码简洁
当为表起别名后，查询的字段就只能使用别名来进行限定，不能再使用原来的表名

2、等值连接的应用

（1）、查询两个表中id相匹配的员工

SELECT NAME,boyName   # name属于一个表，boyName属于另一个表
FROM beauty,boys
WHERE beauty.boyfriend_id=boys.id;  # 判断条件

（2）、查询员工名和对应的部门名（员工名和部门名分别在两张表中）

SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id=departments.department_id;

（3）、查询员工名、工种号、工种名

# 在查询的字段前需添加表名来进行限制，因为不同表中可能有同名的字段
SELECT employees.last_name,employees.job_id,job_title
FROM employees,jobs
WHERE employees.job_id=jobs.job_id;

# 为表起别名可以让代码简洁
SELECT e.last_name,e.job_id,j.job_title
FROM employees e,jobs j
WHERE e.job_id=j.job_id;

（4）、查询有奖金的每个部门的部门名、部门领导编号和该部门的最低工资

SELECT d.department_name,d.manager_id,MIN(salary)
FROM departments d,employees e
WHERE e.`commission_pct` IS NOT NULL
AND d.`department_id`=e.`manager_id`
GROUP BY d.department_name;

（5）、三表连接：查询员工名、部门名和所在城市

SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id` 
AND d.`location_id`=l.`location_id`;

3、自连接应用（SQL92的标准）

查询的字段位于同一张表中，此时可以将其看成两张表

如：查询员工名和其上级的名称

/*
将 employees e 看成员工表；employees m 看作上级表
相当于拿员工表的主管id和主管表进行对比，从而查出员工的上级
注意限制的条件是员工的主管id和主管表的id进行对比
*/
SELECT e.last_name,e.employee_id,e.manager_id,m.last_name,m.employee_id,m.manager_id
FROM employees e,employees m
WHERE e.manager_id=m.employee_id;