栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

MySQL-DQL(查询语言)

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

MySQL-DQL(查询语言)

文章目录
  • 五、DQL(查询语言)`重点`
    • 5.1 准备
    • 5.2 指定查询字段
    • 5.3 去重 distinct
    • 5.4 数据库的列(表达式)
    • 5.5 where条件语句
      • 5.5.1 逻辑运算符
      • 5.5.2 练习
    • 5.6 模糊查询:比较运算符
      • 5.6.1 练习
    • 5.7 联表查询(掌握)
      • 5.7.1 join on 连接查询
      • 5.7.2 join on where
      • 5.7.3 自联结查询(了解)
    • 5.8 分页和排序
    • 5.9 子查询和嵌套查询
    • 5.10 分组和过滤


学习视频来自于:秦疆(遇见狂神说)Bilibili地址
他的自学网站:kuangstudy

我们缺乏的不是知识,而是学而不厌的态度


五、DQL(查询语言)重点

重点掌握,MySQL主要就是学习这个。
(Data Query Language:数据库查询语言)。

5.1 准备

查询语句结构

SELECt
	[ ALL | DISTINCT | DISTINCTROW ] -- 去重
		[ HIGH_PRIORITY ]
		[ STRAIGHT_JOIN ]
		[ SQL_SMALL_RESULT ][ SQL_BIG_RESULT ][ SQL_BUFFER_RESULT ]
		[ SQL_CACHE | SQL_NO_CACHE ][ SQL_CALC_FOUND_ROWS ]
	select_expr [, select_expr...]
	[ FROM table_references
		[ PARTITION partition_list ]
	[ WHERe	where_condition ] -- 指定结果需要满足的条件
	[ GROUP BY { col_name | expr | position } -- 指定结果按照那几个字段来分组
		[ ASC | DESC ] ,...[ WITH ROLLUP ]]
	[ HAVINg where_condition ] -- 过滤分组的记录必须满足的次要条件
	[ ORDER BY { col_name | expr | position } -- 指定查询记录一个或者多个条件排序
		[ ASC | DESC ],...]
	[ LIMIT {[ OFFSET ,] row_count | row_count OFFSET offset }] -- 指定查询的记录从那条至那跳
	[ PROCEDURE procedure_name (argument_list) ]
	[ INTO OUTFILe 'file_name' 
			[ CHARACTER SET charset_name ]
			export_options
		| INTO DUMPFILe 'file_name'
		| INTO var_name [, var_name]]
	[ FOR UPDATe | LOCK IN SHARE MODE]]

表结构

