Examples of SQL queries to the MySQL database. Three magic words

Every web developer needs to know SQL to write database queries. And, although phpMyAdmin has not been canceled, it is often necessary to get your hands dirty to write low-level SQL.

That is why we have prepared short excursion By SQL basics. Let's get started!

1. Create a table

The CREATE TABLE statement is used to create tables. The arguments must be the names of the columns, as well as their data types.

Let's create a simple table by name month. It consists of 3 columns:

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

This is what the corresponding SQL query would look like:

CREATE TABLE months (id int, name varchar(10), days int);

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

CREATE TABLE months (id int, name varchar(10), days int, PRIMARY KEY (name));

date and time

Data type	Description
DATE	Date values
DATETIME	Date and time values ​​accurate to the minute
TIME	Time values

2. Inserting rows

Now let's fill out our table months useful information. Adding records to a table is done using the INSERT statement. There are two ways to write this instruction.

The first method is not to specify the names of the columns where the data will be inserted, but to specify only the values.

This recording method is simple, but unsafe, since there is no guarantee that as the project expands and the table is edited, the columns will be in the same order as before. A safe (and at the same time more cumbersome) way of writing an INSERT statement requires specifying both the values ​​and the order of the columns:

Here is the first value in the list VALUES matches the first the specified name column, etc.

3. Extracting data from tables

The SELECT statement is ours best friend when we want to get data from the database. It is used very often, so pay very close attention to this section.

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

SELECT * FROM "characters"

The asterisk (*) symbol means we want to get data from all columns. So base SQL data usually consist of more than one table, it is required to specify keyword FROM , followed by the table name separated by a space.

Sometimes we don't want to get data from not all columns in a table. To do this, instead of an asterisk (*), we must write down the names of the desired columns, separated by commas.

SELECT id, name FROM month

Additionally, in many cases we want the resulting results to be sorted in a specific order. In SQL we do this using ORDER BY. It can accept an optional modifier - ASC (default) sorting in ascending order or DESC, sorting in descending order:

SELECT id, name FROM month ORDER BY name DESC

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

4. Data filtering

You learned how to select from a database using SQL query strictly defined columns, but what if we also need to get certain strings? The WHERE clause comes to the rescue here, allowing us to filter the data depending on the condition.

In this query we are selecting only those months from the table month, in which there are more than 30 days using the greater than (>) operator.

SELECT id, name FROM month WHERE days > 30

5. Advanced data filtering. AND and OR operators

Previously, we used data filtering using a single criterion. For more complex data filtering, you can use the AND and OR operators and comparison operators (=,<,>,<=,>=,<>).

Here we have a table containing the four best-selling albums of all time. Let's choose the ones that are classified as rock and have sold less than 50 million copies. This can be easily done by placing an AND operator between these two conditions.

SELECT * FROM albums WHERE genre = "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 queries. Here they are:

• IN – serves to indicate a range of conditions, any of which can be met
• BETWEEN – checks if a value is within the specified range
• LIKE – searches for specific patterns

For example, if we want to select 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 released between 1975 and 1985, we have to write:

SELECT * FROM albums WHERE released BETWEEN 1975 AND 1985;

7. Functions

SQL is packed with functions that do all sorts of useful things. Here are some of the most commonly used:

• COUNT() – returns the number of rows
• SUM() - returns the total sum of a numeric column
• AVG() - returns the average of a set of values
• MIN() / MAX() – gets the minimum / maximum value from column

To get the most recent year in our table, we must write the following SQL query:

SELECT MAX(released) FROM albums;

8. Subqueries

In the previous paragraph, we learned how to do simple calculations with data. If we want to use the result from these calculations, we cannot do without nested queries. Let's say we want to output artist, album And release year for the oldest album in the table.

We know how to get these specific columns:

SELECT artist, album, released FROM albums;

We also know how to get the earliest year:

SELECT MIN(released) FROM album;

All that is needed now is to combine the two queries using WHERE:

SELECT artist,album,released FROM albums WHERE released = (SELECT MIN(released) FROM albums);

9. Joining tables

In more complex databases, there are multiple tables related to each other. For example, below are two tables about video games ( video_games) and video game developers ( game_developers).

