Oracle数据库入门 基础知识day04 火推阳光笔记

 作业:

1.  显示员工的last_name和对应的部门名称, 要求, 把不存在部门编号的员工也给查询到 :

    题1的前置条件: 
        公司的业务需要 , 把id为25的员工 分配到一个新的项目组中, 部门名称待定!

        update s_emp set dept_id=null where id=25;
        commit;
        select last_name,name from s_emp e,s_dept d where e.dept_id=d.id(+);


2.  显示每个部门的名称 和 对应的地区的名字 , 没有地区编号的部门也要显示

    1.  s_dept表中的 name
    2.  s_region表中的name

    题2的前置条件: 

        公司业务需要, 增加了新的部门, 但是还没确定办公地点 !

        insert into s_dept values(250,'zhiqiang1',null);
        insert into s_dept values(251,'zhiqiang2',null);
        commit;

        select d.id, d.name,r.name from s_dept d,s_region r where d.region_id=r.id(+);

3.  显示每个员工的工资 和 对应的工资级别 , 超出工资级别范围的 也要显示出来

    工资表: salgrade 


    老板觉得自己工资太低了,  都不够每天泡个脚的 , 给自己涨了工资 涨到了66666, 
    给自己的小秘书涨到了8888

    update s_emp set salary=66666 where id=1;
    update s_emp set salary=8888 where id=24;
    commit;


    在进行外连接时, 如果进行了between (区间)进行比较 , 区间的两边都要添加(+)


    between losal(+) and hisal(+);


    select salary,grade from s_emp,salgrade where salary between losal(+) and hisal(+);
组函数

特点: 

    对于一组数据处理完毕后, 只返回一个结果 .  
    要求在查询时, 字段产生的结果要一一对应 !
    组函数对于null的处理是忽略

常用的组函数: 

    count   : 统计数据的数量
    max     : 统计一组数据中的最大值
    min     : 统计一组数据中的最小值
    sum     : 统计一组数据的和
    avg     : 计算一组数据的平均值



1.  求出s_emp表格中存在几个员工? 

    select count(*) from s_emp;

2.  求出s_emp表中最高的工资

    select max(salary) from s_emp;

3.  求出s_emp表格中的最低工资

    select min(salary) from s_emp;

4.  求出员工的工资总和

    select sum(salary) from s_emp;

5.  排重后再使用组函数求和

    select sum(distinct salary) from s_emp;

6.  求出所有员工的工资平均值

    select avg(salary) from s_emp;

7.  求出所有员工的提成的平均值

    select avg(commission_pct) from s_emp;  //得出的结果不对 .

    select sum(commission_pct)/count(*) from s_emp;
软件连接失败network adapter

服务未启动 

    OracleXETNSListener服务未启动的原因: 

        1.  防火墙 / 杀毒软件 阻止了监听服务器的启动

        2.  服务的配置文件产生错误(安装数据库以后, 更改了计算机名称)

    解决方案: 
        重装
group by having分组子句

格式: ...group by 分组标准 having 组中的数据过滤条件

限制: 在带有分组的查询语句中 ,select后跟随的字段, 要么是分组标准字段,要么时经过组函数处理的字段 !

select 语句 完整格式: 
    select 字段 from 表名 [where 条件] [group by 分组标准 [having 过滤条件]] [order by 排序字段 排序规则];

select 子句执行顺序: 
    from子句-->where子句-->group by-->having-->select-->order by
where与having条件的区别:

where 对于当前查询的表格中的所有数据进行过滤 .

having: 对于分组以后的数据进行过滤

where过滤在having之前
-   按照部门的编号进行员工的分组,  统计每个部门的人数

    select dept_id,count(*) from s_emp group by dept_id;

-   按照部门的编号进行员工的分组, 统计每个部门的人数 ,要求 ,部门人数低于3的不显示此部门

    select dept_id,count(*) from s_emp group by dept_id having count(*)>2;

-   按照部门编号分组,显示部门的编号, 平均工资,为了公司的数据看起来漂亮, 只显示平均工资大于1200的部门 .
    select dept_id,avg(salary) from s_emp group by dept_id having avg(salary)>1200;

-   按照部门编号分组 ,显示部门的人数, 部门的编号 部门的名字

    select count(*),dept_id,min(name) from s_emp e,s_dept d where e.dept_id=d.id group by dept_id;

    count(*)--存在12个count
    dept_id--存在12个dept_id
    max(name)   --  存在12个name
子查询

概念: 把一个select语句的结果 , 当作另一个select语句的一部分 . 

每一个select语句 就类似存在于内存中的一个表 .  而子查询就是查询一个已经存在的结果集 !   

例如:  之前我们查询员工信息 : 

    select id,last_name,salary from s_emp;//生成了一个结果集

    基于上面的结果, 查询其中id为25的员工

    select * from (select id,last_name,salary from s_emp) where id=25;

子查询的语句 , 可以出现的位置: 

        1.  where条件之后

        2.  having条件之后

        3.  from子句后, 替换表名.
