1.计算1-100之间的偶数及奇数的和
- (1)
- declare @num int ,@num1 int ,@num2 int
- select @num = 1 ,@num1 =0 , @num2 = 0
- while(@num<101)
- begin
- if (@num%2=0)
- set @num1= @num1+@num
- else
- set @num2 =@num2 +@num2
- set @num = @num +1
- end
- print '偶数的和' + convert (char,@num1) + '奇数的和' + convert(char,@num2)
- go
2.计算2000到3000的闰年
- declare @year int
- set @year =2000
- while(@year<3001)
- begin
- if(@year % 4 = 0 and @year % 100 !=0 or @year %400 = 0)
- begin
- set @year
- end
- set @year = @year + 1
- end
- print @year
- go
3.--阶层
- declare @unm int , @sum int
- select @num=1,@sum = 0
- while(@num<7)
- begin
- set @sum * = @sum
- set @sum = @num + 1
- end
- pfint @sum
- go
4
- create database students
- go
- use students
- go
- --字段名称 数据类型 说明
- --stuID char(10) 学生编号,主键
- --stuName Varchar(10) 学生名称
- --major Varchar(50) 专业
- create table student
- (
- stuID char(10) not null,
- stuName varchar(50),
- major varchar(50)
- )
- go
- select * from student
- alter table student
- add constraint FK_student_stuID primary key(stuID)
- go
- --添加数据
- insert into student values('1001','琳琳','计算机')
- insert into student values('1002','白杨','计算机')
- insert into student values('1003','程倩','工商管理')
- insert into student values('1004','娜娜','数学')
- insert into student values('1005','五月','电子商务')
- insert into student values('1006','唐月','电子商务')
- go
- -- 字段名称 数据类型 说明
- --BID char(10) 图书编号,主键
- --title char(50) 书名
- --author char(20) 作者
- create table Book
- (
- BID char(10) not null,
- title char(20),
- author char(20)
- )
- go
- select * from Book
- alter table Book
- add constraint pk_book_BID primary key(BID)
- go
- --添加数据
- insert into book values('B001','红楼梦','施耐庵')
- insert into book values('B002','回忆','陈侨情')
- insert into book values('B003','一点一滴','许文')
- insert into book values('B004','梦醒了','徐浩')
- insert into book values('B005','回到现实','郭林琳')
- insert into book values('B006','幻想','安如意')
- go
- -- 字段名称 数据类型 说明
- --borrowID char(10) 借书编号,主键
- --stuID char(10) 学生编号,外键
- --BID char(10) 图书编号,外键
- --T_time datetime 借书日期
- --B_time datetime 还书日期
- create table borrow
- (
- borrowID char(10) NOT NULL,
- stuID char(10),
- BID char(10),
- T_time datetime,
- B_time datetime
- )
- go
- select * from borrow
- --添加数据
- insert into borrow values('b01','1001','B001','2007-12-26',null)
- insert into borrow values('b02','1002','B002','2008-12-26','2008-05-26')
- insert into borrow values('b03','1003','B003','2008-12-26','2009-05-12')
- insert into borrow values('b04','1004','B004','2012-12-26','2013-04-10')
- insert into borrow values('b05','1005','B005','2007-12-26',null)
- insert into borrow values('b06','1006','B006','2001-02-26',null)
- go
- --添加外键约束
- alter table borrow
- add constraint fk_borrow_Book foreign key(BID) REFERENCES book(BID)
- go
- alter table borrow
- add constraint fk_borrow_student foreign key(stuID) REFERENCES student(stuID)
- go
- --查看
- select * from borrow
- select * from student
- select * from book
- select * from borrow
- --(1)查询“计算机”专业学生在“2007-12-15”至“2008-1-8”时间段内借书的学生编号、
- --学生名称、图书编号、图书名称、借出日期;参考查询结果如下图所示:
- declare @time datetime,@stuID char(10),@BID char(10),@student varchar(20),@stuName char
- begin
- select @stuID=stuID from student where major='计算机'
- select @BID=BID from Book
- select @stuName= stuName from student
- begin
- select stuID=@stuID,BID=@BID,T_time,title from borrow inner join Book on borrow.BID=Book.BID where T_time >'2007-12-15' AND T_time<'2008-1-8'
- end
- end
- go
- --(2)查询所有借过图书的学生编号、学生名称、专业;参考查询结果如下图所示:
- declare @stuids varchar(10)
- begin
- select @stuids = student .stuID from student inner join BORROW on student.stuID=BORROW.stuID
- select @stuids as'学生编号',Student.stuName as'学生名称',student.major as'专业' from student
- inner join BORROW on BORROW.stuID=@stuids
- end
- select student.stuID as'学生编号',student.stuName as'学生名称',student.major as'专业'
- from borrow inner join student on borrow.stuID=student.stuID where borrow.T_time is not null
- go
- --(3)查询借过作者为“安意如”的图书的
- --学生姓名、图书名称、借出日期、归还日期;参考查询结果如下图所示
- select student.stuName as'学生姓名',Book.title as'图书名称',BORROW.T_time as'借出日期',borrow.B_time as'归还日期'
- from student inner join borrow on student.stuID=BORROW.stuID inner join Book on
- Book.BID=borrow.BID where Book.author='安如意'
- --(4)查询目前借书但未归还图书的学生名称及未还图书数量;参考查询结果如下图所示:
- select student.stuName as '学生名称',count(*) as '未还图书数量'from student inner join borrow on
- student.stuID=borrow.stuID where BORROW.T_time is not null and BORROW.B_time is null
- group by borrow.stuID ,student.stuName
转载于:https://blog.51cto.com/7112081/1195155