MySQL Fundamentals: Installation, Database Management, and Core Operations

Introduction to Databases

A database is essentially a repository for storing and organizing data. In contrast to manual file management, database management systems (DBMS) provide a structured way to handle data persistence and retrieval through specialized commands.

Popular database management systems include MySQL, Oracle, SQLite, Access, and SQL Server. These applications serve two primary functions: storing data in files or memory, and executing commands to manipulate that data. Rather than writing code to read and write files directly, developers send SQL commands to these systems, which handle the underlying file operations.

SQL Overview

SQL (Structured Query Language) is the standardized language designed specifically for database communication. It encompasses commands for data manipulation, schema definition, access control, and various other database operations.

MySQL Installation

MySQL, developed by MySQL AB (now owned by Oracle), is one of the most widely used relational database management systems, particularly for web applications.

Windows Installation

  1. Download Visit the official MySQL download page and obtain MySQL Community Server 5.7.x or later.

  2. Extract Extract the downloaded archive to your desired location, such as C:\mysql-5.7.16-winx64.

  3. Initialize Navigate to the bin directory and run the initialization command:

    cd c:\mysql-5.7.16-winx64\bin
mysqld --initialize-insecure

  4. Start the Server

    cd c:\mysql-5.7.16-winx64\bin
mysqld

  5. Connect to the Server Since initialization was performed with out a root password, connect as follows:

    cd c:\mysql-5.7.16-winx64\bin
mysql -u root -p

    Press Enter when prompted for the password.

Configuration Improvements

Adding to System PATH Modify the system PATH variable to include the MySQL bin directory, enabling command execution from any terminal location.

Registering as a Windows Service Convert MySQL to a Windows service for convenient management:

"c:\mysql-5.7.16-winx64\bin\mysqld" --install

Service control commands:

net start mysql
net stop mysql

Linux Installation

yum install mysql-server
mysql.server start

Connecting:

mysql -h host -u user -p

Disconnecting:

QUIT

Database Operations

Display Databases

SHOW DATABASES;

Default databases include:

  • mysql - stores user permissions and crdeentials
  • test - testing environment
  • information_schema - contains metadata about the MySQL server

Create Database

CREATE DATABASE db_name DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

Select Database

USE db_name;
SHOW TABLES;

User Management

CREATE USER 'username'@'host_ip' IDENTIFIED BY 'password';
DROP USER 'username'@'host_ip';
RENAME USER 'username'@'host_ip' TO 'new_username'@'host_ip';
SET PASSWORD FOR 'username'@'host_ip' = PASSWORD('new_password');

User credentials are stored in the mysql.user table.

Permission Management

SHOW GRANTS FOR 'username'@'host_ip';
GRANT privileges ON database.table TO 'username'@'host_ip';
REVOKE privileges ON database.table FROM 'username'@'host_ip';
FLUSH PRIVILEGES;

Available Privileges:

  • ALL PRIVILEGES - full access excluding grant
  • SELECT, INSERT, UPDATE, DELETE - data manipulation
  • CREATE, DROP - schema modification
  • ALTER - table structrue changes
  • EXECUTE - stored procedures
  • INDEX - index management
  • REFERENCES - foreign key constraints

Scope Specifications:

  • db_name.* - all tables in a database
  • db_name.table_name - specific table
  • *.* - all databases and tables

Host Patterns:

  • 'username'@'192.168.1.%' - IP subnet access
  • 'username'@'%' - unrestricted access

Password Recovery

mysqld --skip-grant-tables
mysql -u root -p
UPDATE mysql.user SET authentication_string = PASSWORD('new_pass') WHERE user = 'root';
FLUSH PRIVILEGES;

Table Management

Create Table

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Column Constraints:

NOT NULL - prevents NULL values
NULL - allows NULL values
DEFAULT value - automatically applied if no value provided

CREATE TABLE staff (
    emp_id INT NOT NULL DEFAULT 0,
    dept_code INT NOT NULL
);

Auto Increment:

CREATE TABLE products (
    product_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100)
);

Modify auto_increment behavior:

SET SESSION auto_increment_increment = 2;
SET SESSION auto_increment_offset = 10;
SET GLOBAL auto_increment_increment = 2;

Primary Key:

CREATE TABLE orders (
    order_id INT NOT NULL AUTO_INCREMENT,
    total DECIMAL(10,2),
    PRIMARY KEY(order_id)
);

Composite primary key:

CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT,
    PRIMARY KEY(order_id, product_id)
);

Foreign Key:

CREATE TABLE categories (
    category_id INT NOT NULL PRIMARY KEY,
    category_name VARCHAR(50) NOT NULL
);

