PHP learning and MYSQL advanced

PHP

Application of php annotation

Comments are the interpretation and explanation of the code, and the comments will be ignored by the interpreter

<?php
    /*
        Multiline text note
    */
?>
<?php
    //Single line text note
?>

Initial experience of php program

Each line of php code must end with an English semicolon

The printout can only output character numeric strings, which are enclosed in quotation marks (single quotation marks and double quotation marks in English)

<?php
    header("content-type:text/html;charset=utf-8");//Set the page code to utf-8
    echo "hello world"; //Output text content hello world
    
    echo 123; //Output number 123
    
    echo "<br>"; //Forced line feed
    
    echo "<h1>title</h1>"; //Output title
    
    echo "<hr>"; //Output horizontal line
?>

variable

A variable is a data container. What kind of data you put into the container, then the container is what data

Declaration and initialization of variables

Start with $(it is joked that PHP programmers are so poor that they require variable names to start with currency symbols);

$variable name = variable value

Naming rules and specifications of variables: 1 Variable names should preferably be composed of numbers, letters and underscores The number cannot start with 3 Try not to use Chinese 4 Strictly case sensitive

<?php
    header("content-type:text/html;charset=utf-8");//Set the page code to utf-8
    
    $name="Zhang San";
    echo $name;
    
    echo "<hr>";
    
    $full name = "Li Si";
    echo $full name;
    
    echo "<hr>";
    
    $a = 123;
    $A = 456;
    echo $a;
    echo "<br>";
    echo $A;
    
    echo "<hr>";
    
    $text = "Article content article content article content article content article content article content article content article content article content article content article content article content article content article content article content";
    echo $text;
    
    echo "<hr>";
    
    $content = "Wang Wu";
    echo $content;
    echo $content;
    echo $content;
    $content = "Zhao Si";
    echo $content;

constant

A constant can be understood as an amount whose value remains unchanged. After a constant value is defined, it cannot be changed elsewhere in the script

Constant names do not need a $prefix (nor can they be set), and in order to distinguish them from variables, they are usually named in uppercase letters (also case sensitive). In addition, the naming rules of constant names, like variable names, only support initials, numbers and underscores, and cannot start with numbers, except that they do not need a $prefix.

Define constants through the define function

<?php
    define("LANGUAGE","PHP");
    define("SCHOOL","Guangming primary school");
    echo LANGUAGE;
    echo SCHOOL;

data type

php supports 8 data types:

Four scalar types: Boolean, integer, float / double, string

Two composite types: array and object

Two special types: resource and null

type	explain
Boolean (Boolean)	This is the simplest type. There are only two values, true and false
Integer (integer)	Integer data types can only contain integers. These data types can be positive or negative
Float / double	The floating-point data type is used to store numbers. Unlike integers, it has decimal places
String (string type)	String is a continuous character sequence, which can be a set of all characters that can be represented by a computer

In PHP, strings can be defined in single quotation marks or double quotation marks, and variables of string type are used to represent [mysql advanced]

$course = "mysql senior";
echo $course;

String type judgment

To determine whether the variable data type is a string, you can use is_string function:

if (is_string($course)) {
    echo '$course Is a string';
}
exit;

Here, we use the if statement to judge whether the variable is a string type (described later on the if statement), and finally exit the program manually through exit.

The difference between single quotation marks and double quotation marks

Through the above code, you can also see that the reference variable in the single quotation mark string will not parse the variable value. If it is a double quotation mark, the reference variable value will be parsed:

if (is_string($course)) {
    echo "\"$course\" Is a string";
}
if (is_string($course)) {
    echo "'$course' It's also a string";
}

In addition, because we have introduced double quotation marks into double quotation string, we need to escape them through escape character \ in order to output normally. If single quotation marks are referenced in double quotation string, there is no need to escape;

