MySQL----DQL 查询语言

基础查询

<语法：select 查询列表 from 表名>
1.查询表中所有字段
select * from 表A;
2.查询表中某个字段值
select 字段1,字段2… from 表A;
3.为字段起别名 (as)可省略
select name (as) 姓名 from 表A
select name 姓名 from 表A
4.去重 distinct
select distinct sex 性别 from 表A
5.mysql中 +号 只能作为运算符
select 100+90; 输出190
不是数值时 会试图转换为数值型 若转换失败 为0
select ‘123’+90; 输出213
select ‘王’+90； 输出90

条件查询

<语法：select 查询列表 from 表名 where 筛选条件>
<按条件表达式筛选
条件运算符:等于=，
不等于: !=，<>，
大于小于:>，<，
大于等于.小于等于：>=，<=
按逻辑表达式筛选
逻辑运算符:
&&，and:两个都为ture，结果为ture
||，or:一个为ture 结果为ture
！，not:条件本身为false，结果为ture
<=>: 安全等于 判断是否等于 等于返回ture
模糊查询
like，between and(包含临界值)，in，is null(是空)，is not null(不为空)>
1.查询成绩＞60分的学生
select * from 表A where score＞60;
2.查询成绩不等于60分的学生和成绩
select name，score from 表A where score<>60;
3.查询成绩在60-90之间的学生和成绩
select name，score from 表A where score＞＝60 and score ＜＝90 ;
select name，score from 表A where score between 60 and 90;
4.查询成绩不在60-80之间或大于80的学生和成绩
select name，score from 表A where not(score＞＝60 and score ＜＝90) or score＞90;
5.查询姓名中包含’丽’字的同学
select name from 表A where name like=’%丽%’;
6.查询所有学生中 有书籍book1,book2,book3其中一种的学生
select name from 表A where books in(‘book1’,‘book2’,‘book3’);
7,查询没有书籍的学生
select name from 表A where books is null;

排序查询

<语法：select 查询列表 from 表名 where 筛选条件 order by 排序列表 desc(asc)> 【asc:升序 可省略,desc:降序】
1.根据成绩从大到小排序
select * from 表A order by score desc
2.查询成绩大于80分的学生 并按照成绩降序排列
select name,score from 表A where score＞80 order by score desc;
3.按多个字段排序
按照学生成绩降序排列、学生姓名升序排列
select *from 表A order by score desc,name asc;

函数

