SQL高级查询

 1.统计查询
         聚合函数： max(),min(),sum(),avg(),count()
         分组：group by
        注意事项：当分组的时候，前面查询的列不能比分组的
                 列多，除聚合函数以外
                 先where在group by 在having(过滤) 
      2.连表查询：数据会变多
        内连接(重点)三种写法：inner join
             两个表都有的数据
         
        左连接/右连接：left/right join
             左/右表有的数据，其他表没有时用null代表数据
  
        全连接：full join  
             全部数据，那个表没有时，用null代表数据

create table tb_stu
(
    SCode int not null primary key,
    SName varchar(10) not null,
    SAddress varchar(50),
    SBirth datetime,
    SGrade varchar(2) default('S1'),
    SEmail varchar(50) check(SEmail like '%@%'),
    ssex varchar(10),
    sage int
)

go

insert into tb_stu(SCode, SName, SAddress, SBirth, SSex,sage)
select 1, '大娃', '株洲', '1990-01-01', '女',22 union
select 2, '二娃', '株洲', '1990-01-01', '男',21 union
select 3, '三娃', '长沙', '1990-01-01', '男',21 union
select 4, '四娃', '长沙', '1990-01-01', '男',20 union
select 5, '五娃', '长沙', '1990-01-01', '男',19 union
select 6, '六娃', '长沙', '1990-01-01', '男' ,18 union
select 7, '七娃', '湘潭', '1990-01-01', '男' ,17 union
select 8, '娃爷爷', '湘潭', '1990-01-01', '男',33 union
select 9, '金刚葫芦娃', '湘潭', '1990-01-01', '男',25 union
select 10, '蛇精', '衡阳', '1990-01-01', '女' ,55 union
select 11, '穿山甲', '衡阳', '1990-01-01', '男',88 union
select 12, '王八', '衡阳', '1990-01-01', '女',null

go

create table tb_score
(
    ScoreID int not null primary key identity(1, 1),
    StudentID int not null foreign key references tb_stu(SCode),
    Course varchar(10) not null,
    Score float
)

go

insert into tb_score
select 1,'JAVA',89 union
select 1,'HTML',80 union
select 2,'JAVA',92 union
select 2,'HTML',74 union
select 3,'JAVA',76 union
select 3,'HTML',95 union
select 4,'JAVA',NULL union
select 4,'HTML',NULL union
select 5,'JAVA',48 union
select 5,'HTML',79 union
select 6,'JAVA',NULL union
select 6,'HTML',NULL union
select 7,'JAVA',66 union
select 7,'HTML',88

select * from tb_stu
select * from tb_score

--去重复distinct
--求班级有哪些性别
select distinct ssex from tb_stu

--函数列没有名字
--1.求班级总人数
select  count(SCode) 总人数 from tb_stu;
--2.求班级年龄总和
select sum(sage) from tb_stu;
--3.求班级年龄平均值 
select avg(sage) from tb_stu;
--4.求班级最大年龄
select max(sage) from tb_stu;
--5.求班级最小年龄
select min(sage) from tb_stu;

--6.求班级男女生各多少人
--当分组的时候，前面查询的列不能比分组的
--列多，除聚合函数以外
select ssex,count(ssex) from tb_stu group by ssex
--7.求各个地方人数分别为多少人
select SAddress,count(SAddress) from tb_stu group by SAddress
--8.求各个姓氏各多少人
--求姓氏  两种方式
select left(SName,1) from  tb_stu;
select left(SName,1),count(left(SName,1)) from tb_stu group by left(SName,1)
--9.求住在衡阳的男女生各多少人
select SAddress,ssex,count(ssex) from tb_stu where SAddress='衡阳' group by ssex,SAddress
--10.求各个地方男女生各多少人
select SAddress,ssex,count(ssex) from tb_stu group by SAddress,ssex

--11.求人数在2人以上的地方
select SAddress,count(SAddress) from tb_stu   group by SAddress having count(SAddress)>2;

--12.求男生人数在1人以上的地方
   select saddress,count(saddress) from tb_stu where ssex='男' group by saddress
    having count(saddress)>1

--13.查出所有考试学生的学号，姓名，课程，分数
--内连接
--1
select SCode,SName,Course,score
from tb_stu a inner join 
tb_score b on a.SCode=b.StudentID
--2   inner省略不写默认内连接
select SCode,SName,Course,score
from tb_stu a  join 
tb_score b on a.SCode=b.StudentID
--3   where 
select SCode,SName,Course,score
from tb_stu a ,tb_score b
where a.SCode=b.StudentID
--14.查出所有学生的学号，姓名，课程，分数,不管有没有参考
--全连接
select SCode,SName,Course,score
from tb_stu a full join 
tb_score b on a.SCode=b.StudentID

--测试左右连接

select SCode,SName,Course,score
from tb_stu a  left join 
tb_score b on a.SCode=b.StudentID

select SCode,SName,Course,score
from tb_stu a  right join 
tb_score b on a.SCode=b.StudentID