Shulou(Shulou.com)06/01 Report--

The first part: the basis of MySQL basic concept Mariadb: design paradigm: the first paradigm: the field is the second normal form of atomicity: the non-primary attribute is completely dependent on the primary attribute and the available primary key. The third normal form: the non-primary attribute has no transfer function dependency on the primary attribute. No one should rely on the non-primary attribute constraints of other tables: primary key, unique key, foreign key, checking constraint SQL component database, table, index, view, stored procedure, stored function, trigger, event scheduler, User and permission metadata database: mysql Mariadb installation method: rpm package general binary compilation and installation part 2: SQL statement DDL DML DDL:create,alter,drop DML:insert,delete,update,select DCL:grant,revoke, MySQL graphical management tools

Part one: the basic concept of MySQL

I. Infrastructure

1. Database system: (general model)

Parser: parses sql statements, commands, syntax, library names, field names, table names, etc.

Operation solver: how the operation should be performed, which may provide multiple execution paths

Plan executor: select the optimal execution path

The lock manager is a basic component of the transaction manager

File access method / / r or w

Buffer management method / /

Disk Space Manager / / limit the size of the space occupied by tables or libraries

Transaction Manager / /

Recovery Manager / / restore previously uncompleted transactions

two。 Transaction concept

Transaction (transaction): organizes multiple operations as a whole, either all or none at all

Rollback: rollback, if the execution is not completed, all will be rolled back

Bos:8000-1000

Alice:5000,5000+1000

Whether a storage system supports transactions: test criteria

ACID:

A: atomicity, an indivisible whole

C: consistency, the sum of before and after is consistent, before and after the change is consistent

I: isolation. Transactions are isolated from each other. When I withdraw money, other people cannot operate the account.

D: persistent. If the transaction is executed successfully, it will be saved in disk and cannot be modified.

Atomicity (Atomicity), consistency (Consistency), isolation (Isolation), persistence (Durability)

II. Database paradigm

Database design has a certain degree of standardization.

Employee Information:

Constraints: foreign key constraints, table A depends on a field of table B

Expression constraints: limit the age range to 1-100

Primary key: unique identity

RDBMS: / / generally satisfy the third normal form, and the paradigm is a progressive relationship.

1. The first normal form 1NF / / columns can not be divided again, and the columns are unique.

Each column is indivisible atomic data, not non-atomic data items such as collections, groups, etc.

When there are multiple values in an entity, it must be split into different attributes, that is, fields without repetition

In a relational schema, each attribute can no longer be separated. Attribute atomicity

Employee number, name, and telephone number form a table (a person may have multiple phone numbers)

Plan 1: repeatedly store employee numbers and names. In this way, the keyword can only be the phone number. / / not satisfied

Plan 2: second, the employee number is the keyword, and the telephone number is divided into two attributes: the unit phone and the residential phone.

two。 Second normal form 2NF//

On the basis of a paradigm, the non-primary attribute is completely dependent on the primary attribute, that is, to eliminate part of the functional dependence of the non-primary attribute on the primary attribute.

Sc (sid,cid,grade,credit) where sid is the student number, cid is the course number, grade is the grade, and credit is the credit. According to the above conditions, the keyword is a combined keyword (sid,cid)

The sc (sid,cid,grade,credit) / / non-keyword attribute credit depends only on the function cid, that is, credit depends partly on the combined keyword (sid,cid) rather than completely.

Must be split into: sc (sid,cid,grade), c (cid,credit)

3. Third Paradigm 3NF / /

On the basis of the second, there is no transfer function dependency between the non-principal attribute and the main attribute.

The attributes of s (sid,sname,did,dname,location) represent student number, name, department, department name and department address respectively.

Problem: did,dname,location will repeat storage

Cause: there is a transitive dependency in the relationship. That is sid-> did. However, did- > sid does not exist, did- > location, so the keyword sid determines the location function by passing the dependency did- > location. That is, sid does not directly determine the non-primary attribute location.

Methods: divided into two relations s (sid,sname,did), d (dno,dname,location)

Note: there must be an external keyword did in relation s. Otherwise, the connection between the two relationships will be lost.

