MySQL Learning Basics Summary

DBMS Model:

Hierarchical model

Mesh model

Relational model

Difficulty accessing data with files: Data redundancy and inconsistencies, data access difficulties, data isolation, data integrity issues, atomicity issues, concurrency access issues, security issues

XML (extensible language)

Relational Model:

Relational model

E-r Entity-Relationship model

Object model

Semi-structured data model

Files: can be analyzed from three levels for a file

1. Presentation Layer

File

2. Logic Layer

File system: Storage Engine

3. Physical Layer

Meta data

Data block

How to represent e-r graphs

Entity: Rectangle representation

Properties: Ellipse representation

Contact: Diamond Representation

Correspondence between the entities concerned: 1:1 1:n m:n

Three-level mode two-level image of the database:

External mode-------mode-------internal mode

External mode/mode image

Mode/Internal mode image

External mode: Also known as sub-mode or user mode, is a description of the logical structure and characteristics of some data that the database user sees and uses.

Pattern: is a description of the logical structure and characteristics of all the data in the database, it is the public view of all users, and the database schema is based on a database model.

Internal mode: Also into the storage mode, is the contempt for data storage and structure storage, is the data in the database internal storage representation method, a database can only have one internal mode

Three-level mode features: 1, to ensure the independence of the data (internal mode and mode separation, to ensure the physical independence of data, mode and external mode independence, to ensure data logic independence)

2. Simple user interface (write application and input commands in the external mode, do not need to know the storage structure inside the database)

3. Facilitates data sharing

4, in favor of data security and confidentiality

The database management system should have four functions:

1, data definition function, DDL language to define

2, data manipulation function, DML language to manipulate

3, database establishment and maintenance functions (establishment: Data loading, dumping, re-organization. Maintenance: modification, alteration, expansion of database structure)

4. Operation and management function of database

There are two types of relational operations commonly used in relational models:

1. Query operation: Select (select) projection (project) connection (join) (divide) and (union) intersection (intersection) difference (difference)

2. Update operation: Add (insert) Delete (delete) modification (update)

Integrity constraints include: Entity integrity, referential integrity, user-defined integrity, the first two are the integrity conditions that the relational model must meet, and become the two invariance of the relationship

Primary key: One or more properties used to uniquely identify an entity (tuple), and entity integrity requires that the primary key cannot be empty. Primary key indicates

FOREIGN key: One or more columns used to establish and strengthen the connection between two table data. Use foreign key to indicate

Referential integrity is for foreign keys. If f is the foreign key of the corresponding relationship s in relation R, then the tuple on R must meet the

1, F is a null value

2, F equals the primary key value of a tuple in s

User-defined integrity: check, unique

Set operators: And, Cross, and poor

Comparison operator: greater than, less than, equal to, greater than or equal to, less than or equal to, not equal to

Logical operators: Non-,-to, or

Specialized relational operators: selection, projection, connection, addition, generalized Cartesian product

SQL language Classification

DDL: Data Definition language

Create/drop/alter

DML: Data Manipulation language

Insert/delete/select/update

DCL: Data Control Language

Grant/revoke

Value of the integrity constraint: NOT NULL, unique, primary key, foreign key, check, default

Database control is also called database protection, including four aspects, namely security control, integrity control, concurrency control, database recovery

Certification Authority mechanism,

Permissions: System permissions, object privileges, implied privileges

libraries, tables, indexes, views, users, stored procedures, stored functions, triggers, event schedulers

Constraint conditions:

Domain constraints

FOREIGN KEY constraints

PRIMARY KEY constraint

Uniqueness constraints

Check for sex constraints

Storage and querying of data:

Storage Manager

Features: Permissions and integrity Manager

Transaction manager

File Manager

Buffer Manager

Query Manager

Function: DML Interpreter

DDL interpreter

Query execution engine

MySQL is a single-process multithreaded

Daemon process

Application process

Queries for databases consume a large amount of memory, which is often the bottleneck of the service.

WORKAROUND: 1, use cache 2, thread re-reuse

Four ways to connect to a database: 1, Application, 2, DBA (using Database management tools) 3, SQL User (using Database client connection) 4, programmer (using Database Interface API)

