【SQL学习】select语句使用实例

今天药忘吃喽~ 2022-04-16 04:00 737阅读 0赞

### 前言 ###

在使用数据库的过程中，我们最常用到还是查询语句，即 `select * from tablename`, 这篇博客就通过具体的实例说明一下select语句的用法。

### 创建数据库及数据表 ###

为了统一下面的查询语句，我这里给出语句，只需要复制然后在你的数据库中执行，就可以创建和笔者一样的数据库环境

create database mydb;//创建数据库
    use mydb; //进入数据库

**学生表**. Student(Sno,Sname,Ssex,Sage,Sdept) Sno为主键;  
说明：Sno 学号，Sname姓名，Ssex 性别，Sage 年龄，Sdept所在系

create table student
    (sno int primary key,
     sname varchar(20),
     ssex nvarchar(1),
     sage tinyint,
     sdept varchar(40)
    );
    insert into student values(95001,'李敏勇','男',20,'CS');
    insert into student values(95002,'刘晨','女',19,'IS');
    insert into student values(95003,'王敏','女',18,'MA');
    insert into student values(95004,'张立','男',18,'IS');
    insert into student values(95005,'王敏','男',19,'IS');

**课程表**. Course(Cno,Cname,Cpno,Credeit) Cno为主键;  
说明：Cno课程号，Cname 课程名，Cpno 先修课号，Credit学分

create table course
    (cno int primary key,
     cname varchar(20),
     cpno int,
     credit int 
    );
    
    insert into course values(1,'数据库',5,4);
    insert into course values(2,'数学',null,2);
    insert into course values(3,'信息系统',1,4);
    insert into course values(4,'操作系统',6,3);
    insert into course values(5,'数据结构',7,4);
    insert into course values(6,'数据处理',null,2);
    insert into course values(7,'Pascal语言',6,4);

**学生选修表**. SC(Sno,Cno,Grade,Testtime) Sno,Cno,为主键;  
说明：Sno 学号，Cno课程号，Grade成绩，Testtime 考试时间

create table sc
    (sno int not null,
     cno int not null,
     grade decimal(10,2),
     testtime date,
     primary key (sno,cno)
    );
    insert into sc values (95001,1,92,'2017-6-20');
    insert into sc values (95001,2,88,'2017-6-23');
    insert into sc values (95001,3,88,'2017-6-26');
    insert into sc values (95002,2,90,'2017-6-23');
    insert into sc values (95002,3,80,'2017-6-26');
    insert into sc values (95005,1,99,'2017-6-20');
    insert into sc values (95005,2,95,'2017-6-23');
    insert into sc values (95005,3,98,'2017-6-26');

### 条件查询 where 的使用 ###

--1. 查询计算机系全体学生的姓名及年龄
    select sname,sage from student;
    --2. 查询所有年龄在20岁以下的学生姓名及其年龄
    select sname,sage from student where sage<20;
    --3. 查询年龄在20到23间（含20和23岁）的学生的姓名,系别及年龄
    select sname,sdept,sage from student where sage>=20 and sage<=23;
    --4. 查询年龄不在20到23间的学生的姓名,系别及年龄
    select sname,sdept,sage from student where sage not between 20 and 23;
    --5. 查询不是信息系(IS)和计算机系(CS)学生的姓名和性别
    select sname,ssex from student where sdept not like 'IS' and sdept not like 'CS';
    --6. 查询所有姓刘的学生的姓名,学号和性别
    select sname,sno,ssex from student where sname like '刘%';
    --7. 查询名字中含有”敏”字的学生姓名和学号
    select sname,sno from student where sname like '%敏%';
    --8. 查询全体学生情况，结果按所在系升序排列，同一系中的学生按年龄降序
    select * from student order by sdept asc,sage desc;
    --9. 查询学分为3分及以上且有先修课程的课程号、课程名
    select cno,cname from course where credit >=3 and cpno is not null;
    --10.查询选修了课程的学生学号
    select distinct sno from sc ;

### 使用统计函数 ###

sum：求和  
avg ：平均  
count ：统计个数  
max ： 求最大值  
min ：求最小值

