MySQL select is the most popular SQL language design in all its dialects on all computing platforms and operating systems. The ability to correctly formulate thoughts in SQL simplifies thinking, makes it systematic and logical.
MySQL select is not necessarily a real query. This can be the calculation of an arithmetic expression or the formation of a variable value. MySQL does not limit the developer in how to use SQL language constructs. It offers only syntax and functionality.
The formal syntax of the SELECT construct
Almost all MySQL select sources declare the official syntax. Accepted all the keywords of the SQL language to write in capital letters, but this is not a general rule. Whoever likes it, but it is very important to understand: rarely a mixture of lowercase and uppercase letters leads to the correct result.
Clarity and strict observance of syntax (1) is the most important condition for correct, safe and reliable programming, stable performance of the code. Regarding MySQL, which allows (2) the use of letters in any case, it is important to understand:
- select distinct `first_name` from` ex_workers` where `first_name` like '% yoke%'"
- select distinct `first_name` from` ex_workers` where `First_name` like '% yoke%'"
- this is not the same thing. If you can write keywords as you please, liberties in changing the names of tables and variables in one query can create serious problems.
Practical syntax, simple tables
The syntax variant (2) is a real practice that draws up any request in the form of a procedure that only receives:
- what to choose;
- where to choose;
- on what condition.
The result of the procedure (in this case: iLineSel) is a regular array of all selected rows. This syntax is particular, but groupings, sorts, and complex joins are far from always necessary.
The simpler the tables in the database, the easier and faster the MySQL query select is. The more often join is used, the more conditions in one request, the worse. The situation is especially complicated if the query is directed to several large tables.
If the query is formulated correctly, MySQL select will work as it should. But how long does it take? The site visitor may not wait for the result. It is necessary not only to know what to say, but also to evaluate how long the answer will take!
Correct request and encoding
Before saying that MySQL is bad and another bug has been detected, you should check the wording of the query, the correctness of the algorithm, and the character encoding.
History and brilliant practice show that MySQL in 99% of cases is an ideal tool for successful work with information. If something does not give the desired result, then somewhere a mistake has been made. After carefully checking the contents of the request, making sure that the page encoding is correct, you can try to reformulate the request.
When working with MySQL, you should never forget that the encoding, localization and hosting conditions can be different. Good practice: always check the working environment before starting work.
The simplest SQL constructs
It is rare that a developer decides to use MySQL as an arithmometer, but the general programming logic is followed: PHP & MySQL select will execute the expression the same way:
The result value will be 7. Priorities of operations and the logic of operations of the MySQL language is performed according to general programming rules. This also applies to expressions and conditions.
In this example, section 1 shows the arithmetic expression in the MySQL select construct, and section 2 contains three arrays from which a table of company employees is randomly built:
The name, surname and position of the employee were formed randomly from arrays of available names, surnames and positions. The time of taking to work and the time of coming to work are intentionally recorded with errors:
The following simple queries are allowed on this record set:
- select `first_name`,` last_name`, `w_status` from` ex_workers`;
- select distinct `w_status` from` ex_workers`;
- select distinct `start_timestamp` from` ex_workers` where `start_timestamp`! = '0000-00-00 00:00:00';
- select distinct `start_timestamp` from` ex_workers` where (`start_timestamp`! = '0000-00-00 00:00:00') and start_timestamp> '2017-12-05 09:00:00'.
The first request forms a list of all company employees. Naturally, the result of this query will contain the entire number of table entries.
The second query selects all existing positions in the enterprise using the 'distinct' keyword so that there are no duplicates in the selection.
The third request selects all the correct dates for hiring, that is, those that contain real information, not zeros.
The fourth query allows us to determine that only two entries in the table make sense, that is, they contain information filled in during working hours. But in this particular fourth request, this meaning does not take into account the number of employees satisfying it: in fact, there are four correct entries.
Building the right queries
The logic of constructing queries develops in the process of their development. It is difficult to immediately formulate the right decision. In particular, the table may contain records of employees, but if the date of hiring is not correct, then most likely it was entered incorrectly.
If the date the person was hired is night time, a day off, or, in general, it was created after hours, then it is erroneous or the table was susceptible to a virus attack.
Based on the foregoing, it would be correct to make such a request:
This request is also not ideal, you can provide lunch for the personnel department, the end time of the working day. But what matters here is not the correctness of the request for a sample of the current state of the staffing table, but the functionality of its formation.
In this case, errors at the stage of designing the staffing table force the developer to make complex and incomprehensible queries.
In real practice, it is extremely rare that the MySQL query select # from * where & construction will contain a position in the "#" position, only one table in the "*" position, and the "&" condition will check for working hours. All this is nonsense, this should not be fundamentally. All positions are a separate table; all entries always contain the correct date of employment.
The right database, the right queries
The select construct can be used to determine which database to use. As a result of executing the MySQL query "select database ()", the name of the current database will be obtained.
Common practice - a database is designed in such a way that:
- ensure safe storage and efficient use of data;
- to form a systematic idea of the structure of information;
- provide easy access to data, a universal design for all requests.
These are not the only criteria, but even their observance will allow you to build a good application, a stable web resource.
A good rule of thumb: before starting work, check the working environment and the state of the database.
When using site management systems, this is important. For example, you can clear the cache or see how many users are already working with the site and rebuild requests in order to optimize them. An interesting solution would be the dynamics of the query when in MySQL the select table is a variable. In any case, query is a string of characters. But there is no reason to make this line static.
If the query structure is formed during the operation of the web resource, this allows you to dynamically switch tables. For example, site visitors begin work with one set of database tables, and after registration continue on another. The dynamics of the query allows you to optimize the performance of the MySQL server.
Record Sorting Queries
Sorting sorting is different, especially when it comes to Russian. But sometimes it’s convenient to use the functionality of the PHP language over MySQL select order by. Ideally, when the query results in the final result, which does not require further development by PHP constructions, but usually the main selection is accompanied by the design of the page, which forces the developer to specify the contents of the control elements.
For example, choosing a staffing table, the site should provide the resource administrator with one functional, another employee of the human resources department, and the third one for the employee himself. In the first case, the administrator analyzes and controls the company in terms of its social component, in the second case, only the operations of changing and adding records (employees) are allowed. In the third case, the employee works with his plan: marks the completed and plans what to do next.
The main request requires adequate execution of auxiliary requests in the context of why it was executed.
In all cases, order by sorting allows you to keep all records in a specific order. This is always important, because it speeds up the orientation process in employees, in tasks, in the dates of work execution.
Typically, job titles rarely change, and they can be written in sorted order initially. When changing the list of posts, you can re-sort it once and make changes to the staff list, as the order of the keys will change.
In this context, the need for sorting remains only on those tables that change dynamically during operation.
Grouped Queries
Grouping records is often no less important than sorting, but sometimes these are mutually exclusive operations.
It is advantageous to use grouping for the purposes of counting records, their systematization, and analysis. For example, you can use the group by clause for queries such as MySQL select users, clients, visits, open pages. This can work for security purposes to prevent unauthorized connections or to monitor company work processes.
Request Condition: Ideal When Not Available
It just so happened, the MySQL select & where construct is a single entity. Although when using various options for joining tables through join, it may not exist at all.
But the fact that where is the constant component of all MySQL select, the novice developer begins to get used to it from the very beginning. Examples are taught, you need to know:
- what is selected;
- from which table;
- by what criteria.
Only after this begins training the rookie in the basics of left and right joining tables, the logic of tricky slang: this table sits on this one, and selects data from a third source altogether.
Practice is always easy. If the database is built in such a way that without joining the tables it is impossible to compose a query in any way, then something has been done wrong in the database. If conditions beyond task = 'value' or var> 0 are needed, that is, more complex conditional constructs are needed - this is an occasion to revise the concept of the database and the logic of the necessary range of queries to it.
It is impossible to consider building a database outside of queries to it. A database is an information structure whose concern is to answer questions (requests) as quickly and simply as possible.
Database Objects and Queries
Relational relations are a very specific database structure and an idea of queries to it. If this familiar concept is slightly changed: there are objects (not tables) and there are methods of objects (properties, not queries), then the relational relations and the queries themselves disappear in the bodies of the objects.
So, staffing is an object that interacts with objects:
- employee
- position;
- work task.
An employee may be a director, administrator, employee. For staffing, this matters, but within the limits of the corresponding instance of the object, the employee. Similarly, an object post can be completely anything. But in the staffing sample, it will be a string of characters, and in the context of the administrator's work, it will be a selector for selecting the desired position from among the available ones.
Strict adherence to MySQL select syntax rules is a good rule: practical, safe, efficient. But if we restrict ourselves to its application at the level of each database object, and work directly with these objects according to their methods (their rules), the efficiency will increase many times.