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