Are you here: Reporting

Send your good work in the knowledge base is simple. Use the form below

Students, graduate students, young scientists who use the knowledge base in their studies and work will be very grateful to you.

Posted on http://www.allbest.ru/

Practical work

Functions in MySQL

Task 1. Built-in functions

math function programming

Functions are operations that allow you to manipulate data. There are several groups of built-in functions in MySQL:

String functions... Used to manipulate text strings, such as trimming or filling values.

Numeric functions... Used to perform mathematical operations on numeric data. Numeric functions include functions that return absolute values, sines and cosines of angles, square root of a number, etc. They are used only for algebraic, trigonometric and geometric calculations. In general, they are rarely used, so we will not consider them. However, you should be aware that they exist and consult the MySQL documentation if necessary.

Summary functions... They are used to obtain summary data for tables, for example, when it is necessary to summarize some data without sampling them.

Date and time functions... Used to manipulate date and time values, for example, to return the difference between dates.

System functions... Returns the service information of the DBMS.

In order to look at the main built-in functions, we need to create a new database so that it contains numeric and date values.

Let's take an online store as an example.

Conceptual model:

Relational model:

So, let's look at the last diagram and create a database - shop.

create database shop;

We choose it for work:

And we create 8 tables in it, as in the scheme: Customers, Suppliers, Purchases (sale), Deliveries (incoming), Purchase journal (magazine_sales), Supply journal (magazine_incoming), Products (products), Prices ( prices). One nuance, our store will sell books, so we will add one more column to the Products table - Author, in principle this is not necessary, but it is somehow more familiar.

Note that in the Purchase Journal, Delivery Journal, and Prices tables, the primary keys are composite, i.e. their unique values consist of pairs of values (there cannot be two rows with the same pairs of values in a table). The column names of these value pairs are indicated by commas after the PRIMARY KEY keyword.

In a real online store, data in these tables will be entered through scripts in any language (such as php), but for now we have to enter them manually. You can enter any data, just remember that the values in the columns of the same name in related tables must match. Or copy the data below:

So, in our store there are 24 items of goods brought in three deliveries from three suppliers, and absolutely three sales. Everything is ready, we can start learning the built-in functions of MySQL.

Assignment 2... Summary Functions, Calculated Columns, and Views

Summary functions are also called statistical, aggregate or summarizing functions. These functions process a set of rows to count and return a single value. There are only five such functions:

AVG () Function returns the average value of a column.

COUNT () The function returns the number of rows in a column.

MAX () Function returns the largest value in the column.

MIN () Function returns the smallest value in a column.

SUM () Function returns the sum of the column values.

We have already met one of them - COUNT () - http://www.site-do.ru/db/sql8.php. Now let's get to know the rest. Suppose we wanted to know the minimum, maximum and average prices for books in our store. Then from the table Prices (prices) it is necessary to take the minimum, maximum and average values for the price column. The request is simple:

SELECT MIN (price), MAX (price), AVG (price) FROM prices;

Now, we want to find out how much the supplier "Printing House" brought us the goods (id = 2). It is not so easy to compose such a request. Let's think about how to compose it:

First, from the Deliveries (incoming) table, select the identifiers (id_incoming) of those deliveries that were made by the "Printing House" supplier (id = 2):

Now from the table Magazine of deliveries (magazine_incoming) you need to select the goods (id_product) and their quantities (quantity), which were carried out in the deliveries found in paragraph 1. That is, the request from paragraph 1 becomes nested:

Now we need to add the prices for the found products to the resulting table, which are stored in the prices table. That is, we need to join the tables Magazine_incoming and Prices by the id_product column:

The resulting table is clearly missing the Sum column, that is, the calculated column. The ability to create such columns is provided in MySQL. To do this, you just need to specify in the query the name of the calculated column and what it should calculate. In our example, such a column will be called summa, and it will calculate the product of the quantity and price columns. The new column name is separated by the word AS:

Great, all we have to do is to sum up the summa column and finally find out how much the supplier "Printing House" brought us the goods. The syntax for using the SUM () function is as follows:

SELECT SUM (column_name) FROM table_name;

We know the name of the column - summa, but we do not have the name of the table, since it is the result of the query. What to do? For such cases, MySQL provides Views. A view is a select query that is assigned a unique name and can be stored in a database for later use.

The syntax for creating a view is as follows:

CREATE VIEW view_name AS query;

Let's save our request as a view named report_vendor:

CREATE VIEW report_vendor AS

SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price,

magazine_incoming.quantity * prices.price AS summa

FROM magazine_incoming, prices

WHERE magazine_incoming.id_product = prices.id_product AND id_incoming =

Now you can use the final SUM () function:

So we have achieved the result, although for this we had to use nested queries, joins, calculated columns and views. Yes, sometimes you have to think to get the result, without it anywhere. But we touched on two very important topics - calculated columns and views. Let's talk about them in more detail.

Calculated fields (columns)

For example, we looked at a mathematical calculated field. Here I would like to add that you can use not only the operation of multiplication (*), but also subtraction (-), and addition (+), and division (/). The syntax is as follows:

SELECT column_1_name, column_2_name, column_1_name * column_2_name AS computed_column_name

FROM table_name;

The second nuance is the AS keyword, we used it to set the name of the calculated column. In fact, this keyword is used to set aliases for any columns. Why is this needed? For shorter and more readable code. For example, our view might look like this:

CREATE VIEW report_vendor AS

SELECT A.id_product, A.quantity, B.price, A.quantity * B.price AS summa

FROM magazine_incoming AS A, prices AS B

WHERE A.id_product = B.id_product AND id_incoming =

(SELECT id_incoming FROM incoming WHERE id_vendor = 2);

Agree that this is much shorter and clearer.

Representation

We have already covered the syntax for creating views. After creating views, they can be used in the same way as tables. That is, execute queries on them, filter and sort data, combine some views with others. On the one hand, this is a very convenient way to store frequently used complex queries (as in our example).

Keep in mind, however, that views are not tables, that is, they do not store data, they only retrieve it from other tables. Hence, firstly, when the data in the tables changes, the results of the presentation will also change. And secondly, when a query is made to a view, the required data is searched, that is, the performance of the DBMS decreases. Therefore, they should not be abused.

Sql String Functions

This group of functions allows you to manipulate text. There are many string functions, we will consider the most common ones.

CONCAT (str1, str2 ...)

Returns a string created by concatenating the arguments (arguments are specified in parentheses - str1, str2 ...). For example, our vendors table has a city column and an address column. Suppose we want the Address and City to appear in the same column in the resulting table, i.e. we want to combine data from two columns into one. To do this, we will use the CONCAT () string function, and as arguments, we will specify the names of the combined columns - city and address:

Note that the merging happened without separation, which is not very readable. Let's tweak our query so that there is a space between the merged columns:

