Comprehensive MySQL Command Guide

Table of Contents

1. Connecting to MySQL Server
2. Database Management
3. User Management
4. Table Management
   • Creating Tables
   • Modifying Tables
   • Deleting Tables
5. Data Manipulation (CRUD)
6. Querying Data
7. Joins
8. Indexing and Optimization
9. Stored Procedures and Functions
10. Triggers
11. Transactions
12. Backup and Restore
13. Security and Privileges
14. Performance and Monitoring
15. Resetting Root Password

Connecting to MySQL Server

• Connect to MySQL as root:

  mysql -u root -p

• Connect to MySQL with a specific user:

  mysql -u username -p

• Connect to a specific database:

  mysql -u username -p database_name

Database Management

• Show all databases:

  SHOW DATABASES;

• Create a new database:

  CREATE DATABASE database_name;

• Select a database to use:

  USE database_name;

• Drop (delete) a database:

  DROP DATABASE database_name;

• Show currently selected database:

  SELECT DATABASE();

• Rename a database (requires dump and restore):

  mysqldump -u username -p old_database > backup.sql
  mysql -u username -p -e "CREATE DATABASE new_database;"
  mysql -u username -p new_database < backup.sql

User Management

• Create a new user:

  CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
  CREATE USER 'newuser'@'%' IDENTIFIED BY 'securepassword';

• Show all users:

  SELECT user, host FROM mysql.user;
  SELECT User, Host, authentication_string, plugin FROM mysql.user;

• Grant privileges to a user:

  GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';
  GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost' WITH GRANT OPTION;

• Show user privileges:

  SHOW GRANTS FOR 'username'@'host';

• Revoke privileges from a user:

  REVOKE privilege_name ON database_name.* FROM 'username'@'host';
  REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'host';

• Change user password:

  ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

• Delete a user:

  DROP USER 'username'@'host';

• Apply changes:

  FLUSH PRIVILEGES;

Table Management

Creating Tables

• Show all tables in the selected database:

  SHOW TABLES;

• Create a new table:

  CREATE TABLE table_name (
    id INT AUTO_INCREMENT PRIMARY KEY,
    column1_name DATATYPE,
    column2_name DATATYPE
  );

• Create a table with a primary key:

  CREATE TABLE table_name (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    age INT
  );

• Create a table with a foreign key:

  CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
  );

• Create a temporary table:

  CREATE TEMPORARY TABLE temp_table_name (
    column1_name datatype constraints,
    column2_name datatype constraints
  );

• Show the structure of a table:

  DESCRIBE table_name;
  DESC table_name;

• Show table creation SQL:

  SHOW CREATE TABLE table_name;

Modifying Tables

• Add a new column:

  ALTER TABLE table_name ADD COLUMN column_name datatype constraints;

• Modify an existing column:

  ALTER TABLE table_name MODIFY COLUMN column_name new_datatype constraints;

• Rename a column:

  ALTER TABLE table_name CHANGE old_column_name new_column_name datatype constraints;

• Remove a column:

  ALTER TABLE table_name DROP COLUMN column_name;

• Rename a table:

  RENAME TABLE old_table_name TO new_table_name;
  ALTER TABLE old_table_name RENAME TO new_table_name;

• Modify a table's character set/collation:

  ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

• Add a primary key:

  ALTER TABLE table_name ADD PRIMARY KEY (column_name);

• Add a unique constraint:

  ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column_name);

• Add a foreign key:

  ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES other_table(column_name);

• Remove a constraint:

  ALTER TABLE table_name DROP CONSTRAINT constraint_name;

Deleting Tables

• Delete a table:

  DROP TABLE table_name;

• Delete multiple tables:

  DROP TABLE table1, table2;

• Delete a table only if it exists:

  DROP TABLE IF EXISTS table_name;

• Truncate (empty) a table:

  TRUNCATE TABLE table_name;

Data Manipulation (CRUD)

• Insert a new row:

  INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2');

• Insert multiple rows:

  INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2'), ('value3', 'value4');

• Example from provided files:

  INSERT INTO Artist (Artist_Name, Up_Date) VALUES ('John Doe', '2023-01-01');
  INSERT INTO Works (Artist_ID, Title, Rating, Code, Source) VALUES (1, 'Work A', 8.5, 'ABC123', 'Source A');

