|
本章以学生——课程数据库为例来讲解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)=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) |