4.MySQL的DQL查询数据

4.1DQL

（Data Query LANGUAGE）：数据查询语言

1. 所以的查询操作都用它， Select

2. 简单和复杂的查询它都能做!5](5.png)

3. 数据库中最核心的语言，最重要的语言

4. 使用频率最高的语句

select完整语法

SELECT[ALL|DISTINCT]
{*|table.* |}[table.field1[as alias1][,table.fiekd2[as alias2]][....]]}
FROM table_name[as table_alias]
[left |right |inner join table_name2] --联合查询
[WHERE...]  -- 指定结果需满足的条件
[GROUP BY..] -- 指定结果按照那几个字段来分组
[HAVING] -- 过滤分组的记录必须满足的次要条件
[ORDER BY ...] -- 指定查询记录按下一个或多个条件排序
[LIMIT{[offset,]row_COUNT|row_countOFFSET offset}];
-- 指定查询的记录从哪条到那条

4.2指定查询字段

使用SQLyog不想输入字段时候，双击列表左边字段名，就可以直接复制到命令列表中。

 

 

-- 查询全部的一个表的全部信息。 SELECT 字段 FROM 表名
SELECT *FROM  student
SELECT *FROM result
-- 查询指定字段
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查询出来的结果中重复的数据，重复的数据只显示一条

-- 查询一下有那些同学参加了考试
SELECT *FROM result  -- 查询全部的考试成绩   有一个result的表在里面查询
-- 查询有哪些同学参加了考试
SELECT `studentno` FROM result
-- 发现重复数字， 去重
SELECT DISTINCT `studentno` FROM result

数据库的列

 AS后跟列名

-- 查询系统版本
SELECT VERSION()
SELECT 100*3-9 AS 计算结果  -- 用来计算
SELECT @@auto_increment_increment  -- 查询自增的步长（变量）

-- 学员考试成绩加一分查看
SELECT  `studentno`,`studentresult`+1 AS '提分后' FROM result

数据库中的表达式：文字值，列，Null，函数，计算表达式，系统变量...

表达式：select 表达式 from 表.

4.3 where条件子句

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

搜索的条件由一个或多个表达式组成！结果为布尔值

逻辑运算符

与 或 非 & & || !

-- ========================where==========================
SELECT `studentno`,`StudentResult` FROM result
-- 查询95~100之间
SELECT `studentno`,`StudentResult` FROM result
WHERE  StudentResult>=95 AND StudentResult<= 100

-- and &&
SELECT `studentno`,`StudentResult` FROM result
WHERE  StudentResult>=95 && StudentResult<= 100

-- 模糊查询(区间） 
SELECT `studentno`,`StudentResult` FROM result
WHERE  StudentResult BETWEEN 95 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结果为真
IS NOT NULL	a is not null	如果操作符为不为null ，结果为真
BETWEEN	a between b and c	若在a和b之间，结果为真
LIKE	a like b	SQL匹配，如果a匹配b，结果为真
IN	a in（a1,a2,a3...)	假设a在a1，或者a2...其中某一个，为真

-- =======================模糊查询=======================
-- 查询姓张的同学
-- 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号学员
SELECT `studentno`,`studentname` FROM `student`
WHERE studentno IN (1000,1001);


-- =================null   ,not null=========
-- ===============查询地址为空的学生=========
SELECT `studentno`,`studentname` FROM `student`
WHERE  address ='' OR address  IS NULL

-- =============查询有出生日期的同学 （不为空）======
SELECT `studentno`,`studentname` FROM `student`
WHERE borndate IS NOT NULL

-- =============查询没有出生日期的同学 为空======
SELECT `studentno`,`studentname` FROM `student`
WHERE borndate IS  NULL

4.4联表查询

JOIN对比

 

 

 

-- ===================联表查询 join========================
 -- 查询了参加考试的同学（学号，姓名，科目编号，分数）
 SELECT * FROM student 
 SELECT * FROM result 
  -- ==join +（连接的表） on +（判断的条件）  ：连接查询
 -- where ：等值查询
 /*思路
 1.分析需求，分析查询的字段来自那些表，（连接查询）
 2，确定使用那种连接查询？七种
 确定交叉点（这两个表中那些数据是相同的）
 判断的条件：学生表中的studentno=成绩表studentno
 
 */
 -- ======inner join=============
 SELECT s.studentno,studentname,subjectno,studentresult
 FROM student AS s          -- 连接是两个表都有studentno，可以给两个表用as取别名，然后前面加上别名.来区分
INNER JOIN result  AS r   -- 图上有inner join 区域
ON  s.studentno = r.studentno   --  where 代替on也行
 
-- =============right join=============
 SELECT s.studentno,studentname,subjectno,studentresult
 FROM student  s
 RIGHT JOIN result  r
ON s.studentno = r.studentno

-- =============left join=============
 SELECT s.studentno,studentname,subjectno,studentresult
 FROM student  s
 LEFT JOIN result  r
ON s.studentno = r.studentno

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

