我们的表是这样子的:
找到列名为xxx那条数据的某一列的值:
select population from world where name =='France’
找到name为(‘Sweden’, ‘Norway’,‘Denmark’)这些数据对应的某些列( name, population )的值:
SELECT name, population FROM world WHERE name IN (‘Sweden’, ‘Norway’,‘Denmark’)
选择某些列,这些数据的area值在20w - 25w之间。
SELECT name, area FROM world WHERE area BETWEEN 200000 AND 250000
where 中使用运算方式:
SELECT name FROM world WHERE population/100000000 >2
查询的时候创建新的列:
select name,GDP/population from world where population/100000000 >2
找到某列中包含某内容的字段的数据:
select name from world where name like '%United%'
两种条件的或关系:
select name,population,area from world where area>3000000 or population>250000000
异或(两种条件只满足一个):
select name,population,area from world where area>3000000 xor population>250000000
保留小数位:
select name,round(population/1000000,2),round(GDP/1000000000,2) from world where continent = ‘South America’
找到某两列的字段值字符串长度相同的数据:
select name,capital from world where length(name) =length(capital)
左面取第一个字符进行判断:
SELECT name,capital FROM world where left(capital,1)=LEFT(name,1) and capital!=name
对数据进行排序:
select winner,yr,subject from nobel where winner like ‘Sir%’ ORDER BY winner
select后使用select,筛选出目标列用来判断:
SELECT name FROM world WHERE population > (SELECT population FROM world WHERE name=‘Russia’)
字符串联接:
select name,concat(round(population*100/(select population from world where name = ‘Germany’),0),’%’) from world where continent = ‘Europe’
大于选取数据中的每一个:
SELECT name,gdp FROM world WHERE gdp >= ALL(SELECT gdp FROM world WHERE continent = ‘Europe’)
选取中设置变量以控制在某列特征相同的条件下选取:
SELECT continent, name, area FROM world x
WHERE area >= ALL
(SELECT area FROM world y
WHERE y.continent=x.continent
AND area>0)
关于join:
inner join:内连接,匹配的才进行连接。结果集为a和b的交集。
根据id值将A和B表进行内连接。
select * from Table A inner join Table B on Table A.id=Table B.id
left join:左连接,按照前面的表进行连接,前面表中的所有项都会有,后面表没有在前面表中出现的显示null。
根据id进行连接。
select * from Table A left join Table B on Table A.id=Table B.id
right join:右连接,和左连接的效果正好相反,这里不再举例。
full outer join:全连接,从两个表返回所有的行,如果没有的地方用null代替。
根据id进行连接。
select * from Table A full outer join Table B on Table A.id=Table B.id
代码实例:
根据goal和eteam的id进行内连接,显示出gtime小于10的数据:
SELECT player, teamid,coach, gtime FROM goal join eteam on (teamid = id) WHERE gtime<=10
选择日期和队伍名从((通过eteam的id和game的team1列)将game和eteam内连接表)找到教练是f:
SELECT mdate,teamname FROM game join eteam on (team1 = eteam.id) where coach = ‘Fernando Santos’