Last Updated: 28 May, 2023
In a MySQL table, data is logically organized in a row-and-column format where each row represents a unique record and each column represents a field in the record.
To create a new table in MySQL, we can use the CREATE TABLE statement followed by the table name and the list of columns along with their data types and any constraints.
See the syntax and example below.
Syntax:
CREATE TABLE table_name ( column_definition1, column_definition2, ........, table_constraints );
Example:
CREATE TABLE student ( stu_id INT AUTO_INCREMENT PRIMARY KEY, stu_name VARCHAR(100) NOT NULL, class VARCHAR(30) NOT NULL, roll SMALLINT, age TINYINT, gender VARCHAR(1), date_of_admission DATE, remarks TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
Create a table with IF NOT EXISTS option
To create a table with the "IF NOT EXISTS" option, creates a new table with the specified name if it doesn't already exist. If the table already exists, then this statement will have no effect.
Syntax:
CREATE TABLE IF NOT EXISTS table_name ( column_definition1, column_definition2, ........, table_constraints );
Example:
CREATE TABLE IF NOT EXISTS student ( stu_id INT AUTO_INCREMENT PRIMARY KEY, stu_name VARCHAR(100) NOT NULL, class VARCHAR(30) NOT NULL, roll SMALLINT, age TINYINT, gender VARCHAR(1), date_of_admission DATE, remarks TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
Create a table with Primary ana Foreign Key
CREATE TABLE course ( course_id INT AUTO_INCREMENT, course_name VARCHAR(50) NOT NULL, course_duration VARCHAR (20), PRIMARY KEY (course_id), FOREIGN KEY (stu_id) REFERENCES student (stu_id) );
In this example, the "course" table has a primary key of "course_id", which uniquely identifies each course record. The course table also has a foreign key of "stu_id", which refers to the primary key in the student table that contains student records. This allows for the creation of a relationship between the two tables, where each student can have multiple courses.
That's all, guys. I hope this MySQL article is helpful for you.
Happy Learning... 😀
feedback@javabytechie.com