Structured Query Language

SQL is a declarative programming language that allows CRUD (Create, Read, Update & Delete) operations in relational databases. It is primarily used in Relational Database Management Systems (RDBMS). 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];