MySQL Logic Operators

Mysql Logical Operators

AND (&&) Operator
OR  (||) Operator
NOT (! or <>)  Operator

Create Table

 Create TABLE myclass(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    score INT NOT NULL,
    class CHAR(2) NOT NULL,
    PRIMARY KEY(id)
 );

Insert Sample Record

 INSERT INTO myclass(name, score, class)
    VALUES('Jenni',89,'A'),
          ('Bob',83,'A'),
          ('Jim',73,'A'),
          ('Christ',81,'A'),
          ('Philip',72,'A'),
          ('Sokha',98,'A'),
          ('Chan',99,'A'),
          ('Thida',79,'A'),
          ('Jim',89,'B'),
          ('Ryan',78,'B'),
          ('Mellisa',73,'B'),
          ('Bill',87,'B'),
          ('John',88,'B'),
          ('Geroge',78,'C'),
          ('Lili',89,'C');

Query or Select all records

mysql> SELECT * FROM myclass;
+----+---------+-------+-------+
| id | name    | score | class |
+----+---------+-------+-------+
|  1 | Jenni   |    89 | A     |
|  2 | Bob     |    83 | A     |
|  3 | Jim     |    73 | A     |
|  4 | Christ  |    81 | A     |
|  5 | Philip  |    72 | A     |
|  6 | Sokha   |    98 | A     |
|  7 | Chan    |    99 | A     |
|  8 | Thida   |    79 | A     |
|  9 | Jim     |    89 | B     |
| 10 | Ryan    |    78 | B     |
| 11 | Mellisa |    73 | B     |
| 12 | Bill    |    87 | B     |
| 13 | John    |    88 | B     |
| 14 | Geroge  |    78 | C     |
| 15 | Lili    |    89 | C     |
+----+---------+-------+-------+
15 rows in set (0.00 sec)

Logical AND(&&) Operators

The AND(&&) Operator requires both condition to be true.

Get All student who are score higher than 80 and in class A

• Student score higher than 80
• Student in Class A
• Both of the condition must be true

mysql> SELECT * FROM myclass
    -> WHERE class='A' AND score > 80;
+----+--------+-------+-------+
| id | name   | score | class |
+----+--------+-------+-------+
|  1 | Jenni  |    89 | A     |
|  2 | Bob    |    83 | A     |
|  4 | Christ |    81 | A     |
|  6 | Sokha  |    98 | A     |
|  7 | Chan   |    99 | A     |
+----+--------+-------+-------+
5 rows in set (0.00 sec)

Get students who are score higher than 80, and in class A or class C

• Student score higher than 80
• Student in class A or Class B

mysql> SELECT * FROM myclass
    -> WHERE
    ->     score > 80
    ->    AND
    ->     class='A'
    ->    OR
    ->     class='C';
+----+--------+-------+-------+
| id | name   | score | class |
+----+--------+-------+-------+
|  1 | Jenni  |    89 | A     |
|  2 | Bob    |    83 | A     |
|  4 | Christ |    81 | A     |
|  6 | Sokha  |    98 | A     |
|  7 | Chan   |    99 | A     |
| 14 | Geroge |    78 | C     |
| 15 | Lili   |    89 | C     |
+----+--------+-------+-------+
7 rows in set (0.00 sec)

Get students who are not in class A

• Get students who are not in class A

mysql> SELECT * FROM myclass
    -> WHERE class <> 'A';
+----+---------+-------+-------+
| id | name    | score | class |
+----+---------+-------+-------+
|  9 | Jim     |    89 | B     |
| 10 | Ryan    |    78 | B     |
| 11 | Mellisa |    73 | B     |
| 12 | Bill    |    87 | B     |
| 13 | John    |    88 | B     |
| 14 | Geroge  |    78 | C     |
| 15 | Lili    |    89 | C     |
+----+---------+-------+-------+
7 rows in set (0.00 sec)

another method using !=

mysql> SELECT * FROM myclass
    -> WHERE
    ->   class != 'A';
+----+---------+-------+-------+
| id | name    | score | class |
+----+---------+-------+-------+
|  9 | Jim     |    89 | B     |
| 10 | Ryan    |    78 | B     |
| 11 | Mellisa |    73 | B     |
| 12 | Bill    |    87 | B     |
| 13 | John    |    88 | B     |
| 14 | Geroge  |    78 | C     |
| 15 | Lili    |    89 | C     |
+----+---------+-------+-------+
7 rows in set (0.00 sec)