http://zh.sqlzoo.net/wiki/SELECT_basics
SQL Correlated Subqueries
https://www.geeksforgeeks.org/sql-correlated-subqueries/
[转]sql子查询 嵌套SELECT语句
https://www.cnblogs.com/wangshenhe/archive/2012/11/28/2792093.html
- 基本
- select查询结果作为范围
- 子查询
- sum等函数
- 分组
- 联接
---- 1
SELECT population FROM world WHERE name = 'France'
选出法国的人口
- where 过滤条件
- 限定范围:< > = <> betwee
- 字符串:like "%_string%"
+ 相关函数:concat(name,"name")拼接,replace
- select 显示视图
- from 指定查询的表
----- 2
-
查询比Russia大的城市
SELECT name FROM world WHERE population > (SELECT population FROM world WHERE name='Russia') -
查找最大的城市
SELECT name FROM world WHERE population >= ALL(SELECT population FROM world WHERE population>0)
- 获得所有欧洲的gdp,然后找比这些gdp都高的城市
SELECT name FROM world WHERE gdp >= ALL (SELECT gdp FROM world WHERE gdp>0 and continent like 'Europe') and continent not like 'Europe'
---- 3
-
对于每一个行,找到该行相同地区的所有区域面积,如果区域面积是这里面最大的,则保留该行。然后显示该行数据的地区,名字和区域。
SELECT continent, name, area FROM world x WHERE area >= ALL (SELECT area FROM world y WHERE y.continent=x.continent AND area>0) -
选择大于等于该州平均大小十倍的国家
SELECT continent, name, area FROM world x WHERE area >= (SELECT AVG(area)10 FROM world y WHERE y.continent=x.continent
AND area>0) order by continent*
- 列出人口<=25000000的地区
select DISTINCT continent from world x where 25000000 >= All(select population from world y where y.continent=x.continent)
- 选出是其余城市三倍的城市
select name, continent from world x where population > ALL(select population*3 from world y where y.continent=x.continent and y.name not like x.name)
---- 4
Using SUM, Count, MAX, DISTINCT and ORDER BY.
SUM: 求和
Count:计数
Max:最大
Distinct:去重
order by 排序
---- 5
-
按分组进行计数
SELECT continent, COUNT(name)
FROM world
GROUP BY continent -
计算总人数在500000000的地区
SELECT continent, SUM(population)
FROM world
GROUP BY continent
HAVING SUM(population)>500000000
---6
select name from casting join actor on(actor.id=casting.actorid)
where movieid=11768
作用于内键和外键相同的时候。
join 分类:
- outer
- left:左表全有
- right:右表全有
- full
- inner:指定相等
- natural:默认相等
- cross:笛卡尔
网友评论