MySQL

数据库概念

实体：客观存在并且相互区别的事物。比如：职工、学生、部门、一门课，学生的一次选课、部门的一次订货、教师在哪个学院工作等都是实体。
属性：实体所具有的某一特性。学生的属性：学号、姓名、成绩、出生年月等等
码：唯一标识实体的属性集称为码。
数据模型：由数据结构、数据操作以及数据的完整性约束条件三部分组成。有网状模型、层次模型、关系模型等组成。
候选码：某一属性组的值能够唯一标识一个元组，而其子集不能，则称该属性组为候选码。
若一个关系中有多个候选码，选定其中一个为主码。
候选码的诸属性称为主属性。不包含在任何候选码中的属性称为非主属性。
外码：如果一个关系中的一个属性是另外一个关系中的主码，且在本关系中不是码，则这个属性为外码。外码的值要么为空，要么要为其本关系的主码中的一个值。所以说当确定本关系中的主码时，一般还要看这些属性是不是别的关系的主码。

完整性约束：

1.not null（非空)约束

  1. unique(惟一)约束：用于指明创建惟一约束的列上的取值必须惟一。

3.primary key(主键)约束：用于定义基本表的主键，起惟一标识作用，其值不能为null,也不能重复，以此来保证实体的完整性。

4.foreign key(外键)约束：定义了一个表中数据与另一个表中的数据的联系。

5.域约束，在创建表的时候定义某一个属性的取值范围。

对基本表的操作

创建表：

create table Student(Sno char(9) primary key,Sname char(20) unique,Ssex char(2),Sage smallint,Sdept char(20));

create table Course(Cno char(4) primary key,Cname char(40) not null,Cpno char(4),Ccredit smallint,foreign key(Cpno) references Course(Cno));//Cpno是先修课，注意这个外码的定义与下边的区别

create table SC(Sno char(9),Cno char(4),Grade smallint,Primary key(Sno,Cno),foreign key(Sno) references Student(Sno),foreign key(Cno) references Course(Cno));

修改表：

向Student表中增加“入学时间”列，其数据类型为日期型。

alter table Student add S_entrance DATA;//不论基本表中原来是否已有数据，新增加的一列一律为空值。

将年龄的数据类型由字符型改为整数

alter table Student alter column Sage int;//column 列

增加课程名称必须取唯一值的约束条件：

alter table Course add unique(Cname);

删除表：

drop table Student CASCADE/RESTRICT；

若选择RESTRICT，则该表的删除是有限制条件的：欲删除的表不能被其他表的约束所引用（check、foreign key等约束），不能有视图，不能有触发器，不能有存储过程或者函数等。如果存在这些依赖该表的对象，则此表不能被删除。

若选择CASCADE，则该表的删除没有限制条件。在删除基本表的同时，相关的依赖对象（比如视图）都被删除。

drop TABLE Student RESTRICT;//若表上建有视图，删除student表，但是选择RESTRICT（限制），则删除失败。

drop TABLE Student CASCADE;//若表上建有视图，删除student表，选择CASCADE（串联），则删除成功。

索引的建立与删除：

建立索引是加快查询速度的有效手段，但是索引也占用一定的空间，当基本表更新时，索引要进行相应的维护。

建立索引：

create unique index Stusno on Student(Sno)//Student表按学号升序建唯一索引

create unique index Coucno on Course(Cno)//Course表按课程号升序建唯一索引

create unique index SCno on SC(Sno ASC,Cno DESC);//SC表按照学号升序和课程号降序建唯一索引。

修改索引：

alter index SCno rename to SCSno;//修改名称

删除索引：

drop index Stusno

数据查询

查询全体学生的姓名及出生年份（表中没有出生年份），也就是说，select后边可以是表达式。

select Sname,2019-Sage from Student;

查询结果加上表头：

select Sname NAME,'Year of Birth:' BIRTH,2019-Sage BIRTHDAY ,LOWER(Sdept) DEPARTMENT from Student;

'Year of Birth:' BIRTH指的是加上表头和表头下边一列字符串Year of Birth:

LOWER表示小写

删除取值重复的行

select DISTINCT Sno from SC;//DISTINCT表示删除结果中重复的行

条件查询

select Sname,Sage from Student where Sage<20;

