【数据库】第二章基础函数、聚合函数、条件查询、子查询和多表查询

最新推荐文章于 2022-05-07 22:08:45 发布

313YPHU3

最新推荐文章于 2022-05-07 22:08:45 发布

阅读量203

点赞数

分类专栏：数据库文章标签：数据库

本文链接：https://blog.csdn.net/qq_45593575/article/details/119113030

版权

数据库专栏收录该内容

4 篇文章 1 订阅

订阅专栏

【数据库】第二章基础函数、条件查询、聚合函数、多表查询和子查询

一、基础函数

数据转小写

SELECT name,LOWER(name) from demo;

数据转大写

SELECT name,UPPER(name) from demo;

求数据的长度

SELECT LENGTH(name) from demo;

截取字符串

SELECT name,SUBSTR(name,1,3) from demo;

拼接数据

select name,concat(name,'123') from demo;

替换数据（用 ‘111’ 替换 ‘a’）

select name,replace(name,'a','111') from demo;

判断空，并赋值（如果 comm 是 null，用 20 替换）

select ifnull(age,10) comm from demo;

四舍五入（保留 1 位小数）

select salary,round(salary,1) from demo;

向上取整

select salary,ceil(salary) from demo;

向下取整

select salary,floor(salary) from demo;

获取当前时间

select CURRENT_DATE();	#年月日
select CURRENT_TIME();	#时分秒
select NOW();			#年月日时分秒
#也可以单独获取年、月、日、时、分、秒，任意组合
select year(now()),month(now()),day(now()),hour(now()),minute(now()),second(now());

二、聚合函数

1.COUNT

#COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）
SELECT COUNT(column_name) FROM table_name;
#COUNT(*) 函数返回表中的记录数
SELECT COUNT(*) FROM table_name;
#COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目
SELECT COUNT(DISTINCT column_name) FROM table_name;

2.MAX/MIN

SELECT MAX(column_name) FROM table_name;
SELECT MIN(column_name) FROM table_name;

3.SUM/AVG

SELECT SUM(column_name) FROM table_name;
SELECT AVG(column_name) FROM table_name

4.GROUP BY

#GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

5.HAVING

#在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用
#HAVING 子句可以让我们筛选分组后的各组数据
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

三、条件查询

1.SELECT DISTINCT

DISTINCT 关键词用于返回唯一不同的值

SELECT DISTINCT column_name,column_name
FROM table_name;

2.WHERE

WHERE 子句用于提取那些满足指定条件的记录

#同时满足两个条件
SELECT column_name,column_name
FROM table_name
WHERE column_name operator value AND column_name operator value;
#满足一个条件即可
SELECT column_name,column_name
FROM table_name
WHERE column_name operator value OR column_name operator value;

3.LIKE

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

#选取 name 以 3 开始的记录
SELECT * FROM dmeo
WHERE name LIKE '3%'; 
#选取 name 以 3 结尾的记录
SELECT * FROM demo
WHERE name LIKE '%3'; 
#选取 name 包含 3 的记录
SELECT * FROM demo
WHERE name LIKE '%3%'; 
#选取 name 不包含 3 的记录
SELECT * FROM demo
WHERE name NOT LIKE '%3%'; 
#选取 name 以 3 开始，且 3 后还有一个任意字符的记录
SELECT * FROM demo
WHERE name LIKE '3_';

4.NULL

#过滤字段值为空的记录
select * from demo where name is NULL；
#过滤字段值不为空的记录
select * from demo where name is not NULL；

5.BETWEEN

BETWEEN 操作符选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期

#name 在 value1 和 value2 之间的记录
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

#name 不在 value1 和 value2 之间的记录
SELECT column_name(s)
FROM table_name
WHERE column_name  NOT BETWEEN value1 AND value2;

#name 在 value1 和 value2 之间，且 no 不为 value3 和 value4 的记录
SELECT column_name(s)
FROM table_name
WHERE (column_name BETWEEN value1 AND value2)
AND no NOT IN(value3, value4);

#name 介于 'A' 和 'H' 之间的字母开始的记录
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN 'A' AND 'H';

6.LIMIT

返回前 n 条记录

select * from emp limit 2; #列出前两条记录
select * from emp limit 1,2; #从第 2 条开始,展示 2 条记录
select * from emp limit 0,3; #从第 1 条开始,展示 3 条记录

7.ORDER BY

ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序
ORDER BY 关键字默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，可以使用 DESC 关键字

SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name DESC;

四、子查询

子查询就是将一个查询（子查询）的结果作为另一个查询（主查询）的数据来源或判断条件的查询。常见的子查询有 WHERE 子查询，HAVING 子查询，FROM 子查询，SELECT 子查询，EXISTS 子查询，子查询要使用小括号（）

WHERE子查询

SELECT *
FROM EMP
WHERE SAL < (SELECT AVG(SAL) FROM EMP);
#查询薪资比平均薪资低的员工信息

HAVING子查询

SELECT JOB,AVG(SAL)
FROM EMP
GROUP BY JOB
HAVING AVG(SAL) = (SELECT MAX(AVG(SAL)) FROM EMP GROUP BY JOB);
#查询平均薪资最高的职位及其平均薪资

FROM子查询

SELECT JOB,AVG(SAL)
FROM (SELECT JOB,AVG(SAL) AS AVGSAL FROM EMP GROUP BY JOB)TEMP
WHERE TEMP.AVGSAL>2000;
#查询平均薪资高于2000的职位以及该职位的平均薪资

SELECT子查询

SELECT (SELECT COUNT(*) FROM EMP WHERE JOB = 'SALESMAN')/(SELECT COUNT(*) FROM EMP)
FROM DUAL;
#职位是SALESMAN的员工占总员工的比例

EXISIT子查询
将主查询的数据带到子查询中验证，如果成功则返回 true，否则返回false。主查询接收 true 就会显示这条数据，flase 就不会显示

SELECT *
FROM EMP E
WHERE EXISIT (
SELECT *
FROM DEPT D
WHERE E.DEPTNO = D.DEPTNO);
#查询有部门的员工信息

五、多表查询

多表查询是指基于两个和两个以上的表的查询。在实际应用中，查询单个表可能不能满足你的需求，如显示员工表 emp 中不只显示 deptno，还要显示部门名称，而部门名称 dname 在 dept 表中

1.笛卡尔积

#把两个表的数据都拼接起来
select * from dept,emp;

上面这种查询两个表的方式称为：笛卡尔积(Cartesian product)，又称直积。一般笛卡尔积没有实际的业务意义，但多表查询都是先生成笛卡尔积，再进行数据的筛选过滤

实际开发中应该尽量少用多表联查，因为在查询过程中，要先在内存中构建一个很大的结果集，然后再进行数据的过滤。这个构建过程和所使用的内存资源，包括过滤时的判断，都是既耗费资源，又浪费时间的

2.JOIN

在这里插入图片描述

SQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段

INNER JOIN
INNER JOIN 关键字在表中存在至少一个匹配时返回行
在这里插入图片描述

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
#或者
SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;
#INNER JOIN 与 JOIN 是相同的

LEFT JOIN
LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL
在这里插入图片描述

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
#或者
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;
#在某些数据库中，LEFT JOIN 称为 LEFT OUTER JOIN

RIGHT JOIN
RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL
在这里插入图片描述

 SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;
#或者
SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;
#在某些数据库中，RIGHT JOIN 称为 RIGHT OUTER JOIN

FULL OUTER JOIN（MySQL不支持！）
FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行
FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果
在这里插入图片描述

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;