mysql之select查询篇2

一、多表查询

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：内连接、外连接

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

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

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

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

2、SQL92 与SQL99分别实现多表查询

2.1、SQL92

在 SQL92 中采用（+）代表从表所在的位置。即左或右外连接中，(+) 表示哪个是从表。

Oracle 对 SQL92 支持较好，而 MySQL 则不支持 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 中，只有左外连接和右外连接，没有满（或全）外连接。

2.2、SQL99

内连接

SELECT 字段列表 FROM A表 INNER JOIN B表 ON 关联条件 WHERE 等其他子句; 注意：inner可以省略

左外连接 (left OUTER JOIN)

#实现查询结果是A 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

mysql之select查询篇2

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

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

4、 7种SQL JOINS的实现

mysql之select查询篇2

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

mysql之select查询篇2

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

mysql之select查询篇2

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

mysql之select查询篇2

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

mysql之select查询篇2

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

mysql之select查询篇2

上面这张图表示的是两张表的所有部分。就是左外连接+右外连接在去重一次就搞定了（全连接，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：就是连接并去重的意思。

mysql之select查询篇2

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

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信息函数、聚合函数等。这里，我将这些丰富的内置函数再分为两类：单行函数、聚合函数（或分组函数）。