1. semicolon exceptions
In MySQL, each line of command ends with a semicolon (;). However, when a line of MySQL Command 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 lead to incorrect analysis by the PHP syntax analyzer, so it is better to omit it. In this case, although the semicolon is omitted, PHP automatically adds the semicolon when executing the MySQL command.
There is also a case where no extra points are required. When you want to display the desired fields in a vertical arrangement, instead of arranging them horizontally as usual, you can use G to end a line of SQL statements. Then, you cannot use a semicolon, for example:
SELECT * from penpals where USER_ID = 1G
2. TEXT, DATE, and SET Data Types
TEXT is not a data type. It should be "long varchar" or "MEDIUMTEXT ".
The format of the DATE data type is YYYY-MM-DD, for example: 2001-10-01. You can easily use the date function to obtain the current system time in this format:
Date ("Y-m-d ")
In addition, subtraction can be performed between DATA types to obtain the time difference days:
$ 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 if we need a set with more than 64 values? In this case, you need to define multiple sets to solve this problem together.
3. 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.
4. not null and NULL records
What if the user presses the submit button without entering anything? If you really need a value, you can use a client script or a server script to perform data verification. However, in the database, some fields are allowed to be empty and nothing is left blank. For such records, MySQL will perform the following steps:
The insert value is NULL, which is the default operation.
If you declare not null in the field definition (when this field is created or modified), 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 terms of experience, any field should be declared as not null. In this way, the following SELECT query statement can run properly: if (! $ CITY) {$ CITY = "% ";}
$ Selectresult = mysql_query ("SELECT * FROM dbname
WHERE FIRST_NAME = Bill
AND LAST_NAME = Gates
And city like $ CITY
");
In the first line, if the user does not specify a CITY value, then the wildcard % will be used to substitute the CITY variable, so that any CITY value will be taken into account during the search, it even includes records with empty CITY fields.
However, if there are some records and Its CITY field value is NULL, then the problem arises. The preceding query cannot find these fields. A solution to the problem can be as follows: if (! $ CITY) {$ CITY = "% ";}
$ Selectresult = mysql_query ("SELECT * FROM dbname
WHERE FIRST_NAME = Bill
AND LAST_NAME = Gates
AND (city like $ city or city is null)
");
Note that the keyword "IS" must be used when searching for NULL, but 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. In this case, be especially careful when using SELECT queries.
