Data Representation Units

Computers use fundamental units for data storage and processing:

• Bit: Smallest storage unit (binary digit like 11001100)
• Byte: Basic data processing unit (1 Byte = 8 bits)
• Character: Human-readable symbols (letters, numbers, punctuation)

UTF-8 encoding examples:

• English characters: 1 byte
• Chinese characters: 3 bytes
• Punctuation: Size matches language

Table Creation

CREATE TABLE users (
  user_id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL DEFAULT 'guest',
  signup_date DATETIME,
  account_status ENUM('active','inactive','suspended')
) CHARSET=utf8;

Common data types:

• Integers: TINYINT, SMALLINT, INT, BIGINT (UNSIGNED for positive-only)
• Decimals: DECIMAL(total_digits, decimal_places) for precision
• Strings: VARCHAR(max_length) for variable text, CHAR(fixed_length)
• Dates: DATETIME for complete timestamps
• Enums: Restricted value lists

Data Insertion

INSERT INTO users (username, signup_date, account_status)
VALUES ('john_doe', NOW(), 'active'),
       ('jane_smith', '2023-05-15 14:30:00', 'active');

Data Modification

UPDATE users
SET account_status = 'inactive'
WHERE signup_date < '2022-01-01';

Data Retrieval

SELECT username, signup_date FROM users
WHERE account_status = 'active'
  AND signup_date BETWEEN '2022-01-01' AND '2023-12-31';

Advanced query techniques:

• DISTINCT for unique values
• IN() for multiple matches
• LIKE for pattern matching

Table Renaming

ALTER TABLE users RENAME TO members;

Table Deletion

DROP TABLE temporary_data;

Data Removal

DELETE FROM audit_log WHERE event_time < '2020-01-01';
TRUNCATE TABLE session_cache;

DELETE vs TRUNCATE differences:

• DELETE removes specific rows, maintains auto-increment
• TRUNCATE clears entire table, resets auto-increment

Schema Exploration

SHOW TABLES;
SHOW CREATE TABLE members;
CREATE TABLE new_users LIKE members;

Column Management

Adding columns:

ALTER TABLE members
ADD last_login DATETIME AFTER signup_date,
ADD login_count INT DEFAULT 0;

Modifying columns:

ALTER TABLE members
MODIFY username VARCHAR(75) NOT NULL,
CHANGE signup_date registration_date DATETIME;

Removing columns:

ALTER TABLE members
DROP COLUMN obsolete_field;