馬士兵視訊SQL程式碼

05 select
select ename||'daiosnad''dase' from emp;
select distinct deptno from emp; //去重複
select distinct deptno,job from emp; //重複的組合去掉


06 where
select * from emp where deptno = 10;//過濾條件
select * from emp where ename = 'CLARK';
select ename,sal from emp where sal >  1500;
select ename,sal,deptno from emp where deptno <>10;
select ename,sal from emp where ename > 'CBA';
select ename,sal from emp where sal between 800 and 1500;
select ename,sal from emp where sal >=800 and <=1500;
  空值處理
select ename,sal,comm from emp where comm is null; //選擇comm為空值的
select ename,sal,comm from emp where comm is not null; //選擇comm不為空值的
select ename,sal,comm from emp where sal in (800,1500,2000);//選擇sal為800,1500,2000的
select ename,sal,comm from emp where ename in ('SMITH','KING');//選擇ename為SIMTH,KING的
  簡單日期處理
select ename,sal,hiredate from emp where hiredate > '20-2月-81';//簡單日期處理
select ename,sal from emp where deptno = 10 and sal > 1000;
select ename,sal from emp where sal not in (800,1500);
  模糊查詢
select ename from emp where ename like '%ALL%';   //%表示一個或者多個
select ename from emp where ename like '_A%';  //_表示一個字母
--select ename from emp where ename like '%\%%';  // 預設\為轉義字元
--select ename from emp where ename like '%$%%' escape '$';  //指定$為轉義字元


07 order by
select * from dept order by deptno desc; //desc 表示降序
select empno, ename from emp order by empno asc; //預設升序，或者asc
select empno, ename from emp where deptno <>10 order by empno asc; 
--select ename, sal, deptno from emp order by deptno asc, ename desc;


08 sql function_1
select lower(ename) from emp; //將ename全部變小寫；
select ename from emp where lower(ename) like '_a%';
select substr(ename,1,3) from emp;//從第1個字元開始截，截3個字元
select chr(65) from dual;  //chr 將asc碼轉換為字元
select assii('A') from dual; //從字元獲得ASC碼
select round(23.23) from dual; // 取整
select round(23.231 , 1) from dual ;// 留1位小數
select to_char(sal, 'L99,999.9999') from emp;//將sal轉換為99,999.9999的字串形式  9代表一位數字
select to_char(sal, 'L00000.0000)from emp;// 9沒有時不顯示，0 強制顯示
select to_char(hiredate, 'YYYY-MM-DD HH:MI:SS') from emp；  //顯示時間
select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;


09 sql function_2
select ename,hiredate from emp where hiredate > to_date('1981-2-20 12:34:56','YYYY-MM-DD HH24:MI:SS');  //to_date 將 字串轉換為日期
select sal from emp where sal > to_number('$1,250.00','$9,999.99');
select ename, sal*12 + nvl(comm, 0) from emp; // nvl如果comm中的值為空值，以0取代他


10 group gunction  //多輸入 一輸出


max min avg sum count


select max(sal) from emp; //取sal最大值
select min(sal) from emp;
select avg(sal) from emp;
select to_char(avg(sal),'99999999.99') from emp;
select round(avg(sal),2) from emp;
select sum(sal) from emp;
select count(*) from emp; //有多少條記錄
select count(*) from emp where deptno =10;


select count(ename) from emp;
select count(comm) from emp;
select count(distinct deptno) from emp;


11 group by


select deptno,avg(sal) from emp group by deptno; //按deptno分組之後求均值
select deptno,job,max(sal) from emp group by deptno, job;//按兩個欄位deptno,job分組
select ename from emp where sal = (select max(sal) from emp);


12 having
select avg(sal),deptno from emp group by deptno having avg(sal) >2000;


select avg(sal) 
from emp 
where sal >1200
group by deptno
having avg(sal) > 1500
order by avg(sal) desc;


13 子查詢
select ename, sal from emp
where sal > (select avg(sal) from emp);


select ename, sal from emp
join (select max(sal) max_sal, deptno from emp group by deptno) t
on (emp.sal = t.max_sal and emp.deptno = t.deptno);


14 self_table_connections


select e1.ename, e2.ename from emp e1 , emp e2 where e1.mgr = e2.empno;


15 1999sql


select ename,dname from emp join dept on (emp.deptno = dept.deptno);
select ename,grade from emp e join salgrade s on (e.sal between s.losal and  s.hisal);




三個表連線
select ename,dname,grade from
emp e join dept d on (e.deptno = d.deptno)
join salgrade s on (e.sal between s.losal and s.hisal)
where ename not like '_A%';


左外連線
select e1.ename, e2.ename from emp e1 left join emp e2 on (e1.mgr = e2.empno);
右外連線
select ename,dname from emp e right join dept d on (e.deptno = d.deptno);
全外連線
select ename,dname from emp e full join dept d on (e.deptno = d.deptno);




部門平均的薪水等級
select deptno,avg_sal,grade from
(select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal);


select deptno ,avg(sal) from
(select deptno ,sal, grade from emp join salgrade s on (emp.sal between s.losal and s.hisal)) t
group by deptno


--19_不用組函式 求薪水最大值
select distinct sal from emp where sal not in
(select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal));




哪些人是經理人
select ename from emp where empno in (select mgr from emp);


20平均薪水最高部門的編號與名稱
select dname,deptno from dept where deptno = (
select deptno from (select deptno,avg(sal) avg_sal from emp group by deptno)
where avg_sal = (select max(avg_sal) from (select deptno,avg(sal) avg_sal from emp group by deptno)))


