/*简单查询*/
use school;
select sno,sname,sage from student;
select sno as "学号",sname "姓名",sage from student;
select sno,sname,sage
from student
where ssex='男' and sage>20;
select count(*),
max(sage),
min(sage),
avg(sage),
sum(sage),
sum(sage)/count(*)
from student;
select *
from student
order by sdept desc, sage asc;
/*分组*/
select sdept,count(*)
from student
group by sdept
order by count(*) desc;
select sdept,count(*)'人数'
from student
group by sdept
having count(*)> 1/*先group by再having*/
order by 人数 desc;/*降序*/
select sno,count(*)
from sc
where grade>=90/*筛选成绩*/
group by sno/*按sno 分组*/
having count(*)>=3/*数目>3*/
order by sno asc;/*升序*/
/*有几种性别*/
select count(distinct ssex)
from student;
/*模糊查询 大于20姓刘*/
select sno,sname,sage
from student
where sage>20 and sname like '刘%';
/* 刘_(名字两个字)或者 刘%(名字几个字都行) 都可 */
/*如果本身就有% _*/
select *
from course
where cname
like 'C#_%' escape '#';
/*选择21-23*/
select *
from student
where sage>=20 and sage<=23;
/*同上*/
select *
from student
where sage between 21 and 23;
/*除了21-23 或在上述代码where后加 not/或直接用not between*/
select *
from student
where sage>23 or sage<21;
select *
from student
where sdept="音乐系" or sdept="计算机系" or sdept="美术系";
/*where not 或 not in*/
select *
from student
where not sdept in('音乐系','计算机系','美术系');
/*is null,而=null不行*/
select *
from course
where not cpno is null;
/* where not cpon is null*/
/* where cpon is not null*/
mysql sql语句简单查询笔记
最新推荐文章于 2024-07-31 17:06:19 发布
这篇博客探讨了SQL查询的不同方面,包括简单的数据选择、分组、聚合函数、排序、模糊查询以及使用LIKE运算符进行模式匹配。还展示了如何处理空值、使用IN操作符以及在查询中应用条件。内容涵盖了从基本查询到复杂分组和过滤的多种技术。
摘要由CSDN通过智能技术生成