As you can see, the space is also considered an argument and is separated by commas. If there were more columns to join, then it would be irrational to indicate spaces each time. In this case, one could use the CONCAT_WS string function (delimiter, str1, str2 ...), which places a delimiter between the strings being concatenated (delimiter is specified as the first argument). Our request would then look like this:

SELECT CONCAT_WS ("", city, address) FROM vendors;

The result did not change externally, but if we were to concatenate 3 or 4 columns, then the code would be significantly reduced.

INSERT (str, pos, len, new_str)

Returns the string str in which the substring starting at position pos and length len characters has been replaced by the substring new_str. Suppose we decide not to display the first 3 characters in the address column (abbreviations st., Etc., etc.), then we will replace them with spaces:

That is, three characters, starting with the first, are replaced by three spaces.

LPAD (str, len, dop_str) Returns string str, left-padded by dop_str to length len. Suppose we want the supplier cities to be on the right when displaying supplier cities and fill the empty space with dots:

RPAD (str, len, dop_str)

Returns the string str, right-padded with dop_str to length len. Suppose we want the supplier cities to be on the left and fill the empty space with dots when displaying supplier cities:

Note that len limits the number of characters to be displayed, i.e. if the city name is longer than 15 characters, it will be truncated.

Returns the string str with all leading spaces removed. This string function is convenient for displaying information correctly in cases where random spaces are allowed when entering data:

SELECT LTRIM (city) FROM vendors;

Returns the string str with all trailing spaces removed:

SELECT RTRIM (city) FROM vendors;

In our case, there were no extra spaces, so we won't see the result outwardly.

Returns the string str with all leading and trailing spaces removed:

SELECT TRIM (city) FROM vendors;

Returns the string str with all characters converted to lowercase. It does not work correctly with Russian letters, so it is better not to use it. For example, let's apply this function to the city column:

You see what kind of gibberish it turned out. But with the Latin alphabet, everything is in order:

Returns the string str with all characters converted to uppercase. It is also better not to use it with Russian letters. But with the Latin alphabet, everything is in order:

Returns the length of the string str. For example, let's find out how many characters are in our supplier addresses:

Returns len left-handed characters of str. For example, suppose only the first three characters are displayed in supplier cities:

Returns len right-handed characters of str. For example, suppose only the last three characters are displayed in supplier cities:

Returns string str n times. For instance:

REPLACE (str, pod_str1, pod_str2)

Returns the string str in which all substrings of pod_str1 have been replaced by substrings of pod_str2. For example, suppose we want the short "SPb" to be displayed instead of the long "St. Petersburg" in the cities of suppliers:

Returns the string str written in reverse order:

LOAD_FILE (file_name)

This function reads the file file_name and returns its contents as a string. For example, create a file proverka.txt, write some text in it (better in Latin, so that there are no problems with encodings), save it on the C drive and make the following request:

Please note that you must specify the absolute path to the file.

As already mentioned, there are many more string functions, but even some of the ones considered here are rarely used. So that's where we will end our review and move on to more commonly used date and time functions.

Assignment 3... Date and time functions

These functions are designed to work with calendar data types. Let's consider the most applicable ones.

CURDATE (), CURTIME () and NOW ()

The first function returns the current date, the second returns the current time, and the third returns the current date and time. Compare:

The CURDATE () and NOW () functions are useful for adding records to the database using the current time. In our store, all shipments and sales use the current time. Therefore, it is convenient to use the CURDATE () function to add records about deliveries and sales. For example, suppose a product has arrived in our store, let's add information about this to the table Delivery (incoming):

If we were to store the delivery date with the datatime type, then the NOW () function would be more suitable for us.

ADDDATE (date, INTERVAL value) The function returns the date date to which value is added. Value can be negative, then the final date will decrease. Let's see when our suppliers made deliveries of the goods:

Suppose we made a mistake when entering the date for the first supplier, let's decrease its date by one day:

The value can be not only days, but also weeks (WEEK), months (MONTH), quarters (QUARTER), and years (YEAR). For example, let's reduce the delivery date for the second supplier by 1 week:

In our Deliveries (incoming) table, we used the date type for the Delivery Date (date_incoming) column. This data type is designed to store only dates. But if we used the datatime type, then we would display not only the date, but also the time. Then we could use the ADDDATE function for time as well. In this case, the value of value can be seconds (SECOND), minutes (MINUTE), hours (HOUR) and their combinations:

minutes and seconds (MINUTE_SECOND),

hours, minutes and seconds (HOUR_SECOND),

hours and minutes (HOUR_MINUTE),

days, hours, minutes and seconds (DAY_SECOND),

days, hours and minutes (DAY_MINUTE),

days and hours (DAY_HOUR),

years and months (YEAR_MONTH).

SUBDATE (date, INTERVAL value)

the function is identical to the previous one, but it performs the operation of subtraction, not addition.

PERIOD_ADD (period, n)

the function adds n months to the date value period. A nuance: the date value must be in the YYYYMM format. Let's add 2 months to February 2011 (201102):

TIMESTAMPADD (interval, n, date)

the function adds a time interval n to the date date, the values of which are set by the interval parameter. Possible values for the interval parameter:

FRAC_SECOND - microseconds

SECOND - seconds

MINUTE - minutes

WEEK - weeks

MONTH - months

QUARTER - quarters

TIMEDIFF (date1, date2)

calculates the difference in hours, minutes, and seconds between two dates.

DATEDIFF (date1, date2)

calculates the difference in days between two dates. For example, we want to know how long ago the supplier "Williams" (id = 1) supplied us with the goods:

PERIOD_DIFF (period1, period2)

the function calculates the difference in months between two dates, represented in the YYYYMM format. Let's find out the difference between January 2010 and August 2011:

TIMESTAMPDIFF (interval, date1, date2)

the function calculates the difference between date2 and date1 in the units specified in the interval parameter. Possible values for the interval parameter:

FRAC_SECOND - microseconds

SECOND - seconds

MINUTE - minutes

WEEK - weeks

MONTH - months

QUARTER - quarters

SUBTIME (date, time)

the function subtracts the time from the date time:

returns the date, cutting off the time. For instance:

returns the time by cutting off the date. For instance:

the function takes date and returns the full version with the time. For instance:

DAY (date) and DAYOFMONTH (date)

functions-synonyms, return from the date the ordinal number of the day of the month:

DAYNAME (date), DAYOFWEEK (date) and WEEKDAY (date)

