SQL基础知识整理

--------------2011-11-3-------------------


---------------建库---------------------------------
create database DB_review
on primary
(
name='db_review_mdf',
filename='D:\1文件暂存处\复习资料1103\db_review.mdf',
size=3MB,
maxsize=100MB,
filegrowth=3MB
)
log on
(
name='db_review_log',
filename='D:\1文件暂存处\复习资料1103\db_review.ldf',
size=3Mb,
maxsize=10Mb,
filegrowth=10%
)




-------------------建表------------------------------------


create Table TB_student
(
stu_name nvarchar(5) not null,--uniqueidentifier--GUID类型---
stu_id int identity(1,1) primary key not null,--int 才能自动增长，切无长度限制
--varchar(n) 长度为 n 个字节的可变长度且非 Unicode 的字符数据。n 必须是一个介于 1 和 8,000 之间的数值。存储大小为输入数据的字节的实际长度
--varchar一个英文和一个汉字都站两个字节，而Nvarchar则是一个英文占一个字节，汉字占两个字节
stu_sex nchar(2),
stu_age nvarchar(5),
stu_course nvarchar(8)
)


----------------------------添加-----------------------------
insert into TB_student values('学生一','男','24','大语');
insert into TB_student values('学生二','男','22','高数');
insert into TB_student values('学生三','男','23','英语');
insert into TB_student values('学生四','女','24','大语');
insert into TB_student values('学生五','男','22','高数');
insert into TB_student values('学生六','男','21','大语');
insert into TB_student values('学生七','女','23','英语');
insert into TB_student values('学生八','男','22','大语');
insert into TB_student values('学生九','女','24','高数');


insert tb_student(stu_name) outPut insert.stu_id values('学生十')


------------------------更新-----------------------------------
update tb_student set stu_name=‘学生十’ where stu_id=10


-----------------------删除--------------------------------------
delete from tb_student where stu_name=‘学生十’


------------------------查询---------------------------------
select stu_name'姓名' from tb_student
select '性别'=stu_sex from TB_student 


select top 5 stu_name from TB_student 
select top 50 percent stu_name from TB_student 


select distinct *from TB_student  where stu_name='学生九'


select *from TB_student order by stu_id desc--倒序
select *from TB_student order by stu_id asc--正序


select stu_sex  from TB_student   group by stu_sex having stu_sex='女'
select COUNT (stu_sex) from TB_student  group by stu_sex having stu_sex='女'


select *from TB_student where stu_age ='24'and stu_sex='女'
select *from TB_student where stu_age='24'or stu_sex='女'
select *from TB_student where stu_name like  '__九'
select *from TB_student where stu_name like '%九'--^’'-'','的使用
select *from TB_student where stu_age between 22and 24
select *from TB_student where stu_age in(22,24)
--字符串截取
select SUBSTRING(stu_name ,3,1) from TB_student --(列名，起始位置，截取位数)


--数据转换
select CAST('123'as int)+CONVERT (int ,'123')--加运算


--获取时间
select YEAR(GETDATE())'年',MONTH(GETDATE())'月'
select DATEADD(YEAR ,2,GETDATE())
select DATEDIFF(YEAR ,DATEADD(YEAR ,2,GETDATE()),GETDATE())----晕


--显示行号
select ROW_NUMBER()over (order by stu_id)'行号',*from TB_student 
select RANK ()over (order by stu_id )'行号',*from TB_student 


--运算
select COUNT(stu_id )from TB_student 
select AVG(stu_id  ) from TB_student 
select MIN(stu_age)from TB_student --nvarchar类型也可以运算
select MAX(stu_age )from TB_student 


--添加新列
alter table tb_student add teacherId int default'1' with values
select *from TB_student


-----------------------建Teacher表并加值----------------------------
create Table TB_teacher
(
te_name nvarchar(5) not null,
te_id int identity(1,1) primary key not null,
te_sex nchar(2),
te_age nvarchar(5),
stu_course int 
)




insert into TB_teacher values('老师一','男','34','101');
insert into TB_teacher values('老师二','男','32','102');
insert into TB_teacher values('老师三','男','33','103');
insert into TB_teacher values('老师四','女','34','104');
insert into TB_teacher values('老师五','男','32','105');


-------------------------------------------------------------------- 
---------------------------多表查询-------------------------------


select *from TB_student s , tb_teacher t where t.te_id  = s.teacherId
select *from TB_student st1 inner join TB_student st2 on st1.stu_age =st2.stu_age 


----------------------------存储过程-----------------------------------


create proc sp_student
(
@age int 
)
as
begin
select stu_name from tb_student where stu_age=@age
end
exec sp_student 24


create view view_student
as
select *from TB_student 
select *from  view_student


-------------------------------触发器，事务见txt文档----------------------


-----------------------------项目用语句----------------------------------


--获取所有学生信息
select distinct stu_name ,stu_id,stu_sex,stu_age, s.stu_course  ,t.te_name  from tb_student s,TB_teacher t where s.teacherId=t.te_id


-----获取name和id
create proc sp_TextId
(
@typ int 
)
as
begin
if @typ=1
select stu_name,stu_id  from tb_student
else if @typ=2
select te_name,te_id  from TB_teacher 
end


--------------------添加用户---------------------


insert into tb_user valus('name','pwd','sex','birthday','like','coutry','city','town','mail')


---------------------临时表---------------------------


declare @table table(id int,name nvarchar(50));--定义临时表--临时表作用周期存储过程中
--output inserted.stu_id into @table(id)--把添加数据所返回的id添加到临时表中
insert tb_student(stu_name) output inserted.stu_id into @table(id) values('li')
declare @tableid int
select @tableid=id from @table--获取临时表中的值
insert tb_teacher values('','','',@tableid)--将学生表自动增长的值添加到教室表中
select *from @table
select *from tb_teacher
---临时表 超大数据库分页：现将所有数据放到临时表，每次删除当前页前部分读取前多少条作为本页数据，