Mysql database replace and regexp usage

Replace and regexp usage
0 Comments | This entry was posted on Apr 08 2010.
Mysql replace usage
1. replace
Replace into table (id, name) values ('1', 'A'), ('2', 'bb ')
This statement inserts two records into the table. If the primary key id is 1 or 2 does not exist
It is equivalent
Insert into table (id, name) values ('1', 'A'), ('2', 'bb ')
Data is not inserted if the same value exists.
2. replace (object, search, replace)
Replace all search objects with replace
Select replace ('www .163.com ', 'w', 'ww')-> WwWwWw.163.com
For example, replace aa in the name field of the table with bb.
Update table set name = replace (name, 'AA', 'bb ')
---------------------------
Other types of pattern matching provided by MySQL use extended regular expressions. When you perform a match test on this type of pattern, use the REGEXP and not regexp operators (or RLIKE and not rlike, which are synonyms ).
Some Characters of the extended regular expression are:
· '.' Matches any single character.
· The character class "[...]" matches any character in square brackets. For example, "[abc]" matches "a", "B", or "c ". To name the character range, use a hyphen (-). "[A-z]" matches any letter, and "[0-9]" matches any number.
· "*" Matches zero or multiple characters before it. For example, "x *" matches any number of "x" characters, "[0-9] *" matches any number, and ". * "matches any number of characters.
If the REGEXP pattern matches any part of the tested value, the pattern matches (different from the LIKE pattern match. The pattern matches only the entire value ).
To locate a pattern so that it must match the start or end of the tested value, use "^" at the start of the pattern or "$" at the end of the pattern ".
To demonstrate how the extended regular expression works, use REGEXP to rewrite the LIKE Query shown above:
To find the name starting with "B", use "^" to match the start of the name:
Mysql> SELECT * FROM pet WHERE name REGEXP '^ B ';
+ --- + -- + ---- +
| Name | owner | species | sex | birth | death |
+ --- + -- + ---- +
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Boane | Diane | dog | m |
+ --- + -- + ---- +
If you want to force REGEXP to be case sensitive, use the BINARY keyword to convert a string to a BINARY string. This query only matches the lowercase 'B' of the first letter of the name '.
Mysql> SELECT * FROM pet WHERE name regexp binary '^ B ';
To find the name ending with "fy", use "$" to match the end Of the name:
Mysql> SELECT * FROM pet WHERE name REGEXP 'fy $ ';
+ --- + -- + ---- + --- +
| Name | owner | species | sex | birth | death |
+ --- + -- + ---- + --- +
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+ --- + -- + ---- + --- +
To locate the name containing "w", use the following query:
Mysql> SELECT * FROM pet WHERE name REGEXP 'W ';
+ ---- + --- + -- + ---- +
| Name | owner | species | sex | birth | death |
+ ---- + --- + -- + ---- +
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Boane | Diane | dog | m |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
+ ---- + --- + -- + ---- +
Since a regular expression appears anywhere in the value, its pattern matches, you do not have to place a wildcard on both sides of the pattern in the previous query so that it matches the entire value, just as if you were using an SQL mode.
To locate a name that contains exactly five characters, use "^" and "$" to match the start and end of the name, and the five "." instances are in the range:
Mysql> SELECT * FROM pet WHERE name REGEXP '^ ..... $ ';
+ --- + -- + ---- + --- +
| Name | owner | species | sex | birth | death |
+ --- + -- + ---- + --- +
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+ --- + -- + ---- + --- +
You can also use the "{n}" "Repeat n times" operator to overwrite the previous query:
Mysql> SELECT * FROM pet WHERE name REGEXP '^. {5} $ ';
+ --- + -- + ---- + --- +
| Name | owner | species | sex | birth | death |
+ --- + -- + ---- + --- +
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+ --- + -- + ---- + --- +
Appendix G: MySQL regular expressions provide detailed information about the syntax of regular expressions.
3.3.4.8. The counting row database is often used to answer this question: "How often does a type of data appear in a table ?" For example, you may want to know how many pets you have, how many pets each master has, or you may want to perform various types of surveys on your animals.
Calculate the total number of animals you own and the number of rows in the pet table ?" This is the same problem because each pet has a record. The COUNT (*) function calculates the number of rows. Therefore, the query for the number of animals should be:
Mysql> select count (*) FROM pet;
+ ---- +
| COUNT (*) |
+ ---- +
| 9 |
+ ---- +
You have previously retrieved the name of a person with a pet. If you want to know how many pets each master has, you can use the COUNT () function:
Mysql> SELECT owner, COUNT (*) FROM pet group by owner;
+ --- + ---- +
| Owner | COUNT (*) |
+ --- + ---- +
| Benny | 2 |
| Diane | 2 |
| Gwen | 3 |
| Harold | 2 |
+ --- + ---- +
Note: If you use group by to GROUP all records of each owner without it, you will receive an error message:
Mysql> SELECT owner, COUNT (*) FROM pet;
ERROR 1140 (42000): Mixing of GROUP columns (MIN (), MAX (), COUNT (),...)
With no GROUP columns is illegal if there is no group by clause
COUNT () and group by classify your data in various ways. The following example shows different methods for conducting an animal census.
Quantity of each animal:
Mysql> SELECT species, COUNT (*) FROM pet group by species;
+ --- + ---- +
| Species | COUNT (*) |
+ --- + ---- +
| Bird | 2 |
| Cat | 2 |
| Dog | 3 |
| Hamster | 1 |
| Snake | 1 |
+ --- + ---- +
Number of animals for each gender:
Mysql> SELECT sex, COUNT (*) FROM pet group by sex;
+ -- + ---- +
| Sex | COUNT (*) |
+ -- + ---- +
| NULL | 1 |
| F | 4 |
| M | 4 |
+ -- + ---- +
(In this output, NULL indicates "unknown gender ".)
Number of animals by type and gender:
Mysql> SELECT species, sex, COUNT (*) FROM pet group by species, sex;
+ --- + -- + ---- +
| Species | sex | COUNT (*) |
+ --- + -- + ---- +
| Bird | NULL | 1 |
| Bird | f | 1 |
| Cat | f | 1 |
| Cat | m | 1 |
| Dog | f | 1 |
| Dog | m | 2 |
| Hamster | f | 1 |
| Snake | m | 1 |
+ --- + -- + ---- +
If you use COUNT (), you do not have to retrieve the entire table. For example, in the previous query, when only a dog or a cat is executed, it should be:
Mysql> SELECT species, sex, COUNT (*) FROM pet
-> WHERE species = 'Dog' OR species = 'cat'
-> Group by species, sex;
+ --- + -- + ---- +
| Species | sex | COUNT (*) |
+ --- + -- + ---- +
| Cat | f | 1 |
| Cat | m | 1 |
| Dog | f | 1 |
| Dog | m | 2 |
+ --- + -- + ---- +
Or, if you only need to know the number of sex-based animals with known gender:
Mysql> SELECT species, sex, COUNT (*) FROM pet
-> WHERE sex IS NOT NULL
-> Group by species, sex;
+ --- + -- + ---- +
| Species | sex | COUNT (*) |
+ --- + -- + ---- +
| Bird | f | 1 |
| Cat | f | 1 |
| Cat | m | 1 |
| Dog | f | 1 |
| Dog | m | 2 |
| Hamster | f | 1 |
| Snake | m | 1 |
+ --- + -- + ---- +
3.3.4.9. use more than one table
The pet table tracks which pet you have. If you want to record other related information, such as seeing a veterinarian in their life or when a descendant is born, you need another table. What should this table look like? Required:
· It needs to contain the pet name so that you know which animal each event belongs.
· You need a date to know when the event occurred.
· A field describing the event is required.
· If you want to classify events, you need an event type field.
Based on the above factors, the create table statement of the event TABLE should be:
Mysql> create table event (name VARCHAR (20), date DATE,
-> Type VARCHAR (15), remark VARCHAR (255 ));
For a pet table, the easiest way is to create a text file that contains information separated by delimiters to load the initial records:
Name
Date
Type
Remark
Fluffy
1995-05-15
Litter
4 kittens, 3 female, 1 male
Buffy
1993-06-23
Litter
5 puppies, 2 female, 3 male
Buffy
1994-06-19
Litter
3 puppies, 3 female
Chirpy
2017-03-21
Vet
Needed beak straightened
Slim
1997-08-03
Vet
Broken rib
Bowser
1991-10-12
Kennel
Fang
1991-10-12
Kennel
Fang
1998-08-28
Birthday
Gave him a new chew toy
Claws
1998-03-17
Birthday
Gave him a new flea collar
Whistler
1998-
Birthday
First birthday
Use the following method to load records:
Mysql> load data local infile 'event.txt 'into table event;
According to what you learned from queries that have been run on the pet table, you should be able to search records in the event table. The principle is the same. But when cannot the event table answer any questions you may ask?
When they have a nest of small animals, suppose you want to find out the age of each pet. We have seen how to calculate the age by two dates. The event table contains the mother's production date, but to calculate the mother's age, you need her birth date, which is stored in the pet table. Two tables are required for query:
Mysql> SELECT pet. name,
-> (YEAR (date)-YEAR (birth)-(RIGHT (date, 5) <RIGHT (birth, 5) AS age,
-> Remark
-> FROM pet, event
-> WHERE pet. name = event. name AND event. type = 'litter ';
+ --- + -- + ---------- +
| Name | age | remark |
+ --- + -- + ---------- +
| Fluffy | 2 | 4 kittens, 3 female, 1 male |
| Buffy | 4 | 5 puppies, 2 female, 3 male |
| Buffy | 5 | 3 puppies, 3 female |
+ --- + -- + ---------- +
Note the following:
The FROM clause lists two tables, because the Query Needs to extract information FROM two tables.
When combining information from multiple tables, you need to specify how the records in a table can match the records in other tables. This is simple because they all have a name column. The query uses the WHERE clause to match records in two tables based on the name value.
Because name is listed in two tables, you must specify which table to reference when referencing columns. You can add the table name to the column name.
You do not have to have two different tables for join. If you want to compare the records of a table with other records of the same table, you can join a table to itself. For example, in order to breed a spouse among your pets, you can use pet to associate yourself with a similar type of female:
Mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
-> FROM pet AS p1, pet AS p2
-> WHERE p1.species = p2.species AND p1.sex = 'F' AND p2.sex = 'M ';
+ --- + -- + --- +
| Name | sex | species |
+ --- + -- + --- +
| Fluffy | f | Claws | m | cat |
| Buffy | f | Fang | m | dog |
| Buffy | f | Bowser | m | dog |
+ --- + -- + --- +
In this query, we specify an alias for the table name so that the column can be referenced and the table instance associated with each column reference is more intuitive.
3.4. Obtain information about databases and tables. What if you forget the database or table name or the given table structure (for example, what is its column name? MySQL solves this problem by providing several statements about the database and its supported tables.
You have seen show databases, which lists the DATABASES managed by the server. To find the DATABASE Selected, use the DATABASE () function:
Mysql> select database ();
+ ---- +
| DATABASE () |
+ ---- +
| Menagerie |
+ ---- +
If you have not selected any database, the result is NULL.
To find out what table the current database contains (for example, when you cannot determine the name of a table), run the following command:
Mysql> show tables;
+ ------- +
| Tables in menagerie |
+ ------- +
| Event |
| Pet |
+ ------- +
If you want to know the structure of a table, you can use the DESCRIBE command; it displays the information of each column in the table:
Mysql> DESCRIBE pet;
+ --- + ----- + -- + --- +
| Field | Type | Null | Key | Default | Extra |
+ --- + ----- + -- + --- +
| Name | varchar (20) | YES | NULL |
| Owner | varchar (20) | YES | NULL |
| Species | varchar (20) | YES | NULL |
| Sex | char (1) | YES | NULL |
| Birth | date | YES | NULL |
| Death | date | YES | NULL |
+ --- + ----- + -- + --- +
Field indicates the column name. Type indicates the column data Type. Null indicates whether the column can contain NULL values. Key indicates whether the column is indexed and Default indicates the Default value of the column.
If the table has an INDEX, show index from tbl_name generates information about the INDEX.
During a mysql search today, I found that when I used select name from contact where name like '% a %', the results included the Chinese "new" in addition to the name of" the name also appears in the search results, this makes me want to find out what the matching modes and rules of mysql are like, so I decided to check the information for details. In addition, regular expressions are also frequently used during matching! So I am going to record what I learned here!
The cause of this problem is: MySQL is case insensitive when querying strings, in programming MySQL, The ISO-8859 character set is generally used as the default character set, therefore, the case-sensitivity conversion of Chinese encoding Characters During the comparison process causes this phenomenon.
Solution
1. when creating a table, add the "BINARY" attribute to the fields that contain Chinese characters to make BINARY comparison. For example, change "name char (10)" to "name char (10) BINARY ". However, it is case-sensitive when you match this field in the table.
2. If you use the source code to compile MySQL, you can use the-with-charset = gbk parameter during compilation, so that mysql directly supports Chinese searching and sorting.
3. Use the locate function of mysql to determine. For example:
SELECT * FROM table WHERE locate (substr, str)> 0;
Locate () has two forms: LOCATE (substr, str), LOCATE (substr, str, pos ). Returns the position of substr in str. If str does not contain substr, return 0. This function is case insensitive.
4. use the SQL statement: SELECT * FROM TABLE WHERE FIELDS LIKE BINARY '% FIND % ', however, this is case-sensitive like 1. If you want to perform a case-insensitive query, use upper or lower for conversion.
5. Use binary and ucase functions and concat functions. Ucase converts all English-speaking letters into uppercase letters, and concat concatenates strings. The new SQL statement is as follows:
Select id, title, name from achech_com.news where binary ucase (title) like concat ('%', ucase ('A'), '% ')
It can also be written as select id, title, name from achech_com.news where binary ucase (title) like ucase ('% a % ')
The search results are satisfactory, but the speed may be slow for N milliseconds. Because matching with like and % will affect the efficiency.
Regular Expression:
Regular Expressions are a powerful way to specify patterns for complex searches.
^
Start of the string following the matched string
Mysql> select "fonfo" REGEXP "^ fo $";-> 0 (mismatch)
Mysql> select "fofo" REGEXP "^ fo";-> 1 (matching)
$
The end of the matched string
Mysql> select "fono" REGEXP "^ fono $";-> 1 (matching)
Mysql> select "fono" REGEXP "^ fo $";-> 0 (mismatch)
.
Match any characters (including new lines)
Mysql> select "fofo" REGEXP "^ f. *";-> 1 (matching)
Mysql> select "fonfo" REGEXP "^ f. *";-> 1 (matching)
A *
Match any number of a (including empty strings)
Mysql> select "Ban" REGEXP "^ Ba * n";-> 1 (matching)
Mysql> select "Baaan" REGEXP "^ Ba * n";-> 1 (matching)
Mysql> select "Bn" REGEXP "^ Ba * n";-> 1 (indicating matching)
A +
Matches any sequence of one or more a characters.
Mysql> select "Ban" REGEXP "^ Ba + n";-> 1 (matching)
Mysql> select "Bn" REGEXP "^ Ba + n";-> 0 (indicating mismatch)
A?
Match one or zero
Mysql> select "Bn" REGEXP "^ Ba? N ";-> 1 (indicating matching)
Mysql> select "Ban" REGEXP "^ Ba? N ";-> 1 (indicating matching)
Mysql> select "Baan" REGEXP "^ Ba? N ";-> 0 (indicating mismatch)
De | abc
Match de or abc
Mysql> select "pi" REGEXP "pi | apa";-> 1 (indicating matching)
Mysql> select "axe" REGEXP "pi | apa";-> 0 (indicating mismatch)
Mysql> select "apa" REGEXP "pi | apa";-> 1 (indicating matching)
Mysql> select "apa" REGEXP "^ (pi | apa) $";-> 1 (indicating matching)
Mysql> select "pi" REGEXP "^ (pi | apa) $";-> 1 (indicating matching)
Mysql> select "pix" REGEXP "^ (pi | apa) $";-> 0 (indicating mismatch)
(Abc )*
Match any number of abc (including empty strings)
Mysql> select "pi" REGEXP "^ (pi) * $";-> 1 (indicating matching)
Mysql> select "pip" REGEXP "^ (pi) * $";-> 0 (indicating mismatch)
Mysql> select "pipi" REGEXP "^ (pi) * $";-> 1 (indicating matching)
{1} {2, 3}
This is a more comprehensive method, which can implement the functions of the previous several Reserved Words
A *
Can be written as a {0 ,}
A
Can be written as a {1 ,}
A?
Can be written as a {0, 1}
There is only one integer parameter I in {}, indicating that the character can only appear I times; there is an integer parameter I in,
Followed by a ",", indicating that the character can appear I times or more than I times; there is only one integer parameter in,
Followed by a ",", followed by an integer parameter j, indicating that the character can only appear more than I times, less than j times
(Including I and j ). The integer parameter must be greater than or equal to 0 and less than or equal to RE_DUP_MAX (default value: 25 ).
5 ). If both m and n are given, m must be less than or equal to n.
[A-dX], [^ a-dX]
Match any character that is (or is not, if ^ is used) a, B, c, d, or X. The "-" character between two other characters constitutes a range, and matches all characters starting from 1st characters to 2nd characters. For example, [0-9] matches any decimal number. To include the text character "]", it must be followed by the brackets. To contain the text character "-", it must be written first or last. For any character that does not define any special meaning for [], it only matches itself.
Mysql> select "aXbc" REGEXP "[a-dXYZ]";-> 1 (indicating matching)
Mysql> select "aXbc" REGEXP "^ [a-dXYZ] $";-> 0 (indicating mismatch)
Mysql> select "aXbc" REGEXP "^ [a-dXYZ] $";-> 1 (indicating matching)
Mysql> select "aXbc" REGEXP "^ [^ a-dXYZ] $";-> 0 (indicating mismatch)
Mysql> select "gheis" REGEXP "^ [^ a-dXYZ] $";-> 1 (indicating matching)
Mysql> select "gheisa" REGEXP "^ [^ a-dXYZ] $";-> 0 (indicating no matching)
[[. Characters.]
Indicates the order of comparison elements. The Character Sequence in parentheses is unique. However, parentheses can contain wildcards,
So he can match more characters. For example, the regular expression [[. ch.] * c matches the first five characters of chchcc.
.
[= Character_class =]
It indicates an equal class, which can replace other equal elements in the class, including its own. For example, if o and () are
For members of an equal class, [[= o =], [[= () =], and [o ()] are completely equivalent.
[: Character_class:]
In parentheses, the name of the character class is in the middle of [: And:], which can represent all characters of the class.
The character classes are named alnum, digit, punct, alpha, graph, space, blank, lower, and uppe.
R, cntrl, print, and xdigit
Mysql> select "justalnums" REGEXP "[[: alnum:]";-> 1 (indicating matching)
Mysql> select "!" REGEXP "[[: alnum:]";-> 0 (not matching)
Alnum text and numeric characters
Alpha character
Blank white space characters
Cntrl control characters
Digit numeric characters
Graph character
Lower lowercase characters
Print graphics or space characters
Punct punctuation
Space, tab, new line, and carriage return
Upper uppercase characters
Xdigit hexadecimal numeric characters
[[: <:]
[[:>:]
Matches an empty string at the beginning and end of a word. the start and end of the word are not included in alnum.
Cannot contain underscores.
Mysql> select "a word a" REGEXP "[[: <:] word [[: >:]]";-> 1 (indicating matching)
Mysql> select "a xword a" REGEXP "[[: <:] word [[:]";-> 0 (indicating that the request does not match)
Mysql> select "weeknights" REGEXP "^ (wee | week) (knights | nights) $";-> 1 (indicating
Matching)
To use a text instance with special characters in a regular expression, add two backslash (\) characters before it. The MySQL parser is responsible for interpreting one of them, and the regular expression library is responsible for interpreting the other. For example, to match the string "1 + 2" that contains the special character "+", in the following regular expression, only the last one is correct:
Mysql> SELECT '1 + 2' REGEXP '1 + 2';-> 0
Mysql> SELECT '1 + 2' REGEXP '1 \ + 2';-> 0
Mysql> SELECT '1 + 2' REGEXP '1 \ + 2';-> 1