functions return the day of the week, the first - its name, the second - the number of the day of the week (counting from 1 - Sunday to 7 - Saturday), the third - the number of the day of the week (counting from 0 - Monday, to 6 - Sunday:

WEEK (date), WEEKOFYEAR (datetime)

both functions return the week number of the year, the first for the date type, and the second for the datetime type, the first week starts from Sunday, the second - from Monday:

MONTH (date) and MONTHNAME (date)

both functions return month values. The first is its numerical value (from 1 to 12), the second is the name of the month:

the function returns the value of the quarter of the year (from 1 to 4):

The YEAR (date) function returns the value of the year (1000 to 9999):

returns the ordinal number of the day in the year (from 1 to 366):

returns the hour for the time (0 to 23):

MINUTE (datetime)

returns the minutes for the time (0 to 59):

SECOND (datetime)

returns the seconds value for the time (0 to 59):

EXTRACT (type FROM date)

returns the date part specified by the type parameter:

TO_DAYS (date) and FROM_DAYS (n)

reciprocal functions. The first converts the date to the number of days since year zero. The second, conversely, takes the number of days since year zero and converts them to a date:

UNIX_TIMESTAMP (date) and FROM_UNIXTIME (n)

reciprocal functions. The first converts the date to the number of seconds since Jan 1, 1970. The second, conversely, takes the number of seconds since January 1, 1970 and converts them to a date:

TIME_TO_SEC (time) and SEC_TO_TIME (n)

reciprocal functions. The first converts the time into the number of seconds elapsed from the beginning of the day. The second, conversely, takes the number of seconds since the beginning of the day and converts them to the time:

MAKEDATE (year, n)

the function takes the year and the number of the day in the year and converts them to a date:

Task 4. Fdate and time formatting features

These functions are also designed to work with calendar data types. Let's consider them in more detail.

DATE_FORMAT (date, format)

formats the date according to the selected formate. This function is used very often. For example, in MySQL, the date is in the YYYY-MM-DD (year-month-day) format, while the DD-MM-YYYY (day-month-year) format is more familiar to us. Therefore, to display the date we are accustomed to, it must be reformatted. Let's give the request first, and then figure out how to set the format:

Now the date looks familiar to us. To set the date format, special qualifiers are used. For convenience, we list them in the table.

	Description
	The abbreviated name of the day of the week (Mon - Monday, Tue - Tuesday, Wed - Wednesday, Thu - Thursday, Fri - Friday, Sat - Saturday, Sun - Sunday).
	Abbreviated month names (Jan - January, Feb - February, Mar - March, Apr - April, May - May, Jun - June, Jul - July, Aug - August, Sep - September, Oct - October, Nov - November, Dec - December).
	Month in numeric form (1 - 12).
	The day of the month in numeric form with a zero (01 - 31).
	Day of the month in English (1st, 2nd ...).
	Day of the month in numeric form without zero (1 - 31).
	Hours with leading zeros from 00 to 23.
	Hours with leading zeros from 00 to 12.
	Minutes from 00 to 59.
	Day of the year from 001 to 366.
	Hours with leading zeros from 0 to 23.
	Hours without leading zero from 1 to 12.

	Month name without abbreviation.
	Month in numeric form with a leading zero (01 - 12).
	AM or PM for 12 hour format.
	Time in 12-hour format.
	Seconds from 00 to 59.
	Time in 24-hour format.
	Week (00 - 52), where Monday is considered the first day of the week.
	Week (00 - 52) with Sunday as the first day of the week.
	The name of the day of the week without abbreviation.
	Day of the week number (0 - Sunday, 6 - Saturday).
	Year, 4 categories.
	Year, 2 categories.

STR_TO_DATE (date, format)

the function is the opposite of the previous one, it accepts date in format, and returns a date in MySQL format.

TIME_FORMAT (time, format)

the function is similar to the DATE_FORMAT () function, but it is used only for time:

GET_FORMAT (date, format)

the function returns a format string corresponding to one of five time formats:

EUR - European standard

USA - American standard

JIS - Japanese Industrial Standard

ISO - ISO standard (international organization of standards)

INTERNAL - international standard

This function is good to use in conjunction with the previous one -

Let's see an example:

As you can see, the GET_FORMAT () function itself returns the presentation format, and together with the DATE_FORMAT () function, it returns the date in the desired format. Make your own queries with all five standards and see the difference.

Well, now you know almost everything about working with dates and times in MySQL. This will be very useful for you when developing various web applications. For example, if a user enters a date in a form on a website in the format he is accustomed to, it will not be difficult for you to apply the required function so that the date appears in the database in the required format.

Task 5. Stored Procedures

As a rule, when working with a database, we use the same queries, or a set of sequential queries. Stored procedures allow you to combine a sequence of requests and store them on the server. This is a very handy tool, and now you will see for yourself. Let's start with the syntax:

CREATE PROCEDURE

sp_name (parameters)

operators

Parameters are the data that we will pass to the procedure when it is called, and operators are the actual requests. Let's write our first procedure and see how convenient it is. When we added new records to the shop database, we used a standard add query of the form:

INSERT INTO customers (name, email) VALUE ("Ivanov Sergey", " [email protected]");

Because We will use such a request every time we need to add a new customer, then it is quite appropriate to issue it in the form of a procedure:

CREATE PROCEDURE ins_cust (n CHAR (50), e CHAR (50))

insert into customers (name, email) value (n, e);

Pay attention to how the parameters are set: you need to give a name to the parameter and indicate its type, and in the body of the procedure we already use the names of the parameters. One caveat. As you remember, the semicolon marks the end of the request and sends it for execution, which is not acceptable in this case. Therefore, before writing the procedure, you must override the separator with; to "//" so that the request is not sent ahead of time. This is done using the DELIMITER // statement:

Thus, we have indicated to the DBMS that the commands should now be executed after //. It should be remembered that the redefinition of the separator is carried out only for one session of work, i.e. the next time you work with MySql, the separator will become a semicolon again and will have to be redefined again if necessary. Now we can place the procedure:

So, the procedure has been created. Now, when we need to enter a new customer, we just need to call it by specifying the necessary parameters. A CALL statement is used to call a stored procedure, followed by the name of the procedure and its parameters. Let's add a new customer to our customers table:

Agree that this is much easier than writing a full request every time. Let's check if the procedure works by looking to see if a new customer has appeared in the customers table:

Appeared, the procedure works, and will always work until we delete it using the DROP PROCEDURE statement procedure_name.

As mentioned at the beginning of the assignment, procedures allow you to combine a sequence of requests. Let's see how this is done. Let's try to find out how much the supplier "Printing House" brought us the goods? Previously, we would have had to use nested queries, joins, calculated columns, and views to do this. And if we want to know how much the other supplier brought us the goods? You will have to compose new queries, associations, etc. It is easier to write a stored procedure for this action once.

It would seem that the easiest way is to take the already written view and a request to it, combine it into a stored procedure and make the vendor identifier (id_vendor) an input parameter, like this:

But the procedure won't work that way. The thing is, no parameters can be used in views. Therefore, we will have to slightly change the sequence of requests. First, we will create a view that will output the vendor id (id_vendor), product id (id_product), quantity, price, and summa from three tables Deliveries (incoming), Magazine_incoming, Prices ( prices):

And then we will create a request that will sum up the supply amounts of the supplier we are interested in, for example, with id_vendor = 2:

SELECT SUM (summa) FROM report_vendor WHERE id_vendor = 2;

Now we can combine these two requests into a stored procedure, where the input parameter will be the vendor identifier (id_vendor), which will be substituted into the second request, but not into the view:

Let's check the operation of the procedure, with different input parameters:

As you can see, the procedure fires once and then throws an error, telling us that the report_vendor view is already in the database. This is because the first time a procedure is called, it creates a view. When accessing a second time, it tries to create a view again, but it already exists, which is why an error appears. To avoid this, there are two possible options.

The first is to take the view out of the procedure. That is, we will create a view once, and the procedure will only refer to it, but not create it. He will not forget to delete the already created procedure and view in advance:

Checking the work:

call sum_vendor (1) //

call sum_vendor (2) //

call sum_vendor (3) //

The second option is to add a command right in the procedure that will delete the view, if it exists:

Remember to remove the sum_vendor procedure before using this option, and then test it:

As you can see, complex queries or their sequence is really easier to form once into a stored procedure, and then just refer to it, specifying the necessary parameters. This significantly reduces the code and makes working with queries more logical.

Task 6. Stored Procedures

Now let's find out how you can see what stored procedures we have on the server, and how they look. To do this, let's get acquainted with two operators:

SHOW PROCEDURE STATUS - allows you to view a list of available stored procedures. It is true that viewing this list is not very convenient, since for each procedure, information is displayed about the name of the database to which the procedure belongs, its type, account on whose behalf the procedure was created, the date of creation and modification of the procedure, etc. And yet, if you need to see what procedures you have, then you should use this operator.

SHOW CREATE PROCEDURE procedure_name - allows you to get information about a specific procedure, in particular, view its code. The view for viewing is also not very convenient, but you can figure it out.

Try both operators in action to see what it looks like. Now let's consider a more convenient option for obtaining such information. The MySQL system database has a proc table where information about procedures is stored. So here we can make a SELECT query to this table. Moreover, if we create a familiar request:

SELECT * FROM mysql.proc //

Then we get something as unreadable as when using SHOW operators. Therefore, we will create conditional queries. For example, if we create a request like this:

SELECT name FROM mysql.proc //

Then we will get the names of all procedures of all databases available on the server. For example, we are currently only interested in the shop database procedures, so let's change the query:

SELECT name FROM mysql.proc WHERE db = "shop" //

Now we got what we wanted:

If we want to see only the body of a specific procedure (i.e. from begin to end), then we will write a request like this:

SELECT body FROM mysql.proc WHERE name = "sum_vendor" //

And we will see a completely readable version:

In general, in order to extract the information you need from the proc table, you just need to know what columns it contains, and for this you can use the familiar operator describe table_name, in our case, describe mysql.proc. True, her view is also not very readable, so here are the names of the most popular columns:

db is the name of the database in which the procedure is saved.

name is the name of the procedure.

param_list - list of procedure parameters.

body - the body of the procedure.

comment - a comment to the stored procedure.

We have already used the db, name and body columns. Write a query that retrieves the parameters of the sum_vendor procedure yourself. But now we will talk in more detail about comments to stored procedures.

Comments are an extremely necessary thing, because after some time we may forget what a particular procedure is doing. Of course, our memory can be restored by its code, but why? It is much easier to indicate immediately when creating a procedure what it does, and then, even after a long time, referring to the comments, we will immediately remember why this procedure was created.

It is extremely easy to create comments. To do this, immediately after the list of parameters, but even before the beginning of the body of the stored procedure, specify the COMMENT keyword "comment here". Let's remove our sum_vendor routine and create a new one with a comment:

Now let's make a request for the procedure comment:

In fact, you didn't have to delete the old procedure to add a comment. You could edit an existing stored procedure using the ALTER PROCEDURE statement. Let's see how to do this using the ins_cust procedure from the previous job as an example. This procedure enters information about a new customer in the customers table. Let's add a comment to this procedure:

ALTER PROCEDURE ins_cust COMMENT

Enters information about a new customer in the Customers table. "//

And let's request a comment to check:

SELECT comment FROM mysql.proc WHERE name = "ins_cust" //

There are only two procedures in our database, and comments on them seem superfluous. Don't be lazy, be sure to write comments. Imagine that there are dozens or hundreds of procedures in our database. Having made the necessary request, you can easily find out what procedures are and what they do, and you will understand that comments are not excesses, but saving your time in the future. By the way, here's the query itself:

Well, now we are able to extract any information about our procedures, which will allow us not to forget anything and not get confused.

Task 7. Stored Procedures

Stored procedures are not just a container for groups of requests, as it might seem. Stored procedures can use branching operators in their work. Such operators cannot be used outside of stored procedures.

Let's start with the IF ... THEN ... ELSE statements. If you are familiar with any programming language, then this construction is familiar to you. Recall that the IF conditional statement allows you to organize program branching. In the case of stored procedures, this operator allows you to execute different queries, depending on the input parameters. An example, as always, will be clearer. But first, the syntax is:

The logic of work is simple: if the condition is true, then query 1 is executed, otherwise - query 2.

Suppose we have happy hours in our store every day, i.e. we make a 10% discount on all books in the last hour of the store's opening. To be able to choose the price of a book, we need to have two options - with and without a discount. To do this, we need to create a stored procedure with a branch operator. Since we have only two price options, it is more convenient to have a boolean value as an input parameter, which, as you remember, can take either 0 - false, or 1 - true. The procedure code can be like this:

Those. at the input we have a parameter that can be either 1 (if there is a discount) or 0 (if there is no discount). In the first case, the first request will be executed, in the second - the second. Let's see how our procedure works in both cases:

call discount (1) //

call discount (0) //

The IF statement allows you to choose more options for queries, in which case the following syntax is used:

CREATE PROCEDURE sp_name (parameters)

IF (condition) THEN

ELSEIF (condition) THEN

Moreover, there can be several ELSEIF blocks. Suppose that we decided to make discounts to our customers depending on the purchase amount, there is no discount up to 1000 rubles, from 1000 to 2000 rubles - a 10% discount, more than 2000 rubles - a 20% discount. The input parameter for such a procedure should be the purchase amount. Therefore, first we need to write a procedure that will calculate it. We will do this by analogy with the sum_vendor procedure created in Lesson 15, which calculated the amount of the product by the vendor ID.

The data we need is stored in two tables - Purchase magazine (magazine_sales) and Prices (prices).

CREATE PROCEDURE sum_sale (IN i INT)

COMMENT "Returns the purchase amount by its ID."

DROP VIEW IF EXISTS sum_sale;

CREATE VIEW sum_sale AS SELECT magazine_sales.id_sale,

magazine_sales.id_product, magazine_sales.quantity,

prices.price, magazine_sales.quantity * prices.price AS summa

FROM magazine_sales, prices

WHERE magazine_sales.id_product = prices.id_product;

SELECT SUM (summa) FROM sum_sale WHERE id_sale = i;

Here, before the parameter, we have a new IN keyword. The fact is that we can both transfer data to a procedure, and transfer data from a procedure. By default, i.e. if you omit the word IN, the parameters are considered input (that's why we didn't use this word before). Here we have explicitly indicated that the i parameter is an input parameter. If we need to extract some data from a stored procedure, then we will use the OUT keyword, but more on that later.

So, we wrote a procedure that creates a view by selecting a purchase ID, product ID, quantity, price and calculates the amount for all the rows of the resulting table. Then there is a request to this view, where the total amount of this purchase is calculated using the input parameter of the purchase ID.

Now we need to write a procedure that will recalculate the total amount taking into account the discount provided. This is where we need the branching operator:

Those. we pass the procedure two input parameters, the amount (sm) and the purchase identifier (i), and depending on what amount it is, a request is made to the sum_sale view to calculate the total purchase amount multiplied by the desired coefficient.

It remains only to make sure that the purchase amount is automatically transferred to this procedure. For this, it would be nice to call the sum_discount procedure directly from the sum_sale procedure. It will look something like this:

The question mark is posed when calling the sum_discount procedure, because it is not clear how to transfer the result of the previous request (i.e. the total sum) to the sum_discount procedure. In addition, it is not clear how the sum_discount procedure will return the result of its work. You probably already guessed that to solve the second question, we just need a parameter with the OUT keyword, i.e. parameter that will return data from the procedure. Let's introduce such a parameter ss, and since the sum can be a fractional number, let's set it to the DOUBLE type:

So, in both procedures, we introduced the ss output parameter. Now the call to the procedure CALL sum_discount (?, i, ss); means that by passing the first two parameters, we are waiting for the third parameter to return to the sum_sale procedure. It remains only to understand how to assign any value to this parameter inside the sum_discount procedure itself. We need the result of one of the requests to be passed to this parameter. And, of course, MySQL provides such an option, the INTO keyword is used for this:

Using the INTO keyword, we indicated that the query result should be passed to the ss parameter.

Now let's deal with the question mark, or rather, learn how to pass the result of the previous queries to the sum_discount procedure. To do this, we will get acquainted with such a concept as a variable.

Variables allow you to save the result of the current query for use in future queries. A variable declaration begins with the @ symbol followed by the variable name. They are declared using the SET statement. For example, let's declare a variable z and give it an initial value of 20.

A variable with such a value is now in our database, you can check it by making the appropriate request:

Variables are valid only within one session of connection with the MySQL server. That is, after disconnection, the variable will cease to exist.

To use variables in procedures, the DECLARE statement is used, which has the following syntax:

DECLARE variable_name type DEFAULT default_value_if_is

So, in our procedure, let's declare a variable s, into which we will store the value of the purchase amount using the INTO keyword:

This variable will be the first input parameter for the sum_discount procedure. So, the final version of our procedures looks like this:

In case you get confused, let's see how our sum_sale procedure works:

We call the sum_sale procedure, specifying the identifier of the purchase we are interested in as an input parameter, for example, id = 1, and indicating that the second parameter is an output variable that is the result of the sum_discount procedure:

call sum_sale (1, @sum_discount) //

The sum_sale procedure creates a view that collects data about all purchases, products, their quantity, price and amount for each line.

This view is then queried for the purchase total with the desired ID, and the result is written to the s variable.

Now the sum_discount procedure is called, in which the variable s (purchase amount) acts as the first parameter, the purchase identifier i as the second, and the ss parameter is specified as the third parameter, which acts as an output, i.e. the result of the action of the sum_discount procedure will be returned to it.

The sum_discount procedure checks which condition the input sum meets, and executes the corresponding query, the result is written to the ss output parameter, which is returned to the sum_sale procedure.

To see the result of the sum_sale procedure, you need to make a request:

select @ sum_discount //

Let's make sure our procedure works:

The sum of our both purchases is less than 1000 rubles, so there is no discount. You can independently enter purchases with different amounts and see how our procedure will work.

Perhaps this lesson seemed difficult or confusing enough to you. Dont be upset. Firstly, everything comes with experience, and secondly, in fairness, I must say that both variables and branching operators in MySQL are used extremely rarely. Preference is given to languages such as PHP, Perl, etc., with the help of which branching is organized, and simple procedures are sent to the database itself.

Task 8. Stored Procedures

Today we will learn how to work with loops, i.e. execute the same query multiple times. MySQL uses WHILE, REPEAT, and LOOP statements to work with loops.

WHILE Loop Operator

First the syntax is:

WHILE DO condition

The request will be executed as long as the condition is true. Let's see an example of how this works. Suppose we want to know the titles, authors, and the number of books that have arrived in various shipments. The information we are interested in is stored in two tables - Magazine of Deliveries (magazine_incoming) and Product (products). Let's write the query we're interested in:

But what if we need the result to be displayed not in one table, but for each delivery separately? Of course, you can write 3 different queries, adding one more condition to each one:

But it can be done much shorter with a WHILE loop:

Those. we introduced the variable i, by default equal to 3, the server will execute the request with the delivery id equal to 3, then decrement i by one (SET i = i-1), make sure that the new value of the variable i is positive (i> 0) and execute the request again , but already with the new value of the delivery id equal to 2. This will continue until the variable i receives the value 0, the condition becomes false, and the loop ends its work.

To make sure that the loop works, let's create the books stored procedure and put a loop in it:

Now let's call the procedure:

We now have 3 separate tables (for each shipment). Agree that the code with a loop is much shorter than three separate requests. But there is one inconvenience in our procedure, we declared the number of output tables as the default value (DEFAULT 3), and we will have to change this value with each new delivery, which means the procedure code. It is much more convenient to make this number an input parameter. Let's rewrite our procedure by adding an input parameter num, and given that it shouldn't be 0:

Make sure that with different parameters, we still receive tables for each shipment. Our loop has another drawback - if we accidentally set an input value that is too large, then we get a pseudo-infinite loop that will load the server with useless work. Such situations are avoided by tagging the loop and using the LEAVE statement to denote an early exit from the loop.

So, we supplied our loop with the label wet at the beginning (wet :) and at the end, and also added one more condition - if the input parameter is greater than 10 (the number 10 is taken arbitrarily), then the loop with the label wet should end (IF (i> 10) THEN LEAVE wet). Thus, if we accidentally call a procedure with a large num value, our loop will break after 10 iterations (iteration is one pass of the loop).

Loops in MySQL, like branching statements, are almost never used in practice in web applications. Therefore, for the other two types of loops, we will give only the syntax and differences. It is unlikely that you will happen to use them, but you still need to know about their existence.

REPEAT loop statement

The loop condition is checked not at the beginning, as in the WHILE loop, but at the end, i.e. at least once, but the loop runs. The loop itself is executed while the condition is false. The syntax is as follows:

UNTIL condition

LOOP Loop Operator

This loop has no conditions at all, so it must have a LEAVE statement. The syntax is as follows:

This concludes our study of SQL. Of course, we have not considered all the possibilities of this query language, but in real life you are unlikely to have to face even what you already know.

Posted on Allbest.ru

...

Create a function

To begin with, close the current form for creating a procedure by clicking on the button labeled Go. Then, in the same window, again click on the Add routine inscription, a familiar form will appear, fill it out.

Name - GetPostID Type - Function Parameters - ComID BIGINT (20) UNSIGNED Return type (return type) - BIGINT Return length / values - 20 Return options - UNSIGNED Definition: BEGIN RETURN (SELECT comment_post_ID FROM wp_comments WHERE comment_ID = ComID); END;

You can also specify additional parameters:

Is deterministic - a deterministic function always returns the same result with the same input parameters, otherwise it is not deterministic. In our case, check the box.

Definer and Security type security parameters, in this example we will leave them unchanged.

SQL data access has several meanings:

NO SQL - does not contain sql.

Contains SQL - contains built-in sql functions or operators that do not read, write or modify data in the database. For example, setting the value of a variable: SET name = value;

READS SQL DATA - read-only data, without any data modification, specified for a SELECT query.

MODIFIES SQL DATA - changing or entering data into the database, indicated for queries: INSERT, UPDATE, but the SELECT query should not be present.

Our function uses a SELECT query, we indicate READS SQL DATA.

Comment a comment.

After all the fields are filled in, click on the button that says Go.

Go back to the Routines tab and edit our procedure by clicking on the edit button.

Let's assign the PostID variable as a value the result that the GetPostID function will return.

SET postID = GetPostID (ComID);

As a result, the final body of the procedure will be like this

BEGIN DECLARE Author tinytext DEFAULT "admin"; DECLARE UserID bigint (20) DEFAULT 1; DECLARE Email varchar (100); DECLARE Date DATETIME DEFAULT NOW (); DECLARE ParentCom varchar (20); DECLARE Approved varchar (20); DECLARE PostID BIGINT (20); IF Author = "admin" THEN SET Approved = 1; ELSE SET Approved = 0; END IF; SET ParentCom = ComID; SET Email = " [email protected]"; SET PostID = GetPostID (ComID); INSERT INTO wp_comments (comment_author, comment_author_email, comment_content, comment_date, comment_date_gmt, comment_post_id, comment_parent, comment_approved, user_id) VALUES (Author, Email, Content, Parent, Date, PostID, Approved UserID); END;

Leave the rest of the form fields unchanged, click on the Go button. The procedure has been created.

You can also set values for one or more variables as a result of a query. For example, the fields: Author, mail and user id are stored in the wp_users table.

Knowing this, you can set values for these variables as follows:

BEGIN - Declare variables DECLARE Author tinytext DEFAULT "admin"; DECLARE UserID bigint (20) DEFAULT 1; DECLARE Email varchar (100); - execution of a query and setting values for the variables SELECT user_login, user_email, ID INTO Author, Email, UserID FROM wp_users WHERE user_login LIKE "adm%"; END;

Stored Procedure Call

It remains to test the created procedure. To get started, let's add a comment to any article, approve it, and verify that it appears on the page.

Then we find out the id of the added comment

Return to the Routines tab and click on the Execute label

A form will appear

We indicate the values of the transmitted parameters: the response text and the comment id, after which we click on the button with the inscription Go.

Attention! This work is based on the translation of section “17.1. Stored Routines and the Grant Tables "for MySQL 5.0.19," Reference Manual. It documents MySQL 5.0 through 5.0.19. Document generated on: 2006-01-23 (revision: 995) "
"Read everything first, then try examples."

Stored procedures are a collection of SQL commands that can be compiled and stored on the server. Thus, instead of storing a frequently used query, clients can refer to the corresponding stored procedure. This provides better performance because a given request only needs to be analyzed once and there is less server-client traffic. You can also increase your conceptual level by creating a library of functions on the server.

A trigger is a stored procedure that is invoked when a specific event occurs. For example, you can define a stored procedure that fires every time a record is deleted from a transactional table, thus ensuring that the corresponding customer is automatically removed from the customer table when all of its transactions are deleted.

Stored programs (procedures and functions) are supported in MySQL 5.0. Stored procedures are a collection of SQL statements that can be stored on the server. Once this is done, the client does not need to retransmit the request, it just needs to call the stored program.

This can be useful when:

numerous client applications are written in different languages or run on different platforms, but need to use the same database of operations
safety in the first place

Stored procedures and functions (routines) can provide better performance because less information is required to transfer between client and server. The choice increases the load on the database server, but reduces the cost on the client side. Use this if many client machines (such as Web servers) are served by one or more databases.

Stored routines also allow you to use libraries of functions stored in the server database. This feature is provided for many modern programming languages that allow you to call them directly (for example, using classes).

MySQL follows SQL: 2003 syntax for stored procedures, which is already used by IBM's DB2.

From words to deeds ...

When creating, modifying, deleting stored routines, the server manipulates the mysql.proc table

Starting with MySQL 5.0.3, the following privileges are required:

CREATE ROUTINE to create stored procedures

ALTER ROUTINE are required to modify or delete procedures. This privilege is automatically assigned to the creator of the procedure (function)

EXECUTE the privilege is required to execute the subroutine. However, it is automatically assigned to the creator of the procedure (function). Also, by default, the SQL SECURITY parameter for the DEFINER routine, which allows users with access to the database to call the routines associated with that database.

Stored Procedure and Function Syntax

A stored subroutine is a procedure or function. Stored routines are created using the CREATE PROCEDURE or CREATE FUNCTION expressions. The stored routine is called using a CALL expression, with only the return variables being used as outputs. A function can be called like any other function and can return a scalar. Stored routines can call other stored routines.

As of MySQL 5.0.1, a loaded procedure or function is associated with a specific database. This has several meanings:

When a subroutine is called, the implication is to make a call to USE db_name (and cancel the use of the base when the subroutine has finished and the base is no longer needed)
You can qualify common names with the database name. This can be used to refer to a subroutine that is not in the current database. For example, to execute a stored procedure p or a function f that relate to the database test, you can tell the shell to CALL test.p () or test.f ().
When a database is deleted, all loaded routines associated with it are also deleted. In MySQL 5.0.0, the loaded routines are global and not associated with the database. They inherit by default the database from the caller. If USE db_name is executed within the subroutine, the original current DB will be restored after exiting the subroutine (For example, the current DB is db_11, we call a subroutine using db_22, after exiting the subroutine, the current db_11 remains)

MySQL fully supports extensions that allow normal SELECT expressions (without using cursors or local variables) to be used inside stored procedures. The result set returned from the request is simply sent directly to the client. Multiple SELECT queries generate multiple result sets, so the client must use a library that supports multiple result sets.

CREATE PROCEDURE- create a stored procedure.

CREATE FUNCTION- create a stored function.

Syntax:

CREATE PROCEDURE sp_name ([sp_parameter [, ...]])
[feature ...] routine-body

CREATE FUNCTION function_name ([function_parameter [, ...]])
RETURNS a type
[characteristic ...] routine-body

procedure_parameter:
[ IN | OUT | INOUT] parameter_name type
function_parameter:
parameter_name type

a type:
Any MySQL data type

routine-body:
Correct SQL statement.

Let's consider everything in practice.

First, let's create a stored procedure with the following query:

CREATE PROCEDURE `my_proc` (OUT t INTEGER (11))
NOT DETERMINISTIC
SQL SECURITY INVOKER
COMMENT ""
BEGIN
select val1 + val2 into "t" from `my` LIMIT 0,1;
END;

The use of the LIMIT clause in this query is made because not every client is capable of accepting a multi-line result set.

After that, let's call it:

CALL my_proc (@a);
SELECT @a;

To separate the internal request from the external one, a separator other than the usual one is always used (to set, use the command DELIMITER <строка/символ>)

Here's another example with all the requirements in mind.

Mysql> delimiter //
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
-> BEGIN
-> SELECT COUNT (*) INTO param1 FROM t;
-> END;
-> //

mysql> delimiter;
mysql> CALL simpleproc (@a);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @a;
+------+
| @a |
+------+
| 3 |
+------+
1 row in set (0.00 sec)

The whole process can be seen in the figure below:

Triggers

Trigger support has been introduced in MySQL since version 5.0.2.

Trigger- a named DB object that is associated with a table and activated when a certain event occurs, an event associated with this table.

For example, the code below creates a table and an INSERT trigger. The trigger sums up the values that are inserted into one of the table columns.

Mysql> CREATE TABLE account (acct_num INT, amount DECIMAL (10,2));
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
-> FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.06 sec)