4. The fifth paradigm, Bastco paradigm.

Http://www.cnblogs.com/hi-bazinga/archive/2012/06/05/2536806.html

III. Other concepts of database

Databases: collections of data

Table: in order to meet the normal form design requirements, split a dataset into multiple constraints: constraint, insert the restriction rules to be followed into the data table primary key: the combination of one or more fields, the data of the primary key must be unique and cannot repeat the foreign key: the data that can be inserted into a field in table A depends on the unique key in the primary key of table B: the combination of one or more fields Constraints filled in unique keys must be different from existing data and can be empty checkable constraints: depending on the request index of the expression: extract one or more fields from the table and store them separately as a unique data structure Index is a data structure: common index type: tree: hash: purpose: speed up search, but not conducive to w request relation operation: select: select qualified row projection: select qualified column join: associate multiple tables left join, right link Cartesian product, interconnect data abstraction: physical layer: determines the format of data storage, that is, how data is organized into physical files, logical layer: describes what data is stored in DB and what relationships exist between data view layers: describe some of the data in DB Classification of relational models: general relational model: entity relational model: object-based relational model: semi-structured relational model: xml

Mariadb (Mysql)

Unireg:

MySQL AB--- > company, early is the application interface for Unireg

Sold to Sun, initially running on Solaris: binary version, and later on linux

Sun was acquired by oracle.

My: the author's eldest daughter

Maria: the author's third daughter

Mysql:www.mysql.com

Mariadb:wwww.mariadb.org

Fourth, install MariaDB

Features of Mariadb:

Plug-in storage engine, storage manager has a variety of implementation versions, users can choose flexibly according to their needs

Storage engine is also known as "table type"

1. More storage engines / / show engines

MyISAM / / 5.1 is used by default

MyISAM-- > Aria / /

InnoDB-- > XtraDB / / support transactions after 5.5i

two。 Many new extended features:

3. Provides more test components

4.truly open source; is really open source.

The issuing mechanism of MySQl

Enterprise: provides richer features

Community:

Installation method:

1.rpm package

1. Provided by the publisher of OS

two。 The program is officially provided.

two。 Source code packet

3. After decompressing the common binary format / /, you can simply configure it.

Install mariadb in universal binary format: / / uninstall the previously installed version first

1. Uninstall the original version and extract the new version

Systemctl stop mariadb cp / usr/lib/systemd/system/mariadb.service / root backup unit file yum remove mariadb-server https://downloads.mariadb.org/ mariadb-5.5.46-linux-x86_64.tar.gz / / filename tar xvf mariadb-5.5.46-linux-x86_64.tar.gz-C / usr/local/ cd / usr/local Ln-sv mariadb-5.5.46-linux-x86_64/ mysql

two。 Belong to groups, directories and permissions, profile

Id mysql | | group add-r-g 306 mysql/ / if not, create the system user cd / usr/local/mysql/ chown-R root:mysql. / * mkdir / mydata/data-pv chown-R mysql.mysql / mydata/data/ Note: / etc/my.cnf is a previous package and needs to be deleted Or move to another place / usr/local/mysql/support-files: provide a configuration file mkdir / etc/mysql cp support-files/my-large.cnf / etc/mysql/my.cnf / / this directory will also be read to configure vim / etc/mysql/my.cnf [mysqld]. Datadir = / mydata/data skip_name_resolve = ON innodb_file_per_table = ON...

3. Initialize metadata:

Scripts/mysql_install_db-- help scripts/mysql_install_db-- user=mysql-- datadir=/mydata/data-- skip-name-resolve launch: 1.cp support-files/mysql.server / etc/init.d/mysqld / / use centos6 chmod + x chkconfig-- add mysqld service mysqld start 2.vim / root / mairadb.service / / use unit file to start unit file modification and addition

Summary:

1. Add system users, modify belonging groups

two。 Create the target directory and modify the master group

3. Copy and modify the configuration file

4. Initialize the database (specify user,datadir,--skip-name-resolve)

Next steps:

1. Add lib

