#格式：SELECT 字段1,字段2... FROM 表名;
#SELECT 简单使用

SELECT 1 + 1 ; #这种情况隐式使用了伪表

SELECT 1 + 1 FROM DUAL #DUAL：伪表  显示使用伪表

SELECT sname,sex 
FROM stu; #查询stu表的sname,sex字段

别名，去重和空值参与运算

给字段设置别名

设置别名：就是查询出来的字段名显示为我们设置的别名

#给字段设置别名的三种方式
#方式一：直接加
SELECT sname name FROM stu  #name就是设置的别名
#使用AS关键字
SELECT sname AS name FROM stu
#使用双引号
SELECT sname "name 1" FROM stu

去重操作

使用关键字：DISTINCT

#去重 DISTINCT
SELECT DISTINCT * 
FROM employees;

空值参与运算

所有运算符或列值遇到null值，结果都为空值。

SELECT 1 + null FROM DUAL; //null

着重号的使用和查询常数

当表名与关键字重名时，可以使用一对` ` 着重号引起来

SELECT * FROM `order`;

查询常数

添加常数可以在每一条记录都可以显示这个常数。

SELECT '常数',name FROM t_test;

显示表结构DESC和过滤数据WHERE

显示表结构

使用关键字：DESCRIBE或它的简写DESC

DESC t_test;
DESCRIBE t_test;

过滤数据：WHERE

语法说明：

过滤条件：此条件将不满足的记录排除掉。

SELECT 字段1,字段2
FROM 表名
WHERE 过滤条件

SELECT *
FROM t_test
WHERE id < 5;

运算符

算数运算符

算数运算符主要用于数学运算，其可以连接运算符前后的两个数值或表达式，对数值或表达式进行以下运算：

在sql中，+号只有相加的意思，由此：1 + ‘1’ =2 （隐式转换），并不存在连接的意思。

比较运算符

比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较结果为真返回1，反之返回0，其他情况返回null。

等号运算符

等号运算符（=）判断等号两边的值、字符串或表达式是否相等，如果相等则返回1，不相等则返回0。
在使用等号运算符时，遵循如下规则：
- 如果等号两边的值，字符串或表达式都为字符串，则MySQL会按照字符串进行比较，其比较的是每个字符串中字符的ANSI编码是否相等。
- 如果等号两边的值都是函数，则MySQL会按照整数来比较两个值的大小。
- 如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较。
- 如果等号两边值，字符串或表达式中有一个为null，则比较结果为null

安全等于

安全等于运算符（<=>）与等于运算符（=）的作用是相识的，唯一的区别是：<=>可以对NULL进行判断。在两个操作数均为NULL时，其返回值为1，而不为NULL；当一个操作数为NULL时，其返回值为0，不为NULL。

其他运算符

IS NULL / IS NOT NULL / ISNULL( )

一组关于是否为空的过滤条件

#IS NULL 过滤为空
SELECT *
FROM t_test
WHERE salary IS NULL; #返回一条记录

#IS NOT NULL 过滤不为空
SELECT * 
FROM t_test
WHERE salary IS NOT NULL;#返回除了上面那条记录

#ISNULL(expr)过滤为空
SELECT *
FROM t_test
WHERE ISNULL(salary);#返回一条记录

LEAST / GREATEST

-- LEAST / GREATEST
SELECT LEAST('a','b','c');#找出最小 a
SELECT GREATEST('a','b','c');#找出最大 c

BETWEEN AND

SELECT `name`,salary
FROM emp
WHERE salary BETWEEN 6000 AND 8000;
#过滤6000<=salary<=8000
#需要注意的是:写法范围：左边小，右边大，不然报错。
例如错误写法：BETWEEN 8000 AND 6000

IN /NOT IN

SELECT id,`name`,salary
FROM emp
WHERE id IN (100,101,102);
#确定id的选项为100 101 102

LIKE 模糊查询

#需求：名称包含字符h的员工id
SELECT id,`name`
FROM emp
WHERE `name` LIKE '%h%';# %符号表示任意一个字符，或多个字符

#需求：查询名称以a开头的员工id
SELECT id,`name`
FROM emp
WHERE `name` LIKE 'a%';

