课外天地 李树青学习天地数据库系统原理课件 → 关于数据库T-SQL语句学习的资料


  共有18852人关注过本帖树形打印复制链接

主题:关于数据库T-SQL语句学习的资料

帅哥哟,离线,有人找我吗?
admin
  1楼 博客 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信 管理员
等级:管理员 帖子:1939 积分:26594 威望:0 精华:34 注册:2003/12/30 16:34:32
关于数据库T-SQL语句学习的资料  发帖心情 Post By:2006/4/17 22:29:14 [只看该作者]

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


 回到顶部