Vim / etc/ld.so.conf.d/mariadb2.conf

/ usr/local/mysql/httpd

Ldconfig-p | grep mysqld

2.path

Vim / etc/profile.d/mysql.sh

Export PATH=$PATH:/usr/local/mysql/bin

Source / etc/profile

3.man / / you can find it yourself in the / usr/local directory.

4. Header file include

Ln-sv / usr/local/mysql/include/mysql/ / usr/include/

Step: cat INSTALL-BINARY / / this file has specific installation steps

1. Prepare / mydata/datda2.useradd-r mysql cd / usr/local ln-sv mari-server mysql cd / usr/local/mysql/ chown-R root:mysql. / * scripts/mysql_install_db-- user=mysql-- datadir=/mydata/data cp support-files/mysql.server / etc/init.d/mysqld chconfig-- add mysqld3. Configuration file ini format configuration file, each program can obtain configuration information through this configuration file; [program_name] configuration file search order: mysql-- help / / you can see the order 1.RPM package / etc/mysql/my.cnf-- > / etc/my.cnf--- > ~ /. My.cnf if conflict: the later, the role 2. General binary / etc/my.cnf / etc/mysql/my.cnf ~ /. My.cnf if conflict: the later, cp support-files/my-large.cnf / etc/my.cnf add: datadir = / mydata/data innodb_file_per_table = ON skip_name_resolve = ON4. Start the service service mairadb start

Note: database systems are a waste of memory and hardware

The original / etc/my.cnf will be affected and needs to be dealt with

Note:

1. 5.x is to be compatible with MySQL5.x, and the interface is almost the same. Users who support open source free software are transferred from the community version of MySQL to MariaDB.

2.10.x said it would use a lot of new technology, and the interface would go its separate ways from mysql completely. The goal is to transition to the new MariaDB interface in the future.

Problem: unit fil

1. Controlled by systemd

Files generated after installation: / usr/lib/systemd/system/dhcpd.service

[root@MT ~] # systemctl enable dhcpd

Created symlink from / etc/systemd/system/multi-user.target.wants/dhcpd.service to / usr/lib/systemd/system/dhcpd.service.

2.cp mariadb55.service / usr/lib/systemd/system/

Vim / usr/lib/systemd/system/mariadb55.service

ExecStart=/usr/local/mysql/bin/mysqld_safe-- basedir=/usr/local/mysql / / modify, and delete both pre and post

Configuration file, throwing it to the original

Part two: SQL statement

DDL,DML

DDL:create,alter,drop

DML:insert,delete,update,select

DCL:grant,revoke

MySQL graphical management tool

1. The composition of Mariadb program:

C:client

Mysql:CLI interactive client program

Mysqldump: backup

Mysqladmin: management tool

Mysqlbinlog: viewing binary logs

...

S:server

Three types of socket addresses:

IPv4,3306/tcp

Unix Sock:/var/lib/mysql/mysql.lock, / tmp/mysql.lock / / Service starts generation and closes deletion

Native: C===S:127.0.0.1

Mysqld

Mysqld_safe / / it is recommended to run the server program, which is used by default

Mysqld_multi: multiple instances

Mysqld_upgrade: upgrade tool

...

The method of getting its reading order

Mysqld-- verbose-- help | less

Mysql-- help | less

Mysql command

Common option

-u user name, default is root

-h remote host, default is localhost

-p password, empty by default

-D-- database=name, directly select the database / / display the current library on 6 by default, select database (); view, use db_name uses this library

-e-excute=name, run the command directly, and do not enter the interactive

Note: the user account of mysql consists of two parts: 'usernmae'@'host';, through which host is used to restrict which remote hosts this user can connect to the current mysql service

The representation of host, which supports the use of wildcards

%: any character of any length

172.16%.%

_: match a single character:

II. Commands related to cswab

Client command

Server command; send to the server via mysql connection to execute and retrieve the result

Client:

Mysql > help\ u db_name\ Q exit\ d CHAR: set the new statement Terminator\ g to send to the server, the universal Terminator\ G statement end tag, and the result displays the connection status in vertical mode.

Server command:

