_ Rui_ ao 2022-02-13 07:07:13 阅读数:800
data (Data)
surface
database
Database management system (DBMS)
Database system
First generation database
Second generation database
Third generation database
It's not convenient to write an application
Data redundancy is inevitable
Application dependencies
Concurrent access to files is not supported
Weak connection between data
Difficult to represent data by user view
No safety control function
……
Embryonic stage : file system
Use disk files to store data
Primary stage : First generation database
There's a mesh model 、 Hierarchical model database
Intermediate stage : Second generation database
Relational database and structured query language
Advanced stage : A new generation of databases
“ Relationship —— object ” Type database
Structured data : That is, data with fixed format and limited length . For example, the completed form is structured data , nationality : The People's Republic of China , nation : han , Gender : male , It's called structured data
Unstructured data : More and more unstructured data , It's just an indefinite length 、 Data without fixed format , Such as web pages , Sometimes very long , Sometimes a few words are gone ; For example, voice. , Video is unstructured data
Semi-structured data : such as :XML perhaps HTML The format of the data
A collection of interrelated data
Less data redundancy
Programs and data are independent of each other
Secure data 、 reliable
Ensure the correctness of data to the greatest extent
Data can be used concurrently and ensure consistency at the same time
……
Relational Database Management System, The relational model was originally developed by IBM Edgar, a British computer scientist at the company · Codd (Edgar F. Codd) On 1969 Year description ,1974 year ,IBM Start developing the system R, This is a development RDBMS Prototype research project .
However , The first commercially available RDBMS It's Oracle , On 1979 By relationship software ( Now Oracle ) Release
MySQL( Oracle buys ): MySQL, MariaDB, Percona Server
PostgreSQL: Referred to as pgsql,EnterpriseDB
Oracle( Oracle products )
MSSQL
DB2(IBM The products of the company )
SQL Server( Microsoft products )
Entity
attribute
contact
The collection of all entities and their relationships constitutes a relational database
The storage structure of relational database is two-dimensional table
In every two-dimensional table
Non relational databases are also called NoSQL(Not Only SQL)
Storing data is not based on a relational model , No fixed table format is required
Advantages of non relational database
Common non relational databases :Redis、mongoDB etc.
relational database
The relational database structure is a two-dimensional database table , Each field in the two-dimensional table ( Column ) Used to describe an attribute of an object ,
Every record ( That's ok ) Information used to describe an object ( Complete information ), Where the relational database is written, it is stored in the hard disk
The reading and writing system will be affected IO Limitations or bottlenecks
other
The most typical data structure of relational database is table , A data organization consisting of two-dimensional tables and their connections
advantage :
shortcoming :
Cache acceleration software
Non relational database (NoSQL):
MongoDB 、 Redis( In-memory database / Cache database )K-V Key value pair 、 Similar Memcache,K-V Key value pair
redis-memecache contrast :
The same thing : Store high heat data ( Run at high speed in memory )
Difference :redis It can be persisted , Can store objects
A non relational database is not strictly a database , It should be a collection of data structured storage methods , It can be document or key value equivalence .
advantage :
shortcoming :
yum install , Uncomfortable installation reference architecture
MySQL form
Client program
MyISAM Management tools for storing wins :
Server side program
[[email protected] ~]# yum info mariadb
Installable packages
name :mariadb
framework :x86_64
period :1
edition :5.5.68
Release :1.el7
size :8.8 M
Source :base/7/x86_64
brief introduction : A community developed branch of MySQL
website :http://mariadb.org
agreement : GPLv2 with exceptions and LGPLv2 and BSD
describe : MariaDB is a community developed branch of MySQL.
: MariaDB is a multi-user, multi-threaded SQL database server.
: It is a client/server implementation consisting of a server daemon (mysqld)
: and many different client programs and libraries. The base package
: contains the standard MariaDB/MySQL client programs and generic MySQL files.
MySQL By Sun After the acquisition , Made a transition 6.0 edition , It didn't take long to get offline , Was later Oracle After the acquisition , Finally ushered in a decent 5.6 edition , After is 5.7、8.0 edition . because 6.0 Version number has been used ,7.x The series version is dedicated to NDB Cluster, Therefore, the new version number is from 8.0 Start .
5.5 The version has been eliminated , Not recommended
[[email protected] ~]#yum install mariadb-server -y
[[email protected] ~]#systemctl start mariadb.service
[[email protected] ~]#mysql
[[email protected] ~]#mysql_secure_installation
Use Tsinghua source to install
[[email protected] ~]#tee /etc/yum.repos.d/mysql.repo <<EOF
[mysql]
name=mysql5.7
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-x86_64/
gpgcheck=0
EOF
[[email protected] ~]#yum -y install mysql-community-server
Start and view status
[[email protected] yum.repos.d]# systemctl start mysqld
[[email protected] yum.repos.d]# ss -ntap |grep 3306
[[email protected] yum.repos.d]# mysql -u root -p
mysql command :
mysql [OPTIONS] [database]
Command format
-A, --no-auto-rehash It is forbidden to complete
-u, --user= user name , The default is root
-h, --host= Server host , The default is localhost
-p, --passowrd= User password , It is recommended to use -p, The default is blank password
-P, --port= Server port
-S, --socket= Specify the connection socket File path
-D, --database= Specify the default database
-C, --compress Enable compression
-e "SQL" perform SQL command
-V, --version Display version
-v --verbose Show details
--print-defaults Get the default configuration used by the program
# Default blank password login
mysql -u root -p
mysql>use mysql # Switch database
mysql> select database(); # View the current database
mysql>select user(); # View the current user
mysql>SELECT User,Host,Password FROM user;
mysql>system clear # Clear the screen
mysql> ^DBye #ctrl+d sign out
mysqladmin command
mysqladmin [OPTIONS] command command...
# see mysql Whether the service is normal , If it's normal mysqld is alive
mysqladmin -u user -p password ping
mysqladmin -uroot -p123123 ping
# close mysql service , but mysqladmin The command cannot be turned on
mysqladmin -uroot -pcentos shutdown
# Create database testdb
mysqladmin -uroot -pcentos create testdb
# Delete database testdb
mysqladmin -uroot -pcentos drop testdb
# modify root password
mysqladmin -uroot -pcentos password 'magedu'
# Log scrolling , Generate new file /var/lib/mysql/mariadb-bin.00000N
mysqladmin -uroot -pcentos flush-logs
Introduction of multiple examples
Multiple instances similar to wechat double opening , The port number is similar to the wechat account , Database analogy chat window , Table analogy chat records
MySQL Multi instance is to open multiple different service ports on a server at the same time ( Such as :3306、3307 etc. ), Running multiple simultaneously MySQL Service process , These service processes go through different Socket Listen to different service ports to provide services .
Multiple instances may be MySQL Different versions of , It could be MySQL The same version of
Can effectively use server resources .
When a single server resource is left , We can make full use of the remaining resources to provide more services , And it can realize the logical isolation of resources and save server resources . For example, the company's server resources are tight , But databases need to provide services independently as much as possible , And also need to master-slave replication technology , Multiple instances are the best choice
There is a problem of resources grabbing each other .
such as : When a database instance is highly concurrent or SQL When the query is slow , The whole instance will consume a lot of CPU、 disk I/O And so on , As a result, the quality of service provided by other database instances on the server will also decline , So the specific needs should be determined according to the actual situation .
In the database system ,SQL Statement is case insensitive , Capital letters are recommended
SQL Statements can be written in one or more lines , Default to " ; " ending
Keywords cannot span multiple lines or be abbreviated
Use spaces and TAB Indent to improve the readability of statements
Clause is usually on a separate line , Easy to edit , Improve readability
database 、 surface 、 Indexes 、 View 、 user 、 stored procedure 、 function 、 trigger 、 Event scheduler, etc
Must start with a letter , The following can include letters , Numbers and three special characters (# _ $)
Do not use MySQL Reserved words ,tabble select show databases
CREATE,DROP,ALTER
INSERT,DELETE,UPDATE
software development :CRUD
SELECT
GRANT,REVOKE
COMMIT,ROLLBACK,SAVEPOINT
#DDL: Data Defination Language Data definition language
CREATE,DROP,ALTER
#DML: Data Manipulation Language Data manipulation language
INSERT,DELETE,UPDATE
#DQL:Data Query Language Data query language
SELECT
#DCL:Data Control Language Data control language
GRANT,REVOKE,COMMIT,ROLLBACK
SELECT * #SELECT Clause
FROM products #FROM Clause
WHERE price>666 #WHERE Clause
mysql> help create
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
CREATE DATABASE
CREATE EVENT
CREATE FUNCTION
CREATE FUNCTION UDF
CREATE INDEX
CREATE LOGFILE GROUP
CREATE PROCEDURE
CREATE SERVER
CREATE TABLE
CREATE TABLESPACE
CREATE TRIGGER
CREATE USER
CREATE VIEW
SHOW
SHOW CREATE DATABASE
SHOW CREATE EVENT
SHOW CREATE FUNCTION
SHOW CREATE PROCEDURE
SHOW CREATE TABLE
SHOW CREATE USER
SPATIAL
show charset;
# View supported character sets
Default Latin text
utf8 | UTF-8 Unicode
# Castrated version
utf8mb4 | UTF-8 Unicode
# The real version
Our compilation and installation have been modified
mysql> show databases;
# View the current database
mysql> use mysql;
# Use mysql surface
CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME'
CHARACTER SET 'character set name'
COLLATE 'collate name';
MySQL [email protected]:(none)> create database db1;
Query OK, 1 row affected
Time: 0.002s
MySQL [email protected]:(none)> show create database db1;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| db1 | CREATE DATABASE "db1" /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set
Time: 0.018s
MySQL [email protected]:(none)> create database db2 charset=utf8;
Query OK, 1 row affected
Time: 0.001s
ALTER DATABASE DB_NAME character set utf8;
DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';
MySQL [email protected]:(none)> drop database db1;
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
Query OK, 0 rows affected
Time: 0.005s
MySQL [email protected]:(none)> show databases;
SHOW DATABASES;
MySQL [email protected]:(none)> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bbs |
| db2 |
| db3 |
| kgc |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
9 rows in set
Time: 0.010s
data type :
What does data look like
How much space does data need to be stored
data type
System built-in data type
User defined data types
MySQL**** Support multiple built-in data types
value type
date / Time type
character string ( character ) type
Data type reference link
https://dev.mysql.com/doc/refman/8.0/en/data-types.html
Choosing the right data type is crucial for high performance , The three principles :
Smaller ones are usually better , Try to use the smallest data type that can store data correctly
Simple is good , Simple data type operations usually require less CPU cycle
Try to avoid NULL, Include as NULL The column of , Yes MySQL Harder to optimize
Integer type
tinyint(m) 1 Bytes Range (-128~127)
smallint(m) 2 Bytes Range (-32768~32767)
mediumint(m) 3 Bytes Range (-8388608~8388607)
int(m) 4 Bytes Range (-2147483648~2147483647)
bigint(m) 8 Bytes Range (±9.22*10 Of 18 Power )
The above data types , If you add a modifier unsigned after , Then double the maximum value
Such as :tinyint unsigned The value range of is (0~255)
floating-point **(float and double)****, Approximate value **
float(m,d) Single precision floating point 8 Bit accuracy (4 byte ) m Total number ,d Decimal places , Be careful : The decimal point does not occupy the total number
double(m,d) Double precision floating point 16 Bit accuracy (8 byte ) m Total number ,d Decimal places , Be careful : The decimal point does not occupy the total number
Let's define a field as float(6,3), If you insert a number 123.45678, What's in the actual database is 123.457, But the total number is still in
The actual situation shall prevail , namely 6 position
Fixed-point number
What is stored in the database is the exact value , Save as decimal
Format decimal(m,d) Express most m Digit number , among d Decimals , The decimal point is not included in the length
such as : DECIMAL(6,2) Can save a total of 6 Digit number , At the end of 2 Place is a decimal , The maximum value of the field 9999.99 ( The decimal point is not included in the length )
Parameters m<65 It's the total number ,d<30 And d<m It's a decimal place
MySQL5.0 And later packages numbers into a binary string ( Every time 4 Bytes of storage 9 A digital ).
for example : decimal(18,9) Each side of the decimal point will be stored 9 A digital , Use it all together 9 Bytes : among , Before the decimal point 9 For numbers 4 individual
byte , After the decimal point 9 For numbers 4 Bytes , The decimal point itself accounts for 1 Bytes
Floating point types store the same range of values , Often than decimal Use less space .float Use 4 Byte store .double Occupy
8 Bytes
Because of the extra space and computing overhead , So we should try our best to calculate the decimals exactly
character string (char,varchar,text)
char(n) Fixed length , most 255 Characters , Note that it's not bytes
varchar(n) Variable length , most 65535 Characters
tinytext Variable length , most 255 Characters
text Variable length , most 65535 Characters
mediumtext Variable length , most 2 Of 24 Power -1 Characters
longtext Variable length , most 2 Of 32 Power -1 Characters
BINARY(M) Fixed length , Can store binary or characters , The length is 0-M byte
VARBINARY(M) Variable length , Can store binary or characters , The allowable length is 0-M byte
Built in type :ENUM enumeration , SET aggregate
char and varchar**** Comparison :
Reference resources :https://dev.mysql.com/doc/refman/8.0/en/char.html
CREATE DATABASE school;
USE school;
CREATE TABLE ky15 (id int NOT NULL,name char(10) NOT NULL,score decimal(5,2),passwd char(48) DEFAULT'', PRIMARY KEY (id));
DESC zzz;
for example : decimal(18,9) Each side of the decimal point will be stored 9 A digital , Use it all together 9 Bytes : among , Before the decimal point 9 For numbers 4 individual
byte , After the decimal point 9 For numbers 4 Bytes , The decimal point itself accounts for 1 Bytes
Floating point types store the same range of values , Often than decimal Use less space .float Use 4 Byte store .double Occupy
8 Bytes
Because of the extra space and computing overhead , So we should try our best to calculate the decimals exactly
character string (char,varchar,text)
char(n) Fixed length , most 255 Characters , Note that it's not bytes
varchar(n) Variable length , most 65535 Characters
tinytext Variable length , most 255 Characters
text Variable length , most 65535 Characters
mediumtext Variable length , most 2 Of 24 Power -1 Characters
longtext Variable length , most 2 Of 32 Power -1 Characters
BINARY(M) Fixed length , Can store binary or characters , The length is 0-M byte
VARBINARY(M) Variable length , Can store binary or characters , The allowable length is 0-M byte
Built in type :ENUM enumeration , SET aggregate
char and varchar**** Comparison :
Reference resources :https://dev.mysql.com/doc/refman/8.0/en/char.html
copyright:author[_ Rui_ ao],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/02/202202130707110128.html