DQL MySQL查询数据（最重点）

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

---