A MySQL starter in MySQL learning notes

I previously contacted the relational database mainly Oracle and SQL Server, but for MySQL know very little, but read the online data found that MySQL is very similar to Oracle, so it should not be very laborious to learn, There may be a lot of other things to focus on when summarizing the differences between it and Oracle.

first, Brief introduction

MySQL is a true multi-user, multi-threaded SQL database server. SQL (Structured Query language) is the most popular and standardized database language in the world. MySQL is an implementation of a client/server structure,

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

The popularity of MySQL is not confined to the open source team. Although it is performed on a personal computer (it is true that MySQL development is generally done on an inexpensive Linux system), it is portable and capable of performing

On commercial operating systems and on various hardware to Enterprise server. In addition, it has the capability to rival any other system, and it can handle large databases with level millions of.

1.1 MySQL Advantage:

1, speed. MySQL executes very fast. The developer claims that MySQL is probably the fastest database available right now.

2, easy to use. MySQL is a high-performance and relatively simple database system that is less complex than the setup and management of some larger systems.

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

4, support the query language. MySQL is able to use SQL (Structured Query language) and SQL is a language that all modern database systems use. An application that supports ODBC (Open Database Connectivity) is also available, and 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 interactively access MySQL by using several interfaces that input queries and view results.

6, connectivity and security superior. MySQL is completely networked, and its database can be visited anywhere on the internet, so it can be shared with anyone, no matter where. and MySQL has access control that controls who can't see your data.

7, the portability is strong. MySQL can be executed on various versions of UNIX and other non-UNIX systems such as Windows and OS/2.

II. relational database management system (RDBMS)

A relational table has a named set of properties (a T t r i b u T e) or a column, and a set of tuples (t U P l e) or rows. Sometimes a column is called a field, and a row is called a record, and the intersection of columns and rows is often called a cell. A column indicates the position, with a scope or data type, such as a character or an integer. The line itself is the data.

Relational tables must meet certain criteria

1. The data stored in the cell must be atomic. Each unit can only store a single piece of data, which is also called the Information principle (information Principle). Although many systems have been established in the past few years in ways that violate this article, it is not possible to use good design principles in violation of this article. When a cell contains more than one piece of information, this is called the Information encoding (information coding). in this case, it is a matter of design choice to adopt a scheme that violates the theory , although in most cases the result proves that it is disadvantageous to the integrity of the data.

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

3, each line must be unique

4. Column No Order

5, Line no order

6. Column name Unique

Relationship Model two integrity principles: the Entity Integrity principle succinctly indicates that the primary key cannot be empty or blank in all or part, and the referential integrity principle succinctly indicates that a foreign key must be empty or be consistent with the current value of the primary key it refers to.

2.1 SQL and non-procedural programming languages

SQL is a typical non-procedural programming language, which is characterized by specifying only what data is manipulated, and what operations are being run on the data, and how those operations are run.
Corresponding to this is the process of programming language, we are familiar with the various high-level programming languages belong to this category. The characteristic of such a language is that the operation of a statement is related to its statements and control structures (such as conditional statements, circular statements, etc.).

Languages such as SQL are designed to disagree that you are taking a record in a particular order, because doing so reduces the efficiency of SQL sever fetching records. With SQL, you can only read records by query criteria.

Third,MySQL data processing

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

3.1 String Value:

Not only can you use normal characters in a string, but you can also use several escape sequences, which are used to represent special characters. Each escape sequence starts with a backslash ("\"), indicating that the following character is interpreted using the escape character instead of the normal character. Note that the NUL byte differs from the null value, NUL is a 0-value byte, and null represents no value.

Instructions for use in strings:

Assuming that the string is enclosed in the same argument, it is possible to write the argument repeatedly in the string where it is required.
Assuming that the string is enclosed in a different argument, there is no need to double-write the corresponding argument and use it directly in the string, which is not treated in a special way.
Use a backslash, expressed in the form of a transfer sequence, that does not take care of whether to enclose the string with a single or double argument.

3.1.1 MySQL string column type

3.2 Numeric value:

MySQL supports values that are described as integers (no fractional parts) or floating-point numbers (with fractional parts).

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

3.2.1 MySQL Numeric column type

3.2.26 Binary digits:

MySQL supports hexadecimal values. Integers in 16 binary form consist of "0x" followed by one or more hexadecimal digits ("0" to "9" and "a" to "F"). hexadecimal digits do not differentiate between uppercase and lowercase, but their prefix "0x" cannot be "0X". That is, 0x0a and 0x0a are legal, but 0x0a and 0x0a are not legal.

In the numeric context, they behave like an integer (64-bit precision). In the context of strings, they behave like a binary string, where each pair of hexadecimal digits is transformed to a single character.

3.3 Date and Time column types:

A MySQL starter in MySQL learning notes