MySQL查询

SELECT

SELECT 字段1,字段2,…… FROM 表名;
SELECT * FROM employes;
/* `*` 表中的所有的字段*/

列的别名

加空格直接写在列名后

AS alias（别名）可省略

列的别名可以用一对双引号"可以有空格"引起来，不要使用’ ';

SELECT employee_id em_id,last_name AS lname,department_id "部门id",
FROM employees;

去除重复行

DISTINCT

SELECT DISTINCT department_id
FROM employees;

空值参与运算

空值：NULL

null不等同于0，’ ',"null"

空值参与运算：结果也为空

SELECT salary*(1+IFNULL(commission-pct,0))*12 "年工资"
FROM employees;
/*
当comminssion_pct为NULL时则commission-pct为0
*/

着重号

`  `

字段名、表名与保留字、关键字冲突时使用

SELECT * FROM `order`;
/*
查询名叫order的表
*/

查询常数

SELECT ABC,123,salary
FROM employees;
/*
在SELECT查询结果中增加一列固定的常数（ABC/123）
*/

过滤数据

WHERE紧随FROM

SELECT *
FROM employees
WHERE department_id=90;
/*
查询90号部门员工的信息
*/

运算符

算术运算符

条件	谓词	例
+	加	100+'1’会将字符串转换为数值型（隐式转换） 100+'a’将 ‘a’ 看作 0
-	减
*	乘
/或DIV	除	/ 会自动转换为浮点型 DIV结果取整数 分母如果为0，则结果为NULL
%或MOD	取余数	%出来的数有正有负，符号取决于左操作数 mod只能是正

比较运算符

比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回1，比较的结果为假则返回0，其它情况则返回NULL

符号

=

等于

• 判断等号两边的值、字符串或表达式是否相等；相等返回1、不相等返回0
• 规则如下
  • 两边都是字符串——比较字符串的ANIS编码
  • 一个整数、一个为字符串——字符串转化为数字（隐式转换）进行比较
  • 任意一个为NULL——结果为NULL

<=>

<=> NULL相似于is NULL

安全等于

为NULL而生

• 与等于运算符作用相似

• 区别是可以来对NULL进行判断

  • 在两个操作数均为NULL时，其返回值为1，而不为NULL；
  • 当有一个操作数为NULL时，其返回值为0，而不为NULL；

其它

符号
<>(!=)	不等于
<(<=)	小于(小于等于)
>(>=)	大于(大于等于)

非符号（关键字）

IS NULL`IS NOT NULL\ ISNULL`

• IS NULL和 ISNULL 查询为NULL的数据
• IS NOT NULL查询不为NULL的数据

LEAST( ) \ GREATEST( )

• LEAST( )最小
• GREATEST( )最大

BETWEEN……AND……

• 在 条件下界1 和 条件上界2 之间（包含边界）

IN (set) ` NOT IN (set)`

/*查询部门为10，20，30部门的员工信息*/
SELECT last_name,salary,department_id
FROM empolyees
WHERE department_id IN (10,20,30);

/*查询工资不是6000，7000，8000的员工信息*/
SELECT last_name,salary,department_id
FROM employees
WHERE salary NOT IN(6000,7000,8000);

LIKE

模糊查询

• % ：代表不确定个数的字符（0个，1个，或多个）

• _ ：代表一个不确定的字符

• 转义字符：\ '_ \ _a’第二个字符是 _ 且第三个字符是 a

  • 或者用 ESCAPE 表转义符

    WHERE last_name LIKE  '_$_a' ESCPAE '$';
    /*将$作为转义符，此段为查询第二个字符是_且第三个字符是 a的last_name*/
    

  /*查询last_name中包含字符‘a'且包含字符’e‘的员工信息*/
  SELECT *
  FROM employees
  WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';
  /*或*/
  WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';
  

正则表达式

REGEXP

expr REGXP 匹配条件

expr满足匹配条件，返回1；反之为0；

任意一个条件为NULL，则结果为NULL

• '^'匹配以该字符后面的字符开头的字符串
• '$'匹配以该字符前面的字符结尾的字符串
• '.'匹配任何一个单元符
• "[…]“匹配在方括号内的任何字符。还可以加个”-"控制字符范围
  • "[abc]“匹配"a”“b"或"c”
  • "[a-z]"匹配任何字母
  • "[0-9]"匹配任何数字
• "[ ^… ] "匹配不在字符集合中的任意一个字符
• '*'匹配零个或多个二在它前面的字符
  • "x*"匹配任何数量的’x’字符
  • "[0-9]"匹配任何数量的数字
  • "*"匹配任何数量的任意字符
