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

This article mainly explains the "common skills of MySQL database". The explanation in this article is simple and clear, easy to learn and understand. Please follow the ideas of Xiaobian and go deep into it slowly to study and learn the "common skills of MySQL database" together.

regular expression

--Does it start with an a?

select 'abcdefg' REGEXP '^a'

--Does it end with fg www.2cto.com

select 'abcdefg' REGEXP 'fg$'

- Wow, Chinese characters work too.

select STAFF_Name,STAFF_NAME REGEXP '^TEST' from g_staff

select STAFF_Name from g_staff where STAFF_NAME REGEXP '^'= 1

-- ". "Match any single character

select 'abcdefg' REGEXP '.h','abcdefg' REGEXP '.g'

-- "[...] "Matches any character inside the square brackets

select 'abcdefg' regexp '[weh]'

select 'abcdefg' REGEXP '^[ead]'

-- "[^...] "Match does not match any character in parenthesis

select 'efg' REGEXP '[^XYZ]','X' REGEXP '[^XYZ]'

www.2cto.com

select * from user where email REGEXP '@163.com$'

Using rand() to extract random rows skillfully

--Randomly select 5 users

select * from tf_f_user order by rand() limit 5

with rollup bit_or bit_and

Used with group by to sum

Database and table name capitalization issues

Always create and reference database names and table names in lowercase or uppercase

MySQL case rules for the following names, table names, column names and aliases are as follows:

1. Database names and table names are strictly case-sensitive;

2. The alias of the table is strictly case-sensitive;

3. Column names and column aliases are case insensitive in all cases;

Variable names are also case sensitive; www.2cto.com

MySQL is case-insensitive under Windows.

----------------------------------------------------------------------------------------------------

1. Take Ubuntu as an example: Ubuntu is installed by default: case-sensitive table names, case-insensitive column names;

2. After logging in with root account, add lower_case_table_names=1 after [mysqld] in/etc/mysql/my.cnf, restart MYSQL service, and the setting is successful: case-insensitive table names;

lower_case_table_names parameter details:

lower_case_table_names = 0

0: case-sensitive, 1: case-insensitive

----------------------------------------------------------------------------------------------------

3. If you want to distinguish the case of the field value when querying, you need to set the BINARY attribute for the field value. There are many ways to set it:

A. Set when creating:

CREATE TABLE T(

A VARCHAR(10) BINARY

); www.2cto.com

B. Use alter to modify:

ALTER TABLE `tablename` MODIFY COLUMN `cloname` VARCHAR(45) BINARY;

C. Check BINARY directly in mysql table editor.

Foreign key considerations

In mysql, the InnoDB storage engine supports checking for external keyword constraints. For tables of other types of storage engines, reference can be used when defining columns using the reference table_name(colon_name) clause, but this clause has no practical effect, only as a memo or gaze to remind the user that the column currently being defined executes a column in another table.

Thank you for reading, the above is the content of "MySQL database common skills", after the study of this article, I believe that everyone has a deeper understanding of MySQL database common skills this problem, the specific use of the situation also needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

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.