关系数据库标准语言SQL

本章以学生——课程数据库为例来讲解SQL的数据定义、数据操纵、数据查询和数据控制语句。

记一次上机课操作：

-- 创建数据库student
create database student;

-- 使用数据库
use student;

-- 创建学生表
create table student(
 id_sno char(12) primary key,
 sname char(8),
 sex char(2),
 sdept char(20)
 );

--  创建课程表
create table course(
id_cno char(4) primary key,
cname char(40) not null,
cpno char(4),
credit smallint,
   foreign key (cpno) references course(id_cno)
);

-- 创建学生选课表
create table sc(
id_sno char(12),
id_cno char(4),
grade  char(4),
foreign key (id_sno) references student(id_sno),
foreign key (id_cno) references course(id_cno),
primary key(id_sno,id_cno)
);

-- 添加电话号码列
alter table student add column tel char(11);

-- 删除学生表中系部的一列
alter table student drop column sdept;

-- 插入学生表的数据信息
insert into student values(202010917237,'张三','男',123445560);
insert into student values(202010917238,'李四','男',123445560);
insert into student values(202010917239,'王五','男',123445560);
insert into student values(202010917240,'赵七','男',123445560);
insert into student values(202010917241,'张','女',123445560);

-- 查询学生全部信息
select * from student;

-- 插入课程表的数据信息
insert into course(id_cno,cname,cpno,credit) values(01,'数据结构',null
,4);
insert into course(id_cno,cname,cpno,credit) values(02,'高数',null
,3);
insert into course(id_cno,cname,cpno,credit) values(03,'数据库',02
,4);
insert into course(id_cno,cname,cpno,credit) values(04,'算法设计与分析',01
,3);
insert into course(id_cno,cname,cpno,credit) values(05,'操作系统',04
,5);

-- 查询课程全部信息
select * from course

-- 插入学生选课表的数据信息
insert into sc(id_sno,id_cno,grade) values(202010917237,01,78);
insert into sc(id_sno,id_cno,grade) values(202010917237,02,48);
insert into sc(id_sno,id_cno,grade) values(202010917237,03,58);
insert into sc(id_sno,id_cno,grade) values(202010917237,04,70);
insert into sc(id_sno,id_cno,grade) values(202010917237,05,77);

insert into sc(id_sno,id_cno,grade) values(202010917238,01,57);
insert into sc(id_sno,id_cno,grade) values(202010917238,02,90);
insert into sc(id_sno,id_cno,grade) values(202010917238,03,99);
insert into sc(id_sno,id_cno,grade) values(202010917238,04,45);
insert into sc(id_sno,id_cno,grade) values(202010917238,05,68);

insert into sc(id_sno,id_cno,grade) values(202010917239,01,88);
insert into sc(id_sno,id_cno,grade) values(202010917239,02,58);
insert into sc(id_sno,id_cno,grade) values(202010917239,03,78);
insert into sc(id_sno,id_cno,grade) values(202010917239,04,68);
insert into sc(id_sno,id_cno,grade) values(202010917239,05,78);

insert into sc(id_sno,id_cno,grade) values(202010917240,01,88);
insert into sc(id_sno,id_cno,grade) values(202010917240,02,88);
insert into sc(id_sno,id_cno,grade) values(202010917240,03,88);
insert into sc(id_sno,id_cno,grade) values(202010917240,04,88);
insert into sc(id_sno,id_cno,grade) values(202010917240,05,98);

insert into sc(id_sno,id_cno,grade) values(202010917241,01,88);
insert into sc(id_sno,id_cno,grade) values(202010917241,02,98);
insert into sc(id_sno,id_cno,grade) values(202010917241,03,98);
insert into sc(id_sno,id_cno,grade) values(202010917241,04,100);
insert into sc(id_sno,id_cno,grade) values(202010917241,05,98);

-- 查询学生选课表全部信息
select * from sc

select id_sno as 学号 from sc;

-- 查询所有年龄在20岁以下的学生姓名和年龄
alter table student add column data_time char(12);
alter table student change column data_time born_time char(12);
update student set born_time='2000-12-23' where id_sno=202010917237;
update student set born_time='2002-12-23' where id_sno=202010917238;
update student set born_time='2003-12-24' where id_sno=202010917239;
update student set born_time='2001-09-20' where id_sno=202010917240;
update student set born_time='2000-04-23' where id_sno=202010917241;
select sname,year(now())-year(born_time) as age from student where year(now())-year(born_time)<20;

-- 查询考试成绩不及格的学生的学号
-- 在表中，可能会包含重复值。这并不成问题，不过，有时您也许希望仅仅列出不同（distinct）的值。 
-- 关键词 DISTINCT 用于返回唯一不同的值。
select distinct id_sno from sc where grade<60;

-- 查询年龄在20-23岁之间的学生姓名、系别和年龄
alter table student add column sdept char(20);
update student set sdept='计算机学院' where id_sno=202010917237;
update student set sdept='软件学院' where id_sno=202010917238;
update student set sdept='土木工程学院' where id_sno=202010917239;
update student set sdept='计算机学院' where id_sno=202010917240;
update student set sdept='理学院' where id_sno=202010917241;
select sname,sdept,year(now())-year(born_time) as age from student where year(now())-year(born_time) between 20 and 23;