In the table video_games there is a developer column ( developer_id), but it contains an integer, not the name of the developer. This number represents the identifier ( id) of the corresponding developer from the table of game developers ( game_developers), logically linking two lists, allowing us to use the information stored in both of them at the same time.

If we want to create a query that returns everything we need to know about games, we can use an INNER JOIN to link columns 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 = game_developers.id;

This is the simplest and most common JOIN type. There are several other options, but these apply to less common cases.

10. Aliases

If you look at the previous example, you will notice that there are two columns called name. This is confusing, so let's set an alias to one of the repeating columns, like this name from the table game_developers will be called developer.

We can also shorten the query by aliasing the table names: video_games let's call 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 = devs.id;

11. Data update

Often we need to change the data in some rows. In SQL this is done using the UPDATE statement. The UPDATE statement consists of:

• The table in which the replacement value is located;
• Column names and their new values;
• The rows selected using WHERE that we want to update. If this is not done, all rows in the table will change.

Below is the table tv_series with TV series and their ratings. However, a small error crept into the table: although the series Game of Thrones and is described as a comedy, it really isn't. Let's fix this!

Table data tv_series UPDATE tv_series SET genre = "drama" WHERE id = 2;

12. Deleting data

Deleting a table row using SQL is a very simple process. All you need to do is select the table and row you want to delete. Let's delete the last row in the table from the previous example tv_series. This is done using the >DELETE instruction.

DELETE FROM tv_series WHERE id = 4

Be careful when writing the DELETE statement and make sure the WHERE clause is present, otherwise all rows in the table will be deleted!

13. Delete a table

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

TRUNCATE TABLE table_name;

In the case when we actually want to delete both the data and the table itself, then the DROP command will be useful to us:

DROP TABLE table_name;

Be very careful with these commands. They cannot be canceled!/p>

This concludes our SQL tutorial! There's a lot we haven't covered, but what you already know should be enough to give you some practical skills for your web career.

Queries are written without escape quotes, since MySQL, MS SQL And PostGree they are different.

SQL query: getting the specified (necessary) fields from the table

SELECT id, country_title, count_people FROM table_name

We get a list of records: ALL countries and their populations. The names of the required fields are indicated separated by commas.

SELECT * FROM table_name

* denotes all fields. That is, there will be shows EVERYTHING data fields.

SQL query: outputting records from a table excluding duplicates

SELECT DISTINCT country_title FROM table_name

We get a list of records: countries where our users are located. There can be many users from one country. In this case, it is your request.

SQL query: displaying records from a table based on a given condition

SELECT id, country_title, city_title FROM table_name WHERE count_people>100000000

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

SQL query: displaying records from a table with ordering

SELECT id, city_title FROM table_name ORDER BY city_title

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

SELECT id, city_title FROM table_name ORDER BY city_title DESC

We get a list of records: cities in reverse ( DESC) okay. At the beginning I, at the end A.

SQL query: counting the number of records

SELECT COUNT(*) FROM table_name

We get the number (number) of records in the table. IN in this case NO list of entries.

SQL query: outputting the desired range of records

SELECT * FROM table_name LIMIT 2, 3

We get 2 (second) and 3 (third) records from the table. The query is useful when creating navigation on WEB pages.

SQL queries with conditions

Displaying records from a table based on a given condition using logical operators.

SQL query: AND construction

SELECT id, city_title FROM table_name WHERE country="Russia" AND oil=1

We get a list of records: cities from Russia AND have access to oil. When to use the operator AND, then both conditions must match.

SQL query: OR construct

SELECT id, city_title FROM table_name WHERE country="Russia" OR country="USA"

We get a list of records: all cities from Russia OR USA. When to use the operator OR, then AT LEAST one condition must match.

SQL query: AND NOT construction

SELECT id, user_login FROM table_name WHERE country="Russia" AND NOT count_comments<7

We get a list of records: all users from Russia AND who made NOT LESS 7 comments.

SQL query: IN construction (B)

SELECT id, user_login FROM table_name WHERE country IN ("Russia", "Bulgaria", "China")

