MySQL如何select呢...

DQL 查询数据

前言

爆肝学习了数据查询之后，为了理一理思路，在学习的时候做下了如下的笔记，供学习参考和交流！ 这一章是MySQL的重点之一，查询数据在数据库非常重要，学好这一章是数据库的关键！

1、DQL

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

  • 所有查询操作都用它 Select
  • 简单的查询、复杂的查询它都能做
  • 数据库最核心、重要的语句
  • 使用频率最高的语句

2、指定查询字段

在开始之前，为了更加方便了解查询，我们先创建几个表.接下来的演示内容都是和创建的表有关！

CREATE DATABASE IF NOT EXISTS `school`;
-- 创建一个school数据库
USE `school`;-- 创建学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
	`studentno` INT(4) NOT NULL COMMENT '学号',
    `loginpwd` VARCHAR(20) DEFAULT NULL,
    `studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
    `sex` TINYINT(1) DEFAULT NULL COMMENT '性别，0或1',
    `gradeid` INT(11) DEFAULT NULL COMMENT '年级编号',
    `phone` VARCHAR(50) NOT NULL COMMENT '联系电话，允许为空',
    `address` VARCHAR(255) NOT NULL COMMENT '地址，允许为空',
    `borndate` DATETIME DEFAULT NULL COMMENT '出生时间',
    `email` VARCHAR (50) NOT NULL COMMENT '邮箱账号允许为空',
    `identitycard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
    PRIMARY KEY (`studentno`),
    UNIQUE KEY `identitycard`(`identitycard`),
    KEY `email` (`email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;

-- 创建年级表
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
	`gradeid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
  `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
    PRIMARY KEY (`gradeid`)
) ENGINE=INNODB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;

-- 创建科目表
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
	`subjectno`INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
    `subjectname` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
    `classhour` INT(4) DEFAULT NULL COMMENT '学时',
    `gradeid` INT(4) DEFAULT NULL COMMENT '年级编号',
    PRIMARY KEY (`subjectno`)
)ENGINE = INNODB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8;

-- 创建成绩表
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(
	`studentno` INT(4) NOT NULL COMMENT '学号',
    `subjectno` INT(4) NOT NULL COMMENT '课程编号',
    `examdate` DATETIME NOT NULL COMMENT '考试日期',
    `studentresult` INT (4) NOT NULL COMMENT '考试成绩',
    KEY `subjectno` (`subjectno`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

select的使用：

-- 语法 select 字段 from 表

-- 查询全部的学生
select * from student

-- 查询指定字段
select `StudentNo`,`StudentName` from student

-- 别名,给结果起一个名字 用as 可以给字段起别名,也可以给表起别名
select `StudentNo` as 学号,`StudentName` as 学生姓名 from student

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

语法：select 字段 .... from 表

有时候,列名字不是那么见名知意.我们起别名 字段名 as 别名 表名 as 别名

去重 distinct

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

--查询一下有哪些同学参加了考试(查询成绩)
select * from result --查询全部的考试成绩

select `StudentNo` from result --查询有哪些同学参加了考试

select distinct `StudentNo` from result--发现重复数据\去重

数据库的列(表达式)

--查询系统版本
select version()     --函数

--查询计算结果
select 100*10-1 as 计算结果    --计算表达式

--查询自增步长
select @@auto_increment_increment    --变量

--查询加分后成绩
SELECT `Studentno`,`Studentresult`+1 AS '加分后的成绩' FROM result

数据库中的表达式:文本值,列,null,函数,计算表达式,系统变量...

select 表达式 from 表

3、 where条件子句

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

搜索的条件由一个或者多个表达式组成 ， 一般返回结果都是 boolen值

逻辑运算符

运算符语法描述
and 或 &&a and b a&&b与，两个都为真，结果为真
or 或 ||a or b a||b或，其中一个为真，结果为真
not 或 !not a !a非，真为假，假为真

尽量使用英文字母

--查询成绩大于等于90的数据
select `studentno`,`studentresult` from result where  studentresult>=90

--查询成绩在90和100之间的数据
select `studentno`,`studentresult` from result where  studentresult>=90 && studentresult<=100

select `studentno`,`studentresult` from result where  studentresult between 90 and 100

--产线成绩不为60的数据
select `studentno`,`studentresult` from result where studentresult!=60

select `studentno`,`studentresult` from result where not studentresult=60

模糊查询 ： 比较运算符

运算符语法描述
is nulla is null如果操作符为null，则结果为真
is not nulla is not null如果操作符为 not null，则结果为真
between anda between b and c如果a在b和c之间，则为真
likea like bSQL匹配，如果a匹配到b，则结果为真
ina in (a1,a2,a3...)假设a在a1/a2/a3...中某一个值，则结果为真

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运算符:具体的一个或多个值

--查询10086,10087,10088号学生
select `studentno`,`studentname` from `student` where studentno in (10086,10087,10088)

is null / not null

--查询地址为空的学生  (null或'')
select `studentno`,`studentname` from `student` where address = '' or address is null

--查询有出生日期的学生(不为空)
select `studentno`,`studentname` from `student` where borndate is not null

4、联表查询

join 对比

在这里插入图片描述

为了更深入了解，我们进行如下操作：

--查询参加了考试的同学(学号,姓名,科目编号,分数)
/*
思路
1、分析需求，分析查询字段来自哪里？  （连接查询）
2、确定使用哪种连接查询？      （一共7种）
确定交叉点（两个表哪个数据是相同的）
判断的条件：总学生表的 studentno = 成绩表中的studentno
*/

--join(连接的表) on(判断的条件) 是连接查询
-- where 是等值查询

select s.studentno,studentname,subjectno,studentresult
from student as s
inner join result as r
on s.studentno = r.studentno

-- right join  (result为右表)
select s.studentno,studentname,subjectno,studentresult
from student as s
right join result as r
on s.studentno = r.studentno

-- left join  (student为左表)
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

--查询科目成绩(三表连接 student result subject)
SELECT s.studentno AS '学生学号' ,`studentname` AS '学生姓名' ,`subjectname` AS '学科名称',`studentresult` AS '学生成绩'
FROM student s
RIGHT JOIN result r
ON s.studentno = r.studentno
LEFT JOIN SUBJECT sub
ON sub.subjectno = r.subjectno

要领： 1、我要查询哪些数据 select ... 2、从哪及格表中查 from 表 xxx join 连接的表 on 交叉条件 3、假设存在一种多张表查询，先查询两张表，再一张一张加。

自连接

自己的表和自己的表连接

核心：一张表拆为两张一样的表即可

举例 在这里插入图片描述

父类

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

子类

pidcategoryidcategoryName
34数据库
28办公信息
36web开发
57ps技术

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

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

联表查询练习:

--查询父子信息:把一张表分为两张一模一样的表

select `categoryName` as '父栏目',`categoryName` as '子栏目' from `category` as a , `category` as b
where a.`categoriyid` = b.`pid`

--查询学员所属的年纪(学号,学生姓名,年级名称)
select studentno,studentname,gradename
from student as s
inner join `grade` as g
on s.`gradeid` = g.`gradeid`


--查询科目所属的年级(科目名称,年级名称)
select `subjectname`,`gradename`
from `subject` as sub
inner join `grade` as g
on sub.`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 subjectname = '数据库结构-1'

5、分页和排序

排序 order by

-- 排序分为俩种:升序 ASC 降序 DESC

-- 引用上方的一个查询'数据库结构-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 subjectname = '数据库结构-1'
order by studentresult desc

升序 asc 降序 desc

分页 limit

--分页,每页只显示5条数据
--语法,limit 起始值 , 页面的大小
/*
第n页 limit (n-1)*pagesize,pagesize
n代表当前页,pagesize表示页面容量
*/
select s.udentno,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 subjectname = '数据库结构-1'
order by studentresult desc
limit 0,5

6、分组和过滤

分组 group by

过滤 having （不同于where，having是次要过滤）

举个例子

--查询不同课程的平均分,最高分,最低分,平均分大于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 r.subjectno	--通过(学科编号)来分组
having 平均分>80	--次要条件过滤用having

7、子查询和嵌套查询

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

（我个人把它称为 表中查表 ， 由里及外的思想）

联表查询和子查询的使用对比：

inner join 更好理解

子查询、嵌套查询 代码量少

--1、查询 数据库结构-1 的所有考试结果（学号，科目编号，成绩），降序

--方式一 联表查询
select studentno,r.subjectno,studentresult
from result as r
inner join subject as sub
on r.subjectno=sub.subjectno
where subjectname = '数据库结构-1'
order by studentresult desc

--方式二 子查询
select studentno,subjectno,studentresult
from result
where subjectno = (
	select subjectno
    from subject
    where subjectname = '数据库结构-1'
)
order by studentresult desc

--2、查询 高等数学-2 分数不小于80分的学生的编号和姓名

--方法一：联表查询
select distinct s.studentno,studentname
from student as s
inner join result as r
on s.studentno = r.studentno
inner join `subject` as sub
on sub.subjectno = r.subjectno
where subjectname = '高等数学-2'
and studentresult >= 80

--方法二：子查询（嵌套查询）
/*
where 什么 
就
select 什么
*/
select studentno,studentname
from student
where studentno in(
	select studentno
    from result
    where studentresult>=80
    and subjectno=(
    	select subjectno
        from `subject`
        where subjectname = '高等数学-2'
    )
)


-- 思考:
-- 查询 JAVA第一学年 课程成绩排名前十的学生,并且分数要大于80的学生信息(学号,姓名,分数)

--方法一 联表查询
select s.studentno,studentname,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 subjectname = 'JAVA第一学年'
and studentresult>=80
order by studentresult desc
limit 0,10

--方法二 子查询
select s.studentno,studentname,studentresult
from student as s
inner join result as r
where s.studentno = r.studentno
and studentresult>=80
and subjectno = (
	select subjectno
    from `subject`
    where subjectname = 'JAVA第一学年'
)
order by studentresult desc
limit 0,10

8、select小结

select完整的语法：

select [all | distinct]
{*	|	table.*	|	[table.field1[as alias1][,table.field2[as alias2]][,.[.]]}	
from table_name [as table_alias]
	[left |	right |inner/left/right join table_name]	--联合查询
	[where...]			--指定结果满足的条件
	[group by ...]		--指定结果按照哪几个字段来分组 
    [having ...]		--过滤分组的记录必须满足的条件
    [order by ...]		--指定查询记录按一个或多个条件排序
    [limit x,y]			--知道那个查询的记录从x开始至y

注意：[]括号代表的是可选的，{}括号代表的是必选的

后话

MySQL的学习仍在继续，如果有任何错误或者问题可以在评论区共同交流，与君共勉！