• “{n,m}”
  • "{n}"匹配前面的字符串n次
  • "{n,m}"匹配前面的字符串至少n次，至多m次

SELECT'shkstart' REGEXP '^S','shkstart' REGEXP 't$','shkstart' REGEXP 'hk'
FROM DUAL;

逻辑运算符

运算符	作用	示例
NOT或!	逻辑非	WHERE NOT A
AND或&&	逻辑与 (任意一个假就假)	WHERE a AND b WHERE a && b
OR或||	逻辑或 (任意一个真就真)	WHERE a OR b WHERE a || b
XOR	逻辑异或 (一真一假为真)	WHERE a XOR b

OR可以和AND一起使用，但AND的优先级会高于OR

位运算符

运算符	作用	示例
&	按位与（位AND）[二进制比较俩数，有一个1则为1]	SELECT a & b
`|`	按位或（位OR） [二进制比较俩数，两个都为1则为1]	SELECT a | b
^	按位异或（位XOR） [二进制比较俩数，一0一1则为1]	SELECT a ^ b
~	按位取反 [二进制，1变0，0变1]	SELECT ~a
>>	按位右移	SELECT a>>2
<<	按位左移	SELECT b<<2

运算符的优先级

越往下优先级越高，但想自定义优先级时可以有"()"括起来表最高级

