mysql多表查询sql语句怎么写_day4：MySQL基础sql语句DQL(多表查询，子查询)

一、MySQL四大主要设计语言

DQL(data query language)数据查询语言(续)

承接DQL的内容，继续补充多表查询。

在以后的使用中，我们对于数据的不用会紧紧局限于单张表中的数据。那学生表举例子，我们通过学生的id获得了他在学生数据表中的成绩，那么我们也能通过id获得学生对应课程的成绩。那么将两个结果结合在一起，可以一个语句里面对两个表一起查询。

先在一个sql创建个多表数据库，方便接下来的多表查询。

SET NAMES UTF8;

DROP DATABASE IF EXISTS base;

CREATE DATABASE IF NOT EXISTS base CHARSET=UTF8;

USE base;

#学生表

CREATE TABLE student(

s_id INT PRIMARY KEY AUTO_INCREMENT,#学生编号主键自增

s_name VARCHAR(6) NOT NULL,#学生姓名不可为空

sex BOOL#学生性别0女1男

);

#课程表

CREATE TABLE class(

c_id INT PRIMARY KEY AUTO_INCREMENT,#课程编号主键自增

c_name VARCHAR(12) NOT NULL,#课程名不可为空

t_id INT#教师编号

);

#教师表

CREATE TABLE teacher(

t_id INT PRIMARY KEY AUTO_INCREMENT,#教师编号主键自增

t_name VARCHAR(6) NOT NULL#教师姓名不可为空

);

#成绩表

CREATE TABLE score(

s_id INT,#学生编号

c_id INT,#课程编号

score TINYINT#课程成绩

);

#插入学生数据

INSERT INTO student VALUES(NULL,'张三',0);

INSERT INTO student VALUES(NULL,'李四',1);

INSERT INTO student VALUES(NULL,'王五',0);

#插入教师数据

INSERT INTO teacher VALUES(NULL,'赵一孙');

INSERT INTO teacher VALUES(NULL,'钱一孙');

INSERT INTO teacher VALUES(NULL,'孙一');

#插入课程数据

INSERT INTO class VALUES(NULL,'语文',1);

INSERT INTO class VALUES(NULL,'数学',2);

INSERT INTO class VALUES(NULL,'英语',3);

#插入成绩数据

INSERT INTO score VALUES(1,1,60);

INSERT INTO score VALUES(1,2,80);

INSERT INTO score VALUES(1,3,90);

INSERT INTO score VALUES(2,1,80);

INSERT INTO score VALUES(2,3,90);

INSERT INTO score VALUES(3,2,80);

扔到小黑板运行一下，一个简陋的数据库就做好了。

接下来就开始讲解多表查询。

首先规范一下用于

在单表查询的时候我们直接使用了列名作为查询项。那么在多表查询中建议使用表名.列表名作为查询项，一来是万一表中有重复列名会影响查询，有表名的列更方便电脑查询。

语句都是一样的，从最简单的SELECT…FROM 开始

查询出同学的姓名与他的成绩，先不使用WHERE 条件我们看一下查询结果。

我们发现student中的三条数据与score中每一条数据都进行了一次匹配，这种现象叫做笛卡尔积，有兴趣的可以链接了解一下。

那么如何解决呢。两张表中共有的属性是s_id(学生编号)，那么我们定义条件学生表中的编号与成绩表中的相等，WHERE student.s_id=score.s_id。

显然多变查询只是比单表查询多了一个数据匹配的条件。

那么结合一道综合题，来讲述一下子查询。

查询总成绩的平均数在70及以上的学生的最高分的成绩

1.首先我们要知道满足条件的学生的编号

SELECT student.s_id,student.s_name

FROM student,score

WHERE student.s_id=score.s_id

GROUP BY student.s_id

HAVING AVG(score.score)>=70;

第一个坑就来了。

怎么每个人都出来了呢，李四只考了二门，王五只有一门，怎么还和全考了的张三一起成了本次成绩良好学生了。明显使用AVG时忽略了NULL，所以换一种思路，平均70，那么总和210，所以改HAVING为SUM(score.score)>=210.

结果就只有张三一个人了。

2.我们知道了学生编号是1的张三满足了条件，然后我们要知道他最高分数的成绩是多少

SELECT MAX(score)

FROM score

WHERE s_id=1;

条件中的1是我们第一次查询出的结果，所以可以看作一个整体，用()表示一个整体

然后填入上面的代码。

SELECT MAX(score)

FROM score

WHERE s_id IN (

SELECT student.s_id

FROM student,score

WHERE student.s_id=score.s_id

GROUP BY student.s_id

HAVING AVG(score.score)>=70

);

s_id IN ()表示s_id是()中的一个值。

也就是s_id是满足()查询条件的值。

这种查询嵌套就是子查询了。

今天就这么多了。明天开始写基于nodejs的express模块创建的服务器了。

标签：INSERT,多表,day4,查询,INTO,score,VALUES,student,id

来源： https://blog.csdn.net/weixin_41108537/article/details/88858011