[MySQL] Common SQL Tips--18.5

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.