What data query statements are there in MySQL?

1. Basic concepts (query statements)

① Basic statements

1. "select * from table name; ”, — can query all the data in the table;
2. “select field name from table name;”, — can query the data of specified fields in the table;
3. “select distinct field name from table name;” ;", - you can perform deduplication query on the data in the table.
4. "select field name from table name where query condition;", - the data of the specified field in the table can be queried according to the conditions;

②Conditional query

1) Comparison operators: >, <, >=, <=, =, !=, <>

Query information for those over 18 years old

select * from students where age>18;
select id, name,gender from students where age>18;

Query information for those under 18 years old

select * from students where age<18;

Query the names of all students who are 18 years old

select * from students where age=18;

2) Logical operators: and, or, not

–Student information between 18 and 28

select * from students where age>18_and age<28:

–Female over 18 years old

select * from students where age>18 and gender="女";
select * from students where age>18 and gender=2;

–18 and over Or the height has been checked and is over 180 (inclusive)

select * from students where age>18 or height>=180;

Information that is not within the range of women over 18 years old

select * from students where not (age>18 and gender=2);

The age is not less than or equal to 18 and she is a woman

select * from students where (not age<=18) and gender=2;

3) Fuzzy query: like, rlike

% Replace 1 or more
_ Replace 1
to query names with "small" Starting name

select name from students where name="小";
select name from students where name like"小%";

Query all the names with "小" in the name

select name from students whece name like "%小%";

Query the names with 2 characters

select name from students where name like "__";

Query the names with 3 characters

select name from students where name like "__";

Query names with at least 2 characters select name from

students where name like "__%";

rlike regular
Query names starting with Zhou

select name from students where name rlike "^周.*";

Query names starting with Zhou and ending with Lun

select name from students where name rlike "^周.*伦$";

4) Range query: in, not in, between…and, not between…and

Query those whose ages are 18 and 34 Name

select name, age from students where age=18 or age=34;
select name,age from students where age in (18，34);

not in a non-continuous range
Age is not between 18 and 34 years old

select name,age from students where age not in (18，34);

between … and …Indicates that it is in a continuous range
Query information about people whose ages are between 18 and 34

select name,age from students where age between 18 and 34;

not between … and…

select * from students where age not between 18 and 34;

Null Judgment

Judgment is null

Query information about height being null

select *from students where height is null/NULL/Null;

Judgment is not null is not null

select * from students where height is not null;

Sort: order_by

–Query men aged between 18 and 34, sorted by age from youngest to oldest Sort

select * from students where (age between 18 and 34) and gender=1; 
select * from students where (age between 18 and 34) and gender=1 order by age; 
select * from students where (age between 18 and 34) and gender=1 order by age asc;

Query women aged between 18 and 34 years old, sort by height from tallest to short

select * from students where (age between18 and 34) and gender=2 order by height desc;

order by multiple fields

Query women aged between 18 and 34 years old For women, the heights are sorted from tallest to shortest. If the heights are the same, they are sorted by age from smallest to largest.

select * from students where (age between 18 and 34) and gender=2 order by height desc,age asc;

Query the females aged between 18 and 34 years old, and the heights are sorted from tallest to shortest. If the heights are In the same situation, sort by age from small to large, if the age is the same, then sort by ID from large to small

select * from students where (age between 18 and 34) and gender=2 order by height desc,age asc, id desc;

Sort by age from small to large, height from high to short

select * from students order by age asc,height desc;

Group: group_by, group_concat(): Query content, having

where: It is the judgment of the entire data table information;

having: It is the judgment after grouping Judge the data
–group by
Group by gender, query all genders

select gender from students group by gender;

–Calculate the number of people in each gender

select gender, count(*) from students group by gender;

where is in group by Front

–Calculate the number of men

select count(*) from students where gender='男';

–group_concat(…)

Query the names of the same gender

select gender,group_concat(name) from students group by gender;

having: having is after group by

Query the genders whose average age is more than 30 years old, and their names

select gender ,avg(age) from students group by gender having avg(age) > 30;

Query the information where there are more than 2 people in each gender

select gender,count(*) from students group by gender having count(*) > 2;

– Query the average age of each gender group

select gender,avg(age) from students group by gender;

Paging: limit

limit start,count   (start:表示从哪─个开始;count:表示数量)
即limit（第N页-1)*每个的个数，每页的个数;
limit在使用的时候，要放在最后面.

Limit the number of queried data

select *from students where gender=1 limit 2;

Query the first 5 data

select* from students limit 0,5;

Query the book sequence of id6-10 (inclusive)

select * from students limit 5,5;

Display 2 per page, the first page

select * from students limit 0,2;

Display 2 per page, the second page

select * from students limit 2,2;

Display 2 per page, the 3rd page

select * from students limit 4,2;

Display 2 per page, the 4th page

select * from students limit 6,2;

Display 2 per page, display the 6th page Information, sorted by age from small to large

select * from students order by age asc limit 10,2;

– If reordered, the first page will be displayed

select * from students where gender=2 order by height des limit 0,2;

5) Aggregation functions: count(), max() , min(), sum(), avg(), round()

Aggregation function

-Total number--count
-Query how many men there are and how many women there are

select count(*) from students where gender=1;
select count(*) as 男性人数 from students where gender=1;
select count(*) as 女性人数 from students where gender=2;

