提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

MySQL的CASEWHEN语句的几个使用实例

mysql实例 使用 语句 几个 casewhen
2023-06-13 09:15:26 时间

使用CASEWHEN进行字符串替换处理

复制代码代码如下:
mysql>select*fromsales;

9rowsinset(0.01sec)

SELECTnameASName,
   CASEcategory
   WHEN"Holiday"THEN"Seasonal"
   WHEN"Profession"THEN"Bi_annual"
   WHEN"Literary"THEN"Random"ENDAS"Pattern"
FROMsales;                   

9rowsinset(0.00sec)

CREATETABLEsales(
   numMEDIUMINTNOTNULLAUTO_INCREMENT,nameCHAR(20),winterINT,springINT,
   summerINT,fallINT,categoryCHAR(13),primarykey(num)
)ENGINE=MyISAM;

insertintosalesvalue(1,"Java",1067,200,150,267,"Holiday");
insertintosalesvalue(2,"C",970,770,531,486,"Profession");
insertintosalesvalue(3,"JavaScript",53,13,21,856,"Literary");
insertintosalesvalue(4,"SQL",782,357,168,250,"Profession");
insertintosalesvalue(5,"Oracle",589,795,367,284,"Holiday");
insertintosalesvalue(6,"MySQL",953,582,336,489,"Literary");
insertintosalesvalue(7,"Cplus",752,657,259,478,"Literary");
insertintosalesvalue(8,"Python",67,23,83,543,"Holiday");
insertintosalesvalue(9,"PHP",673,48,625,52,"Profession");


简单语句
复制代码代码如下:

SELECTCASEWHEN10*2=30THEN"30correct"
  WHEN10*2=40THEN"40correct"
  ELSE"Shouldbe10*2=20"
ENDAS`result`;

多重表达式
复制代码代码如下:SELECTCASE10*2
  WHEN20THEN"20correct"
  WHEN30THEN"30correct"
  WHEN40THEN"40correct"
  ELSE"Noresults"
ENDAS`result`;
在SELECT查询中使用CASEWHEN
复制代码代码如下:CREATETABLEDVDs(
  IDSMALLINTNOTNULLAUTO_INCREMENTPRIMARYKEY,
  NameVARCHAR(60)NOTNULL,
  NumDisksTINYINTNOTNULLDEFAULT1,
  RatingIDVARCHAR(4)NOTNULL,
  StatIDCHAR(3)NOTNULL
)ENGINE=INNODB;
INSERTINTODVDs(Name,NumDisks,RatingID,StatID)
VALUES("Christmas",1,"NR","s1"),("Doc",1,"G", "s2"),("Africa",1,"PG","s1"),("Falcon",1,"NR","s2"),
      ("Amadeus",1,"PG","s2"),("Show",2,"NR","s2"),("View",1,"NR","s1"),("Mash",2,"R", "s2");
SELECTName,RatingIDASRating,
  CASERatingID
     WHEN"R"THEN"Under17requiresanadult."
     WHEN"X"THEN"Noone17andunder."
     WHEN"NR"THEN"Usediscretionwhenrenting."
     ELSE"OKtorenttominors."
  ENDASPolicy
FROMDVDs
ORDERBYName;

8rowsinset(0.01sec)

相关文章