4、DQL查询数据(最重点)
4、DQL查询数据(最重点)
4.1、DQL
(data query language : 数据查询语言)
- 所有的查询操作都用它 select
- 简单的查询,复杂的查询它都能做~
- 数据库中最核心的语言,最重要的语言
- 使用频率最高的语句
SELECT 语法
4.2、指定查询字段
--查询所有的字段 格式:SELECT * FROM 表名
SELECT * FROM `student`
--查询指定字段 SELECT 字段1,字段2,... FROM 表名
SELECT `studentno` , `studentname` FROM `student`
--给查询结果,起一个别名 AS 可以给字段起别名,也可以给表起别名
SELECT `studentno` AS 学生学号 ,`studentname` AS 学生名字 FROM `student` AS s
-函数 CONCAT(a,b)拼接函数,将a和b拼接在一起,在一列中显示
SELECT CONCAT('新名字:',`studentname`) AS 新名字 FROM `student`
SELECT CONCAT(`studentno`,`studentname`) AS 学生信息 FROM `student`
语法:SELECT 字段1,字段2,... FROM 表名
有的时候,列名字不是那么见名知意,我们可以起别名 AS 字段名 AS 别名 表名 AS 别名
去重 distinct
--查询哪些同学参加了考试,(有成绩)
SELECT * FROM `result` ---查询了全部的考试成绩
SELECT `studentno` FROM `result`--查询哪些同学参加了考试
SELECT DISTINCT `studentno` FROM `result` --发现重复数据,去重 DISTINCT
作用:去除SELECT查询出来的结果中重复的数据,重复的数据只显示一条
数据库的 列(表达式)
SELECT VERSION()--查询数据库的版本号(函数)
SELECT 100*3-20 AS 计算结果 --用来计算(结果)
SELECT @@auto_increment_increment--查询自增步长(变量)
--学员成绩+1查看
SELECT `studentresult` +1 AS 成绩加一后 FROM `result`
数据库中select后面可以跟的表达式:文本值、列、NULL,函数、计算表达式、系统变量...
select 表达式 from 表
4.3、where条件子句
作用:检索数据中符合条件的值
搜索的条件由一个或多个表达式组成! 返回的结果为一个布尔值
逻辑运算符
运算符 | 语法 | 描述 |
---|---|---|
and && | a and b a&&b | 与 |
or || | a or b a||b | 或 |
not ! | not a !a | 非 |
尽量使用英文字母
--===========WHERE==============
--查询成绩在70~100之间学生的学号,成绩
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult`>=70 AND `studentresult`<=100
--and &&
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult`>=70 && `studentresult`<=100
--模糊查询(区间)
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult` BETWEEN 70 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,结果为true |
IS NOT NULL | a IS NOT NULL | 如果操作符不为null,结果为true |
BETWEEN | a BETWEEN b AND c | 若a在b和c闭区间中,结果为true |
LIKE | a LIKE b | SQL匹配,若a能匹配b,结果为true |
IN | a IN (a1,a2,a3,...) | 如果a为a1,a2,a3...中的某一个值,结果为true |
--=============模糊查询=============
--like
--查询所有姓赵的同学
--结合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(具体的一个或多个值,里面不能用%,_)
--查询学号为1000,1001,1002的学员
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentno` IN (1000,1001,1002)
--查询在北京和广州的同学
SELECT `studentno`,`studentname` FROM `student`
WHERE `address` IN ('北京朝阳','广东深圳')--这里不能IN ('北京%','广东___'),会出错,%,_是在like中用的
--IS NULL NOT 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 对比
7种SQL join语句
--===============联表查询JOIN===========
--查询参加考试的同学(学号,姓名,科目编号,分数)
/*思路:
1.分析需求,需要查询哪些字段,这些字段来自哪些表
2.确定使用哪种连接查询? 7种
3,。判断交叉点(者两个表哪个数据是相同的) 判定的条件:学生表中的`studentno` = 结果表中`studentno`
*/
--join(连接的表)ON(判断条件) 连接查询
--where 等值查询
--inner JOIN
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno`= r.`studentno`/*这儿可以是WHERE, 也可以是ON*/
--left JOIN
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` s
LEFT JOIN `result` r /*AS可省略*/
ON s.`studentno`= r.`studentno` /*注意,这儿是ON*/
--right JOIN
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` s
RIGHT JOIN `result` r
ON s.`studentno`= r.`studentno`
--查询缺考的同学
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` s
RIGHT JOIN `result` r
ON s.`studentno`= r.`studentno`
WHERE `studentresult` IS NULL
操作 | 描述 |
---|---|
INNER JOIN | 如果表中至少有一个匹配,就返回行 |
LEFT JOIN | 会从左表中返回所有值,即使右表中没有匹配 |
RIGHT JOIN | 会从右表中返回所有值,即使左表中没有匹配 |
--思考题(查询参加考试的同学的信息:学号、姓名、科目名、分数)
--学号、姓名在student表中,科目名在subject表中,分数在result表中
--student表中studentno=result表中student result表中`subjectno`=subject表中`subjectno`
--第一步:先查student表和result表,注意是`subjectno`
SELECT stu.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student AS stu
RIGHT JOIN `result` AS res
ON stu.`studentno`=res.`studentno`
--第二步,再连接查询subject表,注意是`subjectname`
SELECT stu.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student AS stu
RIGHT JOIN `result` AS res
ON stu.`studentno`=res.`studentno`
INNER JOIN `subject` AS sub
ON res.`subjectno`=sub.`subjectno`
/*思考步骤
1.我要查询哪些数据 SELECT.....
2.从哪几张表中查 FROM 表1 xxx JOIN 表2 ON 交叉条件
3.如果存在多张表查询,慢慢来,先查询两张表,再慢慢添加*/
练习:
--查询学员所属的年级(学号,学生姓名,年级名称)
SELECT `studentno`,`studentname`,`gradename`
FROM `student`
INNER JOIN `grade`
ON `student`.`gradeid`=`grade`.`gradeid`
--查询科目所属的年级(科目名称,年级名称)
SELECT `subjectname`,`gradename`
FROM `subject` AS s
INNER JOIN `grade` AS g
ON s.`gradeid`=g.`gradeid`
--查询参加 数据库结构-1 考试的同学的信息:学号、姓名、科目名、分数
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
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 sub.`subjectname`='高等数学-2';
自连接
自己的表和自己的表连接,核心:一张表拆成两张一样的表即可
--查询父子信息:把一张表看成两张一模一样的表
SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目'
FROM `category` AS a ,`category` AS b
WHERE a.`categoryid` = b.`pid`
4.5分页和排序
排序(ORDER BY)
--排序:升序 ASC 默认 降序 DESC
--ORDER BY 通过哪个字段排序 怎么排
--根据学生成绩降序排列
SELECT *
FROM `result`
ORDER BY `studentresult` DESC
-- 有100万条数据
-- 为什么要分页
-- 缓解数据库压力 给人更好的体验 当然也还是有瀑布流,要根据需求
-- 分页,每页只显示5条数据
-- 语法:LIMIT 页面的起始值,页面的大小
SELECT *
FROM `subject`
ORDER BY `subjectno` ASC
LIMIT 0,5
-- 网页应用 :当前页数,总页数,页面的大小
-- 第一页 LIMIT 0,5 (1-1)*5
-- 第二页 LIMIT 5,5 (2-1)*5
-- 第三页 LIMIT 10,5 (3-1)*5
-- 第n页 LIMIT (n-1)*5,5 (n-1)*pageSize,pageSize
-- pageSize 页面的大小
-- (n-1)*pageSize 页面的起始值
-- n:当前页
-- 数据总数/页面大小(pageSize)=总页数
语法:limit 页面起始下标,页面大小
4.6子查询
where(固定值|这个值是计算出来的)
本质:在where语句中镶嵌一个子查询语句
-- 查询分数不小于80分的学生的学号和姓名
SELECT DISTINCT s.`studentno`,`studentname`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno`=r.`studentno`
WHERE `studentresult`>=80
-- 查询分数不小于80分的学生的学号和姓名 课程名称为 高等数学-2
-- 方式一:单纯连表查询
SELECT s.`studentno`,`studentname`
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`='高等数学-2' AND `studentresult`>=80
-- 方式二:单纯子查询
SELECT `studentno`,`studentname`
FROM `student`
WHERE `studentno`=(
SELECT `studentno`
FROM `result`
WHERE `studentresult`>=80 AND `subjectno`=(
SELECT `subjectno`
FROM `subject`
WHERE `subjectname`='高等数学-2'
)
)
-- 方式三:混合一下
SELECT s.`studentno`,`studentname`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno`=r.`studentno`
WHERE `studentresult`>=80 AND `subjectno`=(
SELECT `subjectno`
FROM `subject`
WHERE `subjectname`='高等数学-2'
)
4.7、分组和过滤
-- 查询不同课程的平均分,最高分,最低分,平均分大于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`
GROUP BY `subjectname`
HAVING AVG(`studentresult`)>80 -- 对分组进行过滤,用having,如果用 WHERE AVG(`studentresult`)>80 会报错