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

r囧r小猫 2023-07-14 08:09 32阅读 0赞

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语句的优点

#### 分组函数 ####

1，常用的函数

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

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

SELECT COUNT(DISTINCT department_id) FROM   employees

2，分组数据

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

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

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

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

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

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

3，过滤分组

*  使用 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');

2，使用子查询应注意的事项：

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

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

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

3，单行子查询：

*  只返回一行
 *  使用单行比较操作符  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70]

4，多行子查询：

*  返回多行
 *  使用多行比较操作符  
    ![在这里插入图片描述][20200313223153520.png]  
    \----根据部门编号分组，查询部门中大于某一部门的平均工资的部门信息

select department_id
      from employees
     group by department_id
    having avg(salary) >= any (select avg(salary)
                                 from employees
                                group by department_id);

#### 创建和管理表 ####

1，常见的数据库对象

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 1]

2，查询数据字典

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

SELECT table_name FROM user_tables;

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

SELECT DISTINCT object_type FROM user_objects;

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

SELECT * FROM user_catalog;

*  命名规则  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 2]

3，创建表

*  语法  
    CREATE TABLE student (  
    sno NUMBER(20),  
    sname VARCHAR2(14),  
    sclass VARCHAR2(13)  
    );
 *  数据类型  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 3]
 *  使用子查询创建表

create table emp1 as select * from emp;

创建的emp2是空表：

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

4，修改表

*  ALTER TABLE 语句

追加新的列：

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

修改现有的列：

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

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

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

删除一个列：

alter table  student drop column sno;

重命名表的一个列名：

alter table student rename column sno to sid;

5，删除表

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

DROP TABLE STUDENT;

6，清空表

*  TRUNCATE TABLE 语句:

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

TRUNCATE TABLE student;

*  TRUNCATE语句不能回滚

7，改变对象的名称

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

RENAME student TO student1;

#### 数据处理 ####

1，插入数据

*  INSERT 语句语法

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

2，创建脚本

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

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 4]

*  从其它表中拷贝数据

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

3，更新数据

*  UPDATE 语句语法

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

4，删除数据

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

DELETE FROM STUDENT WHRER STUID ='1'

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

5，COMMIT和ROLLBACK语句的优点

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

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

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

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

--------------------

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70]: /images/20230529/306e9259fb6b42c596f0cfc1b6169747.png
[20200313223153520.png]: /images/20230529/a02863f662994d68916bce02a0d0eee6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 1]: /images/20230529/b05e578f830745a8b725dd88908c2795.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 2]: /images/20230529/c78750d20d6746b5a72d13e837d82a45.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 3]: /images/20230529/01a2d12221df4d9a9545c8eddcc2f698.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 4]: /images/20230529/e0b7c85be7c74a1ea4ce34a689aadd98.png
[4_Oracle_]: https://blog.csdn.net/qq_41530004/article/details/104910148