学习笔记---DQL查询数据

DQL查询数据

1. DQL(数据查询语言)

  • 所有的查询都用它select
  • 简单的查询,复杂的查询它都能做
  • 数据库中最核心的语言

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}];
    --  指定查询的记录从哪条至哪条

2. 指定查询字段

select

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

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

-- 将列名用常用的别名替换
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM `student` AS 学生信息

-- 函数,拼接函数CONCAT(a,b)
SELECT CONCAT('姓名:',`studentname`) AS 新名字 FROM student 

去重 distinct

作用:去除select查询出来的结果中重复的数据,重复的数据只显示一条

-- 查询一下有哪些同学参加了考试
SELECT * FROM `result`  -- 查询全部的考试成绩
SELECT `studentno` FROM `result`  -- 查询有哪些同学有成绩
SELECT DISTINCT `studentno` FROM `result`  -- 发现重复数据,去重

数据库的列(表达式)

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

-- 学生考试成绩+1分
SELECT `studentno`,`studentresult`+1 AS 提分后 FROM `result`

数据库中的表达式:文本值,列,NULL,函数,计算表达式,系统变量…

select 表达式 from 表

3.where条件子句

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

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

逻辑运算符

--      ====================== where ======================
-- 查询学生成绩
SELECT `studentno`,`studentresult` FROM `result`

-- 通过成绩区间查询学生成绩
-- AND &&
SELECT `studentno`,`studentresult` FROM `result` 
WHERE `studentresult`>=80 && `studentresult`<=100

SELECT `studentno`,`studentresult` FROM `result` 
WHERE `studentresult`>=90 AND `studentresult`<=100

-- BETWEEN  AND  闭区间
SELECT `studentno`,`studentresult` FROM `result` 
WHERE `studentresult` BETWEEN 70 AND 100

-- 查询分数为98的学生信息
SELECT `studentno`,`studentresult` FROM `result` 
WHERE `studentresult`=98

-- 排除分数为98的学生信息
SELECT `studentno`,`studentresult` FROM `result` 
WHERE NOT `studentresult`=98

-- OR ||
SELECT `studentno`,`studentresult` FROM `result` 
WHERE `studentresult`=85 OR `studentresult`=98

模糊查询:比较运算符

运算符 语法 描述
IS NULL a IS NULL 如果a为空,则结果为真
IS NOT NULL a IS NOT NULL 如果a不为空,则结果为真
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,a3…其中的某一个,结果为真
          --   ====================== 模糊查询 ======================

--   =============== LIKE ===============
-- like结合  %(代表0到任意多个字符)  _(代表一个字符)
-- 查询所有学生的学号和姓名
SELECT `studentno`,`studentname` FROM `student`

-- 查询姓张的同学
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 ===============
-- IN()中括号里面的是具体的值
-- 查询地址在广东深圳和北京朝阳的学生
SELECT `studentno`,`studentname` FROM `student`
WHERE `address` IN('广东深圳','北京朝阳')
-- 只写'深圳'时查询不到

--  =============== NULL    NOT NULL ===============
-- 查询地址为空的同学   NULL或空字符''
SELECT `studentno`,`studentname` FROM `student`
WHERE `address` IS NULL OR `address`=''

-- 查询出生日期不为空的同学  IS NOT NULL
SELECT `studentno`,`studentname` FROM `student`
WHERE `borndate` IS NOT NULL

-- 查询出生日期为空的同学   IS NULL
SELECT `studentno`,`studentname` FROM `student`
WHERE `borndate` IS NULL

4.联表查询

JOIN对比

学习笔记---DQL查询数据

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

-- 查询学生考试成绩信息(名字,学号,科目编号,分数)
SELECT * FROM student
SELECT * FROM result

/* 思路:
1. 分析需求,分析查询的字段来自哪些表
2. 确定使用哪种方式连接?(7种)
3. 确定交叉点(这两个表中哪些数据是相同的)
判断的条件:学生表中的 studentno = 成绩表中的studentno
*/

-- INNER JOIN
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
WHERE s.studentno = r.studentno    -- 此处的where可改成on

-- RIGHT JOIN
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.studentno = r.studentno  -- 此处的on不能为where

-- LEFT JOIN
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
LEFT JOIN `result` AS r
ON s.studentno = r.studentno  -- 此处的on不能为where

-- 查询缺考的同学
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
LEFT JOIN `result` AS r
ON s.studentno = r.studentno  -- 此处的on不能为where
WHERE studentresult IS NULL

