ИТ Блог. Работа с базой данных (MySQL, CouchDB, MariaDB, MongoDB)
(Пока оценок нет)
Загрузка...
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Maxim | 35 | Moscow | 21000.00 | | 2 | AndreyEx | 38 | Krasnodar | 55500.00 | | 3 | Oleg | 33 | Rostov | 34000.00 | | 4 | Masha | 35 | Moscow | 31500.00 | | 5 | Ruslan | 34 | Omsk | 43000.00 | | 6 | Dima | 32 | SP | 45000.00 | | 7 | Roma | 34 | SP | 10000.00 | +----+----------+-----+-----------+----------+ 7 rows in set (0.00 sec)
Вот некоторые простые примеры, демонстрирующие использование операторов сравнения в SQL:
SQL> SELECT * FROM CUSTOMERS WHERE AGE >= 33 AND SALARY >= 42000;
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 2 | AndreyEx | 38 | Krasnodar | 55500.00 | | 5 | Ruslan | 34 | Omsk | 43000.00 | +----+----------+-----+-----------+----------+ 2 rows in set (0.00 sec)
SQL> SELECT * FROM CUSTOMERS WHERE AGE >= 35 OR SALARY >= 45000;
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Maxim | 35 | Moscow | 21000.00 | | 2 | AndreyEx | 38 | Krasnodar | 55500.00 | | 4 | Masha | 35 | Moscow | 31500.00 | | 6 | Dima | 32 | SP | 45000.00 | +----+----------+-----+-----------+----------+ 4 rows in set (0.00 sec)
SQL> SELECT * FROM CUSTOMERS WHERE AGE IS NOT NULL;
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Maxim | 35 | Moscow | 21000.00 | | 2 | AndreyEx | 38 | Krasnodar | 55500.00 | | 3 | Oleg | 33 | Rostov | 34000.00 | | 4 | Masha | 35 | Moscow | 31500.00 | | 5 | Ruslan | 34 | Omsk | 43000.00 | | 6 | Dima | 32 | SP | 45000.00 | | 7 | Roma | 34 | SP | 10000.00 | +----+----------+-----+-----------+----------+ 7 rows in set (0.00 sec)
SQL> SELECT * FROM CUSTOMERS WHERE NAME LIKE 'An%';
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 2 | AndreyEx | 38 | Krasnodar | 55500.00 | +----+----------+-----+-----------+----------+ 1 row in set (0.00 sec)
SQL> SELECT * FROM CUSTOMERS WHERE AGE IN ( 38, 34 );
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 2 | AndreyEx | 38 | Krasnodar | 55500.00 | | 5 | Ruslan | 34 | Omsk | 43000.00 | | 7 | Roma | 34 | SP | 10000.00 | +----+----------+-----+-----------+----------+ 3 rows in set (0.00 sec)
SQL> SELECT * FROM CUSTOMERS WHERE AGE BETWEEN 33 AND 35;
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 3 | Oleg | 33 | Rostov | 34000.00 | | 4 | Masha | 35 | Moscow | 31500.00 | | 5 | Ruslan | 34 | Omsk | 43000.00 | | 7 | Roma | 34 | SP | 10000.00 | +----+----------+-----+-----------+----------+ 4 rows in set (0.00 sec)
SQL> SELECT AGE FROM CUSTOMERS WHERE EXISTS (SELECT AGE FROM CUSTOMERS WHERE SALARY > 40000);
+-----+ | AGE | +-----+ | 35 | | 33 | | 35 | | 34 | +-----+ 4 rows in set (0.02 sec)
SQL> SELECT * FROM CUSTOMERS WHERE AGE > ALL (SELECT AGE FROM CUSTOMERS WHERE SALARY < 40000);
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 2 | AndreyEx | 38 | Krasnodar | 55500.00 | +----+----------+-----+-----------+----------+ 1 row in set (0.02 sec)
SQL> SELECT * FROM CUSTOMERS WHERE AGE > ANY (SELECT AGE FROM CUSTOMERS WHERE SALARY < 40000);
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Maxim | 35 | Moscow | 21000.00 | | 2 | AndreyEx | 38 | Krasnodar | 55500.00 | | 4 | Masha | 35 | Moscow | 31500.00 | | 5 | Ruslan | 34 | Omsk | 43000.00 | | 7 | Roma | 34 | SP | 10000.00 | +----+----------+-----+-----------+----------+ 5 rows in set (0.00 sec)