#需求：查询名称以a结尾的员工id
SELECT id,`name`
FROM emp
WHERE `name` LIKE '%a';

#需求：查询名称中包含字符a,并且包含h的员工id
SELECT id,`name`
FROM emp
WHERE `name` LIKE '%a%' AND `name` LIKE '%h%';

#需求：查询名称第二个字符为a的员工id
SELECT id,`name`
FROM emp
WHERE `name` LIKE '_a%';# _符号表示任意一个字符

#需求：查询名称以_开头的员工id
SELECT id,`name`
FROM emp
WHERE `name` LIKE '_\_a%'; # \转义字符

关键字ESCAPE：自定义转义符

#需求：查询名称以_开头的员工id
SELECT id,`name`
FROM emp
WHERE `name` LIKE '_$_a%' ESCAPE '$'; # 自定义转义字符

逻辑运算符

逻辑运算符主要用来判断表达式的真假，在MySQL中，逻辑运算符的返回结果为1，0或者NULL。MySQL支持如下逻辑运算符：

排序和分页

排序

排序规则

使用ORDER BY子句排序

ASC（ascend）升序

DESC（descend）降序

ORDER BY子句在SELECT语句的结尾

关于别名的说明

在设置别名时，order by中可以设置，但是在where中不能设置。

一级排序和二级排序

-- 一级排序
#需求：按照工资升序排序
SELECT salary
FROM emp
ORDER BY salary ASC

#需求：按照工资降序排序
SELECT salary
FROM emp
ORDER BY salary DESC

-- 二级排序
#需求：按工资降序再按照id升序
SELECT id,salary
FROM emp
ORDER BY salary DESC,id ASC

分页 LIMIT

将查询出来的结果分页呈现。

#显示前5条数据
SELECT * 
FROM emp
LIMIT 0,5;#0为偏移量 5为查询记录条数

#显示第6-10条数据
SELECT * 
FROM emp
LIMIT 5,5;

MySQL8新特性 LIMIT OFFSET

#需求 查询第20-21条记录
SELECT * 
FROM emp
LIMIT 2 OFFSET 19; #2为条数，19为偏移量

排序求最值

#需求：查询salary最高的记录
SELECT id,salary
FROM employees
ORDER BY salary DESC
LIMIT 0,1
#或者
-- LIMIT 1 OFFSET 0;

多表查询

多表查询，也成为关联查询，指两个或多个表一起完成查询操作。

前提条件：这些一起查询得表之间是有关系得（一对一，一对多）。

SELECT t_emp.id,t_dep.dep_name
FROM t_emp,t_dep   #给两个表设置别名
#建立两个表之间的连接:两个表都有dep_id字段
WHERE t_emp.dep_id = t_dep.dep_id


#写法优化：设置别名
#需求：查询t_emp表中的id，和t_dep表中的dep_name
SELECT e.id,d.dep_name
FROM t_emp e,t_dep d   #给两个表设置别名
#建立两个表之间的连接:两个表都有dep_id字段
WHERE e.dep_id = d.dep_id

#需要注意的是：设置了别名后，必须使用别名，不能使用表明

多表查询的分类

等值连接和非等值连接

两者最大区别：关系是否为等量关系，例如上述案例中关系即为等量的。

案例：

#等值连接
SELECT e.id,d.dep_name
FROM t_emp e,t_dep d   
#连接条件
WHERE e.dep_id = d.dep_id


#非等值连接
SELECT e.`name`,g.grade
FROM t_emp e , t_salgrade g
#连接条件
WHERE e.salary BETWEEN g.minsalary AND g.maxsalary

自连接和非自连接

两者区别

自连接：一张表自己连接自己，也就是一张表复制一份出来，在进行连接。

非自连接：连接非自身就是非自连接。

#自连接 自我引用
#利用employees表查询employee_id 对应的manager_id
#再利用manager_id 查询出对应的manager名称
SELECT e.last_name,e.employee_id,m.employee_id,m.last_name
FROM employees e,employees m;
WHERE e.`manager_id` = m.`employee_id`;

内连接和外连接

内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行。以上案例均为内连接。
外连接：两个表在连接过程中除了返回满足连接条件以外还返回左（或右）表中不满足条件的行，没有匹配行时，结果为空（NULL）。
如果是左外连接，则连接条件中左边的表称为主表，右边的表称为从表。
如果是右外连接，则连接条件中右边的表称为主表，左边的表称为从表。

