DQL(Data Query Language) 数据查询语言
它是SQL中最核心的部分!可难可易!
1.掌握MySQL的标准查询语句结构
SELECT [ALL|DISTINCT]
{* | 表名.* | 表名.字段名.... [AS 别名]}
FROM 要查询的表名 [AS 别名]
[LEFT | RIGHT | INNER JOIN 连接表名 [AS 别名]]
WHERE 查询条件;
1.1 SELECT后 FROM前的这一块内容 它是用来筛选查询字段的
简单理解就是你想查看哪些信息 例如:我想查看student表中的学生名、学生年龄、学生性别信息
1.查看学生表中的所有信息(不推荐写* 推荐写全字段名年列表) 效率低
SELECT * FROM student;
查看学生表中的学生姓名和性别
SELECT stuName,gender FROM student;
1.2 别名(比较常用) 注意: 此处使用中文只不过是方便你理解而已 后面不会采用写中文别名
AS关键字可以省略 ''也可以省略
SELECT stu.stuName AS '姓名',stu.gradeId + 1 年级 FROM student stu;
如果SQL过长可以进行"美化"
目前个人推荐写法
SELECT
stuName AS '姓名',
gradeId + 1 年级
FROM
student;
1.3 去重 (默认的)ALL所有的、全部 DISTINCT直接的、明显的 (去重)
SELECT ALL stuName,gradeId FROM student;
DISTINCT 用来在指定的查询字段值范围内 去除重复数据
SELECT DISTINCT stuName,gradeId FROM student;
SELECT DISTINCT stuName,gender,gradeId FROM student;
1.4 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);
2.掌握模糊查询
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 '_三%';
3.理解连接查询原理及掌握连接查询的使用 (多表连查)
连接查询: 内连接查询、外连接查询、[自连接查询]
内连接: 显式内连接、隐式内连接
查询学生的姓名和所在年级
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 JOIN grade g
ON stu.gradeId = g.gradeId;
自连接(内连接)
查询游戏名称和所属分类
SELECT
c1.categoryName,c2.categoryName
FROM
category c1,category c2
WHERE
c1.pid = c2.id
4.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]
4.1掌握排序查询 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
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;
4.2 掌握限制查询(分页) LIMIT
LIMIT offset,row;
LIMIT startIndex起始索引<从0开始>,pageSize 分页场景
需求:只想看前两条学生信息
SELECT
*
FROM
student
LIMIT 0,2; -- 如果offset是从0开始 那么可以省略 LIMIT 2
分页: 因为数据量比较大的时候 如果把所有数据显示在一页上
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;
3.掌握MySQL子查询 ()
子查询: 在一个查询中又嵌套了其他的查询,那么嵌套的查询就被称为子查询,而外层的查询被称为父查询。
子查询可以任意嵌套!可以出现在INSERT UPDATE DELETE WHERE等中…
需求: 查询在高等数学考试中,成绩比张三高的学生的姓名信息
如果不考虑连表查询
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;
4.掌握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();
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;
5.掌握分组查询 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是对分组后的数据进行筛选