Detailed example of creating and deleting tables in MySQL

The table creation command requires:

• The name of the table
• Field Name
• Define each field (type, length, etc.)

grammar

Following is the general SQL syntax used to create a MySQL table:

CREATE TABLE table_name (column_name column_type);

Now, we will create the following tables in the test database.

create table tutorials_tbl(
  tutorial_id INT NOT NULL AUTO_INCREMENT,
  tutorial_title VARCHAR(100) NOT NULL,
  tutorial_author VARCHAR(40) NOT NULL,
  submission_date DATE,
  PRIMARY KEY ( tutorial_id )
);

Here, some data items need explanation:

The field uses the NOT NULL attribute because we do not want the value of this field to be NULL. Hence, if user will try to create a record with NULL value then MySQL will generate an error.

The AUTO_INCREMENT attribute of the field tells MySQL to automatically increment the id field to the next available number.

The keyword PRIMARY KEY is used to define this column as a primary key. You can define multiple columns to define a primary key by using commas to separate them.

1. Create a table through the command prompt

From the mysql> prompt, it is easy to create a MySQL table. Use the SQL command CREATE TABLE to create a table.

Example

Here is an example that creates a table: tutorials_tbl

root@host#mysql -u root -p
Enter password:
mysql> use TUTORIALS;
Database changed
mysql> CREATE TABLE tutorials_tbl(
  -> tutorial_id INT NOT NULL AUTO_INCREMENT,
  -> tutorial_title VARCHAR(100) NOT NULL,
  -> tutorial_author VARCHAR(40) NOT NULL,
  -> submission_date DATE,
  -> PRIMARY KEY ( tutorial_id )
  -> );
Query OK, 0 rows affected (0.16 sec)
mysql>

Note: MySQL does not terminate the command until a semicolon (;) is given to indicate the end of the SQL command.

It is very easy to delete existing MySQL tables but you have to be very careful while deleting any existing table as the data lost after deleting the table cannot be recovered.

grammar

This is the generic SQL syntax used to delete a MySQL table:

DROP TABLE table_name ;

1. Delete the table from the command prompt

This requires only executing the DROP TABLE SQL command at the MySQL> prompt.

Example

Here is an example, which will delete the table: tutorials_tbl

root@host#mysql -u root -p
Enter password:
mysql> use test;
Database changed
mysql> DROP TABLE tutorials_tbl
Query OK, 0 rows affected (0.8 sec)
mysql>

Summarize

The above is a detailed example of how to create and delete a table in MySQL. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!