jwallace.us

tech, tunes, and other stuff

Database 101

There are a few basic database operations that you should know:

Creating a database:

1
2

CREATE DATABASE <database_name>;
CREATE SCHEMA <database_name>;

Deleting a database:

1
2

DROP DATABASE <database_name>;
DROP SCHEMA <database_name>;

Creating database tables:

1
2
3
4
5
6

CREATE TABLE <table_name>;
CREATE TABLE <table_name> {
   <column_1_name> <column_1_type> [<column_1_attributes>],
   [... more columns ...],
   <column_X_name> <column_X_type> [<column_X_attributes>]
};

Deleting a table:

1

DROP TABLE <table_name>;

Creating database indices:

1

CREATE INDEX <index_name> ON <table_name> (<column_1>, ..., <column_X>);

Adding data:

1
2
3

INSERT INTO <table_name> VALUES (<value_1> [, <value_2>, ..., <value_X>]);
INSERT INTO <table_name> (<col_1> [, <col_2>, ..., <col_X>])
   VALUES (<value_1> [, <value_2>, ..., <value_X>]);

Modifying all data in a table:

1

UPDATE <table_name> SET <column_name> = <value>;`

Modifying selective data in a table:

1

UPDATE <table_name> SET <column_1> = <value_1>, <column_2> = <value_2> WHERE <key> = <value>;

Deleting all data from a table:

1

DELETE FROM <table_name>;

Deleting selective data from a table:

1

DELETE FROM <table_name> WHERE <key> = <value>;

Retrieving all data from a table:

1

SELECT * FROM <table_name>;

Retrieving selective data from a table:

1
2
3
4
5
6

SELECT * FROM <table_name> WHERE <column_name> = <value>;

SELECT * FROM <table_name> WHERE <column_name> = <value>
                           OR <column_name> = <value>;
SELECT * FROM <table_name> WHERE <column_name> = <value>
                           AND <column_name> = <value>;

Retrieving selective data from a table using a SQL INNER Join:

Inner joins return a record set by combining data from two different tables.

1
2

SELECT * FROM <table_1> INNER JOIN <table_2>
                        ON <table_1_column> = <table_2_column>;

Retrieving selective data from a table using SQL OUTER Joins:

There are two types of outer joins:  left joins and right joins.   Left joins return ALL of the records from the left table, combined with the selected data from the right table.  Right joins return ALL of the records from the right table, combined with the selected data from the left table.

1
2
3
4

SELECT * FROM <left_table> LEFT JOIN <right_table>
                           ON <left_column> = <right_column>;
SELECT * FROM <left_table> RIGHT JOIN <right_table>
                           ON <left_column> = <right_column>;

Transactions:

1
2
3
4
5
6

START TRANSACTION;
...do something here ...
ROLLBACK;
START TRANSACTION;
...do something here ...
COMMIT;