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, and BIGINT. These are used for storing whole numbers, both positive and negative.
  • Floating-Point and Decimal Types: For decimal numbers, MySQL provides FLOAT, DOUBLE, and DECIMAL data types. FLOAT and DOUBLE are approximate types, while DECIMAL 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, and YEAR. These types allow for the storage of dates, times, or both.
  • String Types: For textual data, MySQL offers types like CHAR (fixed length) and VARCHAR (variable length). There are also TEXT and BLOB 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, while SET 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. The NOT NULL constraint enforces a column to NOT accept NULL 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.