Detailed Mysql basic query, connection query, subquery, regular expression query _mysql

Query data refers to obtaining the required data from the database. Query data is the most commonly used and most important operation in database operations. Users can use different query methods according to their needs for the data. Different data can be obtained through different query methods. In MySQL, a SELECT statement is used to query the data. The content to be covered in this chapter.

1, the basic syntax of the query statement
2. Query data on a single table
3. Use aggregate function to query data
4, multiple tables on the joint query
5, sub-query
6. Combined Query Results
7. Aliases for tables and fields
8. Use Regular expression query

What is a query?

How did you find it?

The data are prepared as follows:

CREATE TABLE STUDENT (stu_id int primary KEY, stu_name char not NULL, stu_age smallint unsigned not NULL, stu_sex 
char (2) not NULL); 
INSERT INTO STUDENT values (2001, ' Xiao Wang ', 13, ' Male '); 
INSERT into STUDENT values (2002, ' Mingming ', 12, ' Male '); 
INSERT into STUDENT values (2003, ' Red Red ', 14, ' female '); 
INSERT into STUDENT values (2004, ' Floret ', 13, ' female '); 
INSERT into STUDENT values (2005, ' Day Son ', 15, ' Male '); 
INSERT into STUDENT values (2006, ' Hunter ', 13, ' female '); 
INSERT into STUDENT values (2007, ' Cat ', 16, ' Male '); 
INSERT into STUDENT values (2008, ' A dog ', 17, ' Male '); 
INSERT into STUDENT values (2009, ' sunspots ', 14, ' Male '); 
INSERT into STUDENT values (2010, ' Little Jade ', 13, ' female '); 
INSERT into STUDENT values (2011, ' head ', 13, ' female '); 
INSERT into STUDENT values (2012, ' Ice Ice ', 14, ' female '); 
INSERT into STUDENT values (2013, ' beautiful ', 13, ' female '); 
INSERT into STUDENT values (2014, ' divine Music ', 12, ' Male '); 
INSERT into STUDENT values (2015, ' Day five ', 13, ' Male '); 
INSERT into STUDENT values (2016, ' N ', 11, ' Male '); 
INSERT into STUDENT values (2017, ' Ah Zhang ', 13, ' Male '); 
INSERT into STUDENT values (2018, ' Jie ', 13, ' Male '); Insert inTo STUDENT values (2019, ' Po ', 13, ' female ');
 INSERT into STUDENT values (2020, ' King ', 14, ' Male ');

Then this is the Student score table, which defines a foreign key constraint

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); 

The basic syntax of a query statement

Query data is the data you need from a datasheet or view in a database, and in MySQL, you can use a SELECT statement to query the data. Depending on the query criteria, the database system will find different data.
The basic syntax format for the SELECT statement is as follows:

SELECT Property list from 
 table name or view list 
 [WHERE condition expression 1] 
 [GROUP by property name 1 [having conditional expression 2]] 
 

List of properties: Represents the name of a field that needs to be queried.
Table name or view list: A table or view that represents a data query that is about to be queried, and can have multiple tables or views.
Conditional expression 1: Sets the criteria for the query.
Property Name 1: Represents grouping by data in this field.
Conditional expression 2: Represents the data that satisfies the expression to output.
Property 2: Specifies that the data in the field is sorted by the ASC or DESC parameter.
ASC: Represents sorting in ascending order. That is, the values are arranged in the order of the small to the large. This is the default parameter.
DESC: Represents sorting in descending order. That means that the values are arranged in order from large to small.

If there is a WHERE clause, the query is based on the criteria specified by conditional expression 1, and if there is no WHERE clause, all records are queried.
If there is a GROUP BY clause, the fields specified by property name 1 are grouped, and if the GROUP BY clause is followed by the HAVING keyword, only records that meet the criteria specified in conditional expression 2 can be exported. The GROUP by clause is typically used with aggregate functions such as count (), SUM ().
If you have an ORDER BY clause, sort by the field specified by property name 2. The sort method is specified by the ASC or DESC parameter. The default sort method is ASC.

Second, query data on a single table

2.1. Query all Fields

Copy Code code as follows:

SELECT * from STUDENT;

2.2, according to the conditions of inquiry

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