CREATE TABLE `grade` (
  `gradeid` int(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
  `gradename` varchar(50) NOT NULL COMMENT '年级名称',
  PRIMARY KEY (`gradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

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

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=InnoDB DEFAULT CHARSET=utf8

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 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');

-- 插入成绩数据  这里仅插入了一组,其余自行添加
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);

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

-- 插入科目数据
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);
5.2 指定查询字段
-- 查询全部的学生 格式:SELECT * FROM 表
SELECt * FROM `student`;
-- 查询指定字段
SELECt `name` AS '姓名',`sex` AS '性别' FROM `student`;
-- 别名,给结果起一个名字 AS 可以给字段起别名,也可以给表起别名
SELECt `name` AS '姓名',`sex` AS '性别' FROM `student` AS 's';
-- 函数Concat(a,b)
SELECt CONCAt('姓名:',`name`) AS '新名字' FROM student;
5.3 去重 distinct

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

-- 查询所有
SELECT * FROM student
-- 查询学生名字
SELECt `name` FROM `student`;
-- 将名字相同的数据只显示一条
SELECt DISTINCT `name` FROM `student`;
5.4 数据库的列(表达式)
-- 查询系统版本
SELECt VERSION();
-- 用来计算(表达式)
SELECT 100*3-50 AS '计算结果'; 
-- 查询自增的步长
SELECT @@auto_increment_increment;

-- 所有学生年龄+1
SELECT `name`,`age`+1 AS '年龄+1' FROM `student`;

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

5.5 where条件语句

**作用:**检索数据中符合条件的值。
搜索的条件有一个或者多个表达式组成,结果布尔值。

5.5.1 逻辑运算符
运算符语法描述
and &&a and b a&&b逻辑与,两个都为真,结果为真
or ||a or b a||b逻辑或,其中一个为真,结果为真
not !not a !a逻辑非,真伪假,假为真
5.5.2 练习
-- 查询考试成绩在95~100之间的
select `studentno`,`subjectno`,`studentresult` from `result`
where `studentresult` >=95 and `studentresult` <=100;
-- 除了1000号学生之外的学生成绩
select  `studentno`,`subjectno`,`studentresult` from result 
where `studentno`!=1000;
5.6 模糊查询:比较运算符
运算符语法描述
IS NULLa is null如果操作符位null,结果为真
IS NOT NULLa is not null如果操作符不为null,结果位真
BETWEENa between b and c若a在b和c之间,结果位真
LIKEa like bSQL匹配,如果a匹配b,则结果为真
INa in (a1,a2,a3)假设a在a1,或者a2…其中的某一个值中,结果位真
5.6.1 练习
-- 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 '%强%'
-- 查询 1001 1000 号学员
SELECt `studentno`,`studentname` FROM `student`
WHERe `studentno` in (1000,1001);
-- 查询地址在北京的学员
SELECt `studentno`,`studentname` FROM `student`
WHERe `address` in ('北京朝阳');
-- 查询出生日期为null的学员
SELECt `studentno`,`studentname` FROM `student`
WHERe address='' OR address IS NULL;
-- 查询出生日期不为空的学员
SELECt `studentno`,`studentname` FROM `student`
WHERe`address` IS NOT NULL;
5.7 联表查询(掌握) 5.7.1 join on 连接查询

join(连接的表)on(判断条件)连接查询

-- student表、result表联合查询:姓名、学科编号、分数
-- INNER JOIN:取两张表公共部分
SELECt s.studentname AS '姓名',subjectno AS '学科',studentresult AS '分数'
from student AS s
INNER JOIN result AS r
ON s.studentno = r.studentno;

-- LEFT JOIN:以左表为主表查询
SELECt s.studentname AS '姓名',subjectno AS '学科',studentresult AS '分数'
from student AS s
LEFT JOIN result AS r
ON s.studentno = r.studentno;

-- RIGHT JOIN:以右表左主表查询
SELECt s.studentname AS '姓名',subjectno AS '学科',studentresult AS '分数'
from student AS s
RIGHT JOIN result AS r
ON s.studentno = r.studentno;

-- student表、result表、subject表联合查询:姓名、学科、分数
-- Left Join
SELECt s.studentname AS '姓名',su.subjectname AS '学科',studentresult AS '分数'
from `student` AS s
LEFT JOIN `result` AS r
ON s.studentno = r.studentno 
LEFT JOIN `subject` AS su 
ON r.subjectno = su.subjectno;
操作描述
Inner join如果表中至少有一个匹配,就返回行
left join会从左表中返回所有的值,即使右表中没有匹配
rigth join会从右表中返回所有的值,即使左表中没有匹配
5.7.2 join on where

where等值查询

-- LEFT JOIN + WHERe
SELECt s.studentname AS '姓名',subjectno AS '学科',studentresult AS '分数'
from student AS s
LEFT JOIN result AS r
ON s.studentno = r.studentno
WHERe studentresult IS NULL;
5.7.3 自联结查询(了解)

自己的表和自己的表连接,核心:一张表拆成两张一样的表。
表结构

-- 自连接数据库:
CREATE TABLE `school`.`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 CHARSET = utf8

-- 数据插入
INSERT INTO `school`.`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 s.studentno AS '学号',s.studentname AS '姓名',g.gradename AS '年级'
FROM student AS s
INNER JOIN grade AS g
ON s.gradeid = g.gradeid;

-- 查询学科所属的年级(学科名称、年级名称)
SELECt su.subjectname,g.gradename
FROM `subject` AS su
INNER JOIN grade AS g
ON su.gradeid = g.gradeid;

-- 查询参加了考试同学的信息:学号、姓名、学科、分数
SELECt s.studentno,s.studentname,su.subjectname,r.studentresult
FROM student AS s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` AS su
ON r.subjectno = su.subjectno
WHERe su.subjectname = '数据库结构-1';
5.8 分页和排序

排序:
order by
分页:
缓解数据库的压力,给人的体验更好。
limit

-- 排序:升序ASC、降序DESC 按成绩升序排列
SELECt s.studentno,s.studentname,su.subjectname,r.studentresult
FROM student AS s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` AS su
ON r.subjectno = su.subjectno
ORDER BY r.studentresult ASC;

-- 分页:limit 起始值,每页显示数量
SELECt s.studentno,s.studentname,su.subjectname,r.studentresult
FROM student AS s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` AS su
ON r.subjectno = su.subjectno
ORDER BY r.studentresult ASC
LIMIT 0,5;
-- 第一页	limit	0,5
-- 第二页	limit	5,5
-- 第n也	limit	(n - 1) * pageSize,pageSize
-- pageSize:页面大小
-- n:代表当前页数
-- 数据总数/页面大小 = 总页数

-- 查询大一高等数学考试排名前10且分数大于80的同学信息:学号、姓名、学科、分数
SELECt s.studentno AS '学号',s.studentname AS '姓名',g.gradename as '年级',su.subjectname as '科目',r.studentresult as '分数'
FROM student AS s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` AS su
ON r.subjectno = su.subjectno
INNER JOIN grade AS g
ON su.gradeid = g.gradeid
WHERe gradename = '大一'
AND su.subjectname = '高等数学-1'
AND r.studentresult >80
ORDER BY r.studentresult DESC
LIMIT 0,10;
5.9 子查询和嵌套查询

在查询条件中嵌套查询。

-- 查询'数据库结构-1'的学生学号
SELECt studentno ,`subjectno`,studentresult
FROM result
WHERe subjectno = (SELECt subjectno FROM `subject` WHERe subjectname = '数据库结构-1');

5.10 分组和过滤

GROUP BY
分组
**HAVINg **
过滤

-- 查询不同课程的平均分、最高分、最低分
-- 核心:(根据不同的课程分组)
SELECt `subjectname`,AVG(studentResult) AS 平均分,MAX(studentResult) AS 最大,MIN(studentResult) AS 最小
FROM result AS r
INNER JOIN `subject` su
ON r.subjectno = su.subjectno
GROUP BY r.subjectno -- 通过subjectno来分组
HAVINg 平均分 >= 70 -- 过滤平均分大于70
转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/985886.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号