MySQL review record: basic knowledge of database and MySQL

1, Database

(1) Principle of database

If data is defined as a symbolic record describing things, the database is an organized, shareable and large data set stored in the computer for a long time,

The data in the database is organized, described and stored according to a certain data model. The role of organizing, describing, acquiring and maintaining data according to the data model is a layer of software between users and operating system, that is, database management system.

The database system is composed of database, database management system, application program and database administrator.

(2) Data model

Data model is the abstraction of actual data features in computer.

This abstraction needs to meet:

  1. More realistic simulation of things in the real world.
  2. Easy to understand.
  3. It is easy to realize in computer.

In fact, it is difficult to meet all the three at the same time. Therefore, different data models are adopted in the database system for different use objects and purposes:

  1. Conceptual model: in the database design stage, data and information are modeled from the perspective of users.
  2. Logical model and physical model: they are similar to the concepts of logical structure and storage structure of data in data structure.

This series of abstract activities from the real world to the information world and then to the machine world is the core of database system technology.

1. Conceptual model

Conceptual model is an abstraction from the real world to the information world.

First, let's take a look at some concepts in the information world:

  • entity: things that exist objectively and can be distinguished from each other. Such as students, courses, a course selection of students, the relationship between students and courses, etc.
  • Attribute: an attribute of an entity. For example, the student entity can be composed of student number, name, gender, date of birth, Department, enrollment time and other attributes.
  • key: the attribute set that uniquely identifies the entity. For example, the student number is the code of the student entity.
  • entity type: entity of the same kind abstracted from the entity name and its attribute name collection. For example, students (student number, name, gender, date of birth, department and time of enrollment) are a real body.
  • entity set: a collection of entities of the same type. For example, all students are a set of entities.
  • Relationship: the relationship between different entity sets. There are many types of relationships between entities, such as one-to-one, one to many and many to many.

One representation method of conceptual model is entity relation method, specifically using E-R diagram To describe the conceptual model of the real world,

2. Logical model

The data model usually consists of three parts: data structure, data operation and data integrity constraints:

  • The data structure describes the constituent objects of the database and the relationships between objects.
  • Data operation refers to the collection of operations allowed to be performed on the instances (values) of various objects (types) in the database, including operations and relevant operation rules.
  • Data integrity constraints are a set of integrity rules. Integrity rules are the constraints and dependency rules of data and its relations in a given data model, which are used to limit the database state and state changes in line with the data model, so as to ensure the correctness, effectiveness and compatibility of data.

The main logical data models in the database field are:

  • Hierarchical model
  • Network model
  • Relational model
  • Object oriented data model
  • Object relational data model
  • Semi structure data model

Relational model is the most important data model. Relational database systems, such as MySQL, use the relational model as the data organization,.

From the user's point of view, the relationship model consists of a set of relationships. The data structure of each relationship is a normalized two-dimensional table:

Student numberfull nameAgeGenderDepartment namegrade
2013004Xiao Ming Wang19femaleSociology2013
2013008Zhang Wenbin18femalelaw2013
..................
  • Entities and relationships between entities are represented by relationships. For example, the tutor entity, the graduate student entity, and the X-to-X relationship between tutors and graduate students can be represented by a relationship.

Because the relational model is based on mathematical theories such as relational algebra and relational calculus, the so-called set-at-a-time method can be used to realize the data operation of the relational model, which provides a complete set of advanced relational operations to support various operations on the database, mainly including querying, inserting, deleting and updating data.

These operations must meet certain constraints on the relationship, that is, the integrity constraint of the relationship:

2, MySQL architecture and storage engine

(1) MySQL architecture

First of all, MySQL is a network database system. The most typical architecture in network applications is C/S architecture and B/S architecture . MySQL on Adopt C/S architecture:


