MySQL Learning note (vii) check (supplement)

PS: Wuyi still want to learn ... Though it's a bit hard. But the road is their own choice, then they will persist to go on ...

Learning content:

1. Database Lookup Supplement ...

Find more things involved, not fully covered in the previous section ... We also take the pet form as an example , here to supplement ....

Pattern matching:

The so-called pattern matching, is simply to follow a pattern to find ... We give a model, and then follow this model to match the completion of the search to belong to the pattern matching find ... In a pattern matching lookup, we are not able to operate with = or! =, and should be manipulated using like or not.

//Find a pet whose name starts with B ...
Select *  fromPetwhereName like 'b%';
//find a pet whose name ends in FY.
Select *  fromPetwhereName like '%fy';
Find a pet with a W in the name ...
Select *  fromPetwhereName like '%w%';

This class belongs to the SQL standard pattern matching: There is another type of matching pattern matching that belongs to the extended regular expression ... The following is a brief introduction:

Extend some characters of the regular expression:

• ‘.‘ Match any single character:
• [...] Matches the characters that are included in the square brackets, which can be either a specified value or a range value: The [ABC] match character can only have ' a ', ' B ', ' C ' as a named range of characters: [A-z] matches a A-Z character between a named range ...
• ' * ' matches characters before 0 or more *:

Attention:

• If the regexp mode matches any of the measured values, then the pattern matches.
• In order to locate a pattern so that it must match the beginning or end of the test value ... The pattern starts with ' ^ ', or ends with ' $ '.

//Find all pet names starting with B ... Case insensitiveSelect *  fromPetwhereName RegExp'^b';//case-sensitive ...Select *  fromPetwhereName RegExpBINARY '^b';//Find the pet name ending in FY ...Select *  fromPetwhereName RegExp'fy$';

Count line: Just use the count (*) function.

Select Count (* from pet; // display the number of rows of database data:

When we want to find some specific data information: You can use the count (*) function to query with GROUP by: For example, when we want to inquire about the data of a pet cat and a dog. We do not need to query the entire table, just use the Count function with group by to query to:

Select Count (*from where species=or species=' cat 'groupby Species,sex;

Some examples of common queries are:

For example, we set up a store database ... There are some items in the store, and each item has a value ...

Mysql>Create Table Shop -( -Articleint(4) unsigned Zerofilldefault '0000'  not NULL, -DealerChar( -)default "'  not NULL, -PriceDouble( -,2)default '0.00'  not NULL, -       Primary Key(Article,dealer) -); MySQL>Insert  intoShopValues(1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),(3,'C','1.69'),(3,'D',1.25),(4,'D',19.95);1the maximum value of the query column ...Select Max(article) asArticle fromShop ;2. Query one row of data for the maximum value of a column ... Two methods:SelectArticle,dealer,price fromShopwherePrice=(Select Max(article) fromShop );SelectArticle,dealer,price fromShopOrder  byPricedesclimit;3the maximum value of the column, unlike the first one, is that the first is to display the maximum value of all items, which is a maximum value for displaying each item.SelectArticleMax(Price) asPrice fromShopOrder  byarticle;4A row that has the largest value between groups in a field ...SelectArticle,dealer,price fromShop S1wherePrice=(Select Max(S2.price) fromShop s2whereS1.article=S2.article);

Fourth the use of temporary tables can also be done ...

CREATE Temporary TABLEtmp (articleINT(4) UNSIGNED ZerofillDEFAULT '0000'  not NULL, PriceDOUBLE( -,2)DEFAULT '0.00'  not NULL); LOCK TABLES articleRead; INSERT  intoTmpSELECTArticle,MAX(Price) fromShopGROUP  byarticle; SELECTArticle, dealer, price fromShop , TmpWHEREShop.article=Tmp.articel andShop.price=Tmp.price;　　　　UNLOCK TABLES; DROP TABLEtmp

This is to read someone else's writing, I did not fully understand. Interested can study ... There is another way:

SELECTarticle,SUBSTRING(MAX(CONCAT (Lpad (Price,6,'0'), dealer),7) asDealer,0.00+left(MAX(CONCAT (Lpad (Price,6,'0'), dealer),6) as Price from ShopGROUP  byarticle;

Use User variables:

If we want to find the highest and lowest price items. And you do not want to save to a temporary variable in the client: We can use User variables ...

MySQL>SELECT@min_price:=min(price),@max_price:=  MAX from shop;mysql>SELECT* from WHERE Price=@min_priceOR Price=@max_price;

The query also uses a foreign key query: Use auto_increment query ... I just know, it's not here swim ...

MySQL Learning note (vii) check (supplement)