We get a list of records: all users who live in ( IN) (Russia, or Bulgaria, or China)

SQL query: NOT IN construction

SELECT id, user_login FROM table_name WHERE country NOT IN ("Russia","China")

We get a list of records: all users who do not live in ( NOT IN) (Russia or China).

SQL query: IS NULL construct (empty or NOT empty values)

SELECT id, user_login FROM table_name WHERE status IS NULL

We get a list of records: all users where status is not defined. NULL is a separate issue and is therefore checked separately.

SELECT id, user_login FROM table_name WHERE state IS NOT NULL

We get a list of records: all users where status is defined (NOT NULL).

SQL query: LIKE construction

SELECT id, user_login FROM table_name WHERE surname LIKE "Ivan%"

We get a list of records: users whose last name begins with the combination “Ivan”. The % sign means ANY number of ANY characters. To find the % sign you need to use the “Ivan\%” escape.

SQL query: BETWEEN construction

SELECT id, user_login FROM table_name WHERE salary BETWEEN 25000 AND 50000

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

There are a LOT of logical operators, so study the SQL server documentation in detail.

Complex SQL queries

SQL query: combining multiple queries

(SELECT id, user_login FROM table_name1) UNION (SELECT id, user_login FROM table_name2)

We get a list of entries: users who are registered in the system, as well as those users who are registered separately on the forum. The UNION operator can combine multiple queries. UNION acts like SELECT DISTINCT, that is, it discards duplicate values. To get absolutely all records, you need to use the UNION ALL operator.

SQL query: counting field values ​​MAX, MIN, SUM, AVG, COUNT

Displaying one, maximum counter value in the table:

SELECT MAX(counter) FROM table_name

Output of one, minimum counter value in the table:

SELECT MIN(counter) FROM table_name

Displaying the sum of all counter values ​​in the table:

SELECT SUM(counter) FROM table_name

Displaying the average counter value in the table:

SELECT AVG(counter) FROM table_name

Displaying the number of counters in the table:

SELECT COUNT(counter) FROM table_name

Displaying the number of meters in workshop No. 1 in the table:

SELECT COUNT(counter) FROM table_name WHERE office="Workshop No. 1"

These are the most popular teams. It is recommended, where possible, to use SQL queries of this kind for calculations, since no programming environment can compare in 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 get a list of records: with the name of the continent and the sum of the areas of all their countries. That is, if there is a directory of countries where each country has its area recorded, then using the GROUP BY construct you can find out the size of each continent (based on grouping by continents).

SQL query: using multiple tables via alias

SELECT o.order_no, o.amount_paid, c.company FROM orders AS o, customer AS with WHERE o.custno=c.custno AND c.city="Tyumen"

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

In fact, with a properly designed database of this type, the request is the most frequent, so a special operator was introduced into MySQL that works many times faster than the above written code.

SELECT o.order_no, o.amount_paid, z.company FROM orders AS o LEFT JOIN customer AS z ON (z.custno=o.custno)

Nested Subqueries

SELECT * FROM table_name WHERE salary=(SELECT MAX(salary) FROM employee)

We get one record: information about the user with the maximum salary.

Attention! Nested subqueries are one of the biggest bottlenecks in SQL servers. Together with their flexibility and power, they also significantly increase the load on the server. Which leads to a catastrophic slowdown for other users. Cases of recursive calls in nested queries are very common. Therefore, I strongly recommend NOT using nested queries, but breaking them into smaller ones. Or use the LEFT JOIN combination described above. In addition, this type of request is an increased source of security violations. If you decide to use nested subqueries, then you need to design them very carefully and make initial runs on copies of databases (test databases).

SQL queries changing data

SQL query: INSERT

Instructions INSERT allow you to insert records into a table. In simple words, create a row with data in a table.

Option #1. The instruction that is often used is:

INSERT INTO table_name (id, user_login) VALUES (1, "ivanov"), (2, "petrov")

To the table " table_name"2 (two) users will be inserted at once.

Option #2. It is more convenient to use the style:

INSERT table_name SET id=1, user_login="ivanov"; INSERT table_name SET id=2, user_login="petrov";

This has its advantages and disadvantages.

Main disadvantages:

