6 tips you should master when learning MySQL

MySQL is increasingly used in database applications due to its compactness and efficient operation. As an important part of LAMP (or WAMP) development, MySQL deserves the attention and careful study of PHP developers.

#1. Each line of command ends with a semicolon (;)

For MySQL, the first thing you must remember is Each line of its commands ends with a semicolon (;), but when a line of MySQL is inserted into the PHP code, it is best to omit the following semicolon, for example:

mysql_query ("INSERT INTO tablename (first_name, last_name) VALUES ('$first_name', $last_name')");

This is Because PHP also ends a line with a semicolon, extra semicolons sometimes confuse the PHP parser, so it's better to omit them. In this case, although the semicolon is omitted, PHP will automatically add it for you when executing the MySQL command.

2. Use associative array to access query results

Look at the following example:

$connection = mysql_connect("localhost", "albert", "shhh");  
mysql_select_db("winestore", $connection);  
$result = mysql_query("SELECT cust_id, surname,  
firstname FROM customer", $connection);  

while ($row = mysql_fetch_array($result))  
{  
echo "ID:t{$row["cust_id"]}n";  
echo "Surnamet{$row["surname"]}n";  
echo "First name:t{$row["firstname"]}nn";  
}

The function mysql_fetch_array() puts a row of the query result into the array. You can use two at the same time. For example, cust_id can be referenced in the following two ways at the same time: $row["cust_id"] or $row[0]. Obviously, the former is much more readable than the latter.

In multi-table continuous query, if two column names are the same, it is best to separate them with aliases:

SELECT winery.name AS wname, region.name AS rname, FROM winery, region WHERE winery.region_id = region.region_id;  

列名的引用为：$row["wname"] 和 $row["rname"]

In the case of specifying the table name and column name Below, only quote the column name:

SELECT winery.region_id   
FROM winery   
列名的引用为： $row["region_id"]

The reference of the aggregate function is the reference name:

SELECT count(*) 
FROM customer; 
列名的引用为： $row["count(*)"]

3. TEXT, DATE, and SET data types

The fields of the MySQL data table must have a data type defined. There are about 25 options, most of which are straightforward and require no further explanation. But there are a few that need to be mentioned.

TEXT is not a data type, although some books may say so. It should actually be "LONG VARCHAR" or "MEDIUMTEXT".

The format of DATE data type is YYYY-MM-DD, for example: 1999-12-08. You can easily use the date function to get the current system time in this format: date("Y-m-d") Moreover, you can subtract between DATA data types to get the difference in time and days:

$age = ($current_date - $birthdate);

Collection SET is a useful data type that is somewhat similar to the enumeration ENUM, except that SET can save multiple values ​​and ENUM can only save one value. Moreover, the SET type can only have a maximum of 64 predefined values, while the ENUM type can handle a maximum of 65,535 predefined values. What should we do if we need a collection with more than 64 values? At this time, we need to define multiple collections to solve this problem together.

4. Use mysql_unbuffered_query() to develop fast scripts

This function can be used to replace the mysql_query() function. The main difference is that mysql_unbuffered_query() returns immediately after executing the query, without waiting. Or lock the database. But the number of rows returned cannot be checked with the mysql_num_rows() function because the size of the output result set is unknown.

5. Wildcard characters

There are two types of wildcard characters in SQL: “*” and “%”. Used in different situations. For example: If you want to see all the contents of the database, you can query like this:

SELECT * FROM dbname WHERE USER_ID LIKE '%';

Here, both wildcard characters are used. They mean the same thing - they both match any string - but they are used in different contexts. " * " is used to match field names, and " % " is used to match field values. Another thing that is not easy to notice is that the "%" wildcard character needs to be used together with the LIKE keyword. There is also a wildcard character, which is the underscore "_", which has a different meaning from the above and is used to match any single character.

6. NOT NULL and empty records

What happens if the user presses the submit button without filling in anything? If you really need a value, you can use client-side scripting or server-side scripting for data validation. However, in the database, some fields are allowed to be left blank and filled with nothing. For such a record, MySQL will do something for it: insert the value NULL , which is the default operation.

If you declare NOT NULL for it in the field definition (when creating or modifying this field), MySQL will leave this field empty and fill it with nothing. For a field of ENUM enumeration type, if you declare NOT NULL for it, MySQL will insert the first value of the enumeration set into the field. In other words, MySQL uses the first value in the enumeration set as the default value for this enumeration type.

There are some differences between a record with a value of NULL and an empty record. The % wildcard character can match empty records, but it cannot match NULL records. At some point, this distinction can have unintended consequences. As far as my experience goes, any field should be declared NOT NULL . In this way, many SELECT query statements can run normally. Note that when searching for NULL, you must use the "IS" keyword, and LIKE will not work properly. The last thing to mention is that if you already have some records in the database before adding or modifying a new field, the value of the newly added field in the original record may be NULL or NULL. null. This can be regarded as a bug of MySQL, so in this case, you must be particularly careful when using SELECT queries.