Common SQL statements for MySQL database operations

Mysql database operations include data query, data storage, data update, and data deletion. These are the most basic and commonly used statements in the database. I will introduce them one by one, hope to bring me good luck.

SELECT query statement and Condition Statement
INSERT statement
UPDATE statement)
DELETE statement

Writing standard: All SQL operation statements are written in uppercase letters, and all others are in lowercase.

Exercise Database: newdb
Exercise table (table name: test)
Field:
Id uid regdate remark
1 Michael NOW () Student
2 Li Si student
3 Wang Wu workers
4 Zhao Liu

1. query statements and condition statements
Note: "'" in the SQL statement! = ''.
Format: SELECT 'query field 'from' table name 'where' condition'
Query field: You can use the wildcard "*", field name, and field alias.
Table Name: database. Table Name and table name.
Common conditions: = equals, <> NOT equal to, IN contains, not in does NOT include, LIKE match, BETWEEN is IN the range, not between is not in the range, <,>
Conditional operations: AND, OR ,()

Instance (Representative)
Normal query: SELECT * FROM 'test' WHERE 1 (query all, WHERE can be omitted)
Conditional query: SELECT * FROM 'test' WHERE 'id' = 2 (id = 2 is the condition, you can replace the condition with other conditions)
SELECT * FROM 'test' WHERE 'id' IN (, 4) (use the Condition Statement IN to output data that contains IDs 1, 2, 3, not in, and vice versa)
SELECT * FROM 'test' WHERE 'uid' LIKE "% %" (The uid field must be varchar type, so the condition must be "" % % ", fuzzy search can be performed for matching queries ")
SELECT * FROM 'test' WHERE 'id' BETWEEN 1 and 3 (equivalent to output data with id = 1-3, a range, not between otherwise)
SELECT * FROM 'test' WHERE 'id' = 2 and 'remark' = "student" (two conditions must be met before output)

 

2. sorting, grouping, pointer query, and computing
Grouping statement: group by field
Sort statement: order by field, field ASC/DESC
Pointer query: initial LIMIT value and end value
Instance (Representative)
Query by group: SELECT * FROM 'test' group by 'remark' (GROUP all non-duplicated information in remark)
Sort query: SELECT * FROM 'test' order by 'regdate' ASC [, id)
Pointer query: SELECT * FROM 'test' LIMIT, 3 (how many pieces of information are customized, subscript is 0, this function is important, useful for paging)
Note: The order of the three functions is as follows. (Priority)

Computing:
COUNT (*) statistical function select count (*) FROM 'test' WHERE 1
MAX (*) maximum function select max ('id') FROM 'test' WHERE 1
MIN (*) MIN value function select min ('regdate') FROM 'test' WHERE 1
AVG (*) Average Function
SUM (*) cumulative value function

 

3. INSERT statement
Format: insert into 'table name' (field...,...) values (value ...,...)
Example: insert into 'test' ('id', 'uid', 'regdate', 'remark') VALUES (NULL, 'small square ', NOW (), 'studen ') (id field auto-increment, insert data should be written as NULL)

 

4. UPDATE Statement (modify Statement)
Format: UPDATE 'table name 'set' field '= value WHERE condition LIMIT
Instance: UPDATE 'test' SET 'uid' = "" WHERE 'id' = 4

 

5. DELETE Statement (use with caution)
Format: delete from 'table name' WHERE Condition
Instance: delete from 'test' WHERE 'id' = 3

--------------------------------------------------------

Basic Applications of PHP + MySQL

1. Establish a connection between PHP and MySQL
PHP connection to MySQL Functions
Mysql_connect: Enable MySQL connection
Mysql_select_db: open a database
@ And or die hide error and condition display

Format: mysql_connect ("host", "User Name", "password ");
Mysql_select_db ("Open Database", connection identifier );

If the connection identifier is not specifically declared, the last opened connection is used by default.

Connect PHP to a MySQL database instance:
A.

The Code is as follows:	Copy code
<? Php $ Conn = @ mysql_connect ("localhost", "root", "root "); If ($ conn) Echo "successful "; ?> B. <? Php $ Conn = @ mysql_connect ("localhost", "root", "root") or die ("connection error "); ?>

Open MySQL database instance in PHP

The Code is as follows:	Copy code
<? Php $ Conn = @ mysql_connect ("localhost", "root", "root") or die ("connection error "); Mysql_select_db ("newdb", $ conn ); ?>

2. How to execute an SQL statement
Format: mysql_query (SQL statement, connection identifier );
$ SQL = "SELECT * FROM 'test '";
$ Result = @ mysql_query ($ SQL, $ conn) or die (mysql_error ());
Instance (insertion is successful after the test, but Chinese characters cannot be inserted. It should be a code problem ):

