Basic knowledge of MySQL database

1. Understanding Databases

1.1 The relationship between database and data structure

Data structure:

It refers to a collection of data elements that have one or more specific relationships with each other. It is an abstract discipline.

The data structures we are familiar with are:

Sequential list, linked list, stack, queue, binary tree, hash table...

database:

Unlike data structures, it is a more specific type of software that can manage data. However, to implement this database software, many data structures are needed.

The specific functions of using database to manage data include:

• Description: describes how a piece of data is represented
• Organization: How to aggregate lots of data

The purpose of management is to:

In order to add, delete, modify and check

1.2 Why do we need a database?

The prerequisite for a database to manage data is to store the data. But why do we need a database when we can just use files to store data?

Because using files to save data has the following disadvantages:

• File security issues
• Files are not conducive to data query and management
• Files are not conducive to storing massive amounts of data
• It is inconvenient to control files in the program

1.3 Database Storage

Most computers today follow the von Neumann architecture.

Data is generally stored in internal memory and external memory (external memory includes: hard disk, CD, USB flash drive, floppy disk, etc., but here it refers to the hard disk)

There are many types of databases, and there is no unified regulation on where all databases are stored, but the data storage locations of the main databases are as follows:

• Most databases organize data in external storage, such as MySQL , Oracle , SQL Server
• A few databases organize data into memory, such as Redis

Supplement: Why does MySQL store data in external storage (hard disk)? Here we need to first understand the difference between memory and hard disk

Based on the differences between 2, 3, and 4 above, it is believed that MySQL is better to use hard disk to store data.

2. SQL

2.1 Introduction

Structured Query Language (SQL) is a special-purpose programming language. It is a database query and programming language used to access data and query, update, and manage relational database systems.

Note: Differences between SQL and MySQL

• SQL is a programming language
• MySQL is a software that belongs to a type of relational database. All relational databases support SQL, while some non-relational databases do not support SQL.

2.2 Classification

SQL can be divided into 6 parts:

• Data Query Language ( DQL ): Its statements are also called "data retrieval statements" and are used to obtain data from tables and determine how the data is presented in the application.
• Data Manipulation Language ( DML ): Its statements include the verbs INSERT , UPDATE , and DELETE . They are used for adding, modifying, and deleting respectively.
• Transaction Control Language ( TCL ): Its statements ensure that all rows of the table affected by the DML statement are updated in a timely manner. Including COMMIT (commit) command, SAVEPOINT (save point) command, ROLLBACK (rollback) command.
• Data Control Language ( DCL ): Its statements implement permission control through GRANT or REVOKE, determining the access of individual users and user groups to database objects. Some RDBMS can use GRANT or REVOKE to control access to individual columns of a table.
• Data Definition Language ( DDL ): Its statements include the verbs CREATE, ALTER, and DROP. Create new tables in the database or modify or delete tables (CREATE TABLE or DROP TABLE), add indexes to tables, etc.
• Cursor Control Language (CCL): Its statements, like DECLARE CURSOR , FETCH INTO , and UPDATE WHERE CURRENT are used to operate on individual rows of one or more tables.

3. Types of databases

Databases can be roughly divided into:

• Relational Database
• Non-relational database

3.1 Relational Database

introduce:

• Refers to a database that uses a relational model to organize data. Simply put, the relational model refers to a two-dimensional table model, and a relational database is a data organization consisting of two-dimensional tables and the connections between them.
• Relational databases are all based on standard SQL, but some of the internal implementations are different.

Common relational databases:

Oracle : Oracle products are the king of databases. Due to the charges and the increasing demand for machines as the amount of data increases, the cost of use is very high. Applicable to companies such as banks that have high requirements for data security and services and are wealthy
MySQL : Acquired by Oracle, open source and free. Now many companies use it widely
SQL Server : A Microsoft product. Since this database was once bundled with the windows server system and most server systems are on Linux, it is gradually losing market share. Now supports Linux

3.2 Non-relational Databases

introduce:

Non-relational databases generally refer to NoSQL . Unlike relational databases, they do not guarantee the ACID properties of relational data. They remove the relational properties of relational databases and do not require SQL-based implementation.

Common non-relational databases:

• redis : based on key-value pairs
• mongodb : based on column family
• hbase : document-based

3.3 Differences

Replenish:

• OLTP(On-Line Transaction Processing)：
• OLAP(On-Line Analytical Processing)：

4. MySQL Program Structure

MySQL is actually a "client-server" structure program

So what are clients and servers? The following will briefly introduce

4.1 Client and Server

introduce:

• Client: The party that initiates the request
• Server: is the party that passively accepts requests
• The data sent by the client to the server is called Request .
• The data returned by the server to the client is called Response .
• A server may provide services to multiple clients at the same time
• Most client servers use this "one request, one response" interaction model.
• The client and server communicate through the network

Example:

When you go to a restaurant to eat, you ask the boss for a bowl of Nongjia or a bowl of Xiang, and then the boss will bring you the meal you ordered.

At this point, you are the "client" and the restaurant owner is the "server". Ordering food from the owner is your "request", and the owner serving you the food is the "response". And one boss can actually serve multiple customers.

4.2 MySQL Client-Server Architecture

When we use MySQL, we will actually encounter the following situations:

• Install the MySQL client and MySQL server on the same host (this is the case when we install it on our own PC)

• MySQL client and MySQL server are on two different hosts

• In actual work, the following situation often occurs: clients of multiple hosts connect to the server of one host.

Notice:

• MySQL stores data on the server, which is the database entity and is responsible for managing data.
• The MySQL client is actually a very simple program that is only used to interact with users. This program may be: a command line program, a program with a visual interface, or a program implemented by your own code

4.3 MySQL Server

introduce:

• The MySQL server actually contains several databases (each database is considered as a logical collection)
• A database contains several related tables
• A table contains several rows (each row has several records)
• A row contains several columns (each column has specific type requirements)

Example:

If there is a MySQL server storing CSDN data, it may have 3 databases. Database 1 stores data such as user information, user rankings, and number of articles. Database 2 stores data such as activity content, contributed articles, and prize information. Database 2 stores data such as videos and resources. The data in database 1, such as user information, is actually a table, similar to an Excel table, and several related tables constitute a database.

Notice:

The meaning of database actually depends on the context. It can be a discipline, a type of software, a server, or a collection of data.

This is the end of this article about the basic knowledge of MySQL database. For more relevant MySQL database content, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!