Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the six things MySQL should learn?" in the operation of actual cases, many people will encounter such a dilemma, and then let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

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

For MySQL, you must keep in mind that each line of its command ends with a semicolon (;), but when a line of MySQL is inserted into the PHP code, the semicolon is omitted, 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, and the extra semicolon is sometimes confusing to PHP's parser, so it's better to omit it. In this case, although the semicolon is omitted, PHP will automatically add it for you when you execute the MySQL command.

two。 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 query results into an array and can be referenced in two ways at the same time. For example, cust_id can be referenced in both ways: $row ["cust_id"] or $row [0]. Obviously, the former is much more readable than the latter.

In multiple tables, if the two column names are the same, * will be separated by aliases:

References to SELECT winery.name AS wname, region.name AS rname, FROM winery, region WHERE winery.region_id = region.region_id; column names are: $row ["wname"] and $row ["rname"]

When the table and column names are specified, only the column names are referenced:

The reference to the SELECT winery.region_id FROM winery column name is: $row ["region_id"]

The reference to the aggregate function is the reference name:

The reference to the SELECT count (*) FROM customer; column name is: $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, so don't waste your breath. But there are a few that need to be mentioned.

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: 1999-12-08. You can easily use the date function to get the current system time in this format: date ("Y-m-d") and you can subtract between DATA data types to get the difference in time days:

$age = ($current_date-$birthdate)

The collection SET is a useful data type, which is somewhat similar to enumerating ENUM, except that SET can hold multiple values while ENUM can hold only one value. Furthermore, the SET type can only have up to 64 predetermined values, while the ENUM type can handle up to 65535 predefined values. What if you need to have sets with more than 64 values? you need to define multiple sets to solve the problem together.

4. Develop fast scripts with mysql_unbuffered_query ()

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 locking the database. However, 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 character

There are two wildcard characters for SQL: "*" and "%". They are used in different situations. For example, if you want to see all the contents of the database, you can query it like this:

SELECT * FROM dbname WHERE USER_ID LIKE'%'

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

6. NOT NULL and empty record

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

If you declare NOT NULL in the field definition (when you create or modify the field), MySQL will leave the field empty and fill in nothing. For a field of ENUM enumerated type, if you declare NOT NULL for it, MySQL will insert * values of the enumerated set into the field. That is, MySQL takes the * values of the enumerated set as the default values for this enumerated type.

There are some differences between a record with a value of NULL and an empty record. Wildcards can match empty records, but not NULL records. At some point, this distinction can have unintended consequences. In my experience, any field should be declared NOT NULL. In this way, many SELECT query statements can work properly. Note that when searching for NULL, the keyword "IS" must be used, and LIKE will not work properly. To mention in *, if you already have some records in the database before you add or modify a new field, the value of the newly added field in the original record may be NULL or empty. This is also a Bug of MySQL, so in this case, use SELECT queries with special care.

This is the end of the content of "what are the six things MySQL should learn". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.