• Select all rows:

  SELECT * FROM table_name;

• Select specific columns:

  SELECT column1, column2 FROM table_name;

• Select with a condition:

  SELECT * FROM table_name WHERE column1 = 'value';

• Update records:

  UPDATE table_name SET column1 = 'new_value' WHERE column2 = 'condition_value';

• Delete records:

  DELETE FROM table_name WHERE column1 = 'value';

Querying Data

• Filter rows with WHERE clause:

  SELECT * FROM table_name WHERE column_name = 'value';

• Sort results (ascending/descending):

  SELECT * FROM table_name ORDER BY column1 ASC;
  SELECT * FROM table_name ORDER BY column1 DESC;

• Limit results:

  SELECT * FROM table_name LIMIT 10;

• Count rows:

  SELECT COUNT(*) FROM table_name;

• Group by a column:

  SELECT column1, COUNT(*) FROM table_name GROUP BY column1;

• Find unique values:

  SELECT DISTINCT column_name FROM table_name;

• Subqueries:

  SELECT column_name FROM table_name WHERE column_name IN (SELECT column_name FROM another_table);

Joins

• Inner Join (return rows when there is a match in both tables):

  SELECT a.column1, b.column2 
  FROM table1 a 
  INNER JOIN table2 b ON a.id = b.id;

• Left Join (return all rows from the left table, and matching rows from the right table):

  SELECT a.column1, b.column2 
  FROM table1 a 
  LEFT JOIN table2 b ON a.id = b.id;

• Right Join (return all rows from the right table, and matching rows from the left table):

  SELECT a.column1, b.column2 
  FROM table1 a 
  RIGHT JOIN table2 b ON a.id = b.id;

• Full Outer Join (using UNION in MySQL):

  SELECT a.*, b.*
  FROM table1 a
  LEFT JOIN table2 b ON a.id = b.id
  UNION
  SELECT a.*, b.*
  FROM table1 a
  RIGHT JOIN table2 b ON a.id = b.id
  WHERE a.id IS NULL;

• Example from provided files:

  SELECT Artist.Artist_ID, Artist.Artist_Name, Artist.Up_Date,
         Works.Title, Works.Rating, Works.Code, Works.Source
  FROM Artist
  LEFT JOIN Works ON Artist.Artist_ID = Works.Artist_ID;

Indexing and Optimization

• Create an index:

  CREATE INDEX index_name ON table_name (column_name);

• Create a unique index:

  CREATE UNIQUE INDEX index_name ON table_name (column_name);

• Show indexes:

  SHOW INDEX FROM table_name;

• Delete an index:

  DROP INDEX index_name ON table_name;

• Check the size of a table:

  SELECT table_name, round(((data_length + index_length) / 1024 / 1024), 2) AS size_MB 
  FROM information_schema.TABLES 
  WHERE table_schema = 'database_name';

• Analyze a table:

  ANALYZE TABLE table_name;

• Optimize a table:

  OPTIMIZE TABLE table_name;

Stored Procedures and Functions

• Create a stored procedure:

  DELIMITER //
  CREATE PROCEDURE procedure_name ()
  BEGIN
    -- SQL statements here
  END //
  DELIMITER ;

• Example of a procedure with parameters:

  DELIMITER //
  CREATE PROCEDURE GetCustomerOrders(IN customerId INT)
  BEGIN
    SELECT * FROM orders WHERE customer_id = customerId;
  END //
  DELIMITER ;

• Call a stored procedure:

  CALL procedure_name();
  CALL GetCustomerOrders(123);

• Create a function:

  DELIMITER //
  CREATE FUNCTION function_name(param1 DATATYPE) RETURNS RETURN_TYPE
  BEGIN
    -- Function logic
    RETURN value;
  END //
  DELIMITER ;

• Example of a function:

  DELIMITER //
  CREATE FUNCTION CalculateDiscount(price DECIMAL(10,2), discount_rate DECIMAL(5,2)) RETURNS DECIMAL(10,2)
  BEGIN
    DECLARE discounted_price DECIMAL(10,2);
    SET discounted_price = price - (price * discount_rate / 100);
    RETURN discounted_price;
  END //
  DELIMITER ;

