sql语句整理

创建表

//学生表创建
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
);

向表中添加数据

//向学生表中添加数据
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)

数据查询

单表查询

查询某几列

1 2	select Sno,Sname from student

查询所有列

1 2	select * from student

查询全体学生的姓名及其出生年份（使用符号进行数学运算）

1 2	select Sname,2020-Sage from Student

条件查询

= … 语句

1
2
3

select Sname
from student
where Sdept='CS'

< … 语句

//查询所有年龄在20岁以下的学生姓名及其年龄
select Sname,Sage
from student
where Sage<20

between .. and .. 语句

//查询年龄在20-23岁（包括20和23）之间的学生的姓名，系别，年龄
select Sname,Sdept,Sage
from student
where Sage between 20 and 23

//不在20-23岁
select Sname,Sdept,Sage
from student
where Sage not between 20 and 23

in(… , … , …) 语句

//查询计算机科学系（CS），数学系（MA），信息系（IS）学生的姓名和性别
select Sname,Ssex
from student
where Sdept in('CS','MA','IS')

//不在
select Sname,Ssex
from student
where Sdept not in('CS','MA','IS')

like ‘ … ‘ 语句

// _表示一个占位符，可以是任何字符
select Sname
from student
where Sname like '欧阳_'

// %表示多个占位符，可以是任何长度的字符串
select Sname,Ssex
from student
where Sname like '_阳%'

is null 语句

//查询所有有成绩的学生的学号和课程号
select Sno,Cno
from sc
where grade is null

and 语句

//查询计算机科学系且年龄在20岁以下的学生的姓名
select Sname
from student
where Sdept='CS' and Sage<=20

order by … 语句（写在where语句之后）

//查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列
//order by 默认升序，ASC是升序，DESC是降序
select Sno,Grade
from sc
where Cno='3'
order by Grade desc //按成绩降序排序

select *
from student
order by Sdept,Sage DESC //先按Sdept升序排序，同Sdept再按降序排序

聚集函数

count 语句

//查询学生总人数
select count(*)
from student

//查询选修了课程的学生人数
//学生可以选多门课程，避免重复需在count函数里加distinct短语
select count(distinct Sno) //按Sno统计个数
from sc

avg 语句

//计算选修1号课程的学生平均成绩
select avg(Grade)
from sc
where Cno='1'

max 语句

//查询选修1号课程的学生最高分数
select max(Grade)
from sc
where Cno='1'

sum 语句

//查询学生201215121选修课程的总学分数
select sum(Grade)
from sc
where Sno='201215121'

group by … 语句（写在where语句之后）

group by Sno 只展示出所聚集的属性 Sno ，不展示出Cno；但实际上返回的是上图这样一个关系图，所以在使用 Count(Cno) 聚集函数的时候会在图上统计

注意：

先根据where条件子句进行过来

再根据group by子句进行聚合

最后再根据having子句对聚合结果进行过滤

//查询一共有哪几个课程号，并展示出来
select Sno
from sc
group by Sno

//求各个课程号及相应的选课人数
//group up 是将查询结果按某个属性进行分组
select Cno ,Count(Sno)
from sc
group by Cno //相当只拿出一个Cno当作主键

//查询选修了3门以上课程的学生学号
//having和group配合使用，且带聚集函数（能不能单独使用看使用的是哪一种数据库）
//having作用于组，这里先用group by按Sno进行分组，再用聚集函数count对每一组进行计数，用having提取出满足条件的组
select Sno
from sc
group by Sno
having count(*)>3

//where句中不能用聚集函数作为条件表达式
//查询平均成绩大于等于90分的学生学号和平均成绩
select Sno,avg(Grade)
from sc
group by Sno
having avg(Grade)>=90

连接查询

//查询每个学生及其选修课的情况
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

//查询选修2号课程且成绩在90分以上的所有学生的学号和姓名
select student.Sno,Sname
from Student,sc
where student.Sno=sc.Sno and 
      sc.Cno='2' and 
      sc.Grade>=90
      
//查询每一门课的间接先修课（先修课的先修课）
//先对一门课找到其先修课，再按此先修课的课程号查找它的先修课，
select first.Cno,second.Cpno
from Course first,Course second //将表与自身连接，就要取别名
where first.Cpno=second.Cno

嵌套查询

//查询与刘晨在同一个系学习的学生
select Sno,Sname,Sdept
from student
where Sdept in(
    select Sdept
    from student
    where Sname='刘晨'
)

//找出每个学生超过他自己选修课程平均成绩的课程号
select Sno,Cno
from sc x
where Grade >=(
    select avg(Grade)
    from sc y
    where y.Sno=x.Sno
)

//查询非计算机系中比计算机系任意学生年龄小的学生姓名和年龄
//任意：any   所有:all
select Sname,Sage
from student
where Sage<any(
   select Sage
   from student
   where Sdept='CS'
)

//查询选修了全部课程的学生姓名
//exists 表示每次从表中选取一个元组进行条件的查询如果能查询到数据则返回true，否则返回false；not exists 语句相反
select Sname
from student
where not exists(
  select * 
  from course
  where not exists(
     SELECT *
     from sc
     where Sno=student.Sno AND 
     Cno=course.Cno
  )    
)

集合查询

//查询选修了1号课程或则2号课程的学生
select Sno
from sc
where Cno='1'
UNION
select Sno
from sc
where Cno='2'

//查询既选修了课程1又选修了课程2的学生
select Sno
from sc
where Cno='1'
intersert
select Sno
from sc
where Cno='2'

数据更新

插入数据

//将一个新学生元组
insert into student
values ('201215128','陈东','男',18,'IS')

//插入一条选课记录
insert into sc(Sno,Cno) VALUES('201215128','1')

//insert语句与查询语句结合使用
//计算数据，存放到表中
insert into Deptage(Sdept,avg_age)
select Sdept,avg(Sage)
from student
group by Sdept

修改数据

//update语句与where语句结合使用
//将学生201215121的年龄改为22岁
update student
set Sage=22
where Sno='201215121'

//将所有学生的年龄增加1岁
update student
set Sage=Sage+1

删除数据

//delete语句与where语句结合使用
delete 
from student
where Sno='201215128'

//删除计算机系所有学生的选课记录
delete 
from sc
where Sno in(
  select Sno
  from student
  where Sdept='CS'
)