本文共 7535 字，大约阅读时间需要 25 分钟。

DQL(Data Query Language) 数据查询语言

MySQL的标准查询语句结构

SELECT [ALL|DISTINCT]		{  * | 表名.* | 表名.字段名.... [AS 别名]}	FROM 要查询的表名 [AS 别名]		[LEFT | RIGHT | INNER JOIN 连接表名 [AS 别名]]	WHERE 查询条件;

• SELECT后 FROM前的这一块内容 它是用来筛选查询字段的

简单理解就是你想查看哪些信息 例如：我想查看student表中的学生名、学生年龄、学生性别信息

查看学生表中的所有信息（不推荐写* 推荐写全字段名年列表） 效率低

SELECT * FROM student;

查看学生表中的学生姓名和性别

SELECT stuName,gender FROM student;

• 别名(比较常用) 注意： 此处使用中文只不过是方便你理解而已 后面不会采用写中文别名

AS关键字可以省略 ''也可以省略

SELECT stu.stuName AS '姓名',stu.gradeId + 1 年级 FROM student stu;

• 如果SQL过长可以进行"美化" 目前个人推荐写法：

SELECT	stuName AS '姓名',	gradeId + 1 年级 FROM	student;

• 去重 (默认的)ALL所有的、全部 DISTINCT直接的、明显的 (去重)

SELECT ALL stuName,gradeId FROM student;

DISTINCT 用来在指定的查询字段值范围内 去除重复数据

SELECT DISTINCT stuName,gradeId FROM student;SELECT DISTINCT stuName,gender,gradeId FROM student;

where查询条件

在修改和删除时用，目的是为了防止修改/删除全表。

用于检索数据表中符合条件的记录的 简单理解： 上方的操作是用来筛选列的 where是用来筛选行的

• 在where条件语句中 可以由一个或者多个逻辑表达式组成 结果一般为真或假

   <关系 比较运算符和逻辑运算符>
    SELECT * FROM student;
   

查询年级是大于1的学生信息

SELECT * FROM student WHERE gradeId > 1;

• 复杂条件的处理： 逻辑运算符 与and 或or 非not

查询姓名为 张三 且 性别为女 的学生信息

SELECT * FROM student WHERE stuName = '张三' AND gender = '女';

查询性别是女的 或者 年级为3的

SELECT * FROM student WHERE gender = '女' OR gradeId = 3;

查询性别不是女的

三种方法SELECT * FROM student WHERE NOT gender = '女';SELECT * FROM student WHERE gender <> '女';SELECT * FROM student WHERE gender != '女';

特殊的比较运算符

IS NULL          IS NOT NULL

• 查询地址为空的学生姓名 查询空千万记住不能使用 “ = ”

SELECT stuName FROM student WHERE address IS NULL;SELECT stuName FROM student WHERE address IS NOT NULL;

• BETWEEN … AND 在…之间/范围内 它等价于>= and <=

查询年级在2-3之间的学生姓名

SELECT stuName FROM student WHERE  gradeId >= 2 AND gradeId <= 3;-- 更加简洁SELECT stuName FROM student WHERE  gradeId BETWEEN 2 AND 3;

IN查询 在…内/里面

查询年级为1或年级为3的学生信息

SELECT * FROM student WHERE gradeId = 1 OR gradeId = 3;-- 更加简洁SELECT * FROM student WHERE gradeId IN (1,3);SELECT * FROM student WHERE gradeId NOT IN (1,3);

模糊查询

• LIKE 像…一样 喜欢
• %表示任意单个/多个字符
• _表示任意单个字符

查询姓为张的学生信息

SELECT * FROM student WHERE stuName LIKE '张%';

查询姓张的两个字的学生信息

SELECT * FROM student WHERE stuName LIKE '张_';

查询名称中带有三的学生信息

SELECT * FROM student WHERE stuName LIKE '%三%';

查询三是姓名第二个字符的学生信息

SELECT * FROM student WHERE stuName LIKE '_三%';

连接查询原理及掌握连接查询的使用 (多表连查)

连接查询： 内连接查询、外连接查询、自连接查询

内连接： 显式内连接、隐式内连接

查询学生的姓名和所在年级

SELECT stuName,gradeId FROM student;

采用显式内连接[推荐给多表起别名 区分清楚 防止出现模糊不清错误]

内连接可以理解为交集

SELECT 	stu.stuName,g.gradeNameFROM 	student stuINNER JOIN grade g ON stu.gradeId = g.gradeId; # 关联条件

