One of MySQL study notes: MySQL entry _ MySQL

One of the MySQL study notes is that I have been familiar with relational databases such as oracle and SQL Server, but I know little about MySQL. However, I found that mysql is very similar to oracle, therefore, it should not be difficult to learn. in the summary, we may focus more on the differences between oracle and oracle.

I. INTRODUCTION

MySQL is a real multi-user, multi-thread SQL database server. SQL (Structured Query Language) is the world's most popular and standardized database language. MySQL is the implementation of a client/server structure,

It consists of a server daemon mysqld and many different client programs and libraries.

The popularity of MySQL is not limited to the open source code team. Although it runs on a personal computer (indeed, MYSQL development is generally performed on a non-expensive linux system), it can be transplanted and can run

On a commercial operating system and a variety of hardware until the enterprise server. In addition, it has sufficient performance to match any other system, and it can also process large databases with millions of levels.

1.1 MYSQL advantages:

1. speed. MySQL runs fast. The developer claims that MySQL may be the fastest available database.

2. easy to use. MySQL is a high-performance and relatively simple database system. it is less complex than setting and managing some larger systems.

3. the price is free. MySQL is free for most individual users.

4. query languages are supported. MySQL can use SQL (Structured Query Language). SQL is a language used by all modern database systems. You can also use applications that support ODBC (open database connection). ODBC is a database communication protocol developed by Microsoft.

5. high performance. Many clients can connect to the server at the same time. Multiple clients can use multiple databases at the same time. You can use several input queries and view results to interactively access MySQL.

6. superior connectivity and security. MySQL is fully networked, and its database can be accessed anywhere on the internet. Therefore, it can be shared with anyone anywhere. MySQL also supports access control to control who cannot view your data.

7. strong portability .. MySQL can run on UNIX of various versions and other non-UNIX systems (such as Windows and OS/2.

2. relational database management system (RDBMS)

A relational table has a group of named attributes (a t r I B u t e) or columns, and a group of tuples (t u p l e) or rows. Sometimes a column is called a field and a row is called a record. the intersection of a column and a row is usually called a unit. Position of a column, with a scope or data type, such as a character or integer. The row itself is the data.

The relational table must meet certain conditions.

1. data stored in a unit must be atomic. Each unit can store only one piece of data, which is also calledInformation Principles(Information Principle ). Although many systems have been built in the past few years in some ways that violate this line, this violation will not apply to good design principles. When a unit contains more than one piece of information, this is calledInformation encoding(Information coding ).In this case, whether to adopt a solution that violates the theory is a design choice problem.In most cases, the results prove to be detrimental to data integrity.

2. the data stored in the column must have the same data type

3. each row must be unique.

4. no column order

5. the rows are not ordered.

6. the column name is unique.

Two integrity principles of the relational model: the entity integrity principle indicates that the primary key cannot be completely or partially vacant or empty, the integrity principle of reference indicates that a foreign key must be null or be consistent with the current value of the referenced primary key.

2.1 SQL and non-procedural programming languages

SQL is a typical non-procedural programming language. It is characterized by specifying only the data to be manipulated and the operations to perform on the data, and how these operations are executed, it is not specified.
It corresponds to a procedural programming language, which is applicable to the various advanced programming languages we are familiar. This language features that the execution of a statement is related to its statements and control structures (such as conditional statements and loop statements.

The SQL language is designed to not allow you to retrieve records in a specific order, because this will reduce the efficiency of SQL Server to retrieve records. With SQL, you can only read records based on query conditions.

III,MYSQL data processing

MySQL supports a large number of column types, which can be divided into three types: number type, date and time type, and string (character) type.

3.1 string value:

Character strings can contain not only common characters, but also escape sequences. they are used to represent special characters. Each escape sequence starts with a backslash ("/"), indicating that the subsequent characters are interpreted using escape characters rather than common characters. Note that NUL bytes are different from NULL values. NUL is a zero-value byte, and NULL indicates no value.

Note for using quotation marks in strings:

If the string is enclosed by the same quotation marks, you can repeat the quotation marks in the place where the quotation marks are required in the string.
If a string is enclosed by another pair of quotation marks, you do not need to double the corresponding quotation marks and use them directly in the string. this quotation marks are not specially treated.
Use a backslash, which is expressed in the form of a transfer sequence. this method is not used to enclose a string in single quotes or double quotes.

3.1.1 MySQL string column type

3.2 Numeric value:

MySQL supports values described as integers (no decimal part) or floating-point numbers (with decimal part.

MySQL supports scientific notation. Scientific notation is represented by an integer or floating point number followed by "e" or "E", a symbol ("+" or "-", which must have), and an integer index.

3.2.1 MySQL numeric column type

3.2.2 hexadecimal digits:

MySQL supports hexadecimal values. An integer in hexadecimal format is composed of "0x" followed by one or more hexadecimal numbers ("0" to "9" and "a" to "f. Hexadecimal numbers are case-insensitive, but the prefix "0x" cannot be "0X ". That is, 0x0a and 0x0A are both valid, but 0X0a and 0X0A are not valid.

In the numeric context, they represent an integer (64-bit precision ). In the string context, they are similar to a binary string, where each pair of hexadecimal numbers is transformed into a character.

3.3 Date and Time column types: