整理總結資料庫常用sql語句，建議收藏，忘記了可以來看一下
第一節課：sql語言介紹（參照PPT）及基本查詢sql學習
1、資料庫表的介紹
emp表：員工表
dept表：部門表
salgrady：薪資水平表
Balance：
2、基本的查詢語句：
知識點：
select * from 表名 查詢表中所有的數據
select 欄位名1，欄位名2，..from 表名 查詢指定欄位的所有值，不同欄位之間使用逗號隔開
使用算術表達式：select 欄位名(運算符），欄位名(運算符）…from 表名
案例：
select * from emp—查詢所有員工資訊
select ename,job,sal from emp–查詢員工姓名，工作，薪資
查詢所有員工一年的工資，以及每位員工年終3000後的所有薪資（不包括津貼）
select ename,job,sal*12,sal*12+3000 from emp 使用運算符
第二節課：
1、使用別名
as可以省略不寫，如果有特殊字元獲取空格使用雙引號添加別名
select ename 姓名,job 工作,sal*12 年資,sal*12+3000 年薪 from emp 直接在欄位後面添加別名
select ename “姓名”,job 工作,sal*12 』年資』,sal*12+3000 年薪 from emp 使用雙引號添加別名,不能使用單引號
select ename as 姓名,job “工作”,sal*12 年資,sal*12+3000 年薪 from emp 使用as關鍵字添加別名
select ename “姓 名”,job 工作,sal*12 年資,sal*12+3000 年薪 from emp 如果別名中有空格或者其他特殊字元，使用雙引號
2、使用鏈接符：
select ename,job,sal from emp;
select ename||job,sal from emp; 使用||符號進行字元鏈接
select ename||’ 的工作是 ‘||job||’ 並且月工資是 ‘||sal from emp; 字元鏈接，普通字元使用單引號
3、去除重複 distinct
select * from emp;
select job from emp–發現工作是重複的，而工作種類是需要去除重複的
問題：查看員工工作種類
select distinct job from emp 使用distinct關鍵字去除重複值
問題；怎麼多欄位去除重複
select distinct job,sal from emp; 多欄位去除重複，去除的是重複的一整條資訊
4、排序
知識點：
select 欄位名，欄位名…from 表名 order by 欄位名
select *from 表名 order by 欄位名
講解：
–查詢員工姓名，工作，薪資
select ename,job,sal from emp
–查詢按照薪資排序的員工姓名，工作，薪資
select ename,job,sal from emp order by sal; 使用order by排序
select ename,job,sal from emp order by sal desc; 使用desc關鍵從大到小排序
select ename,job,sal from emp order by sal asc; 使用asc關鍵從小到大排序，默認是從小到大排序
select * from emp order by sal,ename 多欄位作為排序條件的時候，優先按照第一個欄位排序，然後依次按照其他欄位排序。
select ename,job,sal 工資 from emp order by 工資 使用別名
select ename,job,sal from emp order by sal*12 使用算術表達式
第三節課：簡單的where子句
使用where條件進行結果篩選
語法：select * from 表名 where 條件；注意：條件中欄位值區分大小寫，欄位名不區分大小寫，欄位值使用單引號括起來
1、在where中使用算術表達式 = ,< , > ,>= ,<= ,<>
2、使用order by 對篩選結果進行排序，order by 出現在where後面
查看工資等於1250的員工資訊
select * from emp where sal=’1250′–篩選條件是個數字也可以使用單引號
查看工作等於CLERK的員工資訊
select * from emp where job=’CLERK’–在篩選條件中欄位值如果是字元需要加上單引號
select * from emp where job=’clerk’–在sql語句中欄位的值是區分大小寫的
select * from emp where JOB=’CLERK’–在sql中欄位是不區分大小寫的
查看工資大於1250的員工姓名和工作
select ename,job ,sal from emp where sal>’1250′ order by sal–使用order by 對篩選結果進行排序，order by 出現在where後面
查看工資大於等於2000的員工資訊
select * from emp where sal>=2000;
查看工資小於等於2000的員工資訊；
select * from emp where sal<=2000
查看工資不等於1500的員工資訊
select * from emp where sal<>1500 order by sal
查看入職日期在81年後的員工資訊
select * from emp where hiredate>’1981年12月31號’
select * from emp where hiredate>’1981/12/31′
select * from emp where hiredate>’31-12月-1981′–使用日期的默認格式查詢符合要求的數據，日-月-年
第四節課：where子句使用關鍵字
使用where子句進行結果的篩選
知識點：where查詢條件中使用關鍵字
1、and 用於多條件的與篩選：select * from 表名 where 條件 and 條件 and 條件….
2、or 用於多條件的或篩選： select * from 表名 where 條件 or 條件 or 條件….
3、in 用於多條件的或篩選： select * from 表名 where 欄位名 in(值，值，值….)
4、like用於模糊查詢： select * from 表名 where 欄位名 like ‘%值%’ 包含
5、is null 和is not null 用來判斷欄位是否為空 select * from 表名 where 欄位名 is null
講解： 在where子句中使用關鍵字（and, or ,like, is null,is not null,between and,）
–查詢工資在2000-3000之間的員工資訊
select * from emp where sal>=2000 and sal<=3000–使用and關鍵字進行”與”的多條件篩選；
select * from emp where sal between 2000 and 3000;–使用between and 關鍵字進行篩選；
–查詢工作為SALESMAN,ANALYST,MANAGER的員工資訊
select * from emp where job=’SALESMAN’ or job=’ANALYST’ or job=’MANAGER’–使用or關鍵字進行”或”的多條件篩選
select * from emp where job in(‘SALESMAN’,’ANALYST’,’MANAGER’);–使用in關鍵字進行”或”的多條件篩選
select * from emp where job=’ANALYST’
–查詢姓名中包含s的，以s開頭的，以s結尾的，第二個字元為A的。
select * from emp where ename like ‘%S%’;–使用like關鍵字，姓名中包含S的,%代表任意多個字元
select * from emp where ename like ‘S%’;–使用like關鍵字，以S開頭的
select * from emp where ename like ‘%S’;–以S結尾的
select * from emp where ename like ‘_A%’–使用”_”指定位置包含指定字元的資訊，”_”代表任意一個字元
————–查詢名字中包含下劃線的用戶資訊
select * from emp where ename like ‘%A_%’escape ‘A’;–使用escape關鍵字將普通字元設置成為轉譯字元。
–查詢有津貼的員工資訊
select * from emp where comm is not null;
select * from emp where comm is null;–查詢沒有津貼的員工資訊
第五節課：函數學習
–查詢工作為SALESMAN，MANAGER並且工資大於2500的員工資訊
–1、使用小括弧提升where篩選條件的執行優先順序別
–2、and的優先順序別高於or
select * from emp
select * from emp where job=’SALESMAN’ or job=’MANAGER’ and sal>2500
select * from emp where (job=’SALESMAN’ or job=’MANAGER’) and sal>2500
———————————————————————————————————–
使用函數 單行函數 多行函數 轉換函數 其他函數
–單行函數學習：不改變原始數據，只改變結果
—1、字元函數
select * from emp;
select INITCAP(ename) from emp;–initcap函數將首字母大寫
select lower(ename)from emp;–lower 字母小寫
select replace(ename,’S’,’M’) from emp;–replace 替換
–2、數值函數–Math
—-偽表 dual
select * from dual
select abs(-3) 絕對值，ceil(3.1415926)向上取整,floor(3.1415926)向下取整,power(2,3)冪,round(3.4)四捨五入 from dual
–3、日期函數
select months_between(’13-12月-2016′,’13-10月-2016′) from dual–months_between兩個日期之間的月份數
第六節課：多行函數學習
多行函數 ：
max: max(欄位名) 返回此欄位的最大值
min：min(欄位名) 返回此欄位的最小值
avg：avg(欄位名) 返回平均值
sum：sum（欄位名） 返回欄位的和
count:count
–count(*)，用來查詢表中有多少條記錄
–count（欄位名），用來查詢某個欄位有值的個數
–count(distinct 欄位名),可以先去除重複再計數。
注意：
–多行函數不能和普通欄位直接出現在查詢語句中，除非group by
–多行函數和單行函數不能直接出現在查詢語句中，除非group by
查看員工的最高工資
select max(sal),ename from emp–多行函數不能和普通欄位直接出現在查詢語句中，除非group by
select max(sal)，lower(ename) from emp–多行函數和單行函數不能直接出現在查詢語句中，除非group by
select ename from emp
查看員工的最低工資
select min(sal) from emp
查看員工的平均工資
select avg(sal) from emp
查看所有的員工工資之和
select sum(sal) from emp
查詢公司有多少員工
select * from emp
select count(*) from emp–使用count(*)來查看一張表中有多少條記錄
查詢有津貼的員工人數
select count(comm) from emp–使用count(欄位名),查詢該欄位有值的記錄數
select count(ename) from emp
查詢公司有多少工作種類
select count(job) from emp
select count(distinct job) from emp
========================================================================================================================================
第三天：
第一節課：轉換函數學習
轉換函數:在轉換的時候改變的是數據的類型，數據內容不會改變，可以指定格式。
1、to_number：將數字字元轉換為數字類型的數值，to_number(數字字元)
2、to_char：將數字轉換為字元類型，將日期轉換為字元類型 to_char(數字/日期)
3、to_date：將字元類型的日期轉換為日期類型： to_date(char)
————————————————————————————–
number–>char 轉換的時候使用的是默認格式，
select to_char(123) from dual–將數字轉換為字元類型，使用to_char(number)
select sal, to_char(sal) from emp
number–>char 使用指定的格式
to_char(number,’格式’)，格式：
$代表美元符，9程式碼數字佔位。例如，L999,999,999表示三位一組的顯示方式L代表人民幣符號，0代表保留數字個數，不足使用0補充。
select to_char(sal),to_char(sal,’L999,999,999′) from emp
select to_char(sal),to_char(sal,’L0000.00′) from emp
char—>number to_number(數字字元)
select to_number(‘123’) from dual
char—>date，轉換的字元必須是日期格式的字元串，默認格式 dd-mm-yyyy
注意：因為字元串有很多，所以在字元轉換為日期的時候，需要指定格式，因為日期是具備一定格式的字元組合。
字元轉換為日期的時候，指定的格式為字元的日期順序，無需指定間隔符。
yyyy：表示年 mm:表示月 dd表示日
select to_date(’05-12月-2016′) from dual;
select to_date(‘2016-05-12′,’yyyy-mm-dd’) from dual–使用指定的格式將指定的日期字元串轉換為日期
select to_date(’12-05-2016′,’mm/dd/yyyy’) from dual
date—>char
注意：因為日期本身就具備一定的格式在不是指定格式的情況下會默認使用dd-mm-yyyy格式顯示數據
指定的格式會作為日期轉換為字元串類型的顯示格式存在。例如：
yyyy-mm-dd’ 『2016-12-05』
yyyy/mm/dd’ ‘2016/12/05’
yyyy”年”mm”月”dd”日”‘ 2016年12月05日
select hiredate,to_char(hiredate) from emp–日期轉換為字元的時候，不指定格式使用默認格式：dd-mm-yyyy
select hiredate,to_char(hiredate,’yyyy-mm-dd’) from emp;–使用指定格式將日期轉換為字元串類型
select hiredate,to_char(hiredate,’yyyy/mm/dd’) from emp;–使用指定格式將日期轉換為字元串類型
select hiredate,to_char(hiredate,’yyyy”年”mm”月”dd”日”‘) from emp;–使用指定格式將日期轉換為字元串類型
查詢入職日期在81年10月20日後的員工資訊
第一種：自動轉型
select * from emp where hiredate>’20-10月-1981′
第二種：將日期轉換為字元串
select * from emp where to_char(hiredate,’yyyy-mm-dd’)>’1981-10-20′
第三種：
select * from emp where hiredate>to_date(‘1981-10-20′,’yyyy/mm/dd’)
第二節課：其他函數：
單行函數：lower
多行函數:min max sum avg count
轉換函數:to_number to_char to_date
—————————————————————————————–
其他函數：
1、nvl():nvl(欄位名，執行)–相當於java中的if條件判斷
2、nvl2():nvl2(欄位名，值，值)–相當於java中的If(){}else{}判斷
3、decode():decode(欄位名，條件1，執行內容1，條件2，執行內容2，條件3，執行內容3，默認執行內容)
相當於java中if(){}else if(){}else if(){}…else{}
查詢所有員工的月薪及姓名和工作
select * from emp
select ename,sal 基本工資,comm 績效,sal+comm 月薪 from emp
select ename,sal 基本工資,comm 績效,sal+nvl(comm,0)月薪 from emp
查詢所有員工的月薪及姓名和工作
select ename,sal 基本工資,comm 績效,nvl2(comm,sal+comm,sal)月薪 from emp
顯示員工的職稱
select ename,job,decode(job,’MANAGER’,’經理’,’SALESMAN’,’銷售人員’,’普通員工’) from emp
第三節課：使用group by分組
在多行函數中不能直接使用普通欄位，除非group by
在多行函數中不能直接使用單行函數，除非group by
group by學習：
—1、使用group by進行數據分組 select 多行函數，分組欄位 from 表名 group by 分組欄位
—2、多欄位進行分組的時候，按照欄位順序進行分組，第一條件分組完成後，繼續使用其他條件依次分組。
—3、group by依然可以和order by 聯合使用
—4、可以和單行函數聯合進行分組，注意使用了單行函數那麼在查詢語句中必須也要使用
查詢最高工資和員工數
select max(sal),count(*) from emp
查詢不同部門的最高工資
select * from emp order by deptno
select deptno,max(sal) from emp group by deptno–使用group進行分組查詢，分組的欄位可以出現在查詢中，其他欄位依然不可以
查詢不同工作崗位的員工數
select * from emp for update
select lower(job),count(*) from emp group by lower(job)–使用單行函數進行分組
查詢不同部門的不同工作崗位的人數
select deptno,job ,count(*) from emp group by deptno,job–使用多欄位組合進行分組
select deptno,job ,count(*) from emp group by deptno,job order by deptno
查詢不同部門的不同工作崗位的並且人數大於1的資訊t count(*) from emp where count(*)>3 group by deptno
selec
select deptno,job ,count(*) from emp where count(*)>1 group by deptno,job order by deptno
查詢部門號大於10的不同部門的不同工作崗位的人數
select deptno,job ,count(*) from emp where deptno>10 group by deptno,job order by deptno
使用having進行分組後篩選
having學習：
–1、使用group by分組後在進行數據篩選的時候，where中不能出現多行函數，所以使用新的關鍵字having進行條件篩選
–2、where條件篩選的執行順序：from–>where—>group –>select
–3、having條件篩選的執行順序：from–>group by –>having–>select
–4、where的執行效率比having要高，能使用where的情況下盡量不要使用having
查詢不同部門的不同工作崗位的並且人數大於1的資訊
使用where語句進行篩選
where條件語句sql執行順序：from–>where—>group –>select
select count(*) from emp where count(*)>1 group by deptno，job
使用having語句進行篩選
having條件語句的執行順序:from–>group by –>having–>select
select deptno, count(*) from emp group by deptno having count(*)>5
select deptno,job ,count(*) from emp group by deptno,job having deptno>10 order by deptno
第四節課：插入數據學習及數據的備份
單表查詢語句（select）
1、插入數據(insert)
1、語法規範 insert into 表名(欄位1，欄位2，欄位3，….)values(‘值1′,’值2’,’值3’…..)
2、主鍵：用來唯一標識一條數據的欄位通常設置主鍵，主鍵是唯一不可以重複的
3、如果插入的數據是全欄位數據，欄位可以省略不寫。部分欄位，必須加上欄位說明和欄位值，但是主鍵不能為空
4、事務的提交：如果一個事件是由多個動作組成，只要有一個動作沒有執行成功則自動將數據回滾到原始狀態，此們技術稱之為事務
保證數據的安全和完整
事物的提交：
使用第三放插件的提交按鈕
使用commit語句
5、增加刪除修改的數據sql語句執行完畢後，不會立馬進入數據的寫入
需要手動的對數據進行提交，如果數據有問題還可以回滾
select * from dept for update
在北京新建了一個名為LOL學院的新部門，請插入
insert into dept(deptno,dname,loc)values(’50’,’lol學院’,’北京’);
主鍵是唯一不可以重複的
insert into dept(deptno,dname,loc)values(’50’,’教學部’,’北京’);
如果插入的數據是全欄位數據，欄位可以省略不寫。部分欄位，必須加上欄位說明和欄位值，但是主鍵不能為空
insert into dept values(’80’,’教學部’,’北京’);
insert into dept values(’90’,’教學部’,’北京’);
insert into dept values(100′,’教學部’,’北京’);
insert into dept values(‘110′,’教學部’,’北京’);
2、創建數據的備份
（1）create table 表名 as 查詢語句，創建的是和查詢結果一樣的表，查詢結果是什麼就會備份一個相同的表
（2）insert into 表名 查詢語句，注意：查詢出來的結果在結構上必須和插入數據的表相同，欄位個數必須相同
（3）注意：備份表只有欄位和數據相同，並不會備份約束。
1、備份完整的數據和表
select * from dept;
create table tdept as select * from dept;–備份dept表和數據，只能備份數據和欄位
select * from tdept
2、備份完整表
create table tdept1 as select * from dept where 1>2–備份表，不備份數據。
3、備份部分數據和表.create table 表名 as 查詢語句，創建的是和查詢結果一樣的表，查詢結果是什麼就會備份一個相同的表
create table tdept2 as select dname,loc from dept
select *from tdept2
4、給備份表添加數據 insert into 表名 查詢語句，注意：查詢出來的結果在結構上必須和插入數據的表相同，欄位個數必須相同
select * from tdept1
insert into tdept1 select dname,loc from dept where deptno>40
select *from tdept2
insert into tdept2 select dname,loc from dept where deptno>40
第五節課：數據的更新和刪除：
更新數據:update
語法結構：update 表名 set 欄位名1=’欄位值1′,欄位名2=’欄位值2’，…where 條件
將部門70的名字改為教學部2
update dept set dname=’教學部2′ where deptno=’70’
update dept set dname=’教學部2′,loc=’上海’ where deptno=’70’
select * from dept
刪除數據：delete
1)刪除語句：delete 表名 where 條件
–刪除部門標號為70的數據
delete dept where deptno=’70’—刪除指定數據
select * from dept
delete tdept—清空表數據
truncate table tdept—清空表數據建議使用truncate關鍵字，但是此關鍵字不能回滾數據
===================================================================================================================================
第四天：
第一節課：sql92學習
-查詢員工姓名，工作，薪資，部門名稱
sql的聯合查詢（多表查詢）
–1、sql92標準
—-笛卡爾積：一件事情的完成需要很多步驟，而不同的步驟有很多種方式，完成這件事情的所有方式稱之為笛卡爾積
select * from emp–14
select * from dept–7
select * from emp,dept order by ename–7*14=98
等值鏈接，鏈接條件。等值鏈接的時候欄位的名字可以不相同，但是欄位的值要相同。
–查詢員工姓名，工作，薪資，部門名稱
select * from emp,dept where emp.deptno=dept.deptno–使用等值鏈接進行結果篩選
select ename,job,sal,dname from emp,dept where emp.deptno=dept.deptno;–使用等值鏈接查詢指定欄位數據
select ename,job,sal,emp.deptno,dname from emp,dept where emp.deptno=dept.deptno;–多表查詢的時候，查看相同欄位的值，必須聲明所在表
select emp.ename,emp.job,emp.sal,emp.deptno,dept.dname from emp,dept where emp.deptno=dept.deptno;–在查詢指定欄位值的時候，加上表名提高查詢效率
select e.ename,e.job,e.sal,e.deptno,d.dname from emp e,dept d where e.deptno=d.deptno;–多表查詢中使用別名進行表的區分
select e.ename,e.job,e.sal,e.deptno,d.dname from emp e,dept d where e.deptno=d.deptno order by deptno;–還可以使用order by 排序
非等值鏈接
—查詢員工姓名，工作，工資，工資等級
select * from salgrade
select * from emp,salgrade order by ename
select * from emp,salgrade where sal>losal and sal<hisal
select e.ename,e.job,e.sal,s.grade from emp e,salgrade s where e.sal>s.losal and e.sal<s.hisal;
自連接:使用頻率不是很高，因為自連接的條件要求不同資訊共存在一張里，其實就兩張相同的表的等值鏈接。
–查詢員工姓名，工作，薪資，及上級領導姓名
select * from emp for update
create table temp as select * from emp
select * from emp e,temp t where e.mgr=t.empno
外連接
select * from emp,dept
–查詢員工姓名，工作，薪資以及部門名稱並且查詢沒有部門的員工資訊
select * from emp e,dept d where e.deptno=d.deptno(+)
–查詢員工姓名，工作，薪資以及部門名稱並且查詢沒有員工的部門資訊
select * from emp e,dept d where e.deptno(+)=d.deptno
第二節課：sql99
sql99學習：
交叉鏈接(沒啥用)
select * from emp cross join dept
自然鏈接:natural join
(1)、 自然鏈接會自動使用多表中所有相同欄位（不但值相同，名字也要相同）進行篩選
前提：多表一定要有同名同值的欄位
注意：自然連接會自動使用所有的相同欄位進行結果篩選
（2）、使用using關鍵字可以指定欄位進行鏈接查詢，但是必須是同名欄位 inner join
（3）、使用on關鍵字可以直接在其後書寫鏈接條件，沒有限制 inner join
查詢員工及員工所在的部門資訊
select * from dept natural join emp
select * from dept d,emp e where d.deptno=e.deptno
問題1：假如在鏈接查詢的時候不想使用所有相同欄位進行篩選怎麼辦？
—-使用using關鍵字
—查詢員工及員工所在的部門資訊
select * from dept join emp using(deptno)
問題2：假如在鏈接查詢中沒有同名欄位，但是有同值欄位怎麼篩選？
使用on關鍵字
查詢員工及員工所在的部門資訊
select * from dept inner join emp on dept.deptno=emp.deptno
查詢部門及部門的城市資訊
select * from dept inner join city on dept.loc=city.cid
外鏈接
（1）、左外鏈接 left outer join
(2)、右外鏈接 right outer join
(3)、全外鏈接 full outer join
左外鏈接
查詢沒有部門的員工資訊及員工和部門資訊
select * from emp e,dept d where d.deptno(+)=e.deptno–sql92
select * —sql99
from emp e
left join dept
using(deptno)
–右外鏈接
select * from emp e,dept d where d.deptno=e.deptno(+)–sql92
select * —sql99
from emp e
right outer join dept d
on e.deptno=d.deptno
–全外鏈接
select *
from emp e
full outer join dept d
on e.deptno=d.deptno
第三節課：自連接及三表聯合查詢
查詢員工姓名，職位，薪資，上級領導姓名
select e.ename,e.job,e.sal,m.ename from emp e,emp m where e.mgr=m.empno–sql92
sql99實現：
select e.ename,e.job,e.sal,m.ename
from emp e
inner join emp m
on e.mgr=m.empno
———————————————————————–
sql99三表聯合查詢
查詢員工姓名，工作，薪資，部門名稱，城市名稱
sql92實現：
select e.ename,e.job,e.sal,d.dname,c.cname
from emp e,dept d,city c
where e.deptno=d.deptno and d.loc=c.cid
sql99實現：
select e.ename,e.job,e.sal,d.dname,c.cname
from emp e
inner join dept d
on e.deptno=d.deptno
inner join city c
on d.loc=c.cid
查詢工資高於2000的員工姓名，工作，薪資，部門名稱，城市名稱
sql92實現：
select e.ename,e.job,e.sal,d.dname,c.cname
from emp e,dept d,city c
where e.deptno=d.deptno and d.loc=c.cid and sal>2000
sql99實現：
select e.ename,e.job,e.sal,d.dname,c.cname –查詢內容
from emp e –查詢表
inner join dept d –鏈接表
on e.deptno=d.deptno –鏈接條件
inner join city c –鏈接表
on d.loc=c.cid –鏈接條件
where e.sal>2000 –篩選條件
第四節課：單行子查詢
子查詢（單行子查詢、多行子查詢）
單行子查詢
select 內容 from 表名 where 子查詢語句
1、什麼時候使用單行子查詢？
–在不能直接獲取有效資訊的時候，考慮使用子查詢
2、單行子查詢的注意事項？
—子查詢的結果必須只有一個值
—可以直接使用算術鏈接符
—子查詢出現在where中,一般出現在條件語句的右邊
問題：查詢所有比僱員「CLARK」工資高的員工資訊
解讀1：查詢僱員CLARK的工資
select sal from emp where ename=’CLARK’
解讀2：查詢工資大於2450的員工資訊
select * from emp where sal>’2450′
使用子查詢
select * from emp where sal>(select sal from emp where ename=’CLARK’)
select * from emp where (select sal from emp where ename=’CLARK’)<sal
查詢工資高於平均工資的員工的名字和工資
select * from emp where sal>(select avg(sal) from emp) order by sal
查詢和soctt屬於同一部門且工資比他低的員工資料
select * from emp where deptno=(select deptno from emp where ename=’SCOTT’) and sal<(select sal from emp where ename=’SCOTT’)
查詢工資最高的員工資料
select * from emp where sal=(select max(sal) from emp)
查詢職務和scott相同，僱傭時間早的員工資訊
select * from emp where job=(select job from emp where ename=’SCOTT’) and hiredate<(select hiredate from emp where ename=’SCOTT’)
查詢工資比scott高或者僱傭時間早的員工編號和名字
select empno,ename from emp where sal>(select sal from emp where ename=’SCOTT’) or hiredate<(select hiredate from emp where ename=’SCOTT’)
查詢工資高於任意一個CLERK的所有員工資訊
select * from emp where sal>(select min(sal) from emp where job=’CLERK’) and job<>’CLERK’
———————————————————————————————-
第五節課： 多行子查詢
多行子查詢學習：其實就使用使用關鍵字：any all in
注意：子查詢返回多個值建議使用多行子查詢，返回的單個值使用單行子查詢
select * from emp where sal> any (select sal from emp where job=’CLERK’) and job<>’CLERK’
查詢工資高於所有SALESMAN的員工資訊
select * from emp where sal>(select max(sal) from emp where job=’SALESMAN’)–單行子查詢的寫法
select * from emp where sal>all (select sal from emp where job=’SALESMAN’;
查詢部門20中同部門10的僱員工作一樣的僱員資訊
select * from emp where deptno=’20’ and (job=’MANAGER’ or job=’PRESIDENT’ or job=’CLERK’)
select * from emp where deptno=’20’ and job in (‘MANAGER’, ‘PRESIDENT’ , ‘CLERK’)
select * from emp where deptno=’20’ and job in (select job from emp where deptno=’10’)
select * from emp where deptno=’20’ and job= any (select job from emp where deptno=’10’)
select job from emp where deptno=’10’
第六節課：用戶創建
創建用戶
當前用戶介紹：許可權級別的分配
——-system：系統賬戶
——-sys：超級管理員
——-scott：普通用戶
1、 創建自定義用戶: create user 用戶名 identified by 密碼；
普通用戶不具備創建用戶的許可權，需要使用system賬戶進行創建
直接創建好的用戶不能登錄，還需要使用system進行許可權的分配（角色：一系列許可權的集合）
create user zyp identified by 123456;
給創建的用戶賦予角色。 grant 角色名，角色名…to 用戶名；
grant connect to zyp;–賦予鏈接庫許可權
grant resource to zyp;–賦予操作資源許可權
grant dba to zyp;–賦予DBA角色
grant dba to zyp
刪除用戶許可權
revoke dba from zyp
revoke connect from zyp
2、 刪除用戶
drop user zyp
========================================================================================================================================
第五天：
第一節課：創建表和欄位講解
–創建表學習
1、 創建表的基本語句：create table 表名（欄位名 類型，欄位名 類型，欄位名 類型….）
創建學生表：學號，姓名，性別，年齡，qq號，郵箱。
create table student(
snum number(10),—指定學號的數字長度為10
sname varchar2(100),—指定姓名的存儲上限為100個位元組
sex char(4),
age number,
qq number,
sal number(6,2),–指定工資的整數位為6和小數位為2
mail varchar2(50)
)
2、欄位類型
number類型：數字類型，用來聲明存儲數字的欄位。 number(指定數字的整數長度，指定數字的小數位長度)
varchar2類型：字元類型，用來聲明存儲字元的欄位。會根據存儲的數據自動增長大小，varchar2(存儲上限)
char類型：字元類型，用來聲明存儲字元的字元。會開闢指定大小的記憶體來存儲數據。
區別1：char的存儲效率高於varchar2
區別2：char是開闢指定大小的記憶體空間，varchar2是根據數據大小來開闢空間的大小
date類型：存儲日期類型
第二節課：修改表學習：
修改表
在命令窗口查看錶結構 desc 表名
添加新欄位 alter table 表名 add 欄位名 類型
alter table student add phone number(11)
修改欄位類型 alter table 表名 modify 欄位名 類型
alter table student modify sname varchar2(200)
刪除欄位 alter table 表名 drop column 欄位名
alter table student drop column phone
修改表名 rename 表名 to 新的表名
rename student to stu
rename stu to student
刪除表 drop table 表名
drop table student
第三節課：約束學習(根據情況切分成兩節課)
select * from student for update
添加數據
問題1:學號重複了，數據還可以插入成功
insert into student(snum,sna,sex,age,qq,sal,mail)values(‘123′,’張三’,’男’,18,12345678,88.88,’[email protected]’)
insert into student values(123,’李四’,’男’,18,7890122,88.99,’[email protected]’)
問題2:姓名可以為空。
insert into student values(456,’柳岩’,’女’,18,666999,99.66,’[email protected]’)
insert into student (snum,sex,age,qq,sal,mail) values(789,’女’,18,888444,99.66,’[email protected]’)
問題3：性別不但可以為空，還可以為其他不知道的字元
insert into student (snum,sna,age,qq,sal,mail) values(108,’景甜’,18,000999,99.66,’[email protected]’)
insert into student (snum,sna,sex,age,qq,sal,mail) values(102,’景甜’,’a’,18,000999,99.66,’[email protected]’)
insert into student (snum,sna,sex,age,qq,sal,mail) values(102,’景甜’,’女’,18,000999,99.66,’[email protected]’)
問題4：年齡可以超過200
insert into student (snum,sna,sex,age,qq,sal,mail) values(103,’唐嫣’,’女’,23,78900,99.66,’[email protected]’)
insert into student (snum,sna,sex,age,qq,sal,mail) values(103,’唐嫣’,’女’,230,78900,99.66,’[email protected]’)
問題5：qq號一致
insert into student (snum,sna,sex,age,qq,sal,mail) values(104,’關曉彤’,’女’,19,111000,99.66,’[email protected]’)
insert into student (snum,sna,sex,age,qq,sal,mail) values(105,’袁華’,’男’,22,111000,99.66,’[email protected]’)
——————————————————————————————————–
約束學習：
create table student(
snum number(10),–primary key,–使用主鍵約束
sna varchar2(100), –not null,
sex char(4), –default ‘男’ check(sex=’男’ or sex=’女’) not null,
age number check(age>0 and age<120),
qq number, –unique,
sal number(6,2),
mail varchar2(50)
— constraints pk_student_snum primary key(snum)
— constraints ck_student_sna check(sna is not null)
— constraints ck_student_sex check(sex =’男’ or sex=’女’)
–constraints uk_student_qq unique(qq)
)
alter table student add constraints pk_student_snum primary key(snum);–在創建表後添加主鍵
alter table student drop constraints pk_student_snum–刪除主鍵
alter table student modify sna varchar2(100) not null;–添加非空約束
alter table student modify sna varchar2(100) null;–修改欄位為空
alter table student add constraints ck_student_sex check(sex=’男’ or sex=’女’)–添加檢查約束
alter table student drop constraints ck_student_sex –刪除檢查約束
alter table student add constraints uk_student_qq unique(qq)
alter table student drop constraints uk_student_qq
drop table student
問題1:學號重複了，數據還可以插入成功
使用主鍵約束：學號是唯一標識一條數據的，所以必須唯一且不能為空
—（1）、在確定為主鍵的欄位後添加 primary key關鍵字
—(2)、在創建表的後面使用：constraints pk_表名_欄位名 primary key(欄位名)
–（3）、在創建表後使用 alter table 表名 add constraints pk_表名_欄位名 primary key(欄位名);
–刪除主鍵：alter table 表名 drop constraints pk_表名_欄位名
問題2:姓名可以為空。
使用非空約束
—（1）、創建表的時候在欄位後面添加not null
—(2)、在創建表欄位後使用 constraints ck_表名_欄位名 check(欄位名 is not null) 了解
–（3)、在創建表後使用alter table 表名 modify 欄位名 類型 not null;
—(4)、修改欄位可以存儲空值：alter table 表名 modify 欄位名 類型 null;
問題3：性別不但可以為空，還可以為其他不知道的字元
使用檢查約束
—(1)、創建表的時候在欄位後使用 default 值 check(條件)，
———但是會允許空值的出現，並且默認值只有在欄位不聲明的情況下生效
—（2）、在創建表所有欄位後使用：constraints ck_表名_欄位名 check(條件)
—(3)、在創建表後使用：alter table 表名 add constraints ck_表名_欄位名 check(條件)
問題4：年齡可以超過200
–使用檢查約束條件
問題5：qq號一致
使用唯一約束
–（1）、在欄位後直接使用unique關鍵字
–（2）、在所有欄位後使用：constraints uk_表名_欄位名 unique(欄位名)
–（3）、 alter table 表名 add constraints uk_表名_欄位名 unique(欄位名)
–刪除唯一約束：alter table 表名 drop constraints uk_表名_欄位名
第四節課：外鍵約束學習：
創建學生表
create table stu(
snum number(10) primary key,
sname varchar2(100) not null,
sex char(4) check(sex=’男’ or sex=’女’),
cinfo number(10)– references clazz(cid)–外鍵
— constraints fk_stu_cinfo foreign key(cinfo) references clazz(cid)
)
alter table stu add constraints fk_stu_cinfo foreign key(cinfo) references clazz(cid) on delete cascade–添加外鍵
alter table stu add constraints fk_stu_cinfo foreign key(cinfo) references clazz(cid) on delete set null–添加外鍵
alter table stu drop constraints fk_stu_cinfo –刪除外鍵
drop table stu
創建班級表
create table clazz(
cid number(10) primary key,
cname varchar2(100) not null,
cdesc varchar2(500)
)
創建班級表數據
insert into clazz values(1,’軟體01班’,’最厲害的班級’);
insert into clazz values(2,’軟體02班’,’掛科最多的班級’);
insert into clazz values(3,’508班’,’大牛和最帥的班級’);
select * from clazz
delete from clazz where cid=1
創建學生數據
insert into stu values(1002,’張三’,’男’,1);
insert into stu values(1003,’李四’,’女’,2);
insert into stu values(1004,’王五’,’男’,3);
insert into stu values(1005,’趙四’,’男’,4);
select * from stu
問題1：在插入學生資訊的時候，出現了不存在的班級資訊？
使用外鍵約束
–（1）、在欄位後使用 references 參照表表名(參照欄位)
–（2）、在所有欄位後使用 constraints fk_表名_欄位名 foreign key(欄位名) references 參照表名(參照欄位名)
–（3）、在創建表後使用alter table 表名 add constraints fk_表名_欄位名 foreign key(欄位名) references 參照表名(參照欄位名)
–刪除外鍵 alter table 表名 drop constraints fk_表名_欄位名
select table_name from all_all_tables where owner=’SCOTT’
問題2：在刪除父表數據的時候需要先刪除子表數據？
解決方式1：先解除主外鍵關聯，然後刪除數據，再然後添加主外鍵關聯
解決方式2：在創建外鍵的時候使用級聯操作。
–在創建外鍵時 on delete cascade
–在創建外鍵時 on delete set null
怎麼選取外鍵？
一般將主表的主鍵作為子表的外鍵
外鍵的值能為 not null? 不建議在外鍵後使用非空約束
第五節課： 序列
–序列
select cid from clazz order by cid
insert into clazz values(4,’402班級’,’老師最污的班’);
insert into clazz values(10,’402班級’,’老師最污的班’);
–問題1：如果數據量比較大，插入新的數據的時候id怎麼選取才能避免重複？
使用序列
1、創建序列 create sequence 序列名
特點：默認沒有初始值的，nextval的值默認從1開始，默認每次自增+1
特點：可以使用 序列名.nextval坐為主鍵使用
create sequence cnum;
select cnum.currval from dual—獲取當前序列值
select cnum.nextval from dual—序列自增後返回當前值
truncate table clazz
select * from clazz
insert into clazz values(cnum.nextval,’402班級’,’老師最污的班’);–將序列作為主鍵
問題2：如果當前表中已有很多數據，但是接下來需要使用序列進行主鍵自增？
創建序列時指定序列初始值及步長
create sequence cc
start with 90—設定序列初始值
increment by 5—設定步長
insert into clazz values(cc.nextval,’402班級’,’老師最污的班’);
select max(cid) from clazz
—
select cc.nextval from dual
select cc.currval from dual
select * from clazz
刪除序列
drop sequence cc
—————-完整的序列格式———————————–
create sequence aa–序列名
start with 20 –設置序列初始值
increment by 10 –設置步長
maxvalue 10000—設置最大值
cache 10 —–設置快取
第六節課：索引
索引學習
select * from emp order by sal
select * from emp where sal=3000
問題：當表中數據量比較大的時候，使用條件查詢就會出現效率的問題
使用索引進行解決
1、原理：類似於java中數組的折半查找，在資料庫中數據使用B樹形結構進行數據的存儲，
–這樣可以對外提供快速的數據查找方式。
2、創建索引 create index 表名_index_欄位名 on 表名(欄位名)
特點：顯示的創建，隱式的執行.在數據中會給主鍵默認創建索引。
create index emp_index_sal on emp(sal)–創建查詢索引
select * from emp where sal=3000
select * from emp order by sal
create index emp_index_sal_desc on emp(sal desc);–創建單排序索引
select * from emp order by sal,job
create index emp_index_sal_desc2 on emp(sal desc,job asc);–創建多條件排序索引
3、刪除索引 drop index 索引名
drop index emp_index_sal_desc
=====================================================================================================================================
文章首發公眾號：Java架構師聯盟，每日更新技術好文
