MySQL的多表联立查询实际应用教程

进入数据库后我们先创建一个student和score表。（如何创建数据库的内容看我之前的文章）

 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)
     );

 CREATR 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 表和 score 表插入数据。

向 student 表插入记录的 INSERT 语句如下：

 INSERT INTO student VALUES(901,'张老大','男',1985,'计算机系','北京市海 淀区');

 INSERT INTO student VALUES( 902,'张老二', '男',1986,'中文系', '北京市昌平区');

 INSERT INTO student VALUES( 903,'张三', '女',1990,'中文系', '湖南省永 州市');

INSERT INTO student VALUES( 904,'李四', '男',1990,'英语系', '辽宁省阜新市');

 INSERT INTO student VALUES( 905,'王五', '女',1991,'英语系', '福建省厦门市');

 INSERT INTO student VALUES( 906,'王六', '男',1988,'计算机系', '湖南省衡阳市');

score表插入记录的INSERT语句如下：

  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);

这样我们的数据库的数据就已经录入好了，接下来我们根据一些问题来练习操作MySQL单表和多表联立的基本查询语句。

1.查询student表的所有记录。

 select * from student;

这个查询使用了 "SELECT" 语句，后跟一个星号 "*"，表示检索所有列的数据。"FROM" 关键字指定了要检索数据的表的名称，即 "student"。

2.查询student表的第5条和6条记录。

mysql> SELECT * FROM student LIMIT 2 OFFSET 4;

从"student"表中选择所有列的数据，然后跳过前4行，取接下来的2行数据。

3.从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息。

 SELECT id, name, department FROM student;

这个查询简单地选择了"student"表中的学号（id）、姓名（name）和院系（department）列的信息。

4.从student表中查询计算机系和英语系的学生的信息。

SELECT * FROM student WHERE department='计算机系' OR department='英语系';

这个查询使用了WHERE子句来筛选出"student"表中院系为"计算机系"或"英语系"的学生信息。

5.从student表中查询年龄18~22岁的学生信息。

 SELECT * FROM student WHERE YEAR(CURDATE()) - YEAR(birth) BETWEEN 18 AND 22;

这个查询使用了YEAR函数和CURDATE函数来计算学生的年龄，并使用BETWEEN来筛选出年龄在18到22岁之间的学生信息。

6.从student表中查询每个院系有多少人。

 SELECT department, COUNT(*) as total_students FROM student GROUP BY department;

这个查询使用了COUNT函数和GROUP BY子句来计算每个院系有多少学生，并返回每个院系的学生总数。

7.从score表中查询每个科目的最高分。

SELECT c_name, MAX(grade) as highest_grade FROM score GROUP BY c_name;

这个查询使用了MAX函数和GROUP BY子句来计算每个科目的最高分，并返回每个科目的最高分数。

以上是一些简单的单表查询，接下来就是两表之间的联立查询。

8.查询李四的考试科目（c_name）和考试成绩（grade）。

 SELECT c_name, grade FROM score WHERE stu_id=(SELECT id FROM student WHERE name='李四');

这个查询使用了子查询来获取李四的学生ID，然后使用这个ID来从"score"表中获取李四的考试科目和成绩信息。

9.用连接的方式查询所有学生的信息和考试信息。

  SELECT * FROM student
  JOIN score ON student.id = score.stu_id;

这个查询使用了INNER JOIN来连接"student"表和"score"表，以便同时检索学生的信息和考试信息。

10.计算每个学生的总成绩。

 SELECT student.id, student.name, SUM(score.grade) as total_grade
    FROM student
    JOIN score ON student.id = score.stu_id
    GROUP BY student.id, student.name;

这个查询使用了SUM函数和GROUP BY子句来计算每个学生的总成绩，并返回每个学生的ID、姓名和总成绩。

11.计算每个考试科目的平均成绩。

 SELECT c_name, AVG(grade) as average_grade
    FROM score
    GROUP BY c_name;

这个查询使用了 AVG函数来计算每个考试科目的平均成绩。 SELECT c_name, AVG(grade) as average_grade选择了考试科目和其对应的平均成绩， FROM score表示从"score"表中获取数据， GROUP BY c_name表示按照考试科目进行分组，以便计算每个考试科目的平均成绩。

12.查询计算机成绩低于95的学生信息。

 SELECT student.*
    FROM student
    JOIN score ON student.id = score.stu_id
    WHERE score.c_name = '计算机' AND score.grade < 95;

这个查询使用了 JOIN来连接"student"表和"score"表，以便同时检索学生的信息和考试成绩。 WHERE子句用于筛选出计算机成绩低于95的学生。

13.查询同时参加计算机和英语考试的学生的信息。

 SELECT student.*
     FROM student
    JOIN score ON student.id = score.stu_id
     WHERE student.id IN (
    SELECT stu_id FROM score WHERE c_name = '计算机'
    ）AND student.id IN (
    SELECT stu_id FROM score WHERE c_name = '英语');

这个查询使用了子查询来获取参加计算机和英语考试的学生信息。它通过两个子查询分别检索参加了计算机考试和英语考试的学生ID，然后使用 IN子句来找出同时满足这两个条件的学生信息。

14.将计算机考试成绩按从高到低进行排序。

SELECT *
    FROM score
    WHERE c_name = '计算机'
    ORDER BY grade DESC;

这个查询使用了 ORDER BY子句来按照计算机考试成绩从高到低进行排序，以便获取排名靠前的学生信息。

15.从student表和score表中查询出学生的学号，然后合并查询结果。

SELECT id FROM student
    UNION
    SELECT stu_id FROM score;

这个查询使用了 UNION操作符来合并"student"表和"score"表中的学生学号，以便获取所有学生的学号信息。

16.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩。

 SELECT student.name, student.department, score.c_name, score.grade
     FROM student
     JOIN score ON student.id = score.stu_id
     WHERE student.name LIKE '张%' OR student.name LIKE '王%';

这个查询使用了 LIKE子句来筛选出姓张或者姓王的学生信息，并使用 JOIN来连接"student"表和"score"表，以便同时获取学生的姓名、院系和考试成绩信息。

以上全部内容就是我对MySQL多表联立基础查询语句的展示以及实际应用教程展示和对一些MySQL语句的解释，希望对你可以有帮助。