21. Basic usage of MariaDB

1. Three data management models ：
hierarchical model ： For example, one master and many slaves can be compared to one master process with many children
Network structure ： The Lord and from much , You can trace multiple masters from one up
relational model ： It can be regarded as a two-dimensional table composed of horizontal and vertical axes
1.1 data classification
Structured data 、 Semi-structured data 、 Unstructured data
**2. Relational database （RDBMS）：** A database that uses a relational model to organize data , It stores data in the form of rows and columns , To make it easy for users to understand , This series of rows and columns in a relational database is called a table , A set of tables makes up the database .

3.RDBMS Basic concepts of design paradigm ：

4. Database structure composition

4.1、 Business

4.2 、 Responsible for the interaction with the underlying disk Storage engine 、 A system for managing data The core 、 And the corresponding database driver is required SQL（SQL drive ）
Storage engine ： The database table information can be transformed into data that can be stored on disk , And can realize the related constraint function of the table , Also known as Table type
**4.3、SQL：** It is a database protocol, and its manifestation is SQL The driver , Can explain SQL Language and provide command line interface

5. Basic concepts


6、mariadb Compare with mysql New features

7、： Three installation methods

centos7 On can yum install mariadb, Directly provided package

centos6 You need to compile and install mariadb
8.centos6 Compile and install mariadb
（1） establish mysql Users and groups
（2） Download the decompression package , Be sure to unzip to /usr/local Directory and create a connection

（3） jurisdiction
cd /usr/local/mysql/
chown -R root:mysql ./*
（4） The data is stored in the installation directory by default data Next , For more security and capacity expansion, it's best to store it separately on a disk ：
Create a disk partition and create a logical volume and set up boot auto mount
establish data Directory and set mysql Belong to the main genus group
** Add ：** You can do it again centos6 To create a xfs Format the partition , First installation xfs modular
yum install xfsprogs
modprobe xfs In process module
see
modinfo xfs
（5） Execute the installation configuration method Sure –help View related options , There must be more mysql Execute the initialization script under the directory ：

（6） Copy the template to generate the execution script and add it to the startup

(7） Copy the generated configuration file , Copy the corresponding files according to the memory size used
Add ：mariadb The last order of reading configuration files will take effect ：



(8)、service mysqld start Start the test
(9)、 Related internal scripts

Add ：

9. Use of command line client program

10 data type

List data types ：
HELP ‘Data Type’

11.SQL grammar

12. Table grammar

Example ：

Delete table ：DROP TABLE [IF EXIST] ‘tbl_name’

Modify table ：

Add ：
change： Modify field name
modify： Modify field properties
alter： Delete the default value of a field

Example ：

Look at the index ：SHOW INDEXES FROM ‘tbl_name’
Be careful ： The key must be an index , An index is not necessarily a key
Delete index ALTER TABLE students DROP age ;
Example ：

13. Indexes
usage ：

Example ：

14.DML:INSERT.DELETE,SELECT,UPDATE usage

Insert examples ：

Query examples ：
SELECT * FORM students WHERE sid<3;
SELECT * FORM students WHERE gender=‘m’;
SELECT * FORM students WHERE gender IS NULL
SELECT * FORM students WHERE gender IS NOT NULL
SELECT * FORM students WHERE ORDER BY name;
SELECT * FORM students WHERE ORDER BY name DESC; ( null )
SELECT * FORM students WHERE ORDER BY name DESC LIMIT 2;（ Only the first two ）
SELECT * FORM students WHERE ORDER BY name DESC LIMIT 1,2; （ Skip the first and show the first two ）
=》SELECT * FORM students WHERE sid>=2 and sid <=4;
=》 Same as above, another expression ：SELECT * FORM students WHERE sid BETWEEN 2 AND 4;
SELECT * FORM students WHERE name LIKE ‘Z%’ ; (name Field Z Beginning line )
Regular expression based query , But try not to , Because of the need for regular expression engine, the query efficiency is slow
SELECT * FORM students WHERE name RLIKE ‘.u.’; ( Inquire about name There is... In the field u Lines of letters )
The alias sample ：

Delete example ：
DELETE FORM student WHERE sid=3;
Update example :
UPDATE student SET gender=‘f’ WHERE sid=4;

15.DCL

Authorization example ：

View permission example ：

View the current user authorization
SHOW GRANTS FOR CURRENT_USER;
Delete permission ： REVOKE DELETE ON testdb.* FROM ‘testuser’@’%’;