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;
result select * from command in mysql - MySQL Commands Class 11
result select * from command in mysql

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';
fetching records from mysql table
fetching records from mysql table

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

select * from students where marks < 80;
using less than operator in mysql
using less than operator in mysql

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

select * from students where marks > 90;
using greater than operator in mysql
using greater than operator in mysql

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

 select * from students where marks >= 95;
displaying data 95 or more than 95 marks in MySQL
displaying data 95 or more than 95 marks in MySQL

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

 select * from students where marks<=78.50; 
displaying records with less than or equal to 78.50 in mysql
displaying records with less than or equal to 78.50 in mysql

6. Display the records of students except Asmita.

select * from students where name<>'Asmita';
displaying data except one record in mysql
displaying data except one record in mysql

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;
selecting range data in mysql
selecting range data in mysql

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;
use of not in mysql
use of not in mysql

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

select * from students where name like 'A%';
pattern matching operator in mysql
pattern matching operator in mysql

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

 select * from students where name like '_a%';
display record whose name's second letter is a
display record whose name’s second letter is 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;
display limited columns all rows in mysql
display limited columns all rows in mysql

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;
limited rows limited columns
limited rows limited columns

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:

Informatics Practices

Leave a Reply