MySQL学习笔记 04 - 多表查询与单行函数

一、多表查询

在前面的章节中介绍的都是基本的查询，这些查询基本都是基于一个表的数据查询，实际上在项目中需要的数据很少来自于一张单独的表进行处理，经常会用到两张或者两张以上的表进行操作，这种查询两个或者两个以上的数据表或者视图的查询称之为连接查询。这种连接查询通常建立在存在相互关系的父子表之间。比如员工表emp中的部门编号deptno字段都是从部门表deptno的部门编号deptno中取的值表的连接查询包括等值连接、自连接、内连接和外连接等等。

笛卡尔乘积现象
表查询中的笛卡尔乘积现象：多行表在查询时，如果定义了无效连接或者漏写了连接条件，就会产生笛卡尔乘积现象，所谓笛卡尔乘积就是每个表的每一行都和其他表的每一行组合，假设两张表的总行数分别是X行和Y行，笛卡尔乘积就会返回X*Y行记录。

1、等值连接查询

等值连接查询时最常见的一种，通常是在存在主键外键关联关系的表之间进行，并且将连接条件设定为有关系的列（主键-外键）使用等号"="连接相关的表。为了避免笛卡尔乘积现象，n个表进行等值连接查询，最少需要n-1个条件来约束。

• 实例：

SELECT
	dept.dname,
	emp.ename
FROM
	emp,
	dept
WHERE
	emp.deptno = dept.deptno;

2、自联接查询

多表查询不仅仅在多个表之间进行，也可以在一个表中进行多表查询，称为自联接查询。

• 实例：

SELECT
	e1.empno AS 员工编号,
	e1.ename AS 员工姓名,
	e2.empno AS 领导编号,
	e2.ename AS 领导姓名
FROM
	emp AS e1,
	emp AS e2
WHERE
	e1.mgr = e2.empNo;

3、内连接查询

内连接是多表连接查询的一种方式，这种方式使用inner join关键字实现，inner可以省略

1. join子句实现内连接查询。
2. on关键字表示连接条件。
3. 内连接返回满足on条件的记录。

SELECT
	dept.dname,
	emp.ename
FROM
	emp
INNER JOIN dept ON emp.deptno = dept.deptno;

4、外连接查询

外连接分为左外连接（left outer join）和右外连接（right outer join），其值的outer可以省略。

1. 在左外连接中，关键字join左侧的表叫做左表，右侧的表叫做右表，左表的每一行数据去匹配右表的每一行数据，如果左表的数据与右表的数据满足on的条件，则将相关数据返回到查询结果集中，如果左表的数据与右表的数据不满足on的条件，那么左表的数据也将返回的查询结果集中，而右表使用NULL填充结果集。
2. 右外连接与左外连接原理相同，只是右表数据都显示处理，左表不满足on条件的记录用NULL填充结果集。

• 实例：

INSERT INTO dept
VALUES
	(40, '项目部', '西安');

SELECT
	dept.dname,
	emp.ename
FROM
	emp
RIGHT OUTER JOIN dept ON emp.deptno = dept.deptno;

5、子查询

在实际项目中，经常会遇到在WHERE查询条件中的限制条件不是一个确定的值，而是来自另外一个查询的结果。比如公司要查询比员工吴用工资高的员工，首先要获取吴用的工资，然后用这个工资来做条件查询其他的员工。
在SQL中，这种为了给主查询语句提供数据而首先执行的查询语句称为子查询。根据返回结果的不同，可以分为单行子查询、多行子查询等等。

5.1、单行子查询

单行子查询：查询出的结果只有一行，且只有一个字段。

SELECT
	*
FROM
	emp e
WHERE
	e.deptno = (
		SELECT
			d.deptno
		FROM
			dept d
		WHERE
			d.dname = '软件部'
	);

5.2、多行子查询

多行子查询：查询出的结果有多行，但只有一列。如果子查询返回了多行记录，则称这样的嵌套查询为多行子查询，多行子查询就需要用到多行记录的操作符，如：IN、ANY、ALL等等

SELECT
	*
FROM
	dept d
WHERE
	d.deptno IN (SELECT e.deptno FROM emp e);

SELECT
	*
FROM
	emp e1
WHERE
	e1.sal > ANY (SELECT e2.sal FROM emp e2);

SELECT
	*
FROM
	emp e1
WHERE
	e1.sal > ALL (
		SELECT
			e2.sal
		FROM
			emp e2
		WHERE
			e2.job LIKE '%助理'
	);

5.3、多列子查询

多列子查询：查询出多行和多个列。

SELECT
	*
FROM
	emp e1
WHERE
	(e1.sal, e1.comm) = (
		SELECT
			e2.sal,
			e2.comm
		FROM
			emp e2
		WHERE
			e2.ename = '张青'
	);

6、分页查询limit子句

当表中的数据很多时，可以使用分页查询，以降低对数据库服务器的压力。使用limit子句实现分页查询。

SELECT * FROM emp LIMIT 0,2;

1. 使用limit子句实现分页查询。
2. 第一个参数0表示从第几条开始查询。
3. 第二个参数2表示查询出几条数据。

7、综合查询示例

统计2000年以后入职，部门人数超过2人的部门，按照部门人数从多到少排序输出，分页显示，每页5条。

SELECT
	d.deptno as '部门编号',
	d.dname as '部门名称',
	count(*) as '人数'
FROM
	emp as e INNER JOIN dept as d on e.deptno = d.deptno
WHERE
	hirdate >= '2000-01-01'
