3、DQL查询数据(最重点)
3.1、DQL
(Date Query Language:数据查询语言)
- 所有查询操作都用它 select
- 简单查询,复杂的查询它都能做~
- 数据库中最核心的语言,最重要的语句
- 使用频率最高的语言
select语法
SELECT [ALL | DISTINCT] -- 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] -- 过滤分组的记录必须满足的次要条件,和where是一样的,位置不同
[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
[LIMIT {[offset,]row_count | row_countOFFSET offset}];
-- 指定查询的记录从哪条至哪条
注意:[ ]括号代表可选,{ }括号代表必选
3.2、指定查询字段
-- 查询所有学生
-- 语法:select 字段 from 表名
select * from student
--查询指定字段
select `studentno`,`studentname` from student
-- 别名 可以给字字段和表取别名
select `studentno` as 学号,`studentname`as 学生姓名 from
-- 函数 concat (a,b) 拼接字符串
select concat('姓名:',studentname) as 新名字 from student
去重 distinct
作用:去除select查询出来的结果中重复的数据,重复数据只有一条
-- 查询一下有哪些同学参加了考试
select `studentno` from result
-- 发现重复数据,distinct去重
select distinct `studentno` from result
数据库的列(表达式)
select version() -- 查询mysql系统版本(函数)
select 100*3-1 as 计算结果 -- 输出计算结果(计算表达式)
select @@auto_increment_increment -- 查询自增的步长(变量)
-- 所有学员考成绩 +1分后查看 注:实际并没有真正修改数据
select `studentno`,`studentresult`+1 as '提分后' from result
数据库中的表达式:文本值,列,Null,函数,计算表达式,系统变量…
用法:select表达式
from 表
3.3、where条件子句
作用:检索数据中符合条件
的值
搜索的条件由一个或者多个表达式组成!结果为布尔值
逻辑运算符
运算符 | 语法 | 描述 |
---|---|---|
and && | a and b a&&b | 逻辑与,两个都为真,结果为真 |
or || | a or b a||b | 逻辑或,其中一个为真,则结果为真 |
Not ! | not a !a | 逻辑非,真为假,假为真 |
尽量使用英文字母
-- ============ where案例 =============
--查询考试成绩在95~100分之间
select `studentno`,`studentresult` from result
where studentresult>=85 and studentresult<=100
-- 区间查询(类似模糊查询)
select `studentno`,`studentresult` from result
where `studentresult` between 85 and 100
-- 查询除了学号为1000之外同学的成绩
-- 方式1
select `studentno`,`studentresult` from result
where studentno !=1000
-- 方式2
select `studentno`,`studentresult` from result
where not studentno =1000
模糊查询 :比较运算符
运算符 | 语法 | 描述 |
---|---|---|
is null | a is null | a为null,结果为真 |
is not null | a is not null | a 不为null,结果为真 |
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 中的某一个值结果为真 |
-- ================= 模糊查询 like =================
-- 查询姓张的同学/查询张某某同学
-- like结合 %(代表没有字符或者多个字符) _(代表一个字符)
select `studentno`,`studentname` from `student`
where studentname like '张%'
-- _(代表一个字符)
select `studentno`,`studentname` from `student`
where studentname like '张_'
-- ============ in (值,值2) ==============
-- 查询1001,10002,1003学员
select `studentno`,`studentname` from `student`
where studentno in(1001,1002,1003)
-- ======= null not null ========
-- 查询地址为空的学生 可以使用nul 或者空字符串 '' 空字符串不是null
select `studentno`,`studentname` from `student`
where `address`='' or `address` is null
-- 查询无出生日期同学 not nul
select `studentno`,`studentname` from `student`
where `borndate` is not null
注意: 空字符串不是null
3.4、连表查询(重点,难点)
join
7种join理论:
-- =================连表查询 join===================
/*思路
1.分析需求,分析查询的字段来自哪些表,(连接查询)
2.确定使用哪种连接查询? 一共7种
确定交叉点(这两个表中那个数据是相同的)
判断的条件:学生表中的studentNo=成绩表 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
inner 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
操作 | 描述 |
---|---|
inner join | 如果表中至少有一个匹配,就返回行 |
left join | 会从左表中返回所有的值,即使右表中没有匹配 |
right join | 会从右表中返回所有的值,即使左表中没有匹配 |
-- 查询缺考的同学
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 s
right join result r
on r.studentno =s.studentno
inner join `subject` sub
on r.subjectno =sub.subjectno
-- 格式:select 字段 from 表名 xxx join 连接的表 on 交叉条件
-- 思路:假设存在一种多表查询,先查询两张表然后再慢慢增加
一些连表查询案例:
-- 查询学员及所属的年级(学号,学生姓名,年级名)
select studentno,studentname,gradename
from student s
inner join `grade` g
on s.`gradeid` =g.`gradeid`
-- 查询科目所属的年级 (科目名称,年级名称)
select subjectname,gradename
from `subject` sub
inner join grade g
on sub.gradeid =g.gradeid
-- 查询了参加 "高等数学-2" 考试同学的信息:学号,学生姓名,科目名,分数
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'
自连接(了解)
自己的表和自己的表连接,核心:一张表拆为两张一样的表即可
/*
自连接
数据表与自身进行连接
需求:从一个包含栏目ID , 栏目名称和父栏目ID的表中
查询父栏目名称和其他子栏目名称
*/
-- 创建一个表
CREATE TABLE `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','办公信息');
-- 编写SQL语句,将栏目的父子关系呈现出来 (父栏目名称,子栏目名称)
-- 核心思想:把一张表看成两张一模一样的表,然后将这两张表连接查询(自连接)
SELECT a.categoryName AS '父栏目',b.categoryName AS '子栏目'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`
3.5、分页和排序
limit 分页
-- 分页 limit 页码,单页面的大小
-- 第N页 : limit (pageNo-1)*pageSzie,pageSzie
-- [pageNo:页码,pageSize:单页面显示条数]
-- 总页数=数据总数/页面大小
select * from student limit 1,4;
order by 排序
-- 排序:升序 ASC,降序 DESC 与order搭配使用
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'
order by studentresult desc
-- 4表联查
-- 查询大一学习了《Java程序设计-1》课程成绩前2名并且分数大于80的学生信息(学号,姓名,课程名,分数)
select s.studentno,studentname,subjectname,studentresult
from student s
inner join result r
on r.studentno=s.studentno
inner join `subject` sub
on sub.subjectno=r.subjectno
inner join grade g
on g.gradeid =sub.gradeid
where gradename='大一' and subjectname='Java程序设计-1' and studentresult>80
order by studentresult desc
limit 0,2
3.6、子查询
where(这个值是计算出来的)
本质:在where语句中嵌套一个子查询语句
语法:where(select* from 表)
3.7、分组和过滤
-- 查询不同课程的平均分,最高分,平均分,最高分,最低分,平均分>80
-- 核心:(根据不同的课程分组)
select subjectname,AVG(studentresult),MAX(studentresult),MIX(studentresult)
from result r
inner join `subject` sub
on r.subjectno=sub.subjectno
group by r.subejectno -- 通过字段分组,假如内容相同,则有去重功能
having AVG(studentresult)>80