Detailed explanation of database principle, save your final exam four parts [1]: from the introduction of database to relational algebra and SQL sentences

Database principles one to three chapters

Chapter One Introduction

1. Overview of the database system

1. Four basic concepts of database

① Data

Data is the basic object stored in the database and a symbolic record describing things.

② Database

Database referred to as DB, is 长期存储in the computer, 有组织and 可共享the大量数据的集合

Basic features :

  • Organize, describe, and store according to a certain data model
  • Can be shared, less redundancy, and easy to expand
  • High data independence
③ Database Management System (DBMS)

The database management system (DBMS) is located 用户与操作系统之间on the first floor 数据管理软件, and it 操作系统一样都是基础软件is also a large and complex one 软件系统. It is used to organize and store data scientifically, and obtain and maintain data efficiently.

Main functions :

  • Data definition function: Provide DDL (Data Definition Language)
  • Data organization, storage and management functions
  • Data manipulation function: Provide DML (Data Manipulation Language)
  • Database transaction management and operation management
  • Database establishment and maintenance function
  • Other functions: connect to other software, data conversion
④ Database system (DBS)

The database system ((Database System, DBS) refers to the system structure after the introduction of the database into the computer system. The database system is called the database without causing confusion.

Composition :

  • database
  • Database management system
  • application
  • Database Administrator (DBA)

Features :

  • Data structured
  • High data sharing, low redundancy and easy to expand
  • High data independence: applications and databases are independent of each other, reflected in physical and logical independence
  • The data is uniformly managed and controlled by the database management system

2. The relationship between DBS, DBMS, and DB

DBS is the largest, including DBMS, DB, DBA

Second, the data model

数据模型也是一种模型,它是对现实世界数据特征的抽象

1. Two types of data models

第一类模型:概念模型,第二类模型:逻辑模型和物理模型

① Transformation process

Three worlds, two-step abstraction: Data comes from the real world. We need to abstract the real world data into data in the information world, and then abstract the data in the information world into a data form that can be recognized by the machine world.

Real world "" "Conceptual model" "" Data model supported by database management system

Conceptual Model "" Logical Model "" "Physical Model

② Basic concepts

Conceptual model : Model data and information according to the user's point of view for database design

Data models supported by the database management system :

Logical model : mainly includes mesh model, hierarchical model, relational model, object-oriented data model, object relational data model, semi-structured data model, etc. Model data according to the point of view of the computer system for DBMS implementation.

Physical model : It is an abstraction of the lowest level of data, describing the representation and access methods of data in the system, and the storage and access methods on disks or tapes.

2. Conceptual model

① Basic concepts of the information world

Substance : things that exist objectively and can be distinguished from each other学生、老师等可称为实体

Attribute : A certain characteristic of an entity is called an attribute学生的学号

Code : The attribute that uniquely identifies the entity is integrated as a code身份证号

Entity type : entities with the same attributes must have common characteristics and properties一张表的表名

Entity set : a collection of entities of the same type学生实体的集合,即一张表的所有属性

Relations : Relations between different entities are one-to-one, one-to-many, and many-to-many.

A representation of the conceptual model: entity-connection method, using ER diagrams to describe the conceptual model of the real world.

3. The components of the data model

数据结构、数据操作、数据的完整性

① Data structure

The data structure describes the constituent objects of the database and the connections between the objects .

There are two types of content described: one is related to the type, content, and nature of the object; the other is related to the connection between data.

The data structure is a collection of the object types described, and is a description of the static characteristics of the system.

② Data operation

Data operation refers to the set of operations allowed to be performed on various object instances in the database. Adding, deleting, modifying and checking is the main operation.

Data manipulation is the description of the dynamic characteristics of the system.

③ Data integrity constraints

Data integrity constraints are a set of integrity rules.

4. Commonly used data models

层次模型、网状模型、关系模型、面向对象的数据模型、对象关系数据模型、半结构化数据模型

① Hierarchical model

Two basic conditions :

  • There is only one node and no parent node, this node is called the root node
  • Nodes other than the root have one and only one parent node

Connection : The connection between nodes (directed edges) is used to indicate that this connection is a one-to-many connection between father and son.

Features : The node's parent is unique, and can only handle one-to-many physical connections. Any record value can only be viewed by its path to reveal its full meaning. No child record value can exist independently of the parent record value.

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-DPFr47Eo-1622640599186) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602151143763.png)]

