MySQL学习Day04（狂神说Java）

4 DML查询数据（最重点）

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（a，b）
SELECT CONCAT('姓名：',StudentName) AS 新名字 FROM student

语法：SELECT 字段 FROM 表

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

去重 distinct

作用：去除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 表

4.3 where条件子句

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

搜索的条件由一个或者多个表达式组成，

逻辑运算符

运算符	语法	描述
and &&	a and b a&&b	逻辑与，两个为真结果为真
or ||	a or b a||b	逻辑或，一个为真结果为真
not ！	not a !a	逻辑非，真为假，假为真

尽量使用英文字母

 -- and &&
 SELECT studentNo,`StudentResult` FROM result WHERE StudentResult>=95 &&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

模糊查询：比较运算符

运算符	语法	描述
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结合  %代表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 '%伟%'
 
 
 -- 查询1000学员信息
 SELECT `StudentNo`,`StudentName` FROM `student` WHERE StudentNo=1000
 -- 查询在北京的学生
 SELECT `StudentNo`,`StudentName` FROM `student` WHERE `Address` IN ('北京朝阳')
 
 -- 查询地址为空的学生 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对比

 -- 查询参加了考试的同学（学号，姓名，科目编号，分数）
 SELECT * FROM student
 SELECT * FROM result
 
 /*思路
 1.分析需求，分析查询的字段来自哪些表（连接查询）
 2.确定使用哪种连接查询
 确定交叉点（这两个表中的哪些数据是相同的）
 判断条件：学生表中studentNo=成绩表 studentNo
 */
 SELECT s.studentno,studentname,subjectno,studentresult
 FROM student AS s
 INNER JOIN result AS r
 WHERE r.studentno=r.studentno
 
 -- right join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
RIGHT JOIN result r
ON s.studentno=r.studentno

-- left join
 -- right join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
LEFT JOIN result r
ON s.studentno=r.studentno
 
 -- 查询缺考的同学
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
LEFT JOIN result r
ON s.studentno=r.studentno
WHERE studentresult IS NULL
 
 
 -- 查询了参加考试的同学信息：学号，学生姓名，科目名，分数
  /*思路
 1.分析需求，分析查询的字段来自哪些表student,result,subject（连接查询）
 2.确定使用哪种连接查询
 确定交叉点（这两个表中的哪些数据是相同的）
 判断条件：学生表中studentNo=成绩表 studentNo
 */
 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
 
 -- 我要查询哪些数据select
 -- 从哪几个表中查 from 表 XXX join 连接的表 on 交叉条件
 -- 假设存在一种多张表查询，慢慢来，先查询两张表然后再慢慢增加
 -- from a left join b
 -- from a right join b

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

自连接

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

父类

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`

4.5 分页和排序

 -- 分页limit和排序order by
 -- 排序 升序asc 降序desc
 -- 查询结果根据成绩降序排序
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 DESC

分页

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

-- 分页，每页只显示五条数据
-- 语法：limit 当前页，页面的大小
-- 网页应用：当前，总的页数，页面的大小
-- limit 0,5   1~5
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 DESC
LIMIT 1,5
-- 第一页 limit 0，5
-- 第二页 limit 5，5
-- 第三页 limit 10，5
-- 第n页 limit 0，5
-- pagesize 页面大小
-- （n-1）*pagesize
-- n：当前页
-- 数据总数/页面大小=总页数

语法：limit(查询起始值，pagesize)

4.6 子查询

where（这个值是计算出来的）

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

where(select * from)

-- 查询数据库-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
 
 -- 方式二：使用子查询（）
  -- 查询所有的数据库结构-1的学生学号
 SELECT `studentno`,`subjectno`,`studentresult`
 FROM `result`
 WHERE studentno = (
SELECT subjectno FROM `subject` 
WHERE subjectname='数据库结构-1' 
 )
 ORDER BY studentresult DESC
 
-- 分数不小于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'
)