Let's declare the variable sum and assign it the value 1. After that, with each insert into the table account the value of this variable will increment according to the inserted part.

Comment... If the value of the variable is not initialized, then the trigger will not work!

Trigger Syntax

CREATE

TRIGGER trigger_name trigger_time trigger_ event
ON table_name FOR EACH ROW expression_executed when_trigger_fired

If everything is clear at once with the trigger name and user name, then we will talk about the "trigger time" and "event" separately.

trigger_time

Determines the time of the trigger action. BEFORE means that the trigger will be executed before the end of the trigger's firing event, and AFTER means after. For example, when inserting records (see example above), our trigger fired before actually inserting a record and calculated the amount. This option is appropriate when pre-calculating some additional fields in a table or parallel insertion into another table.

trigger_ event

Everything is simpler here. It is clearly indicated at which event the trigger is executed.

INSERT: i.e. on inserts or similar expressions (INSERT, LOAD DATA, and REPLACE)
UPDATE: when an entity (row) is updated
DELETE: when a record is being deleted (queries containing DELETE and / or REPLACE statements)

math function programming

Functions are operations that allow you to manipulate data. There are several groups of built-in functions in MySQL:

String functions... Used to manipulate text strings, such as trimming or filling values.

Summary functions... They are used to obtain summary data for tables, for example, when it is necessary to summarize some data without sampling them.

