Table Creation and Data Types

Table creation falls under DDL statements. Each database contains structural files for its tables:

SHOW TABLES;

Display table structure:

DESCRIBE table_name;
SHOW COLUMNS FROM table_name;

Field Types

Integer Types

• TINYINT (1 byte)
• SMALLINT (2 bytes)
• MEDIUMINT (3 bytes)
• INT (4 bytes)
• BIGINT (8 bytes)

Decimal Types

• FLOAT (4 bytes, ~7 digit precision)
• DOUBLE (8 bytes, ~15 digit precision)
• DECIMAL (variable precision)

Date and Time Types

• DATE (3 bytes)
• TIME (3 bytes)
• DATETIME (8 bytes)
• TIMESTAMP (4 bytes)
• YEAR (1 byte)

String Types

• CHAR (fixed length)
• VARCHAR (variable length)

Text and Binary Types

• TEXT (plain text)
• BLOB (binary data)

Enumeration Types

ENUM('value1', 'value2')

Set Types

SET('option1', 'option2')

Table Operations

Create table:

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Modify table:

ALTER TABLE users ADD COLUMN email VARCHAR(100);
ALTER TABLE users MODIFY username VARCHAR(75);
ALTER TABLE users DROP COLUMN email;

Remove table:

DROP TABLE IF EXISTS temp_data;

Data Manipulation Operations

Inserting Records

INSERT INTO products (name, price) VALUES ('Laptop', 999.99);

Bulk Insertion

INSERT INTO orders (customer_id, total)
SELECT customer_id, SUM(amount) FROM payments GROUP BY customer_id;

Updating Records

UPDATE inventory SET quantity = quantity - 1 WHERE item_id = 101;

Deleting Records

DELETE FROM logs WHERE created_at < '2023-01-01';

Database Constraints

Primary Key Constraints

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    full_name VARCHAR(100)
);

Foreign Key Constraints

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(cust_id)
);

Storage Engines

• InnoDB (transactional support)
• MyISAM (non-transactional)
• MEMORY (in-memory storage)

Transaction Management

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Isoaltion Levels

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

Index Optimization

Index Creation

CREATE INDEX idx_product_name ON products(name);

Index Removal

DROP INDEX idx_product_name ON products;

Index Analysis

EXPLAIN SELECT * FROM products WHERE name LIKE 'Apple%';

View Management

Creating Views

CREATE VIEW active_users AS 
SELECT * FROM users WHERE status = 'active';

Removing Views

DROP VIEW active_users;

Database Administration

Backup Procedures

mysqldump -u root -p inventory > inventory_backup.sql

User Privileges

GRANT SELECT, INSERT ON sales.* TO 'report_user'@'localhost';
REVOKE DELETE ON sales.* FROM 'report_user'@'localhost';

Database Design Principlse

Normalization Rules

1. Atomic values and primary keys
2. Full functional dependency
3. Transitive dependency elimination

Table Relationships

• One-to-one (shared primary key)
• One-to-many (foreign key)
• Many-to-many (junction table)

Variable Usage

System Variables

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Session Variables

SET @current_user_id = 42;

Flow Control Structures

Conditional Logic

IF temperature > 100 THEN
    UPDATE machines SET status = 'overheated';
END IF;

Iteration

WHILE counter < 10 DO
    SET counter = counter + 1;
END WHILE;

Function Implementation

Built-in Functions

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;
SELECT DATE_FORMAT(order_date, '%Y-%m-%d') FROM orders;

Custom Functions

DELIMITER $$
CREATE FUNCTION calculate_tax(amount DECIMAL(10,2)) 
RETURNS DECIMAL(10,2)
BEGIN
    RETURN amount * 0.07;
END$$
DELIMITER ;

Stored Procedures

DELIMITER $$
CREATE PROCEDURE update_inventory(IN item INT, IN qty INT)
BEGIN
    UPDATE stock SET quantity = quantity - qty WHERE product_id = item;
END$$
DELIMITER ;

Trigger Implementation

DELIMITER $$
CREATE TRIGGER log_changes 
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    INSERT INTO audit_log SET action = 'UPDATE', product_id = OLD.id;
END$$
DELIMITER ;