mysql数据库(DQL)

mysql数据库的常规操作分为DCL , DDL , DML , DQL

　　今日就让我们探讨其中的DQL吧。

 

DQL(面向数据级别的【查】):
　　A、简单查询
　　　　1、基本语法：
　　　　　  select EXP_1 (as) 别名,...,EXP_N [as] 别名 =》表达式　　　　　　　　　　　　　　　    列裁剪 只列出需要的字段
　　　　　　from TABLE【表】|SUBQUERY【子查询】 [as] 别名 　　　　　　　　　　　　　　　    列范围 只要是数据源即可
　　　　　　where CONDITION【条件】                                                                                           行筛选（裁剪）【where|on[优先]|分组聚合(去重)】
　　　　　　order by EXPR_1【ASC[升序]|DESC[降序]】,... 默认升序                                                  数据排序（多字段排序：左为主，右为次）
　　　　　　limit OFFSET,SIZE【做分页，即：（数据筛选）分段】                                                     行区间裁剪（limit不能用于子查询中）
　　　　　　　　　　OFFSET :指定的偏移量,跳过查询结果中的前几行记录
　　　　　　　　　　SIZE : 从偏移位置开始，返回多少条记录。案例：SELECT * FROM table_name LIMIT 5, 10; 这将跳过前 5 行记录，返回接下来的 10 条记录

　　　　2、别名:
　　　　　　解释：非表的原始部分
　　　　　　语法：(as) 表达式别名
　　　　　　用处：
　　　　　　　　表达式字段   => 表达式别名
　　　　　　　　多表关联       => 表别名
　　　　　　案例：select stu_score (as) score from yb12211;
　　　　3、
　　　　where条件格式[CONDITION]：

　　　　　　1.等于：EXP[表达式]=VAL;
　　　　　　2.不等于：EXP<>VAL;
　　　　　　3.表字段为空值|不为空：EXP is [not] NULL ******
　　　　　　4.关系运算符：EXP>[=]VAL;
　　　　　　　　　　　　　EXP<[=]VAL;
　　　　　　　　　　　　　EXP between VAL_SAMLL and VAL_BIG 《=》 EXP>=VAL_SMALL and EXP<=VAL_BIG
　　　　　　5.范围：EXP in (VAL_1,...,VAL_N | SUBQUERY)【in为范围，满足在范围中的一个即可】
　　　　　　6.逻辑运算符：
　　　　　　　　　　与：and
　　　　　　　　　　或：or
　　　　　　　　　　非：not
　　　　　　7.模糊查询：【面向字符串】
　　　　　　　　　　EXP [not] like '通配符LIKE_EXP'【通配符的位置没有硬性要求】
　　　　　　　　　　通配符：
　　　　　　　　　　　　% : 任意个任意内容 【姓张：name like '张%'】
　　　　　　　　　　　　_ : 一个任意内容 【姓张且名字为两个字：name like '张_'】

　　B、分页查询：页码【哪一页】，页容【每一页显示多少条数据】
　　　　　　　　pageNumber：1 【页码】
　　　　　　　　pageSize: 3 【页容】
　　　　　　1.语法：
　　　　　　　　select cell(count(1)/pageSize) from 表名;
　　　　　　　案例：
　　　　　　　　条件：查第一页，每一页放三条数据
　　　　　　　　select ceil(count(1)/3) as total_page from student;

　　　　　　2.查某一页的数据：
　　　　　　　　select * from student limit (pagenNumber-1)*pageSize,pageSize;
　　　　　　　　案例：
　　　　　　　　　　select * from student limit 0,3;

　　C、分组聚合查询:
　　　　1.语法：
　　　　　　select [(GROUP_EXPR as 别名)*N],(AGGR_FUNC(EXPR) as 别名)*N
　　　　　　from TABLE【表】|SUBQUERY【子查询】 [as] 别名
　　　　　　where CONDITION【条件】　　　　　　　　　　　　 　　　　 =>针对表的原始的行,进行初步筛选
　　　　　　group by (GROUP_EXPR as 别名)*N 　　　　　　　　 　　　　   （多字段分组：左为主，右为次）
　　　　　　having CONDITION【条件】　　　　　　　　　　　　 　　　　 =>针对聚合后的行,进行二次筛选，产生聚合后的新的列
　　　　　　order by EXPR_1【ASC[升序]|DESC[降序]】,... 默认升序 　　　　 数据排序（多字段排序：左为主，右为次）
　　　　　　limit OFFSET,SIZE【做分页】 　　　　　　　　　　　　　　　　 行区间裁剪（limit不能用于子查询中）
　　　　2.注意点：
　　　　　　group by分组后,SELECT 子句中只能包含【被 GROUP BY 列出的列】或者【聚合函数】。否则，会报错。

