MySQL-04（最重点）

最新推荐文章于 2024-07-09 11:09:43 发布

Mr.Rop

最新推荐文章于 2024-07-09 11:09:43 发布

阅读量87

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/qq_46567633/article/details/115149198

版权

MySQL 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

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

4.1、DQL

(Data Query LANGUAGE：数据查询语言)

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

创表的sql语句：

-- 创建一个school数据库
CREATE DATABASE IF NOT EXISTS `school`;
USE `school`;


-- 创建年级表
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;

-- 插入年级数据
INSERT INTO `grade` (`GradeId`,`GradeName`) VALUES(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');

-- 创建成绩表
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;

-- 插入成绩数据  这里仅插入了一组，其余自行添加
INSERT INTO `result`(`StudentNo`,`SubjectNo`,`ExamDate`,`StudentResult`)
VALUES
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);

-- 创建学生表
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;

-- 插入学生数据 其余自行添加 这里只添加了2行
INSERT INTO `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233');

-- 创建科目表
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;

-- 插入科目数据
INSERT INTO `subject`(`SubjectNo`,`SubjectName`,`ClassHour`,`GradeId`)VALUES
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);

4.2、指定查询字段

-- 查询全部的学生     SELECT 字段 FROM 表
SELECT * FROM student

-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM student

-- 别名，给结果起一个名字    AS  可以给字段起别名，也可以给表起别名
SELECT `StudentNo` AS 学号, `StudentName` AS 学生姓名 FROM student AS s

-- 函数 Concat(a,b)
SELECT CONCAT('姓名：',StudentName) AS 新名字 FROM student

语法：SELECT 字段… FROM 表

有的时候，列名不是那么的见名知意，起别名 AS 字段名 AS 别名表名 AS 别名

SELECT语法

SELECT [ALL/DISTINCT]
{* | TABLE.* | [TABLE.FIELD][AS ALIAS][,TABLE.FIELD2][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)];
	-- 指定查询的记录从哪条至哪条

去重 distinct

作用：去除SELECT查询出来的重复的数据，重复的数据只显示一条

-- 查询一下有哪些同学参加了考试，成绩
SELECT * FROM result -- 查询全部的考试成绩

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

-- 发现重复数据，去重
SELECT DISTINCT `StudentNo` FROM result

数据库的列（表达式）

-- 查询系统版本（函数）
SELECT VERSION()

-- 用来计算（表达式）
SELECT 100*3-1 AS 计算结果

-- 查询自增的步长（变量）
SELECT @@auto_increment_increment

-- 学员考试成绩全部加1分
SELECT `StudentNo`,`StudentResult` +1 AS '提分后' FROM result

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

select 表达式 from 表

4.3、where条件子句

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

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

逻辑运算符

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

尽量使用英文字母

-- ================= 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和c之间，则结果为真
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，1002，1003学员
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo IN (1000,1001,1002)

-- 查询在南昌的学生
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `Address` IN ('江西南昌')

-- =============NULL    NOT NULL==================
-- 查询地址为空的学生
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE Address='' OR Address IS NULL

-- 查询有出生日期的同学
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NOT NULL

4.4、联表查询

JOIN 对比

-- ========= 连表查询 join =============
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM student
SELECT * FROM result


/*思路
1、分析需求，分析查询的字段来自哪些表，（连接查询）
2、确定使用哪种连接查询？ 7种
确定交叉点（这两个表中哪个数据是相同的）
判断条件：学生表中 StudentNo = 成绩表 StudentNo
*/

-- join （连接的表） on （判断的条件） 连接查询
-- where 等值查询

SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student AS s
INNER JOIN result AS r
WHERE s.StudentNo=r.StudentNo

-- 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

-- 查询缺考的同学
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 r.`StudentNo`=s.`StudentNo`
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`

-- 我要查询哪些数据 select ...
-- 从哪几个表中查 FROM 表 XXX JOIN 连接的表 ON 交叉条件
-- 假设存在一种多张表查询，慢慢来，先查询两张表然后再慢慢增加

-- FROM a LEFT JOIN b
-- FROM a RIGHT JOIN b

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

自连接（了解即可）

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

父类

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

子类

pid	categoryid	categoryName
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	ps技术

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

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

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` sub
INNER JOIN `grade` g
ON sub.`GradeId` = g.`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、分页和排序

排序 ASC 升序 DESC 降序

-- 排序： 升序 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

分页

-- 为什么要分页？
-- 缓解数据库压力，给人的体验更好，瀑布流

-- 分页，每页只显示五条数据
-- 语法：limit 起始值,页面大小
-- 网页应用：当前，总的页数，页面大小
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
LIMIT 0,1

语法：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第一学年'
ORDER BY StudentResult DESC
LIMIT 0,10

4.6、子查询

where (这个值是计算出来的)

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

-- ============== where ===============
-- 1.查询 JAVA第一学年 的所有考试结果（学号，科目编号，成绩），降序排列
SELECT `StudentNo`,r.`SubjectNo`,`StudentResult`
FROM `result` r
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE SubjectName = 'JAVA第一学年'
ORDER BY StudentResult DESC

-- 2.使用子查询（由里及外）
SELECT `StudentNo`,`SubjectNo`,`StudentResult`
FROM `result`
/*单个学生就要加这个any，不然会报错，多个学生不用加*/
WHERE SubjectNo = ANY(
	SELECT SubjectNo FROM `subject` 
	WHERE SubjectName = 'JAVA第一学年'
)
ORDER BY StudentResult DESC


-- 查询课程为 高等数学-2 且 分数不小于80 的同学的学号和姓名
SELECT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON s.`StudentNo`=r.`StudentNo`
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE SubjectName='高等数学-2'


-- 分数不小于80分的学生的学号和姓名
-- 在这个基础上增加一个科目，高等数学-2
SELECT s.`StudentNo`,`StudentName`
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 `StudentNo`,`StudentName` FROM student WHERE StudentNo IN (
	SELECT StudentNo FROM result WHERE StudentResult>80 AND SubjectNo=(
		SELECT SubjectNo FROM `subject` WHERE `SubjectName`='高等数学-2'
	)
)

4.7、分组和过滤

-- 查询不同课程的平均分，最高分，最低分，平均分大于80分
-- 核心：（根据不同的课程分组）
SELECT `SubjectName`,AVG(StudentResult) AS 平均分,
	             MAX(StudentResult) AS 最高分,
	             MIN(StudentResult) AS 最低分
FROM result r
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
GROUP BY r.`SubjectNo` -- 通过什么字段来分组
HAVING AVG(StudentResult)>80

Mr.Rop

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL-04（最重点）

4、DQL查询数据（最重点）4.1、DQL(Data Query LANGUAGE：数据查询语言)所有的查询操作都用它 Select简单的查询，复杂的查询它都能做数据库中最核心的语言，最重要的语句使用频率最高的语句创表的sql语句：-- 创建一个school数据库CREATE DATABASE IF NOT EXISTS `school`;USE `school`;-- 创建年级表DROP TABLE IF EXISTS `grade`;CREATE TABLE `gra
复制链接

扫一扫