How to use DQL commands to query data in MySQL

In this article, the blogger will take you to learn the commonly used data query language in MySQL.

DQL (Data Query Language)

SELECT syntax

SELECT [ALL | DISTINCT]
{ * | table.* | [table.field1 [as alias1][,table.field2] [as alias2][,...]]}
FROM table_name [as table_alias]
[left | out | inner join table_name2] #Joint query [WHERE ...] #Specify the conditions that the results need to meet [GROUP BY ...] #Specify which fields the results should be grouped by [HAVING ...] #Secondary conditions that must be met for filtering grouped records [ODER BY ...] #Specify that the query records are sorted by one or more conditions [LIMIT [offset, number of result records returned]; #Specify which records to query from which to which

Basic query statements & AS

The following examples use data from the student table.

To query all the data columns in a table, use the "*" symbol, which is inefficient.

select * from student;

The query column can be specified, which is highly efficient.

select StudentName,Phone from student;

Function and usage of AS clause

Note: AS can be omitted

(1) You can give a new alias to the data column

select StudentName as 'Student Name' from student;

(2) Give the table an alias

select stu.address from student as stu;

(3) You can replace the result of a calculation or summary with another new name.

SELECT Phone +1 AS Tel FROM student;

DISTINCT Keyword

Function: Remove duplicate values ​​from SELECT query (when all returned values ​​are the same, only one record is returned)

grammar:

SELECT DISTINCT fieldname1, fieldname2, ... from tablename

Note: The ALL keyword is the default and returns all records.

When filtering duplicate values ​​in multiple columns, the filter is performed only when duplicate values ​​exist in all the columns selected for filtering.

SELECT DISTINCT StudentNo,address from student;

Filter a single column: Query the address in the student table

SELECT DISTINCT address from student;

Expressions in SQL statements

SELECT VERSION(),100*3 as product; #Return the MySQL version and calculation results SELECT NOW() 'current time'; #Return the current time 

Avoid SQL return results containing ".", "*", and brackets that interfere with the development language program

SELECT VERSION() as MySQL_V,12.3*100 as expression;
#The returned results will not be confused with the background development program

CONCAT

SELECT CONCAT(studentName,'@.com') as Email from student;

Adding numeric types

select StudentNo+100 from student;

Comparison Operators & Wildcards

Where conditional statement: used to retrieve records that meet the conditions in the data table

The search condition can consist of one or more logical expressions, and the result is generally true or false.

Search condition composition: logical operators, comparison operators

#where conditional statement select * from student where address='四川';
 
#Query the names of students whose phone numbers are not null in the student table select StudentName from student where Phone is not null;
 
#Query the names of students whose phone numbers are empty in the student table select StudentName from student where Phone is null;
 
#Query the data just deleted - empty value select StudentName from student where Phone = '';
 
# BETWEEN AND works with time ranges

Logical Operators

Comparison Operators

Use the LIKE keyword for fuzzy search

• Used with "%" to match 0 or any number of characters
• Used with "_" to match a single character

#Query the name of the student with the surname Zhang* in the student tableSELECT StudentName from student where studentname like '张_';
 
SELECT StudentName from student where studentname like '%丽%';
# IN
SELECT * from student where Address in ('Sichuan','Shanghai');

Notice:

• Arithmetic operations can only be performed between records of numeric data types
• Only data of the same data type can be compared

NULL

• null means "no value"
• Distinguish from the zero value 0 and the empty string " "
• Can only appear in fields that are defined to allow NULL
• You must use the IS NULL or IS NOT NULL comparison operator to compare

Inner Join & Self Query

If you need to query data from multiple tables, you can use the join operator to implement multiple queries.

Categories include:

• Inner join:
  • Equivalent and non-equivalent join queries
  • Self-connection query
• Outer join
  • LEFT JOIN
  • RIGHT JION

ORDER BY sort query

Sort the results of the select statement query by certain fields

Use with DESC (descending order) or ASC (ascending order), the default is ASC

Take the subject table and grade table data as an example:

SELECT * from subject ORDER BY classhour; #Ascending SELECT * from subject ORDER BY classhour desc; #Descending

Multi-field sorting: Sort by the first field first, then sort by the second field. If the data in the first field is the same, sort by the second field.

