欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

20200713学习Mysql总结(一)

程序员文章站 2022-05-21 12:17:37
Mysql数据库关系数据库的特点数据结构化存储在二维表中支持事务的原子性A,一致性C,隔离性I,持久性D 特性支持使用SQL语言对存储在其中的数据进行操作宽表模式:把对象的属性全部存储在一个表中缺点:数据冗余:相同的数据在一个表中出现了多次数据更新异常:修改一行中某列的值时,同时修改了多行数据数据插入异常:部分数据由于缺失主键信息而无法写入表中数据删除异常:删除某一数据时不得不删除另一数据宽表模式的应用场景:配合列存储的数据报表应用三大范式:第一范式...

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