select DISTINCT Sno from SC where Grade<60;

select Sname,Sage from Student where Sage BETWEEN 20 AND 23;//not between表示不包含在20-23岁的 也有not between

select Sname,Sage from Student where Sdept in('CS','MA','IS');//查询科学系、数学系、信息系的学生姓名和年龄

select Sname,Sage from Student where Sdept not in('CS','MA','IS');//查询既不是科学系也不是数学系、信息系的学生姓名和年龄

字符串匹配查询：使用谓词LIKE。

select * from Student where Sno LIKE '201215121';等价于select * from Student where Sno = '201215121';

select Sname,Sno,Ssex from Student where Sname like '刘%'; //查询所有姓刘的学生姓名、学号、性别。

select Sname,Sno,Ssex from Student where Sname like '欧阳_'; //查询所有姓欧阳且全名为三个汉字的学生姓名、学号、性别。

select Sname,Sno,Ssex from Student where Sname like '_阳%'; //查询所有姓名第二个字为阳的学生姓名、学号、性别。

select Sname,Sno,Ssex from Student where Sname not like '刘%'; //查询所有不姓刘的学生姓名、学号、性别。

select Cno,Ccredit from Course where Cname like 'DB_Design' escape ''; //escape ''(忽略、逃避)表示‘\’为换码字符，使得后边紧跟的不再像上边一样具有通配符的含义，转义为普通的‘’字符。

select * from Course where Cname like 'DB_%i__' escape ''; 查询以DB_开头，且倒数第三个字符为i的课程详细情况。

select Sno,Cno from SC where Grade is NULL;//查询缺少成绩的所有学生学号和课程号。is not null表示有成绩的

select Sname from Student where Sdept = 'CS' AND Sage < 20;

selece Sname,Ssex from Student where Sdept = 'CS' or Sdept = 'MA' or Sdept = 'IS';//等同于上边的 in

order by（用于对查询结果进行升序或者降序排序）

需要注意的是子查询（后边那个查询）的select语句不能使用order by子句，order by子句只能对最终结果排序。

select Sno,Grade from SC where Cno = '3' order by Grade DESC;//查询选修了3号课程的所有学生学号和成绩，并按成绩降序排序（含有空值的 放在最后）

select * from Student order by Sdept,Sage DESC;//查询所有学生情况，按所在系的系号升序排序，同一系按年龄降序排序。

聚集函数：

聚集函数是对选择到的数据进行操作，比如求和、求平均值、计算个数、求最大/最小值。聚集函数遇到空值时，除了count(*)外，都跳过。

select count(*) from Student;//查询学生的总人数

select count(DISTINCT Sno) from SC;//查询选修了课程的学生人数

select avg(Grade) from SC where Cno='1';//查询1号课程的学生平均成绩

select max(Grade) from SC where Cno='1';//查询1号课程学生的最高分

select sum(Ccredit) from SC,Course where Sno = '201215012' and SC.Cno=Course.Cno;//查询某个学生选修课程的总学分

group by（用于将查询到的结果按照某一列或者多列的值分组，值相等的为一组）

select Cno,count(Sno) from SC group by Cno;//各个课程号及其选课人数 查询结果按照Cno的值分组，所有具有相同Cno值的元组为一组，然后 对每一组作用聚集函数count进行计算，求得该组学生人数。

select Sno from SC group by Sno having count(*)>3;//从SC中选出学号，学号相同的为一组，这一组中学号的个数表示该学号的学生选修的课程数目，用having进行筛选元素个数大于3的组。最终结果即为 选修了三门以上课程的学生学号。

having与where的区别是：where作用于基本表或者视图从中选出满足条件的元组，having短语作用于组，从中选出满足条件的组。

select Sno,avg(Grade) from SC group by Sno having avg(Grade)>=90;//查询平均成绩大于等于90的所有学生学号和平均成绩。

group by一般与聚集函数一起使用，选出来一列。

连接查询：查询同时涉及两个以上的表，称为连接查询。

等值与非等值连接查询：

select s.,sc. from Student s,SC sc where s.Sno = sc.Sno;//查询每个学生及其选修课程的情况。

select Student.Sno,Sname from Student,SC where Student.Sno = SC.Sno and SC.Cno = '2' and SC.Grade > 90;//选修2号课程而且成绩在90 分以上的学生