应用到where条件之后 : 
    把一个select语句的结果, 当作了另一个select语句的where条件

    需求: 查询部门id最大的员工信息

        1.  先查询出最大的部门id

        select max(dept_id) from s_emp;
        2.  根据上面查询的部门id,查询部门的人员信息
        select id,salary,last_name from s_emp where dept_id=(select max(dept_id) from s_emp);

    需求: 查询公司所有的领导
        1.  先查询到公司所有的领导id(s_emp的mananger_id字段),并去重
        select distinct manager_id from s_emp where manager_id is not null;
        2.  根据上面查询到的领导的id , 通过in去匹配公司所有的领导
        select id,last_name,salary from s_emp where id in(select distinct manager_id from s_emp where manager_id is not null);
    需求: 查询公司所有的普通员工
        1.  先查询到公司所有的领导id(s_emp的mananger_id字段),并去重
        select distinct manager_id from s_emp where manager_id is not null;
        2.  根据上面查询到的领导的id , 通过not in去匹配公司所有的普通员工
        select id,last_name,salary from s_emp where id not in(select distinct manager_id from s_emp where manager_id is not null);
应用到having之后
    把一个select语句的结果, 当作了另一个select语句的分组 过滤条件

    需求:找出部门的平均薪资比32号部门的平均薪资高的部门 ,显示部门的编号与平均薪资

        1.  先查找出32号部门的平均薪资
            select avg(salary) from s_emp where dept_id=32;

        2.  根据部门进行分组, 计算部门的平均薪资, 分组的过滤条件为, 平均薪资大于上面的查询结果
            select dept_id,avg(salary) from s_emp group by dept_id having avg(salary)>(select avg(salary) from s_emp where dept_id=32);
应用到from之后

    把一个select查询语句的结果, 当作一个内容中的二维表

    需求: 查询所有的员工的id,last_name,salary, 条件为 薪资大于1200
        select id,last_name,salary from s_emp where salary>1200;
        从上面的结果集中 查询所有的id,条件为id大于15
        select id from (select id,last_name,salary from s_emp where salary>1200) where id>15;
DDL(数据定义语句)

创建一个表格

数据类型: 

    number  : 数字
    varchar2: 变长字符串
    date    : 日期类型

创建表格的格式: 


create table 表名(
    字段名1 数据类型(长度),
    字段名2 数据类型(长度),
    ...
    字段名n 数据类型(长度)
);


练习: 

    创建一个表格, 
    表名为person
    字段为: 
        -   id  长度为5的number类型
        -   name 长度为10的varchar2类型
        -   age 长度为3的number类型


    create table person(
        id number(5),
        name varchar2(10),
        age number(3)
    );


练习2.

    创建一个表格,
    表名为dsj13_user
        字段为:
        -   id  长度为5的number类型
        -   username 长度为20的varchar2类型
        -   password 长度为20的varchar2类型


    create table dsj13_user(id number(5),username varchar2(20),password varchar2(20));

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

创建完毕, 使用desc查看一下表结构
删除一个表格

格式: drop table 表名;

练习: 
    删除person表
    drop table person;
    删除dsj13_user表
    drop table dsj13_user;
修改表的结构(了解)

很浪费系统的性能 ,
    1.  将表中的数据 进行备份
    2.  将表中的数据删除, 并将结构修改
    3.  将备份的数据 插入


-   删除表中的字段

    格式: alter table 表名 drop column 字段名称;

    需求: 删除person表格中的name字段

    alter table person drop column name;


-   添加表中的字段

    格式: alter table 表名 add 字段名 字段类型(长度);

    需求: 向person表中 添加一个name字段 ,类型为varchar2长度为10 

    alter table person add name varchar2(10);
DML 数据操作语言

所有的dml操作. 都需要结束事务 !

事务: 将一系列的sql语句 看作一个业务, 统一处理 , 要么一起成功, 要么一起回滚!

银行金融系统 

志强   1020000

陈凯     6
----------------

5块

1.  将志强余额减5
2.  凯凯的余额加5
3.  提交

转账业务

mysql

-----------
提交事务

commit;
回退事务

rollback;
insert语句 - 向数据库插入数据

1.  全字段添加
    格式: insert into 表名 values(数据列表);

    数据列表 : 表示的是一个数据组, 数据的顺序 按照创建表格时的字段顺序传入 ,不同字段的值使用英文逗号隔开

    例如:  表格的字段创建顺序如下: 

        create table person(
            id number(5),
            name varchar2(100),
            age number(8)
        );

    我们在对这个表格进行全字段的添加数据时: 

    insert into person values(id,name,age);

    练习: 

        -   向数据库中的person表格插入如下数据: 

            1.  编号:10,姓名:张三,年龄:18
            insert into person values(10,'张三',18);
            2.  编号:11,姓名:李四,年龄:28
            insert into person values(11,'李四',28);
            3.  编号:12,姓名:王二,年龄:38
            insert into person values(12,'王二',38);
            4.  编号:13,姓名:麻子,年龄:8
            insert into person values(13,'麻子',8);

            commit;
2.  选择字段添加

    格式: insert into 表名(字段列表) values(数据列表);
    --  字段列表可以任意排列 , 多个字段名之间使用英文逗号隔开 , 数据列表的顺序, 参照字段列表

    练习: 
        -   向数据库中的person表格插入如下数据: 
            1.  编号:20,姓名:待定,年龄:10
            insert into person(age,id) values(10,20);
            2.  编号:21,姓名:待定,年龄:20
            insert into person(age,id) values(20,21);
            3.  编号:22,姓名:待定,年龄:30
            insert into person(age,id) values(30,22);
            4.  编号:23,姓名:待定,年龄:40
            insert into person(age,id) values(40,23);
            commit;