MySQL entry (3): mysql entry

MySQL entry (3): mysql entry

After writing two articles on MySQL getting started, I found that people who write books are still competent. At least people know how to orchestrate the knowledge points they want to talk about, I really don't know where to say it first, so let me know what to say.

1. Write SQL

In fact, I don't want to write about SQL, because this part is actually very simple. Basically, in college, I only need to listen to the database introduction course, which can basically write SQL statements that meet the functional requirements, but I will think about it later, SQL is actually an interface for interaction between people and databases. It is not possible to use SQL statements. It is hard to write SQL statements, but it cannot be felt as powerful as a database, it may even lead to a bad impression that the database is slow.

As an open-source database of most popular, MySQL is not so perfect in SQL support. For example, MySQL does not support all external connections until 5.6 (5.7 has never tried to support it ). If it is said that full outer join is not supported, it is not supported. But I want to say that since RDBMS is based on the Codd theory, full outer join is part of relational algebra, if it is not implemented, it cannot be said. On the contrary, the PostgreSQL developed by Berkeley is doing well. After all, people are most advanced open source databases.

Less Gossip. In fact, the most commonly written SQL statements are as follows:

select t1.a, t1.b, t2.c, t2.d from table1 t1, table2 t2 where t1.m = t2.m and t1.k = ?;

This writing method has no problem at all. Naturally, it is as follows:

I need to extract some data from table1 and table2. The two tables are joined by m columns to obtain the intersection. table1 requires the k condition.

This SQL statement is not shown in the table. Let's continue with some technical things.

Let's talk about the basic syntax. The most frequently used types are internal connections and external connections. In my opinion, the connection is a set operation. For example, the inner join operation is a natural intersection operation, which is based on the inner join of two tables, that is, the set A and B perform the ∩ B operation.

Inner join is translated into English, and outer join is translated into English. Therefore, SQL is easy to understand and even close to natural languages.

Write an internal connection:

select t1.a, t1.b, t2.c, t2.d from table1 t1 inner join table2 t2 on t1.m = t2.m where t1.k = ?;

This SQL is the equivalent form of the above SQL, but this is the ANSI SQL92 writing method, the above is the ANSI SQL89 writing method, SQL is a language, but also a standard, the new standard is better understood, obviously, we know that this SQL statement adopts the internal connection form.

As for how to write the connection, I wrote a note long ago: Oracle connection. I will not reinvent the wheel. Although Oracle was used at the time, I have said that SQL is a standard, so it is common in any RDBMS.

I didn't plan to write this into something formal. I didn't have the dream of writing a book, so I basically spoke about it in the middle.

2. Data Types

SQL is also a programming language! SQL is also a programming language! SQL is also a programming language!

I want to emphasize this three times, because there are many, many of them will think that SQL writing is a very simple thing. SQL is to add, delete, modify, and query data, what you will learn in an hour is a very low thing. Many people I have seen have asked me what SQL statements are written. Isn't there any broken commands? I really don't know how to answer them.

SQL can really do a lot of things, but it is not easy to write SQL, of course, it is more difficult.

MySQL supports the following data types: numeric, time and date, and numeric.

In the past, when I was playing Oracle, the number type was always number, so it was easy to use. However, this is not the case in MySQL. MySQL's numeric types include tinyint, smallint, mediumint, int, and bigint. Each type has "Signed" and "unsigned" attributes. Their scopes are as follows:

Type	Occupied Space (bytes)	Minimum value	Maximum Value
Tinyint	1	-128	127
Tinyint (unsigned)	1	0	255
Smallint	2	-32768	32767
Smallint (unsigned)	2	0	65535
Mediumint	3	-8388608	8388607
Mediumint (unsigned)	3	0	16777215
Int	4	-2147483648	2147483647
Int (unsigned)	4	0	4294967295
Bigint	8	-9223372036854775808	9223372036854775807
Bigint (unsigned)	8	0	18446744073709551615

It is hard to remember, but it is not very difficult. Here, we all know that 1 byte is equal to 8 bit, so the value range of tinyint should be [-2 ^ (8-1 ), 2 ^ (8-1)-1]. The value range of the unsigned type can be derived and can be understood with basic mathematical knowledge.

When I first played MySQL, I found that the column type is usually int (4). What is this 4? From the above table, we can see that the space occupied by the number type is always certain, so here 4 is not the meaning in char (4), which is actually used in the filling format, it must be used with the ZEROFILL attribute, so that the field can be filled up, but it does not affect the value. It feels like a syntactic sugar.

Later, I thought MySQL's varchar model was interesting because the length in the varchar brackets is not the number of bytes, but the number of characters. It is irrelevant to Chinese characters and English characters, that is, the number of characters, it feels like a syntactic sugar. When designing a table, I don't need to consider any character sets. I directly told the programmer that I gave you a column, this column contains a maximum of 100 characters.

But there is a trap here. Many books and online documents will say that the total length of all varchar fields in the InnoDB table cannot exceed 65535. In fact, one premise cannot be ignored, that is, the character set should be latin1, and it is definitely not 65535, It is 65532, there are other overhead. What if I change to the UTF8 character set? The total limit of this theory will change to 21845. This number is actually 65535/3, and the actual limit is 21844. Therefore, I say this is a syntactic sugar. In fact, it is still related to character sets, but it is not to be considered by upper-layer programmers, but to be ensured by DB itself.

MySQL of the time and date types is also very rich, and it is very simple, '2017-12-12 12:31:20 'so that you can write a function for Oracle to convert the string to the date type. Of course, the time and date types also occupy space. In many documents, we will say that the TIMESTAMP type is used to replace the DATETIME type, which saves space. There is indeed no problem. The space occupied by TIMESTAMP is only half of the DATETIME type, however, it should be noted that the TIMESTAMP range is not large. From 1970 to 2038, it is right to save space, but pay attention to application scenarios.

Of course, there are also big data types (huge size data type) such as BLOB and TEXT, which are not very popular nowadays. I don't think this part of data can be used unless necessary. They are stored separately, and I personally feel worried about efficiency. However, you should pay attention to the Application Scenario during the design and give the appropriate data type.

Let's talk about it today.