PL SQL新建用户及员工信息表数据库基本操作练习

￡神魔★判官ぃ 2023-10-13 15:29 1阅读 0赞

### 1、创建用户sett ###

登录sys账号，左上角new，新建一个user

默认表空间users，临时表空间temp， 概要文件default（密码我设为了sett）

![aac1c83003d44ca49fabd32ea14251f6.png][]

角色权限创建如下，然后点击apply应用

![e82ed6f7a5b84f2ead7917b23eacdec4.png][]

登录sett

![66554decfb554675a20cc3eb299100f5.png][]

### 2、SQL练习1——基础 ###

--员工信息表 sett.t_staff_info
    CREATE TABLE sett.t_staff_info(
    	dept 		char(2) , 				--部门
    	staff_name 	varchar2(10) ,--工号
    	sex 		char(1) , 				--性别  
    	sal 		number(18,2)      --工资
    );
    insert into sett.t_staff_info(dept,staff_name,sex,sal) values('01','张三','0',5000);
    insert into sett.t_staff_info(dept,staff_name,sex,sal) values('01','李三','0',4000);
    insert into sett.t_staff_info(dept,staff_name,sex,sal) values('01','李四','1',5500);
    insert into sett.t_staff_info(dept,staff_name,sex,sal) values('02','王五','0',4500);
    insert into sett.t_staff_info(dept,staff_name,sex,sal) values('02','赵六','1',6000);
    insert into sett.t_staff_info(dept,staff_name,sex,sal) values('02','赵七','1',3000);

--1. 查询所有男员工的全部信息
    select * 
    from sett.t_staff_info 
    where sex='0';--假设0表示男士

![0129730508274319b572d2a9c95a3170.png][]

