SQL data types (Data Types)

 

SQL basically has the following data types. We must understand which data type fields are used to store each type of data value in order to design the database.

• Numeric Types-11, 2.5492, -91
• String Types-'Learn SQL Language'
• NULL-(the field is empty)
• Boolean-true / false

Numeric Data

The numeric types of SQL include integer, float, money, etc. One advantage of using numeric data is that you can use built-in numeric functions for data processing. For example, the SUM() function can directly obtain the sum of the numeric fields.

Character & Strings Data

The data type for storing characters or symbols.

Date/time data (Date Data)

The data type used to record the date/time, including date, time, timestamp, etc.

Boolean Data

true/false, Yes/No, 1/0.

NULL Data

Null value, no data exists in the field. Usually when creating a data table, you can set whether the field allows null values.

CREATE TABLE customer (
  C_id INT PRIMARY KEY,
  Name VARCHAR(10) NOT NULL,
  Address VARCHAR(255) NULL,
  Phone VARCHAR(10) NULL
);

A NULL value and an empty string'' have different meanings. For example, the following SQL statement:

INSERT INTO customer (phone) VALUES (NULL);
INSERT INTO customer (phone) VALUES ('');

The first narrative sentence means that the phone number is unknown; the second one means that there is no phone.

In most databases, you cannot index fields that allow NULL values. You must declare the field to be indexed as NOT NULL. In addition, you cannot insert NULL into an indexed field.

It is also worth noting that the literal constant (Literal) of NULL has different meanings in some special situations:

1. If you insert NULL into the TIMESTAMP field in the data table, it represents the current date and time.
2. If you insert NULL into an AUTO_INCREMENT field, it represents the next number in the current sequence.