Since PHP uses quotation marks to parse strings, the performance of strings with single quotation marks is worse than that of strings with single quotation marks, For example, it contains escape characters (characters beginning with \, such as \ n `, ` \ "`, ` \ t `, etc.) or single quotation marks. Single quotation mark strings do not support escape characters (` \ ').

Of course, for single quotation string, you can also realize the function similar to the above double quotation string, but writing code is more troublesome and less readable

if (is_string($course)) {
    echo '\"$course\" Is a string';
}

String: Everything enclosed in quotation marks is a string

var_dump() is a function that can print data and output data types at the same time

$a = '123';
var_dump($a);

array

Arrays are divided into index arrays and associative arrays

Index array

The so-called index array means that the key of the array is an implicit number and will be maintained automatically, just like the array of static language.

The index value of php index array, like other languages, starts from 0.

$courses = array('php','html','mysql');
print_r($courses);

$nums = [1,2,4,5];
print_r($nums);

In addition to initializing the array by specifying the initial value like the above code, you can also initialize an empty array. In PHP, when initializing an empty array, you do not need to specify the array size or data type:

$fruits = [];

Then we can add elements to the array in turn:

$fruits[] = 'Apple';
$fruits[] = 'Orange';
$fruits[] = 'banana';

We can get the length of the array through the count function.

$len = count($fruits);

If you want to obtain the element value corresponding to the specified index, you can do so as follows:

$fruit = $fruits[0];

To update the element value corresponding to the specified index position, you can do the following:

$fruits[2] = 'Banana';

To delete the element value corresponding to the specified index position, you can use the unset function:

unset($fruits[1]);

Associative array

Unlike indexed arrays, associative arrays usually need to explicitly specify the keys of array elements

$student = [
    'name'=>'Zhang San',
    'age' => 14,
    'sex' => 'male'
]
$student = array(
    'name'=>'Zhang San',
    'age' => 14,
    'sex' => 'male'
)

At this time, the key is no longer a continuous number, but the key name set during initialization

$student = [];

Then add new elements by specifying key value pairs

$student['name'] = 'Li Si';
$student['age'] = 18;
$student['sex'] = 'male';

To obtain the value of an element, it is more convenient than indexing the array, because the corresponding element value can be obtained directly through the more readable key name:

$name = $student['name'];

To update the value of an element, you can also modify it by specifying the key name:

$student['age'] = 19;

To delete the value of an array element, you can still use the unset function:

unset($student['sex']);

operator

Arithmetic operator

Arithmetic operators include addition (+), subtraction (-), multiplication (*), division (/), and remainder (%)

$a=10;
$b=2;
echo $a+$b;
echo '<br>';
echo $a-$b;
echo '<br>';
echo $a*$b;
echo '<br>';
echo $a/$b;
echo '<br>';
echo $a%$b;

echo $b%$a;

Comparison operator

Therefore, the comparison operator refers to the comparison of variable values, which is usually the comparison between numerical values.

Comparison operators include: greater than (>), less than (<), less than or equal to (< =), greater than (> =), equal to (= =), unequal (! =), identity (= =), non identity (! = =)

Identity is not only numerically equal, but also of the same type

Non identity indicates unequal values or types

Auto increment / Auto decrement operator

// $a=1;
// echo ++$a is to add 1 before output
// echo $a + + is to output the original value of $a first and then add 1
// echo $a;
// $a = $a+3 is equivalent to $a+=3
// ++$a is equivalent to $a+=1

$a=1;
echo ++$a;
echo $a;
    
echo $a++;
echo $a;
    
echo $a+=3;
echo $a;

// echo --$b is minus 1 before output
// echo $b -- first output the original value of $b, and then subtract 1
// $b = $b-3 is equivalent to $b-=3
// --$b is equivalent to $b-=1

$b = 10;
echo --$b;
echo $b;

echo $b--;
echo $b;

echo $b-=3;
echo $b;

Logical operator

The so-called logical operation is AND (AND OR & &), OR (OR or |), NOT (NOT OR!) XOR operation, which is also a concept with the logical operation introduced in the high school mathematics textbook

XOR: returns true when the value is one true and one false

control structure

Single branch structure

The so-called single branch refers to the existence of a conditional judgment and selection:

$score = 80;
if($score>=80 && $score<=90){
    echo 'excellent';
}

Double branch structure

In order to deal with the case of if condition miss, we can introduce an else statement to deal with other business logic. This code mode that meets the if condition and executes the corresponding business logic, but does not meet the if condition and executes the else set business logic is called double branch structure:

$score = 70;
if($score>=80 && $score<=90){
    echo 'excellent';
}else{
    echo 'qualified';
}

Multi branch structure

Although this processing method can obtain some clear information, such as score data, the grade information is not clear, which is only a rough "other grade". To obtain clear grade information when the if condition is not tenable, you need to introduce multiple else if statements to add more other condition judgments, Finally, take an else statement as the bottom (default branch). This code selection structure mode is called multi branch structure:

$score = 60;
if ($score >= 90) {
    echo 'excellent';
} else if ($score >= 80 && $score < 90) {
    echo 'good';
} else if ($score >= 60 && $score < 80) {
    echo 'qualified';
} else if ($score < 60) {
    echo 'difference';
} else {
    echo 'No results';
}

$score = 60;
if ($score >= 90) {
    echo 'excellent';
} else if ($score >= 80) {
    echo 'good';
} else if ($score >= 60) {
    echo 'qualified';
} else if ($score < 60) {
    echo 'difference';
} else {
    echo 'No results';
}

Cyclic structure

The so-called loop structure means that when the loop conditions are met, the loop executes the code in the loop body:

The implementation of loop structure generally includes while, do The final functions of while and for loops are the same, but they are different in the setting of loop conditions.

while

To print the score information through the while loop, you can write the implementation code as follows:

$score = [70,90,60,76];
$num = count($score);
$i=1;
while($i<=$num){
    echo "The first $i Grade information of students:";
    echo $score[$i-1];
    echo "<br>";
    $i++;
}

In this code, first get the total amount of data $num, and then set an iteration variable $i. after printing one student information each time, the iteration variable will be + 1 until the value of the iteration variable exceeds the total amount of data, and the cycle ends.

do...while

You can also do Write the above code in the while loop:

$score = [70,90,60,76];
$num = count($score);
$i=1;
do{
    echo "The first $i Grade information of students:";
    echo $score[$i-1];
    echo "<br>";
    $i++;
}while($i<=$num);

The print result is consistent with the while loop, because do It is not common for a loop to be executed after the while condition is set in the do block.

for

The most common is the for loop, which is flexible enough and better readable. Let's reconstruct the above loop code through the for loop

$score = [70,90,60,76];
$num = count($score);
for($i=1;$i<=$num;$i++){
    echo "The first $i Grade information of students:";
    echo $score[$i-1];
    echo "<br>";
}

foreach

In PHP, another more powerful and simple language structure foreach is provided for circular array traversal, especially associative array traversal. Through foreach statements, it is more convenient to obtain key value information

$score = [70,90,60,76];
foreach($score as $key=>$val){
    echo "The first $key Grade information of students:";
    echo $val;
    echo "<br>";
}

Where $data represents the array to be traversed (or the class implementing the ArrayAccess interface), $key represents the key of the array and $val represents the corresponding key value. There is no need to set iteration variables and loop termination conditions. After traversal, the loop will exit automatically

$student = [
    'name'=>'Zhang San',
    'age' => 14,
    'sex' => 'male'
];
foreach($student as $val){
    echo $val;
    echo "\n";
}
$student = [
    'name'=>'Zhang San',
    'age' => 14,
    'sex' => 'male'
];
foreach($student as $key=>$val){
    echo "$key\n:\n$val";
    echo '<br>';
}

php mysql database operation

Connect database

mysql_connect('Database server ',' account to log in to database server ',' password to log in to database server ')
//Database server: localhost or 127.0.0.1
 //Login account of database server: root
 //Login account of database server: root

Select database

mysql_ select_ The db() function sets the active MySQL database.

If successful, the function returns true. false if failed.

mysql_ select_ DB (database name, database connection)

Execute Sql

mysql_ The query() function executes a MySQL query.

If there is no open connection, this function will try to call MySQL without parameters_ Connect() function to establish a connection and use it.

mysql_query() only returns a resource identifier for SELECT, SHOW, EXPLAIN or DESCRIBE statements. If the query is not executed correctly, it returns FALSE.

For other types of SQL statements, mysql_query() returns TRUE on successful execution and FALSE on error.

A non FALSE return value means that the query is legal and can be executed by the server. This does not indicate any information about the number of rows affected or returned. It is likely that a query was executed successfully but did not affect or return any rows.

mysql_query(sql statement, database connection);

mysql_fetch_array() function

mysql_ fetch_ The array() function takes a row from the result set as an associative array, a numeric array, or both

MYSQL_ASSOC - associative array MYSQL_NUM - numeric array MYSQL_BOTH - default. Generate both associative and numeric arrays

Returns the array generated from the rows obtained from the result set, or false if there are no more rows.

header("Content-Type:text/html;charset=utf-8");

 
#Connect database

$conn=@mysql_connect('localhost','root','root') or die('Failed to connect to database~');


#Select database

mysql_select_db('db_school') or die('Failed to select database~');


#Set Chinese character set

mysql_query('set names "utf8"');
$sql = "SELECT * from tb_student";
$result = mysql_query($sql,$conn);
print_r(mysql_fetch_array($result));
//print_r(mysql_fetch_array($result,MYSQL_ASSOC ));
//print_r(mysql_fetch_array($result,MYSQL_NUM ));

mysql_close($conn); //Close database connection

mysql_ fetch_ Array (query result set, MYSQL_NUM) is equivalent to MySQL_ fetch_ Row (query result set) returns an array of numbers

mysql_ fetch_ Array (query result set, MYSQL_ASSOC) is equivalent to MySQL_ fetch_ Assoc (query result set) returns an associative array

mysql_num_rows() function

Get the number of queried data

mysql_affected_rows() function

mysql_ affected_ The rows() function returns the number of record rows affected by the previous MySQL operation.

Get the latest contact with link_ The number of record rows affected by the INSERT, UPDATE or DELETE query associated with the identifier.

If the execution is successful, the number of affected rows is returned. If the last query fails, the function returns - 1.

If the last operation is a DELETE query without any conditions (WHERE), all records in the table will be deleted, but the return value of this function was 0 before version 4.1.2.

When using UPDATE query, MySQL will not UPDATE the columns with the same original value as the new value. This makes MySQL_ affected_ The return value of the rows () function is not necessarily the number of records that match the query criteria. Only the number of records that have been modified will be returned.

REPLACE statementfirst deletes a record with the same primary key, and then inserts a new record. This function returns the number of records deleted plus the number of records inserted.

$sql = 'delete from tb_student whre studentno=1';
mysql_query($sql,$conn);
$num = mysql_affected_rows();
echo $num;
mysql_close($con);

mysql events

Concept of event

Events perform certain tasks based on a specific time period

Events are procedural database objects called by MySQL at the corresponding time. An event can be called once or started periodically. It is managed by a specific thread, which is the so-called "event scheduler"

Generally speaking, events are similar to mysql triggers, which means that a sql program is executed once or more at a certain time (a specific time is represented by time) or in a certain time period

Advantages and disadvantages

1) Advantages

