DQL查询数据

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

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 表
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 result ; -- 查询全部的考试成绩

-- 查询哪些同学参加了考试

SELECT `studentno` AS 学号 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,函数，计算表达式，系统变量。。。

4.3 where条件子句

作用： 检索数据中符合条件的值
逻辑运算符

• and(&&),or(||),not(!)

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;


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 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,1000,10001);

-- 查询在北京的学生
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 连表查询

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

-- ==========联表查询=======

-- 查询参加了考试的同学（学号、姓名、科目编号、分数）

SELECT * FROM student;
SELECT * FROM result;

/*

思路
1.分析需求，分析查询的字段来自哪些表，（连接查询）

2.确定使用哪种连接查询？7种
确定交叉点（两个表中哪个数据是相同的）

判断条件 学生表中的studentno == 成绩表studentno


*/



-- 连接查询 join(连接的表) on 
-- 等值查询 where
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
INNER JOIN result AS r
ON 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;

-- 查询缺考的同学-------
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
LEFT JOIN result AS r
ON s.studentno = r.studentno
 WHERE studentresult IS NULL;
 
 -- 思考题（查询参加考试的学生信息：学号，学生姓名，科目名，分数）
/*
用了哪些表？ student，subject，result

*/

SELECT s.studentno AS 学生学号, studentname AS 学生姓名,subjectname AS 科目名,studentresult AS 分数
FROM student AS s
RIGHT JOIN result AS r
ON r.studentno = s.studentno
INNER JOIN SUBJECT AS sub
ON r.subjectno =sub.subjectno

-- left join      a left join b    以a为准
-- right join     a right join b   以b为准

• 自连接
  1.自己的表和自己的表连接，核心：一张表拆成两张一样的表即可

CREATE TABLE `catefory`(

	`catogoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
	`pid` INT(10) NOT NULL COMMENT '父id',
	`catogoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
	PRIMARY KEY (`catogoryid`)
)ENGINE= INNODB AUTO_INCREMENT = 9 DEFAULT CHARSET = utf8;



INSERT INTO `catefory` (`catogoryid`,`pid`,`catogoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');


-- 查询父子信息:把一张表看成两张一模一样的表
SELECT a.`catogoryName` AS '父栏目',b.`catogoryName` AS '子栏目'
FROM `catefory` AS a,`catefory` AS b
WHERE a.catogoryid =b.pid;

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

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




-- 查询参加高等数学-1考试的学生信息：学号，学生姓名，科目名，分数


SELECT DISTINCT s.`studentno` AS '学号',`studentname` AS '学生姓名',

`studentresult` AS '分数',`subjectname` AS '科目名'
FROM `student` AS s
INNER JOIN `result` AS r
ON s.studentno = r.studentno
INNER JOIN `subject` AS sub
ON r.subjectno = sub.subjectno
WHERE subjectName= '高等数学-1';
`

4.5 分页和排序

• 排序

-- 分页和排序
-- 排序：   升序：asc 降序：desc

SELECT DISTINCT s.`studentno` AS '学号',`studentname` AS '学生姓名',

`studentresult` AS '分数',`subjectname` AS '科目名'
FROM `student` AS s
INNER JOIN `result` AS r
ON s.studentno = r.studentno

INNER JOIN `subject` AS sub
ON r.subjectno = sub.subjectno
WHERE subjectName= '高等数学-1'
ORDER BY studentresult ASC;


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

-- 分页分页只显示五条数据
-- 语法： limit 起始页，页面的大小
-- 网页应用： 当前页 总页数 页面大小
-- limit 0,5     1~5
-- limit 1,5 	 2~6
-- limit 6,5 	 


SELECT DISTINCT s.`studentno` AS '学号',`studentname` AS '学生姓名',

`studentresult` AS '分数',`subjectname` AS '科目名'
FROM `student` AS s
INNER JOIN `result` AS r
ON s.studentno = r.studentno
INNER JOIN `subject` AS sub
ON r.subjectno = sub.subjectno
WHERE subjectName= '高等数学-1'
ORDER BY studentresult ASC;
LIMIT 5,5

-- 第一页 limit 0,5(pagesize)
-- 第二页 limit 5,5
-- 第三页 limit 10,5
-- 第四页 limit 15,5
-- 第五页 limit 20,5

-- ........

-- 第n页 limit (n-1)*pageSizes,pageSizes
-- 【pageSizes： 页面大小， n: 当前页， (n-1)*pageSize起始值】
-- 【数据总数/页面大小= 总页数】



-- 思考; java第一学年课程成绩排名前十的同学，并且分数大于80的学生信息
-- 学号，姓名，课程名称，分数


SELECT DISTINCT s.`studentno` AS '学号',`studentname` AS '学生姓名',

`studentresult` AS '分数',`subjectname` AS '科目名'
FROM `student` AS s
INNER JOIN `result` AS r
ON s.studentno =r.studentno 
INNER JOIN `subject` AS sub
ON r.subjectno= sub.subjectno
WHERE subjectname = 'C语言-1' AND studentresult>40
ORDER BY studentresult DESC
LIMIT 0,10

4.6 子查询

where(这个值是计算出来的)
本质： 在where语句中嵌套一个子查询

--  1、查询C语言-1的所有考试结果（学号、科目编号、成绩），降序排列

-- 方式一：使用连接查询
SELECT studentno,sub.subjectno,studentresult

FROM `result` r 
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE `subjectname` = 'C语言-1'


-- 方式二：使用子查询（）


SELECT studentno,subjectno,studentresult
FROM `result`

WHERE subjectno=(
	SELECT subjectno
	FROM `subject`
	WHERE subjectname= 'C语言-1'

)
ORDER BY StudentResult DESC
-- 分数不小于80分的学生的学号和姓名
SELECT  DISTINCT   s.`studentno`,`studentname` 
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
WHERE `studentresult`>80
-- 在这个基础上增加一个科目，高等数学-2
-- 查询 高等数学-2的编号
SELECT  DISTINCT   s.`studentno`,`studentname` 
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
WHERE `studentresult`>80 AND `subjectno`= (
SELECT `subjectno` 
FROM `subject` 
WHERE `subjectname` = '高等数学-2'
)

-- 查询课程为高等数学-2且分数不小于80 分的同学的学号和姓名

SELECT s.`studentno` , `studentname`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub  
ON r.`subjectno` = sub.`subjectno` 
WHERE `subjectname` = '高等数学-2' AND `studentresult`>80


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

-- 查询 C语言-1前五名同学的成绩的信息（学号，姓名，分数）
-- 方法1
SELECT s.`studentno`, `studentname`,`studentresult` 
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN  `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = 'C语言-1'
ORDER BY `studentresult` DESC
LIMIT 0,5

-- 方法2
SELECT s.`studentno`,`studentname`,`studentresult` 
FROM `student`  s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno` 
WHERE `subjectno` = (
SELECT `subjectno` FROM `subject` 
	WHERE `subjectname` = 'C语言-1'
LIMIT 0,5
)