[MySQL] Do you want to know what a database is? Come in and take a look if you think about it

What is a database

• Preface
• Officially begin
• • connect mysql
  • Understand mysql
  • • first level understanding
    • second level understanding
    • third level understanding
  • piece of cake
  • Meet the pigs running
  • • show databases;
    • create database xxx;
    • create table xxx;
  • Mainstream database
  • mysql architecture
  • Types of SQL statements
  • storage engine

Foreword

Do you have MySQL on hand? It’s best if you don’t have one. If you don’t have one, I recommend the next one. If your operating system happens to be centos and you haven’t downloaded it, it’s a coincidence. I just talked about how to download it in my last article: [MySQL] Teach you step by step how to download MySQL for centos7.

Officially begins

Connect to mysql

Without further ado, connect to the MySQL server:

Maybe you haven’t used such a long option when logging in before. Students who have studied networking should look familiar to 127.0.0.1. Students who haven’t studied networking don’t mind. Okay, no more nonsense.

Mysql is actually a network service. Because it is currently accessed from a single machine (downloading mysq is not only a mysql client, but also a mysqld server), so the local loopback 127.0.0.1 is used directly.

-The capital P refers to the port number of the process you want to access. The server port number was set to 3306 in the configuration file in my previous article, so if you want to manually use the well-known port number here, it is 3306.

-u refers to what identity you want to log in as. I am logging in as root (the root of mysql, not the root in the Linux system). This involves a little knowledge of user management. I won’t go into detail. I will explain it later. A dedicated user-managed blog.

-p means you need to enter a password when logging in. Because when I set up the configuration file in the previous article, I did not require a password for login, so I don’t need this option here. I can log in directly without adding the -p option:

Because the configuration file is already set up in the previous piece, if you do not specify -h when connecting, it will connect to mysqld built on the local server by default. If you remove the – capital P, you will also use 3306 in the configuration file. You can change this in the configuration file. Port number, as long as the port in the configuration file is changed, the modified port must be used when accessing later. I will also talk about mysq in a later blog that can serve across networks. Of course, IP and port are required across networks. The ones used here are local, so there is no need to consider them for the time being.

To briefly summarize:
-h specifies the IP address of the host where the mysql service is deployed.

-Big P specifies the port number of the process to be accessed

-u specifies the user type. Since I haven’t blogged about user management yet, I will use root for the time being.

-p is also the same. We will talk about passwords after we talk about user management. For now, you can log in without a password. You will have to set a password later. Just pay attention. When the password is entered, it will not be echoed, just like Linux.

Understanding mysql

The first level of understanding

The mysql we downloaded has two very important things, one mysql and one mysqld:

Mysql is equivalent to the client, and mysqld is equivalent to the server.

So mysql is actually a network service.

Generally speaking, anything ending with d runs in the background as a daemon process (I also said this before when I talked about the network. For students who don’t understand and want to know more, read this article: [Network] Explain the protocol + sequence with code. ization and deserialization + daemon + jsoncpp).

So mysql is actually a network service based on the CS (client(mysql)-server(mysqld)) mode.

Use netstat to check mysqld on your machine:

The second level of understanding

MySQL is a network service, so what services does it provide? For example, Douyin provides us with short video services. What does mysql do?
mysql is a set of network programs that provide us with data storage services.

The third level of understanding

Generally speaking, the database we talk about refers to data in a specific structure/organization stored on disk or memory. This will be reflected later, so don’t be impatient. To put it bluntly, it is a set of database solutions stored on disk.

For example, certain files in the /val/lib/mysql path:

Let’s not go into detail here.

In order to facilitate your understanding later, when I talk about the database, I refer to the files stored on the disk, when I talk about the database service, I refer to mysqld, and when I talk about the database client, I refer to mysql.

Minor problem

Wouldn’t it be enough to use files to store data? Why use a database?
General files do provide data storage capabilities, but from the perspective of our users, files do not provide very good data content management capabilities.