• Using a function:

  SELECT product_name, price, CalculateDiscount(price, 10) AS discounted_price FROM products;

• Drop a stored procedure:

  DROP PROCEDURE IF EXISTS procedure_name;
  DROP PROCEDURE IF EXISTS GetArtistDetails;

• Drop a function:

  DROP FUNCTION IF EXISTS function_name;

Triggers

• Create a trigger:

  CREATE TRIGGER trigger_name BEFORE INSERT ON table_name
  FOR EACH ROW BEGIN
    -- SQL statements here
  END;

• Example of a trigger:

  DELIMITER //
  CREATE TRIGGER before_employee_update
  BEFORE UPDATE ON employees
  FOR EACH ROW
  BEGIN
    INSERT INTO employees_audit
    SET action = 'update',
        employee_id = OLD.id,
        changed_by = USER(),
        change_date = NOW();
  END //
  DELIMITER ;

• Show triggers:

  SHOW TRIGGERS;

• Drop a trigger:

  DROP TRIGGER trigger_name;

Transactions

• Start a transaction:

  START TRANSACTION;

• Commit changes:

  COMMIT;

• Rollback changes:

  ROLLBACK;

• Set autocommit mode off:

  SET autocommit = 0;

Backup and Restore

• Backup a database:

  mysqldump -u username -p database_name > backup.sql

• Backup all databases:

  mysqldump -u username -p --all-databases > all_databases_backup.sql

• Restore a database:

  mysql -u username -p database_name < backup.sql

• Restore all databases:

  mysql -u username -p < all_databases_backup.sql

Security and Privileges

• Show user privileges:

  SHOW GRANTS FOR 'username'@'host';

• Grant a specific privilege:

  GRANT SELECT ON database_name.* TO 'username'@'host';

• Revoke a specific privilege:

  REVOKE SELECT ON database_name.* FROM 'username'@'host';

• Remove all privileges:

  REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'host';

• Check current user:

  SELECT USER();

Performance and Monitoring

• Show running processes:

  SHOW PROCESSLIST;

• Kill a query:

  KILL process_id;

• Show table status:

  SHOW TABLE STATUS;

• Show all variables:

  SHOW VARIABLES;

Resetting Root Password

Resetting Root Password on Linux (Ubuntu/Debian)

1. Stop the MySQL Service

   sudo systemctl stop mysql
   

   or for older versions:

   sudo service mysql stop
   

2. Start MySQL in Safe Mode (Skip Authentication)

   sudo mysqld_safe --skip-grant-tables --skip-networking &
   

   or:

   sudo mysqld --skip-grant-tables --skip-networking &
   

3. Log in Without a Password

   mysql -u root
   

4. Reset the Root Password For MySQL 5.7+ or MariaDB:

   ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewStrongPassword';
   FLUSH PRIVILEGES;

   For MySQL 5.6 and below:

   SET PASSWORD FOR 'root'@'localhost' = PASSWORD('NewStrongPassword');
   FLUSH PRIVILEGES;

5. Restart MySQL Normally

   sudo systemctl stop mysql
   sudo systemctl start mysql
   

   or:

   sudo service mysql restart
   

Resetting Root Password on CentOS/RHEL

1. Stop MySQL Service

   sudo systemctl stop mysqld
   

2. Start MySQL Without Authentication

   sudo mysqld_safe --skip-grant-tables --skip-networking &
   

3. Reset Root Password

   mysql -u root
   ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewStrongPassword';
   FLUSH PRIVILEGES;
   

4. Restart MySQL

   sudo systemctl restart mysqld
   

Resetting MySQL Root Password Using --init-file

1. Create a File with the Password Update Command

   echo "ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewStrongPassword';" > /tmp/mysql-init
   

2. Restart MySQL with the Init File

   sudo systemctl stop mysql
   sudo mysqld --init-file=/tmp/mysql-init --skip-networking &
   

3. Restart Normally

   sudo systemctl restart mysql
   

Resetting Root Password on Windows

1. Stop the MySQL Service

   net stop mysql
   

2. Start MySQL Without Authentication

   mysqld --skip-grant-tables
   

3. Log in and Reset Password

   mysql -u root
   ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewStrongPassword';
   FLUSH PRIVILEGES;
   

4. Restart MySQL

   net start mysql