美文网首页
04-DQL数据查询语言

04-DQL数据查询语言

作者: UncleZ_strive | 来源:发表于2019-12-20 21:08 被阅读0次

    1.DQL单表数据查询

    --------------------------------------------------------------------------------------------old guo

    1.SELECT

    1.SELECT 单独使用

    • 查询数据库的参数
    SELECT @@port;
    SELECT @@datadir;
    SELECT @@basedir;
    SELECT @@innodb_flush_log_at_trx_commit;
    SHOW VARIABLES  LIKE '%trx%';
    
    • 调用内置函数.
    SELECT DATABASE();     查询当前所在数据库
    SELECT NOW();       查看时间
    SELECT CONCAT(USER,"@",HOST) FROM mysql.user;      合并
    SELECT GROUP_CONCAT(xid) FROM student;      将列合并
    SELECT SUM(xid) FROM student;    xid列求和
    
    • 简易计算器
      SELECT 4*5;

    2.select 配合其他子句使用

    子句列表介绍

    FROM     -- 查询对象(表,视图)
    WHERE    -- 过滤子句(grep)
    GROUP BY -- 分组子句(统计分析类)
    ORDER BY -- 排序子句
    HAVING   -- 后过滤子句
    LIMIT    -- 限制子句(分页子句)
    
    1.配合FROM应用
    • 1.查询表中所有数据(cat)
      SELECT * FROM city;
      1. 查询name和population信息 (awk取列)
        SELECT NAME,population FROM city;
    2.select+ from + where(grep)使用
    • where 配合等值查询
      1.查询中国所有的城市信息
      SELECT * FROM city WHERE countrycode='CHN';
      2.查询ID为100的城市信息
      SELECT * FROM city WHERE id=100;
      3.查询 中国河北省的城市信息
      SELECT * FROM city WHERE countrycode='CHN' AND district='hebei';
      4.查询 中国或者美国的城市
      SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA';
      或者:
      SELECT * FROM city WHERE countrycode IN ('CHN','USA');
      或者:
      SELECT * FROM city WHERE countrycode='CHN' UNION ALL SELECT * FROM city WHERE countrycode='USA' ;
    • where 配合范围查询
      1.查询人口数量小于100人的城市
      SELECT * FROM city WHERE population<100;
      2.查询人口数量100w-200w之间的
      SELECT * FROM city WHERE population>=1000000 AND population<=2000000 ;
      或者:
      SELECT * FROM city WHERE population BETWEEN 1000000 AND 2000000 ;
      3.查询国家代号是CH开头的城市信息
      SELECT * FROM city WHERE countrycode LIKE 'CH%';
    3.group by 分组子句+聚合函数应用

    聚合函数?

    COUNT()  -- 计数
    SUM()    -- 求和
    AVG()    -- 求平均值
    MAX()    -- 求最大值
    MIN()    -- 最小值
    GROUP_CONCAT() -- 聚合列值 
    
    • 结果集显示特点: 必须是1v1,不能是1vN
      1.统计一下每个国家的人口总数
      SELECT countrycode,SUM(population) FROM city GROUP BY countrycode;
      2.统计中国每个省的人口总数
      SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district;
      3.统计下中国每个省的城市个数及城市名.
      SELECT district,COUNT(NAME),GROUP_CONCAT(NAME) FROM city WHERE countrycode='CHN' GROUP BY district;
      4.统计每个国家城市个数
      SELECT countrycode ,COUNT(NAME) FROM city GROUP BY countrycode;
    4.having 后判断

    1.统计中国每个省的人口总数,只显示总人口数大于500w的省信息.
    SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district HAVING SUM(population) >=5000000;

    6.order by 排序子句

    1.查询中国所有城市信息,人口数从大到小排序输出.
    SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC ;

    2..查询中国所有城市信息,按城市名排序.
    SELECT * FROM city WHERE countrycode='CHN' ORDER BY NAME;

    3.查询中国所有省的总人口,并按总人口数从大到小排序输出.

    SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district ORDER BY SUM(population) DESC;

    5.limit 分页限制子句

    limit m offset n 跳过n行显示m行

    查询中国所有省的总人口,并按总人口数从大到小排序输出.

    SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district ORDER BY SUM(population) DESC LIMIT 5 OFFSET 1;

    SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district ORDER BY SUM(population) DESC LIMIT 10;

    SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district ORDER BY SUM(population) DESC LIMIT 1,5;

    注意: LIMIT 谨慎使用, 500w+的表.
    LIMIT 5000000,100
    一般会改为明确查找范围

    2.DQL多表数据查询

    1.介绍
    内连接  inner join
    外连接  left  join right join
    笛卡尔  
    
    2.作用

    聚合多张表数据,实现查询需求

    3.多表连接的语法
    • 内连接(交集)
    FROM A 
    INNER JOIN B 
    ON  A.x=B.y
    
    • 外连接
    FROM A 
    LEFT JOIN B 
    ON  A.x=B.y
    
    FROM A 
    RIGHT JOIN B 
    ON  A.x=B.y
    

    1.3.3 笛卡尔乘积

    FROM A 
    JOIN B 
    
    4.多表连接例子

    套路1. 找关联表 2. 找关系列

    • 查询人口数小于100人城市名,国家名,国土面积?
    SELECT 
      city.name,
      country.name,
      country.surfacearea,
      city.population 
    FROM
      city 
      JOIN country 
        ON city.countrycode = country.code 
    WHERE city.population < 100  
    
    • 统计查询每位学员的平均分
    SELECT student.xid ,student.xname,AVG(score.score)   
    FROM  student
    JOIN score 
    ON student.xid=score.xid
    GROUP BY student.xid,student.xname
    
    • 统计每位学员学习了几门课
    SELECT student.xid ,student.xname,COUNT(score.score)   
    FROM  student
    JOIN score 
    ON student.xid=score.xid
    GROUP BY student.xid,student.xname 
    
    • 每位老师教的学生的个数和姓名列表
    SELECT teacher.tname,COUNT(student.xid),GROUP_CONCAT(student.xname)
    FROM student 
    JOIN score 
    ON student.xid=score.xid 
    JOIN course
    ON score.cid=course.cid
    JOIN teacher 
    ON course.tid=teacher.tid
    GROUP BY teacher.tid,teacher.tname;
    
    5.left/right join 外连接应用
    • 一般应用在强制驱动表时,强制小结果集驱动大表
    • 生产中可以使用left join 强制 驱动表.尽量减少next loop的出现.
    • 为什么要强制? inner join 优化器 自动选择, 按照索引选择的几率较大
    6 补充 别名的应用 .
    • 列别名
    SELECT 
    course.`cname` AS 课程名称,
    GROUP_CONCAT(CASE WHEN score.`score` >= 85 THEN student.xname END )  AS "优秀",
    GROUP_CONCAT(CASE WHEN score.`score` >=70 AND score.`score` < 85  THEN student.xname  END) AS "良好",
    GROUP_CONCAT(CASE WHEN score.`score` >=60  AND score.`score` <70 THEN  student.xname  END  )AS "一般",
    GROUP_CONCAT(CASE WHEN score.`score` <60  THEN  student.xname END  ) AS "不及格"
    FROM student 
    JOIN score
    ON student.xid = score.xid 
    JOIN course 
    ON score.`cid`=course.`cid`
    GROUP BY course.`cid`;
    

    说明: 1. 为了显示的好看. 2. 可以在 having 或 order by 子句中调用

    • 表别名
    SELECT 
    CONCAT(te.tname,"_",te.tid) AS "教师名",GROUP_CONCAT(CONCAT(st.xname,":",sc.score))
    FROM teacher as te 
    JOIN course as  co 
    ON te.`tid`=co.`tid`
    JOIN score as sc 
    ON co.`cid`=sc.`cid`  
    JOIN student  as st
    ON sc.`xid`=st.`xid`
    WHERE sc.`score`<60
    GROUP BY te.tid,te.tname;
    

    相关文章

      网友评论

          本文标题:04-DQL数据查询语言

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