SQL 简单查询

1.count

2.top

3.having

4.case

use mastergoif db_id('SchoolDB') is not nulldrop database SchoolDBcreate database SchoolDBuse SchoolDB go-- 学生信息表if OBJECT_ID(N'StuInfo',N'U') is not null        -- 如果该表已经存在，则现删除再建立drop table  StuInfocreate table StuInfo(StuID int primary key,  -- 学号StuName nvarchar(10),   -- 姓名StuCity nvarchar(50),   -- 所在城市StuAge int,             -- 年龄MajorID int          -- 专业编号);-- 专业信息表if OBJECT_ID(N'MajorInfo',N'U') is not null     -- 如果该表已经存在，则先删除该表，再重新建立该表drop table MajorInfocreate table MajorInfo(MajorID int primary key not null,-- 专业编号MajorName nvarchar(30)           -- 专业名称);-- 课程表if OBJECT_ID(N'CourseInfo',N'U') is not null    -- 如果该表已经存在，则先删除再建立drop table CourseInfocreate table CourseInfo(CourseID int not null,      -- 课程编号CourseName nvarchar(20)     -- 课程名称);-- 选课表if OBJECT_ID(N'SelCourse',N'U') is not nulldrop table SelCoursecreate table SelCourse(StuID int not null,               -- 学生学号CourseID int not null,            -- 课程编号Score float                       -- 课程分数);-- 查看所有约束select *from sysobjects where parent_obj in(select id from sysobjects where name='SelCourse');-- 添加主键约束alter table CourseInfo add constraint PK_CourseInfo primary key(CourseID);alter table SelCourse add constraint PK_SelCourse primary key(StuID,CourseID);-- 添加外键约束alter table  StuInfo add constraint FK_StuInfo_MajorInfo foreign key (MajorID) references MajorInfo;alter table SelCourse add constraint FK_CourseInfo foreign key(CourseID) references CourseInfo;alter table SelCourse add constraint FK_SelCourse_StuInfo foreign key(StuID) references StuInfo;-- 删除主键alter table SelCourse drop constraint PK_SelCourse;-- 添加数据-- 向StuInfo添加数据insert into StuInfo(StuID,StuName,StuCity,StuAge,MajorID)values(1001,'Hello','江西南昌',21,20001); -- 哲学insert into StuInfo(StuID,StuName,StuCity,StuAge,MajorID)values(1002,'Amli','巴基斯坦',22,20006);  -- 物理学insert into StuInfo(StuID,StuName,StuCity,StuAge,MajorID)values(1003,'War','阿欧斯',21,20005);     -- 数学insert into StuInfo(StuID,StuName,StuCity,StuAge,MajorID)values(1004,'Warys','阿修罗',24,20004);   -- 化学insert into StuInfo(StuID,StuName,StuCity,StuAge,MajorID)values(1005,'Piesf','北京',14,20003);     -- 文学insert into StuInfo(StuID,StuName,StuCity,StuAge,MajorID)values(1006,'Fusnas','上海',34,20002);    -- 音乐insert into StuInfo(StuID,StuName,StuCity,StuAge,MajorID)values(1007,'Mlks','南昌',12,20002);      -- 音乐    insert into StuInfo(StuID,StuName,StuCity,StuAge,MajorID)values(1008,'Pols','海南',36,20000);      -- 天文学insert into StuInfo(StuID,StuName,StuCity,StuAge,MajorID)values(1009,'oosa','龙南',26,20001);      -- 哲学insert into StuInfo(StuID,StuName,StuCity,StuAge,MajorID)values(1010,'ddawa','长沙',25,20004);     -- 化学insert into StuInfo(StuID,StuName,StuCity,StuAge,MajorID)values(1011,'Dwad','常熟',24,20005);      -- 数学insert into StuInfo(StuID,StuName,StuCity,StuAge,MajorID)values(1012,'Osjsa','美国',23,20006);     -- 物理学insert into StuInfo(StuID,StuName,StuCity,StuAge,MajorID)values(1013,'Posangdas','纽约',28,20004); -- 化学insert into StuInfo(StuID,StuName,StuCity,StuAge,MajorID)values(1014,'Yhndsg','休斯敦',21,20001);  -- 哲学insert into StuInfo(StuID,StuName,StuCity,StuAge,MajorID)values(1015,'Cgajjd','印度',55,20003);    -- 文学-- 查询select*from StuInfo;-- 插入专业表insert into MajorInfo(MajorID,MajorName)values(20001,'哲学');insert into MajorInfo(MajorID,MajorName)values(20002,'音乐艺术');insert into MajorInfo(MajorID,MajorName)values(20003,'文学');insert into MajorInfo(MajorID,MajorName)values(20004,'化学');insert into MajorInfo(MajorID,MajorName)values(20005,'数学');insert into MajorInfo(MajorID,MajorName)values(20006,'物理学');insert into MajorInfo(MajorID,MajorName)values(20007,'核物理学');insert into MajorInfo(MajorID,MajorName)values(20009,'计算机科学');insert into MajorInfo(MajorID,MajorName)values(20000,'天文学');-- 查询select *from MajorInfo;-- 插入课程表insert into CourseInfo(CourseID,CourseName) values(101,'大学英语');insert into CourseInfo(CourseID,CourseName) values(102,'信号与系统');insert into CourseInfo(CourseID,CourseName) values(103,'高等数学');insert into CourseInfo(CourseID,CourseName) values(104,'数字电路基础');insert into CourseInfo(CourseID,CourseName) values(105,'电机学');-- 查询select *from CourseInfo;-- 插入选课表insert into SelCourse(StuID,CourseID,Score) values(1001,101,89.5);insert into SelCourse(StuID,CourseID,Score) values(1002,102,99.5);insert into SelCourse(StuID,CourseID,Score) values(1003,103,87.9);insert into SelCourse(StuID,CourseID,Score) values(1004,104,76.7);insert into SelCourse(StuID,CourseID,Score) values(1005,105,99.2);insert into SelCourse(StuID,CourseID,Score) values(1006,101,68.7);insert into SelCourse(StuID,CourseID,Score) values(1007,102,54.6);insert into SelCourse(StuID,CourseID,Score) values(1008,103,12.3);insert into SelCourse(StuID,CourseID,Score) values(1009,104,90);insert into SelCourse(StuID,CourseID,Score) values(10010,105,100);insert into SelCourse(StuID,CourseID,Score) values(10011,101,90.2);insert into SelCourse(StuID,CourseID,Score) values(10012,102,98.2);insert into SelCourse(StuID,CourseID,Score) values(10013,103,66.5);insert into SelCourse(StuID,CourseID,Score) values(10014,104,77.8);insert into SelCourse(StuID,CourseID,Score) values(10015,105,89.2);insert into SelCourse(StuID,CourseID,Score) values(1001,101,89.4);insert into SelCourse(StuID,CourseID,Score) values(1002,102,89.5);insert into SelCourse(StuID,CourseID,Score) values(1003,103,99.3);insert into SelCourse(StuID,CourseID,Score) values(1004,104,92);insert into SelCourse(StuID,CourseID,Score) values(1005,105,93);use SchoolDBgo-- 1、count-- count()函数返回匹配指定条件的行数-- 1) 查询学上总数select *from StuInfo;select COUNT(*) '学生总数' from StuInfo;select COUNT(StuID) from StuInfo;select COUNT(StuAge) from StuInfo;-- 2) 查询学生年龄分布的总数,例如'北京市'select distinct StuAge as '年龄阶段'from StuInfowhere StuCity='南昌';-- 3) 查询在南昌的同学select StuCity,StuNamefrom StuInfowhere StuCity like '%南昌%';-- 4) 查询各城市共有多少人select StuCity as '城市',COUNT(*) as '人数'from StuInfogroup by StuCity;-- 2.having-- 在SQL中增加having子句原因是,where关键字无法与合计函数一起使用-- 格式:/*select column_name,aggregate_function(column_name)from table_namewhere column_name operator valuegroup by column_namehaving aggregate_function(column_name) operator value*/-- 1) 查询平均成绩大于等于60的学生ID，以及平均成绩select *from SelCourse;select StuID as 学号,AVG(Score) as 分数from SelCoursegroup by StuIDhaving AVG(Score)>=60;-- 2) 查询平均成绩大于等于60，且学生ID等于8001605的学生ID及平均成绩select StuID,AVG(Score)from SelCoursewhere StuID = '1001'group by StuIDhaving AVG(Score)>=60;-- 3) 查询总成绩在90以上select StuID,Scorefrom SelCoursegroup by StuID,Scorehaving SUM(Score)>=90;-- 3、top-- top子句用于规定要返回的记录的数目。对大数据很有用，在分页的时候也会常常用到-- 查询前几个的记录select *from StuInfo;-- 1) 查询年龄最大的3名学生信息-- 首先要先排序select top 3 StuAge,StuName,StuID -- 查询前三个from StuInfo                      -- 从学生信息表中查询order by StuAge DESC;             -- 降序排序-- 与前面3条记录相等的记录都可以输出select *from StuInfowhere StuAge in(select top 3 StuAge from StuInfoorder by StuAge DESC);-- 2) 查询年龄最小的5个同学，如果有相同的年龄的该如何处理select top 5 StuAge,StuName,StuIDfrom StuInfoorder by StuAge;-- 4.case 语句-- 计算条件列表，并返回多个可能的结果表达式之一-- case表达式有两种模式：--  简单表达式，它通过将表达式与一组简单的表达式进行比较来确定结果--  搜索表达式，它通过计算一组布尔表达式来确定结果-- 简单表达式/*case input_expressionwhen when_expression then result_expression [....][else else_result_expression]end*/-- 搜索式语法/*case when boolean_expression then result_expression [....][else else_result_expression]end*/-- 1) 查询学生信息，如果城市为南昌市则显示NC，如果为台湾则显示为TW，其他的则显示为其他select *from StuInfo;select StuID,StuAge,StuCity,MajorID,case StuCitywhen '南昌' then 'NC' when '台湾' then 'TW'else '其他'end as 城市拼音首写字母from StuInfo;-- 2)查询学生信息，根据年龄来统计是否成年，大于等于18为成年，小于18为未成年select *,case                            when StuAge>=18 then '成年'else '未成年'end as '是否成年'from StuInfo;