数据查询DQL

select * from student; -- 查询某个表中所有数据,返回所有的列
select stuname,stugender from student; -- 不区分大小写,一般开发中不会用所有列

create table examresult(
	stuid int primary key not null auto_increment,
	stuname varchar(40),
	examscore double,
	examDate date
);
# +-*/% 对返回结果做运算,要修改数据用update 不建议对返回结果运算,会引起索引失效
select examscore + 1,stuid from examresult;

# as 起别名,[as]可省略 (注意)*后面不可跟别名,因为*代表的是很多列,一个别名是不行的
select examDate '日期' from examresult;
select *'别名' from examresult; -- 会报错

# distinct 去重 相同的内容只保留一份 去重一般要么完整去重,要么就返回一列进行去重
select distinct examscore,examDate from examresult; -- 只返回成绩去重的

# order by 排序 asc升序desc降序 默认升序 可以按照多个列排序,规则:首先按照第一个列排序,然后按照第二个列排序...但是第二个列顺序不会影响第一个列排序,是在第一个列的基础上排序
select * from examresult order by examscore,examdate desc;

-- 条件查询where 等职和非等值操作
# 等值操作 >、<、>=、<=、=
select * from examresult where examscore >= 90;

# 非等值(日期比较要用''括起来,排序要写在where条件的后面,可以使用!=、<>过滤数据,<>代表不等于)
select * from examresult where examdate > '2021-08-09' order by examdate;
# 多个条件是并且(与)关系利用and连接,或者关系利用or连接,范围关系利用between and连接
# 8月17日这天考试成绩大于80分的人(and),成绩大于90或小于60的人(or),成绩在70~90之间的人(between and)
select * from examresult where examdate = '2021-08-17' and examscore > 80;
select * from examresult where examscore between 70 and 90;

# 判空和非空 student表中邮箱为空的(is null)不为空的(is not null)
select * from student where stuemail is not null;

# 查找学生编号是1 3 4的学生 相当于stuid = 1 or stuid = 3 or stuid = 4 可以利用in枚举查询
select * from student where stuid in(1,3,4);

# like 模糊查询 %代表长度不确定,任意长度字符,而_代表单个任意字符
select * from student where stuname like '张%';
select * from student where stuname like '%三%';
select * from student where stuname like '_三';

# 分支语句 多个条件,注意条件顺序,判断是从上而下判断的,只要有一个符合就不再继续判断
select stuid,stuname,examscore,examdate,
	case
		when qualified = 1 then '及格'
		when qualified = 0 then '不及格'
	end '是否及格'-- 起个别名as省略
 from examresult order by 是否及格
 
# 时间查询 时间函数
select sysdate(); -- 系统日期,年月时分秒
select curdate(); -- 当前日期,年月日
select curtime(); -- 当前时间,时分秒
select week(curdate());-- week(date)这个日期为一年中的第几周
select year(curdate());-- year(date)这个日期的年份
-- HOUR(time)指定时间的小时值
-- MINUTE(time)指定时间的分钟值
-- DATEDIFF(date1,date2)两个日期见相隔天数
-- ADDDATE(date,n)date加上n天后的日期
#求学生年龄,活了多少天 根据不同的性别拼接不同的内容
select stuname,(YEAR(CURDATE())-YEAR(borndate))'年龄' from examresult;
select stuname, DATEDIFF(CURDATE(),borndate) '活了多少天' from examresult;

# 字符串查询 
select CONCAT('a','b','c','d');-- 字符串拼接
select concat('mr.',stuname) '姓名' from student where stugender = '男';
-- 根据不同的性别拼接不同的内容
SELECT stugender '性别',
case 
when stugender='男' then CONCAT('mr.',stuname)
when stugender='女' then CONCAT('ms.',stuname)
end '名字'
 from student ORDER BY 性别;
