Mysql database Replace, regexp usage _mysql

Use of Replace and regexp
0 Comments | This entry is posted on APR 08 2010
MySQL Replace usage
1.replace into
Replace into table (Id,name) VALUES (' 1′, ' AA '), (' 2′, ' BB ')
The purpose of this statement is to insert two records into table tables. If the primary key ID is 1 or 2 does not exist
is equivalent
Insert into table (Id,name) VALUES (' 1′, ' AA '), (' 2′, ' BB ')
Data is not inserted if the same value exists
2.replace (Object,search,replace)
Replaces all occurrences of search in object with replace
Select replace (' www.163.com ', ' w ', ' Ww ')->wwwwww.163.com
To replace a AA in the Name field in table tables with BB
Update table Set Name=replace (name, ' AA ', ' BB ')
——————————————————————————–
Other types of pattern matching provided by MySQL are the use of extended regular expressions. When you match this pattern to a test, 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.
· Character class "[...]" Matches any character within the square brackets. For example, "[ABC]" matches "a", "B", or "C". To name the range of characters, use a "-". "[A-z]" matches any letter, and "[0-9]" matches any number.
· "*" matches 0 or more characters in front of it. For example, "x*" matches any number of "X" characters, "[0-9]*" matches any number of numbers, and ". *" matches any number of any characters.
If the regexp pattern matches anywhere that is tested, the pattern matches (unlike like pattern matching, which 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 beginning of the pattern or at the end of the pattern with "$".
To illustrate how an extended regular expression works, use RegExp to rewrite the like query shown above:
To find the name that begins with "B", use "^" to match the beginning 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|
| Bowser | Diane | Dog| m | 1989-08-31 | 1995-07-29 |
+--–+--–+ ——— +--+ ———— + ———— +
If you want to force the regexp comparison to be case-sensitive, use the binary keyword to make one of the strings into a binary string. The query matches only 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 find the name that contains a "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|
| bowser| Diane | Dog| m | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen | Bird | NULL | 1997-12-09 | null|
+ ———-+ ——-+ ——— +--+ ———— + ———— +
Since a regular expression appears anywhere in the value and its pattern matches, there is no need to place a wildcard on both sides of the pattern in the previous query so that it matches the entire value, just as you would use an SQL schema.
To find names containing exactly 5 characters, use "^" and "$" to match the start and end of the first name, and the 5 "." Instances in between:
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 rewrite the previous query using the "{n}" "Repeat N Times" operator:
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 count row database is often used to answer the question, "how frequently does a certain type of data appear in a table?" For example, you may want to know how many pets you have, or how many pets each owner has, or you may want to conduct various types of surveys of your animals.
Calculate the total number of animals you own and "How many rows are there in the pet table?" Is the same problem because each pet has a record. The count (*) function calculates the number of rows, so the query for calculating the number of animals should be:
Mysql> SELECT COUNT (*) from pet;
+ ———-+
| COUNT (*) |
+ ———-+
| 9 |
+ ———-+
In the front, you retrieve the name of the person who owns the pet. If you want to know how many pets each owner 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 that by using GROUP by to group all records for each owner, you will get an error message without it:
Mysql> SELECT owner, COUNT (*) from pet;
ERROR 1140 (42000): Mixing of GROUP columns (MIN (), MAX (), COUNT (),...)
With no group columns are illegal if there is no GROUP BY clause
COUNT () and group by classify your data in various ways. The following examples show different ways of conducting animal census operations.
Number of animals per species:
Mysql> SELECT species, COUNT (*) from the pet GROUP by species;
+ ——— + ———-+
| Species | COUNT (*) |
+ ——— + ———-+
| Bird | 2 |
| Cat| 2 |
| Dog| 3 |
| Hamster | 1 |
| snake| 1 |
+ ——— + ———-+
Number of animals per sex:
mysql> SELECT Sex, COUNT (*) from the pet GROUP by sex;
+--+ ———-+
| sex | COUNT (*) |
+--+ ———-+
| NULL | 1 |
| f | 4 |
| m | 4 |
+--+ ———-+
(in this output, null denotes "unknown sex.") ）
Number of animals by type and sex:
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, the previous query, when only for dogs and cats, 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-disaggregated animals known as sex:
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 1 tables
Pet Watch track which pet you have. If you want to record other relevant information, for example, when you see a vet in their lifetime or when a descendant is born, you need another watch. What should this watch look like? Need:
· It needs to contain pet names so that you know which animal each event belongs to.
· A date is needed so that you know when the event happened.
· A field that describes the event is required.
· If you want to categorize events, you need an event type field.
To synthesize the above factors, the CREATE TABLE statement for the event table should be:
Mysql> CREATE TABLE Event (name VARCHAR), date date,
-> type VARCHAR, remark VARCHAR (255));
The easiest way for a pet table is to create a placeholder-delimited text file that contains information to mount the initial record:
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
1999-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-12-09
Birthday
The birthday
Load records in the following ways:
mysql> LOAD DATA local INFILE ' event.txt ' into TABLE event;
You should be able to perform a retrieval of the records in the event table based on what you have learned from the queries that have been run on the pet table; the principle is the same. But when does the event table itself not answer the questions you might ask?
When they have a litter of small animals, suppose you want to find out the age of each pet. We have seen how to calculate age by two dates. The event table has a mother's production date, but in order to calculate the mother's age, you need her date of birth, stored in the pet table. Description query requires two tables:
Mysql> SELECT Pet.name,
-> (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|
+--–+--+ ————————— –+
A few things to note about this query:
The FROM clause lists two tables because the query needs to extract information from both tables.
When you combine (join) information from multiple tables, you need to specify how records in one table can match the records of other tables. This is simple, because they all have a name column. The query uses a WHERE clause to match records in 2 tables based on the name value.
Because name is listed in two tables now, you must specify which table to use when referencing columns. This can be achieved by attaching the table name to the column name.
You don't have to have 2 different tables to connect. If you want to compare a table's records with other records in the same table, you can connect a table to itself. For example, to breed a mate in your pet, you can use pet to link itself to a similar kind of male-female pairing:
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 | 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 it can refer to the column and make it more intuitive for each column reference to be associated with which table instance.
3.4. Get information about databases and tables What if you forget the name of the database or table, or what the structure of the given table is (for example, what is its column called)? MySQL solves this problem by providing several statements of information about the database and its supported tables.
You've seen show DATABASES, which lists the databases managed by the server. To find out which database is currently selected, use the DB () function:
Mysql> SELECT DATABASE ();
+ ———— +
| DATABASE () |
+ ———— +
| Menagerie |
+ ———— +
If you have not selected any database, the result is null.
To find out what tables the current database contains (for example, when you can't determine the name of a table), use this 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, which displays information for 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 displays the column name, type is the data type of the column, and NULL indicates whether the column can contain null values, and the key shows whether the column is indexed and default specifies the column defaults.
If the table has an index, the show index from Tbl_name generates information about the index.
Today, when I was doing a search for MySQL, I found out that when I used the select name from the contact where name like '%a% ' the result of the inclusion of the name "new" in addition to the name of A is also appearing in the search results, This makes me want to figure out what the MySQL matching pattern and rules are like, so I decided to check the data to understand, and in addition, regular expressions are also used in the match. So prepare to record my learning these two things in the harvest!
The reason for this problem is: MySQL in the query string is not sensitive to the case, when compiling the MySQL in general with the ISO-8859 character set as the default character set, so in the comparison process of Chinese encoded character case conversion caused this phenomenon.
Solutions
1. Add the "BINARY" attribute to the field containing the Chinese in the table to make a binary comparison, such as ″name char (10) to "name Char" BINARY. However, you are 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 when compiling, so MySQL directly supports Chinese search and sorting.
3. Use the MySQL locate function to judge. Such as:
SELECT * FROM table WHERE Locate (SUBSTR,STR) >0;
Locate () has two forms: locate (SUBSTR,STR), locate (Substr,str,pos). Returns the position of the substr in STR if STR does not contain substr return 0. This function is also case-insensitive.
4. Use SQL statements like this: SELECT * from TABLE WHERE FIELDS like BINARY '%find% ', but this is case-sensitive if you want to do case-insensitive queries using upper or lower for conversion.
5. Use binary and UCase functions and concat functions. UCase is spoken in English all uppercase, concat strings are concatenated. The new SQL statement is as follows:
Select Id,title,name from achech_com.news where binary UCase (title) like Concat ('% ', UCase (' a '), '% ')
Can also be written as select Id,title,name from achech_com.news where binary UCase (title) Like UCase ('%a% ')
The results of the search are satisfactory, but the speed may be slower than n milliseconds oh. Because the use of like and% to match, the efficiency will have a certain impact.
Regular expression:
Regular expressions are a powerful way to specify patterns for complex searches.
^
The matched string starts with the following string
mysql> Select "Fonfo" REGEXP "^fo$"; -> 0 (indicates no match)
mysql> Select "Fofo" REGEXP "^FO"; -> 1 (matching)
$
The matched string ends with the preceding string
Mysql> Select "Fono" REGEXP "^fono$"; -> 1 (matching)
Mysql> Select "Fono" REGEXP "^fo$"; -> 0 (indicates no match)
.
Match any character (including new rows)
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 (matching)
A +
Matches any sequence of 1 or more a characters.
mysql> Select "Ban" REGEXP "^ba+n"; -> 1 (matching)
mysql> Select "Bn" REGEXP "^ba+n"; -> 0 (indicates no match)
A?
Match one or 0 a
mysql> Select "Bn" REGEXP "^ba?n"; -> 1 (matching)
mysql> Select "Ban" REGEXP "^ba?n"; -> 1 (matching)
mysql> Select "Baan" REGEXP "^ba?n"; -> 0 (indicates no match)
De|abc
Match de or ABC
mysql> Select "PI" REGEXP "Pi|apa"; -> 1 (matching)
Mysql> select "Axe" REGEXP "Pi|apa"; -> 0 (indicates no match)
Mysql> Select "APA" REGEXP "Pi|apa"; -> 1 (matching)
Mysql> Select "APA" REGEXP "^ (Pi|apa) $"; -> 1 (matching)
mysql> Select "PI" REGEXP "^ (Pi|apa) $"; -> 1 (matching)
mysql> select "Pix" REGEXP "^ (Pi|apa) $"; -> 0 (indicates no match)
(ABC) *
Match any number of ABC (including Empty strings)
mysql> Select "PI" REGEXP "^ (pi) *$"; -> 1 (matching)
mysql> Select "Pip" REGEXP "^ (pi) *$"; -> 0 (indicates no match)
mysql> Select "Pipi" REGEXP "^ (pi) *$"; -> 1 (matching)
{1} {2,3}
This is a more comprehensive approach that enables you to implement several of the previous 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 {}, which indicates that the character can only appear I; there is an integer parameter I in {}.
Followed by a ",", indicating that the character can appear I or more than the first, in {} There is only an integer parameter I,
Followed by a ",", and then an integer parameter J, which indicates that the character can only appear more than I, J times the following
(including I and J times). The integer argument must be greater than or equal to 0, and less than or equal to Re_dup_max (default is 25
5). If both M and n,m must be less than or equal to N.
[A-DX], [^A-DX]
Matches any character that is (or is not, if used ^ words) A, B, C, D, or X. The "-" character between two other characters constitutes a range that matches all characters from the beginning of the 1th character to the 2nd character. For example, [0-9] matches any decimal digit. To include the literal character "]", it must be immediately after the opening parenthesis "[". To include the literal character "-", it must be written first or last. for [] Any character that does not have any special meaning defined internally, only matches itself.
mysql> Select "AxBC" REGEXP "[a-dxyz]"; -> 1 (matching)
mysql> Select "AxBC" REGEXP "^[a-dxyz]$"; -> 0 (indicates no match)
mysql> Select "AxBC" REGEXP "^[a-dxyz] $"; -> 1 (matching)
mysql> Select "AxBC" REGEXP "^[^a-dxyz] $"; -> 0 (indicates no match)
mysql> Select "Gheis" REGEXP "^[^a-dxyz] $"; -> 1 (matching)
mysql> Select "Gheisa" REGEXP "^[^a-dxyz] $"; -> 0 (indicates no match)
[[. characters.]]
Represents the order in which elements are compared. The order of characters within parentheses is unique. However, the parentheses can contain wildcard characters,
So he can match more characters. For example: Regular expressions [[. Ch.]] *c matches the first five characters of CHCHCC
。
[=character_class=]
A class that represents equality and can replace other equal elements in a class, including itself. For example, if O and () are
A member of an equal class, then [[=o=]], [[=]] and [O ()] are completely equivalent.
[: Character_class:]
Inside the brackets, in [: And:] The middle is the name of the character class, which can represent all characters belonging to this class.
The names of the character classes are: alnum, digit, punct, alpha, graph, space, blank, lower, Uppe
R, Cntrl, print and Xdigit
mysql> Select "Justalnums" REGEXP "[[: Alnum:]]"; -> 1 (matching)
mysql> Select "!!" REGEXP "[[: Alnum:]]"; -> 0 (indicates no match)
Alnum literal numeric character
Alpha literal characters
Blank whitespace characters
Cntrl control characters
Digit numeric characters
Graph graphic Characters
Lower lowercase text characters
Print graphic or space character
Punct punctuation Characters
Space spaces, tabs, new lines, and carriage returns
Upper uppercase literal characters
Xdigit hexadecimal digit character
[[:;:]]
[[::]]
An empty string that matches the beginning and end of a word, and the beginning and end of the word are not included in the Alnum
Character can not be underlined.
Mysql> Select "A word a" REGEXP "[[: <:]]word[[:>:]]"; -> 1 (matching)
Mysql> Select "A Xword a" REGEXP "[[: <:]]word[[:>:]]"; -> 0 (indicates no match)
Mysql> Select "Weeknights" REGEXP "^ (Wee|week) (knights|nights) $"; -> 1 (indicates
Match
To use a literal instance of a special character in a regular expression, precede it with 2 backslash "\" characters. The MySQL resolver is responsible for interpreting one of these, 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