View SQL Search for all tables. Simple SQL Requests - Short Help and Examples

Let's start with elementary queries. What about without them? The most concise form SQL queryand translates as "I want to get all the data from this table." The result of the next query is all records with all fields from the D_STAFF table.

SELECT * from D_STAFF

Enter SQL query in the training program.

Select [SQL] in the explorer on the left, press [SQL button] above the list on the right, enter the request and press the [SQL button] again. After confirming the execution of the query, the list will look like this as follows. In the case, everything is easier: just enter the SQL query in the corresponding field and click [Run].


The result of performing the simplest SQL query.

Use (*) after operator SELECTOf course, it is convenient, especially if it is unknown, what fields in the table are generally there, but also costly - structures that store the result of the query spent quite a lot of "unnecessary" memory, and the time for the execution of the very query is only increasing. The next option is exactly as preferable if you need information only about FI. Employee and its experience. Translation about this: "I want to know only this and this is about all from the table ..."

SELECT S_NAME, S_EXPERIENCE FROM D_STAFF

The result of the last SQL query occupies noticeably less space "width".


Select values \u200b\u200bof specific table fields.

Step 2. SQL Request with a simple selection criterion

The simplest requests are practically not applicable in practice, since "pull out" absolutely all records from the specified table, and there may be hundreds of thousands. DBMS may simply refuse to perform such a request, and random access memory On the client machine can not be elementary. What to do with the results of such requests, even if they are performed correctly, it is not always clear, though, for some will go. In order to impose restrictions on the selection of the entries you need, the keyword is used in SQL. The query below selects only employees with work experience less than 5 years.

SELECT S_NAME, S_EXPERIENCE FROM D_STAFF WHERE S_EXPERIENCE


Using simple criterion Selection of records.

Step 3. SQL Request with a composite selection criterion

For what is needed composite selection criteria Records, explain, I think no need. For the same, for what and requests with simple criteria. Conditions are combined using logic operations conjunctions and dysuunction (Operators "and" (and) and "or" (OR)), and are grouped by brackets. The following request will return the record about employees with experience less than 5 years old and with an additional restriction on their position.

SELECT S_NAME, S_EXPERIENCE, S_POSITION FROM D_STAFF WHERE (D_STAFF.S_POSITION 20) AND D_STAFF.S_EXPERIENCE


Using sophisticated criterion Selection of records.

Step 4. Between Operator

The Between operator simplifies the syntax of the description of the criteria defining the interval permissible values. Instead of the Between 3 and 7 below, it would be possible to write d_staff.s_experience\u003e \u003d 3 and d_staff.s_experience<=7 . Первый вариант способствует наглядности запроса – это раз, поиск на стороне СУБД может выполняться по отдельному алгоритму, специально оптимизированному для подобного вида ограничений – это два.

SELECT S_NAME, S_EXPERIENCE, S_POSITION FROM D_STAFF WHERE (D_STAFF.S_POSITION 20) AND D_STAFF.S_EXPERIENCE BETWEEN 3 AND 7


Using the Between operator.

Step 5. Like operator

This wonderful operator allows you to impose restrictions on the values \u200b\u200bof text fields using templates. I will not explain the syntax, I think that from the example and so everything is clear. We search for employees, with F.O.O. Beginning on "FROM", in the middle should meet "Shaft" and end everything should "ICH". In some DBMS, LIKE keyword can also be used with date and time values.

SELECT S_NAME FROM D_STAFF WHERE S_NAME LIKE "S%" AND S_NAME LIKE "% SALE%" AND S_NAME LIKE "% ICH"

SELECT S_NAME FROM D_STAFF WHERE S_NAME LIKE "C% WALL% ICH"


Use LIKE operator.

Step 6. Pseudonyms of tables and fields