SELECT * from subject ORDER BY classHour,GradeID; 

LIMIT Pagination

LIMIT [m,] n or LIMIT n OFFSET m

Limit the number of rows returned by a SELECT

m is the offset of the first returned record row

n Returns the number of rows

Notice:

• If m is not specified, the offset is 0, and the first n records are returned starting from the first record.
• LIMIT is often used for paging display
• If the data exceeds the table, display all

For example:

SELECT * FROM grade LIMIT 3; #Return the first 3 records SELECT * FROM grade LIMIT 1,3; #Return 2~4 records 

Total number of records: total

SELECT COUNT(subjectNO) 'total data' from subject; 

Total number of pages: int totalPage = total % pagesize ==0 ? total / pagesize : total / pagesize + 1

Subqueries

In the WHERE clause of the query statement, another query statement is nested

Note: The result returned by the subquery is generally a collection. It is recommended to use the IN keyword

SELECT subjectName from subject
where gradeID IN
(SELECT GradeID from grade); 

Aggregate functions

Commonly used statistical functions: COUNT(), SUM(), AVG(), MAX(), MIN()

SELECT COUNT(StudentNo) 'Total data' FROM student;
SELECT sum(classHour) 'total class hours' from subject;
SELECT AVG(classHour) 'Average class hours' FROM subject;
SELECT MAX(classHour) 'Longest class hours' FROM subject;
SELECT MIN(classHour) 'Shortest class hours' FROM subject;

GROUP BY

Use the GROUP BY keyword to group query results

• Group all data
• There can be multiple grouping fields, and they are grouped in sequence.
• Used in conjunction with HAVING to filter data after grouping

Take the student table as an example

(1) Group the student table by address and perform group by

SELECT Address,COUNT(Address) FROM student GROUP BY Address; 

HAVING A secondary condition that the records in the filtering group must satisfy

(2) Group the student table by address, satisfying the HAVING condition where address = 1

SELECT GROUP_CONCAT(StudentName),COUNT(Address) FROM student GROUP BY Address HAVING COUNT(Address)=1; 

Merge UNION, UNION

• ALLUNION #Merge identical data
• UNION ALL #Merge all data

Note: When merging two tables, the number of columns must be the same.

If the number of columns in the two tables is different, the following error will be reported:

[SQL]SELECT * FROM grade UNION SELECT * FROM student;
[Err] 1222 - The used SELECT statements have a different number of columns

(1) Merge the subject table and the student table

SELECT * FROM subject UNION SELECT * FROM student;  
SELECT * FROM subject UNION ALL SELECT * FROM student; 

Subquery EXISTS ——> true false

EXISTS / NOT EXISTS If the subquery condition is met, the result of the parent query will be displayed, otherwise no result will be displayed

(1) The subquery condition is true (gradeid 1 to 5 in the grade table exists 1)

SELECT subjectName,GradeID FROM subject
WHERE EXISTS 
(SELECT * FROM grade WHERE GradeID=1);

or

SELECT subjectName,GradeID FROM subject
WHERE NOT EXISTS 
(SELECT * FROM grade WHERE GradeID=999); 

(2) The subquery condition is false (gradeid 1 to 5 in the grade table does not exist 999)

SELECT subjectName,GradeID FROM subject
WHERE EXISTS 
(SELECT * FROM grade WHERE GradeID=999);

or

SELECT subjectName,GradeID FROM subject
WHERE NOT EXISTS 
(SELECT * FROM grade WHERE GradeID=1); 

Subquery (any & all)

ANY: In the judgment condition, if any value in the subquery meets the condition, the parent query is executed
ALL: In the judgment condition, if all values ​​in the subquery meet the condition, the parent query is executed
(1) If the condition is met: subject.gradeid >= grade.gradeid, execute the parent query

SELECT subjectname,gradeid FROM subject 
WHERE GradeID >=ANY
(SELECT GradeID FROM grade); 

(2) If the condition is not met: all subject.gradeid >= grade.gradeid, the parent query will not be executed.

SELECT subjectname,gradeid FROM subject 
WHERE GradeID >=ALL
(SELECT GradeID FROM grade); 

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.