Mysql ---- SELECT names/zh, mysqlsetnamesutf8

Mysql ---- SELECT names/zh, mysqlsetnamesutf8
<SELECT names

Language:	English•Chinese

Name	Continent
Afghanistan	Asia
Albania	Europe
Algeria	Africa
Andorra	Europe
Angola	Africa
....

Name: Country name
Continent: continent

Pattern Matching Strings

This tutorial usesLIKEThe operator checks the country name.worldUse in tablesSELECTStatement:

Summary
1.

You can useWHERE name LIKE 'B%'To find out the country where B is the first.
% Is a ten-character, which can be used to represent any word.

Find the country that starts with Y.

select name from world where name like 'Y%';

 

 

 

2.

Find the country ending with Y.

select name from world where name like '%Y';

 

 

 

 

 

3.

"Luxembourg" contains an x letter and a country name contains x. List the two countries.

Find all countries whose names include letters x.

select name from world where name like '%x%';

 

 

 

 

 

4.

The names of Iceland and Switzerland end with land. Is there anything else?

Find all countries whose names end with land.

select name from world where name like '%land'

 

 

 

 

 

 

5.

Columbia begins with C and ends with ia. Two other countries are the same.

Find all countries whose names start with C and end with ia.

select name from world where name like 'C%'and name like '%ia';

 

 

 

 

 

 

 

6.

"Greece" contains double e characters. Which country has dual o characters?

Find all countries, whose names include letters oo.

select name from world where name like '%oo%';

 

 

 

 

7.

"Bahamas" has three a, are there?

Find all countries. The names include three or more.

SELECT  name  FROM  world   WHERE  name  LIKE  '%a%a%a%'

 

 

 

 

 

 

 

8.

The second letter of "India" and "Angola" is n.
You can use the bottom line operator_It is a ten-character for a single letter.

SELECT  name  FROM  world  WHERE  name  LIKE  '_n%' ORDER  BY  name

Find all countries. The name t is the second letter.

SELECT name FROM world WHERE name LIKE '_t%'

 

 

 

 

 

9.

"Lesotho Lai Miao" and "Moldova" both have two letters, o, separated by the other two letters.

Find out all countries whose names are separated by the other two letters.

SELECT name FROM world WHERE name LIKE '%o__o%'

 

 

 

 

 

 

10.

"Cuba" and "Togo" are both 4 letters.

The names of all countries are 4 letters.

SELECT name FROM world WHERE LENGTH(name)=4

 

 

 

 

 

 

More difficult problems

If you think the above questions are too easy and very good. Well done for getting this far. The questions below are more difficult and more challenging!

11.

The capital of "Luxembourg" is also called "Luxembourg ".

The names of all countries are displayed. Their capital and country names are the same.

SELECT name FROM world WHERE name=concat(capital,'');

 

 

 

12.

The capital of "Mexico" is "Mexico City ".

The names of all countries are displayed. The capital is the country name plus "City ".

Concat Functions

SELECT name FROM world WHERE capital=concat(name,' City');

 

 

13.

Find out all the capitals and their country names, and the capital must contain country names.

SELECT capital,name FROM world WHERE capital like concat('%',name,'%');

 

 

14.

Find all the capitals and their country names, and the capital is an extension of the Country name.
You should display the Mico City because it is longer than its country name Mico.
You should not display Luxembourg because its capital and country name are the same.

SELECT name,capital FROM world WHERE capital like concat('%',name,'%') and LENGTH(capital)<>LENGTH(name);

 

 

 

 

 

 

15.

"Monaco-Ville" is the name of the merging country "Monaco" and the extension word "-Ville ".

Displays the country name and its extension words. For example, the capital is an extension of the Country name.

You can use the SQL function REPLACE or MID.

select name,replace(capital, name, '') from world where capital Like concat(name,'%_')