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 成绩表
搜索卷网是谁 -----王五 ;竟然报了两个学科;并算出他的平均成绩