-- 查询参加考试的考试信息(学号,姓名,科目名称,成绩)
/* 思路:
1. 分析需求,分析查询的字段来自哪些表 student,result,subject
2. 确定使用哪种方式连接?(7种)
3. 确定交叉点(这两个表中哪些数据是相同的)
判断的条件:学生表中的 studentno = 成绩表中的studentno
	    成绩表中的 subjectno = 科目表中的subjectname
*/
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
RIGHT JOIN result r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno

-- 我要查询那些数据 select ...
-- 从那几个表中查FROM表 xxx join 连接的表 on 交叉条件
-- 假设存在一种多张表查询,从两张慢慢增加
操作 描述
INNER JOIN 如果表中至少有一个匹配,就返回行
LEFT JOIN 会从左表中返回所有的值,即使右表中没有
RIGHT JOIN 会从右表中返回所有的值,即使左表中没有

自连接(了解)

自己的表跟自己的表连接,核心:一张表拆为两张一样的表即可

父类

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

子类

pid categoryid categeryName
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
  • 自连接在两张表中的应用
-- 查询学生所属的年级(学号,学生姓名,年级名称)
SELECT `studentno`,`studentname`,`gradename`
FROM `student` AS s,`grade` AS g
WHERE s.gradeid = g.gradeid


SELECT `studentno`,`studentname`,`gradename`
FROM `student`
INNER JOIN `grade`
ON student.`gradeid` = grade.`gradeid`


-- 查询科目所属的年级(科目名称,所属年级)
SELECT `subjectname`,`gradename`
FROM `subject`,`grade`
WHERE `subject`.`gradeid` = `grade`.`gradeid`

SELECT `subjectname`,`gradename`
FROM `subject`
INNER JOIN `grade`
ON `subject`.`gradeid` = `grade`.`gradeid`
  • join连接多张表查询多个信息
-- 查询参加高等数学-1考试的考试信息(学号,姓名,科目名称,成绩)
SELECT `subjectname`,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'

5.分页和排序

排序

-- order by通过哪个字段排序,怎么排
-- 查询的结果根据成绩升序ASC     降序DESC
-- 查询参加高等数学-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'
ORDER BY `studentresult` DESC

分页

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

-- 例子:分页每行显示5条数据
-- 语法:LIMIT a,b     
-- a代表当前页的第一条数据在总数据中的顺序-1,b代表每页有多少条数据
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 0,5

-- 第一页 LIMIT 0,5  (1 - 1)* 5
-- 第二页 LIMIT 5,5  (2 - 1)* 5
-- 第三页 LIMIT 10,5 (3 - 1)* 5
-- 第四页 LIMIT 15,5 (4 - 1)* 5  
-- 第n页的书写方式:(n - 1)*pagesize, pagesize
-- [pagesize:页面大小]
-- [(n - 1)*pagesize 起始值]
-- [n: 当前页]
-- [数据总数/页面大小 + 1= 总页数]

综合运用

-- 查询 高等数学-2 课程成绩排名前十的学生,并且分数要大于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` = '高等数学-2' AND `studentresult`>80
ORDER BY `studentresult`DESC
LIMIT 0,10

6.子查询

where(一个计算出来的值)

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

-- 查询高等数学-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 `subjectno` = (
	SELECT `subjectno` FROM `subject` 
	WHERE `subjectname` = '高等数学-1'
)
ORDER BY `studentresult` DESC


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


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

-- 在这个基础上增加一个科目,高等数学-2
-- 需要查询 高等数学-2 的编号
SELECT DISTINCT s.`studentno`,`studentname`
FROM `student` s
INNER JOIN `result` r
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 r.`studentno` = s.`studentno`
INNER JOIN `subject` sub
ON sub.`subjectno` = r.`subjectno`
WHERE `studentresult`>=80 AND `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'
		)
)

7.分组过滤

-- 查询不同课程的平均分,最高分,最低分,平均分大于80
-- 核心:根据不同课程分组
SELECT `subjectname` AS '科目名称',AVG(`studentresult`) AS '平均分',MAX(`studentresult`) AS '最高分',
MIN(`studentresult`) AS '最低分'
FROM `result` r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
GROUP BY r.`subjectno`	-- 通过什么字段来分组
HAVING 平均分>80
上一篇:HTML标签:字体标签和超链接


下一篇:26.(linq)linq过滤skip 及提取tank查询操作