Some data timing operations no longer rely on external programs, but directly use the functions provided by the database itself. It can execute one task per second, which is very practical in some environments with high real-time requirements.

2) Shortcomings

It is triggered regularly and cannot be called by the program

View events

1) Check whether the event is turned on

show variables like 'event_scheduler'


select @@event_scheduler

2) Set event on

set global event_scheduler = 1;

set global event_scheduler = true;

set global event_scheduler = On

modify my.ini In which, add EVENT_SCHEDULER = 1

Create event

Events can be created through the createa event statement. The syntax format is:

CREATE EVENT [if not exists] event_ Name (event name) ON SCHEDULE schedule [ENABLE | DISABLE |DISABLE ON SLAVE]
DO event_ Body (event body)

The syntax format of schedule is
 At < timestamp > [+ interval < interval >]
perhaps
 Every < interval > [starts < timestamp > [+ interval < interval >]...] [ends < timestamp > [+ interval < interval >]]

The syntax format of interval is

quantity { YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND }

event_name: the specified event name can be decorated with the keyword IF NOT EXISTS

< event body >: the < event body > part of the DO clause is used to specify the code to be executed when the event starts. If it contains mu lt iple statements, you can use begin End conforms to structure

schedule: event scheduling, used to specify when or how often an event occurs, corresponding to the following two clauses respectively