What is data content management capability?

For example, there are 100,000 lines of content in a file, and each line is an IP address. I want you to count how many IPs start with 110 and what they are. In this case, an ordinary person would faint without counting. .

Fortunately, you are still a programmer and can simply implement it through file reading and writing operations. However, you still need to write code manually, such as reading by line and making statistical judgments on edge changes. Therefore, the analysis process still needs to be done by the programmer himself. Do. The file does not provide such capabilities. It only provides basic reading and writing capabilities and does not manage the content. So what if you want to change the words starting with 110 to 120? Do I want you to delete everything starting with 110?

If you want to modify, query, add, etc. to the content, you have to do it yourself, which is inconvenient.

So this does not provide good data management capabilities.

If it were you, would you be willing to write such code?
Anyway, I don’t want to, so the database is essentially a set of solutions for data content storage. The user gives the database certain fields or requirements, and the database directly returns the results the user wants.

For example, you tell the database: Can you help me pick out the IPs starting with 110?
The database said: OK, here it is for you.
Then the database does the work for you and returns the results to you after it is done.

The whole process is like this:

In this way, we programmers do not need to write these codes ourselves. This is the meaning of the existence of the database.

And saving data in files 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

So what is a database?
From a local perspective, the database is a file that provides us with data storage capabilities. The file has a specific format that allows the mysqld service process to directly add, delete, check, and modify it, instead of letting our programmers directly use system-level file operations to read it. ,Too much trouble.

Overall, the entire process above is a database, a set of data storage solutions.

Databases are generally stored on disk, but there are also memory-level database files, which we will encounter when blogging later.

The level of the database is also one of the important indicators to measure the level of a programmer, so it is also very important to learn the database well.

Meet the pig running

show databases;

First, let’s briefly understand some SQL statements. This article mainly introduces the basic concepts of database. I won’t talk about many SQL statements. I will explain these in detail in my later blog.

First, you can first look at what database files you have and use this statement:

show databases; #Remember to add a semicolon

What I have here is not important, I just want to show you how to use this sentence.

These are actually files, and files must be placed somewhere. So where should these files be placed?
First take a look at your configuration file: /etc/my.cnf, open it with vim, there is a datadir field:

The database files are stored in this path (you must have permission to enter this path). In fact, I have already demonstrated it before. Take another look:

Let’s compare it again:

See, they are just files, but they are all directory files.

create database xxx;

Let’s create a database and see:

At this time, a request to create a database is issued from the mysql client to the mysqld server.

Show it:

Let’s take a look at the files in the corresponding directory:

Go into this directory and take a look:

There is only one db.opt file, which is related to the database, so don’t worry about it.

Then we can draw the conclusion that create suggests a database, which is essentially creating a directory in Linux.

create table xxx;

Next, create a table in the database you just created. What is a table? You can tell from the name that it is a table. Anyway, it is a thing that stores data. Like Excel.

Before creating a table, you need to select a database, just like there are many directories and you want to enter which directory to perform operations.

So first:

use xxx;#xxx is the name of the database

Then you can create the table:

This involves table operations, which I will talk about in a special blog later. Here I will briefly explain what the above means:

In fact, just like Excel, there is a table called student, and the first column represents name, which is the student’s name; the second column represents age, which is the student’s age; and the third column represents gender, that is, the gender of the student. In fact, the above writing method can also be written on one line, but it looks more beautiful this way.

I just want to see pig running here, I will talk about it in detail in the blog later.

Creating a table in the database is equivalent to creating a file in the corresponding directory:

You can see that two new files have been added. Don’t worry about what these two files are until they are two files created for us.

Therefore, the essence of building a table in the database is to create the corresponding file under Linux. You can see the sizes of these two files, indicating that they contain content. Anyway, the database server does it for us.

Then insert some data into the table:

insert into table name (each column name) values (each column corresponds to the data you want to insert)

like:

Use the following statement to view all contents in the table:

select * from table name;

look:

