03. Creating Databases & Tables.md

• To clear terminal, use

Ctrl + L

Showing Databases

• To see the databases we have:

show databases;

Creating Databases

• The general command for creating a database:

CREATE DATABASE <database name>;

• Example:

CREATE DATABASE my_data;

Dropping and Using databases

• To drop a database:

DROP DATABASE <database_name>;

• Example:

DROP DATABASE my_data;

This command will remove the all contents from the my_data database.

• To use a database:

USE <database_name>;

• Example:

USE my_data;

• To know we are at my_data:

SELECT database();

    +------------+
    | database() |
    +------------+
    | my_data    |
    +------------+
    1 row in set (0.00 sec)

Datatypes

---

• INT: A Whole number with a max (signed) value of 2147483647.
• varchar: A Variable-Length String

Refer official documentation to know more about datatypes in MySQL.

Tables

---

• A collection of related data held in a structured format within a database.
• Databases are made up of lots of tables.

Creating Tables

• General Command to create tables:

CREATE TABLE tablename 
(
  column_name data_type,
  column_name data_type
); 

• Example:

CREATE TABLE cats
(
  name varchar(50)
  age INT
);

• To view table

SHOW tables;

+------------+
| database() |
+------------+
| my_data    |
+------------+
1 row in set (0.00 sec)

• To view the columns from a specific table:

SHOW COLUMNS FROM <table_name>;

• Example:

SHOW COLUMNS FROM cats;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

• We can use

DESCRIBE <table_name>;

or

DESC <table_name>;

• For example

DESC cats;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Dropping Tables

• To drop a table:

DROP TABLE <table_name>;

Comments in SQL

• We can use two dashes -- for comments:

     This is not a Comment
  -- This is a Comment