Overall, MySQL can be divided into three layers:

  • Application connection layer: mainly Client Connectors, which are mainly responsible for connection management, authorization and authentication, security, etc.
  • MySQL service layer: responsible for specific query processing, transaction management, storage management, recovery management, and other additional functions.
  • Storage engine layer: responsible for storing and extracting data through the specified storage engine.
  • Physical storage layer: data and files actually stored in storage media.

(1) Client Connectors

Provide standard based drivers for JDBC, python, etc., so that developers can build database applications in their own language of choice.

Whenever a client sends a connection request through the client connector, the server will create a thread to maintain the communication service.

reference resources:
Mysql view the number of connections (total number of connections, active number, maximum concurrent number)

(2) Connection Pool

In order to maintain database access performance under the requirements of infinite client summation and limited number of database client connections, a thread mechanism for managing MySQL client connection tasks is needed, which is database connection pool.

reference resources:
Database connection pool (1),MySql database connection pool topic

(3) Manage services & Utilities

System management and control tools, such as backup and recovery, MySQL replication, cluster, etc.

(4) SQL Interface

Accept the user's SQL command and return the query results required by the user.

(5) Query Parser

When SQL commands are passed to the parser, they will be verified and parsed by the parser (permissions, syntax structure).

(6) Query Optimizer

SQL statements use the query optimizer to optimize the query before the query.

(6) Caches

If there are hit query results in the query cache, the query statement can directly fetch data from the query cache.

reference resources:
Figure 1 MySQL architecture,MySQL architecture and storage engine

(2) Storage engine

1. What is MySQL storage engine

MySQL belongs to relational database, and the data storage of relational database is in the form of data table. MySQL storage engine is the essential method to complete the technology of table creation, data storage, retrieval and update.

  • In relational database, database table is a set of two-dimensional arrays, which is used to store data and operate the logical structure of data. It consists of vertical columns and horizontal rows. Rows are called records and are the units that organize data; Columns are called fields. Each column represents an attribute of a record and has corresponding description information, such as data type, data width, etc.

Plug in storage engine selection is one of the most important features that distinguish MySQL from other databases. Starting from MySQL database version 5.5.8, InnoDB is used by default.

Due to the open source feature of MySQL database, users can define the storage engine interface according to MySQL Writing a custom storage engine.

2. Common storage engines

(1)InnoDB

InnoDB storage engine It supports transaction, and its design goal is mainly for the application of online transaction processing (OLTP). It is characterized by row lock design and supports foreign keys.

InnoDB provides good transaction management, crash repair and concurrency control capabilities, but the reading and writing efficiency is relatively poor and the data space is relatively large.

(2)MyISAM

MyISAM storage engine It does not support transaction and table lock design, and supports full-text indexing. It is mainly oriented to online analytical processing (OLAP) database applications.

MyISAM provides good data table read performance, but relatively poor write performance.

(3)MEMORY

MEMORY storage engine Save the table structure in the disk file, and store the contents of the table in memory. If the database restarts or crashes, the data in the table will disappear. It is very suitable for temporary tables used to store temporary data and latitude tables in data warehouse.

Although the MEMORY storage engine is very fast, there are still some restrictions on its use. For example, it only supports table locks, poor concurrency performance, and does not support TEXT and BLOB column types.

3. How to select a storage engine

Each storage engine has its own advantages, and choosing the right one is the most important.

4. Set the storage engine of the table

1. Check which storage engines the database supports:

mysql> SHOW ENGINES;	# Query supported storage engines
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

2. View the default storage engine used by the database:

mysql> SHOW VARIABLES LIKE '%storage_engine%';
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| default_storage_engine          | InnoDB    |
| default_tmp_storage_engine      | InnoDB    |
| disabled_storage_engines        |           |
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+

3. Specify the storage engine when creating a table:

mysql> CREATE TABLE t1(...) ENGINE=MyISAM;

4. View the storage engine used by a table:

mysql> SHOW CREATE TABLE t1;

5. Modify the storage engine of a table:

mysql> ALTER TABLE t1 ENGINE=InnoDB;
  • Or edit the default storage engine parameter in the configuration file and restart MySQL.

