MySQL 学习笔记3——增删改查、连表

IINSERT 增加:

　　insert into 表名(列名1,列名2.....) values(列1插入值，列2插入值...),(列1插入值,列2插入值)...

　　insert into 表名1(列名1,列名2)  values( select 列名3,列名4 from 表名2);

DELETE TRUNCATE 删除：

　　delete from 表名 where 条件

　　trancate from 表名 where 条件

　　两者区别:delete删除的数据，auto_increment的值不会发生变化，而truncate删除的值则会使auto_increment减少。

DROP 删除表:

    drop table 表名;

UPDATE 更改：

    update 表名 set 列名1= 值1，列名2=值2,....where 条件

SELECT 搜索:

    select */列名1,列名2,列名3,常数...from 表名 where 条件1 or/and 条件2;

　　IN (NOT IN）例子：

　　　　① select sid from student where (not) in (2,4); 

　　　　② select sid from student,number where in (select student_id,number from course having avg(number)>60 group by student_id);

　　BETWEEN AND  例子： 

    　　select sid from student where between 2 and 4;    

　　模糊匹配LIKE 与通配符%_ 例子：

　　　　 ① select name from tb1 where name like 'a%'; -- 表示选出以a为开头的字符串，%可指代多个字符串。

　　　　 ② select name from tb1 where name like '%a%'; -- 中间含有a的字符串

　　　　 ③ select name from tb1 where name like 'a_';  -- 以a为开头的长度为2字符串，_表示单个字符串。

　　分页显示 LIMIT 例子:

　　　　① select * from tb1 limit 10; -- 查找前10条

　　　　② select * from tb1 limit 0,10; -- 从0开始往后找10条

　　　　③ select * from tb1 limit 10 offset 0; -- 从0开始往后找10条

　　顺序 ORDER BY 例子：

　　　　① select * from tb1 order by sid asc/desc; -- 按照sid 从小到大/从大到小 顺序排列

　　　　② select * from tb1 order by sid asc, course_id desc; -- 首先先按照sid从小到大顺序排列，在这基础上，再基于course_id从大到小的顺序排列。

　　　　③ select * from tb1 order by sid asc, course_id desc limit 10;  -- 在排序基础上进行分页，从而可以取后10条

　　分组GROUP BY、聚合函数与聚合筛选HAVING ：

　　　　除了group by 基于的列以外，其余列需要使用max min avg count sum等聚合函数进行聚合后。

　　　　在基于上述聚合函数进行筛选时，必须使用having 关键词。

　　　　例：

　　　　　　① select sid from tb1 group by course_id; -- 会报错，因为对于同一个course_id

　　　　　　② select student_id from f group by student_id having avg(num) >60; -- 找出平均成绩大于60的学生的学生ID

　　GROUP BY, HAVING, WHERE, ORDER BY执行顺序：

　　　　执行顺序:Where, Group By, Having, Order by

　　　　1、 首先where将最原始记录中不满足条件的记录删除(所以应该在where语句中尽量的将不符合条件的记录筛选掉，这样可以减少分组的次数)

　　　　2、 然后通过Group By关键字后面指定的分组条件将筛选得到的视图进行分组

　　　　3、 接着系统根据Having关键字后面指定的筛选条件，将分组视图后不满足条件的记录筛选掉

　　　　4、 最后按照Order By语句对视图进行排序，这样最终的结果就产生了。

　　连表查询 LEFT/INNER/RIGHT JOIN：

　　　　1、不使用join关键词时

　　　　　　select * from 表1，表2； -- 这时返回两个表之间的笛卡尔积，即将任意两行之间进行组合

　　　　　　select * from 表1, 表2 where 表1.列1=表2.列2; --  这时基于列1，列2中的相等值进行连表

　　　　2、使用join关键词时

　　　　　　select * from 表1 left/inner/right join 表2 on 表1.列1 = 表2 .列2;  -> left join会将表1 中的各行全显示，right join 会将表2中的各行全显示（若有些值为独有，则另一个表中各列的信息显示为NULL，inner join 则会将存在NULL的行隐藏。

　　　　　　例子：多表连表

　　　　　　select *  from score 

　　　　　　left join student on student.sid = score.student_id

　　　　　　left join course on course.cid = score.course_id

　　　窗口函数Over

　　　　开窗函数 over(partition by 分类类别 order by 排序类别) as 类名

　　　　   常用的窗口开窗函数:  （1）专属的窗口函数①row_number() ② rank() ③ dense_rank()

　　　　　　　　　　   　　　　（2）聚合函数 sum,avg,min,max....

　　　　

　　　　  窗口函数的结果为生成一个新列，用于对数据进行一个动态的分析和统计。

　　　　专属窗口函数会返回排名，在三种不同专属窗口函数中，row_number()直接返回在当前排序中的行数，rank()会在考虑并列下计算排名，dense_rank()则会在不考虑并列下的排名。具体如下图所示，来源(https://zhuanlan.zhihu.com/p/92654574)。

         

 

　　　　基于上述结果便可以得到各分组中前N条的数据。

　　　　select * from
　　　　(select *,
　　　　row_number/rank/dense_rank() over(partition by 分类类别 order by 排名类别) as ranking)
　　　　where ranking >n; -- n为想要得到的排名数

　　　　而其余窗口函数则会返回基于排序结果得到的当前统计量（最小值、平均数等），从分析每一行数据，对最终统计结果的影响，如下图所示。