MariaDB?條件語句WHERE

MariaDB 條件語句WHERE

WHERE Clause Operators Operator Description

= Equality
<> Nonequality
!= Nonequality
<?Less than
<= Less than or equal to?

> Greater than

>= Greater than or equal to BETWEEN Between two specified values?

BETWEEN AND

(jlive)[crashcourse]>SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;

+----------------+------------+

| prod_name? ? ? | prod_price |

+----------------+------------+

| .5 ton anvil ? | ? ? ? 5.99 |

| 1 ton anvil? ? | ? ? ? 9.99 |

| Oil can? ? ? ? | ? ? ? 8.99 |

| TNT (5 sticks) |? ? ? 10.00 |

| Bird seed? ? ? |? ? ? 10.00 |

+----------------+------------+

5 rows in set (0.00 sec)

通配LIKE(_%)

(jlive)[crashcourse]>SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 5 AND 10 AND prod_name LIKE 'Oil%';

+-----------+------------+

| prod_name | prod_price |

+-----------+------------+

| Oil can ? | ? ? ? 8.99 |

+-----------+------------+

1 row in set (0.00 sec)

(jlive)[crashcourse]>SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil';

+---------+-------------+

| prod_id | prod_name ? |

+---------+-------------+

| ANV02 ? | 1 ton anvil |

| ANV03 ? | 2 ton anvil |

+---------+-------------+

?

2 rows in set (0.00 sec)

(AND) OR組合

(jlive)[crashcourse]>SELECT vend_id,prod_name,prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10 ORDER BY prod_price DESC;

+---------+----------------+------------+

| vend_id | prod_name? ? ? | prod_price |

+---------+----------------+------------+

|? ? 1003 | Safe ? ? ? ? ? |? ? ? 50.00 |

|? ? 1003 | Detonator? ? ? |? ? ? 13.00 |

|? ? 1003 | TNT (5 sticks) |? ? ? 10.00 |

|? ? 1003 | Bird seed? ? ? |? ? ? 10.00 |

|? ? 1002 | Oil can? ? ? ? | ? ? ? 8.99 |

|? ? 1002 | Fuses? ? ? ? ? | ? ? ? 3.42 |

+---------+----------------+------------+

6 rows in set (0.00 sec)

(jlive)[crashcourse]>SELECT vend_id,prod_name,prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10 ORDER BY 3 DESC;

+---------+----------------+------------+

| vend_id | prod_name? ? ? | prod_price |

+---------+----------------+------------+

|? ? 1003 | Safe ? ? ? ? ? |? ? ? 50.00 |

|? ? 1003 | Detonator? ? ? |? ? ? 13.00 |

|? ? 1003 | TNT (5 sticks) |? ? ? 10.00 |

|? ? 1003 | Bird seed? ? ? |? ? ? 10.00 |

+---------+----------------+------------+

?

4 rows in set (0.00 sec)

(NOT) IN

(jlive)[crashcourse]>SELECT vend_id,prod_name,prod_price FROM products WHERE vend_id IN (1002,1003) ORDER BY vend_id;

+---------+----------------+------------+

| vend_id | prod_name? ? ? | prod_price |

+---------+----------------+------------+

|? ? 1002 | Oil can? ? ? ? | ? ? ? 8.99 |

|? ? 1002 | Fuses? ? ? ? ? | ? ? ? 3.42 |

|? ? 1003 | Sling? ? ? ? ? | ? ? ? 4.49 |

|? ? 1003 | TNT (1 stick)? | ? ? ? 2.50 |

|? ? 1003 | TNT (5 sticks) |? ? ? 10.00 |

|? ? 1003 | Bird seed? ? ? |? ? ? 10.00 |

|? ? 1003 | Carrots? ? ? ? | ? ? ? 2.50 |

|? ? 1003 | Safe ? ? ? ? ? |? ? ? 50.00 |

|? ? 1003 | Detonator? ? ? |? ? ? 13.00 |

+---------+----------------+------------+

9 rows in set (0.00 sec)

(jlive)[crashcourse]>SELECT vend_id,prod_name,prod_price FROM products WHERE vend_id NOT IN (1002,1003)?ORDER BY vend_id;

+---------+--------------+------------+

| vend_id | prod_name? ? | prod_price |

+---------+--------------+------------+

|? ? 1001 | .5 ton anvil | ? ? ? 5.99 |

|? ? 1001 | 1 ton anvil? | ? ? ? 9.99 |

|? ? 1001 | 2 ton anvil? |? ? ? 14.99 |

|? ? 1005 | JetPack 1000 |? ? ? 35.00 |

|? ? 1005 | JetPack 2000 |? ? ? 55.00 |

+---------+--------------+------------+

?

5 rows in set (0.00 sec)

IS (NOT) NULL

(jlive)[crashcourse]>SELECT cust_id,cust_email FROM customers;

+---------+---------------------+

| cust_id | cust_email? ? ? ? ? |

+---------+---------------------+

| ? 10001 | ylee@coyote.com ? ? |

| ? 10002 | NULL? ? ? ? ? ? ? ? |

| ? 10003 | rabbit@wascally.com |

| ? 10004 | sam@yosemite.com? ? |

| ? 10005 | NULL? ? ? ? ? ? ? ? |

+---------+---------------------+

5 rows in set (0.00 sec)

(jlive)[crashcourse]>SELECT cust_id,cust_email FROM customers WHERE cust_email IS NOT NULL;

+---------+---------------------+

| cust_id | cust_email? ? ? ? ? |

+---------+---------------------+

| ? 10001 | ylee@coyote.com ? ? |

| ? 10003 | rabbit@wascally.com |

| ? 10004 | sam@yosemite.com? ? |

+---------+---------------------+

?

3 rows in set (0.00 sec)