-Maximum value-Minimum value

– max --min
Query the oldest age

select max (age) from students;

–Query the highest height of a woman

select max (height) from students where gender=2;

-Sum

–sum
-Calculate the sum of the ages of all people

select sum ( age) from students;

–average

– avg
–Calculate the average age

select avg(age) from students;

–Calculate average age

select sum ( age) / count(* ) from students;

–四舍五入round ( 123.23 ,_1)保留1位小数
–计算所有人的平均年龄,保留2位小数

select round (sum(age)/count(*)，2) from students; select round ( sum(age)/count(*)，3) from students ;

–计算男性的平均身高保留2位小数

select round(avg (height),2) from students where gender=1; select name,round(avg(height)，2) from students where gender=1;

6)连接查询 ：inner join, left join, right join

inner join
select … from 表 A inner join表B;

select * from students inner join classes;

查询有能够对应班级的学生以及班级信息

select * from students inner join classes on students.cls_id=classes.id;

按照要求显示姓名、班级

select students.*, classes.name from students inner join classes on students.cls_id=classes.id;
select students.name，classes.name from students inner join classes on students.cls_id=classes.id;

给数据表起名字

select s.name,c.name from students as s inner join classes as c on s.cls_id=c.id;

查询有能够对应班级的学生以及班级信息，显示学生的所有信息，只显示班级名称

select s.*,c.name from students as s inner join classes as c on s.cls_id=c.id;

在以上的查询中,将班级姓名显示在第1列

select c.name，s.* from students as s inner join classes as c on s.cls_id=c.id;

查询有能够对应班级的学生以及班级信息，按照班级进行排序
select c.xxx s.xxx from student as s inner join clssses as c on … order by …;

select c.name，s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name;

当时同一个班级的时候，按照学生的id进行从小到大排序

select c.name，s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name,s.id;

left join
查询每位学生对应的班级信息

select * from students as s left join classes as c on s.cls_id=c.id;

查询没有对应班级信息的学生
– select … from xxx as s left join xxx as c on… where …
– select … from xxx as s left join xxx as c on… . … having …

select * from students as s left join classes as c on s.cls_id=c.id having c.id is null;
select * from students as s left join classes as c on s.cls_id=c.id where c.id is null;

left join是按照左边的表为基准和右边的表进行查询，查到就显示，查不到就显示为null

补充

查询所有字段：select * from 表名;
查询指定字段：select 列1,列2,... from 表名;
使用 as 给字段起别名： select 字段 as 名字.... from 表名;
查询某个表的某个字段：select 表名.字段 .... from表名；
可以通过 as 给表起别名： select 别名.字段 .... from 表名 as 别名;
消除重复行： distinct 字段

注意：WHERE子句中是不能用聚集函数作为条件表达式的！

二、总结

1、普通查询

（1）命令：select * from <表名>;

（2）命令：select <要查询的字段> from <表名>;

2、去重查询（distinct）

命令：select distinct <要查询的字段> from <表名>

3、排序查询（order by）

升序：asc

降序：desc

降序排列命令：select <要查询的字段名> from <表名> order by <要查询的字段名> desc

不加desc一般默认为升序排列

4、分组查询(group by)

命令：select <按什么分的组>, Sum(score) from <表名> group by <按什么分的组>

假设现在又有一个学生成绩表(result)。要求查询一个学生的总成绩。我们根据学号将他们分为了不同的组。

命令：

select id, Sum(score) 
from result 
group by id;

现在有两个表学生表（stu）和成绩表（result）。

5.等值查询

当连接运算符为“=”时，为等值连接查询。

现在要查询年龄小于20岁学生的不及格成绩。

select stu.id,score 
from stu,result 
where stu.id = result.id and age < 20 and score < 60;

等值查询效率太低

6.外连接查询

①语法

select f1,f2,f3,....
from table1 left/right outer join table2
on 条件;

②左外连接查询，例如

select a.id,score
from
  (select id,age from stu where age < 20) a (过滤左表信息)
left join
  (select id, score from result where score < 60) b (过滤右表信息)
on a.id = b.id;

左外连接就是左表过滤的结果必须全部存在。右表中如果没有与左表过滤出来的数据匹配的记录，那么就会出现NULL值

③右外连接查询，例如

select a.id,score
from
  (select id,age from stu where age < 20) a (过滤左表信息)
right join
  (select id, score from result where score < 60) b (过滤右表信息)
on a.id = b.id;

右外连接就是左表过滤的结果必须全部存在

7.内连接查询

①语法

select f1,f2,f3,....
from table1 inter join table2
on 条件;

②例如

select a.id,score
from
  (select id,age from stu where age < 20) a (过滤左表信息)
inner join
  (select id, score from result where score < 60) b (过滤右表信息)
on a.id = b.id;

8.合并查询

在图书表(t_book)和图书类别表(t_bookType)中

①.union

使用union关键字是，数据库系统会将所有的查询结果合并到一起，然后去掉相同的记录；

select id 
from t_book  
 union 
select id 
from t_bookType;

②.union all

使用union all，不会去除掉重复的记录；

select id 
from t_book  
 union all 
select id 
from t_bookType;

The above is the detailed content of What data query statements are there in MySQL?. For more information, please follow other related articles on the PHP Chinese website!