MySQL 데이터베이스 기본 지식 포인트 보유(정리된 요약)

이 글은 mysql 데이터베이스에 대한 관련 지식을 주로 인덱싱, 구문 순서, 실행 순서, 저장 프로시저 및 기타 관련 문제를 포함하여 데이터베이스의 몇 가지 기본 지식 포인트로 구성합니다.

추천 학습: mysql 튜토리얼

1. 데이터베이스

1.1 트랜잭션

1.1.1 트랜잭션의 네 가지 특성(ACID)

• 원자성(원자성 )Atomicity）
  整个事务中的所要操作要么全部提交成功，要么全部失败回滚。
• 一致性（Consistency）
  保证数据库中的数据操作之前和操作之后的一致性。（比如用户多个账户之间的转账，但是用户的总金额是不变的）
• 隔离性（Isolation）
  隔离性要求一个事务对数据库中数据的修改，在未提交完成前对于其它事务是不可见的。（即事务之间要串行执行）
• 持久性（Durability）
  持久性是指一个事务一旦被提交了，那么对数据库中的数据改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

SQL标准定义了四种隔离性：（下面隔离性是由低到高，并发性由高到低）

• 未提交读。
  最低的隔离等级，允许其他事务看到没有提交的数据，会导致脏读。
• 已提交读。
  由于数据库是读写分离，事务读取的时候获取读锁，但是在读完之后立即释放，释放读锁之后，就可能被其他事务修改数据，再进行读是就发现前后读取数据的结果不同，造成不可重复读。（读锁不需要事务提交后释放，而写锁需要事务提交后释放。）
• 可重复读。
  所有被select 전체 트랜잭션에 필요한 모든 작업은 성공적으로 제출되거나 모두 실패하여 롤백되어야 합니다.
• 일관성(Consistency)
  은 작업 전후에 데이터베이스의 데이터 일관성을 보장합니다. (예를 들어, 사용자의 여러 계정 간에 이체하지만 사용자의 총 금액은 변경되지 않습니다.)

격리(Isolation)

격리를 위해서는 트랜잭션이 데이터베이스의 데이터를 수정해야 하며 그렇지 않습니다. 나중에 커밋이 완료될 때까지 다른 트랜잭션에 표시되지 않습니다. (즉, 트랜잭션은 순차적으로 실행되어야 합니다.)

내구성(Durability)
내구성이란 일단 트랜잭션이 제출되면 데이터베이스의 데이터 변경 사항이 영구적이라는 것을 의미합니다. 데이터베이스 시스템에 오류가 발생하더라도 트랜잭션 커밋 작업은 손실되지 않습니다.


SQL 표준은 네 가지 유형의 격리를 정의합니다. (다음 격리는 낮은 수준에서 높은 수준, 동시성은 높은 수준에서 낮은 수준입니다.)

커밋되지 않은 읽기입니다.

가장 낮은 격리 수준으로 다른 트랜잭션이 커밋되지 않은 데이터를 볼 수 있도록 허용하여 더티 읽기로 이어질 수 있습니다. 🎜🎜읽기 위해 제출되었습니다. 🎜 데이터베이스는 읽기와 쓰기가 분리되어 있기 때문에 트랜잭션을 읽을 때 읽기 잠금을 획득하지만, 읽기 잠금이 해제된 후 다른 트랜잭션에 의해 데이터가 수정될 수 있습니다. 읽기 전후의 데이터가 다르기 때문에 반복할 수 없는 읽기가 발생합니다. (읽기 잠금은 트랜잭션이 커밋된 후에 해제될 필요가 없지만, 쓰기 잠금은 트랜잭션이 커밋된 후에 해제되어야 합니다.) 🎜🎜반복 읽기. 🎜 select로 얻은 모든 데이터는 거래 전후의 일관성 없는 읽기를 방지하기 위해 수정할 수 없습니다. 그러나 다른 트랜잭션은 현재 선택된 데이터를 변경할 수 없지만 데이터를 추가할 수 있으므로 가상 읽기를 제어할 수 없습니다. 🎜🎜직렬화 가능. 🎜 팬텀 읽기를 피하기 위해 모든 트랜잭션은 차례로 실행됩니다. 잠금을 기반으로 동시성 제어를 구현하는 데이터베이스의 경우 직렬화에서는 범위 쿼리를 실행할 때 동시성 제어가 구현되지 않은 경우 범위 잠금을 획득해야 합니다. 잠금, 데이터베이스, 직렬 작업을 위반하는 트랜잭션이 감지되면 해당 트랜잭션을 롤백해야 합니다. 🎜🎜🎜요약: 4가지 레벨이 점차 강화되며, 각 레벨에서 문제가 해결됩니다. 거래 레벨이 높아질수록 성능이 저하됩니다. ( 읽기 커뮤니티)는 불가능할 수 있습니다. 커밋되지 않은 읽기는 더티 읽기를 유발합니다. -> 커밋된 읽기는 더티 읽기를 해결하지만 반복 불가능한 읽기를 유발합니다. -> 반복 가능한 읽기는 불일치 전후의 결과를 읽는 문제를 해결하지만 가상 읽기를 유발합니다(이전이 아니라 현재) -> 직렬화 기능은 팬텀 읽기를 해결하지만 많은 범위 잠금을 추가하여 잠금 시간 초과를 유발할 수 있습니다.

