Single table query

• Basic format

select [ all | distinct ] selection_list

from table_score

[where search_condition]

[group by grouping_colums] [with rollup]

[having search_condition]

[order by order_expression [asc \ desc]] --asc ascending, desc descending

[limit count] - limit the output result line of query

 

SQL select_expr

• Alias a field

  The name as alias of the select item

• Filter records using predicates

  Duplicate rows can be filtered with distinct

  select distinct column

 

SQL where

Many complex conditional queries can be realized through the where clause, and the query conditions need to be determined with operators

Syntax: SELECT column name FROM table name WHERE column operator value

# Examples ##################################################################
#The student number, name and telephone information of students with an enrollment score of more than 800 in the query table student.
select studentno, sname, phone
from student
where entrance > 800;

 

• Query with in keyword

  In keyword can determine whether the value of a field is in the specified set

  Using in search criteria is equivalent to connecting two comparison criteria with or

  For example, x in(10,15) is equivalent to the expression x=10 or x=15

  You can also use not in to query data that is not in the range

# Examples ##################################################################
#Query the student number, course number, usual score and final score of students with student numbers of 123, 456 and 789 respectively.
select studentno, courseno, daily, final
from score
where studentno in ('123','456','789');

 

• Range query with between and

  Using between search criteria is equivalent to connecting two comparison criteria with and

  For example, x between 10 and 27 is equivalent to the expression x > = 10 and x < = 27

  The search condition specifies the value excluding a certain range, which can generally be realized by keyword

# Examples ##################################################################
#Query the student number and final grade of students with elective course No. c05109, and the usual score is required to be 80 ~ 95.
select studentno, final
from score
where courseno='c05109'and daily between 80 and 95;

 

• Character matching query with like

  like can be used to implement fuzzy queries, including two wildcard% and_

  %Can match one or more characters, can represent any length of string, and the length can be 0

  _ Match only one character

# Example ##################################################################
#Display the names, birthdays and emails of all students whose surnames are he or Han in the student table.
select sname, birthdate, Email
from student
where sname like 'What%' or sname like 'Han%';

 

• Query null value with is null keyword

  A field value is null or not null, or is null or is not null; Cannot be expressed as = null or < > null

  If it is written as field = null or field < > null, the operation results of the system are directly processed as null values, which are processed as false without error

  Common format column is [not] null

# Examples ##################################################################
#In Se_ Add the score field in the score table to query se_ The student number, course number and grade of middle school students in the score table.
alter table se_coruse
add score float(3,1) null after teacherno;

select studentno, courseno, teacherno, score
from se_course
where score is null;

 

• Multi condition query with and

  Selects rows in the result set using the specified criteria

  Character type and date type values in conditional expressions should be placed in single quotation marks

  Juxtaposed conditions cannot have commas

# Examples ##################################################################
#The score table shows the student number, course number and grade of students whose midterm score is higher than 90 and final score is higher than 85.
select studentno, courseno, daily, final
from score
where daily>=90 and final>=85;

 

• Multi condition query with or

  or can be used to connect two conditional expressions

# Examples ##################################################################
#Query the teacher number, students and majors of teachers with senior professional titles in the computer college.
select teacherno, tname, major
from teacher
where department='school of computing' and (prof='associate professor' or prof='professor');

 

{% note info simple%} if both the first AND second conditions are true, the AND operator displays a record.

If only one of the first and second conditions holds, the OR operator displays a record. {% endnote %}

#Subquery returns a value, scalar subquery
#There is no need to alias the subquery

#Tables in the where subquery cannot be updated directly
select * from tb where money = (select max(money) from tb);

#Column subquery
	#If the subquery result returns a column
	#Use in or not in to complete the query
	
	#If the sub query returns data, it returns 1 or 0, which is often used for judgment
	select column from t1 where exists (select * from t2);

#Row subquery
	#The query condition is a row
	select * from t1 where (id,gender) in (select id, gender from t2);
	#Line constructor
	#Row constructors are often used to compare subqueries that can return two or more columns
	(col1, col2, ...)or row (col1, col2, ...)

  

• Special operator

  ! = all is equivalent to not in

  =some() is equivalent to in. any is an alias for some

  =some() is not equal to not in, not equal to one of them

  all and some can be used together with other operators

 

SQL group by & having

The group by clause can group the query results above the row by attribute column or attribute column combination. Each group has the same aggregate value on the attribute column or attribute column combination.

Define one or more columns as a group, so that all rows in the group have the same value in those columns. Each column in the select list that appears in the query must appear in the group by clause at the same time

 

• Use the group by keyword to group

  Using the group by keyword alone, the query result displays only one record of each group.

