Month: April 2024

-- Create a new database
-- This creates a new database named "sandbox". This is not necessary if you already have a database to work with.
CREATE DATABASE database_name;

-- Use a specific database
-- This selects the "sandbox" database for use. Replace "sandbox" with the name of the database you want to use.
USE database_name;

-- Create a new table
-- This creates a new table named "table_name" with columns "column1" and "column2" of data types "datatype1" and "datatype2" respectively. Replace "table_name", "column1", "column2", "datatype1", and "datatype2" with the desired names and data types.
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);

-- Insert data into a table
-- This inserts a new row into the "table_name" table with values "value1" and "value2" for columns "column1" and "column2" respectively. Replace "table_name", "column1", "column2", "value1", and "value2" with the desired table name, column names, and values.
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

-- Select all rows from a table
-- This selects all rows from the "table_name" table. Replace "table_name" with the name of the table you want to select from.
SELECT * FROM table_name;

-- Select specific columns from a table
-- This selects specific columns "column1" and "column2" from the "table_name" table. Replace "column1", "column2", and "table_name" with the desired column names and table name.
SELECT column1, column2, ... FROM table_name;

-- Filter rows using WHERE clause
-- This selects all rows from the "table_name" table where the condition is met. Replace "table_name" with the name of the table you want to select from and "condition" with the desired condition.
SELECT * FROM table_name WHERE condition;

-- Update data in a table
-- This updates the "column1" and "column2" columns in the "table_name" table with new values "value1" and "value2" where the condition is met. Replace "table_name", "column1", "column2", "value1", "value2", and "condition" with the desired table name, column names, values, and condition.
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

-- Delete data from a table
-- This deletes rows from the "table_name" table where the condition is met. Replace "table_name" with the name of the table you want to delete from and "condition" with the desired condition.
DELETE FROM table_name WHERE condition;

-- Join two or more tables
-- This joins the "table1" and "table2" tables on the "column" column. Replace "table1", "table2", and "column" with the names of the tables and column you want to join on.
SELECT * FROM table1 JOIN table2 ON table1.column = table2.column;

-- Order rows in ascending or descending order
-- This selects all rows from the "table_name" table and orders them in ascending or descending order based on the "column" column. Replace "table_name" and "column" with the name of the table and column you want to order by, and "ASC" or "DESC" with the desired order.
SELECT * FROM table_name ORDER BY column ASC/DESC;

-- Limit the number of rows returned
-- This selects the first "number" rows from the "table_name" table. Replace "table_name" with the name of the table you want to select from and "number" with the desired number of rows.
SELECT * FROM table_name LIMIT number;

-- Group rows and perform aggregate functions
-- This groups rows in the "table_name" table by the "column" column and counts the number of rows in each group. Replace "table_name" and "column" with the name of the table and column you want to group by.
SELECT column, COUNT(*) FROM table_name GROUP BY column;

-- Create an index on a table
-- This creates an index named "index_name" on the "column" column in the "table_name" table. Replace "index_name", "table_name", and "column" with the desired index name, table name, and column.
CREATE INDEX index_name ON table_name (column);

-- Drop a table
-- This drops the "table_name" table. Replace "table_name" with the name of the table you want to drop. This is a destructive operation and cannot be undone.
DROP TABLE table_name;

-- Drop a database
-- This drops the "database_name" database. Replace "database_name" with the name of the database you want to drop. This is a destructive operation and cannot be undone.
DROP DATABASE database_name;

-- Show all databases
SHOW DATABASES;

-- Show all tables in a database
SHOW TABLES;

-- Show the structure of a table
-- This shows the columns, data types, and other information about the "table_name" table. Replace "table_name" with the name of the table you want to show the structure of.
DESC table_name;

-- Show the indexes on a table
-- This shows the indexes on the "table_name" table. Replace "table_name" with the name of the table you want to show the indexes of.
SHOW INDEX FROM table_name;

-- Show the status of the server
-- This shows various status information about the server, such as the number of connections, queries, and other statistics.
SHOW STATUS;

-- Show the current user
SELECT USER();

-- Show the current database
SELECT DATABASE();

-- Show the version of MySQL
SELECT VERSION();

-- Show the current date and time
SELECT NOW();

-- Show the current time zone
SELECT @@time_zone;

-- Join two or more tables
-- This joins the "table1" and "table2" tables on the "column" column.
-- A join combines rows from two or more tables based on a related column between them.
SELECT * FROM table1 JOIN table2 ON table1.column = table2.column;

-- Left join two or more tables
-- This left joins the "table1" and "table2" tables on the "column" column.
-- A left join returns all rows from the left table and the matched rows from the right table.
SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column;

-- Right join two or more tables
-- This right joins the "table1" and "table2" tables on the "column" column.
-- A right join returns all rows from the right table and the matched rows from the left table.
SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;

-- Full outer join two or more tables
-- This full outer joins the "table1" and "table2" tables on the "column" column.
-- A full outer join returns all rows when there is a match in either the left or right table.
SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column;

-- Cross join two or more tables
-- This cross joins the "table1" and "table2" tables.
-- A cross join returns the Cartesian product of the two tables, i.e., all possible combinations of rows.
SELECT * FROM table1 CROSS JOIN table2;