• Many small SQL queries will execute a little slower than one large SQL query, but other queries will queue for service. That is, if a large SQL query takes 30 minutes to complete, then during all this time the remaining queries will smoke bamboo and wait their turn.
• The request turns out to be more massive than the previous version.

Main advantages:

• During small SQL queries, other SQL queries are not blocked.
• Ease of reading.
• Flexibility. In this option, you don’t have to follow the structure, but add only the necessary data.
• When creating archives in this way, you can easily copy one line and run it through the command line (console), thereby not restoring the entire ARCHIVE.
• The writing style is similar to the UPDATE statement, making it easier to remember.

SQL query: UPDATE

UPDATE table_name SET user_login="ivanov", user_surname="Ivanov" WHERE id=1

In the table " table_name"in the record with number id=1, the values ​​of the user_login and user_surname fields will be changed to the specified values.

SQL query: DELETE

DELETE FROM table_name WHERE id=3

In table table_name the record with id number 3 will be deleted.

1. It is recommended to write all field names in small letters and, if necessary, separate them with a forced space “_” for compatibility with different programming languages, such as Delphi, Perl, Python and Ruby.
2. Write SQL commands in CAPITAL letters for readability. Always remember that other people can read the code after you, and most likely you yourself after N amount of time.
3. Name the fields first with a noun and then with an action. For example: city_status, user_login, user_name.
4. Try to avoid reserve words in different languages ​​that can cause problems in SQL, PHP or Perl, such as (name, count, link). For example: link can be used in MS SQL, but is reserved in MySQL.

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

SQL language is used to retrieve data from the database. SQL is a programming language that closely resembles English but is intended for database management programs. SQL is used in every query in Access.

Understanding how SQL works helps you create more accurate queries and makes it easier to correct queries that return incorrect results.

This is an article from a series of articles about the SQL language for Access. It describes the basics of using SQL to retrieve data and provides examples of SQL syntax.

In this article

What is SQL?

SQL is a programming language designed to work with sets of facts and the relationships between them. Relational database management programs such as Microsoft Office Access use SQL to manipulate data. Unlike many programming languages, SQL is readable and understandable even for beginners. Like many programming languages, SQL is an international standard recognized by standards committees such as ISO and ANSI.

Data sets are described in SQL to help answer questions. When using SQL, you must use the correct syntax. Syntax is a set of rules that allow the elements of a language to be combined correctly. SQL syntax is based on English syntax and shares many elements with Visual Basic for Applications (VBA) syntax.

For example, a simple SQL statement that retrieves a list of last names for contacts named Mary might look like this:

SELECT Last_Name
FROM Contacts
WHERE First_Name = "Mary";

Note: The SQL language is used not only to perform operations on data, but also to create and change the structure of database objects, such as tables. The part of SQL that is used to create and modify database objects is called DDL. DDL is not covered in this article. additional information see Create or modify tables or indexes using a data definition query.

SELECT statements

The SELECT statement is used to describe a set of data in SQL. It contains a complete description of the set of data that needs to be retrieved from the database, including the following:

tables that contain data;

connections between data from different sources;

fields or calculations based on which data is selected;

selection conditions that must be met by the data included in the query result;

necessity and method of sorting.

SQL statements

An SQL statement is made up of several parts called clauses. Each clause in an SQL statement has a purpose. Some offers are required. The table below shows the most commonly used SQL statements.

SQL statement	Description	Mandatory
	Defines the fields that contain the required data.
	Defines tables that contain the fields specified in the SELECT clause.
	Defines the field selection conditions that all records included in the results must meet.
	Determines the sort order of the results.
	In an SQL statement that contains aggregation functions, specifies the fields for which a summary value is not calculated in the SELECT clause.	Only if such fields are present
	An SQL statement that contains aggregation functions defines the conditions that apply to the fields for which a summary value is calculated in the SELECT clause.

SQL terms

Each SQL sentence consists of terms that can be compared to parts of speech. The table below shows the types of SQL terms.