自身连接：比如查询某一门课的先修课的先修课，要得到结果，必须先查询到先修课，再查询先修课的先修课，这就需要将Course表与自身连接。

自身连接的实现是定义同一个表的不同表名。

select c1.Cno,c2.Cpno from Course c1,Course c2 where c1.Cpno = c2.Cno;

外连接：对于上边查询每个学生及其选修课程的情况来说，如果有学生没有选课，那么是不会出现在结果中的，如果想让未选课的学生出现，就需要外连接，且其选择的课程号和成绩属性为NULL。

select s.,sc. from Student s left join SC sc on(s.Sno = sc.Sno);

1.以A，B两张表为例

A left join B

选出A的所有记录，B表中没有的以null 代替

right join 同理

2.inner join

A,B的所有记录都选出，没有的记录以null代替

3.cross join (笛卡尔积)

A中的每一条记录和B中的每一条记录生成一条记录

例如A中有4条，B中有4条，cross join 就有16条记录

多表连接：

查询每个学生的学号、姓名、选修的课程名和成绩。

select s.Sno,S.Sname,c.Cname,sc.Grade from Student s,Course c,SC sc where s.Sno = sc.Sno and sc.Cno = c.Cno;

嵌套查询：select-from-where为一个查询块，将一个查询块嵌套在另一个查询块的where子句或者having短语中的查询称为嵌套查询。需要注意的是子查询（后边那个查询）的select语句不能使用order by子句，order by子句只能对最终结果排序。

查询选修了课程名为“信息系统”的学生学号和姓名:

select Sno,Sname from Student where Sno in

    (select Cno from SC where Cno in

         (select Cno from Course where Cname = '信息系统')

    )；

也可以使用连接查询：

select s.Sno,s.Sname from Student s,SC sc,Course c where 
       s.Sno = sc.Sno and sc.Cno = c.Cno and c.Cname='信息系统'；

相关子查询：上边嵌套查询的子查询（后边那个查询）是独立的，不依赖于父查询称为不相关子查询。反之称为相关子查询。

找出每个学生超过他自己选修课程平均成绩的课程号：

select Sno,Cno from SC x where Grade >= (select avg(Grade) from SC y where y.Sno = x.Sno);

先在外层查询中找出一个学号x.Sno,传送给内层查询，然后执行内层查询，求得这个学号的平均成绩，最后再在外层查询中求得最终结果。

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。

+----+-------+--------+--------------+

| Id | Name | Salary | DepartmentId |

+----+-------+--------+--------------+

| 1 | Joe | 70000 | 1 |

| 2 | Henry | 80000 | 2 |

| 3 | Sam | 60000 | 2 |

| 4 | Max | 90000 | 1 |

+----+-------+--------+--------------+

Department 表包含公司所有部门的信息。

+----+----------+

| Id | Name |

+----+----------+

| 1 | IT |

| 2 | Sales |

+----+----------+

编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。

+------------+----------+--------+

| Department | Employee | Salary |

+------------+----------+--------+

| IT | Max | 90000 |

| Sales | Henry | 80000 |

+------------+----------+--------+

select d.Name as Department,e.Name as Employee,e.Salary from Employee e,Department d

        where e.DepartmentId=d.Id and(e.DepartmentId,e.Salary) in#注意格式，必须一样，且顺序也必须一样

                (select ee.DepartmentId,max(ee.Salary) from Employee ee group by ee.DepartmentId);

为什么要select ee.DepartmentId？

如果单独运行(select ee.name,max(ee.Salary) from Employee ee group by ee.DepartmentId);若不用ee.DepartmentId,max(ee.Salary)，得到的是 Joe 9000，Henry 8000，而我们想要的结果是：Max 9000，Henry 8000。因为group by 之后一般用聚集函数返回，如果不用聚集函数返回，得到的就是第一个name，所以我们select ee.DepartmentId，这样结果一定是正确的。

查询表中每个部门工资排名前三的员工：

select d.name Department,e.name Employee,e.salary Salary from employee  e, Department d where                      

 e.DepartmentId = d.Id and

    (select count(distinct salary) from employee where salary > e.salary and departmentid=e.DepartmentId )<3

        order by e.departmentid,e.Salary desc;