-- 查询缺考的同学
 SELECT s.studentno,studentname,subjectno,studentresult
 FROM student  s
 LEFT JOIN result  r
ON s.studentno = r.studentno
WHERE studentresult IS NULL

-- 思考题（查询了参加考试的同学信息：学号，学生姓名，科目名，分数
 SELECT s.studentno,studentname,subjectname,studentresult
 FROM student  s
 RIGHT JOIN result  r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno

-- 我要查询那些数据 select...
-- 从那几个表中查 from 表xx join 连接的表 on 交叉条件
-- 假设存在一个多张表查询，慢慢来，先查询两张表，在慢慢加


-- from a left join b
-- form a right join b

 

自连接

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

父类

categoryid	categoryname
2	信息技术
3	软件开发
5	美术设计

子类

pid	categroyid	categroyname
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	美术设计

操作：查询父类对应子类的关系

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

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','办公信息');
-- 查询父子信息，把一张表看为两个一模一样的表。 
SELECT a.`categoryname` AS '父栏目',b.`categoryname` AS '子栏目'   -- 把一张表看成两张一模一样的表
FROM category AS a, category AS b
WHERE a.categoryid=b.pid

 

-- 查询学员所属年纪(学号，学生的姓名，年纪姓名）
SELECT studentno,studentname,gradename
FROM student s
INNER JOIN grade g
ON s.gradeid = g.gradeid

-- 查询科目所属的年纪（科目名称，年纪名称）
SELECT subjectname,gradename
FROM `subject` a
INNER JOIN grade b
ON a.gradeid = b.gradeid

-- 查询了参加 高等数学-1 考试同学的信息：学号，学生姓名，科目名，分数
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '高等数学-1'

4.5分页和排序

排序

--    ==========================分页limit和排序 order by============

-- 排序 升序ASC  降序DESC
-- order by 通过那个字段排序，怎么排
-- 查询的结果根据成绩 降序 排序
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '高等数学-1'
ORDER BY studentresult ASC      --   desc降序，写完这一行不要只运行这一行，和上面的一块运行

分页

-- 100w条数据
-- 为什么要分页
-- 缓解数据库压力，给人体验会更好，。 现在有瀑布流，比如查看图片拉不到底

-- 分页，每页只显示五条数据
-- 语法 limit 起始值，页面的大小
-- 在网页上：当前,总的页数,页面的大小
-- LIMIT 0,5   1~5 1到五条数据
-- LIMIT 1,5    2~6
-- LIMIT 6,5   
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '高等数学-1'
ORDER BY studentresult ASC      --   desc降序，写完这一行不要只运行这一行，和上面的一块运行
LIMIT 1,1      -- 我的推测是第一个是第一行的数据开始，然后后面1是页面大小= 数据总数/页数
-- 第一页 limit 0,5    （1-1）*5
-- 第二页 limit 5,5     （2-1）*5
-- 第三页 limit 10,5      （3-1）*5
-- 第N页 limit       （n-1）*pagesize，pagesize
-- 【pagesize：页面大小   （n-1）*pagesize：起始值,n ：当前页】
-- 【数据总数/页面大小=总页数】

语法：limit（查询起始下标，pagesize）

-- 思考 
-- 查询java第一学年课程成绩排名前十的学生，并且分数要大于80分的学生信息（学号，姓名，课程名称，分数）
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 = 'java第一学年' AND studentresult>=80
ORDER BY studentresult DESC
LIMIT 0,10

4.6子查询

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

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

where（select*from）

--  ====================== where==============
--  1.查询数据库结构-1的所有考试结果（学号，科目编号，成绩），降序排列
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE subjectname = '数据库结构-1'
ORDER BY studentresult DESC

-- 方式二：使用子查询(由里到外）
-- 查询所有数据库结构-1学生的学号
SELECT `studentno`,`subjectno`,`studentresult`
FROM result 
WHERE studentno = (
SELECT subjectno FROM `subject` 
WHERE subjectname = '数据库结构-1'
)ORDER BY studentresult DESC

-- 分数不小于80分的学生的学号和姓名
SELECT DISTINCT s.`studentno` ,studentname
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
WHERE studentresult >=80
-- 在这个基础上增加一个科目，高等数学-2
-- 查询高等数学-2的编号
SELECT DISTINCT s.studentno,studetname
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
WHERE studentresult >=80 AND subjectno =(
SELECT subjectno FROM `subject`
WHERE subjectname = '高等数学-2'


SELECT DISTINCT s.`studentno` ,studentname
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
WHERE studentresult >=80
) -- 再改造（由里及外）
SELECT studentno,studetname FROM student WHERE studentno IN (
SELECT studentno FROM result WHERE studentresult>80 AND sujectno = (
SELECT subjectno FROM `subject` WHERE subjectname ='高等数学-2'
)
)

-- 查询课程为高等数学-2，且分数不小于80分同学学号姓名
SELECT s.studentno,studetname
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE subjectname = '高等数学-2' AND studentresult>=80