常用易忘sql

常用易忘sql

---

ORACLE預設是根據ROWID來排序的啊，ROWID是資料庫唯一標識不能更改的，
所以你想在資料庫更改順序是不可能。只能透過索引對欄位排序，之後使用索引，
這樣效果其實一樣的。

關於表空間：
1 檢視錶空間：
  select *from v$tablespace;

2 建立臨時表空間
  create temporay tablespace temp1 tempfile
  '/home/oracle/app/oradata/orcl/temp1.dbf' size 20M;

3 建立資料表空間
  create tablespace data datafile '/home/oracle/app/oradata/orcl/data.dbf'
  size 20M;

4 建立oudo表空間
  create undo tablespace undo_dud datafile '/home/oracle/app/oradata/oral/undo_dud.dbf'
  size 20M;

5 更改undo表空間屬性
  alter system set undo_talespace=undo;



關於表屬性：
1 表的後面增加一個屬性
  alter table emp add (ename varchar2(10) not null);

2 在固定屬性後面新增一個屬性
  alter table emp add (job varchar2(9) not null) afer ename; (不行)

3 檢視錶在哪個表空間下
  select table_name, tablespace_name from user_tables where table_name='EMP';
  (這裡EMP一定要大寫)

4 更改列屬性
   alter table emp modify job varchar2（5）；


5 檢視錶的屬性
  desc emp；
6 清空表
truncate table ‘emp’；

6 檢視錶的行數
  select count(column_id) from user_tab_columns where tabel_name = 'emp';


7 修改關係表的名字
  alter table emp rename to ddemp；

8 修改表屬性名字
  alter table emp rename column empno to id；

9 刪除行屬性
  alter table emp drop column empno；



查詢語句：

1  找出有獎金的員工的不同工作
  select distinct job from emp where comm is not null and comm>0;

2 找出部門10中既不是經理也不是普通員工，而且工資大於2000的員工
  select *from emp where deptno=10 and job not in ('manager', 'clerk') and sal>2000;

3  顯示僱員名字，根據其服務年限，將最老的僱員排在最前面
   select ename from emp order by hiredate;

4  查詢工資與獎金的總共total
   select sal, comm, nvl2(comm, sal+comm, sal) total from emp;
5  查詢在相同deptno的人的平均工資的最高
   select max(avg(sal)) from emp group by deptno;

6  算出部門30中得到最多獎金的員工的姓名
   select ename from emp where comm = (select max(comm) from emp where deptno=30);

7  算出每個職位員工數的和最低工資
   select job，min(sal), count(*） from emp group by deptno；

8  
   select deptno, job, avg(sal)
   from emp
   where hiredate>=to_date('1981-05-01', 'yyyy-mm-dd')
   group by deptno, job
   having avg(sal)>1200
  order by deptno, job;

9  統計各個部門下平均工資大於500的部門
   select deptno, avg(sal) from emp group by deptno having avg(sal)>500;

10 將第一個字母變成大寫
   select initcap('hello world') from dual;

SQL 中的 TRIM 函式是用來移除掉一個字串中的字頭或字尾。最常見的用途是移除字首或字尾的空白。這個函式在不同的資料庫中有不同的名稱：
MySQL: TRIM(),RTRIM(),LTRIM()
Oracle: RTRIM(),LTRIM()
SQL Server: RTRIM(),LTRIM()



11 算出每個職位的員工數和最低工資
   select job, min(sal), count(*) from emp group by job;

12 得到工資大於自己部門平均工資的員工的資訊
   select *from emp e1, (select depno, avg(sal)  as avgsal from emp group by deptno)e2
   where e1.deptno=e2.deptno and e1.sal>e2.avgsal;

13 分組統計每個部門下，每種職位的平均獎金 （也要算沒得獎金的人） 和總工資 （包括獎金）
   select deptno, job, avg(nvl(comm, 0)), sum(sal+nvl(comm， 0）） from emp group deptno, job;

14算出每個部門，每個職位的平均工資和平均獎金， 如果平均獎金大於300，顯示。。
   按部門編號降序，平均工資降序排列
   SELECT EMPNO, ENAME, SAL,
    CASE DEPTNO
    WHEN 10 THEN '10'
    WHEN 20 THEN '20'
    WHEN 30 THEN '30'
    ELSE 'ELSE'
    END DEPTNO
    FROM EMP;