- SELECT
Purpose: Retrieve or extract data from a database.
SQL SELECT Syntax
SELECT column_name,column_name
FROM table_name;
and
SELECT * FROM table_name;
Retrieve from all columns
>SELECT * FROM <tables>
Retrieve from selected columns
>SELECT <column 1>, <column 2> FROM <table>
Retrieve data satisfying a given condition
>SELECT <column> FROM <tables> WHERE <condition>
Retrieve data satisfying multiple conditions
>SELECT <column> FROM <tables> WHERE <condition> AND <condition>
Other SELECT purposes
- JOIN
Purpose: used to combine rows from two or more tables, based on a common field between them.
Different SQL JOINs
Before we continue with examples, we will list the types of the different SQL JOINs you can use:
- INNER JOIN: Returns all rows when there is at least one match in BOTH tables
- LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
- RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
- FULL JOIN: Return all rows when there is a match in ONE of the tables
The syntax of the MySQL INNER JOIN clause is as follows:
Example
SELECT
productCode, productName, textDescription
FROM
products T1
INNER JOIN
productlines T2 ON T1.productline = T2.productline;
- INSERT INTO
Purpose: The INSERT INTO statement is used to insert new records in a table.
SQL INSERT INTO Syntax
It is possible to write the INSERT INTO statement in two forms.
The first form does not specify the column names where the data will be inserted, only their values:
INSERT INTO table_name
VALUES (value1,value2,value3,...);
The second form specifies both the column names and the values to be inserted:
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
INSERT INTO teachers (id, name, age) VALUES
(NULL, ‘John’, ‘12’);
- UPDATE
Purpose: UPDATE statement is used to update records in a table.
SQL UPDATE Syntax
UPDATE table
SET column1 = expression1,
column2 = expression2,
...
[WHERE conditions];
OR
The syntax for the SQL UPDATE statement when updating a table with data from another table is:
UPDATE table1
SET column1 = (SELECT expression1
FROM table2
WHERE conditions)
[WHERE conditions];
- DELETE
Purpose: The DELETE statement is used to delete records in a table.
Syntax
Syntax
The syntax for the DELETE statement in SQL is:
DELETE FROM table [WHERE conditions];
- DROP
Purpose: The SQL DROP TABLE statement allows you to remove or delete a table from the SQL database.
DROP INDEX Syntax for MySQL:
ALTER TABLE table_name DROP INDEX index_name
OR
DROP TABLE table_name;
- CREATE TABLE
Purpose: The SQL CREATE TABLE statement allows you to create and define a table.
Syntax
The syntax for the CREATE TABLE statement in SQL is:
CREATE TABLE table_name ( column1 datatype [ NULL | NOT NULL ], column2 datatype [ NULL | NOT NULL ], ... );
