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
加好友 
发短信
管理员
Post By：2006/4/17 22:29:14 [只看该作者]
