-- 一:常用聚合函数
--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)