优先级	运算符
1	:=, = (赋值)
2	`||,OR,XOR`
3	&&,AND
4	NOT
5	BETWEEN,CASE,WHEN,THEN和ELSE
6	=,<=>,>=,>,<=,<,<>,!=(比较运算符),IS,LIKE,REGEXP和IN
7	`|`
8	&
9	<<与>>(左、右移)
10	_和+
11	*,/,DIV,%和MOD`
12	^
13	-(负号)和~(按位取反)
14	!
15	()

排序

ORDER BY 关键字 ASC/DESC

• ASC(ascend)：升序【默认值】
• DESC(descend)：降序
• 声明时仅可在LIMIT之前作倒数第二

ORDER BY可以使用别名

SELECT emloyee_id,salary,salary*12 annual_sal
FROM emloyees
WHERE ORDER BY annual_sal;

/*不能在WHERE中使用,因为FROM和WHERE会比ORDER BY先执行*/
/*WHERE annual_sal>81600;*/

二级排序

• 显示员工信息，按照department_id的降序，salary的升序排列

SELECT employee_id,salary,department_id
FROM employees
ORDER BY department_id DESC,salary ASC;
/*DESC不可省略*/

分页

LIMIT 偏移量，条目数；

或 LIMIT 条目数 OFFSET 偏移量;（MySQL8.0新特性）

• 当取第一页时，偏移量可省略
• 声明时总是在语句最后，作倒数第一

• 每页显示20条记录，显示第3页

SELECT emloyee_id,last_name
FROM employees
LIMIT 40,20;

/*每页显示pageSize条记录，此时显示第pageNo页*/
/*LIMIT （pageNo-1）*pageSize,pageSize*/

多表查询

• 查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表
  • 建议：从sql优化的角度，每个字段钱都指明其所在的表
• 可以给表起别名，在SELECT和WHERE中使用表的别名
  • 如果给表起了别名，一旦在SELECT或WHERE中使用别名的话，则在后续使用中必须使用表的别名，不能再使用表的原名

内连接

INNER JOIN …… ON<连接条件> 

合并聚友同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行。

SELECT last_name,department_name,city
FROM employees e INNER JOIN department d
ON e.department_id=d.department_id
INNER JOIN locations
ON d.location_id=l.location_id;
/*查询员工的last_name,department_name,city信息*/

外连接

FROM …… OUTER JOIN …… ON <连接条件> 

合并具有同一列的两个以上的表的行，结果集中除了包含一个表与另一个表匹配的行之外，还查询到了左表 或 右表中匹配的行。

左外连接

FROM ……  LEFT OUTER JOIN …… ON <连接条件> 

两个表再连接过程中除了返回满足连接条件的行意外还返回左表中不满足条件的行，这种连接称为左外连接

SELECT last_name,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id=d.department_id;
/*查询所有的员工的last_name,department_name信息*/

右外连接

FROM …… RIGHT OUTER JOIN …… ON <连接条件> 

两个表再连接过程中除了返回满足连接条件的行意外还返回右表中不满足条件的行，这种连接称为右外连接

SELECT last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id=d.department_id;
/*查询所有的员工的last_name,department_name信息*/

满外连接

MySQL不支持FULL OUTER JOIN这种写法

但可以用UNION ALL平替，

SELECT employee_id,department_name
FROM employees e LEFT JOIN department d
ON e.department_id=d.department_id;
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN department d
ON e.department_id=d.department_id
WHERE d.department_id IS NULL;

/*查询所有的员工的last_name,department_name信息*/
SELECT last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id=d.department_id;
/*此方法MySQL不适用*/

UNION关键字

UNION 返回两个查询的结果集的并集，对于重复的部分去重

UNION ALL返回两个查询的结果集的并集，对于重复的部分不去重

如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去重的数据，则尽量使用UNION ALL语句，以提高查询的效率

7种JOIN操作

• 一般来说JOIN共有7种操作，但MySQL并不适用outer但可以综合其它语句加上UNION ALL达成一样的效果

A∪B-B

/*左外连接*/
SELECT <select_list>
FROM TableA a
LEFT JOIN TableB b
ON a.key=b.key;

A∪B-A

/*右外连接*/
SELECT <select_list>
FROM TableA a
RIGHT JOIN TableB b
ON a.key=b.key;

A-A∩B

SELECT <select_list>
FROM TableA a
LEFT JOIN TableB b
ON a.key=b.key
WHERE b.key IS NULL;

B-A∩B

SELECT <select_list>
FROM TableA a
RIGHT JOIN TableB b
ON a.key=b.key
WHERE b.key IS NULL;

A∪B

/*满外连接*/
SELECT <select_list>
FROM TableA a
LEFT JOIN TableB b
ON a.key=b.key;
UNION ALL/*UNION ALL比UNION更有效率*/
SELECT <select_list>
FROM TableA a
RIGHT JOIN TableB b
ON a.key=b.key
WHERE b.key IS NULL;

或者

SELECT <select_list>
FROM TableA a
RIGHT JOIN TableB b
ON a.key=b.key;
UNION ALL
SELECT <select_list>
FROM TableA a
LEFT JOIN TableB b
ON a.key=b.key
WHERE b.key IS NULL;

A∪B-A∩B

SELECT <select_list>
FROM TableA a
LEFT JOIN TableB b
ON a.key=b.key
WHERE b.key IS NULL;
UNION ALL
SELECT <select_list>
FROM TableA a
RIGHT JOIN TableB b
ON a.key=b.key
WHERE b.key IS NULL;

A∩B

SELECT <select_list>
FROM TableA a
INNER JOIN TableB b
ON a.key=b.key;

自然连接

NATURAL JOIN

查询两张连接表种所有相同的字段，然后进行等值连接

但不够灵活，不建议使用

SELECT emloyee_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;

/*自然连接为*/
SELECT employees_id,last_name,department_name
FROM employees NATURAL JOIN departments d;

USING连接

• 指定数据表里的同名字段进行等值连接
• 但只能配合JOIN一起使用，且要求两个关键字段在关联表种名称一致，而且只能表示关联字段值相等
• 不灵活，不建议使用

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING(department_id);
/*等于WHERE e.department_id=d.department_id*/

子查询

在SELECT中，除了GROUP BY和LIMIT之外，其他位置都可声明子查询

• 子查询在主查询之前一次执行完成
• 子查询的结果被主查询使用
• 注意
  • 子查询要包含在括号内
  • 将子查询放在比较条件的右侧【为了可读性】
  • 单行操作符对应单行子查询，多行操作符对应多行子查询

/*查询工资大于Abel的人*/
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e2.`salary`>e1.`salary`
	AND e1.last_name='Abel';
	/*自连接*/
	
SELECT last_name,salary
FROM emloyees
WHERE salary >(
    SELECT salary
    FROM employees
    WHERE last_name='Abel'
	)
/*子查询*/

子查询的分类

• 返回结果的条目数
  • 单行子查询 vs 多行子查询
• 子查询是否被执行多次
  • 相关子查询 vs 不相关子查询
    • 相关子查询的需求：查询工资大于本部门平均工资的员工信息
    • 不相关子查询的需求：查询工资大于本公司平均工资的员工信息

单行子查询

查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id,manager_id,department_id

SELECT  employee_id,manager_id,department_id
FROM employees
WHERE manager_id =(
    SELECT manager_id
    FROM employees
    WHERE employee_id =141
)
AND department_id = (
    SELECT department_id
    FROM employees
    WHERE employee_id=141
)
AND employee_id <> 141;

/*也可简化为*/
SELECT  employee_id,manager_id,department_id
FROM employees
WHERE (manager_id,department_id)=(
	SELECT manager_id,department_id
    FROM employees
    WHERE employee_id = 141
)
AND employee_id <> 141;

HAVING

查询最低工资大于50号部门最低工资的部门id和其最低工资

SELECT department_id,MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
HAVING MIN(salary)>(
	SELECT MIN(salary)
    FROM employees
    WHERE department_id =50
);

CASE

显式员工的employee_id,last_name和location.

若员工department_id与location位1800的department_id相同，

则location为’canada’，其余则为’USA’

SELECT employee_id,last_name,
	(CASE department_id
	 WHEN
	 	(SELECT department_id FROM departmetns
	 	WHERE lacation_id=1800
	 	)
     WHEN 'Canada' ELSE 'USA' END
	)location
FROM employees;

空值

子查询不返回任何行

SELECT last_name,job_id
FROM employees
WHERE job_id=
	(SELECT job_id
     FROM employees
     WHERE last_name='Haas'
    )

多行子查询

• 也称为集合比较子查询
• 内查询返回多行
• 使用多行比较操作符

符号

操作符	含义
IN	列表中的任意一个
ANY	需要和单行比较符一起使用，和子查询返回的某一个值比较
ALL	需要和单行比较符一起使用，和子查询返回的所有值进行比较
SOME	实际上是ANY的别名，作用相同，一般使用ANY

SELECT employee_id,last_name
FROM employees
WHERE salary IN (
    SELECT MIN(salary)
    FROM employees
    GROUP BY department_id
);

job_id中比job_id为’IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT PROG'
AND salary < ANY (
    SELECT salary
    FROM employees
    WHERE job_id = 'IT PROG'
);

MySQL中聚合函数不能嵌套，

查询平均工资最低的部门id

SELECT department_id
FROM employees
GROUP BY department_id
HAVING  AVG(salary) <= ALL(
    SELECT AVG(salary) avg_sal
    FROM employees
    GROUP BY department_id	
);

空值

空值也会参与运算

SELECT last_name
FROM employees
WHERE employee_id NOT IN (
    SELECT manager_id
    FROM employees
    WHERE mannager_id IS NOT NULL/*不加这一行就没有结果*/
);

相关子查询

查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为关键子查询

• GET
  • 从主查询中获取候选列
• EXECUTE
  • 子查询使用主查寻
• USE
  • 如果满足子查询的条件则返回该行

SELECT column1,column2,...
FROM table1 outer/*外部的表*/
WHERE column1 operator(
    SELECT colum1,colum2
    FROM table2
    WHERE expr1 =
    			outer.expr2
);

实例

查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id

SELECT last_name,salary,department_id
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE department_id=e1.`department_id`
);/*相关子查询的方式*/

SELECT e.last_name,e.salary,e.department_id
FROM employees e,(
    SELECT department_id,AVG(salary) avg_sal
    FROM employees
    GROUP BY department_id
) t_dept_avg_sal
WHERE e.departmetn_id=t_dept_avg_sal.department_id
AND e.salary > t_dept_avg_sal.avg_sal;
/*在FROM中声明子查询*/

在ORDER BY中使用子查询

查询员工的id，salary，按照department_name排序

SELECT employee_id,salary
FROM employees e
ORDER BY(
    SELECT department_name
    FROM departments d
    WHERE e.`department_id`=d.`department_id`
)ASC;

EXISTS与NOT EXISTS关键字

• 关联子查询通常也会和EXIST操作符一起来使用，用来检查在子查询中是否存在满足条件的行
• 如果在子查询中不存在满足条件的行:
  • 返回FALSE
  • 继续在子查询中查找
• 如果在子查询中存在满足条件的行:
  • 不在子查询中继续查找
  • 条件返回TURE
• NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE,否则返回FALSE

/*查询公司管理者的employee_id,last_name,job_id,department信息*/
SELECT enployee_id,last_name,job_id,department_id
FROM employees e1
WHERE EXISTS(
    SELECT *
    FROM employees e2
    WHERE e1.`employee_id`=e2.`manager_id`
);/*EXISTS*/

/*查询department表中，不存在与employees表中的部门的department——id和department_name*/
SELECT department_id,department_name
FROM departments d
WHERE NOT EXISTS(
    SELECT *
    FROM employees e
    WHERE d.`department_id`=e.`department_id`
);