create database stu_infor
go
use stu_infor
go
create table student(
stuName varchar(10),
stuNo varchar(10),
stuSex char(10),
stuAge int,
stuSeat char(10),
stuAddress varchar(40),
stuGrade int)
go
insert student values('liming1','20071','male',22,'0001','huannan1',102)
insert student values('liming2','20072','female',24,'0002','huannan2',100)
insert student values('liming3','20073','male',25,'0003','huannan3',58)
insert student values('liming4','20074','female',26,'0004','huannan4',95)
insert student values('liming5','20075','male',28,'0005','huannan5',86)
alter table student
add stuInfor varchar(100)
select * from student
select * from student where stuAge > (select stuAge from student where stuName = 'liming2')
select * from student where stuName in (select stuName from student where stuAge >25)
select * from student where stuName not in (select stuName from student where stuAge >25)
if exists(select * from student where stuName = 'liming2')
begin
delete from student where stuName = 'liming2'
select * from student
end
else
begin
print 'was deleted'
select * from student
end
declare @id int
set @id = 1
while(@id < 6)
begin
update student set stuInfor = 'student_information'+convert(varchar(2),@id) where stuName ='liming'+convert(varchar(2),@id)
set @id = @id + 1
end
select * from student
declare @stuName varchar(10)
set @stuName = 'liming2'
select * from student where stuName = @stuName
declare @stuName varchar(10)
declare @stuSeat char(10)
set @stuName = 'liming2'
select @stuSeat = stuSeat from student where stuName = @stuName
print @stuName + '''s seat:' + @stuSeat
drop table student
drop database stu_infor
declare @grade int
select @grade = avg(stuGrade) from student
print 'the averge grade of studnet'' is :'+convert(char(4),@grade)
if(@grade > 56)
begin
print 'top 3:'
select top 3 * from student order by stuGrade desc
end
else
begin
print 'buttom 3:'
select top 3 * from student
end
select stuName,grade =
case
when stuGrade > 133 then 'A'
when stuGrade between 130 and 133 then 'B'
when stuGrade between 110 and 130 then 'C'
when stuGrade < 110 then 'D'
end
from student
declare @number int
declare @i int
set @i = 0
while(1=1)
begin
select @number = count(*) from student where stuGrade < 90
if(@number > 0)
begin
update student set stuGrade =stuGrade + 2
set @i = @i + 1
end
else
begin
print '加分次数：' + convert(char(4),@i) + ', 加分后的成绩：'
select stuName,stuGrade from student
break
end
end
select * from student
create database stu_infor
go
use stu_infor
go
create table student(
stuName varchar(10) not null,
stuNo varchar(10) not null,
stuSex char(10) not null default 'male',
stuAge int not null default 20,
stuSeat char(10) not null default '0000',
stuAddress varchar(40) not null default 'do not know',
stuGrade int not null default 0)
drop table student
declare @id int
set @id = 1
while(@id < 1000)
begin
insert student(stuName,stuNo) values('liming'+convert(varchar(5),@id),@id)
set @id = @id + 1
end
create procedure stu_proc
@stuName1 varchar(10),
@stuName2 varchar(10)
as
select * from student where stuName = @stuName1 or stuName = @stuName2
drop procedure stu_proc
execute stu_proc 'liming3','liming23'
select * from student
create proc stu_proc
@stuName varchar(10) output
as
select @stuName = stuName from student where stuName = 'liming300'
declare @stuName varchar(10)
execute stu_proc @stuName output
print 'hehe:'+@stuName
declare @id int
set @id = 501
while(@id <= 1000)
begin
delete student where stuName = 'liming'+convert(varchar(5),@id)
set @id = @id + 1
end
create trigger stu_trigger
on student
for insert
as
declare @stuName varchar(10)
select @stuName = stuName from inserted
insert stu_insert(stuName) values(@stuName)
go
select * from stu_insert
declare @id int
set @id = 501
while(@id < 1000)
begin
insert student(stuName,stuNo) values('liming'+convert(varchar(5),@id),@id)
set @id = @id + 1
end
create table stu_del
(
id int not null,
stuAge varchar(10) not null
)
create trigger stu_del_trigger
on student
for delete
as
declare @stuAge int
select @stuAge = stuAge from deleted
insert stu_del(id,stuAge) values(100,@stuAge)
select * from student
declare @id int
set @id = 501
while(@id < 1000)
begin
delete student where stuName = 'liming'+convert(varchar(5),@id)
set @id = @id + 1
end
drop table student
create table student(
id int,
stuName varchar(10))
declare @id int
set @id = 1
while(@id < 20)
begin
insert student(id,stuName) values(@id,'liming'+convert(varchar(5),@id))
set @id = @id + 1
end
create table stu_update1(
stuName varchar(10))
create table stu_update2(
stuName varchar(10))
create trigger stu_update
on student
for update
as
declare @stuOldName varchar(10),
@stuNewName varchar(10)
select @stuOldName = stuName from deleted
select @stuNewName = stuName from inserted
insert stu_update1(stuName) values(@stuOldName)
insert stu_update2(stuName) values(@stuNewName)
go
select * from student
declare @id int
set @id = 1
while(@id < 20)
begin
update student set stuName = 'huyang'+convert(varchar(4),@id) where stuName = 'liming'+convert(varchar(4),@id)
set @id = @id + 1
end
select * from stu_update1
select * from stu_update2
use master
select * from sysdatabases
if exists(select * from sysdatabases where name = 'stu_infor')
drop database stu_infor
else
print 'no'
create unique index stu_index on student(stuName)
drop index student.stu_index
create clustered index stu_index on student(stuName)
create nonclustered index stu_index on student(stuName)
create view stu_view
as
select * from student
select * from stu_view
create whatIndex index indexName on tableName(tableColumns)
create view viewName as sql statements
create procedure procedureName <[local] variable> as sql statements
create trigger triggerName
on tableName
for [INSERT|DELETE|UPDATE]
as
sql statements
select * from student where stuName in('liming1','liming34','liming103')
select * from student
create table students(
studentName varchar(10) not null,
studentId int)
insert students values('liming1',1)
insert students values('liming2',2)
insert students values('liming3',3)
insert students values('liming4',4)
create table score(
studentId int,
courseId varchar(10),
score int)
insert score values(1,'001',89)
insert score values(1,'002',90)
insert score values(2,'001',49)
insert score values(3,'005',200)
insert score values(3,'002',89)
insert score values(4,'001',59)
select * from students
select * from score
select stu.studentName,sco.score from students stu,score sco where stu.studentId = sco.studentId
select stu.studentName,sco.score from students stu inner join score sco on stu.studentId = sco.studentId
select stu.studentName,sco.score from students stu left join score sco on stu.studentId = sco.studentId
select stu.studentName,sco.score from students stu right join score sco on stu.studentId = sco.studentId
select stu.studentName,sco.score from students stu full join score sco on stu.studentId = sco.studentId
select stu.studentName,sco.score from students stu cross join score sco on stu.studentId = sco.studentId
create database stu_infor
on(
name = 'stu_infor_data',
filename = 'e:\stu_infor_data.mdf',
size = 10mb,
maxsize = 50mb,
filegrowth = 5mb)
log on(
name = 'stu_infor_log',
filename = 'e:\stu_infor_data.ldf',
size = 5mb,
maxsize = 25mb,
filegrowth = 5mb)
if exists(select * from sysdatabases where name = 'stu_infor')
drop database stu_infor
exec sp_grantlogin 'liming\huyang'
exec sp_addlogin 'liming','huyang'