# Examples ##################################################################
#Use the group by clause to group the data of the score table and display the student number and average total score of each student.
#The avg function is used to find the average value, and the round function is used to round a bit of data of the average value.
select student Student number, round(avg(daily*0.3 + final*0.7),2)as'average'
from score
group by studentno;

 

• group by keyword and group_ Use with concat() function

  You can display all fields in each group.

# Examples ##################################################################
#By grouping and querying the studentno field in the score table, you can view the student number of the course.
select courseno Course number, group_concat(studentno) Student number of selected course
from score
group by courseno;

 

• The group by keyword is used with having

  The having clause is usually used in combination with the group by clause. The having clause specifies the filter to be further applied after the filter of the where clause is applied.

# Examples ##################################################################
#Query the student number and total score of students with more than 3 courses and the final score of each course is higher than 75. The query results are listed in descending order of total score.
select studentno Student number, sum(daily*0.3 + final*0.7) as 'Total score'
from score
where final>=75
group by studentno
having count(*)>=3
order by sum(daily*0.3 + final*0.7) desc;

 

SQL order by

You can use the order by clause to sort the query results in ascending (asc) or descending (desc).

Matters and principles:

1. By default, result sets are arranged in ascending order
2. The columns contained in the order by clause do not necessarily appear in the selected list
3. The order by clause can be sorted by specifying the values of column names, function values, and expressions
4. The order by clause cannot use columns of type text, ntext, or image
5. Multiple sort items can appear simultaneously in the order by clause

# Examples ##################################################################
#In the student table, query the student number, name and admission score of students with scores higher than 850, and arrange them in descending order according to the admission percentage.
select studentno Student number, sname full name, entrance Admission results
from student
where entrance > 850
order by entrance desc;

 

SQL limit

Limit is a clause used to limit the number of query results. You can specify which record the query results are displayed from and how many records are displayed in total. Limit can specify the initial location.

# Examples ##################################################################
#In the query score table, if the final score is higher than 85, it shall be arranged in ascending order according to the usual score daily, starting from No. 2, and query 5 records.
select * from score
where final>85
order by daily desc
limit 2,5;

 

 

Aggregate function query

The aggregate functions commonly used in MySQL include count(), sum(), avg(), max(), min(), etc

count() is used to count the number of pieces of data

sum() is used to calculate the sum of the values of the fields

avg() is used to calculate the average of the values of the fields

max() is used to query the maximum value of the field

min() is used to query the minimum value of the field

 

SQL count()

The count() function returns the number of non null rows in the selected aggregate for any parameter other than "*"

For the parameter "*", return the number of all rows selected for aggregation, including rows with null values

# Examples ##################################################################
#Find the total number of students in grade 18 through query.
select count(studentno) as '18 Number of students at grade'
from student
where substring(studentno,1,2)='18';

 

SQL sum() / ayg()

The sum() function can find the sum of the values of a field in the table

avg() function can calculate the average value of a field in the table

# Examples ##################################################################
#Query the student number, total score and average score of students whose final total score is greater than 270 in the score table.
select studentno Student number, sum(final) Total score, avg(final) average
from score
group by studentno
having sum(final) > 270
order by studentno;

 

SQL max() / min()

The max() function can find the maximum value of a field in the table

The min() function can find the minimum value of a field in the table

# Examples ##################################################################
#Query the final maximum score, minimum score and difference score of the course with elective course No. c05109.
select max(final) Highest score, min(final)Lowest score , max(final)-min(final) as fraction
from score
where course='c05109';

 

SQL with rollup

The same total daily statistics can be carried out on the basis of grouped statistics

# Examples ##################################################################
#Query the final average score and average score of each course in the score table.
select course Course number, avy(final) Average score at the end of the course
from score
group by courseno with rollup;

 

 

Multi table connection

The keywords related to connection that are displayed and defined are as follows:

1. Inner join: inner join. The result contains only the columns that meet the conditions
2. Left outer join: left outer join. The result only includes the qualified rows and all rows in the left table
3. Right outer join: right outer join. The result only includes the rows that meet the conditions and all the rows in the right table
4. cross join: the result only contains the combination of all rows in the two tables, indicating the Cartesian product operation between the two tables

 

Inner connection

Inner join query is the operation of retrieving qualified rows from multiple source tables by comparing the values of shared columns between data source tables. You can use the connection of the equal sign operator, or you can connect the values of two unequal columns.

# Examples ##################################################################
#Query the student number, name and final grade of students with elective course number c05109.
select student.studentno, sname, final
from student inner join score
on student.studentno = score.studentno
where score.courseno = 'c05109'

#Method 2:
select student.studentno, sname, final
from student, score
where student.studentno=score.studentno and score.course = 'c05109';

 

Left outer connection

Left outer connection refers to connecting and combining the data in the left table with each data in the right table. The returned results include unqualified data in the left table in addition to the data in the inner connection, and null values are added to the corresponding columns in the right table.

