当前位置:   article > 正文

mysql多表查询、函数查询_mysql多表多条件函数查询

mysql多表多条件函数查询

一、多表查询

1、多表查询概述

1.1、为什么要多表查询

执行多条单表查询语句延时

数据放在一个表出现字段数据冗余

1.2、笛卡尔积错误

select userid depname

from user ,dep

出现每个员工会出现在所有部门错误

正确的方式是需要有连接条件

select userid depname

from user ,dep

where user.depid=dep.id

1.3、多表查询注意

sql优化角度而言,多表查询时,每个字段加上其所在的表

可以给表起别名,在select和where中使用别名,一旦起了别名,在select和where必须使用别名,原因还是根sql执行顺序有关

1.4、多表查询分类

角度1:等值连接、非等值

//等值
select userid depname
from user ,dep
where user.depid=dep.id
//非等值
select userid depname
from user ,dep
where user.id>12

角度2:自连接、非自连接

//自连接 查询员工id以及管理者id
select emp.id,mgr.id
from employee emp ,employee mgr

角度3:内连接 、外连接

内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行

如:

select userid depname from user ,dep where user.depid=dep.id

外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左(或右)表中不满足条件的 行 ,这种连接称为左(或右) 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。

如果是左外连接,则连接条件中左边的表也称为主表 ,右边的表称为 从表 。

如果是右外连接,则连接条件中右边的表也称为主表 ,左边的表称为 从表 。

注意:一般涉及到所有字样就要使用外连接

sql92实现内连接如上

sql92实现外连接使用(+),但是mysql不支持sql92的外连接,Oracle 对 SQL92 支持较好

  1. #左外连接
  2. SELECT last_name,department_name
  3. FROM employees ,departments
  4. WHERE employees.department_id = departments.department_id(+);
  5. #右外连接
  6. SELECT last_name,department_name
  7. FROM employees ,departments
  8. WHERE employees.department_id(+) = departments.department_id;

 而且在 SQL92 中,只有左外连接和右外连接,没有满(或全)外连接。

在mysql中,通常使用sql99语法查询,sql99支持内连接同时也支持外连接

2、sql99实现多表查询

内连接

SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;

注意:inner可以省略

左外连接 (left OUTER JOIN)

SELECT 字段列表
FROM A表 LEFT OUTER JOIN B表
ON 关联条件
WHERE 等其他子句;

注意:OUTER可以省略

右外连接(RIGHT OUTER JOIN)

FROM A表 RIGHT OUTER JOIN B表
ON 关联条件
WHERE 等其他子句;

注意:OUTER可以省略

满外连接(FULL OUTER JOIN)

满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。

SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。 需要注意的是,MySQL不支持FULL JOIN,但是可以用 LEFT JOIN UNION RIGHT join代替。

3、UNION的使用

合并查询结果 利用UNION关键字,可以给出多条SELECT语句,并将它们的结果组合成单个结果集。合并 时,两个表对应的列数和数据类型必须相同,并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

查询中国用户中男性的信息以及美国用户中年男性的用户信息

SELECT id,cname FROM t_chinamale WHERE csex='男'
UNION ALL
SELECT id,tname FROM t_usmale WHERE tGender='male';

4、 7种SQL JOINS的实现

这就是两张表共有的部分(内连接),取交集。
SQL语句:
SELECT * FROM TABLEA A INNER JOIN TABLEB B ON A.KEY=B.KEY;

A独有的部分加上和A和B公共 的部分。也叫左外连接。
SQL语句:
SELECT * FROM TABLEA A LEFT JOIN TABLEB B
ON A.KEY = B.KEY;

这张图恰好跟左外连接相反(右外连接)。
SQL语句如下:
SELECT * FROM TABLEA A RIGHT JOIN TABLEB B
ON A.KEY = B.KEY;

这张图就是A表独有的部分。
SQL语句如下:
SELECT * FROM TABLEA A LEFT JOIN TABLEB B
ON A.KEY = B.KEY
WHERE B.KEY IS NULL;

