Common MySQL Tips (51)

Use of regular expressions

MySQL uses the regexp command to provide user-extended regular expression functionality with the following sequence of patterns:

Sequence	Sequence description	Sequence	Sequence description
^	Match at the beginning of a string	A?	Match 1 or 0 a
$	Match at the end of a string	A1|a2	Match A1 or A2
.	Match any single character, including line break	A (m)	Match m A
[...]	Match out any character in parentheses	A (M,)	Match at least m a
[^ ...]	Matches any character that does not appear in parentheses	A (M,n)	Match m to n a
A *	Match 0 or more a (including empty strings)	A (, N)	Match 0 to n a
A +	Match 1 or more (not including empty strings)	(...)	Make a single element of a pattern element

Use regular Expressions "$" and "[...]" to match:

Mysql>select Name,email from t where email REGEXP
"@163[.,]com$"

Use the like method to query:

Mysql>select Name,email from t where email like "%@163.com"
or email like "%@163,com"

Skillfully extracting random rows with rand ()

MySQL database has a random function rand () is to get a number between 0-1, using this function together and order by to be able to sort the data randomly.

Mysql>select * FROM Stu Order by Rand ();

The following is a random sampling of 3 data samples by limit.

Mysql>select * FROM Stu Order by rand () limit 3;

Using GROUP BY with ROLLUP

Use the WITH ROLLUP clause of GROUP by to retrieve more packet aggregation information

Mysql> SELECT * from demo;
+-------+-------+
| CNAME | PName |
+-------+-------+
| BJ | HD |
| BJ | HD |
| BJ | XC |
| BJ | XC |
| BJ | HD |
| SH | DH |
| SH | DH |
| SH | RG |
| SH | DH |
+-------+-------+
9 Rows in Set (0.00 sec)

The PName column is aggregated for the demo table according to the CNAME, pname column groupings as follows

Mysql> Select Cname,pname,count (pname) from the demo group by Cname,pname;

+-------+-------+--------------+

| CNAME | PName | Count (pname) |

+-------+-------+--------------+

| BJ |            HD | 3 |

| BJ |            XC | 2 |

| SH |            DH | 3 |

| SH |            RG | 1 |

+-------+-------+--------------+

4 rows in Set (0.00 sec)

Also use the WITH rollup keyword to count more information, as follows. Note: With rollup cannot be used in conjunction with the Ordery by

Mysql> Select Cname,pname,count (pname) from the demo group by Cname,pname with rollup;

+-------+-------+--------------+

| CNAME | PName | Count (pname) |

+-------+-------+--------------+

| BJ |            HD | 3 |

| BJ |            XC | 2 |

| BJ |            NULL | 5 |

| SH |            DH | 3 |

| SH |            RG | 1 |

| SH |            NULL | 4 |

| NULL |            NULL | 9 |

+-------+-------+--------------+

7 Rows in Set (0.00 sec)

Using bit GROUP functions to do statistics

You can use the Bit_and, Bit_or function to do the statistical work at the same time using the GROUP by statement. The function of these two functions is to do the logical bitwise operation between the numbers.

Mysql>select id,bit_or (kind) from Order_rab GROUP by ID

bitwise AND and or calculations for kind when grouping IDs in the Order_rab table

Mysql>select Id,bit_and (kind) from Order_rab GROUP by ID

Issues to be aware of when using foreign keys

Create a foreign key in the following way

Mysql>create table temp (id int, name char, foreign key (ID) references outtable (ID) on DELETE cascade on update CAs CADE);

Note: InnoDB Types of tables support foreign keys, myisam types of tables, although creating foreign keys can be successful, but do not work, mainly because foreign keys are not supported.

MySQL Help uses

How many commands in MySQL can be remembered is a problem, here is a very good way to get help, of course, in the mysql> prompt operation:

1.? % can get all the commands in the mysql>, this is the most, so how do things get further help here?

2.? Create

3.? opti% because can't remember optimize's full name, this time can use% to replace

4.? reg% obtained the regexp usage that cannot be remembered.

5. See all uses? Contents can get all the help outline, through this directory reuse? Continue to look down.

Common MySQL Tips (51)