SQL

/*科目信息 */
drop table subinfo
create table subinfo(
 Objid int identity(1,1) not null,
 Objname varchar(20) null,
 Objexplain varchar(50) null
)

insert into subinfo(Objname,Objexplain) values ('数学','计算，微机分')
insert into subinfo(Objname,Objexplain) values ('语文','文言文')
insert into subinfo(Objname,Objexplain) values ('地理','世界地理')
insert into subinfo(Objname,Objexplain) values ('历史','中国历史')

/*班级表 */
drop table classinfo
create table classinfo(
 Objid int identity(1,1) not null,
 Objname varchar(20) null,
 Objexplain varchar(255) null,
 Teacherid varchar(20) null,
)

insert into classinfo(Objname,Objexplain,Teacherid) values ('121','理科班','李老师')
insert into classinfo(Objname,Objexplain,Teacherid) values ('122','文科班','王老师')
insert into classinfo(Objname,Objexplain,Teacherid) values ('123','理科班','高老师')
insert into classinfo(Objname,Objexplain,Teacherid) values ('124','文科班','龙老师')
insert into classinfo(Objname,Objexplain,Teacherid) values ('124','文科班',NULL)

select count(*) from classinfo

/*班级科目表 */
drop table classub
create table classub(
 Classid int not null,
 Subjectid int null,
 Teacherid varchar(20) null
)
insert into classub(Classid,Subjectid,Teacherid) values (2,1,'订单师')
insert into classub(Classid,Subjectid,Teacherid) values (2,1,'张老师')
insert into classub(Classid,Subjectid,Teacherid) values (3,2,'蒋老师')
insert into classub(Classid,Subjectid,Teacherid)values (1,3,'李老师')
insert into classub(Classid,Subjectid,Teacherid) values (2,4,'高老师')
insert into classub(Classid,Subjectid,Teacherid) values (4,1,'龙老师')
insert into classub(Classid,Subjectid,Teacherid) values (1,2,'王老师')

SELECT *FROM CLASSUB

SELECT SUBJECTID  FROM CLASSUB WHERE SUBJECTID <3

select * From (select Subjectid,(select min(Subjectid) from classub where t.Subjectid<Subjectid) Subjectid2 from classub t) t where Subjectid-Subjectid2>1

/*教师表 */
drop table teainfo
create table teainfo(

 Username varchar(20) null,
 Objjc varchar(50) null,
 Sex varchar(2) null
)

insert into teainfo(Username,Objjc,Sex) values ('李老师','语言老师，121 班主任','女')
insert into teainfo(Username,Objjc,Sex)values ('王老师','化学老师，122 班主任','男')
insert into teainfo(Username,Objjc,Sex) values ('高老师','数学老师，123 班主任','女')
insert into teainfo(Username,Objjc,Sex) values ('龙老师','物理老师，124 班主任','男')
insert into teainfo(Username,Objjc,Sex) values ('唐老师','历史老师','男')
insert into teainfo(Username,Objjc,Sex) values ('蒋老师','地理老师','女')
insert into teainfo(Username,Objjc,Sex) values ('罗老师','地理老师','女')

select * From teainfo

/*学生表 */
drop table stuinfo
create table stuinfo(

 Username varchar(20) null,
 Objjc varchar(50) null,
 Sex varchar(2) null,
 Classid varchar(20) null
)

insert into stuinfo(Username,Objjc,Sex,Classid) values ('李明','文科','女','121')
insert into stuinfo(Username,Objjc,Sex,Classid)values ('王二','理科','男','122')
insert into stuinfo(Username,Objjc,Sex,Classid) values ('老三','文科','女','123')
insert into stuinfo(Username,Objjc,Sex,Classid) values ('李明','理科','男','124')
insert into stuinfo(Username,Objjc,Sex,Classid) values ('唐三','文科','男','125')
insert into stuinfo(Username,Objjc,Sex,Classid) values ('李四','理科','女','126')
insert into stuinfo(Username,Objjc,Sex,Classid) values ('张三','理科','男','126')

select count(*) from stuinfo

 

/*显示班级信息及班主任*/
select a.Objname as 学生班级,a.Objexplain as 学生科目,a.Teacherid as 任教老师,b.Objjc as 老师名称,b.Sex as 老师性别
from classinfo a
join teainfo b on a.Teacherid=b.Username

/*显示所有班级的科目和任课老师*/
select a.Objname as 科目 ,b.Teacherid as 任课老师 from subinfo a join classub b on a.Objid=b.Subjectid

/*显示所有教师信息*/
select Username as 名字,Objjc as 名称,Sex as 性别 From teainfo

