1、初始化工作
a)创建表
#学生表创建
CREATE table student(
Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20) UNIQUE,
Ssex char(2),
Sage SMALLINT,
Sdept char(20)
);
#课程表创建
CREATE table course(
Cno char(4) PRIMARY KEY,
Cname char(40) not NULL,
Cpno char(4),
Ccredit SMALLINT
);
#学生选课表创建
CREATE table SC(
Sno char(9),
Cno char(4),
Grade SMALLINT
);
b)写入数据
#向学生表中添加数据
INSERT into Student values(
201215121,'李勇','男',20,'CS'),
(201215122,'刘晨','女',19,'CS'),
(201215123,'王敏','女',18,'MA'),
(201215125,'张立','男',19,'IS'
);
#向课程表中添加数据
insert into course VALUES
('1','数据库','5',4),
('2','数学','',2),
('3','信息系统','1',4),
('4','操作系统','6',3),
('5','数据结构','7',4),
('6','数据处理','',2),
('7','Java语言','6',4)
#向学生选课表中添加数据
insert into sc values
(201215121,1,92),
(201215121,2,85),
(201215121,3,88),
(201215122,2,58),
(201215122,3,80)
2、实例和答案
2.1单表查询
- 2.1.1 查询全体学生的详细记录
select *
from student
- 2.1.2 查询全体学生的学号与姓名
select Sno,Sname
from student
- 2.1.3 查询全体学生的姓名及其出生年份
select Sname,2020-Sage
from Student
- 2.1.4 查询全体学生的姓名,出生年份和所在的院系,要求用小写字母表示系名
select Sname,2020-Sage,lower(Sdept)
from student
- 2.1.5 查询计算机科学系全体学生的名单(Sdept=‘CS’)
select Sname
from student
where Sdept='CS'
- 2.1.6 查询所有年龄在20岁以下的学生姓名及其年龄
select Sname,Sage
from student
where Sage<20
- 2.1.7 查询年龄在20-23岁(包括20和23)之间的学生的姓名,系别,年龄
select Sname,Sdept,Sage
from student
where Sage between 20 and 23
#也可以
# where Sage>=20 and Sage<=23
- 2.1.8 查询计算机科学系(CS),数学系(MA),信息系(IS)学生的姓名和性别
select Sname,Ssex
from student
where Sdept in('CS','MA','IS')
# 不是就用 not in
- 2.1.9 查询所有姓刘的学生的姓名,学号,性别
select Sname,Sno,Ssex
from student
where Sname like '刘%'
- 2.1.10 查询姓欧阳且全名为3个汉字的学生的姓名
select Sname
from student
where Sname like '欧阳_'
- 2.1.11 查询名字中第二个字为“阳”的学生的姓名和性别
select Sname,Ssex
from student
where Sname like '_阳%'
- 2.1.12 查询缺少成绩的学生的学号和响应的课程号
select Sno,Cno
from sc
where grade is null
# 非空就是 is not null
- 2.1.13 查询计算机科学系且年龄在20岁以下的学生的姓名
select Sname
from student
where Sdept='CS' and Sage<=20
- 2.1.14 查询选修了3号课程的学生的学号及其成绩,查询结果按分数的降序排列
//order by 默认升序,ASC是升序,DESC是降序
select Sno,Grade
from sc
where Cno='3'
order by Grade desc
- 2.1.15 查询全体学生情况,查询结果按所在系的系号升序排列,同一系的学生按年龄降序排列
select *
from student
order by Sdept,Sage DESC
- 2.1.16 查询学生总人数
select count(*)
from student
- 2.1.17 查询选修了课程的学生人数
//学生可以选多门课程,避免重复需在count函数里加distinct短语
select count(distinct Sno)
from sc
- 2.1.18 计算选修1号课程的学生平均成绩
select avg(Grade)
from sc
where Cno='1'
- 2.1.19 查询选修1号课程的学生最高分数
select max(Grade)
from sc
where Cno='1'
- 2.1.20 查询学生201215121选修课程的总学分数
select sum(Grade)
from sc
where Sno='201215121'
- 2.1.21 求各个课程号及相应的选课人数
//group up 是将查询结果按某个属性进行分组
select Cno ,Count(Sno)
from sc
group by Cno
- 2.1.22 查询选修了3门以上课程的学生学号
//having作用于组,这里先用group by按Sno进行分组,再用聚集函数count对每一组进行计数,用having提取出满足条件的组
select Sno
from sc
group by Sno
having count(*)>3
2.2 连接查询
- 2.2.1 查询每个学生及其选修课的情况
select student.*,sc.*
from Student,sc
where student.Sno=sc.Sno
- 2.2.2 查询选修2号课程且成绩在90分以上的所有学生的学号和姓名
select student.Sno,Sname
from Student,sc
where student.Sno=sc.Sno and
sc.Cno='2' and
sc.Grade>=90
- 2.2.3 查询每一门课的间接先修课(先修课的先修课)
//先对一门课找到其先修课,再按此先修课的课程号查找它的先修课,
//将表与自身连接,就要取别名
select FIRST.Cno,second.Cpno
from Course first,Course SECOND
where `first`.Cpno=`SECOND`.Cno
2.3 嵌套查询
- 2.3.1 查询与刘晨在同一个系学习的学生
select Sno,Sname,Sdept
from student
where Sdept in(
select Sdept
from student
where Sname='刘晨'
)
- 2.3.2 找出每个学生超过他自己选修课程平均成绩的课程号
select Sno,Cno
from sc x
where Grade >=(
select avg(Grade)
from sc y
where y.Sno=x.Sno
)
- 2.3.3 查询非计算机系中比计算机系任意学生年龄小的学生姓名和年龄
//任意:any 所有:all
select Sname,Sage
from student
where Sage<any(
select Sage
from student
where Sdept='CS'
)
- 2.3.4 查询选修了全部课程的学生姓名
select Sname
from student
where not exists(
select *
from course
where not exists(
SELECT *
from sc
where Sno=student.Sno AND
Cno=course.Cno
)
)
补:如果对这个not exists不够理解的,可以看下面这个文档
sql语句里面最难的not exists,exists,口语化解释(个人笔记)
2.4 集合查询
- 2.4.1查询选修了1号课程或则2号课程的学生
select Sno
from sc
where Cno='1'
UNION
select Sno
from sc
where Cno='2'
2.5 数据更新
- 2.5.1 将一个新学生元组(学号:201215128,姓名:陈东,性别:男,系别:IS,年龄:18岁)
insert into student
values ('201215128','陈东','男',18,'IS')
- 2.5.2 对每一个系,求学生的平均年龄,并把结果存入数据库
//首先创建一张表来存放数据
create table Deptage(
Sdept char(15),
avg_age smallint
)
//计算数据,存放到表中
insert into Deptage(Sdept,avg_age)
select Sdept,avg(Sage)
from student
group by Sdept
- 2.5.3 将学生201215121的年龄改为22岁
update student
set Sage=22
where Sno='201215121'
- 2.5.4 将所有学生的年龄增加1岁
update student
set Sage=Sage+1
- 2.5.5 删除学号为201215128的学生记录
delete
from student
where Sno='201215128'
- 2.5.6 删除学生表所有记录
truncat student //该语句是删除该张表,重新创建表,不是一条一条删除表中数据;且truncat只能作用于表,delete,drop可作用于表,视图
3、总结
这些都是比较基础的sql语句,这些实例基本可以带你入门,但是还有大量的函数你都不是很了解,比如条件函数、日期函数。
进阶:
参考文档:
- 这个文档部分有错:sql语句大全+实例讲解
- sql语句非实例:常用经典SQL语句大全完整版–详解+实例