java：Oracle（聚合函数，多表查询，表之间的关系）

1.聚合函数

　　--max，min，sum，avg，count，nvl(,)

　　-- max:最大值
　　-- max既能取数字的最大值，也可以取字符串的最大值(英文字母排列顺序)，根据场景实际意义来看，最好一条sql中只出现一次
　　select max(age) ,max(name) from teacher;
　　

　　--min:最小值
　　select min(age) from teacher;
　　select min(name) from teacher;
　　

　　--sum:求和:在求和函数中，只能计算数字类型， 并不能计算字符串类型
　　select sum(age) from teacher;
　　select sum(name) from teacher;

　　

　　--avg:平均值:也只能用于计算数字类型
　　select avg(age) from teacher;
　　select avg(name) from teacher;

　　

　　--count(*),count(任意数字):获取某张表的数据总条数

　　-- 在实际开发岗位中，一定要使用 count(1)不要去使用count(*)
　　select count(*) counts from teacher;
　　-- count(*)和select *执行效率很像：首先把所有的数据全部查出来(调用一次select * from xxx)，然后进行统计总条数
　　select count(1) counts from teacher;
　　-- count(1):首先把表中的所有数据全部换成1，然后在进行统计总条数
　

　　-- nvl:如果查询出的数据有null值，就给一个默认值
　　select name, age, nvl(description,'我是默认值') from teacher;

 

2.多表查询
　　-- 首先把数据多的表全部查询出来---->开始对应数据量少的表,然后把数据量少的表进行分组
　　select * from classes;
　　select * from teacher;
　　select * from teacher, classes;
　　-- 以classes为基准，查询遍数:classes有多少条数据，就会查询多少遍
　　-- 在实际开发中，from xxx,xxx,xx这种多表查询决绝使用，因为效率非常低
　　select c.*, t.* from classes c, teacher t;
　　select t.id teahcer_id, t.description teacher_description, t.age teacher_age, t.name teahcer_name, t.hire_date teacher_hire_date, c.id 　　classes_id, c.name classes_name, c.description classes_description from classes c, teacher t;
　　-- 多表查询中，一旦给表起了一个别名，必须要使用别名来进行点列名(只有两张表同时存在相同字段的时候要使用别名点列名)
　　-- 字典表中 是不存在主键的
　　select c.*, t.* from classes c, teacher t where age = 33;
　　-- 查询出两张表id相同的数据内容
　　select c.* , t.* from classes c, teacher t where c.id = t.id;
　　-- 冒泡排序：把小的冒上来，大的压下去
　　-- oracle也有自己的排序规则，类似于冒泡排序
　　select c.* , t.* from classes c, teacher t where c.id > t.id;
　　select c.name, t.name from classes c, teacher t group by c.name, t.name;
　　select * from teacher;
　　select * from classes;

 

 

3.表和表之间的关系

　　-- 三种关系：一对一，一对多，多对多
　　-- 这两张表或多张表就必然存在一个联系

　　--外键：FK_当前表名_外键表名_外键主键

　　-- 一对一：一对一关系的两张表，外键是可以随意建的，换句话说，外键建立在哪一张表都没有关系

　　  
　　--第一步：建立两张表
　　--第二步：找这两张表的关系(一对一，一对多，或者多对多)
　　-- 发现籍贯和人民存在一对一的关系
　　--第三步：建立外键(就是和另一张进行关联的列)，必须要指向另一张关联表的主键，并且外键的列的类型一定要和指向主键的那一列的         类型保持一致
　　-- person表，id主键，native_place_id外键(外键的列类型一定要和native_place表中的主键id类型(长度)相同)
　　-- native_place表，id主键
　　select p.name, np.area from person p, native_place np where p.native_place_id = np.id;

 

　　-- 一对多： 在一对多的情况下，外键要建立在多的一方
　　-- 第一步：建两张表
　　-- employee/staff 员工表
　　-- department 部门表
　　-- 第二步：寻找这两张表的关系(一对多，部门是一的一方，员工就是多的一方)
　　-- 第三步：建立外键
　　-- 外键需要建立在一的一方：以部门和员工表为例：会出现一个bug：一个部门只能拥有一个员工，虽不违反语法，但是不符合逻辑思维
　　-- 如果我需要查询出所有的员工所属于哪个部门
　　-- 有部门的员工：
　　-- select * from department d, staff s where d.staff_id = s.id;
　　-- 查询所有员工:
　　select * from department d, staff s where d.id = s.department_id;
　　-- 查询出各部门的员工人数
　　-- 第一步：查询出部门
　　-- 第二步：查询出该部门的关联表
　　-- 第三步：需要把部门进行分组
　　select d.name, count(1) from department d, staff s where d.id = s.department_id group by d.name;

 

