Select SQL – Database requests

Select SQL – I don’t intend to go into sql, you can read about it in any guide to sql server,mysql mainly supports all the basic commands of the ansi 92 standard,but the select command deserves to be dedicated to a separate chapter. The select command is used to query the database to extract information from it. The syntax of the command is as follows:

select [straight_join] [distinct | all] select_expression,...
[from tables... [where where_definition] [group by column,...]
[order by column [asc | desc], ...] having full_where_definition
[limit [offset,] rows] [procedure procedure_name]]
[into outfile 'file_name'... ]

As you can see from the above, keywords are used together with the select command, the use of which greatly affects the server response.

distinct..

Skips the lines in which all selected fields are identical, that is, eliminates duplication of data.

where.

Offer the command select, which allows you to set predicates, the condition of which may be correct or incorrect for any row in the table. Extract only those rows for which this statement is correct:

select u_id,lname from publishers where city ='new york';

Outputs columns u_id and lname from the publishers table for which the value is in the city-new york column. This makes it possible to make the query more specific.

Relational operators SQL

A relational operator is a mathematical symbol that indicates a certain type of comparison between two values. Relational operators that mysql has.

• = Equally
• > More
• < Less
• >= More or equal
• <=Less or equal
• < > Not equal

These operators have standard values for numerical values.

Suppose that you want to see all customers with a rating above 200. Since 200 is a scalar value, like the value in the rating column, you can use a relational operator to compare them.

select * from customers where rating > 200;

Boolean operators

Basic Boolean operators are also recognized in mysql. Boolean expressions are either true or false, like predicates. Boolean operators bind one or more true/faulty values and produce a single true or false value. The standard Boolean operators that are recognized in sql are: and, or not.

Suppose you want to see all customers in Dallas who are rated above 200:

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

When using the and, operator, both conditions must be met, i.e. all customers from Dallas whose rating is over 200 must be selected.

When using the or,operator, one of the conditions must be met, for example:

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

In this case, all customers will be selected from Dallas and all those rated above 200, even if they are not from Dallas.

can be used to invert Boolean values. Example request with not:

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

This query will select all customers from Dallas and all customers whose rating is less than 200. In this query, the operator is not applied to the rating expression >200. More complex query can be made:

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

This query does not apply to both expressions in parentheses. In this case, the server reads the expressions in parentheses, determines whether the equality city = ‘dallas’ or equality rating > 200 is true. If any condition is true, the Boolean expression within parentheses is true. However, if Boole’s expression inside parentheses is correct, the predicate as a whole is incorrect because it does not convert correctly into incorrect and vice versa. That is, all customers that are not in Dallas and whose rating is less than 200 will be selected.

in

The in operator defines a set of values in which a given value may or may not be included. For example, a query:

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

can be rewritten more easily:

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

in – defines a set of values using the names of the members of the set in parentheses and separated by commas. It then checks the different values of the specified one, trying to find a match with the values in the set. If this happens, the predicate is correct. When the dialling contains number values rather than characters, single quotes are omitted.

between

Operator between looks like operator in. Unlike in, between defines a range whose values should decrease, which makes the predicate correct. You must enter the keyword between with the initial value, the keyword and the end value. Unlike in, between is order sensitive, and the first value in the sentence must be the first alphabetically or numerically ordered. For example:

select * from salespeople where comm between .10 and .12;
select * from salespeople where city between 'berlin' and 'london';

Like applies only to char or varchar fields with which it is used to find substrings. As a condition, it uses wildkards – special characters that can match anything. There are two types of wildcards used with like:

• The underscore character ( _ ) replaces any single character.
• The ‘%’ character, which replaces any number of characters.

If we set the following conditions:

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

all customers whose names start with j:john, jerry, james, etc. will be selected.

count

Aggregate function, calculates the column values or the number of rows in the table. When working with a column, it uses this as an argument:

select count ( distinct snum ) from orders;

It has syntax when calculating strings:

select count (*) from customers;

group by

The group by offer allows you to define a subset of values in a special field in terms of another field, and apply the unit function to the subset. This allows you to combine fields and aggregate functions in a single selection sentence. For example, suppose that you want to find the largest amount of acquisitions received by each vendor. You can make a separate query for each of them by selecting max () from the table for each field value. group by will allow you to put them all into one command:

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

having

Having defines the criteria used to remove certain groups from the output, just as a sentence does for individual rows. For example:

select cid,cname,price,max(price) //max()- this is also an aggregate function
from customers having max(price)>500;

It acts in a similar way to where, but you cannot use the aggregate functions.

order by

This command organizes the query output according to the values in a certain number of selected columns. Multiple columns are ordered one inside the other, just like with group by.

exists

It’s used in subqueries:

select cnum, cname, city from customers where exists
(select * from customers where city = " san jose' );

He takes the subquery as an argument and evaluates it as correct if he makes any conclusion or as wrong if he does not. This makes it different from other predicate operators where it cannot be unknown. For example, we can decide if we want to extract some data from a customer table if, and only if, one or more customers in that table are in san jose.

union

It differs from subqueries in that neither of the two (or more) requests are controlled by the other request. All requests are executed independently of each other, and their output is already united. For example:

select snum, sname from salespeople where city = 'london' union
select cnum, cname from customers
where city = 'london';

The union offer combines the output of two or more sql requests into a single set of rows and columns.

desc,asc

desc-descedent, outputting data in reverse order (alphabetically and numerically). The default is asc.

Well, that’s it in brief.

MYSQL supports almost all the basic commands of sql server, so you can read more about the select command in any sql tutorial.

SQL SELECT Tutorial. SQL Tutorial. SQL for Beginners (video)