Queries..
MYSQL QUERIES
1. Insert query
2. Select query
3. Update query
4. Delete query
1. Insert Record
- MySQL INSERT statement is used to store or add data in MySQL table within the database.
1. If we want to store single records for all fields, use the syntax as follows:
Ex :
INSERT INTO People (id, name, occupation, age)
VALUES (101, 'Peter', 'Engineer', 32);
2. If we want to store multiple records, use the following statements
Ex :
INSERT INTO People VALUES
(102, 'Joseph', 'Developer', 30),
(103, 'Mike', 'Leader', 28),
(104, 'Stephen', 'Scientist', 45);
2. Select Query
- The SELECT statement in MySQL is used to fetch data from one or more tables.
- We can retrieve records of all fields or specified fields that match specified criteria using this statement.
1. If we want to retrieve a single column from the table, we need to execute the below query:
mysql> SELECT name FROM People;
2. If we want to query multiple columns from the table, we need to execute the below query:
mysql> SELECT name, occupation FROM People;
3. If we want to fetch data from all columns of the table
mysql> SELECT * FROM People;
3. Update query
- MySQL UPDATE query is a DML statement used to modify the data of the MySQL table within the database.
- The UPDATE statement is used with the SET and WHERE clauses.
- The SET clause is used to change the values of the specified column. We can update single or multiple columns at a time.
1. Update Single Column
This query will update the email id of Java course with the new id as follows:
UPDATE People
SET name = 'abc'
WHERE id= '101';
2.Update Multiple Columns
The UPDATE statement can also be used to update multiple columns by specifying a comma-separated list of columns. Suppose we have a table as below:
MySQL UPDATE Query
This statement explains will update the name and occupation whose id = 101 in the People table as follows:
UPDATE People
SET name = 'Mary', occupation = 'Content Writer'
WHERE id = 101;
4. Delete query
- MySQL DELETE statement is used to remove records from the MySQL table that is no longer required in the database.
- This query in MySQL deletes a full row from the table and produces the count of deleted rows.
- Once we delete the records using this query, we cannot recover it.
- Therefore before deleting any records from the table, it is recommended to create a backup of your database.
Syntax:
The following are the syntax that illustrates how to use the DELETE statement:
DELETE FROM table_name WHERE condition;
Ex :
DELETE FROM People WHERE emp_id=101;