SQL term	Comparable part of speech	Definition	Example
identifier	noun	A name used to identify a database object, such as a field name.	Clients.[Phone Number]
operator	verb or adverb	A keyword that represents or modifies an action.
constant	noun	A value that does not change, such as a number or NULL.
expression	adjective	A combination of identifiers, operators, constants, and functions designed to calculate a single value.	>= Products.[Price]

Basic SQL Clauses: SELECT, FROM, and WHERE

General format SQL statements:

SELECT field_1
FROM table_1
WHERE criterion_1
;

Notes:

Access does not respect line breaks in SQL statements. Despite this, it is recommended to start each sentence with new line so that the SQL statement is easy to read both for the person who wrote it and for everyone else.

Every SELECT statement ends with a semicolon (;). The semicolon can appear either at the end of the last sentence or on a separate line at the end of the SQL statement.

Example in Access

The example below shows what an SQL statement might look like in Access for simple request per sample.

1. SELECT clause

2. FROM clause

3. WHERE clause

Let's look at the example sentence by sentence to understand how SQL syntax works.

SELECT clause

SELECT,Company

This is a SELECT clause. It contains a (SELECT) statement followed by two identifiers ("[Address Email]" and "Company").

If the identifier contains spaces or special signs(for example, "Email Address"), it must be enclosed in rectangular brackets.

The SELECT clause does not require you to specify the tables that contain the fields, and you cannot specify selection conditions that must be met by the data included in the results.

In a SELECT statement, the SELECT clause always comes before the FROM clause.

FROM clause

FROM Contacts

This is a FROM clause. It contains a (FROM) statement followed by an identifier (Contacts).

The FROM clause does not specify the fields to select.

WHERE clause

WHERE City="Seattle"

This is the WHERE clause. It contains a (WHERE) statement followed by the expression (City="Rostov").

There are many things you can do with SELECT, FROM, and WHERE clauses. For more information about using these offers, see the following articles:

Sorting results: ORDER BY

As in Microsoft Excel, in Access you can sort the results of a query in a table. By using the ORDER BY clause, you can also specify how the results are sorted when the query is executed. If an ORDER BY clause is used, it must appear at the end of the SQL statement.

The ORDER BY clause contains a list of fields to sort, in the same order in which the sort will be applied.

Suppose, for example, that the results first need to be sorted by the "Company" field in descending order, and then if there are records with the same value fields "Company", - sort them by the field "Email Address" in ascending order. The ORDER BY clause would look like this:

ORDER BY Company DESC,

Note: By default, Access sorts values ​​in ascending order (A to Z, smallest to largest). To sort the values ​​in descending order instead, you must specify the DESC keyword.

For more information about the ORDER BY clause, see the ORDER BY clause article.

Working with summary data: GROUP BY and HAVING clauses

Sometimes you need to work with summary data, such as total sales for the month or the most expensive items in stock. To do this, in the SELECT clause, apply to the field aggregate function. For example, if you were to run a query to get the number of email addresses for each company, the SELECT clause might look like this:

The ability to use a particular aggregate function depends on the type of data in the field and the desired expression. For more information about available aggregate functions, see SQL Statistical Functions.

Specifying fields that are not used in an aggregate function: GROUP BY clause

When using aggregate functions, you usually need to create a GROUP BY clause. The GROUP BY clause specifies all fields to which the aggregate function does not apply. If aggregate functions apply to all fields in the query, you do not need to create a GROUP BY clause.

GROUP offer The BY must immediately follow the WHERE or FROM clause if there is no WHERE clause. The GROUP BY clause lists the fields in the same order as the SELECT clause.

Let's continue the previous example. In the SELECT clause, if the aggregate function applies only to the [Email Address] field, then the GROUP BY clause would look like this:

GROUP BY Company

For more information about the GROUP BY clause, see the GROUP BY clause article.

Restricting aggregated values ​​using grouping conditions: the HAVING clause

If you need to specify conditions to limit the results, but the field to which you want to apply them is used in an aggregate function, you cannot use a WHERE clause. The HAVING clause should be used instead. The HAVING clause works the same as the WHERE clause, but is used for aggregated data.

For example, suppose that the AVG function (which calculates the average) is applied to the first field in the SELECT clause:

SELECT COUNT(), Company