Date and time functions... Used to manipulate date and time values, for example, to return the difference between dates.

System functions... Returns the service information of the DBMS.

In order to look at the main built-in functions, we need to create a new database so that it contains numeric and date values.

Let's take an online store as an example.

Conceptual model:

Relational model:

So, let's look at the last diagram and create a database - shop.

create database shop;

We choose it for work:

SQL Lesson 10. Built-in Functions

Functions are operations that allow you to manipulate data. There are several groups of built-in functions in MySQL:

String functions. Used to manipulate text strings, such as trimming or filling values.

Numeric functions. Used to perform mathematical operations on numeric data. Numeric functions include functions that return absolute values, sines and cosines of angles, square root of a number, etc. They are used only for algebraic, trigonometric and geometric calculations. In general, they are rarely used, so we will not consider them. However, you should be aware that they exist and consult the MySQL documentation if necessary.

Summary functions. They are used to obtain summary data for tables, for example, when it is necessary to summarize some data without sampling them.

Date and time functions. Used to manipulate date and time values, for example, to return the difference between dates.

System functions. Returns the service information of the DBMS.

In order to look at the main built-in functions, we need to create a new database so that it contains numeric and date values. In Lesson 5 Database Basics, we created a relational database model for an online store. It's time to implement it in MySQL, at the same time we will consolidate the past.

