Using MySQL

Using MySQL | Generated by AI

Home PDF

Here’s a step-by-step guide to using MySQL, a popular open-source relational database management system:

1. Installation

Windows: Download the MySQL Installer from mysql.com/downloads

macOS:

brew install mysql
brew services start mysql

Linux (Debian/Ubuntu):

sudo apt update
sudo apt install mysql-server mysql-client
sudo systemctl start mysql
sudo mysql_secure_installation  # Run security setup

2. Connect to MySQL

Default root login:

mysql -u root -p  # Enter password when prompted

Connect to remote server:

mysql -u username -p -h hostname -P 3306

3. Basic Database Operations

Create User:

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';

Create Database:

CREATE DATABASE mydb;
GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost';
FLUSH PRIVILEGES;

Basic SQL Commands:

-- Create table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100) UNIQUE
);

-- Insert data
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');

-- Query data
SELECT * FROM users WHERE name LIKE 'B%';

-- Update data
UPDATE users SET email = 'new@email.com' WHERE id = 1;

-- Delete data
DELETE FROM users WHERE id = 2;

4. Advanced Features

Joins:

SELECT orders.id, users.name 
FROM orders
JOIN users ON orders.user_id = users.id;

Stored Procedures:

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

JSON Support (MySQL 5.7+):

CREATE TABLE products (
    id INT PRIMARY KEY,
    details JSON
);

5. Maintenance

Backup:
```
mysqldump -u root -p mydb > backup.sql
```
Restore:
```
mysql -u root -p mydb < backup.sql
```

Optimize Tables:

ANALYZE TABLE users;
OPTIMIZE TABLE users;

6. Security

Create Read-Only User:

CREATE USER 'reader'@'localhost' IDENTIFIED BY 'readpass';
GRANT SELECT ON mydb.* TO 'reader'@'localhost';
FLUSH PRIVILEGES;

7. Useful MySQL Commands

List databases: SHOW DATABASES;
Use a database: USE mydb;
List tables: SHOW TABLES;
Describe table: DESCRIBE users;
Show users: SELECT user FROM mysql.user;
Exit MySQL: EXIT; or QUIT;

8. GUI Tools

MySQL Workbench: Official GUI tool
phpMyAdmin: Web-based interface
DBeaver: Cross-platform database tool
HeidiSQL: Lightweight Windows client

9. Learning Resources

Official Documentation: dev.mysql.com/doc
Interactive Tutorial: mysqltutorial.org
Practice: sqlzoo.net or leetcode.com

Key Differences from PostgreSQL:

Uses AUTO_INCREMENT instead of SERIAL
Case-insensitive table names by default
Uses mysqldump instead of pg_dump
Default port: 3306 (vs PostgreSQL’s 5432)
Different authentication system and user management

Tip: Always back up databases before major changes. Use transactions (START TRANSACTION/COMMIT) for critical operations.

Back 2025.02.26 Donate