1.1.2 더티 읽기, 반복 불가능 읽기 및 팬텀 읽기

• 더티 읽기(롤백 작업용): 트랜잭션 T1이 레코드 행의 내용을 업데이트했지만 수정 사항을 커밋하지 않았으며, 트랜잭션 T2 읽기 Get 업데이트된 행을 삭제한 다음 T1이 롤백 작업을 수행하여 방금 수행한 수정 사항을 취소합니다. 이제 T2가 읽은 행 수가 유효하지 않습니다(한 트랜잭션이 다른 트랜잭션을 읽음)
• 반복 불가능한 읽기(수정된 작업의 경우): 트랜잭션 T1이 레코드 행을 읽은 다음 T2가 T1이 방금 읽은 레코드를 수정합니다. 그런 다음 T1은 이 레코드 행을 다시 읽었고 결과가 방금 읽은 결과와 다르다는 것을 발견했습니다.
• 팬텀 읽기(업데이트 작업용): 트랜잭션 T1은 지정된 where 절에서 반환된 결과 집합을 읽은 다음 트랜잭션 T2는 T1에서 사용하는 쿼리 조건을 충족하는 새로운 레코드 행을 삽입합니다. 그런 다음 T1은 테이블을 다시 검색하지만 T2가 삽입한 데이터를 다시 봅니다. (처음에는 안 봤는데 두 번째에 봤어요)

2. 인덱스

2.1 인덱스 기능

1. 데이터베이스 검색 속도를 높일 수 있습니다.
2. 테이블에서만 생성할 수 있습니다.
3. 직접 또는 간접적으로 생성할 수 있습니다.
4. 인덱스는 최적화 숨기기에 사용할 수 있습니다.
5. 테이블에서는 한 번에 하나의 인덱스만 사용할 수 있습니다.

2.1.1 인덱스 장점

1. 데이터베이스 테이블의 각 데이터 행의 고유성을 보장하기 위해 고유 인덱스를 생성합니다.
2. 인덱스 생성의 주요 이유인 데이터 검색 속도를 크게 높입니다.
데이터베이스 속도 향상 테이블 간의 링크는 데이터베이스 참조 무결성을 달성하는 데 특히 의미가 있습니다.
3. 그룹화 및 정렬 절을 사용하여 검색할 때 쿼리의 그룹화 및 정렬 시간도 크게 줄일 수 있습니다.
4. 인덱스를 사용하면 최적화가 가능합니다. 시스템 성능을 향상시키기 위해 쿼리에 hider를 사용할 수 있습니다.
5. 2.1.2 인덱스 단점

인덱스를 생성하고 유지하는 데 시간이 걸리며, 이는 숫자가 증가함에 따라 증가합니다.

인덱스에는 물리적 공간이 필요합니다. 데이터 테이블이 차지하는 데이터 공간 외에도 각 인덱스는 일정량의 물리적 공간을 차지합니다. 클러스터형 인덱스가 구축되면 필요한 공간이 더 커집니다.
1. 테이블에 데이터를 추가, 삭제, 수정할 때, 인덱스도 유지해야 하므로 데이터 유지 관리 속도가 줄어듭니다.
3. 2.2 인덱스 분류

(1) 일반 인덱스(제한이 없습니다.) (2) 고유 인덱스(인덱스 열의 값) 고유해야 하지만 null 값은 허용됩니다. )

(3) 기본 키 인덱스(null 값을 허용하지 않는 특수한 고유 인덱스입니다. 기본 키 인덱스는 일반적으로 테이블 생성과 동시에 생성됩니다.)

