MARIADB Basic Explanation

Database structure Model Classification 1, hierarchical Model 2, mesh Model 3, relational model of the relationship model components

• Two-dimensional relationship
  • Table
    • Row
    • Column
  • Index
    • Index
  • View
    • View (contains only fixed fields, not other fields)

Common components of a relational database:

• 数据库: Database
• 表: Table
  • 行: Row
  • 列: Column
• 索引: Index
• 视图: View
• 用户: User
• 权限: Privilege
• 存储过程: procedure
• 存储函数: function
• 触发器: Trigger
• 事件调度器: Event Schedule

Part of the DBA

• SQL engine
  • Analyzer
  • Plan Actuator
  • Optimizer
  • Operation Solver
• Storage Engine
  • File and Storage interfaces
  • Buffer Manager
  • Disk space Manager
  • Recovery Manager
  • Transaction manager
  • Lock Manager

SQL Interface (structured query Language)

Similar to the OS Shell interface, the operation of the database data interface, but also provides the programming function

• Standard for SQL interface language (defined by the ANSL organization)
  • SQL86
  • SQL89
  • SQL92
  • SQL99
  • SQL03
• Language classification for SQL interfaces
  • DDL: (Data Defined Language)
    • Create
    • Alter
    • Drop
  • DML: (Data manapulating Language)
    • Insert
    • Delete
    • Update
    • Select
  • DCL: (Data Control Language)
    • Grant
    • Revoke

Whether the DB supports transactional test criteria

• ACID

  • A: atomicity--indivisible whole
  • C: Consistency--data changes are consistent
  • I: Isolation--transactions are isolated from each other
  • D: Persistent--as long as a transaction is complete, it is persisted.
• A transaction is to organize multiple operations as a whole, or to execute them all, without executing them all. Its implementation mechanism is:
  • Rollback mechanism
  • Transaction mechanism

The basic concept of RDBMS design paradigm

When designing the relational database, we should design a reasonable relational database to conform to different specifications, and the different norm requirements are called different paradigms, and the higher paradigm database redundancy is lower. There are currently six paradigms for relational databases: the first paradigm (1NF), the second paradigm (2NF), the third paradigm (3NF), the Badesco paradigm (BCNF), the fourth paradigm (4NF), and the fifth normal (5NF, also known as the perfect paradigm). The paradigm that satisfies the minimum requirements is the first paradigm (1NF). The second paradigm (2NF) is further satisfied on the basis of the first paradigm, and the rest of the paradigms are referred to by analogy. In general, the database only needs to meet the third normal form (3NF) on the line.

• 1) First paradigm (1NF)
  • The so-called First paradigm (1NF) refers to a specification requirement for domain additions in a relational model, where all domains should be atomic, that is, each column of a database table is an indivisible atomic data item, not a collection, an array, a record, or a non-atomic data item. That is, when an attribute in an entity has multiple values, it must be split into different properties. Each domain value in a table that conforms to the first normal form (1NF) can only be a property of an entity or part of an attribute. In short, the first paradigm is a non-repeating domain and cannot be analyzed
• 2) Second normal form (2NF)
  • The second paradigm (2NF) is established on the basis of the first paradigm (1NF), i.e. satisfying the second normal form (2NF) must first satisfy the first paradigm (1NF). The second paradigm (2NF) requires that each instance or record in a database table must be divided by a unique region, that is, two rows cannot be the same. Select a property or group of attributes that can differentiate each entity as a unique identifier for the entity.
• 3) Third paradigm (3NF)
  • The third paradigm (3NF) is a subset of the second paradigm (2NF), i.e. satisfying the third normal form (3NF) must satisfy the second normal form (2NF). In short, the third paradigm (3NF) requires that a relationship not contain non-primary key information already contained in other relationships. In short, there is no dependency on a non-primary attribute, and the table cannot have the same field as the child table.

Operating model of the database:

The database is the C/s architecture, as follows:

• S:server, listening to sockets, receiving and processing application requests from clients
• C:client
  • Program Interface
    • Cli
    • Gui
  • Application Programming Interface
    • Odbc:open Database Connection (Open Data interconnect)

MySQL's working model

