【SQL学习】select语句使用实例_sql编写教程select例子

前言

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

创建数据库及数据表

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

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

学生表. 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');

1
2
3
4
5
6
7
8
9
10
11
12
13

课程表. 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);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

学生选修表. 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');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

条件查询 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 ;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

使用统计函数

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;

1
2
3
4
5
6
7
8
9
10
11
12
13
14

分组统计查询 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; 

1
2
3
4
5
6
7
8
9
10
11
12
13

连接查询 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22