这张图是B表独有的部分。
SQL语句如下:
SELECT * FROM TABLEA A RIGHT JOIN TABLEB B
ON A.KEY B.KEY
WHERE A.KEY IS NULL;

上面这张图表示的是两张表的所有部分。就是左外连接+右外连接在去重一次就搞定了(全连接,mysql中不支持,oracle中是支持的)。虽然MySQL不支持全连接的直接实现方式,但是提供了间接的实现方式,就是A表独有+B表独有,在去重一次。

SQL语句如下(正常全连接的SQL语句):

SELECT * FROM TABLEA A FULL OUTER JOIN TABLEB B

ON A.KEY = B.KEY;

但是,在mysql中不支持上面这条语句。

MySQL实现全连接的SQL语句:

SELECT * FROM TABLEA A LEFT JOIN TABLEB B

ON A.KEY = B.KEY

UNION

SELECT * FROM TABLEA A RIGHT JOIN TABLEB B

ON A.KEY = B.KEY;

这里解释一下关键字union:就是连接并去重的意思。

同理,这个模型是一个全外连接。

SQL语句如下:

SELECT * FROM TABLEA A FULL OUTER JOIN TABLEB B

ON A.KEY = B.KEY

WHERE A.KEY IS NULL OR B.KEY IS NULL;

在MySQL中上面这条语句还是不支持。但是,我们还是有间接的实现方式。其实就是第4和第5张图加起来去重就OK了。

MySQL中的语句如下:

SELECT * FROM TABLEA A LEFT JOIN TABLEB B

ON A.KEY = B.KEY

WHERE B.KEY IS NULL

UNION

SELECT * FROM TABLEA A RIGHT JOIN TABLEB B

ON A.KEY = B.KEY

WHERE A.KEY IS NULL;

UNOIN 关键字跟上面的作用一样。

5、 SQL99语法新特性

NATURAL

SQL99 在 SQL92 的基础上提供了一些特殊语法,比如 NATURAL JOIN 用来表示自然连接。我们可以把 自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中 所有相同的字段 ,然后进行 等值 连接 。 在SQL92标准中:

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;

在 SQL99 中你可以写成:

SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

USING连接

当我们进行连接的时候,SQL99还支持使用 USING 指定数据表里的 同名字段 进行等值连接。但是只能配 合JOIN一起使用。比如:

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

能看出与自然连接 NATURAL JOIN 不同的是,USING 指定了具体的相同的字段名称,你需要在 USING 的括号 () 中填入要指定的同名字段。同时使用 JOIN...USING 可以简化 JOIN ON 的等值连接。它与下 面的 SQL 查询结果是相同的:

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

表连接的约束条件可以有三种方式:

WHERE, ON, USING WHERE:适用于所有关联查询

ON :只能和JOIN一起使用,只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起 写,但分开写可读性更好。

USING:只能和JOIN一起使用,而且要求两个关联字段在关联表中名称一致,而且只能表示关联字 段值相等

#关联条件
#把关联条件写在where后面
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id;
#把关联条件写在on后面,只能和JOIN一起使用
SELECT last_name,department_name
FROM employees INNER JOIN departments
ON employees.department_id = departments.department_id;
SELECT last_name,department_name
FROM employees CROSS JOIN departments
ON employees.department_id = departments.department_id;
SELECT last_name,department_name
FROM employees JOIN departments
ON employees.department_id = departments.department_id;
#把关联字段写在using()中,只能和JOIN一起使用
#而且两个表中的关联字段必须名称相同,而且只能表示=
#查询员工姓名与基本工资
SELECT last_name,job_title
FROM employees INNER JOIN jobs USING(job_id);
#n张表关联,需要n-1个关联条件
#查询员工姓名,基本工资,部门名称
SELECT last_name,job_title,department_name FROM employees,departments,jobs
WHERE employees.department_id = departments.department_id
AND employees.job_id = jobs.job_id;
SELECT last_name,job_title,department_name
FROM employees INNER JOIN departments INNER JOIN jobs
ON employees.department_id = departments.department_id
AND employees.job_id = jobs.job_id;