(1) AT clause: used to specify that the event occurs AT a certain time. Among them, timestamp represents a specific time point, which can be followed by a time interval, indicating that the event occurs after this time interval; Interval refers to the time interval, which is composed of a value and unit; quantity is the value of the interval time.

(2) EVERY clause: used to indicate how often an event occurs in a specified time interval. The STARTS clause is used to specify the start time and the ENDS clause is used to specify the end time

event_body: event in do clause_ The body part is used to specify the code to be executed when the event starts. If there are multiple statements, you can use begin End composite structure

enable | disable | disable on slave: optional, indicating an attribute of the event. The keyword enable indicates that the event is active, which means that the scheduler checks whether the event action must be called;

The keyword disable indicates that the event is closed, which means that the declaration of the event is stored in the directory, but the scheduler does not check whether it should be called

The keyword disable on slave indicates that the event is closed in the slave.

clause	explain
DEFINER	Optional, used to define the user who checks the permission when the event is executed
IF NOT EXISTS	Optional to determine whether the event to be created exists
EVENT event_name	Required to specify the event name, event_ The maximum length of name is 64 characters. If event is specified_ Name, the default is the current MySQL user name (case insensitive)
ON SCHEDULE schedule	Required to define the execution time and interval
ON COMPLETION [NOT] PRESERVE	Optional. It is used to define whether the event is executed circularly, that is, once or permanently. By default, it is executed once, Not preset
ENABLE \| DISABLE \| DISABLE ON SLAVE	Optional to specify a property of the event. The keyword ENABLE indicates that the event is active, that is, the scheduler checks whether the event must be called; The keyword DISABLE indicates that the event is closed, that is, the declaration of the event is stored in the directory, but the scheduler will not check whether it should be called; The keyword DISABLE ON SLAVE indicates that the event is closed in the slave. If you do not specify any of these three choices, an event becomes active immediately after it is created.
COMMENT 'comment'	Optional, used to define the comment of the event
DO event_body	Required to specify the code to be executed when the event starts. It can be any valid SQL statement, stored procedure, or a planned event. If it contains multiple statements, you can use begin End composite structure