(4) 결합 인덱스
(5) 클러스터형 인덱스는 각 테이블의 기본 키를 기반으로 B+ 트리를 구성하며, 전체 테이블의 행 레코드 데이터는 리프 노드에 저장되므로 클러스터형 인덱스의 리프 노드도 데이터 페이지가 됩니다.
(6) 비클러스터형 인덱스(보조 인덱스)(페이지 노드는 전체 레코드 행을 저장하지 않습니다).


2.3 Index failure

(1) 조건에 or가 있으면 조건에 index가 있어도 사용하지 않습니다(사용하거나 최대한 적게); (2) 쿼리처럼 SELECT * FROM mytable WHEREt Name like'%admin';

(3) 열 유형이 문자열인 경우 조건에서 따옴표로 묶어야 하며, 그렇지 않으면 인덱스가 사용되지 않습니다.

2.4 각 엔진은 인덱스를 지원합니다

엔진 유형 비교:

Index   MyISAM 인덱스   InnoDB 인덱스  메모리 인덱스   B-트리 인덱스  Support       지원 Hash 인덱스  지원되지 않음     해시 인덱스  지원되지 않음     지원됨  R‐Tree 인덱스  지원        지원되지 않음       지원되지 않음   전체 텍스트 색인 지원되지 않음     지원되지 않음       지원되지 않음

MyISAM，InnoDB,Memonry三个常用MySQL
2.5 데이터베이스의 인덱스 구조


이진 트리를 사용하는 경우 이진 트리의 깊이가 너무 커서 I/O 읽기 및 쓰기가 너무 자주 발생하여 쿼리 효율성이 떨어집니다. 따라서 다중 방향 트리 구조를 사용하면 B-트리에 대한 다양한 작업을 통해 B-트리의 높이를 낮게 유지할 수 있습니다.

B-트리는 균형 다중 경로 탐색 트리라고도 합니다. m-차수 B-트리의 특징은 다음과 같습니다.

• 1. 트리의 각 노드에는 최대 m개의 하위 노드(m>=2)가 있습니다.
• 2. 루트 노드와 리프 노드를 제외하고 각 노드에는 최소 (ceil(m/2))개의 하위 노드가 있습니다. 여기서 ceil(x)는 상한을 취하는 함수입니다.
• 3. 루트 노드에는 최소 2개의 하위 노드가 있습니다(B-트리에 루트 노드가 하나만 포함되어 있지 않은 경우).
• 4. 포인트는 모두 동일한 레이어에 나타나며 리프 노드에는 키워드 정보가 포함되어 있지 않습니다(쿼리가 실패한 외부 노드 또는 노드로 간주될 수 있으며 이러한 노드를 가리키는 포인터는 null임). 자식과 자식을 가리키는 포인터도 있고, 레드-블랙 트리와 유사하게 각 널 포인터는 리프 노드로 간주되지만 그려지지 않습니다.)
  
  B+ 트리
  
  어디에 있습니까? 어떤 상황에서 인덱스를 생성하는 것이 적합합니까?
  (1) 키워드 order by, group by, independent 다음에 자주 나타나는 필드에 대한 인덱스를 생성합니다. Union 및 기타 Set 연산의 결과 집합 필드 및 인덱스 생성 목적은 위와 동일합니다.
  (3) 쿼리 선택에 자주 사용되는 필드에 대한 인덱스를 생성합니다.
  (4) 자주 사용되는 속성에 대한 인덱스를 생성합니다.
  (5) 데이터가 거의 필요하지 않은 인덱스 범위 사용을 고려하십시오. 사용자가 업데이트된 테이블의 몇 가지 필드만 자주 쿼리하는 경우 이러한 필드에 인덱스를 생성하여 테이블 스캔을 인덱스 스캔으로 변경하는 것을 고려할 수 있습니다.
  

3.Mysql 구문 순서

즉, SQL에 다음 키워드가 존재할 때 이 순서를 유지해야 합니다:

select[distinct], from, Join (예: Left Join), on, where, group

by, had, Union, order by,limit

4. MySQL 실행 순서

즉, 실행 시 sql은 다음 순서로 실행됩니다.

from, on, Join, where, group by, had, select, discover, Union, order by

group by는 집계 함수와 함께 사용해야 합니다.

예:

select a.Customer,sum(a.OrderPrice) from orders a where a.Customer=’Bush’ or a.Customer = ‘Adams’ group by a.Customer;

다중 테이블 쿼리 구현(내부 조인)