So, we look at the last diagram of lesson 5 on the database and create a database - shop.

Create database shop;

We choose it for work:

create table customers (id_customer int NOT NULL AUTO_INCREMENT, name char (50) NOT NULL, email char (50) NOT NULL, PRIMARY KEY (id_customer)); create table vendors (id_vendor int NOT NULL AUTO_INCREMENT, name char (50) NOT NULL, city char (30) NOT NULL, address char (100) NOT NULL, PRIMARY KEY (id_vendor)); create table sale (id_sale int NOT NULL AUTO_INCREMENT, id_customer int NOT NULL, date_sale date NOT NULL, PRIMARY KEY (id_sale), FOREIGN KEY (id_customer) REFERENCES customers (id_customer)); create table incoming (id_incoming int NOT NULL AUTO_INCREMENT, id_vendor int NOT NULL, date_incoming date NOT NULL, PRIMARY KEY (id_incoming), FOREIGN KEY (id_vendor) REFERENCES vendors (id_vendor)); create table products (id_product int NOT NULL AUTO_INCREMENT, name char (100) NOT NULL, author char (50) NOT NULL, PRIMARY KEY (id_product)); create table prices (id_product int NOT NULL, date_price_changes date NOT NULL, price double NOT NULL, PRIMARY KEY (id_product, date_price_changes), FOREIGN KEY (id_product) REFERENCES products (id_product)); create table magazine_sales (id_sale int NOT NULL, id_product int NOT NULL, quantity int NOT NULL, PRIMARY KEY (id_sale, id_product), FOREIGN KEY (id_sale) REFERENCES sale (id_sale), FOREIGN KEY (id_product) REFER_product) products (id) create table magazine_incoming (id_incoming int NOT NULL, id_product int NOT NULL, quantity int NOT NULL, PRIMARY KEY (id_incoming, id_product), FOREIGN KEY (id_incoming) REFERENCES incoming (id_incoming), FOREIGN KEY (id_product) REFERPENCES) products (id_product) REFERPENCES) products (id_product)

