Zookeeper
This article describes the query statements. All examples are performed according to the following table.
StuID |
StuName |
Age |
Sex |
11090241031 |
Wang Xiaohu |
21 |
Male |
11090241032 |
Wang xiaoliu |
22 |
Male |
11090241033 |
Zhang San |
23 |
Male |
1. Use the where clause to query the comparison queries of records that meet the conditions in the table.
What is commonly used in arithmetic comparison is the so-called >,<>,= ,! <(Not less ),! = (Not equal to). The comparison syntax is as follows:
Select * from T_student where age!> 30
Select * from T_student where stuName <> 'wang xiaoqi'
Note: In the where clause, if numeric data is compared, single quotation marks can be omitted. If it is a string or date, it must be caused by single quotation marks.
Between and
Used to query records within a specified condition range
Select * from T_student where agebetween 3and 45
Note: The query records of between and include two boundary values.
Query records matching the list using the IN Operator
You can use the IN operator to query records matching the list.
Select * from T_student where agein (32, 15)
Note: You can also use the not in operator in SQL statements to exclude some records.
Logical Query
In SQL, logical operators include AND, OR, AND NOT.
AND: records that meet multiple conditions at the same time.
OR: Used to satisfy any of the multiple conditions
NOT: record used to satisfy the inverse Condition
Select * from T_student where agein (30, 15) andstuName = 'wang Xiaohu'
(If one condition is False, the result is not displayed)
Select * from T_student where agein (32, 15) orstuName = 'wang Xiaohu'
(Records can be displayed if any of the conditions is met)
Select * from T_student where agenotin (32, 15)
(Query is a counter-condition record, that is, a record whose age is 32 or 15 is not met)
Complex logic
Complex logic is actually a combination of the AND, OR, and not operators. As we know, operators also have a sequential order.
NOT> AND> OR
Select * from T_student where agenotin () ORstuName = 'wang xiaoliu'
(The query results show that the age is not 32 or 15, or the name is Wang xiaoliu)
Null Value Query
When using an SQL statement, it is sometimes possible that there is no value in this field, so it is called a null value, and the query statement is also very simple.
Select * from T_student where stuNameISNOT NULL
(Query records whose summary names are not null)
Like fuzzy query
Fuzzy search using the LIKE Operator
When using SQL, you may encounter a situation where you cannot fully determine the information you want to search for. However, this information has some obvious characteristics, so you can use the following fuzzy query
Match any single character
The wildcard "_" indicates matching a single character, which can be used in queries.
Select * from T_student where stuName like 'wang _ hu'
(Query Wang * Hu's records)
Matches 0 or multiple characters
The wildcard "%" indicates that the wildcard matches 0 or more characters. That is, one "%" indicates one or more characters.
Select * from T_student where stuName like 'wang %'
(The record of the first character Shi Wang in the name)
Wildcard []
Any single character in the character Column
Select * from T_student where stuName like '[Faye Wong] %'
(Records whose names start with Wang or Fei)
Wildcard character [^]
Represents any single character not in the character Column
Select * from T_student where stuName like '[^ Fei] %'
(Indicates that the name is not a record starting with "already)
Escape characters
What should I do if a field in the data table itself contains wildcards when I use SQL to perform fuzzy queries?
(1): Add an escape character before "%" or "_" to be escaped. The escape character can be any character.
(2): specify the name of the ESCAPE character after the ESCAPE keyword.
Select * from T_student where sutID like '% $ _ %' ESCAPE '$'
(This query statement indicates the records whose IDs contain _. The Escape Character $ has escaped)
Bytes ---------------------------------------------------------------------------------------
2. Grouping Data Tables
Sorting and grouping of data in a table
When we use SQL statements for query operations, the statements that we may query may be irregular, so we can sort the statements to better observe the results we have found.
GROUP
The order by clause can be used to sort the columns specified in the query results in ascending or descending ORDER, depending on the following keywords.
Select * from T_student order by age desc
(Sort the results in descending order)
Note: The order by clause only sorts the final result and cannot be used in the select statement of the select subquery. The position of the order by clause must be placed at the end of all clauses, that is, if the query statement contains multiple clauses, the order by clause must be placed after these clauses.
Common Aggregate functions
The so-called aggregate function is a statistical function, mainly used to sort and evaluate the obtained data.
Function |
Function |
COUNT (column expression) |
Calculates the number of non-null rows in a given column or expression. |
COUNT (*) |
Calculates the total number of rows in a data table, including null values. |
MAX (column expression) |
Calculates the maximum value in a given column or expression. |
MIN (column expression) |
Calculates the minimum value in a given column or expression. |
SUM (column expression) |
Calculates the sum of all given columns or expressions. |
AVG (column expression) |
Calculates the average value of all values in a given column or expression. |
GROUP data in a table using the group by clause
The GROUP by clause can be grouped based on the results of multiple data queries in the data column.
Select age, count (stuname) from t_student whereage <50 group by age order by age
(Group by age and count the number of students of each age group)
Use having to restrict query results after grouping
If you want to restrict query conditions for grouping results, you can use the having Statement, which is placed behind the group by statement.
Select age, count (stuname) from t_student whereage <50 group by age having age> 15 order by age
(The same as above, but the age limit is higher than 15)
Limit the number of rows in the result set
In some cases, we do not want to see all the results, but only display a few rows. Especially in paging operations, it is okay to only care about the first few records, at this time, we need to limit the result set.
Syntax: select top n {precent} column name 1, column name 2 from Table Name
Top: keyword, indicating the keyword that limits the number of rows in the result set
N: Limit the number of rows in the result set.
Precent: returns the first N % of the number of rows in the query result set.
Selecttop 1agefromt_studentwhere age <50
(Only one row is returned)