In (V1,v2. VN), in line with v1,v2,,, VN can be detected
The In keyword can determine whether a field's value is in the specified collection. If the value of the field is in the collection, the query condition is met and the record is queried. If it is not in the collection, the query condition is not met. The syntax rules are as follows: [NOT] in (element 1, Element 2, ..., element N)

Between V1 and V2 between V1 to V2 (including V1,V2)
The BETWEEN and keyword can interpret whether a field's value is within a specified range. If the value of the field is within the specified range, the query condition is met and the record is queried. If it is not in the specified range, the query condition is not met. The syntax rules are as follows:
[NOT] BETWEEN value 1 and take value 2

SELECT * from STUDENT where stu_age between and 15; 

(2) Logical operators

Not (!) logical

or (| |) logic OR
The OR keyword can also be used to combine multiple criteria for querying, but unlike the and keyword. When the or keyword is used, such a record will be queried as long as one of these query criteria is met. If any of these query criteria are not met, such records will be excluded. The syntax rules for the OR keyword are as follows:
Conditional expression 1 OR condition expression 2 [... An OR conditional expression n]
Where or can be used to connect two conditional expressions. Also, you can use multiple or keywords at the same time, so you can connect more conditional expressions.

and (&&) logic and
The and keyword can be used to combine multiple criteria for querying. When the and keyword is used, only records that satisfy all of the query criteria are queried. If one of these query criteria is not met, such records will be excluded. The syntax rules for the AND keyword are as follows:
Conditional expression 1 and condition Expression 2 [...] An and conditional expression N]
Where and can connect two conditional expressions. Also, multiple and keywords can be used at the same time, so that more conditional expressions can be connected.

(3) Fuzzy query

Like

The LIKE keyword can match whether strings are equal. If the value of the field matches the specified string, the query condition is met and the record is queried. If the specified string does not match, the query condition is not met. The syntax rules are as follows: [NOT] like ' string ' not ' optional parameter, plus not to match the specified string mismatch; string represents a string that specifies a match that must be enclosed in single or double quotes.

Wildcard characters:

% any character

That matches any of the king's endings.

That matches any of the beginning of a

_ Single Character

such as inserting

And then

The result of the query is empty

But if you add a two _ symbol to the back

Query result is not empty

The value of the string argument can be either a complete string or a wildcard character that contains a percent semicolon (%) or an underscore (_). There's a big difference.
"%" can represent any length of string, which can be 0 in length;
' _ ' can only represent a single character.
If you want to match the record of a person whose last name is only two words, you must have two "_" symbols after the word "Zhang". Because one character is two characters, and an "_" symbol can only represent one character.

(4) Null value query

The IS NULL keyword can be used to determine whether a field's value is a null value (NULL). If the value of the field is a null value, the query condition is met and the record is queried. If the value of a field is not a null value, the query condition is not met. The syntax rules are as follows:
is [NOT] NULL
Where "not" is an optional parameter, plus not the condition is satisfied when the field is not a null value.
Is null is a whole and cannot be replaced by "=".

Querying data using aggregate functions

3.1, GROUP by group
As follows:

Without conditions, then only the first article of each group is taken.

If you want to see the content of the group, you can add Groub_concat

Select Stu_sex,group_concat (stu_name) from STUDENT Group by Stu_sex;

3.2. In general, the group needs to be used together with statistical functions (aggregate functions) to make sense
prepare some data first:

CREATE TABLE EMPLOYEES ( 
emp_name char () NOT NULL, 
emp_salary INT unsigned not NULL, 
EMP_DEP char (a) not NUL L 
); 
INSERT into EMPLOYEES values (' Xiao Wang ', 5000, ' sales department '); 
INSERT into EMPLOYEES values (' Ah Xiao Wang ', 6000, ' sales department '); 
INSERT into EMPLOYEES values (' work is not ', 7000, ' sales department '); 
INSERT into EMPLOYEES values (' Everyone le ', 3000, ' resources department '); 
INSERT into EMPLOYEES values (' Full head big ', 4000, ' resources department '); 
INSERT into EMPLOYEES values (' Born One ', 5500, ' resources department '); 
INSERT into EMPLOYEES values (' Floret ', 14500, ' Resource Department '); 
INSERT into EMPLOYEES values (' Big jade ', 15000, ' Research and Development Department '); 
INSERT into EMPLOYEES values (' All stripes ', 12000, ' Research and Development Department '); 
INSERT into EMPLOYEES values (' stupid ', 13000, ' Research and Development Department '); 
INSERT into EMPLOYEES values (' I'm a genius ', 15000, ' R & D '); 
INSERT into EMPLOYEES values (' No language ', 6000, ' audit department '); 
INSERT into EMPLOYEES values (' Who ', 5000, ' audit Department '); 
INSERT into EMPLOYEES values (' Don't know ', 4000, ' audit department '); 

Five kinds of statistical functions in MySQL:
(1) Max: Find the maximum value
Ask the highest salary in each department:

Select Emp_name,emp_dep,max (emp_salary) from EMPLOYEES Group by EMP_DEP;

(2) Min: Find the minimum value
Ask for the maximum salary per department:

Select Emp_name,emp_dep,min (emp_salary) from EMPLOYEES Group by EMP_DEP; 

(3) Sum: Find Total and
Ask for the sum of each department's salary:

Select Emp_dep,sum (emp_salary) from EMPLOYEES GROUP by EMP_DEP 

(4) Avg: averaging
Ask the average wage for each department

Select Emp_dep,avg (emp_salary) from EMPLOYEES Group by EMP_DEP; 

(5) Count: Find the total number of rows
The number of people who pay more than a certain amount in each department

Select Emp_dep,count (*) from EMPLOYEES where emp_salary>=500 group by EMP_DEP; 

3.3, with the conditional Groub by field having, filtering the grouped data with the having statement
the HAVING clause is used to filter the groups that meet the criteria, that is, to filter the data after grouping, often including the clustering function, using the having condition to display a specific group, or multiple grouping criteria for grouping.
The HAVING clause is restricted to a column and an aggregate expression defined in the SELECT statement. Typically, you need to refer to an aggregate value by repeating the aggregate function expression in the HAVING clause, as you do in the SELECT statement.

Copy Code code as follows:

Select Emp_dep,avg (emp_salary), Group_concat (Emp_name) from EMPLOYEES GROUP by EMP_DEP having avg (emp_salary) >=6000;

Find a department with an average wage greater than 6000 and list all the people in the department

Four, multiple table on the joint query
the joint query on multiple tables is divided into inner-join query and outer-join query
(1) Implicit in-connection query

Copy Code code as follows:

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;

Find student information that is greater than 90 points:

(2) Explicit in-connection query

Copy Code code as follows:

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.
Usage: Select .... from table 1 left JOIN table 2 on conditional expression
This means that the data found in table 1 cannot be null, but its corresponding table 2 data can be null

Copy Code code as follows:

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, same usage
When you use the LEFT join, the information from the left-hand join operator is queried, and the records that are not in the right table fill in the blanks (NULL). Right join; When inner join, only conditions are displayed.
Full Join ()
A full outer join returns all rows in the left and right tables. When a row does not match rows in another table, the select list column for the other table contains null values. If there is a matching row between the tables, the entire result set row contains data from the base table
Value.
An inner JOIN returns rows only if at least one row that belongs to the two tables conforms to the join condition. An inner join eliminates a row that does not match any row in another table. The outer join returns at least one table mentioned in the FROM clause or
All rows of the view, as long as the rows conform to any WHERE or having search conditions. All rows of the left table referenced by a left-outer join are retrieved, along with all the rows of the right table referenced by the right outer join. Complete outside
All rows of the two tables in the part join are returned.

V. Sub-query
Criteria for another query as a result of a query select
Syntax: SELECT * FROM table 1 wher condition 1 (SELECT. From table 2 Where Condition 2)
1, in combination with

SELECT * from STUDENT where stu_id in (select stu_id from GRADE where stu_score>85); 

Find student information that is greater than 85 points

2. Combined with exists
The exists and not EXISTS operators only test whether a subquery returns rows of data. 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 you have a student score greater than 100, just check all the student information

3. All, any, and some subqueries
the common use of the operator of any and all is to test the results of a data subquery query in conjunction with 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 comparison value is less than or equal to each value returned by the subquery, <=all will be true, and <=any will be true as long as the comparison value is less than or equal to any one of the values returned by the subquery. Some is a synonym for any.

As long as the study number is greater than any one of the above shows:

Copy Code code as follows:

SELECT * from STUDENT where stu_id >= any (select stu_id from GRADE where Stu_score <67);

Vi. Combined Query Results
The result of a merged query is the merging of the query results from multiple SELECT statements. Because in some cases, the results of queries from several SELECT statements are merged to display.
When using the Union keyword, the database system merges all the query results together and then removes the same records. The UNION ALL keyword is simply merged together. The syntax rules are as follows:

SELECT statement 1 
UNION | UNION ALL 
SELECT statement 2 
Union | UNION all .... 
SELECT statement n;

Vii. sorting and number-taking
7.1. ORDER BY
(1) Order BY price//default ascending arrangement
(2) Order BY price desc//Descending order
(3) Order BY price ASC//Ascending, as the default
(4) Order by RAND ()//random, inefficient

SELECT * from GRADE where Stu_score >80 order by Stu_score; 

The default is in ascending order,
I can write that.

SELECT * from GRADE where Stu_score >80 order by Stu_score ASC; 

The results are as follows:

If you want to change to a descending:

7.2, limit

Limit [offset,] N
Offset offsets, optional, not written is equivalent to limit 0,n
N Remove Entry
Take the top 5 of the highest score

Take the top 5 with the lowest score

SELECT * from GRADE ORDER by stu_score ASC limit 5; 

Take the score ranked between 10-15 of the 5

Eight, alias tables and fields

Use as to name columns

When the name of a table is particularly long, it is inconvenient to use the table name directly in the query. You can then take an alias for the table. Use this alias to replace the name of the table.
The basic form of aliases for tables in MySQL is as follows:
Alias of table Name table

Copy Code code as follows:

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;

Nine, use regular expression query

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

Insert some data before you use it:

INSERT into STUDENT values (2022, ' 12wef ', 13, ' Male '); 
INSERT into STUDENT values (2023, ' faf_23 ', 13, ' Male '); 
INSERT into STUDENT values (2024, ' Fafa ', 13, ' female '); 
INSERT into STUDENT values (2025, ' Ooop ', 14, ' Male '); 
INSERT into STUDENT values (2026, ' 23oop ', 14, ' Male '); 
INSERT into STUDENT values (2027, ' woop89 ', 14, ' Male '); 

(1) Use the character "^" to match records that begin with a particular character or string.
query for all the Attou

Start with a number

SELECT * from STUDENT where Stu_name REGEXP ' ^[0-9] ';

(2) Use the character "$" to match a record at the end of a particular character or string
End With number

(3) When querying with regular expressions, you can use the "." To replace any one of the characters in the string.

Start with W, End with number, 4 in the middle

(4) Use square brackets ([])

You can make a character set that requires query characters. The record will be queried as long as the record contains any characters in the square brackets.
For example, through [ABC], you can query for records that contain any of the three letters A, B, and C.

Use square brackets to specify the range of the collection.
"[A-z]" means all letters from a-Z;
"[0-9]" means all numbers from 0-9;
"[A-z0-9]" indicates that all lowercase letters and numbers are included.
"[A-za-z]" indicates that all letters are matched.
SELECT * from STUDENT where Stu_name REGEXP ' [0-9a-z] ';
Queries all containing numbers and lowercase letters.

Use [^ character set] to match characters other than the specified character

(5) {} Indicates the number of occurrences

In a regular expression, "string {M}" indicates that the string appears consecutively for m times; "String {m,n}" indicates that the string is contiguous with at least m times, up to N times. For example, "Ab{2}" indicates that the string "AB" appears two consecutive times. "ab{2,4}" indicates that the string "AB" appears at least two times, up to four times.
O appeared 2 times

(6) + to show less than once
The FA appears at least once

SELECT * from STUDENT where Stu_name REGEXP ' (FA) + '; 

Attention:
Regular expressions can match strings. When a record in a table contains this string, you can query the record. If you specify more than one string, you need to use the symbol "|" Separated. Just match any one of these strings. Each string and "|" Cannot have spaces between them. Because, during the query process, the database system treats the spaces as one character. This will not query the desired results.
In regular expressions, "*" and "+" can match more than one character before the symbol. However, "+" represents at least one character and "*" can represent 0 characters.

The above is the entire content of this article, I hope you can enjoy.