Shulou(Shulou.com)06/01 Report--

MariaDB or MySQL:

Imprecise description:

Data is stored in the form of a data stream on the hard disk. When reading a file, the file is first loaded into memory, and then the required fields are found in memory. When the file is very large, the file cannot be placed in memory. And searching for a field is also quite a waste of time, so the database was born.

The database is stored in a specific format (formatted or semi-formatted data), and then you can set up an INDEX (index) to find and locate a certain data (the index can be divided into multiple levels). Only the index information can be loaded in memory, and the data corresponding to the index can be found on disk, which greatly saves time.

History of development:

Hierarchical model-- > mesh model-- > (Codd) relational model

DBMS database management system-- > RDBMS relational database management system

RDBMS:

Paradigm: the first paradigm, the second paradigm, the third paradigm

Table: row (row), column (column)

Relational operation:

Select: pick out the data that meets the criteria

Projection: picking out specified fields

Database concepts: tables, indexes, views (virtual tables), SQL, stored procedures, stored functions, triggers, event schedulers

DDL

DML

Constraints:

Primary key constraints: unique, non-empty; there can be only one table; defined to uniquely represent the contents of a row of data in the entire table (a field or a combination of fields)

Unique key constraint: unique, multiple can exist

Foreign key constraints: reference constraints; there is a constraint relationship between tables. There is a primary key on the referenced table, and a field on the referenced table must be data that exists on the referenced table.

Checking constraints: the scope of data defined by check;

Three-tier model:

Physical layer data is stored on hardware as a data stream at the file system level (system engineer)

The logic layer converts the data flow into (DBA) through a specific structure.

Visual layer visual text (table) (programmer)

Achieve:

Oracle, DB2, Sybase, Infomix, SQL Server

MySQL, MariaDB, PostgreSQL, SQLite

MySQL:

5.1-> 5.5-- > 5.6-- > 5.7

MariaDB:5.5.x-- > 10.x

Properties:

Plug-in storage engine

Single-process multithreading (32-bit systems can only use 3G memory)

Install MySQL:

OS Vendor:rpm

MySQL:

Source code:cmake (compiler)

Binary package: general binary package

I686, x86_64

Glibc VERSION (version of glibc required)

Prepackage:rpm, deb

Os, arch

Configuration file:

Read multiple configuration files in multiple places and proceed in the specified order

# my_print_defaults to view the order in which the configuration takes effect

Default options are read from the following files in the given order:

/ etc/mysql/my.cnf / etc/my.cnf ~ / .my.cnf

When the same parameter appears in different configuration files and has different values, later reading will be the final effective value.

Modify the default read configuration file (mysqld_safe command):

-- defaults-file=file_name

Load another file in addition to the default configuration file that is read:

-- defaults-extra-file=path

Configuration file format: ini-style configuration file that provides configuration information for various applications of mysql:

Configuration segment:

[mysqld]

[mysqld_safe] Thread-safe MySQL

[mysqld_multi] multiple MySQL running on one host

[server]

[mysql] client configuration

[mysqldump] backup client program

[client]

...

PARAMETER:

Some distributions of innodb_file_per_table are general-purpose and some are only one of them

Innodb-file-per-table

Program file:

Server programs: mysqld_safe, mysqld_multi

Client programs: mysql, mysqldump, mysqladmin can connect to mysql through mysql protocol to initiate a request and obtain the corresponding

Tool program: myisampack,...

Yum installation of mysql initializes the database automatically after startup, but does not install in binary format.

Mysql-- > mysql protocol-- > mysqld

Mysql: interactive CLI tool

Mysql [options] db_name

Common options:

-- host=host_name,-h host_name: server address

-- user=user_name,-u user_name: user name

-- password [= password],-p [password]: user password

-- port=port_num,-P port_num: server port

-- protocol= {TCP | SOCKET | PIPE | MEMORY}:

Local communication: the request is based on the local loopback address and will be based on the local communication protocol

Linux:SOCKET

Windows:PIPE,MEMORY

Non-local communication: requests made using non-local loopback addresses; TCP protocol

-- socket=path,-S path

-- database=db_name,-D db_name:

-- compress,-C: data compression and transmission

-- execute=statement,-e statement: execute SQL statements in non-interactive mode

-- vertical,-E: vertical display of query results

Help for using the mysql command:

# man mysql

# mysql-help-verbose

The sql script runs:

Mysql [options] [DATABASE] < / PATH/FROM/SOME_SQL_SCRIPT

Mysqld server programs: how work characteristics are defined

Command line options

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.