Database Concepts and Initialization

A database is a repository for structured data storage and management.

Service Management Commands

net start mysql
net stop mysql
mysqladmin -u root password new_password
mysql -u root -p

SQL Statement Categories

Data Definition Language (DDL)

CREATE DATABASE IF NOT EXISTS inventory CHARSET=utf8;
USE inventory;
ALTER DATABASE inventory CHARSET=utf8mb4;
DROP DATABASE IF EXISTS obsolete_db;

Table Operations

CREATE TABLE products (
  item_id INT PRIMARY KEY AUTO_INCREMENT,
  product_name VARCHAR(90) UNIQUE NOT NULL,
  price DECIMAL(10,2) DEFAULT 0.00
) ENGINE=InnoDB;

ALTER TABLE products ADD category VARCHAR(30) AFTER product_name;
ALTER TABLE products MODIFY product_name VARCHAR(120);
DROP TABLE deprecated_products;

Constraints

Data Types Overview

Column Properties

CREATE TABLE employees (
  emp_id INT AUTO_INCREMENT PRIMARY KEY,
  start_date DATE DEFAULT (CURRENT_DATE),
  notes TEXT COMMENT 'Employee records'
);

Key Management

ALTER TABLE orders ADD FOREIGN KEY (customer_id) 
REFERENCES customers(id) ON DELETE SET NULL;

Data Manipulation Language (DML)

INSERT INTO orders (product_id, quantity) 
VALUES (101, 5), (102, 3);

UPDATE inventory SET stock = stock - 5 
WHERE item_id = 101;

DELETE FROM sessions 
WHERE last_activity < NOW() - INTERVAL 30 DAY;

Data Query Language (DQL)

SELECT product_name, AVG(price) AS avg_price
FROM products
WHERE category = 'Electronics'
GROUP BY product_name
HAVING AVG(price) > 500
ORDER BY avg_price DESC
LIMIT 10;

Join Operations

SELECT c.name, o.order_date
FROM clients c
INNER JOIN orders o ON c.id = o.client_id
LEFT JOIN payments p ON o.id = p.order_id;

Subqueries

SELECT employee_name 
FROM staff 
WHERE department_id IN (
  SELECT department_id 
  FROM departments 
  WHERE location = 'New York'
);

Database Maintenance

mysqldump -u root -p inventory > backup.sql
mysql -u root -p inventory < restore.sql

Access Control

CREATE USER 'reports_user'@'%' IDENTIFIED BY 'secure_pass';
GRANT SELECT ON inventory.* TO 'reports_user'@'%';
REVOKE DELETE ON inventory.employees FROM 'reports_user'@'%';

Programmable Objects

DELIMITER //
CREATE FUNCTION calculate_discount(price DECIMAL, discount_rate DECIMAL) 
RETURNS DECIMAL DETERMINISTIC
BEGIN
  RETURN price * (1 - discount_rate);
END//
DELIMITER ;

CREATE TRIGGER update_inventory 
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  UPDATE products 
  SET stock = stock - NEW.quantity 
  WHERE product_id = NEW.product_id;
END;

Tranasctions

START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 101;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 202;
COMMIT;