select u.uname,a.addr from lm_user u inner join lm_addr a on u.uid = a.uid;

select from where도 사용할 수 있습니다. 구현 <p></p> <pre class="brush:php;toolbar:false">select u.uname,a.addr from lm_user u, lm_addr a where u.uid = a.uid;</pre> <code>select from where同样可以实现

delimiter $$
create procedure procedure_bill()
comment '查询所有销售情况'
begin
select billid, tx_time, amt from lm_bill;
end $$
delimiter ;

五、存储过程

call procedure_bill();

调用存储过程

show procedure status like 'procedure_bill';

查看存储过程

/**学生表*/
CREATE TABLE Student (
stu_id INT AUTO_INCREMENT,
NAME VARCHAR(30),
age INT ,
class VARCHAR(50),
address VARCHAR(100),
PRIMARY KEY(stu_id)
)
/*学生课程表*/
CREATE TABLE Course(
cour_id INT AUTO_INCREMENT,
NAME VARCHAR(50),
CODE VARCHAR(30),
PRIMARY KEY(cour_id)
)
/**学生课程关联表*/
CREATE TABLE Stu_Cour(
sc_id INT AUTO_INCREMENT,
stu_id INT ,
cour_id INT,
PRIMARY KEY(sc_id)
)

六、建立多对多数据表关系

在数据库中，如果两个表的之间的关系为多对多的关系，如：“学生表和课程表”，一个学生可以选多门课，一门课也可以被多个学生选;根据数据库的设计原则，应当形成第三张关联表。
步骤1:创建三张数据表Student ,Course,Stu_Cour

/*添加外键约束*/
ALTER TABLE Stu_Cour ADD CONSTRAINT stu_FK1 FOREIGN KEY(stu_id) REFERENCES Student(stu_id);
ALTER TABLE Stu_Cour ADD CONSTRAINT cour_FK2 FOREIGN KEY(cour_id) REFERENCES Course(cour_id);

第二步:为Stu_Cour关联表添加外键

rrreee

完成创建！

• 注:为已经添加好的数据表添加外键:
  -语法:alter table 表名 add constraint FK_ID foreign key(你的外键字段名) REFERENCES 外表表名(对应的表的主键字段名);

例: alter table tb_active add constraint FK_ID foreign key(user_id) REFERENCES tb_user(id);

七、数据库引擎（存储引擎）

当你访问数据库时，不管是手工访问，还是程序访问，都不是直接读写数据库文件，而是通过数据库引擎去访问数据库文件。

以关系型数据库为例，发SQL语句给数据库引擎，数据库引擎解释SQL语句，提取出你需要的数据返回给你。因此，对访问者来说，数据库引擎就是SQL语句的解释器。

7.1 MYISAM和InnoDB引擎的区别

主要区别：

• MYISAM 是非事务安全型的，而InnoDB是事务安全型；
• NYISAM锁的粒度是表级锁，而InnoDB支持行级锁；
• MYISAM支持全文本索引，而InnoDB不支持全文索引
• MYISAM相对简单，所以在效率上要优于InnoDB,小型应用可以考虑使用MYISAM;
• MYISAM表是保存成文件的形式，在跨平台的数据转移中使用MYISAM存储会省去不少的麻烦;
• （6）InnoDB表比MYISAM 5. 저장 프로시저
rrreee

저장 프로시저 호출

rrreee

저장 프로시저 보기 🎜rrreee🎜 6. 다대다 데이터 테이블 관계 설정 🎜🎜 데이터베이스에서, 두 테이블 사이의 관계가 여러 쌍인 경우: "학생 테이블 및 코스 일정"과 같은 다중 관계에서는 한 학생이 여러 코스를 선택할 수 있으며 데이터베이스의 설계 원칙에 따라 한 코스를 여러 학생이 선택할 수도 있습니다. 세 번째 연관 테이블이 형성되어야 합니다. 🎜 1단계: Student, Course, Stu_Cour🎜rrreee🎜 3개의 데이터 테이블 생성 2단계: Stu_Cour 관련 테이블에 외래키 추가🎜rrreee🎜생성 완료! 🎜🎜🎜참고: 추가된 데이터 테이블에 외래 키를 추가합니다:🎜 - 구문: alter table table name add Constraint FK_ID 외래 키(외래 키 필드 이름) REFERENCES 외래 테이블 이름(해당 테이블의 기본 키) 필드 name);🎜🎜🎜예: alter table tb_active add Constraint FK_ID foreign key(user_id) REFERENCES tb_user(id);🎜🎜Seven. 데이터베이스 엔진(스토리지 엔진)🎜🎜When 수동 액세스이든 프로그램 액세스이든 데이터베이스에 액세스하고, 데이터베이스 파일을 직접 읽고 쓰지 않고 데이터베이스 엔진을 통해 데이터베이스 파일에 액세스합니다. 🎜🎜관계형 데이터베이스를 예로 들어보겠습니다. 데이터베이스 엔진에 SQL 문을 보내면 데이터베이스 엔진이 SQL 문을 해석하여 필요한 데이터를 추출하여 반환합니다. 따라서 방문자에게 데이터베이스 엔진은 SQL 문의 해석기입니다. 🎜

