等值连接

create database test_db;

学生信息表
学号 姓名 性别 年龄 所在院系

create table Student (
    Sno char(9),
    Sname char(50),
    Ssex char,
    Sage int,
    Sdept char(10),
    primary key (Sno)
);
insert into Student (Sno, Sname, Ssex, Sage, Sdept)
values
('201215121', '李勇', '男', 20, 'CS'),
('201215122', '刘晨', '女', 19, 'CS'),
('201215123', '王敏', '女', 28, 'MA'),
('201215125', '张力', '男', 19, 'IS');

选修表
学号 课程号 成绩

create table SC (
    Sno char(9),
    Cno int,
    Grade int,
    primary key (Sno, Cno)
);
insert into SC (Sno, Cno, Grade)
values
('201215121', 1, 92),
('201215121', 2, 85),
('201215121', 3, 88),
('201215122', 2, 90),
('201215122', 3, 80);

查询每个学生及其选修课程的情况

select Student.*, SC.*
from Student, SC
where Student.Sno = SC.Sno;

在等值连接中去除重复属性的为自然连接

select Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
from Student, SC
where Student.Sno = SC.Sno;

一条SQL语句可以同时完成连接查询，并对产生的元组筛选

select Student.Sno, Sname, SSex, Sage, Sdept, Cno, Grade
from Student, SC
where Student.Sno = SC.Sno and
        SC.Cno = 2;

当然也是可以进行分组刷选的
查询学生选修课的平均成绩，并按照从小到大进行排序

select Student.Sno, Sname, avg(Grade)
from Student, SC
where Student.Sno = SC.Sno
group by Sno
order by avg(Grade);