MYSQL-DATATYPES CONSTRAINTS
CLICK MYSQL-DATATYPES CONSTRAINTS
PHP PROGRAM
MYSQL DATABASE
DATATYPES INT BOOLEAN CONSTRAINTS –
PRIMARY KEY PART 12
What are different datatypes of MYSQL ?
What are different constraints on tables ?
MANY EXAMPLES ON SELECT QUERIES ARE GIVEN WITH SAMPLE TABLES..
MySQL, a widely used relational database management system, offers a variety of data types and constraints that are fundamental to storing and managing data effectively. Here’s an overview of its key data types and constraints:
- Integer Types: MySQL supports various integer types, such as
INT
,SMALLINT
,TINYINT
,MEDIUMINT
, andBIGINT
. These are used for storing whole numbers, both positive and negative. - Floating-Point and Decimal Types: For decimal numbers, MySQL provides
FLOAT
,DOUBLE
, andDECIMAL
data types.FLOAT
andDOUBLE
are approximate types, whileDECIMAL
is used for exact precision arithmetic, often in financial calculations. - Date and Time Types: MySQL includes several data types for date and time, such as
DATE
,TIME
,DATETIME
,TIMESTAMP
, andYEAR
. These types allow for the storage of dates, times, or both. - String Types: For textual data, MySQL offers types like
CHAR
(fixed length) andVARCHAR
(variable length). There are alsoTEXT
andBLOB
for storing large amounts of text or binary data, respectively. - ENUM and SET Types: The
ENUM
type is used for creating a field that can hold one of a specified set of values, whileSET
allows for the selection of multiple values from a predefined list. - Primary Key Constraint: This constraint uniquely identifies each record in a database table. It must contain unique values, and cannot contain
NULL
values. - Foreign Key Constraint: Used to link two tables together. It is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.
- Unique Constraint: Ensures that all values in a column are different. This provides a way to enforce the uniqueness of a data column other than the primary key.
- NOT NULL Constraint: By default, a column can hold
NULL
values. TheNOT NULL
constraint enforces a column to NOT acceptNULL
values. - Default Constraint: This constraint provides a default value for a column when none is specified. If no default value is set for a column, it defaults to
NULL
. - Check Constraint: The
CHECK
constraint ensures that all values in a column satisfy a specific condition. It’s used to limit the type of data that can go into a table. - Indexing: While not a data type or constraint, indexing is essential in MySQL for speeding up the retrieval of records from a database. An index in a database is similar to an index in the back of a book.
Understanding these data types and constraints is crucial for anyone working with MySQL, as they are fundamental to designing efficient and robust database schemas.