How to understand the concept of data types in MySQL?

The content of this article is about how to understand the concept of data types in MySQL. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

1. What is data?

Data is a collection of information, stored in a database in a certain data type. Data includes names, numbers, currency, text, images, decimals, calculations, statistics, and more—almost anything imaginable. Data clients save as uppercase, lowercase, or mixed case, data can be manipulated or modified, and most data does not remain unchanged during its lifetime.

The data type is used to specify the rules for the data contained in the column. It determines how the data is stored in the column, including the width assigned to the column, and whether the value can be letters, numbers, dates, and times. Any data or combination of data has a corresponding data type. These data types are used to store letters, numbers, dates and times, images, binary data, etc. In more detail, data can include names, descriptions, numbers, calculations, images, image descriptions, documents, etc.

Data is the purpose of a database and must be protected. The person who protects the data is the database administrator (DBA), but each database user is also responsible for taking the necessary measures to protect the data.

2. Basic data types

The data type is the characteristic of the data itself, and its characteristics are set to the fields in the table. For example, we can specify that a field must contain a numeric value and not allow strings of numbers or letters to be entered; we also do not want letters to be entered in a field that stores currency values. Defining data types for each field in the database can greatly reduce erroneous data due to errors in the database. Field definition (data type definition) is a data verification method that informs the data that can be entered in each field.

1. Fixed-length string.

Fixed-length strings usually have the same length and are saved using fixed-length data types. The following is the standard for Sql fixed-length strings:

CHARACTER(n)

n is a number that defines the maximum number of characters that can be stored in the field.

Some Sql implementations use the CHAR data type to save fixed-length data. Letters can be saved into this data type.

In fixed-length data types, spaces are usually used to supplement insufficient characters. If the character length is 10 and the input data is only 5 digits, the remaining 5 digits will be recorded as spaces. Padding ensures that each value in the field is the same length.

PS: Do not use fixed-length data types to store data of variable length, such as names. Improper use of fixed-length data types may result in a waste of available space and affect the accurate comparison of different data. . Variable-length data types should be used to save strings of variable length to save database space.

2. Variable length string.

Sql supports variable-length strings, that is, strings with variable lengths. The following is the standard for Sql variable-length strings:

CHARACTER VARYING(n)

n is a number, indicating the maximum number of characters that can be stored in the field.

Common variable-length string data types include VARCHAR, VARINARY and VARCHAR2. VARCHAR is an ANSI standard and is also used by Microsoft Sql Server and MySql; VARINARY and VARCHAR2 are both used by Oracle. Fields defined as characters can hold numbers and letters, which means the data may contain numeric characters. VARBINARY is similar to VARCHAR and VARCHAR2, except that it contains bytes of variable length. This data type is commonly used to store digital data, such as image files.

Fixed-length data types use spaces to fill gaps in fields, but variable-length strings do not do this. For example, if the length of a variable-length field is defined as 10, and the length of the input string is 5, then the total length of the value is 5, and spaces will not be used to fill the gaps in the field.

3. Large object type.

Some variable-length data types need to store longer data, exceeding the length generally reserved for VARCHAR fields, such as the now common BLOB and TEXT data types. These data types are specifically designed to hold large data sets. BLOB is a binary large object, and its data is a very long binary string. BLOB is suitable for storing binary media files in the database, such as images and MP3.

The TEXT data type is a long string type that can be regarded as a large VARCHAR field and is usually used to save large characters in the database. Set, such as HTML input for a blog site. Saving this type of data in the database enables dynamic updates of the site.

4. Numeric type.

Numbers are stored in fields defined as a certain numerical type, generally including NUMBER, INTERGER, REAL/DECIMAL, etc.

Xiami is the standard for Sql values:

(1).BIT(n);

(2).BIT VARYING(n);

(3).DECIMAL(p,s);

(4).INTEGER;

(5).SMALLINT;

(6).SIGINT;

(7).FLOAT(p,s);

