SQL Table

Creating a basic table entails naming it, defining its columns, and specifying the data type for each column.

To create a new table, use the SQL CREATE TABLE statement.

Syntax

The basic syntax of the CREATE TABLE statement is as follows −

CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ..... columnN data type, PRIMARY KEY( one or more columns ) );

The keyword CREATE TABLE tells the database system what you want to accomplish. You want to make a new table in this scenario. The CREATE TABLE statement is followed by the table's unique name or identifier.

Then there's a list in brackets that defines each column in the table, and it's a data type. With the following example, the syntax becomes more evident.

The Generate TABLE statement and the SELECT query can create a clone of an existing table. You may find more information at Create Table Using Another Table.

Example

The following code block creates a CUSTOMERS table with an ID as the primary key and NOT NULL constraints indicating that these values cannot be NULL while creating records in this table.

mysql> create table customers(cus_id int(6), cus_name varchar(20), cus_age int(3),salary float(8.2), primary key(cus_id) );

Look at the message given by the SQL server to see if your table was successfully created; otherwise, use the DESC command as follows: SQL> DESC CUSTOMERS;

Image Not found

Drop Table

The SQL DROP TABLE statement is used to delete a table definition and all associated data, indexes, triggers, constraints, and permissions.

NOTE: You should exercise extreme caution when using this command because once a table is removed, all of the data in that table is permanently lost.

The following is the fundamental syntax for the DROP TABLE statement:

TABLE table name; DROP TABLE

Example

Let's first double-check the CUSTOMERS table before deleting it from the database, as seen below.

Image Not found

This indicates that the CUSTOMERS table exists in the database; therefore, we will drop it as described below.

DROP TABLE CUSTOMERS; QUERY SQL> OK, there are no rows that have been affected (0.01 sec)

If you try the DESC command now, you will receive the following error:

ERROR 1146 (42S02): SQL> DESC CUSTOMERS TEST.CUSTOMERS is a table that does not exist.

droap table: mysql> drop table customers; mysql> describe customers; ERROR 1146 (42S02): Table 'students.customers' doesn't exist