Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use the case when then sentence in mysql, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Build a table:

Create table hank (id int,name varchar (20))

Insert data:

Insert into hank values (1)

Insert into hank values (2)

Insert into hank values (3 recorder C')

Update data through case when then:

Update hank set name = case id

When 1 then'D'

When 2 then 'E'

When 3 then 'F'

End

Where id in (1, 2, 3)

Sql means to update the name field so that the value of name is D if id=1, the value of name is E if id=2, and the value of name is F if id=3.

That is, conditional statements are written together.

The where section here does not affect the execution of the code, but it will improve the efficiency of sql execution. Make sure that the sql statement executes only the number of rows that need to be modified, where there are only three pieces of data to update, while the where clause ensures that only three rows of data are executed

Creation of tables

CREATE TABLE `lee` (

`id`int (10) NOT NULL AUTO_INCREMENT

`name` char (20) DEFAULT NULL

`dayday` datetime DEFAULT NULL

PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8

Data insertion:

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')

The first usage:

SELECT name

CASE WHEN birthday

< '1981' THEN 'old' WHEN birthday >

'1988' THEN 'yong'

ELSE 'ok' END YORN

FROM lee

The second usage:

SELECT NAME, CASE name

WHEN 'sam' THEN' yong'

WHEN 'lee' THEN' handsome'

ELSE 'good' END as oldname

FROM lee

The third: of course, case when statements can also be combined.

Select name, birthday

Case

When birthday > '1983' then 'yong'

When name='lee' then 'handsome'

Else 'just so so' end

From lee

If you use the SQL statement to compare dates here, you need to quote the year, otherwise the result may be different from the expected result.

Of course, it can also be realized by using the year function.

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的罚款 select payment_date, amount, case when amount >

= 0 AND amount

< 40 then 'low' when amount >

= 40 AND amount

< 80 then 'moderate' when amount >

= 80 then 'high'

Else 'null' END

FROM penalties

Question 2: count out the fine number belonging to low

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'

The above is all the contents of the article "how to use case when then sentences in mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.