MySQL-06-数据管理(2)
DQL(数据查询语言)
- 所有的查询操作都用它 select
- 无论是简单还是复杂的查询它都能做
- 数据库中最核心的语言
- 使用频率最高的语言
1.简单查询----指定字段查询
-- 查询所有的学生 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`
去重 distinct
-- 查询有多少同学参加了考试
SELECT `studentno` FROM `result`
-- 发现重复数据,去重
SELECT DISTINCT `studentno` FROM `result`
数据库中的表达式:文本值,列,null,函数,计算表达式,系统变量…
-- 查看系统的版本 (函数)
SELECT VERSION()
-- 用来计算(计算表达式)
SELECT 100*2+50 AS 计算结果
-- 查询自增的步长(变量)
SELECT @@auto_increment_increment
-- 学员成绩+10分 查看
SELECT `studentno`,`studentresult`+10 AS '提分后' FROM `result`
select + 表达式
2.where 条件子句
作用:检索数据中符合条件的值
搜索的条件由一个或多个表达式构成,结果返回 布尔值
逻辑运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| and && | a and b a && b | 逻辑与 |
| or || | a or b a || b | 逻辑或 |
| not ! | not a ! b | 逻辑非 |
例子
-- =============where================
SELECT `studentno`,`studentresult` FROM `result`
-- 查询95-100分之间的
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult` BETWEEN 95 AND 100
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult` >=95 AND `studentresult`<=100
模糊查询:比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| is null | a is null | a为空返回true |
| is not null | a is not null | a为非空返回true |
| between | a between b and c | a在b和c之间返回true |
| like | a like b (比如书籍 like …书…) | SQL匹配,若a匹配b则返回true |
| in | a in (a1,a2,a3…) | 假设a在a1,或者a2…其中的某一个中,返回true |
LIKE用法,注意与通配符的结合使用
-- 查询姓赵的同学
-- like结合 %(代表0到任意个字符) _(代表一个字符)
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '赵%'
-- 查询姓赵的同学 名只有一个字
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '赵_'
IN用法,注意in()括号中是具体的值,通配符在这里不起作用
-- 查询 1001,1002号学员(给定集合,用in关键字)
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentno` IN (1001,1002);
-- 查询在北京的学生
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentno` IN ('北京');
NULL和NOT NULL,判断是否为空
-- 查询地址为空的学生
SELECT `studentno`,`studentname` FROM `student`
WHERE `address` = '' OR `address` IS NULL
-- 查询由出生日期的同学
SELECT `studentno`,`studentname` FROM `student`
WHERE `borndate` IS NOT NULL
3.联表查询
join on
-- 查询参加了考试的同学(学号,姓名,科目编号,分数)
/*
分析:
查看所查询字段来自哪些表
若大于两张,联表查询
查看表的交叉部分
将其作为连接表格的条件
*/
-- inner join
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`
-- 查询缺考的学生
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
LEFT JOIN `result` AS r
ON s.`studentno` = r.`studentno`
WHERE `studentresult` IS NULL
-- 查询参加了考试的同学的信息: 学号,姓名,科目名,分数 三表查询
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.`studentno` = r.`studentno`
LEFT JOIN `subject` AS sj
ON r.`subjectno` = sj.`subjectno`
| 操作 | 描述 |
|---|---|
| inner join | 如果表中至少有一个匹配,就返回行 |
| left join | 会从左表中返回所有的值,即使右表中没有匹配 |
| right join | 会从右表中返回所有的值,即使左表中没有匹配 |
tips
- 注意,由于连表时存在公共列,必须指明来自哪个表(别名方便),否则存在歧义
- on 是先筛选后关联(批量)
- where 是先关联后筛选
- inner join用on和where都可以,其他用where可能会报错
- join (连接的表) on (判断条件[联表条件])-------用于连接查询
- where 等值查询
自连接
自己的表和自己的表连接,核心:一张表拆为两张一样的表
有些表格包含了树性结构,将父类和子类放在一起,pid为1的为父,其余皆为派生
| categoryid | pid | categoryname |
|---|---|---|
| 2 | 1 | 信息技术 |
| 3 | 1 | 软件开发 |
| 4 | 3 | 数据库 |
| 5 | 1 | 美术设计 |
| 6 | 3 | web开发 |
| 7 | 5 | ps技术 |
| 8 | 2 | 办公信息 |
现要求查询父类对应子类的信息表
-- 查询父类对应子类的信息表
SELECT parent.`categoryname` AS '父栏目',child.`categoryname` AS '子栏目'
FROM `category` AS parent,`category` AS child
WHERE parent.`categoryid` = child.`pid`
4.分页和排序
排序:order by
-- 排序: 升序ASC ,降序 DESC
-- 查询的结果根据成绩排序
-- ORDER BY 要排序字段 排序方式
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.`studentno` = r.`studentno`
LEFT JOIN `subject` AS sj
ON r.`subjectno` = sj.`subjectno`
ORDER BY `studentresult` DESC
分页:limit
-- 分页
-- 为什么需要分页?
-- 缓解数据库压力,用户友好体验 但也存在瀑布流
-- 每页只显示2条数据
-- 语法:limit 起始值,页面的大小
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.`studentno` = r.`studentno`
LEFT JOIN `subject` AS sj
ON r.`subjectno` = sj.`subjectno`
ORDER BY `studentresult` DESC
LIMIT 0,2
-- 对前端而言
-- 第一页 limit 0,2 (1-1)*2
-- 第二页 limit 2,2 (2-1)*2
-- ...
-- 第n页 limit 2*(n-1),2 (n-1)*pageSize,pageSize
-- pageSize:页面大小(几行)
-- n:当前页
-- (n-1)*pageSize:起始值
-- 总页数:数据总数/页面大小
综合题
-- 查询高等数学-1课程成绩排名前3的学生，并且分数要大于80的学生信息(学号，姓名，课程名称，分数) 注意顺序
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` AS sj
ON r.`subjectno` = sj.`subjectno`
WHERE `subjectname`='高等数学-1' AND `studentresult`>=80
ORDER BY `studentresult` DESC
LIMIT 0,3
5.子查询
where(可以是常量也可以是变量)
在where语句中嵌套一个子查询语句
where(select * from…)
where 只负责查询而没有连表(更快)
-- ====查询高等数学-1课程的所有成绩(学号,科目编号,成绩),降序排列
-- 方式1:连接查询
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM `result` AS r
INNER JOIN `subject` AS sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname`='高等数学-1'
ORDER BY `studentresult` DESC
-- 方式2.使用子查询
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM `result` AS r
WHERE `subjectno` = (
-- 查询名字为高等数学-1课程号
SELECT `subjectno`
FROM `subject`
WHERE `subjectname` ='高等数学-1'
)
ORDER BY `studentresult` DESC
-- 查询分数不小于80分的学生的学号和姓名
SELECT s.`studentno`,`studentname`
FROM `student` AS s
WHERE s.`studentno` IN (-- 注意,由于子查询返回了多个数据(集合),这里用in
SELECT DISTINCT r.`studentno`
FROM`result` AS r
WHERE `studentresult`>=80
)
6.分组和过滤
-- 查询不同课程的平均分,最高分,最低分,平均分需>=80
-- 核心(根据不同课程分组)
SELECT `subjectname`,AVG(`studentresult`) AS 平均分 ,MAX(`studentresult`) AS 最高分,MIN(`studentresult`) AS 最低分
FROM `result` AS r
INNER JOIN `subject` AS sub
ON r.`subjectno` = sub.`subjectno`
-- where 平均分>=80 -- 报错 分组前
GROUP BY r.`subjectno` -- 通过什么字段分组
HAVING 平均分>=80 -- 在分组后进行
7.select小结
顺序
select [distinct] 查询字段 form 表 -- 字段和表可取别名
*** join 要连接的表
on 连接条件
where (具体的值,子查询语句)
group by 分组字段 -- 对哪个字段进行分组
having 过滤条件 -- 分组后进行根据条件筛选过滤
order by 排序字段 排序方式[升/降] -- 通过排序字段进行排序
limit startIndex,pageSize -- 分页
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
