MySQL basic concept of the database

Problems with data storage:
Data redundancy and inconsistencies
Data access trapped
Data isolation
Integrity
Atomicity issues
For example, AB2 accounts, from A to B transfer money, no matter how to turn, A and B of the total is unchanged. As long as a decreases, b increases.
Concurrent Access Exceptions
Security issues

Hierarchy of files:
Presentation layer: See the file
Logic Layer: File system, Storage engine
Physical layer: Meta Data + data

Structure of the relational database:
Presentation tiers: Libraries, tables, indexes, and so on
Logic Layer: Storage engine (Deconstruction)
Physical Layer: Data file


Workflow structure of a relational database:
initiating queries (applications, SQL users, programmers, DBAs, etc.)--query Manager (various interpreters---query execution engine)--storage Manager (various managers)--write to disk (data files, index files, log files, etc.)


Relational operations:
Select (Select a tuple):
Only rows that match the criteria are output.
Specifies that a field is used as the search code, and the logical operation is to filter rows that match the criteria.

Projection (select Properties):
Outputs only the properties of the specified field.
Displays a section of the specified field and filters the columns that match the criteria.

Natural connections:
Only 2 rows with the same field name and the same property value are output. 2 tables establish an equivalent connection.
For example, there are 2 tables, the field name in Table A is username, the corresponding property value is X, the other field in the same row is the shell, the property value is Y, and table B has the same name as the shell, and the property is Y. Another field in the same row in B is Shelltype, and the property value is Z. The natural connection is displayed as X y y Z. The rest of the output and so on.

Cartesian set (cross Connect):
Reference (A+B) * (c+d) =ac+ad+bc+bd this formula. The relationship between tables is not considered at this time.
Suppose in table A, the fields are username (Tom, Jerry, Mike), Shellnum (1, 2, 3). Table B has the fields QQ (QQ1, QQ2, qq3), TYPE (use, lock, use). The output is 3*3 total 9 lines:
Tom 1 qq1 use
Tom 1 QQ2 Lock
Tom 1 Qq3 use
Jerry 2 QQ1 use
Jerry 2 QQ2 Lock
Jerry 2 QQ3 use
Mike 3 QQ1 use
Mike 3 QQ2 Lock
Mike 3 QQ3 User

Note: A multi-table query essentially generates a Cartesian set in memory before querying, and the build process takes up a lot of resources.

and (set operation):
2 of all the parts of the table are called and.

SQL query statement:
Ddl
Integrity Definition Language

Dml
View definition
Things control
Embedded SQL and dynamic SQL
DCL

How to interact with RDBMS using the programming language:
Embedded SQL:
Similar to dynamic SQL, but its statements must be fully determined at the time of program compilation. lower than dynamic SQL. For example, ODBC (c). Note that ODBC is between embedded and dynamic intermediate, not exactly embedded.
Dynamic sql:
The programming language uses functions or methods to establish a connection to the RDBMS server resume, sends a query statement to the SQL Server, saves the result to a variable, and then processes it. For example, JDBC (Java).

This article is from "Small Private blog" blog, please be sure to keep this source http://ggvylf.blog.51cto.com/784661/1679318

MySQL basic concept of the database