GROUP BY
	e.deptno
HAVING
	人数 >= 2
ORDER BY
	人数 DESC
LIMIT 0, 5;

二、单行函数

函数用于执行数据处理和进行一些复杂运算，函数的输入值叫参数，在函数的内部对输入的参数进行一些运算，最终计算得到一个结果返回给用户。
MySQL的内置函数用于实现特定的功能，单行函数可以应用于SQL语句中。

1、字符串函数

1.1、 大小写转换函数：UPPER、LOWER

SELECT UPPER('AbcDef'), LOWER('AbcDef');

1.1、 字符串连接函数CONCAT

SELECT CONCAT('hello',' world');

1.2、 获取子字符串函数SUBSTR(注意下标是从几开始的)

SELECT SUBSTR('java web sniper', 10);

1.3、 获取字符串长度 LENGTH

SELECT LENGTH('java web sniper');

1.4、 获取字符串在父字符串中的索引 INSTR

SELECT INSTR('java web sniper','web');

1.5、 去掉左侧空格 LTRIM

SELECT LTRIM('  java web');

1.6、 去掉右侧空格 RTRIM

SELECT RTRIM('java web  ');

1.7、 去掉两端空格 TRIM

SELECT TRIM('  java web  ');

1.8、 替换文本 REPLACE

SELECT REPLACE('hello java','java','sql');

2、数学函数

2.1、 对指定的值x进行四舍五入的操作，可以指定保留的数值位数y ROUND(x,[y])

SELECT ROUND(5.63);
SELECT ROUND(4.36,1);

2.2、 对指定的值x进行截取的操作，可以指定保留的数值位数y TRUNCATE(x,y)

SELECT TRUNCATE(5.6,0);

2.3、 返回不小于指定的值x的最小整数 CEIL(x)

SELECT CEIL(4.56);

2.4、 返回不大于指定的值x的最大整数 FLOOR(x)

SELECT FLOOR(8.5);

2.5、 取绝对值函数 ABS(x)

SELECT ABS(-12);

2.6、 取x除以y的余数函数 MOD(x,y)

SELECT MOD(5,3);

2.7、 取x的符号函数（负数、0、正数） SIGN(x)

SELECT SIGN(-128);

2.8、 取x的y次幂函数 POWER(x,y)

SELECT POWER(2,4);

2.9、 取x的二次方根函数 SQRT(x)

SELECT SQRT(16);

3、日期函数

3.1、当前日期函数

1. 获取当前时间戳函数 CURRENT_TIMESTAMP()

SELECT CURRENT_TIMESTAMP();

2. 获取当前日期函数 CURRENT_DATE()

SELECT CURRENT_DATE();

3. 获取当前时间函数 CURRENT_TIME()

SELECT CURRENT_TIME;

4. 获取当前日期+时间 NOW()

SELECT NOW();

3.2、日期截取函数

1. 返回日期date是星期几 DAYOFWEEK(date)

SELECT DAYOFWEEK('2030-02-03');

2. 返回date是一月中的第几日 DAYOFMONTH(date)

SELECT DAYOFMONTH('2030-02-03');

3. 返回date是一年中的第几日 DAYOFYEAR(date)

SELECT DAYOFYEAR('2030-02-03');

4. 返回date的年份 YEAR(date)

SELECT YEAR('2030-02-03');

5. 返回date中的月份数值 MONTH(date)

SELECT MONTH('2030-02-03');

6. 返回date中的日期数值 DAY(date)

SELECT DAY('2030-02-03');

7. 返回date是一年的第几周 WEEK(date)

SELECT WEEK('2030-02-20');

8. 返回time的小时数 HOUR(time)

SELECT HOUR('10:05:03');

9. 返回time的分钟数 MINUTE(time)

SELECT MINUTE('10:05:03');

10. 返回time的秒数 SECOND(time)

SELECT SECOND('10:05:03');

3.3、日期增加函数 DATE_ADD(date,INTERVAL expr type)

1. date是一个DATETIME或DATE值
2. INTERVAL表示时间间隔，是固定写法
3. expr对date进行加减法的一个表达式
4. type是预期的格式，预期格式见下表。

type值	预期的expr格式
MICROSECOND	MICROSECONDS
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
WEEK	WEEKS
MONTH	MONTHS
QUARTER	QUARTERS
YEAR	YEARS
SECOND_MICROSECOND	SECONDS.MICROSECONDS
MINUTE_MICROSECOND	MINUTES.MICROSECONDS
MINUTE_SECOND	MINUTES:SECONDS
HOUR_MICROSECOND	HOURS.MICROSECONDS
HOUR_SECOND	HOURS:MINUTES:SECONDS
HOUR_MINUTE	HOURS:MINUTES
DAY_MICROSECOND	DAYS.MICROSECONDS
DAY_SECOND	DAYS HOURS:MINUTES:SECONDS
DAY_MINUTE	DAYS HOURS:MINUTES
DAY_HOUR	DAYS HOURS
YEAR_MONTH	YEARS-MONTHS

SELECT DATE_ADD("2030-12-31 23:59:59",INTERVAL 1 SECOND);
SELECT DATE_ADD("2030-12-31 23:59:59",INTERVAL 1 DAY);
SELECT DATE_ADD('2030-01-01 23:59:59', INTERVAL 1 YEAR);
SELECT DATE_ADD('2030-12-31 23:59:59',INTERVAL '1:1' MINUTE_SECOND);
SELECT DATE_ADD('2030-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR);