数据查询

  • 基本查询
  • 条件查询
  • 连接查询
  • 内连接查询（INNER JOIN）
  • 外连接查询（OUTER JOIN）
  • 复合连接查询
  • 高级应用
  • Limit
  • Union
  • GROPE BY
  • ORDER BY
  • DISTINCT
  • 函数使用
  • 聚合函数
  • 字符函数
  • 数学函数
  • 时间和日期
  • 其他函数
  • MySQL基础系列


数据查询也称为数据检索，是从数据库中获取所需数据的操作和过程。用户根据实际应用的需求，可以使用不同的查询方式，以获得不同的数据。数据査询是数据库管理系统(DBMS )的重要功能之一，是数据库操作中最常用，最重要的操作。

基本查询

SELECT语句的基本语法为：
SELECT select_expr [, select_expr...] 〃要査询的列（字段）或表达式
FROM table_references                 〃指定査询的数据来源，即数据表
WHERE where_condition                 〃査询时需要满足的条件
GROUP BY col_name                     〃对结果进行分组
HAVING where_condition                〃对分组内的数据进行筛选
ORDER BY col_name                     〃査询结果进行排序
LIMIT row_count                       〃限定输出的査询结果

* 表示所有字段
如果查询多个字段，字段间用逗号分隔
SELECT * FROM teacher;
SELECT
    Teacher_id,
    Teacher_name
FROM teacher;

指定列的别名 AS

SELECT
    Teacher_id AS 教师编号,
    Teacher_name AS 教师姓名
FROM teacher;

对查询结果的列值进行替换
将原表中的性别以0、1分别修改表示为男女。

SELECT
    Employee_id AS 员工编号,
    Employee_name AS 员工姓名,
    (CASE WHEN Sex='0' THEN '女' ELSE '男' END) AS 性别
FROM employee;
SELECT
    Student_id AS 学生编号,
    Student_name AS 学生姓名,
    (CASE WHEN Score>=60 THEN '及格' ELSE '不及格' END) AS 成绩情况
FROM employee;

条件查询

WHERE条件判断符
条件查询用于过滤数据，即查询结果中只包含满足条件的记录。在WHERE子句中多用关系运算符和逻辑运算符构造查询条件。

关系运算符

运算符

说明

示例

=

等于

ID=1

>

大于

Score>100

<

小于

Score<60

>=

大于等于

Score>=60

<=

小于等于

Score<=59

<>、!=

不等于

Sex!=男

IS NULL

为空

IS NOT NULL

不为空

BETWEEN AND

ID BETWEEN 1 AND 15

IN

在

ID in (1,3,5,7)

NOT IN

不在

Name NOT IN (‘White’,‘Black’)

LIKE

模式匹配

Name LIKE (‘B%’)

NOT LIKE

模式匹配

Name NOT LIKE (‘B%’)

REGEXP

re正则表达式

逻辑运算符

运算符

说明

AND、&&

与

OR、||

或

NOT、！

非

XOR

异或

查询1997年生的学生信息

SELECT
	Student_id,
	Student_name,
	Sex,
	Score
FROM student
WHERE Birthday>='1997-01-01' and Birthday<='1997-12-31'；
SELECT
	Student_id,
	Student_name,
	Sex,
	Score
FROM student
WHERE Sex='女' and (Political='团员' OR Political='党员')；

IN构造查询条件时，将条件用括号括起来，条件之间用逗号分隔开，只要满足其中的一个条件即为满足条件。格式为：
WHERE fieldname IN(值1, 值2, …)

SELECT
	Student_id,
	Student_name,
	Sex,
	Score
FROM student
WHERE Birthday IN (1997,1998)；

BETWEEN AND用来查询某个范围内的值，该操作符需要两个参数，即范围的开始值和结束值，包含开始与结束值。如果字段值位于指定的范围之内，则这些记录被返回。格式为：
WHERE fieldname BETWEEN 开始值 AND 结束值

SELECT
	Student_id,
	Student_name,
	Sex,
	Score
FROM student
WHERE Score BETWEEN 60 AND 69；

如果只想按字段值的部分内容进行匹配，即执行“模糊查询”，则需要使用关键字LIKE配合通配符“%”和“_”。

