mysql 查询所有queryid MySQL 查询所有女生学号
最编程
2024-08-11 17:59:44
...
MySQL 查询题型总结
### MySQL 3
use `school`;
-- 查询所有学生的所有信息
select * from tb_student;
select stu_id,
stu_name,
stu_addr
from tb_student;
use school;
-- 查询学生的学号、姓名和籍贯(投影)
select stu_id as 学号,
stu_name as 姓名,
stu_addr as 籍贯
from tb_student;
-- alias _ 别名
-- 查询所有课程的名称及学分(投影和别名)
select stu_id as '学生编号',
stu_name as '学生姓名'
from tb_student;
-- 查询所有女学生的姓名和出生日期(筛选)
select stu_name,
stu_birth
from tb_student
where stu_sex = 0;
-- 查询籍贯为“四川成都”的女学生的姓名和出生日期(筛选)
select stu_name,
stu_birth
from tb_student
where stu_sex = 0 and
stu_addr = '四川成都';
-- 查询籍贯为“四川成都”或者性别是女的学生
select stu_name,
stu_birth
from tb_student
where stu_sex = 0 or
stu_addr = '四川成都';
-- 查询所有80后学生的姓名、性别和出生日期(筛选)
select stu_name,
stu_sex,
stu_birth
from tb_student
where stu_birth>='1980-1-1' and
stu_birth<='1990-1-1';
-- 或者使用betwee and
-- 可以做数学运算，性别+10，还可以做除法运算，加法，求余数，加减乘除求余运算，写筛选条件也可以写数学运算
select stu_name,
stu_sex +10,
stu_birth
from tb_student
where stu_birth between '1980-1-1' and '1990-1-1';
-- 查询学分大于2的课程的名称和学分(筛选)
select cou_name as '课程名称',
cou_credit as '课程学分'
from tb_course
where cou_credit > 2;
-- 查询学分是奇数的课程的名称和学分(筛选)
select cou_name ,
cou_credit
from tb_course
where cou_credit mod 2 <> 0; -- 这两种方法都可以的
-- where cou_credit % 2 <> 0 ; -- mod 取余
-- 查询选择选了1111的课程考试成绩在90分以上的学生学号(筛选)
select stu_id
from tb_record
where cou_id = 1111 and
score > 90;
-- 查询名字叫“杨过”的学生的姓名和性别
select stu_name as '学生姓名',
-- case when then else end 分支结构
-- 使用if函数构造分支结构， if 是mysql方言，不建议使用，不适用于其他的数据库
-- if(stu_sex ,'男','女') as 性别
case stu_sex when 1 then '男' when 0 then '女' else '未知' end as '学生性别'
from tb_student
where stu_name = '杨过';
-- ￥￥￥ 复习的时候，练习写的
select stu_name as '学生性别',
case stu_sex when 1 then '男' when 0 then '女' else '未知' end as '性别'
from tb_student
where stu_name = '杨过';
-- 查询姓“杨”的学生姓名和性别(模糊)
-- like是像的意思
-- 通配符 - wild card - 百搭牌
-- % 匹配0个或任意多个字符
-- _ 下划线精确匹配一个字符，如果要匹配两个字符，就写两个下划线
-- 方法1 使用 _ 来匹配，一个_匹配一个字符，两个_ _ 匹配两个字符
select stu_name,
stu_sex
from tb_student
-- where stu_name like '杨%';
where stu_name like '杨_' or
stu_name like '杨__'; -- 两种方法都可以
-- 方法2 使用%来匹配
select stu_name,
case stu_sex when 1 then '男' when 0 then '女' else '未知' end as '性别'
from tb_student
where stu_name like '杨%';
-- 查询姓“杨”名字两个字的学生姓名和性别(模糊)
select stu_name,
stu_sex
from tb_student
where stu_name like '杨_';
-- 查询姓“杨”名字三个字的学生姓名和性别(模糊)
select stu_name,
stu_sex
from tb_student
where stu_name like '杨__';
-- 查询名字中有“不”字或“嫣”字的学生的姓名(模糊)
select stu_name,
stu_sex
from tb_student
where stu_name like '%不%' or
stu_name like '%嫣%';
-- 方法2使用union 连接两个查询，把两个条件分开写。
select stu_name,
stu_sex
from tb_student
where stu_name like '%不%'
union -- union all是把集合的元素不去重，直接把数据组装起来，而union 是会去重的，
select stu_name,
stu_sex
from tb_student
where stu_name like '%嫣%';
-- 关系代数-以集合论、一阶谓词逻辑逻辑
-- mysql没有交，差运算，只有并集运算。
-- 使用正则表达式进行查询，在必知必会第9章。
-- 查询姓“杨”名字三个字的学生姓名和性别(模糊)
-- regexp - regular expression - 正则表达式
select stu_name
from tb_student
where stu_name regexp '[杨][\\u4e00-\\u9fa5]{1,2}'; # -- \表示转义，\u表示的是unicode编码。
-- 查询姓“杨”或姓“林”名字三个字的学生的姓名(正则表达式模糊查询)
select stu_name
from tb_student
where stu_name regexp '[杨林][\\u4e00-\\u9fa5]{2}';
-- 查询没有录入籍贯的学生姓名(空值处理)
-- 三值逻辑: true / false / unkown <-----空值做任何运算的结果，结果仍然是空值，空值做关系运算，逻辑运算，会产生unkown,判断空或者非空，要写is null ,或者is not null。
-- 为空要写 is null ,不为空，写is not null
-- tirm --MYSQL函数- 修剪字符串两端的空格.
select stu_name
from tb_student
where stu_addr is null ; -- sql关系运算，真假未知,
update tb_student set stu_addr = '' where stu_id = 3755;
-- 查询录入了籍贯的学生姓名(空值处理)
select stu_name
from tb_student
where stu_addr is not null ;
-- 如果字符串是空字符串
-- 下面查询没有籍贯的学生，包括字符串的值为空
select stu_name
from tb_student
where stu_addr is null or
stu_addr = ''; #可以查到两个学生记录
-- 下面查询有籍贯的学生，不包括字符串的值为空
select stu_name
from tb_student
where stu_addr is not null and
stu_addr <> '';
-- 如果查询的数据中，字符串是很多空格，以上方法任然查不出来。,要加函数trim(),如果要去掉左边的空格或数据，就使用leleading'什么内容'
-- 德摩根律
update tb_student set stu_addr = ' ' where stu_id = 3755;
-- 找地址空的
select stu_name
from tb_student
where stu_addr is null or
trim(stu_addr) = '';
-- 找地址非空
select stu_name
from tb_student
where stu_addr is not null and
trim(stu_addr) <> '';
-- 查询学生选课的所有日期(去重)
-- distinct 去重
select distinct sel_date
from tb_record;
-- 查询学生的籍贯(去重)
select distinct sel_date
from tb_record
where stu_addr is not null and
trim(stu_addr) <>'';
-- 查询男学生的姓名和生日按年龄从大到小排列(排序)
-- asc ascending -上升 - 从小到大
-- desc -- descending - 下降 - 从大到小
select stu_name,
stu_birth
from tb_student
where stu_sex = 1
order by stu_birth asc, stu_id desc;
-- 时间日期函数
-- now() - 获取当前数据库服务器的时间
-- curdate() - current date - 获取当前日期
-- curtime() --current time - 获取当前时间
-- datediff(dt1,dt2)
-- 数数值函数
-- floor() - 向下取整
-- ceiling() /ceil() - 向上取整
-- round（）- 四舍五入
select now(); -- 当前时间日期
select curdate(); -- 当前日期
select curtime(); -- 当前时间
-- 补充：将上面的生日换算成年龄(日期函数、数值函数)
select stu_birth,
stu_name,
floor(datediff(curdate(),stu_birth)/365) as '年龄'
from tb_student
where stu_sex = 1 order by 年龄 desc;
help functions;
help time;
-- 查询年龄最大的学生的出生日期(聚合函数) - 查询最小的生日
select min(stu_birth)
from tb_student;
-- 查询年龄最小的学生的出生日期(聚合函数) - 查询年龄最小的
select max(stu_birth)
from tb_student;
-- 查询编号为1111的课程考试成绩的最高分(聚合函数)
select max(score)
from tb_record
where cou_id = 1111; -- 95.0
-- 查询学号为1001的学生考试成绩的最低分(聚合函数)
select min(score)
from tb_record
where stu_id = 1001; -- 87.5
-- 查询学号为1001的学生考试成绩的平均分(聚合函数)
-- 注意：聚合函数不会处理空值（null），即求和，计数不会考虑空值的。
-- 聚合函数有max/min/avg/sum/count
-- count(*) 统计所有的行数，count（score）统计所有的非空值
-- coalesce（x,y,z）返回参数中第一个非空值。
select round(avg(score),1) -- 95.6
from tb_record
where stu_id = 1001; -- 95.62500 --想要保留几位小数，使用round函数。
-- 查询学号为1001的学生考试成绩的平均分，如果有null值，null值算0分(聚合函数)
select round(avg(score),1) -- 95.6
from tb_record
where stu_id = 1001; -- 95.62500
-- 方法1
select sum(score)/count(*)
from tb_record
where stu_id = 1001; -- 76.50000
-- 方法2 ifnull --MYSQL函数，如果score是空值，就返回0，否则就是返回score.
select avg(ifnull(score,0))
from tb_record
where stu_id = 1001; -- 76.5
-- 方法3 使用 coalesce
select avg(coalesce(score,0)) -- 通用的
from tb_record
where stu_id = 1001; -- 76.50000
-- 方法4
select avg(case when score is null then 0 else score end)
from tb_record
where stu_id = 1001; -- 76.5000
-- Hao（jackfrued） 14:09:05
-- 获取描述性统计信息
-- 均值、中位数、分位数、最大值、最小值、极差（全距）、方差、标准差
-- 均值在数据大小比较几种的时候，有很好的代表性
-- 方差 ：给误差（每个数据跟均值的差）的平方求平均值。
-- 总体方差：
-- 样本方差:
-- 方差越大，均值的代表性越弱，方差越小，均值的代表性越好。
-- 查询学号为1001的学生考试成绩的标准差(聚合函数)
-- MYSQL函数 - std
-- oracle函数 - stddev
-- 标准SQL
-- 标准差SQL - stddev_pop / sttdev_samp 总体标准差与样本标准差
-- 标准方差SQL - var_pop / var_samp 总体方差与样本方差
-- population - 总体 / sample - 样本
-- MYSQL方言 - std
--
select variance(score)
from tb_record
where stu_id = 1001; -- 方差 26.17
select round(stddev(score),2)
from tb_record
where stu_id = 1001; -- 标准差 5.12
select round(stddev(score),2)
from tb_record
where stu_id = 1002; -- 11.5
-- 举例说明 样本方差和标准差的计算
select stddev_samp(score)
from tb_record
where stu_id = 1002; -- 样本标准差16.263455967290593
select var_samp(score)
from tb_record
where stu_id = 1002; -- 样本方差 264.5
select stddev_pop(score)
from tb_record
where stu_id = 1002; -- 总体标准差11.5
select var_pop(score)
from tb_record
where stu_id = 1002; -- 总体方差132.25
help functions; --查函数
help 'Aggregate Functions and Modifiers'; -- 查聚合函数
-- 查询男女学生的人数(分组和聚合函数) - 根据性别统计人数
-- 要学会在不同的维度拆解数据
select case stu_sex when 1 then '男'else '女' end as 性别,
count(*) as 人数
from tb_student
group by stu_sex;
-- 查询每个学院学生人数(分组和聚合函数)
select col_id as 学院,
count(*) as 人数
from tb_student
group by col_id;
-- 查询每个学院男女学生人数(分组和聚合函数)
select col_id as 学院编号, -- 别名不需要单引号
case stu_sex when 1 then '男' else '女' end as 性别,
count(*) as 人数
from tb_student
group by col_id,stu_sex;
-- 查询每个学生的学号和平均成绩(分组和聚合函数)
select stu_id as 学生学号,
round(avg(score),1) as 平均分
from tb_record
group by stu_id;
-- 听过学号分组，然后计算平均分
-- 查询平均成绩大于等于90分的学生的学号和平均成绩
select stu_id,
avg(score) as '平均成绩'
from tb_record
group by stu_id
having avg(score) >= 90;
-- 查询1111、2222、3333三门课程平均成绩大于等于90分的学生的学号和平均成绩
select stu_id as 学号,
cou_id as 课程编号,
round(avg(score),1) as 平均成绩
from tb_record
where cou_id in (1111,2222,3333)
group by stu_id
having avg(score) >= 90;
-- 查询年龄最大的学生的姓名(子查询/嵌套查询)
-- 以前的方法
select min(stu_birth) -- 赋值不能用等号，等号表示相等，判断的，赋值要用海象运算符
from tb_student; -- 1985-04-17
-- 新的方法
select @x := min(stu_birth) -- 赋值不能用等号，等号表示相等，判断的，赋值要用海象运算符
from tb_student;
select @x ; -- 把值赋值给了@X
select stu_name
from tb_student
where stu_birth = @x; -- 杨不悔
-- 方法2 subquery - 把一个查询结果作为另一个查询的一部分来使用！！！
select stu_name
from tb_student
where stu_birth = (select min(stu_birth)
from tb_student); -- 杨不悔
-- 查询选了两门以上的课程的学生姓名(子查询/分组条件/集合运算)
select stu_name
from tb_student
where stu_id in (select stu_id
from tb_record
group by stu_id
having count(*) > 2);
-- 学号在这个集合里面就要去把数据拿出来。也可以写成一下方法
-- any 是一个谓词，可以产生真和假 ，作用是只要和后面任意一个匹配上，就返回真。
-- all 是一个谓词，----> 所有的元素都匹配成功才返回true.
-- in 集合运算也称为一个谓词。
-- 自己理解的 any是只要匹配上，就会返回值，然后会把集合里面每一个都元素都进行匹配？
select stu_name
from tb_student
where stu_id = any (select stu_id
from tb_record
group by stu_id
having count(*) > 2);
%*
杨过
王语嫣
项少龙
*%
-- 查询学生的姓名、生日和所在学院名称 （连接/表查询--- 从多张表投影字段）
-- from 子句如果放多张表但是没有连接记录的条件就会形成一个笛卡尔积，
-- 通过连接条件可以多张表满足条件的记录连接起来
-- python -- > 包名.模块名.函数名 ---> 完全限定名
-- 列，表都可以别名 as 可以省略
-- from a import foo
-- from b import foo
-- import a
-- import b
-- a.foo
-- b.foo
select stu_name,
stu_birth,
col_name
-- from tb_student as t1,tb_college as t2 -- 给两张表换个名字
-- where t1.col_id = t2.col_id;
from tb_student as t1,tb_college as t2
where tb_student.col_id = tb_college.col_id;
-- 或者使用 from 表1 iner join 表名2 on 条件 ;
select stu_name,
stu_birth,
col_name
from tb_student inner join tb_college on tb_student.col_id = tb_college.col_id ;
-- 查询学生姓名、课程名称以及成绩(连接查询/联结查询)
select stu_name,
cou_name,
score
from tb_student as t1,tb_course as t2,tb_record as t3
where t1.stu_id = t3.stu_id and
t2.cou_id = t3.cou_id and
score is not null
-- 方法2 内连接 使用 inner join on
-- Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'nul' at line 5
select stu_name,
cou_name,
score
from tb_student inner join tb_record on tb_student.stu_id = tb_record.stu_id inner join tb_course on tb_course.cou_id = tb_record.cou_id
where score is not null ;
-- 注意: 写时候on 后面的连接条件的时候，一定要注意对应关系。
-- 方法3 自然连接 表1 natural join 表2 ，如果有同名列，就会连接，如果没有同名列，就会产生笛卡尔积。
select stu_name,
cou_name,
score
from tb_student natural join tb_course natural join tb_record
where score is not null;
-- 补充：上面的查询结果取前5条数据(分页查询)
-- limit 数字 只拿前几条
-- offset 数字 --- 跳过指定数字个 ，分页数据
select stu_name,
cou_name,
score
from tb_student natural join tb_course natural join tb_record
where score is not null
order by cou_id asc ,score desc
limit 5
offset 10;
-- limit 数字1,数字2 --- 跳过数字1个数据，拿数字2个数据 。
-- 方法2 使用limit 数字1，数字2
select stu_name,
cou_name,
score
from tb_student natural join tb_course natural join tb_record
where score is not null
order by cou_id asc ,score desc
limit 10 , 5;
-- 补充：上面的查询结果取第6-10条数据(分页查询)
use school;
select stu_name,
cou_name,
score
from tb_student natural join tb_course natural join tb_record
where score is not null
order by cou_id asc ,score desc
limit 5 , 5;
-- 补充：上面的查询结果取第11-15条数据(分页查询)
select stu_name,
cou_name,
score
from tb_student natural join tb_course natural join tb_record
where score is not null
order by cou_id asc ,score desc
limit 10 , 5;
-- 查询选课学生的姓名和平均成绩(子查询和连接查询)
select stu_name ,
avg_score
from tb_student natural join (select stu_id,
round(avg(score),1) as avg_score
from tb_record
group by stu_id) as tb_ave;
select stu_name as 学生姓名,
round(avg(score),1) as 平均成绩
from tb_student natural join tb_record
group by stu_id;
-- 查询学生的姓名和选课的数量 （只能查到选了课的学生的姓名和数量，还有三个没有选课的学生，不会查到）
-- 方式1 使用两个表的自然连接，是通过学号
select stu_name as 学生姓名,
count(*) as 选课数量
from tb_student natural join tb_record
group by stu_id;
-- 方式2 使用内连接
select stu_name as 学生姓名,
count as 选课数量
from tb_student inner join select（
group by stu_id;
-- 查询每个学生的姓名和选课数量(左外连接和子查询)
-- 内连接/自然连接：只会将两张表满足连表条件的记录取到
-- 左外连接:获取左表（写在join前面的表）所有的数据，不满足连表条件的地方填充null
-- 写法：left outer join /left join
-- 右外连接：获取右表（写在join后面的表）所有的数据，不满足连表条件的地方填充null
-- 写法：rignt outer join /rignt join
-- 全外连接：获取左表和右表所有数据，不满足连表条件的地方填充null (mysql不支持全外连接)
-- 写法：full outer join /full join
-- 在MYsql中如果要实现全外连接的效果，可以用左外连接和右外连接求并集
select stu_name,
count(rec_id) as 选课数量
from tb_student left outer join tb_record on tb_student.stu_id = tb_record.stu_id
group by tb_student.stu_id;
select stu_name,
count(rec_id) as total
from tb_student left join (select )
-- 查询没有选课的学生的姓名
-- 方法一、去重加集合运算，集合运算 去重操作，性能比较低，
select stu_name
from tb_student
where stu_id not in (select distinct stu_id
from tb_record);
-- 方法2 按照两个集合的差集方法,两个表进行左连接，然后，右表的主键是空集
select stu_name
from tb_student left join tb_record
on tb_record.stu_id = tb_student.stu_id
where rec_id is null;
#### 练习提升题
-- 01. 查询课程1111比课程2222成绩高的所有学生的学号
select
sid1
from
(select stu_id as sid1, score from tb_record where cou_id=1111) as t1
inner join
(select stu_id as sid2, score from tb_record where cou_id=2222) as t2
on sid1=sid2
where t1.score>t2.score;
-- 02. 查询没有学过张三丰老师的课程的同学的学号和姓名
select stu_id, stu_name
from tb_student
where stu_id not in (
select stu_id from tb_teacher
natural join tb_course
natural join tb_record
where tea_name='张三丰'
);
select stu_id, stu_name
from tb_student as t1
where not exists (
select 'x' from tb_teacher
natural join tb_course
natural join tb_record as t2
where t1.stu_id=t2.stu_id and tea_name='张三丰'
);
-- 03. 查询学过课程1111和课程2222的同学的学号和姓名
select stu_id, stu_name
from tb_student
where stu_id in (
select t1.stu_id
from tb_record t1
inner join tb_record t2
on t1.stu_id=t2.stu_id
where t1.cou_id=1111 and t2.cou_id=2222
);
-- 04. 查询所有课程成绩大于80分的同学的学号和姓名
select stu_id, stu_name
from tb_student
where stu_id in (
select stu_id
from tb_record
group by stu_id
having min(score)>80
);
-- 05. 查询学习了所有课程的同学的学号和姓名
select stu_id, stu_name
from tb_student
where stu_id in (
select stu_id
from tb_record
group by stu_id
having count(cou_id)=(
select count(cou_id) from tb_course
)
);
-- 06. 查询至少有一门课跟学号1001同学所学课程相同的同学的学号和姓名
select stu_id, stu_name
from tb_student
where stu_id in (
select distinct stu_id from tb_record
where cou_id in (
select cou_id from tb_record where stu_id=1001
) and stu_id<>1001
);
-- 07. 查询所学课程与1002同学完全相同的同学的学号和姓名
-- 08. 查询有课程不及格（60分以下）的同学的学号和姓名
-- 09. 查询每门课程的名称、最高分、最低分和平均分
-- 10. 查询每门课程第2名和第3名的学生姓名和对应的成绩
-- 11. 查询每门课程各分数段（100-90，90-80，80-70，70-60，60以下）的人数占比
select
cou_id,
sum(if(score>=90 and score<=100, 1, 0))/count(score) as A,
sum(if(score>=80 and score<90, 1, 0))/count(score) as B,
sum(if(score>=70 and score<80, 1, 0))/count(score) as C,
sum(if(score>=60 and score<70, 1, 0))/count(score) as D,
sum(if(score<60, 1, 0))/count(score) as E
from tb_record
group by cou_id;
-- 12. 查询本周过生日的学生的姓名和生日（考察对日期函数的熟练程度）
select stu_name, stu_birth
from tb_student
where weekofyear(stu_birth)=weekofyear(curdate());
-- where weekofyear(stu_birth)+1=weekofyear(curdate()); -- 上周过生日
-- where weekofyear(stu_birth)=weekofyear(curdate())+1; -- 下一周过生日
-- 补充日期函数中日期格式化---date_format(数据，‘转换的格式’)
-- 1.日期格式化函数
-- date_format()
-- %a 缩写星期名
-- %b 缩写月名
-- %c 月，数值
-- %D 带有英文前缀的月中的天
-- %d 月的天，数值(00-31)
-- %e 月的天，数值(0-31)
-- %f 微秒
-- %H 小时 (00-23)
-- %h 小时 (01-12)
-- %I 小时 (01-12)
-- %i 分钟，数值(00-59)
-- %j 年的天 (001-366)
-- %k 小时 (0-23)
-- %l 小时 (1-12)
-- %M 月名
-- %m 月，数值(00-12)
-- %p AM 或 PM
-- %r 时间，12-小时（hh:mm:ss AM 或 PM）
-- %S 秒(00-59)
-- %s 秒(00-59)
-- %T 时间, 24-小时 (hh:mm:ss)
-- %U 周 (00-53) 星期日是一周的第一天
-- %u 周 (00-53) 星期一是一周的第一天
-- %V 周 (01-53) 星期日是一周的第一天，与 %X 使用
-- %v 周 (01-53) 星期一是一周的第一天，与 %x 使用
-- %W 星期名
-- %w 周的天 （0=星期日, 6=星期六）
-- %X 年，其中的星期日是周的第一天，4 位，与 %V 使用
-- %x 年，其中的星期一是周的第一天，4 位，与 %v 使用
-- %Y 年，4 位
-- %y 年，2 位
SELECT DATE_FORMAT(day,'%W, %M %e, %Y') AS day
FROM Days;
-- 问题 {"headers":{"Days":["day"]},"rows":{"Days":[["2022-04-12"],["2021-08-09"],["2020-06-26"]]}}
-- 结果 {"headers": ["day"], "values": [["Tuesday, April 12, 2022"], ["Monday, August 9, 2021"], ["Friday, June 26, 2020"]]}
### MySQL 4 第几前几——---——窗口函数
-- 如果存在就删除名为hrs的数据库
drop database if exists `hrs`;
-- 创建名为hrs的数据库并指定默认的字符集
create database `hrs` default charset utf8mb4;
-- 切换到hrs数据库
use `hrs`;
-- 创建部门表
create table `tb_dept`
(
`dno` int not null comment '编号',
`dname` varchar(10) not null comment '名称',
`dloc` varchar(20) not null comment '所在地',
primary key (`dno`)
);
-- 插入4个部门
insert into `tb_dept` values
(10, '会计部', '北京'),
(20, '研发部', '成都'),
(30, '销售部', '重庆'),
(40, '运维部', '深圳');
-- 创建员工表
create table `tb_emp`
(
`eno` int not null comment '员工编号',
`ename` varchar(20) not null comment '员工姓名',
`job` varchar(20) not null comment '员工职位',
`mgr` int comment '主管编号',
`sal` int not null comment '员工月薪',
`comm` int comment '每月补贴',
`dno` int not null comment '所在部门编号',
primary key (`eno`),
constraint `fk_emp_mgr` foreign key (`mgr`) references tb_emp (`eno`),
constraint `fk_emp_dno` foreign key (`dno`) references tb_dept (`dno`)
);
-- 插入14个员工
insert into `tb_emp` values
(7800, '张三丰', '总裁', null, 9000, 1200, 20),
(2056, '乔峰', '分析师', 7800, 5000, 1500, 20),
(3088, '李莫愁', '设计师', 2056, 3500, 800, 20),
(3211, '张无忌', '程序员', 2056, 3200, null, 20),
(3233, '丘处机', '程序员', 2056, 3400, null, 20),
(3251, '张翠山', '程序员', 2056, 4000, null, 20),
(5566, '宋远桥', '会计师', 7800, 4000, 1000, 10),
(5234, '郭靖', '出纳', 5566, 2000, null, 10),
(3344, '黄蓉', '销售主管', 7800, 3000, 800, 30),
(1359, '胡一刀', '销售员', 3344, 1800, 200, 30),
(4466, '苗人凤', '销售员', 3344, 2500, null, 30),
(3244, '欧阳锋', '程序员', 3088, 3200, null, 20),
(3577, '杨过', '会计', 5566, 2200, null, 10),
(3588, '朱九真', '会计', 5566, 2500, null, 10);
-- 查询员工及其主管的姓名
insert into tb_emp values
(8899, '骆昊', '分析师', 7800, 9000, 5000, 20);
-- 查询月薪最高的员工姓名和月薪 ---> 至少给出3种写法
-- 方法一：排序法（不推荐）
select ename,
sal
from tb_emp
order by sal desc
limit 1;
-- 方法二：嵌套查询
select ename,
sal
from tb_emp
where sal = (select max(sal)
from tb_emp);
-- 方法三：all / any
select ename,
sal
from tb_emp
where sal >= all(select sal
from tb_emp);
-- 方法四：计数法
-- 推广到找工资的第几名或者工资的前几名
select ename,
sal
from tb_emp as t1
where (select count(*)
from tb_emp as t2
where t2.sal > t1.sal) = 0;
-- 方法五：存在性判断
select ename,
sal
from tb_emp as t1
where not exists (select 'x'
from tb_emp as t2
where t2.sal > t1.sal);
-- 查询月薪前3名的员工的姓名和月薪
select ename,
sal
from tb_emp as t1
where (select count(*)
from tb_emp as t2
where t2.sal > t1.sal) < 3
order by sal desc;
-- 查询员工的姓名和年薪(年薪=(sal+comm)*13)
select ename,
(sal + coalesce(comm, 0)) * 13 as ann_sal
from tb_emp;
-- 查询部门的编号和人数
select dno,
count(*) as total
from tb_emp
group by dno;
-- 查询部门人数超过5个人的部门的编号和人数
select dno,
count(*) as total
from tb_emp
group by dno
having total > 5;
-- 查询所有部门的名称和人数
select dname,
count(eno) as total
from tb_dept left join tb_emp
on tb_dept.dno = tb_emp.dno
group by tb_dept.dno;
select dname,
coalesce(total, 0) as total
from tb_dept left join (select dno,
count(*) as total
from tb_emp
group by dno) as t
on tb_dept.dno = t.dno;
-- 查询月薪超过平均月薪的员工的姓名和月薪
select ename,
sal
from tb_emp
where sal > (select avg(sal)
from tb_emp);
-- 查询月薪超过其所在部门平均月薪的员工的姓名、部门编号和月薪
-- Error Code: 1052. Column 'dno' in field list is ambiguous
select ename,
dno,
sal
from tb_emp as t1
natural join (select dno,
avg(sal) as avg_sal
from tb_emp
group by dno) as t2
where sal > avg_sal;
-- 查询部门中月薪最高的人姓名、月薪和所在部门名称
select ename,
sal,
dname
from tb_emp natural join tb_dept
where (dno, sal) in (select dno,
max(sal) as max_sal
from tb_emp
group by dno);
select ename,
sal,
dname
from tb_emp natural join tb_dept
natural join (select dno,
max(sal) as max_sal
from tb_emp
group by dno) as tmp
where sal = max_sal;
select ename,
sal,
dname
from tb_emp inner join tb_dept
on tb_emp.dno = tb_dept.dno
inner join (select dno,
max(sal) as max_sal
from tb_emp
group by dno) as tmp
on tb_emp.dno = tmp.dno
where sal = max_sal;
-- 查询主管的姓名和职位
select ename,
job
from tb_emp as t1
where exists (select 'x'
from tb_emp as t2
where t1.eno = t2.mgr);
select distinct t1.ename,
t1.job
from tb_emp as t1 inner join tb_emp as t2
on t1.eno = t2.mgr;
select ename,
job
from tb_emp
where eno in (select distinct mgr
from tb_emp
where mgr is not null);
-- 查询普通员工的姓名和职位
select ename,
job
from tb_emp as t1
where not exists (select 'x'
from tb_emp as t2
where t1.eno = t2.mgr);
select ename,
job
from tb_emp
where eno not in (select distinct mgr
from tb_emp
where mgr is not null);
-- 查询主管和普通员工的平均月薪
select tag,
avg(sal) as avg_sal
from (select sal,
case when exists (select 'x'
from tb_emp as t2
where t1.eno = t2.mgr)
then '主管'
else '普通员工'
end as tag
from tb_emp as t1) as tmp
group by tag;
-- 查询月薪排名4~6名的员工排名、姓名和月薪
select t1.*
from (select @x := @x + 1 as r,
ename,
sal
from tb_emp
order by sal desc) as t1, (select @x := 0) as t2
where r between 4 and 6;
-- 窗口函数
-- 排名函数：rank() / dense_rank() / row_number()
select r1,
ename,
sal
from (select ename,
sal,
rank() over (order by sal desc) as r1,
dense_rank() over (order by sal desc) as r2,
row_number() over (order by sal desc) as r3
from tb_emp) as tmp
where r1 between 4 and 6;
-- 查询每个部门月薪排前2名的员工姓名、月薪和部门编号
select ename,
sal,
dno
from tb_emp as t1
where (select count(*)
from tb_emp as t2
where t1.dno = t2.dno
and t2.sal > t1.sal) < 2
order by dno asc, sal desc;
-- 窗口函数
select ename,
sal,
dno
from (select ename,
sal,
dno,
rank() over (partition by dno order by sal desc) as r
from tb_emp) as tmp
where r <= 2;
#### 练习提升题
create database `demo1` default charset utf8mb4;
use `demo1`;
drop table if exists `tb_order`;
create table `tb_order` (
`id` int not null,
`order_no` varchar(20) not null comment '订单编号',
`user_id` varchar(50) not null comment '用户ID',
`order_date` date not null comment '下单日期',
`store` varchar(50) not null comment '店铺ID',
`product` varchar(50) not null comment '商品ID',
`quantity` int not null comment '购买数量',
primary key (`id`)
) engine=innodb comment='订单表';
lock tables `tb_order` write;
insert into `tb_order` values
(1,'d001','customera','2018-01-01','storea','proda',1),
(2,'d001','customera','2018-01-01','storea','prodb',1),
(3,'d001','customera','2018-01-01','storea','prodc',1),
(4,'d002','customerb','2018-01-12','storeb','prodb',1),
(5,'d002','customerb','2018-01-12','storeb','prodd',1),
(6,'d003','customerc','2018-01-12','storec','prodb',1),
(7,'d003','customerc','2018-01-12','storec','prodc',1),
(8,'d003','customerc','2018-01-12','storec','prodd',1),
(9,'d004','customera','2018-01-01','stored','prodd',2),
(10,'d005','customerb','2018-01-23','storeb','proda',1);
unlock tables;
drop table if exists `tb_product`;
create table `tb_product` (
`prod_id` varchar(50) not null comment '商品ID',
`category` varchar(50) not null comment '种类',
`color` varchar(10) not null comment '颜色',
`weight` decimal(10,2) not null comment '重量',
`price` int not null comment '价格',
primary key (`prod_id`)
) engine=innodb comment='商品表';
lock tables `tb_product` write;
insert into `tb_product` values
('proda','catea','yellow',5.60,100),
('prodb','cateb','red',3.70,200),
('prodc','catec','blue',10.30,300),
('prodd','cated','black',7.80,400);
unlock tables;
drop table if exists `tb_store`;
create table `tb_store` (
`store_id` varchar(50) not null comment '店铺ID',
`city` varchar(20) not null comment '城市',
primary key (`store_id`)
) engine=innodb comment='店铺表';
lock tables `tb_store` write;
insert into `tb_store` values
('storea','citya'),
('storeb','citya'),
('storec','cityb'),
('stored','cityc'),
('storee','cityd'),
('storef','cityb');
unlock tables;
-- 查出购买总金额不低于800的用户的总购买金额，总订单数和总购买商品数，订单号相同的算作一单。
select sum(quantity*price) as '总购买金额',
count(distinct order_no) as '总订单数',
count(distinct product) as '总购买数'
from tb_order inner join tb_product
on tb_order.product = tb_product.prod_id
group by user_id
having sum(quantity*price) >= 800;
-- 查出所有城市（包含无购买记录的城市）的总店铺数，总购买人数和总购买金额。
select city as 城市,
count(distinct store_id) as 总店铺数,
count(distinct user_id) as 总购买人数,
sum(quantity*price) as 总购买金额
from tb_store left join tb_order
on tb_store.store_id = tb_order.store
left join tb_product
on tb_order.product = tb_product.prod_id
group by city;
-- 查出购买过"catea"产品的用户和他们的平均订单金额，订单号相同的算作一单。
select user_id as '用户名',
sum( quantity*price) / count(distinct order_no ) as '平均订单金额'
from tb_order inner join tb_product
on tb_order.product = tb_product.prod_id
where category = 'catea'
group by user_id;
### MySQL 5 留存率与选课系统查询
-- 创建用户登录日志表
create database homework ;
use homework;
create table `tb_user_login`
(
`user_id` varchar(300) comment '用户ID',
`login_date` date comment '登录日期'
) engine=innodb comment='登录日志表';
-- 插入数据
insert into `tb_user_login` (`user_id` , `login_date`)
values
('A', '2019/9/2'),
('A', '2019/9/3'),
('A', '2019/9/4'),
('B', '2019/9/2'),
('B', '2019/9/4'),
('B', '2019/9/10'),
('C', '2019/1/1'),
('C', '2019/1/2'),
('C', '2019/1/30'),
('C', '2019/9/3'),
('C', '2019/9/4'),
('C', '2019/9/5'),
('C', '2019/9/11'),
('C', '2019/9/12'),
('C', '2019/9/13');
-- 任务1：查询连续3天登录的用户ID。
-- 方法1
use homework;
Select distinct user_id
from tb_user_login
where (user_id ,date_add(login_date,interval 1 day)) in (select * from tb_user_login)
and (user_id ,date_add(login_date,interval 2 day)) in (select * from tb_user_login);
-- 方法2
select distinct `user_id`
from (
select
`user_id`, `login_date`,
dense_rank() over (
partition by `user_id` order by `login_date`
) as `rn`
from (
select `user_id`, `login_date`
from `tb_user_login`
group by `user_id`, `login_date`
) as temp
) as temp
group by `user_id`, subdate(`login_date`, `rn`)
having count(*) >= 3;
-- 任务2：查询每天新增用户数以及他们次日留存率和三十日留存率。
-- 查询每天新增用户数以及他们次日留存率和三十日留存率。
-- 方法1：
select
`first_login`
, count(distinct t1.`user_id`) as 新增用户数
, count(distinct t2.`user_id`) / count(distinct t1.`user_id`) as 次日留存率
, count(distinct t3.`user_id`) / count(distinct t1.`user_id`) as 三十日留存率
from (
select
`user_id`, `login_date`,
min(`login_date`) over (
partition by `user_id` order by `login_date`
) as `first_login`
from `tb_user_login`
) as t1
left join `tb_user_login` as t2
on t1.`user_id`=t2.`user_id` and datediff(`first_login`, t2.`login_date`)=-1
left join `tb_user_login` as t3
on t1.`user_id`=t3.`user_id` and datediff(`first_login`, t3.`login_date`)=-29
group by `first_login`;
-- 方法2：
select
`first_login`,
count(distinct `user_id`) as 新增用户数,
sum(case datediff(`login_date`, `first_login`) when 1 then 1 else 0 end) / count(distinct `user_id`) as 次日留存率,
sum(case datediff(`login_date`, `first_login`) when 29 then 1 else 0 end) / count(distinct `user_id`) as 三十日留存率
from (
select
`user_id`, `login_date`,
min(`login_date`) over (partition by `user_id` order by `login_date`) as `first_login`
from `tb_user_login`
) as t1
group by `first_login`;
#### Practice
-- 第一题
use homework;
create table `tb_result` (
`rq` date not null,
`sf` char(1) not null
) engine=innodb;
insert into `tb_result` values
('2017-04-09','胜'),
('2017-04-09','胜'),
('2017-04-09','负'),
('2017-04-09','负'),
('2017-04-10','胜'),
('2017-04-10','负'),
('2017-04-10','胜');
-- 用上面的表查询出如下所示的结果。
-- | date | win | los |
-- | ---------- | ---- | ---- |
-- | 2017-04-09 | 2 | 2 |
-- | 2017-04-10 | 2 | 1 |
-- 方案1
select distinct rq as date,
sum(if(sf = '胜',1,0)) as win,
sum(if(sf = '负',1,0)) as los
from tb_result
group by rq;
-- 方案2
select distinct rq as date,
sum( case sf when '胜' then 1 else 0 end ) as win,
sum( case sf when '负' then 1 else 0 end ) as los
from tb_result
group by rq;
-- 第二题
create table `tb_course`
(
`course_id` int unsigned not null comment '课程编号',
`course_name` varchar(50) not null comment '课程名称',
primary key (`course_id`)
) engine=innodb;
insert into `tb_course` values
(1, 'Python'),
(2, 'Java'),
(3, 'Database'),
(4, 'JavaScript');
create table `tb_open_date`
(
`month` char(6) not null comment '开课日期',
`course_id` int unsigned not null comment '课程编号'
) engine=innodb;
insert into `tb_open_date` values
('202106', 1),
('202106', 3),
('202106', 4),
('202107', 4),
('202108', 2),
('202108', 4);
-- 用上面的表查询出如下所示的结果。
-- | course_name | Jun | Jul | Aug |
-- | ----------- | --- | --- | --- |
-- | Python | o | x | x |
-- | Java | x | x | o |
-- | Database | o | x | x |
-- | JavaScript | o | o | o |
select t1.course_name ,
case right(month,1) when '6' then 'o' else 'X' end as Jun,
case right(month,1) when '7' then 'o' else 'X' end as Jul,
case right(month,1) when '8' then 'o' else 'X' end as Aug
from tb_course as t1 inner join tb_open_date as t2 on t1.course_id = t2.course_id
-- group by course_name
order by t1.course_id asc;
-- 第三题
create table `tb_table1`
(
`userid` int unsigned not null,
`username` varchar(20) not null,
primary key (`userid`)
);
insert into `tb_table1` values
(1, '张三'),
(2, '李四'),
(3, '王五'),
(4, '赵大'),
(5, '孙二');
create table `tb_table2`
(
`month` varchar(20) not null,
`userid` int unsigned not null,
`ye` int not null
);
drop table `tb_table2`;
insert into `tb_table2` values
('一月份', 1, 10),
('一月份', 2, 10),
('一月份', 3, 5),
('一月份', 1, 5),
('二月份', 2, 8),
('二月份', 2, 7),
('二月份', 4, 9),
('三月份', 3, 8),
('三月份', 5, 6);
-- 用上面的tb_table1和tb_table2查询出如下所示的结果。
-- +----------+-----------+-----------+-----------+
-- | 用户名 | 一月份 | 二月份 | 三月份 |
-- +----------+-----------+-----------+-----------+
-- | 张三 | 15 | 0 | 0 |
-- | 李四 | 10 | 15 | 0 |
-- | 王五 | 5 | 0 | 8 |
-- | 赵大 | 0 | 9 | 0 |
-- | 孙二 | 0 | 0 | 6 |
-- +----------+-----------+-----------+-----------+
select username as 用户名,
sum(case month when '一月份' then ye else 0 end ) as '一月份',
sum(case month when '二月份' then ye else 0 end) as '二月份',
sum(case month when '三月份' then ye else 0 end) as '三月份'
from tb_table1 natural join tb_table2
group by username;
### MYSQL 补充中位数
### MySQL 6 视图
use hrs;
create table tb_sales
(
sales_id int unsigned auto_increment,
sales_date date not null comment '销售日期',
sales_area varchar(20) not null comment '销售区域',
sales_channel varchar(20) not null comment '销售渠道',
order_no varchar(50) not null comment '销售订单',
brand varchar(20) not null comment '品牌',
unite_price integer not null comment '售价',
sales_quantity integer not null comment '销售数量'
primary key (sales_id)
) engine=innodb comment '销售明细表';
load data local infile '"C:\Users\Lenovo\Desktop\2020年销售数据.csv"'
into table tb_sales
fields terminated by ','
lines terminated by '\n';
-- Error Code: 3948. Loading local data is disabled; this must be enabled on both the client and server sides
-- ##### 表中的数据导入到数据库和数据库的内容导出到表中 ?
load data local infile '"C:\Users\Lenovo\Desktop\2020年销售数据.csv"'
into table tb_sales
fields terminated by ','
lines terminated by '\n';
-- 1 .查询月度销售金额
select month (sales_date) as 月份,
sum(unite_price*sales_quantity) as 销售额
from tb_sales
group by month(sales_date);
-- 补充：计算按月环比，
-- 环比=（本期数-上期数）/上期数×100%
-- 同比=（本期数－同期数）÷同期数×100%
-- year /quarter/month/day / weekday
-- 窗口函数lag(,数字，默认值0) 可以往回拿几条数据
-- lead( ，数字，默认值) 往后拿几条数据
select 月份,
(本月销售额-上月销售额)/上月销售额 as 环比
from (select 月份,
销售额 as 本月销售额,
lag(销售额,1) over (order by 月份 asc ) as 上月销售额
from(select month (sales_date) as 月份,
sum(unite_price*sales_quantity) as 销售额
from tb_sales
group by month(sales_date)) as temp) as temp;
-- 2. 查询各品牌对销售额贡献的占比
-- 方法1 定义变量，求出总销售额
select @total_sales := sum(unite_price*sales_quantity) as 销售额
from tb_sales;
-- 计算各品牌的销售额贡献占比
select brand as 品牌,
sum(unite_price*sales_quantity)/@total_sales as 销售贡献占比
from tb_sales
group by brand; #这些数据是正确的
-- 方法2 直接用一个，不分开写
select brand as 品牌,
sum(unite_price*sales_quantity)/(select @total_sales := sum(unite_price*sales_quantity) as 销售额
from tb_sales) as 销售贡献占比
from tb_sales
group by brand;
-- with rollup -- 算总计
-- 方法三： 把需要算的东西提前计算好，加入一些辅助列
-- 视图：查询的快照（命名一个一有的查询，下次可以通过这个名字执行对应的查询）
use hrs;
-- 创建视图
create view vw_sales_detail
as -- 别名
select year(sales_date) as 'year', -- 年
quarter(sales_date) as 'quarter', -- 季度
month(sales_date) as sales_month, -- 月
day(sales_date) as sales_day, -- 日
weekday(sales_date) as sales_weekday, -- 星期几
sales_channel, -- 起到
order_no, --
brand, -- 品牌
sales_quantity, -- 数量
unite_price*sales_quantity as sales_amount -- 交易金额
from tb_sales;
-- 查询视图
select * from vw_sales_detail;
-- 也可以这样，创建或替换视图 ，加入一些条件
create view vw_sales_detail_new
as -- 别名
select year(sales_date) as 'year', -- 年
quarter(sales_date) as 'quarter', -- 季度
month(sales_date) as sales_month, -- 月
day(sales_date) as sales_day, -- 日
weekday(sales_date) as sales_weekday, -- 星期几
sales_channel, -- 起到
order_no, --
brand, -- 品牌
case when unite_price <300 then 'low' when unite_price <800 then 'middle' else 'high' end as price_segment, -- 对价格进行等级划分
sales_quantity, -- 数量
unite_price*sales_quantity as sales_amount -- 交易金额
from tb_sales;
-- 1 .查询月度销售金额
select sales_month,
sales_amount
from vw_sales_detail_new
group by sales_month;
-- 借助视图来计算销售额月环比
-- 环比=（本期数-上期数）/上期数×100%
select 月份,
(月销售额-上月销售额)/上月销售额 as 月环比
from (select 月份,
月销售额,
lag(月销售额,1) over (order by 月份 asc )as 上月销售额
from (select sales_month as 月份,
sum(sales_amount) as 月销售额
from vw_sales_detail_new
group by sales_month) as temp ) as temp;
-- 2. 查询各品牌对销售额贡献的占比
select brand,
sales_amount/sum(sales_amount) as 销售额贡献
from vw_sales_detail_new
group by brand;
-- 3. 查询各销售区域月度销售额 --- 宽表
-- 方法1 没有使用视图，再原表中进行操作
select sales_area as 渠道,
month(sales_date) as 月份,
sum(case month(sales_date) when 1 then unite_price*sales_quantity else 0 end ) as '1月销售额',
sum(case month(sales_date) when 2 then unite_price*sales_quantity else 0 end ) as '2月销售额',
sum(case month(sales_date) when 3 then unite_price*sales_quantity else 0 end ) as '3月销售额',
sum(case month(sales_date) when 4 then unite_price*sales_quantity else 0 end ) as '4月销售额',
sum(case month(sales_date) when 5 then unite_price*sales_quantity else 0 end ) as '5月销售额',
sum(case month(sales_date) when 6 then unite_price*sales_quantity else 0 end ) as '6月销售额',
sum(case month(sales_date) when 7 then unite_price*sales_quantity else 0 end ) as '7月销售额',
sum(case month(sales_date) when 8 then unite_price*sales_quantity else 0 end ) as '8月销售额',
sum(case month(sales_date) when 9 then unite_price*sales_quantity else 0 end ) as '9月销售额',
sum(case month(sales_date) when 10 then unite_price*sales_quantity else 0 end ) as '10月销售额',
sum(case month(sales_date) when 11 then unite_price*sales_quantity else 0 end ) as '11月销售额',
sum(case month(sales_date) when 12 then unite_price*sales_quantity else 0 end ) as '12月销售额'
from tb_sales
group by sales_area;
-- 方法2 使用视图，先根据需要的数据，键视图表
-- 3. 查询各销售区域月度销售额 --- 宽表
create view vw_sales_detail_add
as
select sales_area,
month(sales_date) as sales_month,
unite_price * sales_quantity as sales_amount
from tb_sales;
-- 3. 查询各销售区域月度销售额 --- 宽表
select sales_area,
sum(case sales_month when 1 then sales_amount else 0 end ) as '1月销售额',
sum(case sales_month when 2 then sales_amount else 0 end ) as '2月销售额',
sum(case sales_month when 3 then sales_amount else 0 end ) as '3月销售额',
sum(case sales_month when 4 then sales_amount else 0 end ) as '4月销售额',
sum(case sales_month when 5 then sales_amount else 0 end ) as '5月销售额',
sum(case sales_month when 6 then sales_amount else 0 end ) as '6月销售额',
sum(case sales_month when 7 then sales_amount else 0 end ) as '7月销售额',
sum(case sales_month when 8 then sales_amount else 0 end ) as '8月销售额',
sum(case sales_month when 9 then sales_amount else 0 end ) as '9月销售额',
sum(case sales_month when 10 then sales_amount else 0 end ) as '10月销售额',
sum(case sales_month when 11 then sales_amount else 0 end ) as '11月销售额',
sum(case sales_month when 12 then sales_amount else 0 end ) as '12月销售额'
from vw_sales_detail_add
group by sales_area;
-- 4. 查询各渠道品牌销量 -- 宽表
select sales_channel as 销售渠道,
sum(case brand when '八匹马' then sales_quantity else 0 end ) as '八匹马',
sum(case brand when '皮皮虾' then sales_quantity else 0 end )as '皮皮虾',
sum(case brand when '壁虎' then sales_quantity else 0 end ) as '壁虎',
sum(case brand when '花花姑娘' then sales_quantity else 0 end) as '花花姑娘',
sum(case brand when '啊哟喂' then sales_quantity else 0 end ) as '啊哟喂'
from vw_sales_detail_new
group by 销售渠道;
-- 5. 不同价格区间产品的月度销量
select price_segment as 价格区间,
sales_month as 月份,
sum(sales_quantity) as 销量
from vw_sales_detail_new
group by sales_month ;
-- 按照不同的价格区间做月度销量分析
-- 写成宽表
-- 根据价格段进行分组
use hrs;
select sales_month as 月份,
sum(case price_segment when 'low' then sales_quantity else 'o' end) as '低价销售量',
sum(case price_segment when 'middle' then sales_quantity else 'o' end) as '中价销售量',
sum(case price_segment when 'high' then sales_quantity else 'o' end) as '高价销售量'
from vw_sales_detail_new
group by sales_month;
use hrs;
### MySQL 7 函数
use school;
-- 函数 ：功能上相对独立且会被重复使用的代码 --- 用户自定义函数
-- 如果学院介绍超过了50个字符，截断它后补上省略号
-- 如果学院介绍没有超过50个字符，就完整到的显示出来
select col_id,
col_name,
case when char_length(col_intro) > 50 then concat( left(col_intro,50) , '......' ) else col_intro end as col_intro
from tb_college;
-- 修改代码终止符
--函数只能的分号表示一个语句的结束，而不是整个代码的结束
delimiter $$
-- 创建函数 User Defined Function
-- reads sql data 如果使用了sql语句，在最后面要加一个说明，没有使用sql语句，就写no sql
-- 自定义一个不需要传参的函数
delimiter $$
create function say_hello() returns varchar(16383) reads sql data no sql
begin
return 'hello,world';
end $$
delimiter ;
use school;
-- 练习 from dual 是一个虚拟表，只是让代码完整。
select say_hello() from dual;
-- 自定义一个需要传参的函数
delimiter $$
create function say_name( tb_name varchar(30)) returns varchar(16383) reads sql data no sql
begin
return concat('hello, ',tb_name , '!');
end $$
delimiter ;
select say_name('tom') from dual;
select say_name('kaixing') from dual;
-- 把字符串截断变成函数
delimiter $$
create function truncate_string(
content varchar(16383),
max_length int unsigned
) returns varchar (16383) no sql
begin
if char_length(content) > max_length then
return concat(left(content,max_length),'......');
else
return content;
end if;
end $$
delimiter ;
-- 可以定义变量，让函数变得更简洁
delimiter $$
create function truncate_string(
content varchar(16383),
max_length int unsigned
) returns varchar (16383) no sql
begin
declare result varchar(16383) default content;
if char_length(content) > max_length then
set result=concat(left(content,max_length),'......');
end if;
return result;
end $$
delimiter ;
use school;
-- 使用自定义函数来解决前面的问题
select col_id,
col_name,
truncate_string(col_intro,50)
from tb_college ;
##### practice
-- 杨过 --> 杨*
-- 杨不悔-- > 杨*悔
-- 东方不败--> 东**败
##### Practice 2 YY
-- 1：函数和过程有什么用？二者有什么区别？
-- 2：base64是什么？
-- 1. BASE64编码的原理和应用场景
-- 2. 自定义函数实现保护姓名隐私
-- 3. 函数和过程有什么作用？二者有何区别？
-- 4. 绘制school数据库对应的ER图
drop function if exists protect_name;
use school;
delimiter $$
create function protect_name(thy_name varchar(16383))
returns varchar(16383) no sql
begin
declare result varchar(16383) default thy_name;
if char_length(thy_name) = 2 then
set result = concat(left(thy_name,1),'*');
else
set result = concat(left(thy_name,1),repeat('*',(char_length(thy_name)-2)),right(thy_name,1));
end if;
return result;
end$$
delimiter ;
select protect_name(stu_name)
from tb_student;
### MySQL 8 JSON数据查询
use hrs;
-- json 对象的数据查询
drop table if exists `tb_test`;
create table `tb_test`
(
user_id bigint unsigned,
login_info json,
primary key (`user_id`)
)engine=innodb;
insert into `tb_test` values
(1, '{"tel": "13122335566", "QQ": "654321", "wechat": "jackfrued"}'),
(2, '{"tel": "13599876543", "weibo": "wangdachui123"}'),
(3, '{"wechat": "luohao"}');
select
user_id,
json_unquote(json_extract(login_info, '$.tel')) as 手机号
from tb_test;
select
`user_id`,
json_unquote(json_extract(`login_info`, '$.tel')) as 手机号,
json_unquote(json_extract(`login_info`, '$.wechat')) as 微信
from `tb_test`;
select
`user_id`,
`login_info` ->> '$.tel' as 手机号,
`login_info` ->> '$.wechat' as 微信
from `tb_test`;
--***** 用户打标签
create table `tb_tags`
(
`tag_id` int unsigned not null comment '标签ID',
`tag_name` varchar(20) not null comment '标签名',
primary key (`tag_id`)
) engine=innodb;
insert into `tb_tags` (`tag_id`, `tag_name`)
values
(1, '70后'),
(2, '80后'),
(3, '90后'),
(4, '00后'),
(5, '爱运动'),
(6, '高学历'),
(7, '小资'),
(8, '有房'),
(9, '有车'),
(10, '爱看电影'),
(11, '爱网购'),
(12, '常点外卖');
drop table if exists `tb_users_tags`;
create table `tb_users_tags`
(
`user_id` bigint unsigned not null comment '用户ID',
`user_tags` json not null comment '用户标签'
) engine=innodb;
insert into `tb_users_tags` values
(1, '[2, 6, 8, 10]'),
(2, '[3, 10, 12]'),
(3, '[3, 8, 9, 11]');
delete from tb_users_tags where user_id=1;
-- 查询爱看电影（有10这个标签）的用户ID
select user_id
from tb_users_tags
where 10 member of (user_tags -> '$'); -- $代表的是user_tags的数组
-- 查询爱看（10）电影的80后（2）
-- json_contains( 列名-> '$' ,'[ 2,10]') 前面的数组有么有包含后面的数组（超集）
select user_id
from tb_users_tags
where json_contains (user_tags -> '$','[2,10]');
-- 查询爱看电影或80或90后的用户ID
-- json_overlaps(user_tags ->'$','[2,3,10]' ) $与[2,3,10] 这两个是不是有交集，有交集就留下来
select user_id
from tb_users_tags
where json_overlaps(user_tags -> '$','[2,3,10]');
### MySQL 9 过程与索引
-- 科普知识
-- * 把明文变成密文的过程叫加密，把密文变回明文的过程叫做解密
-- 对称加密：加密和解密是同一个密密匙
-- 非对称加密：加密和集美不是一个密钥，有公钥和私钥之分。
-- 摘要： 通过一个单向哈希函数给一个对象生成一个签名（指纹）。
-- MD5/SHA1/SHA256
-- 编码：把一种表示方式转换成另外的表示方式，逆操作叫做解码。
-- 单机 —> 多级 复杂均衡 分布式集群 数据如何同步？？
-- 事务transaction - 把多个写数据的操作（增删改）视为不可分割的原子性操作，要么全都成功，要么全都失败。 程序 ：procedure
-- 过程学习
-- 互联网会因为遭遇高并发和大流量，所以不推荐使用存储过程，使用存储过程会增加
use hrs;
-- 工资普调
-- 规则 ： 10部门 +500, 20部门+1000 ，30部门+300
update tb_emp set sal = sal+50 where dno = 10;
update tb_emp set sal = sal+1000 where dno = 20;
update tb_emp set sal = sal+300 where dno = 30;
-- TCL----transction Control Language
-- start transaction /begin /commit /roolback
-- 如何判断代码执行是否成功，方法，如果代码出现异常，代码会打断，所有加一个异常处理器，
-- 存储过程的参数，两个，一个输入参数in（把信息代入过程中）和输出参数out（把数据带出过程），变量名前面加@表示这个变量是用户自定义的一个变量
-- 想要把过程得到的数据，拿出来用，就要加入输出参数，看例2
-- 例1
-- 使用过程来完成调薪：
delimiter $$
create procedure sp_upgrade_salary( ) -- 如果需要参数，可以传参。
begin
declare flag boolean default 1;
declare continue handler for sqlexception set flag = 0;
start transaction;
-- 下面这个三条命令是原子性操作，必须全部成功。要么全部失败
update tb_emp set sal = sal+500 where dno = 10;
update tb_emp set sal = sal+1000 where dno = 20;
update tb_emp set sal = sal+300 where dno = 30;
if falg then
commit;
else
rollback ;
end if ;
end $$
delimiter ;
-- 调用过程的程序
call sp_upgrade_salary();
-- mysql必知必会上第24章会讲到使用游标cursor；来抓取二维表数据
drop procedure sp_upgrade_salary # 删除存储过程
-- 例2
delimiter $$
create procedure sp_upgrade_salary( out avg_sal decimal(10,2)) -- 如果需要参数，可以传参out 是输出参数，通过输出参数，将过程中的数据带出去使用。
begin
declare flag boolean default 1;
declare continue handler for sqlexception set flag = 0;
start transaction;
-- 下面这个三条命令是原子性操作，必须全部成功。要么全部失败
update tb_emp set sal = sal+500 where dno = 10;
update tb_emp set sal = sal+1000 where dno = 20;
update tb_emp set sal = sal+300 where dno = 30;
if falg then
commit;
else
rollback ;
end if ;
select avg(sal) into avg_sal from tb_emp;
end $$
delimiter ;
-- 调用过程存储程序
call sp_upgrade_salary(@x);
select @x;
select @x from tb_emp where sal > @x ;
推荐阅读
-
mysql 查询所有queryid MySQL 查询所有女生学号
-
SSM三大框架基础面试题-一、Spring篇 什么是Spring框架？ Spring是一种轻量级框架，提高开发人员的开发效率以及系统的可维护性。 我们一般说的Spring框架就是Spring Framework，它是很多模块的集合，使用这些模块可以很方便地协助我们进行开发。这些模块是核心容器、数据访问/集成、Web、AOP（面向切面编程）、工具、消息和测试模块。比如Core Container中的Core组件是Spring所有组件的核心，Beans组件和Context组件是实现IOC和DI的基础，AOP组件用来实现面向切面编程。 Spring的6个特征： 核心技术：依赖注入（DI），AOP，事件（Events），资源，i18n，验证，数据绑定，类型转换，SpEL。 测试：模拟对象，TestContext框架，Spring MVC测试，WebTestClient。 数据访问：事务，DAO支持，JDBC，ORM，编组XML。 Web支持：Spring MVC和Spring WebFlux Web框架。 集成：远程处理，JMS，JCA，JMX，电子邮件，任务，调度，缓存。 语言：Kotlin，Groovy，动态语言。 列举一些重要的Spring模块？ Spring Core：核心，可以说Spring其他所有的功能都依赖于该类库。主要提供IOC和DI功能。 Spring Aspects：该模块为与AspectJ的集成提供支持。 Spring AOP：提供面向切面的编程实现。 Spring JDBC：Java数据库连接。 Spring JMS：Java消息服务。 Spring ORM：用于支持Hibernate等ORM工具。 Spring Web：为创建Web应用程序提供支持。 Spring Test：提供了对JUnit和TestNG测试的支持。 谈谈自己对于Spring IOC和AOP的理解 IOC（Inversion Of Controll，控制反转）是一种设计思想： 在程序中手动创建对象的控制权，交由给Spring框架来管理。IOC在其他语言中也有应用，并非Spring特有。IOC容器实际上就是一个Map(key, value)，Map中存放的是各种对象。 将对象之间的相互依赖关系交给IOC容器来管理，并由IOC容器完成对象的注入。这样可以很大程度上简化应用的开发，把应用从复杂的依赖关系中解放出来。IOC容器就像是一个工厂一样，当我们需要创建一个对象的时候，只需要配置好配置文件/注解即可，完全不用考虑对象是如何被创建出来的。在实际项目中一个Service类可能由几百甚至上千个类作为它的底层，假如我们需要实例化这个Service，可能要每次都搞清楚这个Service所有底层类的构造函数，这可能会把人逼疯。如果利用IOC的话，你只需要配置好，然后在需要的地方引用就行了，大大增加了项目的可维护性且降低了开发难度。 Spring中的bean的作用域有哪些？ 1.singleton：该bean实例为单例 2.prototype：每次请求都会创建一个新的bean实例（多例）。 3.request：每一次HTTP请求都会产生一个新的bean，该bean仅在当前HTTP request内有效。 4.session：每一次HTTP请求都会产生一个新的bean，该bean仅在当前HTTP session内有效。 5.global-session：全局session作用域，仅仅在基于Portlet的Web应用中才有意义，Spring5中已经没有了。Portlet是能够生成语义代码（例如HTML）片段的小型Java Web插件。它们基于Portlet容器，可以像Servlet一样处理HTTP请求。但是与Servlet不同，每个Portlet都有不同的会话。 Spring中的单例bean的线程安全问题了解吗？ 概念用于理解：大部分时候我们并没有在系统中使用多线程，所以很少有人会关注这个问题。单例bean存在线程问题，主要是因为当多个线程操作同一个对象的时候，对这个对象的非静态成员变量的写操作会存在线程安全问题。 有两种常见的解决方案（用于回答的点）： 1.在bean对象中尽量避免定义可变的成员变量（不太现实）。 2.在类中定义一个ThreadLocal成员变量，将需要的可变成员变量保存在ThreadLocal(线程本地化对象)中（推荐的一种方式）。 ThreadLocal解决多线程变量共享问题（参考博客）：https://segmentfault.com/a/1190000009236777 Spring中Bean的生命周期： 1.Bean容器找到配置文件中Spring Bean的定义。 2.Bean容器利用Java Reflection API创建一个Bean的实例。 3.如果涉及到一些属性值，利用set方法设置一些属性值。 4.如果Bean实现了BeanNameAware接口，调用setBeanName方法，传入Bean的名字。 5.如果Bean实现了BeanClassLoaderAware接口，调用setBeanClassLoader方法，传入ClassLoader对象的实例。 6.如果Bean实现了BeanFactoryAware接口，调用setBeanClassFacotory方法，传入ClassLoader对象的实例。 7.与上面的类似，如果实现了其他*Aware接口，就调用相应的方法。 8.如果有和加载这个Bean的Spring容器相关的BeanPostProcessor对象，执postProcessBeforeInitialization方法。 9.如果Bean实现了InitializingBean接口，执行afeterPropertiesSet方法。 10.如果Bean在配置文件中的定义包含init-method属性，执行指定的方法。 11.如果有和加载这个Bean的Spring容器相关的BeanPostProcess对象，执行postProcessAfterInitialization方法。 12.当要销毁Bean的时候，如果Bean实现了DisposableBean接口，执行destroy方法。 13.当要销毁Bean的时候，如果Bean在配置文件中的定义包含destroy-method属性，执行指定的方法。 Spring框架中用到了哪些设计模式？ 1.工厂设计模式：Spring使用工厂模式通过BeanFactory和ApplicationContext创建bean对象。 2.代理设计模式：Spring AOP功能的实现。 3.单例设计模式：Spring中的bean默认都是单例的。 4.模板方法模式：Spring中的jdbcTemplate、hibernateTemplate等以Template结尾的对数据库操作的类，它们就使用到了模板模式。 5.包装器设计模式：我们的项目需要连接多个数据库，而且不同的客户在每次访问中根据需要会去访问不同的数据库。这种模式让我们可以根据客户的需求能够动态切换不同的数据源。 6.观察者模式：Spring事件驱动模型就是观察者模式很经典的一个应用。 7.适配器模式：Spring AOP的增强或通知（Advice）使用到了适配器模式、Spring MVC中也是用到了适配器模式适配Controller。 还有很多。。。。。。。 @Component和@Bean的区别是什么 1.作用对象不同。@Component注解作用于类，而@Bean注解作用于方法。 2.@Component注解通常是通过类路径扫描来自动侦测以及自动装配到Spring容器中（我们可以使用@ComponentScan注解定义要扫描的路径）。@Bean注解通常是在标有该注解的方法中定义产生这个bean，告诉Spring这是某个类的实例，当我需要用它的时候还给我。 3.@Bean注解比@Component注解的自定义性更强，而且很多地方只能通过@Bean注解来注册bean。比如当引用第三方库的类需要装配到Spring容器的时候，就只能通过@Bean注解来实现。 @Configuration public class AppConfig { @Bean public TransferService transferService { return new TransferServiceImpl; } } <beans> <bean id="transferService" class="com.kk.TransferServiceImpl"/> </beans> @Bean public OneService getService(status) { case (status) { when 1: return new serviceImpl1; when 2: return new serviceImpl2; when 3: return new serviceImpl3; } } 将一个类声明为Spring的bean的注解有哪些？ 声明bean的注解： @Component 组件，没有明确的角色 @Service 在业务逻辑层使用（service层） @Repository 在数据访问层使用（dao层） @Controller 在展现层使用，控制器的声明 注入bean的注解： @Autowired：由Spring提供 @Inject：由JSR-330提供 @Resource：由JSR-250提供 *扩：JSR 是 java 规范标准 Spring事务管理的方式有几种？ 1.编程式事务：在代码中硬编码（不推荐使用）。 2.声明式事务：在配置文件中配置（推荐使用），分为基于XML的声明式事务和基于注解的声明式事务。 Spring事务中的隔离级别有哪几种？ 在TransactionDefinition接口中定义了五个表示隔离级别的常量：ISOLATION_DEFAULT：使用后端数据库默认的隔离级别，Mysql默认采用的REPEATABLE_READ隔离级别；Oracle默认采用的READ_COMMITTED隔离级别。ISOLATION_READ_UNCOMMITTED：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。ISOLATION_READ_COMMITTED：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生ISOLATION_REPEATABLE_READ：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。ISOLATION_SERIALIZABLE：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。但是这将严重影响程序的性能。通常情况下也不会用到该级别。 Spring事务中有哪几种事务传播行为？ 在TransactionDefinition接口中定义了八个表示事务传播行为的常量。 支持当前事务的情况：PROPAGATION_REQUIRED：如果当前存在事务，则加入该事务；如果当前没有事务，则创建一个新的事务。PROPAGATION_SUPPORTS： 如果当前存在事务，则加入该事务；如果当前没有事务，则以非事务的方式继续运行。PROPAGATION_MANDATORY： 如果当前存在事务，则加入该事务；如果当前没有事务，则抛出异常。（mandatory：强制性）。 不支持当前事务的情况：PROPAGATION_REQUIRES_NEW： 创建一个新的事务，如果当前存在事务，则把当前事务挂起。PROPAGATION_NOT_SUPPORTED： 以非事务方式运行，如果当前存在事务，则把当前事务挂起。PROPAGATION_NEVER： 以非事务方式运行，如果当前存在事务，则抛出异常。 其他情况：PROPAGATION_NESTED： 如果当前存在事务，则创建一个事务作为当前事务的嵌套事务来运行；如果当前没有事务，则该取值等价于PROPAGATION_REQUIRED。 二、SpringMVC篇 什么是Spring MVC ？简单介绍下你对springMVC的理解? Spring MVC是一个基于Java的实现了MVC设计模式的请求驱动类型的轻量级Web框架，通过把Model，View，Controller分离，将web层进行职责解耦，把复杂的web应用分成逻辑清晰的几部分，简化开发，减少出错，方便组内开发人员之间的配合。 Spring MVC的工作原理了解嘛？ image.png Springmvc的优点: （1）可以支持各种视图技术,而不仅仅局限于JSP； （2）与Spring框架集成（如IoC容器、AOP等）； （3）清晰的角色分配：前端控制器(dispatcherServlet) , 请求到处理器映射（handlerMapping), 处理器适配器（HandlerAdapter), 视图解析器（ViewResolver）。 （4） 支持各种请求资源的映射策略。 Spring MVC的主要组件？ （1）前端控制器 DispatcherServlet（不需要程序员开发） 作用：接收请求、响应结果，相当于转发器，有了DispatcherServlet 就减少了其它组件之间的耦合度。 （2）处理器映射器HandlerMapping（不需要程序员开发） 作用：根据请求的URL来查找Handler （3）处理器适配器HandlerAdapter 注意：在编写Handler的时候要按照HandlerAdapter要求的规则去编写，这样适配器HandlerAdapter才可以正确的去执行Handler。 （4）处理器Handler（需要程序员开发） （5）视图解析器 ViewResolver（不需要程序员开发） 作用：进行视图的解析，根据视图逻辑名解析成真正的视图（view） （6）视图View（需要程序员开发jsp） View是一个接口， 它的实现类支持不同的视图类型（jsp，freemarker，pdf等等） springMVC和struts2的区别有哪些? （1）springmvc的入口是一个servlet即前端控制器（DispatchServlet），而struts2入口是一个filter过虑器（StrutsPrepareAndExecuteFilter）。 （2）springmvc是基于方法开发(一个url对应一个方法)，请求参数传递到方法的形参，可以设计为单例或多例(建议单例)，struts2是基于类开发，传递参数是通过类的属性，只能设计为多例。 （3）Struts采用值栈存储请求和响应的数据，通过OGNL存取数据，springmvc通过参数解析器是将request请求内容解析，并给方法形参赋值，将数据和视图封装成ModelAndView对象，最后又将ModelAndView中的模型数据通过reques域传输到页面。Jsp视图解析器默认使用jstl。 SpringMVC怎么样设定重定向和转发的？ （1）转发：在返回值前面加"forward:"，譬如"forward:user.do?name=method4" （2）重定向：在返回值前面加"redirect:"，譬如"redirect:http://www.baidu.com" SpringMvc怎么和AJAX相互调用的？ 通过Jackson框架就可以把Java里面的对象直接转化成Js可以识别的Json对象。具体步骤如下 ： （1）加入Jackson.jar （2）在配置文件中配置json的映射 （3）在接受Ajax方法里面可以直接返回Object,List等,但方法前面要加上@ResponseBody注解。 如何解决POST请求中文乱码问题，GET的又如何处理呢？ （1）解决post请求乱码问题： 在web.xml中配置一个CharacterEncodingFilter过滤器，设置成utf-8； <filter> <filter-name>CharacterEncodingFilter</filter-name> <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class> <init-param> <param-name>encoding</param-name> <param-value>utf-8</param-value> </init-param> </filter> <filter-mapping> <filter-name>CharacterEncodingFilter</filter-name> <url-pattern>/*</url-pattern> </filter-mapping> （2）get请求中文参数出现乱码解决方法有两个： ①修改tomcat配置文件添加编码与工程编码一致，如下： <ConnectorURIEncoding="utf-8" connectionTimeout="20000" port="8080" protocol="HTTP/1.1" redirectPort="8443"/> ②另外一种方法对参数进行重新编码： String userName = new String(request.getParamter("userName").getBytes("ISO8859-1"),"utf-8") ISO8859-1是tomcat默认编码，需要将tomcat编码后的内容按utf-8编码。 Spring MVC的异常处理 ？ 统一异常处理： Spring MVC处理异常有3种方式： （1）使用Spring MVC提供的简单异常处理器SimpleMappingExceptionResolver； （2）实现Spring的异常处理接口HandlerExceptionResolver 自定义自己的异常处理器； （3）使用@ExceptionHandler注解实现异常处理； 统一异常处理的博客：https://blog.csdn.net/ctwy291314/article/details/81983103 SpringMVC的控制器是不是单例模式,如果是,有什么问题,怎么解决？ 是单例模式,所以在多线程访问的时候有线程安全问题,不要用同步,会影响性能的,解决方案是在控制器里面不能写成员变量。（此题目类似于上面Spring 中 第5题 有两种解决方案） SpringMVC常用的注解有哪些？ @RequestMapping：用于处理请求 url 映射的注解，可用于类或方法上。用于类上，则表示类中的所有响应请求的方法都是以该地址作为父路径。 @RequestBody：注解实现接收http请求的json数据，将json转换为java对象。 @ResponseBody：注解实现将conreoller方法返回对象转化为json对象响应给客户。 SpingMvc中的控制器的注解一般用那个,有没有别的注解可以替代？ 一般用@Controller注解,也可以使用@RestController,@RestController注解相当于@ResponseBody ＋ @Controller,表示是表现层,除此之外，一般不用别的注解代替。 如果在拦截请求中，我想拦截get方式提交的方法,怎么配置？ 可以在@RequestMapping注解里面加上method=RequestMethod.GET。 怎样在方法里面得到Request,或者Session？ 直接在方法的形参中声明request,SpringMVC就自动把request对象传入。 如果想在拦截的方法里面得到从前台传入的参数,怎么得到？ 直接在形参里面声明这个参数就可以,但必须名字和传过来的参数一样。 如果前台有很多个参数传入,并且这些参数都是一个对象的,那么怎么样快速得到这个对象？ 直接在方法中声明这个对象,SpringMVC就自动会把属性赋值到这个对象里面。 SpringMVC中函数的返回值是什么？ 返回值可以有很多类型,有String, ModelAndView。ModelAndView类把视图和数据都合并的一起的。 SpringMVC用什么对象从后台向前台传递数据的？ 通过ModelMap对象,可以在这个对象里面调用put方法,把对象加到里面,前台就可以拿到数据。 怎么样把ModelMap里面的数据放入Session里面？ 可以在类上面加上@SessionAttributes注解,里面包含的字符串就是要放入session里面的key。 SpringMvc里面拦截器是怎么写的： 有两种写法,一种是实现HandlerInterceptor接口，另外一种是继承适配器类，接着在接口方法当中，实现处理逻辑；然后在SpringMvc的配置文件中配置拦截器即可： <!-- 配置SpringMvc的拦截器 --> <mvc:interceptors> <!-- 配置一个拦截器的Bean就可以了 默认是对所有请求都拦截 --> <bean id="myInterceptor" class="com.zwp.action.MyHandlerInterceptor"></bean> <!-- 只针对部分请求拦截 --> <mvc:interceptor> <mvc:mapping path="/modelMap.do" /> <bean class="com.zwp.action.MyHandlerInterceptorAdapter" /> </mvc:interceptor> </mvc:interceptors> 注解原理： 注解本质是一个继承了Annotation的特殊接口，其具体实现类是Java运行时生成的动态代理类。我们通过反射获取注解时，返回的是Java运行时生成的动态代理对象。通过代理对象调用自定义注解的方法，会最终调用AnnotationInvocationHandler的invoke方法。该方法会从memberValues这个Map中索引出对应的值。而memberValues的来源是Java常量池 三、Mybatis篇 什么是MyBatis？ MyBatis是一个可以自定义SQL、存储过程和高级映射的持久层框架。 讲下MyBatis的缓存 MyBatis的缓存分为一级缓存和二级缓存,一级缓存放在session里面,默认就有, 二级缓存放在它的命名空间里,默认是不打开的,使用二级缓存属性类需要实现Serializable序列化接口, 可在它的映射文件中配置<cache/> Mybatis是如何进行分页的？分页插件的原理是什么？ 1）Mybatis使用RowBounds对象进行分页，也可以直接编写sql实现分页，也可以使用Mybatis的分页插件。 2）分页插件的原理：实现Mybatis提供的接口，实现自定义插件，在插件的拦截方法内拦截待执行的sql，然后重写sql。 举例：select * from student，拦截sql后重写为：select t.* from （select * from student）t limit 0，10 简述Mybatis的插件运行原理，以及如何编写一个插件？ 1）Mybatis仅可以编写针对ParameterHandler、ResultSetHandler、StatementHandler、 Executor这4种接口的插件，Mybatis通过动态代理， 为需要拦截的接口生成代理对象以实现接口方法拦截功能， 每当执行这4种接口对象的方法时，就会进入拦截方法， 具体就是InvocationHandler的invoke方法，当然， 只会拦截那些你指定需要拦截的方法。 2）实现Mybatis的Interceptor接口并复写intercept方法， 然后在给插件编写注解，指定要拦截哪一个接口的哪些方法即可， 记住，别忘了在配置文件中配置你编写的插件。 Mybatis动态sql是做什么的？都有哪些动态sql？能简述一下动态sql的执行原理不？ 1）Mybatis动态sql可以让我们在Xml映射文件内， 以标签的形式编写动态sql，完成逻辑判断和动态拼接sql的功能。 2）Mybatis提供了9种动态sql标签：trim|where|set|foreach|if|choose|when|otherwise|bind。 3）其执行原理为，使用OGNL从sql参数对象中计算表达式的值， 根据表达式的值动态拼接sql，以此来完成动态sql的功能。 #{}和${}的区别是什么？ 1）#{}是预编译处理，${}是字符串替换。 2）Mybatis在处理#{}时，会将sql中的#{}替换为?号，调用PreparedStatement的set方法来赋值(有效的防止SQL注入)； 3）Mybatis在处理${}时，就是把${}替换成变量的值。 为什么说Mybatis是半自动ORM映射工具？它与全自动的区别在哪里？ Hibernate属于全自动ORM映射工具， 使用Hibernate查询关联对象或者关联集合对象时， 可以根据对象关系模型直接获取，所以它是全自动的。 而Mybatis在查询关联对象或关联集合对象时， 需要手动编写sql来完成，所以，称之为半自动ORM映射工具。 Mybatis是否支持延迟加载？如果支持，它的实现原理是什么？ 1）Mybatis仅支持association关联对象和collection关联集合对象的延迟加载， association指的就是一对一，collection指的就是一对多查询。 在Mybatis配置文件中， 可以配置是否启用延迟加载lazyLoadingEnabled=true|false。 2）它的原理是，使用CGLIB创建目标对象的代理对象， 当调用目标方法时，进入拦截器方法， 比如调用a.getB.getName， 拦截器invoke方法发现a.getB是null值， 那么就会单独发送事先保存好的查询关联B对象的sql， 把B查询上来，然后调用a.setB(b)， 于是a的对象b属性就有值了， 接着完成a.getB.getName方法的调用。 这就是延迟加载的基本原理。 MyBatis与Hibernate有哪些不同？ 1）Mybatis和hibernate不同，它不完全是一个ORM框架， 因为MyBatis需要程序员自己编写Sql语句， 不过mybatis可以通过XML或注解方式灵活配置要运行的sql语句， 并将java对象和sql语句映射生成最终执行的sql， 最后将sql执行的结果再映射生成java对象。 2）Mybatis学习门槛低，简单易学，程序员直接编写原生态sql， 可严格控制sql执行性能，灵活度高，非常适合对关系数据模型要求不高的软件开发， 例如互联网软件、企业运营类软件等，因为这类软件需求变化频繁， 一但需求变化要求成果输出迅速。但是灵活的前提是mybatis无法做到数据库无关性， 如果需要实现支持多种数据库的软件则需要自定义多套sql映射文件，工作量大。 3）Hibernate对象/关系映射能力强，数据库无关性好， 对于关系模型要求高的软件（例如需求固定的定制化软件） 如果用hibernate开发可以节省很多代码，提高效率。 但是Hibernate的缺点是学习门槛高，要精通门槛更高， 而且怎么设计O/R映射，在性能和对象模型之间如何权衡， 以及怎样用好Hibernate需要具有很强的经验和能力才行。 总之，按照用户的需求在有限的资源环境下只要能做出维护性、 扩展性良好的软件架构都是好架构，所以框架只有适合才是最好。 MyBatis的好处是什么？ 1）MyBatis把sql语句从Java源程序中独立出来，放在单独的XML文件中编写， 给程序的维护带来了很大便利。 2）MyBatis封装了底层JDBC API的调用细节，并能自动将结果集转换成Java Bean对象， 大大简化了Java数据库编程的重复工作。 3）因为MyBatis需要程序员自己去编写sql语句， 程序员可以结合数据库自身的特点灵活控制sql语句， 因此能够实现比Hibernate等全自动orm框架更高的查询效率，能够完成复杂查询。 简述Mybatis的Xml映射文件和Mybatis内部数据结构之间的映射关系？ Mybatis将所有Xml配置信息都封装到All-In-One重量级对象Configuration内部。 在Xml映射文件中，<parameterMap>标签会被解析为ParameterMap对象， 其每个子元素会被解析为ParameterMapping对象。 <resultMap>标签会被解析为ResultMap对象， 其每个子元素会被解析为ResultMapping对象。 每一个<select>、<insert>、<update>、<delete> 标签均会被解析为MappedStatement对象， 标签内的sql会被解析为BoundSql对象。 什么是MyBatis的接口绑定,有什么好处？ 接口映射就是在MyBatis中任意定义接口,然后把接口里面的方法和SQL语句绑定, 我们直接调用接口方法就可以,这样比起原来了SqlSession提供的方法我们可以有更加灵活的选择和设置. 接口绑定有几种实现方式,分别是怎么实现的? 接口绑定有两种实现方式,一种是通过注解绑定,就是在接口的方法上面加 上@Select@Update等注解里面包含Sql语句来绑定, 另外一种就是通过xml里面写SQL来绑定,在这种情况下, 要指定xml映射文件里面的namespace必须为接口的全路径名. 什么情况下用注解绑定,什么情况下用xml绑定？ 当Sql语句比较简单时候,用注解绑定；当SQL语句比较复杂时候,用xml绑定,一般用xml绑定的比较多 MyBatis实现一对一有几种方式?具体怎么操作的？ 有联合查询和嵌套查询,联合查询是几个表联合查询,只查询一次, 通过在resultMap里面配置association节点配置一对一的类就可以完成; 嵌套查询是先查一个表,根据这个表里面的结果的外键id, 去再另外一个表里面查询数据,也是通过association配置, 但另外一个表的查询通过select属性配置。 Mybatis能执行一对一、一对多的关联查询吗？都有哪些实现方式，以及它们之间的区别？ 能，Mybatis不仅可以执行一对一、一对多的关联查询， 还可以执行多对一，多对多的关联查询，多对一查询， 其实就是一对一查询，只需要把selectOne修改为selectList即可； 多对多查询，其实就是一对多查询，只需要把selectOne修改为selectList即可。 关联对象查询，有两种实现方式，一种是单独发送一个sql去查询关联对象， 赋给主对象，然后返回主对象。另一种是使用嵌套查询，嵌套查询的含义为使用join查询， 一部分列是A对象的属性值，另外一部分列是关联对象B的属性值， 好处是只发一个sql查询，就可以把主对象和其关联对象查出来。 MyBatis里面的动态Sql是怎么设定的?用什么语法? MyBatis里面的动态Sql一般是通过if节点来实现,通过OGNL语法来实现, 但是如果要写的完整,必须配合where,trim节点,where节点是判断包含节点有 内容就插入where,否则不插入,trim节点是用来判断如果动态语句是以and 或or 开始,那么会自动把这个and或者or取掉。 Mybatis是如何将sql执行结果封装为目标对象并返回的？都有哪些映射形式？ 第一种是使用<resultMap>标签，逐一定义列名和对象属性名之间的映射关系。 第二种是使用sql列的别名功能，将列别名书写为对象属性名， 比如T_NAME AS NAME，对象属性名一般是name，小写， 但是列名不区分大小写，Mybatis会忽略列名大小写，
-
mysql 查询一个班级有多少男生和女生 sql 查询一个班级有多少男生和女生