Comprehensive notes MySQL DML Commands Class 11

In this article, I am going to cover MySQL DML Commands Class 11. As of now, you are familiar with MySQL and database concepts and how to open the database. So let’s begin with MySQL DML Coammdns Class 11!

Comprehensive notes MySQL DML Commands Class 11

As you know MySQL commands are categorized in DDL and DML commands. In this article you are going to learn about the following commands:

• Insert
• Select
• Update
• Delete

So let’s begin the article MySQL DML Commands Class 11. As we have discussed create a database and create table command in the previous article. After creating a database and a table, you need to insert records or values in the table. So in this section of MySQL DML Commands Class 11, you will learn how to insert the values in the MySQL database table.

The MySQL insert command

The MySQL insert command syntax:

insert into <tablename> (col1,col2,...) values (Val1,Val2,....);

In the above syntax:

1. insert into command to insert a record
2. tablename – this is the tablename
3. col1,col2,…,coln – This is an optional part for insert command when you want to insert values for specific columns this part is mandatory
4. values – this keyword is used to specify the values
5. val1,val2,…,valn – Here values are provided for specific columns separated by column names

Observe the following commands:

Method 1

 insert into students values(1116,'Nandini',95.00,'A1');

Method 2

 insert into students (rollno,name,marks) values (1117,'Manish',88);

In the above methods, method 2 one value for the field Grade is skipped, hence this is displayed as NULL in the table. The text and data type values must be enclosed in single quote.

In the next section of the MySQL DML Commands Class 11, I will talk about the select command.

The select command

The select command is used to retrieve data from the database. The select command is taking the following form:

select <*/column_list> from <table_name> where <condition>

In the above syntax,

• select – this is a command to fetch all records
• */column_list – * is required for all columns and column_list will be written separated by commas
• from – from keyword is used to point the table
• table_name – table_name is the table name to fetch from the database table
• where – where is used to specify the condition
• condition – condition is used along with the column names and values with relations operators

Now look at the ways of fetching records from table for MySQL Commands Class 11.

This command you can use in the following ways:

1. Fetch all rows and columns
2. Fetch limited rows and all columns
3. Fetch all rows and limited columns
4. Fetch limited rows and limited columns

Fetch all rows and columns

To retrieve or fetch all rows and columns use the following command:

Syntax

select * from <table_name>;

Example

select * from students;

Fetch limited rows and all columns

To retrieve limited rows and columns use the following command let’s have a look on syntax:

Syntax

select */column_list from <table_name> where <condition>;

Here the where the condition will use different conditions using relational operators (<,>,<=,>=,=, <>), logical operators (and, or, not), Special operators like between-and, in etc. So here we will see some example for each operator with different conditions. So here we will see some examples to understand it:

Relational operators

You can use relational operators to fetch the limited records from database table.

1. Display data from students who secured A1 grade.

select * from students where grade ='A1';

2. Display the records of students who secured less than 80 marks.

select * from students where marks < 80;

3. Display records of students who secured more than 90 marks.

select * from students where marks > 90;

4. Display the record who secured 95 or greater than 95 marks.

 select * from students where marks >= 95;

5. Display records of students 78.50 or less that 78.50 marks.

 select * from students where marks<=78.50; 

6. Display the records of students except Asmita.

select * from students where name<>'Asmita';

7. Display records of those students who score marks in the range of 81 to 90.

select * from students where marks >=81 and marks<=90;
select * from students where marks between 81 and 90;

8. Display records of students who secured grade A1,A2.

 select * from students where grade = 'A1' or grade = 'A2';
 select * from students where grade in('A1' , 'A2');

9. Display record of students who secured marks except range from 70 to 80.

 select * from students where not marks between 70 and 80;

10. Display the record whose name starts with ‘A’.

select * from students where name like 'A%';

11. Display records whose name’s second letter is ‘a’.

 select * from students where name like '_a%';

Fetch all rows and limited columns

To fetch all rows with limited columns just use the column names separated by commas with select clause.

select rollno,name from students;

Fetch limited rows and limited columns

For this you can use column list along with where condition.

 select rollno,name from students where marks>90;

In the next section of MySQL DML Commands Class 11, you will learn about update record in the table.

The MySQL update command

The general form of the update command is something like this:

update <tablename> set col1=val1,col2=val2,...,coln=valn where <condition>;

In this syntax,

• update – Command to update the record
• tablename – Table name for the table
• set – set is a keyword that allows changing the values
• columnlist and values – Provide the desired column name along with new values for the new update
• where <condition> – It is where clause with condition

Observe the following examples:

update students set marks=86,grade='A2' where rollno=1114;

It will display a message Query OK, then number of records updated. Check the table data to verify it.

update students set marks=marks+5;

This will update all the records in the table and add 5 marks to the existing marks.

The delete command

Delete is used to delete rows from the table. The syntax is like this:

delete from <tablename> where <condition>

Observe these examples:

delete from students where rollno=1111;
delete from students;

So I hope you enjoyed this article. If you have any doubt or queries related to this article, feel free to ask in the comment section.

Thank you for reading this article.

Follow this link to access more: