0. 子句顺序

SELECT
FROM
WHERE/JOIN ON
GROUP BY
HAVING
ORDER BY
LIMIT

1.查询数据

这里插一嘴，后面的内容我们需要用到示例数据，地址如下,下好的文件加压，之后在test_db-master目录下执行命令。

mysql -u root -p < employees.sql

如下图：

mysql中怎样查询第几行数据 mysql查询第二名_结果集

1.1 查询全部

命令：
SELECT * FROM 表名;，用于查询表的全部数据。
例如：

SELECT * 
FROM departments;

可以使用SELECT DISTINCT 列名 FROM 表名;来排除结果集中重复的值。

1.2 选择列

命令：
SELECT 列名1,列名2,... FORM 表名;，用于选择你需要的列。
例如：

SELECT emp_no,dept_no 
FROM dept_manager;

可以查询的列指定别名，SELECT 列名 AS 别名,例如：SELECT COUNT(*) AS count。
表名也可以起别名。如：

SELECT e.first_name, e.last_name 
FROM employees AS e 
WHERE e.emp_no = 10001;

2 条件过滤

命令：
SELECT 列名 FROM 表名 WHERE 查询条件;，WHERE子句用来指定查询的条件。
例如：

SELECT emp_no 
FROM employees 
WHERE first_name='Georgi' AND last_name='Facello';

WHERE子句中可以是用的关键字：使用AND表示且关系，OR表示或关系。
AND的优先级要高于OR，可以使用()把想要进行的运算包含起来。
可以使用的操作符：
= 等于
<>或!= 不等于
> 大于
< 小于
= 大约等于
<= 小于等于

2.1 IN

检查一个值，是否在一组值中。
命令：
列名 IN (值1,值2,...)
例如：

SELECT COUNT(*) 
FORM employees 
WHERE last_name IN ('Christ','Lamba','Baba');

