mysql（四）

4,DQL查询数据（最重点）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VPsEHNzO-1628593264949)(E:\学习\开发学习\mysql\mysql04.assets\image-20210809103926011.png)]

SELECT语法

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
  [left | right | inner join table_name2]  -- 联合查询
  [WHERE ...]  -- 指定结果需满足的条件
  [GROUP BY ...]  -- 指定结果按照哪几个字段来分组
  [HAVING]  -- 过滤分组的记录必须满足的次要条件
  [ORDER BY ...]  -- 指定查询记录按一个或多个条件排序
  [LIMIT {[offset,]row_count | row_countOFFSET offset}];
   -- 指定查询的记录从哪条至哪条

关键字顺序也是有严格要求的

注意 : [ ] 括号代表可选的 , { }括号代表必选得

4.1 DQL

（Data Query Language）

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

4.2 指定查询字段

简单查询：单表

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

-- 查询指定字段
SELECT `studentno`,`studentname` FROM student

-- 别名，给结果起一个名字，AS 可以给字段和表起别名
SELECT `studentno` AS 学号,`studentname` AS 名字 FROM student
AS s 

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

语法：SELECT 字段，。。。 FROM 表

有时候，列名字不是那么的见名知意。我们起别名 AS 字段名 as 别名 表名 as 别名

4.3 去重及数据库的表达式

去重 distinct

作用：q去除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 DISTINCT `studentno`,`studentresult`+1 AS 加分后 FROM result

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

select 表达式 from 表

4.4 Where 条件子句

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

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

逻辑运算符

运算符	语法	描述
and &&	A and B	逻辑与
or ||	A or B	逻辑或
Not !	not 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 99

-- 除了1000号学生之外的同学的成绩
SELECT `studentno`,`studentresult` FROM result
WHERE `studentno`!=1000

-- != not
SELECT `studentno`,`studentresult` FROM result
WHERE NOT `studentno`=100

4.4 模糊查询操作符详解

模糊查询：比较运算符

运算符	语法	描述
IS NULL	a is NULL	如果操作符为NULL,结果为真
IS NOT NULL	a is not NULL	如果操作符为NOT NULL,结果为真
BETWEEN	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 “红 …”

-- ==================  模糊查询 =====================
-- 查询姓张的同学
-- like 结合 %(代表0到任意一个字符) _(代表一个字符)
SELECT `studentno`,`studentname`  FROM `student`
WHERE `studentname` LIKE '张%';`student`

-- 查询姓张的同学，名字后面只有一个字
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

4,4 联表查询

JOIN 对比

以 LEFT JOIN 为例：在使用 LEFT JOIN 时，ON 和 WHERE 过滤条件的区别如下：

ON 条件是在生成临时表时使用的条件，它不管 ON 中的条件是否为真，都会返回左边表中的记录；
WHERE 条件是在临时表已经生成后，对临时表进行的过滤条件。因为此时已经没有 LEFT JOIN 的含义（必须返回左侧表的记录）了，所以如果 WHERE 条件不为真的记录就会被过滤掉。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fh1KvmxI-1628593264950)(E:\学习\开发学习\mysql\mysql04.assets\image-20210807224117222.png)]

市面上的七种Join理论

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eB5SzKCz-1628593264951)(E:\学习\开发学习\mysql\mysql04.assets\OIP-C.HWoArurXY_rnq6gZVSOWBAHaF0)]`

-- ======== 联表查询 ========
-- 查询参见了考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM `student`
SELECT * FROM `result`
`subjectno`
/* 思路
1. 分析需求，分析查询的字段来自哪些表
2. 确定使用哪种连接查询？ 7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件：学生表中的 studentno = 成绩表 studentno
*/
-- 这里不用飘 `
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 AS s
RIGHT JOIN result AS r
ON s.studentno=r.studentno

-- left join
SELECT s.studentno,`studentname`,`subjectno`,`studentresult`
FROM student AS s
LEFT JOIN result AS r
ON s.studentno=r.studentno

操作	描述
inner join	如果表中至少有一个匹配，就返回行
left join	会从左表中返回所有的值，即使右表中没有匹配
right join	会从右表中返回所有的值，即使左表中没有匹配

4.5 自连接

CREATE TABLE `school`.`category`( 
`categoryid` INT(3) NOT NULL COMMENT 'id', 
`pid` INT(3) NOT NULL COMMENT '父id 没有父则为1', 
`categoryname` VARCHAR(10) NOT NULL COMMENT '种类名字', 
PRIMARY KEY (`categoryid`) 
)ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 

INSERT INTO `school`.`category` 
(`categoryid`, `pid`, `categoryname`) VALUES (2, 1, '信息技术');
INSERT INTO `school`.`CATEGOrY` 
(`categoryid`, `pid`, `categoryname`) VALUES (3, 1, '软件开发');
INSERT INTO `school`.`category` 
(`categoryid`, `PId`, `categoryname`) VALUES (5, 1, '美术设计');
INSERT INTO `School`.`category` 
(`categoryid`, `pid`, `categorynamE`) VALUES (4, 3, '数据库'); 
INSERT INTO `school`.`category` 
(`CATEgoryid`, `pid`, `categoryname`) VALUES (8, 2, '办公信息');
INSERT INTO `school`.`category` 
(`categoryid`, `pid`, `CAtegoryname`) VALUES (6, 3, 'web开发'); 
INSERT INTO `SCHool`.`category` 
(`categoryid`, `pid`, `categoryname`) VALUES (7, 5, 'ps技术');

