• notice
  • Congratulations on the launch of the Sought Tech site

Detailed mysql basic inquiry, connection query, subquery, regular expression query

Query data refers to obtaining the required data from the database.Querying data is the most commonly used and most important operation in database operations.Users can use different query methods according to their data needs.Through different query methods, different data can be obtained.MySQL uses the SELECT statement to query data.The content that will be explained in this chapter includes.

1.Basic syntax of query statement
2.Query data on a single table
3.Use aggregate functions to query data
4.Joint query on multiple tables
5.Subquery
6.Merge query results
7.Aliasing tables and fields
8.Use regular expression query

What is a query?

How did you find it?

Data preparation is 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,'Xiao Wang',13,'Male');
insert into STUDENT values(2002,'Mingming',12,'Male');
insert into STUDENT values(2003,'Honghong',14,'Female');
insert into STUDENT values(2004,'Xiaohua',13,'Female');
insert into STUDENT values(2005,'Tian'er',15,'Male');
insert into STUDENT values(2006,'Ahun',13,'Female');
insert into STUDENT values(2007,'Amao',16,'Male');
insert into STUDENT values(2008,'Agou',17,'Male');
insert into STUDENT values(2009,'Heizi',14,'Male');
insert into STUDENT values(2010,'Xiaoyu',13,'Female');
insert into STUDENT values(2011,'Toutou',13,'Female');
insert into STUDENT values(2012,'Bingbing',14,'Female');
insert into STUDENT values(2013,'beautiful',13,'female');
insert into STUDENT values(2014,'Kagura',12,'Male');
insert into STUDENT values(2015,'Tianwu',13,'Male');
insert into STUDENT values(2016,'Little San',11,'Male');
insert into STUDENT values(2017,'A Zhang',13,'Male');
insert into STUDENT values(2018,'Ajie',13,'Male');
insert into STUDENT values(2019,'Abao',13,'Female');
insert into STUDENT values(2020,'King',14,'Male');

Then here is the student grades table with foreign key constraints 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 refers to obtaining the required data from the data table or view in the database.In MySQL, the SELECT statement can be used to query the data.Depending on the query conditions, the database system will find different data.
The basic syntax of the SELECT statement is as follows:

SELECT attribute list
 FROM table name or view list
 [WHERE conditional expression 1]
 [GROUP BY attribute name 1 [HAVING conditional expression 2]]
 [ORDER BY attribute name 2 [ASC|DESC]] 

Attribute list: Indicates the field name to be queried.
Table name or view list: Indicates the data table or view to be queried for data.There can be multiple tables or views.
Conditional expression 1: Set the condition of the query.
Attribute name 1: Indicates grouping by the data in this field.
Conditional expression 2: Indicates that only data that satisfies the expression can be output.
Attribute 2: Indicates that the data in this field is sorted, and the sorting method is specified by the ASC or DESC parameter.
ASC: Indicates sorting in ascending order.That means the values ​​are arranged in ascending order.This is the default parameter.
DESC: Indicates sorting in descending order.That means the values ​​are arranged in descending order.

If there is a WHERE clause, query according to the conditions specified by "conditional expression 1"; if there is no WHERE clause, query all records.
If there is a GROUP BY clause, it will be grouped according to the field specified by "attribute name 1"; if the GROUP BY clause is followed by the HAVING keyword, then only records that meet the conditions specified in "conditional expression 2" can be output..The GROUP BY clause is usually used with aggregate functions such as COUNT() and SUM().
If there is an ORDER BY clause, it is sorted according to the field specified by "attribute name 2".The sorting method is specified by the ASC or DESC parameter.The default sorting method is ASC.

Second, query data on a single table

2.1, query all fields

The code is as follows:
select * from STUDENT;


2.2.Query by condition

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

select * from STUDENT where STU_AGE>13; 

in(v1,v2..vn) , only if v1,v2,,,vn can be detected
The IN keyword can determine whether the value of a field is in the specified set.If the value of the field is in the collection, the query condition is satisfied, and the record will be queried.If it is not in the collection, the query condition is not satisfied.Its 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 between v1 and v2 (including v1,v2)
The BETWEEN AND keyword can interpret whether the value of a field is within the specified range.If the value of the field is within the specified range, the query condition is satisfied, and the record will be queried.If it is not within the specified range, the query condition is not satisfied.The grammar 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 (!) logical negation

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


or (||) logical or
The OR keyword can also be used to combine multiple conditions to query, but it is different from the AND keyword.When using the OR keyword, as long as one of these query conditions is satisfied, such records will be queried.If any of these query conditions are not met, such records will be excluded.The syntax rules for the OR keyword are as follows:
conditional expression1 OR conditional expression2 [ …OR conditional expressionn ]
Among them, OR can be used to connect two conditional expressions.Also, multiple OR keywords can be used at the same time, so that more conditional expressions can be connected.

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

and (&&) logical AND
The AND keyword can be used to combine multiple conditions to query.When using the AND keyword, only records that satisfy all query conditions will be queried.If one of these query conditions is not met, such records will be excluded.The syntax rules for the AND keyword are as follows:
conditional-expression1 AND conditional-expression2 [ … AND conditional-expressionn ]
Among them, AND can connect two conditional expressions.Also, multiple AND keywords can be used at the same time, which allows more conditional expressions to be connected.

(3) Fuzzy query

like like

The

LIKE keyword can match strings for equality.If the value of the field matches the specified string, the query condition is satisfied, and the record will be queried.If it does not match the specified string, the query condition is not satisfied.The grammar rules are as follows: [ NOT ] LIKE 'string' "NOT" optional parameter, plus NOT indicates that the condition is met when it does not match the specified string; "string" indicates the specified string to match, the character Strings must be enclosed in single or double quotes.

Wildcard:

% any character

select * from STUDENT where STU_NAME LIKE '%王'; 

matches anything ending in king

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

means match any
starting with A

_single character

For example insert

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

then

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

The result of the query is empty

But if two _ symbols are added after the bottom

select * from STUDENT where STU_NAME LIKE '_下__'; 

The query result is not empty

The value of the "string" parameter can be a complete string, or a wildcard character containing a percent sign (%) or an underscore (_).There is a big difference between the two
"%" can represent a string of any length, the length can be 0;
"_" can only represent a single character.
To match records of people with the last name Zhang and only two characters in their first name, there must be two "_" symbols after the word "Zhang".Because a Chinese character is two characters, and a "_" symbol can only represent one character.

(4) Null value query

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

Third, use aggregate functions to query data

3.1, group by group
As follows:

select * from STUDENT group by STU_SEX; 

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

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

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

3.2.In general, group needs to be used together with statistical functions (aggregation 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('Xiao Wang',5000,'Sales Department');
insert into EMPLOYEES values('A Xiaowang',6000,'Sales Department');
insert into EMPLOYEES values('Work is not',7000,'Sales Department');
insert into EMPLOYEES values('Renrenle',3000,'Resources Department');
insert into EMPLOYEES values('full head', 4000, 'resource department');
insert into EMPLOYEES values('born a family', 5500, 'resources department');
insert into EMPLOYEES values('Xiaohua',14500,'Resources');
insert into EMPLOYEES values('Dayu',15000,'R&D Department');
insert into EMPLOYEES values('bars',12000,'R&D department');
insert into EMPLOYEES values('Benben',13000,'R&D Department');
insert into EMPLOYEES values('I am a genius', 15000, 'R&D department');
insert into EMPLOYEES values('Speechless',6000,'Audit Department');
insert into EMPLOYEES values('Who', 5000,'Audit Department');
insert into EMPLOYEES values('I don't know',4000,'Audit department');

Five statistical functions in mysql:
(1) max: find the maximum value
Find the maximum salary for each department:

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

(2) min: find the minimum value
Find the highest salary for each department:

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

(3) sum: sum of totals
Find the sum of wages for each department:

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

(4) avg: Average
Find the average salary for each department

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

(5) count: find the total number of rows
Find the number of people whose salary is greater than a certain amount in each department

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

3.3.Conditional group by field having, using HAVING statement to filter grouped data
The function of the having clause is to filter the groups that meet the conditions, that is, to filter the data after grouping.The conditions often include a grouping function.Use the having condition to display a specific group, or you can use multiple grouping criteria for grouping.
The having clause is restricted to columns and aggregate expressions that have been defined in the SELECT statement.Typically, you need to refer to aggregated values ​​by repeating the aggregate function expression in the HAVING clause, as you do in the SELECT statement.

The code is as follows:
select EMP_DEP,avg(EMP_SALARY),group_concat(EMP_NAME) from EMPLOYEES group by EMP_DEP HAVING avg(EMP_SALARY) >=6000;

Find departments with an average salary greater than 6000, and list all the people in the department


Fourth, joint query on multiple tables
Joint query on multiple tables is divided into inner join query and outer join query
(1) Implicit inner join query

The code is 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 with a score greater than 90:

(2) Explicit inner join query

The code is 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) Outer join 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

The code is 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, the usage is the same
When using left join, the information in the left table of the left join operator will be queried, and the records that are not in the right table will be filled in (NULL).The same is true for right join; when inner join is used, only if the conditions are suitable show it
full join()
A full outer join returns all rows from the left and right tables.When a row has no matching rows in another table, the select list column of the other table contains null values.If there are matching rows 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 both tables matches the join condition.Inner joins eliminate rows that do not match any rows in another table.while an outer join returns at least one table or
mentioned in the FROM clause All rows of the view as long as those rows match any of the WHERE or HAVING search criteria.All rows of the left table referenced by a left outer join, and all rows of the right table referenced by a right outer join will be retrieved.Complete Outside
All rows from both tables in the partial join will be returned.

V.Subquery
Use the result of one query select as the condition of another query
Syntax: select * from table 1 where condition 1 (select..from table 2 where condition 2)
1.Combine with In

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

Find student information with a score greater than 85

2.Combine with EXISTS
The EXISTS and NOT EXISTS operators only test whether a subquery returns a row of data.If yes, EXISTS will be true and NOT EXISTS will be false.

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

If a student's grade is greater than 100, all student information will be queried

3, ALL, ANY and SOME subqueries
A common use of the any and all operators is to combine a relative comparison operator to test the results of a subquery on a column of data.They test whether the comparison value matches all or some of the values ​​returned by the subquery.For example, <=all will be true if the comparison value is less than or equal to every value returned by the subquery, and <=any will be true as long as the comparison value is less than or equal to any value returned by the subquery.some is a synonym for any.

select STU_ID from GRADE where STU_SCORE <67; 

As long as the student number is greater than any of the above, it will be displayed:

The code is as follows:
select * from STUDENT where STU_ID >=any (select STU_ID from GRADE where STU_SCORE <67);

6.Merge query results
Combining query results is to combine the query results of multiple SELECT statements together.Because in some cases, the results of several SELECT statements need to be combined and displayed.
When using the UNION keyword, the database system will combine all query results together, and then remove the same records.The UNION ALL keywords are simply combined together.The grammar rules are as follows:

SELECT statement 1
UNION | UNION ALL
SELECT statement 2
UNION | UNION ALL ….
SELECT statement n ;

Seven, sorting and fetching
7.1, order by

(1)order by price//default ascending order
(2) order by price desc//sort in descending order
(3) order by price asc//Ascending order, same as default
(4) order by rand()//Random arrangement, not very efficient

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

The default is ascending order,
You can also write

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

The results are as follows:

If you want to change to descending order:

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

7.2, limit

limit [offset,] N
offset offset, optional, if not written, it is equivalent to limit 0,N
N fetch entry
Take the top 5 with the highest score

select * from GRADE order by STU_SCORE desc limit 5; 

Take the top 5 with the lowest score

select * from GRADE order by STU_SCORE asc limit 5;

Take 5 scores between 10-15

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

8.Aliasing tables and fields

Use AS to name columns

select STU_ID as 'Student ID',STU_SCORE as 'Score' from GRADE; 

When the name of the table is particularly long, it is inconvenient to use the table name directly in the query.In this case, you can take an alias for the table.Use this alias in place of the table name.
The basic form of aliasing a table in MySQL is as follows:
table name table alias

The code is 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

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

Insert some data before using:

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');
insert into STUDENT values(2028,'abcdd',11,'male'); 

(1) Use the character "^" to match records starting with a specific character or string.
Search for all

select * from STUDENT where STU_NAME REGEXP '^A'; 

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
ends with a number

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

(3) When using regular expressions to query, you can use "." to replace any character in the string.

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

Start with w, end with number, there are 4 in the middle

(4) Use square brackets ([])

You can combine the required query characters into a character set.As long as the record contains any characters in square brackets, the record will be queried.
For example, "[abc]" can query records that contain any of the three letters a, b, and c.

Use square brackets to specify the range of a collection.
"[a-z]" means all letters from a-z;
"[0-9]" means all numbers from 0-9;
"[a-z0-9]" means all lowercase letters and numbers are included.
"[a-zA-Z]" means match all letters.
select * from STUDENT where STU_NAME REGEXP '[0-9a-z]';
Query all characters containing numbers and lowercase letters

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

(5){} indicates the number of occurrences

In the regular expression, "string {M}" means that the string appears M times in a row; "string {M, N}" means that the string appears at least M times in a row, and at most N times.For example, "ab{2}" means the string "ab" appears twice in a row."ab{2,4}" means that the string "ab" appears at least twice and at most four times in a row.
oAppears 2 times

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

(6)+ means at least once
fa appears at least once

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

Note:
Regular expressions can match strings.When a record in the table contains this string, the record can be queried.If multiple strings are specified, they need to be separated by the symbol "|".Just match any of these strings.There can be no spaces between each string and "|".Because, during the query process, the database system will also treat the space as a character.In this way, the desired result cannot be obtained.
In regular expressions, both "*" and "+" can match multiple characters before the symbol.However, "+" represents at least one character, while "*" can represent zero characters.

The above is the whole content of this article, I hope you like it.

Tags

Technical otaku

Sought technology together

Related Topic

0 Comments

Leave a Reply

+