Scribd
Upload
16 views14 pages

MySQL Numeric Data Types Explained

The document provides an overview of MySQL numeric data types, categorizing them into integer types, decimal/floating-point types, and bitwise/boolean types. It details the characteristics and use cases for each type, along with examples of SQL table creation. Additionally, it emphasizes best practices for using these data types, such as using DECIMAL for monetary values and utilizing UNSIGNED when negative values are not expected.

Uploaded by

sp2392546
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
16 views14 pages

MySQL Numeric Data Types Explained

The document provides an overview of MySQL numeric data types, categorizing them into integer types, decimal/floating-point types, and bitwise/boolean types. It details the characteristics and use cases for each type, along with examples of SQL table creation. Additionally, it emphasizes best practices for using these data types, such as using DECIMAL for monetary values and utilizing UNSIGNED when negative values are not expected.

Uploaded by

sp2392546
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd

MySQL Numeric Data Types

Understanding Integer, Decimal, and


Floating-Point Types
Categories of Numeric Data Types

• Integer Types: TINYINT, SMALLINT, INT, BIGINT


• Decimal/Floating-Point: FLOAT, DOUBLE,
DECIMAL
• Bitwise/Boolean: BIT, BOOLEAN (alias of
TINYINT(1))
Integer Types Overview

• TINYINT: 1 byte (-128 to 127 / 0 to 255


UNSIGNED)
• SMALLINT: 2 bytes (-32k to 32k)
• INT: 4 bytes (-2B to 2B)
• BIGINT: 8 bytes (~±9 quintillion)
Integer Type Use Cases

• TINYINT: Age, flags, ratings


• INT: IDs, counters, balances
• BIGINT: Bank accounts, precise timestamps
Integer Type Example

• CREATE TABLE students (


• id INT PRIMARY KEY,
• age TINYINT UNSIGNED
• );
Decimal & Floating-Point Types

• FLOAT(p): Approximate, 4 bytes


• DOUBLE: More accurate floating point, 8 bytes
• DECIMAL(p,s): Exact values, best for money
Decimal Example

• CREATE TABLE products (


• id INT,
• price DECIMAL(10, 2) -- Up to 99999999.99
• );
BOOLEAN and BIT Types

• BOOLEAN: Alias for TINYINT(1)


• BIT(n): Bitwise values, useful for permissions
or flags
BIT and BOOLEAN Example

• CREATE TABLE features (


• id INT,
• is_active BOOLEAN,
• permissions BIT(4)
• );
SIGNED vs UNSIGNED

• SIGNED: Allows negative values


• UNSIGNED: Only positive values, doubles
upper range
• Example: age TINYINT UNSIGNED (0–255)
Useful Numeric Functions

• ABS(x): Absolute value


• ROUND(x, d): Round to d decimals
• CEIL(x), FLOOR(x): Round up/down
• MOD(x, y): Modulo
Best Practices

• Use DECIMAL for money, not FLOAT or


DOUBLE
• Avoid large types (BIGINT) unless necessary
• Use UNSIGNED when no negatives are
expected
Practice Tasks (20 minutes)

• 1. Create a table with INT, FLOAT, DECIMAL


columns
• 2. Insert 3 rows with values like salary, tax,
bonus
• 3. Write queries to:
• - Round salary to 2 decimals
• - Add bonus to salary
• - Use MOD to check even/odd employee IDs
Questions?

• Clarify any confusion with numeric types or


SQL examples.

You might also like