SQL 表连接

CREATE DATABASE student; -- 新建学生 数据库

CREATE TABLE student.student (  -- 新建 学生表
  studentid INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  sex VARCHAR(10)
);

INSERT INTO student.student (studentid, name, age, sex)  -- 插入3条数据
VALUES (1, '张三', 20, '男');

INSERT INTO student.student (studentid, name, age, sex)
VALUES (2, '李四', 22, '男');

INSERT INTO student.student (studentid, name, age, sex)
VALUES (3, '王五', 21, '女');
select  * from student.student;


CREATE TABLE student.score (   -- 新建成绩表
  id INT PRIMARY KEY,
  student_name VARCHAR(255),
  subject VARCHAR(255),
  score INT
);

INSERT INTO student.score (id, student_name, subject, score) -- 插入成绩数据
VALUES (1, 'John Doe', 'Math', 90);

INSERT INTO student.score (id, student_name, subject, score)
VALUES (2, 'Jane Smith', 'Science', 85);

INSERT INTO student.score (id, student_name, subject, score)
VALUES (3, 'Mike Johnson', 'English', 95);
INSERT INTO student.score (id, student_name, subject, score) -- 插入成绩数据
VALUES (4, '张三', 'Math', 90);

INSERT INTO student.score (id, student_name, subject, score)
VALUES (5, '李四', 'Science', 85);

INSERT INTO student.score (id, student_name, subject, score)
VALUES (6, '王五', 'English', 95);
INSERT INTO student.score (id, student_name, subject, score)
VALUES (7, '王五', 'Science', 95);
ALTER TABLE student RENAME TO students; --  修改学生表student名称为students

select  * from student.students;
select * from student.score;
alter table student.student rename column studentid to id; -- 修改student表中的列名  需要使用alter table;update 更新表中的值
-- 表连接
select * from students  s
left join score sc on s.name=sc.student_name;  

-- 搜索结果包含 王五 报了2个学科 求平均成绩

SELECT s.name,AVG(sc.score) AS average_score -- 显示两行 一行是姓名  一行是平均成绩
FROM students s
JOIN score sc ON s.name = sc.student_name   -- 连接两个表 为一个表
GROUP BY s.name
HAVING COUNT(DISTINCT sc.subject) = 2;

数据库 student

第一张students表

 第二张score 成绩表

 

 搜索卷网是谁  -----王五 ；竟然报了两个学科；并算出他的平均成绩