#如下都是单表查询
#1.查询学生表所有列所有数据 慎用
#注意：不建议使用*，有些公司可能禁用*
#https://www.cnblogs.com/huangsheng/p/6857503.html
#①给数据消费者传数据的低效，因为我们往往只需要表的某几个字段
#②使用*，我们的索引可能就会失效
#③绑定问题：若多张表有相同的列名
/*
select
from
left/innner/right join
on xx=xxx
where xxx and |or .....
group by
having .....
order by ....
limit .....
*/
select * from student;
#2.查询学生表指定列 最好使用这种方式
select StudentNo,StudentName from student;
#3.给学生表 列添加别名
#使用as，as可以省略，建议写上，增加可读性
select StudentNo as "学号",StudentName '姓名' from student;
#4.查询出学号为1001的学生
select * from student where StudentNo=1001;
#5.查询出分数大于90分的信息
select * from result where StudentResult > 90;
#6.查询出分数在[80~90]分之间的信息
select * from result where StudentResult >= 80 and StudentResult <= 90;
#between 值1 and 值2 两端包含
select * from result where StudentResult between 80 and 90;
#7.查询出分数在[80~90)分之间的信息
#select * from result where StudentResult >= 80 and StudentResult < 90;
select * from result where StudentResult >= 80 && StudentResult < 90;
#8.查询出分数大于90或者小于70分的信息
#逻辑运算符：and(&&) or(||)
#select * from result where StudentResult >90 or StudentResult < 70;
select * from result where StudentResult >90 || StudentResult < 70;
#9.查询出分数不等于93分的信息
select * from result where StudentResult != 93;
select * from result where StudentResult <> 93;
#10.查询出分数不在[80~90]之家
select *
from result
where StudentResult < 80
or StudentResult > 90;
select * from result where StudentResult not between 80 and 90;
#11.查询学生姓名以李开头 like
#%匹配0个或多个
#_匹配一个字符
select * from student where StudentName like '%李%';
select * from student where StudentName like '李%';
select * from student where StudentName like '李__';
#12.给学生添加一个常数列 中国
select StudentNo,StudentName,'中国' as '国籍'
from student;
#12.为每个分数添加一列，在原有分数列的基础上+2分
#StudentResult + 2 :我们称之为表达式，类似java中的用法
select StudentNo,StudentResult as old,StudentResult + 2 as new from result;
select StudentNo,StudentResult as old,StudentResult + StudentResult as new from result;
#13.查询成绩表中所包含的课程ID
#使用distinct 可以对字段进行去重处理
select distinct r.SubjectNo from result as r;
#14.IS NULL 和 is not null
#找出出生日期为空的那个哥们
/*
NULL代表“无值”
区别于零值0和空符串“”
只能出现在定义允许为NULL的字段
须使用 IS NULL 或 IS NOT NULL 比较操作符去比较
*/
select *
from student where Email is not null;
#15.IN 查询学号为1001,1002,1003的学生信息 类似switch
#邮箱：批量删，每个邮件在数据库中有一条记录
select StudentNo,StudentName
from student where StudentNo in('1001','1002','1003');
#备注：删除数据是真的删吗？delete 、update ，不做物理删除，而是逻辑删除
#使用一个字段去标识该条记录是否被删除
#======多表关联查询=======================
#玩多表关联，一定要清楚你需要的字段在哪几张表中
#一般多表查询，我们使用表别名
/*
多表管理分为这么几种连接
1.内连接 ，又称为等值连接
2.左外连接
3.右外
结果集的处理，又分为union 和 union all
注意：
1.多表关联，避免笛卡尔积，因为你没有给我关联条件 hive禁用
2.n个表关联，至少也有n-1关联条件
*/
#1.查询出学号、姓名以及他所在的年级名称 stu/grade
select s.StudentNo,s.StudentName,g.GradeName
from student s,grade g
where s.GradeId = g.GradeID;
select s.StudentNo,s.StudentName,g.GradeName
from student s
inner join grade g on s.GradeId = g.GradeID;
#2.学号、姓名、年级名称、课程名称、考试分数
select stu.StudentNo, stu.StudentName, g.GradeName, sub.SubjectName, res.StudentResult
from student stu
inner join grade g on stu.GradeId = g.GradeID
inner join result res on stu.StudentNo = res.StudentNo
inner join subject sub on res.SubjectNo = sub.SubjectNo;
select stu.StudentNo, stu.StudentName, g.GradeName, sub.SubjectName, res.StudentResult
from student stu,grade g,result res,subject sub
where stu.GradeId = g.GradeID and stu.StudentNo = res.StudentNo
and res.SubjectNo = sub.SubjectNo;
#3.小练习
#3.1 查询出分数 > 90 且 学号<= 1010，且性别为1 的那帮哥们的如下信息：stuNo、stuName、SubjectName、StudentResult
# 30
select stu.StudentNo, stu.StudentName, g.GradeName, sub.SubjectName, res.StudentResult
from student stu
inner join grade g on stu.GradeId = g.GradeID
inner join result res on stu.StudentNo = res.StudentNo
inner join subject sub on res.SubjectNo = sub.SubjectNo
and res.StudentResult > 90 and stu.StudentNo <= 1010 and stu.Sex = 1;
select stu.StudentNo, stu.StudentName, g.GradeName, sub.SubjectName, res.StudentResult
from student stu,
grade g,
result res,
subject sub
where stu.GradeId = g.GradeID
and stu.StudentNo = res.StudentNo
and res.SubjectNo = sub.SubjectNo
and res.StudentResult > 90
and stu.StudentNo <= 1010
and stu.Sex = 1;
#3.2 查询出大一年级有哪些人,他们学了哪些课程： stuNo/stuName/gradeName/subjectName
# 50
select stu.StudentNo, stu.StudentName, g.GradeName, sub.SubjectName
from student stu,
grade g,
subject sub
where stu.GradeId = g.GradeID
and stu.GradeId = sub.GradeID
and g.GradeID = 1;
#3.3 查询出大一开设了哪些学时>=120课程，以及每个课程的学时 gradeId/gradeName/subjectName/classHour
# 1
select g.GradeID,g.GradeName,s.SubjectName,s.ClassHour
from grade g,subject s
where g.GradeID = s.GradeID
and g.GradeName = '大一' and s.ClassHour >= 120;
#4.左外连接，即使跟右表关联不上的，左边数据都会显示出来
#注意：left join 或者 left out join 等效，一般就不写
select s.StudentNo,s.StudentName,g.GradeName
from student s
left join grade g on s.GradeId = g.GradeID;
#5.右外连接: 即使跟左表关联不上的，右边数据都会显示出来
select s.StudentNo,s.StudentName,g.GradeName
from student s
right outer join grade g on s.GradeId = g.GradeID;
#6.自连接
#比如还有省市县，这种数据量不是特别大，我们没必要建多张表的情况下，
#可以在一张表中维护上下级关系
CREATE TABLE IF NOT EXISTS category(
categoryId int(10) auto_increment primary key,
categoryName varchar(32) not null ,
pid int(10)
);
#查询出数码一级分类下面有哪几个二级分类
/*
categoryId categoryName secondCategoryName
1 数码 相机
1 数码 内存条
categoryId categoryName secondCategoryName thirdCategoryName
1 数码 相机 单反
1 数码 相机 卡片
1 数码 内存条
*/
select c1.categoryId,c1.categoryName,c2.categoryName as secondCategoryName
from category c1,category c2
where c1.categoryId = c2.pid
and c1.categoryId = 1;
#关于差集的计算
#求出上述左外连接结果中，只包含没有连接上的部分
select s.StudentNo,s.StudentName,g.GradeName
from student s
left join grade g on s.GradeId = g.GradeID
where g.GradeID is null;
#求出上述右外连接结果中，只包含没有连接上的部分
select s.StudentNo,s.StudentName,g.GradeName
from student s
right outer join grade g on s.GradeId = g.GradeID
where s.GradeId is null;
#7.满外，mysql中不支持 ，可以使用union和union all，这个面试会问到，而且hive中也会用到
#求并集
#注意：在使用union和union all的时候，要保证列的个数一样！若没有对应的列，你可以使用常量值凑！！
#union具有去重功能
select s.StudentNo,s.StudentName,g.GradeName
from student s
left join grade g on s.GradeId = g.GradeID
union
select s.StudentNo,s.StudentName,g.GradeName
from student s
right outer join grade g on s.GradeId = g.GradeID;
#union all 不具有去重功能
select s.StudentNo,s.StudentName,g.GradeName
from student s
left join grade g on s.GradeId = g.GradeID
union all
select s.StudentNo,s.StudentName,g.GradeName
from student s
right outer join grade g on s.GradeId = g.GradeID;
#8.常用函数
#数学函数
#字符串函数
select CHAR_LENGTH(LoginPwd)
from student where StudentNo=1001;
#（2）CONCAT(s1,s2，...) 可变参数
select CONCAT(LoginPwd,'aaaa',LoginPwd)
from student where StudentNo=1001;
#（3）CONCAT_WS(x,s1,s2,...) 就是我们Java中String.join(....)
select CONCAT_WS('#','aaaa',LoginPwd)
from student where StudentNo=1001;
#（5）LOWER(str)和LCASE(str)、UPPER(str)和UCASE(str)
select LOWER(StudentName)
from student where StudentNo=10066;
# （6）LEFT(s,n)、RIGHT(s,n)
select left(BornDate,4)
from student where StudentNo=1001;
#（15）SUBSTRING(s,n,len)、MID(s,n,len)
#从1开始
select SUBSTRING(BornDate,1,4)
from student where StudentNo=1001;
#日期和时间函数 也很重要
#（1）CURDATE()、CURRENT_DATE()
select curdate();
select CURRENT_DATE();
#（2）CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE()
select CURRENT_TIMESTAMP();
select NOW();
# （10）YEAR(date)、QUARTER(date)、MINUTE(time)、SECOND(time)
select year(BornDate)
from student where StudentNo = 1001;
/*
条件判断函数 超超级重要
（1）IF(expr,v1,v2) ->三元
如果expr是TRUE则返回v1，否则返回v2
（2）IFNULL(v1,v2) --->map.getOrDefault
如果v1不为NULL，则返回v1，否则返回v2
（3）CASE expr WHEN v1 THEN r1 [WHEN v2 THEN v2] [ELSE rn] END
如果expr等于某个vn，则返回对应位置THEN后面的结果，如果与所有值都不想等，则返回ELSE后面的rn
*/
#需求：查询出stuNo、stuName、性别 1：男 2：女
# 1001 张三 男
select StudentNo,StudentName,IF(Sex=1,'男','女')
from student;
select StudentNo,ifnull(StudentName,'zzzzzz') 'name'
from student where StudentNo = 10089;
select StudentNo,StudentName,
case Sex
when 1 then '男'
when 2 then '女'
else 'other'
end '性别'
from student;
#SQL练习
/*
================基本单表查询==================
1.查询出姓李，且年级是二年级的学生信息
select from student where stuName like '李%' and gradeId = 2;
2.查询出成绩范围在[90,100]之间的分数情况
select * from StudentResult between 90 and 100;
3.查询出出生日期不为null的学生信息
where bornDate is not null;
================多表关联查询===================
1.查询出姓李的学生相关信息：stuNo、stuName、gradeName、subjectName
select 略
from student stu,grade g,subject sub
where stu.gradeId = g.gradeId
and sub.gradeId = g.gradeId
2.查询出大二开设了哪些课程，
以及哪些学生选了这些课程 【先拿到大二的课程编号，大一大二大三大四是否也有这些课就不管】：stuNo、stuName、gradeName、subjectName
select gradeId,subjectNo
from subject where gradeId = 2;
3.查询出姓李的学生各科成绩情况：stuNo、stuName、subjectName、studentResult
4.查询出性别为1，且成绩大于90分的学生信息：stuNo、stuName、subjectName、studentResult
5.查询出address中包含北京，且是大一的学生信息：stuNo、stuName、address、gradeName
6.查询出subjectNo=1,5,9 这三课程对应的学生信息：stuNo、stuName、gradeName、subjectName、studentResult
*/
select stu.StudentNo,SubjectName,g.GradeName,sub.SubjectName,sub.GradeID
from student stu,grade g,subject sub
where stu.GradeId = g.GradeID and g.GradeID = sub.GradeID
and sub.GradeID = 2;
select stu.StudentNo,stu.StudentName,sub.SubjectName,res.StudentResult
from student stu,subject sub,result res
where stu.StudentName like '李%' and stu.StudentNo = res.StudentNo and sub.SubjectNo = res.SubjectNo;
select stu.StudentNo,stu.StudentName,stu.Address,g.GradeName
from student stu,grade g
where stu.GradeId = g.GradeID and stu.Address like '%北京%' and stu.GradeId = 1;
select stu.StudentNo, stu.StudentName, g.GradeName, sub.SubjectName, res.StudentResult
from student stu,
grade g,
result res,
subject sub
where stu.GradeId = g.GradeID
and stu.StudentNo = res.StudentNo
and res.SubjectNo = sub.SubjectNo
and res.SubjectNo in (1,5,9);
#======================日期函数2================
/*
（1）CURDATE()、CURRENT_DATE()
将当前日期按照"YYYY-MM-DD"或者"YYYYMMDD"格式的值返回，具体格式根据函数用在字符串或是数字语境中而定
（2）CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE()
这四个函数作用相同，返回当前日期和时间值，格式为"YYYY_MM-DD HH:MM:SS"或"YYYYMMDDHHMMSS"，具体格式根据函数用在字符串或数字语境中而定
（3）UNIX_TIMESTAMP()、UNIX_TIMESTAMP(date)
前者返回一个格林尼治标准时间1970-01-01 00:00:00到现在的秒数，后者返回一个格林尼治标准时间1970-01-01 00:00:00到指定时间的秒数
（4）FROM_UNIXTIME(date)
和UNIX_TIMESTAMP互为反函数，把UNIX时间戳转换为普通格式的时间
（5）UTC_DATE()和UTC_TIME()
前者返回当前UTC（世界标准时间）日期值，其格式为"YYYY-MM-DD"或"YYYYMMDD"，后者返回当前UTC时间值，其格式为"YYYY-MM-DD"或"YYYYMMDD"。具体使用哪种取决于函数用在字符串还是数字语境中
（6）MONTH(date)和MONTHNAME(date)
前者返回指定日期中的月份，后者返回指定日期中的月份的名称
（7）DAYNAME(d)、DAYOFWEEK(d)、WEEKDAY(d)
DAYNAME(d)返回d对应的工作日的英文名称，如Sunday、Monday等；DAYOFWEEK(d)返回的对应一周中的索引，1表示周日、2表示周一；WEEKDAY(d)表示d对应的工作日索引，0表示周一，1表示周二
（8）WEEK(d)、WEEKOFYEAD(d)
前者计算日期d是一年中的第几周，后者计算某一天位于一年中的第几周
（9）DAYOFYEAR(d)、DAYOFMONTH(d)
前者返回d是一年中的第几天，后者返回d是一月中的第几天
（10）YEAR(date)、QUARTER(date)、MINUTE(time)、SECOND(time)
YEAR(date)返回指定日期对应的年份，范围是1970~2069；QUARTER(date)返回date对应一年中的季度，范围是1~4；MINUTE(time)返回time对应的分钟数，范围是0~59；SECOND(time)返回制定时间的秒值
（11）EXTRACE(type FROM date)
从日期中提取一部分，type可以是YEAR、YEAR_MONTH、DAY_HOUR、DAY_MICROSECOND、DAY_MINUTE、DAY_SECOND
（12）TIME_TO_SEC(time)
返回以转换为秒的time参数，转换公式为"3600*小时 + 60*分钟 + 秒"
（13）SEC_TO_TIME()
和TIME_TO_SEC(time)互为反函数，将秒值转换为时间格式
（14）DATE_ADD(date,INTERVAL expr type)、ADD_DATE(date,INTERVAL expr type)
返回将起始时间加上expr type之后的时间，比如DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 SECOND)表示的就是把第一个时间加1秒
（15）DATE_SUB(date,INTERVAL expr type)、SUBDATE(date,INTERVAL expr type)
返回将起始时间减去expr type之后的时间
（16）ADDTIME(date,expr)、SUBTIME(date,expr)
前者进行date的时间加操作，后者进行date的时间减操作
*/
#日期加减
select DATE_SUB(now(),INTERVAL 2 DAY);
select subdate(now(),INTERVAL 2 DAY);
select DATE_ADD(now(),INTERVAL 2 MINUTE );
#（3）UNIX_TIMESTAMP()、UNIX_TIMESTAMP(date) 秒
select UNIX_TIMESTAMP();
select UNIX_TIMESTAMP(); #不传参数，跟上面一样 秒
select UNIX_TIMESTAMP('2021-06-17'); #1970-1-1到指定日期的时间戳
select FROM_UNIXTIME(1623977987); #必须是10位时间错 ，场景：db中存储的是时间戳，etl_time 存储的就是10位时间戳
select UTC_DATE();
select UTC_TIME(); #北京时间 -8 小时
select left(BornDate,4) from student;
select SUBSTRING(BornDate,1,4) from student;
select year(BornDate) from student;
#（起始值是 1）
SELECT MID(BornDate,1,4) from student;
/*
可以实现判空以及给默认值的几种方式：
1.IF表达式 IF(表达式,值1,值2默认值) ---》 三元运算符
2.IFNULL(值1,值2-对null给默认值处理)
3.case when ... then ... else end
4.COALESCE(值1,值2-对null给默认值处理)
*/
#注意：这种内置函数要会灵活嵌套
select MONTH(ifnull(BornDate,'9999-12-12')) from student;
select MONTH(if(BornDate is not null and BornDate !=0,BornDate,'9999-10-12')) from student;
#可以实现跟IFNULL() 函数类似的交过
select COALESCE(BornDate,'9999-99-99') from student;
select MONTHNAME(curdate());
#格式化日期，也蛮重要的
select DATE_FORMAT(Now(),'%Y-%m-%d');
#======================系统信息函数：对我们用处不大==============
/*
（1）VERSION()
查看MySQL版本号
（2）CONNECTION_ID()
查看当前用户的连接数
（3）USER()、CURRENT_USER()、SYSTEM_USER()、SESSION_USER()
查看当前被MySQL服务器验证的用户名和主机的组合，一般这几个函数的返回值是相同的
（4）CHARSET(str)
查看字符串str使用的字符集
（5）COLLATION()
查看字符串排列方式
*/
select VERSION();
select CONNECTION_ID();
select USER();
#这两个在使用SqlYog创建db的时候，有选项
select CHARSET('aaa');
select COLLATION('aaaa');
#=====================加密函数=================
/*
业务系统：Java开发，一般存到业务数据库中的比如密码就是加密后的 ---》一般使用java代码的方式进行加密
（1）PASSWORD(str)
从原明文密码str计算并返回加密后的字符串密码，注意这个函数的加密是单向的（不可逆），因此不应将它应用在个人的应用程序中而应该只在MySQL服务器的鉴定系统中使用
（2）MD5(str):这个已经被山大的王教授破解了
为字符串算出一个MD5 128比特校验和，改值以32位十六进制数字的二进制字符串形式返回
（3）ENCODE(str, pswd_str)
使用pswd_str作为密码，加密str
（4）DECODE(crypt_str,pswd_str)
使用pswd_str作为密码，解密加密字符串crypt_str，crypt_str是由ENCODE函数返回的字符串
*/
#数据脱敏 --- 》 火车票 ****，快递 135****333
select password(BornDate) from student;
#知道MD5
select MD5(BornDate) from student;
select ENCODE(LoginPwd,'aaaaaa') from student;
#=======================其他函数===============
/*
（1）FORMAT(x,n) ----》很有用
将数字x格式化，并以四舍五入的方式保留小数点后n位，结果以字符串形式返回
（2）CONV(N,from_base,to_base)
不同进制数之间的转换，返回值为数值N的字符串表示，由from_base进制转换为to_base进制
（3）INET_ATON(expr)
给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数，地址可以使4或8比特
（4）INET_NTOA(expr)
给定一个数字网络地址（4或8比特），返回作为字符串的该地址的点地址表示
（5）BENCHMARK(count,expr)
重复执行count次表达式expr，它可以用于计算MySQL处理表达式的速度，结果值通常是0（0只是表示很快，并不是没有速度）。另一个作用是用它在MySQL客户端内部报告语句执行的时间
（6）CONVERT(str USING charset)
使用字符集charset表示字符串str
*/
select format(19.2227,3);
# ===========================排序、分页、统计函数、分组=====================
#1.查询《数据库结构-1》的所有考试结果，并按成绩由高到低排列
/*
1.排序使用order by，后面跟排序字段，排序字段可有多个，多个之间用,分割，默认不写跟鞋asc等效
2.降序使用desc
*/
select res.StudentNo,sub.SubjectName,res.StudentResult
from subject sub,result res
where sub.SubjectNo = res.SubjectNo
and SubjectName='数据库结构-1'
order by res.StudentResult desc ;
select res.StudentNo,sub.SubjectName,res.StudentResult
from subject sub,result res
where sub.SubjectNo = res.SubjectNo
#and SubjectName='数据库结构-1'
and sub.GradeID = 1
order by res.StudentResult desc,res.StudentNo asc ;
#2.分页：20条数据，每页6条 ---》 4页
/*
目的：
1.不需要查询所有数据，之前查询所有对数据库服务器造成比较大的压力
2.对于用户而言，类似百度查询，若没有分页，用户体验会很差！！！！
limit 0,6
参数1：(n-1)*参数2 n代表第n页，从1开始
参数2：每页的条数
*/
select * from student limit 0,6;
select * from student limit 6,6;
select * from student limit 12,6;
#3.统计函数 avg()、sum()、max()、min()、count()
select *
from result where SubjectNo = 1;
#result表中所有记录的统计情况 ---》 全局
select max(StudentResult) '最大值', min(StudentResult) as '最小值', avg(StudentResult) '均值',
sum(StudentResult) '总分',count(StudentResult) as '记录数'
from result;
#where SubjectNo = 1;
#4.group by + having + 统计函数组合
select StudentNo,max(StudentResult) '最大值', min(StudentResult) as '最小值', avg(StudentResult) '均值',
sum(StudentResult) '总分',count(StudentResult) as '记录数'
from result
group by StudentNo;
#获取总分top5
select StudentNo,max(StudentResult) '最大值', min(StudentResult) as '最小值', avg(StudentResult) '均值',
sum(StudentResult) as total,count(StudentResult) as '记录数'
from result
group by StudentNo
#order by total desc ; 注意，中文别名可能不好使
order by sum(StudentResult) desc
limit 5;
#获取平均分>=80
#在前面分组结果之上，过滤出分数>=80分以上
select StudentNo,max(StudentResult) '最大值', min(StudentResult) as '最小值', avg(StudentResult) avgScore,
sum(StudentResult) as total,count(StudentResult) as '记录数'
from result
group by StudentNo
#order by total desc ; 注意，中文别名可能不好使
having avgScore >= 80;
#获取获取平均分>=80的topN 先分组，分组之后过滤，在排序
#我们这里肯定是这种方式要优于子查询的方式
select StudentNo,max(StudentResult) '最大值', min(StudentResult) as '最小值', avg(StudentResult) avgScore,
sum(StudentResult) as total,count(StudentResult) as '记录数'
from result
group by StudentNo
#order by total desc ; 注意，中文别名可能不好使
having avgScore >= 80
order by avgScore desc
limit 4;
#5.子查询
#变换：可以将这个分组之后的结果当成一张表 --- 使用到了子查询实现上面同样的效果
#子查询的概念：在一个查询中存在多层嵌套关系 ---》实际写的可能表多的 多表查询 + 子查询
select t.*
from (
select StudentNo,
max(StudentResult) maxScore,
min(StudentResult) as minScore,
avg(StudentResult) avgScore,
sum(StudentResult) as total,
count(StudentResult) as totalCount
from result
group by StudentNo) t
where t.avgScore >= 80
order by t.avgScore desc
limit 4;
#1.t
select StudentNo,
max(StudentResult) maxScore,
min(StudentResult) as minScore,
avg(StudentResult) avgScore,
sum(StudentResult) as total,
count(StudentResult) as totalCount
from result
group by StudentNo;
#2.
/*select t.*
from t
where t.avgScore >= 80
order by t.avgScore desc
limit 4;*/
#3.组合，拼装成最后的大sql
select max(StudentResult) '最大值', min(StudentResult) as '最小值', avg(StudentResult) '均值',
sum(StudentResult) '总分',count(StudentResult) as '记录数'
from result where StudentNo = 1000;
#小练习
#1.统计出平均分大于80的学生信息：stuNo、stuName、avg_score
#1.2 1.1的结果作为一张表，跟学生表进行关联
select stu.StudentNo, stu.StudentName, t.avg avg_score
from student stu
#1.1 拿出平均分大于80的这波人
inner join (
select r.StudentNo, avg(r.StudentResult) avg
from result r
group by r.StudentNo
having avg >= 80) t
where stu.StudentNo = t.StudentNo;
#2.统计出生日期最小值对应的那个哥们，它的如下信息：stuNo、stuName、avg()、gradeName
#2.1 拿出出生日期最小的值
/*
select max(BornDate)
from student
*/
#2.2 拿出这个人的学号,姓名，GradeId
/*
select StudentNo, StudentName,GradeId
from student
where BornDate = (select max(BornDate)
from student)
*/
#2.3 关联
/*
select t.StudentNo,t.StudentName,g.GradeName
from (
select StudentNo, StudentName,GradeId
from student
where BornDate = (select max(BornDate)
from student)
)t
left join grade g on t.GradeId = g.GradeID;
*/
#2.4 将2.3的结果当成一张表，跟result表进行关联
#2.5 将2.4的结果当成一张表进行查询
select max(BornDate)
from student;
#3.统计出《数据结构-1》这门课有成绩的学生信息：stuNo、stuName、studentResult
#3.1 根据课程名称，拿到成绩编号
select SubjectNo
from subject sub where SubjectName = '数据库结构-1';
#3.2 去result表中拿出13这个课程的成绩，以及stuno
select StudentNo,StudentResult,SubjectNo
from result where SubjectNo = (select SubjectNo
from subject sub where SubjectName = '数据库结构-1') and StudentResult is not null ;
#3.3 拿着2的结果去跟我们的stu进行链接查询即可
select stu.StudentNo, StudentName, t.StudentResult
from student stu,
(select StudentNo, StudentResult, SubjectNo
from result
where SubjectNo = (select SubjectNo
from subject sub
where SubjectName = '数据库结构-1')
and StudentResult is not null) t
where stu.StudentNo = t.StudentNo;
#4.统计出 大一的 学生《高等数学-1》这门课的信息：
# gradeId，gradeName,subjectName,avg()、max()、min()、max()、count()-选这门课人数
# 并按照平均分做top3处理
#3.1 根据大一年级名称，拿到年级编号
select g.GradeID,g.GradeName
from grade g where g.GradeName = '大一';
#3.2 3.1的编号，去subject中，同时根据《高等数学-1》 拿到 课程编号
select sub.SubjectNo,sub.SubjectName,sub.GradeID
from subject sub where SubjectName = '高等数学-1' and sub.GradeID = (select g.GradeID
from grade g where g.GradeName = '大一');
# 3.3 有了编号，就可以查询成绩表
select res.SubjectNo,avg(res.StudentResult),count(res.StudentResult)
from result res where res.SubjectNo = (select sub.SubjectNo
from subject sub where SubjectName = '高等数学-1' and sub.GradeID = (select g.GradeID
from grade g where g.GradeName = '大一')) and res.StudentNo in (select student.StudentNo from student where GradeId = (select g.GradeID
from grade g where g.GradeName = '大一'))
group by res.SubjectNo;
#3.1 3.2 3.3 三者join在一起
select t1.GradeID, t1.GradeName, t2.SubjectName, t3.avg, t3.cnt
from (select g.GradeID, g.GradeName
from grade g
where g.GradeName = '大一') t1
inner join (select sub.SubjectNo, sub.SubjectName, sub.GradeID
from subject sub
where SubjectName = '高等数学-1'
and sub.GradeID = (select g.GradeID
from grade g
where g.GradeName = '大一')) t2
on t1.GradeID = t2.GradeID
inner join (select res.SubjectNo, avg(res.StudentResult) avg, count(res.StudentResult) cnt
from result res
where res.SubjectNo = (select sub.SubjectNo
from subject sub
where SubjectName = '高等数学-1'
and sub.GradeID = (select g.GradeID
from grade g
where g.GradeName = '大一'))
group by res.SubjectNo) t3
on t2.SubjectNo = t3.SubjectNo;
#5.统计出课程名称为《高等数学-2》，《高等数学-2》，《高等数学-3》
# 这三门课的学生信息：stuNo,stuName,gradeName,StudentResult
#5.1 查询出这3门口的课程编号以及年级编号
select SubjectNo,GradeID
from subject where SubjectName in('高等数学-1','高等数学-2','高等数学-3');
#5.2 根据5.1的结果 GradeID可以关联出grade 以及 stu表
#SubjectNo 可以关联出result表
select stu.StudentNo, stu.StudentName, g.GradeName, res.StudentResult
from student stu
inner join (select SubjectNo, GradeID
from subject
where SubjectName in ('高等数学-1', '高等数学-2', '高等数学-3')) t
on stu.GradeId = t.GradeID
inner join grade g on stu.GradeId = g.GradeID
inner join result res on res.SubjectNo = t.SubjectNo;
#6.统计出 “大一” 开设的课程，且这些课程在成绩表中有成绩，求出stunNo,avg(),stuName,gradeName,sex-
# 要求显示 男 女 case when then end
#6.1 根据大一这个信息，查出年级编号
select GradeID
from grade where GradeName = '大一';
/*select distinct StudentNo
from result where SubjectNo in(1,5,9,13);*/
#6.2 有了6.1的编号之后，可以去subject表中查询课程 编号列表
select SubjectNo
from subject where GradeID = (select GradeID
from grade where GradeName = '大一');
#6.3 去result表中查询这些课程，而且成绩栏有值，这步就能算出avg() 、 stunNo
select res.StudentNo,avg(res.StudentResult) avg
from result res where SubjectNo in(select SubjectNo
from subject where GradeID = (select GradeID
from grade where GradeName = '大一')) group by res.StudentNo;
# 6.4 有了6.3的stuNo stuName sex 以及 gradeId
select stu.StudentNo,
stu.StudentName,
t.avg,
g.GradeName,
case stu.Sex
when 1 then '男'
when 2 then '女'
else 'other'
end
from student stu
inner join (select res.StudentNo, avg(res.StudentResult) avg
from result res
where SubjectNo in (select SubjectNo
from subject
where GradeID = (select GradeID
from grade
where GradeName = '大一'))
group by res.StudentNo) t
on stu.StudentNo = t.StudentNo
inner join grade g on stu.GradeId = g.GradeID;
#7.统计出《高等数学-1》这门课的且是大一学生情况，根据分数为学生划分等级，[90,100]优秀，依次类推
#需要显示的格式：stuName、stuName、gradeName、studentResult、level
#8.统计出每个年级所有课程的情况：gradeId、gradeName、avg()、sum()、max()、min()、count() --每个年级成绩记录数
/*
1 大一 10 20 50
注意：子查询很灵活，可以在很多地方
*/
#
select g.GradeID,
g.GradeName,
(select avg(t.StudentResult)
from (select s.GradeID, result.StudentResult
from result
left join subject s on result.SubjectNo = s.SubjectNo) t
where t.GradeId =g.GradeID
group by t.GradeID) avg
from grade g;
#作业
/*
1.按照年份，统计每个年份出生的那些人的平均分、最高分、最低分、人数、总分并按照人数获取top5，
同时根据总分划分智商等级，比如[1000,2000] 高智商、范围自定义 ，最后显示
year max min avg count eqLevel
2.PDF的两个大题
3.事务、理论知识（索引、表的设计-范式、ER图）
*/
本文详细介绍了SQL的基本查询语法，包括单表查询中的选择指定列、使用别名、条件过滤，以及多表关联查询中的内连接、外连接、自连接等操作。此外，还讲解了日期函数、条件判断函数以及分页、统计函数的使用，帮助理解SQL在数据处理中的核心功能。
扫一扫
312
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
