How to use MySQL DQL statement

DQL

The full English name of DQL is Data Query Language, a data query language used to query records in tables in the database.

Basic syntax

DQL query statement, the syntax structure is as follows:

SELECT
Field list

FROM
Table name list

WHERE
Condition list

GROUP BY
Group field list

HAVING
After grouping condition list

ORDER BY
Sort field list

LIMIT
Paging parameters

Basic query

1. Query multiple fields

SELECT 字段1, 字段2, 字段3 ... FROM 表名 ;
SELECT * FROM 表名 ;

Note: * number It represents querying all fields and should be used as little as possible in actual development (it is not intuitive and affects efficiency).

2. Field setting alias

SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] ... FROM 表名;
SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... FROM 表名;

3. Remove duplicate records

SELECT DISTINCT 字段列表 FROM 表名;

Case

A. Query the specified fields name, workno, age and return

select name,workno,age from emp;

B. Query returns all fields

select id ,workno,name,gender,age,idcard,workaddress,entrydate from emp;

C. Query the working addresses of all employees and give them aliases

select workaddress as '工作地址' from emp;
-- as可以省略
select workaddress '工作地址' from emp;

D. Query the working addresses of company employees (do not repeat )

select distinct workaddress '工作地址' from emp;

Conditional query

Syntax

SELECT 字段列表 FROM 表名 WHERE 条件列表 ;

Conditions

Commonly used comparison operators are as follows:

##> is greater than ##>=< is less than or equal to ## is equal to <> or != is not equal to Logical operator

Comparison operator	##Function
## is greater than or equal to
## is less than	##<=
	##=
##BETWEEN .. . AND ...	within a certain range (including minimum and maximum values)
IN( ...)	The value in the list after in, select one more
LIKE placeholder	Fuzzy matching (_ matches a single character, % matches any number of characters)
IS NULL	is NULL
Commonly used logical operators are as follows:

Function##NOT or!Case

select * from emp where idcard is null;

select * from emp where age = 18 or age = 20 or age =40;
select * from emp where age in(18,20,40);

E. Query the name of two people Employee information_ %

select * from emp where name like '__';

##AND or&	AND (Multiple conditions are true at the same time)
##OR or ||	OR (Multiple conditions are arbitrary One is established)
##non, not
A. Query employees whose age is equal to 22 select * from emp where age = 22; B. Query employee information without ID number	c. Query age Information about employees between 15 years old (inclusive) and 20 years old (inclusive) select * from emp where age >= 15 && age <= 20; select * from emp where age >= 15 and age <= 20; select * from emp where age between 15 and 20; D. Query employee information whose age is equal to 18 or 20 or 40

F. Query employee information whose last digit of ID number is X

select * from emp where idcard like '%X';
select * from emp where idcard like '_________________X';

Aggregation function

Common aggregation function

Function

Function

countmaxminavgsumGrammar

SELECT 聚合函数(字段列表) FROM 表名 ;

select count(*) from emp; -- 统计的是总记录数
select count(idcard) from emp; -- 统计的是idcard字段不为null的记录数

select max(age) from emp;

D. Count the minimum age of employees in this company

select min(age) from emp;

Statistical quantity
Maximum value
Minimum value
Average
Sum
Case A. Count the number of employees in this company	B. Count the average age of employees in this company select avg(age) from emp; C. Count the maximum age of employees in this company

E. Count the sum of the ages of employees in Xi’an

select sum(age) from emp where workaddress = '西安';

The above is the detailed content of How to use MySQL DQL statement. For more information, please follow other related articles on the PHP Chinese website!