If you want to limit query results based on the value of the COUNT function, you cannot apply a selection condition to this field in the WHERE clause. Instead, the condition should be placed in the HAVING clause. For example, if you want your query to return rows only if a company has multiple email addresses, you can use the following HAVING clause:

HAVING COUNT()>1

Note: A query can include both a WHERE clause and a HAVING clause, with selection conditions for fields that are not used in statistical functions specified in the WHERE clause, and conditions for fields that are used in statistical functions in the HAVING clause.

For more information about the HAVING clause, see the HAVING clause article.

Combining query results: UNION operator

The UNION operator is used to simultaneously view all the data returned by multiple similar select queries as a combined set.

The UNION operator allows you to combine two SELECT statements into one. The SELECT statements being merged must have the same number and order of output fields with the same or compatible data types. When a query is executed, the data from each set of matching fields is combined into a single output field, so the query output has as many fields as each individual SELECT statement.

Note: In join queries, numeric and text data types are compatible.

Using the UNION operator, you can specify whether duplicate rows, if any, should be included in the query results. To do this, use the ALL keyword.

A query to combine two SELECT statements has the following basic syntax:

SELECT field_1
FROM table_1
UNION
SELECT field_a
FROM table_a
;

For example, suppose you have two tables called "Products" and "Services". Both tables contain fields with the name of the product or service, price and warranty information, as well as a field that indicates the exclusivity of the product or service offered. Although the "Products" and "Services" tables provide different types guarantees, the basic information is the same (whether certain products or services are provided with a quality guarantee). You can use the following join query to join four fields from two tables:

SELECT name, price, warranty_available, exclusive_offer
FROM Products
UNION ALL
SELECT name, price, guarantee_available, exclusive_offer
FROM Services
;

For more information about combining SELECT statements using the UNION operator, see

Syntax:

* Where fields1— fields for selection separated by commas, you can also specify all fields with a *; table— the name of the table from which we extract data; conditions— sampling conditions; fields2— field or fields separated by commas by which to sort; count— number of lines to upload.
* request in square brackets is not required for data retrieval.

Simple examples of using select

1. Normal data sampling:

> SELECT * FROM users

2. Data sampling with joining two tables (JOIN):

SELECT u.name, r.* FROM users u JOIN users_rights r ON r.user_id=u.id

* V in this example data is being sampled with tables being merged users And users_rights. They are united by fields user_id(in the users_rights table) and id(users). The name field is retrieved from the first table and all fields from the second.

3. Sampling with time and/or date intervals

a) the starting point and a certain time interval are known:

* data for the last hour will be selected (field date).

b) the start date and end date are known:

25.10.2017 And 25.11.2017 .

c) start and end dates + time are known:

* select data between 03/25/2018 0 hours 15 minutes And 04/25/2018 15 hours 33 minutes and 9 seconds.

d) pull out data for a certain month and year:

* extract data where in the field date there are values ​​for April 2018 of the year.

4. Sampling of maximum, minimum and average values:

> SELECT max(area), min(area), avg(area) FROM country

* max— maximum value; min- minimal; avg- average.

5. Using String Length:

* this request should show all users whose name is 5 characters long.

Examples of queries that are more complex or rarely used

1. Concatenation with grouping of selected data into one row (GROUP_CONCAT):

* from table users field data is retrieved id, they are all placed on one line, the values ​​are separated commas.

2. Grouping data by two or more fields:

> SELECT * FROM users GROUP BY CONCAT(title, "::", birth)

* in summary, in this example we will upload data from the users table and group it by fields title And birth. Before grouping, we combine the fields into one line with a delimiter :: .

3. Merging results from two tables (UNION):

> (SELECT id, fio, address, "Users" as type FROM users)
UNION
(SELECT id, fio, address, "Customers" as type FROM customers)

* in this example, data is sampled from tables users And customers.

4. Sample of average values ​​grouped for each hour:

SELECT avg(temperature), DATE_FORMAT(datetimeupdate, "%Y-%m-%d %H") as hour_datetime FROM archive GROUP BY DATE_FORMAT(datetimeupdate, "%Y-%m-%d %H")