IN 操作符一般比 OR 操作符清单执行更快(#TODO 给出理由)。
IN可以包含子查询。

2.2 BETWEEN...AND

检查一个值是否在一个范围内，结果是包含开始值和结束值的。
命令：
列名 BETWEEN 值1 AND 值2。
例如：

SELECT COUNT(*) 
FROM employees
WHERE hire_date BETWEEN '1986-12-01' AND '1986-12-31';

2.3 NOT

排除限定范围，放在其他操作符之前。
例如:

NOT BETWEEN 值1 AND 值2
NOT IN (值1,值2,...)

MySQL中，支持对EXISTS、IN、BETWEEN的子句取反（使用NOT）。

2.4 LIKE

简单模式匹配

符号

描述

_

匹配一个字符

%

匹配任意个字符（不匹配NULL）

例如：

#以er结尾
LIKE '%er'
#以任意两个字符开头后接ka，再以任意字符结尾
LIKE '__ka%'

不要过度使用通配符。（性能问题）
将通配符放在开头时，查询最慢。

2.5 正则表达式

命令：
REGEXP 'pattern'。
例如：

# 返回名字中包含ab的行
SELECT first_name 
FROM employees 
WHERE first_name REGEXP 'ab';

符号

描述

^

开头

$

结尾

|

或（1000|2000）

[charlist]

charlist中的一个

[^charlist]

除了charlist中的任意一个

charlist可以是范围型的，比如0-9，a-z。

转义字符，对于一些特定字符，如.、-这些需要使用转义字符。

元字符

说明

\\f

换页

\\n

换行

\\r

回车

\\t

制表符

\\v

纵向制表符

\\符号

匹配符号，比如.、-、|、\等

数量，只作用在其前一个字符上。

元字符

说明

*

匹配0或多个

+

匹配1或多个

?

匹配0或1个

指定匹配n个

不少于n个

匹配n到m个（m不超过255）

2.6 IS

用来检测NULL值。
例如：

SELECT * 
FROM employees 
WHERE hire_date IS NULL;

NULL只无值，与0、空字符串和空格不同。

3. 限制结果的数量

命令：
LIMIT 数量
例如：

# 选择结果集的前十行
SELECT first_name, last_name 
FROM employees
WHERE hire_date < '1986-01-01' LIMIT 10;

LIMIT 3,4 从第4行开始，返回4行，即第4到第7行。
还有另外一种写法，即LIMIT 4 OFFSET 3。
结果集的行数从0开始计数。

4. 对结果排序

命令：
ORDER BY 列名(列序号) 规则, 列名2 规则,...
例如：

SELECT emp_no, salary FROM salaries ORDER BY salary DESC;
SELECT emp_no, salary FROM salaries ORDER BY 2 DESC;#与上面的等价

其中DESC表示降序，最大值在上。
ASC表示升序，最小值在上。
如果不指定，默认为升序ASC。
可以对非选择列进行排序。

5. 对结果分组（聚合函数）

将结果分组，便于使用聚合函数来处理结果集。
命令：
GROUP BY 列名1,列名2,...
例如：

SELECT gender, COUNT(*) FROM employees GROUP BY gender;

除了聚合函数外，SELECT语句中的每个列，都必须在GROUP BY中列出。

5.1 HAVING

HAVING不是聚合函数，它是用来过滤GROUP BY结果集的子句。
例如：

SELECT emp_no, AVG(salary) AS avg 
FROM salaries GROUP BY emp_no HAVING avg > 140000 ORDER BY avg DESC;

5.2 COUNT

用于对结果集计数。
例如：
COUNT(*)

使用*作为函数参数时，会把NULL值也统计进来。
如果想排除列中含有NULL值的行，可以使用COUNT(列名)来统计。

5.3 SUM

用于对结果集求和。
例如：

SELECT YEAR(from_date) AS year, SUM(salary) AS sum 
FROM salaries GROUP BY YEAR(from_date) ORDER BY sum DESC;

5.4 AVERAGE

用于求结果集的平均数。
例如：

SELECT emp_no, AVG(salary) AS avg 
FROM salaries GROUP BY emp_no ORDER BY avg DESC LIMIT 10;

5.5 MAX/MIN

用户求最大、小值。
例如：

# 最小值与最大值的用法相同。
SELECT YEAR(from_date) AS year, MAX(salary) AS max 
FROM salaries GROUP BY YEAR(from_date) ORDER BY max DESC;

5.6 使用DISTINCT排除重复列

在MySQL5以后的版本是可以将DISTINCT用于聚合函数的。
在聚合函数的参数中可以指定ALL或DISTINCT两个参数。
默认情况下为ALL。

  • 在COUNT函数中，只有指定列名的情况可以使用DISTINCT排除重复列。
  • 对于MIN、MAX函数来说，虽然技术上可以使用DISTINCT，但是完全没有必要。

6. 函数

函数的移植性通常不强，使用的时候需要多加注意。

6.1 拼接字符串

例如：

SELECT Concat(Trim(first_name), ' ', Trim(last_name)) AS name 
FROM employees ;
  • Concat():会将其中的结果拼接起来，字符串需要用引号标记。
  • Trim():会将结果两侧的空格删除。同时还有删除右侧空格的RTrim()和删除左侧空格的LTrim()。

出了拼接字符串，MySQL还支持+、-、*、\的运算。

6.2 文本处理

函数

说明

Left()

返回串左边的字符

Length()

返回串的长度

Locate()

找出串的一个子串

Lower()

将串转换为小写

LTrim()

删除左侧的空格

Right()

返回串右边的字符

RTrim()

删除右侧的空格

Soundex()

返回串的SOUNDEX值（匹配发音类似的值）

SubString()

返回子串的字符

Upper()

将串转换为大写

6.3 日期和时间处理

使用一下函数，对存储日期的字段进行部分比较。

函数

说明

AddDate()

增加一个日期（天、周等）

AddTime()

增加一个时间（时、分等）

CurDate()

返回当前日期

CurTime()

返回当前时间

Date()

返回日期部分,yyyy-MM-dd

DateDiff()

计算两个日期之差

Date_Add()

高度灵活的日期运算函数

Date_Format()

返回一个格式化的日期或时间串

Day()

返回一个日期的天数部分

DayOfWeek()

返回周几

Hour()

返回小时部分

Minute()

返回分钟部分

Month()

返回月份部分

Now()

返回当前的日期和时间

Second()

返回秒的部分

Time()

返回一个时间部分,hh:mm:ss

Year()

返回年份部分

6.4 数值处理函数

数值处理函数在主要DBMS的函数中，是最一致的函数。

函数

说明

Abs()

返回绝对值

Cos()

返回余弦

Exp()

返回指数

Mod()

返回余数

Pi()

圆周率

Rand()

返回随机数

Sin()

返回正弦

Sqrt()

返回平方根

Tan()

返回正切

7. 联表查询

从多个表中查询数据的方法。
关联有几种类型:

  1. INNER JOIN (JOIN).
  2. LEFT JOIN (LEFT OUTER JOIN).
  3. RIGHT JOIN (RIGHT OUTER JOIN).
  4. FULL JOIN.

这里的左表为先指定的表，即FROM指定的表。右表为后指定的表，即JOIN指定的表。

7.1 JOIN

内连接，返回匹配的到的行。
比如，我们查询一个员工编号为110022的人的员工编号、姓名和部门名称。

employees员工表存放员工信息。
departments部门表存放部门信息。
dept_manager部门员工关联表存放部门与员工的对应关系。

SELECT emp.emp_no,emp.first_name,emp.last_name,dept.dept_name
FROM employees AS emp 
JOIN dept_manager AS dept_mgr 
    ON emp.emp_no = dept_mgr.emp_no AND emp.emp_no = 110022 
JOIN departments AS dept 
    ON dept_mgr.dept_no = dept.dept_no;

8.子查询

MySQL支持将一个查询的返回结果作为其他查询语句的一部分来使用。
MySQL总是从内向外的执行查询。即先执行子查询，再执行外层的查询。

8.1 作为In的查询条件

例如：想要查询工资最大的员工信息。

# 在salaries表中查询工资最高。
SELECT MAX(salary) 
FROM salaries;
# 在salaries表中，查询工资等于最大工资的员工编号。
SELECT emp_no 
FROM salaries 
WHERE salary = (SELECT MAX(salary)
                FROM salaries);
# 在employees表中个，查找员工编号等于该编号的姓名。
SELECT first_name, last_name 
FROM employees 
WHERE emp_no = (SELECT emp_no 
                FROM salaries 
                WHERE salary = (SELECT MAX(salary)
                                FROM salaries));

MySQL8中，不在子查询中支持LIMIT语句。

8.2 作为字段使用子查询

例如：查询前十名薪资的员工信息和薪资总额。

SELECT emp_no, first_name, last_name, (SELECT SUM(salary) 
                               FROM salaries 
                               WHERE salaries.emp_no = employees.emp_no) AS salaries 
FROM employees 
ORDER BY salaries DESC
LIMIT 10;

注意如果两个表中个列名相同时，我们希望匹配到两个表中，emp_no相同的情况。所以在子查询中使用相关子查询来约束emp_no，否则sum会对salaries全表的工资进行加和。
子查询的性能通常不是太好，尤其嵌套多层子查询时。

9. 组合查询 UNION

将多个SELECT语句组合成一个结果集。

9.1 UNION

例如：查找工资平均年薪高于140000和低于39500的人的编号和平均年薪。

SELECT emp_no, AVG(salary) AS avg
FROM salaries  
GROUP BY emp_no
HAVING AVG(salary) >=140000 
UNION
SELECT emp_no, AVG(salary) AS avg
FROM salaries 
GROUP BY emp_no 
HAVING AVG(salary) <= 39500;

同一个表的不同条件可以用OR来实现，但是从不同表查询相同列的情况使用UNION更好。

使用UNION的规则：

  • UNION必须由两条或两条以上的SELECT语句组成，语句之间用UNION分隔。
  • UNION中的每个查询必须包含相同的列、表达式或聚合函数。
  • 列数据类型必须兼容，不必完全相同，但是必须是可以隐性转换的。

9.2 UNION ALL

UNION是不包含重复列的，如果想要包含重复列，需要是用UNION ALL。