[MySQL] Common SQL Tips--18.5

1. Regular Expressions use

MySQL uses the regexp command to provide regular expression functionality.

Example: Select ' abcdef ' REGEXP ' ^a ';

Select ' EFG ' REGEXP ' [^xyz] ';

2. Extracting random rows with the rand () function

Use the rand () function to randomly extract record rows from a table, which is useful for some sample analysis statistics.

For example: SELECT * from category ORDER by rand () limit 5;

3. Using GROUP BY's with ROLLUP clause to make statistical reports

In the SQL statement, the WITH ROLLUP clause of GROUP by can be used to retrieve more packet clustering and information, and to make the report easier.

4. Database name, table name case-sensitive issues

In MySQL, the database corresponds to the data directory under the operating system. Each table in the database corresponds to at least one file in the database directory (or possibly multiple, depending on

Storage engine). Therefore, the operating system case sensitivity determines the sensitivity of the database name and table name to the case (triggers and table aliases are also sensitive).

Most Unix operating systems are case-sensitive, but in Windows, case insensitive and case insensitive.

columns, indexes, stored procedures (not including triggers) are insensitive to capitalization on any platform.

It is recommended to write the library name, table name, trigger name, alias as lowercase, and set lower_case_tables_name=1 in the parameters;

Use on all lower_case_table_names=1 systems. The main disadvantage with this is if you use or, if you don't see SHOW TABLES SHOW DATABASES the names in their original lettercase .

　　

[MySQL] Common SQL Tips--18.5