oracle数据库多表查询、sql1999语法、子查询、内连接、外连接（左外连接，右外连接）、统计查询、多字段分组、创建和管理表

Oracle重点知识点

1、排序：排序可以是实现多条件排序(同一条件相同的情况下按照后续条件进行排序即可一次类推完成)

语法：

order by  列名1 desc ,列名2 desc

案例：

---

2.多表查询：
语法：

1.Select *   from  表1，表2=select count(*)  表1 × select count(*) from 表 2
2.Select *   from  表1，表2≠select count(*) 表1+select count(*) from 表2

分析：产生“积”的问题
解决：消除“积”-，添加条件查询结果此时正常显示，但是“积”还存在。
“积”的影响：
数据库数据量小的情况下无明显影响。
数据库数据量大的情况下处理性（查询）能差。
说明：处理性能差会会影响用户下降

3.内连接：多张表指定的列必须相等 :[from 左表，右表] 左表.字段=右表.字段
“（+）” 此符号属于oracle数据库控制外连接：

  右外连接：[from 左表，右表]  左表.字段（+）=右表.字段
  左外连接：[from 左表，右表]  左表.字段=右表.字段（+）

说明：外连接可以控制具体表的显示。防止数据丢失

sqll1999语法：
作用：解决多表连接查询积过多造成性能的问题，
语法： Select 别名1., 别名2.
From 表1 别名1【 CROSS JOIN 表2 别名】…
【NATURAL JOIN 表2】…
【JOIN 表2 USING(column_name)】…
【JOIN 表2 ON (表1.column_name=表2.column_name)】…
【LEFT |R|GHR|FULL OUTER JOIN 表2
ON(表1.column_name=表2.column_name)】

交叉连接（ CROSS JOIN）的目的主要是为了产生“笛卡尔积”。

语法：

Select  别名1.*, 别名2.*
From 表1 别名1    CROSS JOIN  表2 别名....；

结果：产生“笛卡尔积”数据量大时,查询效率低，性能低；

---

自然连接（NATURAL JOIN）自动获取关联列（同名）。
语法：

SELECT *  FROM  表1  NATURAL JOIN  表2;

作用：自然连接可以直接消除笛卡尔积

---

3.USING子句（手动设置指定的关联列）
语法：

Select *  from 表1...... JOIN  表2  USING(列名称);

---

4.ON子句（手动设置关联条件）
语法：

 Select  *  from  table1  JOIN   table2 ... ON（table1.name=table2.name）---->等值条件

作用：使用等值连接形式消除了“笛卡尔积”；

---

5.外连接（left左外连接，right右外连接，FULL全外连接）
语法：

Select  表1.column_name，表2.column_name 
From 表1  LEFT |RIGHT|FULL   OUTER  JOIN   表2
ON(表1.column_name=表2.column_name)

---

6.全外连接
语法：

Select   * 
From  表1  列名1   FULL   OUTER  JOIN  表2 列名2
ON （列名1.column_name=列名2.column_name）-----等值条件

作用：显示所有数据的显示，
Sql1999语法相对提升数据库性能查询多表连接查询所有定律都有可能被打破。

---

多表查询
使用sql1999语法
评估数据量

多表查询思路：
确定数据表
确定已知关联字段
数据集合操作

作用：集合是针对于查询结果的一种处理，包含交集，补集，差集，并集等
应用场景：微博共同好友，qq共同好友等功能。

多表查询是一种数据的确定而数据集合是对查询结果的一种处理。

---

统计查询
作用：数据表的信息统计

一、统计函数
统计函数包括：
COUNT()：统计个数
SUM()：计算求和
MIN()：求最小值
AVG()：求平均值
MAX()：求最大值
注意min(),max()也可以查询时间
Count的使用三种方式作用：
count():统计所有的数据个数
Count(列)：count( DISTINCT 列)和count（）统计的个数结果一样
如果列为空时，count（列）不作为统计计算
说明：如果“列”为空统计不作为计算，所以count()和count(列)比较之下，使用count（）作为统计计算更合理。DISTINCT(不能为空)

---

二、多字段分组：
Select 分组字符，统计函数，
From 表1 列名1，表2 列名2
Where 参与运算的条件
Group by（分组字段1 ，分组字段2…）
Order by （排序字段）asc|desc
注意：多字段分组必须多个字段内容重复
Having子句
Select 分组字符，统计函数，
From 表1 列名1，表2 列名2
Where 参与运算的条件
Group by（分组字段1 ，分组字段2…）
Having分组条件过滤（二次条件过滤）
Order by （排序字段）asc|desc
Where 和having的区别：
Where在group by前使用目的是分组数据
Having在group by后使用目的是进行二次数据过滤

---

三、子查询
子查询用（）定义
Where 子查询：子查询返回单行单列，多行单列，单行多列
Having子查询：单行单列包含统计函数
From 子查询：子查询返回多行多列（单行多列）
Select 子查询：单行单列

---

四、Exists运算
In 和exists区别：
In:表示范围判断
Extsis:表示结果集判断

---

