One 、MySQL Database first experience

• Basic concepts of database
• The development of database
• Introduction to mainstream database
• MySQL Installation method
• operation MySQL

1. Basic concepts of database

data （Data）

• A symbolic record of things
• Including digital 、 written words 、 graphics 、 Images 、 voice 、 Records, etc
• With “ Record ” Forms are stored in a unified format

surface

• Organize different records together
• Used to store specific data

database

• Set of tables , It's a warehouse for storing data
• A collection of interrelated data stored in a certain organization

Database management system （DBMS）

• Is to achieve effective organization of database resources 、 System software for management and access

Database system

• It's a human computer system , By hardware 、OS、 database 、DBMS、 Application software and database users
• The user can go through DBMS Or the application operates the database

2. The history of database system development

First generation database

• since 20 century 60 s , The first generation database system came out
• It is a database system of hierarchical model and mesh model
• It provides strong support for unified management of shared data

Second generation database

• 20 century 70 s , Second generation database —— Relational databases are beginning to appear
• 20 century 80 s ,IBM The company's relational database system DB2 available , Start Gradually replace hierarchical and mesh models The database of , Become the mainstream of the industry
• up to now , Relational database system still occupies the main position of database application

Third generation database

• since 20 century 80 s , New database systems adapted to different fields are emerging
• Object oriented database system , Practical 、 It has a wide range of adaptability
• 20 century 90 Late S , A variety of database systems support applications together
• Some new elements have been added to mainstream databases
  • for example ：Oracle Supported by “ Relationship - object ” database model

2.1 Disadvantages of file management system

• 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

• ……

2.2 Development stage of database system

1. Embryonic stage ： file system

   Use disk files to store data

2. Primary stage ： First generation database

   There's a mesh model 、 Hierarchical model database

3. Intermediate stage ： Second generation database

   Relational database and structured query language

4. Advanced stage ： A new generation of databases

   “ Relationship —— object ” Type database

3. Database principle

3.1 The era of data

• The amount of data involved is large
• The data does not disappear with the end of the program
• Data is shared by multiple applications
• big data

3.2 Classification of data

• 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

3.3DBMS Database management system

• Database： A database is a collection of data , It exists on the storage medium in a certain organization form
• DBMS： It's the system software that manages the database , It implements the functions of various databases . Is the core of the database system
• DBA： Responsible for database planning 、 Design 、 Coordinate 、 Maintenance and management, etc
• Applications ： A database based application

3.4 Advantages of database management system

1. A collection of interrelated data

2. Less data redundancy

3. Programs and data are independent of each other

4. Secure data 、 reliable

5. Ensure the correctness of data to the greatest extent

6. Data can be used concurrently and ensure consistency at the same time

7. ……

3.5 Basic functions of database management system

1. Data definition
2. Data processing
3. Data security
4. The data backup

3.6 The structure of database system

• Stand alone architecture
• Mainframe / Terminal architecture
• Master-slave architecture （C/S）
• Distributed architecture

3.7RDBMS Relational database

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

3.7.1 Related concepts of relational unified database

• Relationship Relational ： Relationships are two-dimensional tables , among ： The rows in the table 、 Order is not important
• That's ok row： Every line in the table , Also known as a record record
• Column column： Every column in the table , Called attribute , Field , Domain field
• Primary key Primary key：PK , The field used to uniquely identify a record , A table has only one primary key
• Domain domain： Property value range , Such as , Sex can only be ’ male ’ and ’ Woman ’ Two values , Human age can only 0-150

3.7.2 Common relational databases

1. MySQL（ Oracle buys ）: MySQL, MariaDB, Percona Server

2. PostgreSQL: Referred to as pgsql,EnterpriseDB

3. Oracle（ Oracle products ）

   1. For all mainstream platforms
   2. Security 、 perfect , The operation is complicated

4. MSSQL

5. DB2（IBM The products of the company ）

   1. For all mainstream platforms
   2. large 、 Security 、 perfect

6. SQL Server（ Microsoft products ）

   1. oriented Windows operating system
   2. Simple 、 Easy to use

4. relational database

• A relational database system is a database system based on a relational model
• The data structure of relational model uses two-dimensional data table which is easy to understand
• The relational model can be simply “ Entity —— Relationship ”（E-R） It is shown in the figure
• E-R The figure contains the entity （ Data objects ）、 Three elements of relationship and attribute

Entity

• Also known as instances , Corresponding to... That can be distinguished from other objects in the real world “ event ” or “ thing ”
• Such as bank customers 、 Bank accounts, etc

attribute

• A property of an entity , An entity can have multiple properties
• Such as “ Bank customers ” Each entity in the entity set has a name 、 address 、 Phone and other attributes

contact

• The correspondence between entity sets becomes a connection , Also called relationship
• For example, there is a gap between a bank customer and a bank account “ savings ” The relationship between

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

• Each line is called a record , Information used to describe an object
• Each column is called a field , Used to describe an attribute of an object

5. Nonrelational database

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

• Database can be read and written with high concurrency
• Efficient storage and access to massive data
• Database has high scalability and high availability

Common non relational databases ：Redis、mongoDB etc.

6. Relational database and non relational database

The difference between the two

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 ：

1. Easy to maintain ： They all use the table structure , The format is consistent ;
2. Easy to use ：SQL Common language , Can be used for complex queries ;
3. Complex operation ： Support SQL, It can be used for very complex queries between one table and multiple tables .

shortcoming ：

1. The reading and writing performance is poor , Especially the efficient reading and writing of massive data ;
2. Fixed watch structure , Less flexibility ;
3. High concurrent read and write requirements , For traditional relational databases , Hard disk I/O It's a big bottleneck .

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 ：

1. Flexible format ： The format for storing data can be key,value form 、 Document form 、 Picture form and so on , Document form 、
   Picture form and so on , Flexible use , Wide application scenarios , Relational databases only support basic types .
2. Fast ：nosql You can use hard disk or ram as a carrier , The relational database can only use hard disk ;
3. High scalability ;
4. The cost is low ：nosql Database deployment is simple , It's basically open source software .

shortcoming ：

1. Does not provide sql Support , The cost of learning and using is high ;
2. No transactions ;
3. The data structure is relatively complex , It's a little bit less in terms of complex queries .

7. install mysql

yum install , Uncomfortable installation reference architecture

MySQL form

Client program

• mysql： Interactive CLI Tools
• mysqladmin： be based on MySQL Agreement Management MySQLd
• mysqlimport： Data import tool

MyISAM Management tools for storing wins ：

• myisamchk： Check MyISAM library
• myisampack： pack MyISAM surface , read-only

Server side program

• mysqld_safe
• mysqld
• mysqld_multi Multiple instances , example ：mysqld_multi

[[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

yum install 5.7 edition

[[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

8. Multiple instances

Introduction of multiple examples

• What is database multi instance

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

• The benefits of multiple instances

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

• The disadvantages of multiple examples

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 .

9. Basic operation

SQL classification

• database ：database
• surface ：table, That's ok ：row Column ：column
• Indexes ：index
• View ：view
• stored procedure ：procedure
• Storage function ：function
• trigger ：trigger
• event scheduler ：event scheduler, Task plan
• user ：user
• jurisdiction ：privilege

SQL language norm

1. In the database system ,SQL Statement is case insensitive , Capital letters are recommended

2. SQL Statements can be written in one or more lines , Default to " ; " ending

3. Keywords cannot span multiple lines or be abbreviated

4. Use spaces and TAB Indent to improve the readability of statements

5. Clause is usually on a separate line , Easy to edit , Improve readability

Database objects and naming

Components of the database ( object )：

database 、 surface 、 Indexes 、 View 、 user 、 stored procedure 、 function 、 trigger 、 Event scheduler, etc

Naming rules ：

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

SQL Sentence classification

• DDL: Data Defination Language Data definition language

CREATE,DROP,ALTER

• DML: Data Manipulation Language Data manipulation language

INSERT,DELETE,UPDATE

software development ：CRUD

• DQL：Data Query Language Data query language

SELECT

• DCL：Data Control Language Data control language

GRANT,REVOKE

• TCL：Transaction Control Language Transaction control language

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 

10. View help information

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

11. View the supported character sets

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

12. Management database

1. Create database

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

2. modify the database

ALTER DATABASE DB_NAME character set utf8;

3. Delete database

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;

4. Check the database list

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

5. data type

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 ：

1. Smaller ones are usually better , Try to use the smallest data type that can store data correctly

2. Simple is good , Simple data type operations usually require less CPU cycle

3. 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