　　D、子查询：
　　　　1.语法：
　　　　　　select exp1,exp2,(select ...)√ as ALIA,....,expN
　　　　　　from (table ...)√
　　　　　　where exists (select ...)|expA in (select expB ...)√ 【expA===expB】
　　　　　　group by exp1,...
　　　　　　order by exp1,...
　　　　　　limit offset,size
　　　　　　注意：
　　　　　　　　1.exp => field【字段】,partof(field)【字段的一部分】,concat(fa,fb,....)【拼接后的】,cal(field)【数值】....
　　　　　　　　2.打√处，表示可以运用子查询
　　　　　　　　3.【group by】通常与【聚合函数】一起使用
　　　　2.具体讲解：
　　　　　　1.对于select的子查询：
　　　　　　　　案例(两表联系):
　　　　　　　　　　select
　　　　　　　　　　fk_talent_id,
　　　　　　　　　　(select talent_name from yb_talents where talent_id=fk_talent_id) as talent_name,
　　　　　　　　　　subject,
　　　　　　　　　　score
　　　　　　　　　　from yb_score;

　　　　　　2.对于from的子查询：
　　　　　　　　案例(数据表可以由子查询代替)：
　　　　　　　　　　　　select
　　　　　　　　　　　　fk_talent_id,score
　　　　　　　　　　　　from (
　　　　　　　　　　　　　　select fk_talent_id,score
　　　　　　　　　　　　　　from yb_score
　　　　　　　　　　　　　　where subject in ('logicJava','javascript')
　　　　　　　　　　　　) as T
　　　　　　　　　　　　order by score DESC
　　　　　　　　　　　　limit 0,10;

　　　　　　3.对于where的子查询：
　　　　　　　　案例：
　　　　　　　　　　select subject,score
　　　　　　　　　　from yb_score
　　　　　　　　　　where exists(select * from yb_score where subject='oop');

　　E、连接查询：
　　　　1.原则：即可子查询又可连接查询，首选连接查询
　　　　2.解释：
　　　　　　多表联合查询
　　　　　　join：横向拼接表，宽变大
　　　　3.操作：
　　　　　　两个主要形式：
　　　　　　　　1.内连接（交集，都有的数据）：
　　　　　　　　　　语法：
　　　　　　　　　　　　select A.内容,....,B.内容,... =>字段别名：提高筛选的性能
　　　　　　　　　　　　from tablea as A
　　　　　　　　　　　　inner join tableb as B
　　　　　　　　　　　　on A.主键=B.外键 (and A.fa = VALUE;) 多表√ 两表√ =>表进行合并时进行【连接条件】
　　　　　　　　　　　　where A.fa = VALUE; 两表√ =>合并后进行【条件筛选】

