Mysql 数据库
企业要求: 至少掌握mysql /oracle/sqlserver等关系型数据库中一种
一、基本了解
关系型数据库 mysql 、 oracle 、 sqlserver、db2等,其最大的特点是以二维表形式的存储数据,行列
非关系型数据库 redis (key-value)(内存型数据库)、memcache (内存型数据库)、mongodb(文档型数据库)等
对比: 关系型数据库是基于SQL语句,查询统计非常方便,存储在硬盘上的数据文件中,效率比较低
(效率低的原因是:内存与硬盘间需要进行io操作,但内存的运行效率远高于硬盘,因此效率受限于硬盘速度)
非关系型数据库,统计排序等等非常复杂,主要运行在内存上,效率会远远高于关系型数据库,大量的应用在缓存
二、掌握的内容
备注:
分为了解和掌握(理解),在这个基础上再提炼一些知识点(针对性面试的内容)
目标: 开发和运维
内容:
1、mysql的基本安装(windows和linux、docker)了解
2、mysql的基本管理(使用客户端连接数据库、连接的命令、创建数据库命令、创建用户的命令、授权等)
mysql中的数据类型 char和varchar区别,blob/text ,int ,date ,datetime , numeric(decimal) 重点了解 numeric(5,2) 总共5位(小数+整数位数),小数2位
人名 char(10) —>‘john’ —>'john ’
varchar(10)—>‘john’—>‘john’
状态值 char(1) 0或1
“john”.equals("john ")
blob(longblob) binary large object 二进制的大对象 (存图片、音频、视频或者文件等)
text(longtext/clob) 字符型的大对象 存文章
numeric(7,2)
3、mysql的 SQL (DDL\DCL\DQL\DML) 重点
DDL: create
create table student(
sid int primary key,
sname varchar(10)
)
DCL: grant
DQL: select * from student
select age,sex,name from student where id = 1
select * from student order by name
select max(score) from student group by class
DML: insert 、 update 、 delete
insert into student values(1,‘张三’,21)
insert into student(name,age) values(‘张三’,21)
update student set age=age+1 where id = 1
delete from student where name=‘张三’
4、DQL 单表查询、多表查询 (重点)
复杂查询(多表查询)
子查询(嵌套查询)和关联查询
select id from student where name in (select name from member where id > (select id from xx where xx=xx)))
关联查询: 交叉(笛卡儿积)查询、外连接查询、内连接查询、自然连接等
select a.* , b.* from a,b where a.id = b.id
select a.* ,b.* from a left join b on a.id=b.id 区分左外和右外连接的区别
表1: student id name age
表2: course id name
表3: record sid cid score
1.查询学生选了哪些课?
select s.name,c.name from student s,course c,record r where s.id=r.sid and c.id=r.cid
2.查询张三选了哪些课?
select s.name,c.name from student s,course c,record r where s.id=r.sid and c.id=r.cid and s.name=‘张三’
3.查询成绩大于90分的有哪些学生?
select id,name from student where id in (select sid from record where score>90)
4.查询所有学生选了哪些课?
select s.name,c.name from student s left join record r on s.id = r.sid left join course c on r.cid = c.id
create view vstudent_record is select s.name,c.name from student s left join record r on s.id = r.sid left join course c on r.cid = c.id
select * from vstudent_record
表如何设计?
看表和表直接的关系
表和表是一对一的关系, 学生和手机号 一个学生对应一个手机号,一个手机号也对应一个学生,正向一对一,反向也是一对一的,可以使用主外键的约束
表和表之间是多对多关系 ,学生和课程 一个学生可以选择多个课程,一个课程也可以被多个学生选择,正向是一对多,反向也是一对多,是多对多的关系,在数据库设计中只能使用关联表
5、存储过程、函数、触发器、游标等 部分(了解)
使用数据库支持的语言进行编程,把java中的业务代码转到数据库中完成
为什么使用这个?
效率会比java程序要高,缺点是非常不利于代码的维护和扩展
存储过程就是一段功能代码(完成某一个业务操作)
函数不是一个完整的操作,依赖于SQL select max(age) from student
max(age)
触发器trigger 删除一条记录 (触发器监控这个表,比如可以触发会把删除的记录信息记入到日志表)
只能在工作时间去操作学生表 周一-周五 8:00-18:00
游标 cursor 一个结果集 ,通过遍历操作结果集(查询的结果)
应用案例: 3年以上的员工年终奖是5个月工资,1年以上的员工年终奖是3个月工资 ,1年以下的员工是1个月工资
6、视图view、索引、约束
视图: 一个表或多张表的查询结果
create view student2 is/as select * from student where age>20
select * from student2
create view student2 is select a.* ,b* from a,b where a.id=b.id and a.age>20
应用:(1)存入复杂查询的结果,使用sql的时候就比较简单
(2)使用view可以隐藏一些敏感信息
create view student2 is select id,age,sex from student
比如公安部的系统,所有公民的信息 姓名 年龄 籍贯 身份证号
住酒店 – 扫描身份证(存个人信息的数据)–系统(程序)
索引(重要)
什么是全表扫描,什么时候会使用到全表扫描?
全表扫描(不好的前提是指数据量大的时候),在没有使用索引的时候进行查询会使用全表扫描
在表的数据量大的时候,尽量使用索引。
什么是索引?索引有什么作用?索引的原理是什么?
索引类似像表一样的地位,是一个对象。
索引最大的作用就是优化查询,增加查询的效率(查询的速度更快)
索引的优势是体现在数据量相对较大的时候,数据量小的时候没有优势
在数据需要频繁的删除/添加的时候,索引也会受到影响
索引的原理首先大家了解数据结构的设计。
为什么mysql的索引采用b±tree的数据结构?
数据库索引采用的存储结构默认就是b+(b-tree),位图索引/hash索引
mysql的存储引擎 innodb myisam(2种引擎的区别)
innodb聚簇索引 (索引和数据文件存在同一个文件中)
myisam非聚簇索引(索引和数据文件是分开存储的)
注意的点:
1.mysql为什么建议使用自增的无意义的字段作为主键?
2.mysql不定义主键会出现什么问题?
理解相关的概念:
聚簇索引和非聚簇索引的原理
主键索引、非主键索引、组合索引等涉及到的原理
回表查询和覆盖查询等概念
约束 主键(非空和唯一)primary key、非空not null、唯一unique、检查 check(age>10 and age<15)、外键约束foreign key
7、数据库的备份和恢复 (对开发岗要求不高,对运维岗要求很高)
冷备份 需要停机备份(mysql服务器关掉)
热备份 不要停机的备份
逻辑备份 数据库中表结构和数据
物理备份 拷贝数据库的数据文件、日志文件等
相关的mysql备份和恢复的命令
8、mysql的事务(重点)
9、mysql的优化(对开发和运维都比较重要)
非常重要的优化基于索引,尽量的保证能使用到索引
sql的优化(不要一概而论)
不同的数据库,优化可能都不一样,有些数据库优化的很好,有些数据库的优化就比较差
sql随着数据库的升级,有些常见的sql优化已经形同虚设
select * from student (到数据字典中查询student中所有字段名,替换*)
select id,name,age from student
select count(*) from student
select count(id) from student
sql优化非常非常多,几十条sql,5/6条举个例子
索引优化主要避免索引失效,导致全表扫描
select * from student where name like ‘%张%’ //索引失效,全表扫描
select * from student where name like ‘张%’ //使用索引
select * from student where age+1>23
select * from student where age>23-1
age>‘22’
数据库结构设计
1nf 2nf 3nf 不是绝对的,符合范式的要求不一定效率会高
互联网的企业由于数据量非常大,适当冗余
student id name age
student_detail id s_addr
拆开好处:地址用的很少,查询不需要查地址
不分开的好处: 假如地址经常要用到的数据,建议大家不要拆,合并
student id name age gid gname
1 张三 23 1 大一
grade gid gname
1 大一
假如经常会用到gname,在很多的时候都需要进行多表查询
10、mysql的replication和cluster(偏运维)
mysql的replication主从复制
MySQL的cluster集群