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 */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

查询相关练习

-- 查询所有的学生信息
SELECT * FROM t_student;

-- 子查询： 查找 01 成绩及格的学生的 学号、姓名、生日、性别

SELECT * FROM t_student s JOIN score sc ON sc.`s_id` = s.s_id WHERE sc.`score` > 60 AND sc.`c_id` = (SELECT c_id FROM course WHERE c_name = '语文');

-- 子查询： 查看教语文的老师的 工号、姓名
SELECT * FROM t_teacher WHERE t_id = (SELECT t_id FROM course WHERE c_name = '语文');

-- 关联查询： 查找所有男生的 语文 的成绩，学号、姓名、01成绩

SELECT * FROM t_student s JOIN score sc ON sc.`s_id` = s.`s_id` JOIN course c ON c.`c_id` = sc.`c_id` HAVING s.`s_sex` = '男' AND c_name = '语文';

-- 关联查询： 查找 1990年出生的学生 数学 的成绩， 学号，姓名，生日，02成绩 

SELECT * FROM t_student s JOIN score sc ON sc.`s_id` = s.`s_id` JOIN course c ON c.`c_id` = sc.`c_id` HAVING c.`c_name` = '数学' AND s.`s_birthday` BETWEEN '1990-01-01' AND '1990-12-30';

-- 查询每个老师教授课程的列表，显示列表 老师工号、老师名称、课程编号、课程名称。 对数据表做别名，teacher 别名为t , course 别名为 c

SELECT t.`t_id`, t_name, c.`c_id`, c_name FROM t_teacher t JOIN course c ON c.`c_id` = t.`c_id`;

-- 查询每个老师教授课程，显示： 老师工号、老师名称、课程编号、课程名称 ，并以 老师工号倒序/降序(desc)排序。
SELECT t.`t_id`, t_name, c.`c_id`, c_name FROM t_teacher t JOIN course c ON c.`c_id` = t.`t_id` ORDER BY t.`t_id` DESC;

-- 查询张三教授的课程 ，显示： 老师工号、姓名、课程编号、课程名称。 这需要使用 where 条件
SELECT * FROM t_teacher t JOIN course c ON c.`c_id` = t.`c_id` WHERE t_name = '张三';

-- 查询每个学生、每一课的成绩，显示列表  学生学号、学生姓名、生日、性别、课程名称、课程成绩。 
SELECT s.`s_id`, s_name, s_birthday, s_sex, c.`c_name`, score FROM t_student s JOIN score sc ON s.`s_id` = sc.`s_id` JOIN course c ON c.`c_id` = sc.`c_id`;

-- 查询每个学生、每科的成绩，显示列表  学生学号、学生姓名、生日、性别、课程名称、课程成绩。  先以课程id正序(asc)，再以课程成绩倒序(desc)。

SELECT s.`s_id`, s_name, s_birthday, s_sex, c_name, score FROM t_student s JOIN score sc ON sc.`s_id` = s.`s_id` JOIN course c ON c.`c_id` = sc.`c_id` ORDER BY c.`c_id`, score DESC;

-- 查询所有分数 80-90  的学生，成绩， 并以学生成绩倒序排序， 显示列表： 学生学号、学生姓名、生日、性别、课程名称、课程成绩
SELECT s.`s_id`, s_name, s_birthday, s_sex, c_name, score FROM t_student s JOIN score sc ON sc.`s_id` = s.`s_id` JOIN course c ON c.`c_id` = sc.`c_id` WHERE score BETWEEN 80 AND 90;

-- 查询有不及格成绩的学生的 学号、姓名、生日、性别 
SELECT * FROM t_student s JOIN score sc ON sc.`s_id` = s.`s_id` WHERE score < 60;

-- 查询学习过张三老师教过的课程的学生的 学号、姓名、生日、性别 
SELECT * FROM t_student s JOIN course c ON c.`t_id` = (SELECT t_id FROM t_teacher WHERE t_name = '张三');

-- 查询数学老师的信息，显示 老师工号、老师姓名、课程编号、课程名称，
SELECT t.`t_id`, t.`t_name`, c.`c_id`, c.`c_name` FROM t_teacher t JOIN course c ON c.`t_id` = t.`t_id` WHERE c.`c_name` = '数学';

本文就到此结束了， 通过大量的sql查询练习，一定会提升自己的能力，所以说，坚持下去，认真的做下去，未来你会感谢你现在的自己，加油，我们下篇见~

看到这的小伙伴赏给博主个赞吧，卑微博主在线求赞~😙