[SQL]: Creating DB & Tables

SQL

09/20/2019

Creating DB

SQL

CREATE DATABASE <name>; -- <name> used for placeholder

For naming convention, use underscores or capitalizations
- Although allowed, do not use spaces in DB name
- Remember to be consistent with what you choose!
- Capitalization is not required, but can be a good practice to distinguish what comes from SQL

Deleting DB

SQL

DROP DATABASE <name>;

Show DB

SQL

show databases;

output

TEXT

+--------------------+
| Database           |
+--------------------+
| information_schema |
| animals            |
| performance_schema |
| sys                |
+--------------------+

Use DB

SQL

USE <DB name>;

Can be used to search data from one particular db

SQL

SELECT database();

Shows the current db being used
Shows NULL after you drop the currently used db
output:

TEXT

+------------+
| database() |
+------------+
| animals    |
+------------+

Creating Tables

SQL

CREATE TABLE <name>
    (
        column_name data_type,
        column_name data_type
    );

Example

SQL

CREATE TABLE dogs
    (
        name varchar(100),
        age INT
    );

Creates table with name dogs with two columns, name that accepts up to 100 characters and age as integer
For naming convention, pluralize the table name!

Check created table

SQL

SHOW TABLES;

output:

TEXT

+-------------------+
| Tables_in_animals |
+-------------------+
| dogs              |
+-------------------+

Check columns

SQL

SHOW COLUMNS FROM <tablename>;
DESC <tablename>; # 'describe' slightly different usage but shorter

Example:

SQL

SHOW COLUMNS FROM dogs;

Output:

TEXT

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(100) | YES  |     | NULL    |       |
| age   | int(11)      | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

Side note

For column_names there may be some SQL reserved words such as size. It's a good practice to make column name to dogSize or dog_size instead

Deleting Tables

SQL

DROP TABLE <tablename>; # Permanently remove table

Output after deleting dogs table:

TEXT

mysql> SHOW TABLES;
Empty set (0.00 sec)

WRITTEN BY

Ellis Min

Keeping a record

GitHub