INSERT INTO vendors (name, city, address) VALUES ("Williams", "Moscow", "Lesnaya St., 43"), ("House of the Press", "Minsk", "F. Skaryna Ave., d. 18 "), (" BHV-Petersburg "," St. Petersburg "," Esenina street, 5 "); INSERT INTO customers (name, email) VALUES ("Ivanov Sergey", " [email protected]"), (" Lenskaya Katya "," [email protected]"), (" Demidov Oleg "," [email protected]"), (" Afanasiev Victor "," [email protected]"), (" Pazhskaya Vera "," [email protected] "); INSERT INTO products (name, author) VALUES (" Poems about love "," Andrei Voznesensky "), (" Collected works, volume 2 "," Andrei Voznesensky "), (" Collected works, volume 3 "," Andrey Voznesensky "), (" Russian poetry "," Nikolai Zabolotsky "), (" Mashenka "," Vladimir Nabokov "), (" Doctor Zhivago "," Boris Pasternak "), (" Ours "," Sergey Dovlatov ") , ("Invitation to execution", "Vladimir Nabokov"), ("Lolita", "Vladimir Nabokov"), ("Dark alleys", "Ivan Bunin"), ("Gift", "Vladimir Nabokov"), (" The son of the leader "," Julia Voznesenskaya "), (" Emigrants "," Alexei Tolstoy "), (" Woe from Wit "," Alexander Griboyedov "), (" Anna Karenina "," Leo Tolstoy "), (" Stories and stories "," Nikolai Leskov "), (" Antonov apples "," Ivan Bunin "), (" Dead Souls "," Nikolai Gogol "), (" Three Sisters "," Anton Chekhov "), (" Runaway ", "Vladimir Dal"), ("The Idiot", "Fyodor Dostoevsky"), ("The Brothers Karamazov", "Fyodor Dostoevsky"), ("The Inspector General", "Nikolai Gogol"), ("Garnet Bracelet", "Alexander Kuprin" ); INSERT INTO incoming (id _vendor, date_incoming) VALUES ("1", "2011-04-10"), ("2", "2011-04-11"), ("3", "2011-04-12"); INSERT INTO magazine_incoming (id_incoming, id_product, quantity) VALUES ("1", "1", "10"), ("1", "2", "5"), ("1", "3", "7 "), (" 1 "," 4 "," 10 "), (" 1 "," 5 "," 10 "), (" 1 "," 6 "," 8 "), (" 1 ", "18", "8"), ("1", "19", "8"), ("1", "20", "8"), ("2", "7", "10") , ("2", "8", "10"), ("2", "9", "6"), ("2", "10", "10"), ("2", "11 "," 10 "), (" 2 "," 21 "," 10 "), (" 2 "," 22 "," 10 "), (" 2 "," 23 "," 10 "), ( "2", "24", "10"), ("3", "12", "10"), ("3", "13", "10"), ("3", "14", "10"), ("3", "15", "10"), ("3", "16", "10"), ("3", "17", "10"); INSERT INTO prices (id_product, date_price_changes, price) VALUES ("1", "2011-04-10", "100"), ("2", "2011-04-10", "130"), ("3 "," 2011-04-10 "," 90 "), (" 4 "," 2011-04-10 "," 100 "), (" 5 "," 2011-04-10 "," 110 ") , ("6", "2011-04-10", "85"), ("7", "2011-04-11", "95"), ("8", "2011-04-11", "100"), ("9", "2011-04-11", "79"), ("10", "2011-04-11", "49"), ("11", "2011-04 -11 "," 105 "), (" 12 "," 2011-04-12 "," 85 "), (" 13 "," 2011-04-12 "," 135 "), (" 14 ", "2011-04-12", "100"), ("15", "2011-04-12", "90"), ("16", "2011-04-12", "75"), ( "17", "2011-04-12", "90"), ("18", "2011-04-10", "150"), ("19", "2011-04-10", "140 "), (" 20 "," 2011-04-10 "," 85 "), (" 21 "," 2011-04-11 "," 105 "), (" 22 "," 2011-04-11 "," 70 "), (" 23 "," 2011-04-11 "," 65 "), (" 24 "," 2011-04-11 "," 130 "); INSERT INTO sale (id_customer, date_sale) VALUES ("2", "2011-04-11"), ("3", "2011-04-11"), ("5", "2011-04-11") ; INSERT INTO magazine_sales (id_sale, id_product, quantity) VALUES ("1", "1", "1"), ("1", "5", "1"), ("1", "7", "1 "), (" 2 "," 2 "," 1 "), (" 3 "," 1 "," 1 "), (" 3 "," 7 "," 1 ");