Create an event, start in 5 seconds, execute every 10 seconds, and end in 1 minute

mysql> delimiter $$
mysql> create event if not exists event_insert
    -> on schedule every 10 second
    -> starts current_timestamp + interval 5 second
    -> ends current_timestamp + interval 1 minute
    -> do
    -> begin
    -> INSERT INTO tb_student VALUES(NULL,'Zhang Xiaoyong','male','1997-12-11','Shanxi','Chinese','AC1301');
    -> end $$
mysql> delimiter ;

Create event, execute in 10 seconds

mysql> delimiter $$
mysql> create event if not exists event_insert_2
    -> on schedule at current_timestamp + interval 10 second
    -> do
    -> begin
    -> INSERT INTO tb_student VALUES(NULL,'Zhang Xiaoyong','male','1997-12-11','Shanxi','Chinese','AC1301');
    -> end $$
mysql> delimiter ;

View events

select * from mysql.event

Modify event

After an event is created, its definition and related attributes can be modified through the alter event statement

Syntax format:

ALTER 
    EVENT <Event name>
    [ON hu schedule]
    [RENAME TO <New event name>]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [DO <Event subject>]
alter event event_insert disable;
alter event event_insert rename to e_insert;


Open event

alter event event_insert_2 enable;


Close event

alter event event_insert disable;