--1. 查询学生的总人数
    select count(sno) as '总人数' from student;
    
    --2. 查询选修了课程的学生人数
    select count(distinct cno) as '选课人数' from sc; 
    --3. 计算选2号课程的学生平均成绩（提示：使用convert函数可以设置输出效果）
    select convert(decimal(4,2),avg(grade)) as  '平均分' from sc where cno=2;
    --4. 查询选修3号课程的学生最高分数
    select max(grade) as '最高分' from sc where cno=3;
    --5. 查看有选课记录的课程相应的选课人数
    select cno as '课程号',count(sno) as '选课人数' from sc group by cno;
    --6. 查询选修了课程的同学的选课数目、最高分
    select sno as '学号',count(cno) '选课数目',max(grade) '最高分' from sc group by sno;

### 分组统计查询 group by的使用 ###

--1、	查询选修了2门及以上课程的学生的学号和 平均成绩，并根据平均成绩升序排列。
     select sno as '学号',convert(decimal(4,2),avg(grade))as '平均成绩' from sc group by sno having count(sno)>=2 order by avg(grade) ;
    --2、	查询各学分等级的课程数目
     select credit as 'credit',count(credit)as '该学分对应的课程数目' from course group by credit; 
    --3、	查询信息系（IS）男女生的人数
     select ssex as '性别',count(ssex) as '人数' from student where sdept = 'IS' group by ssex;
    --4、	查询人数最多的系及人数
     select top 1 sdept as '系名',count(sdept) as '人数' from student group by sdept order by count(sdept) desc;
    --5、查询选修“数据库”课程的学生的学号
     select sc.sno as '学号' from sc,course c where sc.cno =c.cno and c.cname='数据库'; 
    --6、查询没有一门考试科目成绩低于86分的学生的姓名以及平均分
     select st.sname as '学生姓名',convert(decimal(5,2),avg(sc.grade)) as '平均分' from student st,sc where st.sno = sc.sno and st.sno not in(select distinct sno from sc where grade<=86 ) group by sname;

### 连接查询 join ###

--1.	 查询学生“李敏勇”，成绩大于80分的课程号、成绩
    select sc.cno as '课程号',sc.grade as '成绩' from student st,sc where st.sno = sc.sno and st.sname='李敏勇' and grade>80;
    --2.	 查询选课表中每位学生的平均分，显示学生姓名和平均分（注意重名情况）
    select st.sname,convert(decimal(4,2), avg(sc.grade)) from student st,sc where st.sno = sc.sno group by st.sno,st.sname;
    --3.	 查询选修“信息系统”课程的学生姓名
    select sname as '姓名' from student st,sc,course c where st.sno = sc.sno and sc.cno = c.cno and c.cname = '信息系统';
    --4.	 查询至少有一门课程成绩大于90分的学生的姓名
    select st.sname as '姓名'from student st,sc where st.sno = sc.sno and st.sno in(select sno from sc where grade>90 group by sno )group by st.sname;
    --5.	 查询数学考试超过75分的学生的总人数
    select count(sname) as '数学成绩超过75分的人数' from student st,sc,course c where st.sno = sc.sno and sc.cno = c.cno and grade>75 and cname ='数学';
    --6. 查询所有学生的成绩情况，显示的列名包括学生姓名、课程号、考试时间、成绩，注意结果集中也应包括未选修课程的学生。
    select sname as '姓名',cno as '课程号',testtime as '考试时间',grade as '成绩'from student st left join sc on st.sno = sc.sno;
    --7. 查询所有课程的成绩情况，显示的列名包括学生课程名称、学生学号、考试时间、成绩，注意结果集中也应包括未考试的课程。
    select cname as '课程名称',sno as '学生学号',testtime as '考试成绩',grade as '成绩' from sc right join course on sc.cno = course.cno;
    --8. 查询所有课程的选课情况，显示课程名、选课人数，如果没有人选，选课人数显示为0。
    select cname as '课程名',count(sc.sno) as '选课人数' from course c left join sc on sc.cno =c.cno group by cname;
    --9. 查询所有同学的选课情况，显示的列名包括学生姓名、课程名称、考试时间、成绩。
    select sname as '学生姓名',cname as '课程名称',testtime as '考试时间',grade as '成绩'  from student st, sc,course c where st.sno = sc.sno and sc.cno = c.cno;
    --10. 查询所在系相同的学生，显示的列名包括学生姓名、系名、相同系的学生姓名，剔除自己跟自己系相同的情况。
    select st1.sname,st1.sdept,st2.sname from student st1,student st2 where st1.sdept=st2.sdept and st1.sname != st2.sname