MySQL数据库基础--DQL查询数据详解

DQL查询数据（最重点）

（Data Query LANGUAGE : 数据查询语言）

• 所有的查询操作都用它 Select
• 简单的查询，复杂的查询它都能做
• 数据库中最核心的语言
• 使用频率最高

-- SELECT 语法
SELECT[ALL|DISTINCT|DISTINCTROW|TOP]
{*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]}
FROM tableexpression[,…][IN externaldatabase]
[WHERE…]
[GROUP BY…] -- 指定结果按照哪几个字段来分组
[HAVING…]  -- 过滤分组的记录必须满足的次要条件
[ORDER BY…]  -- 指定查询记录按一个或多个条件排序
[limit]  -- 指定查询的记录从哪条到哪条

指定查询字段

-- 查询全部的学生  SELECT 字段 FROM 表
SELECT * FROM student

SELECT * FROM result

-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM student

-- 起别名，给结果起一个名字  AS    表头更名
-- 也可以给表起别名
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM student AS s

-- 函数 CONCAT(A,B)  拼接字符串
SELECT CONCAT('姓名: ',StudentName) AS 新名字 FROM student

语法：SELECT 字段,… FROM 表

有时候，列名不容易见名知意，可以起别名 AS 字段名 AS 别名 表名 AS 别名

去重

作用，去除SELECT语句查询出的重复结果

SELECT * FROM result -- 查询全部成绩
SELECT `StudentNo` FROM result -- 查询哪些学生参加了考试

SELECT DISTINCT `StudentNo` FROM result  -- 发现重复数据，去重

数据库的列（表达式）

SELECT VERSION()  -- 查询系统版本  （函数）
SELECT 100*3-1 AS 计算结果  -- 用于计算  （表达式）
SELECT @@auto_increment_increment  -- 查询自增步长  （变量）

-- 学员考试成绩 +1分 查看
SELECT `StudentNo`,`StudentResult`+1 AS 加分后 FROM result

数据库中表达式：文本值，列，null，函数，计算表达式，系统变量…

select 表达式 from 表

where条件子句

作用：检索数据中符合条件的值

搜索的条件由一个或多个表达式组成！结果 布尔值

1. 逻辑运算符

   运算符	语法	描述
   and &&	a and b a&&b	逻辑与，两个都为真结果为真
   or ||	a or b a||b	逻辑或，其中1个为真即为真
   Not ！	Not a ！a	逻辑非

   尽量使用英文字母

   -- where
   select `studentNo`,`StudentResult` from result  -- 列名也不区分大小写
   
   -- 查询考试成绩在95-100范围内
   SELECT `studentNo`,`StudentResult` FROM result
   where studentresult >= 95 and studentresult <= 100
   
   -- 模糊查询（区间）
   select `StudentNo`,`StudentResult` from result
   where `StudentResult` between 95 and 100
   
   -- 除了1000号以外的学生的成绩
   SELECT `StudentNo`,`StudentResult` FROM result
   where `StudentNo` != 1000;
   
   -- !=  Not
   SELECT `StudentNo`,`StudentResult` FROM result
   WHERE not `StudentNo`=1000;
   

2. 模糊查询：比较运算符

   运算符	语法	描述
   IS NULL	a is null	如果操作符为NULL，结果为真
   IS NOT NULL	a is not null	如果操作符不为NULL，结果为真
   BETWEEN AND	a between b and c	若a在b和c之间，结果为真
   Like	a like b	SQL匹配，如果a匹配到b，结果为真
   In	a in (a1,a2,a3…)	假设a在a1,a2…其中的某个值中，结果为真

-- ===============模糊查询=================

-- 查询姓刘的同学
-- like结合 %(代表0-任意个字符)  _（代表一个字符）
select `StudentNo`,`StudentName` from `student`
where `StudentName` like '刘%'

-- 查询姓刘的同学名字后面只有一个字的
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `StudentName` LIKE '刘_'

-- 查询姓刘的同学名字后面只有两个字的
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `StudentName` LIKE '刘__'

-- 查询名字中有嘉字的
select `StudentNo`,`StudentName` from `student`
where `StudentName` like '%嘉%'

-- ===== in（具体的一个或多个值） =====
-- 查询1001，1002，1003号学员信息
SELECT `StudentNo`,`StudentName` FROM `student`
where `StudentNo` in (1001,1002,1003);

-- 查询在北京的学生
SELECT `StudentNo`,`StudentName` FROM `student`
where `Address` in ('北京','河南洛阳')

-- ===== null not null =====
-- 查询地址为空的学生 null 或者空串''
SELECT `StudentNo`,`StudentName` FROM `student`
where `Address`='' or `Address` is null 

