SQL语句练习

Create table student--学生表
(
ssno varchar(50) not null primary key,--主键学生学号
ssname varchar(50) not null,--学生姓名
sssex varchar(50) not null,--学生性别
sbirthday date,--学生出生年月
ssclass varchar(50) not null,--学生班级
)
--primary key identity(1,1)自增长加入后设置的列无需输入也可自动成序号并排序，此列若需手动输入，就不
insert into student values(‘108‘,‘曾华‘,‘男‘,‘1977-09-01‘,‘95033‘)
insert into student values(‘105‘,‘匡明‘,‘男‘,‘1975-10-02‘,‘95031‘)
insert into student values(‘107‘,‘王丽‘,‘女‘,‘1976-01-23‘,‘95033‘)--学生信息
insert into student values(‘101‘,‘李军‘,‘男‘,‘1977-02-20‘,‘95033‘)
insert into student values(‘109‘,‘王芳‘,‘女‘,‘1975-02-10‘,‘95031‘)
insert into student values(‘103‘,‘陆君‘,‘男‘,‘1974-06-03‘,‘95031‘)

create table teacher--教师表
(能用自增长
jsno varchar(50) not null primary key,--主键 教师工号
jsname varchar(50) not null ,--教师名字
jssex varchar(50) not null,--教师性别
tbirthday date,--教师出生年月
jsprof varchar(50) not null,--教师职称
jsdepart varchar(50) not null,--教师所在部门
)
insert into teacher values(‘804‘,‘李诚‘,‘男‘,‘1958-12-02‘,‘副教授‘,‘计算机系‘)
insert into teacher values(‘856‘,‘张旭‘,‘男‘,‘1969-03-12‘,‘讲师‘,‘电子工程系‘)
insert into teacher values(‘825‘,‘王萍‘,‘女‘,‘1972-05-05‘,‘助教‘,‘计算机系‘)--教师信息
insert into teacher values(‘831‘,‘刘冰‘,‘女‘,‘1977-08-14‘,‘助教‘,‘电子工程系‘)

create table course --课程表
(
kcno varchar(50) not null primary key,--课程号 主键
kcname varchar(50) not null,--课程名称
jsno varchar(50) references teacher(jsno) not null,--教师工号
)
insert into course values(‘3-105‘,‘计算机导论‘,‘825‘)
insert into course values(‘3-245‘,‘操作系统‘,‘804‘)
insert into course values(‘6-166‘,‘数字电路‘,‘856‘)--输入课程
insert into course values(‘9-888‘,‘高等数学‘,‘831‘)

create table score --成绩表
(
ssno varchar(50) references student(ssno) not null,--学生学号外键
kcno varchar(50) references course(kcno) not null,--课程外键
degree int not null,--课程成绩
)
insert into score values(‘103‘,‘3-245‘,86)
insert into score values(‘105‘,‘3-245‘,75)
insert into score values(‘109‘,‘3-245‘,68)--输入成绩
insert into score values(‘103‘,‘3-105‘,92)
insert into score values(‘105‘,‘3-105‘,88)
insert into score values(‘109‘,‘3-105‘,76)
insert into score values(‘101‘,‘3-105‘,64)
insert into score values(‘107‘,‘3-105‘,91)
insert into score values(‘108‘,‘3-105‘,78)
insert into score values(‘101‘,‘6-166‘,85)
insert into score values(‘107‘,‘6-166‘,79)
insert into score values(‘108‘,‘6-166‘,81)

--在插入外键前要先插入主键否则会出现冲突
select ssname,sssex,ssclass from student
--1`查询指定表中，指定的列的信息
select distinct jsdepart from teacher
--2`用去重方法查询 jsdepart（教师所在部门） 中不重复的信息
select *from student
--3`查询Student表的所有记录
select *from score where degree between 60 and 80
--4`查询Score表中成绩在60到80之间的所有记录 between 查询 xx and xx 之间的值
select *from score where degree in (85,86,88)
--5`查询Score表中成绩为85，86或88的记录
select *from student where ssclass=95031 or sssex=‘女‘
--6`查询Student表中“95031”班或性别为“女”的同学记录 or 逻辑语 “或”
select *from student order by ssclass desc
--7`以Class降序查询Student表的所有记录 desc 降序
select *from student order by ssclass asc
--7-1`以Class降序查询Student表的所有记录 asc 降序
select *from score order by kcno asc,degree desc
--7-2`以Cno升序、Degree降序查询Score表的所有记录
--先依表中（课程号）进行升序排序当遇到相同的（课程号）时再以（成绩）为条件对（课程号）相等的值进行降序排序
select count(*)from student where ssclass=‘95031‘
--9`查询“95031”班的学生人数，聚合函数针对数据列 计算求和或者计数等一系列算术性操作
select kcno,ssno from score where degree=(select max(degree) from score)
select kcno,ssno from score where degree=(select min(degree) from score)
--10`查询最高分学生的 课程号，学号 select from 之间一定是列名
--Max 是最大 Min是最小 degree=（select max（degree）from score）为degree生成条件 where后面是条件
select avg(degree) from score where kcno=‘3-245‘
--11`计算‘3-245’课程成绩的平均分,degree必须是int格式否则不能计算
select AVG(degree) from score where kcno=‘3-105‘
select AVG(degree) from score where kcno=‘6-166‘
--同上
--查询表中至少有5名学生选修并且依3开头的课程的平均分
select kcno,avg(degree) as pingjunfen from score where kcno like ‘3%‘
group by kcno having COUNT(*)>=3 --order by pingjunfen --order by 排在最后 当语句中运算完成后再进行排序如果 不用AS 取别名在无法排序
--先从from开始往后排 where 进行筛选把like筛选完成后 再进入 having筛选 筛选完成后在到avg进行运算
select ssno from score where degree>70 and degree<90
--查询分数大于70小于90的 ssno列
--15`查询
select ssname,kcno,degree from score,student where student.ssno=score.ssno
--逗号隔开要查的两个表形成笛卡尔积，任何进行where筛选，通过2个表主外键关系筛选
--student.ssno=score.ssno 筛选必须一致 主外键相连
select ssname,kcno,degree from score--from前 输入想要显示的列名
join student on score.ssno=student.ssno
--正规 双表合并比笛卡尔积更简便 join连接 score与student俩个表 no后面为 主外键相连
--查询所有学生的
--查询所有学生的 ssname, cname, degree
select ssname,kcname,degree from student
join score on student.ssno=score.ssno
join course on course.kcno=score.kcno