Built-in SQL Functions

String Manipulation

Concatenation The CONCAT() function links multiple strings into one.

-- Combining status codes and messages
SELECT CONCAT('Error:', 500, ' - ', 'Internal Server Error');

String Length The LENGTH() function returns the byte length of a string. Note that for UTF-8 encoded characters, a single Chinese character typically occupies 3 bytes.

-- Get byte length of 'data'
SELECT LENGTH('data');

-- Retrieve records where the identifier byte length is 9
SELECT * FROM users WHERE LENGTH(identifier) = 9;

String Extraction

• LEFT(str, num): Retrieves the specified number of characters from the start.
• RIGHT(str, num): Retrieves the specified number of characters from the end.
• SUBSTRING(str, pos, num): Extracts a substring starting from a specific position.

-- Get the first 3 characters
SELECT LEFT('System Admin', 3);

-- Get the last 5 characters
SELECT RIGHT('System Admin', 5);

-- Extract 4 characters starting from the 3rd position
SELECT SUBSTRING('Database System', 3, 4);

-- Extract the first character (surname) from the user table
SELECT LEFT(full_name, 1) FROM users;

Whitespace Removal

• LTRIM(str): Removes leading spaces.
• RTRIM(str): Removes trailing spaces.
• TRIM(str): Removes spaces from both ends.

SELECT TRIM('  unnecessary spaces  ');

Mathematical Operations

Rounding Numbers ROUND(number, decimals) rounds a value to the specified precision.

-- Round to integer
SELECT ROUND(19.86);

-- Round to 2 decimal places
SELECT ROUND(19.865, 2);

-- Calculate average score and round it
SELECT ROUND(AVG(score)) FROM exam_results;

Random Generation RAND() generates a random floating-point number between 0.0 and 1.0.

SELECT RAND();

-- Retrieve a random row from the products table
SELECT * FROM products ORDER BY RAND() LIMIT 1;

Date and Time Handling

Current Temporal Data

• CURRENT_DATE(): Returns the date (YYYY-MM-DD).
• CURRENT_TIME(): Returns the time (HH:MM:SS).
• NOW(): Returns both date and time.

SELECT CURRENT_DATE();
SELECT CURRENT_TIME();
SELECT NOW();

Stored Procedures

A stored procedure is a prepared SQL code block that can be saved and reused.

Creating a Procedure

DELIMITER //
CREATE PROCEDURE GetAllUsers()
BEGIN
    SELECT * FROM users;
END //
DELIMITER ;

Executing a Procedure

CALL GetAllUsers();

Deleting a Procedure

DROP PROCEDURE IF EXISTS GetAllUsers;

Views

Views are virtual tables created by the result-set of a SQL query. They simplify complex queries and provide a layer of abstraction. Note that views are generally read-only in this context.

Creating a View

-- Create a view for active administrators
CREATE VIEW active_admins AS
SELECT id, username, email 
FROM users 
WHERE role = 'admin' AND status = 'active';

Querying a View

-- Select all active admins
SELECT * FROM active_admins;

-- Filter further on the view
SELECT * FROM active_admins WHERE username LIKE 'A%';

Deleting a View

DROP VIEW IF EXISTS active_admins;

Transaction Control

Transactions ensure data integrity by treating a sequence of operations as a single unit. They follow the ACID principle (Atomicity, Consistency, Isolation, Durability).

Scenario: Fund Transfer When transferring funds between two accounts, both the deduction and the credit must succeed together. If one fails, the other must be reverted.

Transaction Commands

• START TRANSACTION or BEGIN: Initiates the transaction.
• ROLLBACK: Reverts all changes made in the current transaction.
• COMMIT: Saves all changes permanently to the database.

Example: Transaction Rollback

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
-- If an error occurs or logic dictates a revert
ROLLBACK;

Example: Transaction Commit

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
-- Confirm changes
COMMIT;

Database Indexing

Indexes are used to retrieve data from database tables more quickly. Without an index, the database performs a full table scan, which is inefficient for large datasets.

Creating Indexes

-- Index on a numeric column
CREATE INDEX idx_user_age ON users(age);

-- Index on a string prefix (first 10 characters)
CREATE INDEX idx_username ON users(username(10));

Usage Impact

-- This query utilizes the idx_user_age index
SELECT * FROM users WHERE age = 25;

-- This query runs slower if no index exists on the 'email' column
SELECT * FROM users WHERE email = 'test@example.com';

Managing Indexes

-- Display indexes for a table
SHOW INDEX FROM users;

-- Remove an index
DROP INDEX idx_user_age ON users;

Performance Considerations

• Pros: Significantly increases the speed of data retrieval.
• Cons: Decreases the speed of data insertion and updating (INSERT, UPDATE, DELETE) because the index must also be updated.
• Strategy: It is recommended to index columns frequently used in WHERE clauses. For bulk data imports, consider dropping indexes before insertion and recreating them afterward.

Command-Line Database Administration

Selecting a Database Before performing table operations, the target database must be selected.

USE app_database;

Database Management Commands

-- List all existing databases
SHOW DATABASES;

-- Create a new database with specific character encoding
CREATE DATABASE company_data DEFAULT CHARACTER SET utf8mb4;

-- Delete a database
DROP DATABASE company_data;