# Examples ##################################################################
#In mysqltest database, the student number, name, usual score and final score of students are queried by using the left outer connection.
use mysqltest
select student.studentno, sname, daily, final
from student left join score
on student.studentno = score.studentno;

 

Right outer connection

The right outer join contains all rows of the rightmost table in the join clause. If the rows in the right table do not match the rows in the left table, null values are assigned to all columns from the left table in the result set.

# Examples ##################################################################
#Query the connection mode of teachers outside the right class.
select teacher.teacherno, tname, major, course
from teacher right join teach_course
on teacher.teacherno=teacher_course.teacherno;

 

Cross connect

Cross join is the Cartesian product of a table without a where clause. When two tables are cross joined, the size of the result set is the product of the number of rows.

# Examples ##################################################################
#Displays the Cartesian product of the student table and the score table.
select student.student, sname, score
from student corss join score;

 

Connect multiple tables

For the connection query of more than three relational tables, the following rules are generally followed:

Connecting n tables requires at least n-1 connection conditions to avoid Cartesian product

In order to reduce the result set, it is allowed to use more than n-1 connection conditions or other conditions

# Examples ##################################################################
#Query the student number, name, course number, final score and credits of grade 18 students.
select student.studentno, sname, cname, final, round(period/16,1)
from score join student on student.studentno=score.studentno
	join course on score.course=course.courseno
where substring(student.studentno,1,2)='18';

 

Merge multiple result sets

The union operation can combine the returned results of multiple select statements into a result set.

When using union to merge two query result sets, the number and order of columns in all queries must be the same, and the data types must be compatible.

The syntax format is as follows:

select_statement union [all] select_statement

Format reference description:

1. select_statement: select statement
2. union: Specifies that multiple result sets are combined and returned as a single result set
3. All: merge all rows into the result, including duplicate rows. If not specified, duplicate rows are deleted

# Examples ##################################################################
#Create student01 with student table in mysqltest database, and merge some query result sets of student01 and student table.
create table student01 as
select studentno, sname, phone from teaching.student;

select studentno, sname, phone from student01
where phone like '%131%'
union
select studentno, sname, phone from teaching.studnet
where phone like '%132%'

 

join query

#Connect the fields of multiple tables, and you can specify the connection conditions

#inner join
	#The default is inner connection, and inner can be omitted
	#The connection can only be sent when the data exists, that is, there can be no blank line in the connection result
	#on indicates the connection condition. Its expression is similar to where. You can also omit the condition (indicating that the condition is always true)
	#You can also use where to indicate the connection condition
	#If using is used, the field name should be the same  
	using (`Field name`);
	
	#cross join
	#Unconditional connection
	select * from tb1 cross join tb2;
	
#outer join
	#If the data does not exist, it will also appear in the connection result
	
	#left join
	#If the data does not exist, the left table record will appear, while the right table is filled with null
	
	#right join
	#If the data does not exist, the right table record will appear, while the left table is filled with null
	
#natural join
	#Automatically judge the connection conditions and complete the connection
	#It is equivalent to omitting using, which will automatically find the same field
	natural join;
	natural left join;
	natural right join;

 

 

Subquery

1. Execution process of subquery

   MySQL processes nested queries from the inner layer to the outer layer, that is, first process the innermost sub query, then use the query results for the query conditions of the outer query, then solve them layer by layer, and finally get the query results.

2. Subquery connection relationship

   Generally, query statements containing subqueries can be written as join queries. Therefore, the query between multiple tables can also be realized through sub query. In some aspects, the performance of multi table join is better than sub query because join does not require query optimizer to perform additional operations such as sorting.

3. Common operations in sub query

   The subquery can include logical operators such as in, not in, any, all exists and not exists, as well as comparison operators such as "="! = ">" and "<".

4. Type of subquery:

   ● the subquery that returns a table is a table subquery.
   ● a subquery that returns a row with one or more values is a row subquery.
   ● column subquery that returns one or more rows but has only one value on each row.
   ● scalar subqueries that return only one value. By definition, each scalar subquery is a column subquery and a row subquery.

5. When using subquery, you should pay attention to the following:

   ● sub queries need to be enclosed in parentheses. Sub queries can also be included in sub queries, which can be nested up to 32 layers.

   ● when you need to return a value or a value list, you can use sub query instead of an expression. You can also use the sub query to return the result set containing multiple columns to replace the same function of representative or connection operation.
   ● sub query cannot retrieve columns with data types of varchar (max), nvarchar (max) and varbinary (max).
   ● when the sub query uses order by, it can only be used in the outer layer, not in the inner layer.

 

expression