-- 查询有出生日期的同学
SELECT `StudentNo`,`StudentName` FROM `student`
where `BornDate` is not null

-- 查询没有出生日期的同学
SELECT `StudentNo`,`StudentName` FROM `student`
where `BornDate` is null

联表查询

1. JOIN 对比

   Join(连接的表) on(判断条件) 连接查询

   where 等值查询

   七种JOIN理论

   

   -- 查询参加了考试的同学（学号，姓名，科目编号，分数）
   SELECT * FROM student
   SELECT * FROM result
   
   /*思路
   1. 分析需求，分析查询的字段来自哪些表 （连接查询）
   2. 确定使用哪种连接查询？ 7种
   确定交叉点（这两个表中哪个数据是相同的）
   判断的条件：学生表中 StudentNo = 成绩表中 StudentNo
   */
   
   -- inner join
   SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult`
   FROM `student` AS s
   INNER JOIN result AS r
   WHERE s.StudentNo = r.StudentNo
   
   -- Right Join
   SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult`
   FROM `student` s -- 别名可以不写 AS
   RIGHT JOIN result r
   ON s.StudentNo = r.StudentNo  -- 这里on与where作用大致相同
   
   -- Left Join
   SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult`
   FROM `student` s -- 别名可以不写 AS
   LEFT JOIN result r
   ON s.StudentNo = r.StudentNo  -- 这里on与where作用大致相同
   

   操作	描述
   Inner Join	用from表为主表中的每一行去匹配另一个表，匹配条件为On语句或where语句
   Left Join	会从左表中返回所有的值，即使右表没有匹配
   Right Join	会从右表中返回所有的值，即使左表没有匹配

   -- 查询缺考的同学
   SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult`
   FROM `student` s -- 别名可以不写 AS
   LEFT JOIN result r
   ON s.StudentNo = r.StudentNo
   WHERE `StudentResult` IS NULL
   
   -- 思考题（查询参加考试的同学信息：学号、学生姓名、科目名、分数）
   -- 分析：student result subject 三张表
   
   SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
   FROM student s
   RIGHT JOIN result r
   ON r.StudentNo = s.StudentNo
   INNER JOIN `subject` sub
   ON r.SubjectNo=sub.SubjectNo
   
   -- 多表查询要一步步写，先查询两张再增加
   

2. 自连接

   自己的表和自己连接，核心：一张表拆为两张一样的表即可

   父类

   categoryid	categoryname
   2	信息技术
   3	软件开发
   5	美术设计

   子类

   pid	categoryid	categoryname
   3	4	数据库
   2	8	办公信息
   3	6	web开发
   5	7	ps技术

   操作：查询父类对应的子类关系

   父类	子类
   信息技术	办公信息
   软件开发	数据库
   软件开发	web开发
   美术设计	ps技术

   -- 查询父子信息
   SELECT a.`categoryname` AS '父栏目',b.`categoryname` AS '子栏目'
   FROM `category` AS a,`category` AS b
   WHERE a.`categoryid`=b.`pid`
   

   -- ========== 自连接 ===========
   CREATE TABLE `school`.`category`( 
     `categoryid` INT(3) NOT NULL COMMENT '主题id', 
     `pid` INT(3) NOT NULL COMMENT '父id',
     `categoryname` VARCHAR(10) NOT NULL COMMENT '主题名字',
   PRIMARY KEY (`categoryid`)
   ) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 
   
   INSERT INTO `category` (`categoryid`, `pid`, `categoryname`)
   VALUES (2,1,'信息技术'),
   (3,1,'软件开发'),
   (4,3,'数据库'),
   (5,1,'美术设计'),
   (6,3,'web开发'),
   (7,5,'ps技术'),
   (8,2,'办公信息');
   
   -- 查询父子信息
   SELECT a.`categoryname` AS '父栏目',b.`categoryname` AS '子栏目'
   FROM `category` AS a,`category` AS b
   WHERE a.`categoryid`=b.`pid`
   
   -- 查询学员所属年级（学号、学生姓名、年级名称）
   SELECT `StudentNo`,`StudentName`,`GradeName`
   FROM student s
   INNER JOIN `grade` g
   ON s.`GradeId` = g.`GradeId`
   
   -- 查询科目所属的年级 (科目名称、年级名称)
   SELECT `SubjectName`,`GradeName`
   FROM `subject` sub
   INNER JOIN `grade` g
   ON sub.`GradeId` = g.`GradeId`
   
   -- 查询参加 数据库结构-1 考试的同学信息：学号、学生姓名、科目名、分数
   SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
   FROM `student` s
   INNER JOIN `result` r
   ON s.StudentNo = r.StudentNo
   INNER JOIN `subject` sub
   ON r.`SubjectNo` = sub.`SubjectNo`
   WHERE `SubjectName` = '数据库结构-1'
   

