Structured Query Language

SQL is a declarative programming language that allows CRUD (Create, Read, Update & Delete) operations in relational database. It is primarily used in. It is used in the implementation stage of the database development lifecycle, as well as the general real-time use of an application.

More specifically, SQL is also a:

• Data Definition Language (DDL): Supports creation and definition of databases
• Data Manipulation Language (DML): Supports insertion, deletion and modification of data in databases
• Data Control Language (DCL): Supports access control in a database.

Data Definition §

CREATE §

• Used to create schemas, tables, views and more
• The most common usecase is CREATE TABLE to define a table:

CREATE TABLE [table_name] (
	[Column definition],
	[ (Complusory) Primary key definition(s)],
	[ (Optional) Foreign key definition(s)]
	[ (Optional) Constraints]
) [table_parameters];

A column definition defines a column/field:

[column_name] DATATYPE [NULL | NOT NULL] {column_options}

• AUTO_INCREMENT is a column option that automatically increments (usually used for the primary key)

A primary key definition defines a primary key

PRIMARY KEY (ColumnA, ColumnB, etc.)

A foreign key definition defines a foreign key:

FOREIGN KEY (ColumnA, ColumnB, etc.) REFERENCES [parent_table](ColumnC, ColumnD, etc.) [ (Optional) Referential Actions]

We can also enforce foreign key referential actions, that define what to do when the parent table gets modified:

• ON DELETE CASCADE: If a referenced record from parent_table is deleted, delete all records in this table that reference that record.
• ON DELET RESTRICT: Block deletion of any referenced records in parent_table

ALTER §

• Add or delete a column/attribute from the relation/table
• Used ADD to add a column and DROP to delete one:

--Add a new column
ALTER TABLE [table_name] ADD [column_name] [data_type];

--Delete a column
ALTER TABLE [table_name] DROP [column_name];

RENAME §

• Allows the renaming of relations.
• To rename columns, use AS

RENAME TABLE [table_name] TO [new_table_name];

Danger Zone

The two commands below are used to delete data and tables, and cannot be undone without rolling back from a backup! You have been warned.

TRUNCATE §

• Deletes all rows from a table
• Very fast, performance-wise
• Equivalent to DELETE * FROM TABLE

TRUNCATE TABLE [table_name];

DROP §

• Completely deletes it’s argument. Can delete entire databases, tables, columns, constraints and more.

DROP [DATABASE | TABLE | COLUMN | Constraint] [name];

Data Manipulation §

INSERT §

• Used to add rows/records/tuples to a table
• Three ways of inserting:
  • Insert by only providing values to certain columns. Every unprovided column value is given the NULL value
  • Insert by providing value to every single column
  • Insert rows from a SQL query with SELECT

--Inserting by only providing certain column values
INSERT INTO [table_name] (ColumnA, ColumnB, etc.) VALUES 
	--Row 1
	(Value1, Value2, etc.),
	--Row 2
	(Value1, Value2, etc.);
	--Multiple rows can be inserted at once.
	
--Inserting with every column value provided
INSERT INTO [table_name] VALUES
	--Row 1
	(Value1, Value2, etc.),
	--Row 2
	(Value1, Value2, etc.);
	--Multiple rows can be inserted at once.
 
--Inserts all rows from B into A. Tables A and B must have the same degree. 
INSERT INTO [table_A]
	SELECT * FROM [table_B]
	[Optional Query]

• DEFAULT can be used as a value if the column it is used for has a default constraint.

REPLACE §

• Almost identical to INSERT, except if any primary key value already exists, overwrites the row.

SELECT §

UPDATE §

• Can be used to modify existing column values
• To modify a row/record, use the WHERE clause (See SELECT)
• The CASE clause can be used here, which acts like a if-then-else statement

--Modify every row that contains column_name
UPDATE [table_name]
	SET [column_name] = [new_value];

--Modify only rows that satisfy condition
UPDATE [table_name]
	SET [column_name] = [new_value]
	WHERE [condition];

-- Modifies based on conditions
UPDATE [table_name]
	SET [column_name] =
	CASE
		WHEN [condition1] THEN [new_value1]
		WHEN [condition2] THEN [new_value2]
		ELSE [new_value3]
	END;

DELETE §

• Wipes all records from a table. but keeps the table intact.
• BE CAREFUL, use a WHERE clause to specify certain rows only.

--Delete every record from table_name
DELETE FROM [table_name];

--Delete only records that satisfy condition
DELETE FROM [table_name]
WHERE [condition];