SQL

SQL Commands Overview

Data Definition Commands

• CREATE: Make something new.

• DROP: Delete something (risky).

• ALTER: Change something that has already been created.

• ADD: Add something to an existing item.

Data Manipulation Commands

• INSERT INTO: Allows insertion of new records into a table.

  • Format: INSERT INTO table_name (columns) VALUES ('Your value');

• UPDATE: Lets you update existing records.

  • Uses SET to specify the changes.

• DELETE: Allows removal of records from a table.

  • Format: DELETE FROM table WHERE condition;

Query Commands

• SELECT: Retrieve data from a table.

  • Use * to select everything.

• FROM: Specify the table to select data from.

• WHERE: Add a condition to filter results.

  • Example: SELECT * FROM table WHERE column = 22;

• LIMIT: Limit the number of results returned.

• ORDER BY: Specify the order of the results.

  • ASC: Ascending order (default).

  • DESC: Descending order.

• DISTINCT: Select unique values only.

  • Example: SELECT DISTINCT name FROM table;

Advanced Query Clauses

• LIKE: Check if a column contains a specific substring.

  • Use % to represent any sequence of characters.

  • Example: SELECT * FROM table WHERE column_name LIKE '%dea%';

• AND: Combine multiple conditions.

  • Example: SELECT * FROM table WHERE column > 22 AND column2 < 33;

• BETWEEN: Check if a value falls within a range.

  • Example: SELECT * FROM table WHERE column BETWEEN 666 AND 999;

• IS NULL: Check if a column has no value.

  • Example: SELECT * FROM table WHERE release_year IS NULL;

Table Relationships

• JOIN: Combine rows from two or more tables based on related columns.

  • JOIN ON: Specifies the condition to join on.

  • Example: SELECT * FROM table1 JOIN table2 ON table1.column1 = table2.column2;

Additional Concepts

• AUTO_INCREMENT: Automatically increases a value in a column for new records.

• PRIMARY KEY: Uniquely identifies each row in a table; often used with AUTO_INCREMENT.

• FOREIGN KEY: A field in one table that uniquely identifies a row in another table.

• REFERENCES: Allows referencing another table's primary key.