MySQL入门篇之高级查询(1)

前言

好久不见，今日为大家带来mysql中的查询，

查询及其重要，敲重点了，下面进入具体教程

准备前提

新建数据表

t_student:学生表

t_teacher:教师表

score:成绩表

course:课程表

各个表之间都有关联

课程表关联教师表：c_id：课程编号 t_id：教师编号

c_name：课程名称

成绩表关联学生表与课程表：s_id：学生id，c_id：课程id

score:课程成绩

/*

SQLyog Ultimate v12.09 (64 bit)

MySQL - 5.7.24 : Database - demo

*********************************************************************

*/



/*!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*/`demo` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;



USE `demo`;



/*Table structure for table `course` */



DROP TABLE IF EXISTS `course`;



CREATE TABLE `course` (

  `c_id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `c_name` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

  `t_id` int(11) DEFAULT NULL,

  PRIMARY KEY (`c_id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



/*Data for the table `course` */



insert  into `course`(`c_id`,`c_name`,`t_id`) values (1,'语文',1),(2,'数学',3),(3,'英语',2);



/*Table structure for table `score` */



DROP TABLE IF EXISTS `score`;



CREATE TABLE `score` (

  `s_id` int(10) NOT NULL,

  `c_id` int(11) DEFAULT NULL,

  `score` decimal(10,2) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



/*Data for the table `score` */



insert  into `score`(`s_id`,`c_id`,`score`) values (1,2,'90.00'),(1,1,'87.00'),(1,3,'68.50'),(3,1,'79.00'),(3,2,'89.00'),(3,3,'99.00'),(2,3,'86.00'),(2,1,'79.00'),(2,2,'87.00');



/*Table structure for table `t_student` */



DROP TABLE IF EXISTS `t_student`;



CREATE TABLE `t_student` (

  `s_id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `s_name` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

  `s_birthday` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  `s_sex` char(12) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

  PRIMARY KEY (`s_id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



/*Data for the table `t_student` */



insert  into `t_student`(`s_id`,`s_name`,`s_birthday`,`s_sex`) values (1,'张旺','2021-07-25 16:09:46','男'),(2,'李四','2021-07-25 16:09:42','女'),(3,'张无忌','2021-07-25 16:10:14','男');



/*Table structure for table `t_teacher` */



DROP TABLE IF EXISTS `t_teacher`;



CREATE TABLE `t_teacher` (

  `t_id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `t_name` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

  `c_id` int(11) DEFAULT NULL,

  PRIMARY KEY (`t_id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



/*Data for the table `t_teacher` */



insert  into `t_teacher`(`t_id`,`t_name`,`c_id`) values (1,'张三',1),(2,'李四',3),(3,'王五',2);



/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;


### 最后

手绘了下图所示的kafka知识大纲流程图（xmind文件不能上传，导出图片展现），但都可提供源文件给每位爱学习的朋友

![image.png](https://img-blog.csdnimg.cn/img_convert/c9a0910741a087b65d38d6300a581244.webp?x-oss-process=image/format,png)




加入社区：https://bbs.csdn.net/forums/4304bb5a486d4c3ab8389e65ecb71ac0
GN_KEY_CHECKS */;


### 最后

手绘了下图所示的kafka知识大纲流程图（xmind文件不能上传，导出图片展现），但都可提供源文件给每位爱学习的朋友

[外链图片转存中...(img-ygm0vSlt-1725699821778)]




加入社区：https://bbs.csdn.net/forums/4304bb5a486d4c3ab8389e65ecb71ac0