Technology Sharing

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

Login Basic Use

enter:mysql -h 127.0.0.1 -P 3306 -u root -p

-hIndicates the host where the mysql service is deployed.
-PIndicate the port number we want to access
-uSpecify the logged in user
-pIndicates the password that needs to be entered

If -h 127.0.0.1 is not specified, the default is to connect to the local
If -P 3306 is not specified, the default connection is to port 3306.

enter:mysql -y root

Basic concepts of database

Database storage solution

Understanding from the server's perspective

mysqlIs the client of the database service
mysqldIs the server side of the database service
MySQL is essentially a network service based on the C (mysql) S (mysqld) mode.

mysql is a network process, and the corresponding mysqld is the backend of this network service

can usenetstat -lntpCheck the port number:

Understanding from the perspective of function

MySQL is a network program that provides us with data access services.
mysqld database service

Database: generally refers to data organized in a specific structure stored on disk or in memory. It is a set of database solutions stored on disk, so the medium for database storage is disk or memory.

Why do we need a database when we can just use files to store data?
There are several disadvantages to saving data in files:

• 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

The essence of a database is a set of solutions for storing data content. Give the database fields or requirements, and the client/database file will give you the results directly.

Usage Examples

1. Creating a database is essentially a directory under Linux
   Establish ahelloworld：create database helloworld
   

---

2. The essence of creating a table in a database is to create the corresponding file under Linux
   usehelloworld：use helloworldAnd create a table

---

Logical storage structure:
select * from student;
Determinant structure

The above work is actually completed by mysqld

---

3. Databases are essentially files, but these files are not operated directly by programmers. Instead, they are operated with the help of database services.

Mainstream databases (relational)

• SQL Sever: A Microsoft product, a favorite of .Net programmers, suitable for medium and large projects.
• Oracle: Oracle products are suitable for large projects and complex business logic, but their concurrency is generally not as good as MySQL.
• MySQL: The most popular database in the world, owned by Oracle, with good concurrency, not suitable for complex business. Mainly used in e-commerce, SNS, forums. Good for simple SQL processing.
• PostgreSQL: A relational database developed by the Department of Computer Science at the University of California, Berkeley. It can be used, modified, and distributed free of charge for private, commercial, or academic research purposes.
• SQLite: It is a lightweight database and a relational database management system that complies with ACID. It is contained in a relatively small C library. It is designed for embedded systems and is currently used in many embedded products. It takes up very little resources. In embedded devices, only a few hundred KB 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.

Relationship between servers, databases, and tables

The so-called installation of a database server is just installing a database management system program on the machine. This management program can manage multiple databases. Generally, developers will create a database for each application.
In order to save the data of entities in the application, multiple tables are generally created in the database to save the data of entities in the program.

MySQL overall architecture

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

MySQL Classification

• DDL [data definition language] Data definition language, used to maintain the structure of stored data
  Representative instructions:create, drop, alter
• DML [data manipulation language] Data manipulation language, used to operate on data
  Representative instructions:insert，delete，update
• DML is divided into a separate DQL, data query language, which represents instructions: select
• DCL [Data Control Language] Data Control Language, mainly responsible for authority management and transactions
  Representative instructions:grant，revoke，commit

Storage Engine

The storage engine is how the database management system stores data, how to create indexes for stored data, and how to update and query data.
The core of MySQL is the plug-in storage engine, which supports multiple storage engines.

View the storage engine: `show engines;

Storage engine comparison:

`