• Single-Process multithreading
• User connections are implemented by threading
• A thread pool can define concurrent connections for MySQL
• To process a user connection called a connection thread
• Data dictionary: Metadata information for all data, relying on MySQL library to store

Basic concepts of the database

• 1) 约束 : Constraint
  • 主键: A combination of one or more fields in which the data must be uniquely identified in this table and the data must be provided, i.e.NOT NULL
  • 唯一键: A combination of one or more fields in which the data must be uniquely identified in this table and allowed to benull
  • 外键: A field in one table can be populated with data depending on the data already in the primary key in another table.
  • 检查性约束: Expression constraints, depending on the requirements of an expression
• 2) 索引 : Copies data from one or more fields in a table to save one copy, and these fields need to be stored in a specific order, common index types:
  • 树形索引(mariadb default to Btree index)
  • bash索引The index facilitates read requests, but cannot write requests
• 3)关系运算
  • 选择: Select rows that match the criteria (part)
  • 投影: Select the required fields
  • 连接: Associating multiple tables together
    • Internal connection
    • External connection
      • Left outer connection
      • Right outer connection
    • Self-connect
• 4) 数据抽象 :
  • 物理层: Determines the storage format of the data, that is, how the RDBMS organizes files on disk
  • 逻辑层: Describes what data the DB stores and what relationships exist between the data
  • 视图层: Describes some of the data in DB
• 5) Classification of relational models
  • 关系模型
  • 实体关系模型
  • 基于对象的关系模型
  • 半结构化的关系模型
    • XML format is a kind of semi-structured data

Common RDBMS databases

• Mysql
• MariaDB
• PostgreSQL (Pgsql)
• Oracle
• MSSQL

=============

Characteristics of MARIADB

• Plug-in storage engine (note: The storage engine is also called " 表类型 ")
  • MYISAM-Aria
    • Transaction not supported
  • INNODB-XtraDB
    • Support Transactions
• Many extensions and new features
• More Test components are available
• Truly Open source

Installation of MARIADB

Common binary format installation process A. Prepare data Catalog Take/mydata/data as an example; B.                    Configure MARIADB # Groupadd-r-G 306 MySQL # useradd-r-G 306-u 306 MySQL # tar XF mariadb-version.tar.xz-c/usr/local # ln-sv mariadb-version MySQL # Cd/usr/local/mysql # Chown-r Root:mysql./* # scripts/mysql_install_db--datadir=/ Mydata/data--user=mysql # CP SUPPER-FILES/MYSQL.SERVER/ETC/RC.D/INIT.D/MYSQLD # CH Kconfig--add MYSQLDC. Prepare configuration file Configuration format: The class INI format, for each program through a single configuration file to provide configuration information; [Prog_name] can be installed in binary format, configuration file Lookup order:/etc/my.cnf--/etc/mysql/my.cnf--- Default-extra-file=/path/to/conf_file---~/.my.cnfos provides MARIADB RPM package installation configuration file Lookup Order:/etc/mysql/my.cnf--/etc/                    MY.CNF-----default-extra=/path/to/conf_file--and ~/my.cnf above are the last to take effect. # mkdir/etc/mysql # CP support-files/my-large.cnf/etc/mysql/my.cnf Add three optionsEntry: [mysqld] DataDir =/mydata/data Innodb_file_per_ta ble = on skip_name_resolve = On

Composition of the MARIDB program

• Client
  • mysql: CLI Interactive client program
  • mysqldump: Backup tool
  • mysqladmin: Administrative Tools
  • mysqlbinlog: View binary Log Tools
• Server
  • mysqld: Server-side process
  • mysqld_safe: Server-side process, which is also running this process by default
  • mysqld_multi: Server-side process, multi-instance
  • mysql_upgrade: Upgrade Tool

Two types of socket addresses for server monitoring

• ip socket
  • Monitor in 3306/TCP, support remote communication
• unix socket
  • Listen on the sock file (/tmp/mysql.sock,/var/lib/mysql/mysql.sock), only support local communication, communication host for localhost,127.0.0.1 is based on UNIX socket file communication

Command line interactive client program---mysql tool