第三行意思是 在连接的表中工资 小于employee表中工资的数目 是不到三个的。也就是说寻找连接的表中工资排名前三的行

最后将结果按照部门号升序排序，且同一部门号工资降序排序。

">any "大于查询结果中的某个值 >all 大于查询结果中的全部值。

查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄：

select Sname,Sage from Student where Sage < any(select Sage from Student where Sdept = 'CS') and Sdept <> 'CS';

若用聚集函数实现：

select Sname,Sage from Student where Sage < (select max(Sage) from Studentwhere Sdept = 'CS') and Sdept <> 'CS';

查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名和年龄：

select Sname,Sage from Student where Sage < all(select Sage from Student where Sdept = 'CS') and Sdept <> 'CS';

select Sname,Sage from Student where Sage < (select min(Sage) from Studentwhere Sdept = 'CS') and Sdept <> 'CS';

Limit子句:Limit子句可以被用于强制 SELECT 语句返回指定的记录数。Limit接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参 数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。

//初始记录行的偏移量是 0(而不是 1)：

SELECT * FROM table LIMIT 5,10; //检索记录行6-15 从第六行开始往后数10行

//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：

SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last

//如果只给定一个参数，它表示返回最大的记录行数目。换句话说，LIMIT n 等价于 LIMIT 0,n：

SELECT * FROM table LIMIT 5; //检索前 5 个记录行

集合查询：并 union 交intersect 差 except

查询计算机科学系的学生及年龄不大于19岁的学生：

select * from Student where Sdept = 'CS' union select * from Student where Sage <= 19;

查询选修了课程1或者选修了课程2的学生:

select Sno from SC where Cno = '1' union select Sno from SC where Cno = '2';

查询计算机科学系中年龄不大于19的学生：

select * from Student where Sdept = 'CS' and Sage <= 19;或者：

select * from Student where Sdept = 'CS' intersect select * from Student where Sage <= 19;

查询既选修了课程1又选修了课程2的学生：

select Sno from SC where Cno = '1' intersect select Sno from SC where Cno = '2';或者：

select Sno from SC where Cno = '1' and Sno in(select Sno from SC where Cno = '2');

查询计算机科学系的学生与年龄不大于19岁的学生的差集，即查询计算机系中年龄大于19岁的学生：

select * from Student where Sdept = 'CS' except select * from Student where Sage <= 19;或者：

select * from Student where Sdept = 'CS' and Sage > 19;

带有exists谓词的子查询

查询所有选修了1号课程的学生姓名：

select Sname from Studend,SC where Student.Sno = SC.Sno and SC.Cno = '1';

select Sname from Student where exists 

                (select * from SC where Sno = Student.Sno and Cno = '1');

exists后边是真值或者假值，非空即为真，所以exists后边的select通常为*，给出列的话没有意义。

所有带in、比较运算符、any，all的子查询都能用exists或者not exists来代替

查询没有选修1号课程的学生姓名：

select Sname from Student where not exists

                (select * from SC where Sno = Student.Sno and Cno = '1');

查询刘晨所在院系的所有学生学号、姓名和院系：

select s1.Ssno,s1.Sname,s1.Sdept from Student s1 where s1.Sdept exists

        (select *from Student s2 where s1.Sdept= s2.Sdept and s2.Sname = '刘晨');

查询选修了全部课程的学生姓名：

没有一门课是他不选的。

select Sname from Student where not exists

        (select * from Course where not exists

                (select * from SC where Sno = Student.Sno and Cno = Course.Cno));

查询至少选修了学生201215122选修的全部课程的学生号码：

不存在这样的课程y，学生201215122选择了y，而所要选择的学生没有选。

select distinct s1.Sno from SC s1 where not exists

        (select * from SC s2 where s2.Sno = '201215122' and not exists 

                (select * from SC s3 where s3.Sno = s1.Sno and s3.Cno = s2.Cno));

表中数据更新

数据更新包括：向表中添加若干行数据，修改表中的数据，删除表中的若干行数据。

插入数据：包括插入一个元组和插入子查询结果，插入子查询结果可以一次插入多个元组。

将一个新学生元组（201215128，陈冬，男，IS，18岁）插入到Student表中：

