Database_learning_notes
集合函数
group by 语句
group by 的字面意义就是“根据一定的规则进行分组”
它的作用是通过一定的规则将一个数据集划分成若干个小的区域,然后针对若干个小区域进行数据处理。
如果要用到group by 一般用到的就是“每”这个字 例如说明现在有张部门分布表,求每个部门有多少人 就要用到分组的技术
在select语句可以加集合函数(如count(),sum()等)
比如: select customerstype,COUNT(*) from customers group by customerstype
如果不用count(*) 而用类似下面的语法 select DepartmentID,DepartmentName from BasicDepartment group by DepartmentID
将会出现错误 选择列表中的列 ‘BasicDepartment.DepartmentName’ 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。 这就是我们需要注意的一点,如果要在select语句中的字段,这些字段要么就要包含在Group By语句的后面,作为分组的依据;要么就要被包含在聚合函数中。
出现的错误详解: 咱们看看group by 的执行的过程,先执行select 的操作返回一个程序集, 然后去执行分组的操作,这时候他将根据group by 后面的字段进行分组,并且将相同的字段并称一列数据,如果group by 后面没有这个字段的话就要分成好多的数据。但是分组就只能将相同的数据分成两列数据,而一列中又只能放入一个字段,所以那些没有进行分组的 数据系统不知道将数据放入哪里,所以就出现此错误 目前一种分组情况只有一条记录,一个数据格是无法放入多个数值的, 所以这里就需要通过一定的处理将这些多值的列转化成单值,然后将其放在对应的 数据格中,那么完成这个步骤的就是聚合函数。这就是为什么这些函数叫聚合函数(aggregate functions)了
group by 只能在where语句后面 select语句后面的属性一定要先出现在group by语句后面
group by 对多个字段进行分组: 有时候我们需要同时分多个组,比如“查询每个学期每个学生的选课情况”这种多个“每”字的查询,就要用到多个字段
group by 字段1,字段2 即为对字段1进行分组,再在分组的结果中每个组对字段2再分组 详见 https://segmentfault.com/a/1190000006821331
having 语句
由于SQL的执行顺序为:where->group by->having,如果要对分组数据进行筛选,就要使用having语句(where字句不能使用集合函数)
系统会根据group by 的分组产生虚拟表,然后用having语句对虚拟表进行筛选
having语句后面可以使用集合函数,可以是其他地方都没有出现过的集合函数,或者是判断的语句 注意:这里的字段要求跟select语句一样,必须是group by语句后面的或者是一个集合函数
只有在出现了group by语句才能使用
where的嵌套查询
在子查询中使用order by没有意义,order by只能出现在最外层查询的最后,作用于最终的输出结果
in 和 not in语句
判断一个属性是否在子集合中 (找出既在2009年秋开课又在2009年春开课的课程)
select distinct course_id from section where semester =‘Fall’ and year = 2009 and course_id in (select course_id from section where semester='spring’and year=2009);
相当于子查询得到一个集合,然后外层查询判断某一个属性是否在这个集合中。 适用于“既……又……”的结构 注意:
- 外层查询的(course_id)部分可以是多个属性,但是要跟内层查询的select 部分完全一样
some any all 语句
some相当于存在量词,all相当于全称量词 (找出比生物系的某些老师的工资高的老师)
select name from instuctor where salary > some (select salary from instructor where dept_name=‘Binary’);
some: 跟子集合的某一些比较,满足条件的元组存在就可以,通常跟比较符号一起用: >some =some <=some <>some
>some 存在一些元组比子集合中的某些元组大 <=some 存在一些元组小于等于子集合中的某些元组 适用于 “找出一个/些,使他比……中的某些大,使他大于至少一个/些” ###all:跟子集合的所有元组比较,>all =all <=all <>all
注意:
- where 后的属性可以是一个,也可以是多个,但是一定要跟子查询里面的select后的属性完全相同
- =some 等价于 in,<>some并不等价于 not in
- <>all 等价于not in,=all并不等价于in
exist语句
判断外层查询的结果集合在不在内层查询的结果集合中,用于检查集合包含关系 (找出既在2009年秋开课又在2010年春开课的同一个课程)
select course_id from section as S where semester =‘Fall’ and year=2009 and exist (select * from section as T where semester ='Spring’and year=2010 and S.course_id=T.course_id);
注意:
子查询中用到了外层查询中的换名的表,称为correlated subquery 内外层查询没有先后之分,同时进行
unique 语句
几乎没有DBMS支持unique语句
from的嵌套查询
注意:外层的查询的来源from 一定要包含在内层查询的select结果中的
select dept_name,avg_salary from(select dept_name,avg(salary)as avg_salary from instructor group by dept_name) where avg_salary > 4300; from嵌套的select可以选择出一个子集合,要在外层的where语句使用这个子集合,则要换名,换为 _表名 (_元组1,_元组2,……),如:找出总薪水最高的部门 select max(tot_salary) from (select dept_name,sum(salary) from instructor group by dept_name)as dept_total(dept_name,tot_salary);
lateral 语句
with语句
with相当于建立一个临时的表,供接下来的查询,这个查询结束之后with生成的表就不存在了。 如寻找总薪水比所有部门平均总薪水高的部门
with dept_total(dept_name,value)as (select dept_name,sum(salary) from instructor group by dept_name), dept_total_avg(value)as (select avg(value) from dept_total) select dept_name from dept_total,dept_total_avg where dept_total.value>=dept_total_avg.value;
语法格式:with 临时表名1(属性1,属性2,……)as (select语句) select …… from…… where……;
表的联接查询
- 使用natural join 或者其他形式的join来联接表,然后查询(不过联接之后不可以对表做很多操作,不是很方便)
- 在where语句中使主码等于外码将两表联接。然后查询
except语句
相当于集合运算中的‘-’号 用法:
except
注意 except 前后的查询语句可以用括号括起来,也可以不括
排序
语法: SELECT <列名>
FROM <表名>
[WHERE <查询条件表达式>]
[ORDER BY <排序的列名>[ASC或DESC]]
例如: SELECT SCode,SName,SAddress
FROM Students
WHERE SSEX = 0
ORDER BY SCode