TheDeveloperBlog.com

Home | Contact Us

C-Sharp | Java | Python | Swift | GO | WPF | Ruby | Scala | F# | JavaScript | SQL | PHP | Angular | HTML

MySQL Create Table

MySQL create table for beginners and professionals with examples on CRUD, insert statement, select statement, update statement, delete statement, use database, keys, joins etc.

<< Back to MYSQL

MySQL CREATE TABLE

A table is used to organize data in the form of rows and columns and used for both storing and displaying records in the structure format. It is similar to worksheets in the spreadsheet application. A table creation command requires three things:

  • Name of the table
  • Names of fields
  • Definitions for each field

MySQL allows us to create a table into the database mainly in two ways:

  1. MySQL Command Line Client
  2. MySQL Workbench

MySQL Command Line Client

MySQL allows us to create a table into the database by using the CREATE TABLE command. Following is a generic syntax for creating a MySQL table in the database.

CREATE TABLE [IF NOT EXISTS] table_name(
	column_definition1,
	column_definition2,
	........,
	table_constraints
);

Parameter Explanation

The parameter descriptions of the above syntax are as follows:

Parameter Description
database_name It is the name of a new table. It should be unique in the MySQL database that we have selected. The IF NOT EXIST clause avoids an error when we create a table into the selected database that already exists.
column_definition It specifies the name of the column along with data types for each column. The columns in table definition are separated by the comma operator. The syntax of column definition is as follows:
column_name1 data_type(size) [NULL | NOT NULL]
table_constraints It specifies the table constraints such as PRIMARY KEY, UNIQUE KEY, FOREIGN KEY, CHECK, etc.

Example

Let us understand how to create a table into the database with the help of an example. Open the MySQL console and write down the password, if we have set during installation. Now open the database in which you want to create a table. Here, we are going to create a table name "employee_table" in the database "employeedb" using the following statement:

mysql> CREATE TABLE employee_table(
	id int NOT NULL AUTO_INCREMENT,
	name varchar(45) NOT NULL,
	occupation varchar(35) NOT NULL,
	age int NOT NULL,
	PRIMARY KEY (id)
);

NOTE:
1. Here, NOT NULL is a field attribute, and it is used because we don't want this field to be NULL. If we try to create a record with a NULL value, then MySQL will raise an error.
2. The field attribute AUTO_INCREMENT specifies MySQL to go ahead and add the next available number to the id field. PRIMARY KEY is used to define a column's uniqueness. We can use multiple columns separated by a comma to define a primary key.

Visual representation of creating a MySQL table:

MySQL CREATE TABLE

We need to use the following command to see the newly created table:

mysql> SHOW TABLES;

It will look like the below output:

MySQL CREATE TABLE

See the table structure:

We can use the following command to see the information or structure of the newly created table:

mysql> DESCRIBE employee_table;

It will look like this:

MySQL CREATE TABLE

Create Table Using MySQL Workbench

It is a visual GUI tool used to create databases, tables, indexes, views, and stored procedures quickly and efficiently. To create a new database using this tool, we first need to launch the MySQL Workbench and log in using the username and password that you want. It will show the following screen:

MySQL CREATE TABLE

Now do the following steps for table creation:

1. Go to the Navigation tab and click on the Schema menu. Here, we can see all the previously created databases. Now we are ready to select the database in which a table is created.

2. Select the database, double click on it, and we will get the sub-menu under the database. These sub-menus are Tables, Views, Functions, and Stored Procedures, as shown in the below screen.

MySQL CREATE TABLE

3. Select Tables sub-menu, right-click on it, and select Create Table option. We can also click on create a new table icon (shown in red rectangle) to create a table.

4. On the new table screen, we need to fill all the details to create a table. Here, we will enter the table name (for example, employee_table) and use default collation and engine.

5. Click inside the middle window and fill the column details. Here, the column name contains many attributes such as Primary Key(PK), Not Null (NN), Unique Index (UI), Binary(B), Unsigned Data type(UN), Auto Incremental (AI), etc. The following screen explains it more clearly. After filling all the details, click on the Apply button.

MySQL CREATE TABLE

6. As soon as you click on the Apply button, it will open the SQL statement window. Again, click on the Apply button to execute the statement and Finish button to save the changes.

MySQL CREATE TABLE

7. Now, go to the Schema menu and select the database which contains the newly created table, as shown in the screen below.

MySQL CREATE TABLE
Next TopicMySQL Alter Table




Related Links:


Related Links

Adjectives Ado Ai Android Angular Antonyms Apache Articles Asp Autocad Automata Aws Azure Basic Binary Bitcoin Blockchain C Cassandra Change Coa Computer Control Cpp Create Creating C-Sharp Cyber Daa Data Dbms Deletion Devops Difference Discrete Es6 Ethical Examples Features Firebase Flutter Fs Git Go Hbase History Hive Hiveql How Html Idioms Insertion Installing Ios Java Joomla Js Kafka Kali Laravel Logical Machine Matlab Matrix Mongodb Mysql One Opencv Oracle Ordering Os Pandas Php Pig Pl Postgresql Powershell Prepositions Program Python React Ruby Scala Selecting Selenium Sentence Seo Sharepoint Software Spellings Spotting Spring Sql Sqlite Sqoop Svn Swift Synonyms Talend Testng Types Uml Unity Vbnet Verbal Webdriver What Wpf