MySql basic query, connection query, subquery, and regular expression query _ MySQL

Original works of Lin Bingwen Evankaka. For more information, see http: blogcsdnnetevankaka. Data Query refers to obtaining the required data from the database. Data query is the most common and important operation in database operations. You can use different query methods based on your data requirements. You can obtain different data through different query methods. MySQL uses the SELECT statement to query data. The content in this chapter includes.

1. basic syntax of the query statement
2. query data on a single table
3. use aggregate functions to query data
4. multi-table joint query
5. subquery
6. merge query results
7. alias for tables and fields
8. query using regular expressions

What is query?

How to check it?

Prepare the data as follows:

Create table STUDENT (STU_ID int primary KEY, STU_NAME char (10) not null, STU_AGE smallint unsigned not null, STU_SEX char (2) not null); insert into STUDENT values (2001, 'Wang ', 13, 'male'); insert into STUDENT values (2002, 'Ming', 12, 'male'); insert into STUDENT values (2003, 'red ', 14, 'female '); insert into STUDENT values (2004, 'xiaoao', 13, 'female'); insert into STUDENT values (2005, 'tian'er ', 15, 'male'); insert into STUDENT values (2006, 'akel', 13, 'female '); insert into STUDENT values (2007, 'cat', 16, 'male'); insert into STUDENT values (2008, 'agou', 17, 'male'); insert into STUDENT values (2009, 'Sunday', 14, 'male'); insert into STUDENT values (2010, 'xiaoyu ', 13, 'female'); insert into STUDENT values (2011, 'head', 13, 'femal'); insert into STUDENT values (2012, 'bingbing ', 14, 'female'); insert into STUDENT values (2013, 'beautiful', 13, 'femal'); insert into STUDENT values (2014, 'shile ', 12, 'male'); insert into STUDENT values (2015, 'Day 5', 13, 'male'); insert into STUDENT values (2016, 'sous', 11, 'male'); insert into STUDENT values (2017, 'agout', 13, 'male'); insert into STUDENT values (2018, 'ajel', 13, 'male'); insert into STUDENT values (2019, 'abao', 13, 'Female '); insert into STUDENT values (2020, 'Kingdom', 14, 'male ');

This is the student partition table, where foreign key constraints are defined.

create table GRADE(STU_ID INT NOT NULL,STU_SCORE INT,foreign key(STU_ID) references STUDENT(STU_ID));insert  into GRADE values(2001,90);insert  into GRADE values(2002,89);insert  into GRADE values(2003,67);insert  into GRADE values(2004,78);insert  into GRADE values(2005,89);insert  into GRADE values(2006,78);insert  into GRADE values(2007,99);insert  into GRADE values(2008,87);insert  into GRADE values(2009,70);insert  into GRADE values(2010,71);insert  into GRADE values(2011,56);insert  into GRADE values(2012,85);insert  into GRADE values(2013,65);insert  into GRADE values(2014,66);insert  into GRADE values(2015,77);insert  into GRADE values(2016,79);insert  into GRADE values(2017,82);insert  into GRADE values(2018,88);insert  into GRADE values(2019,NULL);insert  into GRADE values(2020,NULL);

I. basic syntax of query statements

Querying data is to obtain the required data from a database data table or view. in MySQL, you can use the SELECT statement to query data. The database system will find different data based on different query conditions.

The basic syntax format of the SELECT statement is as follows:

SELECT attribute list FROM table name or view list [WHERE condition expression 1] [group by attribute name 1 [HAVING condition expression 2] [order by attribute name 2 [ASC | DESC]

Attribute List: name of the field to be queried. Table name or view list: lists the data tables or views to be queried. there can be multiple tables or views. Condition expression 1: Set the query conditions. Attribute name 1: Group by data in this field. Conditional expression 2: indicates that only data that meets the expression can be output. Attribute 2: sort by the data in the field. the sorting method is specified by ASC or DESC parameters. ASC: indicates sorting in ascending order. That is, the values are arranged in ascending order. This is the default parameter. DESC: sort in descending order. That is, the values are arranged in ascending order.

If a WHERE clause exists, query based on the conditions specified by condition expression 1. if no WHERE clause exists, query all records.

If a group by clause exists, the GROUP is based on the field specified BY "attribute name 1". if the group by clause is followed BY the HAVING keyword, only records that meet the conditions specified in condition expression 2 can be output. The group by clause is usually used together with aggregate functions such as COUNT () and SUM.

If an order by clause exists, the system sorts the fields specified BY "attribute name 2. The sorting method is specified by ASC or DESC parameters. The default sorting method is ASC.

2. query data 2.1 on a single table and query all fields

select * from STUDENT;

2.2 query by conditions

(1) comparison operators >,<, = ,! = (<>), >=, <=

select * from STUDENT where STU_AGE>13;

In (v1, v2.. vn), conforming to v1, v2, and vn can be detected
The IN keyword can be used to determine whether the value of a field is IN a specified set. If the value of a field meets the query conditions in the set, the record is queried. If not in the collection, the query conditions are not met. The syntax rules are as follows: [NOT] IN (element 1, element 2 ,..., Element n)

select * from STUDENT where STU_AGE in(11,12);

Between v1 and v2 are between v1 and v2 (including v1 and v2)
The between and keyword can be used to determine whether the value of a field is within the specified range. If the field value is within the specified range, the query condition is met and the record is queried. If it is not within the specified range, the query conditions are not met. The syntax rules are as follows:
[NOT] BETWEEN value 1 AND value 2

select * from STUDENT where STU_AGE between 13 and 15;

(2) logical operators Not (! ) Non-logical

select * from STUDENT where STU_AGE NOT IN(13,14,16);

Or (|) logic or
The OR keyword can also be used to join multiple conditions for query, but it is different from the AND keyword. When the OR keyword is used, as long as one of these query conditions is met, such records will be queried. If none of these query conditions is met, such records will be excluded. OR keyword syntax rules are as follows:
Conditional expression 1 OR conditional expression 2 [... OR condition expression n]
OR can be used to connect two conditional expressions. In addition, multiple OR keywords can be used at the same time to connect more conditional expressions.

select * from STUDENT where STU_ID<2005 OR STU_ID>2015;

And (&) logic and
The AND keyword can be used to join multiple conditions for query. When the AND keyword is used, only records that meet all query conditions are queried. If one of these query conditions is not met, such records will be excluded. AND keyword syntax rules are as follows:
Conditional expression 1 AND conditional expression 2 [... AND condition expression n]
AND can be used to connect two conditional expressions. In addition, multiple AND keywords can be used at the same time to connect more conditional expressions.

(3) Fuzzy search for like images
The LIKE keyword can match strings to determine whether they are equal. If the field value matches the specified string, the query condition is met and the record is queried. If it does not match the specified string, the query conditions are not met. The syntax rules are as follows: [NOT] LIKE 'string'
"NOT" is an optional parameter. the addition of "NOT" indicates that the specified string does NOT match the specified string. "string" indicates that the specified string is used for matching. the string must be enclosed by single quotation marks or double quotation marks.

Wildcard: % Arbitrary characters

Select * from STUDENT where STU_NAME LIKE '% King ';

Matches any string ending with a king.

Select * from STUDENT where STU_NAME LIKE 'A % ';

Indicates matching any

_ Single characterFor example, insert

Insert into STUDENT values (2021, 'No mirror in the world ', 14, 'male ');

Then

Select * from STUDENT where STU_NAME LIKE '_ Lower _';

The query result is null.

However, if two _ symbols are added to the end

Select * from STUDENT where STU_NAME LIKE '_ Lower __';

The query result is not empty.

The value of the "string" parameter can be a complete string, or a wildcard character containing the percent sign (%) or underscore. There is a big difference between the two
"%" Can represent strings of any length, and the length can be 0;
"_" Can only represent a single character.
If you want to match a record with only two characters, two "_" symbols must be followed by the word "Zhang. Because one Chinese character is two characters, and one "_" symbol can only represent one character.
(4) null query

The is null keyword can be used to determine whether the field value is null ). If the field value is null, the query condition is met and the record is queried. If the field value is not a null value, the query conditions are not met. The syntax rules are as follows:
IS [NOT] NULL
"NOT" is an optional parameter, and "NOT" indicates that the condition is met when the field is NOT null.
Is null is a whole and cannot be replaced with "= ".

3. use aggregate functions to query data 3.1 and group by groups

As follows:

select * from STUDENT group by STU_SEX;

If no conditions are added, the first entry of each group is used.

To view the group content, add groub_concat

select STU_SEX,group_concat(STU_NAME) from STUDENT group by STU_SEX;

3.2. generally, group must be used with statistical functions (aggregate functions) to make sense.

Prepare some data first:

Create table EMPLOYEES (EMP_NAME CHAR (10) not null, EMP_SALARY INT unsigned not null, EMP_DEP CHAR (10) not null); insert into EMPLOYEES values ('Wang ', 5000, 'sales department '); insert into EMPLOYEES values ('aow', 6000, 'sales department'); insert into EMPLOYEES values ('Work is no', 7000, 'sales department '); insert into EMPLOYEES values '); insert into EMPLOYEES values ('Born family ', 5500, 'resource'); insert into EMPLOYEES values ('flathflower', 14500, 'resource '); insert into EMPLOYEES values ('dayay', 15000, 'R & D department '); insert into EMPLOYEES values ('entries', 12000, 'R & D department '); insert into EMPLOYEES values ('dump', 13000, 'R & D department '); insert into EMPLOYEES values (' I am a genius ', 15000, 'R & D department '); insert into EMPLOYEES values ('Speechless ', 6000, 'Audit data'); insert into EMPLOYEES values ('who?', 5000, 'Audit data '); insert into EMPLOYEES values ('Unknown ', 4000, 'Audit authority ');

Five statistical functions in mysql:
(1) max: calculate the maximum value.
Calculate the highest salary for each department:

select EMP_NAME,EMP_DEP,max(EMP_SALARY) from EMPLOYEES group by EMP_DEP;

(2) min: minimum value

Calculate the highest salary for each department:

select EMP_NAME,EMP_DEP,min(EMP_SALARY) from EMPLOYEES group by EMP_DEP;

(3) sum: sum

Sum of salaries for each department:

select EMP_DEP,sum(EMP_SALARY) from EMPLOYEES group by EMP_DEP

(4) avg: calculate the average value.

Average salary of each department

select EMP_DEP,avg(EMP_SALARY) from EMPLOYEES group by EMP_DEP;

(5) count: calculates the total number of rows.
Number of people whose salaries are higher than a certain amount in each department

select EMP_DEP,count(*) from EMPLOYEES where EMP_SALARY>=500 group by EMP_DEP;

3.3 Use the having Statement to filter grouped data for the groub by field HAVING with conditions.

The having clause is used to filter groups that meet the conditions. that is, data is filtered after the group. conditions often contain clustering functions and the having condition is used to display specific groups, you can also use multiple grouping standards for grouping.
The having clause is limited to columns and aggregate expressions defined in the SELECT statement. Generally, you need to repeat the aggregate function expression in the HAVING clause to reference the aggregate value, just as you did in the SELECT statement.

select EMP_DEP,avg(EMP_SALARY),group_concat(EMP_NAME)from EMPLOYEES  group by EMP_DEP HAVING  avg(EMP_SALARY) >=6000;

Search for departments with an average salary greater than 6000 and list all the employees in the departments

4. multi-table joint query

Multi-table join queries are classified into internal connection queries and external connection queries.

(1) implicit injoin query

select STUDENT.STU_ID,STUDENT.STU_NAME,STUDENT.STU_AGE,STUDENT.STU_SEX,GRADE.STU_SCORE from STUDENT,GRADE WHERE STUDENT.STU_ID=GRADE.STU_ID AND GRADE.STU_SCORE >=90;

Search for student information greater than 90:

(2) explicit internal connection query

select STUDENT.STU_ID,STUDENT.STU_NAME,STUDENT.STU_AGE,STUDENT.STU_SEX,GRADE.STU_SCORE from STUDENT inner join GRADE on STUDENT.STU_ID=GRADE.STU_ID AND GRADE.STU_SCORE >=90;

Usage: select... from Table 1 inner join Table 2 on conditional expression

(3) external connection query

Left join. left join query.

Usage: select... from Table 1 left join Table 2 on conditional expression

It means that the data found in table 1 cannot be null, but the data corresponding to Table 2 can be null.

select STUDENT.STU_ID,STUDENT.STU_NAME,STUDENT.STU_AGE,STUDENT.STU_SEX,GRADE.STU_SCORE from STUDENT left join GRADE on STUDENT.STU_ID=GRADE.STU_ID;

Right join is the opposite, with the same usage

When left join is used, information in the left table of the left join operator will be queried, and records not found in the right table will be blank (NULL ). right join is also true. when inner join is performed, only suitable conditions are displayed.

Full join ()
The complete external join operation returns all rows in the left and right tables. If a row does not match a row in another table, the selection list column of the other table contains a null value. If there are matched rows between tables, the entire result set row contains the data of the base table.
Value.
Rows are returned only when at least one row in the same two tables meets the join conditions. The inner join removes rows that do not match any row in the other table. The outer join will return at least one table or
All rows in the view, as long as these rows meet any WHERE or HAVING search conditions. Searches all rows in the left table referenced by the left outer join and all rows in the right table referenced by the right outer join. Complete
All rows of the two tables in the join operation are returned.

5. subquery

Use the select result of one query as the condition of another query

Syntax: select * from Table 1 wher condition 1 (select .. from Table 2 where condition 2)

1. Integration with In

select * from STUDENT where STU_ID IN(select STU_ID from GRADE where STU_SCORE>85);

Search for student information greater than 85

2. Integration With EXISTS

The EXISTS and not exists operators only test whether a subquery returns data rows. If yes, EXISTS will be true, not exists will be false.

select * from STUDENT where  EXISTS (select STU_ID from GRADE where STU_SCORE>=100);

If a student scores more than 100, all student information is queried.

3. ALL, ANY, and SOME subqueries

The common use of any and all operators is to test the results of a data column subquery by combining a relative comparison operator. They test whether the comparison value matches all or part of the values returned by the subquery. For example, if the compare value is less than or equal to each value returned by the subquery, <= all is true, as long as the compare value is less than or equal to any value returned by the subquery, <= any will be true. Some is a synonym of any.

select STU_ID from GRADE where STU_SCORE <67;

As long as the student ID is greater than any of the above, it is displayed:

select * from STUDENT where STU_ID >= any (select STU_ID from GRADE where STU_SCORE <67);

6. merge query results

Merging query results combines the query results of multiple SELECT statements. In some cases, you need to combine the query results of several SELECT statements to display them.
When the UNION keyword is used, the database system merges all query results and removes the same records. The union all keyword is simply merged. The syntax rules are as follows:

SELECT statement 1 UNION | union allselect statement 2 UNION | union all .... SELECT statement n;

VII. sorting and fetch 7.1, order

(1) order by price // Sort by default in ascending order
(2) order by price desc // sort in descending order
(3) order by price asc // sort in ascending order, the same as the default
(4) order by rand () // random arrangement with low efficiency

select * from GRADE where STU_SCORE >80 order by STU_SCORE;

The default value is in ascending order,

It can also be written in this way.

select * from GRADE where STU_SCORE >80 order by STU_SCORE ASC;

The result is as follows:

If you want to change to a descending order:

select * from GRADE where STU_SCORE >80 order by STU_SCORE desc;

7.2. limit

Limit [offset,] N
Offset. optional. it is equivalent to limit 0, N
N retrieve entries

Top 5 with the highest score

select * from GRADE order by STU_SCORE desc limit 5;

Take the first 5 with the lowest scores

select * from GRADE order by STU_SCORE asc limit 5;

Take 5 scores in the top 10-15

select * from GRADE order by STU_SCORE desc limit 10,5

8. alias for tables and fields

Use AS to name columns

Select STU_ID as 'student id ', STU_SCORE as 'score' from GRADE;

When the table name is too long, it is inconvenient to directly use the table name in the query. In this case, you can obtain an alias for the table. Replace the table name with this alias.
The basic form of getting an alias for a table in MySQL is as follows:
Table name table alias

select S.STU_ID,S.STU_NAME,S.STU_AGE,S.STU_SEX,G.STU_SCORE from STUDENT S,GRADE G WHERE S.STU_ID=G.STU_ID AND G.STU_SCORE >=90;

9. query using regular expressions

Regular expressions are a way to match a type of strings in a certain pattern. For example, you can use A regular expression to query strings containing any letter in A, B, and C. The query capability of regular expressions is more powerful and flexible than that of wildcard characters. Regular expressions can be applied to very complex queries.
In MySQL, the REGEXP keyword is used to match the query regular expression. The basic form is as follows:
Property name REGEXP 'matching method'

Insert some data before use:

Insert into STUDENT values (2022, '12wef', 13, 'male'); insert into STUDENT values (2023, 'faf _ 23', 13, 'male '); insert into STUDENT values (2024, 'faf', 13, 'femal'); insert into STUDENT values (2025, 'ooop ', 14, 'male '); insert into STUDENT values (2026, '23oop ', 14, 'male'); insert into STUDENT values (2027, 'woop89', 14, 'male '); insert into STUDENT values (2028, 'abcd', 11, 'male ');

(1) the character "^" can be used to match records starting with a specific character or string.

Query all headers

Select * from STUDENT where STU_NAME REGEXP '^ ';

Start with a number

select * from STUDENT where STU_NAME REGEXP '^[0-9]';

(2) use the character "$" to match records ending with a specific character or string

End with a number

select * from STUDENT where STU_NAME REGEXP '[0-9]$';

(3) when using a regular expression for query, you can use "." to replace any character in the string.

select * from STUDENT where STU_NAME REGEXP '^w....[0-9]$';

Start with w and end with a number. There are 4 in the middle

(4) square brackets ([]) can be used to form a character set. As long as the record contains any character in square brackets, the record will be queried.
For example, you can use [abc] to query any one of the three letters a, B, and c.

Square brackets can be used to specify the range of a set.
"[A-z]" indicates all letters from a-z;
"[0-9]" indicates all numbers from 0-9;
"[A-z0-9]" represents containing all lowercase letters and numbers.
"[A-zA-Z]" indicates that all letters are matched.

select * from STUDENT where STU_NAME REGEXP '[0-9a-z]';

Query all numbers and lowercase letters

You can use [^ character set combination] to match characters other than the specified character set.

(5) {} indicates the number of occurrences

In a regular expression, "string {M}" indicates that the string appears M times consecutively. "string {M, N}" indicates that the string can appear at least M times consecutively, up to N times. For example, "AB {2}" indicates that the string "AB" appears twice in a row. "AB {2, 4}" indicates that the string "AB" appears at least twice consecutively, up to four times.

O appears twice

select * from STUDENT where STU_NAME REGEXP 'o{2}';

(6) + indicates that only once appears

Fa appears at least once

select * from STUDENT where STU_NAME REGEXP '(fa)+';

Note:

Regular expressions can match strings. When the record in the table contains this string, the record can be queried. If multiple strings are specified, separate them with the '|' symbol. Match any of these strings. No space is allowed between each string and '|. Because, during the query process, the database system treats spaces as one character. In this way, the desired results cannot be queried.
In a regular expression, "*" and "+" can both match multiple characters before the symbol. However, "+" represents at least one character, while "*" can represent zero characters.