----------------------------------------------建库-----------------------------------------------
--1.新建数据库dbstudy
CREATE DATABASE [dbstudy]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'dbstudy', FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\dbstudy.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'dbstudy_log', FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\dbstudy_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
--------------------------------------------建表----------------------------------------------
--2.建班级表（班号，班名）--ClassInfo(cid,cname)
-------需要在学生信息表建立之前建立，因为学生信息表中的班号必须与班级表的班号对应
use dbstudy
create table classinfo
(
cid int not null primary key identity(1,1),
cname nvarchar(10) not null
)
select * from classinfo
--3.建学生信息表（学号，姓名，性别，年龄，系名，班号）
----StudentInfo(stid,sname,sgender,sage,sdept,cid)
-----学号为主键---班号为外键
create table studentinfo
(
stid int not null primary key,
sname nvarchar(10) not null,
ssex bit default(0),
sage int not null,
sdept nvarchar(15) not null,
cid int not null,
foreign key (cid) references classinfo (cid)
)
select * from studentinfo
--4.建课程表（课号，课名,先修课号，学分）
--SubjectInfo(subid ,subname,pnoid,subcredit)------先修课号为外键
create table SubjectInfo
(
subid int not null primary key identity (1,1),
subname nvarchar(30),
pnoid int not null foreign key(pnoid) references SubjectInfo(subid) ,
subcredit int
)
select * from SubjectInfo
--5.建选课表（学号，课程号，分数）
----sc(stid,subid,grade)----------------学号+课程号为主键，学号为外键，课程号为外键
create table sc
(
stid int not null foreign key(stid) references StudentInfo(stid),
subid int not null foreign key(subid) references SubjectInfo(subid),
primary key(stid,subid),
grade int,
)
select *from sc
------------------------------------------填加数据--------------------------------------------
--6.向班级表中添加数据
insert into ClassInfo values
('计科181'),('计科182'),('计科183'),('计数181'),
('计数182'),('网络181'),('网络182'),('数字181'),
('数字182'),('数媒181'),('数媒182'),('软件181'),
('软件182')
select * from ClassInfo
--7.向学生信息表中添加数据
insert into StudentInfo values
('464','张三','1','19','计算机学院','2'),
('465','李四','0','20','信电学院','3'),
('466','王五','0','21','人文学院','5'),
('467','赵六','1','20','管理学院','1'),
('468','周琦','0','18','外语学院','7'),
('469','詹姆斯','1','19','计算机学院','2'),
('470','乔丹','0','20','信电学院','3'),
('471','杜兰特','0','21','人文学院','5'),
('472','库里','1','20','管理学院','1'),
('473','戴维斯','0','18','外语学院','7')
select * from studentinfo
--8.向课程表中添加数据
insert into SubjectInfo values
('数据库','1','3'),('大学英语','1','2'),('高等数学','2','5'),
('C语言','3','5'),('概率论','2','3'),('大学体育','3','2'),
('大学物理','6','4'),('线性代数','5','4'),('统计学','4','4'),
('计算机组成原理','5','5'),('数据结构','7','6'),('面向对象','8','3')
select * from subjectinfo
--9.向选课表中添加数据
insert into sc values
('464','1','80'),('465','4','85'),
('466','10','88'),('467','3','80'),
('468','6','86'),('469','2','87'),
('470','4','90'),('471','5','79'),
('472','9','77'),('473','11','96')
select * from sc
-----------------------------------------修改表结构----------------------------------------------
--10.为学生表添加属性电话号码-------修改表结构,添加字段
alter table studentinfo
add sphone char (11)
select * from studentinfo
--11.为学生表添加属性邮箱-------修改表结构,添加字段
alter table studentinfo
add semail nvarchar (20)
select * from studentinfo
--12.给电话号码赋值-------更新数据,给电话号码赋值
update StudentInfo set sphone='18334896547' where stid=464
update StudentInfo set sphone='15934896547' where stid=465
update StudentInfo set sphone='17534896547' where stid=466
update StudentInfo set sphone='18634896547' where stid=467
update StudentInfo set sphone='19534896547' where stid=468
update StudentInfo set sphone='15634896547' where stid=469
update StudentInfo set sphone='17734896547' where stid=470
update StudentInfo set sphone='18534896547' where stid=471
update StudentInfo set sphone='15934896547' where stid=472
update StudentInfo set sphone='17634896547' where stid=473
select * from studentinfo
--13. 给邮箱赋值-------更新数据
update StudentInfo set semail='491515415@qq.com' where stid=464
update StudentInfo set semail='256926415@qq.com' where stid between 465 and 473
----------------------------------------------模糊查询---------------------------------------
-------可以根据自己的表内数据适当修改查询条件
--1.查询名字中有冰字的学生
select * from studentinfo
where sname like '%冰%'
--2. 查询名字姓“黄”的学生
select * from studentinfo
where sname like '黄%'
--3.查询名字为两个字,姓唐的学生
select * from studentinfo
where sname like '唐_'
--4.查询姓名为null的学生
select * from studentinfo
where sname is null
--5.查询姓名不为空的学生
select * from studentinfo
where sname is not null
--6.查询电话号码为13开头的学生
select * from studentinfo
where sphone like '15%'
--7.查询电话号码为15,17,19开头的学生
select * from studentinfo
where sphone like '1[579]%'
--8.查询电话号码不是15,17,19开头的学生
select * from studentinfo
where sphone like '1[^579]%'
--9.查询电话号码是以15-19开头的学生
select * from studentinfo
where sphone like '1[5-9]%'
--10.查询邮箱是qq邮箱的学生
select * from studentinfo
where semail like '%@qq.com'
----------------------------------------------------连接查询------------------------------------------
----1. 内连接 inner join : a.cid =b.cid----- a 和b中的cid都完全相等
select * from studentinfo as si
inner join classinfo as ci on si.cid=ci.cid
----2.左连接 left join : a.cid =b.cid-------a中全部的cid都显示，包括cid为null值的
select * from studentinfo as si
left join classinfo as ci on si.cid=ci.cid
---3. 右连接 right join：a.cid =b.cid------b中全部的cid都显示，包括3-10班的
select * from studentinfo as si
right outer join classinfo as ci on si.cid=ci.cid
----4. 完全连接full join：a.cid =b.cid-----a中全部的cid都显示，包括cid为null值的,b中全部的cid都显示，包括3-10班的
select * from studentinfo as si
full outer join classinfo as ci on si.cid=ci.cid
--5. 查询学生的姓名和班级名称
select si.sname,ci.cname from studentinfo as si
inner join classinfo as ci on si.cid=ci.cid
--6. 查询学号,姓名,课程名,分数-------多表连接
select si.stid,si.sname,su.subname,sc.grade
from sc
inner join studentinfo as si on sc.stid=si.stid
inner join subjectinfo as su on sc.subid=su.subid
--7. 查询学号,姓名,课程名,分数,按照学号升序排列，学号相同时，按照分数降序排列----多表连接+连接后排序
select si.stid,si.sname,su.subname,sc.grade
from sc
inner join studentinfo as si on sc.stid=si.stid
inner join subjectinfo as su on sc.subid=su.subid
order by si.stid asc,sc.grade desc
--8.查询学生学号，姓名，性别，年龄，班名，课名，课程分数-----多表连接
select si.stid,si.sname,si.ssex,si.sage,class.cname,
su.subname,sc.grade
from sc
inner join studentinfo as si on sc.stid=si.stid
inner join subjectinfo as su on sc.subid=su.subid
inner join classinfo as class on si.cid=class.cid
use dbstudy
select * from studentinfo
------------聚合函数---------------
---------------------------------------作业1-------聚合函数--视频15----------------------------------------------
----1. 查询共有多少人
select count (*) as 总人数 from studentinfo
----2. 查询编号为1的班级有多少人
select count (*) as 总人数 from studentinfo
where cid=1
----3. 输出电话号码的个数
select count (sphone) as 总人数 from studentinfo
----4. 输出最高成绩
select max(grade) as 分数 from sc
----5.选择科目编号为2的最高成绩
select max(grade) as 分数 from sc
where subid =2
----6. 输出学号为2的学生的最低分数
select min(grade) as 分数 from sc
where stid =465
----7.输出高数(不记得是高数,还是高等数学,还是高级数学) 科目的平均分
select avg(grade) as 高数 from SubjectInfo as su
inner join sc on sc.subid=su.subid
where su.subname like '高%数%'
----------------------------------------作业2--------over函数-----视频16---------------------
----8. 将科目4的各科成绩和平均成绩列出来,其中平均成绩列在每一行中
select grade ,avg(grade) over () as 平均成绩
from sc
where subid=4
--------------------------------------------作业3-------复习-------------------------
----9.查询邮箱是qq邮箱并且电话号码是以15-19开头的学生
select * from studentinfo
where semail like '%@qq.com' and sphone like '1[5-9]%'
----10.查询名字为三个字的学生
select * from studentinfo
where sname like '___'
----11. 查询学号,姓名,课程名,分数,按照分数降序排列，分数相同时，按照学号升序排列----多表连接+连接后排序
SELECT stu.stid,stu.sname,sub.subname,sc.grade
FROM sc
INNER JOIN StudentInfo stu ON stu.stid = sc.stid
INNER JOIN SubjectInfo sub ON sub.subid = sc.subid
ORDER BY sc.grade DESC ,stu.stid ASC
----12.查询有不及格科目的学生姓名，性别，年龄，班名，课名，课程分数,并按照分数降序排列-----多表连接
SELECT stu.sname,stu.ssex,stu.sage,ci.cname,sub.subname,grade
FROM sc
INNER JOIN StudentInfo stu ON stu.stid = sc.stid
INNER JOIN ClassInfo ci ON ci.cid = stu.cid
INNER JOIN SubjectInfo sub ON sub.subid = sc.subid
WHERE sc.grade<60
ORDER BY grade DESC
use dbstudy
select *from studentinfo
select * from classinfo
select *from sc
select * from subjectinfo
----------------------------------分组--------group by-------------------------------
---1.按照性别统计人数,只列出人数
select count(*) from studentinfo
group by ssex
---2.按照性别统计人数,列出性别,人数
select ssex,count(*) from studentinfo
group by ssex
---3.按照科目分组,列出科目序号和每科的平均成绩
select subid ,avg(grade)from sc
group by subid
---4.显示每个班的男女生人数,列出性别,班号,人数
select ssex,cid,count(*) from studentinfo
group by cid,ssex
---5.统计学号大于2的各班各性别人数,列出班号,性别,人数
select cid,ssex,count(*) as 人数 from studentinfo
where stid >2
group by cid,ssex
---6.统计学号大于2的各班各性别人数,列出班名,性别，人数
select ci.cname,st.ssex,count(*) as 人数 from studentinfo as st
inner join classinfo as ci on st.cid=ci.cid
where st.stid >2
group by ci.cname,st.ssex
---7.统计学号>2的1班的各性别的学生信息, 列出性别,人数
select cid,ssex,count(*) as 人数 from studentinfo
where stid >2 and cid=1
group by cid,ssex
---8.统计学号>2的各班级各性别的学生人数>3的信息,列出班号,性别,人数
select cid,ssex,count(*) as 人数 from studentinfo
where stid >2
group by cid,ssex having count(*) > 1
---9.统计学>2的各班级各性别的学生人数>1的信息,列出班号,性别,人数,并按照人数进行降序排列
select cid,ssex,count(*) as 人数 from studentinfo
where stid >2
group by cid,ssex having count(*) > 1
order by count(*)
---10.统计学号>2的各班级各性别的学生人数>1的信息,列出班名,性别,人数,并按照人数进行降序排列
select cname,ssex,count(*) as 人数 from studentinfo as st
inner join classinfo as ci on ci.cid=st.cid
where stid >2
group by cname,ssex having count(*) > 1
order by count(*)
--11. 小结：select语句语法
/*
select distinct top n * -------distinct :消除重复行 ，top n:前n条，
--------top percent :前百分之n条，
------- 可以用聚合函数，如count(*) ,max(grade)等
from t1 ------从表t1中
inner join t2 on ... ----内连接表t2 on 连接条件
inner join t3 on ...
where ... ---------选择条件，可以用like进行模糊查询
group by ....having ... -----分组，having：对分组后的数据进行筛选
order by... -----------排序,asc 升序，desc降序
*/
--------------------------------------------联合查询----------------------------------
---------------将多个结果集按照规定合并
---------------要求：结果集列数一致 ，对应列的类型要一致
---------------union ， union all 并，except差，intersect交--------------
----1.将cid和stid合并，去掉重复值----cid和stid类型必须一致
select cid from classinfo
union
select stid from studentinfo
---2. 将cid和stid合并，保留重复值
select cid from classinfo
union all
select stid from studentinfo
---3. 求cid和stid的交集
select cid from classinfo
intersect
select stid from studentinfo
---4.求cid-stid
select cid from classinfo
except
select stid from studentinfo
---5.求stid-cid
select stid from studentinfo
except
select cid from classinfo
------------------------------------快速备份-------------------------------------
---1. 将班级表中的所有信息存入到一个不存在的表test1中
select * into test1 from studentinfo
select * from test1
---2.将学生的表结构存入表test5中，数据不复制
select top 0 * into test5 from studentinfo
select * from test5
---3.将学生表中的学生姓名和性别复制到已经存在的表test5中
insert into test5(sname,ssex)
select sname,ssex from studentinfo
select * from test5
use dbstudy
select * from studentinfo
select *from classinfo
select * from sc
select * from subjectinfo
---------------视图---------视图是个虚表，只存放视图的定义，不存放对应的数据，若基表数据变化，视图中查找的数据也改变
--1. 显示网络班学生的班级名及学生的所有信息，将该查询定义为一个视图，名为stu_class
create view stu_class
as
select studentinfo.*,classinfo.cname from studentinfo
inner join classinfo on studentinfo.cid=classinfo.cid
where cname like '网络%'
select * from stu_class
--2. 修改视图stu_class,为查询计科班学生的班级名及学生的所有信息
alter view stu_class
as
select studentinfo.*,classinfo.cname from studentinfo
inner join classinfo on studentinfo.cid=classinfo.cid
where cname like '计科%'
select * from stu_class
--3.删除视图stu_class
drop view stu_class
--4. 建立1班学生的视图
create view stu
as
select * from studentinfo
where cid=1
---5. 建立2班学生的视图,并要求进行修改和插入式仍需保证此视图内只有2班的学生-----with check option
create view stu1
as
select * from studentinfo
where cid=2
with check option
--6.定义一个反映学生出生年份,姓名的视图---可以添加视图的列名--create view stu_6(sname,sbirth)
alter view stu_6(sname,sbirth)
as
select sname,2020-sage from studentinfo
select * from stu_6
--7. 将学生的学号和平均成绩定义为一个视图
create view stu4(stid,grade)
as
select studentinfo.stid,avg(grade) from studentinfo
inner join sc on studentinfo.stid=sc.stid
group by studentinfo.stid
select * from stu4
-----------------子查询-----从一个表中查询数据,查询条件来源于另一个表--------------
--1. 查询sc表中的所有学生学号--即参加了考试的学生的学号--结果为集合(2,3,4,6,7,8,9,10)
select distinct stid from sc
--2. 查询所有学生信息 ---结果为集合(2,3,4,5,6,7,8,9,10,11...,30)
select * from studentinfo
--3. 查询参加了考试的学生所有信息---用in完成--查询学号在集合(2,3,4,6,7,8,9,10)中的学生信息
select * from studentinfo
where stid in (select distinct stid from sc)
--4.查询参加了考试的学生所有信息--用exists完成----比in效率高,可以实现空值
select * from studentinfo
where exists (select * from sc where studentinfo.stid=sc.stid)
--5.查询学生信息,按照学号降序排列,并将查询出的记录,重新从1开始编号.
select *,row_number() over(order by stid desc)
from studentinfo
--6. 查询重新标号数为5-8的记录
select * from
(
select *,row_number() over(order by stid desc) as row
from studentinfo
) as t1
where row between 5 and 8
--7.选择跟姚明在一个班学习的学生的信息----用in完成
select * from studentinfo
where cid in
(select cid from studentinfo
where sname like '库里')
--8.选择跟姚明在一个班学习的学生的信息----用exists完成
select * from studentinfo
where exists
(select * from studentinfo as t
where sname like '库里'and studentinfo.cid=t.cid)
--9. 查询选修了“数据结构”的学生的学号
select stid from studentinfo
where stid in(select stid from sc
inner join subjectinfo on sc.subid=subjectinfo.subid
where subname like '数据结构' )
--10.查询选修了“数据结构”的学生所有信息
select * from studentinfo
where stid in(select stid from sc
inner join subjectinfo on sc.subid=subjectinfo.subid
where subname like '数据结构' )
--11.找出每个学生超过他选修课程平均成绩的课程号
select s1.*,t1.subid,sc.grade from subjectinfo as t1
inner join sc on sc.subid=t1.subid
inner join studentinfo as s1 on s1.stid=sc.stid
where exists
(
select * from
(
select t1.subid,avg(grade) as a1 from subjectinfo as t1
inner join sc on sc.subid=t1.subid
group by t1.subid
) as s2
where t1.subid=s2.subid and sc.grade>s2.a1
)
--12.查询没有选修1号课程的学生姓名-----用not exists 完成
select sname from studentinfo
where not exists (select stid from
(select s1.stid from subjectinfo as t1
inner join sc on sc.subid=t1.subid
inner join studentinfo as s1 on s1.stid=sc.stid
where t1.subid=1) as s2
where studentinfo.stid=stid )
综合练习
use dbstu
/*表名和字段
–1.学生表
Student(s_id,s_name,s_birth,s_sex) --学生编号,学生姓名, 出生年月,学生性别
–2.课程表
Course(c_id,c_name,t_id) – --课程编号, 课程名称, 教师编号
–3.教师表
Teacher(t_id,t_name) --教师编号,教师姓名
–4.成绩表
Score(s_id,c_id,s_score) --学生编号,课程编号,分数
*/
--测试数据
--建表
--学生表
CREATE TABLE Student(
s_id VARCHAR(20),
s_name VARCHAR(20) NOT NULL DEFAULT '',
s_birth VARCHAR(20) NOT NULL DEFAULT '',
s_sex VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(s_id)
);
--课程表
CREATE TABLE Course(
c_id VARCHAR(20),
c_name VARCHAR(20) NOT NULL DEFAULT '',
t_id VARCHAR(20) NOT NULL,
PRIMARY KEY(c_id)
);
--教师表
CREATE TABLE Teacher(
t_id VARCHAR(20),
t_name VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(t_id)
);
--成绩表
CREATE TABLE Score(
s_id VARCHAR(20),
c_id VARCHAR(20),
s_score INT,
PRIMARY KEY(s_id,c_id)
);
--插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
select * from student
select * from course
select * from teacher
select * from score
--课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
--教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
--成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);
--练习题
-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select t1.*,c_id,s_score from student as t1
inner join score as t2 on t1.s_id=t2.s_id
where t1.s_id in
(
select a1.s_id from student
inner join score as a1 on student.s_id=a1.s_id
where exists (select * from score as a2
where a1.s_id=a2.s_id and a1.c_id=1 and a2.c_id=2 and a1.s_score>a2.s_score )
)
-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select t1.*,c_id,s_score from student as t1
inner join score as t2 on t1.s_id=t2.s_id
where t1.s_id in
(
select a1.s_id from student
inner join score as a1 on student.s_id=a1.s_id
where exists (select * from score as a2
where a1.s_id=a2.s_id and a1.c_id=1 and a2.c_id=2 and a1.s_score<a2.s_score )
)
-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select student.s_id,s_name,avg(s_score) from student
inner join score on student.s_id=score.s_id
group by student.s_id,s_name having avg(s_score)>=60
-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
-- (包括有成绩的和无成绩的)
select student.s_id,s_name,avg(s_score) from student
inner join score on student.s_id=score.s_id
group by student.s_id,s_name having avg(s_score)<60 or avg(s_score) is null
-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select student.s_id,s_name,count(c_id),count(s_score) from student
inner join score on student.s_id=score.s_id
group by student.s_id,s_name
-- 6、查询"李"姓老师的数量
select count(*) as 人数 from teacher
where t_name like '李%'
-- 7、查询学过"张三"老师授课的同学的信息
select student.* from student
inner join score on student.s_id=score.s_id
inner join course on course.c_id=score.c_id
inner join teacher on course.t_id=teacher.t_id
where t_name like '张三'
-- 8、查询没学过"张三"老师授课的同学的信息
select * from student
where s_id not in (select t1.s_id from student as t1
inner join score on t1.s_id=score.s_id
inner join course on course.c_id=score.c_id
inner join teacher on course.t_id=teacher.t_id
where t_name like '张三')
-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select * from student
where s_id in
(
select s_id from score
where c_id=1
) and s_id in (
select s_id from score
where c_id=2
)
-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select * from student
where s_id in
(
select s_id from score
where c_id=1
) and s_id not in (
select s_id from score
where c_id=2
)
-- 11、查询没有学全所有课程的同学的信息
select distinct t1 .s_id,s_name,s_birth,s_sex from student as t1
inner join score on t1.s_id=score.s_id
group by t1 .s_id,s_name,s_birth,s_sex having count(c_id)!= (select count(*) as 个数 from course)
-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select distinct t1 .* from student as t1
inner join score on t1.s_id=score.s_id
where c_id in(select c_id from student
inner join score on student.s_id=score.s_id
where student.s_id =1)
-- 13、查询没学过"张三"老师讲授的任一门课程的学生姓名 不对
select s_name from student
where s_id not in (select t1.s_id from student as t1
inner join score on t1.s_id=score.s_id
inner join course on course.c_id=score.c_id
inner join teacher on course.t_id=teacher.t_id
where t_name like '张三')
-- 14、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select student.s_id,s_name,avg(s_score) from student
inner join score on student.s_id=score.s_id
where s_score <=60
group by student.s_id,s_name having count (* ) >=2
-- 15、检索"01"课程分数小于60，按分数降序排列的学生信息
select student.* from student
inner join score on student.s_id=score.s_id
where c_id=1 and s_score <60
order by s_score desc
-- 16、查询不同老师所教不同课程平均分从高到低显示
select t_name,score.c_id,avg(s_score) from score
inner join course on course.c_id=score.c_id
inner join teacher on course.t_id=teacher.t_id
group by t_name,score.c_id
order by avg(s_score) desc
-- 17、查询每门课程被选修的学生数
select c_id ,count (*) as 人数 from score
group by c_id
-- 18、查询出只有两门课程的全部学生的学号和姓名
select student.s_id,student.s_name from student
inner join score on student.s_id=score.s_id
group by student.s_id,student.s_name having count(c_id)=2
-- 19、查询男生、女生人数
select s_sex,count(*) as 人数 from student
group by s_sex
-- 20、查询名字中含有"风"字的学生信息
select * from student
where s_name like '%风%'
-- 21、查询同名同性学生名单，并统计同名人数
select s_name ,count (*) as 人数 from student
group by s_name
-- 22、查询1990年出生的学生名单select * from student
select * from student
where s_birth like '1990%'
-- 23、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select c_id from score
group by c_id
order by avg(s_score) desc,c_id
-- 24、查询平均成绩大于等于85的所有学生的学号和平均成绩
select student.s_id,avg(s_score) as 平均成绩 from student
inner join score on score.s_id=student.s_id
group by student.s_id having avg(s_score) >=85
-- 25、查询课程名称为"数学"，且分数低于60的学生姓名和分数
select student.s_name,s_score from student
inner join score on student.s_id=score.s_id
inner join course on course.c_id=score.c_id
where s_score <60 and c_name like '数学'
-- 26、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
select student.s_id,c_name,s_score from student
inner join score on student.s_id=score.s_id
inner join course on course.c_id=score.c_id
where s_score >70
-- 27、查询不及格的学生号，课程号，课程名，分数
select student.s_id,score.c_id,c_name,s_score from student
inner join score on student.s_id=score.s_id
inner join course on course.c_id=score.c_id
where s_score <60
--28、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
select student.s_id,s_name from student
inner join score on student.s_id=score.s_id
where c_id=1 and s_score >=80
-- 29、求每门课程的学生人数
select c_id,count(*) from score
group by c_id
-- 30、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select student.s_id,c_id,s_score from student
inner join score on student.s_id=score.s_id
where score.c_id!=score.c_id
-- 31、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select c_id,count(*) from score
group by c_id having count(*)>5
order by count(*) desc ,c_id
-- 32、检索至少选修两门课程的学生学号
select student.s_id from student
inner join score on student.s_id=score.s_id
group by student.s_id having count(c_id)>=2
-- 33、查询选修了全部课程的学生信息
select student.s_id from student
inner join score on student.s_id=score.s_id
group by student.s_id having count (c_id)=(select count(c_id) from course)
select * from Student
select * from Course
select * from teacher
select * from score
7335
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