The Code is as follows:	Copy code
<? Php $ Conn = @ mysql_connect ("localhost", "root", "root") or die ("connection error "); Mysql_select_db ("newdb", $ conn ); $ SQL = "INSERT INTO test (id, name, regdate) values (NULL, 'hahaha', now ())"; Mysql_query ($ SQL, $ conn ); ?>

3. Differences between the two query (read) functions array/row
Format: mysql_fetch_row (result );
Instance:

The Code is as follows:	Copy code
<? Php $ Conn = @ mysql_connect ("localhost", "root", "root") or die ("connection error "); Mysql_select_db ("newdb", $ conn ); $ SQL = "SELECT * FROM 'test '"; $ Read = mysql_query ($ SQL, $ conn ); $ Row = mysql_fetch_row ($ read ); Print_r ($ row ); ?>

Format: mysql_fetch_array (result );
Instance:

The Code is as follows:	Copy code
<? Php $ Conn = @ mysql_connect ("localhost", "root", "root") or die ("connection error "); Mysql_select_db ("newdb", $ conn ); $ SQL = "SELECT * FROM 'test '"; $ Read = mysql_query ($ SQL, $ conn ); $ Row = mysql_fetch_array ($ read ); Print_r ($ row ); Echo $ row ['0']; ?>

Output all data cyclically

The Code is as follows:

Copy code

<? Php $ Conn = @ mysql_connect ("localhost", "root", "root") or die ("connection error "); Mysql_select_db ("newdb", $ conn ); $ SQL = "SELECT * FROM 'test '"; $ Rr = mysql_query ($ SQL, $ conn ); While ($ row = @ mysql_fetch_array ($ rr )){ Echo $ row ['id']. "<br>"; // use "'' "to include fields in the output. Remember, whether it is $ _ POST [] or get, session, array, or something, [] must contain'' } Mysql_close (); ?>

4. Introduction to other common MySQL Functions
Mysql_num_rows is used to calculate the number of rows in the query results.
Instance:

The Code is as follows:

Copy code

<? Php $ Conn = @ mysql_connect ("localhost", "root", "root") or die ("connection error "); Mysql_select_db ("newdb", $ conn ); $ SQL = "SELECT * FROM 'test '"; $ Rr = mysql_query ($ SQL, $ conn ); While ($ row = @ mysql_fetch_array ($ rr )){ // Echo $ row ['id']. "<br>"; // use "'' "to include fields in the output. Remember, whether it is $ _ POST [] or get, session, array, or something, [] must contain'' } Echo mysql_num_rows ($ rr ); Mysql_close (); ?>

Mysql_insert_id returns the ID of the last INSERT command.
Mysql_tablename get the Database Name
Error message returned by mysql_error
Mysql_close close the MySQL Link

The above Code has no conditions. We can create a database operation class.

A PHP + MySQL class

1. Consolidate and learn common MySQL functions.
2. Use classes to encapsulate common SQL operations.
3. Understand the important role of object-oriented in development.

----------------------------------------------

Knowledge used:
1. private Attribute keywords.
2. _ construct () initialization method.
3. Common MySQL functions.
4. Common SQL knowledge.

Instance:

The Code is as follows:

Copy code

<? Php /* File name: class_mysql_operating.php * File Description: operation class for PHP to MySQL database * Last modification time: 18: 13 * Last modification: New File * Modifier: if0else1 */ Class MySQL_Operating { Private $ mysql_url; // attributes of the MySQL database address Member. Private $ mysql_id; // MySQL Database ID member attribute. Private $ mysql_password; // attributes of the MySQL Database Password member. Private $ database; // attributes of the MySQL database name Member. Private $ ut; // character encoding. // Initialize the data method Function _ construct ($ mysql_url, $ mysql_id, $ mysql_password, $ database, $ ut ){ $ This-> mysql_url = $ mysql_url; $ This-> mysql_id = $ mysql_id; $ This-> mysql_password = $ mysql_password; $ This-> database = $ database; $ This-> ut = $ ut; $ This-> conn (); } // Method for enabling the database connection table Function conn (){ $ Database_tab = mysql_connect ($ this-> mysql_url, $ this-> mysql_id, $ this-> mysql_password) or die (mysql_error ()); Mysql_select_db ($ this-> database, $ database_tab) or die ("database connection error! <Br/> name of the currently connected database: <B> ". $ this-> database." </B> "); Mysql_query ("set names '$ this-> ut '"); } // SQL statement execution Method Function run_ SQL ($ SQL ){ Return mysql_query ($ SQL ); } /* The following is the function area */ // SQL data insertion method Function fn_insert ($ table, $ field, $ value ){ $ This-> run_ SQL ("INSERT INTO". $ table. "(". $ field. ") VALUES (". $ value .")"); } } $ Mysql_operathing = new MySQL_Operating ('localhost', 'root', 'root', 'j001', "GBK "); $ Mysql_operathing-> fn_insert ("'gb'", "'id', 'user', 'title', 'content', 'update'", "NULL, 'insert', 'insert', 'dele', NOW ()"); ?>