в 13:46, 26 мая 2010

2010-05-26T13:46:00Z

Admin: 1 версия

2010-05-26T10:15:53Z

1 версия

Новая страница

==AND and OR may be intermixed==



<source lang="sql">
/*
mysql> select * from Bird;
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| BlueBird | Joe | Car | f | 1999-03-30 | NULL |
| RedBird | Yin | Bus | m | 1979-03-30 | NULL |
+----------+-------+---------+------+------------+-------+
2 rows in set (0.00 sec)
mysql> /*although AND has higher precedence than OR*/
mysql> SELECT * FROM Bird WHERE (species = "Car" AND sex = "m")
-> OR (species = "Bus" AND sex = "f");
Empty set (0.00 sec)

*/
Drop table Bird;
CREATE TABLE Bird (
name VARCHAR(20),
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth DATE,
death DATE
);

INSERT INTO Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1979-03-30",NULL);

select * from Bird;

/*although AND has higher precedence than OR*/

SELECT * FROM Bird WHERE (species = "Car" AND sex = "m")
OR (species = "Bus" AND sex = "f");

</source>

==An OR operator==



<source lang="sql">
/*
mysql> select * from Bird;
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| BlueBird | Joe | Car | f | 1999-03-30 | NULL |
| RedBird | Yin | Bus | m | 1979-03-30 | NULL |
+----------+-------+---------+------+------------+-------+
2 rows in set (0.01 sec)
mysql> SELECT * FROM Bird WHERE species = "Bus" OR sex = "f";
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| BlueBird | Joe | Car | f | 1999-03-30 | NULL |
| RedBird | Yin | Bus | m | 1979-03-30 | NULL |
+----------+-------+---------+------+------------+-------+
2 rows in set (0.00 sec)

*/
Drop table Bird;
CREATE TABLE Bird (
name VARCHAR(20),
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth DATE,
death DATE
);

INSERT INTO Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1979-03-30",NULL);

select * from Bird;

SELECT * FROM Bird WHERE species = "Bus" OR sex = "f";

</source>

==Combine conditions in select clause==



<source lang="sql">
Drop table employee;
CREATE TABLE employee (
id int unsigned not null auto_increment primary key,
firstname varchar(20),
lastname varchar(20),
title varchar(30),
age int,
yearofservice int,
salary int,
perks int,
email varchar(60)
);

INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru");
select * from employee;
SELECT firstname, lastName , age from employee
where (lastName like "S%" OR lastName like "A%") AND
age < 30;

</source>

==Combine condition with OR==



<source lang="sql">
/*
mysql> select * from Student;
+-----------+------------+-----------+
| StudentID | first_name | last_name |
+-----------+------------+-----------+
| 1 | John | Jones |
| 2 | Gary | Burton |
| 3 | Emily | Scarlett |
| 4 | Bruce | Lee |
| 5 | Anna | Wolff |
| 6 | Vic | Andrews |
| 7 | Steve | Alaska |
+-----------+------------+-----------+
7 rows in set (0.02 sec)
mysql> select * from Student WHERE first_name="Bruce" OR last_name="Burton" OR
-> last_name="Wolff";
+-----------+------------+-----------+
| StudentID | first_name | last_name |
+-----------+------------+-----------+
| 2 | Gary | Burton |
| 4 | Bruce | Lee |
| 5 | Anna | Wolff |
+-----------+------------+-----------+
3 rows in set (0.00 sec)

*/
Drop table Student;
CREATE TABLE Student (
StudentID INT NOT NULL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL

)TYPE = InnoDB;

INSERT INTO Student (StudentID,first_name, last_name) VALUES (4,"Bruce", "Lee");
INSERT INTO Student (StudentID,first_name, last_name) VALUES (1,"John", "Jones");
INSERT INTO Student (StudentID,first_name, last_name) VALUES (2,"Gary", "Burton");
INSERT INTO Student (StudentID,first_name, last_name) VALUES (7,"Steve", "Alaska");
INSERT INTO Student (StudentID,first_name, last_name) VALUES (5,"Anna", "Wolff");
INSERT INTO Student (StudentID,first_name, last_name) VALUES (6,"Vic", "Andrews");
INSERT INTO Student (StudentID,first_name, last_name) VALUES (3,"Emily", "Scarlett");
select * from Student;
select * from Student WHERE first_name="Bruce" OR last_name="Burton" OR
last_name="Wolff";

</source>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL/MySQL/Where Clause/OR - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия