Database structure Model Classification 1, hierarchical Model 2, mesh Model 3, relational model of the relationship model components
- Two-dimensional relationship
- Table
- Index
- View
- View (contains only fixed fields, not other fields)
Common components of a relational database:
数据库
: Database
表
: Table
索引
: 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)
- DML: (Data manapulating Language)
- Insert
- Delete
- Update
- Select
- DCL: (Data Control Language)
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
- 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
- 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服务器
支持通配符
:
内置命令
\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
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
- Create a database
- Deleting a database
- To view all supported character sets
- View all supported collations
- 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=‘ ‘
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
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
-
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_name
Method 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
- 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
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