/*显示所有学生信息和所在班级信息*/
select a.Username as 学生姓名,a.Objjc as 学生科目,a.Sex as 学生性别,b.Objname as 所在班级,b.Objexplain as 科目,b.Teacherid as 教师
from stuinfo a
join classinfo b on a.Classid=b.Objname

/*显示是班主任的教师*/
select ISNULL(Teacherid,0) AS 班主任 from classinfo where Teacherid<>'0'

select Teacherid AS 班主任 from classinfo where Teacherid<>'NULL'

/*不用not in任课老师的教师*/
select a.UserName as 老师 from teainfo a
left join classinfo b on b.Teacherid= a.Username
left join  classub c on  a.Username=c.Teacherid
where b.Teacherid is not NULL and c.Teacherid is not NULL

select Username from teainfo where Username
not in(select isnull(Teacherid,0) from classinfo)
and Username not in(select isnull(Teacherid,0) from classub)

 

 

/*没有用到NOT IN left连接*/
select a.UserName as 老师  from teainfo a left join classinfo b on b.Teacherid= a.Username
left join  classub c on  a.Username=c.Teacherid
where b.Teacherid is NULL and c.Teacherid is not NULL

/*没有用到NOT IN right连接*/
select c.Teacherid as 老师 from teainfo a right join classinfo b on b.Teacherid= a.Username
right join  classub c on  a.Username=c.Teacherid
where b.Teacherid is NULL and c.Teacherid is not NULL

select c.Teacherid as 老师 from teainfo a right join classinfo b on b.Teacherid= a.Username
right join  classub c on  a.Username=c.Teacherid
where b.Teacherid is NULL and c.Teacherid is not NULL

 

CREATE   INDEX  _index_dd   ON   classinfo(Teacherid) 

drop index classinfo._index_dd

select

/*显示是任课老师的教师*/
select Username from teainfo where Username not in (select isnull(Teacherid,0) from classinfo) and Username in (select Teacherid from classub)

/*显示出不是任课老师也不是班主任的老师*/
select Username from teainfo where Username not in (select isnull(Teacherid,0) from classinfo) and Username not in (select Teacherid from classub)

select count(*) from classinfo
select count(*) from subinfo
select count(*) from classub
select count(*) from stuinfo

select * from classinfo
select * from subinfo
select * from classub
select * from stuinfo

select a.Teacherid from classinfo a left join

select isnull(Teacherid,0) from classinfo where  Teacherid not in (select isnull(Teacherid,0) from classub)

declare @d datetime
set @d=getdate()

SQL 语句

select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

/*求共有几个班级,几位教师,几位学生*/
select 班级=(select count(*) from  classinfo), 教师=(select count(*) from teainfo),学生=(select count(*) from stuinfo)

/*显示班级里面的学生数量*/

select 班级=Classid,学生数量=sum(case 
   when Classid=Classid  then 1
   else 0
   end
  )
from stuinfo group by Classid

select * From stuinfo

 

 

if exists (select * From sysindexes where name='IX_CLASS_TNAME')
drop index classinfo.IX_CLASS_TNAME

create nonclustered index IX_CLASS_TNAME
on classinfo(objexplain)
with fillfactor=30
go

declare @d datetime
set @d=getdate()

SELECT objexplain as 索引 FROM classinfo(index=IX_CLASS_TNAME) WHERE objexplain like '%班%'    --指定按‘IX_CLASS_TNAME’索引查询

select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

declare @d1 datetime
set @d1=getdate()

SELECT objexplain as 没用索引 FROM classinfo WHERE objexplain like '%班%' 

select [执行时间]=datediff(ms,@d1,getdate())

 

drop table test

create table test(
  id int primary key
 )
go
insert into test values (1 )
insert into test values (2 )
insert into test values (3 )
insert into test values (4 )
insert into test values (8 )
insert into test values (6 )
insert into test values (8 )
insert into test values (9 )
insert into test values (7)
insert into test values (12)
insert into test values (13)
insert into test values (14)
insert into test values (18)
insert into test values (22)
go

 

select * From test

/*得出三个数据连续数中的开始不连续的数值*/
select * From (select id,(select min(id) from test where id>t.id)id2 from test t) t where id2-id>1

/*得到三个数据连续数的中间数据*/
select b.id
from test a,test b,test c
where a.id=b.id-1 and b.id=c.id-1

 

select *
from test a
where exists (select 1 from test where id=a.id-1)
and exists (select 1 from test where id=a.id+1)

/*得到结果id 6 9 14 18
id2 8 11 18 22*/