当前位置： 首页 > news >正文

sqlserver小练习

news 2024/10/23 3:39:00

sqlserver小练习

USE Student_info1;
-- 建表
DROP TABLE STUDENT;
DROP TABLE COURSE;
--创建student表
create table student
(sno		int primary key identity(1, 1),sname	nvarchar(10) not null,ssex	nchar(1) not null,birthday date not null,en_time  date not null,specialty nvarchar(10) not null,grade tinyint check(grade>=1 and grade<=4)
)
-- 创建course表
create table course
(cno		int primary key identity(1, 1),cname	nvarchar(10) not null,classhour int not null,credit tinyint not null
)
-- 创建scoreinfo表
create table scoreinfo
(sno		int,cno		int,score	decimal(4, 1) not null,constraint pk_sc primary key(sno, cno),constraint lo_li foreign key(sno) references student(sno),constraint li_lo foreign key(cno) references course(cno)
)
-- 插入数据-- 给student表插入数据
insert into student(sname, ssex, birthday, en_time, specialty, grade)
values
('loli1', '女', '2010-06-21'	, '2018-09-01', '网络工程', 2),
('loli2', '男', '2011-02-01'	, '2019-06-01', '网络工程', 1),
('loli3', '女', '2010-03-21'	, '2018-09-01', '网络工程', 2),
('loli4', '男', '2012-06-23'	, '2016-04-01', '网络工程', 4),
('loli5', '女', '2009-04-17'	, '2018-05-01', '网络工程', 2),
('loli6', '女', '2009-04-17'	, '2018-05-01', '计算机', 2),
('loli7', '女', '2009-04-17'	, '2018-05-01', '计算机', 2),
('loli8', '男', '2009-04-17'	, '2018-05-01', '计算机', 4)-- 给course表插入数据
insert into course(cname, classhour, credit)
values
('C++', 3, 4), ('English', 4, 5), ('操作系统', 5, 6)-- 给scoreinfo表插入数据
insert into scoreinfo
values
(3, 1, 90), (3, 2, 88), (3, 3, 99),
(4, 1, 56), (4, 2, 78), (4, 3, 49),
(5, 1, 80), (5, 2, 88), (5, 3, 89),
(6, 1, 30), (6, 2, 45), (6, 3, 45),
(7, 1, 76), (7, 2, 81), (7, 3, 78)SELECT * FROM STUDENT;
SELECT * FROM COURSE;
SELECT * FROM scoreinfo;SELECT SUM(CASE WHEN SNO <> 0 THEN 1 ELSE 0 END),AVG(CASE WHEN SCORE <=100 THEN SCORE ELSE 0 END),AVG(SCORE)FROM scoreinfo;--日期函数
SELECT GETDATE()
SELECT CONVERT(VARCHAR(10),GETDATE(),102)
SELECT CONVERT(VARCHAR(10),GETDATE(),105)
SELECT DATEDIFF(hh,'2024-10-10 21:20:00',GETDATE())
SELECT DATEDIFF(MI,'2024-10-10 22:20:00',GETDATE())
SELECT DATEDIFF(yyyy,'2023-10-10 22:20:00',GETDATE())
SELECT DATEDIFF(mm,'2023-10-10 22:20:00',GETDATE())
SELECT DATEDIFF(dd,'2023-10-10 22:20:00',GETDATE())--DATAPART
/* 
1. datepart：指定要提取的日期部分的参数，可以是以下值之一：year：年份部分quarter：季度部分（1 到 4）month：月份部分（1 到 12）dayofyear：年中的第几天（1 到 366）day：月中的第几天（1 到 31）week：年中的第几周weekday：周中的第几天（1 = Sunday，2 = Monday，依此类推）hour：小时部分（0 到 23）minute：分钟部分（0 到 59）second：秒部分（0 到 59）millisecond：毫秒部分
2. date：要从中提取日期部分的日期表达式，通常是一个 datetime 或者 date 数据类型的列名、变量或者常量。
*/
SELECT DATEPART(YEAR,GETDATE());
SELECT DATEPART(MONTH,GETDATE());
SELECT DATEPART(DAY,GETDATE());--CEILING 向上取整函数，向上取整到最接近的整数或指定最接近整数的倍数
SELECT CEILING(2.1)
--SELECT CEILING(2,1)--1.查询teaching数据库中学生的姓名、性别和专业
SELECT SNAME,SSEX,SPECIALTY FROM STUDENT;--2.查询teaching库中student表的专业名称，滤掉重复行
SELECT DISTINCT SPECIALTY FROM STUDENT--3.查询teaching库中course表的前三行信息
SELECT TOP 3 * FROM COURSE--4.查询course表前59%行的信息（之后的题目都省略了“teaching库中”）
SELECT TOP 59 PERCENT * FROM COURSE--5.查询student表中所有学生的学号、姓名,并指定列名为“学号”，“姓名”
SELECT SNO AS 学号,SNAME AS 姓名
FROM STUDENT--6.查询scoreInfo表，按150分制计算成绩
SELECT SNO,CNO,SCORE*1.5 AS GREAD FROM SCOREINFO;--7.查询scoreInfo表中成绩大于60的学生的学号、课程号、成绩
SELECT SNO,CNO,SCORE FROM SCOREINFO WHERE SCORE > 60;--8.查询网络工程专业的男生的信息SELECT * FROM STUDENT WHERE specialty='网络工程' AND ssex='男'--9.查询是网络工程专业的，或，是男生，的学生的信息SELECT * FROM STUDENT WHERE specialty='网络工程' OR ssex='男'--10.查询成绩在80~90之间的学生的学号、课程号、成绩SELECT  * FROM scoreinfo WHERE SCORE BETWEEN 80 AND 90SELECT * FROM scoreinfo WHERE SCORE>=80 AND SCORE<=90--11.查询成绩不在80~90之间的学生的学号、课程号、成绩SELECT  * FROM scoreinfo WHERE SCORE NOT BETWEEN 80 AND 90SELECT * FROM scoreinfo WHERE SCORE<80 OR SCORE>90--13.查询网络工程专业和通信工程专业的学生的姓名、学号、专业SELECT SNAME,SNO,specialty FROM STUDENT WHERE specialty IN ('网络工程','通信工程')--14.查询所有成绩为不空值的学生的学号，课程号，成绩
SELECT SNO,CNO,SCORE FROM scoreinfo WHERE SCORE IS NOT NULL--15.从student表中查询所有网络工程和通信工程的女生的信息
SELECT * FROM STUDENT WHERE specialty IN ('网络工程','通信工程') AND SSEX='女'--16.查询scoreInfo表中成绩额的平均值，平均值显示列标题为“平均成绩”
SELECT AVG(SCORE) AS 平均成绩 FROM SCOREINFO--17.从student表中查询专业的种类个数
SELECT COUNT(DISTINCT specialty)FROM STUDENT --18查询4号学生的平均成绩和最高成绩
SELECT AVG(SCORE) 平均成绩,MAX(SCORE) 最高成绩 FROM scoreinfo WHERE SNO=4--19.查询男生和女生的个数
select SSEX,COUNT(*) from STUDENT GROUP BY SSEXSELECTSUM(CASE WHEN SSEX='男' THEN 1 ELSE 0 END) 男生,SUM(CASE WHEN SSEX='女' THEN 1 ELSE 0 END) 女生from student--20.在scoreInfo表中查询选修了两门及以上课程的学生学号和选课数SELECT SNO,COUNT(*) FROM SCOREINFO GROUP BY SNO HAVING COUNT(*)>=2 --SNO是分组依据，COUNT是每组一个的，--HAVING是对分组后再进行筛选--21.查询scoreInfo表，求被选修的各门课程的平均成绩和选修该课程的人数，以及所有课程的总平均成绩和总选修人数
SELECT * FROM SCOREINFO
SELECT CNO,AVG(SCORE),COUNT(*) FROM SCOREINFO GROUP BY CNO
SELECT AVG(SCORE),COUNT(CNO) FROM SCOREINFO select cno, AVG(score), COUNT(*)
from scoreinfo 
group by cno with cube  --WITH CUBE--22.查询student表，统计各专业男生、女生人数及每个专业学生人数,男生总人数、女生总人数，以及所有学生总人数
SELECT * FROM STUDENT
SELECT SPECIALTY,SSEX,COUNT(*) FROM STUDENT GROUP BY specialty,SSEX WITH CUBEselect specialty, ssex, grade, count(*)
from student
group by specialty, ssex,grade with cube--(★重难点)
-- cube用在group by之后，作用为：不仅根据分组依据进行分组统计，再根据分组依据的所有子集(包括空集)进行分组统计
-- demo: 分组依据为 a, b, c，则结果集多了很多行统计结果分组依据分别为(a, b, c)(a, b)(a, c)(a, c)(a)(b)(c)(空)--23.统计每个专业的男女生人数，每个专业的总人数和所有学生的总人数
SELECT SPECIALTY,SSEX,COUNT(*) FROM STUDENT GROUP BY specialty,SSEX WITH ROLLUP
--(★重难点)
-- rollup用在group by之后，作用为：和cube类似，但不是所有子集，而是从最低级别的分组依据开头的子集和空集
-- demo: 分组依据为 a, b, c，则结果集多了很多行统计结果分组依据分别为(a, b, c)(a, b)(a)(空)--24.查询每个学生的姓名、课程号、成绩
SELECT * FROM STUDENT
SELECT * FROM COURSE
SELECT * FROM scoreINFOSELECT a.SNAME,b.cno,b.score 
FROM STUDENT a 
left join scoreinfo b on a.sno=b.snoselect sname, cno, score
from scoreinfo												
inner join student on student.sno = scoreinfo.sno--25.查询网络工程专业的学生所选的每门课的平均分
select b.cno,avg(b.score) from student a
inner join scoreinfo b
on a.sno=b.sno where a.specialty='网络工程'
group by b.cno--26.查询同名学生的信息
select * from student a join student b on a.sname=b.sname and a.sno<>b.sno--27.查询每个学生及其选修课程的成绩情况（含未选课的学生信息）
select * from student a left join scoreINFO b on a.sno=b.sno left join COURSE c on b.cno=c.cnoselect * from student a left join scoreINFO b on a.sno=b.sno--28.查询每个学生及其选修课程的情况（含未选课的学生信息及未被选修的课程信息）
select * from student a left join scoreINFO b on a.sno=b.sno left join COURSE c on b.cno=c.cnoselect student.*, course.*
from scoreinfo
full join student on student.sno = scoreinfo.sno
full join course on course.cno = scoreinfo.cno	-- 29.查询成绩在75分以上的学生的学号、姓名，选修课的课程号、成绩
select a.sno,a.sname,b.cno,b.score from student a left join scoreINFO b on a.sno=b.sno
where b.score>75select student.sno, sname, course.cno, score
from scoreinfo
inner join student on student.sno = scoreinfo.sno
inner join course on course.cno = scoreinfo.cno
where score > 75-- 30.查询所有学生可能的选课情况
select sname, cname
from student 
cross join course	--31.查询与loli1同一个专业的学生的学号、姓名
select specialty from student where sname='loli1'
select sno,sname from student where specialty=(select specialty from student where sname='loli1')--32.查询课程号为3的考试成绩比loli2高的学生的学号和姓名
select b.score from student a left join scoreinfo b on a.sno=b.sno 
where a.sname='loli4' and b.cno=3select distinct a.sno,a.sname from student a left join scoreinfo b on a.sno=b.sno 
where b.score>(select b.score from student a left join scoreinfo b on a.sno=b.sno 
where a.sname='loli4' and b.cno=3)select student.sno, sname 
from student
inner join scoreinfo on student.sno = scoreinfo.sno
where cno = 3
and score > (select score from scoreinfo where cno = 3 and sno = (select sno from student where sname = 'loli4'))--33.查询网络工程专业年龄最大的学生的姓名、学号
select sname,sno from student where specialty='网络工程' 
and birthday<=all(select birthday from student where specialty='网络工程')--34.查询与任何一个网络工程专业同学同龄的学生的信息
select sname,sno from student where  
birthday<=any(select birthday from student where specialty='网络工程')
--★上面的两道题使用看新的知识：使用all, any(some)的子查询
-- 顾名思义，all是指“所有值”，any(some)是指“只要有一个值”；弄清其使用的场景，真的可以省很多事--35.查询选修了1号课程的学生姓名和所在专业
select sname,specialty from student where sno = (select sno from scoreinfo where cno=1)
--用等于会出错，因为子查询返回多个值
select sname,specialty from student where sno in (select sno from scoreinfo where cno=1)--36.查询有两个以上学生平均成绩超过80分的班级（专业+年级）
--先找出平均成绩超过80分的学生
select sno from scoreinfo group by sno HAVING AVG(SCORE)>80select specialty,grade from student 
where sno in (select sno from scoreinfo group by sno HAVING AVG(SCORE)>80)
group by specialty,grade
having count(*)>=2
--1.先select出平均成绩大于80的所有学生的学号：select sno from scoreinfo group by sno having avg(score) > 80
--2.上面子查询的结果用在了主查询中（in的使用）
--3.子查询和主查询都用到了having（是关键点也是难点）
-- 关于group by...having结构，明白一点问题就变得简单了：分组依据就是select后紧跟的要查询的列名
-- 上面讲的都是【无关子查询】———— 子查询只用一次，也只执行一次
-- 下面讲的是【相关子查询】———— 子查询重复执行，外部查询每遍历一行，子查询就执行一次
-- 【相关子查询】有个极为显著的特点：就是因为子查询需要用到外部查询的表(“相关”)，因此【外部查询用的表和内部查询用的表有联系且都有别名！！！】--37.查询成绩比该课的平均成绩低的学生的学号、课程号、成绩
select cno,avg(score) from scoreinfo group by cno select a.sno,a.cno,a.score from scoreinfo a 
where a.score<(select avg(score) from scoreinfo b where a.cno=b.cno) --38.查询两门及以上课程的成绩在80分以上(包括80)的学生的学号、姓名、年级、专业
select sno from scoreinfo where score>=80 group by sno having count(*)>=2select sno,sname,grade,specialty from student where sno in (select sno from scoreinfo 
where score>=80 group by sno having count(*)>=2)
--使用相关子查询方法
select sno,sname,grade,specialty from student a 
where (select count(*) from scoreinfo b where a.sno=b.sno and b.score>=80)>=2 --39.查询所有选修了1号课程的学生姓名
select sname from student a where  exists (select sno from scoreinfo b where a.sno=b.sno and b.cno=1)--40.查询网络工程专业没有选修C++的学生的学号和姓名
select sno,sname from student select cno from course where cname='C++'select sno from scoreinfo where cno=(select cno from course where cname='C++')
--最后代码
select sno,sname from student 
where sno not in (select sno from scoreinfo where cno=(select cno from course where cname='C++'))
and specialty='网络工程'select HOST_NAME()
--从字符串右边返回指定数目的字符
select right('123456',5)
select left('123456',5)--返回传入给它的字符串长度
select len('B123 456')
select right('B123 456',LEN('B123 456')-1)--清楚字符串左右边空格
select ltrim(' 123456 1 ')
select rtrim(' 123456 1 ')--转变数据类型
select * from student
select birthday,convert(varchar(10),birthday,102)  as 转换后的日期
FROM STUDENT