Relational operations:

Projection

Choose

Natural connection

Cartesian product

And

How to use the programming language to interact with the RDBMS

Embedded SQL: Similar to dynamic SQL, but its language must be fully determined when the program is compiled

For example: ODBC

Dynamic SQL: The programming language uses functions or methods to connect to and interact with RDBMS servers, send query statements through established connection-based SQL Servers, and save known variables before processing

For example: JDBC

Basic organizational structure of MySQL server:

Connection Manager, parser, cache, optimizer, storage engine

The MySQL storage engine uses MyISAM before 5.5.8 (does not support things operations, makes scenarios for multi-query, such as Data Warehouse), and after mysql5.5.8, the storage engine takes InnoDB (supporting things processing)

Table Manager: Responsible for creating modify read table definition file, maintaining table descriptor telling cache, managing table lock

Table Modify module: Table creation Modify, delete Insert, remove, UPDATE, etc. operations

Table Maintenance Module: Inspection, modification, backup, recovery optimization (defragmentation) and parsing

Line: fixed length, variable length

Record organization in file:

Heap file organization: A record can be placed anywhere in the file

Sequential file organization: stored according to the "Search Code" value order

Hash file organization:

Table structure definition file

Table Data File

B-Tree Index

R-Tree Index

MySQL Data open source site

MARIADB:MARIADB database management System is a branch of MySQL, mainly by the open source community in the maintenance, the use of GPL license. One of the reasons for developing this branch is:

After Oracle acquired MySQL, there was a potential risk of shutting MySQL out of the source, so the community used a branching approach to avoid the risk. MARIADB is designed to be fully compatible with MySQL,

Includes APIs and command lines that make it easy to be a replacement for MySQL. For the storage engine, use XtraDB (English: XtraDB) instead of the MySQL InnoDB.

MARIADB, led by the founder of MySQL Michael Widenius (English: Michael Widenius), has sold his own company, MySQL AB, to Sun for $1 billion earlier,

Since then, as Sun was acquired by Oracle, MySQL's ownership also fell into Oracle's hands. Mariadb name comes from Michael Widenius's daughter Maria's name. MARIADB transaction-based Maria storage engine,

Replacing MySQL's MyISAM storage engine, which uses a variant of the Percona XTRADB,INNODB, branch developers want to provide access to the upcoming MySQL 5.4 InnoDB performance. This version also includes a

PrimeBase XT (PBXT) and Federatedx storage engines.

Percona:percona has improved MySQL database server, with a significant increase in functionality and performance compared to MySQL. This version improves the performance of InnoDB under high load conditions,

Provides some very useful performance diagnostic tools for DBAs, plus more parameters and commands to control server behavior.

Version selection:

Alpha

Beta:

RC:

Ga:

MySQL Change password three ways:

1, mysqladmin-u username @ host password ' New-password ' (-p)

2, mysql>set password for ' username ' @ ' host ' =password (' new_password ');

3, Mysql>update Mysql.user set Password=password (' New_password ') where conditions;

The third method needs to flush privileges; Make the database reread the user table

MySQL client and server are in the same peer mode:

On the same host: communication based on Mysql.sock on a Linux host

Memory (pipe) on the Windows host for communication

Communication on the same host is not communicated using TCP/IP

Dedicated client Tools for MySQL:

MySQL, mysqldump, mysqladmin, Mysqlcheck, Mysqlimport

MySQL Non-client tools

Myisamchk, Myisampack

Server-side:

Msyqld/mysqld_safe/mysqld_mutil (multi-instance)

The MUYSL server lookup configuration file can be found from multiple paths, in the following order:

/etc/my.cnf-->/etc/mysql/my.cnf--> Installation path/my.cnf-->--defult-extra=/path-->~./MY.CNF If you can find several configuration files at the same time, The default is the sequential file, sir.

"Client"

-u-h-P--prot--protocol--database parameter database

The protocol used by the connection is TCP, socket, pipe, memory

How the program connects data:

Dynamic sql:

Embedded SQL:

Jdbc/odbc

Storage Engine:

MyISAM: There are class files in each table. FRM table structure file. MYD table data file. MYI Table Index File

InnoDB: All tables use a table space by default, and you can open a feature to make each table use a separate table space

Show engines; View the storage engines supported by the current server

Show table status [like ' keyword '];

Summarize database error conditions:

1. Previous server not shutdown

2. Data initialization failure

3, Data Directory location error

4. Data Directory Permissions

MySQL data type:

Numeric type

Exact values

Int

Decimal decimal

Approximate values

Float

Double

Real Real number

Character type

Fixed length char, binary (case-sensitive)

Variable length varchar,varbinary (case sensitive)

Enum enum

Set Set

Date-Time Type

Date

Time

Datetime

Timestamp

Meaning of the data type:

1. Types of stored data values

2, the space occupied by the data

3, fixed length or variable length

4. How to sort and index

5. Is it possible to index

Common commands:

Mysql> show Character set; Display the supported character sets

Mysql> show collation; display Character Set collation

SQL Model:

Server variables: User variables (denoted with @) and system variables (denoted by @@ 表示

Scope: Divided into two categories

Global variables: Show global variables

Session variable: Show "session" variables

Effective Time "

Dynamic: variable that can take effect at any time

Static: It needs to be written in the configuration file or passed to the mysqld via parameters

Dynamic adjustment of the parameters of the effective way:

For global variables: invalid for the current session, only valid for new session

For session variables: takes effect in time, but only valid for the current session

Set variables using Set global|session variable name = ' value '

MySQL Server mode:

View current server mode: Show global|session variables like ' Sql_mode ';

SELECT @ @global |session.sql_mode;

The main server models are commonly used in several ways:

INT (5) Zerofill

FLOAT (M,D) or real (m,d) or double PRECISION (m,d). M for precision, d for scale (that is, several after the decimal point, the extra rounding method)

To ensure maximum portability, code stored using approximate numeric data values should use float or double PRECISION, with no precision or number of digits

The DECIMAL (m,d) and numeric (M,D) types are considered to be the same type in MySQL. They are used to hold values that must be of exact precision, such as currency data. When declaring columns of this type, you can (and usually want to) specify the precision and scale; the M default value is 10.

The bit data type can be used to hold the bits field value. The bit (m) type allows the storage of M-bit values. M range of 1 to 64

To specify a bit value, you can use the B ' value ' character. Value is a binary value written in 0 and 1. For example, B ' 111 ' and B ' 100000000 ' represent 7 and 128 respectively

If the length of the value assigned to the bit (m) column is less than M bits, the left side of the value is filled with 0. For example, assigning a bit (6) column A value B ' 101 ', with the same effect as assigning B ' 000101 '

When you want to save a value in a numeric column that exceeds the allowable range of the column, the operation of MySQL depends on the SQL mode that is valid at this time. If the mode is not set,

MySQL crops the values to the corresponding endpoints of the range and saves the trimmed values. However, if the mode is set to traditional ("strict mode"), the out-of-range value will be rejected and the error will be indicated, and the insert based on the SQL standard fails

The storage function is called with select: Example select Database ();

The stored procedure is called with curl

Numeric modifiers are: not null/mull/default/unsigned/unique/auto_increment

Auto_increment

Plastic

Non-empty

No sign

Primary KEY or Unique key

Built-in function select last_insert_id ();

MySQL server shutdown process:

1. Start the shutdown process,

2. The server creates a shutdown thread as needed

3. The server stops receiving connections

4. The server terminates the current activity

5. The storage engine is stopped or shut down

6. Server exit

SQL statements:

show character set; view Character set

Show collation; see how characters are sorted

Creating databases: Help CREATE Database

Create DATABASE db_name character set ' collate ';

Represents a colleague who creates a database specifies how the database character set character is sorted

Cases:

Create database if not exists mydb3 character set ' GBK ' collate ' gbk_chinese_ci ';

CREATE TABLE: Three ways to help create table

1. Direct definition of empty table

2, from other white oh to query out the data, and to create

3. Create a table as a template in another table

Cases:

Way One:

CREATE TABLE tb1 (id int unsigned NOT NULL auto_increment,

Name Char (a) is not NULL,

Age tinyint is not NULL,

Primary KEY (Id,age),

Unique key (name),

Index (age)) Engine=engine_name; Create a table's storage engine while making tables

Way two:

Mysql> CREATE TABLE Testcourse select * from courses where CID <=2;

Way three:

CREATE table test like courses;

Modify table definition: Help ALTER TABLE

InnoDB storage Engine supports foreign KEY constraints

Create an index

Single-Table query:

Distinct means to remove duplicates

Select DISTINCT Cid2 from students where cid2 are NOT null;

Select CNAME from Courese the where CID not in (the select distinct CID2 from students where CID2 are not null);

View:

In general, you cannot insert data into a view

Some databases support materialized views: just save the view

MySQL does not support materialized views, view creation index is not supported

Show create displays the statement used when creating an object

Connection Manager:

Accept Request

Creating Threads

Certified Users

Establish a secure connection

concurrency control problem: Referring to multiple users accessing the same resource at the same time will raise concurrency control issues.

Multi-version concurrency control concept: abbreviation MVCC

Lock type:

Read Lock: Shared lock read abbreviation S lock

Write Lock: Exclusive lock (also called exclusive block) write, abbreviated X-Lock

Add lock manually: Lock table table_name Lock_type Read or write

Unlock: Unlock Table

Blockade protocol: The blockade rules are called blockade agreements according to different rules. can be divided into

1, first-class blockade agreement: Plus x lock, can only solve the lost update problem, but not the end of the pollution reading and rereading problems

2, level Two blockade protocol: on the basis of the first level of blocking protocol plus S lock, can end dirty reading problems, but still can't solve the stress problem

3, Level Three blockade protocol: with S protocol does not immediately release s lock, but after the end of the end of the S lock, can solve the stress problem.

Lock granularity: From large to small, MySQL server only supports table-level locks, row locks require storage engine support complete

Table Lock:

Page lock

Row lock

Thing: A thing is an independent unit of work executed in a database system, which is a set of sequence of operations that a user defines one or more statements, which are either wholly or wholly

Three steps of a thing:

BEGIN TRANSACTION

Commit

Rollback

4 Characteristics of a thing: the acid criterion for short

1, atomicity: A thing is an inseparable unit, either do it all, or do nothing, if the execution of things because some failures lead to failure, the operation has been performed will be revoked.

2, consistency: Things to the database role is the database from a consistent state to another consistent state. A consistent state refers to a database that satisfies a constraint condition. Consistency and atomicity are inseparable.

3, Isolation: If more than one thing concurrent execution, should think of a single thing independent execution, the execution of each thing can not be disturbed by other things, that is, the internal operation of a thing and the use of data on the other things are isolated, concurrency control is to ensure the isolation of things.

4, Persistence: refers to the things once submitted, the database changes in the data is persistent, even if the database because of damage, the DBMS should be able to recover.

Isolation: Isolation Level

READ UNCOMMITTED: Unread uncommitted

Read Committed: Reading commit

Repatable read: Can be reread

Seriablizable: Serializable

To view the isolation level of the current database:

Mysql> Show Golbal variables like '%iso% ';

The concurrency control and recovery control of the database is to ensure that when things are executed concurrently and the fault in the system is thrown to satisfy the acid characteristics of things.

How things are executed: serial execution, parallel access

Serial execution is done sequentially in order of things.

Parallel access is the time when multiple things cross execution, concurrent access to the database.

Causes of inconsistencies in database data due to concurrent operations: 1, missing update, 2, dirty read 3, non-stressed

The scheduling of things is divided into serial scheduling and parallel scheduling. If the serial schedule and parallel schedule Execute the same structure, it becomes serializable schedule.

The current DBMS generally uses the blocking method,

Things are supported by the storage engine, MyISAM do not support things, innodb support things.

Things log:

Redo log: Redo log is written to the log before each execution, and the operation

Undo log: Undo log records the state before execution before each execution,

This article is from the "Seven Stars" blog, please be sure to keep this source http://qikexing.blog.51cto.com/7948843/1672128

MySQL Learning Basics Summary