数据库sql server增删改查（二）分页查询和多表联查

-------------------------查询(查)-----------------------------------

--14、查询的语法：
--select 列名1, 列名2....------》查询所有字段用“*”代替
--from 表名
--[where 条件]
--[order by 排序字段 asc 或 desc，默认是升序]

--基本格式一：省略where和order by的情况
--例：查询学生表中的所有信息
select * from stuinfo

--例：查询学生表中的学生学号和姓名
select stuno,stuname from stuinfo

--基本格式二：带where条件
--例：查询学生表中所有上海的男生
select * from stuinfo where addr = '上海' and sex = '男'

--基本格式三：带where条件和order by排序
--例：查询学生表中有地址的学生，并按照年龄从大到小排序
select * from stuinfo
where addr is not null and addr <> ''
order by age desc

--组合排序
--例：查询学生表中有地址的学生，并先按照年龄从大到小排序，如果年龄一样的再按照学号由大到小排序
select * from stuinfo
where addr is not null and addr <> ''
order by age asc,stuno desc

--联系：查询学生表中地址不在北京和深圳的学生，并先按照年龄小到大排序
select * from stuinfo
where addr is null or addr not in('北京','深圳')
oder by age asc

--15、列名和表名去别名,注意：别名不影响表真正的名字
--格式一：列名 as 别名
--例：查询学生表中的学生学号和姓名
select stuno as '学号',stuname as '姓名' from stuinfo

--格式二:省略as，列名 别名
--例：查询学生表中的学生学号和姓名
select stuno '学号',stuname '姓名' from stuinfo

--格式三：别名=列名
--例：查询学生表中的学生学号和姓名
select '学号'=stuno ,'姓名' = stuname  from stuinfo

--格式四：列名取别名是合并多个列，(列名 + 列名) 别名，显示是可以增加其他字符
select stuno '学号',stuname '姓名' , sex +'|| '+ email '其他信息'
from stuinfo

--表名也可以取别名
select A.stuno '学号',A.stuname '姓名'
from stuinfo A

--常量列:增加某个列取固定的数据，仅用于显示
select stuno '学号',stuname '姓名', '软件测试' as '课程'
from stuinfo

--16、空值的查询:列名 is (not) null
--例：查询没有地址的学生信息
select * from stuinfo
where addr is null or addr = ''

--例：查询地址存在的学生信息
select * from stuinfo
where addr is not null and addr <> ''

--错误情况：
select * from stuinfo where addr = null------>错误的
select * from stuinfo where addr = 'null' --->错误的
select * from stuinfo where addr = ''-------->查询出地址是空白的数据，不是null

--17、查询限制显示的行数
--格式一：用top行数
--例：查询年龄排在前三位的学生信息
select top 3 * from stuinfo
order by age desc

--格式二：用百分比的方式显示数据，向上取整(如得到3.1~3.9，取4)
--例：查询年龄排在前30%的学生信息
select top 30 percent * from stuinfo
order by age desc

--练习：查询综合年龄（age*0.8）大于20岁，且年龄排在最前面的20%的学生信息
select top 20 percent * from stuinfo
where age * 0.8 > 20
order by age desc

--18、聚合函数:MAX(),MIN(),SUM(),AVG(),COUNT()
--注意：null不参与聚合运算
create table stuscore(
id int primary key,
stuid int not null,
stuname varchar(30) not null,
couname varchar(20) not null,
score int
)

insert into stuscore
select 1,1001,'小明','语文',90 union
select 2,1001,'小明','数学',99 union
select 3,1001,'小明','英语',100 union
select 4,1002,'小红','语文',78 union
select 5,1002,'小红','数学',56 union
select 6,1002,'小红','英语',90 union
select 7,1003,'小黑','语文',99 union
select 8,1003,'小黑','数学',null union
select 9,1004,'小丁','语文',70 union
select 10,1004,'小丁','数学',null union
select 11,1004,'小丁','英语',34

--18.1 求和SUM()函数
--例:求所有人的所有科目的总分
select SUM(score) '总分'  from stuscore

--例:求小黑的所有科目的总分
select SUM(score) '总分'  from stuscore where stuname ='小黑'

--18.2 求AVG()平均值
--例:求全班语文的平均分
select AVG(score) '平均分'  from stuscore where couname='语文'

--例:求小黑的所有科目的平均
select AVG(score) '平均分'  from stuscore where stuname ='小黑'

--18.3 求MAX()最大值
--例：求小明的三门课的最高分
select MAX(score) '最高分' from stuscore where stuname = '小明'

--18.4 求MIN()最大值
--例：求小黑的三门课的最高分
select Min(score) '最低分' from stuscore where stuname = '小黑'

--18.5 计数COUNT()
--例：计算小黑参加了几门课的考试
select count(*) '科目数' from stuscore where stuname = '小黑' ---显示2门课
select count(score) '科目数' from stuscore where stuname = '小黑' ---显示1门课

--19、去重复distinct()
--例：求全班有多少人参加了考试
select count(distinct(stuid)) '人数' from stuscore

--20、查询中出现了聚合函数,查询前后都不允许出现其他字段
--除非（1）另一个字段也是聚合函数 （2）对该字段进行分组

--第一种情况：
--例：求小丁的总分，要求显示姓名和总分
select sum(score) '总分', avg(score) '平均分' from stuscore
where stuname = '小丁'---报错,

