20200713学习Mysql总结(一)

Mysql数据库

关系数据库的特点

数据结构化存储在二维表中

支持事务的原子性A，一致性C，隔离性I，持久性D 特性

支持使用SQL语言对存储在其中的数据进行操作

宽表模式:

把对象的属性全部存储在一个表中

缺点：
数据冗余：相同的数据在一个表中出现了多次

数据更新异常：修改一行中某列的值时，同时修改了多行数据

数据插入异常：部分数据由于缺失主键信息而无法写入表中

数据删除异常：删除某一数据时不得不删除另一数据

宽表模式的应用场景：

配合列存储的数据报表应用

三大范式：

第一范式：表中的所有字段都是不可再分的

第二范式: 表中必须存在业务主键，并且非主键依赖于全部业务主键

第三范式:表中的非主键列之间不能相互依赖

MYISAM 跟 INNODB

5.6之前跟5.6之后

MYISAM不支持事务

InnoDB存储引擎的特点

事务型存储引擎支持ACID

数据按主键聚集存储

支持行级锁及MVCC

支持Btree和自适应Hash索引

支持全文和空间索引

truncate table

rename table

UNION ALL  查询含有mysql 学习人数小于5000且 不含有mysql学习人数大于5000
select title,study_cnt from imc_course where title like '%mysql%' and study_cnt < 5000 union all select title, study_cnt from imc_course where title not like '%mysql%' and study_cnt > 5000;

select a.course_id,a.title,b.chapter_name
from imc_course a
join imc_chapter b on b.course_id = a.course_id
where title ='SQLServer课程-98961';

select a.course_id,a.title,b.chapter_name
from imc_course a
left join imc_chapter b on b.course_id = a.course_id
where title ='SQLServer课程-98961';

查询只存在于课程表中，但是不存在于章节表中的课程的课程名称和课程ID信息

select a.course_id,a.title
from imc_course a 
where course_id not in (select b.course_id from imc_chapter b);

select a.course_id,a.title
from imc_course a 
left join imc_chapter b on b.course_id = a.course_id
where b.course_id is null;

SQL LEFT JOIN 关键字
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。

group by ... having 子句的作用
把结果集按某些列分成不同的组，并对分组后的数据进行聚合操作

select level_name, count(1) from imc_course a join imc_level b on b.level_id = a.level_id
group by level_name;

统计每个分类下不同难度的课程的数量
select level_name,class_name,count(*)
from imc_course a 
join imc_class b on b.`class_id`=a.class_id
join imc_level c on c.level_id = a.level_id
group by level_name,class_name;

统计每个分类下课程大于3门的难度有哪些

select level_name,class_name,count(*)
from imc_course a 
join imc_class b on b.`class_id`=a.class_id
join imc_level c on c.level_id = a.level_id
group by level_name,class_name having count(*) >3;

分组函数
--统计课程表的总课程数 以及总共几个讲师 --distinct
select count(course_id)
,count(distinct user_id)
from imc_course;

统计出所有课程总的学习人数
select level_name,sum(study_cnt)
from imc_course a 
join imc_level b on b.level_id = a.level_id
group by level_name order by sum(study_cnt) desc;

统计出每门课平均学习人数
select avg(study_cnt) from imc_course;

利用课程评价表中的评分，更新课程表中的课程的评分
select course_id
    ,avg(content_score) as avg_content
    ,avg(level_score) as avg_level
    ,avg(logic_score) as avg_logic
    ,avg(score) as avg_score
from imc_classvalue
group by course_id;

查询出学习人数最多的课程
select title 
from imc_course
where study_cnt =( select max(study_cnt) from imc_course);

--查询出每门课程的学习人数并按学习人数从高到低排列

select title, study_cnt
from imc_course 
order by study_cnt desc;

分页返回课程id和课程名称，每页返回10行记录

select course_id,title
from imc_course
order by study_cnt desc
limit 0,10;

视图

定义一个包括课程ID,课程名称，课程分类，课程方向以及课程难度的视图

create view vm_course
as
select a.course_id,a.title,b.`class_name`,c.type_name,d.level_name
from imc_course a
join imc_class b on b.`class_id` = a.class_id
join imc_type c on c.type_id = a.type_id
join imc_level d on d.level_id = a.level_id;

select * from vm_course;

--删除课程表中没有章节信息的课程

delete a 
from imc_course a
left join imc_chapter b on b.course_id = a.course_id
where b.course_id is null;

--删除课程方向表中重复的课程方向， （不用学，别手贱删）
--保留方向ID最小的一条，并在方向名称上增加唯一索引

delete a 
from imc_type a
join (
select type_name,MIN(type_id) as min_type_id,count(*)
from imc_type
group by type_name having count(*) >1
) b on a.type_name=b.type_name and a.type_id > min_type_id;

--冻结用户‘沙占’的账号

select user_nick,user_status
from imc_user
where user_nick= '沙占';

update imc_user set user_status =0
where user_nick ='沙占';

--随机推荐10门课程
alter table imc_course
add is_recommand tinyint

时间函数
select curdate(),curtime(),now();
+------------+-----------+---------------------+
| curdate()  | curtime() | now()               |
+------------+-----------+---------------------+
| 2020-07-13 | 22:01:16  | 2020-07-13 22:01:16 |
+------------+-----------+---------------------+

--计算每门课程，上线时间距当前时间的天数

select title,datediff(now(),online_time)
from imc_course
order by 2 desc;

--出于seo优化的目的，我们需要合并显示课程分类名称和课程标题

select concat_ws('||',class_name,title)
from imc_course a
join imc_class b on b.class_id = a.class_id;

mysql8之后才支持窗口函数....

-- row number rank dense_rank之间的区别

select study_name,class_name,score
    ,ROW_NUMBER() over(partition by class_name order by score desc) as rw
    ,rank() over(partition by class_name order by score desc) as rk
    ,dense_rank() over(partition by class_name order by score desc) as rdk
from test
order by class_name, rw;

--按学习人数对课程进行排名，并
--列出每类课程学习人数排名前三的课程名称
--学习人数以及名次。

select class_name,title,score
,RANK() OVER (PARTITION by class_name order by score desc ) as cnt
from imc_course a 
join imc_class b on b.`class_id`=a.`class_id`;

特别提示:
在sql开发中易犯的错误
使用
count(*)判断是否存在符合条件的数据
——可以使用select ... limit 1

在执行完一个更新语句后，使用查询方式判断此更新语句是否有执行成功

——可以使用row_count()函数判断修改行数

试图在on条件中过滤不满足条件的记录
——在where条件中进行过滤

本文地址：https://blog.csdn.net/weixin_44611305/article/details/107327405