Six things that MySQL should learn

Six things that MySQL should learn

Because of its small size and efficient operations, MySQL is increasingly used in database applications. As an important part of LAMP or WAMP development, MySQL deserves the attention and earnest learning of PHP developers.

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

For MySQL, you must keep in mind that each line of its command is ended with a semicolon (;), but when a line of MySQL is inserted in PHP code, it is best to omit the semicolon, for example:
Mysql_query ("insert into tablename (first_name, last_name) VALUES ('$ first_name', $ last_name ')");
This is because PHP also ends with a semicolon as a line. The extra semicolon may sometimes make PHP syntax analyzer confused, so it is better to omit it. In this case, although the semicolon is omitted, PHP will automatically add it when executing the MySQL command.

2. Access query results using Correlated Arrays

See 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 mysql_fetch_array () function puts a row of the query result into an array and can be referenced in two ways 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 join queries, if the names of the two columns 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;
  
  

  
  
Column Name Reference: $ row ["wname"] and $ row ["rname"]
 
 

When the table name and column name are specified, only the column name is referenced:

 
 

  
  
SELECT winery. region_id
  
  
FROM winery
  
  
Column Name Reference: $ row ["region_id"]
 
 

The reference of the aggregate function is the reference Name:

 
 

  
  
SELECT count (*)
  
  
FROM customer;
  
  
Column Name Reference: $ row ["count (*)"]
 
 

3. TEXT, DATE, and SET Data Types

A field in the MySQL Data Table must define a data type. There are about 25 options, most of which are straightforward, so there is not much to worry about. But there are a few things to mention.

TEXT is not a data type, although it may be said in some books. It should actually be "long varchar" or "MEDIUMTEXT ".

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

 
 

  
  
$age = ($current_date - $birthdate);  
 
 

SET is a useful data type. It is similar to ENUM, except that SET can save multiple values while ENUM can only save one value. In addition, the SET type can have up to 64 predefined values, while the ENUM type can process up to 65,535 predefined values. What should we do if we need a set with more than 64 values? At this time, we need to define multiple sets to solve this problem together.

4. Use mysql_unbuffered_query () to develop a quick script

This function can be used to replace the mysql_query () function. The main difference is that mysql_unbuffered_query () returns immediately after the query is executed, without waiting or locking the database. However, the number of returned rows cannot be checked using the mysql_num_rows () function, because the size of the output result set is small and unknown.

5. wildcard characters

There are two types of SQL wildcards: "*" and "% ". In different cases. For example, if you want to view all the database content, you can query it like this:

 
 

  
  
SELECT * FROM dbname WHERE USER_ID LIKE '%';   
 
 

Here, both Wildcards are used. They mean the same ?? They are used to match any string, but they are used in different contexts. "*" Is used to match the field name, while "%" is used to match the field value. The wildcard "%" must be used with the LIKE keyword. Another wildcard is the underscore "_", which represents a different meaning from the above and is used to match any single character.

6. not null and empty records

What if the user presses the submit button without entering anything? If you really need a value, you can use the client script or server script for data verification. However, in the database, some fields are allowed to be empty and nothing is left blank. For such records, MySQL will execute some tasks for them: Insert a NULL value, that is, the default operation.
If you declare that not null is created or modified in the field definition), MySQL will leave this field blank and leave nothing blank. For an ENUM enumeration field, MySQL inserts the first value of the enumeration set into the field if it is not null. That is to say, MySQL regards the first value of the enumeration set as the default value of this enumeration type.

There are some differences between a NULL record and an empty record. The % wildcard can match a NULL record, but cannot match a NULL record. In some cases, this difference may cause unexpected consequences. In my experience, any field should be declared as not null. In this way, many SELECT query statements can run normally. Note that when searching for NULL, the keyword "IS" must be used, and LIKE does not work normally. In the end, if you already have some records in the database before you add or modify a new field, the value of the newly added field is in the original record, it may be NULL or NULL. This is also a MySQL Bug, so in this case, be especially careful when using SELECT queries.