# 大写 
select UPPER(列名) from 表名; 
# 小写 lower()
# 截取 这个从什么位置开始起始值是 1 并不是 0,并且包含这个下标值,如果长度不够,能返回多少个返回多少个,一个都不够就没有任何内容
SELECT stuid, SUBSTRING(stuname, 1,2) from student;
-- 将指定数据的指定位置开始(1 开始)多长的字符串替换为新的字符串,如果长度不够替换,全部变成被替换的内容,替换的内容有多长和你要替换多长没有关系
SELECT INSERT(stuname,1, 2,'0') from student;

# 聚合函数:求和(sum),最大值(max),最小值(min),平均值(avg),数量(count)
SELECT sum(examscore) from examResult;


# group by分组查询	按照...分组
-- 查询总分数的时候按照学生分一下组,也就是每个学生总分多少分
SELECT stuid,sum(examscore) from examResult GROUP BY stuid;
-- 如果要分组 select中的列必须出现在  GROUP BY中,除非 select中是一个聚合函数
select gradeId,stuname from student GROUP BY gradeId ,stuname;
-- 对数据过滤后分组
select gradeId,stuname from student where gradeId in(1,4) GROUP BY gradeId ,stuname;
-- 先分完组再过滤,效率低
select gradeId,stuname from student  GROUP BY gradeId ,stuname HAVING gradeId IN(1,3);

# limit 分页(限定)查询 一般用于分页中 后面的第二个参数代表最多要多少条 ,第一个参数 叫偏移量
# 一共有x条数据,每一页显示y条,总共需要多少页 天花板函数 专门做分页的 总共需要(x+y-1)/y
# 注意,在大量数据查询分页的时候 不会使用这种 limit,因为 limit 是先把数据查出来,然后再跳过,会导致浪费大量的 io
select * from student limit 0,2;

# 子查询 一个查询的结果作为查询的条件
# 查询比stuid为1同学的最高分还要高的同学的成绩
select max(examscore) from examresult where stuid = 1;
select * from examresult where examscore > 90;
-- 合并语句
select * from examresult where examscore > (select max(examscore) from examresult where stuid = 1);
# 查询课程id为2的班级的学生信息
select * from student where gradeId IN (SELECT gradeId from grade where gradeId =2);
-- all 实际上相当于挑出来最大值进行比较
SELECT * from examResult where examscore > all (select examscore from examResult where stuid =1);
-- any 只要符合其中任意一个就可以, 实际上就是符合最小值,等同于下面的 min
SELECT * from examResult where examscore > any (select examscore from examResult where stuid =1);
SELECT * from examResult where examscore >  (select min(examscore) from examResult where stuid =1);

# 一个查询的结果作为另一个查询的表,需要起别名,别名不是字符串
select * from (SELECT * from examResult where examscore> 60) as  tmp where stuid in (1,6);

# union合并查询 要求:表的两列列数一样
-- 会按照列的位置合并数据, 第一个表的第一个列的数据会和第二个表的第一个列数据合并,依次类推,如果有相同的数据则去除
select * from student union select * from student_copy1;
-- 和 UNION是一样,但是相同的数据会保留
SELECT * from student UNION all SELECT * from student_copy1;

# 连接查询 inner join内连接 查询相同数据 也就是两个表中都存在的数据
select * from student s inner join grade g on s.gradeid = g.gradeid where g.gradeid = 1;
-- 如果有多个表 哪个写*, 列多的写* 不然你需要手动写很多个列
# 左外连接 右外连接 多表查询
-- leftjoin 会返回左表 的所有数据以及在右表中的关联的相同数据, 如果右表中没有这个数据,则全部填 null
SELECT * from grade g left join student s on  g.gradeId= s.gradeId; -- pyhton全为空
-- 右外连接 返回的是右边表的所有数据以及在左边表中的关联数据,如果在左边没有数据,则填 null
-- 多表连接 inner join 就是把两个有关联的表放一起,然后再关联下一个表,如果有更多 不断的关联
-- 开发中实际上都是单表查询,然后组合数据,没有这么强的关联查询,理论上不会超过两个表关联,实际开发中会做请求拆分和结果合并
# 事务...
# 视图...