Operation and integrity constraints :

  • No corresponding parent node value cannot be inserted into the child node value
  • If the parent node value is deleted, the corresponding child node value is also deleted
  • To perform an update operation, all corresponding records must be updated

Advantages :

The hierarchical model is relatively simple and clear, provides good integrity support, and has high query efficiency

Disadvantages :

The many-to-many relationship between nodes is unnatural, the operation is complicated, and there are many restrictions on insertion and deletion. The query of child nodes must pass through the parent node, which is too procedural.

② Mesh model structure

层次模型可看作网状模型的特例

Two conditions :

  • Allow more than one node without parents
  • A node can have multiple parents

Features : There can be multiple nodes without parent nodes, at least one node has multiple parent nodes, and the relationship between the child node and the parent node may not be unique.

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-UY5bhnu2-1622640599188) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602151836522.png)]

Operation and integrity constraints :

  • Code: unique identification data
  • There is a one-to-many relationship between parent records and child records in a contact
  • Support certain constraints between parent records and child records

Advantages : able to directly describe the real world

Disadvantages : The structure is more complicated, DDL and DML languages ​​are more complicated, the connection defines the access path, and the user must understand the details of the structure

③Relationship model

Terminology of the relational model :

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-lwxLupqz-1622640599192) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602152554305.png)]

Domain: A set of values ​​with the same data type, and the value range of the attribute comes from a certain domain. For example, the domain of the age attribute of college students is (15~35), and the domain of gender is (male, female).

The condition of the relational model : the data structure of each relation is a standardized two-dimensional table. For example, the above table is a two-dimensional table with a relation named relational model, which represents the structure of relational model.

Operation and integrity constraints :

  • Physical integrity
  • Referential integrity
  • User-defined integrity

Advantages : Based on strict mathematical concepts, the concept is single, and the access path is transparent.

Disadvantages : Insufficient query efficiency, users' query requests must be optimized, which increases the difficulty of development

Three, the structure of the database system

1. The concept of database system mode

Type : description of the structure and attributes of a certain data

Value : specific assignment of type

Mode : description of the logical structure and characteristics of all data in the database

2. Three-level model structure

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-TrlcFNYS-1622640599194) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602160354773.png)]
外模式:用户所看到的数据视图
模式/逻辑模式:所建立的表的集合
内模式:表中数据的存储方式和物理结构
① Mode

Concept : Mode is also called logical mode, which is a description of the logical structure and characteristics of all data in the database, and is a common data view for all users.

Status : It is the middle layer of the database system schema structure

Definition : the logical structure of the data, the connection between the data, the related security and integrity requirements

② Outer mode

Concept : External mode is also called sub-mode or user mode. It is a description of the logical structure and characteristics of local data that database users can see and use. It is a data view of database users and a logical representation of data related to a certain application.

Status : between mode and application

Relationship : It has a one-to-many relationship with internal models and applications

③ Internal mode

一个数据库只有一个内模式

Concept : The internal mode is also called the storage mode, which is the description of the physical structure and storage mode of the data, and the organization mode of the data in the database.

3. Secondary image

① External mode/mode image

Concept :

It describes the global logical structure of the data, and the external mode describes the local logical structure of the data. A mode can have multiple external modes. Each database has an external mode/mode image, which defines the corresponding relationship between the external mode and the mode.

Logical independence of data :

When the mode is changed, the database administrator makes corresponding changes to the external mode/mode image (changes to attributes and relationships), and the application program is written in the external mode, so that the application program does not need to be changed, ensuring the logical independence of the data and the program. Logical independence of data

② Pattern/pattern image

Concept :

The database has only one mode and only one internal mode, so the mode/mode map is unique, and it defines the correspondence between the global logical structure of the data and the storage structure.

Physical independence of data :

When the database storage structure changes (changes to the storage structure), the database administrator makes corresponding changes to the mode/mode image to keep the mode unchanged, so that the application does not need to be changed. Ensure the physical independence of data and programs, referred to as physical independence of data.

