Logo (2)
Database

MYSQL & MSSQL

  • April 7, 2025
  • Com 0

1. Introduction to MySQL & MSSQL

   1.1 MySQL

  • Open-source relational database management system (RDBMS).
  • Uses Structured Query Language (SQL).
  • Commonly used for web applications (e.g., WordPress, e-commerce platforms).

1.2 MSSQL (Microsoft SQL Server)

  • Developed by Microsoft, widely used for enterprise applications.
  • Supports T-SQL (Transact-SQL), an extension of SQL with procedural programming.
  • Integrates well with Microsoft Azure & .NET applications.

2. Basic SQL Commands

Operation MySQL Command MSSQL Command
Create Database CREATE DATABASE mydb; CREATE DATABASE mydb;
Use Database USE mydb; USE mydb;
Drop Database DROP DATABASE mydb; DROP DATABASE mydb;
Create Table CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50)); CREATE TABLE users (id INT PRIMARY KEY, name NVARCHAR(50));
Insert Data INSERT INTO users (id, name) VALUES (1, ‘John’); INSERT INTO users (id, name) VALUES (1, ‘John’);
Select Data SELECT * FROM users; SELECT * FROM users;
Update Data UPDATE users SET name = ‘Jane’ WHERE id = 1; UPDATE users SET name = ‘Jane’ WHERE id = 1;
Delete Data DELETE FROM users WHERE id = 1; DELETE FROM users WHERE id = 1;

MySQL uses VARCHAR, while MSSQL prefers NVARCHAR for Unicode support.

3. Data Types in MySQL vs. MSSQL

Category MySQL MSSQL
Integer INT, TINYINT, BIGINT INT, SMALLINT, BIGINT
Floating Point FLOAT, DOUBLE FLOAT, REAL
String VARCHAR, TEXT NVARCHAR, VARCHAR, TEXT
Date & Time DATE, DATETIME, TIMESTAMP DATE, DATETIME, DATETIME2
Boolean TINYINT(1) (0 = False, 1 = True) BIT (0 = False, 1 = True)

4. Joins in MySQL & MSSQL

     4.1 Types of Joins

  • INNER JOIN → Returns matching records from both tables.
  • LEFT JOIN → Returns all records from the left table & matching records from the right.
  • RIGHT JOIN → Returns all records from the right table & matching records from the left.
  • FULL JOIN → Returns all records when there is a match in either table (Only in MSSQL).

Inner Join Example

Copy Code 


SELECT u.id, u.name, o.order_id
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

MySQL & MSSQL support similar JOIN syntax.

5. Indexing for Performance

5.1 Why use Indexes?

  • Improves query performance by allowing faster data retrieval.
  • Reduces the need for full table scans.

Create Index

Copy Code 


CREATE INDEX idx_users_name ON users(name);

Check Index Usage (MSSQL Only)

Copy Code 


SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('users');

Best Practices:

  • Index columns used in WHERE, JOIN, and ORDER BY clauses.
  • Avoid too many indexes, which slow down INSERT/UPDATE operations.

6. Stored Procedures

6.1 Stored Procedures → Precompiled SQL statements for efficiency & security.

MySQL Stored Procedure

Copy Code 


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

MSSQL Stored Procedure

Copy Code 


CREATE PROCEDURE GetUsers
AS
BEGIN
   SELECT * FROM users;
END;

Stored procedures help encapsulate business logic inside the database.

7. Triggers for Automation

7.1 Triggers → Automatically execute an action before/after an event.

MySQL Trigger

Copy Code 


CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
SET NEW.created_at = NOW();

MSSQL Trigger

Copy Code 


CREATE TRIGGER trg_UserInsert
ON users
AFTER INSERT
AS
BEGIN
   PRINT 'New user added!';
END;

Use triggers wisely to avoid performance overhead.

8. Backup & Restore

MySQL Backup & Restore

Copy Code 


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

MSSQL Backup & Restore

Copy Code 


BACKUP DATABASE mydb TO DISK = 'C:backupmydb.bak';  -- Backup
RESTORE DATABASE mydb FROM DISK = 'C:backupmydb.bak';  -- Restore

Regular backups prevent data loss in case of failure.

9. Security Best Practices

9.1 Security Measures for MySQL & MSSQL

  • Use least privilege → Grant only necessary permissions.
  • Encrypt sensitive data using AES_ENCRYPT() (MySQL) or Transparent Data Encryption (MSSQL).
  • Use parameterized queries to prevent SQL Injection.

MySQL User Management

Copy Code 


CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT ON mydb.* TO 'admin'@'localhost';

MSSQL User Management

Copy Code 


CREATE LOGIN admin WITH PASSWORD = 'password';
CREATE USER admin FOR LOGIN admin;
GRANT SELECT, INSERT ON users TO admin;

Never use root or sa user in production.

10. Query Optimization & Performance Tuning

10.1 Optimize Queries for Speed

Optimization Technique Example
Use EXPLAIN (MySQL) or EXECUTION PLAN (MSSQL) EXPLAIN SELECT * FROM users;
Avoid SELECT *, fetch only required columns SELECT name FROM users;
Use indexes on frequently queried columns CREATE INDEX idx_name ON users(name);
Optimize JOINs by using indexes EXPLAIN SELECT u.id, o.order_id FROM users u JOIN orders o ON u.id = o.user_id;
Use LIMIT for pagination in MySQL SELECT * FROM users LIMIT 10 OFFSET 20;
Use TOP for pagination in MSSQL SELECT TOP 10 * FROM users;

Optimizing queries reduces response times & server load.

Download Elysium Spark Note