DDL,DML,DCL

Note: each statement must have a statement Terminator, which defaults to the semicolon (;)

Third, data type: / / purpose, determine the data format, can participate in the operation, available valid range

Tables: rows and column

Creating tables: defining fields in a table

When defining fields: it is critical to determine the data type

Used to determine: data storage format, types of operations that can participate in operations, and valid data ranges that can be represented

Character types: character set

Code table: establishing a mapping relationship between characters and binary numbers

Db, client, server, Conn can have different character sets

Show characet set

Type:

Character type fixed length character type: / / 0255character char (#): case insensitive binary (#): case sensitive varbinary (#): 0-65535 variable length character type: / / 0-65535 varchar (#): requires a Terminator varbinary (#): requires a Terminator Take up a character object storage: / / the pointers are stored in the table, and the real data is stored elsewhere text: store a large amount of text, 2 ^ 32 characters tinytext 0255 / / byte text 0-65535 mediumtext 0-2 ^ 24 longtext 0-2 ^ 32 blob: binary large objects Case-sensitive tinyblob,blob,mediumblob,longblob is similar to the built-in type of text: set: the collection SET ('axiomagronomy pencyclicc') indicates that the inserted value can be any combination of the three characters of' afield', that is, the insertion of 'aforme'. 'All abc' are legal enum: enumerating ENUM ('afetch,' breadtheconc') means that the inserted value can only be in aLog b. C to select the numerical type precise numerical type int (tinyint,smallint,mediumint,int,bigint) 1, 2, 3, 4 8 int: 2 ^ 32-1 or-(2 ^ 32 / 2)-(2 ^ 32 / 2-1) / unsigned bit Bit type has only 1 and 0 values approximate numeric float 4-byte dobule 8-byte DECIMAL occupies (msqu2) byte DECIMAL (mcamera d) M represents the total length of the number. The decimal point is not occupied. D represents the number length after the decimal point date-time type date-type: date time type: time date-time type: datetime time stamp: timestamp / / 00:00:00 on 01 January 1970 (08:00:00 on 01 January 1970 Beijing time) Total seconds from now year: year (2) Year (4) data type modifier: unsigned: unsigned numeric not null: non-empty / / default value: default

Character type:

Numerical type:

Character modifier:

Not null/null/default 'string'/collation' / rules for sorting

Show character set

Show collation

DML: data manipulation language, which is mainly used to manage data in tables and to add, delete, modify and query data.

Insert,delete,update,select

Numeric type modifier:

Not null/null/default numer/

Auto_increment/select last_insert_id () / / check which one has grown to

Float and double modifiers:

Auto_increment,default,not null,null,unsigned,zerofile

Bit: two-tier type

Booltype:

The Bool type of tinyint (1) / / mysql displays only tinyint (1), 1 or 0

Boolean

Bool

Date modifier:

Null/not null/default

Enum and set types

Enum / / from 1 to 65535 strings, any element, but cannot be combined

Set / / 1-64 bit string collection, elements can be combined arbitrarily

When storing: enum ('axiomative1') / / when storing data, the indexes in enmu are stored.

For example, insert.. Value ('a') / / stores 0, that is, the index of enum ('a'), not the a string

Set storage:

Set / / insert data is 100 / because an appears, the other does not appear

/ / so generally enum and set are not used for sorting and comparison

Modifier:

Not null/null/default

IV. Server-side commands

DDL: data definition language, mainly used to define database components, such as tables, indexes, views, users, stored procedures

Create,alter,drop

DML: data manipulation language, which is mainly used to manage data in tables and to add, delete, modify and query data.

Insert,delete,update,select

Get help with the command

Mysql > help COMMAND

Database Management:

1. Create: CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification].

Create_specification:

[DEFAULT] CHARACTER SET [=] charset_name [DEFAULT] COLLATE [=] collation_name

Character set view: show character set

View collation: show collation

two。 Modify ALTER {DATABASE | SCHEMA} [db_name] alter_specification.

Alter_specification:

[DEFAULT] CHARACTER SET [=] charset_name [DEFAULT] COLLATE [=] collation_name