Used in the query names of tables and fields can be defined pseudonyms. How it is done - demonstrated below. Why this is done - will be shown in the following steps, including in step 7, and this example illustrates the most obvious use aliases in SQL - Registration of the result of the request in accordance with the requirements for the convenience of perception by his person. To determine the pseudonym of the table or fields in SQL, the AS keyword is used. The result of the query (Table Title) in this option looks more suitable in order to make a report on it.

SELECT S_NAME AS Employee, S_Experience AS [Work Experience], S_Position AS Position from D_STAFF AS STAFF


The use of table pseudonyms and fields.

Step 7. The ratio "Chief - Subordinate"

This example completes the "first steps" of studying SQL requests is the most difficult of them. Here we are "programming" issuing a list of employees with their direct leadership. The complexity is that the records and about those and others are stored in the same table, and here without aliases can not do. DBMS, during the query processing, will access the D_STAFF table, as if to two different tables (under the pseudonyms STAFF and CHIEF), in order to combine the records into a single court Based on the relationship "Chief - subordinate". The ratio is simulated as follows: The value of the S_CHIEF_ID field in the subordinate corresponds to the value of the xD_iid field value.

SELECT STAFF.S_NAME AS Subordinate, STAFF.S_Position AS [Position of the subordinate], chief.s_name as head, chief.s_position AS [Position of the head] from d_staff as staff, d_staff as chief where staff.s_chief_id \u003d chief.xd_iid


Getting a hierarchy "Head - Slave" using a table alias in SQL.

Each web developer should know SQL to write requests to databases. And, although, phpmyadmin no one canceled, it is often necessary to stain his hands to write a low-level SQL.

That is why we have prepared a brief excursion on the basics of SQL. Let's start!

1. Creating a table

Create Table instruction is designed to create tables. As arguments, the name of the columns, as well as their data types, should be specified.

Create a simple table by name mONTH.. It consists of 3 columns:

  • id - number of months in the calendar year (integer).
  • name. - Name of the month (string, maximum of 10 characters).
  • days. - Number of days this month (integer).

This is how the corresponding SQL query will look like:

CREATE TABLE MONTHS (ID INT, NAME VARCHAR (10), DAYS INT);

Also when creating tables, it is advisable to add the primary key for one of the columns. This will keep the records unique and speed up requests for the sample. Let in our case the name of the month will be unique (column name.)

CREATE TABLE MONTHS (ID INT, NAME VARCHAR (10), DAYS INT, PRIMARY KEY (NAME));

date and time
Data typeDescription
Date.Date values
DateTime.Date and time values \u200b\u200bup to Mint
Time.Time values

2. Inserting string

Now let's fill out our table monhs. Useful information. Adding records to the table is made through the Insert instruction. There are two ways to record this instruction.

The first way is not to specify the names of the columns where the data will be inserted, and indicate only values.

This method of recording is simple, but unsafe, since there is no guarantee that as the project expansion and edit the table, the columns will be located in the same order as before. Safe (and at the same time more cumbersome) The method of recording Insert instructions requires an indication of both values \u200b\u200band the order of the columns:

Here is the first value in the list. Values. Corresponds to the first specified column name, etc.

3. Extraction of data from tables

SELECT statement is our best friend when we want to get data from the database. It is used very often, so take this section very carefully.

The simplest use of the SELECT statement is a query that returns all columns and lines from the table (for example, tables by name characters.):

SELECT * from "Characters"

An asterisk symbol (*) means that we want to get data from all columns. So, SQL databases usually consist of more than one table, it is required to specify the keyword from, followed by which the table should be followed through the space.

Sometimes we do not want to get data from all columns in the table. For this, instead of an asterisk (*), we must write the names of the desired columns through the comma.

SELECT ID, NAME FROM MONTH

In addition, in many cases, we want the results to be sorted in a certain order. In SQL, we do it with ORDER BY. It can receive an optional modifier - ASC (default) ascending or DESC, sorting descending:

SELECT ID, NAME FROM MONTH ORDER BY NAME DESC

