MySQL学习笔记（四）

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

4.1 DQL语言

数据查询语言

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

4.2指定查询字段

--  查询全部学生 select 字段 from 表
select * from  student

--  查询指定字段 
select `id`,`name` from student

--  别名，给结果起一个名字 as 可以给字段，也可以给表取别名
select `id` as 学号,`name` as 学生姓名 from student as student01

--  函数 concat(a,b)   字符串拼接
select concat('姓名：',name) as 新名字 from student

语法：select 字段… from 表

有的时候，列名字不是那么见名知意，我们可以起别名

字段名 as 别名 表名 as 别名

4.3 去重 distinct

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

-- 查询一下有哪些同学参加了考试，成绩
select * from result -- 查询全部的考试成绩
select `studentid` from result
-- 发现重复数据，去重
select distinct `studentid` from result

数据库的列（表达式）

select version() -- 查询系统版本
select 100*3-1 as 计算结果 -- 计算
select @@auto_increment -- 查询自增步长
select `id`,`result`+1 as `加分后` from result
-- 学员考试成绩全员+1分查看

数据库中的表达式：文本值，列，null ，函数，计算表达式，系统变量…

select 表达式 from 表

4.4 where条件子句

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

逻辑运算符

and && or || not !

尽量在语句中使用英文字母

select `id`,`result` from result
-- 查询考试成绩在95-100分之间
select `id`,`result` from result
where result>=95 and result <=100

-- 模糊查询（区间范围）
select `id`,`result` from result
where result between 95 and 100

-- 除了1000号学生之外的成绩
select `id`,`result` from result
where id！=1000 -- where not id =1000 二者一样

模糊查询：本质是比较运算符

运算符	语法	描述
is null	a is null	如果操作符为null ，则结果为真
is not null	a is not null	如果操作符为null，则结果为假
between	a between b and c	如果a在b和c之间，则结果为真
like	a like b	SQL 匹配 ，如果a匹配b，则结果为真
a	a in（a 1，a 2，a 3…）	假设a在a1 a2 a3 其中的某个值中，则结果为真

-- 模糊查询
--查询姓刘的同学
-- like结合 %（代表0到任意个字符） _（一个字符）
select `id`,`name` from student
where name like '刘%'

select `id`,`name` from student
where name like '刘_'

-- 查询名字中带有嘉字的名字
select ``id`,`name` from student
where name like '%嘉%'

-- 查询1001 1002 1003号学员
select ``id`,`name` from student
where `id` in(1001,1002,1003);

--查询在北京的学生
select ``id`,`name` from student
where `address` in ('安徽','北京')

---------- null      not null---------

-- 查询地址为空的学生 null
select ``id`,`name` from student
where address='' or address is null

--查询有出生日期的学生 not null
select ``id`,`name` from student
where `birthday` is not null

4.5联表查询

join对比

----联表查询------

--查询参加考试的学生
select * from student
select * from result
/*思路
分析需求，分析查询的字段来自哪些表（连接查询）
确定使用哪种连接查询？七种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件：学生表的中 id 等于成绩表 id
*/
select s.id,name,result
from student as s
inner join result as r
where s.id=r.id

--right join
select s.id,name,result
from student as s
right join result  as r
on s.id=r.id

--left join
select s.id,name,result
from student as s
left join result as  r
on s.id=r.id

操作	描述
inner join	如果表中至少有一个匹配，就返回行
left join	会从左表中返回所有的值，即使右表中没有匹配
light join	会从右表中返回所有的值，即使左表中没有匹配

-- 查询缺考的同学
select s.id,name, result
from student as s
left join result as  r
on s.id=r.id
where result is null

--查询参加考试的同学的信息（学号 姓名 科目 结果）
-- 分析 ：student表 subject表 result表
select id, name ,subjectno ,result
from student s
right join result r
on r.id=s.id
inner join subject sub
on r.subjectno=sub.subjectno

-- 我要查询哪些数据 select......
-- 从哪几个表中查 from 表 xxx join 链接的表 on 交叉条件
--假设存在一种多张表查询，慢慢来，先查询两张表然后慢慢增加

-- from a left join b 以左边为基准
-- from a left join b 以右边为基准

自连接（了解）

自己的表和自己的表连接，核心：一张表拆为两张一样的表即可

父类

categoryid	categoryname
2	信息技术
3	软件开发
5	数据库

子类

pid	categoryid	categoryname
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

4.6分页和排序

-- 排序：升序asc，降序desc
-- order by 通过哪个字段排序，怎么排序
-- 查询的结果根据成绩排序
select id,name,subname,result
from student as s
inner join result r
on s.id=r.id
inner join subject sub
on sub.subjectid=r.subjectid
where subjectname='数据结构'
order by studentresult desc/asc

分页

-- 如果有一百万数据，不可能一次性显示在网页上
-- 缓解数据库压力，给人的体验更好
--分页 每页只显示五条数据
-- 语法 limit 起始页 ，页面大小
第一页：limit 0,5
第二页：limit 5,5
第三页：limit 10,5
第n页：limit (n-1)*5,5 --  (n-1)*pagesize,pagesize
-- pagesize 页面大小  
-- (n-1)*pagesize 起始值
-- n：当前页
-- 数据总数/页面大小=总页数

语法：limit (查询起始下标，pagesize)

-- 思考
-- 查询Java第一学年 课程成绩排名前十的学生，并且分数要大于80的学生信息
select id,studentname,subjectname,result
from student as s
inner join subject as sub
on s.id=sub.id
inner join result as res
on res.subjectno=sub.subjectno
where subjectname='java第一学年' and result>80
order by result desc
limit 0,10

4.7 子查询

where （这个值是计算出来的）

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

-- 1.查询数据库结构的所有考试结果，通过降序排列
-- 方式一：使用连接查询
select id,subjectno,result
from result as r
inner join subject sub
on r.subjectno=sub.subjectno
where name='数据库结构'
order by result desc

-- 方式二：使用子查询（）
select id,subjectno,result
from result
where id =(
    select subjectno from subject where name='数据库结构'
)
order by result desc
-- 查询所有数据库结构的学生的学号
select subjectno from subject where name='数据库结构'


-- 分数不小于80分的学生的学号和姓名  distinct 去重
select distinct s.id,name
from student as s
inner join result as r
on r.id=s.id
where result>=80




-- 在这个基础上加一个科目 高等数学
--这是使用嵌套子查询
select distinct s.id,name
from student as s
inner join result as r
on r.id=s.id
where result>=80 and subjectno=(select subjectno
from subject 
where subjectname='高等数学'
)
-- 查找出高等数学的编号
select subjectno
from subject 
where subjectname='高等数学'

-- 改造（由里及外）
select id,name from student where id in (select id from result where result>=80 and subjectno=( select subjectno from subject where subjectname='高等数学'                    )           
)



-- 查询课程为高等数学 且分数不小于八十的学生的学号与姓名
-- 这是使用连表查询
select id,name
from student as s
inner join result as r
on s.id=r.id
inner join subject sub
on r.subjectno=sub.subjectno
where `subjectname`='高等数学' and result>=80