搜索

首页  >  文章  >  数据库  >  MySQL中有哪些数据查询语句

MySQL中有哪些数据查询语句

王林
王林转载
2023-05-26 14:16:203060浏览

    一、基本概念（查询语句）

    ①基本语句

    1、“select * from 表名;”，—可查询表中全部数据；
    2、“select 字段名 from 表名;”，—可查询表中指定字段的数据；
    3、“select distinct 字段名 from 表名;”，—可对表中数据进行去重查询。
    4、“select 字段名 from 表名 where 查询条件;”，—可根据条件查询表中指定字段的数据；

    ②条件查询

    1）比较运算符：>, 95ec6993dc754240360e28e0de8de30a=, c4290fa39d2a3d6a61c5df07c46bf05e

    查询大于18岁的信息

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

    查询小于18岁的信息

    select * from students where age<18;

    查询年龄为18岁的所有学生的名字

    select * from students where age=18;

    2）逻辑运算符：and, or, not

    –18到28之间的学生信息

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

    –18岁以上的女性

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

    –18以上或者身高查过180(包含)以上

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

    不在18岁以上的女性这个范围内的信息

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

    年龄不是小于或者等于18并且是女性

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

    3)模糊查询：like， rlike

    % 替换1个或者多个
    _ 替换1个
    查询姓名中 以“小”开始的名字

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

    查询姓名中有“小”所有的名字

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

    查询有2个字的名字

    select name from students where name like "__";

    查询有3个字的名字

    select name from students where name like "__";

    查询至少有2个字的名字 select name from

    students where name like "__%";

    rlike正则
    查询以周开始的姓名

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

    查询以周开始、伦结尾的姓名

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

    4)范围查询：in，not in，between…and，not between…and

    查询年龄为18、34的姓名

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

    not in不非连续的范围之内
    年龄不是 18、34岁之间的信息

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

    between … and …表示在一个连续的范围内
    查询年龄在18到34之间的的信息

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

    not between … and …表示不在一个连续的范围内
    查询年龄不在在18到34之间的的信息

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

    空判断

    判空 is null
    查询身高为空的信息

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

    判非空is not null

    select * from students where height is not null;

    排序：order_by

    –查询年龄在18到34岁之间的男性，按照年龄从小到大排序

    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;

    查询年龄在18到34岁之间的女性，身高从高到矮排序

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

    order by多个字段
    查询年龄在18到34岁之间的女性，身高从高到矮排序，如果身高相同的情况下按照年龄从小到大排序

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

    查询年龄在18到34岁之间的女性，身高从高到矮排序，如果身高相同的情况下按照年龄从小到大排序，如果年龄也相同那么按照id从大到小排序

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

    按照年龄从小到大、身高从高到矮的排序

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

    分组：group_by, group_concat():查询内容， having

    where :是对整个数据表信息的判断;
    having:是对于分组后的数据进行判断
    –group by
    按照性别分组,查询所有的性别

    select gender from students group by gender;

    –计算每种性别中的人数

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

    where是在group by 前面
    –计算男性的人数

    select count(*) from students where gender=&#39;男&#39;;

    –group_concat(…)
    查询同种性别中的姓名

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

    having :having是在group by后面
    查询平均年龄超过30岁的性别，以及姓名

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

    查询每种性别中的人数多于2个的信息

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

    – 查询每组性别的平均年龄

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

    分页： limit

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

    限制查询出来的数据个数

    select *from students where gender=1 limit 2;

    查询前5个数据

    select* from students limit 0,5;

    查询id6-10(包含)的书序

    select * from students limit 5,5;

    每页显示2个，第1个页面

    select * from students limit 0,2;

    每页显示2个，第2个页面

    select * from students limit 2,2;

    每页显示2个，第3个页面

    select * from students limit 4,2;

    每页显示2个，第4个页面

    select * from students limit 6,2;

    每页显示2个，显示第6页的信息,按照年龄从小到大排序

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

    – 如果重新排序了,那么会显示第一页

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

    5)聚合函数：count(), max(), min(), sum(), avg(), round()

    聚合函数
    -总数-- count
    -查询男性有多少人，女性有多少人

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

    -最大值-最小值
    – max --min
    一查询最大的年龄

    select max (age) from students;

    –查询女性的最高身高

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

    -求和
    –sum
    -计算所有人的年龄总和

    select sum ( age) from students;

    –平均值
    – avg
    –计算平均年龄

    select avg(age) from students;

    –计算平均年龄

    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 a26d98d33123a70024fa8ba5642906c6;

    （2）命令：select 7d63c9f8ce32ff1ae4915f31bebead74 from a26d98d33123a70024fa8ba5642906c6;

    2、去重查询（distinct）

    命令：select distinct 7d63c9f8ce32ff1ae4915f31bebead74 from a26d98d33123a70024fa8ba5642906c6

    3、排序查询（order by）

    升序：asc

    降序：desc

    降序排列命令：select dfb08b8fe164362ddbbdb0005b7a6721 from a26d98d33123a70024fa8ba5642906c6 order by dfb08b8fe164362ddbbdb0005b7a6721 desc

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

    4、分组查询(group by)

    命令：select 162131b8562e13ae131483a4c9d25bb6, Sum(score) from a26d98d33123a70024fa8ba5642906c6 group by 162131b8562e13ae131483a4c9d25bb6

    假设现在又有一个学生成绩表(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;

    以上是MySQL中有哪些数据查询语句的详细内容。更多信息请关注PHP中文网其他相关文章！

    mysql NULL 运算符 比较运算符 逻辑运算符 count select union 数据库
    声明：
    本文转载于：yisu.com。如有侵权，请联系admin@php.cn删除
    上一篇：在MySQL中如何优化SQL语句下一篇：MySQL中HOUR函数怎么用

    相关文章

    查看更多