2. SQL Database (1) CREATE, DROP, ALTER

SQL CREATE DATABASE Statement

CREATE DATABASE: used to create a new SQL database

Syntax

CREATE DATABASE *databasename*;

---

SQL DROP DATABASE Statement

DROP DATABASE: to drop an existing SQL database

Syntax

DROP DATABASE *databasename;*

---

SQL CREATE TABLE Statement

CREATE TABLE: to create a new table in a database

CREATE TABLE *table_name* (
		column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

• column parameters specify the names of the columns of the table
• datatype parameters specify the type of the data the column can hold

Create table using another table

• the new table gets the same column definitions
• all columns or specific columns can be selected
• If you create a new table using an existing table, the new table will be filled with the existing values from the old table.

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

---

SQL DROP TABLE Statement

: used to drop an existing table in a database

DROP TABLE *table_name*;

TRUNCATE TABLE: to delete the data inside a table but not the table itself

TRUNCATE TABLE table_name;

---

SQL ALTER TABLE Statement

• to add, delete, or modify columns in an existing table
• to add and drop various constraints on an existing table

ADD column

ALTER TABLE *table_name*
ADD *column_name datatype*;

DROP column

ALTER TABLE *table_name*
DROP COLUMN *column_name*;

MODIFY column

ALTER TABLE *table_name*
MODIFY COLUMN *column_name datatype*;

---

This post is based on W3School's articles about SQL.