准语法结构:编写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]

*/

DQL是SQL中最核心的部分！

一、MySQL的标准查询语句结构

SELECT [ ALL | DISTINCT ]

{ * | 表名.* | 表名.字段名…… [ AS 别名] }

FROM 要查询的表名 [ AS 别名 ]

[ LEFT | RIGHT | INNER JOIN 连接表名 [ AS 别名] ]

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

简单理解就是你想查看那些信息

1.查询数据库列表

show databases;

2.查询数据表列表

-- 查询当前连接的数据库的数据表列表

show tables;

-- 如果没有连接

show tables from 数据库名;

3.查询数据表的字段

desc/describe table 数据库名.表名;

show columns from 数据表名 from 数据库名；

show columns from 数据库名.表名;

-- 查询当前已连接的数据表列表

desc 表名;

示例：

mysql> desc table person_db.student;

mysql> show columns from student from person_db;

mysql> show columns from person_db.student;

mysql> desc student;

-- 查询指定字段名

desc/describe 数据库名.表名 字段名;

-- 查询当前已连接的数据表

desc 表名 字段名；

示例：

describe person_db.student0 sex;

desc student0 sex;

4.查询当前数据库的数据表内容

select * from 表名;

示例：

select * from student0;

5.查询学生表中的学生姓名和性别

SELECT stuName,gender FROM student;

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

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

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

-- 如果SQL过长可以进行美化

SELECT stuName AS '姓名',

gradeId + 1 '年级',

FROM

student;

二、ALL和DISTINCT关键字

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

-- ALL 是默认的

-- SELECT ALL stuName,gradeId FROM student;

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

示例：

SELECT DISTINCT stuName,gradeId FROM student;

SELECT DISTINCT stuName,gender,gradeId FROM student;

2.1 where 查询条件

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

用于检索数据表中符合条件的记录的

简单理解：上方的操作是用来筛选列的 where是用来筛选行的

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

2.2<关系/比较运算符和逻辑运算符>

SELECT * FROM student;

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

SELECT * FROM student WHERE gradeId > 1;

-- 复杂条件的处理：逻辑运算符 与AND 或OR 非NOT

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

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

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

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

-- 查询性别不是女的

SELECT * FROM student WHERE NOT gender = '女';

2.3特殊的比较运算符

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);

三、模糊查询

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.gradeName

FROM

student stu

INNER JOIN

grade g

ON

stu.gradeId = g.gradeId;

-- 笛卡尔积

SELECT

stu.stuName,g.gradeName

FROM

student stu,grade g;

-- 采用隐式内连接查询

SELECT

stu.stuName,g.gradeName

FROM

student stu,grade g

WHERE

stu.gradeId = g.gradeId;

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

SELECT

stu.stuName,sub.subjectName,r.result

FROM

student stu,'subject' sub, result r

WHERE

stu.stuId = r.stuId

AND

sub.subjectId = r.subjectId;

-- 外连接：左外链接、右外连接

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

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

SELECT

stu.stuName,g.gradeName

FROM

student stu

LEFT OUTER JOIN grade g

ON stu.gradeId = g.gradeId;

-- 采用右外连接

SELECT

stu.stuName,g.gradeName

FROM

student stu

RIGHT OUTER JOIN grade g

ON stu.gradeId = g.gradeId;

-- 自连接(内连接)

-- 查询游戏名称和所属分类

SELECT

c1.categoryName ,c2.categoryName

FROM

category c1,category c2

WHERE

c1.pid = c2.id;

五、排序查询 ORDER BY

排序：学生成绩、游戏排行榜(金额、战力、等级…)、音乐播放热度、淘宝销量

价格、评分、美团外卖距离…

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

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

SELECT

-- 要查询的字段名

stu.stuName,sub.subjectName,r.result

FROM

-- 表名

result r,student stu,`subject` sub

WHERE

-- 关联条件

r.stuId = stu.stuId AND r.subjectId = sub.subjectId

-- 排序字段

ORDER BY r.result DESC;

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

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

SELECT

*

FROM

student

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

SELECT

stu.stuName,sub.subjectName,r.result,r.examDate

FROM

result r,student stu,`subject` sub

WHERE

r.stuId = stu.stuId AND r.subjectId = sub.subjectId

ORDER BY

r.result DESC , r.examDate DESC; (X) ORDER BY r.result,r.examDate DESC;

六、限制查询(分页) LIMIT

LIMIT offset,row;

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

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

既不利于阅读也不利于定位查找。所以要对数据进行分页。

假设有20条数据 现在可以将数据拆分成4条每页 则有5页（在Java Web有对其进行业务的实现）

假设有21条数据 现在可以将数据拆分成4条每页 则有6页（多余一条自己占一页）

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等中…

建议在初期写子查询时 先将查询进行步骤化

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

如果不考虑连表查询

-- 先写出比张三高的学生ID

SELECT

stuId

FROM

result

WHERE

subjectId = 1

AND

result > 86;

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

SELECT

subjectId

FROM

`subject`

WHERE

subjectName = '高等数学';

-- 整合

SELECT

stuId

FROM

result

WHERE

subjectId = (

SELECT

subjectId

FROM

`subject`

WHERE

subjectName = '高等数学'

)

AND

result > 86;

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

SELECT

result

FROM

result

WHERE

stuId = (SELECT stuId FROM student WHERE stuName = '张三')

AND subjectId = (SELECT subjectId FROM `subject` WHERE subjectName = '高等数学');

-- 整合

SELECT

r.stuId,stu.stuName

FROM

result r,student stu

WHERE

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 gradeName

FROM

student stu;

四、MySQL常用函数

-- 常用的字符串函数

-- 4.1 字符串拼接 CONCAT(str1,str2,...)

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

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

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

-- 4.3 去除左侧空格

SELECT LTRIM(' Hello World ');

-- 去除右侧空格

SELECT RTRIM(' Hello World ');

-- 4.4 获取字符串长度

SELECT LENGTH('Hello');

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

SELECT SUBSTR('Hello World',5);

-- SUBSTR(str,pos,len)

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

-- 常用的数学函数

-- 4.6 获取随机数 Math.random();

SELECT RAND();

-- 4.7 向上取整

SELECT CEIL(20.4);

-- 向下取整

SELECT FLOOR(20.7);

-- 4.8 四舍五入

SELECT ROUND(20.5);

SELECT ROUND(20.76,1)

-- 常用的时间函数

-- 4.9 获取当前时间

SELECT NOW();

SELECT CURRENT_DATE();

SELECT CURRENT_TIME(http://www.my516.com);

-- 4.10 获取各种时间信息的函数

SELECT MONTH(NOW());

SELECT YEAR(NOW());

SELECT DAY(NOW());

SELECT HOUR(NOW());

SELECT MINUTE(NOW());

SELECT SECOND(NOW());

-- 4.11 日期转换

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

-- 4.12 计算时间差额

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.gradeName

FROM

student stu,grade g

WHERE

stu.gradeId = g.gradeId

GROUP BY

gradeId

HAVING

count(1) > 1;

/*

HAVING和WHERE的区别？

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

不同点：

位置不同：WHERE是在分组之前 HAVING是在分组之后

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

*/