外连接的分类

左外连接，右外连接，满外连接。

左外连接：两个表在连接过程中除了返回满足连接条件的行以外，还返回左表中不满足条件的行。

右外连接：两个表在连接过程中除了返回满足连接条件的行以外，还返回右表中不满足条件的行。

满外连接：

SQL99语法

实现多表查询内连接 JOIN ON

SELECT e.id ,d.dep_name
FROM t_emp e JOIN t_dep d
#on后跟连接条件
ON e.dep_id = d.dep_id

#语法结构：
# 表1 JOIN 表2
  ON 表1 表2 连接条件
  JOIN 表3
  ON 表3 和表1或表2的连接条件
  JOIN....
  ON...

左外连接LEFT-OUTE 和右外连接RIGHT-OUTER

#左外连接
SELECT e.id ,d.dep_name
FROM t_emp e LEFT OUTER JOIN t_dep d
ON e.dep_id = d.dep_id

#右外连接
SELECT e.id ,d.dep_name
FROM t_emp e RIGHT OUTER JOIN t_dep d
ON e.dep_id = d.dep_id

（OUTER 和INNER 可以省略）

满外连接FULL-OUTER

#满外连接
SELECT e.id ,d.dep_name
FROM t_emp e FULL OUTER JOIN t_dep d
ON e.dep_id = d.dep_id

7种SQL的JOINS连接方式

如上图，我们只能得到左外，右外和满外连接，但是其他情况我们无法直接得到，但是有时候又有需求，这时候我们就需要使用关键字：UNION。

UNION和UNION ALL 关键字使用

合并查询结果

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

语法：

SELECT colimn… FROM table1

UNION [ 可选 ]

SELECT column… FROM table2

…

UNION和UNION ALL区别

UNION：返回两个查询结果的并集，去除重复记录。

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

使用须知：执行UNION ALL时所需要的资源比UNION少，因为UNION ALL不需要执行去重操作，如果已知合并后结果无重复数据或不需要去重的，选择UNION ALL更为合适。节约资源，提高效率。

SQL函数

单行函数/基本函数

操作数据对象
接受参数返回一个结果
只对一行进行交换
每行返回一个结果
可以嵌套
参数可以是一列或一个值

SQL中的常用单行函数

进制间转换相关的函数

操作字符串相关的函数

需要注意的是：SQL的索引是从1开始的，而并非0开始。

时间和日期相关的函数

获取日期时间

日期和时间戳的转换

获取月份，星期，星期数，天数相关的函数

时间和秒钟的函数

计算日期和时间的函数

信息函数

聚合函数

聚合函数用于一组数据，并对一组数据返回一个值。例如求最大值。

常见的聚合函数

AVG()
SUM()
MAX()
MIN()
COUNT()

COUNT()函数

#聚合函数COUNT
SELECT COUNT(id),COUNT(salary),COUNT(salary*2),COUNT(1),COUNT(*)
FROM emp;

** 一般查询表中存在记录数量：使用count(*)

**count()函数在计数时，自动过滤空值，null不计算在内。所以使用某字段计算表中存在的记录数量是不准确的。

COUNT(*)和COUNT(列名)

用count（*）和count（列名）谁更好？
对于MyISAM引擎的表是没有区别的，这种引擎内部有一计数器在维护者行数。
Innodb引擎的表用count（*）直接读取行数，复杂度是O(n)因为innodb真的要去数一遍
但是好于具体的count（列名）。
能不能使用count（列名）替换count（*）？
不要使用count（列名）来代替count（*）是SQL92定义的标准统计行数的语法，跟数据库无关，跟NULL和非NULL无关。
说明：count（*）会统计值为NULL的行，而count（列名）不会统计此列为NULL的行。

AVG（）函数

特殊情况：当使用AVG（）函数计算平均值的时候，存在有null的情况：

#commission字段中存在多行为null
#错误写法：
SELECT AVG(commission_pct)
FROM emp_all; #输出 0.222857

#正确写法1：
SELECT SUM(commission_pct)/COUNT(IFNULL(commission_pct,0))
FROM emp_all #输出 0.072897

