4、DQL查询数据

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

4.1 DQL

(Data Query Language:数据查询语言)

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

4.2、指定查询字段

-- 查询全部的学生  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 别名

去重 distINCT
作用：发现重复的数据只显示一条

-- 查询一下有哪些同学参加了考试，成绩

SELECT * FROM result  -- 查询全部的考试成绩

SELECT `studentname` FROM `result` -- 查询有哪些同学参加了考试

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

数据库的列

-- 查询系统的版本
SELECT VERSION()
SELECT 100*3-1 AS 计算结果  -- 用来计算 (表达式)
SELECT @@auto_increment_increment  -- 查询自增的步长 (变量)
-- 学院考试成绩加一分查看
SELECT `studentno`,`studentresult` +1 AS '提分后' FROM result 

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

4.3、 where 条件子句

作用：检索数据中符合条件的值
搜索的条件由一个或多个表达式组成 返回值为boolean值

逻辑运算符

运算符	语法	描述
and &&	a and b a&&b	逻辑与
or	a or b	逻辑或
not !	not a !a	逻辑非

SELECT `studentno`,`studentresult`FROM result

-- 查询考试成绩在95~100分之间的
SELECT `studentno`,`studentresult`FROM result
WHERE `studentresult`>=95 AND `studentresult`<=100;  


-- 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  除了1000 号以外的成绩
SELECT `studentno`,`studentresult`FROM result
WHERE NOT `studentno` =1000

模糊查询：比较运算符

运算符	语法	描述
IS NULL	a is null	如果操作符的结果为NULL，结果为真
IS NOT NULL	a is not null	如果操作符不为NUL，结果为真
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。。。其中的某一个值，结果为真。

--  ========= NULL   NOT NULL ==========

-- ======================模糊查询===========
--  查询姓刘的同学
-- 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

4.4、联表查询

JOIN 对比

操作	描述
inner join	只要有一个匹配即可
left join	左表为主表，主表中匹配即可
right join	右表为主表，主表匹配即可

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

-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM student
SELECT * FROM result



/*思路：
1.分析需求，分析查表得字段来自哪些表，（连接查询）
2.确定使用哪种连接查询？7种
确定交叉点（这两个表中的哪个数据是相同的）
判断条件：学生表中的 学号 = 成绩表中的 学号
*/

-- join （连接的表） on （判断条件） 连接查询
-- where (等值查询)


SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student AS s
INNER JOIN result AS r
WHERE s.studentno = r.studentno

-- 交集查询
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


/* 思考题（查询参加考试的同学信息：学号，学生姓名，科目名，分数）
思路：
1.分析需求，看看你需要哪些表
2.寻找表与表之间的联系，即相同的字段
3.确定使用哪种连接
判断条件   学生表.字段 = 成绩表.字段
*/

SELECT s.`studentno`,`studentname`,`studentresult`,`subjectname`
FROM student AS s
INNER JOIN result AS r
ON s.studentno = r.studentno
LEFT JOIN `subject` AS sub
ON r.subjectno = sub.subjectno


-- 我要查询哪些数据
-- 从那几个表中查 from xxxx join 连接的表 on 交叉条件
-- 假设存在一种多张表连接，要慢慢开，两个两个连接 慢慢增加

-- from a left join b
-- from a right join b

自链接

自己的表和自己的表连接，核心：一张表拆为两张一模一样的表
父类：

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

子类

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

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

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

-- ================自连接=======
DROP TABLE category


CREATE TABLE IF NOT EXISTS category (
	categoryid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题ID',
	pid INT(10) NOT NULL COMMENT '父ID',
	categoryName VARCHAR(50) 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  b.`pid` = a.`categoryid`

4.5 分页和排序

ORDER BY studentresult ASC 升序： asc 降序 ： desc

-- =========分页和排序 ==========
-- 升序： asc 降序 ： desc
-- order by  通过哪个字段排序 
-- 根据查询的结果  成绩降序 排序
SELECT s.`studentno`,`studentname`,`studentresult`,`subjectname`
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万
-- 为什么分页
-- 环节数据库压力，给人的体验更好，  瀑布流

-- 分页 每页显示2条信息
-- 语法： limit 起始页，页面大小
-- 网页应用： 当前页，总的页数，页面的大小
-- LIMIT 0~2
-- LIMIT 0,2 1~0
-- LIMIT 1,2 2~3
SELECT s.`studentno`,`studentname`,`studentresult`,`subjectname`
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 1,2
-- 第一页 limit 0,5 
-- 第二页 limit 5，5
-- 第三页 limit 10,5
-- 第N页  limit  （N-1）*pageSize ，5
-- [pageSize] 页面大小    
-- （N-1）*pageSize  起始值   
-- N：当前页
-- 数据总数/页面大小 = 总页数

语法： limit (查询起始下标*页面大小)

4.6、子查询

where (这个值是计算出来的)
本质：在where语句中嵌套where语句
where(select * from)

-- ============where(子查询)========
-- 1.查询高等数学-1的所有考试结果（学号，科目名称号，成绩），降序排列
-- 方式一：使用连接查询
SELECT `studentno`,`subjectname`,`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 `subjectno` = (
	SELECT `subjectno` FROM `subject` 
	WHERE `subjectname` = '高等数学-1'
)

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

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

-- 在这个基础上增加一个科目，高等数学-1
SELECT DISTINCT s.`studentno`,`studentname`
FROM student AS s
INNER JOIN `result` r
ON r.`studentno`=s.`studentno`
WHERE `studentresult`>=80 AND `subjectno`= (
	SELECT `subjectno` FROM`subject` 
	WHERE `subjectname` ='高等数学-1'
)

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






-- 查询课程为 高等数学-1 且分数不小于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` = '高等数学-1' AND `studentresult`>=80



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