Fourth, the composition of the database system (DBS)

Database (DB), database management system (DBMS), application, database administrator (DBA)

Chapter 2: Relational Database

1. Relationship definition, operation and completeness

1. Relationship

① Domain

Concept: a set of values ​​with the same data type

Features: The number of different values ​​allowed in a domain is called the cardinality of the domain

② Cartesian product

Example :

D1=Supervision set SUPERVISOR={Zhang Qingmei, Liu Yi}
D2=Specialty set SPECIALITY={Computer major, Information major}
D3=Postgraduate set POSTGRADUATE={Li Yong, Liu Chen, Wang Min}

The Cartesian product of D1, D2, and D3 is D1 × D2 × D3 = {
(Zhang Qingmei, computer major, Li Yong), (Zhang Qingmei, computer major, Liu Chen)
(Zhang Qingmei, computer major, Wang Min), (Zhang Qingmei, information Major, Li Yong), (Zhang Qingmei, Information Major, Liu Chen), (Zhang Qingmei, Information Major, Wang Min), (Liu Yi, Computer Major, Li Yong), (Liu Yi, Computer Major, Liu Chen), (Liu Yi, computer major, Wang Min), (Liu Yi, information major, Li Yong), (Liu Yi, information major, Liu Chen), (Liu Yi, information major, Wang Min)}

Terminology :

Zhang Qingmei-can be called a component (a value in a tuple)

(Zhang Qingmei, computer major, Li Yong)-can be called a tuple (one line)

2×2×3=12-called the cardinality of the Cartesian product, that is, the number of tuples

③ relationship

关系是笛卡尔积的有限子集,是一张二维表

Relationship terms :

Candidate code: The value of a certain attribute group can uniquely identify a tuple, but a subset cannot.

Main code: Select one of multiple candidate codes as the main code.

Main attributes: all attributes of the candidate code

Non-primary attributes/non-code attributes: attributes other than the primary attribute of the attribute group

Full code: When all attributes of the relational pattern are candidate codes for this relational pattern

Type of relationship :

Basic relationship (basic table or base table), query table, view table

The nature of the basic relationship :

  • The columns are of the same type
  • Different columns can come from the same domain
  • The column or the order of the columns does not matter
  • Candidate codes of any two tuples cannot be the same
  • The components must take atomic values, and each component is inseparable

2. Relationship model

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-uIp7dLVS-1622640599196) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602171938197.png)]

3. Relationship operations

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-1LTquDv9-1622640599197) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602172017794.png)]

4. Relationship integrity

出题会根据完整性规则

① Entity integrity

If the attribute A is the main attribute of the basic relationship R, then A cannot take a null value.

  • The main attribute is not empty (candidate code cannot be empty)
  • The main code is different
② Referential integrity

Concept :

Let F be one or a group of attributes of the basic relation R, but not the code of the relation R, and Ks is the main code of the basic S relation. If F corresponds to Ks, then F is the outer code of R. It is also said that the basic relationship R is the reference relationship, the referenced relationship or the target relationship of the basic relationship S. The relationship R and the relationship S may be the same relationship.

Meaning : If a tuple t2 of the relation R2 refers to a tuple t1 of the relation R1, then t1 must exist.

Understand :

If the outer code F of the relation R2 corresponds to the main code P of the relation R1, then the value of each tuple in R2 on F:
❖ or a null value,
❖ or equal to the P value of a tuple in R1 .

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-X8YTdmEk-1622640599198) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602190650566.png)]
③ User-defined completeness

Completeness defined by the user: For example, the score cannot be negative, the gender cannot be a ladyboy, only male and female.

Two, relational algebra

用集合运算符和专门的关系运算符,运算对象是关系,运算结果还是关系

1. Relational operators

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-GVQDzH4J-1622640599198) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602191136279.png)]

2. And

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-kFDsqcMo-1622640599199) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602191216021.png)]

Combine the two tables, keep the difference between yourself and the other party, and save the other party's same

3. Poor

[External link image transfer failed. The source site may have a hotlink prevention mechanism. It is recommended to save the image and upload it directly (img-o5PXLvf9-1622640599199) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602191418011.png)]

