MySQL Learning 1: first knowledge of MySQL

What is a database?

A database is a warehouse that organizes, stores, and manages data according to its structure, and each database has one or more different APIs for creating

To build, access, manage, search, and replicate saved data.

We can also store the data in a file, but it is relatively slow to read and write data in the file. So, now we're using relational database management

System (RDBMS) to store and manage large amounts of data. The so-called relational database is a database based on the relational model, with the aid of the set algebra

and other mathematical concepts and methods to process data in the database.

The RDBMS is the feature of the relational database management system (relational Management systems):

1) The data appears in tabular form

2) Each record name of the behavior

3) the data field corresponding to the record name for each column

4) A number of rows and columns form a single sheet

5) A number of forms form database

Two-relational database (RDBMS) Terminology

The following explanation is given for the relational database:

1) relational database can be easily understood as a two-dimensional database, tabular format similar to Excel table, we usually contact the data according to, is generally closed

System database.

2) relational database is not the only advanced database model, nor is it an optimal one, but the relational database is the most widely used and easy-to-use

To understand and use the database model.

3) Before we start learning MySQL database, let's look at some of the terminology of the following RDBMS:

1 Database: A database is a collection of related tables.

2 Data sheet: A table is a matrix of data. Tables in a database look like a simple spreadsheet.

3 Relationship: Can be understood as a two-dimensional table, each relationship has a relationship name, that is, the table name.

4 Relational Schema: a description of the relationship, often referred to as a table structure in a database.

5 tuples: It can be understood as a row in a two-dimensional table, which is often called a record in a database. A row (= tuple, or record) is a set of related data, for example

Data that a user subscribes to.

6 Properties: It can be understood as a column in a two-dimensional table, which is often used as a field in a database. A column (the data element) contains the same data, such as a postal

The encoded data.

7 domain: The value range of a property and the value limit for a column in the database.

8 keyword: A set of properties that uniquely identifies a tuple. A database is often called a primary key that consists of one or more columns.

9 Redundancy: storage twice times the data, redundancy can make the system faster.

10 PRIMARY key: The primary key is unique. Only one primary key can be included in a data table. You can use the primary key to query the data.

11 foreign key: A foreign key is used to correlate two tables.

12 composite key: Composite key (key combination) multiple columns as an index key, typically used for composite indexes.

13 Index: Use the index to quickly access specific information in a database table. An index is a knot that sorts the values of one or more columns in a database table

Frame. A directory similar to a book.

14 Referential integrity: Referential integrity requires that references to non-existent entities are not allowed in the relationship. and entity integrity is a complete, relational model that must meet

To ensure consistency of data.

4) relational database features:

1 Easy to understand: two-dimensional table structure is very close to the logical world of a concept, relative to the mesh, hierarchy and other models easier to understand.

2 easy to use: SQL language programmers and data administrators can easily manipulate databases at the logical level without having to understand their underlying implementations.

3 Ease of maintenance: rich integrity (Entity integrity, referential integrity, user-defined integrity) reduces data redundancy and data inconsistency

Rate.

5) Relationship operation:

1 data query: Selection, projection, connection, and, intersection, difference, except

2 Data manipulation: Add, delete, modify, query

6) SQL (Structured query Language): Structured Query Language, a special purpose programming language, database query and programming language,

Used to store data and query, update, and manage relational databases.

SQL Query Language component:

1DDL Languages statement: Data definition statement for defining different data segments, databases, tables, columns, indexes, etc.

the object definition for the library. Commonly used keywords include create, drop,alter and so on.

2DML (Data manipulation Language) statement: Manipulation statements for adding, deleting, updating, and querying database records, and checking the number of

data integrity. Commonly used keywords include insert, delete, UPDATE, Select, and so on.

3DCL (Data Control Language) statement: A statement that controls the level of permissions and access to different data segments directly for

defines the database, table, field, user access, and security level. Common keywords are grant, revoke, and so on.  

three MySQL database

MySQL is a relational database management system developed by the Swedish MySQL AB company, currently owned by Oracle Corporation. MySQL's most popular off

System of database management, MySQL is the best RDBMS in Web applications (relational DB Management system, number of relationships

Database management System) application software.

MySQL is an associated database management system that stores data in different tables rather than putting all the data in a large warehouse

Increase the speed and increase flexibility.

The SQL language used by MySQL is the most commonly used standardized language for accessing databases. MySQL software uses a dual licensing policy, which is divided into Community edition

and commercial editions, due to its small size, speed, and low total cost of ownership, in particular, the characteristics of open source, the general development of small and medium-sized websites are selected

MySQL as a Web site database. Thanks to the performance of its community edition, PHP and Apache make a good development environment.

Four features of MySQL

1) MySQL is open source, so you don't have to pay an extra fee.

2) MySQL supports a large database. You can handle large databases that have thousands records.

3) MySQL uses the standard SQL data language form.

4) MySQL can be allowed on multiple systems and supports multiple languages. These programming languages include C, C + +, Python, Java, Perl, PHP,

Eiffel, Ruby and Tcl.

5) MySQL has good support for PHP, which is currently the most popular web development language.

6) MySQL support large database, support 50 million records of Data Warehouse, 32-bit system table file can support 4gb,64 bit system to support the most

The large table file is 8TB.

7) MySQL can be customized, using the GPL protocol, you can modify the source code to develop their own MySQL system.

five MySQL system features

1) write in C and C + +, and use a variety of compilers to test, to ensure the portability of the source code.

2) support for AIX, FreeBSD, HP-UX, Linux, Mac OS, Novellnetware, OpenBSD, Os/2 Wrap, Solaris,

Various operating systems such as Windows.

3) provides APIs for a variety of programming languages. These programming languages include C, C + +, Python, Java, Perl, PHP, Eiffel, Ruby,. NET

and the Tcl and so on.

4) Support multi-threading, make full use of CPU resources.

5) optimized SQL query algorithm to improve query speed effectively.

6) can be used as a separate application in the Client server network environment, but also as a library and embedded in other software.

7) Provide multi-language support, common encoding such as Chinese GB 2312, BIG5, Japanese shift_jis, etc. can be used as data table name and data column name.

8) provides a variety of database connectivity pathways such as TCP/IP, ODBC, and JDBC.

9) provides management tools for managing, inspecting, and optimizing database operations.

10) support for large databases. You can handle large databases that have thousands records.

11) support multiple storage engines.

12) Online ddl/change function, data architecture supports dynamic application and developer flexibility (5.6 new).

13) Replicate the global transaction identity to support self-healing clusters (5.6 new).

14) Copy no crash slave to increase availability (5.6 new).

15) Copy multi-threaded slave to improve performance (5.6 new).

Six MySQL database implementation model
MySQL core components (reference)

1) connection pooling: Used to complete user authentication, thread reuse, concurrent connection limit, memory detection, and connection caching.

2) SQL interface: an interface that uses SQL constructs to query the database for the operation of the language (DDL DML, etc.)

3) Parser: parser, used for query transformation, permission analysis to get the operation.

4) Optimizer: Optimizer, generate and select the best access path.

5) Cache & buffers: I/O performance improvement tool related to the storage engine.

6) Storage engine (tools that really perform data query operations MyISAM, InnoDB, NDB, Archive, Federated, Memory, Merge,

Partner, Community, custom and so on.

MySQL Learning 1: first knowledge of MySQL