SQL All Commands

 SQL All Commands

Here is a list of common MySQL commands, categorized by their usage. This covers creating, managing, and interacting with databases and tables.

1. Database Commands

• Create a new database:

  sql
  CREATE DATABASE database_name;
  

• Select a database to use:

  sql
  USE database_name;
  

• Show all databases:

  sql
  SHOW DATABASES;
  

• Delete a database:

  sql
  DROP DATABASE database_name;
  

2. Table Commands

• Create a new table:

  sql
  CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
  );
  

• Show all tables in the selected database:

  sql
  SHOW TABLES;
  

• Show table structure (schema):

  sql
  DESCRIBE table_name;
  

• Rename a table:

  sql
  RENAME TABLE old_table_name TO new_table_name;
  

• Drop a table:

  sql
  DROP TABLE table_name;
  

• Add a column to a table:

  sql
  ALTER TABLE table_name ADD column_name datatype;
  

• Modify a column in a table:

  sql
  ALTER TABLE table_name MODIFY column_name datatype;
  

• Drop a column from a table:

  sql
  ALTER TABLE table_name DROP COLUMN column_name;
  

3. Data Manipulation Commands

• Insert data into a table:

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

• Insert multiple rows:

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

• Select data from a table:

  sql
  SELECT column1, column2, ...
  FROM table_name
  WHERE condition;
  

• Select all data from a table:

  sql
  SELECT * FROM table_name;
  

• Update data in a table:

  sql
  UPDATE table_name
  SET column1 = value1, column2 = value2, ...
  WHERE condition;
  

• Delete data from a table:

  sql
  DELETE FROM table_name
  WHERE condition;
  

4. Index Commands

• Create an index:

  sql
  CREATE INDEX index_name
  ON table_name (column1, column2, ...);
  

• Drop an index:

  sql
  DROP INDEX index_name
  ON table_name;
  

5. Constraints

• Add a primary key constraint:

  sql
  ALTER TABLE table_name
  ADD PRIMARY KEY (column_name);
  

• Add a foreign key constraint:

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

• Drop a constraint:

  sql
  ALTER TABLE table_name
  DROP FOREIGN KEY fk_name;
  

6. Joins

• Inner Join:

  sql
  SELECT columns
  FROM table1
  INNER JOIN table2
  ON table1.column = table2.column;
  

• Left Join:

  sql
  SELECT columns
  FROM table1
  LEFT JOIN table2
  ON table1.column = table2.column;
  

• Right Join:

  sql
  SELECT columns
  FROM table1
  RIGHT JOIN table2
  ON table1.column = table2.column;
  

7. Aggregate Functions

• Count rows:

  sql
  SELECT COUNT(*)
  FROM table_name;
  

• Sum a column:

  sql
  SELECT SUM(column_name)
  FROM table_name;
  

• Average a column:

  sql
  SELECT AVG(column_name)
  FROM table_name;
  

• Max value in a column:

  sql
  SELECT MAX(column_name)
  FROM table_name;
  

• Min value in a column:

  sql
  SELECT MIN(column_name)
  FROM table_name;
  

8. User Management

• Create a new user:

  sql
  CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
  

• Grant privileges to a user:

  sql
  GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';
  

• Revoke privileges from a user:

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

• Show all users:

  sql
  SELECT user, host FROM mysql.user;
  

• Delete a user:

  sql
  DROP USER 'username'@'localhost';
  

9. Backup and Restore

• Backup a database (from the command line):

  bash
  mysqldump -u username -p database_name > backup.sql
  

• Restore a database (from the command line):

  bash
  mysql -u username -p database_name < backup.sql
  

10. Miscellaneous

• Show current user:

  sql
  SELECT USER();
  

• Show current date and time:

  sql
  SELECT NOW();
  

• Show server status:

  sql
  SHOW STATUS;
  

This is a broad overview of common MySQL commands that you'll frequently use in database operations.