（3）Oracle——————分组函数，子查询，创建和管理表和数据处理_创造函数能否用子查询

1，分组函数

• 1.1，常用的函数
• 1.2，分组数据
• 1.3，过滤分组

2，子查询

• 2.1，子查询的使用
• 2.2，使用子查询应注意的事项
• 2.3，单行子查询
• 2.4，多行子查询

3，创建和管理表

• 3.1，常见的数据库对象
• 3.2，查询数据字典
• 3.3，创建表
• 3.4，修改表
• 3.5，删除表
• 3.6，清空表
• 3.7，改变对象的名称

4，数据处理

• 4.1，插入数据
• 4.2，创建脚本
• 4.3，更新数据
• 4.4，删除数据
• 4.5，COMMIT和ROLLBACK语句的优点

分组函数

• AVG（平均值）
• SUM（合计）
• MIN（最小值）
• MAX（最大值）
• COUNT（计数）函数

例：计算不重复的记录总数

SELECT COUNT(DISTINCT department_id) FROM   employees
1

• 使用GROUP BY子句将表中的数据分成若干组

----求出emp表中的各部门的平均工资,按照平均工资从高往低排序

select a.deptno,avg(a.sal) avg_sal from emp a group by a.deptno order by avg_sal desc;
1

• 在GROUP BY子句中包含多个列

----求出emp表中的不同部门，不同职位的平均工资

select a.deptno,avg(a.sal) avg_sal,a.job from emp a group by a.deptno,a.job; 
1

• 使用 HAVING 过滤分组：

        ①， 行已经被分组

        ②， 使用了组函数

        ③， 满足HAVING 子句中条件的分组将被显示

----求出emp表中的不同部门，不同职位的平均工资且平均工资大于1000

select a.deptno,avg(a.sal) avg_sal,a.job from emp a  having avg(a.sal) > 1000 group by a.deptno,a.job; 
1

子查询

• 子查询 (内查询) 在主查询之前一次执行完成。
• 子查询的结果被主查询(外查询)使用 。

----查询比ALLEN工资高的职工名字

select a.ename
  from emp a
 where a.sal > (select sal from emp where ename = 'ALLEN');
1
2
3

        ①，子查询要包含在括号内。

        ②， 将子查询放在比较条件的右侧。

        ③，单行操作符对应单行子查询，多行操作符对应多行子查询。

• 只返回一行
• 使用单行比较操作符
  

• 返回多行
• 使用多行比较操作符
  
  ----根据部门编号分组，查询部门中大于某一部门的平均工资的部门信息

select department_id
  from employees
 group by department_id
having avg(salary) >= any (select avg(salary)
                             from employees
                            group by department_id);
1
2
3
4
5
6

创建和管理表

---- 查看用户定义的表

SELECT table_name FROM user_tables;
1

---- 查看用户定义的各种数据库

SELECT DISTINCT object_type FROM user_objects;
1

---- 查看用户定义的表, 视图, 同义词和序列

SELECT * FROM user_catalog;
1

• 命名规则
  

• 语法
  CREATE TABLE student (
  sno NUMBER(20),
  sname VARCHAR2(14),
  sclass VARCHAR2(13)
  );
• 数据类型
  
• 使用子查询创建表

create table emp1 as select * from emp;
1

创建的emp2是空表：

create table emp2 as select * from emp where 1=2;
1

• ALTER TABLE 语句

       追加新的列：

   alter table student add (sno varchar2(16));
1

       修改现有的列：

   alter table	student modify (sno varchar2(30));
1

       为新追加的列定义默认值：

   alter table	student modify (sno varchar2(30) default '000');
1

       删除一个列：

   alter table  student drop column sno;
1

       重命名表的一个列名：

   alter table student rename column sno to sid;
1

①，数据和结构都被删除
②，所有正在运行的相关事务被提交
③，所有相关索引被删除
④，DROP TABLE 语句不能回滚

DROP TABLE STUDENT;
1

• TRUNCATE TABLE 语句:

    ①，删除表中所有的数据
    ②，释放表的存储空间

  TRUNCATE TABLE student;
1

• TRUNCATE语句不能回滚

• 执行RENAME语句改变表, 视图, 序列, 或同义词的名称

 RENAME student TO student1;
1

数据处理

• INSERT 语句语法

   INSERT INTO student (stuid, sname, gender) VALUES (11, 'Tommey周', '男');
1

• 在SQL 语句中使用 & 变量指定列值，& 变量放在VALUES子句中

INSERT INTO student (stuid) VALUES ('&stuid');
1

• 从其它表中拷贝数据

INSERT INTO student
  (id, name, age, sclass)
  SELECT id, last_name, age, sclass FROM teacher
1
2
3

• UPDATE 语句语法

   update student set sname = 'Tommey周1' where stuid='1'
1

• DELETE 语句（如果条件省略，则删除表中的所有数据）

   DELETE FROM STUDENT WHRER STUID ='1'
1

• 删除中的数据完整性错误（应先删外键）
  You cannot delete a row that contains a primary key that is used as a foreign key in another table.

①，确保数据完整性。
②，数据改变被提交之前预览。
③，将逻辑上相关的操作分组。

• 使用 SAVEPOINT 语句在当前事务中创建保存点，使用 ROLLBACK TO SAVEPOINT 语句回滚到创建的保存点。

update student set sname = 'Tommey周1' where stuid='1'; 
SAVEPOINT update_done;
ROLLBACK TO update_done;
1
2
3

• 会话异常结束或系统异常会导致自动回滚。
• 提交或回滚前的数据状态
  改变前的数据状态是可以恢复的，执行 DML 操作的用户可以通过 SELECT 语句查询之前的修正其他用户不能看到当前用户所做的改变，直到当前用户结束事务。DML语句所涉及到的行被锁定， 其他用户不能操作。
• 提交后的数据状态
  数据的改变已经被保存到数据库中。改变前的数据已经丢失。所有用户可以看到结果。锁被释放,其他用户可以操作涉及到的数据。所有保存点被释放。

---

下一章，（4）Oracle——————约束，视图，序列和索引