Relational operators.

The relational operator - which specifies a mathematical symbol the certain type of comparison between two values. Relational operators with which has mysql:

= Ravno

> It is more

<it is Less

> = it is More or equally

<= It is less than or equal

<> it is not equal

These operators have standard values for numerical values.

Let's assume that you want to see all customers with a rating (rating) above 200. As 200 is a scalar value, as well as value in stolbce ratings, for their comparison you can use the relational operator.

select * from customers where rating> 200;

Bulevy operators.

The basic Bulevy operators also are distinguished in mysql. Boulle's expressions - are either correct or incorrect, similarly to predicates. Bulevy operators connect one or more correct / incorrect values and make unique correct or incorrect value. Boulle's standard operators raspoznavaemymi in sql javljajutsja:and, or and not.

Let's assume you want to see all customers in Dallas which have a rating above 200:

select * from customers where city = ' dallas ' and rating> 200;

At use of the operator and, both conditions should be executed, that is all customers should be chosen from Dallas which rating is more 200.

At use of the operator or, one of conditions should be executed. For example:

select * from customers where city = ' dallas ' or rating> 200;

In this case all customers will be chosen from Dallas and all having a rating more than 200, even if they and not from Dallas.

not it can be used for inverting values of Boulle. An example of search with not:

select * from customers where city = ' dallas ' or not rating> 200;

At such search all customers will be chosen from Dallas and all customers which rating is less 200. In this search the operator not is applied only to expression rating> 200. It is possible to make more complex{difficult} search:

select * from customers where not (city = ' dallas' or rating> 200);

In this search not it is applied to both expressions in brackets. In this case, the server reads expressions in brackets, defines{determines}, whether there corresponds{meets} to true equality city = ' dallas ' or equality rating> 200. If any condition is correct, Boulle's expression inside parentheses correctly. However, if Boulle's expression inside parentheses correctly, the predicate is as a unit incorrect, because not will transform correctly in incorrect and on the contrary. That is, all customers not taking place in Dallas and which rating less than 200 will be chosen.

in.

The operator in defines{determines} a set of values in which given value can or cannot be switched on. For example, search

select * from salespeople where city = ' barcelona ' or city = ' london ';

Can be copied more simply:

select * from salespeople where city in (' barcelona ',' london ');

in defines{determines} a set of values with the help of names of members of a set of prisoners in parentheses and separated by points. Then he checks various values specified, trying to find concurrence to values from a set. If it happens, the predicate is correct. When the set contains values of numbers{rooms} instead of symbols, single inverted commas fall.

between.

The operator between is similar on the operator in. As against definition under numbers{rooms} from a set as it does{makes} in, between defines{determines} a range which values should decrease that does{makes} a predicate correct. You should enter a keyword between with initial value, key and and final value. As against in, between it is sensitive to the order, and the first value in the offer should be to the first on alphabetic or numerical order. For example:

select * from salespeople where comm between .10 and .12;

select * from salespeople where city between ' berlin 'and' london ';

like.

like it is applicable only to fields such as char or varchar with which he is used to find podstroki. I.e. he searches for a field of a symbol to see, whether the part of his{its} line coincides with a condition. As a condition he uses group symbols (wildkards) - special symbols which can correspond{meet} to something. There are two types of group symbols used with like:

The symbol of underlining{emphasis} (_) replaces any single symbol.

Sign on ' % ', replacing any quantity{amount} of symbols.

If we shall set the following conditions:

select * from customers where fname like ' j % ';

That all customers will be chosen, whose names begin on j:john, jerry, james, etc.

count.

Modular function, makes calculation of values in stolbce or numbers of lines in the table. At job with stolbcom uses distinct as argument:

select count (distinct snum) from orders;

At calculation of lines has syntax:

select count (*) from customers;

group by.

The offer group by allows to define{determine} a subset of values in a special field in terms of other field, and to apply function of the unit to a subset. It enables to unite fields and modular functions in the uniform offer select. For example, we shall assume that you want to find the greatest sum of purchases received by each seller. You can make separate search for each of them, having chosen max () of the table for each value of a field. group by will allow you to place them all in one command:

select snum, max (amt) from orders group by snum;

having.

having defines{determines} criteria used to delete the certain groups of a conclusion just as the offer where does{makes} it for individual lines. For example:

select cid, cname, price, max (price) // max () it too modular function

from customers having max (price)> 500;

having operates it is similar with where, but with where it is impossible to use modular functions.

order by.

This command orders a conclusion of search according to values in this or that quantity{amount} chosen stolbcov. Numerous stolbcy are ordered one inside another, as well as with group by.

exists.

It is used in subrequests.

select cnum, cname, city from customers where exists

(select * from customers where city = " san jose ');

He takes a subrequest as argument and estimates it{him} as correct if that makes any conclusion or as incorrect if that does not do{make} it. It he differs from other operators of a predicate in whom he cannot be the unknown person. For example, we can solve whether take to us some data from the table of Customers if, and only if, one or more customers in this table are in san jose.

union.

union any of two differs from subrequests that that in him (or more) searches do not cope other search. All searches are carried out independently from each other, and already the conclusion of them is is united. For example:

select snum, sname from salespeople where city = ' london ' union

select cnum, cname from customers

where city = ' london ';

The offer union unites a conclusion of two or more sql searches in a uniform set of lines and stolbcov.

desc, asc.

desc-descedent, the conclusion of the data upside-down (alphabetically and to numerical values) .Po to default is used asc.

Well in brief and all supports almost all basic sql server commands so in more detail about select command you can read in any tutorial on language sql.