3. Delete DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

Note: you can create a database by creating a subdirectory in the data directory, but this is not recommended

Table Management:

Create: create table if not exists tb_name (create_defination) create_defination field: col_name data_type key: primary key (col1,col2,...) Unique key (col1,col2,...) Foreign key (clo) Index: key | index [index_name} (col1,col2,..) Create table student (id int unsigned not null,name char (30) not null,age tinyint unsigned,gender enum); create table student (id int unsigned not null,name char (30) not null,age tinyint unsigned,gender enum), primary key (id,name) Note: unsigned should be placed before not null, immediately after int, otherwise there will be an error engine = storage engine show tables status\ G; view default engine show tables status like student\ G; view specified table show engines: view all supported engines show table status like 'student'\ G

Note: the database has special design tools.

Modify: alter table table_name alter_specification

Alter_specification

Field:

Add: ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name] alter table student add class varchar [COLUMN] col_name | PRIMARY KEY | FOREIGN KEY fk_symbol alter table student drop column class; modifty:MODIFY [COLUMN] col_name column_definition only modifies the definition lter table student modify class varchar (100) not null after id Change:CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name]

Key:

Add: add {primary key | unique key | unique key} index_name

Delete:

Drop primary key

Drop froeign key fk_symbol

Drop key | index index_name / / key is also an index to some extent

Index:

Add: add {index | key} [index_name} {col1,col2,...}

Delete: drop primary key

Alter table student drop primary key

Alter table student add unique key (name)

Viewing: show indexes from student

Table options:

Engine = engine_name / / is not recommended. You can specify it directly when creating it.

How the table is referenced:

Tbl_name

Create mydb.class / / absolute path, library name. Show that

5. DDL and DML statements

The second way to create: copy the table structure

The third way to create; copy table data

1. Indexes: speed up queries, but not conducive to query operations

Indexes are special data structures:

The index should have a name:

Create: crete [Index options] index index_name [index_type] on tbl_name (col1,col2,...)

Index option: unique,fulltext,spatial

Index_type: btree/hash

In mysql, only memory supports hash, while memroy tables are rarely created manually

Create index named_class on student (name,class)

ALTER TABLE table_name ADD INDEX index_name (column_list)

ALTER TABLE table_name ADD UNIQUE (column_list)

ALTER TABLE table_name ADD PRIMARY KEY (column_list)

Delete

DROP INDEX index_name ON talbe_name

ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE table_name DROP PRIMARY KEY

/ / No modification, because the index was created in time

View the index:

SHOW INDEX FROM mytable FROM mydb

SHOW INDEX FROM mydb.mytable

Mysql > show index from tblname

Mysql > show keys from tblname

2.DML statement:

Insert [into] tbl_name {col1,clo2,} {values | value} (va1,va2,va3...), (va1,va2,va3,),...

Note:

Character type: quotation mark

Numerical type: don't use quotation marks in the first place

Insert into student values (1 recordable Xiyujia pageant ou yang feng',78,'f')

Insert into student (id,name) values (2 yao shi'), (3 botong')

Select * from student

Select

1.select * from tbl_name

2.select col1,col2,col3,.. From tbl_name

When displayed, fields can be displayed as aliases

Select id as stuid,name from student; / / projection

3.select col1,col2,... From tbl_name where claues

Where clause: used to specify selection criteria

Age > 30

Operator:

>, > =, less than, less than or equal to

Combined testing:

And

Or

Not

Operator:

Between / / age between 30 and 80

Like 'pattern' / /

Wildcards supported by like

%: any character of any length

_: single character

Rlike / / regular expression

^ the beginning of the line

End of $

Is

Is null// is empty

Is not null / / is not empty

4.select col1,col2,... Tbl_name [where clause] order by col_name,col_name2 [asc | desc]

Desc sorts the order

Select id,name,age from student where age > 30

Elect id,name,age from student where name='OU yANG FENG' and age > 30

Select name from students where name like'% ang%'

Select name from student where name rlike'^ z.roomboxing'

Select id,name from student order by id desc

Delete

Delete from tbl_name [where where_conditon] [order by...] [limit row_count]

Deletions are deleted by line, not just one field of a line

Delete from student where age is null / / if where is not given, the entire table will be deleted

Generate random data:

For i in {1... 100}; do AGE=$RANDOM0;mysql-e "insert into testdb.student (id,name,age) values

Stu$i,%ARG / / it is also possible to use ""

Delete from student order by age desc limit 20

Select * from student order by age desc

Update:

The age of the oldest person in update / / id minus 10 years old

Update student set age=age-5 order by id desc limit 10

Select id,name,age from student order by id desc

Update student set age=age-15 where name not like 'stu%'

VI. User account and authority management

User account: 'username'@'host'

Host: which hosts are allowed to create links remotely when this user accesses the current server

Representation: IP, network, hostname, wildcard {% and _}

Do not check hostname: my.cnf

[mysqld]

Skip_name_resolve = ON

Create user 'wolf'@'host' identified by' password' / / only have permission to view test libraries

Delete a user:

Drop user test

View user information:

Select host,user,password from user

Authorization level:

Permission levels: administrative permissions, databases, tables, fields, stored procedures

Grant priv_type,... On [object_type] db_name.tbl_name to 'user'@'host' [identified by' password']

Db_name.tb_name

All tables in db_name.* / / Library

*. * all libraries and tables

Db_name.routine_name: specify a stored procedure or stored function on the library

[object_type]

TABLE

FUNCTION

PROCEDURE

Grant select on testdb.* to 'testdb'@'192.168.4.%'; / / if the user is not changed, the user will be created automatically

Grant select on testdb.student to 'wolf'@'192.168.4.%'

Flush privileges

Show grants for 'wolf'@'192.168.4.%'

Show grants for current_user; / / View current user authorization

Revoke the authority:

Revoke select on testdb.student from 'wolf'@'192.168.4.%'

Revoke... From

Grant... On.... To.. Aguilb

Note: when the MariaDB service process starts, all authorization tables of the mysql library are read into memory

Permission operations performed by 1.grant or revoke commands will be saved in the table. MariaDB will generally reread the authorization table automatically, and the permission modification will take effect immediately.

two。 Permissions implemented in other ways must be flush privileges in order to take effect.

Note; security options

/ usr/local/mysql/bin/mysql_secure_installation set initialization password

Add password later:

Grant all privileges on MT.* to 'mt'@'192.168.4.%'; / / if you forget to add identified by "password"

Select user,host,password from user; / / does not have a password

Update user set password=password ('mingtian') where user='mt'; / / can only use this

Insert is to insert a new row and cannot be used here

7. PhpMyAdmin / / mysql graphical management tools

Extract to the web directory

Ln-sv phpMyAdmin-3.4.10.1-all-languages MyAdmin

Cp config.sample.inc.php config.php

Openssl rand-base64 15

FSJJJFVLvfiQeoepws88

Vim config.php / / encrypt cookie

$cfg ['blowfish_secret'] =' FSJJJFVLvfiQeoepws88'; / * YOU MUST FILL IN THIS FOR COOKIE AUTH! * /

Systemctl restart httpd

Yum install php-mbstring-y / / multilingual support

Problem: missing mcrypt extension

Yum install php-mcrypt

Problem: cookie must be enabled to use

1.vim config.inc.php

$cfg ['blowfish_secret'] =' cookie'

$cfg ['Servers'] [$I] [' connect_type'] = 'socket'; / / if MySQL connects using socket

2.vim libraries/config.default.php

The $cfg ['blowfish_secret'] =' cookie'; file is empty by default, and you need to fill in the same value as config.inc.php.

$cfg ['Servers'] [$I] [' socket'] ='/ tmp/mysql.sock'; this needs to be filled in according to your own mysql configuration. If you don't know how to leave it empty,

Drawing management tools:

PhpMyAdmin

Navicat

Mysql-Front

ToadForMySQL

SQLyog

The concept of storage engine: myisam,innodb

Myisam: transactions are not supported

Innodb: supports transactions and does not support fulltext indexes

Later articles will explain in detail

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.