insert into Student(Sno,Sname,Ssex,Sdept,Sage) values ('201215128'，'陈冬'，'男'，'IS'，1);

如果属性的顺序和建表时顺序一样，且数据很全，可以不用绿色的。

对每一个系。求学生平均年龄，并把结果存入数据库：

create table Dept_age(Sdept char(15),Avg_age smallint);

insert into Dept_age(Sdept,Avg_age) 

    select Sdept,avg(Sage) from Student group by Sdept;

修改数据：也称为更新操作

将学生201215121的年龄改为22岁:

update Student set Sage = 22 where Sno = '201215121';

将所有学生的年龄增加一岁：

update Student set Sage = Sage+1;

将计算机科学系全部学生成绩置零：

updata SC set Grade = 0 where Sno in (select Sno from Student where Sdept = 'CS');

删除数据：

删除学号为201215121的学生记录：

delete from Student where Sno = '201215121';

删除所有学生选课记录：

delete from SC;

删除计算机系所有学生的选课记录：

delete from SC where Sno in
        (select Sno from Student where Sdept = 'CS');

选出选修1号课程的不及格学生（包括缺考的）：

select Sno from SC where (Grade < 60 or Grade is null) and Cno = '1'; #不为空：is not null

视图：

视图是对若干张基本表的引用，是一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）；

可以跟基本表一样，进行增删改查操作(ps:增删改操作有条件限制)；对视图增删改，原表也增删改。视图的作用是方便操作，特别是查询操作，减少复杂的SQL语句，增强可读性；

更加安全，数据库授权命令不能限定到特定行和特定列，但是通过合理创建视图，可以把权限限定到行列级别；总的来说就是简化操作，更安全。

创建视图：

create VIEW IS_STUDENT

AS

select Sno,Sname,Sage

FROM Student where Sdept = 'IS';//创建视图，选择student表中院系为IS的学生学号、姓名、年龄。

将学生的学号及平均成绩定义为一个视图：

create view S_G(Sno,Gavg) as

select Sno,avg(Grade) from SC group by Sno;

删除带有视图的表：

drop TABLE Student RESTRICT;//若表上建有视图，删除student表，但是选择RESTRICT（限制），则删除失败。

drop TABLE Student CASCADE;//若表上建有视图，删除student表，选择CASCADE（串联），则删除成功。

在视图上查找：

SELECT

    is.Sno,

    is.Sname

FROM

    IS_STUDENT  is

WHERE

     is.Sname = '小张';

那么哪些操作可以在视图上进行呢？

视图与表是一对一关系情况：即一个表创建的视图。如果没有其它约束（如视图中没有的字段，在基本表中是必填字段情况），是可以进行增删改数据操作；

视图与表是一对多关系情况：如果只修改一张表的数据，且没有其它约束（如视图中没有的字段，在基本表中是必填字段情况），是可以进行改数据操作。

更新视图：

update IS_STUDENT set Sname = '刘晨' where Sno = '201215121';

注意上边创建IS_STUDENT视图是Student表中IS系学生的内容。所以上边更新视图就相当于：

update Student set Sname = '刘晨' where Sno = '201215121' and Sdept = 'IS';

https://www.cnblogs.com/chenpi/p/5133648.html

数据库范式

完全函数依赖：x->y,并且对于x的真子集x，x不能推出y，则y对x完全函数依赖。

部分函数依赖：x->y,并且对于x的真子集x，x->y，则y对x部分函数依赖。

传递函数依赖：x->y，而且y不能推出x，而且y->z，则称z对x传递函数依赖。

(Sno,Cno)->Grade是完全依赖；

(Sno,Cno)->Sdept是部分依赖，因为Sno能推出来Sdept。

Sno->Sdept,且Sdept->Mname,是传递依赖。

2NF：满足第一范式，且每一个非主属性完全函数依赖于任何一个候选码，则R属于第二范式。

例如关系模式S-L-C(Sno,Sdept,Sloc,Cno,Grade),其中Sloc为学生住处：

(Sno,Cno)->Grade完全函数依赖

Sno->Sdept,所以(Sno,Cno)->Sdept部分函数依赖

Sno->Sloc,所以(Sno,Cno)->Sloc部分函数依赖

Sdept->Sloc