　　-- 多对多：建立外键：重新定义一张表来管理里面的外键关系
　　-- 学生和课程
　　-- 首先建立两张表
　　-- 分析这两张表之间的关系(多对多)
　　-- 建立外键:外键建立在哪一张表都不合适
　　-- 控制课程选择的表就在第三张外键表
　　-- 第一步：把所有的学生存入学生表(并不知道学生会选择什么课程)
　　-- 第二步：把所有的课程存入课程表(并不知道这些课程会被哪些学生选择)
　　-- 第三步：多对多的关系表(确定选择课程的结果):确定哪些学生选择了什么课程，这些课程被哪些学生选择了
　　-- 多对多关系表(insure):里面只会放两个字段，并且没有主键，这两个字段分别代表两张表的主键(student_id--->stu_students表的                   id，class_id----->stu_class表的主键id)
　　select * from stu_students sst, stu_class sc, insure i where i.student_id = sst.id and i.class_id = sc.id;

 　　

 

 

　　在一对多的情况下，外键必须要建立在多的一方

　　一对多为例：(表中插入数据时，要先插入一的一方，再插入多的一方)
　　user和order(外键user_id--->user表中的id主键)

　　一对一为例：(表中插入数据，就一定首先插入没有外键的那张表的数据)
　　在一对一中，外键可以随意建立:外键建立在哪一张表都可以
　　people
　　idcard_id
　　idcard

　　一对多和一对一：
　　如果想实现级联删除的效果：
　　一定要删除没有外键的一方

 

　　无论是一对多，还是一对一：
　　插入数据的顺序：一定要先插入没有外键的那张表

　　多对多：
　　首先，多对多并不存在主外键的关系
　　第二，如果要实现多对多的关系，就要建立一张关系表
　　也就是多对多关系会有三张表存在，只是第三张表比较特殊：没有主键，里面只有两列(分别是两张表的主键id)

　　order和goods,order_goods(关系表)
　　如果要删除数据，只需要删除关系表的数据
　　在添加数据的时候：
　　我先添加order中的数据和先goods中的数据都无所谓
　　只要添加关系表(order_goods)中的数据才会影响order和goods表
　　在添加关系表数据的时候，一定要遵循一个原则，如果order中没有id=9的数据，在order_goods表中就不能在order_id这一列添加9，同           理goods中没有id=10的数据，在order_goods表中就不能在goods_id这一列添加10

 

 Practice：

 

　　--1.查询出每个月倒数第三天被雇佣的所有教师 last_day
　　　　select * from teacher where hire_date = last_day(hire_date)-2;
　　--2.找出早于2年前被雇佣的教师 months_between
　　　　select * from teacher where months_between(sysdate, hire_date)/12 > 2;
　　--3.以首字母大写的方式显示所有教师的姓名 initcap
　　　　select initcap(name) from teacher;
　　--4.显示不带有'a'的教师的姓名 not like
　　　　select name from teacher where name not like '%a%';
　　--5.显示正好为5个字符的教师的姓名 length
　　　　select name from teacher where length(name) = 5;
　　--6.显示所有教师姓名的前三个字符 substr
　　　　select substr(name, 1, 3) from teacher;
　　--7.显示所有教师的姓名，用'a'替换所有的'A' replace
　　　　select replace(name,'A','a') from teacher;
　　--8.显示满1年的教师的姓名和受雇日期 months_between
　　　　select name, hire_date from teacher where months_between(sysdate,hire_date) /12 >= 1;
　　--9.显示教师的详细资料，按姓名排序 order by
　　　　select * from teacher order by name;
　　--10.显示教师的姓名和被雇佣日期，根据其工作年限，将最老的教师排在最前面--->升序
　　　　select name, hire_date from teacher order by hire_date;
　　--11.显示所有教师的姓名，加入公司的年份和月份，按被雇佣日期的月份进行倒序排序，若月份相同则将最早年份的教师排在最前面
　　--to_char()
　　　　select name, to_char(hire_date,'yyyy') years, to_char(hire_date,'mm') monthes from teacher order by monthes desc, years;
　　--12.显示所有教师的姓名，年龄，描述，按年龄的降序排列，若年龄相同则按姓名升序排序 order by
　　　　select name, age, description from teacher order by age desc, name;
　　--13.找出在（任何年份的）2月份被雇佣的所有教师 to_char
　　　　select * from teacher where to_char(hire_date, 'mm') = 2;
　　--14.对于每个教师，显示其进入学校的天数 sys_date-
　　　　select ceil(sysdate-hire_date) from teacher;
　　--15.显示姓名字段的任何位置包含‘a’的所有教师的姓名
　　　　select name from teacher where name like '%a%';