Isn’t it very similar to an Excel spreadsheet? Note that the above table is only logical storage. It is the table structure in which way you want to present it. What is actually stored in the file is still binary.

The above work is done for us by the mysql server, which is mysqld. So based on the above display, we can know that the essence of the database is also a file, but these files are not directly operated by our programmers, but are served by the database. Duan did it for us.

Okay, I’ve almost read about Pig Running, and I’ll talk about it in more detail later in the blog. Let’s talk about something else.

Mainstream database

• SQL Sever: Microsoft’s product, the favorite of .Net programmers, suitable for medium and large projects.
• Oracle: Oracle products are suitable for large projects and complex business logic. Concurrency is generally not as good as MySQL.
• MySQL: The most popular database in the world, belonging to Oracle, has good concurrency and is not suitable for complex businesses. Mainly used in e-commerce, SNS, and forums. It has good effect on simple SQL processing.
• PostgreSQL: A relational database developed by the Department of Computer Science at the University of California, Berkeley. It is free to use, modify, and distribute, whether for private, commercial, or academic research use.
• SQLite: is a lightweight database, an ACID-compliant relational database management system, which is contained in a relatively small C library. Its design target is embedded, and it has been used in many embedded products. It occupies very low resources. In embedded devices, only a few hundred K of memory may be enough.
• H2: It is an embedded database developed in Java. It is just a class library and can be directly embedded into application projects.

Most companies choose to use mysql. Although mysql is open source, it does not mean that it is not powerful. Just like Linux is open source, it is very powerful.

mysql architecture

MySQL is a portable database that runs on almost all current operating systems, such as Unix/Linux, Windows, Mac, and Solaris. Various systems have different underlying implementations, but MySQL can basically guarantee the consistency of the physical architecture on each platform.

Take a look at this picture first:

Among them, the top-level Client Connectors:

The embodiment in this article is the command line mysql. In the future, I will also use C++ to connect to mysql, as well as other graphical interfaces (such as Navicat).

For the MySQL server, the overall function is mainly composed of three layers.

The first layer is the Connection Pool, which is the connection pool:

Mysql is also a network service. It also needs to do connection management and permission confirmation functions. Therefore, connection management and establishing rights for users to ensure security are all done at this layer.

Second floor:

The second layer mainly performs lexical and grammatical analysis on the SQL statement. If necessary, the statement will be optimized and then handed over to the third layer.

the third floor:

What is matched is each storage engine, which functions much like a driver.

Different types of storage engines do different things, and different data also have different types. Some are documents, binaries, etc. For different types of data, different processing solutions are used, and the results are different.

For example, MyISAM above is more suitable for reading large texts, and InnoDB has a strong indexing function and can be searched quickly.

The next level belongs to the file-related system layer. Therefore, the three layers of the mysql service mentioned above actually all belong to the user layer. From an os perspective, they belong to the user process. The bottom layer that reads files belongs to the os. From a network perspective, these three layers belong to the application layer.

Types of SQL statements

There are three categories.

• DDL [data definition language] Data definition language, used to maintain the structure of stored data
  Representative instructions: create, drop, alter.
  ? They are all related statements for creating tables, modifying table structures, and maintaining storage structures.

• DML [data manipulation language] Data manipulation language, used to operate data
  Representative instructions: insert, delete, update.
  DML is divided into a separate DQL, data query language, which represents the command: select
  ? Operate on the contents of the table.

• DCL [Data Control Language] Data control language, mainly responsible for permission management and transactions
  Representative instructions: grant, revoke, commit, begin.
  ? Statements for permissions and transaction management.

Storage engine

The storage engine is the implementation method of how the database management system stores data, how to index the stored data, and how to update and query data.

The core of MySQL is the plug-in storage engine, which supports multiple storage engines.

You can use show engines; to see what storage engines are:

I have another picture here:

The two most used are InnoDB and MyISAM, 80% of which are InnoDB. When I create configuration files, InnoDB is the default.

It ends here. . .