The two tables are subtracted, discard the same, keep the difference, and do not merge

4. Hand in

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-VwW0ygOP-1622640599200) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602191654816.png)]

Combine the two tables, discard the differences, and keep the same

5. Cartesian product

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-GlDlMcnf-1622640599201) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602191932693.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-wKyMJjOx-1622640599202) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602191945744.png)]

Find all cases of the combination of two table tuples

6. Choose

Find tuples based on attributes

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-rJDkqtwA-1622640599202) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602192310711.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-LoI9T1zA-1622640599203) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602192318166.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-hOmJrVzz-1622640599204) (C:\Users\Official Er's Leizi\Desktop\Future Village Chief\image -20210602192416569.png)]

7. Projection

Select attributes to re-establish relationships

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-ksChLMWS-1622640599205) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602192449045.png)]

8. Connect

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-Vm7MFsAE-1622640599206) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602193610898.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-SZfKknZy-1622640599207) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602193135314.png)]
[External link image transfer failed. The source site may have a hotlink prevention mechanism. It is recommended to save the image and upload it directly (img-zV8vSBrt-1622640599208) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602193217890.png)]

Combine first (Cartesian product), then compare θ according to the values ​​of the two attributes of CE, and discard the combination that does not meet CθE.

① Equivalent connection
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-ji8Z6IAV-1622640599209) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602192920301.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-ZzJN5WIh-1622640599210) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602193228282.png)]

During equivalence connection, tuples that are not equal will be discarded, and the discarded tuples are called floating tuples .

② Natural connection
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-HDhqpO4z-1622640599210) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602193006900.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-zNqsQMHO-1622640599211) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602193247433.png)]

Natural connection discards repeated attribute columns on the basis of equivalence connection

③ External connection

Keep the natural connection of floating tuples

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-7DJOv2ub-1622640599212) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602194057022.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-qm8YmpMt-1622640599213) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602194157605.png)]

Left outer connection :

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-rvWHvx1C-1622640599213) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602194228295.png)]

Right outer connection :

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-JbJ6bpJj-1622640599214) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602194237226.png)]

9. Division operation

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-3EZ6OyMA-1622640599215) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602194316852.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-0sMhv5Tu-1622640599216) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602194325339.png)]

From the same attribute column of R and S, find the combination of all the attribute value combinations of BC in S (the same attribute of A is a group) in R, and get the attribute value except BC.

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-mP8BhMgZ-1622640599216) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602194654055.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-EnuPqTLe-1622640599217) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602194702332.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-gydAfJcB-1622640599218) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602194708341.png)]

Three, comprehensive calculation examples

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-vhrXT8Bq-1622640599218) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602194903618.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-RpVYY6Ko-1622640599219) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602194911450.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-yMb9KDpn-1622640599221) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602194917899.png)]
[External link image transfer failed. The source site may have a hotlink prevention mechanism. It is recommended to save the image and upload it directly (img-9ng0PdfL-1622640599222) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602194923900.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-sRP4iWih-1622640599223) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602194939726.png)]
[External link image transfer failed. The source site may have a hotlink prevention mechanism. It is recommended to save the image and upload it directly (img-Ss6ntkPP-1622640599224) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602194953206.png)]

Chapter 3: Overview of SQL

SQL is an integrated language that integrates data query, data definition, data manipulation, and data control. It is a self-contained language, sad embedded language. It is a collection-oriented language.

Basic table : a relationship corresponds to a basic table

Storage files : the logical structure constitutes the internal model of the relational database, and the physical structure is hidden from users

View : A virtual table. The database only stores the definition of the view and does not store the data corresponding to the view.

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-rOmN5la5-1622640599225) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602195943959.png)]

Relations : A relational database system can build multiple databases, and a database can build multiple patterns. A pattern usually includes multiple database objects such as tables, views, and indexes.

1. The definition and deletion of the model

① Define mode
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-Pp7W8cnx-1622640599226) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602200138617.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-55hGqIPX-1622640599227) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602200353449.png)]
② Delete mode
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-vPb1ZF22-1622640599228) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602200432102.png)]

Two, the definition, deletion and modification of the basic table