-- 查询系部在计算机学院、软件学院学生的姓名和性别
select sname,sex from student where sdept in('计算机学院','软件学院');
-- 查询系部不在计算机学院、软件学院学生的姓名和性别
select sname,sex from student where sdept not in('计算机学院','软件学院');

-- 查询所有姓张的学生的姓名、学号、性别
select sname,id_sno,sex from student where sname like '张%';

-- 查询学号倒数二个字是3的学生姓名和学号
select sname,id_sno from student where id_sno like '%3_';

-- order by子句
-- 查询选修了3号课程的学生的学号以及成绩，查询结果按成绩的降序排列
select id_sno,grade from sc where id_cno='3' order by grade desc;
-- 查询选修了1号课程的学生的学号以及成绩，查询结果按成绩的升序排列
select id_sno,grade from sc where id_cno='1' order by grade asc;
-- 查询全体学生情况，查询结果按所在系的系号降序排列，出生年月日按升序排列
select * from student order by sdept desc,born_time asc;

-- 聚集函数
-- 查询学生总人数
select count(*) from student;
-- 查询选修课程的学生人数.distinct短语表示取消列中重复值
select count(id_sno) from sc;
select count(distinct id_sno) from sc;
-- 计算选修1号课程的学生平均成绩
select avg(grade) from sc where id_cno='1';
-- 查询选修1号课程的学生最高成绩
select max(grade) from sc where id_cno='1';
-- 查询学号为202010917240先修课程的总学分数
select sum(credit) from sc,course where id_sno='202010917240' and sc.id_cno=course.id_cno;

-- group by子句
-- 求各个课程及相应的选课人数
select id_cno,count(id_sno) from sc group by id_cno; 
-- 查询选修了三门以上课程的学生学号
select id_sno from sc group by id_sno having count(*)>3;
-- 查询平均成绩大于等于90分的学生学号和平均成绩
select id_sno,avg(grade) from sc group by id_sno having avg(grade)>=90;

-- 连接查询
-- 等值连接与非等值连接
-- 查询每个学生及其选修课程的情况
select student.*,sc.* from student,sc where student.id_sno=sc.id_isno;
-- 在等值连接中把目标列中的重复列的属性列去掉则为自然连接
select student.id_sno,sname,sex,born_time,sdept,id_cno,grade from student,sc where student.id_sno=sc.id_sno;
-- 查询选修2号课程且成绩在90分以上的所有学生的学号和姓名
select student.id_sno,sname from student,sc where student.id_sno=sc.id_sno and sc.id_cno='2' and sc.grade>90;
-- 自身连接
查询每一门课的间接先修课（即先修课的先修课)
select first.id_cno,second.cpno from course first,course second where first.cpno=second.id_cno;
-- 外连接
-- 查询每个学生及其选修课程的情况(左外连接）
select student.id_sno,sname,sex,born_time,sdept,id_cno,grade from student left outer join sc on (student.id_sno=sc.id_sno);
-- 多表连接
-- 查询每个学生的学号、姓名、选修的课程名及成绩（本查询涉及三个表）
select student.id_sno,sname,cname,grade from student,sc,course where student.id_sno=sc.id_sno and sc.id_cno=course.id_cno;

-- 嵌套查询
-- 查询与“张三”在同一个系学习的学生
select id_sno,sname,sdept from student where sdept in (select sdept from student where sname='张三');
-- 查询选修了课程名为“操作系统”的学生学号和姓名
-- 嵌套查询实现
select id_sno,sname from student where id_sno in (select id_sno from sc where id_cno in (select id_cno from course where cname='操作系统'));
--连接实现
select student.id_sno,sname from student,sc,course where student.id_sno=sc.id_sno and sc.id_cno=course.id_cno and course.cname='操作系统'; 
-- 带有比较运算符的子查询
-- 找出每个学生超出他自己选修课程平均成绩的课程号
select id_sno,id_cno from sc x where grade>=(select avg(grade) from sc y where y.id_sno=x.id_sno);
-- 查询非计算机科学系中比计算机科学系中任意一个学生年龄小的学生姓名和年龄
select sname,sdept,year(now())-year(born_time) as age from student where year(now())-year(born_time);
select sname,year(now())-year(born_time) age from student where year(now())-year(born_time)<any(select year(now())-year(born_time) from student where sdept='计算机学院') and sdept<>'计算机学院';
 --聚集函数实现
select sname,year(now())-year(born_time) age from student where year(now())-year(born_time)<(select max(year(now())-year(born_time)) from student where sdept='计算机学院') and sdept<>'计算机学院';
-- 查询非计算机科学系中比计算机科学系中所有学生年龄小的学生姓名和年龄
select sname,year(now())-year(born_time) age from student where year(now())-year(born_time)<all(select year(now())-year(born_time) from student where sdept='计算机学院') and sdept<>'计算机学院';
 --聚集函数实现
select sname,year(now())-year(born_time) age from student where year(now())-year(born_time)<(select min(year(now())-year(born_time)) from student where sdept='计算机学院') and sdept<>'计算机学院';