MySQL学习记录04where条件子句、联表查询、子查询

最新推荐文章于 2024-07-06 08:46:49 发布

Jatine

最新推荐文章于 2024-07-06 08:46:49 发布

阅读量986

点赞数

分类专栏：数据库文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/asdfghjkl_zth/article/details/122870864

版权

本文详细记录了MySQL的学习，包括DQL的基础使用，如指定查询字段、WHERE条件子句实现精准检索，联表查询结合ON与WHERE的差异，以及如何进行分页和排序。此外，还介绍了子查询的应用，展示了在不同场景下如何高效地查询数据。

摘要由CSDN通过智能技术生成

文章目录

MySQL学习记录04where条件子句、联表查询、子查询

MySQL学习记录04where条件子句、联表查询、子查询

4.1DQL

Data Query Language：数据查询语言

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

4.2指定查询字段

数据库school建立sql文件：

/*
SQLyog Enterprise v12.08 (64 bit)
MySQL - 5.7.37 : Database - school
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`school` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `school`;

/*Table structure for table `grade` */

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;

/*Data for the table `grade` */

insert  into `grade`(`gradeid`,`gradename`) values (1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');

/*Table structure for table `result` */

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;

/*Data for the table `result` */

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),(1000,6,'2013-11-14 16:00:00',66),(1000,7,'2013-11-14 16:00:00',97),(1000,8,'2013-11-14 16:00:00',87),(1000,9,'2013-11-14 16:00:00',60),(1000,10,'2013-11-14 16:00:00',68),(1000,11,'2013-11-14 16:00:00',87),(1000,12,'2013-11-14 16:00:00',86),(1000,13,'2013-11-14 16:00:00',78),(1000,14,'2013-11-14 16:00:00',88),(1000,15,'2013-11-14 16:00:00',79),(1000,16,'2013-11-14 16:00:00',67),(1001,1,'2013-11-11 16:00:00',85),(1001,2,'2013-11-12 16:00:00',70),(1001,3,'2013-11-11 09:00:00',68),(1001,4,'2013-11-13 16:00:00',98),(1001,5,'2013-11-14 16:00:00',76),(1001,6,'2013-11-14 16:00:00',66),(1001,7,'2013-11-14 16:00:00',87),(1001,8,'2013-11-14 16:00:00',87),(1001,9,'2013-11-14 16:00:00',80),(1001,10,'2013-11-14 16:00:00',65),(1001,11,'2013-11-14 16:00:00',77),(1001,12,'2013-11-14 16:00:00',86),(1001,13,'2013-11-14 16:00:00',98),(1001,14,'2013-11-14 16:00:00',68),(1001,15,'2013-11-14 16:00:00',69),(1001,16,'2013-11-14 16:00:00',77),(1002,1,'2013-11-11 16:00:00',85),(1002,2,'2013-11-12 16:00:00',90),(1002,3,'2013-11-11 09:00:00',78),(1002,4,'2013-11-13 16:00:00',98),(1002,5,'2013-11-14 16:00:00',76),(1002,6,'2013-11-14 16:00:00',76),(1002,7,'2013-11-14 16:00:00',87),(1002,8,'2013-11-14 16:00:00',57),(1002,9,'2013-11-14 16:00:00',80),(1002,10,'2013-11-14 16:00:00',95),(1002,11,'2013-11-14 16:00:00',67),(1002,12,'2013-11-14 16:00:00',66),(1002,13,'2013-11-14 16:00:00',68),(1002,14,'2013-11-14 16:00:00',90),(1002,15,'2013-11-14 16:00:00',79),(1002,16,'2013-11-14 16:00:00',67),(1003,1,'2013-11-11 16:00:00',83),(1003,2,'2013-11-12 16:00:00',90),(1003,3,'2013-11-11 09:00:00',70),(1003,4,'2013-11-13 16:00:00',98),(1003,5,'2013-11-14 16:00:00',74),(1003,6,'2013-11-14 16:00:00