Common MySQL skills

MySQL common skills Regular Expression -- whether to start with a select 'abcdefg' REGEXP '^ a' -- whether to end with fg www.2cto.com select 'abcdefg' REGEXP 'fg $' -- wow, select STAFF_Name, STAFF_NAME REGEXP '^ test' from g_staffselect STAFF_Name from g_staff where STAFF_NAME REGEXP '^ Liu' = 1 --". "match any single character select 'abcdefg' REGEXP '. h', 'abcdefg' REGEXP '. g' -- "[...] "match any character in the brackets select 'abcdefg' regexp '[weh] 'select' abcdefg' REGEXP' ^ [ead] '--" [^...] "does not match any Select 'efg' REGEXP '[^ XYZ]', 'x' REGEXP '[^ XYZ]' www.2cto.com select * from user where email REGEXP '@ 163.com$' Use rand () extract random rows -- select * from tf_f_user order by rand () limit 5 with rollup bit_or bit_and with group, the case sensitivity of databases and table names is always used to create and reference database names and table names in lower case or in upper case. MySQL uses the case sensitivity rules for database names, table names, column names, and alias names in Linux. of: 1. The database name and table name are case sensitive. 2. The table alias is case sensitive; 3. The column name and column alias are case-insensitive in all cases. 4. The variable name is also case-sensitive. Indows are case insensitive. Example 1: Ubuntu is used as an example. After mysql is installed in Ubuntu, the default value is: case-sensitive for table names, not case-sensitive for column names. 2. After logging on with the root account, in/etc/mysql/my. add lower_case_table_names = 1 after [mysqld] In cnf and restart the MYSQL service. The settings are successful: Case Insensitive to table names; lower_case_table_names parameter description: lower_case_table_names = 0 0: case Sensitive, 1: Case Insensitive ------------------------------------------------------------------------------ ---------------------- 3. If you want to query the case sensitivity of the time zone field value, you must set the BINARY Attribute for the field value. You can set multiple methods: A. set at creation: 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. Select BINARY in mysql table editor. Note: in mysql, the InnoDB Storage engine supports checking the constraints of external keywords. For tables of other storage engines, you can use reference when using the reference table_name (colomn_name) clause to define columns, but this clause has no practical effect, only serves as a memo or watch to remind the user to execute one column in another table as the column currently being defined. Author bengda