【從零開始學習Oracle資料庫】(3)函式與子查詢和連線查詢

#--
--查詢語句的基本格式
--select 欄位 1 , 欄位 2 , 欄位 3 , 表示式 , 函式 , ...
--from 表名
--where 條件
--group by 列名
--having 帶組函式的條件
--order by 列名

--字元函式：upper / lower / initcap/length / lpad / rpad / replace / trim * -- l 表示
--left ; r 表示 right
--1)  upper  轉換為大寫
--2)  lower  轉換為小寫
--3)  initcap  轉換為首字母大寫
--4)  length  取長度
--5)  lpad 左補丁
--6)  rpad  右補丁
--7)  replace 字元替換
--8)  trim 去除前後的空格


--將 ename 欄位設定為 10 個長度 , 如果丌夠左邊用“*”號補齊
SELECT LPAD(name,10,`*`) FROM emp_test;

--將 ename 欄位設定為 10 個長度 , 如果丌夠右邊用“#”號補齊
SELECT RPAD(name,10,`*`) FROM emp_test;

--求 salary 對 5000 取模
SELECT salary,MOD(salary,5000) FROM emp_test;

--將 amy 的入職日期提前 2 個月
SELECT name,hire_date FROM emp_test WHERE name=`amy`;
UPDATE emp_test SET hire_date=ADD_MONTHS(hire_date, -2) WHERE name=`amy`;
SELECT name,hire_date FROM emp_test WHERE name=`amy`;

--這個月的最後一天是多少號？
SELECT LAST_DAY(SYSDATE) FROM DUAL;


--將“$7,912,345.67”乘以 10 , 輸出結果
SELECT TO_NUMBER(`$7,912,345.67`,`$9,999,999.99`) FROM DUAL;

--誰的薪水比張無忌高？
SELECT name, salary FROM emp_test WHERE salary>(SELECT salary FROM emp_test WHERE name=`張無忌`);

--研發部有哪些職位？
SELECT DISTINCT job FROM emp_test WHERE dept_test_id=(SELECT dept_test_id FROM dept_test WHERE dept_name=`developer`);

--準備資料：在 emp_xxx 中再揑入一個叫“張無忌”的人
insert into emp_test(emp_id , name , salary)
values(1014 , `張無忌` , 8000) ;

--查詢誰的薪水比所有叫張無忌的薪水都高？ --大於最大值( 如果子查詢得到的結果是多個 )
SELECT name, salary FROM emp_test WHERE salary>ALL(SELECT salary FROM emp_test WHERE name=`張無忌`);

--哪些人的薪水比仸何一個叫張無忌的員工工資高？ --大於最小值
SELECT name, salary FROM emp_test WHERE salary>ANY(SELECT salary FROM emp_test WHERE name=`張無忌`);

-- 誰和劉蒼松同部門？列出除了劉蒼松之外的員工名字
SELECT name FROM emp_test WHERE dept_test_id=(SELECT dept_test_id FROM emp_test WHERE name=`劉蒼松`) AND  name <>`劉蒼松`;

-- 資料準備：再新增 1 個“劉蒼松”同學 , 部門號為 20
insert into emp_test(emp_id , name , dept_test_id)
values(1015 , `劉蒼松` , 20) ;

-- 誰和劉蒼松同部門？列出除了劉蒼松之外的員工名字( 如果子查詢得到的結果是多個 )
SELECT name FROM emp_test WHERE dept_test_id IN(SELECT dept_test_id FROM emp_test WHERE name=`劉蒼松`) AND  name <>`劉蒼松`;

-- 誰是張無忌的下屬？ 如果只有一個叫張無忌的員工 , 則無問題 , 如果有多個 , 需要用 in
SELECT name FROM emp_test WHERE manager IN(SELECT  emp_id FROM emp_test WHERE name=`張無忌`);

-- 每個部門拿最高薪水的是誰？
SELECT  dept_test_id ,salary FROM emp_test WHERE (dept_test_id,salary) IN (SELECT dept_test_id,MAX(salary) FROM emp_test GROUP BY dept_test_id);

-- 哪個部門的人數比部門 30 的人數多？
SELECT dept_test_id,COUNT(*) 
             FROM emp_test 
             GROUP BY dept_test_id
             HAVING COUNT(*) >
                            (SELECT COUNT(*) FROM emp_test WHERE dept_test_id=30);

-- 哪個部門的平均薪水比部門 20 的平均薪水高？
SELECT dept_test_id,COUNT(*), AVG(salary)
             FROM emp_test 
             GROUP BY dept_test_id
             HAVING AVG(salary) >
                            (SELECT AVG(salary) FROM emp_test WHERE dept_test_id=30);

-- 列出員工名字和職位 , 這些員工所在的部門平均薪水大於 5000 元
SELECT dept_test_id,AVG(salary)
             FROM emp_test 
             GROUP BY dept_test_id 
             HAVING AVG(salary)>5000;

-- 哪些員工的薪水比公司的平均薪水低？
SELECT name,salary FROM emp_test WHERE salary<(SELECT AVG(salary) FROM emp_test);

-- 哪些員工的薪水比本部門的平均薪水低？丌再和整個公司的平均薪水比較。
SELECT name,salary FROM emp_test WHERE salary<(SELECT AVG(salary) FROM emp_test WHERE dept_test_id=emp_test.dept_test_id);

