大家好,又见面了,我是你们的朋友全栈君。
1.创建3张表
//学生表创建
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
);
2.向表中添加数据 也可使用图形化工具Navicat或其他进行输入,
//向学生表中添加数据
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)
select Sno,Sname
from student
select Sname,Sno,Sdept
from student
select * from student
select Sname,2020-Sage
from Student
select Sname,2020-Sage,lower(Sdept)
from student
select Sno
from SC
select Sname
from student
where Sdept='CS'
select Sname,Sage
from student
where Sage<20
select Sno
from sc
where Grade<60
select Sname,Sdept,Sage
from student
where Sage between 20 and 23
select Sname,Sdept,Sage
from student
where Sage not between 20 and 23
select Sname,Ssex
from student
where Sdept in('CS','MA','IS')
select Sname,Ssex
from student
where Sdept not in('CS','MA','IS')
select *
from student
where Sno='201215121'
select Sname,Sno,Ssex
from student
where Sname like '刘%'
select Sname
from student
where Sname like '欧阳_'
select Sname,Ssex
from student
where Sname like '_阳%'
select Sname,Sno,Ssex
from student
where Sname not like '刘%'
select Sno,Cno
from sc
where grade is null
select Sno,Cno
from sc
where grade is not null
select Sname
from student
where Sdept='CS' and Sage<=20
//order by 默认升序,ASC是升序,DESC是降序
select Sno,Grade
from sc
where Cno='3'
order by Grade desc
select *
from student
order by Sdept,Sage DESC
select count(*)
from student
//学生可以选多门课程,避免重复需在count函数里加distinct短语
select count(distinct Sno)
from sc
select avg(Grade)
from sc
where Cno='1'
select max(Grade)
from sc
where Cno='1'
select sum(Grade)
from sc
where Sno='201215121'
//group up 是将查询结果按某个属性进行分组
select Cno ,Count(Sno)
from sc
group by Cno
//having作用于组,这里先用group by按Sno进行分组,再用聚集函数count对每一组进行计数,用having提取出满足条件的组
select Sno
from sc
group by Sno
having count(*)>3
//where句中不能用聚集函数作为条件表达式
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
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 student.Sno,Sname,Cname,Grade
from student,sc,course
where student.Sno=sc.Sno AND
sc.Cno=course.Cno
select Sno,Sname,Sdept
from student
where Sdept in(
select Sdept
from student
where Sname='刘晨'
)
//嵌套查询太多了,用连接查询呈现出来
select student.Sno,Sname
from student,sc,course
where student.Sno=sc.Sno and
sc.Cno=course.Cno and
course.Cname='信息系统'
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'
)
select Sname,Sage
from student
where Sage<all(
select Sage
from student
where Sdept='CS'
)
select Sname
from student,sc
where student.Sno=sc.Sno and
sc.Cno='1'
select Sname
from student
where not exists(
select *
from course
where not exists(
SELECT *
from sc
where Sno=student.Sno AND
Cno=course.Cno
)
)
select Sno
from sc
where Cno='1'
UNION
select Sno
from sc
where Cno='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')
//首先创建一张表来存放数据
create table Deptage(
Sdept char(15),
avg_age smallint
)
//计算数据,存放到表中
insert into Deptage(Sdept,avg_age)
select Sdept,avg(Sage)
from student
group by Sdept
update student
set Sage=22
where Sno='201215121'
update student
set Sage=Sage+1
update student
set Sage=0
where Sdept='CS'
delete
from student
where Sno='201215128'
delete
from sc
where Sno in(
select Sno
from student
where Sdept='CS'
)
truncat student //该语句是删除该张表,重新创建表,不是一条一条删除表中数据;且truncat只能作用于表,delete,drop可作用于表,视图
select distinct 列名 //去重
select distinct 列名1,列名2,列名3 //对3列都去重
select count(distinct 列名) //计算不重复的列名个数
发布者:全栈程序员栈长,转载请注明出处:https://javaforall.cn/144192.html原文链接:https://javaforall.cn