SQL ZOO 练习打卡第 2 天

最新推荐文章于 2025-01-13 18:02:07 发布

weixin_40193714

最新推荐文章于 2025-01-13 18:02:07 发布

阅读量1.6k

点赞数 39

CC 4.0 BY-SA版权

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_40193714/article/details/144714764

知识点汇总

查询规则

查询规则
序号	查询语句	说明
1	Select * from 表名；（*指所有字段）	所有字段所有行
2	Select 字段列表 from 表名；	指定字段所有行
3	Select * from 表名 where 条件；	满足条件的部分行
4	Select 字段列表 from 表名 where 条件；	满足条件，指定字段的部分行

1. 运算符

1.1 算数运算符

算数运算符
运算符	功能描述	示例
+	加法
-	减法
*	乘法
/	除法，返回商	3,4,9,10
DIV	除法，返回商，只返回整数部分
%	取余，返回余数
MOD	取余，返回余数

1.2 比较运算符

比较运算符
运算符	功能描述	示例
=	等于	4,9
<=>	安全的等于
<> 或 !=	不等于	12
<=	小于等于
>=	大于等于
>	大于	2,3,7,8,10
<	小于
IS NULL	判断一个值是否为空
IS NOT NULL	判断一个值是否不空
BETWEEN x AND y	判断一个值是否在[x,y]之间
NOT BETWEEN x AND y	判断一个值是否不在[x,y]之间
IN	判断一个值是否是列表中的任意一个值	5
NOT IN	判断一个值是否不是列表中的任意一个值
LIKE	模糊匹配，LIKE一般会搭配通配符“%”或“_”使用，其中“%”表示任意个字符，“_ ”代表一个字符	6,13
REGEXP 或 RLIKE	正则表达式匹配

1.3 逻辑运算符

逻辑运算符
运算符	功能描述	示例
NOT 或！	逻辑非
AND 或 &&	逻辑与	13
OR 或 \|\|	逻辑或	7
XOR	逻辑异或	8

2. 函数

2.1 数学函数

数学函数
函数	功能描述	示例
ROUND(x,y)	返回参数x四舍五入后包含y位小数的值，y不指定时表示四舍五入到整数	9,10

2.2 字符串函数

字符串函数
函数	功能描述	示例
LENGTH(s)	返回字符串s的字节数，和字符集有关	11
LEFT(S,N)	返回字符串s最左边的n个字符	12

select from world

1.Observe the result of running this SQL command to show the name, continent and population of all countries.

2.Show the name for the countries that have a population of at least 200 million. 200 million is 200000000, there are eight zeros.

3.Give the `name` and the per capita GDP for those countries with a `population` of at least 200 million.

4.Show the `name` and `population` in millions for the countries of the `continent` 'South America'. Divide the population by 1000000 to get population in millions.

5.Show the `name` and `population` for France, Germany, Italy

6.Show the countries which have a `name` that includes the word 'United'

7.Show the countries that are big by area or big by population. Show name, population and area.

8.Exclusive OR (XOR). Show the countries that are big by area (more than 3 million) or big by population (more than 250 million) but not both. Show name, population and area.

9.Show the `name` and `population` in millions and the GDP in billions for the countries of the `continent` 'South America'. Use the ROUND function to show the values to two decimal places.

10.Show the `name` and per-capita GDP for those countries with a GDP of at least one trillion (1000000000000; that is 12 zeros). Round this value to the nearest 1000.

11.Show the name and capital where the name and the capital have the same number of characters.

12.Show the name and the capital where the first letters of each match. Don't include countries where the name and the capital are the same word.

13.Find the country that has all the vowels and no spaces in its name.

Equatorial Guinea and Dominican Republic have all of the vowels (a e i o u) in the name. They don't count because they have more than one word in the name.

SQL ZOO 练习打卡 第 2 天

知识点汇总

查询规则

1. 运算符

1.1 算数运算符

1.2 比较运算符

1.3 逻辑运算符

2. 函数

2.1 数学函数

2.2 字符串函数

select from world

1.Observe the result of running this SQL command to show the name, continent and population of all countries.

2.Show the name for the countries that have a population of at least 200 million. 200 million is 200000000, there are eight zeros.

3.Give the name and the per capita GDP for those countries with a population of at least 200 million.

4.Show the name and population in millions for the countries of the continent 'South America'. Divide the population by 1000000 to get population in millions.

5.Show the name and population for France, Germany, Italy

6.Show the countries which have a name that includes the word 'United'