本文共 6017 字,大约阅读时间需要 20 分钟。
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- Drop table sales;
-
- CREATE TABLE sales(
- num MEDIUMINT NOT NULL AUTO_INCREMENT,
- name CHAR(20),
- winter INT,
- spring INT,
- summer INT,
- fall INT,
- category CHAR(13),
- primary key(num)
- )type=MyISAM;
-
- insert into sales value(1, 'Java', 1067 , 200, 150, 267,'Holiday');
- insert into sales value(2, 'C',970,770,531,486,'Profession');
- insert into sales value(3, 'JavaScript',53,13,21,856,'Literary');
- insert into sales value(4, 'SQL',782,357,168,250,'Profession');
- insert into sales value(5, 'Oracle',589,795,367,284,'Holiday');
- insert into sales value(6, 'MySQL',953,582,336,489,'Literary');
- insert into sales value(7, 'Cplus',752,657,259,478,'Literary');
- insert into sales value(8, 'Python',67,23,83,543,'Holiday');
- insert into sales value(9, 'PHP',673,48,625,52,'Profession');
-
- select * from sales;
-
- SELECT name AS Name,
- CASE category
- WHEN "Holiday" THEN "Seasonal"
- WHEN "Profession" THEN "Bi_annual"
- WHEN "Literary" THEN "Random" END AS "Pattern"
- FROM sales;
简单语句
- SELECT CASE WHEN 10*2=30 THEN '30 correct'
- WHEN 10*2=40 THEN '40 correct'
- ELSE 'Should be 10*2=20'
- END;
多重表达式
- SELECT CASE 10*2
- WHEN 20 THEN '20 correct'
- WHEN 30 THEN '30 correct'
- WHEN 40 THEN '40 correct'
- END;
在SELECT查询中使用CASE WHEN
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- Drop table DVDs;
-
- CREATE TABLE DVDs (
- ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
- Name VARCHAR(60) NOT NULL,
- NumDisks TINYINT NOT NULL DEFAULT 1,
- RatingID VARCHAR(4) NOT NULL,
- StatID CHAR(3) NOT NULL
- )
- ENGINE=INNODB;
-
- INSERT INTO DVDs (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');
-
-
- SELECT Name, RatingID AS Rating,
- CASE RatingID
- WHEN 'R' THEN 'Under 17 requires an adult.'
- WHEN 'X' THEN 'No one 17 and under.'
- WHEN 'NR' THEN 'Use discretion when renting.'
- ELSE 'OK to rent to minors.'
- END AS Policy
- FROM DVDs
- ORDER BY Name;
-
- #表的创建
- CREATE TABLE `lee` (
- `id` int(10) NOT NULL AUTO_INCREMENT,
- `name` char(20) DEFAULT NULL,
- `birthday` datetime DEFAULT NULL,
- PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8
-
- #数据插入:
- insert into lee(name,birthday) values ('sam','1990-01-01');
- insert into lee(name,birthday) values ('lee','1980-01-01');
- insert into lee(name,birthday) values ('john','1985-01-01');
-
-
- #使用case when语句
- select name,
- case
- when birthday<'1981' then 'old'
- when birthday>'1988' then 'yong'
- else 'ok' END YORN
- from lee;
-
- select NAME,
- case name
- when 'sam' then 'yong'
- when 'lee' then 'handsome'
- else 'good' end
- from lee;
- #当然了case when语句还可以复合
-
- select name,birthday,
- case
- when birthday>'1983' then 'yong'
- when name='lee' then 'handsome'
- else 'just so so ' end
- from lee;
-
- #在这里用sql语句进行日期比较的话,需要对年加引号。要不然可能结果可能和预期的结果会不同。我的mysql版本5.1
-
- #当然也可以用year函数来实现,以第一个sql为例
-
- select NAME,
- CASE
- when year(birthday)>1988 then 'yong'
- when year(birthday)<1980 then 'old'
- else 'ok' END
- from lee;
-
- create table penalties
- (
- paymentno INTEGER not NULL,
- payment_date DATE not null,
- amount DECIMAL(7,2) not null,
- primary key(paymentno)
- )
-
- insert into penalties values(1,'2008-01-01',3.45);
- insert into penalties values(2,'2009-01-01',50.45);
- insert into penalties values(3,'2008-07-01',80.45);
-
- #对罚款登记分为三类,第一类low,包括大于0小于等于40的罚款,第二类moderate大于40
- #到80之间的罚款,第三类high包含所有大于80的罚款。
- #统计出属于low的罚款编号。
-
- #第一道题的解法与上面的相同
- select paymentno,amount,
- case
- when amount>0 and amount<=40 then 'low'
- when amount>40 and amount<=80 then 'moderate'
- when amount>80 then 'high'
- else 'incorrect' end lvl
- from `penalties`
-
- #统计出属于low的罚款编号。重点看这里的解决方法
- #方法1.
- select paymentno,amount
- from `penalties`
- where case
- when amount>0 and amount<=40 then 'low'
- when amount>40 and amount<=80 then 'moderate'
- when amount>80 then 'high'
- else 'incorrect' end ='low';
-
- #方法2
- select *
- from (select paymentno,amount,
- case
- when amount>0 and amount<=40 then 'low'
- when amount>40 and amount<=80 then 'moderate'
- when amount>80 then 'high'
- else 'incorrect' end lvl
- from `penalties`) as p
- where p.lvl='low';
转载地址:http://ntpdx.baihongyu.com/