SQL基本语句汇总-经典.doc

use mastergo -批处理if exists(select * from sysdatabases where name=bbs)drop database bbsgocreate database bbson -创建数据文件( 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%)gouse bbsgoif exists(select * from sysobjects where name=user_info)drop table user_infogocreate 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=20 and userAge26)begin print大家是成年人 select top 2 * from user_info order by userAge desc endelsebegin print大家是青年人 select top 2 * from user_info order by userAge end-while循环，让年龄都加到30以上declare num intwhile(1=1) begin select num=COUNT(*) from user_info where userAge0) update user_info set userAge=userAge+2 else break endprint增加后的年龄如下：select * from user_infocreate table stu_info( stuId int identity(1,1) not null, stuName varchar(20) not null, writenExam float not null, labExam float not null)insert into stu_info values(王明,91,73)insert into stu_info values(张三,88,59)insert into stu_info values(王四,66,87)insert into stu_info values(刘明,46,82)insert into stu_info values(于水,67,35)-CASEEND多分支语句printABCDE五级显示如下：select stuId,stuName, 成绩=case when writenExam60 then E when writenExam between 60 and 69 then D when writenExam between 70 and 79 then C when writenExam between 80 and 89 then B else A end from stu_info-平均分评级 select * from stu_infouse bbsgoselect 学号=stuId,姓名=stuName,笔试成绩=writenExam,机试成绩=labExam, 平均分=(writenExam+labExam)/2, 等级=case when (writenExam+labExam)/270 and labAvg70) print本班考试成绩：优秀 else print本班考试成绩：较差 print - print参加本次考试没有通过的学生 select stuId,stuName,writenExam,labExam from stu_info where writenExam60 or labExam60GOEXEC proc_stu1 -调用/*-创建带参数存储过程-*/USE bbsGOIF EXISTS (SELECT * FROM sysobjects WHERE name = proc_stu1 ) DROP PROCEDURE proc_stu1GOCREATE PROCEDURE proc_stu1 writenPass int,labPass int-带默认参数 writenPass int=60,labPass int=55 AS print 笔试及格线：+convert(varchar(5),writenPass) print 机试及格线：+convert(varchar(5),labPass) print - print 参加本次考试没有通过的学员： SELECT stuName,stuId,writenExam,labExam FROM stu_info