二、单行函数

1、函数分类

我们在使用 SQL 语言的时候,不是直接和这门语言打交道,而是通过它使用不同的数据库软件,即 DBMS。DBMS 之间的差异性很大,远大于同一个语言不同版本之间的差异。实际上,只有很少的函数是 被 DBMS 同时支持的。比如,大多数 DBMS 使用(||)或者(+)来做拼接符,而在 MySQL 中的字符串拼 接函数为concat()。大部分 DBMS 会有自己特定的函数,这就意味着采用 SQL 函数的代码可移植性是很 差的,因此在使用函数的时候需要特别注意。

从函数定义的角度出发,我们可以将函数分成 内置函数 和 自定义函数 。在 SQL 语言中,同样也包括了 内置函数和自定义函数。内置函数是系统内置的通用函数,而自定义函数是我们根据自己的需要编写 的

MySQL提供的内置函数从 实现的功能角度 可以分为数值函数、字符串函数、日期和时间函数、流程控制 函数、加密与解密函数、获取MySQL信息函数、聚合函数等。这里,我将这些丰富的内置函数再分为两 类: 单行函数 、 聚合函数(或分组函数) 。

2、数值函数

基本函数

角度和弧度函数

三角函数

对数函数

进制转换函数

3、字符串函数

4、 日期和时间函数

获取日期 时间

日期和时间转换

获取月份、星期、星期数、天数等

日期的操作函数

时间和秒钟转换的函数

计算日期和时间的函数

日期的格式化与解析

日期函数应用

mysql获取当天,昨天,本周,本月,上周,上月的起始时间函数

  1. #今天
  2. SELECT DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') AS '今天开始';
  3. SELECT DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') AS '今天结束';
  4. #昨天
  5. SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') AS '昨天开始';
  6. SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59') AS '昨天结束';
  7. #上周
  8. SELECT DATE_FORMAT( DATE_SUB( DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), INTERVAL 1 WEEK), '%Y-%m-%d 00:00:00') AS '上周一';
  9. SELECT DATE_FORMAT( SUBDATE(CURDATE(), WEEKDAY(CURDATE()) + 1), '%Y-%m-%d 23:59:59') AS '上周末';
  10. #本周
  11. SELECT DATE_FORMAT( SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1), '%Y-%m-%d 00:00:00') AS '本周一';
  12. SELECT DATE_FORMAT( SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-7), '%Y-%m-%d 23:59:59') AS '本周末';
  13. #上面的本周算法会有问题,因为mysql是按照周日为一周第一天,如果当前是周日的话,会把时间定为到下一周
  14. SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), '%Y-%m-%d 00:00:00') AS '本周一';
  15. SELECT DATE_FORMAT( DATE_ADD(SUBDATE(CURDATE(), WEEKDAY(CURDATE())), INTERVAL 6 DAY), '%Y-%m-%d 23:59:59') AS '本周末';
  16. #上月
  17. SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00') AS '上月初';
  18. SELECT DATE_FORMAT( LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 23:59:59') AS '上月末';
  19. #本月
  20. SELECT DATE_FORMAT( CURDATE(), '%Y-%m-01 00:00:00') AS '本月初';
  21. SELECT DATE_FORMAT( LAST_DAY(CURDATE()), '%Y-%m-%d 23:59:59') AS '本月末';

 

5、流程控制函数

6、加密函数

加密与解密函数主要用于对数据库中的数据进行加密和解密处理,以防止数据被他人窃取

7、 MySQL信息函数

MySQL中内置了一些可以查询MySQL信息的函数,这些函数主要用于帮助数据库开发或运维人员更好地 对数据库进行维护工作。

8、其他函数

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/秋刀鱼在做梦/article/detail/1019654
推荐阅读
相关标签
  

闽ICP备14008679号