--2. 按部门统计合计薪水  即
    /*
    		部门			薪水
    		01				14500
    		02				13500
    */
    --注意：Oracle数据库只能使用空格起别名，as无效
    --order by是排序，group by是聚合；order by 中列，应该出现在group by 子句中
    select s.dept 部门,sum(s.sal) 薪水
    from sett.t_staff_info s 
    group by s.dept; 		
    
    --3. 按性别统计薪水   即
    /*
    		性别			薪水
    		男				13500
    		女				14500
    */
    --select s.sex,s.sal from sett.t_staff_info s group by s.sex;
    --上述语句报错：不是group by表达式，因为select中的字段必须包含在group中（sum、avg等除外）
    --select s.sex,s.sal from sett.t_staff_info s group by s.sex,s.sal;
    --上述语句查询结果有误，没有进行合并，错在应直接select sum（sal），否则就是按这两列分别合并了
    select 
      case s.sex  --case 字段 when 字段值 then 修改的字段值 end （as 别名）
      when '0' then '男'
      when '1' then '女' 
      end 性别,sum(sal) 薪水
    from sett.t_staff_info s 
    group by s.sex;
    
    		
    /*
    4. 按部门、性别统计人数、薪水		即
    		部门    性别		人数		薪水
    		01		男			2			9000
    		01		女			1			5500
    		02		男			1			4500
    		02		女			2			9000
    */	
    select s.dept 部门,
    case s.sex
      when '0' then '男'
      when '1' then '女'
      end 性别,count(*) 人数,sum(s.sal） 薪水
    from sett.t_staff_info s
    group by s.dept,s.sex
    order by s.dept,s.sex;
    
    /*		
    5. 如何达到如下效果
    	 部门			男薪水		女薪水		合计薪水
    	 01				9000			5500			14500
    	 02				4500			9000			13500
    */
    --方法一
    select s.dept 部门,
    sum(case s.sex when '0' then s.sal else 0 end) 男薪水,
    sum(case s.sex when '1' then s.sal else 0 end) 女薪水,
    sum(sal) 合计薪水
    from sett.t_staff_info s
    group by s.dept;
    --方法二
    select t.dept 部门,
    sum(t.男薪水),
    sum(t.女薪水),
    sum(t.薪水) 合计薪水
    from 
    (select s.dept,
      case when s.sex ='0' then s.sal end 男薪水,
      case when s.sex ='1' then s.sal end 女薪水,
      s.sal 薪水
     from sett.t_staff_info s) t
    group by t.dept;

**错误**：由于将数据insert了两次，所以应该先删除数据，注意，未提交时，查询表格仍未原数据。

![2ffd05a3f8d044cd905db54a7b54d55a.png][]

![cc87f103865d41979f89f0c70695ce95.png][]

### 3、SQL练习2——拓展 ###

/*
    CREATE TABLE sett.t_staff_info(
    	dept 		char(2) , 				--部门
    	staff_name 	varchar2(10) ,			--工号
    	sex 		char(1) , 				--性别0男1女
    	sal 		number(18,2)
    );
    insert into sett.t_staff_info(dept,staff_name,sex,sal) values('01','张三','0',5000);
    insert into sett.t_staff_info(dept,staff_name,sex,sal) values('01','李三','0',4000);
    insert into sett.t_staff_info(dept,staff_name,sex,sal) values('01','李四','1',5500);
    insert into sett.t_staff_info(dept,staff_name,sex,sal) values('02','王五','0',4500);
    insert into sett.t_staff_info(dept,staff_name,sex,sal) values('02','赵六','1',6000);
    insert into sett.t_staff_info(dept,staff_name,sex,sal) values('02','赵七','1',3000);
    */
    
    /*
    1. 查询所有男员工中薪水大于5000和女性中薪水大于4000的全部信息
    */
    -- or和in的互用（单个列表字段值集合）
    -- or和union
    --方法一
    select * from sett.t_staff_info t
    where (t.sex='0' and t.sal>5000) or (t.sex='1' and t.sal>4000);
    --方法二
    select * from sett.t_staff_info t where (t.sex='0' and t.sal>5000) 
    union
    select * from sett.t_staff_info t where (t.sex='1' and t.sal>4000);

![125712614db341f0bb01d7f9402af36e.png][]

/*
    2. 按部门统计合计薪水，仅显示合计薪水大于14000的部门及薪水  即
    		部门			薪水
    		01				14500
    */
    --第一种方法：
    --group by后用了having，对分组之后的结果进行了一个筛选
    SELECT t.DEPT AS 部门,SUM(t.SAL) AS 薪水
    FROM T_STAFF_INFO t
    GROUP BY t.DEPT
    HAVING SUM(t.SAL) >14000;
    --第二种方法：
    SELECT t.DEPT AS 部门, t.薪水
    FROM (SELECT DEPT, SUM(SAL) AS 薪水
          FROM T_STAFF_INFO
          GROUP BY DEPT) t
    WHERE t.薪水 > 14000;
    
    /*		
    3. 检索薪水大于3000的所有员工信息，按部门、性别统计人数、薪水，仅显示人数大于1的记录		即
    		部门    性别		人数		薪水
    		01		男			2			9000
    */
    --第一种方法：
    SELECT t.DEPT AS 部门, 
    CASE t.SEX 
    	WHEN '0' THEN '男'
    	WHEN '1' THEN '女'
    END as 性别, 
    t.人数, t.薪水
    FROM (SELECT DEPT, SEX, COUNT(*) AS 人数, SUM(SAL) AS 薪水
          FROM T_STAFF_INFO
          WHERE SAL > 3000
          GROUP BY DEPT, SEX) t
    WHERE t.人数 > 1;
    --第二种方法：
    SELECT t.DEPT AS 部门, 
    CASE t.SEX 
    	WHEN '0' THEN '男'
    	WHEN '1' THEN '女'
    END as 性别, 
    COUNT(*) AS 人数, SUM(t.SAL) AS 薪水
    FROM T_STAFF_INFO t
    WHERE t.SAL > 3000
    GROUP BY t.DEPT, t.SEX
    HAVING COUNT(*) > 1;	
    ----方法三
    --decode(字段名，字段值1，修改字段值1，字段2，修改字段2，.....) -- 分支函数
    SELECT
       t.DEPT AS 部门, decode(t.sex,'0','男','1','女') 性别,t.人数, t.薪水
    FROM 
       (SELECT 
          DEPT, SEX, COUNT(*) AS 人数, SUM(SAL) AS 薪水
       FROM 
          T_STAFF_INFO 
       WHERE SAL > 3000
       GROUP BY DEPT, SEX) t
    WHERE t.人数 > 1;

### 4、SQL练习3——中阶 ###

--员工基本工资表 sett.t_staff_salary
    CREATE TABLE sett.t_staff_salary(
    	sal_month		char(6) ,		    --基本工资月份,如201604 表示2016年4月
    	staff_serial	number(8), 		    --工号
    	sal  			number(18,2), 	    --基本工资
    	sal_subsidy		number(18,2), 	    --津贴
    	sal_dt			char(8)			    --发放日期,如20160505 表示2016年5月5日
    );
    alter table sett.t_staff_salary
    add constraint PK_STAFF_SALARY primary key (SAL_MONTH, STAFF_SERIAL);
    insert into sett.t_staff_salary(sal_month,staff_serial,sal,sal_subsidy,sal_dt) values('201706',1,7000,800,'20170710');
    insert into sett.t_staff_salary(sal_month,staff_serial,sal,sal_subsidy,sal_dt) values('201706',2,6000,800,'20170710');
    insert into sett.t_staff_salary(sal_month,staff_serial,sal,sal_subsidy,sal_dt) values('201706',3,4500,800,'20170710');
    insert into sett.t_staff_salary(sal_month,staff_serial,sal,sal_subsidy,sal_dt) values('201706',4,4600,800,'20170710');
    insert into sett.t_staff_salary(sal_month,staff_serial,sal,sal_subsidy,sal_dt) values('201706',5,4000,500,'20170710');
    insert into sett.t_staff_salary(sal_month,staff_serial,sal,sal_subsidy,sal_dt) values('201706',6,3600,500,'20170710');
    
    --员工信息表	sett.t_staff_info
    CREATE TABLE sett.t_staff_info(
    	staff_serial	number(8) 	,	    --工号
    	dept 			char(2) 	,	    --部门
    	staff_name 		varchar2(10)	, 	--姓名
    	sex  			char(1) 	,	    --性别，0表示男，1表示女
    	staff_lead		number(8) 		    --工号, 其领导工号		
    );
    alter table sett.t_staff_info
    add constraint PK_STAFF_INFO primary key (STAFF_SERIAL);
    insert into sett.t_staff_info(staff_serial,dept,staff_name,sex,staff_lead) values(1,'00','张三','0',0);
    insert into sett.t_staff_info(staff_serial,dept,staff_name,sex,staff_lead) values(2,'01','张四','0',1);
    insert into sett.t_staff_info(staff_serial,dept,staff_name,sex,staff_lead) values(3,'01','李四','1',2);
    insert into sett.t_staff_info(staff_serial,dept,staff_name,sex,staff_lead) values(4,'01','李五','0',2);
    insert into sett.t_staff_info(staff_serial,dept,staff_name,sex,staff_lead) values(5,'02','王四','1',1);
    insert into sett.t_staff_info(staff_serial,dept,staff_name,sex,staff_lead) values(6,'02','赵六','1',5);

/*
    1. 查询2017年6月所有基本工资低于5000元的男员工或者低于4000元女员工的信息，按部门降序，工号升序排列。 显示为：
       部门		工号		姓名		性别（男/女）		基本工资		津贴		合计收入
    */
    --方法一
    SELECT 
        s.dept 部门,
        s.staff_serial 工号,
        s.staff_name 姓名,
        CASE WHEN s.sex = '0' THEN '男' ELSE '女' END AS 性别,
        sa.sal 基本工资,
        sa.sal_subsidy 津贴,
        (sa.sal + sa.sal_subsidy) AS 合计收入
    FROM 
        sett.t_staff_info s
    JOIN 
        sett.t_staff_salary sa ON s.staff_serial = sa.staff_serial
    WHERE 
        sa.sal < 5000
        AND (sa.sal < 4000 OR s.sex = '0')
        AND sa.sal_month = '201706'
    ORDER BY 
        s.dept DESC,
        s.staff_serial ASC;
    
    --方法二
    SELECT 
        s.dept 部门,
        s.staff_serial 工号,
        s.staff_name 姓名,
        CASE WHEN s.sex = '0' THEN '男' ELSE '女' END AS 性别,
        sa.sal 基本工资,
        sa.sal_subsidy 津贴,
        (sa.sal + sa.sal_subsidy) AS 合计收入
    FROM 
        sett.t_staff_info s
    JOIN 
        sett.t_staff_salary sa ON s.staff_serial = sa.staff_serial
    WHERE 
        (sa.sal < 5000 AND s.sex = '0')
        OR (sa.sal < 4000 AND s.sex = '1')
        AND sa.sal_month = '201706'
    ORDER BY 
        s.dept DESC,
        s.staff_serial ASC;
    --方法三
    SELECT 
        s.dept 部门,
        s.staff_serial 工号,
        s.staff_name 姓名,
        CASE WHEN s.sex = '0' THEN '男' ELSE '女' END AS 性别,
        sa.sal 基本工资,
        sa.sal_subsidy 津贴,
        (sa.sal + sa.sal_subsidy) AS 合计收入
    FROM 
        sett.t_staff_info s,sett.t_staff_salary sa
    WHERE 
        s.staff_serial = sa.staff_serial
        AND ((sa.sal < 5000 AND s.sex = '0')
        OR (sa.sal < 4000 AND s.sex = '1'))
        AND sa.sal_month = '201706'
    ORDER BY 
        s.dept DESC,
        s.staff_serial ASC;

![4660596444ca46b883b974d71da35a24.png][]

/*
    2. 查询2017年6月所有基本工资介于5000元与8000元之间的员工信息，包括
    	 部门		工号		姓名		领导姓名		基本工资		津贴		合计收入
    */		
    --方法一
    SELECT 
        s.dept 部门,
        s.staff_serial 工号,
        s.staff_name 姓名,
        lead.staff_name AS 领导姓名,
        sa.sal 基本工资,
        sa.sal_subsidy 津贴,
        (sa.sal + sa.sal_subsidy) AS 合计收入
    FROM 
        sett.t_staff_info s
    JOIN 
        sett.t_staff_salary sa ON s.staff_serial = sa.staff_serial
    LEFT JOIN 
        sett.t_staff_info lead ON s.staff_lead = lead.staff_serial
    WHERE 
        sa.sal BETWEEN 5000 AND 8000
        AND sa.sal_month = '201706';
    --方法二
    SELECT 
        s.dept 部门,
        s.staff_serial 工号,
        s.staff_name 姓名,
        lead.staff_name AS 领导姓名,
        sa.sal 基本工资,
        sa.sal_subsidy 津贴,
        (sa.sal + sa.sal_subsidy) AS 合计收入
    FROM 
        sett.t_staff_info s
    JOIN 
        sett.t_staff_salary sa ON s.staff_serial = sa.staff_serial
    LEFT JOIN 
        sett.t_staff_info lead ON s.staff_lead = lead.staff_serial
    WHERE 
        sa.sal >= 5000
        AND sa.sal <= 8000
        AND sa.sal_month = '201706';

![f54221228be64a169affb1554eb86328.png][]

/*
    3. 查询2017年6月其所有领导基本工资大于6000元的员工信息
    	 部门		工号		姓名		基本工资		津贴	
    */	 
    --方法一
    SELECT 
        s.dept 部门,
        s.staff_serial 工号,
        s.staff_name 姓名,
        sa.sal 基本工资,
        sa.sal_subsidy 津贴
    FROM 
        sett.t_staff_info s
    JOIN 
        sett.t_staff_salary sa ON s.staff_serial = sa.staff_serial
    JOIN 
        sett.t_staff_salary lead_sa ON s.staff_serial = lead_sa.staff_serial
    WHERE 
        lead_sa.sal > 6000
        AND sa.sal_month = '201706';
    
    --方法二	 
    SELECT 
        s.dept 部门,
        s.staff_serial 工号,
        s.staff_name 姓名,
        sa.sal 基本工资,
        sa.sal_subsidy 津贴
    FROM 
        sett.t_staff_info s
    JOIN 
        sett.t_staff_salary sa ON s.staff_serial = sa.staff_serial
    WHERE 
        s.staff_serial IN (
            SELECT 
                staff_serial
            FROM 
                sett.t_staff_salary
            WHERE 
                sal > 6000
                AND sal_month = '201706'
        );
    	 
    
    /*				
    4. 查询2017年6月部门平均收入大于5000元的所在部门的员工信息
    	 部门		工号		姓名		基本工资		津贴
    */
    --方法一
    SELECT 
        s.dept 部门,
        s.staff_serial 工号,
        s.staff_name 姓名,
        sa.sal 基本工资,
        sa.sal_subsidy 津贴
    FROM 
        sett.t_staff_info s
    JOIN 
        sett.t_staff_salary sa ON s.staff_serial = sa.staff_serial
    WHERE 
        s.dept IN (
            SELECT 
                dept
            FROM 
                sett.t_staff_info
            GROUP BY 
                dept
            HAVING 
                AVG(sal + sal_subsidy) > 5000
        )
        AND sa.sal_month = '201706';
    
    --方法二
    SELECT 
        s.dept 部门,
        s.staff_serial 工号,
        s.staff_name 姓名,
        sa.sal 基本工资,
        sa.sal_subsidy 津贴
    FROM 
        sett.t_staff_info s
    JOIN 
        sett.t_staff_salary sa ON s.staff_serial = sa.staff_serial
    where sa.sal_month = '201706'
    GROUP BY 
        s.dept, s.staff_serial, s.staff_name, sa.sal, sa.sal_subsidy
    HAVING 
        AVG(sa.sal + sa.sal_subsidy) > 5000;

![ea99613c15a040e69364fc503ea495bd.png][]

/*				
    4. 查询2017年6月部门平均收入大于5000元的所在部门的员工信息
    	 部门		工号		姓名		基本工资		津贴
    */
    --方法一
    SELECT 
        s.dept,
        s.staff_serial,
        s.staff_name,
        sa.sal,
        sa.sal_subsidy
    FROM 
        sett.t_staff_info s
    JOIN 
        sett.t_staff_salary sa ON s.staff_serial = sa.staff_serial
    WHERE 
        s.dept IN (
            SELECT 
                dept
            FROM 
                sett.t_staff_info
            GROUP BY 
                dept
            HAVING 
                AVG(sal + sal_subsidy) > 5000
        )
        AND sa.sal_month = '201706';
    
    --方法二
    SELECT 
        s.dept,
        s.staff_serial,
        s.staff_name,
        sa.sal,
        sa.sal_subsidy
    FROM 
        sett.t_staff_info s
    JOIN 
        sett.t_staff_salary sa ON s.staff_serial = sa.staff_serial
    where sa.sal_month = '201706'
    GROUP BY 
        s.dept, s.staff_serial, s.staff_name, sa.sal, sa.sal_subsidy
    HAVING 
        AVG(sa.sal + sa.sal_subsidy) > 5000;

![dd3cb74fa02f4392a0dbd3b1d5158269.png][]

[aac1c83003d44ca49fabd32ea14251f6.png]: https://img-blog.csdnimg.cn/aac1c83003d44ca49fabd32ea14251f6.png
[e82ed6f7a5b84f2ead7917b23eacdec4.png]: https://img-blog.csdnimg.cn/e82ed6f7a5b84f2ead7917b23eacdec4.png
[66554decfb554675a20cc3eb299100f5.png]: https://img-blog.csdnimg.cn/66554decfb554675a20cc3eb299100f5.png
[0129730508274319b572d2a9c95a3170.png]: https://img-blog.csdnimg.cn/0129730508274319b572d2a9c95a3170.png
[2ffd05a3f8d044cd905db54a7b54d55a.png]: https://img-blog.csdnimg.cn/2ffd05a3f8d044cd905db54a7b54d55a.png
[cc87f103865d41979f89f0c70695ce95.png]: https://img-blog.csdnimg.cn/cc87f103865d41979f89f0c70695ce95.png
[125712614db341f0bb01d7f9402af36e.png]: https://img-blog.csdnimg.cn/125712614db341f0bb01d7f9402af36e.png
[4660596444ca46b883b974d71da35a24.png]: https://img-blog.csdnimg.cn/4660596444ca46b883b974d71da35a24.png
[f54221228be64a169affb1554eb86328.png]: https://img-blog.csdnimg.cn/f54221228be64a169affb1554eb86328.png
[ea99613c15a040e69364fc503ea495bd.png]: https://img-blog.csdnimg.cn/ea99613c15a040e69364fc503ea495bd.png
[dd3cb74fa02f4392a0dbd3b1d5158269.png]: https://img-blog.csdnimg.cn/dd3cb74fa02f4392a0dbd3b1d5158269.png