Create Table

CREATE TABLE command is used to create a table in MySQL. You must make it sure that you define the name of the database when you create the connection.

Example

For creating a table named "employees".

Create a js file named employees.js having the following data in DBexample folder.

snippet

var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "12345",
database: "rookienerd"
});
con.connect(function(err) {
if (err) throw err;
console.log("Connected!");
var sql = "CREATE TABLE employees (id INT, name VARCHAR(255), age INT(3), city VARCHAR(255))";
con.query(sql, function (err, result) {
if (err) throw err;
console.log("Table created");
});
});

Now open command terminal and run the following command:

snippet

Node employees.js

Verification

To verify if the table is created or not, use the SHOW TABLES command.

You can also check the structure of the table using DESC command:

Create Table Having a Primary Key

Create Primary Key in New Table:

Let's create a new table named "employee2" having id as primary key.

Create a js file named employee2.js having the following data in DBexample folder.

snippet

var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "12345",
database: "rookienerd"
});
con.connect(function(err) {
if (err) throw err;
console.log("Connected!");
var sql = "CREATE TABLE employee2 (id INT PRIMARY KEY, name VARCHAR(255), age INT(3), city VARCHAR(255))";
con.query(sql, function (err, result) {
if (err) throw err;
console.log("Table created");
});
});

Now open command terminal and run the following command:

snippet

Node employee2.js

Verification

To verify if the table is created or not, use the SHOW TABLES command.

You can also check the structure of the table using DESC command to see that id is a primary key :

Add columns in existing Table:

ALTER TABLE statement is used to add a column in an existing table. Take the already created table "employee2" and use a new column salary.

Replace the data of the "employee2" table with the following data:

snippet

var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "12345",
database: "rookienerd"
});
con.connect(function(err) {
if (err) throw err;
console.log("Connected!");
var sql = "ALTER TABLE employee2 ADD COLUMN salary INT(10)";
con.query(sql, function (err, result) {
if (err) throw err;
console.log("Table altered");
});
});

Now open command terminal and run the following command:

snippet

Node employee2.js

Verification

A new column salary is created in the table employee2.