五、Having子句查询：
Having子句往往结合统计形式查询，并且进行二次筛选查询
多表查询会产积的问题，子查询会提高查询性能，开发中经常子查询

---

六、更新:
Update 表 set 数值=“”，数值=“” ：
可以根据where更新数据返回，单行单列
Update 表 set 数据=“” 更新全表数据，返回多行多列
实际项目中进行逻辑删除不做物理删除

---

七、数据库事务控制：
Commit;提交事务
Rollback：回滚事务
is null：为空
In:不为空
Not

---

数据库

创建和管理表

create table student(

   id number,
   
   name varchar2(100),
   
   email varchar2(100),
   
   class_id number

)

添加新列
alter table student add age number

修改列的数据类型

alter table student modify age varchar2(100)

删除列
alter table student drop (age) 必须加 ()

修改表名

rename student《原表名》 to stu <修改表名> 表名不能有任何重名

删除表
drop table stu《表》

截断表：

truncate table stu 相当于把 stu表删除后，又重新创建

表建好后，添加约束

添加主键约束

    alter table student add constraint stu_pk primary key(id)
	
	
添加唯一约束

    alter　table student add constraint stu_email_un unique(email)	
	
添加非空约束

    alter table student modify student_name varchar2(100) not null

添加外键约束

     alter table student add constraint stu_fk foreign key (class_id) references class (id)
	 
添加检查约束

     alter table student add constraint stu_age_check check(age>0 and age<100)	

---

数据库对象
1.table表
2.view视图
3.sequense序列<重点>
4.index索引
5.synonym 同义词

视图作用：
1.限制数据访问 2.是的复杂查询容易 3.提供数据独立性 4表现相同数据不同观察

视图创建：
create view v_employees_dept90 as
select employee_id,last_name,salary from employees where department_id=90

对视图进行的DML操作，形成视图的表中的数据也会改变！！！
简单视图:能进行DML操作
复杂视图： 不能进行DML操作

select d.department_name,avg(e.salary) avg_sal from employees e,departments d 
 where e.department_id = d.department_id group by e.department_id,d.department_name
 
 select * from v_avgsalary_dept_name
 
 update v_avgsalary_dept_name set avg_sal = 1000 where department_name= 'Finance'	

伪列和TOP-N：《查询数据库总共有多少条数据》

rowid     这条数据在数据文件中的偏移量（理解为地址）根据此地址可以查询数据库中的数据

rownum	


查询数据库表中有多少条数据；
 select  rownum,e. * from  employees《表名》 e = select  e. * from employees《表名》 e 

 按条件查询数据库中条数

             select rownum,e.* from employees《表名》 e where rownum<=5等于select e.* from empoyees<表名> e where rownum<=5
		  
              select rownum,e.* from employees《表名》 e where rownum>=5 查不出数据  也就是说，rownum 不能使用 >   = 和 >= 只能使用<和<=

          	


内建视图：  
    where 中 子查询叫做子查询   from后的子查询 叫做 内建视图


             select t.* from (select rownum rn,e.* from employees e)t where t.rn between 3 and 5	
	 
	 
	  查询工资排在3 到 5 位
	 select tt.* from (select rownum rn,t.* from (select last_name ,salary from employees order by salary desc)t) tt where tt.rn between 3 and 5

序列；《sequense》
1.是自动产生的唯一数
2.是可共享的对象
3.创建主键用途
4.可以替代应用程序编号
5.效率高

存储值

select stu_seq.nextval from dual
insert  into 表名(id ,name,sex ) values (stu_seq.nextval,'name','sex')

索引：
目的提高查询效率

create index emp_last_name_index on employees(last_name)

加了索引就提供了查询效率，那么数据库为什么不把所有列都加上索引？

加了索引就一定会提高数据库的使用效率吗？ 提高查询效率，但会降低增删改的效率

---

DCL：

1 创建用户
create user zhangsan<用户名> identified by a123<旧密码>

2 分配权限

   grant  connect,resource to zhangsan<用户名>   分配角色权限<登录>
   
    revoke connect,resource from zhangsan  撤销角色权限
   
   grant create session, create table to zhangsan 分配系统权限
   
     revoke create session, create table from zhangsan

---

关于角色：
1 创建角色

    create role manager

2 给角色分配权限

  grant create session, create table to manager

3 给用户分配角色

  grant manager to zhangsan,admin	
   用户的角色决定权限	 

4 修改用户密码：

	 alter user zhangsan identified by b123<新密码>

5 对象权限：

  grant insert,update on employees to zhangsan	
  revoke insert,update on employees from zhangsan

---

集合运算：

A (1,2,3,4) B (3,4,5)
—(1,2,3,4,5)
—(3,4)
—(1,2)

并集：

union:
		 select * from hr.employees where employee_id<10
union

select * from employees where employee_id between 104 and 108	

union all :
	select * from hr.employees where employee_id<105
union all
select * from employees where employee_id between 104 and 108  	

---

交集：

 select * from hr.employees where employee_id<105

   intersect

   select * from employees where employee_id between 104 and 108 

差集：

        select * from hr.employees where employee_id<105

                      minus
        select * from employees where employee_id between 104 and 108