1、if语句 显示男生记录 IF (SELECT COUNT(*) FROM stu where sex=1)>0 BEGIN GOTO X END ELSE BEGIN GOTO Y END X: SELECT * FROM stu where sex=1 return Y: SELECT * FROM stu where sex=0

2、while语句 USE students select * into grade_bak from grade select * from grade GO

WHILE (SELECT AVG(grade) FROM grade) < 90 BEGIN UPDATE grade SET grade = grade + 10 SELECT MAX(grade) FROM grade IF (SELECT MAX(grade) FROM grade) > 100 BREAK ELSE CONTINUE END PRINT \'成绩太高\'

select * from grade GO

drop table grade select * into grade from grade_bak drop table grade_bak

3、case语句 USE pubs select type,title,price from titles

SELECT CASE type WHEN \'popular_comp\' THEN \'Popular Computing\' WHEN \'mod_cook\' THEN \'Modern Cooking\' WHEN \'business\' THEN \'Business\' WHEN \'psychology\' THEN \'Psychology\' WHEN \'trad_cook\' THEN \'Traditional Cooking\' ELSE \'Not yet categorized\' END AS Category, CONVERT(varchar(30), title) AS "Shortened Title", price AS Price FROM titles WHERE price IS NOT NULL ORDER BY 1

SELECT (CASE WHEN EXISTS(SELECT * FROM stu WHERE stu.number=\'000009\')THEN MIN(number) ELSE \'000000\' END) as XH FROM stu

更新多列 select * from publishers WHERE country <> \'USA\' OR pub_id = \'9999\' UPDATE publishers SET state = CASE WHEN country <> \'USA\' THEN \'--\' ELSE state END, city = CASE WHEN pub_id = \'9999\' THEN \'LYON\' ELSE city END WHERE country <> \'USA\' OR pub_id = \'9999\' select * from publishers WHERE country <> \'USA\' OR pub_id = \'9999\'

显示男女的汉字性别1 SELECT number,name, CASE WHEN sex =1 THEN \'男\' WHEN sex =0 THEN \'女\' ELSE \'NULL\' END AS "性别" FROM stu

显示男女的汉字性别2 SELECT number,name, CASE sex WHEN 1 THEN \'男\' WHEN 0 THEN \'女\' ELSE \'NULL\' END AS "性别" FROM stu

显示男女的汉字性别3 CREATE FUNCTION sextoCh(@s int) RETURNS nchar(1) --RETURNS char(2) AS BEGIN IF @s=1 RETURN \'男\' RETURN \'女\' END GO

SELECT number,name,students.dbo.sextoCh(sex) from stu GO

DROP FUNCTION sextoCh GO

4、变量 select @@VERSION print @@VERSION

DECLARE @cust VARCHAR(20) SET @CUST=\'FRANK\' SELECT CUSTOMERID,COMPANYNAME FROM CUSTOMERS WHERE CUSTOMERID = @CUST