DQL查询数据-笔记

DQL查询数据

DQL简介

（Data Query Language：数据库查询语言）

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

准备测试数据

创建4张表：学生表、年级表、学科表、成绩表。并编写测试数据

结构关系：

image-20210206173417845

测试数据暂时可以用excel公式生成：

image-20210206173535638

image-20210206173554409

image-20210206173605224

image-20210206173633503

--创建数据库，确保原先的测试数据已经删除
CREATE DATABASE IF NOT EXISTS `school` ;
--切换数据库
USE `school`;
--创建年级表
CREATE TABLE IF NOT EXISTS `Grade`(
	`GradeNo` INT(11) NOT NULL COMMENT '年级编号',
	`GradeName` VARCHAR(50) NOT NULL COMMENT '年级名称',
	PRIMARY KEY (`GradeNo`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
--创建学生表
CREATE TABLE IF NOT EXISTS `Student`(
	`StudentNo` INT(11) NOT NULL COMMENT '学生编号',
	`LoginPsw` VARCHAR(50) NOT NULL COMMENT '登录密码',
	`StudentName` VARCHAR(50) NOT NULL COMMENT '学生姓名',
	`Sex` INT(1) NOT NULL COMMENT '性别',
	`GradeNo` INT(11) NOT NULL COMMENT '年级编号',
	`Phone` VARCHAR(50) NOT NULL COMMENT '手机号码',
	`Address` VARCHAR(50) NOT NULL COMMENT '地址',
	`E-mail` VARCHAR(50) NOT NULL COMMENT '邮箱',
	`IdentityCard` VARCHAR(50) NOT NULL COMMENT '证件号',
	PRIMARY KEY (`StudentNo`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
--创建课程表
CREATE TABLE IF NOT EXISTS `Subject`(
	`SubjectNo` INT(11) NOT NULL COMMENT '课程编号',
	`SubjectName` VARCHAR(50) NOT NULL COMMENT '课程名称',
	`SubjectClassHour` VARCHAR(50) NOT NULL COMMENT '课程学时',
	PRIMARY KEY (`SubjectNo`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
--创建成绩表
CREATE TABLE IF NOT EXISTS `Score`(
	`ScoreNo` INT(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
	`StudentNo` INT(11) NOT NULL COMMENT '学生编号',
	`SubjectNo` VARCHAR(50) NOT NULL COMMENT '课程编号',
	`ExamDate` DATE NOT NULL COMMENT '考试实践',
	`StudentScore` INT(3) NOT NULL COMMENT '学生成绩',
	PRIMARY KEY (`ScoreNo`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
--输入基础数据
--输入学生数据
INSERT INTO `Student` (`StudentNo`,`LoginPsw`,`StudentName`,`Sex`,`GradeNo`,`Phone`,`Address`,`E-mail`,`IdentityCard`) VALUES ('1001','111111','S1','1','1','13800000001','北京','test@qq.com','1234567833212350');
INSERT INTO `Student` (`StudentNo`,`LoginPsw`,`StudentName`,`Sex`,`GradeNo`,`Phone`,`Address`,`E-mail`,`IdentityCard`) VALUES ('1002','111111','S2','1','2','13800000002','上海','test@qq.com','1234567833212356');
INSERT INTO `Student` (`StudentNo`,`LoginPsw`,`StudentName`,`Sex`,`GradeNo`,`Phone`,`Address`,`E-mail`,`IdentityCard`) VALUES ('1003','111111','S3','2','4','13800000003','无锡','test@qq.com','1234567833211312');
INSERT INTO `Student` (`StudentNo`,`LoginPsw`,`StudentName`,`Sex`,`GradeNo`,`Phone`,`Address`,`E-mail`,`IdentityCard`) VALUES ('1004','123456','S4','1','3','13800000004','南京','test@qq.com','1234567833214241');
INSERT INTO `Student` (`StudentNo`,`LoginPsw`,`StudentName`,`Sex`,`GradeNo`,`Phone`,`Address`,`E-mail`,`IdentityCard`) VALUES ('1005','111111','S5','1','3','13800000005','北京','test@qq.com','1234567833212321');
INSERT INTO `Student` (`StudentNo`,`LoginPsw`,`StudentName`,`Sex`,`GradeNo`,`Phone`,`Address`,`E-mail`,`IdentityCard`) VALUES ('1006','123456','S6','2','3','13800000006','上海','test@qq.com','1234567833212211');
INSERT INTO `Student` (`StudentNo`,`LoginPsw`,`StudentName`,`Sex`,`GradeNo`,`Phone`,`Address`,`E-mail`,`IdentityCard`) VALUES ('1007','111111','S7','1','4','13800000007','无锡','test@qq.com','1234567833211111');
INSERT INTO `Student` (`StudentNo`,`LoginPsw`,`StudentName`,`Sex`,`GradeNo`,`Phone`,`Address`,`E-mail`,`IdentityCard`) VALUES ('1008','123456','S8','1','2','13800000008','南京','test@qq.com','1234567833211235');
INSERT INTO `Student` (`StudentNo`,`LoginPsw`,`StudentName`,`Sex`,`GradeNo`,`Phone`,`Address`,`E-mail`,`IdentityCard`) VALUES ('1009','111111','S9','2','2','13800000009','北京','test@qq.com','1234567833241232');
INSERT INTO `Student` (`StudentNo`,`LoginPsw`,`StudentName`,`Sex`,`GradeNo`,`Phone`,`Address`,`E-mail`,`IdentityCard`) VALUES ('1010','123456','S10','1','1','13800000010','上海','test@qq.com','1234567833211231');
INSERT INTO `Student` (`StudentNo`,`LoginPsw`,`StudentName`,`Sex`,`GradeNo`,`Phone`,`Address`,`E-mail`,`IdentityCard`) VALUES ('1011','123456','S11','1','1','13800000011','无锡','test@qq.com','1234567833217567');
INSERT INTO `Student` (`StudentNo`,`LoginPsw`,`StudentName`,`Sex`,`GradeNo`,`Phone`,`Address`,`E-mail`,`IdentityCard`) VALUES ('1012','123456','S12','2','2','13800000012','南京','test@qq.com','1234567833218354');
INSERT INTO `Student` (`StudentNo`,`LoginPsw`,`StudentName`,`Sex`,`GradeNo`,`Phone`,`Address`,`E-mail`,`IdentityCard`) VALUES ('1013','111111','S13','1','3','13800000013','北京','test@qq.com','1234567833212768');
INSERT INTO `Student` (`StudentNo`,`LoginPsw`,`StudentName`,`Sex`,`GradeNo`,`Phone`,`Address`,`E-mail`,`IdentityCard`) VALUES ('1014','123456','S14','1','4','13800000014','上海','test@qq.com','1234567833211239');
INSERT INTO `Student` (`StudentNo`,`LoginPsw`,`StudentName`,`Sex`,`GradeNo`,`Phone`,`Address`,`E-mail`,`IdentityCard`) VALUES ('1015','111111','S15','2','4','13800000015','无锡','test@qq.com','1234567833210987');
INSERT INTO `Student` (`StudentNo`,`LoginPsw`,`StudentName`,`Sex`,`GradeNo`,`Phone`,`Address`,`E-mail`,`IdentityCard`) VALUES ('1016','123456','S16','1','2','13800000016','南京','test@qq.com','1234567833215879');
INSERT INTO `Student` (`StudentNo`,`LoginPsw`,`StudentName`,`Sex`,`GradeNo`,`Phone`,`Address`,`E-mail`,`IdentityCard`) VALUES ('1017','111111','S17','1','1','13800000017','北京','test@qq.com','1234567833218888');
INSERT INTO `Student` (`StudentNo`,`LoginPsw`,`StudentName`,`Sex`,`GradeNo`,`Phone`,`Address`,`E-mail`,`IdentityCard`) VALUES ('1018','123456','S18','2','3','13800000018','上海','test@qq.com','1234567833217777');
INSERT INTO `Student` (`StudentNo`,`LoginPsw`,`StudentName`,`Sex`,`GradeNo`,`Phone`,`Address`,`E-mail`,`IdentityCard`) VALUES ('1019','111111','S19','1','2','13800000019','无锡','test@qq.com','1234567833216666');
INSERT INTO `Student` (`StudentNo`,`LoginPsw`,`StudentName`,`Sex`,`GradeNo`,`Phone`,`Address`,`E-mail`,`IdentityCard`) VALUES ('1020','123456','S20','1','1','13800000020','南京','test@qq.com','1234567833215555');
--输入年级数据
INSERT INTO `Grade` (`GradeNo`,`GradeName`) VALUES ('1','大一');
INSERT INTO `Grade` (`GradeNo`,`GradeName`) VALUES ('2','大二');
INSERT INTO `Grade` (`GradeNo`,`GradeName`) VALUES ('3','大三');
INSERT INTO `Grade` (`GradeNo`,`GradeName`) VALUES ('4','大四');
-- 课程数据
INSERT INTO `Subject` (`SubjectNo`,`SubjectName`,`SubjectClassHour`) VALUES ('1','高等数学','6');
INSERT INTO `Subject` (`SubjectNo`,`SubjectName`,`SubjectClassHour`) VALUES ('2','现代交换','2');
INSERT INTO `Subject` (`SubjectNo`,`SubjectName`,`SubjectClassHour`) VALUES ('3','通信原理','3');
INSERT INTO `Subject` (`SubjectNo`,`SubjectName`,`SubjectClassHour`) VALUES ('4','数据结构','2');
INSERT INTO `Subject` (`SubjectNo`,`SubjectName`,`SubjectClassHour`) VALUES ('5','C语言','3');
INSERT INTO `Subject` (`SubjectNo`,`SubjectName`,`SubjectClassHour`) VALUES ('6','JavaWeb','3');
INSERT INTO `Subject` (`SubjectNo`,`SubjectName`,`SubjectClassHour`) VALUES ('7','大学英语','4');
INSERT INTO `Subject` (`SubjectNo`,`SubjectName`,`SubjectClassHour`) VALUES ('8','大学物理','2');
INSERT INTO `Subject` (`SubjectNo`,`SubjectName`,`SubjectClassHour`) VALUES ('9','模拟电子','3');
INSERT INTO `Subject` (`SubjectNo`,`SubjectName`,`SubjectClassHour`) VALUES ('10','数字电子','3');
-- 成绩数据
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1000','1','2001-01-01','65');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1000','2','2001-01-02','76');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1000','3','2001-01-03','78');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1000','4','2001-01-04','57');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1000','5','2001-01-05','61');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1000','6','2001-01-06','14');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1000','7','2001-01-07','49');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1000','8','2001-01-08','24');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1000','9','2001-01-09','84');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1000','10','2001-01-10','82');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1001','1','2001-01-01','36');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1001','2','2001-01-02','71');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1001','3','2001-01-03','54');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1001','4','2001-01-04','97');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1001','5','2001-01-05','54');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1001','6','2001-01-06','61');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1001','7','2001-01-07','25');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1001','8','2001-01-08','90');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1001','9','2001-01-09','68');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1001','10','2001-01-10','46');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1002','1','2001-01-01','58');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1002','2','2001-01-02','45');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1002','3','2001-01-03','12');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1002','4','2001-01-04','84');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1002','5','2001-01-05','97');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1002','6','2001-01-06','40');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1002','7','2001-01-07','30');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1002','8','2001-01-08','37');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1002','9','2001-01-09','43');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1002','10','2001-01-10','37');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1003','1','2001-01-01','79');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1003','2','2001-01-02','64');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1003','3','2001-01-03','88');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1003','4','2001-01-04','96');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1003','5','2001-01-05','19');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1003','6','2001-01-06','12');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1003','7','2001-01-07','50');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1003','8','2001-01-08','22');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1003','9','2001-01-09','57');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1003','10','2001-01-10','79');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1004','1','2001-01-01','81');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1004','2','2001-01-02','36');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1004','3','2001-01-03','63');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1004','4','2001-01-04','58');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1004','5','2001-01-05','23');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1004','6','2001-01-06','75');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1004','7','2001-01-07','6');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1004','8','2001-01-08','32');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1004','9','2001-01-09','97');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1004','10','2001-01-10','32');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1005','1','2001-01-01','47');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1005','2','2001-01-02','37');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1005','3','2001-01-03','22');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1005','4','2001-01-04','59');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1005','5','2001-01-05','66');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1005','6','2001-01-06','75');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1005','7','2001-01-07','14');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1005','8','2001-01-08','89');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1005','9','2001-01-09','71');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1005','10','2001-01-10','43');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1006','1','2001-01-01','52');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1006','2','2001-01-02','97');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1006','3','2001-01-03','50');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1006','4','2001-01-04','31');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1006','5','2001-01-05','29');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1006','6','2001-01-06','25');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1006','7','2001-01-07','51');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1006','8','2001-01-08','99');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1006','9','2001-01-09','20');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1006','10','2001-01-10','69');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1007','1','2001-01-01','57');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1007','2','2001-01-02','13');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1007','3','2001-01-03','32');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1007','4','2001-01-04','21');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1007','5','2001-01-05','66');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1007','6','2001-01-06','26');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1007','7','2001-01-07','61');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1007','8','2001-01-08','3');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1007','9','2001-01-09','19');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1007','10','2001-01-10','66');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1008','1','2001-01-01','87');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1008','2','2001-01-02','78');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1008','3','2001-01-03','57');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1008','4','2001-01-04','51');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1008','5','2001-01-05','28');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1008','6','2001-01-06','65');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1008','7','2001-01-07','11');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1008','8','2001-01-08','54');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1008','9','2001-01-09','95');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1008','10','2001-01-10','27');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1009','1','2001-01-01','77');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1009','2','2001-01-02','17');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1009','3','2001-01-03','98');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1009','4','2001-01-04','27');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1009','5','2001-01-05','15');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1009','6','2001-01-06','47');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1009','7','2001-01-07','92');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1009','8','2001-01-08','89');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1009','9','2001-01-09','83');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1009','10','2001-01-10','4');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1010','1','2001-01-01','58');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1010','2','2001-01-02','47');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1010','3','2001-01-03','74');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1010','4','2001-01-04','30');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1010','5','2001-01-05','33');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1010','6','2001-01-06','55');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1010','7','2001-01-07','16');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1010','8','2001-01-08','50');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1010','9','2001-01-09','19');
INSERT INTO `Score` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentScore`) VALUES ('1010','10','2001-01-10','8');

检查下SQLyog中数据是否正常显示：

学生表

image-20210206181733234

年级表：

image-20210206181801399

课程项目表：

image-20210206181824697

成绩表：

image-20210206181841811

指定查询字段

SELECT 表达式 FROM 表

查看全部表的内容

-- 查询全部信息
SELECT * FROM `Student`
SELECT * FROM `Score`

查询指定字段

SELECT `StudentNo`,`StudentName` FROM `Student`

image-20210206182307424

指定查询，给结果重命名

SELECT `StudentNo` AS '学号',`StudentName` AS '姓名' FROM `Student`

image-20210206182430545

函数的使用

--函数 concat(a,b) 可以实现叠加组合
--给名字前加上一个“姓名：”
SELECT CONCAT('姓名:',`StudentName`) AS '新名字' FROM `Student`
--两个字段的数据叠加测试案例
SELECT CONCAT(`StudentNo`,'+',`StudentName`) AS '姓名+学号' FROM `Student`

给名字前加上一个“姓名：”测试案例：

image-20210206182734165

两个字段的数据叠加测试案例：

image-20210206182920339

去重复

fistinct

查询一下有哪些同学参加了考试，结果里一个同学会参加好几门考试，需要去重。

SELECT  DISTINCT `StudentNo` FROM `Score`

image-20210206183618754

数据库的列（表达式）

select可以查询函数、表达式、变量、字段

SELECT VERSION() -- 查询系统版本（函数）
SELECT 100*3-1 AS '计算结果' -- 用来计算（表达式）
SELECT @@auto_increment_increment -- 查询自增步长（变量）
SELECT `StudentScore`+10 AS '提10分后成绩' FROM `Score` --查询字段（可以运算）

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

where条件子句

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

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

尽量使用英文语法

逻辑运算符

运算符 语法 描述
and && a and b (a && b) 逻辑与，两个都为真，结果为真。否则为假
or | | a or b (a || b) 逻辑或，两个都为假，结果为假。否则为真
Not ！ not a (! a) 逻辑非，真为假，假为真 
查询需要重考的学生及成绩（40<=成绩<60）
SELECT `StudentNo` AS '需要补考的学生',`StudentScore` AS '成绩' FROM `Score` WHERE `StudentScore`<60 && `StudentScore`>=40

image-20210206185345140

模糊查询：比较运算符

运算符 语法 描述
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，a3...则结果为真

案例1：查询名字S1开头的同学

-- 查询名字S1开头的同学
-- like结合   %代表0到任意私服，_代表1个字符
SELECT `StudentNo`,`StudentName` FROM `Student` WHERE `StudentName` LIKE 'S1%'

image-20210206204326880

-- like结合   %代表0到任意私服，_代表1个字符
SELECT `StudentNo`,`StudentName` FROM `Student` WHERE `StudentName` LIKE 'S1_'

image-20210206204441057

这里就无法再匹配到S1了。

案例2：查询1001，1002，1003的学生

--in 语句，只能跟具体的值
SELECT `StudentNo`,`StudentName` FROM `Student` WHERE `StudentNo` IN ('1001','1002','1003')

结果如下：

image-20210206204843259

案例3：查询地址为空的学生

-- is null /  is not null  的使用
-- 查询地址为空或空字符串，结合or使用
SELECT `StudentNo`,`StudentName`,`Address` FROM `Student` 
WHERE `Address` IS NULL or `Address`='';

由于所有人填了地址，查不到结果。

image-20210206205119194

联表查询

JOIN

JOIN 连接的表 ON 判断的条件 -------------- 连接查询

image-20210206205742740

案例1：查询参加了考试的同学

需要显示的结果：学号、姓名、课程编号、分数。

  • 分析查询的字段来自哪些表（连接字段）
  • 确定使用哪种连接查询（7种，如上图）
  • 确定交叉点是什么（这两个表中那个数据是相同的）
  • 确定判断条件：Student表中的StudentNo = Score表中的StudentNo

INNER JOIN

-- 使用inner join实现
--注意select的数据来源要写清楚是哪张表的
SELECT st.`StudentNo`,`StudentName`,`SubjectNo`,`StudentScore`
FROM `Student` AS `st` INNER JOIN `Score` AS `sc` ON st.StudentNo = sc.StudentNo

image-20210206211013290

LEFT JOIN

-- 使用inner join实现
--注意select的数据来源要写清楚是哪张表的
SELECT st.`StudentNo`,`StudentName`,`SubjectNo`,`StudentScore`
FROM `Student` AS `st` LEFT JOIN `Score` AS `sc` ON st.StudentNo = sc.StudentNo

右表中没有的项，显示为NULL。

image-20210206212051559

对比：

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

查询缺考的同学

--使用left join 然后用条件语句 where + is null实现
SELECT st.`StudentNo`,`StudentName`,`SubjectNo`,`StudentScore` FROM `Student` AS `st` LEFT JOIN `Score` AS `sc` ON st.StudentNo = sc.StudentNo where StudentScore IS NULL

image-20210206212746820

需要显示的结果：学号、姓名、课程名称、分数。

SELECT st.`StudentNo`,`StudentName`,`SubjectName`,`StudentScore` FROM `score` AS `sc` 
INNER JOIN `Student` AS `st` ON  sc.`StudentNo` = st.`StudentNo`
INNER JOIN `Subject` AS `sub` ON sc.`SubjectNo` = sub.`SubjectNo`

成功将学生姓名、课程名称联表至成绩单中

image-20210206214514406

总结

  • 我要查哪些数据 select ...
  • 从哪些表中查 from 表 XXX join 连接的表1 on 交叉条件1 XXX join 连接的表2 on 交叉条件2 ...
  • 从两张表开始，慢慢练习，然后增加表练习

自连接

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

案例2：父子关系表

表结构：

CategoryID PID CategoryName
2 1 通信工程
3 1 电子信息工程
4 2 移动通信技术
5 1 物联网工程
6 3 数据库技术
7 5 单片机原理与应用
8 5 嵌入式系统开发
9 3 JavaWeb开发
10 2 光传输技术

结构说明：

  • CategoryID为自增的主键，其中1为树状节点的根（Root）
  • PID为此数据的上级CategoryID，表示相互间关系

image-20210206221631189

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

原始表：

image-20210206222446523

查询：

SELECT a.`CategoryName` AS '父栏目',b.`CategoryName` AS '子栏目' 
FROM `major` AS a,`major` AS b 
WHERE a.`CategoryID` = b.`PID`

image-20210206223031413

这种数据存储方式比较常见，需要了解其建表的方式，会用会查

案例3：查询学生所属年级

学号、学生的姓名、年级名称

SELECT `StudentNo`,`StudentName`,gr.`GradeName`
FROM `Student` AS st
INNER JOIN `Grade` AS gr
ON st.`GradeNo` = gr.`GradeNo`

完成后的效果：

image-20210206223623520

案例4：查询参加了科目高等数学考试同学的信息

学号、姓名、课程名、分数，练习。

SELECT sc.`StudentNo` AS '学号',`StudentName` AS '姓名',`SubjectName` AS '课程名',`StudentScore` AS '分数'
FROM `Score` AS sc
INNER JOIN `Student` AS st ON sc.`StudentNo`=st.`StudentNo`
INNER JOIN `Subject` AS su ON sc.`SubjectNo`=su.`SubjectNo`
WHERE su.`SubjectName`='高等数学'

参考结果：

image-20210206224833686

分页和排序

例如搜索引擎、博客，每页显示多个条目，超过的分页显示。

  • 分页：limit 减轻数据库压力，使用体验好，特例：瀑布流（抖音、B站视频导航）

    LIMIT 起始条目,显示条目

--总11条，每页5条
--第一页   limit 0,5   (1-1)*5
--第二页   limit 5,5   (2-1)*5
--第三页   limit 10,5  (3-1)*5
--第N页    limit(n-1)*5,5   (n-1)*pagesize,pagesize
--pagesize:每页大小
--(n-1)*pagesize:起始值
--n:当前页
--数据总量/页面大小（向上取整）=总页数

  • 排序：order by 可以用前端实现

    ORDER BY 字段 DESC/ASC

SELECT sc.`StudentNo` AS '学号',`StudentName` AS '姓名',`SubjectName` AS '课程名',`StudentScore` AS '分数'
FROM `Score` AS sc
INNER JOIN `Student` AS st ON sc.`StudentNo`=st.`StudentNo`
INNER JOIN `Subject` AS su ON sc.`SubjectNo`=su.`SubjectNo`
WHERE su.`SubjectName`='高等数学'
-- 排序 降序 desc。升序asc
ORDER BY `StudentScore` DESC
LIMIT 0,5

image-20210207105757675

案例练习

查询JavaWeb成绩排名前5的，且成绩高于70的学生信息（学号，姓名，课程名称，分数），且按成绩排序。

SELECT sc.`StudentNo` AS '学号',`StudentName` AS '姓名',`SubjectName` AS '课程名',`StudentScore` AS '分数'
FROM `Score` AS sc
INNER JOIN `Student` AS st ON sc.`StudentNo`=st.`StudentNo`
INNER JOIN `Subject` AS su ON sc.`SubjectNo`=su.`SubjectNo`
--条件选择JavaWeb且成绩大于等于60
WHERE su.`SubjectName`='JavaWeb' AND `StudentScore`>=60 
--按成绩降序排列
ORDER BY `StudentScore` DESC
--显示前5个
LIMIT 0,5

image-20210207111753180

子查询

案例1：子查询方式

查询参加 高等数学 的所有学生成绩，降序排序，显示学号，课程编号，分数

联表查询的方式（回顾）

SELECT sc.`StudentNo` AS '学号',`SubjectNo` AS '课程编号',`StudentScore` AS '分数'
FROM `Score` AS sc
INNER JOIN `Subject` AS su ON sc.`SubjectNo`=su.`SubjectNo`
WHERE `SubjectName`='高等数学'
ORDER BY `StudentScore` DESC
--子查询
SELECT sc.`StudentNo` AS '学号',`StudentNo` AS '姓名',`SubjectName` AS '课程名',`StudentScore` AS '分数'

子查询方式

SELECT sc.`StudentNo` AS '学号',sc.`SubjectNo` AS '课程编号',`StudentScore` AS '分数'
FROM `Score` AS sc
WHERE `SubjectNo`=(
    SELECT `SubjectNo` FROM `Subject` 
    WHERE `SubjectName`='高等数学'
)
ORDER BY `StudentScore` DESC

案例2：

查询高等数学分数不小于80分的所有学生的学号和学生，学号升序排序

混合方式参考：

SELECT sc.`StudentNo` AS '学号',st.`StudentName` AS '学生姓名' FROM `Score` AS sc
INNER JOIN `Student` AS st 
ON sc.`StudentNo`=st.`StudentNo` 
WHERE `StudentScore`>=80 AND `SubjectNo`=(
    SELECT `SubjectNo` FROM `Subject`
    WHERE `SubjectName`='高等数学'
)
ORDER BY sc.`StudentNo` ASC

子查询方式参考：

SELECT `StudentNo` AS '学号',`StudentName` AS '学生姓名' FROM `Student`
WHERE `StudentNo` IN (
    SELECT `StudentNo` FROM `Score`
    WHERE `StudentScore` >= 80 AND `SubjectNo`=(
        SELECT `SubjectNo` FROM `Subject` 
        WHERE `SubjectName`='高等数学'
        )
)
ORDER BY `StudentNo` ASC

参考结果：

image-20210207115408147

以上为DQL查询语句的笔记记录，以及练习案例记录。

posted @ 2021-02-07 12:09  大川NV  阅读(95)  评论(0)    收藏  举报