*here we extract the average value of the field temperature from the table archive and group by field datetimeupdate(with time division for every hour).

INSERT

Syntax 1:

> INSERT INTO

() VALUES ( )

Syntax 2:

> INSERT INTO

VALUES ( )

* Where table— the name of the table into which we enter the data; fields— listing fields separated by commas;values— listing values ​​separated by commas.
* the first option will allow you to insert only the listed fields - the rest will receive default values. The second option will require insertion for all fields.

Examples of using insert

1. Inserting multiple rows with one query:

> INSERT INTO cities ("name", "country") VALUES ("Moscow", "Russia"), ("Paris", "France"), ("Funafuti" , "Tuvalu");

* in this example we will add 3 records in one SQL query.

2. Inserting from another table (copying rows, INSERT + SELECT):

* extract all records from the table cities, whose names begin with “M” and enter them into the table cities-new.

Update (UPDATE)

Syntax:

* Where table— table name; field— the field for which we will change the value; value- new meaning; conditions— condition (without it, doing an update is dangerous - you can replace all the data in the entire table).

Update using replacement (REPLACE):

UPDATE

SET = REPLACE( , "<что меняем>", "<на что>");

UPDATE cities SET name = REPLACE(name, "Maskva", "Moscow");

If we want to play it safe, we can first check the result of the replacement using SELECT:

Delete (DELETE)

Syntax:

* Where table— table name; conditions— condition (as in the case of UPDATE, using DELETE without a condition is dangerous - the DBMS will not ask for confirmation, but will simply delete all data).

Creating a table

Syntax:

> CREATE TABLE

( , )

