MYSQL database in PHP and mysql database

MYSQL database in PHP and mysql database
MYSQL database Introduction 1. What is a database? A database is an ordered set composed of a batch of data. This set is usually saved as one or more files related to each other. 2. What is a relational database? Data is stored in some structured data tables in different categories, and data tables often form various internal crosstab relationships. This relationship exists between data tables, so that databases are also called relational databases. 3. relational database Systems MySQL, Oracle, Microsoft SQL Server, and IBM DB2 are relational database systems ). In addition to data management, such a system also includes programs used to manage various relational databases. A qualified relational database system not only ensures the security and reliability of the storage of various types of data, but also must be able to query, analyze and sort existing data and store new data. 4. Non-relational databases: mongoDB 5. Data Tables, records, fields, queries, SQL statements, and index tables are framework structures used to actually store relevant data. Each row in a data table is called a data record ("record" for short). The structure and format of each record are determined by the definition of the data table. For example, in a user table, each record may contain multiple fields, such as the user's name, birth date, and registration time ). Each field has certain requirements on the types of information that you can store (for example, it must be a number in a specific format or a string with a maximum number of characters ). Query is constructed using various SQL commands. SQL commands are used to filter and extract result data. Structured Query Lanuage is a Structured Query language; this language has developed into a standard for people to construct database query commands. Structured Query Language (SQL) is a structured Query Language used to construct various database system operation commands, such as SELECT, INSERT, UPDATE, and DELETE. SQL commands can be classified into the following three categories: DML (Data Manipulation Language Data processing Language): This type of command mainly includes SELECT, INSERT, UPDATE, DELETE, and so on, used to read Data from the Data table, command for saving Data to a Data table or modifying existing records in the Data table; (add, delete, modify, and query) DDL (Data Definition Language ): these commands mainly include create table, alter table, and other commands used to define and change the database structure. DCL (Data Control Language ): these commands mainly include GRANT, REVOKE, and several other SQL commands used to help people set and adjust the MySQL access control mechanism; query (SELECT) 1. simple query SELECT * FROM tablename query all
2. limit the number of data columns in the query results to query the two fixed columns SELECT column1, column2 FROM tablename 3. determine the number of data records in the data table. query records: select count (id) FROM tablename WHERE clause sets query conditions to filter out unwanted data rows. WHERE represents the restriction condition, for example, querying records older than 20: SELECT * FROM usertable WHERE age> 20 WHERE clauses can include various conditional operators: 1. Comparison Operators

Operator	Description
=	Equal
>	Greater
> =	Greater than or equal
<	Less
<=	Less than or equal
<>	Not equal

2. logical operators

Operator	Description
AND	Returns TRUE if both conditions are TRUE.
OR	Returns TRUE if either of the conditions of the combination is TRUE.
NOT	Returns TRUE if the condition is FALSE.

3. The LIMIT query LIMIT clause is used to force the SELECT statement to return the specified number of records. LIMIT accepts one or two numeric parameters. The parameter must be an integer constant. If two parameters are specified, the first parameter specifies the offset of the first returned record row, and the second parameter specifies the maximum number of returned record rows. Note: the offset of the initial record row is 0 rather than 1.
For example, SELECT * FROM table LIMIT 5 is retrieved FROM the first five record rows, and 6-15SELECT * FROM table LIMIT 5 and 10 are retrieved FROM the first five record rows. 4. query structure sorting uses the order by clause to sort the results returned BY the query. Syntax of the order by clause: order by {column_name [ASC | DESC]} [,... n] ASC indicates Ascending Order, which is the default value. DESC indicates addition, deletion, and modification of 1 in descending order. INSERT example: insert into user (username, password) VALUES ('admin', '000000') INSERT multiple records at a time insert into user (username, password) VALUES ('user1', '123'), ('user2', '123'), note: Use commas to separate 2. UPDATE example: UPDATE user SET username = 'admin1', passwd = '000000' WHERE uid = 10 3. DELETE example: delete from user WHERE uid = 10 since the beginning of the Database Operation Programming Interface PHP5, PHP has provided two MySQL application programming interfaces to programmers: one is the mysql function module that has been available since the early versions of PHP; the other is the mysqli interface that started from PHP5; the mysql function module is not an integrated component of PHP. To use this function extension module, you must add the "-with-mysql" option to the PHP Linux version during compilation. The windows version of PHP provides corresponding extensions through a DLL file, no matter which operating system is used, it must be in php. enable this extension in the INI file to ensure that PHP can find all the necessary DLL. Procedure

 

The connection to the mySQL database through the mysql function module is to call the mysql_connect () function, which requires three pieces of information: the MySQL server host name, MySQL user name, and password. If the MySQL server runs on the same computer as PHP, you can use localhost as its host name. Example: $ conn = mysql_connect ("localhost", "root", "123456"); $ conn is the handle mysql_connect (server, user, pwd, newlink, clientflag)

Parameters	Description
Server	Optional. Specifies the server to be connected. It can include the port number, for example, "hostname: port", or the path to the local socket, for example, ":/path/to/socket" for localhost ". If the PHP Command mysql. default_host is not defined (the default value is true), the default value is 'localhost: 3306 '.
User	Optional. User name. The default value is the user name of the server process owner.
Pwd	Optional. Password. The default value is null.
Newlink	Optional. If the same parameter is used to call mysql_connect () for the second time, a new connection is not established, but an opened connection ID is returned. The new_link parameter changes this behavior and makes mysql_connect () always open a new connection, even when mysql_connect () was previously called with the same parameter.
Clientflag	Optional. The client_flags parameter can be a combination of the following constants: MYSQL_CLIENT_SSL-SSL encryption MYSQL_CLIENT_COMPRESS-use the compression Protocol MYSQL_CLIENT_IGNORE_SPACE-interval after the function name is allowed MYSQL_CLIENT_INTERACTIVE-allow interaction timeout and inactivity before closing the connection