WHERE fieldname LIKE ‘匹配符和其他字符组合’
%：匹配任意长度的字符。
：只能匹配任意一个字符，如果要匹配多个字符，则需要使用相同数量的“”。

查询所有姓王的同学姓名

SELECT * FROM student
WHERE Student_name LIKE '王%';
SELECT * FROM student
WHERE Student_name LIKE '王_' OR Student_name LIKE '王__';

查询名字叫娜的同学信息

SELECT * FROM student
WHERE Student_name LIKE '%娜';

查询家在北京的同学信息

SELECT * FROM student WHERE Student_adress LIKE '%北京%';

空值（NULL）不同于0，也不同于空字符串。空值表示数据未知、不确定或将在以后添加数据。
查询时使用IS NULL查询某字段值为空的记录，与之相反的字句为IS NOT NULL。

连接查询

连接是关系数据模型的主要特点，连接查询是关系数据库中最主要的查询，包括内连接、外连接等。
当两个或多个表中存在相同意义的字段时，便可以通过这些字段对不同的表进行连接查询，得到存放在多个表中的记录数据。所谓表中相同意义的字段, 是指在多个表中名字不一定相同, 但取值的含义相同的字段，这是表之间实现连接查询的前提。

如：department.Department_id <==> teacher.Department_id

内连接查询（INNER JOIN）

INNER JOIN使用比较运算符（=）根据每个表共有列的列值匹配两个表中的行，其查询结果仅包含符合查询条件和连接条件的行。即查询结果为两个表的交集。
注意：多表查询时，为避免混淆，在查询字段前应添加表名称作为前缀！

查询教师所在学院

SELECT
	teacher.*
	department.*
FROM teacher INNER JOIN department ON teacher.Departmen_id=department.Departmen_id;

别名的使用。

在FROM 接的表后面一个空格，添上别名
SELECT
	T.*
	D.*
FROM teacher T INNER JOIN department D ON T.Departmen_id=D.Departmen_id;

三个表之间的内连接

student A、choose B、 course C 三个表的内连接查询

SELECT
A.Student_id, A.Student_name, C.Course_name, C.Term, B.Score
FROM student A
INNER JOIN choose B ON A.Student_id = B.Student_id
INNER JOIN course C ON B.Course_id = C.Course_id;

在设计内连接查询时，强调几点:

  1. 表之间一定要有连接字段。
  2. 查询结果的多个列来源于不同的表，需要在列名称前加表名称作为前缀。
  3. 为了简化书写，可以给表指定别名，格式为：
    SELECT … FROM table A #别名A和表名table之间至少加一个空格

外连接查询（OUTER JOIN）

内连接的查询结果仅包含符合连接条件的行。如果需要查询结果不仅包含符合连接条件的行，而且还包括左表、右表或两个连接表中的所有数据行，则应该使用外连接。
MySQL支持的外连接有两种类型：

  1. 左外连接（左连接 LEFT [OUTER] JOIN）
  2. 右外连接（右连接 RIGHT [OUTER] JOIN）

MySQL不能直接支持FULL JOIN，要实现FULL JOIN，应该使用LEFT JOIN UNION RIGHT JOIN 的方式。