Delete event

Syntax format:

DROP EVENT [IF EXISTS] event_name

 

for example

drop event if exists event_insert;

Stored procedure and stored function

stored procedure

Stored procedure: a set of sql statements to complete specific functions, which are compiled and stored in the database. Users call and execute the stored procedure by specifying its name and giving parameters (if there are parameters in the stored procedure).

advantage:

Encapsulation: after a stored procedure is created, it can be called many times in the program without having to rewrite the sql statement of the stored procedure, and database professionals can modify the stored procedure at any time without affecting the source code of the application calling it.

It can enhance the function and flexibility of sql statements. Stored procedures can be written with flow control statements, which have strong flexibility and can complete complex judgment and more complex operations.

It can reduce network traffic: because the stored procedure runs on the server and executes fast, when the stored procedure is called on the client computer, only the call statement is transmitted in the network, which can reduce the network load.

High performance: after the stored procedure is executed once, the generated binary code resides in the buffer. In future calls, you only need to execute the binary code from the buffer, which improves the efficiency and performance of the system.

Improve database security and data integrity, use stored procedures to complete all database operations, and control the access authority of database information through programming.

Create stored procedure

Syntax format

Create procedure < procedure name > ([procedure parameter.....])< Process body >

[process parameter] format
 [in | out | inout] < parameter name > < type >

< procedure name >: the name of the stored procedure. It is created in the current database by defau lt. When a stored procedure needs to be created in a specific database, the name of the database, DB, should be added before the name_ name,sp_ name.

[process parameters.....]: mysql stored procedures support three types of parameters: input parameters, output parameters and input / output parameters, which are identified by three keywords: in, out and inout. Among them, the input parameters can be passed to a stored procedure; Output parameters are used when the stored procedure needs to return an operation result; The input / output parameters can act as both input parameters and output parameters.

Note: the parameter name should not be the same as the column name of the data table. Although no error message will be returned, the sql statement in the stored procedure will treat the parameter name as the column name, resulting in unpredictable results

< procedure body >: the main part of the stored procedure, also known as the stored procedure body, contains the sql statements that must be executed when the procedure is called. This section starts with the keyword begin and ends with the keyword end. If the stored procedure has only one sql statement, you can omit begin End flag.

Delimiter

Syntax format:

DELIMITER $$

be careful:

$$is a user-defined terminator. Usually, this symbol can be some special symbols, such as two "?" Or two "￥"

When using the DELIMITER command, you should avoid using the backslash "\" character because it is an escape character of mysql

mysql> delimiter $$
mysql> create procedure up_name(in sno int,in sname varchar(6))
    -> begin
    -> update tb_student set name=sname where studentno = sno;
    -> end $$
mysql> delimiter ;

 
Call stored procedure

mysql> call up_name(2147483647,'Wang Wu');

update tb_student set name='Wang Wu' where studentno = 2147483647;

Stored procedure body

local variable

Local variables can be declared in the stored procedure body to store the temporary results.

Note: local variables, which correspond to global variables and are valid in the local range.

Syntax format

Declare < variable name > [,...]< Type > [default < default >]

< variable name >: the name of the specified local variable,

Note: do not duplicate the keyword, reserved word and column name of mysql

< type >: Specifies the data type of the local variable