分页和排序

1. 排序

-- ========== 分页 limit 和 排序 order by =============
-- 排序：升序 ASC  ，降序  DESC
-- ORDER BY 通过哪个字段排序，怎么排

-- 查询的结果根据 成绩降序排序
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` s
INNER JOIN `result` r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE `SubjectName` = '数据库结构-1'
ORDER BY `StudentResult` ASC

2. 分页

   -- 为什么要分页？
    -- 缓解数据库压力，给人的体验更好
    
    -- 分页，每页只显示五条数据
    -- 语法：limit 起始行，一页显示几行
    -- 网页应用：当前，总的页数，页面的大小
    -- LIMIT 0,5  1~5
    -- LIMIT 1,5  2~6
     
   SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
   FROM `student` s
   INNER JOIN `result` r
   ON s.StudentNo = r.StudentNo
   INNER JOIN `subject` sub
   ON r.`SubjectNo` = sub.`SubjectNo`
   WHERE `SubjectName` = '数据库结构-1'
   ORDER BY `StudentResult` ASC
   LIMIT 1,5
   
   -- 【pageSize：页面大小】
   -- 【(n-1)*pageSize:起始值】
   -- 【n：当前页】
   -- 【数据总数/页面大小 = 总页数】
   

   语法：limit(查询起始下标，pageSize)

   -- 查询 JAVA第一学期 课程成绩排名前十的学生，并且分数要大于80 的学生信息（学号、姓名、课程名称、分数）
   select s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
   from `student` s
   inner join result r
   on s.`StudentNo` = r.`StudentNo`
   inner join `subject` sub
   on sub.`SubjectNo` = r.`SubjectNo`
   WHERE `SubjectName` = 'Java程序设计-1' AND StudentResult>=80
   order by `StudentResult` DESC
   limit 0,10
   

子查询

where (这个值是计算出来的)

本质：在where语句中嵌套一个子查询语句

-- ========= where =========

-- 1.查询数据库结构-1的所有考试结果 （学号、科目编号、成绩），降序排列
-- 方式一：使用连接查询
SELECT `StudentNo`,r.`SubjectNo`,`StudentResult`
FROM `result` r
INNER JOIN `subject` sub
ON r.SubjectNo = sub.SubjectNo
WHERE `SubjectName` = '数据库结构-1'
ORDER BY StudentResult DESC

-- 方式二：使用子查询（由里及外）
SELECT `StudentNo`,`SubjectNo`,`StudentResult`
FROM `result`
WHERE StudentNo = (
      SELECT `SubjectNo` FROM `subject`
      WHERE `SubjectName`='数据库结构-1'
)
ORDER BY StudentResult DESC


-- 2.查询课程为 高等数学-2 且 分数不小于80分的学生的学号和姓名
SELECT s.`StudentNo`,`StudentName`
FROM `student` s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
INNER JOIN `subject` sub
ON sub.SubjectNo = r.SubjectNo
WHERE SubjectName = '高等数学-2' AND StudentResult >= 80


-- 分数不小于80分的学生的学号和姓名
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM `student` s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
WHERE StudentResult >= 80

-- 在这个基础上增加科目  高等数学-2
-- 查询高等数学-2的编号
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM `student` s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
WHERE StudentResult >= 80 AND `SubjectNo` = (
      SELECT `SubjectNo` FROM `subject`
      WHERE `SubjectName`='高等数学-2'
)

-- 再改造 (由里及外)
SELECT `StudentNo`,`StudentName` FROM student WHERE `StudentNo` IN (
    SELECT `StudentNo` FROM `result` WHERE `StudentResult`>=80 AND `SubjectNo` = (
        SELECT `SubjectNo` FROM `subject` WHERE `SubjectName`='高等数学-2'  
  )
) 
-- 子查询效率更高

-- 练习：查询 C语言-1 前五名学员的成绩信息（学号、姓名、分数）(不确保正确！欢迎指正)
SELECT s.`StudentNo`,`StudentName`,`StudentResult`
FROM student s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
WHERE s.`StudentNo` IN (
    SELECT `StudentNo` FROM `result` WHERE `SubjectNo` = (
        SELECT `SubjectNo` FROM `subject` WHERE `SubjectName` = 'C语言-1'
    ) 
)
ORDER BY StudentResult DESC
LIMIT 0,5

过滤和分组

-- 查询不同课程的平均分、最高分、最低分
-- 核心：根据不同课程分组
SELECT `SubjectName`,AVG(StudentResult) AS 平均分,MAX(StudentResult),MIN(StudentResult)
FROM result r
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
GROUP BY r.SubjectNo  -- 通过某个字段分组
HAVING 平均分 > 80  -- 过滤分组的记录必须满足的次要条件