左连接的查询结果为左表的所有记录以及右表中连接字段相等的记录。如果左表的某行在右表中没有匹配行，则在相关联的结果行中，右表的所有选择列均为空值(NULL）。

SELECT tableA.select_list, tableB.select_list
FROM tableA
LEFT [OUTER] JOIN tableB ON tableA.Key = tableB.Key

右连接是左连接的反向连接，查询结果为右表中的所有记录以及左表中连接字段相等的记录。如果右表的某行在左表中没有匹配行，左表的选择列将返回空值(NULL）。

SELECT tableA.select_list, tableB.select_list
FROM tableA
RIGHT [OUTER] JOIN tableB ON tableA.Key = tableB.Key

完全连接返回左表和右表中的所有记录，包括连接字段相等的记录和不相等的记录。如果左表的某行在右表中没有匹配行，将返回空值(NULL），反之亦然。

SELECT tableA.select_list, tableB.select_list
FROM tableA LEFT [OUTER] JOIN tableB ON tableA.Key = tableB.Key
UNION
SELECT tableA.select_list, tableB.select_list
FROM tableA RIGHT [OUTER] JOIN tableB ON tableA.Key = tableB.Key

复合连接查询

外连接、内连接与条件查询可以随意组合。

SELECT
	student.Student_id,
	student.Student_name,
	course.Term,
	course.Course_name,
	choose.Score
FROM student 
	INNER JOIN choose ON student.Student_id=choose.Student_id
	INNER JOIN course ON choose.Course_id=course.Course_id
WHERE course.Term='第二学期' AND choose.Score>=90;

高级应用

Limit

SELECT 列名（集） FROM 表名 LIMIT [起始行号], 返回行数

起始行号从0开始编号，起始行号可省略，表示从0开始。
返回行数为-1表示全部返回。

当数据量很大并且起始行号较小时， 使用limit能够避免全表扫描而提高查询效率；当数据量很大量并且起始行号较大时， 仅用limit来限制提取行数会导致效率低下。

Union

将两次查询结果合并输出

SELECT 字段,... FROM 表1 UNION [ALL] SELECT 字段,... FROM 表2

Union仅显示不重复记录， Union All 显示包括重复记录的所有记录。

GROPE BY

分组查询是指按指定的一列或多列对数据进行分组，使 用GROUP BY分组关键字实现，其语法为：

SELECT <select_list> FROM table
GROUP BY col_name | expr HAVING conditions
·	col_name或expr为分组关键字，可以是列名称，也可以是表达式，
	HAVING conditions过滤分组数据！ 用来引导分组条件。
·	使用多列进行分组时，先按第1列分组，在第1列值相同的记录中，再根据第2列的值进行分组……依次类推。 
·	GROUP BY通常和MAX() 、COUNT()等聚合函数一起使用 。

ORDER BY

SELECT语句的查询结果默认按记录插入到数据表中的顺序来显示。如果希望按指定的一列或多列对查询结果进行排序，使用ORDER BY子句。语法为：

SELECT <select_list> FROM table
ORDER BY col_name | expr [ASC | DESC]
·	col_name或expr为排序关键字，可以是列名称或表达式，ASC为升序排列，可省略；DESC为降序排列。 
·	多列排序时，先按第1列的值排序，第1列的值相同时，才会按第2列的值排序；
	如果第1列的所有值都是唯一的，将不再对第2列进行排序。

DISTINCT

DISTINCT关键字用于去除SELECT查询结果中的重复值,多个重复值只保留一个。语法为：

SELECT DISTINCT fieldname FROM table

函数使用

聚合函数

函数

描述

COUNT()

统计行数

SUM()

对数值型字段的值求和

AVG()

对数值型字段的值求平均值

MAX()

统计数值型字段的最大值

MIN()

求最小值

SELECT COUNT(*) AS 学生人数 FROM student;

SELECT
	b.course_name,
	avg(a.score) as 平均分,
	max(a.score) as 最高分,
	min(a.score) as 最低分
FROM 
	choose a,
	course b
WHERE a.course_id=b.course_id AND b.course_name='高等数学';

字符函数

函数

描述

CONCAT(s1,s2,s3…sn)

连接s1…sn为一个字符串

LEFT(str,n)

返回字符串str左边的n个字符

RIGHT(str,n)

返回字符串str右边的n个字符

SUBSTRING(str,x,y)

返回字符串str从位置x到位置y区间的字符

数学函数

函数

描述

RANG()

返回0-1间的随机数

ROUND(x,y)

返回参数x四舍五入保留y位小数的值

TRUNCATE(x,y)

返回参数x保留y位小数的值 ! 不四舍五入

时间和日期

函数

描述

CURDATE()

返回当前日期，只包含年月日

CURTIME()

返回当前时间，只包含时分秒

dateDiff(a,b)

返回两个日期类型数据的时间差，以天计

NOW()

返回当前日期和时间，年月日时分秒都包含

YEAR(date)

返回日期date的年份，即所给的日期是哪一年

其他函数

函数名称

描述

DATABASE()

返回当前数据库名

VERSION()

返回当前数据库版本

USER()

返回当前登录用户名