--第二种情况：
select stuname '姓名',sum(score) '总分', avg(score) '平均分' from stuscore
where stuname = '小丁'---报错,
group by stuname

--分组
--1.分组可以同时按多个条件分组
--2.having 只能和group by 一起使用

--多表联查

select * from stuscore

学生信息表   stuinfo
成绩表       score
课程表        course
学生成绩表   stuscore

group by
select * from stuinfo
select count(*),sum(sex) from stuinfo group by sex  --------不对  
select count(*),sex from stuinfo group by sex

select count(*),sex from stuinfo where age>=20 and age<=30 group by sex

select count(*),age from stuinfo  having age between 20 and 30

--知道小明学生的姓名，地址，邮箱，还想知道他学习的科目的名字和分数
select stuname,addr,email,courname,score from stuinfo,score,course
--上面的写法，只是单纯的查出 笛卡尔积

insert into course (id,courName)
select 1,1 union
select 2,2 union
select 3,3 union
select 4,1

insert into stuscore
select 1,1001,'小明',1,90 union
select 2,1001,'小明',2,99 union
select 3,1001,'小明',3,100 union
select 4,1002,'小红',1,78 union
select 5,1002,'小红',2,56 union
select 6,1002,'小红',3,90 union
select 7,1003,'小黑',1,99 union
select 8,1003,'小黑',2,null union
select 9,1004,'小丁',1,70 union
select 10,1004,'小丁',2,null union
select 11,1004,'小丁',3,34

insert into stuinfo(stuNo,stuName,sex,age,email,addr)
select 1020,'大明','男',21,'qw@qq.com','深圳' union
select 1021,'大红','女',24,'12e3@qq.com','上海' union
select 1022,'大黑','男',31,'11qwe1@qq.com','北京' union
select 1023,'中明','男',22,'qw@qq.com','深圳' union
select 1024,'中红','女',15,'12e3@qq.com','上海' union
select 1025,'中黑','男',23,'11qwe1@qq.com','北京' union
select 1026,'老明','男',23,'qw@qq.com','深圳' union
select 1027,'老红','女',24,'12e3@qq.com','上海' union
select 1028,'老黑','男',33,'11qwe1@qq.com','北京'

-------分页查询-------

三种常见数据库的加行号查询

----sqlserver
select row_number() over(order by stuno) as rownumber,* from stuinfo  --加行号
--分页语句如下：
select * from (select row_number() over(order by stuno) as r,* from stuinfo) as ss where ss.r>3 and ss.r<7

-----oracle
select rownumber,* from stuinfo   --加行号
---分页语句如下：
select * from（select rownumber,* from stuinfo）as ss where ss.rownumber between 4 and 6

-----mysql
-----分页语句如下：
select * from stuinfo limit 3,6    ---取起始位置的下一行，到结尾位置  4-6
select * from stuinfo limit 3

-------多表联查--------
--笛卡尔积=表1的数据数目X表2的数据数目X.....
--select courname,score from course c,score s where c.id=s.couid
select courname,score
from score s
join course c
on s.couid=c.id

select stuname,courname,score
from score s
join course c
on s.couid=c.id
join stuinfo ss
on ss.stuno=s.stuid

select stuname,courname,score
from score s
inner join course c
on s.couid=c.id
join stuinfo ss
on ss.stuno=s.stuid

---左外连接  表A left join 表B on...     A有的B没有的展示出来
---右外连接    表A right join 表B on ...   A没有的B有的展示出来
---全外连接  表A full join 表B on ...    A和B中对方没有的都展示出来

insert into stuinfo(stuNo,stuName,sex,age,email,addr)
select 1020,'大明','男',21,'qw@qq.com','深圳' union
select 1021,'大红','女',24,'12e3@qq.com','上海' union
select 1022,'大黑','男',31,'11qwe1@qq.com','北京' union
select 1023,'中明','男',22,'qw@qq.com','深圳' union
select 1024,'中红','女',20,'12e3@qq.com','上海' union
select 1025,'中黑','男',23,'11qwe1@qq.com','北京' union
select 1026,'老明','男',23,'qw@qq.com','深圳' union
select 1027,'老红','女',24,'12e3@qq.com','上海' union
select 1028,'老黑','男',33,'11qwe1@qq.com','北京'
insert into stuinfo(stuNo,stuName,sex,age,email,addr)
select 1029,'张三','男',25,'qw@qq.com','上海'

insert into course
select 4,'物理'

insert into course
select 1,'语文' union
select 2,'数学' union
select 3,'英语'

insert into score
select 1,1020,1,90 union
select 2,1020,2,99 union
select 3,1020,3,100 union
select 4,1021,1,78 union
select 5,1021,2,56 union
select 6,1021,3,90 union
select 7,1022,1,99 union
select 8,1022,2,null union
select 9,1023,1,70 union
select 10,1023,2,0 union
select 11,1023,3,34 union
select 12,1024,1,80 union
select 13,1024,2,88 union
select 14,1024,3,81 union
select 15,1025,1,59 union
select 16,1025,2,100 union
select 17,1025,3,30  union
select 18,1026,1,100 union
select 19,1026,2,null union
select 20,1026,3,null union
select 21,1027,1,99 union
select 22,1027,2,100 union
select 23,1027,3,98 union
select 24,1028,1,75 union
select 25,1028,2,71 union
select 26,1028,3,null