上一篇文章我们用具体的例子讲解了MySQL的SELECT查询语句用法和函数的使用点击查看单表查询的使用,那是在单表查询的运用,这一次我们使用多表查询的题来了解一下查询语句和函数中的具体用法。
函数的名称和作用
在我们进行实战之前需要了解一下在查询语句中必不可少的函数
, 在查询语句中它的使用频率是非常高的,那么MySQL中有哪些函数?可以参考我这篇文章—>MySQL函数
如果你已经了解了函数的使用,请继续往下看。
多表关联查询
除此之外我们还需要了解一些关于多表查询的关键字;
1. inner join:代表选择的是两个表的交差部分。
内连接就是表间的主键与外键相连,只取得键值一致的,可以获取双方表中的数据连接方式。语法如下:
SELECT 列名1,列名2... FROM 表1 INNER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;
2. left join:代表选择的是前面一个表的全部。
左连接是以左表为标准,只查询在左边表中存在的数据,当然需要两个表中的键值一致。语法如下:
SELECT 列名1 FROM 表1 LEFT OUTER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;
3. right join:代表选择的是后面一个表的全部
同理,右连接将会以右边作为基准,进行检索。语法如下:
SELECT 列名1 FROM 表1 RIGHT OUTER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;
4.自连接
自连接顾名思义就是自己跟自己连接,参与连接的表都是同一张表。(通过给表取别名虚拟出)
5.交叉连接:不适用任何匹配条件。生成笛卡尔积
联合查询
说明:一般情况下,我们会将 IN 或者 OR 语句 改写成 UNION ALL,来提高性能
UNION 去重复
UNION ALL 不去重复
创建表
- 我们创建两张表并写入一些信息,以方便展示多表查询的操作和结果展示
登陆mysql并使用一个数据库
(这里我使用自己的mydb数据库)
use mydb;
创建student
学生信息表
(为了创表时不出现问题,请手敲一遍不要复制)
CREATE TABLE student (
id INT(10) NOT NULL UNIQUE PRIMARY KEY , #学号;并设置为主键
name VARCHAR(20) NOT NULL , #姓名;设置为非空值
sex VARCHAR(4) , #性别
birth YEAR, #出生年
department VARCHAR(20) , #院系名称
address VARCHAR(50) #住址
);
创建score
分数表
CREATE TABLE score (
id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT ,#序号设置为主键并自增
stu_id INT(10) NOT NULL , #学号,非空值
c_name VARCHAR(20) , #科目名称
grade INT(10) #成绩
);
向student
表插入一些信息
INSERT INTO student VALUES( 901,'张老大', '男',2001,'计算机系', '北京市海淀区');
INSERT INTO student VALUES( 902,'张老二', '男',2002,'中文系', '北京市昌平区');
INSERT INTO student VALUES( 903,'张三', '女',2003,'中文系', '湖南省永州市');
INSERT INTO student VALUES( 904,'李四', '男',1999,'英语系', '辽宁省阜新市');
INSERT INTO student VALUES( 905,'王五', '女',2002,'英语系', '福建省厦门市');
INSERT INTO student VALUES( 906,'王六', '男',1999,'计算机系', '湖南省衡阳市');
向score
表插入一些信息
INSERT INTO score VALUES(NULL,901, '计算机',98);
INSERT INTO score VALUES(NULL,901, '英语', 80);
INSERT INTO score VALUES(NULL,902, '计算机',65);
INSERT INTO score VALUES(NULL,902, '中文',88);
INSERT INTO score VALUES(NULL,903, '中文',95);
INSERT INTO score VALUES(NULL,904, '计算机',70);
INSERT INTO score VALUES(NULL,904, '英语',92);
INSERT INTO score VALUES(NULL,905, '英语',94);
INSERT INTO score VALUES(NULL,906, '计算机',90);
INSERT INTO score VALUES(NULL,906, '英语',85);
一些问题
1.查询student表的所有记录
SELECT * FROM student;
2.查询student表的第2条到4条记录
返回student
表中第2条到第4条记录,即学号902、903和904的学生信息。
LIMIT
用于限制返回的行数,LIMIT 3
表示每次返回3行,
而OFFSET
用于指定起始位置。OFFSET 1
表示从第2行开始返回。
SELECT * FROM student LIMIT 3 OFFSET 1;
3.从student表查询所有学生的学号(id)、姓名(name)和院系(department)的信息
SELECT id, name, department FROM student;
4.从student表中查询计算机系和英语系的学生的信息
OR
运算符表示满足任一条件的行都会被包括在结果中。
SELECT * FROM student WHERE department = '计算机系' OR department = '英语系';
5.从student表中查询年龄18~22岁的学生信息
YEAR(NOW())
用于获取当前年份,然后通过与出生年birth
列的差值来估算学生的年龄。
BETWEEN
操作符用于限定年龄在指定范围内。
SELECT * FROM student
WHERE YEAR(NOW()) - birth BETWEEN 18 AND 22;
6.从student表中查询每个院系有多少人
SELECT department, COUNT(*)
FROM student
GROUP BY department;
7.从score表中查询每个科目的最高分
使用 MAX(grade)
聚合函数来获取最高分。
GROUP BY
子句用于按照科目名称进行分组,以便获取每个科目的最高分。
SELECT c_name, MAX(grade)
FROM score
GROUP BY c_name;
8.查询李四的考试科目(c_name)和考试成绩(grade)
FROM student INNER JOIN score ON student.id = score.stu_id
: 这是一个内连接操作,将 student 表与 score 表连接在一起。
ON student.id = score.stu_id
指定连接条件,即连接两个表中具有相同学生学号的记录。
SELECT student.name, score.c_name, score.grade
FROM student
INNER JOIN score ON student.id = score.stu_id
WHERE student.name = '李四';
9.用连接的方式查询所有学生的信息和考试信息
这个查询使用了左连接LEFT JOIN
,将 student
表与 score
表连接在一起
左连接确保即使某些学生没有考试记录,他们的信息仍然会显示在结果中。
SELECT student.id, student.name, student.sex, student.birth, student.department, student.address,
score.c_name, score.grade
FROM student
LEFT JOIN score ON student.id = score.stu_id;
10.计算每个学生的总成绩
SUM(score.grade)
用于计算每个学生的总成绩
使用 GROUP BY student.name
子句,将结果按照学生的姓名进行分组,以便每个学生的总成绩都被计算和显示在结果中。
SELECT student.name, SUM(score.grade) AS total_score
FROM student
LEFT JOIN score ON student.id = score.stu_id
GROUP BY student.name;
11.计算每个考试科目的平均成绩
使用 AVG(grade)
聚合函数计算平均值。
通过 GROUP BY
子句,将结果按照考试科目名称进行分组,以计算每个科目的平均成绩。
SELECT c_name, AVG(grade)
FROM score
GROUP BY c_name;
12.查询计算机成绩低于95的学生信息
使用 WHERE
子句筛选出满足条件的学生信息。
SELECT student.id, student.name, score.c_name, score.grade
FROM student
INNER JOIN score ON student.id = score.stu_id
WHERE score.c_name = '计算机' AND score.grade < 95;
13.查询同时参加计算机和英语考试的学生的信息
这道题有两种方法
- 1.使用两次内连接
这个查询使用两个内连接INNER JOIN
将 student 表与 score 表两次关联,分别筛选计算机考试和英语考试的记录。
在这两个连接中,使用 AND
条件将学生ID与相应科目进行匹配,以保证学生同时参加了这两门考试。
SELECT s.id, s.name
FROM student s
INNER JOIN score sc1 ON s.id = sc1.stu_id AND sc1.c_name = '计算机'
INNER JOIN score sc2 ON s.id = sc2.stu_id AND sc2.c_name = '英语';
- 2.使用两个子查询
分别查询参加了计算机考试和英语考试的学生的学号,然后通过主查询从 student 表中筛选出同时满足这两个子查询条件的学生信息。
SELECT id, name
FROM student
WHERE id IN (
SELECT stu_id
FROM score
WHERE c_name = '计算机'
)
AND id IN (
SELECT stu_id
FROM score
WHERE c_name = '英语'
);
14.将计算机考试成绩按从高到低进行排序
使用 WHERE
子句筛选出计算机考试的记录。
最后,使用 ORDER BY
和DESC
成绩进行降序
排序。
SELECT student.id, student.name, score.grade
FROM student
INNER JOIN score ON student.id = score.stu_id
WHERE score.c_name = '计算机'
ORDER BY score.grade DESC;
15.从student表和score表中查询出学生的学号,然后合并查询结果
- 若要输出去重的结果
UNION
运算符会自动去除重复的值
SELECT id FROM student
UNION
SELECT stu_id FROM score;
- 若要输出不去重的结果
UNION ALL
运算符不会自动去除重复的值
SELECT id FROM student
UNION ALL
SELECT stu_id FROM score;
16.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
使用 LIKE
运算符以及通配符%
,这样可以匹配以"张"或"王"开头的任何姓名。
SELECT student.name, student.department, score.c_name, score.grade
FROM student
LEFT JOIN score ON student.id = score.stu_id
WHERE student.name LIKE '张%' OR student.name LIKE '王%';
17.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩
YEAR(NOW()) - student.birth AS age
计算并年龄(通过当前年份减去出生年)并显示为 age
年龄
SELECT student.name, YEAR(NOW()) - student.birth AS age, student.department, score.c_name, score.grade
FROM student
INNER JOIN score ON student.id = score.stu_id
WHERE student.address LIKE '湖南%';
本次多表查询实战使用就到这里,想了解更多MySQL的知识可以关注我和查看我的系列文章;