Sql：聚合函数、排序与分组、联合查询、外键约束、子查询

-- 一：常用聚合函数
--1：sum	对与null默认为0
select sum(age) from mytesttable002

--2：max和min
select max(age) from mytesttable002
select min(age) from mytesttable002

--3：avg	不统计null，比如a,b,c三个人，c的成绩为null，则只统计a,b的sum/2
select avg(age) from mytesttable002--int,不会四舍五入，是去尾法
select avg(age*1.0) from mytesttable002

--4：count
select count(*) from mytesttable002--查询表中总共有多少条记录

--聚合函数一般和group by一起使用，如果没有手动group by，则默认将整个表作为一组进行统计

-- 二：group by
--1：统计每个部门的的人数(按照部门分组)
select 部门=department,部门人数=count(*) from mytesttable002 group by department
	--效果同上
select department as 部门,部门人数=count(*) from mytesttable002 group by department
--select department,count(*) from mytesttable002 group by department

-- 三：order by
select * from mytesttable002 order by age asc--按照年龄升序排序
select * from mytesttable002 order by age desc--按照年龄降序排序
select * from mytesttable002 order by age desc,id asc--多项排序
select * from mytesttable002 order by id asc--按照id升序排序

-- 四：联合查询
--1：内连接(inner join/join)------交集
	--两表之间的查询
select mytesttable001.id,mytesttable001.name,
	   mytesttable002.id,mytesttable002.name
from mytesttable001 join mytesttable002 on (mytesttable001.id=mytesttable002.id)

--三表之间查询
select mytesttable001.id,mytesttable001.name,
	   mytesttable002.id,mytesttable002.name,
	   mytesttable003.id,mytesttable003.books
from mytesttable001 
     join mytesttable002 on(mytesttable001.id=mytesttable002.id)
	 join mytesttable003 on(mytesttable001.id=mytesttable003.id)

--2：外连接（左外连接，右外连接，全外连接，可以省略outer，因为内连接只有交集这一种，加上left等
			--就自动是外连接了，加不加outer都一样）
	--两表之间左外连接（显示左表所有部分，并且显示右表部分字段）
select mytesttable001.id,mytesttable001.name,
	   mytesttable002.id,mytesttable002.name
from   mytesttable001 left join mytesttable002 on mytesttable001.id=mytesttable002.id

	--两表之间右外连接（显示右表所有部分，并且显示左表部分字段）
select mytesttable001.id,mytesttable001.name,
	   mytesttable002.id,mytesttable002.name
from   mytesttable001 right outer join mytesttable002 on mytesttable001.id=mytesttable002.id
--这个就没必要用图表示了

--外连接改进（显示左表中去除与右表共有的部分）
select mytesttable001.id,mytesttable001.name,
	   mytesttable002.id,mytesttable002.name
from mytesttable001 left join mytesttable002 on mytesttable001.id=mytesttable002.id
	 where mytesttable002.id is null

	--3：全连接
select mytesttable001.id,mytesttable001.name,
	   mytesttable002.id,mytesttable002.name
from mytesttable001 full outer join mytesttable002 on mytesttable001.id=mytesttable002.id

--五：外键约束(类型必须相同)
	--这里mytesttable001的books必须存在于mytesttable003的books里，因此需要添加外键约束
	--表003的books列为主键，表001主键为id，其列books必须约束在表003的books里，因此需要向表001的books列
	--添加约束

--1：创建好了表后修改表，向其添加约束
--alter table 需要建立外键的表 add constraint 外键名字 foreign key（外键字段）
--references 外键表(被别的表引为外键的字段)
alter table mytesttable001 add constraint fk_books 
foreign key (books)
references mytesttable003(books)

执行后刷新会看到表001里已经有了刚才添加的命名约束fk_books

现在想表001里添加数据时，如果添加的books不在003的books里，就会报错

可以看到添加外键约束成功了，其他方式通过Sql Server软件点击窗口可视化添加，Sql语句在表创建时添加这些就不一一细讲了

--六：子查询
--选择表001中books价格大于42的
select mytesttable001.*
from mytesttable001
where mytesttable001.books in
(select mytesttable003.books from mytesttable003 where mytesttable003.price>42)