Default clause: used to specify a default value for a local variable, which is null by default

declare xname varchar(5) default 'Li Ming';

be careful:

Local variables can only be in the stored procedure body begin Declared in end statement

The scope of local variables is only in begin Between end

Local variables must be declared at the beginning of the stored procedure body

The difference between local variables and user variables is that local variables are declared without the @ symbol and can only be declared in begin Use within the scope of application; When declaring user variables, you need to use the @ sign. The declared user variables exist in the whole session.

set statement

After a variable is declared, you can use the set statement to assign a value to a local variable

Syntax format

Set < variable name > = < expression 1 >

set xname = 'Wang Jie'

select .... into statement

You can use Select The into statement directly stores the value of the selected column in the local variable, and the select in the stored procedure body Into can only return one row of data

Syntax format

select <Listing>  into <Variable name> [.....] <other>
mysql> delimiter $$
mysql> create procedure select_name(in sno int,out sname varchar(6))
    -> begin
    -> select name into sname from tb_student where studentno=sno;
    -> end $$
mysql> delimiter ;
mysql> call select_name(2013110101,@str);
mysql> select @str;
+--------+
| @str   |
+--------+
| Wang Xiaofei |
+--------+
1 row in set

Process control statement

1)IF ~ THEN ~ELSE

Syntax format:

If < judgment statement > then < statement >
[else if < judgment statement > then < statement >]
[else < statement >]
end if

mysql> delimiter $$
mysql> create procedure select_sex(in sno int)
    -> begin
    -> declare stu_sex char(2);
    -> select sex into stu_sex from tb_student where studentno=sno;
    -> if stu_sex='male' then
    -> select stu_sex,1;
    -> elseif stu_sex='female' then
    -> select stu_sex,2;
    -> end if;
    -> end$$
mysql> delimiter ;

2)case

case <parameter>
when <parameter> then <sentence>
[when <parameter> then <sentence>]
[else sentence]
end case

mysql> delimiter $$
mysql> create procedure sel_sex(in sno int)
    -> begin
    -> declare stu_sex char(2);
    -> select sex into stu_sex from tb_student where studentno=sno;
    -> case
    -> when stu_sex='male' then select stu_sex,'1';
    -> when stu_sex='female' then select stu_sex,'2';
    -> else
    -> select stu_sex;
    -> end case;
    -> end $$
mysql> delimiter ;

cursor

select ... After the into statement is successfully executed, a row of data with value will be returned, which can be read into the stored procedure for processing. However, when using the select statement for data retrieval, if the statement is successfully executed, a group of data rows called the result set will be returned. There may be multiple rows of data in the result set. These data cannot be processed directly row by row. At this time, a cursor is needed;

definition:

Cursor is a result set retrieved by the select statement. After storing the cursor, the application or user can scroll or browse the data as needed.

be careful:

Cursors can only be used in stored procedures or stored functions, and cannot be used alone in query operations;

Multiple cursors can be defined in a stored procedure or function, but in a begin The name of each cursor in the end statement block must be unique

The cursor is not a select statement, but a result set retrieved by the select statement

mysql supports cursors from mysql 5.0 0, previous versions of mysql cannot use cursors

Syntax format:

Declare < cursor for < select statement >

Open cursor

Open < tag name >: Specifies the cursor to open

Read data

Fetch < tag name > into < variable name > [variable 2]

Close cursor

Close < swim label >

The cursor must be closed after the end of cursor use.

Keywords: PHP MySQL

Added by scarlson on Tue, 01 Mar 2022 03:16:21 +0200

Programming VIP

PHP learning and MYSQL advanced

PHP

Application of php annotation

Initial experience of php program

variable

constant

data type

array

operator

control structure

Cyclic structure

php mysql database operation

mysql events

Concept of event

Advantages and disadvantages

View events

Create event

View events

Modify event

Delete event

Stored procedure and stored function

stored procedure

Popular Keywords