When using ORDER BY, make sure that it will be the last in the SELECT statement. Otherwise, an error message will be issued.

4. Data filtering

You learned how to choose from a database using SQL query strictly defined columns, but what if we need to get some more lines? To the aid, the WHERE condition comes to the rescue, allowing us to filter the data depending on the condition.

In this query, we only choose the months from the table mONTH., in which more than 30 days with the help of the operator more (\u003e).

SELECT ID, NAME FROM MONTH WHERE DAYS\u003e 30

5. Extended data filtering. Operators and or OR

Earlier, we used filtering data using one criterion. For more complex data filtering, you can use and or OR operators and comparison operators (\u003d,<,>,<=,>=,<>).

Here we have a table containing the four best album albums of all time. Let's choose those of them that are classified as rock and in which there are less than 50 million copies sold. This can be easily done by placing the operator and between these two conditions.


SELECT * from Albums Where Genre \u003d "Rock" and sales_in_millions<= 50 ORDER BY released

6. IN / BETWEEN / LIKE

WHERE also supports several special commands, allowing you to quickly check the most frequently used requests. Here they are:

  • In - serves to specify the range of conditions, any of which can be performed
  • Between - checks whether the value is in the specified range
  • Like - looking for specific patterns

For example, if we want to choose albums with pop and soul Music, we can use in ("Value1", "Value2").

SELECT * from Albums Where Genre In ("Pop", "Soul");

If we want to get all the albums, published between 1975 and 1985, we must write:

SELECT * from albums Where Released Between 1975 and 1985;

7. Functions

SQL Stuffers with features that make different useful things. Here are some of the most commonly used:

  • Count () - Returns the number of rows
  • SUM () - Returns the total amount of the numerical column
  • AVG () - Returns the average value from a variety of values
  • Min () / max () - gets minimal / maximum value from column

To get the very last year in our table we must write such a SQL query:

SELECT MAX (Released) from albums;

8. Subqueries

In the previous paragraph, we learned how to make simple calculations with data. If we want to use the result from these calculations, we can not do without invested requests. Suppose we want to withdraw aRTIST., album and release year For the oldest album in the table.

We know how to get these specific columns:

SELECT ARTIST, ALBUM, REEDASED FROM ALBUMS;

We also know how to get the earliest year:

SELECT MIN (Released) from album;

All you need is to combine two requests using WHERE:

Select Artist, Album, Released from Albums Where Released \u003d (Select Min (Released) from Albums);

9. Combining tables

In more complex databases there are several tables associated with each other. For example, two tables about video games are presented below. video_Games.) and video game developers ( game_Developers.).


Table video_Games. There is a developer column ( developer_id), but it contains an integer, and not the name of the developer. This number is an identifier ( id) The appropriate developer from the game developer table ( game_Developers.) By tying the logically two list, which allows us to use the information stored in them both at the same time.

If we want to create a request that returns everything you need to know about games, we can use Inner Join to communicate speakers from both tables.

Select video_games.name, video_games.genre, game_developers.name, game_developers.country from Video_Games Inner Join Game_Developers on video_games.developer_id \u003d Game_Developers.id;

This is the easiest and most common type of Join. There are several other options, but they apply to less frequent cases.

10. Alias.

If you look at the previous example, you will notice that there are two columns called name.. It confuses, so let's install one of the repetitive columns, for example, name. from the table game_Developers. will be called developer..

We can also reduce the request specifying table name pseudonyms: video_Games. Name games., game_Developers. - devs.:

SELECT GAMES.NAME, GAMES.GENRE, devs.name as developer, devs.country from Video_games as Games Inner Join Game_Developers as devs on games.developer_id \u003d devs.id;

11. Data update

Often we must change the data in some rows. In SQL, this is done using the UPDATE instruction. Update statement consists of:

  • Tables in which the value is for replacement;
  • Names of columns and their new values;
  • Selected with the WHERE line that we want to update. If this is not done, then all the lines will change in the table.

