2022/08/17 学习笔记（day29）查询和子查询【MySQL重点】

激进的黄瓜

已于 2022-08-17 21:36:58 修改

阅读量322

点赞数 3

分类专栏： MySQL 新手文章标签： mysql 学习数据库

于 2022-08-17 21:33:54 首次发布

本文链接：https://blog.csdn.net/Dshuai7191/article/details/126394737

版权

本文详细介绍了MySQL的DQL查询语言，包括单表查询的基本语法、列运算、别名、条件控制、排序、聚合函数、分组查询、分页查询、多表查询中的内连接、外连接以及子查询的使用。内容涵盖查询语句的最佳实践，如避免笛卡尔积，合理使用内连接、外连接，并探讨了where和having的区别。文章通过实例解析了如何构建和执行复杂的SQL查询，以满足不同的数据检索需求。

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

DQL数据库查询语言

重点，DQL是我们每天都要接触编写最多也是最难的SQL，该语言用来查询记录，不会修改数据库和表结构。

构建数据库

创建一张student表：

DROP TABLE IF EXISTS student;
CREATE TABLE student (
	id INT(10) PRIMARY KEY,
	`name` VARCHAR(10),
	age INT(10) NOT NULL,
	gender VARCHAR(2)
);

构建一张course表：

DROP TABLE IF EXISTS course;
CREATE TABLE course(
	id INT(10) PRIMARY KEY,
	`name` VARCHAR(10),
	t_id INT(10)
);

构建一张teacher表：

DROP TABLE IF EXISTS teacher;
CREATE TABLE teacher(
	id INT(10) PRIMARY KEY,
	`name` VARCHAR(10)
);

构建一个score表：

DROP TABLE IF EXISTS score;
CREATE TABLE scores(
	s_id INT(10),
	score INT(10),
	c_id INT(10),
	PRIMARY KEY(s_id,c_id)
);

表格填充数据：

insert into  student (id,name,age,gender)VALUES(1,'小明',19,'男'),(2,'小红',19,'男'),(3,'小刚',24,'男'),(4,'小龙',11,'男'),(5,'小丽',18,'男'),(6,'小军',18,'女'),(7,'小航',16,'男'),(8,'小亮',23,'男'),(9,'小杰',22,'女'),(10,'小虎',21,'男');

insert into  course (id,name,t_id)VALUES(1,'数学',1),(2,'语文',2),(3,'c++',3),(4,'java',4),(5,'php',null);


insert into  teacher (id,name)VALUES(1,'Tom'),(2,'Jerry'),(3,'Tony'),(4,'Jack'),(5,'Rose');


insert into  scores (s_id,score,c_id)VALUES(1,80,1);
insert into  scores (s_id,score,c_id)VALUES(1,56,2);
insert into  scores (s_id,score,c_id)VALUES(1,95,3);
insert into  scores (s_id,score,c_id)VALUES(1,30,4);
insert into  scores (s_id,score,c_id)VALUES(1,76,5);

insert into  scores (s_id,score,c_id)VALUES(2,35,1);
insert into  scores (s_id,score,c_id)VALUES(2,86,2);
insert into  scores (s_id,score,c_id)VALUES(2,45,3);
insert into  scores (s_id,score,c_id)VALUES(2,94,4);
insert into  scores (s_id,score,c_id)VALUES(2,79,5);

insert into  scores (s_id,score,c_id)VALUES(3,65,2);
insert into  scores (s_id,score,c_id)VALUES(3,85,3);
insert into  scores (s_id,score,c_id)VALUES(3,37,4);
insert into  scores (s_id,score,c_id)VALUES(3,79,5);

insert into  scores (s_id,score,c_id)VALUES(4,66,1);
insert into  scores (s_id,score,c_id)VALUES(4,39,2);
insert into  scores (s_id,score,c_id)VALUES(4,85,3);

insert into  scores (s_id,score,c_id)VALUES(5,66,2);
insert into  scores (s_id,score,c_id)VALUES(5,89,3);
insert into  scores (s_id,score,c_id)VALUES(5,74,4);


insert into  scores (s_id,score,c_id)VALUES(6,80,1);
insert into  scores (s_id,score,c_id)VALUES(6,56,2);
insert into  scores (s_id,score,c_id)VALUES(6,95,3);
insert into  scores (s_id,score,c_id)VALUES(6,30,4);
insert into  scores (s_id,score,c_id)