MySQL is an open-source relational database management system (RDBMS) used to store and manage data. It's widely used in web applications and supports SQL (Structured Query Language).

Installation Instructions 

Windows

1. Download MySQL Installer:
   • Visit: https://dev.mysql.com/downloads/installer/
   • Choose MySQL Installer for Windows (Full or Web)
2. Run Installer:
   • Choose “Developer Default” to install MySQL Server, Workbench, and Shell.
   • Follow the setup steps and set a root password.
3. Verify Installation:
   • Open MySQL Command Line Client or MySQL Workbench

   • Run:

     SELECT VERSION();

Linux (Ubuntu/Debian)

1. Install MySQL on Linux (Ubuntu/Debian based systems)

sudo apt update
sudo apt install mysql-server -y

This will install the latest MySQL server.

2. Secure the Installation

Run the security script to set root password and remove insecure defaults:

sudo mysql_secure_installation

You’ll be asked to configure things like:

• Validate password plugin (you can choose y or n)
• Set root password
• Remove anonymous users
• Disallow root remote login
• Remove test database

Set the root password and choose security settings.

To start mysql

sudo systemctl start mysql

To enable the boot

sudo systemctl enable mysql

Access mysql

sudo mysql -u root -p

Basic MySQL Commands

1. Login to MySQL

mysql -u root -p

2. Create a New Database

CREATE DATABASE test_db;

3. Show All Databases

SHOW DATABASES;

4. Use a Database

USE test_db;

5. Create a Table

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

6.Insert Data

INSERT INTO users (name, email)
VALUES ('Alice', '[email protected]');

7.Query Data

SELECT * FROM users;

8.create user

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password123';

9.Grant Permission

GRANT ALL PRIVILEGES ON test_db.* TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

10. Exit Mysql

EXIT;

Advance Commands

1.How to connect  mysql server

mysql -u databse_user_name -p -h host_ip_address -P host_port_number database_name

#example
mysql -u root -p -h localhost -P 3306 school_db

2.How to take backup for database

mysqldump -u database_username -p original_database_which_you_want_to_backup > database_backup_name.sql

#example
mysqldump -u root -p school_db > school_db_backup.sql

3.How to import the databse

mysql -u user_name -p database_name < database_name_you_want_to_import.sql

#example
create database school_db;
mysql -u root -p school_db < dummy.sql

• -u root ? connect as MySQL root user
• -p ? will ask for the root password
• school_db ? the database where you want to import data
• < dummy.sql ? takes the contents of dummy.sql and imports into MySQL

4.How to insert the data form source table to destination table

INSERT INTO destination_database_name.table_name (id,name,created_at,updated_at) SELECT id,name,created_at,updated_at FROM source_database_name;

#example
INSERT INTO production_school.category (id,name,created_at,updated_at) SELECT id,name,created_at,updated_at FROM testing_school.category;

5.command to set  existing value to new field in sql

UPDATE table_name
SET fieled_name_to_store_value  = existed_field_name_from_where_you_want_to_copy;


#example
UPDATE news 
SET created = created_at;

6.command to set  auto-increment value in sql

SET @sort_number := 0;

UPDATE table_name
SET filed_name_where_you_want_to_store = (SELECT @sort_number := @sort_number + 1)
ORDER BY some_ordering_column;


#example
SET @sort_number := 0;

UPDATE category
SET sort = (SELECT @sort_number := @sort_number + 1)
ORDER BY some_ordering_column;

Other userful commands

DROP DATABASE my_database;
DROP TABLE my_table;


RENAME TABLE hu44m1la_terms TO category;

ALTER TABLE category CHANGE term_id id INT;

ALTER TABLE category DROP COLUMN term_group;

ALTER TABLE category
ADD created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ADD updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;


ALTER TABLE category
ADD is_active BOOLEAN DEFAULT TRUE,
ADD category_color VARCHAR(255);

ALTER TABLE author DROP COLUMN user_pass;
ALTER TABLE author DROP COLUMN user_pass,user_nicename,user_login,user_activation_key,user_status,user_url,user_registered;