MySQL Advanced query (study notes)

Study notes, Source: Experimental building, Link: https://www.shiyanlou.com/courses/9

  First, date calculation:1. To determine how big each pet is, you can use the function Timestampdiff () to calculate the difference between the year of the current date and the date of birth, or you can follow the direct usage statement(Year (Curdate ())-year (birth))calculation, where the functioncurdate ()is to calculate the current date. If the calendar year of the current date is earlier than the birth date, subtract one year. The following code queries each pet's birth date, current date, and age (in years as the unit of calculation), where the keyword age is the label for the calculated result of ages.

SELECT name, birth, Curdate (), Timestampdiff (Year,birth,curdate ()) as age from pet; SELECT name, birth, Curdate (), (Year (Curdate ())-year (birth))-(Right (Curdate (),5) <right (birth,  5

Year ()Extract the year part of the date,Right ()Extracts the MM-DD (month and date) portion of a date to the right of 5 characters. 2. Inquire to determine the age of death of an animal that has died. You determine which animals have died by checking whether the death value is null, and then for those non-null-valued animals, you need to calculate the Chalai between death and birth values to know the time they exist in this world:

SELECT name, birth, Death, (Year (death)-year (birth))-(Right (death,5) <right (birth,5

Query usingdeath is not NULL instead of death! = NULL, because NULL is a special value and cannot be compared by using a common comparer. 3, which animal next month's birthday how to do? For such calculations, the year and day are irrelevant, and you only need to extract the month portion of the birth column. MySQL provides several date-related extraction functions, such asYear (), MONTH ()AndDayOfMonth ()。 Here month () is the function we need. To see how it works, you can run the following simple query to display the values of birth and month (birth):

1 MONTH));

Date_add ()Allows the time interval to be added to a given date. If you add one months to the now () value, and then use month () to extract the month, the next month in the month in which your birthday is 4, another way to complete the task is to add 1 to the current month (using the modulo functionMOD (), if the month's current value is 12, then "return" to the value 0):

 A 1;

Note that month returns a number between 1 and 12, and mod (something,12) returns a number between 0 and 11, so you must add 1 after mod (), otherwise we will jump from November (11) to January (1). second, pattern matching:1. mysql provides standard SQL pattern matching and an extended regular expression pattern matching format based on Unix-like programs such as VI, grep, and sed. SQL pattern matching allows you to use"_" matches any single character, while"%" matches any number of characters (including 0 characters)。 In MySQL, SQL mode defaults to case-insensitive. Some examples are given below. Note When you use SQL mode, you cannot use = or! =, instead you should use a like or not-like comparison operator. To find out the animal information for a name that begins with "B":

' b% ';

To find the name ending with "FY":

' %fy ';

To find the name that contains "W":

' %w% ';

To find a name that exactly contains 5 characters, use the "_" mode character:

' _____ ';

　　

2. Other types of pattern matching provided by MySQL are the use of extended regular expressions. When you perform a match test on this type of pattern, use theREGEXPAndNot REGEXThe P operator (orRlikeAndNot rlike, they are synonyms). Some of the characters that extend the 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 a range of characters, use a "-". "[A-z]" matches any letter, while "[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 characters.

IfREGEXPThe pattern matches anywhere the test value is, the pattern matches (this is different from the like pattern match, and the pattern matches only if it matches the entire value). To locate a pattern so that it must match the beginning or end of the value being tested, use "^" at the beginning of the pattern or "$" at the end of the pattern. To illustrate how an extended regular expression works, use RegExp to rewrite the like query shown above: To find the name starting with "B", use "^" to match the beginning of the name:

REGEXP ' ^b ';

If you want to force regexp 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.

REGEXP BINARY ' ^b '

To find the name ending with "FY", use "$" to match the end of the name:

REGEXP ' fy$ ';

To find the name that contains a "w", use the following query:

REGEXP ' W ';

3, since if a regular expression appears anywhere in the value, he will be matched by the pattern, it is not necessary to put a wildcard on either side of the pattern in the previous query so that it matches the entire value, as if you had used a SQL schema. To find a name that contains exactly 5 characters, use "^" and "$" to match the first and last names, and 5 "." The instance is between the two:

' ^.....$ ';

You can also rewrite the previous query by repeating the n-th operator with "{n}":

' ^. {5}$';

　　

third, calculate the number of rows:
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;

If you want to know how many pets each owner has, you can also use the count (*) function:

Mysql> SELECT owner, COUNT (*) from the pet GROUP by owner;

Note that using GROUP by to group all records for each owner, without it, you get the error message:

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 categorize your data in various forms. The following examples show the operation of animal censuses in different ways. To see the number of animals per species:

Mysql> SELECT species, COUNT (*) from the pet GROUP by species;

See the number of animals per sex:

mysql> SELECT Sex, COUNT (*) from the pet GROUP by sex;

Number of animals by category and sex combination:

mysql> SELECT species, Sex, COUNT (*) from the pet GROUP by species, sex;

If you use COUNT (*), you do not have to retrieve the entire table. For example, when you query only dogs and cats, you should:

' Dog ' ' Cat ' GROUP by species, sex;

Or, if you only need to know the number of sex-grouped animals known by sex:

mysql> SELECT species, Sex, COUNT (*) from pet WHERE sex are not NULL GROUP by species, sex;

　　

Iv. use of more than 1 tablesPetThe table tracks the pets you own. If you want to record other relevant information, such as when they look at a vet or the birth of a descendant, you need a different watch. What should this watch have? It needs to include a pet name so that you know which animal each occurrence belongs to. Need a date so you know when the event happened. You need a field that describes the event. If you want to categorize events, you need an event type field. Sum upEventThe table's CREATE TABLE statement should be:

Event (name varchar,date date, type varchar (), remarkvarchar (255));

Similar to the pet table, the simplest method is to create a text file delimited by a locator to load the initial record: Load the record as follows:

' /home/shiyanlou/desktop/event.txt ' Event

As you are already inPetThe query on the table has learned a certain amount of knowledge and you should be able to performEventThe retrieval of records in a table; the principle is the same. But is there an event table in itself that can't answer the questions you might ask? When pets give birth to a litter of small animals, assume that you want to find out the age of each pet at this time. We saw earlier how to calculate the age by two dates. The event table has a mother's production date, but in order to calculate the age of the mother, you need her date of birth, stored in the pet table. A description query requires two tables:

Mysql> SELECT Pet.name,(year (date)-year (birth))-(Right (date,5) <right (birth,5 ) as age, remarkeventevent' event 'litter ';

There are a few things to note about this query: The FROM clause joins two tables because the query needs to extract information from both tables.when you combine (junction) information from multiple tables, you need to specify the columns in one of the tables to match the column names of the other tables. This is simple because they all have a name column, and the query can match the records in two tables by using the WHERE clause based on the name value. Because the Name column exists in two tables,when referencing the column, be sure to specify which table to attach the table name to before the column nameThat can be achieved. If you want to compare a table's records to other records in that table, you can join the table to itself. For example, in order to choose a breeding spouse among your pets, you can use the pet watch to connect itself to the same kind of male-female pairing:

Mysql> SELECT p1.name, P1.sex, P2.name, P2.sex, P1.species, from Pet as P1, pet as P2'
   
    f
    
    '
    m
    ';
   

In this query, we specify aliases P1 and P2 for the table names so that they can reference their columns and make the references to each column more intuitive. v. Access to database and table information1. 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 provides some statements to solve this problem. You already know that show databases can list all the databases managed by the server. To find out which database is currently selected, use theDATABASE ()Function:

Mysql> SELECT DATABASE ();

If you have not selected any databases, the result isNULL。 2. To find out what table the current database contains (for example, when you cannot determine the name of a table), use this command:

Mysql> SHOW TABLES;

3. If you want to know the structure of a table, you can useDESCRIBEcommand, which displays information for each column in the table:

Mysql> DESCRIBE Pet;

FieldShow column names,TypeIs the data type of the column,NullIndicates whether the column can contain null values,KeyDisplays whether the column is indexed andDefaultSpecifies the default value for the column. If the table has an index, SHOW index from tbl_name generates information about the index.

MySQL Advanced query (study notes)