SQL不熟的题（合集版）1：函数，连接查询，建库，删表，删库，in（）
drop database StuManager（删库）
create database StuManager1（建库）
go（执行）
use StuManager（使用数据库，use 后面只能加库名）
create table teacherinfo(
TeacherID int primary key identity (1,1),
Tname nvarchar(30) not null,
Tcity nvarchar(30) default('山东'),
Ttype nvarchar(30)
)
create table classinfo(
ClassId int primary key identity(1,1),
Cname nvarchar(30) not null,
TeacherID int,
foreign key(TeacherID) references teacherinfo(TeacherID)
)
create table stuinfo(
Sid int primary key identity(1,1),
Sname nvarchar(30) not null,
ClassID int,
Sbirthday date,
Scity nvarchar(20) default('湖南'),
Saddress nvarchar(50),
Schengji decimal(18,2)
foreign key(ClassId) references classinfo (ClassId)
)
保存后，在打开数据库时，要把默认的库改为表所在的库
--1.使用sql语句给每张表添加5条数据
--插入教师表数据
insert into teacherinfo values('王兴','湖北 ','语文老师')
insert into teacherinfo values('王力','湖北 ','语文老师')
insert into teacherinfo values('黄飞鹰','湖北 ','体育老师')
insert into teacherinfo values('蔡徐坤','湖南 ','老师')
insert into teacherinfo values('高丽萍 ','湖南 ','语文老师')
--插入班级表数据
insert into classinfo values('软件一班',1)
insert into classinfo values('软件二班',2)
insert into classinfo values('软件三班',3)
insert into classinfo values('软件四班',4)
--插入学生表
insert into stuinfo values('王小胖',1,'1999-11-05','湘潭','九云社区',79.5)
insert into stuinfo values('李老四',2,'1995-10-05','广东','东莞',66)
insert into stuinfo values('王老五',2,'1996-07-05','深圳','深圳1',45.2)
insert into stuinfo values('王小胖',3,'1996-04-05','湘潭','九云社区',88.5)
insert into stuinfo values('张麻子',4,'1992-11-05','山东','莱芜',45.7)
insert into stuinfo values('王小胖',3,'1991-10-05','北京','颐和园',92.5)
--2.修改学生表学生ID为3的学生所在城市为北京
update stuinfo set Scity='北京'where Sid=3
select * from teacherinfo
select * from stuinfo
select * from classinfo
--3.将软件三班的老师改为王兴
update classinfo set TeacherID=(select TeacherID from teacherinfo where Tname='王力')
where classinfo.Cname='软件三班'
--4.将学生ID为5的信息删除掉
delete stuinfo where Sid=5
--5.查询所有学生的名称和出生年月日，使用别名起名
select Sname as '姓名',Sbirthday as '生日' from stuinfo
--6.查询学生ID在3-5之间的学生姓名，班级
select * from stuinfo where Sid between 3 and 5
select * from stuinfo where Sid >=3 and sid<= 5
--7.查询学生ID为3和5的姓名，班级
select sid,a.Sname,b.Cname from stuinfo a inner join classinfo b on a.ClassID=b.ClassId where Sid in(3,5)
--8.按照班级年龄进行倒序排序
select (year(GETDATE())-year(a.Sbirthday)) as age,* from stuinfo a order by age desc
--9.查询所有名字包含王的学生的信息和代课老师
select a.*,c.Tname from stuinfo a inner join classinfo b （分组查询时，查不出全部信息）
on a.ClassID=b.ClassId inner join teacherinfo c
on b.TeacherID=c.TeacherID where a.Sname like '%王%'
--10.查询所有姓王的学生信息和班级信息
--它要查学生信息和班级信息，所有要连接两张表
select * from stuinfo a inner join classinfo b
on a.ClassID=b.ClassId where a.Sname like '王%'
--11.查询名字为两个长度的所有老师信息
select * from teacherinfo where len(Tname)=2
--12.将所有学生的名称进行翻转
select *,REVERSE(Sname) as '翻转后' from stuinfo
--13.查询学生表名称包含王的信息及王的位置
select *,CHARINDEX('王',Sname) as '王的位置' from stuinfo
where Sname like '%王%'
--14.将学生表名称包含王的信息改为李（使用replace函数）
select *,replace(Sname,'王','李') as '替换后' from stuinfo
where Sname like '%王%'
--15.截取老师表所有老师的姓
select *,SUBSTRING(Tname,1,1) from teacherinfo
--16.将所有姓王的老师的改为姓李(使用stuff函数)
select *,stuff(Tname,1,1,'李') from teacherinfo
--17.查询每个学生的年龄
select (year(getdate())-year(Sbirthday)),Sname from stuinfo
--18.将所有学生的出生日期加上一个月
select *,DATEADD(MONTH,1,Sbirthday) from stuinfo
--19.将学生表的学生姓名和地址进行复制一张新表
select sname,scity into stu1 from stuinfo
select * from stu1
将学生姓名和地址 添加到新表 stu1 从老表 stuinfo
--20.使用插入多行语法一次性插入3条数据到学生表
insert into stuinfo (Sname,classid, sbirthday,Scity,saddress,Schengji)
select '张三1',2,'1999-11-11','北京','和平区',88.5 union
select '张三2',2,'1999-11-11','北京','和平区',88.5 union
select '张三3',2,'1999-11-11','北京','和平区',88.5 union
select '张三4',2,'1999-11-11','北京','和平区',88.5 union
select '张三5',2,'1999-11-11','北京','和平区',88.5
select * from stuinfo
insert into stuinfo values ('张三6',2,'1999-11-11','北京','和平区',88),
('张三7',2,'1999-11-11','北京','和平区',77),
('张三8',2,'1999-11-11','北京','和平区',66)
--21.统计每个班级的学生数量
//根据班级进行分组
select classid,COUNT(*) from stuinfo group by(ClassID)
select * from stuinfo
--22.查询带班班级最多的老师信息
--涉及到班级表和老师表
select top 1 a.TeacherID,b.Tname,count(*) as '带班班级数' from classinfo a inner join teacherinfo b
on a.TeacherID=b.TeacherID group by a.TeacherID,b.Tname
--23.查询每个班级里年龄最大的那个学生的信息
select a.ClassID,min(a.Sbirthday) from stuinfo a inner join classinfo b
on a.ClassID=b.ClassId group by a.ClassID
--24.按照学生所在城市进行分类，统计每个城市有多少名学生
select Scity,COUNT(*) from stuinfo group by Scity
--25.查询年龄在20到25岁之间的学生信息
select * from stuinfo where
year(getdate())-YEAR(Sbirthday)>20 and
year(getdate())-YEAR(Sbirthday)<25
--26.按照学生班级统计每个班级的平均分
select a.ClassID,avg(Schengji) from stuinfo a inner join classinfo b
on a.ClassID=b.ClassId group by a.ClassID
select * from classinfo
