MySQL基础--3

作者: Czw_hacker | 来源:发表于2016-10-27 17:38 被阅读31次

    分组统计

    • group by--分组

      语法结构为

      SELECT vend_id,COUNT(*) FROM products(表名) GROUP BY vend_id(执行分组的列名);

      SSELECT vend_id,COUNT(* ) FROM products(表名) GROUP BY vend_id(执行分组的列名) HAVING COUNT(*) > 2(分组过后数据筛选条件);

      其中用到了having来进行数据筛选,having和where的区别在于having用于group by的过滤,而where用于分组前的过滤

      order by--排序

      用于对数据的排序分为升序(默认为升序)和降序(加desc),语法结构为

      SSELECT vend_id,COUNT(* ) FROM products(表名)
      GROUP BY vend_id(执行分组的列名)
      HAVING COUNT(*) > 2(分组过后数据筛选条件)
      ORDER BY COUNT(*)(执行排序的列名)
      DESC(加desc为降序)

    • limit--分页

      SSELECT vend_id,COUNT(* ) FROM products(表名)
      GROUP BY vend_id(执行分组的列名)
      HAVING COUNT(*) > 2(分组过后数据筛选条件)
      ORDER BY COUNT(*)(执行排序的列名)
      DESC(加desc为降序)
      LIMIT 0 ,3(0表示从第0位置向下一个位置显示,3表示显示三个记录)

    • 查询语句顺序

      1.select

      2.from

      3.where

      4.group by

      5.having

      6.order by

      7.limit

    子查询

    • 嵌套在查询中的查询

      语法结构为

      SELECT cust_name FROM customers WHERE cust_id
      IN(SELECT order_num FROM orderitems WHERE prod_id = "TNT2")
      (in后边的查询为第一个select查询的条件由单纯的条件变为一个语句)

    连接查询

    Paste_Image.png
    • 内连接和等值连接

      等值查询

    Paste_Image.png

    > SELECT ts.id AS 'stuid',stu_name,tc.id AS 'class_id',class_name
    > FROM t_student AS ts,t_class AS tc
    > WHERE ts.class_id = tc.id

     (ts为表一名字,tc为表二名字)
    
    内连接 inner join
    > SELECT ts.id AS ' stuid',stu_name,tc.id AS 'class_id',class_name
    > FROM t_student AS ts
    > INNER JOIN t_class AS tc
    > ON ts.class_id = tc.id
    
    (ts为表一名字,tc为表二名字)
    
    • 左外链接
    Paste_Image.png

    > SELECT ts.id AS ' stuid',stu_name,tc.id AS 'class_id',class_name
    > FROM t_student AS ts
    > LEFT JOIN t_class AS tc
    > ON ts.class_id = tc.id

    (ts为表一名字,tc为表二名字)
    左连接会显示左表的所有数据
    
    • 右外连接
    Paste_Image.png

    > SELECT ts.id AS ' stuid',stu_name,tc.id AS 'class_id',class_name
    > FROM t_student AS ts
    > RIGHT JOIN t_class AS tc
    > ON ts.class_id = tc.id

    (ts为表一名字,tc为表二名字)
    右连接会显示右表所有信息
    
    • 组合查询

      1.union必须由两条或两条以上的select语句组成,语句之间使用union分割

      2.union的每个查询必须包含相同的列,表达式或聚合函数

      3.列的数据类型必须兼容:类型不必完全相同,但是必须是相互可以转换的

      4.union查询会自动去除重复的行,如果不需要此特性,可以使用union all

      5.对union结果进行排序,order by语句必须在最后一条select语句之后

      SELECT vend_id FROM vendors
      UNION ALL
      SELECT vend_id FROM products;

    相关文章

      网友评论

        本文标题:MySQL基础--3

        本文链接:https://www.haomeiwen.com/subject/ilvkuttx.html