SQL statement question 30 for mysql

You need to write a stored procedure that can do the above functionality! After all, what you're describing has a branching structure!

There are a total of 6 fields, and if the incoming value is more than 5 more than the corresponding field, add 1 pair.

select (case when value 1 - field 1>=5 then 1 else 0 end).

case when pass in value 2 - field 2 >=5 then 1 else 0 end).

case when the value 6 - field 6 >=5 then 1 else 0 end).

from table where number of rows = rows to be compared.

1.Create two tables with the same data structure<>test1 and test2

Data sheet test1

Data sheet test2

2.Execute SQL queries.

3.The result is 2

1 In the stored procedure of MySQL, there is no fixed output statement, to output the content, use select .form is sufficient;

select var_a;

select column_a from tb_a limit 2;

2. In the stored procedure, if you only output the content (without capturing), you can use select in 1 above; If the value of the output is subsequently captured after the stored procedure is invoked, you need to specify the output parameter with out;

3 @var a indicates that this is a session variable that can be set directly in the stored procedure without declaring, e.g.

set @var_a = 10;

However, it is not recommended to use session variables in stored procedures (because such variables are valid in the same MySQL connection and still exist after the storage is executed), it is recommended to use declarative temporary variables, that is, the following methods:

declare var_a int;

set var_a = 10;

4 Examples. drop procedure p_a;

create procedure p_a(out aa int)

begindeclare bb int;

set bb = 10;

select bb;

select * from t_student limit 1;

select current_date();

set aa = bb;

end;call set @temp = 50;

call p_a(@temp);

select @temp;@temp is a session variable that is returned by the stored procedure to reset the value.

This sql is really hard to understand, and I finally understood it. Something like this:

1. The method of permanent alias assumes that a table is a and b (I will say a and b later);

2. The relationship between A and B is through the equality of classes, and the resume is connected.

Let's get to the point:

3. Use a record from table A to compare the size of the same record as the class in table B ( > then the meaning of the statement after where is: count the number of records with a score in table B greater than the score in table A.

4. If the number of statistics is less than 3, then the record of table A is the top three, otherwise it is not a record of the second record of table A, and it is compared in a loop.

I hope I make it clear, if I don't understand, we can continue to discuss it.

Hello dear, glad to answer for you, <>

The MySQL problem is as follows: Step 1: If the query cache server finds this SQL statement in the query cache, it will directly return the result to the client; If not, move on to the parser phase.

It should be noted that because query caching is often inefficient, this feature is later discarded. Step 2: The parser performs lexical analysis and semantic analysis on the SQL statement in the parser to determine whether the input SQL statement satisfies the MySQL syntax.

Step 3: Optimizer Optimization goes through the parser and MySQL knows what you're going to do. There are many ways to execute a query, although they all return the same result in the end, and the role of the optimizer is to find the best execution plan in Sakurabu.

Step 4: Execute the executorBefore executing, you need to determine whether the user has permissions. If not, a permission error will be returned.

If you have permissions, execute the SQL query and return the results. In the following versions, if the query cache is set, the kernel will cache the query results. Thank you for your consultation, I hope this service can help you, you can click on my avatar to follow me, and if you have any questions in the future, you can consult me again.

I wish you a happy life, a happy family, good health, and all the best <>