preface

It is recommended to read this article after learning PHP Basics (click here to learn) and MySQL Basics (click here to learn).

1. Data backup and restore

Data in the database needs to be backed up periodically

1.1 Data Backup

Use mysqldump tool, syntax: mysqldump database connection > data backup address

Studentinfo = studentInfo = studentinfo = studentInfo = studentInfo

Example: you only want to export the bank table:We drag data.sql into vscode to see:

1.2 Data Restoration

Export data with syntax for creating a database

Create a new database and import the backup data:

Method 2:

2. Connect to the database

2.1 Enabling the mysqli extension

Open mysqli extension==php_mysqli.dll in php.ini and restart the server to use mysqli_.

2.2 Connecting to the Database

Create a data database:Then write the SQL statement under QueiresCreate table news and insert data:

drop table if exists news;

create table news (
  id int UNSIGNED auto_increment PRIMARY KEY comment 'primary key',
  title VARCHAR(20) not null comment 'title',
  content text not null comment 'content',
  createtime int not null comment 'Add time'
) engine=innodb charset=gbk comment 'News Sheet';

-- Insert test data
insert into news values (null.'counter'.'PHP, mysql, vue, react', UNIX_TIMESTAMP());
insert into news values (null.'jack'.'Mysql, vue, react', UNIX_TIMESTAMP());
Copy the code

3. Connect to the database

Mysqli_connect (host IP, username, password, database name, port number)//If the port number is3306You can omit mysqli_connect_error()//Mysqli_connect_errno () mysqli_connect_errno//Mysqli_set_charset ()//Connection object, character encodingCopy the code

Test: mysqli_connect(host IP, username, password, database name, port number)

Mysqli_connect_error () // Get error information about connecting to the database

Mysqli_connect_errno () // Get error code for connecting to database

Mysqli_set_charset (connection object, character encoding) // Connection object, character encoding

<? php $link= @mysqli_connect('localhost'.'root'.'123456'.'data'.'3306');
    // var_dump($link);
    // echo mysqli_connect_error();
    if (mysqli_connect_error()) {
        echo 'Error number'.mysqli_connect_errno(),'<br/>';
        echo Error message:.mysqli_connect_error();
        exit;
    }
    mysqli_set_charset($link, 'gbk');
?>
Copy the code

3. Operational data

3.1 Data Operation statements

Functions used:

mysqli_query()   //performSQLStatements mysqli_insert_id ()//Mysqli_affected_rows ();//Mysqli_error ()//Access to performSQLError message for statement mysqli_errono()//Access to performSQLStatement error codeCopy the code

True is returned on success, false on failure. Select, show, and desc will be returned if the query is successful.

Example: 1, execute insert statement:

<? php $link= @mysqli_connect('localhost'.'root'.'123456'.'data'.'3306');
    mysqli_set_charset($link, 'gbk');
    
    $rs = mysqli_query($link, "insert into news values (null, 'jackson', 'dance,voince', unix_timestamp())");
    var_dump($rs);
?>
Copy the code

Get the auto-growing number on success:

<? php $link= @mysqli_connect('localhost'.'root'.'123456'.'data'.'3306');
    mysqli_set_charset($link, 'gbk');
    
    $rs = mysqli_query($link, "insert into news values (null, 'jackson', 'dance,voince', unix_timestamp())");
    // var_dump($rs);
    if ($rs) {
        echo 'The auto-growing number is:'.mysqli_insert_id($link); }?>
Copy the code

2. Execute the update statement and the existing data is as follows:Statement:

<? php $link= @mysqli_connect('localhost'.'root'.'123456'.'data'.'3306');
    mysqli_set_charset($link, 'gbk');
    
    $rs = mysqli_query($link, "update news set title='kangkang' where id=4");
    if ($rs) {
        echo The number of records affected is:.mysqli_affected_rows($link);
    }
    else {
        echo Error code:.mysqli_errno($link),'<br/>';
        echo Error message:.mysqli_error($link); }?>
Copy the code

In the case of error, change the id to IDS and you will not have this field:Success:

3. Run the deleta statement

<? php $link= @mysqli_connect('localhost'.'root'.'123456'.'data'.'3306');
    mysqli_set_charset($link, 'gbk');
    
    $rs = mysqli_query($link, "delete from news where id=5");
    echo $rs;
?>
Copy the code

3.2 Data Query Statements

Select, desc, and show for data query. Results are returned on success, and false on failure

Example: 1. Select query statement

<? php $link= @mysqli_connect('localhost'.'root'.'123456'.'data'.'3306') or die('Connection error message:'.mysqli_connect_error());
    mysqli_set_charset($link, 'utf8');
    
    $rs=mysqli_query($link,'select * from news');

    //Matches a record to an indexed array $rows=mysqli_fetch_row($rs);
    
    //Matches a piece of data from an object to the associative array $gl=mysqli_fetch_assoc($rs);

    //Matches a piece of data in an object to an associative array and an indexed array $GLsy=mysqli_fetch_array($rs);
    

    print_r($rows);
    echo '<br/>';
    print_r($gl);
    echo '<br/>';
    print_r($glsy);
    echo '<br/>';

    //Total number of columns, total number of rows echo'Total number of rows'.mysqli_num_rows($rs), '<br/>';
    echo 'Total number of columns'.mysqli_num_fields($rs), '<br/>';
?>
Copy the code

Get all data:

<? php $link= @mysqli_connect('localhost'.'root'.'123456'.'data'.'3306') or die('Connection error message:'.mysqli_connect_error());
    mysqli_set_charset($link, 'utf8');
    
    $rs=mysqli_query($link,'select * from news');

    //Get all data $list=mysqli_fetch_all($rs);
    echo 'Total:';
    echo '<pre/>';
    print_r($list);
?>
Copy the code

Destroy the result set and close the connection:

mysqli_free_result($rs);
mysqli_close($link);
Copy the code

Functions used:

mysqli_fetch_assoc()  //Match an array to an associative array mysqli_fetch_row()//Mysqli_fetch_array (); mysqli_fetch_array();//Mysqli_fetch_all () match a record to an associative array and an indexed array//Mysqli_num_rows ()//Total number of rows mysqli_num_fields ()//Total number of records mysqli_free_result()//Destroy result set mysqli_close()//Close the connectionCopy the code

On the way to learning PHP, if you find this article helpful to you, then please pay attention to like comment 3 times, thank you, your must be another support of my blog.