Scribd
Upload
8 views4 pages

MySQL Operators: Types and Examples

SQL

Uploaded by

Srinibash Patra
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
8 views4 pages

MySQL Operators: Types and Examples

SQL

Uploaded by

Srinibash Patra
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Operators in MySql

The following types of operators are supported by MySql –


1. Arithmetic Operators
Arithmetic operators are used to perform basic mathematical calculations. Various
arithmetic operators in MySql are –
+ (Addition): Adds two or more arguments.

- (Subtraction): Subtracts the 2nd argument from the 1st argument.

* (Multiplication): Multiplies two or more arguments.

/ (Division): Divides 1st argument by the 2nd.

% or MOD (Remainder Division): Divides 1st argument by the 2nd and remainder is the result.

DIV (Integer Division): Divides 1st argument by the 2nd and quotient is the result (only integer
value, no decimal value)
Example:
Select 13+7-3; Select 10*30+5; Select 10*2+25-3+8;
Select 19/2; Select 19%2; Select 19 DIV 2;
We can apply these operators on database tables. Let’s consider the table STUDENT_MARK.
Q: Add 5 marks to each students’ mark and display
select TOTAL_MARKS+5 from STUDENT_MARK;
Q: Display Name, Percentage of marks of all students (assume full marks=750)
select SNAME, (TOTAL_MARKS/750)*100 PER_MARKS from STUDENT_MARK;
2. Relational Operators
Relational operators are also called as comparison operators. These are used to compare
two or more values. The comparison result is a boolean value (1 or 0) and based on the
boolean value actual result is produced.
Relational operators supported by MySql are –
= (Equal to): Compares left value with right value. If both are equal result is 1 otherwise 0.
<> or != (Not equal to): Compares left value with right value. If both are not equal result is 1
otherwise 0.
> (Greater than): Compares the left value with the right value. If left value is greater than right
value result is 1 otherwise 0.
>= (Greater than or equal): Compares the left value with the right value. If left value is greater
than or equals to right value result is 1 otherwise 0.
< (Less than): Compares the left value with the right value. If left value is smaller than right
value result is 1 otherwise 0.
<= (Less than or equal): Compares the left value with the right value. If left value is smaller
than or equals to right value result is 1 otherwise 0.

