SQL Server 数据查询

文章目录

前言

首先在SQL Server 2008 中新建一个数据库chaxun.在库中建三个表,结构如下表所示,并且录入数据.

1.

CREATEDATABASE  chaxun
ONPRIMARY(NAME=chaxun_data,              
 FILENAME='D:\software\chaxun.mdf',
 SIZE=10MB,
 MAXSIZE=UNLIMITED,           
 FILEGROWTH=10%)
 LOG ON(NAME=chaxun_log,
  FILENAME='D:\software\chaxun.ldf',

  SIZE=1MB,
  MAXSIZE=5MB,
  FILEGROWTH=1MB
 )use chaxun 
  createtable KC
(
c_ID char(3)primarykey,
c_Name char(10)notnull,
c_Credit int)createtable XS
(
s_ID char(4)primarykey,
s_Name char(6)notnull,
s_department char(20)notnull,
s_telephoe char(11))createtable XS_KC
(
s_ID char(4)foreignkeyreferences XS(s_ID),
c_ID char(3)foreignkeyreferences KC(c_ID),
score int,primarykey(s_ID,c_ID))

KC表数据：

insertinto KC values('101','语文',4)insertinto KC values('102','数学',2)insertinto KC values('103','英语',3)insertinto KC values('104','体育',1)

XS表数据：

insertinto XS values('1001','杨颖','信息技术系','2346666')insertinto XS values('1002','王丽','信息技术系','2346666')insertinto XS values('1003','张亮','经管系','2315555')insertinto XS values('1004','刘强','会计系','2361111')

XS_KC表数据：

insertinto XS_KC values('1001','101',89)insertinto XS_KC values('1001','102',97)insertinto XS_KC values('1001','103',86)insertinto XS_KC values('1002','101',56)insertinto XS_KC values('1003','102',55)

2.

(1)在KC表中查询学分低于三分的课程信息，并按课程号升序排列

select*from KC
where C_Credit<3orderby c_ID ASC

(2)在XS_KC表中按学号分组汇总学生的平均分，并按平均分的降序排列

select s_ID,AVG(score) 平均分 from XS_KC 
groupby s_ID
orderbyAVG(score)DESC

(3)在XS_KC表中查询至少选修了2门课程的学生学号和姓名

select  s_Name,XS.s_ID from XS_KC,XS  where XS.s_ID=XS_KC.s_ID
groupby XS.s_ID,s_Name havingCOUNT(XS_KC.c_ID)>=2

(4)查询成绩不及格的学生的基本信息

方法1

select XS.*from XS,XS_KC
where XS.s_ID=XS_KC.s_ID and score<60

方法2(子查询)

select*from XS where s_ID in(select s_ID from XS_KC where score<60)

(5)分别用子查询和连接查询，查询101号课程不及格的基本信息

子查询

select*from XS where s_ID in(selectdistinct s_ID from XS_KC
where XS_KC.c_ID='101'and score <60)

连接查询

select XS.*from XS join XS_KC on XS.s_ID=XS_KC.s_ID  
where XS_KC.c_ID='101'and score <60

(6)在XS表中查询住在同一宿舍的学生信息，即其所住宿舍电话相同

select*from XS where s_telephoe in(select  s_telephoe from XS groupby s_telephoe havingCOUNT(s_telephoe)>=2)

(7)查询XS表中的所有系名

selectdistinct s_department from XS

(8)查询有多少名同学选修了课程

selectCOUNT(distinct s_ID ) 已选课人数 from XS_KC

(9)查询与杨颖同在一个系的同学姓名

方法1

select x.s_Name from XS as x,XS as y
where x.s_department=y.s_department and y.s_Name='杨颖'and x.s_Name<>'杨颖'

方法2

select s_Name from XS where s_department=(select s_department from XS where s_Name='杨颖')and s_Name <>'杨颖'

(10)查询选修了课程的学生的姓名、课程名与成绩

方法1

select s_Name,c_Name,score from XS,KC,XS_KC
where XS.s_ID=XS_KC.s_ID and KC.c_ID=XS_KC.c_ID

方法2

select s_Name,c_Name,score from XS join XS_KC on XS.s_ID=XS_KC.s_ID
join KC on KC.c_ID=XS_KC.c_ID

(11)修改经管系电话号码为3615555

update XS set s_telephoe='3615555'where s_department='经管系'

(12)统计每门课程的选课人数和最高分

select c_ID,COUNT(s_ID) 选课人数,MAX(score) 最高分 from XS_KC groupby c_ID

(13)统计每个学生的选课门数和考试总成绩，并按选课门数的降序排列

select s_ID,COUNT(c_ID) 选课门数,SUM(score) 总成绩 from XS_KC
groupby s_ID orderbyCOUNT(c_ID)DESC

(14)查询有多少名学生没有选课

selectCOUNT(s_ID) 未选课人数 from XS where s_ID notin(select s_ID from XS_KC)

总结

今天的数据库学习内容就到这里啦,如果对友友们有帮助的话,记得点赞收藏博客,关注后续的数据库学习内容哦~👻👻👻