메뉴 건너뛰기

XEDITION

MYSQL

MYSQL: AND & OR CONDITIONS

엘리후 2015.11.26 20:07 조회 수 : 32

MYSQL: AND & OR CONDITIONS

This MySQL tutorial explains how to use the AND condition and the OR condition together in a MySQL query with syntax and examples.

DESCRIPTION

The MySQL AND condition and OR condition can be combined in a SELECT, INSERT, UPDATE, or DELETE statement.

When combining these conditions, it is important to use round brackets so that the database knows what order to evaluate each condition. (Just like when you were learning the order of operations in Math class!)

SYNTAX

The syntax for the AND condition and OR condition together in MySQL is:

WHERE condition1
AND condition2
...
OR condition_n;

Parameters or Arguments

condition1, condition2, ... condition_n
The conditions that are evaluated to determine if the records will be selected.

Note:

  • The MySQL AND & OR conditions allow you to test multiple conditions.
  • Don't forget the order of operation round brackets!

EXAMPLE - WITH SELECT STATEMENT

Let's look at an example that combines the AND and OR conditions in a SELECT statement.

For example:

SELECT *
FROM customers
WHERE (state = 'California' AND last_name = 'Johnson')
OR (customer_id > 4500);

This AND & OR example would return all suppliers that reside in the state of California whose last_name is Johnson and all suppliers whose customer_id is greater than 4500. The round brackets determine the order that the AND and OR conditions are evaluated. Just like you learned in the order of operations in Math class!

The next example takes a look at a more complex statement.

For example:

SELECT customer_id, last_name, first_name
FROM customers
WHERE (last_name = 'Johnson')
OR (last_name = 'Anderson' AND state = 'California')
OR (last_name = 'Smith' AND status = 'Active' AND state = 'Florida');

This AND & OR example would return all customer_idlast_name, and first_name values from the customers table whose the last_name is Johnson OR whose last_name is Anderson and thestate is California OR whose last_name is Smith, the status is Active and the state is Florida.

EXAMPLE - WITH INSERT STATEMENT

This next AND & OR example demonstrates how the AND condition and OR condition can be combined in the INSERT statement.

For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT customer_id, customer_name
FROM customers
WHERE (customer_name = 'Apple' OR customer_name = 'Samsung')
AND customer_id >= 100;

This MySQL AND and OR example would insert into the suppliers table, all customer_id and customer_name records from the customers table whose customer_name is either Apple or Samsung and where the customer_id is greater than or equal to 100.

EXAMPLE - WITH UPDATE STATEMENT

This AND & OR example shows how the AND and OR conditions can be used in the UPDATE statement.

For example:

UPDATE contacts
SET last_name = 'Johnson'
WHERE last_name = 'Anderson'
AND (state = 'Florida' OR state = 'California');

This MySQL AND & OR condition example would update all last_name values in the contacts table to Johnson where the last_name was Anderson and resides in either the state of Florida or 'California'.

EXAMPLE - WITH DELETE STATEMENT

Finally, this last AND & OR example demonstrates how the AND and OR conditions can be used in the DELETE statement.

For example:

DELETE FROM contacts
WHERE state = 'California'
AND (last_name = 'Smith' OR last_name = 'Anderson');

This MySQL AND and OR condition example would delete all records from the contacts table whose state is California and last_name was either Smith or Anderson.

 

출처 : http://www.techonthenet.com/mysql/and_or.php

번호 제목 글쓴이 날짜 조회 수
55 MySQL 데이터 복구하기 엘리후 2016.10.06 85
54 플래시로 DB테이블 연동 기초~ 엘리후 2016.09.07 62
53 [문제해결 일지] systemctl start mariadb 실행시 Failed to issue method call: No such file or directory. 에러 엘리후 2016.05.10 83
52 알기 쉬운 게임DB 이야기 엘리후 2016.02.28 248
51 mysql 이벤트 스케줄 사용법 엘리후 2016.02.20 1866
50 MySql 문자열 합치기 엘리후 2016.02.19 239
49 MySQL Insert Where query 엘리후 2016.02.19 171
48 [MySQL]이벤트 스케줄러를 사용해서 데이터 삭제하기 엘리후 2016.02.02 215
47 mysql 이벤트 스케쥴러 사용하기 엘리후 2016.02.02 85
46 SQL FULL OUTER JOIN Keyword 엘리후 2016.01.28 132
45 mysql_connect(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead… 엘리후 2015.12.01 67
44 mysql 명령어로 접속시도시 발생되는 오류 엘리후 2015.12.01 328
» MYSQL: AND & OR CONDITIONS 엘리후 2015.11.26 32
42 [MY-SQL] 현재(오늘)부터 특정일 까지를 구하는 Query문(between, subdate) + 오늘 기준 이번주, 이번달 구하는 Query문 엘리후 2015.11.20 201
41 mysql에서 가장 오래된 row select 쿼리는? 엘리후 2015.11.19 158
40 MySQL 함수를 활용한 날짜비교 쿼리문 작성하기 [출처] MySQL 함수를 활용한 날짜비교 쿼리문 작성하기|작성자 네오 에이치 엘리후 2015.11.18 60
39 MySQL 기본쿼리 : DELETE 문 엘리후 2015.11.02 45
38 [서버관리] phpMyAdmin을 통한 DB 및 사용자추가 엘리후 2015.10.28 116
37 Ubuntu MySQL 원격 접속 허용 엘리후 2015.10.22 56
36 [mysql] mysql 중복 제거 쿼리 (dist, group by) 엘리후 2015.10.21 137
위로