(8).DOUBLE PRECISION(P,S);

(9).REAL(S);

P represents the maximum length of the field. s represents the number of digits after the decimal point.

A common numerical type in Sql implementation is NUMERIC, which complies with the ANSI standard. Values ​​can be 0, positive numbers, fixed-point numbers, and floating-point numbers. The following is an example of using NUMERIC:

NUMERIC(s), this command limits the maximum value that a field can accept to 99 999. In all database implementations involved in the examples in this book, NUMERIC is implemented in the DECIMAL type.

5. Decimal type.

The decimal type is the overall length of the value. For example, in the numerical definition ZDECIMZAL(4,2), the significant number of digits is 4, which means that the total number of digits is 4. The scale is the number of digits after the decimal point, which is 2 in the previous example. If the number of decimal places in the actual value exceeds the defined number of digits, the value will be rounded off. For example, when 34.33 is written to a field defined as DECIMAL(3,1), it will be rounded off to 34.3.

If the value is defined as follows, its maximum value is 99.99:

DECIMAL(4,2)

The effective digits are 4, indicating that the overall length of the value is 4 ;The scale is 2, which means 2 digits are retained after the decimal point. The decimal point itself does not count as a character.

The fields defined as DECIMAL(4,2) allow input arrays including:

(1).12

(2).12.4

(3).12.44

(4).12.449

The last value 12.449 is rounded to 12.45 when saved to the field. In this definition, any value between 12.45 and 12.499 will be rounded to 12.45.

6. Integer.

An integer is a numerical value (including positive and negative numbers) that does not include a decimal point.

The following are some valid integers:

(1).1;

(2).0;

(3).-1;

(4).99;

(5).-99;

(6).199;

7. Floating point number.

Floating point numbers are decimal values ​​with variable significant digits and scale and no limit. Any significant digits and scale are acceptable. The data type REAL represents a single-precision floating-point value, while DOUBLE PRECISION represents a double-precision floating-point value. The number of significant digits for a single-precision floating-point value is 1 to 21 (inclusive), and the number of valid digits for a double-precision floating-point value is 22 to 53 (inclusive). The following are some examples of FLOAT data types:

(1).FLOAT;

(2).FLOAT(15);

(3).FLOAT(50) ;

8. Date and time types.

Date and time data types are obviously used to save date and time information. Standard Sql supports the DATETIME data type, which includes the following types:

(1).DATE;

(2).TIME;

(3).DATETIME;

(4).TIMESTAMP;

The elements of the DATETIME data type include:

(1).YEAR;

(2).MONTH;

(3).DAY;

(4).HOUR;

(5).SECOND;

ps: The SECOND element can be further decomposed into several points One second, its range is 00.000~61.999, but not all Sql implementations support this range. The extra 1.999 seconds are used to achieve leap seconds.
Each Sql implementation may have a custom data type to save date and time. The data types and elements introduced earlier are standards that every Sql vendor should adhere to, but most implementations have their own data types to hold date values ​​in a form that is different from how they are actually stored.

Date data generally does not specify the length. Later we will introduce the date type in more detail, including how date information is stored in some implementations, how to use conversion functions to manipulate dates and times, and use examples to show how to use dates and times in practical work.

9. Literal string.

A literal string is a series of characters, such as a name or phone number, that is explicitly specified by the user or program. Literal strings contain data with the same properties as the previously introduced data types, but the value of the string is known. The values ​​of the columns themselves are usually not deterministic because each column usually contains different values ​​for the field in all records.

In fact, it is not necessary to specify the field as a literal string data type, but to specify a string. Examples of literal strings are as follows:

(1).'Hello';

(2).45000;

(3).'45000';

(4).3.14;

(5).'November 1,1997';

The character string is surrounded by single quotes, and the value 45000 has no single quotes surrounded, and the second 45000 is surrounded by double quotes. Generally speaking, character strings require single quotes, but numeric strings do not.