所以说上述关系模式不属于2NF。如何修改为符合第二范式？

Grade对码是完全依赖，Sdept和Sloc对码是部分函数依赖，所以我们拆分一下，拆成SC和S-L

SC包含Sno,Cno,Grade. S-L包含Sno,Sdept,Sloc.

3NF：每一个非主属性既不传递依赖于码，也不部分依赖与码。（也就是说如果满足2NF，消除非主属性对码的传递依赖，就是3NF）。

上边SC和S-L，SC中Grade不传递依赖与(Sno,Cno)，也不部分依赖，所以SC属于3NF。

而S-L中，Sno->Sdept->Sloc(每个系住处一样)，所以S-L不属于3NF。

BCNF：
1.所有非主属性对每一个码都是完全函数依赖

2.所有主属性对每一个不包含它的码也是完全函数依赖，

3.没有任何属性完全依赖于非码的任何一组属性。

简单来说，若R属于3NF，且R只有一个候选码，则R一定是BCNF。（有多个候选码也有可能是BCNF）。

比如关系模式R（Sno,Sname,Sdept,Sage）,假定Sname具有唯一性，那么R就有两个码，满足上述三个条件，所以R是BCNF。

什么是索引？

索引是关系型数据库中给数据库表中一列或多列的值排序后的存储结构，所有MySQL的列类型都可以被索引。

mysql支持BTREE索引、HASH索引、前缀索引、全文本索引(FULLTEXT)【只有MyISAM引擎支持，且仅限于char,varchar,text列】、空间列索引【只有MyISAM引擎支持，且索引的字段必须非空】，但不支持函数索引。

聚集索引与非聚集索引使用的是B+树存储结构。

索引优缺点

提高查询速度，但是在更新表中数据时，也要更新索引，所以索引适合在大量查询情况下使用，当经常有更新操作的时候，不适合使用。

hash索引和B+索引有什么优缺点？

hash索引在进行等值查询时效率较高，但是不能排序，进而也不能进行范围查询。

B+索引数据是有序的，即可以进行范围查询。

数据库索引结构采用的是什么数据结构？为什么要采用这个结构？

数据库索引采用的是B+树的结构。

数据库由于数据量太大，基本全都保存在外存中，对外存的I/O操作比较耗时。B+树单个节点保存的值个数比普通二叉树多，所以能够减少查找时访问的结点就会少很多。

为什么说B+树比B树更适合数据库索引？

[图片上传失败...(image-261c9f-1560667382255)]

image.png

1.B+树的内部节点并没有指向关键字具体信息的指针（只有key没有value），因此其内部节点相对B树更小，读写代价更低。B+树的节点只存储索引key值，具体信息的地址存在于叶子节点的地址中。这就使以页为单位的索引中可以存放更多的节点。减少更多的I/O支出。

2.由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。即查询效率更加稳定。

假如将数据全部加载到内存，b+树还有优势吗？理由。

没有。因为在内存中寻址时间很快，没必要使用B+树这种矮胖的结构（与平衡二叉树差别不大），关键是B+树即使我们只对其中一个关键字操作,也必须将这个B+树节点整个读取出来,修改完关键字后还需要将节点全部写回内存。

假设B+树一个节点保存了101个关键字,即使我们只对其中一个关键字操作,也必须将这个B+树节点整个读取出来,（通常阶数是和磁盘页面大小匹配的，也就是每次都会读取一页的数据，因为磁盘在页面内连续读取速度非常快）修改完关键字后还需要将节点全部写回内存,上述操作冗余操作了100个关键字。而对于内存来说，寻址时间和读取时间的差距不大，可以近似的理解为同样的操作读取的关键字越少速度越快,而B+树在这种情况下劣势，为了弥补内存中B+树的这种劣势，可以把树的阶数设置小点，减少关键字冗余操作,可以提升内存B+树的效率。

聚集索引与非聚集索引

在B+树索引结构的数据库中，从根结点一直到叶子结点查找要查找的数据，叶子结点就是存储的数据（student表中某一行的全部信息），这就是聚集索引。

使用非聚集索引有可能要进行二次查询：非聚集索引叶节点仍然是索引节点，只是有一个指针指向对应的数据块，此如果使用非聚集索引查询，而查询列中包含了其他该索引没有覆盖的列，那么他还要进行第二次的查询，查询节点上对应的数据行的数据。

