select *from score
select *from Student
--统计函数：
--count总数、max最大、min最小、avg平均、sum求和
-- count
select count(*)from Student
-- max
select max(sage)from Student
-- min
select min (sage)from Student
--avg
select avg(sage)from Student
-- sum
select sum(sage)from Student
-- 子查询
select sage from Student where sage>(
select avg(sage)from Student
)
-- 分组查询 group by
-- 按照性别查询
select sssex from Student group by sssex
select sage from Student group by sage
-- 查询每个地址的平均年龄
select saddress,AVG(sage) from Student group by saddress
-- 平均男女
select sssex,AVG (sage) from Student group by sssex
--having:分组后的 过滤查询
--where:分组前的 条件查询
-- 性别查询大于18
select sssex,AVG(sage)from Student group by sssex
having avg(sage)>18
-- 查询男
select *from Student where sssex='男';
-- 查询女
select *from Student where sssex='女';
-- 联表查询
/**
格式
select *from 表一 别名
inner join 表二 别名
on 联表条件
*/
select a.sid id,sname 名字,sssex 性别,sage 年龄,saddress 地址,js 机考,bs 笔考 from Student a
join score b
on a.sid=b.sid
-- 全联
select a.sid id,sname 名字,sssex 性别,sage 年龄,saddress 地址,js 机考,bs 笔考 from Student a
full join score b
on a.sid=b.cid
-- 右联
select *from Student a
left join score b
on a.sid =b.sid;
-- 左联
select *from Student a
right join score b
on a.sid =b.sid
-- 所有的别名
select 学号=a.sid ,sname as 姓名,js 机试,bs as '笔试' from Student a
right join Score b
on a.sid=b.sid
