Create a Table in MySQL Database

In this tutorial, we will learn how to create a table in MySQL database. A database is like a container for database objects like tables, views, etc. We need to create a database before creating database tables. Steps to create a database in MySQL server:

https://www.testingdocs.com/create-a-new-mysql-database/

CREATE TABLE Example

Let’s look at an example. We use the CREATE TABLE SQL statement to create a table with the given name in the MySQL database. We can specify the default database using the USE statement.

MySQL database tables are created using the default InnoDB storage engine. We can find the default storage option in the my.ini configuration file.

We can specify the database name in the SQL statement. The sample table has two columns with different data types. The first column is an integer, and the second column is a string of characters.

 

CREATE TABLE IF NOT EXISTS testdb.supplier (
supplier_id int PRIMARY KEY,
supplier_name VARCHAR(40) NOT NULL);

 

Alternatively, we can connect to the database using the USE statement. For example, to connect to the testdb, we can use the following statement:

mysql> USE testdb

Statements

mysql> USE testdb;
Database changed
mysql> CREATE TABLE IF NOT EXISTS supplier (
-> supplier_id int PRIMARY KEY,
-> supplier_name VARCHAR(40) NOT NULL);
Query OK, 0 rows affected (0.67 sec)

mysql> SHOW TABLES;
+——————+
| Tables_in_testdb |
+——————+
| supplier |
+——————+
1 row in set (0.32 sec)

mysql>

Common Errors

Duplicate Table: An error occurs if the table already exists in the MySQL database.

No default database: Error occurs if there is no default database

No Database: An error occurs if the database does not exist.

Syntax Errors: The SQL statement has syntax errors.

—

MySQL Tutorials

MySQL Tutorials on this website:

https://www.testingdocs.com/mysql-tutorials-for-beginners/