Common MySQL database operations SQL statements

SELECT query Statements and conditional statements
Insert Insertion Statement
Update UPDATES statement (Modify statement)
Delete Deletion statement

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

Practice Database: newdb
Practice Table (table name test)
Field:
ID UID regdate Remark
1 three now () students
2 Dick Students
3 Harry Workers
4 Zhao Six students

1. Query statements and conditional statements
Note: the "'"!= "" in the SQL statement.
Format: SELECT ' query field ' from ' Table name ' WHERE ' condition '
Query fields: You can use the wildcard character "*", field name, field alias.
Table name: Database. Table name, table name.
Common conditions: = equals, <> not equal to, in contains, not in does not contain, like match, BETWEEN in range, not BETWEEN out of range, <, >
Conditional operations: And, or, ()

Example (representative)
Normal query: SELECT * from ' Test ' where 1 (query all, where can also be omitted)
Conditional query: SELECT * from ' test ' WHERE ' id ' =2 (id=2 as a condition, you can swap the condition for other conditions to use)
SELECT * from ' test ' WHERE ' id ' in (1,2,4) (use conditional statement in, output contains data with ID 1,2,3, not in Vice)
SELECT * from ' test ' WHERE ' uid ' like '% King% ' (the UID field should be using the varchar type, so the condition needs to be "% King%", matching query this can do fuzzy query ")
SELECT * from ' test ' WHERE ' id ' BETWEEN 1 and 3 (equivalent to output id=1-3 data, a range, not BETWEEN vice versa)
SELECT * from ' test ' WHERE ' id ' =2 and ' remark ' = ' student ' (two conditions must be satisfied to output)

2. Sorting, grouping, pointer query, calculation
Grouped statements: Group By field
Sort statement: Order By field, field Asc/desc
Pointer query: LIMIT initial value, ending value
Example (representative)
Grouped query: SELECT * from ' Test ' group by ' remark ' (group output of all the information not duplicated in remark)
Sort query: SELECT * from ' test ' ordered by ' regdate ' ASC [, id] (output by the earliest registration time, DESC vice versa newest registration, at the same time, id to sort)
Pointer query: SELECT * from ' Test ' LIMIT 0,3 (custom take how many messages, subscript 0, this function is important to pagination useful)
Note: Three functions are arranged in the order listed above. (priority)

Calculation:
COUNT (*) statistic function SELECT COUNT (*) from ' Test ' WHERE 1
Max (*) max function SELECT max (' id ') from ' Test ' WHERE 1
MIN (*) Minimum function SELECT min (' regdate ') from ' Test ' WHERE 1
AVG (*) Average function
SUM (*) Cumulative value function

3.INSERT INSERT statement
Format: INSERT into ' Table name ' (field ...,...) VALUES (Value ...,...)
Instance: INSERT INTO ' test ' (' id ', ' uid ', ' regdate ', ' remark ') VALUES (null, ' Little Square ', now (), ' Student ') (ID field is increased, insert data should be written as null)

4.UPDATE UPDATE statement (Modify statement)
Format: UPDATE ' table name ' SET ' field ' = value WHERE condition LIMIT
Instance: UPDATE ' Test ' SET ' uid ' = ' small side ' where ' id ' =4

5.DELETE DELETE statement (use caution)
Format: DELETE from ' table name ' WHERE condition
Instance: DELETE from ' Test ' WHERE ' ID ' =3

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

The basic application of Php+mysql

1.PHP connection with MySQL
PHP connection MySQL function
Mysql_connect: Open MySQL Connection
mysql_select_db: Open a Database
@ and Or die hide errors and condition display

Format: mysql_connect ("host", "username", "password");
mysql_select_db ("Open database", connection identifier);

If you do not specifically declare a connection identifier, the connection that was last opened is the default.

PHP connection MySQL Database instance:
A.

The code is as follows	Copy Code
<?php $conn = @ mysql_connect ("localhost", "root", "root"); if ($conn) echo "Success"; ?> B. <?php $conn = @ mysql_connect ("localhost", "root", "root") or Die ("Connection error"); ?>

PHP Open MySQL Database instance

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 (successfully inserted after test, but cannot be inserted in Chinese, should be a coding 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, ' haha ', now ())"; mysql_query ($sql, $conn); ?>

3. The difference between two kinds of query (read) function 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 ']; ?>

Loop output All data

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>"; Please use "'" to include the field, remember, whether it's $_post[] or get,session, array, whatever, [] } Mysql_close (); ?>

4. Other common MySQL function introduction
Mysql_num_rows 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> "; Please use "'" to include the field, remember, whether it's $_post[] or get,session, array, whatever, [] } echo mysql_num_rows ($RR); Mysql_close (); ?>

MYSQL_INSERT_ID returns the ID of the last time the insert instruction was used.
Mysql_tablename Get the database name
Mysql_error return error message
Mysql_close Close MySQL Link

The above code has no condition, we can be a database operation class

A Php+mysql class

1. Consolidate learning common MySQL function.
2. Use classes to encapsulate common SQL operations.
3. Recognize the important role of object-oriented in development.

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

The knowledge used:
1.private private Property key.
2.__construct () initialization method.
3. Common MySQL function.
4. Common SQL knowledge.

Instance:

The code is as follows

Copy Code

<?php /* FileName: class_mysql_operating.php * File Description: Used in PHP for MySQL database operation class * Last modified: 18:13 2010-5-15 * Last modified content: New file * Modified Person: IF0ELSE1 */ Class Mysql_operating { Private $mysql _url; MySQL database address member properties. Private $mysql _id; MySQL database ID member properties. Private $mysql _password; MySQL Database password member properties. Private $database; MySQL database name member properties. Private $ut; Character encoding. Initializing data methods 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 (); } Open the Database Connection table method 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/> The current connection database name:<b>. $this->database. "</b>"); mysql_query ("SET NAMES ' $this->ut '"); } Executing SQL statement methods function Run_sql ($sql) { return mysql_query ($sql); } /* Below is the function method area * * SQL Insert Data 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 ', ' enter ', ' Go ', now ());