自连接

父类

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

子类

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

子类的pid 等于 父类的categoryid

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

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

-- 查询父子信息 把一张表看为两个一模一样的表
SELECT a.`categoryname` AS '父类',b.`categoryname` AS '子类'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid`

练习

-- 查询父子信息 把一张表看为两个一模一样的表
SELECT a.`categoryname` AS '父类',b.`categoryname` AS '子类'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid`

-- 查询学员所属的年级（学号，学生的姓名，年级名称）
SELECT `studentno`,`studentname`,g.`gradename`
FROM `student` AS s,`grade` AS g
WHERE s.`gradeid`=g.`gradeid`

SELECT `studentno`,`studentname`,g.`gradename`
FROM `student` AS s
LEFT JOIN `grade` AS g
ON s.`gradeid`=g.`gradeid`

-- 查询科目所属的年级（科目名称，年级名称）
SELECT s.`subjectname`,g.`gradename`
FROM `subject` s
LEFT JOIN `grade` g
ON s.`gradeid`=g.`gradeid`


-- 查询了参见 数据库结构-1 考试学员的信息（学号，学生的姓名，科目名，分数）
SELECT s.`studentno`,`studentname`,sub.`subjectname`,r.`studentresult`
FROM `student` s 
LEFT JOIN `result` r
ON s.`studentno`=r.`studentno`
LEFT JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE sub.`subjectname`='高等数学-1'

4.5 分页和排序

-- ============= 分页 limit 和排序 order by ===============
-- 排序：升序 asc,降序 desc
-- 根据查询的结果 成绩降序 排序
SELECT s.`studentno`,`studentname`,sub.`subjectname`,r.`studentresult`
FROM `student` s 
LEFT JOIN `result` r
ON s.`studentno`=r.`studentno`
LEFT JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
ORDER BY r.`studentresult` DESC

-- 100万
-- 为什么分页
-- 缓解数据库压力，给人的体验更好，瀑布流

-- 分页，每页只显示五条数据
-- 语法: limit 起始值，页面大小
-- 网页应用：当前，总的页数，页面的大小
-- LIMIT 1,3 第一页，页面大小为3行
SELECT s.`studentno`,`studentname`,sub.`subjectname`,r.`studentresult`
FROM `student` s 
LEFT JOIN `result` r
ON s.`studentno`=r.`studentno`
LEFT JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
ORDER BY r.`studentresult` DESC
LIMIT 4,2

-- 第一页 LIMIT 0,5 (1-1)*5
-- 第二页 LIMIT 5,5 (2-1)*5
-- 第N页  LIMIT 5*(n-1),5  (n-1)*pageSize,pageSize
-- [pageSize:页面大小]
-- [(n-1)*pageSize：起始值]
-- [n：当前页]
-- [(向上取整)数据总数/页面大小=总页数]

语法:

limit（查询起始下标，pageSize）

思考题

-- 思考
-- 查询 高等数学-1课程成绩排名前十的学生 limit 0,10
-- 并且分数要大于80的学生信息（学号，姓名，课程名称，分数）、
SELECT s.`studentno`,`studentname`,sub.`subjectname`,r.`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE r.`studentresult`>80 AND sub.`subjectname`='高等数学-1'
ORDER BY r.`studentresult` DESC

4.6 子查询和嵌套查询

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

本质：在where语句中

-- ============== where ===================
-- 1,查询 高等数学-1 的所有考试结果（学号，科目编号，成绩），降序排列
-- 方式一
SELECT `studentno`,`subjectname`,`studentresult`
FROM `result` r
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE `subjectname`='高等数学-1'

-- 方式二 使用子查询（由里及外）
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result`
WHERE `studentno`=(
       SELECT `subjectno` FROM `subject` 
       WHERE `subjectname`='高等数学-1'
)

-- 查询所有 高等数学-1 的科目编号
SELECT `subjectno` FROM `subject` WHERE `subjectname`='高等数学-1'

-- 分数不小于 80 分的学生
SELECT DISTINCT s.`studentno`,`studentname`
FROM `student` s
INNER JOIN `result` r
ON r.`studentno`=s.`studentno`
WHERE `studentresult`>=80

-- 在这个基础上增加一个科目，'高等数学-1'
-- 查询 '高等数学-1' 的编号
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`='高等数学-1'
)

-- 再改造（由里及外）
SELECT `studentno`,`studentname` FROM `student`
WHERE  `studentno` IN (
     SELECT studentno FROM result WHERE studentresult>80 AND `subjectno`=(
	    SELECT subjectno FROM `subject` WHERE subjectname='高等数学-2‘
     )
)

udents INNER JOINresultr ON r.studentno=s.studentnoWHEREstudentresult>=80 ANDsubjectno=( SELECTsubjectnoFROMsubjectWHEREsubjectname`=‘高等数学-1’
)

– 再改造（由里及外）
SELECT studentno,studentname FROM student
WHERE studentno IN (
SELECT studentno FROM result WHERE studentresult>80 AND subjectno=(
SELECT subjectno FROM subject WHERE subjectname='高等数学-2‘
)
)