In MySQL statement, the result of sub query can be regarded as an ordinary expression and used in the selection conditions of external query. At this time, the subquery must return a value or a single column value list. At this time, the subquery can replace the expression containing the in keyword in the where clause.

# Examples ##################################################################
#Query the enrollment results of students with student number 18125121107, the average enrollment results of all students, and the difference between the student's scores and the average enrollment results of all students.
select studentno, sname, entrance
(select avg(ectrance) from student) Average score,
ectrance - (select avg(ectrance) from student) Difference
from student
where studentno='18125121107';

 

Derived table

Use the subquery to generate a derived table to replace the data source table in the from clause. The derived table can define an alias, that is, the result set of the subquery can be used as the source table of the outer query. In fact, the sub query is used as the data source of the derived table in the from clause.

# Examples ##################################################################
#Query the student number, course number and score of students whose final score is higher than 85 and the total evaluation score is higher than 90
select TT.student Student number, TT.courseno Course number, TT.final*0.8+TT.daily*0.2 General comment
from (select * from score where final>85) as TT
where TT.final*0.8+TT.daily*0.2 > 90;

 

where subquery

The sub query in the where statement actually takes the result of the sub query as a part of the condition of the statement, and then uses this condition to filter the data of this layer query.

 

• Comparison operator

  Comparison operators include =,! =, >, > =, <<=

# Examples ##################################################################
#Query the student number, course number and final score of students whose final score is lower than the average final score of elective courses
select studentno, course, final
from score a
where final <(select avg(final)
             from score b
             where a.courseno=b.courseno
             group by courseno);

 

• in / not in

  The in operator can detect whether a specific value exists in the result set. If the detection is successful, the external query will be executed.

# Examples ##################################################################
#Obtain the student number, name, telephone number and Emial of students whose final grade contains more than 93 points
select studentno, sname, phone, Emial
from student
where studentno in (select studentno
                   from score
                   where final>93);

 

• exists / not exists

  When using the exists keyword, the inner query statement does not return the query record, but returns a true or false value.

  When the return value is true, the outer query statement will query; When false is returned, the outer query statement does not query or query any records.

# Examples ##################################################################
#Query whether there are students born after December 12, 2001 in the student table. If so, output the student number, name, birthday and phone number of the students
select studentno, sname, birthdate, phone
from student
where exists (select *
             from student
             where birthdate<'2001-12-12');

 

• Limit the comparison algorithm

  All, some, and any operations are further limitations of comparison operations. When all is used, true is returned only when all values meet the comparison relationship. Some or any is a synonym, which means that the expression returns true as long as it meets the comparison relationship with a value in the sub query result set

# Examples ##################################################################
#Find all student numbers, names, phone numbers and final grades in the score table that are higher than the final grade of c05109 course
select student.studentno, sname, phone, final
from score inner join student
on score.studentno = student.studentno
where final>all (select final
                from score
                where course='c05109');

 

Add, change and delete

Modifying data with subquery is to use a subquery nested in insert, update or delete statements to add, update and delete data in tables in batches.

• insert

  The select clause in the insert statement can be used to add the values of one or more other tables or views to the table. Use the select subquery to insert multiple rows at the same time.

# Examples ##################################################################
#Add the records of students born after 2001 in the student table to the student 02 table.
insert into mysqltest.student02 (select * from student
                                where birthdaye >= '2001-12-31');

 

• to update

  The select subquery in the update statement can be used to update the values of one or more other tables or views. Use the select subquery to update multiple rows of data at the same time. In fact, it is by taking the result of the subquery as part of the update condition expression.

# Examples ##################################################################
#Increase the final grade of all students whose admission score is less than 800 in the student form by 5%
update score
set final=final*1.5
where studentno in (select studentno from student
                   where entrance < 800);

 

Fuzzy query

• Character / String start

  Use the character "^" to match records that begin with a specific character or string.

# Examples ##################################################################
#Query some information of students whose surname is "Zhao" in the student table
select studentno, sname
from student
where sname regexp '^Zhao';

 

• End of character / String

  Use the character "$" to match records that end with a specific character or string.

# Examples ##################################################################
#Query some information of students whose phone number mantissa is 5 in the student table
select studentno, sname, phone
from student
where phone regexp '5$';

 

• Symbol "."

  When querying with regular expressions, you can use "." To replace any character in the string.

# Examples ##################################################################
#Query some information of students whose names begin with "Zhao" and end with "Jiang" in the student table
select studentno, sname
from student
where phone regexp '^Zhao..river $';

 

• Matches the specified string

  Regular expressions can match strings. If multiple strings are specified, they need to be separated by the symbol "|" as long as they match any one of these characters.

# Examples ##################################################################
#Query the student information with 131 or 132 numbers in the student telephone number
select studentno, sname
from student
where phont regexp '131 | 132';