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 支持较好

#左外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id = departments.department_id(+);
#右外连接
SELECT last_name,department_name
FROM employees ,departments
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获取当天,昨天,本周,本月,上周,上月的起始时间函数

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

 

5、流程控制函数

6、加密函数

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

7、 MySQL信息函数

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

8、其他函数