① Define the basic table
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-kFjJSFPD-1622640599229) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602200620662.png)]
[External link image transfer failed. The source site may have a hotlink prevention mechanism. It is recommended to save the image and upload it directly (img-XvAR5cLQ-1622640599230) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602201022106.png)]
② Modify the basic table
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-EXAYISHc-1622640599230) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602201235979.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-chFNWTQT-1622640599232) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602201245163.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-VMmV375m-1622640599233) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602201422702.png)]
③ Delete the basic table
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-cSYErPgt-1622640599233) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602201643687.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-1foDkrYg-1622640599234) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602201714432.png)]
[External link image transfer failed. The source site may have a hotlink prevention mechanism. It is recommended to save the image and upload it directly (img-5eSJdgbT-1622640599234) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602201729017.png)]

Three, index creation and deletion

Indexes can be dynamically defined, and users are not allowed to reference indexes in data operations. The establishment of the index should be appropriate, established in the column with high utilization rate, too many indexes will reduce the efficiency of insert, delete, and update.

① The establishment of index
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-ZlkbZkon-1622640599235) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602202057652.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-PlyaOvBg-1622640599236) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602202104679.png)]
② Modification of the index
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-QFSaJFwt-1622640599236) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602202155186.png)]
③ Deletion of index
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-RDbtoL4d-1622640599237) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602202210434.png)]

Four, query data

4. Single table query of query

① Single table query according to query conditions
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-4gTNrvZL-1622640599239) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602202529353.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-DKkePeZc-1622640599240) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602202724773.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-OEt0o7dN-1622640599241) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602202753358.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-nzilItuz-1622640599242) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602202805655.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-mmhp3axQ-1622640599243) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602202818311.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-TIRXjlkL-1622640599243) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602202827988.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-9Vc15XdV-1622640599244) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602202839105.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-kNoIJ8Ko-1622640599245) (C:\Users\Official two's Leizi\Desktop\Future Village Chief\image -20210602202900024.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-7bu5TbSY-1622640599245) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602202918320.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-clkm9avd-1622640599245) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602202928624.png)]
② Focus function and GROUP BY
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-F8PgJj0u-1622640599246) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602203041106.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-dIjAnHEx-1622640599247) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602203052448.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-fVf8WIlD-1622640599248) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602203201433.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-HzHieyCQ-1622640599249) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602203109469.png)]

5. Query connection query

① Equivalent connection
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-ltJycB5B-1622640599249) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602204223614.png)]
② Natural connection
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-pcitOnGo-1622640599250) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602204321653.png)]
③ Self-connection
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-iYEPvGEQ-1622640599250) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602204400521.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-2tgXYMx2-1622640599251) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602204412250.png)]
④ External connection
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-Qj4fFlAC-1622640599251) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602204445922.png)]
⑥ Multi-table connection
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-ksx4mEkt-1622640599252) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602205237613.png)]

6. Nested query of query

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-DxBuQmry-1622640599253) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602205316547.png)]
[External link image transfer failed. The source site may have an anti-leeching mechanism. It is recommended to save the image and upload it directly (img-CEX8Tp2S-1622640599254) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602205334055.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-2ZpWx187-1622640599254) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602205607992.png)]
① IN predicate
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-boHZBYe7-1622640599255) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602205440102.png)]
② Operator comparison operator
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-cJ8YkrHz-1622640599256) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602205840842.png)]
③ ANY, SOME, ALL
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-Ptn0iwuJ-1622640599256) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602205858161.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-vY0MC4hi-1622640599257) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602205916109.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-iYcY75KK-1622640599257) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602205931798.png)]
④ EXISTS
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-Kq7XslsN-1622640599258) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602210004554.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-HAs70NIV-1622640599260) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602210024341.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-Qw1RXGwq-1622640599260) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602210037109.png)]

7. Collection query of query

① And operate UNION
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-Pho7iu9V-1622640599261) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602210141426.png)]
② Handover operation INTERSECT
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-VK2r6HeW-1622640599262) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602210153944.png)]
③ Difference operation EXCEPT
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-kdUExzMT-1622640599262) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602210211379.png)]