> CREATE TABLE IF NOT EXISTS `users_rights` (
`id` int(10) unsigned NOT NULL,
`user_id` int(10) unsigned NOT NULL,
`rights` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

* Where table— table name (in the example users_rights); field1, field2— name of the fields (in the example, 3 fields are created — id, user_id, rights); options1, options2— field parameters (in the example int(10) unsigned NOT NULL); table options — Common parameters tables (in the example ENGINE=InnoDB DEFAULT CHARSET=utf8).

Using requests in PHP

Connecting to the database:

mysql_connect("localhost", "login", "password") or die("MySQL connect error");
mysql_select_db("db_name");
mysql_query("SET NAMES "utf8"");

* where the connection is made to the base on local server (localhost); connection credentials - login And password(respectively, login and password); used as a base db_name; encoding used UTF-8.

You can also create a persistent connection:

mysql_pconnect("localhost", "login", "password") or die("MySQL connect error");

* however, there is a possibility of reaching the maximum allowed hosting limit. This method should be used for own servers, where we ourselves can control the situation.

Complete connection:

* in PHP is executed automatically, except permanent connections(mysql_pconnect).

A query to MySQL (Mariadb) in PHP is done with the mysql_query() function, and data retrieval from the query is done with mysql_fetch_array():

$result = mysql_query("SELECT * FROM users");
while ($mass = mysql_fetch_array($result)) (
echo $mass . "
";
}

* in this example a query was made to the table users. The query result is placed in a variable $result. Next the loop is used while, each iteration of which retrieves an array of data and places it in a variable $mass— in each iteration we work with one row of the database.

The mysql_fetch_array() function used returns associative array, which is convenient to work with, but there is another alternative - mysql_fetch_row(), which returns a regular numbered array.

Shielding

If you need to include a special character in the query string, for example, %, you must use escaping using the backslash character - \

For example:

* if you run such a query without escaping, the % sign will be interpreted as any number of characters after 100.

That's all. If you need help completing a request, please email me

So, in our forum database there are three tables: users (users), topics (topics) and posts (messages). And we want to see what data they contain. There is an operator for this in SQL SELECT. The syntax for using it is as follows:

SELECT what_select FROM where_select;

Instead of “what_select” we must specify either the name of the column whose values ​​we want to see, or the names of several columns separated by commas, or the asterisk (*) symbol, meaning the selection of all columns of the table. Instead of "from_select" you should specify the table name.

Let's first look at all the columns from the users table:

SELECT * FROM users;

That's all our data that we entered into this table. But let’s assume that we want to look only at the id_user column (for example, in the last lesson, in order to populate the topics table, we needed to know which id_users are in the users table). To do this, we will specify the name of this column in the request:

SELECT id_user FROM users;

Well, if we want to see, for example, the names and e-mails of our users, then we will list the columns of interest separated by commas:

SELECT name, email FROM users;

Similarly, you can see what data our other tables contain. Let's see what topics we have:

SELECT * FROM topics;

Now we have only 4 topics, but what if there are 100 of them? I would like them to be displayed, for example, in alphabetical order. There is a keyword for this in SQL ORDER BY followed by the name of the column by which the sorting will take place. The syntax is as follows:

SELECT column_name FROM table_name ORDER BY sort_column_name;

By default the sorting is in ascending order, but this can be changed by adding a keyword DESC

Now our data is sorted in descending order.

Sorting can be done by several columns at once. For example, the following query will sort the data by the topic_name column, and if there are multiple identical rows in this column, then the id_author column will be sorted in descending order:

Compare the result with the result of the previous query.

Very often we do not need all the information from the table. For example, we want to find out which topics were created by the user sveta (id=4). There is a keyword for this in SQL WHERE, the syntax for such a request is as follows:

For our example, the condition is the user ID, i.e. We only need those rows whose id_author column contains 4 (user ID sveta):

Or we want to know who created the "bicycles" theme:

Of course, it would be more convenient if instead of the author’s id, his name was displayed, but the names are stored in another table. In subsequent lessons, we will learn how to select data from multiple tables. In the meantime, let’s find out what conditions can be set using the WHERE keyword.

Operator	Description
= (equal)	Values ​​equal to the specified value are selected Example: SELECT * FROM topics WHERE id_author=4; Result:
> (more)	Values ​​greater than specified are selected Example: SELECT * FROM topics WHERE id_author>2; Result:
< (меньше)	Values ​​less than specified are selected Example: SELECT * FROM topics WHERE id_author Result:
>= (greater than or equal to)	Values ​​greater than and equal to the specified value are selected Example: SELECT * FROM topics WHERE id_author>=2; Result:
<= (меньше или равно)	Values ​​less than and equal to the specified value are selected Example: SELECT * FROM topics WHERE id_author Result:
!= (not equal)	Values ​​not equal to the specified value are selected Example: SELECT * FROM topics WHERE id_author!=1; Result:
IS NOT NULL	Selects rows that have values ​​in the specified field Example: SELECT * FROM topics WHERE id_author IS NOT NULL; Result:
IS NULL	Selects rows that have no value in the specified field Example: SELECT * FROM topics WHERE id_author IS NULL; Result: Empty set - there are no such lines.
BETWEEN (between)	Values ​​between the specified values ​​are selected Example: SELECT * FROM topics WHERE id_author BETWEEN 1 AND 3; Result:
IN (value contained)	Values ​​corresponding to the specified values ​​are selected Example: SELECT * FROM topics WHERE id_author IN (1, 4); Result:
NOT IN (value not contained)	Values ​​other than those specified are selected Example: SELECT * FROM topics WHERE id_author NOT IN (1, 4); Result:
LIKE (match)	Values ​​that match the pattern are selected Example: SELECT * FROM topics WHERE topic_name LIKE "led%"; Result: Possible metacharacters for the LIKE operator will be discussed below.
NOT LIKE (not matching)	Values ​​that do not match the pattern are selected Example: SELECT * FROM topics WHERE topic_name NOT LIKE "led%"; Result:

LIKE operator metacharacters

Searches using metacharacters can only be performed in text fields.

The most common metacharacter is % . It means any symbols. For example, if we want to find words that start with the letters "vel", then we will write LIKE "vel%", and if we want to find words that contain the characters "club", then we will write LIKE "%club%". For example:

Another commonly used metacharacter is _ . Unlike %, which denotes few or no characters, the underscore denotes exactly one character. For example:

Pay attention to the space between the metacharacter and the “fish”; if you skip it, the request will not work, because metacharacter _ stands for exactly one character, and a space is also a character.

It's enough for today. In the next lesson we will learn how to write queries against two or more tables. In the meantime, try to create queries against the posts table yourself.