　　　　　　　　2.外连接（全集）：
　　　　　　　　　　左外连接：【主表全集，主表都展示】
　　　　　　　　　　　　语法：
　　　　　　　　　　　　　　select A.内容,....,B.内容,... =>字段别名：提高筛选的性能
　　　　　　　　　　　　　　from tablea as M =>左表【主表】
　　　　　　　　　　　　　　left [outer] join tableb as S =>从表
　　　　　　　　　　　　　　on M.主键|外键=B.外键|主键 (and A.fa = VALUE;) 多表√ 两表√ =>表进行合并时进行【连接条件】
　　　　　　　　　　　　　　where A.fa = VALUE; 两表√ =>合并后进行【条件筛选】
　　　　　　　　　　右外连接：【主表全集，主表都展示】
　　　　　　　　　　　　语法：
　　　　　　　　　　　　　　select A.内容,....,B.内容,... =>字段别名：提高筛选的性能
　　　　　　　　　　　　　　from tablea as M =>从表
　　　　　　　　　　　　　　right [outer] join tableb as S =>主表
　　　　　　　　　　　　　　on M.主键|外键=B.外键|主键 (and A.fa = VALUE;) 多表√ 两表√ =>表进行合并时进行【连接条件】
　　　　　　　　　　　　　　where A.fa = VALUE; 两表√ =>合并后进行【条件筛选】
　　　　　　　　　　全外连接：【无主从关系，都展示】
　　　　　　　　　　　　语法：
　　　　　　　　　　　　　　select A.内容,....,B.内容,... =>字段别名：提高筛选的性能
　　　　　　　　　　　　　　from tablea as A
　　　　　　　　　　　　　　full [outer] join tableb as B
　　　　　　　　　　　　　　on A.主键|外键=B.外键|主键 (and A.fa = VALUE;) 多表√ 两表√ =>表进行合并时进行【连接条件】
　　　　　　　　　　　　　　where A.fa = VALUE; 两表√ =>合并后进行【条件筛选】

　　　　　　　　　　笛卡尔积【典型：比赛时间表】：
　　　　　　　　　　　　　　select A.内容,....,B.内容,... =>字段别名：提高筛选的性能
　　　　　　　　　　　　　　from tablea as A
　　　　　　　　　　　　　　cross join tableb as B
　　　　　　　　　　　　　　on A.主键|外键=B.外键|主键 (and A.fa = VALUE;) 多表√ 两表√ =>表进行合并时进行【连接条件】
　　　　　　　　　　　　　　where A.fa = VALUE; 两表√ =>合并后进行【条件筛选】

　　F、联合查询：
　　　　1.解释：
　　　　　　纵向拼接表，高变大
　　　　　　查询字段的数量与类型必须相同，字段名是以第一张表为准。‘
　　　　2.union与union all的区分：
　　　　　　联合 UNION:去重，排序，效率低
　　　　　　联合所有 UNION ALL:不去重，不排序，效率高 √
　　　　3.相关知识点：行转列与类转行。
　　　　　　案例：

　　　　　　　　列转行：
　　　　　　　　　　方法：使用union all 来合并两个或多个select语句
　　　　　　　　　　如：
　　　　　　　　　　　　select stu_name, JavaEE as score,'JavaEE' as subject from temp_wide_score
　　　　　　　　　　　　union all
　　　　　　　　　　　　select stu_name, Hadoop as score,'Hadoop' as subject from temp_wide_score
　　　　　　　　　　　　union all
　　　　　　　　　　　　select stu_name, Spark as score,'Spark' as subject from temp_wide_score
　　　　　　　　　　　　union all
　　　　　　　　　　　　select stu_name, Python as score,'Python' as subject from temp_wide_score
　　　　　　　　　　　　union all
　　　　　　　　　　　　select stu_name, Project as score,'Project' as subject from temp_wide_score;
　　　　　　　　类转行：
　　　　　　　　　　注意：
　　　　　　　　　　　　根据实际需求结合【聚合函数】使用
　　　　　　　　　　操作：
　　　　　　　　　　　　方法一：使用case..when..then语句
　　　　　　　　　　　　如：
　　　　　　　　　　　　　　select user_id,
　　　　　　　　　　　　　　　　sum(case when subject='语文' then score end) as '语文',
　　　　　　　　　　　　　　　　sum(case when subject='数学' then score end) as '数学',
　　　　　　　　　　　　　　　　sum(case when subject='英语' then score end) as '英语'
　　　　　　　　　　　　　　from tb_score　　
　　　　　　　　　　　　       group by user_id;
　　　　　　　　　　　　方法二：使用if()函数
　　　　　　　　　　　　如：
　　　　　　　　　　　　　　select user_id,
　　　　　　　　　　　　　　　　sum(if(subject='语文',score,0)) as '语文',
　　　　　　　　　　　　　　　　sum(if(subject='数学',score,0)) as '数学',
　　　　　　　　　　　　　　　　sum(if(subject='英语',score,0)) as '英语'
　　　　　　　　　　　　　　from tb_score
　　　　　　　　　　　　　　group by user_id;