8. Query comprehensive exercises

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-Kyo5imDB-1622640599263) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602203305143.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-fscHFUnY-1622640599264) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602203311940.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-TGIeTZTb-1622640599264) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602203319111.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-x4wGprrT-1622640599265)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602203329981.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QaqsGA7V-1622640599265)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602210310267.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FM1AQzMV-1622640599266)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602210316109.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ML19vO72-1622640599267)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602210324891.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JAIMsoFs-1622640599268)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602210337443.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tBjaCTv6-1622640599269)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602210348666.png)]

Five, insert data

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AvoOIaSL-1622640599272)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602210636698.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xW0XlCNA-1622640599273)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602210710614.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LCsWC4JQ-1622640599274)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602210704469.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-f7TCi7qu-1622640599275)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602210845417.png)]

2. Examples

① Insert tuple
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Izz3vPuW-1622640599275)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602210730980.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PrqbZdCP-1622640599276)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602210742109.png)]
② Insert subquery
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ng8IQvDz-1622640599277)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602210802852.png)]

Sixth, modify the data

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xy6XOEqS-1622640599277)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602210909990.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MZ4B6Ii4-1622640599278)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602210925623.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mZE1EeFb-1622640599278)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602211254292.png)]
① Modify the value of a tuple
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qzYiS84A-1622640599279)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602211204142.png)]
② Modify the value of multiple tuples
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zvXj5kii-1622640599279)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602211218524.png)]
③ Modification statement with subquery
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GjcudoNo-1622640599279)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602211238098.png)]

Seven, delete data

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YZRrs1IO-1622640599280)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602211316691.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7JAj1CEb-1622640599281)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602211325829.png)]
① Delete the value of a tuple
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bOLKYuMW-1622640599281)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602211341851.png)]
② Delete the value of multiple tuples
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GuD073sV-1622640599282)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602211410463.png)]
③ Delete statement with subquery
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Zr7Gm0Jt-1622640599282)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602211428445.png)]

Eight, about null values

1. Handling of null values

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uT3YUYie-1622640599283)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602211524852.png)]

2. The generation of null values

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yEJFJXkq-1622640599283)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602211534870.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jwdLWpmS-1622640599284)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602211606461.png)]

3. Judgment of null value

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4yOkqkg1-1622640599284)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602211627287.png)]

4. Constraints on null values

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KMSUJtsZ-1622640599285)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602211643541.png)]

5. Arithmetic operations, comparison operations and logical operations of null values

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VA5tzIM1-1622640599285)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602211748479.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hZs0xC74-1622640599285)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602211754229.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-z7UZsFqa-1622640599286)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602211805197.png)]

Nine, view

1. Concept

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VtyDoaIu-1622640599286)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602211832396.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-C4w7zfgI-1622640599287)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602212552192.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nZcdIfbn-1622640599288)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602212558505.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BpFruKrD-1622640599289)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602212609740.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-B4FeRPOC-1622640599289)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602212618664.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BjzxUZE0-1622640599290)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602212634559.png)]

2. Create a view

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uaMBz0b0-1622640599291)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602211844963.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-M618lagT-1622640599292)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602211902329.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yZqgODFe-1622640599293)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602211911842.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-d6Xm2uh0-1622640599294)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602211928145.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iPIrjvJu-1622640599294)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602212114647.png)]
① Establish a view based on multiple base tables
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TFYI8CfB-1622640599295)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602212140150.png)]
② View based on view
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tMZwFoq2-1622640599295)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602212157377.png)]
③ View with expression
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KfnGO3mP-1622640599296)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602212230222.png)]
④ Group view
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ztsqK5TI-1622640599296)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602212247060.png)]

2. Delete the view

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dkmFS7rv-1622640599297)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602212314364.png)]

3. Query view

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0PcJfGTz-1622640599297)(C:\Users\官二的磊子\Desktop\未来村村长\image-20210602212333154.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-I52rGg97-1622640599298) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602212345943.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-KzcdT71r-1622640599299) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602212409671.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-vXMRaYTk-1622640599299) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602212425664.png)]

4. Update the view

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-VoKkLQmH-1622640599300) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602212454639.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-rp3qoXkO-1622640599300) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210602212500411.png)]