Below is a table tV_Series. With TV series with their rating. However, the table crept a small error: although the series Game of Thrones And described as a comedy, he is actually not it. Let's fix it!

Table data TV_SERIES UPDATE TV_SERIES SET GENRE \u003d "DRAM" WHERE ID \u003d 2;

12. Deleting data

Deleting a table row with SQL is a very simple process. All you need is to choose a table and a string that you want to remove. Let's delete the last string from the previous example in the table. tV_Series.. This is done using the instruction\u003e Delete

Delete from TV_SERIES WHERE ID \u003d 4

Be careful when writing the Delete instruction and make sure that the WHERE condition is present, otherwise all the table lines will be deleted!

13. Delete table

If we want to delete all the lines, but leave the table itself, then use the TRUNCATE command:

Truncate Table Table_Name;

In the case when we actually want to delete and the data, and the table itself, we will use the DROP command:

DROP Table Table_Name;

Be very careful with these teams. They cannot be canceled! / P\u003e

On this we complete our SQL tutorial! We have not told a lot about, but what you already know should be enough to give you some practical skills in your web career.

We continue to study the possibilities of SQL Server from Microsoft and in turn we have a component FULL-TEXT SEARCH, Russian version is " Full text search", And now we will find out why it is needed, and how to implement this most full-text search in the SQL server using this component.

And let's start, of course, with consideration of the foundations of full-text search, i.e. What it is and for which it is generally needed.

What is a full-text search?

Full text search - This is a search for words or phrases in text data. Usually, such a type of search is used to search for text in a large amount of data, for example, a table with a million or more rows, since it is significantly faster than normal search, which can be implemented using the LIKE design.

Full-text search involves creating a special index ( it differs from ordinary indexes) Text data, which is a kind of dictionary of words that are found in this data.

Using full-text search, you can implement a kind search engine documents ( those. Row), according to or phrases in the database of your company. Since in addition to its fast work, it also has the ability to rank found documents, i.e. Store the rank of each found line, in other words, you can find the most relevant records, i.e. The most suitable for your request.

Full-text search features in MS SQL Server

  • In full-text search SQL servers, you can search not only by individual words or phrases, but also by prefix expressions, for example, set the text beginning of the word or phrase;
  • You can also search for words on wordforms, for example, various forms of verbs or nouns in the only and plural, i.e. by derived expressions;
  • You can build a query so as to find words or phrases located next to other words or phrases, i.e. expressions;
  • There is a possibility to find synonymous forms of a particular word (thesaurus), i.e. For example, if in thesaurus it is determined that " Car"And" A car"- these are synonyms, then when searching for the word" Car»The resulting set will include lines containing the word" A car»;
  • In the query you can specify words or phrases with weighted values, for example, if a few words or phrases are specified in the query, then they can be assigned importance from 0.0 to 1.0 ( 1.0 means that this is the most important word or phrase);
  • In order not to consider in the search for some words can be used " list of stop words", I.e. According to this list, the search will not be executed.

Preparation for the implementation of full-text search in MS SQL Server

Before you begin creating a full-text search, you need to know several important points:

  • To implement full-text search component Full-Text Search ( Full text search) must be installed;
  • The table may have only one full-text index;
  • To create a full-text index, the table must contain one unique index that includes one column and does not allow NULL values. It is recommended to use a unique clustered index ( or just primary key), the first column of which must have an integer data type;
  • Full-text index can be created on columns with data type: Char, Varchar, Nchar, Nvarchar, Text, NText, Image, XML, Varbinary or Varbinary (MAX);
  • In order to create a full-text index, you must first create a full-text directory. Starting with SQL Server 2008, a full-text catalog is a logical concept denoting a group of full-text indexes, i.e. is a virtual object and is not included in the file group ( there is a way to create a full-text index using the "master", in which the directory can be created simultaneously with the index, this method we will consider just below).