<语法:select 函数名(实参列表) from 表名>
单行函数：肯定有返回值
1.连接函数concat(A,B);
将两个字段拼接为一个字段
select concat(字段1,字段2) 别名 from 表A
将两个字段拼接为一个字段 并用下划线链接
select concat(字段1,’_’,字段2) 别名 from 表A
2.判断是否为空
ifnull(判断字段,返回值);
查询成绩 若为null 返回0 否则返回原值
select ifnull(score,0) 成绩 from 表A
isnull(字段);
字段为null返回1,否则返回0
3.获取字节长度函数 length(字段A);
查询学生姓名长度
select name length(name) 姓名长度 from 表A;
4.upper(大写)、lower(小写)
select upper(Json)； 输出JSON
select lower(JSon)；输出json
姓大写 名小写 并连接起来
select concat(upper(name1),lower(name2)) 姓名 from 表A
5.截取函数 substr(A,B);
A:要截取的字段 或 字符串
B:数字 从第几位开始截取
索引从 1 开始
截取从指点索引处后面所有字符
select substr(‘张三李四王五’,5) out_put
输出 王五
截取从指点索引处到指定字符长度的字符
select substr(‘张三李四王五’,1,3) out_put
输出 张三李
6.instr 截取函数
select substr(‘张三李四王五’,‘李’) out_put
输出 3
截取邮箱@前面的字符
select substr(email,1,instr(email,"@)-1) from 表
7.replace 替换
select replace(‘张无忌爱上周芷若’,‘周芷若’,‘赵敏’)
输出 张无忌爱上赵敏

数学函数：
1.round 四舍五入
select round(1.65)  输出 2
select round(-1.45) 输出 -1
select round(1.567,2) 保留小数点后两位：1.57
2.ceil 向上取整
select ceil(1.52)  输出1.6
3.floor 乡下取整
select floor(9.99)  输出9
4.truncate 截断
select truncate(1.6999,2)  输出1.69
5.mod 取余
(被除数为正/负  结果为正/负)
select mod(10,3)  输出1
 select mod(10,-3)  输出-1
 6.datediff 两数之差
 select datediff(10,8); 输出2

日期函数
1.now 返回当前系统日期+时间
select now();
2.curdate 仅返回当前系统日期
select urdate();
3.curtime 仅返回当前时间
select curtime();
4.获取指定部分年月日 小时分钟秒
select year(now()) 年;
select year(‘1998-1-1’) 年;
select month(now()) 月;
select day(now()) 日;
5.str_to_date 将日期格式的字符转换成指定格式
select str_to_date(‘4-3 1992’,’%c-%d %Y’)
date_format 将日期转换成字符
select date_format(now(),’%Y年%m月%d日’) 输出2020年11月11日
select date_format(now(),’%m月/%d日 %Y年’) 输出11月/11日 2020年

其他函数
1.select version() 查看版本号
2.select Database() 查看当前库
3.select usre() 查看当前用户

流程控制函数
1.if 函数:if else效果
select if(10＞5,‘大’,‘小’)
是返回’大’
否返回’小’
2.case 函数
使用一：switch case 效果
适用于等值
switch(变量或表达式){
case 常量1：语句1;break;
…
default:语句n;break;}
mysql中：
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
…
else 要显示的值n或语句n;
end
id=30，name为良
id=60，name为及格
id=90，name为优
其他，name为原始值
select id,name
case id
when 30 then name=‘良’
when 60 then name=‘及格’
when 90 then name=‘优’
else name
end as 新名字 from 表A

使用二：类似于多重if
适用于区间
java中
if(条件1){
语句1;
}else if(条件2){
语句2;
}
…
else{
语句n;}
mysql中
case
when 条件1 then 要显示的值1(或语句 要加;)
when 条件2 then 要显示的值2 或语句2
…
else 要显示的值n或语句n
end
如果 score ＜45 级别=‘D’
如果 score ＞60 级别=‘C’
如果 score ＞90 级别=‘B’
否则 级别= ‘A’
select score,
case
when score ＜45 then ‘D’
when score ＞60 then ‘C’
when score ＞90 then ‘B’
else ‘A’
end as 级别 from 表A
￥￥￥￥￥￥￥￥￥￥￥￥￥￥
分组函数(聚合函数、统计函数)：做统计用
1.sum 求和
求成绩的总和：select sum(score) from 表A
2.avg 平均值
求成绩的平均值：select avg(score) from 表A
3.max 最大值
求成绩的最大值：select max(score) from 表A
4.min 最小值
求成绩的最小值：select min(score) from 表A
5.count 计数
共有多少个成绩：select count(score) from 表A
6.搭配 distinct去重 使用
select sum(distinct score),sum(score) from 表A
select count(distinct sex) from 表A
7.group by 子句
语法：select 分组函数,列1,列2(出现在gruop by 后面) from 表 where 筛选条件 group by 分组列
1,列2 having 筛选条件 order by 子句
查询每个班级的最高成绩
select max(score) class from 表 A group by class;
查询每个班级学生个数
select count(),student_id from 表A group by student_id;
分组前添加筛选条件：
查询每个班级平均成绩中包含0的数字
select avg(score),class from 表A where score like %0% group by class;
分组后添加筛选条件：
查询哪个班的成绩数大于5
select count() class from 表A group by class;
%%%%%%%%%%%%%%%%%%%%
连接查询（多表查询）
成绩表(student,score) select * from 表A
班级表(student,sex) select * from 表B
城市表(student,city) select * from 表C
等级表(level,min_lev,max_lev) select * from 表D
1.内连接(sql92)：等值连接,非等值连接,自连接
等值连接
查询学生所对应的班级
select student,class from 表A,表B where 表A.student=表B.student
查询每个班级男同学的成绩
select score,student,sex from 表A a,表B b where a.student=b.student and sex=‘男’
查询学生班级,成绩,城市
select student,class,city from 表A a,表B b,表C c where a.student=b.student,b.student=c.student [加筛选、顺序: and city like’%市%’ order by score desc]

非等值连接
查询学生成绩和对应的级别
select score level from 表A a,表D d where score between d.min_lev and d.max_lev[加筛选条件 and level=‘A级’]

2.内连接(sql99) -等值连接：
语法:select 查询列表 from biao1 [inner] join 表2 on 连接条件
查询学生和班级
select student,class from 表A a inner join 表B b on a.student=b.student [添加筛选条件 where score＞60 order by score desc]
非等值连接
查询学生成绩和对应的级别
select score,level from 表A a join表D d on score between d.min_lev and d.max_lev[加筛选条件 and level=‘A级’]

3.外连接：左外连接，右外连接
外连接查询结果为主表(主要查询数据)的所有记录
从表中有匹配的 则显示匹配值
从表中无匹配的 则显示匹null
语法：select 查询列表 from 表1 连接类型 join 表2 on 连接条件 [where 筛选条件 group by 分组 having 筛选条件 order by 排序列表]
左外连接:left [outer]：左边是主表
右外连接:right [outer] 右边是主表
查询没有参加考试的同学
select score,student from 表B b left join 表A a on b.student=a.student

4.子查询：可放在
where、having后面：
标量子查询(结果集只有一行一列)
一般搭配单行操作符使用
＞ ＜ ＝
例1：谁的成绩比小明高
步骤1：select score from 表A where student =‘小明’
步骤2：select *from 表A where score ＞(select score from 表A where student =‘小明’)
例2：查询大于小明成绩 城市与小红相等的 学生 成绩 城市
步骤1：select score from 表A where student =‘小明’;
步骤2：select city from 表B where student=‘小红’;
步骤3：select student,score,city from 表A where score＞(select score from 表A where student =‘小明’) and city=( select city from 表B where student=‘小红)’;

列子查询(结果集只有一行多列)
一般搭配多行操作符使用
in any some all
例1：返回成绩是80和90 的学生
步骤1：select score from 表A where score in(80,90);
步骤2：select student from 表A where score in(select score from 表A where score in(80,90));
行子查询(结果集有多列多行or一行多列)
例1：查询成绩最小 等级大 的学生信息
方法1：
步骤1：select min(score) from 表A
步骤2：select min(score) from 表D
步骤3：select * from 表A where score=(select min(score) from 表A) and city=(select max(level)from 表D)
方法2：
select * from 表A (score,city)=(select min(score),min(score) from 表A )
from后面：
表子查询(结果集一般为多行多列)
案例1：查询每个学生平均成绩的等级
步骤1：select avg(score) avg student from 表A group by student
select * from 表D
筛选平均工资等级 between min_lev and max_lev
步骤2：select avg1.*,lev.level from(select avg(score) avg student from 表A group by student ) avg1 join 表D lev on avg1.score between min_lev and max_lev

select后面：标量查询(结果集只有一行一列)
exists(相关子查询) 后面：表子查询

分页查询

语法 select 查询列表 from 表 【join type join 表2 on 连接条件 where 筛选条件 group by 分组字段 having 分组后的筛选 order by 排序的字段】limit 【offset】,size
offset：要显示条目的起始索引(从0开始，可省略)
size：要显示的条目个数
*公式 要显示的页数page,每页条目数 size
select * from 表A limit (page-1)size,size;
案例1：查询前五条学生成绩
select * from 表A limit 5;
案例2：查询11-15条数据
select * from 表A limit 10,15;
案例3：有成绩 并且前十名的查询出来
select * from 表A where score is not null order by score desc limit 10;