🎜7.1 MYISAM과 InnoDB 엔진의 차이점🎜

🎜주요 차이점: 🎜🎜🎜MYISAM은 트랜잭션이 불가능하지만 InnoDB는 트랜잭션이 안전합니다. 안전한 유형; 🎜🎜NYISAM 잠금 세분성은 테이블 수준 잠금이고, InnoDB는 행 수준 잠금을 지원하고 🎜🎜MYISAM은 전체 텍스트를 지원합니다. InnoDB는 전체 텍스트 인덱스를 지원하지 않지만 🎜🎜MYISAM은 비교적 간단하므로 효율성 측면에서 InnoDB보다 낫습니다. 소규모 애플리케이션에서는 MYISAM 사용을 고려할 수 있습니다. 🎜🎜MYISAM 테이블은 파일로 저장됩니다. 크로스 플랫폼 데이터 전송에서 MYISAM 저장소를 사용하면 많은 문제가 있습니다.🎜🎜 (6) InnoDB 테이블은 MYISAM 테이블보다 더 안전합니다. 데이터 손실 없이 트랜잭션 테이블로 전환할 수 있습니다. 시나리오: 🎜

• MYISAM은 애플리케이션이 많은 수의 select를 수행해야 하는 경우 고속 저장 및 검색 기능을 제공합니다. code> 쿼리를 사용하는 경우에는 MYISAM이 더 나은 선택입니다. <code>MYISAM管理非事务表，它提供高速存储和检索，以及全文搜索能力，如果应用中需要执行大量的select查询，那么MYISAM是更好的选择。
• InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的insert或update操作，则应该使用innodb
InnoDB는 트랜잭션 처리 애플리케이션에 사용되며 ACID 트랜잭션 지원을 포함한 많은 기능을 갖추고 있습니다. 애플리케이션이 다수의 삽입 또는 업데이트 작업을 수행해야 하는 경우 innodb를 사용해야 합니다. 이는 다중 사용자 성능을 향상시킬 수 있습니다. 동시 작업.

8. 데이터베이스 패러다임

현재 관계형 데이터베이스에는 6가지 패러다임이 있습니다: 첫 번째 정규형 {1NF}, 두 번째 정규형 {2NF}, 세 번째 정규형 {3NF}, Bass-Codd 정규형 {BCNF}, 네 번째 정규형 정규형 {4NF}, 다섯 번째 정규형 {5NF, 완전 정규형이라고도 함}. 최소 요구사항을 충족하는 패러다임이 첫 번째 정규형입니다. 첫 번째 정규형을 기반으로 더 많은 표준 요구 사항을 충족하는 것을 두 번째 정규형 {2NF}라고 하며 다른 패러다임은 이를 따릅니다. 일반적으로 데이터베이스는 세 번째 정규형(3NF)만 충족하면 됩니다. .

패러다임:

• 1NF: 각 열이 원자성을 유지하는지 확인합니다.
• 2NF: 테이블의 각 열이 기본 키(공동 기본 키)와 관련되어 있는지 확인합니다.
• 3NF: 테이블의 각 열이 원자성인지 확인합니다. 기본 키와 직접 관련됨(외래 키)
• BCNF: 1NF를 기반으로 기본이 아닌 속성은 기본 키 하위 집합에 종속될 수 없습니다(3NF를 기반으로 하면 기본 키 하위 집합에 대한 종속성이 제거됩니다).
• 4NF: 동일한 테이블에서 여러 쌍을 결합해야 합니다. 여러 관계 삭제
• 5NF: 최종 구조에서 원래 구조를 다시 설정합니다.

권장 학습: mysql 비디오 튜토리얼

🎜

위 내용은 MySQL 데이터베이스 기본 지식 포인트 보유(정리된 요약)의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!