MYSQL例题
2017-09-06 本文已影响0人
JUNjianshuZHU
例1
Paste_Image.png
Show the name and the continent - but substitute Eurasia for Europe and Asia; substitute America - for each country in North America or South America or Caribbean. Show countries beginning with A or B
|
|
|
解法一：
SELECT name,
CASE WHEN continent ='Europe' or continent ='Asia' THEN 'Eurasia'
WHEN continent ='North America' or continent ='South America' or continent ='Caribbean' THEN 'America'
ELSE continent
END
FROM world
WHERE name LIKE 'A%' OR name LIKE 'B%'
解法二：
SELECT name,
CASE WHEN continent in ('Europe','Asia') THEN 'Eurasia'
WHEN continent in ('North America','South America','Caribbean') THEN 'America'
ELSE continent
END
FROM world
WHERE name LIKE 'A%' OR name LIKE 'B%'
例2
The expression subject IN ('Chemistry','Physics') can be used as a value - it will be 0 or 1.
Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.
|
|
|
SELECT winner,subject
FROM nobel
WHERE yr=1984
ORDER BY subject IN ('Chemistry','Physics'),subject,winner
例3
把年龄分组并计算每组人数
|
|
|
mysql> SELECT COUNT(Age),(CASE
-> WHEN Age <=20 THEN '20岁及其以下'
-> WHEN Age >20 AND Age <=30 THEN '21-30岁'
-> WHEN Age >30 AND Age <=40 THEN '31-40岁'
-> ELSE '40岁以上' END) AS live
-> FROM user
-> GROUP BY live;
+------------+--------------+
| COUNT(Age) | live |
+------------+--------------+
| 8437 | 20岁及其以下 |
| 27037 | 21-30岁 |
| 17093 | 31-40岁 |
| 6534 | 40岁以上 |
+------------+--------------+
4 rows in set
例4
提取OrderDate中的年月日
|
|
|
mysql> SELECT *,
-> EXTRACT(YEAR FROM OrderDate) AS 年,
-> EXTRACT(MONTH FROM OrderDate) AS 月,
-> EXTRACT(DAY FROM OrderDate) AS 日
-> FROM orders
-> GROUP BY OrderDate
-> LIMIT 20;
+-----+-----------+---------------------+--------+-----------+-------+----------+------------+------+----+----+
| ID | OrderID | OrderDate | UserID | ProductID | Price | Quantity | TotalMoney | 年 | 月 | 日 |
+-----+-----------+---------------------+--------+-----------+-------+----------+------------+------+----+----+
| 1 | 119000001 | 2011-09-01 00:00:00 | 119970 | A | 100 | 6 | 600 | 2011 | 9 | 1 |
| 23 | 119000023 | 2011-09-01 00:01:00 | 104629 | A | 100 | 3 | 300 | 2011 | 9 | 1 |
| 47 | 119000047 | 2011-09-01 00:02:00 | 103868 | B | 200 | 6 | 1200 | 2011 | 9 | 1 |
| 74 | 119000074 | 2011-09-01 00:03:00 | 110405 | A | 100 | 8 | 800 | 2011 | 9 | 1 |
| 93 | 119000093 | 2011-09-01 00:04:00 | 113438 | A | 100 | 8 | 800 | 2011 | 9 | 1 |
| 111 | 119000111 | 2011-09-01 00:05:00 | 113265 | A | 100 | 4 | 400 | 2011 | 9 | 1 |
| 133 | 119000133 | 2011-09-01 00:06:00 | 105236 | E | 400 | 9 | 3600 | 2011 | 9 | 1 |
| 155 | 119000155 | 2011-09-01 00:07:00 | 108285 | B | 200 | 9 | 1800 | 2011 | 9 | 1 |
| 178 | 119000178 | 2011-09-01 00:08:00 | 110595 | D | 300 | 3 | 900 | 2011 | 9 | 1 |
| 200 | 119000200 | 2011-09-01 00:09:00 | 105668 | A | 100 | 10 | 1000 | 2011 | 9 | 1 |
| 225 | 119000225 | 2011-09-01 00:10:00 | 100954 | D | 300 | 8 | 2400 | 2011 | 9 | 1 |
| 248 | 119000248 | 2011-09-01 00:11:00 | 101675 | E | 400 | 2 | 800 | 2011 | 9 | 1 |
| 272 | 119000272 | 2011-09-01 00:12:00 | 107291 | A | 100 | 10 | 1000 | 2011 | 9 | 1 |
| 290 | 119000290 | 2011-09-01 00:13:00 | 102565 | D | 300 | 1 | 300 | 2011 | 9 | 1 |
| 320 | 119000320 | 2011-09-01 00:14:00 | 102565 | D | 300 | 7 | 2100 | 2011 | 9 | 1 |
| 356 | 119000356 | 2011-09-01 00:15:00 | 115174 | A | 100 | 1 | 100 | 2011 | 9 | 1 |
| 380 | 119000380 | 2011-09-01 00:16:00 | 104122 | B | 200 | 7 | 1400 | 2011 | 9 | 1 |
| 401 | 119000401 | 2011-09-01 00:17:00 | 100644 | A | 100 | 10 | 1000 | 2011 | 9 | 1 |
| 430 | 119000430 | 2011-09-01 00:18:00 | 101176 | A | 100 | 6 | 600 | 2011 | 9 | 1 |
| 457 | 119000457 | 2011-09-01 00:19:00 | 111251 | D | 300 | 3 | 900 | 2011 | 9 | 1 |
+-----+-----------+---------------------+--------+-----------+-------+----------+------------+------+----+----+
20 rows in set
例5
The expression subject IN ('Chemistry','Physics') can be used as a value - it will be 0 or 1.
Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.
|
|
|
SELECT winner,subject
FROM nobel
WHERE yr=1984
ORDER BY subject IN ('Chemistry','Physics'),subject,winner
例6
Q:Find the continents where all countries have a population <= 25000000. Then find the names of the countries associated with these continents. Show name, continent and population.
|
|
|
SELECT name, continent, population FROM world x
WHERE 25000000>=ALL (
SELECT population FROM world y
WHERE x.continent=y.continent
)
