Mysql分组统计+链接查询+子查询+自然查询+视图

查询操作、完整语法select select选项 表达式 from子句 where子句 group by子句 having子句 order by子句 limit子句group by分组分组语法、group by就是根据某个条件对数据进行分组。语法group by 字段分组之后会对数据进行取第一条。分组主要是用来进行数据统计。统计函数分组统计函数max求最大值min最小值avg平均值count总记录数如果使用count(*)那么是统计记录数如果count(字段)其实也是统计记录数如果改字段某个值为null那么不统计。sum求和count不统计为空的字段以字段为统计单位分组统计原理group_concat将分组统计中的某个字段的所有数据进行连接操作多字段分组group by字段1,字段2….分组排序语法group by 字段 [asc|desc]回溯统计注能理解就去花时间系统根据分组的情况逐层向上回溯最终直到顶层。语法在所有的分组字段之后使用 with rollup;having子句having子句与where子句一样都是用于条件判断的。区别1where是判断数据从磁盘读入内存的时候having是判断分组统计之前的所有条件区别原理区别2having子句中可以使用字段别名而where不能使用区别3having能够使用统计函数但是where不能使用order by子句排序子句对对应的字段进行排序。排序依赖校对集。语法order by 字段 [asc|desc];多字段排序order by 字段 [asc|desc],字段2 [asc|desc];limit子句limit的基本使用用于限制数据的访问量。limit标准使用语法limit offset,length;从指定位置offset开始获取对应长度length条记录limit的应用分页分页必须知道的条件页码每页显示的数据长度length长度offset页码 – 1 * length联合查询联合查询将多个查询的结果进行纵向的联合而不改变字段数只改变记录数。语法select 语句union union选项select 语句union选项与select选项完全一致union选项默认的是distinct去重注意1.union使用的时候必须保证多条查询语句之间的字段数要求一致2.union使用的时候没有数据的类型的概念只有列数相同的概念。union意义1.合并不同表的数据数据量太大导致一张表存不下往往是用于数据的统计2.对同一张表进行数据的不同形式的展示。需求将学生表中1班的学生按照年龄升序排序2班的学生按照年龄降序排序order by使用(select 语句 order by子句)union(select 语句 order by 子句);在union中使用order by必须搭配limit(select 语句 order by子句 limit子句)union(select 语句 order by 子句 limit子句);需求1.获取1班的所有学生select * from student where c_id 1;2.获取1班的所有学生还要获取对应的班级信息连接查询连接查询将两个表中的数据进行字段上的拼接字段数一定会增加。连接查询分为几类内连接外连接交叉连接自然连接连接关键字join左表join关键字左边的表是左表右表join关键字右边的表是右表交叉连接select * from 表1,表2;交叉连接cross join交叉连接得到的结果是笛卡尔积所以应该尽量避免笛卡尔积出现。内连接内连接左表中出现的数据在右表中也同样存在那么记录保存如果不存在就不需要该记录。语法l_table [inner] join r_table on 连接条件没有连接条件的内连接内连接内连接原理内连接的条件匹配可以使用whereon可以用where代替外连接外连接以某一张表为主表拿着表中的所有记录去另外的一张表中匹配如果匹配成功保留全部记录如果匹配失败那么未匹配成功的字段全部置空。语法左外连接左表为主表右表为副表l_table left [out] join r_table on 连接条件右外连接右表为主表左表为副表l_table right [out] join r_table on 连接条件左连接右连接左连接转右连接需求获取学生以及对应的班级信息要求只获取1班的所有的学生自然连接自然连接在连接表的时候不需要指定连接条件系统自动匹配。自然连接包括自然内连接自然外连接语法l_table natural [left/right] join r_table;自然内连接修改student表的name字段之后自然连接在匹配之后只会保留一个同名字段保留的是左表自然外连接内连接和外连接模拟自然连接使用同名字段作为连接条件并且合并同名字段。语法using(字段列表)内连接模拟外连接模拟using模拟多字段自然连接注意实际项目开发中需求上的满足通常是使用内连接和外连接很少使用自然连接和交叉连接。多表连接与两张表完全一样A join B on A.字段 B.字段 join C on A.字段 C.字段 …需求1.查出所有1班的学生select * from student where c_id 1;2.查出所有PHP141115班的学生解决方案1select * from student s left join class c on s.c_id c.id where c.name ‘PHP141115’;解决方案2找出php141115对应的班级idselect id from class where name ‘PHP141115’;通过id找出所有该班级学生select * from student where id ?;子查询一条查询语句出现在另外一条查询语句的内部这条语句就被称之为子查询语句。子查询分类子查询可以根据子查询返回的结果以及子查询出现的位置两种方式进行分类按结果分类标量子查询子查询返回的结果是一行一列一个字段的某一个值列子查询子查询返回的结果是一列多行一个字段有多个值行子查询子查询返回的结果是一行多列多行多列表子查询子查询返回的结果多行多列按照位置分类where子查询子查询出现在where条件之后from子查询子查询出现在from之后exists子查询出现在exists之后exists出现在where之后标量子查询子查询返回的结果是一个标量列子查询子查询返回的结果是一列。需求获取所有班级的所有学生学生必须在班级中存在。select * from student where c_id is not null; -- 无法解决解决方案1.获取所有的现有班级的idselect id from class;2.从学生表中查出所有数据判断学生的班级id是否在刚查出来的班级id中存在集合判断条件理解性知识集合1,2,3,4,5any任意一个1 any(集合)只要结果在集合中出现过就返回trueall全部必须满足全部条件才返回真some等于其中的一部分与any完全一致行子查询子查询返回的结果是一行多列需求找出所有班级中年龄最大同时身高最高的学生行子查询必须构建行元素有多个字段的元素select * from 表名 where (字段1,字段2…) /in (select 字段1,字段2… from 表名);表子查询表子查询从返回结果的层面上讲与行子查询完全一样。因为其出现的位置不是在where之后而是在where之前from之后。from后接数据源。需求求出每个班中身高最高的1个学生。表子查询出现的原因因为某些时候希望order by在group by之前先执行。视图视图视图是一张虚拟表存在表结构但是没有数据。视图关键字view创建视图语法create view 视图名字 as select语句;视图创建之后发生了什么1.创建视图结构虚拟表2.在数据库对应的文件夹下创建结构文件视图是虚拟表只有结构没有数据。视图查看1.视图可以像表一样的查看show tables/show create table/desc 视图名2.可以通过视图查看创建语句的方法show create view 视图名;3.查看视图数据与查看表完全一致视图不保存数据数据的来源指的是当视图被调用的时候系统会自动调用视图的创建语句中的select语句去执行。修改视图视图修改的本质是修改视图的数据来源。语法alter view 视图名字 as select语句;删除视图语法drop view 视图名字;视图作用1.节省查询语句的长度2.对外提供访问接口保证数据表基表的数据安全性。视图能够选择性的从基表获取数据并提供给外部。3.对外友好性视图能够对外提供不同的数据信息。不同的接口定义不同的视图视图数据增删改视图可以为基表进行数据的增删改操作必须满足以下条件视图新增数据1.视图的数据来源基表只能有一个多表视图不能插入数据2.视图中的所有字段必须包含了基表中不为空或者没有默认值的全部字段。更新数据基本没有限制单表视图更新多表视图更新删除数据与插入数据条件一致只有单表视图可以删除多表不能删除单表视图删除多表视图删除视图算法视图在执行的过程中视图被查询到底是如何去执行视图对应的查询语句。视图算法分为三种合并merged先将视图的SQL查询与与外部的查询语句进行语法合并临时表temptable先执行视图里面的查询语句结果变成一个临时表未定义undefined系统自己判断到底使用合并还是临时表默认的算法指定语法create view algorithm 算法 视图名字 as select语句