CREATE TABLE items (
    item_id INT NOT NULL PRIMARY KEY,
    item_name VARCHAR(100),
    category_id INT NOT NULL,
    CONSTRAINT fk_category FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

Drop Table

DROP TABLE table_name;

Truncate Table

DELETE FROM table_name;
TRUNCATE TABLE table_name;

Alter Table

ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name MODIFY COLUMN column_name new_datatype;
ALTER TABLE table_name CHANGE old_column_name new_column_name datatype;

ALTER TABLE table_name ADD PRIMARY KEY(column_name);
ALTER TABLE table_name DROP PRIMARY KEY;

ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY(column) REFERENCES parent_table(column);
ALTER TABLE table_name DROP FOREIGN KEY fk_name;

ALTER TABLE table_name ALTER column_name SET DEFAULT value;
ALTER TABLE table_name ALTER column_name DROP DEFAULT;

MySQL Data Types

Numeric Types

Type Range Description
TINYINT -128 to 127 (signed), 0-255 (unsigned) Small integer
INT -2.1B to 2.1B (signed), 0-4.3B (unsigned) Standard integer
BIGINT -9.2E18 to 9.2E18 (signed) Large integer
DECIMAL(m,d) Accurate decimals Exact numeric storage
FLOAT Single precision Approximate numeric
DOUBLE Double precision Approximate numeric 
INT(5) displays as 00002 when containing value 2

String Types

Type Max Length Description
CHAR(n) 255 Fixed-length string
VARCHAR(n) 65535 Variable-length string
TEXT 65535 Large text storage
MEDIUMTEXT 16M Extended text
LONGTEXT 4GB Maximum text storage

CHAR provides faster processing but consumes fixed storage; VARCHAR is space-efficient for variable-length data.

ENUM('small', 'medium', 'large') - single selection from predefined values
SET('option1', 'option2') - multiple selections from predefined values

Date/Time Types

Type Format Range
DATE YYYY-MM-DD 1000-01-01 to 9999-12-31
TIME HH:MM:SS -838:59:59 to 838:59:59
YEAR YYYY 1901 to 2155
DATETIME YYYY-MM-DD HH:MM:SS Full range with time
TIMESTAMP YYYYMMDDHHMMSS Unix epoch to 2038

Binary Types

TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB for binary data storage.

Data Manipulation (CRUD)

Insert

INSERT INTO employees (name, position) VALUES ('John Doe', 'Engineer');
INSERT INTO employees (name, position) VALUES ('Jane Smith', 'Manager'), ('Bob Wilson', 'Analyst');
INSERT INTO employees (name, position) SELECT name, position FROM contractors;

Delete

DELETE FROM employees;
DELETE FROM employees WHERE id = 5;
DELETE FROM employees WHERE department = 'Sales' AND status = 'Inactive';

Update

UPDATE employees SET position = 'Senior Engineer' WHERE id > 10;
UPDATE employees SET position = 'Lead', salary = 85000 WHERE department = 'R&D';

Select

SELECT * FROM employees;
SELECT name, department FROM employees;
SELECT id, name AS employee_name FROM employees WHERE id > 5;

Filtering and Conditions

SELECT * FROM employees WHERE salary > 50000 AND department != 'HR';
SELECT * FROM employees WHERE id BETWEEN 10 AND 20;
SELECT * FROM employees WHERE department IN ('Sales', 'Marketing', 'Support');
SELECT * FROM employees WHERE department NOT IN ('IT', 'Finance');

Subqueries in WHERE clause:

SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments WHERE budget > 100000);

Pattern Matching

SELECT * FROM employees WHERE name LIKE 'J%';  -- starts with J
SELECT * FROM employees WHERE name LIKE '_mith%';  -- second letter starts with mith

Result Limiting

SELECT * FROM employees LIMIT 10;  -- first 10 records
SELECT * FROM employees LIMIT 20, 10;  -- skip 20, take 10
SELECT * FROM employees LIMIT 10 OFFSET 20;  -- equivalent to above

Sorting

SELECT * FROM employees ORDER BY hire_date ASC;
SELECT * FROM employees ORDER BY salary DESC;
SELECT * FROM employees ORDER BY department ASC, salary DESC;

Grouping and Aggregation

SELECT department, COUNT(*) as headcount FROM employees GROUP BY department;
SELECT department, MAX(salary), MIN(salary), AVG(salary) FROM employees GROUP BY department;

HAVING filters grouped results (WHERE cannot filter aggregates):

SELECT department, COUNT(*) as count FROM employees GROUP BY department HAVING count > 3;
SELECT department, COUNT(*) FROM employees WHERE id > 0 GROUP BY department HAVING COUNT(*) > 1;

Advanced Queries

Joins

Cross Join:

SELECT * FROM employees, departments;
SELECT * FROM employees INNER JOIN departments ON employees.dept_id = departments.id;

Left Join - all records from left table:

SELECT e.name, d.title FROM employees e LEFT JOIN departments d ON e.dept_id = d.id;

Right Join - all records from right table:

SELECT e.name, d.title FROM employees e RIGHT JOIN departments d ON e.dept_id = d.id;

Multi-table Join:

SELECT s.student_name, sc.score, c.course_name, t.teacher_name
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id
LEFT JOIN course c ON sc.course_id = c.id
LEFT JOIN teacher t ON c.teacher_id = t.id;

Unions

SELECT name FROM current_employees
UNION
SELECT name FROM former_employees;

SELECT name FROM current_employees
UNION ALL
SELECT name FROM former_employees;

Unique Constraints

CREATE TABLE inventory (
    item_id INT,
    warehouse_code INT,
    quantity INT,
    UNIQUE KEY uk_item_warehouse (item_id, warehouse_code)
);

Unique indexes allow NULL values and can be composite; primary keys cannot be NULL.

Relationship Patterns

One-to-One

A user has exactly one profile:

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50)
);

CREATE TABLE profiles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    bio TEXT,
    user_id INT NOT NULL,
    UNIQUE uq_user (user_id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

One-to-Many

A department has multiple employees:

CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
);

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(id)
);

Many-to-Many

Users can access multiple servers, servers can be accessed by multiple users:

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

CREATE TABLE servers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    hostname VARCHAR(100)
);

CREATE TABLE user_server_access (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    server_id INT NOT NULL,
    UNIQUE KEY uk_access (user_id, server_id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (server_id) REFERENCES servers(id)
);

Tags: MySQL database sql RDBMS installation

Posted on Thu, 18 Jun 2026 16:16:11 +0000 by RockinPurdy

Hot Tags

©2026 Freaks City