If the connection is successful, this function returns an ID number. Otherwise, FALSE is returned and an error message is sent to the Web server. This will also cause an error message in the HTML document of the result generated by the PHP script. If you do not want end users to see such an error message in their web browser, you must add a @ character before calling the mysql_connect () function. Close the connection after the mySQL server is queried. However, closing the connection is not necessary because PHP's garbage collection mechanism will handle this problem. The mysql_close () function closes the connection corresponding to the optional parameter link_id. If link_id is not specified, it is considered as the last opened connection. For example, mysql_close (); after you select to establish a connection between the database and the MySQL server, you can use various mysql_xxx () functions to execute SQL commands. However, to avoid specifying the target database for every call to the mysql_xxx () function, it is best to USE the mysql_select_db () function (which is equivalent to the SQL command USE databasename) to select a default database for subsequent operations. For example, mysql_select_db ("mycompany"); db (datebase) executes SQL statements. To execute SQL commands, you need to pass them as a string to the mysql_query () function. if you do not want to access the current database, you need to call the mysql_db_query () function to add the SQL command and give the database name clearly. The last parameter of the two functions (the connection ID number, that is, the return value of mysql_connect () is optional. this parameter is required only when multiple connections are established with the MySQL server.
For example, $ result = @ mysql_query ("SELECT * FROM user"); // This function is not recommended since PHP 4.0.6. Do not use this function $ result = @ mysql_db_query ("mycompany", "SELECT * FROM product"); If the SQL command is successfully executed, mysql_query () the function returns the PHP Resource reference pointer (a string in the format of Resource id #2); otherwise, FALSE is returned and an error message is generated; mysql_query () functions can be used to execute any SQL command, such as SELECT (query), INSERT (INSERT new record), UPDATE (modify existing record), DELETE (DELETE existing record) create table, alter table, and so on. Obtain the displayed data 1. the mysql_fetch_row () function to obtain the row mysql_fetch_row () returns a result in the form of a common array. Its fields need to be accessed in the form of $ row [n. 2. the mysql_fetch_array () function returns a result in the form of an associated array, its fields must be accessed in the form of $ row [n] or $ row ["colname. 3. the mysql_fetch_assoc () function will also return a result record in the form of an associated array, but its fields can only be accessed in the form of $ row ["colname. 4. The mysql_fetch_object () function returns a result record in the form of an object. Its fields must be accessed in the form of $ row-> colname. These four functions share the same thing: each call will automatically return the next result record, but if it has reached the end of the result table, FALSE is returned. 5. mysql_free_result () PHP will save the query results until the execution of the script ends. If you want to release a query result in advance (for example, a large number of queries have been performed in a script), you can use the mysql_free_result () function to release it in advance. 6. the mysql_num_rows () function is used to obtain the number of records returned by the query. 7. the mysql_insert_id () function is used to obtain the ID generated by the INSERT operation. 8. the mysql_affected_rows () function is used to obtain the number of records affected by the previous MySQL operation. Example of DEMOS data reading: read data // ******* link to mySQL ******* $ conn = @ mysql_connetc ("localhost", "root", "123456 "); // generally, write four parameter host names, root permissions, and passwords, if the database name and password are empty, you can leave it empty. // ******* determine whether the data link is successful. ****** if ($ conn = FALSE) {echo "database connection failed! "; Exit ;}; // ******* select database ******* mysql_select_db (" test "); // ****** Execute SQL ******* $ result = @ mysql_query ("SELECT * FROM user "); // This line of code extracts all the data from the "user" database and stores it with $ result. It can be analogous to js's belief that $ result is an array object.
// ****** Display the query result ****** if (mysql_num_rows ($ result)> 0) {// use mysql_num_rows ($ result) the function also refers to the length of the "array object" retrieved from the database While ($ row = mysql_fetch_array ($ result) {echo $ row ["username"]; echo "<br/>" ;};}; // ******* release result set ******* mysql_free_result ($ result ); // This line of code is similar to setting $ result as an "array object" to null.
// ******* Close the connection ****** mysql_close (); example of inserting an array $ conn = @ mysql_connetc ("localhost", "root ", "123456") or die ("database connection failed! "); Mysql_select_db (" test "); // defines a row of data and inserts it into the database $ username = 'admin'; $ password = '000000'; $ reg_time = time (); $ SQL = "INSERT INTO user (username, password, reg_time) VALUES ('$ username',' $ password', $ reg_time )"; // Insert the result to $ result = mysql_query ($ SQL); If ($ result) {echo "added successfully" ;}; mysql_close (); example of updating data $ conn = @ mysql_connetc ("localhost", "root", "123456") or die ("database connection failed! "); Mysql_select_db (" test "); $ newname = 'root '; // select the data with id = 1 and select the username field to change its value $ SQL = "UPDATE user SET username = '$ newname' WHERE id = 1 "; $ result = mysql_query ($ SQL); if (mysql_affected_rows ()> 0) {echo "edited" ;}mysql_close (); delete data $ conn = @ mysql_connetc ("localhost", "root", "123456") or die ("database connection failed! "); Mysql_select_db (" test "); // select the data with id = 1 and DELETE the data $ SQL =" DELETE FROM user WHERE id = 1 "; $ result = mysql_query ($ SQL); if (mysql_affected_rows ()> 0) {echo "deleted successfully";} mysql_close ();