3, SQL language and basic operation of MySQL database

(1) SQL language

Structured query language (SQL) is not only the standard language of relational database, but also a general and powerful relational database language.

SQL statements can be divided into the following three categories:

  • DDL (Data Definition Languages) statements: data definition languages. These statements define the definitions of different data segments, databases, tables, columns, indexes and other database objects. Common statement keywords mainly include create, drop, alter, etc.
  • DML (Data Manipulation Language) statement: data manipulation statement, which is used to add, delete, update and query database records and check data integrity. Common statement keywords mainly include insert, delete, udpate and select.
  • DCL (Data Control Language) statement: data control statement, which is used to control the direct permission and access level of different data segments. These statements define the access rights and security levels of databases, tables, fields, users. The main statement keywords include grant, revoke, etc

(2) Create and view existing databases

CREATE DATABASE dbname; # Create database
SHOW DATABASES; # View existing databases

  • Do not duplicate the database name, do not use keywords, and it is best to use lowercase names.
mysql> CREATE DATABASE example;

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| example            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
  • mysql stores the user permission information of the system.
  • information_schema mainly stores some database object information in the system. For example, user table information, column information, permission information, character set information, partition information, etc.
  • performance_schema is mainly used to collect database server performance parameters.
  • sys mainly stores the metadata information of the system, which provides more intuitive data for convenient query of important information and performance optimization.

(3) Select and delete the specified database

USE dbname; # Select database
DROP DATABASE dbname; # Delete database

mysql> USE example;
Database changed	# Indicates that the database was selected successfully
mysql> DROP DATABASE example;
Query OK, 0 rows affected (0.22 sec)	# Indicates that the database was deleted successfully
mysql> SHOW DATABASES;	# Delete and check again
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

4, Use help

You can use the help documents in MySQL to quickly find various syntax definitions:

mysql> help;	# View command line commands

For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
notee     (\t) Don't write into outfile.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
resetconnection(\x) Clean session context.
query_attributes Sets string parameters (name1 value1 name2 value2 ...) for the next query to pick up.

For server side help, type 'help contents'

mysql> help contents	# View the classification of all available for query
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
   Account Management
   Administration
   Components
   Compound Statements
   Contents
   Data Definition
   Data Manipulation
   Data Types
   Functions
   Geographic Features
   Help Metadata
   Language Structure
   Plugins
   Storage Engines
   Table Maintenance
   Transactions
   User-Defined Functions
   Utility

mysql> help SHOW	# View the usage of the SHOW command
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:

SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW RELAYLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW {REPLICAS | SLAVE HOSTS}
SHOW {REPLICA | SLAVE} STATUS [FOR CHANNEL channel]
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]

like_or_where: {
    LIKE 'pattern'
  | WHERE expr
}

If the syntax for a given SHOW statement includes a LIKE 'pattern'
part, 'pattern' is a string that can contain the SQL % and _ wildcard
characters. The pattern is useful for restricting statement output to
matching values.

Several SHOW statements also accept a WHERE clause that provides more
flexibility in specifying which rows to display. See
https://dev.mysql.com/doc/refman/8.0/en/extended-show.html.

URL: https://dev.mysql.com/doc/refman/8.0/en/show.html

mysql> help CREATE DATABASE		# View the usage of the CREATE DATABASE command
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_option] ...

create_option: [DEFAULT] {
    CHARACTER SET [=] charset_name
  | COLLATE [=] collation_name
  | ENCRYPTION [=] {'Y' | 'N'}
}

CREATE DATABASE creates a database with the given name. To use this
statement, you need the CREATE privilege for the database. CREATE
SCHEMA is a synonym for CREATE DATABASE.

URL: https://dev.mysql.com/doc/refman/8.0/en/create-database.html

Tags: Database MySQL

Posted on Mon, 27 Sep 2021 14:34:03 -0400 by impulse()