.png)
"CREATE DATABASE Statement - SQL Database Management | SQL Tutorials"
The CREATE DATABASE statement is used to create a new SQL database.
Syntax
CREATE DATABASE databasename;CREATE DATABASE Student;The DROP DATABASE statement is used to drop an existing SQL database.
Syntax
DROP DATABASE databasename;Note: Be careful before dropping a database. Deleting a database will result in loss of complete information stored in the database!
The MySQL CREATE TABLE Statement
The CREATE TABLE statement is used to create a new table in a database.
Syntax
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);The column parameters specify the names of the columns of the table.
The datatype parameter specifies the type of data the column can hold (e.g. varchar, integer, date, etc.).
Tip: For an overview of the available data types, go to our complete Data Types Reference.
MySQL CREATE TABLE Example
The following example creates a table called "Persons" that contains five columns: PersonID, LastName, FirstName, Address, and City:
Example
CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);The PersonID column is of type int and will hold an integer.
The LastName, FirstName, Address, and City columns are of type varchar and will hold characters, and the maximum length for these fields is 255 characters.
The empty "Persons" table will now look like this:
| PersonID | LastName | FirstName | Address | City |
|---|---|---|---|---|
Tip: The empty "Persons" table can now be filled with data with the SQL INSERT INTO statement.
Create Table Using Another Table
A copy of an existing table can also be created using CREATE 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.
Syntax
CREATE TABLE new_table_name AS
SELECT column1, column2,...
FROM existing_table_name
WHERE ....;The following SQL creates a new table called "TestTables" (which is a copy of the "Customers" table):
Example
CREATE TABLE TestTable AS
SELECT customername, contactname
FROM customers;The MySQL DROP TABLE Statement
The DROP TABLE statement is used to drop an existing table in a database.
Syntax
DROP TABLE table_name;Note: Be careful before dropping a table. Deleting a table will result in loss of complete information stored in the table!
MySQL ALTER TABLE Statement
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
The ALTER TABLE statement is also used to add and drop various constraints on an existing table.
To add a column in a table, use the following syntax:
ALTER TABLE table_name
ADD column_name datatype;The following SQL adds an "Email" column to the "Customers" table:
Example
ALTER TABLE Customers
ADD Email varchar(255);To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):
ALTER TABLE table_name
DROP COLUMN column_name;The following SQL deletes the "Email" column from the "Customers" table:
Example
ALTER TABLE Customers
DROP COLUMN Email;ALTER TABLE - MODIFY COLUMN
To change the data type of a column in a table, use the following syntax:
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