http://www.mrgr.cn/news/56481.html

相关文章：

  • 【前端】如何制作一个自己的网页（15）
  • SpringBoot 单元测试 - 登录认证在 Spring Boot 上的标准单元测试写法。
  • wiki搭建
  • 黑盒测试和白盒测试的具体方法（附加实际应用中的技巧和注意事项）
  • “新物种”即将上线，极氪MIX是近几年最“好玩”的新车？
  • 深入浅出理解玻尔兹曼分布及其广泛应用
  • 基于Multisim三极管B放大系数放大倍数测量电路设计（含仿真和报告）
  • 手机功耗技术领域
  • Java 实现协同过滤算法推荐算法
  • ecmascript标准
  • Python|基于Kimi大模型，实现上传文档并进行“多轮”对话（7）
  • 【C++刷题】力扣-#350-两个数组的交集II
  • 【校园小情书微信小程序源码】
  • 运用AI实践｜如何从AI工具提升工作效率实践
  • 前端算法：字典and哈希表（力扣1题、349题解法）
  • AUTOSAR_EXP_ARAComAPI的6章笔记（2）
  • 在做题中学习（65）：Z字形变换
  • spring源码拓展点3之addBeanPostProcesser
  • 2024年9月 GESP CCF C++五级编程能力等级考试认证真题
  • 【四】企业级JavaScript开发开发者控制台
  • Q宠大乐斗鹅号提取器（基于python实现）
  • 动态规划之路径问题
  • 基于MATLAB(DCT DWT)
  • 在做题中学习（66）：两数相加
  • 每日OJ题_牛客_字符串分类_哈希+排序_C++_Java
  • 算法Day-7