MySQL Data Types String Numeric Date & Time char varchar integer double date year MNS Education
Data Types
The data type of a column defines what value the column can hold: integer, character, float, date, time etc.
Each column in a database table is required to have a name & a data type.
An SQL developer must decide what type of data that will be stored inside each column when creating a table. The data type is a guideline for SQL to understand what type of data is expected inside of each column, and it also identifies how SQL will interact with the stored data.
In MySQL there are three main data types:
String,
Numeric, and
Date and time.
String Data Types
CHAR(size)
A FIXED length string (can contain letters, numbers, and special characters). The size parameter specifies the column length in characters - can be from 0 to 255. Default is 1
VARCHAR(size)
A VARIABLE length string (can contain letters, numbers, and special characters). The size parameter specifies the maximum column length in characters - can be from 0 to 65535.
Some OTHER are TINYTEXT, MEDIUMTEXT, LONGTEXT, BLOB, TINYBLOB ETC…!!!
Numeric Data Types
INT(size)
A medium integer. Signed range is from -2147483648 to 2147483647. Unsigned range is from 0 to 4294967295. The size parameter specifies the maximum display width (which is 255)
FLOAT(size, d)
A floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. This syntax is deprecated in MySQL 8.0.17, and it will be removed in future MySQL versions
DOUBLE(size, d)
A normal-size floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter
DECIMAL(size, d)
An exact fixed-point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. The maximum number for size is 65. The maximum number for d is 30. The default value for size is 10. The default value for d is 0.
Some OTHER are TINYINT, SMALLINT, MEDIUMINT, BIGINT, BOOLEAN, DOUBLEPRECISION ETC…!!!
Date and Time Data Types
DATE
A date. Format: YYYY-MM-DD. The supported range is from '1000-01-01' to '9999-12-31'
TIME(fsp)
A time. Format: hh:mm:ss. The supported range is from '-838:59:59' to '838:59:59'
FSP stands for Fractional Seconds Precision.
YEAR
A year in four-digit format. Values allowed in four-digit format: 1901 to 2155, and 0000. MySQL 8.0 does not support year in two-digit format.
Some OTHER are DATETIME AND TIMSTAMP.
Key aspects of a database in MySQL:
Structure:
Databases in MySQL are implemented as directories within the MySQL data directory, with individual files corresponding to tables and other objects within that database.
Organization:
Databases logically group related data, allowing for better management and retrieval. For example, a single application might use one database to store all its data, or a larger system might divide its data across multiple databases based on different functionalities.
Tables:
The core of a database is its tables, which store data in rows and columns, similar to a spreadsheet. Each table has a defined schema that dictates the data types and constraints for each column.
Schema:
A schema defines the structure of the database, including the tables, columns, data types, relationships between tables, and other constraints. In MySQL, the terms "database" and "schema" are often used interchangeably.
Management:
MySQL provides various commands and tools to manage databases, including:
CREATE DATABASE: To create a new database.
USE database_name: To select a specific database for subsequent operations.
SHOW DATABASES: To list all existing databases on the server.
DROP DATABASE: To delete a database.
ALTER DATABASE: To modify database characteristics, such as setting it to read-only.