Backend.com MySQL database video tutorial resource recommendation

"Backend.com MySQL Database Video Tutorial" introduces the concept of database, as well as some basic contents commonly used in databases, such as addition, deletion, modification, query, etc. It also has knowledge about database security, such as anti-injection, etc.

Course playback address: http://www.php.cn/course/427.html

The teacher’s teaching style:

The teacher’s lectures are simple, clear, layer-by-layer analysis, interlocking, rigorous argumentation, rigorous structure, and use the logical power of thinking to attract students’ attention Strength, use reason to control the classroom teaching process. By listening to the teacher's lectures, students not only learn knowledge, but also receive thinking training, and are also influenced and influenced by the teacher's rigorous academic attitude

The more difficult point in this video is the definition and selection of data types :

1. Numeric types:

Mysql supports all numerical types in standard SQL, including strict data types (INTEGER, SMALLINT, DECIMAL, NUMBERIC), and approximate numerical data Type (FLOAT, REAL, DOUBLE PRESISION), and expand on this basis. After expansion, three integers with different lengths, TINYINT, MEDIUMINT, and BIGINT, were added, and a BIT type was added to store bit data.

INT type:

The 5 main integer types supported in MySQL are TINYINT, SMALLINT, MEDIUMINT, INT and BIGINT. These types are largely identical, only the size of the values ​​they store differ.

MySQL extends the SQL standard in the form of an optional display width indicator so that when a value is retrieved from the database, the value can be lengthened to a specified length. For example, specifying that a field is of type INT(6) ensures that values ​​containing fewer than six digits are automatically padded with spaces when retrieved from the database. Note that using a width indicator does not affect the size of the field or the range of values ​​it can store. In case we need to store a number outside the allowed range in a field, MySQL will truncate it according to the end of the allowed range closest to it before storing it. Another special thing is that MySQL will automatically change the illegal value to 0 before inserting it into the table.

The UNSIGNED modifier specifies that the field only holds positive values. Because there is no need to save the positive and negative signs of the numbers, one "bit" of space can be saved during storage. This increases the range of values ​​that this field can store. The ZEROFILL modifier specifies that 0 (not spaces) can be used to complement the output value. Use this modifier to prevent the MySQL database from storing negative values.

FLOAT, DOUBLE, and DECIMAL types:

The three floating-point types supported by MySQL are the FLOAT, DOUBLE, and DECIMAL types. The FLOAT numeric type is used to represent single-precision floating-point values, while the DOUBLE numeric type is used to represent double-precision floating-point values. Like integers, these types take additional parameters: a display width indicator and a decimal point indicator. For example, the statement FLOAT(7,3) specifies that the displayed value will not exceed 7 digits, with 3 digits after the decimal point.

For a value with more digits after the decimal point than the allowed range, MySQL will automatically round it to the nearest value and then insert it. The DECIMAL data type is used in calculations that require very high precision. This type allows the precision and counting method of the numerical value to be specified as selection parameters. The precision here refers to the total number of significant digits saved for the value, while the counting method indicates the number of digits after the decimal point. For example, the statement DECIMAL(7,3) specifies that the stored value will not exceed 7 digits and will not exceed 3 digits after the decimal point.

Omitting the precision and counting method modifiers of the DECIMAL data type will cause the MySQL database to set the precision of all fields identified as this data type to 10 and the counting method to 0. The UNSIGNED and ZEROFILL modifiers can also be used with the FLOAT, DOUBLE, and DECIMAL data types. And the effect is the same as the INT data type.

1. CHAR and VARCHAR types:

The CHAR type is used for fixed-length strings and must be defined with a size modifier within parentheses. This size modifier ranges from 0-255. Values ​​larger than the specified length will be truncated, while values ​​smaller than the specified length will be padded with spaces.

The CHAR type can use the BINARY modifier. When used in comparison operations, this modifier causes CHAR to participate in the operation in binary form, rather than in the traditional case-sensitive manner.

A variant of the CHAR type is the VARCHAR type. It is a variable-length string type and must also have an indicator in the range 0-255. The difference between CHAR and VARCHGAR is the way the MYSQL database handles this indicator: CHAR treats this size as the size of the value, and pads it with spaces if the length is not insufficient. The VARCHAR type, on the other hand, treats this as the maximum value and only stores the value using the length actually required to store the string (plus one extra byte to store the length of the string itself). So VARCHAR types shorter than the indicator length will not be padded with spaces, but values ​​longer than the indicator will still be truncated. Because the VARCHAR type can dynamically change the length of the stored value based on the actual content, using the VARCHAR type when you are not sure how many characters a field requires can greatly save disk space and improve storage efficiency. The VARCHAR type is identical to the CHAR type when using the BINARY modifier.

2. TEXT and BLOB types:

When the field length requirement exceeds 255, MySQL provides two types: TEXT and BLOB. They all have different subtypes depending on the size of the stored data. These large data are used to store text blocks or binary data types such as images and sound files. There are differences in classification and comparison between TEXT and BLOB types. The BLOB type is case-sensitive, while TEXT is not case-sensitive. Size modifiers are not used on various BLOB and TEXT subtypes. Values ​​larger than the maximum range supported by the specified type will be automatically truncated.

3. Date and time types:

When dealing with date and time type values, MySQL comes with 5 different data types to choose from. They can be divided into simple date and time types, and mixed date and time types. Depending on the required precision, subtypes can be used within each subtype, and MySQL has built-in functionality to convert diverse input formats into a standard format.

1. DATE, TIME and TEAR types:

MySQL uses the DATE and TEAR types to store simple date values, and the TIME type to store time values. These types can be described as strings or sequences of integers without delimiters. If described as strings, values ​​of type DATE should be separated by hyphens as delimiters, and values ​​of type TIME should be separated by colons as delimiters.

It should be noted that a TIME type value without a colon separator will be understood by MySQL as a duration, not a timestamp.

MySQL also interprets to the maximum extent the value of two digits in the year of a date, or two digits entered for the TEAR type in an SQL statement. Because all TEAR type values ​​must be stored with 4 numbers. MySQL attempts to convert a 2-digit year to a 4-digit value. Converts values ​​in the range 00-69 to the range 2000-2069. Converts values ​​in the range 70-99 to 1970-1979. If the value automatically converted by MySQL does not meet our needs, enter a 4-digit year.

2. DATEYIME and TIMESTAMP types:

In addition to date and time data types, MySQL also supports two mixed types, DATEYIME and TIMESTAMP. They can store date and time as a single value. Both types are commonly used to automatically store timestamps containing the current date and time, and can work well in applications that need to perform a large number of database transactions and need to establish an audit trail for debugging and review purposes. If we do not explicitly assign a value to a field of TIMESTAMP type, or it is assigned a null value. MySQL automatically populates it with the system's current date and time.

3. Composite type:

MySQL also supports two composite data types, ENUM and SET, which extend the SQL specification. Although these types are technically string types, they can be treated as different data types. An ENUM type allows only one value to be obtained from a collection; a SET type allows any number of values ​​to be obtained from a collection.

4. ENUM type:

The ENUM type only allows one value to be obtained in the set, which is somewhat similar to a single option. Easier to understand when dealing with mutually exclusive data, such as human gender. ENUM type fields can take a value from a collection or use a null value, otherwise input will cause MySQL to insert an empty string into the field. In addition, if the case of the inserted value does not match the case of the values ​​in the collection, MySQL will automatically use the case of the inserted value to convert it to a value consistent with the case of the collection.

The ENUM type can be stored as a number internally in the system, and is indexed starting from 1. An ENUM type can contain up to 65536 elements, one of which is reserved by MySQL to store error information. This error value is represented by index 0 or an empty string. MySQL considers the values ​​appearing in the ENUM type collection to be legal input, and any other input will fail. This shows that the location of the erroneous record can be easily found by searching for rows that contain an empty string or a corresponding numeric index of 0.

5. SET type:

The SET type is similar to, but not identical to, the ENUM type. The SET type can take any number of values ​​from a predefined collection. And like the ENUM type, any attempt to insert a non-predefined value in a SET type field will cause MySQL to insert an empty string. If you insert a record that contains both legal and illegal elements, MySQL will retain the legal elements and remove the illegal elements.

A SET type can contain up to 64 elements. In a SET element the value is stored as a discrete sequence of "bits" that represent its corresponding element. Bits are a simple and efficient way to create ordered collections of elements. And it also removes duplicate elements, so it is impossible to contain two identical elements in a SET type. To find illegal records from a SET type field simply look for rows that contain an empty string or a binary value of 0.

By having a general understanding of the purpose, physical storage, representation range, etc. of each data type. In this way, when facing specific applications, we can choose the appropriate data type according to the corresponding characteristics, so that we can strive to achieve higher database performance with smaller storage costs on the basis of satisfying the application.

The above is the detailed content of Backend.com MySQL database video tutorial resource recommendation. For more information, please follow other related articles on the PHP Chinese website!