• options
  • -uUSERNAME: User name, default is root
  • -hHOST: Server host, localhost by default
  • -pPASSWD: User's password
  • dDB_NAME: After connecting to the server, indicate the default database
  • -e ‘SCRIPT‘: Connect to MySQL to run a command, exit directly, and return the result
    ````
    Mysql-uroot-h127.0.0.1-pmagedu-e ' show databases; '

注意: mysql的用户帐号由两部分组成,‘username‘@‘hostname‘,其中host用于限制此用户可通过哪些主机连接当前的MSYQL服务器

• 支持通配符:

  • %: matches any character of any length

    172.16.%.%

  • _: Matches any single character
• 内置命令
  • \u DB_NAME: Set which library is the default database
  • \q: Exit
  • \d CHAR: Sets a new statement terminator
  • \g: statement Universal end Tag
  • \G: statement end tag, but displayed in portrait mode
  • \s: Returns the connection status between the client and the server
  • \c: Cancel Command Run

通过mysql协议发往服务器执行并取回结果,每个命令都必须有结束符,默认为";",示例如下:for i in {1..100};do AGE=$[$RANDOM%100];mysql -uroot -pM8T9cw -e "insert mydb.student(id,name,age) value ($i,\"stu$i\",$AGE);"; done

• 获取命令帮助
  • help

data types in SQL

