SQL学习-sqlzoo练习题记录(1)
来源:互联网 发布:笔记本密码重设软件 编辑:程序博客网 时间:2024/06/03 19:51
0-SELECT 基础
1)SELECT population FROM world WHERE name='Germany'
2)SELECT name, gdp/population FROM world WHERE area > 5000000
3)SELECT name, population FROM world WHERE name IN ('Ireland', 'Iceland', 'Denmark')
注意点:IN的用法,检查是否在列表中
4)SELECT name, area FROM world WHERE area BETWEEN 200000 AND 250000
注意点:BETWEEN…AND… 包括首尾
1-SELECT name
1)SELECT name FROM world WHERE name LIKE 'Y%'
2)SELECT name FROM world WHERE name LIKE '%Y'
3)SELECT name FROM world WHERE name LIKE '%x%'
4)SELECT name FROM world WHERE name LIKE '%land'
5)SELECT name FROM world WHERE name LIKE 'C%ia'
6)SELECT name FROM world WHERE name LIKE '%oo%'
7)SELECT name FROM world WHERE name LIKE '%a%a%a%'
注意点:% 替代0个或多个字符,注意第3题和第7题
常用通配符[],[^](任何单一字符)
8)SELECT name FROM world WHERE name LIKE '_t%' ORDER BY name
9)SELECT name FROM world WHERE name LIKE '%o__o%'
10)SELECT name FROM world WHERE name LIKE '____'
注意点:_ 替代1个字符
11)SELECT name FROM world WHERE capital=name
错误点:是’=’,非’==’
12)SELECT name FROM world WHERE capital=concat(name,' City')
注意点:concat(,) ,连接作用,注意str需要加上”
13)SELECT capital,name FROM world WHERE capital like concat('%',name,'%')
错误点:通配符没有加上’ ’
14)SELECT name,capital FROM world WHERE capital like concat(name,'_%')
15)SELECT name,replace(capital,name,'') FROM world WHERE capital LIKE concat(name,'%_')
错误点:读题仔细,concat在这里记得与LIKE搭配
注意点:REPLACE(家庭,原配,小三);MID(字段,开始位置[长度])
问题:MID()如何解决
2-SELECT FROM world
1)SELECT name, continent, population FROM world
2)SELECT name FROM world WHERE population>200000000
3)SELECT name,gdp/population FROM world WHERE population>=200000000
4)SELECT name,population/1000000 FROM world WHERE continent='South America'
5)SELECT name,population FROM world WHERE name IN ('France','Germany','Italy')
6)SELECT name FROM world WHERE name like '%United%'
7)SELECT name,population,area FROM world WHERE area>3000000 OR population>250000000
8)SELECT name,population,area FROM world WHERE (area>3000000 OR population>250000000) and (name not IN ('United States','India','China') )
注意点:多个条件用()表示
9)SELECT name,ROUND(population/1000000,2),ROUND(gdp/1000000000,2) FROM world WHERE continent='South America'
10)SELECT name,ROUND(gdp/population,-3) FROM world WHERE gdp>1000000000000
注意点:ROUND(,返回小数位数|可为负数)
正数是保留的小数点的位数
负数是向左进行保留,默认0
11)
SELECT name, CASE WHEN continent='Oceania' THEN 'Australasia' ELSE continent ENDFROM world WHERE name LIKE 'N%'
12)
SELECT name,CASE WHEN continent IN('Europe','Asia') THEN 'Eurasia' WHEN continent IN('North America','South America','Caribbean') THEN 'America' ELSE continent ENDFROM worldWHERE name LIKE 'A%' OR name LIKE 'B%'
13)
SELECT name,continent,CASE WHEN continent IN ('Eurasia', 'Turkey') THEN 'Europe/Asia' WHEN continent = 'Oceania' THEN 'Australasia' WHEN continent = 'Caribbean' THEN CASE WHEN name LIKE 'B%' THEN 'North America' ELSE 'South America' END ELSE continent ENDFROM worldORDER BY name ASC
注意点:
简单Case函数:
CASE XXX WHEN 条件1 THEN True返回值WHEN 条件2 THEN True返回值…… ELSE 其他条件 ENDWHERE
Case搜索函数
CASE WHEN XXX+条件1 THEN True返回值WHEN XXX+条件2 THEN True返回值…… ELSE 其他条件 ENDWHERE
用于计算条件列表的表达式,并返回可能的结果之一
case后加表达式——根据表达式结果返回
case 后不加表达式——根据When条件返回
Case函数只返回第一个符合条件的值,剩下的Case部分将会被自动忽略
- SQL学习-sqlzoo练习题记录(1)
- SQL学习-sqlzoo练习题记录(2)
- SQL学习-sqlzoo练习题记录(3)
- 【学习记录】练习题-二分查找
- sqlzoo
- SQL Server 练习题1
- SQL Server 练习题1
- SQL Server 练习题1
- SQL Server 练习题1
- SQL Server 练习题1
- SQL Server 练习题1
- SQL Server 练习题1
- SQL Server 练习题1
- SQL Server 练习题1
- SQL Server 练习题1
- SQL Server 练习题1
- SQL Server 练习题1
- SQL Server 练习题1
- 第八章编程练习第一题:设计一个程序,统计从输入到文件结尾为止的字符数
- Redis 前身 MemCache
- 基于Unity3D的相机功能的实现(一)——VR相机
- Anaconda常用命令
- maven仓库管理
- SQL学习-sqlzoo练习题记录(1)
- 如何利用SEO建设企业的品牌与口碑
- 排序算法(Java)——那些年面试常见的排序算法
- Java连接Mysql数据库警告:Establishing SSL connection without server's identity verification is not recommend
- android webView 的 webSettings 最全的说明
- java打压缩包工具方法分享
- MVP+Rectrofit+RXJAVA+Fresco
- 使用ueditor小结
- Springboot 外置配置详解