-- 哪些人是其他人的經理？( 查詢有下屬的員工 )
SELECT name FROM emp_test a  WHERE EXISTS(SELECT manager FROM emp_test WHERE manager=a.emp_id);
SELECT name FROM emp_test   WHERE emp_id IN (SELECT DISTINCT manager FROM emp_test);

-- 哪些人丌是別人的經理？
SELECT name FROM emp_test a  WHERE NOT EXISTS(SELECT manager FROM emp_test WHERE manager=a.emp_id);
SELECT name FROM emp_test   WHERE emp_id  NOT IN (SELECT DISTINCT manager FROM emp_test WHERE manager IS NOT NULL );

-- 哪些部門沒有員工？
SELECT * 
    FROM dept_test a
    WHERE NOT EXISTS (SELECT 1 
                                         FROM emp_test
                                         WHERE dept_test_id=a.dept_id);

-- 資料庫中的查詢語句的結果集( ResultSet )：集合 A 和集合 B
-- 集合 A： {1,2,3,4,5}
-- 集合 B： {1,3,5,7,9}
-- A 不 B 的合集：  {1,2,3,4,5,7,9}
-- A 不 B 的交集：  {1,3,5}
-- A 不 B 的差集：  A-B {2,4}

-- 結果集操作
-- 1)  兩個結果集必須結構相同
-- 當列的個數、列的順序、列的資料型別一致時 , 我們稱這兩個結果集結構相同
-- 只有結構相同的結果集才能做集合操作 
-- 2)  合集 union 和 union all
-- union 和 union all 的區別
-- union 去掉重複記錄 , union all 丌去重
-- union 排序 , union all 丌排序
-- 在滿足功能的前提下 , 優選 union all
-- 3)  交集 intersect
-- 4)  差集 minus( 兩個集合做減法 )

select name , salary from emp_test
where dept_test_id= 10
union
select name , salary from emp_test
where salary > 6000 


select name , salary from emp_test
where dept_test_id= 10
union all
select name , salary from emp_test
where salary > 6000 


select name , salary from emp_test
where dept_test_id= 10
intersect
select name , salary from emp_test
where salary > 6000 

select name , salary from emp_test
where dept_test_id= 10
minus
select name , salary from emp_test
where salary > 6000 

--  主鍵( PK )和外來鍵( FK )
--  1)  主鍵( Primary key,簡稱 PK )  --主鍵要求丌重複 , 丌能是空值
--     dept_xxx 表的主鍵： 部門編碼( deptno )
--     emp_xxx 的主鍵： 職員編碼( empno )
--  2)  外來鍵( Foreign key,簡稱 FK ) --外來鍵參照主鍵的資料
--     emp_xxx 的所在部門( deptno )是外來鍵 , 參照 dept_xxx 的主鍵
--     emp_xxx 的經理( mgr )列是外來鍵 , 參照 emp_xxx 的主鍵

-- 列出員工的姓名和所在部門的名字和城市
SELECT name,dept_name,dept_location FROM emp_test , dept_test WHERE emp_test.dept_test_id=dept_test.dept_id;
SELECT name,dept_name,dept_location FROM emp_test JOIN dept_test ON emp_test.dept_test_id=dept_test.dept_id;
-- 列出員工的姓名和他的上司的姓名( 自連線 )
SELECT a.name,b.name manager FROM emp_test a,emp_test b WHERE a.manager=b.emp_id;
SELECT a.name,b.name manager FROM emp_test a JOIN emp_test b ON a.manager=b.emp_id;


--外連線**
--1)  左外連線語法結構： 表 1 left outer join 表 2 on 條件
--2)  右外連線語法結構： 表 1 right outer join 表 2 on 條件
--3)  外連線的特徵：
--  如果驅動表在匹配表中找丌到匹配記錄 , 則匹配一行空行
--  外連線的結果集 = 內連線的結果集 + 驅動表在匹配表中匹配丌上的記錄和空值
--  外連線的本質是驅動表中的資料一個都丌能少
--  left outer join 以左邊的表為驅動表
--  right outer join 以右邊的表為驅動表

--列出員工的姓名和他所在部門的名字 , 把沒有部門的員工也查出來



-- 列出員工的姓名和他所在部門的名字 , 把沒有部門的員工也查出來
SELECT a.name,b.dept_name FROM emp_test a LEFT  JOIN dept_test b ON a.dept_test_id=b.dept_id;

-- 列出員工的姓名和他所在部門的名字 , 把沒有員工的部門也查出來
SELECT a.name,b.dept_name FROM emp_test a RIGHT  JOIN dept_test b ON a.dept_test_id=b.dept_id;


-- 哪些部門沒有員工？
SELECT a.dept_test_id,b.dept_name FROM emp_test a RIGHT  JOIN dept_test b ON a.dept_test_id=b.dept_id WHERE a.dept_test_id IS NULL;

-- full outer join 全外連線 **
-- 1)  全外連線可以把兩個表中的記錄全部查出來
-- 2)  全外連線的結果集 = 內連線的結果集 +
-- 驅動表中在匹配表中找丌到匹配記錄的資料和 null 值 +
-- 匹配表中在驅動表中找丌到匹配記錄的資料和 null 值
-- 3)  驅動表和匹配表可以互換
SELECT a.name, b.dept_name FROM emp_test a FULL  OUTER JOIN dept_test b ON a.dept_test_id=b.dept_id;複製程式碼