采用隐式内连接查询 笛卡尔积

SELECT	stu.stuName,g.gradeNameFROM 	student stu,grade gSELECT	stu.stuName,g.gradeNameFROM 	student stu,grade gWHERE	stu.gradeId = g.gradeId;

查询所有的学生姓名、课程名称、考试成绩

SELECT	stu.stuName,sub.subjectName,r.resultFROM	student stu,`subject` sub,result rWHERE 	stu.stuId = r.stuId 	AND 	sub.subjectId = r.subjectId;

外连接： 左外连接、右外连接

查询学生的姓名和所在年级

采用左外连接 以左表为主 LEFT JOIN前面的就是左表

SELECT	stu.stuName,g.gradeNameFROM	student stu LEFT OUTER JOIN grade g	ON stu.gradeId = g.gradeId;

采用右外连接

SELECT	stu.stuName,g.gradeNameFROM	student stu RIGHT JOIN grade g	ON stu.gradeId = g.gradeId;

自连接(内连接)

查询游戏名称和所属分类

SELECT	c1.categoryName,c2.categoryNameFROM 	category c1,category c2WHERE	c1.pid = c2.id

DQL标准语法结构

编写DQL一定要严格按照此语法的顺序来实现！

SELECT [ALL | DISTINCT] ALL表示查询出所有的内容 DISTINCT 去重			{  * | 表名.* | 表名.字段名[ AS 别名][,...]} 指定查询出的字段的		FROM			表名[AS 别名][,表1... AS 别名]		[INNER | [LEFT | RIGHT] [OUTER] JOIN 另一张表名 [AS 别名] ON 关联条件]		[WHERE 条件]		[GROUP BY 分组字段[,...]] 		[HAVING 给分组后的数据进行条件筛选]		[ORDER BY 排序字段[,...]]		[LIMIT [startIndex,]pageSize]

排序查询 ORDER BY

排序： 成绩、游戏排行榜(金额、战力、等级…)、音乐、销量

需求： 查询出学生姓名、课程名称、成绩 并且按照成绩进行降序排序

ORDER BY 默认情况下为升序排序即ASC(Ascend) 降序DESC(Descend)

SELECT	stu.stuName,sub.subjectName,r.resultFROM 	result r,student stu,`subject` subWHERE	r.stuId = stu.stuId AND r.subjectId = sub.subjectIdORDER BY r.result DESC;

需求： 在上方基础进行改动 要求按照成绩进行降序 且如果成绩相同按照时间降序

SELECT	stu.stuName,sub.subjectName,r.result,r.examDateFROM 	result r,student stu,`subject` subWHERE	r.stuId = stu.stuId AND r.subjectId = sub.subjectIdORDER BY 	r.result DESC , r.examDate DESC; # (X) ORDER BY r.result,r.examDate DESC;

限制查询(分页) LIMIT

LIMIT offset,row;

LIMIT startIndex起始索引<从0开始>,pageSize 分页场景

需求：只想看前两条学生信息

SELECT	*FROM	studentLIMIT 0,2; -- 如果offset是从0开始 那么可以省略 LIMIT 2

-- 基本分页：select * from result limit 10;-- 取前10行select * from result limit 5,10;-- 从第六行开始，取前10行select * from result limit 10 offset 5;-- 排序分页select * from result order by StudentNo limit 10;

分页： 因为数据量比较大的时候 如果把所有数据显示在一页上

LIMIT 可以在我们的MySQL中实现分页的数据查询/指定页码的数据查询

• 需求： 现在学生信息要求每页显示2条 想查看第一页的数据

SELECT * FROM student LIMIT 0,2;

• 要求查看第二页的数据

SELECT * FROM student LIMIT 2,2;

• 要求查看第三页的数据

SELECT * FROM student LIMIT 4,2;

• 页码和起始索引的计算公式：(页码 - 1) * 显示条数

startIndex = (currentPage - 1) * pageSize;

MySQL子查询

子查询： 在一个查询中又嵌套了其他的查询，那么嵌套的查询就被称为子查询，而外层的查询被称为父查询。子查询可以任意嵌套！可以出现在INSERT UPDATE DELETE WHERE等中…

需求： 查询在高等数学考试中，成绩比张三高的学生的姓名信息

如果不考虑连表查询

SELECT	stuIdFROM	resultWHERE	subjectId = 1	AND	result > 86;

1.查询高等数学的课程编号

SELECT	subjectIdFROM	`subject`WHERE 	subjectName = '高等数学';

