事实上数据库并没有定义DQL,但是一般我们对数据库的操作主要是查询,查多写少。因此我们需要对Query进行重点说明,本小结是写给菜鸟看的哦,已入门的同学们请跳过。
按照下面练习之前,请先下载数据 world.sql

  • select 基本查询
    SELECT  id ,NAME   FROM  world.city;
    SELECT * FROM world.`city`;
      --查询中国(CHN)所有的城市信息
         SELECT * FROM world.`city` WHERE countrycode='CHN';
      --查询中国(CHN)安徽省所有的城市信息。
         SELECT * FROM world.`city`  WHERE countrycode='CHN' AND district='anhui';
      --查询世界上人口数量在10w-20w城市信息
         SELECT * FROM world.`city`  WHERE  population BETWEEN 100000 AND 200000 ;
      --中国或者日本的所有城市信息
         SELECT * FROM world.city WHERE countrycode IN ('CHN','JPN');
      --模糊查询
         SELECT * FROM world.city WHERE countrycode LIKE 'ch%';
     
  • select 排序并限制
      --按照人口数量排序输出中国的城市信息(ASC\DESC)
         SELECT * FROM world.`city` WHERE countrycode='CHN' ORDER BY population ASC;
         SELECT * FROM world.`city` WHERE countrycode='CHN' ORDER BY population DESC;

      -- 按照多列排序人口+省排序
         SELECT * FROM world.`city` WHERE countrycode='CHN'  ORDER BY id DESC  ;
         SELECT * FROM city ORDER BY 5 DESC ;
         SELECT * FROM world.`city` WHERE countrycode='CHN' ORDER BY 5 DESC LIMIT 20;
         SELECT * FROM world.`city` WHERE countrycode='CHN'  ORDER BY 5 DESC LIMIT 10,10 ;
         SELECT * FROM world.`city` WHERE countrycode='CHN'  ORDER BY 5 DESC LIMIT 10 OFFSET 10 ;

  • 表连接查询

         SELECT * FROM country c1  LEFT JOIN city c2 ON c1.code = c2.countryCode WHERE c1.code = 'CHN';
     
  • group by +聚合函数(avg()、max()、min()、sum())
    SELECT countrycode ,SUM(population) FROM city WHERE countrycode = 'chn' GROUP BY countrycode;
  • union 用来替换  or 、in()
    SELECT * FROM world.city WHERE countrycode IN ('CHN','JPN');
    改写为:

    SELECT * FROM world.city WHERE countrycode ='CHN'
    union
    SELECT * FROM world.city WHERE countrycode ='JPN';

最后修改于 2019-08-05 10:07:07
如果觉得我的文章对你有用,请随意赞赏
扫一扫支付
上一篇