Creating and Inserting Data into a MySQL Table
Creating a MySQL Table
Before inserting data, you’ll need to create a table to hold it. Here’s how to create a table named
students.
SQL Query for Creating a Table:
CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), age INT, email VARCHAR(50));
Inserting Data Into the Created MySQL Table
Once the table is created, you can start inserting data into it. The
INSERT INTO statement is used for this purpose.
Example: Inserting Data Into the Students Table
To insert a single row of data into the
students table:
INSERT INTO students (name, age, email) VALUES ('Amit', 20, 'amit@email.com');
To insert multiple rows at once:
INSERT INTO students (name, age, email) VALUES
('Amit', 20, 'amit@email.com'),
('Bhavya', 22, 'bhavya@email.com'),
('Chetan', 23, 'chetan@email.com'),
('Divya', 21, 'divya@email.com'),
('Ekta', 20, 'ekta@email.com');
This will insert five rows into the
students table,
Inserting Additional Data Into the Students Table
You can continue to add more data to the existing
students table. Let’s add three more students with unique Indian names.
SQL Query for Inserting Additional Data:
INSERT INTO students (name, age, email) VALUES
('Faisal', 24, 'faisal@email.com'),
('Geeta', 25, 'geeta@email.com'),
('Harsh', 23, 'harsh@email.com');
This SQL command will insert three additional rows into the
students table,
Additional MySQL Commands with Examples
1. DESCRIBE Command
To see the structure of the table:
DESCRIBE students;
2. SELECT Command
To retrieve all data from the
students table:
SELECT * FROM students;
To retrieve specific columns:
SELECT name, age FROM students;
3. Ordering Columns
To order rows by the age column in ascending order:
SELECT * FROM students ORDER BY age ASC;
To order rows by the age column in descending order:
SELECT * FROM students ORDER BY age DESC;
4. Reordering Columns
MySQL doesn’t have a built-in command to reorder columns in an existing table. However, you can achieve this by creating a new table with the desired column order, and then copying the data over.
Creating a new table with reordered columns:
CREATE TABLE students_new (name VARCHAR(50), age INT, id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(50));
Copying data from the old table to the new table:
INSERT INTO students_new (id, name, age, email) SELECT id, name, age, email FROM students;
Explaining More MySQL Commands with Examples
1. WHERE Clause
To filter rows based on certain conditions, you can use the
WHERE clause:
SELECT * FROM students WHERE age >= 21;
2. Column Alias
To temporarily rename a column for the result set:
SELECT name AS 'Student Name', age AS 'Student Age' FROM students;
3. Inserting Text in Query Output
To insert static text into the query output:
SELECT 'Student Name is: ', name FROM students;
4. Relational Operators
To use relational operators for filtering:
SELECT * FROM students WHERE age > 20 AND age < 25;
5. Logical Operators
Using logical operators like
AND,
OR:
SELECT * FROM students WHERE age >= 20 AND (name = 'Amit' OR name = 'Bhavya');
6. Scalar Expressions
To perform operations on scalar values:
SELECT name, age, age + 5 AS 'Age in 5 years' FROM students;
Selecting Specific Rows and Using the ‘NOT’ Operator
1. Selecting Specific Rows
To select specific rows based on certain conditions:
SELECT * FROM students WHERE name IN ('Amit', 'Geeta', 'Harsh');
2. Using NOT Operator
The
NOT operator is used to negate a condition:
SELECT * FROM students WHERE NOT age <= 20;
Or you can combine it with
IN and
BETWEEN like so:
SELECT * FROM students WHERE name NOT IN ('Amit', 'Geeta');
SELECT * FROM students WHERE age NOT BETWEEN 21 AND 24;