#正确写法2:
SELECT AVG(IFNULL(commission_pct,0))
FROM emp_all; #输出 0.072897

#IFNULL：条件函数 IFNULL(x,y)当x不为NULL时，输出x，否则输出y

GROUP BY使用

该函数用于分组，可以使用GROUP BY子句将表中数据分为若干组。

#需求：求各部门的平均成绩（将部门作为条件分组）
SELECT AVG(salary)
FROM emp_all
GROUP BY department_id #12条记录

GROUP BY中使用WITH ROLLUP

使用WITH ROLLUP关键字之后，在所有查询出的分组记录之后添加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。

#需求：求各部门的平均成绩（将部门作为条件分组）
SELECT AVG(salary)
FROM emp_all
GROUP BY department_id WITH ROLLUP #13条记录

使用WITH ROLLUP时，不能同时使用ORDER BY，两者互斥。

HAVING的使用（用来过滤数据）

要求1：如果过滤条件存在聚合函数，则必须使用HAVING

要求2：HAVING要在GROUP BY 后面

开发中使用HAVING的前提是SQL中使用了GROUP BY

#需求：查询部门id为10，20，30的部门中最高工资比10000高的部门id
SELECT department_id,MAX(salary)
FROM emp_all
WHERE department_id IN (10,20,30)
GROUP BY department_id
HAVING MAX(salary) > 10000;

WHERE和HAVING对比

区别1：WHERE可以直接使用表中的字段作为筛选条件，但是不能使用分组中的计算函数作为筛选条件；HAVING必须要与GROUP BY配合使用，可以把分组计算的函数和分组字段作为筛选条件。例如上述例子：HAVING MAX（salary）> 10000 。

区别2：如果需要通过连接从关联表中获取数据，WHERE是先筛选后连接，而HAVING是先连接后筛选。则在相关查询中，WHERE更高效。

SQL执行过程

关键字声明顺序

SELECT – – FROM – – WHERE – – GROUP BY – – HAVING – – ORDER BY – – LIMIT…

SQL语句执行顺序

FROM – – WHERE – – GROUP BY – – HAVING – – SELECT – – DISTINCT – – ORDER BY – -LIMIT

SQL子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询。

SQL中子查询的使用大大增强了SELECT查询能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算出一个结果，然后与这个数据结果进行比较。

使用要点

1.子查询的结果被主查询使用。

2.子查询要放到括号内。

3.将查询结果放到比较条件的右侧。

案例：

#1.查询和员工Zlotkey相同部门的员工姓名和工资
#方式1：子查询
SELECT last_name,salary,department_id
FROM employees
WHERE department_id =(SELECT department_id
											FROM employees
											WHERE last_name = 'Zlotkey'
										 );

子查询的分类

分类方式1：

将查询的结果返回一条还是多条记录作为区分，分为：单行子查询，多行子查询。

分类方式2：

按照子查询是否被执行多次，将子查询分为相关子查询和不相关子查询。

单行子查询

单行操作符

**在内查询出现空值的情况下，不会报错。

多行子查询

多行比较操作符

利用子查询解决聚合函数不能嵌套的问题

#需求：查询平均工资最低的部门id

#错误写法：聚合函数不可嵌套
SELECT MIN(AVG(salary))

#正确写法：将查询的结果作为一张表
SELECT MIN(avg_salary)
FROM (
				SELECT AVG(salary) as avg_salary 
				FROM emp_all
				GROUP BY department_id	
			)t_empall_avgsalary  #为表设置名字

多行子查询例子

#需求：查询平均工资最低的部门信息
#方式1：单行子查询
SELECT d.department_id,d.department_name
FROM departments d JOIN employees e
ON d.department_id = e.department_id
GROUP BY d.department_id
HAVING AVG(salary) = (SELECT MIN(avg_salary)
											FROM(
													SELECT AVG(salary) avg_salary
													FROM employees
													GROUP BY department_id	
													)t_avg_salary);	

#方式2：多行子查询
SELECT d.department_id,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id
GROUP BY department_id
HAVING AVG(salary) <= ALL (
							SELECT AVG(salary) #查询出来的结果不为单个
							FROM employees
							GROUP BY department_id
							);

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/154581.html