使用以下语句进行查询，不需要进行二次查询，直接就可以从非聚集索引的节点里面就可以获取到查询列的数据。

select id, username from t1 where username = '小明'

select username from t1 where username = '小明'

但是使用以下语句进行查询，就需要二次的查询去获取原数据行的score：

select username, score from t1 where username = '小明'

即查询列中包含了该索引中没有包含的列（where子句中没有包含的列），即会引起二次查询。使用联合索引可以避免二次查询（下边）或者查询的时候去避免。

非聚集索引虽然在查询方面效率比聚集索引低，但是在增删改时，整体效率比聚集索引高。

[图片上传失败...(image-89bc5c-1560667382255)]

image.png

[图片上传失败...(image-f731ae-1560667382255)]

image.png

联合索引的最左前缀匹配

联合索引是指建立多列索引，可以用来优化多条件查询。

为什么使用最左前缀匹配，因为联合索引只有第一个索引列是绝对有序的，第二个索引列相对于第一个索引列的某一条有序，第三个索引列相对于第二个索引列的某一条有序。

字典通过部首和笔画查字，确定部首之后才能再找笔画。

联合索引中的字段，只有某个字段（笔画）左边的所有字段（部首）都被使用了，才能使用该字段上的索引。例如，有索引INDEX idx_i1(col_a, col_b)，如果查询条件为where col_b = 1，则无法使用索引idx_i1。

如果我们不知道一个字的部首是什么的话，那基本是没办法使用这个目录的。这说明仅仅通过笔画数（第二个字段）是没办法使用部首目录的。

数据库操作方法

begin; -开始事物

commit; - 提交事物 Python 默认是取消自动提交的

rollback; - 回撤操作, 只要操作没有执行 commit 就可以进行回滚操作, 撤回,发生死锁的时候也可以使用rollback来解除。

create table tb_account

(

accid char(4) not null,

uname varchar(20) not null,

balance float default 0

)

insert into tb_account values

('1111', '张明禄', 1200.99),

('2222', '王大锤', 500);

-- 开启一个事物 start transaction

begin;

update tb_account set balance=balance-1000

where accid='1111';

update tb_account set balance=balance+1000

where accid='2222';

commit; -- 提交 才能改变

rollback; -- 撤销

begin;

delete from tb_account; -- 没有commmit 不会删除表

rollback;

数据库哈希索引 B+树索引 原理 优缺点实现

在mysql中，只有memory引擎显式支持哈希索引，这也是memory引擎表的默认索引类型.

Hash索引是基于哈希表实现，只对精确匹配所有索引列的查询才有效。存储引擎将所有的索引列根据hash函数映射到索引表中，同时保存指向对应的一条记录的指针，对于hash值相同时，采用链表解决冲突。检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

  • 如果是等值查询，hash索引只需要经过一次算法即可找到相应的键值；但若有hash碰撞，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据，所以当出现大连hash碰撞，他的效率反而低；B+树索引的关键字检索效率比较平均。

  • Hash索引不支持范围查询，B+可以。

  • 哈希索引没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；B+可以

  • 哈希索引不支持组合索引的最左前缀匹配规则；B+可以。

数据库的ACID分别代表什么？各个含义是什么

事务：并发控制的基本单位，是一个操作序列：可以是一条SQL语句、一组SQL语句或者整个程序。事务要么都执行，要么都不执行，它是一个不可分割的工作单位。

事务的特性：

原子性atomicity 一致性constency 隔离性isolation 持续性durability

原子性：指事务是一个不能再分割的工作单元。事务要么都发生，要么都不发生。例如：A向B转账。A扣款B增款，这两个操作要么都执行要么都不执行。

一致性：事务的完成并不破坏数据库的完整性约束，例如,完整性约束了a+b=10,一个事务改变了a,那么b也应该随之改变。A向B转账，转账之后二者的存款总和还是不变的。

隔离性：允许多个用户对同一个数据进行并发访问而不破坏数据的正确性和完整性。

持续性：事务完成之后，对系统的影响是永久的。

数据完整性约束指的是为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS（数据库管理系统）自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。

数据库提供的隔离级别

