What is SQL (15)

Database

The previous explanations are all about tables. When several tables collect together, they become a database.

CREATE DATABASE Statement

To create a database, you need to use CREATE DATABASE statement.

Syntax:

CREATE DATABASE database_name;

DROP DATABASE Statement

To drop an existing database, you can use DROP DATABASE statement.

Syntax:

DROP DATABASE database_name;

All the information in the dropped database will be lost, so be careful before dropping a database.

SHOW DATABASE Statement

After creating or dropping a database, you may want to check it in the list of databases.

Syntax:

SHOW DATABASES (LIKE pattern);

The System will return a list of databases. If there is no LIKE clause, all the databases name will be listed.

CREATE TABLE Statement

After creating a database, it is time to create a table within the database. To create a new table, you can use CREATE TABLE statement.

Syntax:

CREATE TABLE Table_name (
    column1 datatype,
    column2 datatype,
    ...
);

Datatype will define the required data type within the column. You can find the complete list in W3school Website. It has data types and ranges for Microsoft Access, MySQL and SQL Serve. Different servers will have different data type name, so always check the document when set up a new table. Here are some data types for MySQL.

Text Types

• VARCHAR(size)
• TEXT

Number Types

• INT(size)
• FLOAT(size, d)

Date Types

• DATE()
• TIME()
• YEAR()

To make things easier, if you want to create using an existing table, you can use the combination of CREATE TABLE statement and the SELECT statement.

Syntax:

CREATE TABLE new_table_name AS 
    SELECT column1, column2, ...
    FROM existing_table_name
    WHERE condition;

DROP TABLE Statement & TRUNCATE TABLE Statement

To drop an existing table, you can use DROP TABLE statement.

Syntax:

DROP TABLE table_name;

To only delete the data inside a table not the table itself, you can use TRUNCATE TABLE Statement.

Syntax:

TRUNCATE TABLE table_name:

The data inside the table will be delete, but the format of the table will keep.

ALTER TABLE Statement

You have learnt to use INSERT INTO statement to add rows into a table. How about columns? ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

Syntax — Add Column

ALTER TABLE table_name
ADD column_name datatype;

Syntax — Drop Column

ALTER TABLE table_name
DROP COLUMN column_name;

Syntax — Modify Column

ALTER TABLE table_name
ALTER COLUMN column_name datatype.