• 字符型
  • Fixed-length character type: (up to 255 characters)
    • CHAR(#): Character Case insensitive
    • BINARY(#): Distinguishing character case
  • Variable long character type:
    • VARCHAR(#): does not differentiate character case, need to add 1 to character length, up to 65,536 characters
    • VARBINARY(#): Case-sensitive, requires a character length plus 1, up to 65,536 characters
    • TEXT: Can save text (2^32) characters, only plain text, case-insensitive
      • TINYTEXT
      • TEXT
      • MEDIUMTEXT
      • LONGTEXT
    • BLOB: Text (2^32) characters that can store pictures
• 内置类型
  • SET: Collection
  • ENUM: Enumeration
• 数值型
  • 精确数值型
    • INT
      • TINYINT: One byte
      • SMALLINT: two bytes
      • MEDINUMINT: three bytes
      • INT: four bytes
      • BIGINT: eight bytes
  • Approximate data type
    • FLOAT: Single Precision
    • DOBULE: Double Precision
• 日期时间型
  • DATE: Date Type
  • TIME: Time Type
  • DATETIME: Date-Time type
  • TIMESTAMP: Timestamp (number of seconds elapsed from past to present)
  • YEAR(2): 2-digit number of years
  • YEAR(4): 4-digit number of years
• 数据类型修饰符
  • All types are available:
    • NOT NULL: Non-empty
    • DEFAULT value: Default Value
  • Numerical type applicable
    • AUTO_INCREMENT: Self-growth
    • UNSIGNED: unsigned, typically used after int, to be decorated as a positive integer
  • Field modifiers
    • PRIMARY KEY: Primary Key definition
    • UNIQUE KEY: Unique key definition

==========

Database Operations Language Database operations

• 使用格式

create database | schema [if not exists]‘DB_NAME‘;drop database | schema ‘DB_NAME‘;

• View Database
  • show databases;
• Create a database
  • create database mydb;
• Deleting a database
  • drop database mydb;
• To view all supported character sets
  • show character set;
• View all supported collations
  • show collation;
• Modify the default character set for a database
  • alter database testdb character set utf32;
• Modify the default character collation for a database
  • alter database testdb collate utf32_sinhala_ci;

Table Operations

• 使用格式

create table [if not exists] tb_name (col1 datatype 修饰符, col2 datatype 修饰符) engine=‘ ‘

• View all engines (supported)
  • show engines
• To view tables in all databases
  • show tables;
  • show tables from mysql;
• View table Structure
  • desc students;
• Create a table
  • create table students(id int unsigned not null primary key, name varchar(30) not null, age tinyint unsigned not null,gender enum(‘F‘,‘M‘))
• Add a field to a table
  • alter table students add second_name char(30);
• To remove a field from a table
  • alter table students drop second_name;
• To add a primary key to a table
  • alter table students2 add primary key (id);
• To delete a primary key from a table
  • alter table students2 drop primary key;
• To add an index to a table
  • alter table students2 add index name (name);
• To delete an index from a table
  • alter table students2 drop index name;
  • drop index name on students2;
• To add a unique key to a table
  • alter table students drop second_name;
• To remove a unique key from a table
  • alter table students drop index name;

• Modify field literal properties

  • alter table students modify name char(20);
  • alter table students2 change name new_name char(20) after id;

  • alter table students2 change name new_name char(20);

    modify : 只可以修改字段定义的属性change : 可以修改字段名称和定义的字段属性

• View table Status
  • show table status like ‘students2‘\G
• Modify the Table engine
  • alter table students2 engine[=]myisam;
• View field descriptions for a table
  • desc students;
• View all tables in a library
  • show tables
  • show tables from mysql;
• Delete a table
  • drop table students2;

Inserting data into a table

• insert weizi value(1,‘zhen‘,30,‘F‘);
• insert weizi values(3,‘wei‘,30,‘F‘),(4,‘ping‘,31,‘F‘);
• insert weizi (id,name) value (5,"weizi");

Delete data from a table

• delete from weizi where name="zhen";
• delete from weizi where age is null;
• delete from weizi where age > 30;
• delete from weizi where id >=50 and age <=20;
• delete from weizi order by age asc limit 5;

Modify field values in a table

• update weizi set age=35 where id=3 and age=30;
• update weizi set age=age-5 where age=35;
• update weizi age=age-age;
• update weizi set age=age-5 where order by id desc limit 10;
• update weizi set age=age-15 where name not like ‘stu%‘;

Select Action Statement

• Use formatting

select col1,col2,....from tb1_name [where clause] [order y ‘col_name‘] [limit [m,]n]

• field notation
  • * : denotes all fields
  • as : field alias, col1 as ALIAS1
• WHERE clause
  • operator :
    • = =
    • <
    • ;
    • <=
    • >=
    • ! =
    • Between....and
  • conditional logical Actions
    • and
    • or
    • not
  • fuzzy matching
    • like
      • %
      • _
    • rlike ' pattern ' : Based on regular expression matching
    li> is null
    • is no null
• sort
  • desc : Descending
  • asc : Ascending

Select Example

• select name,age from students where age >30 and age<80;
• select name,age from students where age between 30 and 80;
• select name from students where name like ‘%ang%‘;
• select name from students where name rlike ‘^.*ang.*$;
• select name,age from students where age is null;
• select name,age form students where age is not null;
• select id,name from students order by name;
• select id,name from students order by name desc;

Permissions and Authorization Management

• User Representation Method:

[email protected]

• Classification of administrative rights
  • 管理权限
  • 数据库
  • 表
  • 字段
  • 存储例程

Authorized:

• Authorization Syntax format
  • grant pri_type,...on [object_type] db_name.tbl_name to ‘user‘@‘host‘ [identified by ‘PASSWD‘]
    • pri_type
      • all privileges: Indicates full permissions
    • db_name.tbl_nameMethod of representation:
      • *.*: All tables for all libraries
      • db_name.*: Specify all tables for the library
      • db_name.tbl_name: Specify a specific table for the library
      • db_name.routine_name: Specify a stored procedure or stored function on the library
    • object_type
      • table
      • function
      • procedure
• Authorization Example:
  • grant all privileges on mydb.* to ‘zhenping‘@‘172.16.%.%‘ identified by ‘MT8ddd‘;

Cancel Permissions

• De-Authorization syntax format
  • revoke pri_type,.... on db_name.tb_name from ‘user‘@‘host‘;
• Cancellation Authorization Example:
  • revoke all privileges on mydb.* from ‘zhenping‘@‘172.16.%.%‘;

View User Permissions

• show grants for ‘user‘@‘host‘;

Make the newly authorized permissions effective immediately

• flush privileges;

1 Mariadb服务进程启动时会读取mysql库中的所有授权表至内存中;2 grant和revoke等执行权限操作时会保存于表中,mariadb的服务进程会自动重读授权表3 对于不能够或不能及时重读授权表,可手动让服务进程重启授权表,使用flush privileges

Weizhen Ping
Links: http://www.jianshu.com/p/e59afa955a2d
Source: Pinterest
Copyright belongs to the author. Commercial reprint please contact the author for authorization, non-commercial reprint please specify the source.

MARIADB Basic Explanation