SQl基本语句汇总.docVIP

use master go --批处理 if exists(select * from sysdatabases where name=bbs) drop database bbs go create database bbs on --创建数据文件 ( name=bbs_date, filename=d:\bbs_date.mdf, size=5mb, maxsize=100mb, filegrowth=10% ) log on --日志文件 ( name=bbs_log, filename=d:\bbs_log.ldf, size=5mb, maxsize=100mb, filegrowth=10% ) go use bbs go if exists(select * from sysobjects where name=user_info) drop table user_info go create table user_info ( userId int identity(1,1) not null primary key,--自增长列，主键 userName varchar(20) not null, userAge int not null check(userAge=20 and userAge=60), userSex varchar(10)not null check(userSex=男 or userSex=女), userAddress varchar(50) not null default(陕西西安),--默认约束 userEmail varchar(30) null, userSID varchar(30) not null unique(userSID)--唯一约束 ) go /*给表添加约束 alter table user_info add constraint pk_userId primary key(userId) --添加约束 alter table user_info add constraint ck_userId check(userAge=20 and userAge=60) alter table user_info add constraint ck_userSex check(userSex=男 or userSex=女) alter table user_info add constraint df_userAddress default(陕西西安) for userAddress alter table user_info add constraint un_userSID unique(userSID) */ insert into user_info values(小高,26,男,DEFAULT,463042786@,610322) --局部变量与存储过程 --查找小明前面和后面的学生信息 declare @uId int select @uId=userId from user_info where userName=小明 select * from user_info where (userId=@uId-1) or (userId=@uId+1) --if else declare @age int select @age=AVG(userAge) from user_info print平均年龄为+convert(varchar(5),@age) if(@age26) begin print大家是成年人 select top 2 * from user_info order by userAge desc end else begin print大家是青年人 select top 2 * from user_info order by userAge end --while循环，让年龄都加到30以上 declare @num int while(1=1) begin select @num=COUNT(*) from user_info where userAge30 if(@num0) update user_info set userAge=userAge+2 else break end print增加后的年龄如下： select * from user_info create table stu_info ( stuId int identity(1,1) not null, stuName va