今天学了SqlServer模糊查询、聚合函数和分组查询

学生信息管理系统

最新推荐文章于 2024-10-21 14:00:20 发布

原创最新推荐文章于 2024-10-21 14:00:20 发布 · 903 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍了一个学生信息管理系统的设计与实现过程，包括创建班级和学生信息表、插入数据、以及使用SQL查询来筛选和统计学生信息的方法。

use Student
create table ClassInfo(
id int identity(1,1) primary key,
name varchar(20) not null
)
create table StudentInfo1(
id int identity(1,1) primary key,
name varchar(20) not null,
gender varchar(5) check(gender in('男','女')),
city varchar(10) default('郑州'),
physics int,
mathmatics int,
classId int foreign key references ClassInfo(id)
)
insert into ClassInfo values('一年一班')
insert into ClassInfo values('一年二班')
insert into ClassInfo values('二年一班')
insert into ClassInfo values('二年二班')

alter table StudentInfo1
add age int check(age>6 and age<130)

insert into StudentInfo1 (name,age,gender,city,physics,mathmatics,classId)
values ('张三',16,'男','开封',76,87,1)
insert into StudentInfo1 (name,age,gender,city,physics,mathmatics,classId)
values ('张四',17,'男','开封',66,85,1)
insert into StudentInfo1 (name,age,gender,city,physics,mathmatics,classId)
values ('刘三',19,'男','新乡',97,81,2)
insert into StudentInfo1 (name,age,gender,city,physics,mathmatics,classId)
values ('刘四',19,'女','新乡',86,87,2)
insert into StudentInfo1 (name,age,gender,city,physics,mathmatics,classId)
values ('刘五',16,'女','开封',76,87,2)
insert into StudentInfo1 (name,age,gender,city,physics,mathmatics,classId)
values ('刘实在',16,'男','许昌',76,87,2)

select * from StudentInfo1 where age>=19 and gender='男'
select * from StudentInfo1 where name like '[^刘%]'
select * from StudentInfo1 where name like '%实%'
select * from StudentInfo1 where name like '_实_'
select * from StudentInfo1 where age between 16 and 18
select * from StudentInfo1 where city in ('开封','许昌')
select SUM(physics) as '物理总成绩',SUM(mathmatics) as '数学总成绩',SUM(physics+mathmatics) as '总成绩' from StudentInfo1
select SUM(mathmatics) as '数学总成绩' from StudentInfo1
select SUM(physics+mathmatics) as '总成绩' from StudentInfo1
select MAX(physics) as '物理最高分' ,min(mathmatics) as '数学最低分' ,AVG(physics) as '物理平均分' from StudentInfo1
select COUNT(*) as '物理及格人数（成绩>80分）' from StudentInfo1 where physics >80
select city as '城市',COUNT(*)as '人数' from StudentInfo1 group by city
select city as '城市',MIN(age) as '最小年龄' from StudentInfo1 group by city

order by MIN(age) desc

select COUNT(*) as '人数',gender,classid from StudentInfo1 group by classId, gender

select MIN(age) as '最小年龄',city as '城市' from StudentInfo1 group by city
having COUNT(*)>=2
order by MIN(age) asc