MySQL learning note One of MySQL getting Started

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 more focus on the difference between it and Oracle in the summary.

First, 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 the implementation of a client/server architecture,

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

The popularity of MySQL is not limited to open source teams. Although it runs on a personal computer (it is true that MySQL development is generally done on an inexpensive Linux system), it is portable and can run

On commercial operating systems and on a variety of hardware to enterprise servers. In addition, its performance is enough to rival any other system, and it can handle large databases with level millions of.

1.1 MySQL Advantage:

1, speed. MySQL runs fast. The developer claims that MySQL is probably the fastest database available at the moment.

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 can take advantage of SQL (Structured Query Language), and SQL is a language that is used by all modern database systems. You can also take advantage of an application that supports ODBC (Open Database Connectivity), which 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 for input queries and viewing results.

6, connectivity and security superior. MySQL is fully networked and its databases can be accessed anywhere on the Internet, so you can share databases with anyone anywhere. And MySQL also has access control to control who can't see your data.

7, the portability is strong. MySQL can run 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 location, with a scope or data type, such as a character or 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 store only one 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, the violation of this article will not apply good design principles. When a unit contains more than one piece of information, this is called the Information encoding (information coding). in such cases, whether or not to adopt a theory-based approach is a design problem , although in most cases the results prove to be detrimental 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 whole or in 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 that is characterized by specifying only what data is manipulated, and what operations are performed on the data, and how those operations are performed.
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 this language is that the execution of a statement is related to the statements and control structures (such as conditional statements, circular statements, etc.) before and after them.

SQL is designed to not allow you to extract records 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 types, date and time types, and string (character) types.

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 begins 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.

The quotation marks are used in string considerations:

If the string is enclosed in the same quotation marks, repeat the quote in the string where the quotation marks are required.
If the string is enclosed in another quotation mark, you do not need to double-write the corresponding quotation mark and use it directly in the string, which is not treated in a special way.
Use a backslash, expressed as a transfer sequence, which is not used to enclose the string in single or double quotation marks.

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 are not case-sensitive, 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: