Creation of MySQL Database:
1. Goto MySQL Command Line Client
2. Open the application and then Enter your password.
3. After that run the following command to create a database of
your choice.
Syntax: CREATE DATABASE DATABASE_NAME
Ex: CREATE DATABASE KMCE
4. Again run the SHOW Databases command and you will see
your database is created.
5. Run the following command to use the database you have just
created.
Syntax: USE DATABASE_NAME;
Example: USE KMCE
Creation of Table in MySQL:
The syntax is as follows:
CREATE TABLE table_name (
column1_name datatype[size] constraints,
column2_name datatype constraints,
...
columnN_name datatype constraints
);
Parameters:
column_name: This is the name of each column in the table.
datatype: This is the data type of the column (e.g., INT,
VARCHAR, DATE, etc.).
constraints: These are optional and define rules for the data in
the column, such as NOT NULL, UNIQUE, PRIMARY
KEY, FOREIGN KEY, etc.
Example:
CREATE TABLE STUDENT(
ROLL_NO INTEGER,
SNAME VARCHAR(15),
AGE INTEGER(2),
DEPT VARCHAR(10),
CLASS CHAR(3) );
Note: If any error shows for INTEGER then use INT.
To verify that the table was created successfully, you can use
the DESCRIBE statement to view the structure of the STUDENT
table:
Query:
DESCRIBE STUDENT;
This will display the structure of the STUDENT table, confirming that
it has been created with the specified columns.
Output:
Field Type Null Key Default Extra
REG_NO char(5) YES NULL
SNAME varchar(15) YES NULL
AGE int YES NULL
DEPT varchar(10) YES NULL
CLASS char(3) YES NULL
Output Explanation: This output displays the structure of
the STUDENT table, providing information about each column as
specified in the CREATE TABLE command.
By following these steps, you have successfully created a table in the
SQL command-line interface to store information about STUDENT.
The created table is now ready to be populated with data and used
in your MySQL database
INSERT INTO Statement
Let's look at Various ways of using the INSERT INTO statement,
exploring how it can be used to add data efficiently to our table.
1. Insert record in a single row
2. Insert record in multiple rows
Syntax:
There are two ways to write an INSERT INTO statement in MySQL.
1. The general syntax for inserting a single record into a MySQL table
using the SQL INSERT INTO command is as follows:
INSERT INTO table_name (column1, column2, ..., columnN)
VALUES (value1, value2, ..., valueN);
Ex:
CREATE TABLE employees (
name VARCHAR(50),
age INT,
department VARCHAR(50)
);
Syntax:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
Query:
-- Insert single record into the employees table
INSERT INTO employees (name, age, department)
VALUES ('John Doe', 30, 'IT');
-- Select all records from the employees table
select * from employees;
2. Use the following statement to insert multiple records with a
single command:
INSERT INTO table_name (column1, column2, ..., columnN)
VALUES
(value1_1, value1_2, ..., value1_N),
(value2_1, value2_2, ..., value2_N),
...,
(valueM_1, valueM_2, ..., valueM_N);
Example: MySQL INSERT INTO table Multiple Rows
MySQL allows inserting multiple rows of data in a single query. It is
reducing the overhead of executing multiple queries. The syntax is
similar to inserting a single row but with multiple sets of values
enclosed within parentheses and separated by commas
Syntax:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES
(value1_1, value1_2, value1_3, ...),
(value2_1, value2_2, value2_3, ...),
(value3_1, value3_2, value3_3, ...);
Query:
-- Insert multiple records into the employees table
INSERT INTO employees (name, age, department)
VALUES
('Jane Smith', 28, 'HR'),
('Michael Johnson', 35, 'Finance'),
('Emily Brown', 32, 'Marketing');
-- Select all records from the employees table
SELECT * FROM employees;
Output:
Example 3: MySQL INSERT INTO SELECT
Sometimes, We may need to insert data into a table based on the
results of a SELECT query. MySQL facilitates this through the INSERT
INTO SELECT statement. This statement selects data from one table
and inserts it into another
Syntax:
INSERT INTO table_name (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM another_table
WHERE condition;
Query:
-- Create the employees table
CREATE TABLE employees (
name VARCHAR(50),
age INT,
department VARCHAR(50)
);
-- Insert multiple records into the employees table
INSERT INTO employees (name, age, department)
VALUES
('Jane Smith', 28, 'HR'),
('Michael Johnson', 35, 'Finance'),
('Emily Brown', 32, 'Marketing');
-- Create the employees_backup table
CREATE TABLE employees_backup (
name VARCHAR(50),
age INT,
department VARCHAR(50)
);
-- Insert records into the employees_backup table where age > 30
INSERT INTO employees_backup (name, age, department)
SELECT name, age, department
FROM employees
WHERE age > 30;
-- Select all records from the employees table
SELECT * FROM employees_backup;
Output:
MySQL INSERT INTO DATE
Inserting date values into a MySQL database is a common
requirement.
YYYY:MM:DD
MySQL provides several date and time functions to handle date-
related operations efficiently.
To insert the current date into a date column, we can use
the NOW() function with MySQL Insert Date.
Syntax:
INSERT INTO table_name (date_column)
VALUES (NOW());
Query:
INSERT INTO orders (order_date)
VALUES (NOW());
Output:
2024-02-20 12:00:00