Shulou(Shulou.com)05/31 Report--

How to use the DQL command to query data in MySQL? I believe that many inexperienced people are at a loss about this, so this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

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 result needs to meet [GROUP BY...] # specify the fields by which the result is grouped [HAVING...] # the secondary condition that must be met by filtering grouped records [ODER BY...] # specify query records by one. Sort by one or more conditions [LIMIT [offset] Return the number of result records] # specify which record to query from

Basic query statement & AS

The following example uses the data in the student table.

It is inefficient to query the results of all data columns in the table, using the "*" symbol.

Select * from student

Query columns can be specified with high efficiency

Select StudentName,Phone from student

The function and usage of AS clause

Note: AS can be omitted or not written

(1) A new alias can be given 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) the results of calculation or summary can be replaced by another new name.

SELECT Phone + 1 AS Tel FROM student

DISTINCT keyword

Purpose: remove the duplicate values from the SELECT query (only one record is returned when all the returned values are the same)

Syntax:

The SELECT DISTINCT field name is 1, and the field name is 2. From table name

Note: the ALL keyword is the default and returns all records, as opposed to

When filtering multiple column duplicate values, it is filtered only if there are duplicate values in all the columns selected for filtering.

SELECT DISTINCT StudentNo,address from student

Filter single column: query addresses in the student table

SELECT DISTINCT address from student

Expressions in SQL statements

SELECT VERSION (), 100 times 3 as product; # return Mysql version and calculation result SELECT NOW () 'current time'; # return current time

Avoid interference with development language programs such as ".", "*", and parentheses in the returned results of SQL.

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

Splicing CONCAT

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

Addition of numerical types

Select StudentNo+100 from student

Comparison operator & wildcard

Where conditional statement: used to retrieve eligible records in a data table

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

Composition of search conditions: logical operator, comparison operator

# where conditional statement select * from student where address=' Sichuan'; # query the name of the student whose phone number is not empty in the student table select StudentName from student where Phone is not null; # query the name of the student whose phone number is empty in the student table select StudentName from student where Phone is null; # query the data just deleted-null value select StudentName from student where Phone ='; # BETWEEN AND applies to the time range

Logical operator

Comparison operator

Fuzzy query using LIKE keyword

Used with "%" to indicate a match of 0 or any character

Used with "_" to indicate matching a single character

# query the names of students surnamed Zhang * in the student table SELECT StudentName from student where studentname like 'Zhang _'; SELECT StudentName from student where studentname like'% Li%'; # INSELECT * from student where Address in ('Sichuan', 'Shanghai')

Note:

Arithmetic can only be performed between records of numerical data types.

Comparison can only be made between data of the same data type

NULL

Null stands for "no value"

Different from zero value 0 and empty string ""

Can only appear in fields that are defined as NULL

Use the IS NULL or IS NOT NULL comparison operator to compare

Internal connection & self-inquiry

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

The categories include:

Internal connection (inner jion):

Equivalent and non-equivalent join queries

Self-join query

External connection (out jion)

Left connection (LEFT JION)

Right connection (RIGHT JION)

ORDER BY sort query

Sort the results of the select query by some fields

Used with DESC (descending order) or ASC (ascending order). Default is ASC.

Take subject table and grade table data as examples:

SELECT * from subject ORDER BY classhour; # ascending order SELECT * from subject ORDER BY classhour desc; # descending order

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

SELECT * from subject ORDER BY classHour,GradeID

LIMIT paging

LIMIT [m,] n or LIMIT n OFFSET m

Limit the number of rows returned by SELECT

M is the offset of the first returned record row

N returns the number of record rows

Note:

M is not specified, the offset is 0, and the first n records are returned from the first.

LIMIT is often used for paging display

If the data in the table is exceeded, all are displayed.

For example:

SELECT * FROM grade LIMIT 3; # return the first 3 records SELECT * FROM grade LIMIT 1 3; # return 2 records

Total records: total

SELECT COUNT (subjectNO) 'Total data' from subject

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

Subquery

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

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

SELECT subjectName from subjectwhere gradeID IN (SELECT GradeID from grade)

Aggregate function

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

SELECT COUNT (StudentNo) 'Total data' FROM student;SELECT sum (classHour) 'Total hours' from subject;SELECT AVG (classHour) 'average hours' FROM subject;SELECT MAX (classHour) 'longest hours' FROM subject;SELECT MIN (classHour) 'shortest hours' FROM subject

Grouping GROUP BY

Use the GROUP BY keyword to group query results

Group statistics of all the data

There can be multiple fields grouped and grouped in turn

Combined with HAVING for data filtering after grouping

Take student table as an example

(1) group by according to the address grouping of the student table

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

Secondary conditions that must be met by records of HAVING filtering packets

(2) grouping the student table according to the address to satisfy the HAVING with address = 1

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

Merge UNION and UNION

ALLUNION # merges identical data

UNION ALL # merges all data

Note: when merging two tables, the number of columns must be the same to merge.

When 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 subject table and student table

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

Subquery EXISTS-- > true false

If the EXISTS / NOT EXISTS subquery condition is established, the results of the parent query are displayed, otherwise the results are not displayed.

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

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

Or

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

(2) the subquery condition is false (gradeid 1 to 5 in grade table, no 999 exists)

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

Or

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

Subquery (any & all)

ANY: in the judgment condition, if any value in the subquery satisfies the condition, the parent query is executed

ALL: in the judgment condition, if all values in the subquery satisfy the condition, the parent query is executed.

(1) meet the condition: subject.gradeid > = grade.gradeid exists, and the parent query is executed.

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

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

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

After reading the above, have you mastered how to use the DQL command to query data in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.