首先了解会出现的问题：

脏读

读取了未提交的数据。

用户A向用户B转账100元，对应SQL命令如下

update account set money=money+100 where name=’B’; (此时A通知B)

update account set money=money - 100 where name=’A’;

当只执行第一条SQL时，A通知B查看账户，B发现确实钱已到账（此时即发生了脏读），而之后无论第二条SQL是否执行，只要该事务不提交，则所有操作都将回滚，那么当B以后再次查看账户时就会发现钱其实并没有转。

不可重复读

事务T1在读取某一数据，而事务T2立马修改了这个数据并且提交事务给数据库，事务T1再次读取该数据就得到了不同的结果，发送了不可重复读。

所以说，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。

幻读

例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。

脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。

幻读与不可重复读的区别是幻读针对的是一批数据，而不可重复读针对的是一个数据。

数据库提供的四种隔离级别：

Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。

Repeatable read (可重复读)：可避免脏读、不可重复读的发生。

Read committed (读已提交)：可避免脏读的发生。

Read uncommitted (读未提交)：最低级别，任何情况都无法保证。

乐观锁与悲观锁

悲观锁：持悲观态度，认为每一个后进来的事务都要修改数据，所以悲观锁又称为排他锁。在数据库中的实现方式为：select……for update； 悲观锁的实现，往往依靠数据库提供的锁机制 （也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）

要使用悲观锁，我们必须关闭数据库的自动提交属性：set autocommit = 0;

//0.开始事务

begin;

//1.查询出商品信息

select status from t_goods where id=1 for update;

//2.根据商品信息生成订单

insert into t_orders (id,goods_id) values (null,1);

//3.修改商品status为2

update t_goods set status=2;

//4.提交事务

commit;

悲观锁采用先取锁后访问的保守策略，为数据处理的安全性提供了保证，但是在效率方面，加锁会让数据库产生额外的开销，还有可能死锁。而且在只读操作上，根本用不上锁。

乐观锁：持乐观态度，他假设多用户并发操作不影响彼此。

不加锁，修改其版本号，每次对数据的更新操作都对版本号执行+1操作，并判断当前版本号是不是该数据的最新的版本号。如果其他事务有更新的话，正在提交的事务会进行回滚。

1.查询出商品信息

select (status,status,version) from t_goods where id=#{id}

2.根据商品信息生成订单

3.修改商品status为2

update t_goods set status=2,version=version+1 where id=#{id} and version=#{version};

如何设计一个秒杀系统？

https://blog.csdn.net/liangkaiping0525/article/details/80836104//写的非常好

1.使用乐观锁。

2.进入数据库之前使用队列，将请求全部保存在队列中，即将并行转化为串行。

为什么不使用悲观锁呢？

因为如果一个线程拿到锁，对数据进行更新操作，若迟迟没有更新，其他线程根本就无法更新数据，即没法知道自己的本次操作是成功还是失败。

如果使用乐观锁，是不是只要版本变化了，不管库存是不是够用，本次抢购都宣告失败？

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容

  • 练手系列——mysql
    最近打算采用关系型数据库来理一下公司的运营数据，先拿点东西练手找感觉。下面是几个关于学生课业的表，需要建立一个数据...
    九天朱雀阅读 4,569评论 0赞 3
  • mysql练习题
    Student(S#,Sname,Sage,Ssex) 学生表 Course(C#,Cname,T#) 课程表 S...
    忘了呼吸的那只猫阅读 7,982评论 0赞 8
  • 【笔记】数据库实例代码
    一。数据库基本概念：数据、数据库。数据模型/DBMS（数据库管理系统）/DBS（数据库系统）二。数据库内部组成二维...
    S_s_s_a53f阅读 3,486评论 0赞 0
  • SQL干货篇之查询数据
    单表查询 只在一个表中查询数据 多表查询 同时查询多个表 说明：这是在学生表student和成绩表SC中查询成绩大...
    爱撒谎的男孩阅读 4,918评论 0赞 2
  • 此生当有情，忘记谈何易
    翻开日记密密麻麻全是那时的你 无力拿笔 每一个字都提示我正在一点点失去 拿起手机 拨开免提 假装我也不寂寞 可寂寞...
    川湖江海阅读 1,313评论 0赞 4