MySQL Learning notes (i)

After each interview, we decided to change the former. The fact remains the same as the fat men who swore to eat the last meal. Whether this is a three-minute heat, but at least I'm starting.

MySQL engine

Speaking of MySQL engine, I also think of the first company to interview two times--big vein. Then the Mengmengdongdong went, and the interviewer asked me about the system I was developing.

What engine the MySQL database uses. Now think of laughter, obviously at that time have learned this, but to the key time to forget. Okay, no more digression, go to the subject.

There are 9 MySQL engines, which we usually use mainly innob and MyISAM. In fact, Innob support transactions, support foreign keys, in the INSERT, update operation only to the data row

Lock. Using MyISAM is much more than a select operation and does not require transaction processing, it does not support foreign keys, and locks packets when inserting, updating operations. Other than that

The storage structure of the two storage engines is also different.

the difference between a MySQL string char and a varchar

varchar is a variable-length string type, which means that the storage space occupied by the string is its own size. For example, for the Chinese Simplified character set GBK string, varchar (255)

Indicates that 255 kanji can be stored. Each character occupies two bytes of storage space. If the string does not have so many Chinese characters, for example, it includes only a "medium" word, then varchar (255)

Occupies only one character (2 bytes) of storage space, if other costs are not considered. char (255) must occupy 255 characters of storage space, even if only one Chinese character is stored inside.

For Char, however, even 2 bytes will take up all of the storage space.

The difference between datetime and datestamp

datetime is similar to datestamp format, but the range of datetime is much larger than datestamp. Datestamp was finished by 2037. In addition the data of the datestamp type

Defaults to the current datetime when it is not assigned.

the difference between where and having

The objects that are acting differently. A WHERE clause acts on a table and a view, and a HAVING clause acts on a group. Where the input line is selected before grouping and aggregating calculations (so that it controls which rows go into the aggregation calculation),

The having group selects the grouped rows after grouping and aggregation ... Therefore, the WHERE clause cannot contain aggregate functions, because it makes no sense to attempt to use aggregate functions to determine which row input to the aggregation operation.

Instead, the HAVING clause always contains a clustered function. (Strictly speaking, you can write a HAVING clause that doesn't use aggregation, but it's just a waste of effort.) The same conditions can be used more effectively in the WHERE phase. ）

MySQL Learning notes (i)