Note! Implement full-text search I will be on the example version of SQL Server 2008 R2. It is also implied that the Full-Text Search component is already installed if not, then install it by adding the corresponding component through the "SQL Server Installation Center", i.e. Put the appropriate tick.

In the examples below as a tool for creating and managing full-text directories and indexes, I will use SQL Server Management Studio.

Source data to create full-text search

We assume that we have a TestBase database, and there is a TestTable table in which there are only two fields, the first (ID) is the primary key, and the second (TextData) is a text data for which we will implement a full-text search. .

Create Table TestTable (ID Int Intentity (1,1) Not Null, TextData Varchar (500) NULL, CONSTRAINT PK_TESTTABLE PRIMARY KEY CLUSTERED (ID ASC))

For example, it will contain the following data.


Creating a full-text directory in SQL Server

To create a full-text directory, as well, and the index can be used or the SSMS graphical interface, or T-SQL instructions, we will analyze both ways.

Creating a full-text directory on T-SQL

CREATE FULLTEXT CATALOG TESTCATALOG WITH ACCENT_SENSITIVITY \u003d ON AS DEFAULT AUTHORIZATION DBO GO

  • CREATE FULLTEXT CATALOG - command of creating a full-text directory;
  • Testcatalog - the name of our full-text catalog;
  • With accent_sensitivity (ON | OFF) - the option indicates whether the full-text catalog will take into account the diacritical signs for full-text indexing. By default ON;
  • AS Default - option in order to specify that the directory is the default directory. In the event of a full-text index, the default directory is used without explicitly specifying the directory;
  • Authorization DBO - Sets the owner of the full-text directory, it can be a user or database role. IN this case We indicated the role of DBO.

Creating a full-text catalog in graphic interface Management Studio.

The exact same full-text directory can be created in the Management Studio graphical interface. To do this, open the database, go to the folder Storage -\u003e Full-text catalogs, click on the right mouse button on this item and choose " Create a full-text catalog».


A catalog creation window will open, where we specify the name of the directory and its options.


Changing and removing a full-text catalog in SQL Server

To change the directory options, you can use the Alter Fulltext Catalog instruction, for example, let's make our catalog to take into account the diacritical signs, for this we write SQL instruction that will rebuild our directory with a new option.

ALTER FULLTEXT CATALOG TESTCATALOG REBUILD WITH ACCENT_SENSITIVITY \u003d OFF GO

In order to delete the directory, you can use the T-SQL instruction, for example

DROP FULLTEXT CATALOG TESTCATALOG

All this could be done in the GUI GUIEMENT STUDIO ( to change the parameters of the "Properties" directory, to delete "Delete")

Creating a full-text index in SQL Server

After creating a full-text directory, you can start creating full-text index in it. In our case, we want to create a full-text index, in which the TestTable TextData field is involved.

Creating a full-text index on T-SQL

In order to create a full-text index, you can write the following SQL instruction

CREATE FULLTEXT INDEX ON TESTTABLE (TEXTDATA) KEY INDEX PK_TESTTABLE ON (TESTCATALOG) WITH (CHANGE_TRACKING AUTO) GO

  • CREATE FULLTEXT INDEX - command of creating a full-text index;
  • TestTable (TextData) - table and column included in the index;
  • Key Index PK_testTable - the name of the unique index of the TestTable table;
  • ON (Testcatalog) - indicate that the full-text index will be created in the Testcatalog full-text directory. If you do not specify this parameter, the index will be created in the default full-text directory;
  • With (change_tracking auto) - we say that all changes that will be entered into the base table (TestTable) will automatically appear in our full-text index, i.e. Automatic filling.

Creating a full-text index in the MANAGEMENT STUDIO GUI

The full-text index can be created using and graphic tools, for this open the properties of the full-text directory and go to the item " Tables or views", Select the desired table, field, a unique index and a way to track changes. In our case, we have only one available table and one field.


Change and removal of the full-text index

If necessary, you can change the parameters of the full-text index. Let's change the way to track changes from automatic on hand. To change in the graphical interface, you can use the " Full-text catalog properties -\u003e Tables or views", Which we used when creating a full-text index.

Or you can write the following code

ALTER FULLTEXT INDEX ON TESTTABLE SET CHANGE_TRACKING \u003d MANUAL

In order to delete the full-text index, it is enough to simply delete the table from the list of objects associated with the full-text directory in the same window " Full-text catalog properties -\u003e Tables or views»


Or write a T-SQL code

DROP FULLTEXT INDEX ON TESTTABLE

Creating a full-text catalog and index using a wizard

As I have already mentioned a first-text directory and the index can be created using the master, i.e. By steps, for this, click the right mouse button on the table that we want to include in full-text search, and choose " Full-text index -\u003e Determine full-text index».

Note! Before that, I deleted the directory, and the index that we created in the previous examples.


As a result, the SQL Server full-text indexing wizard will be launched.



Then the column that will be included in the full-text index.


Then you need to select a method for tracking changes.


Indicate the name of the catalog and its options, in order to create it, as it is assumed that there are no directory if it were, then we could choose it.


Here we can customize the timetable for filling the full-text catalog.


To create a catalog and index left to click " Ready».


In the next window, we will see the result of the execution of operations to create a full-text directory and index. In my case, everything was successful.


Thus, we completed the creation of a full-text directory and an index at the same time using a wizard.

Examples of full-text requests

I will immediately say that in more detail full-text requests we will be considered in the following materials, and so far, as an example and confirm that our full-text search is working, let's write a couple of simple full-text requests.

If you remember, our TestTable table contains definitions of technologies, programming languages, in general, the definitions of the sphere-related IT. Suppose that we want to get all the records where there is a mention of Microsoft, for this we write a full-text request with keyword Contains, for example:

SELECT * from TestTable Where Contains (TextData, "" Microsoft ")


We got the result, but for example, we also need to sort it on relevance, in other words, which lines correspond to our request. To do this, we will use the CONTAINSTABLE function, which lies the rank for each entry found.

Select table1.id as id, rowrank.rank as, Table1.TextData AS from TestTable Table1 Inner Join Containstable (TestTable, TextData, "" Microsoft ") AS Rowrank on table1.id \u003d rowrank. Order by Rowrank.rank Desc


As you can see, the rank is affixed and lines are sorted. The ranking algorithm itself, as more information on full-text search, can be found in the SQL Server electronic documentation.

This suggests to finish, I hope everything was clear, good luck!

Requests are written without shielding quotes, since Mysql, MS SQL. and Postgree they are different.

SQL Request: Obtaining the specified (necessary) fields from the table

SELECT ID, COUNTRY_TITLE, COUNT_PEOPLE FROM TABLE_NAME

We receive a list of records: all countries and their population. The name of the desired fields is indicated by commas.

SELECT * from table_name

* Indicates all fields. That is, there will be shows EVERYTHING Data fields.

SQL Request: Display Records from Table Excluding Duplicates

Select Distinct Country_Title from Table_Name

We receive a list of records: countries where our users are located. Users can be a lot of one country. In this case, this is your request.

SQL Request: Display Records from Table on a given Condition

SELECT ID, COUNTRY_TITLE, CITY_TITLE FROM TABLE_NAME WHERE COUNT_PEOPLE\u003e 100000000

We receive a list of records: countries where the number of people is more than 100,000,000.

SQL Request: Display Records from Application Table

SELECT ID, CITY_TITLE FROM Table_Name Order by city_title

We receive a list of records: cities in alphabetical order. At the beginning A, at the end of Ya.

SELECT ID, CITY_TITLE FROM TABLE_NAME ORDER by city_title desc

We receive a list of records: Cities in the opposite ( DESC.). At the beginning, I, at the end of A.

SQL query: counting the number of records

SELECT COUNT (*) from table_name

We get the number (number) of records in the table. In this case, there is no list of records.

SQL query: output of the desired record range

Select * from Table_Name Limit 2, 3

We get 2 (second) and 3 (third) entry from the table. The request is useful when you create a navigation on the Web pages.

SQL requests with conditions

Display entries from the table for a given condition using logical operators.

SQL Request: Construction and (and)

SELECT ID, CITY_TITLE FROM TABLE_NAME WHERE COUNTRY \u003d "RUSSIA" AND OIL \u003d 1

We receive a list of records: Cities from Russia AND Have access to oil. When operator is used And., I must coincide both conditions.

SQL Request: Design OR (or)

SELECT ID, CITY_TITLE FROM TABLE_NAME WHERE COUNTRY \u003d "RUSSIA" OR COUNTRY \u003d "USA"

We receive a list of records: all cities from Russia OR USA. When operator is used Or., it should coincide at least one condition.

SQL query: design and not (and not)

SELECT ID, User_Login from Table_Name Where Country \u003d "Russia" and not count_comments<7

We receive a list of records: All users from Russia AND Made NOT LESS 7 comments.

SQL Request: In (B) Design

SELECT ID, User_Login from Table_Name Where Country In ("Russia", "Bulgaria", "China")

We receive a list of records: All users live in ( IN.) (Russia, or Bulgaria, or China)

SQL Request: NOT IN Design (not in)

SELECT ID, User_Login from Table_Name Where Country Not In ("Russia", "China")

We receive a list of records: All users who live are not in ( NOT IN.) (Russia or China).

SQL Request: IS NULL design (empty or not empty values)

SELECT ID, User_Login from Table_name WHERE STATUS IS NULL

We receive a list of records: All users, where Status is not defined. Null is a separate topic and therefore it is checked separately.

SELECT ID, User_Login from Table_Name Where State IS Not Null

We receive a list of records: All users, where Status is defined (not zero).

SQL Request: Like Design

SELECT ID, User_Login from Table_name Where Surname Like "Ivan%"

We receive a list of records: Users who have a surname begins with the combination "Ivan". The% sign means any number of any characters. To find a sign% you need to use the screening "Ivan \\%".

SQL query: Between design

SELECT ID, User_Login from Table_Name Where Salary Between 25000 and 50000

We receive a list of records: Users who receive salary from 25,000 to 50,000 inclusive.

Logic operators are very much, so you will study the SQL server documentation in detail.

Complex SQL requests

SQL query: combining multiple requests

(SELECT ID, User_Login from Table_name1) Union (Select ID, User_Login from Table_name2)

We receive a list of records: Users who are registered in the system, as well as those users who are registered on the forum separately. The Union operator can be combined several requests. Union acts like Select Distinct, that is, discarding repetitive values. To get absolutely all records, you need to use the UNION ALL operator.

SQL Request: Counting Max, Min, Sum, Avg, Count field values

Conclusion of one, maximum counter value in the table:

SELECT MAX (Counter) from table_name

Output one, minimum counter values \u200b\u200bin the table:

SELECT MIN (Counter) from table_name

The output of all the values \u200b\u200bof the meters in the table:

SELECT SUM (Counter) from table_name

The output of the average meter value in the table:

SELECT AVG (Counter) from table_name

The output of the number of meters in the table:

SELECT COUNT (Counter) from table_name

The output of the number of meters in the workshop number 1, in the table:

SELECT COUNT (Counter) from table_name where office \u003d "shop number 1"

These are the most popular teams. It is recommended where it is possible to use the SQL requests for counting this kind, since no programming environment is compared at data processing speed than the SQL server itself when processing its own data.

SQL query: grouping records

Select Continent, Sum (Country_area) from Country Group by Continent

We receive a list of records: with the name of the continent and with the sum of the squares of all their countries. That is, if there is a reference book of countries where each country has its area, then using the GROUP BY design, you can find out the size of each continent (based on grouping by continents).

SQL Request: Using Multiple Tables through Alias \u200b\u200b(Alias)

Select O.Order_no, O.amount_Paid, C.com ORDERS AS O, Customer AS with WHERE O.CUSTNO \u003d C.Custno and C.City \u003d "Tyumen"

We receive a list of records: orders from buyers who live only in Tyumen.

In fact, with a properly projected database of this type, the query is most frequent, so a special operator was introduced into MySQL, which works at times faster than the above-mentioned code.

Select O.Order_No, O.amount_paid, z.comPany from Orders AS O Left Join Customer AS Z ON (z.custno \u003d O.CUSTNO)

Nested subqueries

SELECT * from Table_Name Where Salary \u003d (SELECT MAX (Salary) from Employee)

We get one record: user information with maximum salary.

Attention! Nested subqueries are one of the most narrow seats in SQL servers. Together with its flexibility and power, they also significantly increase the load on the server. What leads to a catastrophic slowdown of other users. There are very frequent cases of recursive calls when attached queries. Therefore, I strongly recommend not to use invested requests, but split them into smaller. Or use the above-described Left Join combination. In addition to this type, requests are an elevated focus of security breach. If you decide to use nested subqueries, then it is necessary to design them very carefully and initial starts to do on database copies (test bases).

SQL Requests Changing Data

SQL Request: INSERT

Instruction Insert. allow you to insert records in the table. Simple words, create a line with data in the table.

Option number 1. Instruction is often used:

INSERT INTO TABLE_NAME (ID, USER_LOGIN) VALUES (1, "IVANOV"), (2, "Petrov")

In the table " table_Name."Will be inserted 2 (two) users immediately.

Option number 2. It is more convenient to use style:

INSERT TABLE_NAME SET ID \u003d 1, User_Login \u003d "Ivanov"; INSERT TABLE_NAME SET ID \u003d 2, User_Login \u003d "Petrov";

This has its advantages and disadvantages.

Basic Disadvantages:

  • Many small SQL queries are performed slightly slower than one big SQL query, but other requests will stand in the service queue. That is, if a large SQL query will be completed 30 minutes, then in all this time, the rest of the requests will smoke bamboo and wait for their turn.
  • The request is massive than the previous option.

Main advantages:

  • During small SQL requests, other SQL requests are not blocked.
  • Convenience in reading.
  • Flexibility. In this embodiment, you can not comply with the structure, but add only the necessary data.
  • When forming similarly archives, you can easily copy one line and start it through the command line (console), thereby not restoring the entire archive.
  • The record style is similar to the UPDATE instruction, which is easier to remember.

SQL Request: Update

Update Table_Name set user_login \u003d "Ivanov", user_surname \u003d "Ivanov" Where id \u003d 1

In the table " table_Name."In records with ID number \u003d 1, the values \u200b\u200bof the user_login and user_surname fields will be changed to the specified values.

SQL Request: Delete

Delete from Table_Name WHERE ID \u003d 3

The table_name table will be deleted with the number 3 ID.

  1. All field names are recommended to write with small letters and if necessary, divide them through the forced space "_" for compatibility with different programming languages, such as Delphi, Perl, Python and Ruby.
  2. SQL teams writing in large letters for readability. Remember always that after you can read the code and other people, and most likely you yourself through N the amount of time.
  3. Call the fields from the beginning of the noun, and then action. For example: city_status, user_login, user_name.
  4. Try to avoid backup words in different languages \u200b\u200bthat can cause problems in SQL, PHP or Perl languages, such as (Name, Count, Link). For example: Link can be used in MS SQL, but in MySQL reserved.

This material is a short certificate for everyday work and does not pretend to a super mega authoritative source, which is the source of SQL queries of a database.

Similar articles