【程序员笔试】+数据库+sql

2018-07-20 本文已影响1人 supermans1202

练习mysql的网站

---- 1

SELECT population FROM world WHERE name = 'France'
选出法国的人口

where 过滤条件
- 限定范围：< > = <> betwee
- 字符串：like "%_string%"
  + 相关函数：concat(name,"name")拼接，replace

----- 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)

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 分类：