整合

SELECT	stuIdFROM	resultWHERE	subjectId = (		SELECT			subjectId		FROM			`subject`		WHERE 			subjectName = '高等数学'	)	AND	result > 86;

2.查询张三的高数考试成绩

SELECT	resultFROM	resultWHERE	stuId = (SELECT stuId FROM student WHERE stuName = '张三')	AND subjectId = (SELECT subjectId FROM `subject` WHERE subjectName = '高等数学');

整合

SELECT	r.stuId,stu.stuNameFROM	result r,student stuWHERE	r.stuId = stu.stuId	AND	subjectId = (		SELECT subjectId FROM `subject` WHERE subjectName = '高等数学'	)	AND	result > (		SELECT result FROM result WHERE stuId = (				SELECT stuId FROM student WHERE stuName = '张三'		)		AND subjectId = (				SELECT subjectId FROM `subject` WHERE subjectName = '高等数学'		)	);

使用子查询解决连表查询

要求查询学生姓名、年级名称

原来

SELECT stu.stuName,g.gradeName FROM student stu,grade g WHERE stu.gradeId = g.gradeId;

子查询

SELECT	stu.stuName,	(SELECT g.gradeName FROM grade g WHERE g.gradeId = stu.gradeId) AS gradeNameFROM	student stu;

MySQL常用函数

常用的字符串函数

• 字符串拼接 CONCAT(str1,str2,…)

SELECT CONCAT('这是','MySQL','数据库');

• 字符串内容替换 REPLACE(str,from_str,to_str)

SELECT REPLACE('这是MySQL数据库','MySQL','Oracle');

• 去除左侧空格

SELECT LTRIM('     Hello World  ');

• 去除右侧空格

SELECT RTRIM('   Hello World  ');

• 获取字符串长度

SELECT LENGTH('Hello');

• 截取字符串SUBSTR(str,pos) 索引从1开始

SELECT SUBSTR('Hello World',5);

• SUBSTR(str,pos,len)

SELECT SUBSTR('Hello World',5,3);

常用的数学函数

• 获取随机数 Math.random()

SELECT RAND();

• 向上取整

SELECT CEIL(20.4);

• 向下取整

SELECT FLOOR(20.7);

• 四舍五入

SELECT ROUND(20.5);SELECT ROUND(20.76,1)

常用的时间函数

• 获取当前时间

SELECT NOW();SELECT CURRENT_DATE();SELECT CURRENT_TIME();

• 获取各种时间信息的函数

SELECT MONTH(NOW());SELECT YEAR(NOW());SELECT DAY(NOW());SELECT HOUR(NOW());SELECT MINUTE(NOW());SELECT SECOND(NOW());

• 日期转换

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');

• 计算时间差额

SELECT DATEDIFF(NOW(),'2019-05-25');

常用的聚合/统计函数

• 求年级编号最大的

SELECT MAX(gradeId) FROM grade;

• 求年级编号最小的

SELECT MIN(gradeId) FROM grade;

• 求平均分

SELECT AVG(result) FROM result WHERE subjectId = 1;

• 求和

SELECT SUM(result) FROM result WHERE subjectId = 1;

• 计算学生总人数

SELECT COUNT(*) FROM student;SELECT COUNT(stuId) FROM student;SELECT COUNT(1) FROM student;

分组查询 GROUP BY

注意事项： 在分组查询的查询字段中 不要出现与分组查询中无关的字段值

需求： 计算各个年级的学生人数

SELECT gradeId,COUNT(1) FROM student GROUP BY gradeId;

• 如果我希望查看 各个年级的总人数 且要求查看对应的学生姓名列表

SELECT gradeId,COUNT(1),GROUP_CONCAT(stuName) FROM student GROUP BY gradeId;

• 计算各个年级各个性别的人数

SELECT gradeId,gender,COUNT(1) FROM student GROUP BY gradeId,gender;

需求： 查询年级的学生人数大于1人的年级名称

SELECT	stu.gradeId,count(1),g.gradeNameFROM	student stu,grade gWHERE	stu.gradeId = g.gradeIdGROUP BY 	gradeIdHAVING 	count(1) > 1;

HAVING和WHERE的区别？

相同点： 都可以用来进行条件判断 筛选行数

不同点：

• 位置不同： WHERE是在分组之前 HAVING是在分组之后
• 条件筛选不同： WHERE是给FROM查询到的数据进行条件筛选 而HAVING是对分组后的数据进行筛选