select deptno,dept.dname from dept where dept.deptno = (
select deptno
from 
(select emp.deptno,avg(emp.sal) from emp group by deptno order by avg(sal) desc) 
where 
rownum =1)


select dname,deptno 
    from dept where deptno = (
        select deptno from 
(select deptno,avg(sal) avg_sal from emp group by deptno)
        where avg_sal = (select max(avg(sal)) from emp group by deptno))




22 平均薪水的等級最低的部門名稱
select dname,t1.deptno,grade,avg_sal from (
       select deptno, grade,avg_sal from
                 (select deptno,avg(sal) avg_sal from emp group by deptno) t
                 join salgrade s on (t.avg_sal between s.losal and s.hisal)
       )t1
join dept on (t1.deptno = dept.deptno)
where t1.grade = (
  select min(grade) from (
       select deptno, grade,avg_sal from
                 (select deptno,avg(sal) avg_sal from emp group by deptno) t
                 join salgrade s on (t.avg_sal between s.losal and s.hisal)))
     


建立檢視 --  v$dept_avg_sal_info
 create view v$dept_avg_sal_info as
select deptno, grade,avg_sal from
                 (select deptno,avg(sal) avg_sal from emp group by deptno) t
                 join salgrade s on (t.avg_sal between s.losal and s.hisal)




使用檢視簡化
select dname,t1.deptno,grade,avg_sal from 
      v$dept_avg_sal_info  t1
join dept on (t1.deptno = dept.deptno)
where t1.grade = (
  select min(grade) from (
       select deptno, grade,avg_sal from
                v$dept_avg_sal_info))




24_比普通員工薪水還要高的 經理人的名字
select ename from emp 
where empno in (select distinct mgr from emp where mgr is not null)
and sal >
(select max(sal) from emp
where empno not in (select distinct mgr from emp where mgr is not null))




25 rownum


複製備份表
create table emp2 as select * from emp;


26 update /transaction  事務
薪水翻倍
update emp2 set sal = sal*2 ,ename = ename||'-' where deptno = 10;


delete
delete from emp2 where deptno = 10;


commit 提交/ 修改起作用


27_create table 
create table  


create table stu 
  2  ( 
  3  id number(6),
  4  name varchar2(20) constraint stu_name_nn  not null,
  5  sex number(1),
  6  age number(3),
  7  sdate date,
  8  grade number(2) default 1,
  9  class number(4),
 10  email varchar2(50) unique  //欄位級約束
 11  );




desc stu;




28_constraint  非空 唯一 主鍵 外來鍵 cheak




名字和EMAIL的組合不重複
create table stu
  2  (
  3  id number(6),
  4  name varchar2(20) constraint stu_name_nn  not null,
  5  sex number(1),
  6  age number(3),
  7  sdate date,
  8  grade number(2) default 1,
  9  class number(4),
 10  email varchar2(50),
 11  constraint stu_name_email_uni unique(email,name) //表級約束
 12  );


主鍵約束  欄位級約束
create table stu
  2  (
  3  id number(6) primary key, //主鍵 非空，唯一   代表了一條記錄
  4  name varchar2(20) constraint stu_name_nn  not null,
  5  sex number(1),
  6  age number(3),
  7  sdate date,
  8  grade number(2) default 1,
  9  class number(4),
 10  email varchar2(50),
 11  constraint stu_name_email_uni unique(email,name) 
 12  );




主鍵約束  表級約束
create table stu
  2  (
  3  id number(6) , //主鍵 非空，唯一   代表了一條記錄
  4  name varchar2(20) constraint stu_name_nn  not null,
  5  sex number(1),
  6  age number(3),
  7  sdate date,
  8  grade number(2) default 1,
  9  class number(4) ,
 10  email varchar2(50),
 11  constraint stu_name_email_uni unique(email,name) ,
 12  constraint stu_id_pk primary key (id)
 13  );






外來鍵  欄位級約束
create table stu
  2  (
  3  id number(6) , //主鍵 非空，唯一   代表了一條記錄
  4  name varchar2(20) constraint stu_name_nn  not null,
  5  sex number(1),
  6  age number(3),
  7  sdate date,
  8  grade number(2) default 1,
  9  class number(4) references class(id),
 10  email varchar2(50),
 11  constraint stu_name_email_uni unique(email,name) ,
 12  constraint stu_id_pk primary key (id)
 13  );


外來鍵  表級約束  被參考的欄位 必須是主鍵
create table stu
  2  (
  3  id number(6) , //主鍵 非空，唯一   代表了一條記錄
  4  name varchar2(20) constraint stu_name_nn  not null,
  5  sex number(1),
  6  age number(3),
  7  sdate date,
  8  grade number(2) default 1,
  9  class number(4) ,
 10  email varchar2(50),
 11  constraint stu_class_fk foreign key(class) references class(id),
 12  constraint stu_name_email_uni unique(email,name) ,
 13  constraint stu_id_pk primary key (id)
 14  );


create table calss
(
id number(4) primary key,
name varchar2(20) not null
)


31_alter
修改表結構


alter table stu add(addr varchar2(100));// 增加欄位
alter table stu drop(addr);//刪減欄位
alter table stu modify(addr varchar2(150));//修改欄位 修改後要能容納原來的資料
alter table stu drop constraint sut_clall_fk;//刪除約束條件
alter table stu add constraint stu_class_fk foreign key (class) references class(id);//增加約束條件