drop database StuManager（刪庫）

create database StuManager1（建庫）

go（執(zhí)行）

use StuManager（使用數(shù)據(jù)庫，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)

)

保存后，在打開數(shù)據(jù)庫時，要把默認(rèn)的庫改為表所在的庫

--1.使用sql語句給每張表添加5條數(shù)據(jù)

--插入教師表數(shù)據(jù)

insert into teacherinfo values('王興','湖北 ','語文老師')

insert into teacherinfo values('王力','湖北 ','語文老師')

insert into teacherinfo values('黃飛鷹','湖北 ','體育老師')

insert into teacherinfo values('蔡徐坤','湖南 ','老師')

insert into teacherinfo values('高麗萍 ','湖南 ','語文老師')

--插入班級表數(shù)據(jù)

insert into classinfo values('軟件一班',1)

insert into classinfo values('軟件二班',2)

insert into classinfo values('軟件三班',3)

insert into classinfo values('軟件四班',4)

--插入學(xué)生表

insert into stuinfo values('王小胖',1,'1999-11-05','湘潭','九云社區(qū)',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','湘潭','九云社區(qū)',88.5)

insert into stuinfo values('張麻子',4,'1992-11-05','山東','萊蕪',45.7)

insert into stuinfo values('王小胖',3,'1991-10-05','北京','頤和園',92.5)

--2.修改學(xué)生表學(xué)生ID為3的學(xué)生所在城市為北京

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.將學(xué)生ID為5的信息刪除掉

delete stuinfo where Sid=5

--5.查詢所有學(xué)生的名稱和出生年月日，使用別名起名

select Sname as '姓名',Sbirthday as '生日' from stuinfo

--6.查詢學(xué)生ID在3-5之間的學(xué)生姓名，班級

select * from stuinfo where Sid? ?between 3 and 5

select * from stuinfo where Sid >=3 and sid<= 5

--7.查詢學(xué)生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.按照班級年齡進(jìn)行倒序排序

select (year(GETDATE())-year(a.Sbirthday)) as age,* from stuinfo a order by? age desc

--9.查詢所有名字包含王的學(xué)生的信息和代課老師

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.查詢所有姓王的學(xué)生信息和班級信息

--它要查學(xué)生信息和班級信息，所有要連接兩張表

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.將所有學(xué)生的名稱進(jìn)行翻轉(zhuǎn)

select *,REVERSE(Sname) as '翻轉(zhuǎn)后' from stuinfo

--13.查詢學(xué)生表名稱包含王的信息及王的位置

select *,CHARINDEX('王',Sname) as '王的位置' from stuinfo

?where Sname like '%王%'

--14.將學(xué)生表名稱包含王的信息改為李（使用replace函數(shù)）

select *,replace(Sname,'王','李') as '替換后' from stuinfo

?where Sname like '%王%'

--15.截取老師表所有老師的姓

select *,SUBSTRING(Tname,1,1) from teacherinfo

--16.將所有姓王的老師的改為姓李(使用stuff函數(shù))

select *,stuff(Tname,1,1,'李') from teacherinfo

--17.查詢每個學(xué)生的年齡

select (year(getdate())-year(Sbirthday)),Sname from stuinfo

--18.將所有學(xué)生的出生日期加上一個月

select *,DATEADD(MONTH,1,Sbirthday) from stuinfo

--19.將學(xué)生表的學(xué)生姓名和地址進(jìn)行復(fù)制一張新表

select sname,scity into? stu1 from stuinfo?

select * from stu1

將學(xué)生姓名和地址 添加到新表 stu1 從老表 stuinfo

--20.使用插入多行語法一次性插入3條數(shù)據(jù)到學(xué)生表

insert into stuinfo (Sname,classid, sbirthday,Scity,saddress,Schengji)

select '張三1',2,'1999-11-11','北京','和平區(qū)',88.5 union

select '張三2',2,'1999-11-11','北京','和平區(qū)',88.5 union

select '張三3',2,'1999-11-11','北京','和平區(qū)',88.5 union

select '張三4',2,'1999-11-11','北京','和平區(qū)',88.5 union

select '張三5',2,'1999-11-11','北京','和平區(qū)',88.5?

select * from stuinfo

insert into stuinfo? ?values ('張三6',2,'1999-11-11','北京','和平區(qū)',88),

('張三7',2,'1999-11-11','北京','和平區(qū)',77),

('張三8',2,'1999-11-11','北京','和平區(qū)',66)

--21.統(tǒng)計每個班級的學(xué)生數(shù)量

//根據(jù)班級進(jìn)行分組

select classid,COUNT(*) from stuinfo group by(ClassID)

select * from stuinfo

--22.查詢帶班班級最多的老師信息

--涉及到班級表和老師表

select top 1 a.TeacherID,b.Tname,count(*) as '帶班班級數(shù)' from classinfo a inner join teacherinfo b

on a.TeacherID=b.TeacherID group by a.TeacherID,b.Tname

--23.查詢每個班級里年齡最大的那個學(xué)生的信息

select? a.ClassID,min(a.Sbirthday)? from stuinfo a inner join classinfo b

on a.ClassID=b.ClassId group by a.ClassID

--24.按照學(xué)生所在城市進(jìn)行分類，統(tǒng)計每個城市有多少名學(xué)生

select Scity,COUNT(*) from stuinfo group by Scity

--25.查詢年齡在20到25歲之間的學(xué)生信息

select * from stuinfo where?

year(getdate())-YEAR(Sbirthday)>20 and?

year(getdate())-YEAR(Sbirthday)<25

--26.按照學(xué)生班級統(tǒng)計每個班級的平均分

select a.ClassID,avg(Schengji) from stuinfo a inner join classinfo b?

on a.ClassID=b.ClassId group by a.ClassID

select * from classinfo