So, in our store there are 24 items of goods brought in three deliveries from three suppliers, and absolutely three sales. Everything is ready, we can start learning the built-in functions of MySQL, which we will do in the next lesson.

Editor's Choice

Old Believers' rumors and accords on the territory of the Nizhny Novgorod region

The Nizhny Novgorod region and Nizhny Novgorod are historically the second center of the Russian Old Believers after Moscow. Currently in ...

What happens at Shrovetide

Maslenitsa is one of the oldest Russian holidays. Pagan in origin, Maslenitsa peacefully "got on" with religious traditions ...

How to draw a beautiful fox with a pencil, even if you are not an artist at all

"Mom, draw!" Every mom sooner or later hears from her child the cherished "Mom, draw for me ...". And the options for ending this phrase ...

The two curses of Archpriest Avvakum

Archpriest Avvakum (1620-1682) is an outstanding historical figure. On Russian soil, the authority of this man in the 17th century was ...

Leo Tolstoy all the best fairy tales and stories Leo Tolstoy fairy tales for children

There were brother and sister - Vasya and Katya; and they had a cat. In the spring, the cat disappeared. The children looked for her everywhere, but could not find it. Once they played ...

How to draw a basket with a pencil step by step

Aspiring artists often have situations when they have no experience in depicting something. In order not to get confused, to understand where to start ...

What is holy water and how to use it correctly How to use holy water at home

Holy water - ordinary in composition and original origin water (well, spring, lake, river, tap), wonderful ...

Chicken hearts in cheese sauce

For a long time, you can buy chicken hearts in the store, but until recently, hearts were sold exclusively together with the liver ...

Turkey baked with dried fruits from Oksana Mysina Turkey fillet with dried apricots recipes

With apples and dried apricots (it is better to take turkey breast fillets for this dish), baked under foil - the dish is not quite ordinary, thanks to ...

New

Popular

Mysql built-in functions. Stored procedures and triggers

Send your good work in the knowledge base is simple. Use the form below

Similar documents

Create a function

Stored Procedure Call

Stored Procedure and Function Syntax

Triggers

SQL Lesson 10. Built-in Functions