Data types in MySQL


Certainly! MySQL supports various data types that you can use to define columns in your tables. Here's a list of commonly used data types in MySQL:

Numeric Data Types

1. INT or INTEGER: A standard integer that can store values from -2147483648 to 2147483647.

- Example: `INT`

2. TINYINT A very small integer that can store values from -128 to 127.

- Example: `TINYINT`

3. SMALLINT: A small integer that can store values from -32768 to 32767.

- Example: `SMALLINT`

4. MEDIUMINT: medium-sized integer that can store values from -8388608 to 8388607.

- Example: `MEDIUMINT`

5.BIGINT:A large integer that can store values from -9223372036854775808 to 9223372036854775807.

- Example: `BIGINT`

6. DECIMAL or NUMERIC: Exact numeric data type where you specify the precision (total number of digits) and scale (number of digits after the decimal point).

- Example: `DECIMAL(10, 2)` (allows up to 10 digits with 2 decimal places)

7. FLOAT: A floating-point number that stores approximate numeric values.

- Example: `FLOAT`

8. DOUBLE: A double-precision floating-point number.

- Example: `DOUBLE`

Date and Time Data Types

9. DATE: A date value in YYYY-MM-DD format.

- Example: `DATE`

10. TIME: A time value in HH:MM:SS format.

- Example: `TIME`

11. DATETIME: A combined date and time value in YYYY-MM-DD HH:MM:SS format.

- Example: `DATETIME`

12. TIMESTAMP: A timestamp that stores the number of seconds since the Unix epoch (January 1, 1970, UTC).

- Example: `TIMESTAMP`

13. YEAR: A year value in YYYY or YY format.

- Example: `YEAR`

String Data Types

14. CHAR: A fixed-length string with a maximum length of 255 characters.

- Example: `CHAR(50)` (stores up to 50 characters)

15. VARCHAR: A variable-length string with a maximum length of 65535 characters.

- Example: `VARCHAR(255)` (stores up to 255 characters)

16.TEXT: A text string with a maximum length of 65535 characters.

- Example: `TEXT`

17. ENUM: A string object that can have only one value, chosen from a list of possible values.

- Example: `ENUM('Option1', 'Option2', 'Option3')`

18. SET: A string object that can have zero or more values, chosen from a list of possible values.

- Example: `SET('Value1', 'Value2', 'Value3')`

Other Data Types

19. BINARY: Similar to `CHAR` but stores binary byte strings.

- Example: `BINARY`

20. VARBINARY: Similar to `VARCHAR` but stores binary byte strings.

- Example: `VARBINARY`

21. BLOB: Binary large object that can store a large amount of binary data.

- Example: `BLOB`

22. JSON: Stores JSON (JavaScript Object Notation) data.

- Example: `JSON`

 Spatial Data Types (for Geographic Information Systems - GIS)

23. GEOMETRY: Stores geometric objects (points, lines, polygons).

- Example: `GEOMETRY`

24. POINT: Stores a single point in geographic coordinates.

- Example: `POINT`

25. LINESTRING: Stores a collection of points representing a line.

- Example: `LINESTRING`

26. POLYGON: Stores a polygon consisting of one exterior ring and zero or more interior rings.

- Example: `POLYGON`

Example Usage

Here's an example of creating a table using some of these data types:

CREATE TABLE ExampleTable (

id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(100),

age TINYINT,

height DECIMAL(5, 2),

birthdate DATE,

registration_datetime DATETIME,

is_active BOOLEAN,

profile_text TEXT,

image_data BLOB

);

```

This `ExampleTable` includes columns with various data types such as `VARCHAR`, `TINYINT`, `DECIMAL`, `DATE`, `DATETIME`, `BOOLEAN`, `TEXT`, and `BLOB`. Adjust the data types and sizes according to your specific requirements and the nature of the data you are storing.

Comments

Post a Comment

Popular posts from this blog

KTU DBMS LAB CSL 333 BTech S5 - Dr Binu V P

 About Me About the course Syllabus and Experiments **************************************** Introduction to RDBMS Working with MySQL ER Diagrams-MySQL Workbench Introduction to SQL Data types in MySQL Built-in Functions MySQL Aggregate Functions Enforcing rules using constraints MySQL -Lab Exercise ****************************************** Joining tables Order by, group by and having clause Nested Queries MySQL -Lab Exercise ******************************************* Insert Data from a CSV file Set Operations Queries using temporary tables TCL commands in MySQL DCL commands in MySQL Views and Assertions Library Database- Lab Exercise ********************************************* PL/SQL and Stored Procedures IF statement in PL/SQL CASE statement in PL/SQL Loops in PL/SQL Procedures in PL/SQL Functions in PL/SQL Triggers in PL/SQL Cursors in PL/SQL Packages in PL/SQL Exception Handling in PL/SQL Backing up and Restoring Databases Query Analysis using Query Plan ***********************
Read more

KTU DBMS LAB CSL 333 Syllabus and Experiments

 Syllabus 1. Design a database schema for an application with ER diagram from a problem description **. 2. Creation, modification, configuration, and deletion of databases using UI and SQL Commands **. 3. Creation of database schema - DDL (create tables, set constraints, enforce relationships, create indices, delete and modify tables). Export ER diagram from the database and verify relationships** (with the ER diagram designed in step 1). 4. Database initialization - Data insert, Data import to a database (bulk import using UI and SQL Commands)**. 5. Practice SQL commands for DML (insertion, updating, altering, deletion of data, and viewing/querying records based on condition in databases)**. 6. Implementation of built-in functions in RDBMS**. 7. Implementation of various aggregate functions in SQL**. 8. Implementation of Order By, Group By & Having clause **. 9. Implementation of set operators nested queries, and join queries **. 10. Implementation of queries using temp tables. 11
Read more

Creating a Database and Table ,executing queries - MySQL Lab Exercise

Create a database school and use it CREATE DATABASE school; USE school; Let's create a simple table schema for a students table in MySQL, followed by a set of queries to interact with the data. id : An integer that uniquely identifies each student (primary key). first_name : The student's first name. last_name : The student's last name. birthdate : The student's birth date. email : The student's email address. gender : The student's gender. enrollment_date : The date when the student was enrolled. CREATE TABLE students (     id INT AUTO_INCREMENT PRIMARY KEY,     first_name VARCHAR(50) NOT NULL,     last_name VARCHAR(50) NOT NULL,     birthdate DATE NOT NULL,     email VARCHAR(100) NOT NULL,     gender ENUM('Male', 'Female', 'Other') NOT NULL,     enrollment_date DATE NOT NULL ); check the schema desc students; Inserting Sample Data INSERT INTO students (first_name, last_name, birthdate, email, gender, enrollment_date) VALUES ('John&
Read more