10
Select 88888=8888; Select 676767! =767676; or Select 676767 <>767676;
Select 5689>5893; Select 2356>=2458; Select 7853<6578; Select 4587<=2569;
Let’s apply these operators on the table STUDENT_MARK
Q: Retrieve the information of students who have secured B grade.
Select * from STUDENT_MARK where GRADE='B';
Q: Retrieve the Name, Branch, Mark of the students who have scored 700 or above.
Select SNAME, BRANCH, TOTAL_MARKS from STUDENT_MARK where
TOTAL_MARKS>=700;
Q: Retrieve the Name, Branch, Grade of the students who have not secured B grade.
Select * from STUDENT_MARK where GRADE!='B';
3. Logical Operators
Logical operators are used to compare two or more conditional expressions. The comparison
result is a boolean value (1 or 0) and based on the boolean value actual result is produced.
Logical operators supported by MySql are –
AND: It compares two or more conditional expressions and returns true (1) only when all
expressions evaluate to true otherwise it returns false (0).
OR: It compares two or more conditional expressions and returns true (1) when any one of
the expressions evaluate to true otherwise it returns false (0).
NOT: It operates on a single expression and inverses its value i.e., if the expression evaluates
to true (1) it converts to false (0) and vice-versa.
Select (45>50) AND (25<125); Select (45>50) OR (25<125); Select NOT ((45>50);
Select (45>50) OR (25<125) AND (12>6); Select NOT ((45>50) OR (25<125) AND (12>6)); etc.
Let’s apply these operators on the table STUDENT_MARK
Q: Retrieve the details of students who have secured A+ grade and having Electrical branch.
Select * from STUDENT_MARK where BRANCH='Electrical' AND GRADE='A+';
Q: Retrieve the details of students who have secured A grade and having branch either
Electrical or Mechanical.
Select * from STUDENT_MARK where (BRANCH='Electrical' OR
BRANCH='Mechanical') AND GRADE='A';
Q: Retrieve the Name, BRANCH, Mark and Grade of those students who have secured A+
grade but not belong to Computer Science branch.
Select SNAME, BRANCH, TOTAL_MARKS, GRADE from student_mark where
GRADE='A+' AND NOT (BRANCH='Computer Science');
4. Range Selection Operators
Range selection operators are used to retrieve information from a specified range of values.
Various range selection operators in MySql are –
BETWEEN: This operator matches the required value/information in range (a lower limit and
an upper limit) and retrieves the value along with other values fall in the range (lower limit
and upper limit values may be included).

11
IN: This operator checks the required value/information within a given list of values and
retrieves that value if it finds in the list.
Let’s apply these operators on the table STUDENT_MARK
Q: Retrieve the details of students whose marks are between 700 and 750.
select * from STUDENT_MARK where TOTAL_MARKS between 700 and 750;
Q: Retrieve the details of students whose marks in 550, 590, 620, 650, 680,700.
select * from STUDENT_MARK where TOTAL_MARKS in (550, 590,620,650,680,700);
Q: Retrieve the Name, Branch and Grade of students whose Branch in IT, Computer Science,
Electronics, Data Science;
select SNAME, BRANCH, GRADE from STUDENT_MARK where BRANCH in

('IT’, Computer Science', 'IT', 'Electronics', ‘Data Science’);

5. Pattern-matching Operator
This operator is used to retrieve data/information by matching a given pattern. The following
pattern matching operator is used in MySql.
LIKE: It retrieves the required data/information when it matches with the given pattern. This
operator is useful when we do not know the exact data to retrieve. LIKE operators uses the
following wildcard characters to retrieve information.
% (percentage): used with LIKE to match any sequence of characters.
_ (underscore): used with LIKE to match a single character.
✓ Both % and _ can be used simultaneously.
✓ LIKE is applied on the columns having data types char or varchar.
Let’s apply these operators on the table STUDENT_MARK
Q: Retrieve the details of students whose name starts with A.
select * from STUDENT_MARK where SNAME like 'A%';
Q: Retrieve the information of students whose name contains S at any position.
select * from STUDENT_MARK where SNAME like '%S%';
Q: Retrieve the information of students whose name’s 2nd character is i.
select * from STUDENT_MARK where SNAME like '_i%';
Q: Retrieve the information of students whose name’s 3rd character is a.
select * from STUDENT_MARK where SNAME like '__a%';
Soring of Records (use of ORDER BY)
When we execute SELECT command to display the contents of a table the records appear in the
same order as those were inserted. However, we can display records in ascending or
descending order of a particular column value. For this ORDER BY option can be used with the
SELECT command.
For ascending we have to use the option ASC and for descending the option DESC is to be used.
The default option is ascending.

12
Syntax: SELECT * FROM table_name ORDER BY col_name ASC/DESC;
Example: select * from STUDENT_MARK order by TOTAL_MARKS DESC;
select ROLL_NO, SNAME, BRANCH from STUDENT_MARK order by BRANCH ASC;
Column Alias
It is a process used to temporary renaming a table’s column while executing a query. There is
no effect on original column of table in the database.
Syntax: SELECT col-name, col-name… AS alias-name FROM table_name;
Example: select ROLL_NO, SNAME as STUDENT_NAME, GRADE from STUDENT_MARK;

Here, SNAME is the original name of column and STUDENT_NAME is the alias name.

13

Common questions

Powered by AI

The LIKE operator increases query flexibility and depth by allowing pattern-based data retrieval, essential in a learning institution's database for dynamic data needs. It can handle unknown or partial data, as seen when searching for students by partial names or patterns, such as names starting with 'A' (SELECT * FROM STUDENT_MARK WHERE SNAME LIKE 'A%'). This ability to use wildcards for matching sequences and single characters makes LIKE invaluable for queries needing non-exact matches amidst large datasets, greatly enhancing query adaptability and functional reach .

Logical operators in MySQL combine multiple conditions in queries, allowing for complex data retrieval criteria. The AND operator requires all conditions to be true, while OR needs at least one condition to be true. NOT inverts the condition. For example, retrieving A+ students in Electrical (SELECT * FROM STUDENT_MARK WHERE BRANCH='Electrical' AND GRADE='A+') demonstrates AND usage. Similarly, OR and NOT are used in queries like locating students in Electrical or Mechanical branches with A grade (WHERE (BRANCH='Electrical' OR BRANCH='Mechanical') AND GRADE='A').

The MOD and DIV operators in MySQL serve different purposes in integer operations. MOD returns the remainder of a division operation, useful for checking divisibility (e.g., SELECT 19%2 returns 1). Conversely, DIV returns the integer quotient, ideal for pure count-based calculations without decimals (SELECT 19 DIV 2 returns 9). Understanding both operators is vital for scenarios requiring accurate integer manipulations versus scenarios needing remainder contexts .

The BETWEEN operator streamlines complex queries by reducing multiple conditions into a single, clear statement, which simplifies code and enhances readability. In a student marks management system, it enables selecting students within a marks range without repetitive conditional statements, such as (SELECT * FROM STUDENT_MARK WHERE TOTAL_MARKS BETWEEN 700 AND 750). This operator is efficient for continuous range value retrieval, where listing discrete values would be cumbersome .

Arithmetic operators in MySQL enhance data manipulation by allowing basic mathematical operations directly on table data, thus facilitating calculations and data analysis. Specific operations include addition (e.g., SELECT TOTAL_MARKS+5), subtraction, multiplication, division, remainder division (MOD), and integer division (DIV). These operations can be applied to database tables to, for instance, adjust student marks or calculate percentages .

Column aliasing in SQL queries assigns temporary alternate names to columns, improving clarity and presentation without altering original data structures. It enhances readability in output, as seen in changing 'SNAME' to 'STUDENT_NAME' for better understanding (SELECT ROLL_NO, SNAME AS STUDENT_NAME, GRADE FROM STUDENT_MARK). Aliasing is crucial in complex queries or when integrating datasets requiring user-friendly outputs .

Range selection operators like BETWEEN and IN in MySQL are used to retrieve data within a specified range or list, beneficial for grouped data management. For example, BETWEEN is used in a query selecting students with marks between 700 and 750 (SELECT * FROM STUDENT_MARK WHERE TOTAL_MARKS BETWEEN 700 AND 750). These operators simplify queries needing value ranges or specific discrete values in lists, improving query clarity and efficiency .

The pattern-matching operator LIKE in MySQL facilitates data retrieval by matching text data using wildcard characters (%) for sequences and (_) for single characters. This is especially useful for partial or flexible searches. Practical applications include finding students whose names start with a specific letter, such as 'A' (SELECT * FROM STUDENT_MARK WHERE SNAME LIKE 'A%') or names containing a specific character sequence, like '%S%' for any name containing 'S', enhancing data searches without exact data prerequisites .

Sorting query results in MySQL impacts functionality by presenting data in user-preferred orders, enhancing readability and analysis. For example, sorting student marks in descending order helps easily identify top-performing students (SELECT * FROM STUDENT_MARK ORDER BY TOTAL_MARKS DESC). This function is essential for report generation and comparative reviews where the order of data significantly affects interpretation .

Relational operators in MySQL filter query results by comparing columns' values, thus enabling conditional data retrieval. For instance, using the equality operator (=), one can retrieve students who have secured a certain grade, like 'B' (SELECT * FROM STUDENT_MARK WHERE GRADE='B'). Other operators such as '!=' and '<>' help to exclude specific values (e.g., WHERE GRADE!='B').

You might also like