Implicit conversion of a data type into a numeric type. During this process, the database will automatically determine which data type should be used. Therefore, if a data is not surrounded by single quotes, the Sql program will identify it as a numeric type. Therefore, special attention must be paid to the format of the data. Otherwise, the storage results may be biased or errors may be reported. Later we will explain how to use literal strings in database queries.

10.NULL data type.

NULL value means no value. NULL values ​​are widely used in SQL, including table creation, query search conditions, and even literal strings.

When using the NULL data type, it is necessary to clearly indicate that the corresponding field does not require data input. If a field must contain data, set it to NOT NULL. As long as the field may not contain data, it is best to set it to NULL.

11.Boolean value.

The value range of Boolean values ​​is TRUE, FlASE and NULL, which is used for data comparison. For example, when you set conditions in a query, each condition is evaluated to TRUE, FLASE, or NULL. If the value of all conditions in the query is TRUE, the data will be returned; if the value of a certain condition is FLASE or NULL, the data will not be returned. For example, the following example:

WHERE NAME = ‘SMITH’

This may be a condition in the query. Each row of data in the target table is evaluated based on this condition. If the NAME field value of a row in the table is SMITH, the value of the condition is TRUE, and the corresponding record will be returned.

Most database implementations do not have a strict BOOLEAN type, but instead use different implementation methods. MySql uses the BOOLEAN type, but is essentially the same as its existing TINYINT type. Oracle prefers to let users use a CHAR(1) value instead of a Boolean value, while SQL Server uses BIT instead.

12. Custom type.

Custom types are user-defined types, which allow users to customize their own data types based on existing data types to meet data storage needs. Custom types greatly enrich the possibilities of data storage, giving developers greater flexibility in the development process of database programs. The statement CREATE TYPE is used to create a custom type.

For example, in MySql and Oracle, you can create a type like this:

CREATE TYPE PERSON ASOBJECT

(NAME VARCHER (30),

SSN VARCHAR(9);

)

Then you can reference the custom type like this:

CREATE TABLE EMP_PAY

(EMPLOYEE PERSON,

SALARY DECIMAL(10,2),

HIRE_DATE DATE);

The type of EMPLOYEE in the first column of table EMP_PAY is PERSON, which is exactly what is shown in front Custom type created.

13. Domain.

A domain is a collection of valid data types that can be used. Domains are associated with data so that only specific data is accepted. After the domain is created, we can add constraints to the domain. Constraints work with data types to further restrict the data a field can accept. Fields are used similarly to custom types.

You can create a domain as follows:

CREATE DOMAIN MONEY_D AS NUMBER(0,2);

Add constraints to the domain as follows:

ALTER DOMAIN MONEy_D

ADD CONSTRAINT MONEY_CON

CHECK(VALUE>5);

Then reference the domain like this:

CREATE TABLE EMP_PAY

(EMP_ID NUMBER(9),

EMP_NAME VARCHER2(30),

PAY_RATE MONEY_D);

3. Summary

Sql has A variety of data types, these are not unfamiliar to anyone who has used other programming languages. Data types allow different types of data to be saved to the database, such as single characters, decimals, dates, and times. Whether you are using a third-generation programming language like C or using a relational database to implement SQL coding, the concept of data types is the same. Of course, the names of the data types vary from implementation to implementation, but the way they work is basically the same. In addition, a relational database management system does not have to be implemented for all data types specified in the ANSI standard to be considered compatible with ANSI, so it is best to check the specific documentation to understand the data types used.

Be sure to plan carefully for both the short and long term when considering data type, length, scale, and precision. In addition, company policies and how you want users to access data are also factors to consider. Developers should understand the nature of the data. and how data is related to each other in the database so that appropriate data types are used.

This article only describes the data types of Sql! Pure text description! This article is excerpted from "Sql Beginner's Classic". The next article explains how to get started with Sql (3) Managing database objects !

The above is the detailed content of How to understand the concept of data types in MySQL?. For more information, please follow other related articles on the PHP Chinese website!