DQL查询数据语言(MySQL)
DQL查询数据语言(重点)
Select (核心)
1.查询所有字段
select * from `表名`;
2.查询指定字段
select `字段1`,`字段2` from `表名`;
3.给结果取一个名字
select `字段1` as 名字1,`字段2` as 名字2 from `表名`;
4.去重distinct
select distinct `字段` from `表名`;(例:select distinct `age` from `students`;)
5.限定行limit
select * from 表 limit 0,5;
6.排序查询
升序:select * from t_student ORDER BY id asc;
降序:select * from t_student order by id desc;
组合:select * from t_student ORDER BY id asc,class_id desc;
7.最大最小
select MAX(id) from t_student;
select MIN(id) from t_student;
8.子查询
-- 查询身高大于其所在班级平均身高的基本信息
select * from t_student t1 where height>(select avg(height) from t_student where class_id=t1.class_id);
9.分组查询,聚合函数
select sex,sum(iq),max(iq),avg(iq),count(id) from t_student GROUP BY sex;
-- 表连接:左连接,右连接,把多张表合并展示
-- 内连接
select t1.id,t1.sex,t1.class_id,t2.id,t2.class_name from t_student t1,t_class t2 where t1.class_id=t2.id;
where条件语句
检索符合条件的值
运算符 | 语法 | 描述 |
and && | a and b / a && b |
逻辑与 两个都为真结果为真 |
or || | a or b / a || b | 逻辑或 一个为真结果为真 |
not ! | is not / != | 逻辑非 真假反转 |
select `classhour` from `subject` where `classhour` = 110 and `gradeid` =1;
select `classhour` from `subject` where `classhour` !=110; (select `classhour` from `subject` where not `classhour`=110;)
模糊查询
运算符 | 语法 | 描述 |
is null | a is null | 如果a为null,结果为真 |
is not null | a is not null | 如果a不为null,结果为真 |
between | a between b and c | 若a在b和c之间,结果为真 |
like | a like b | SQL匹配,如果a匹配b,结果为真 |
in | a in (a1,a2,a3...) | a在数列内,结果为真 |
1.like例子
查询表中姓胡的同学 like结合(%代表任意字符,_代表一个字符)
select `name` from `students` where `name` like '胡%';
查询姓胡的同学,名字后面只有一个字
select `name` from `students` where `name` like '胡_';
查询姓胡的同学,名字后面有两个字
select `name` from `students` where `name` like '胡__';
查询名字中间有太字的同学 %太%
select `name` from `students` where `name` like '%太%';
2.in例子
查询学号为10001,10002,10003的同学
select `name` from `students` where id in (10001,10002,10003);
3.null和not null
查询班级号为空的人
select `name` from `students` where `gradid` is null;
查询学科表内学时在100和140之间的人
select `classhour` from `subject` where `classhour` between 100 and 140;