數(shù)據(jù)庫基本操作1229

1、數(shù)據(jù)庫：數(shù)據(jù)倉庫小型數(shù)據(jù)庫：Access FoxBase中型數(shù)據(jù)庫：MySQL SQLServer中大型：Sybase Oracle DB2都支持標準的SQL語言O(shè)racle數(shù)據(jù)庫基本組成Oracle服務(wù)網(wǎng)絡(luò)服務(wù)，監(jiān)聽服務(wù),數(shù)據(jù)庫服務(wù).Oracle實例 配置數(shù)據(jù)庫實例的DataBase Configuration AssistantOracle客戶端SQL Plus 客戶端如果要連上數(shù)據(jù)庫，必須要有兩個服務(wù)：數(shù)據(jù)庫實例服務(wù)數(shù)據(jù)庫監(jiān)聽服務(wù)Oracle數(shù)據(jù)庫默認管理員用戶SysSystemsysdba數(shù)據(jù)的操作一般不會用管理員用戶，而是會用普通用戶默認的普通用戶 scott/tiger 被鎖定給用

2、戶解鎖需要管理員權(quán)限：alter user 用戶名 account unlock;將用戶鎖定需要管理員權(quán)限alter user 用戶名 account lock;切換用戶conn 用戶名;查看當前用戶show user;斷開當前用戶disc退出客戶端exit修改其他用戶密碼：alter user 用戶名 identified by 新密碼;修改自己的密碼password創(chuàng)建新用戶create user 用戶名 identified by 密碼;新用戶沒有任何權(quán)限給用戶一個連接數(shù)據(jù)庫的權(quán)限grant connect to 用戶名;刪除用戶drop user 用戶名;對于數(shù)據(jù)庫的操作，使用SQL語

3、言SQL：結(jié)構(gòu)化查詢語言DDL:數(shù)據(jù)定義語言Create 創(chuàng)建Alter修改Drop刪除DML:數(shù)據(jù)操作語言Insert into 插入數(shù)據(jù)Update 修改數(shù)據(jù)Delete 刪除數(shù)據(jù)Select 查詢數(shù)據(jù)DCL:數(shù)據(jù)控制語言Grant 授權(quán)Revoke 撤銷授權(quán)TCL:事物控制語言創(chuàng)建用戶:create user wangerxiao identified by 1234;給用戶授權(quán)：Grant connect to wangerxiao;/允許連接數(shù)據(jù)庫Grant resource to wangerxiao;/允許使用表空間切換到新用戶Conn wangerxiao/1234;在默認表空

4、間中創(chuàng)建一張表用來保存學生信息：學號，姓名,性別,年齡,成績Studentnum,name,sex,age,scoreCreate table student(Num number(10),Name varchar2(30),Sex char(3),Age number(3),Score number(5,2);查看表結(jié)構(gòu)：Desc student;修改表結(jié)構(gòu)1. 將num字段的類型改為number(20),并且不允許為nullAlter table student modify num number(20) not null;Alter table student modify name v

5、archar2(20) not null;2. 添加一個新字段tel Alter table student add (tel number(11);3. 刪除tel字段Alter table student drop column tel;4. 刪除整張表Drop table student;oracle中的數(shù)據(jù)類型用來定義字段的字符char :定長的 最多2000字節(jié)Varchar2：可變長度最多4000字節(jié)Long：可變長度 最多2GB數(shù)字Number(a,b)Number(a) 表示沒有小數(shù)部分a表示是幾位數(shù)包括小數(shù)位b表示小數(shù)有幾位日期Date:精確到秒Timestamp：精確到0.

6、000001秒二進制類型Raw：存儲2000字節(jié)Long Raw：存儲2GBLOB:大對象，以二進制的保存，最大可以4GBCLOB :大字符類型BLOB：大字節(jié)類型BFILE：大字節(jié)文件1. 保存一條數(shù)據(jù)到student表中Insert into student(num,name,sex,age,score) values (123,張三豐,男,100,90.5);2. 查詢student表中的數(shù)據(jù)Select num,name,sex,age,score from student;3. 查詢所有字段的值Select * from student;4. 帶偽列的查詢 Select rownum

7、,rowid,num,name,sex,age,score from student;5. 修改第二條數(shù)據(jù)的學號和姓名Update student set num=111,name=張無忌 where sex=女;6. 刪除姓名是張無忌的數(shù)據(jù) Delete from student where name=張無忌;任務(wù)：1. 使用system用戶登錄2. 創(chuàng)建一個新用戶 zhangsanfeng3. 給新用戶授權(quán)可以連接數(shù)據(jù)庫，操作表空間4. 切換到新用戶zhangsanfeng5. 創(chuàng)建一張新表 WLMJ秘籍編號(num),秘籍名稱(name),等級(level),殺傷力(power)6. 往

8、表中至少插入5條數(shù)據(jù)7. 練習使用DML語句事物控制語言Savepoint 設(shè)置回滾點 事物中間的一個臨時標記點 可以讓事物回滾到指定位置設(shè)置回滾點 savepoint a;Rollback 回滾Rollback to a; /回滾到指定位置Rollback;/回滾到初始狀態(tài)Commit 提交事物：事物一旦被提交，就意味著不可以回滾哪些時候事物會被提交1. 當執(zhí)行commit語句的時候會提交事物2. 當正常退出客戶端的時候會自動提交事物示例用戶：scottDeptbak 部門表Deptno：部門編號Dname部門名稱Loc部門位置 Empbak 員工表Empno:員工編號Ename員工姓名MG

9、R 上司編號Job崗位Hiradate入職日期Sal工資Comm獎金Deptno 所屬部門編號復制表(帶數(shù)據(jù))Create table empbak as select * from emp ;復制表(不帶數(shù)據(jù))Create table empbak as select * from emp where 1=2;設(shè)計一個事物1. 給7839員工降低1000塊工資Update empbak set sal=sal-1000 where empno=7839; 2. 設(shè)置保存點Savepoint a;3. 給7369員工加1000塊工資Update empbak set sal=sal+1000

10、where empno=7369;4. 設(shè)置保存點Savepoint b;5. Update empbak set sal=sal+1000 where empno=7934;6. 回滾到bRollback to b;7. 回滾到aRollback to a;8. 回滾到初始狀態(tài)Rollback;查詢語句Select * from empbak;Select empno,ename,job,sal from empbak;盡量只查詢需要查詢的字段別名查詢Select empno as 編號,ename as 姓名, sal as 工資 from empbak;查詢所有人的姓名和年薪帶運算符的查

11、詢Select ename 姓名,sal*12 年薪 from empbak;查詢年薪超過3萬的人帶條件的查詢Select ename 姓名,sal*12 年薪 from empbak where sal*12>30000;-從結(jié)果集中去掉重復的數(shù)據(jù) distinct-查詢empbak表中有多少個工作崗位select distinct job from empbak;-查詢工資大于2000，并且小于3000的員工姓名，崗位和工資Select ename,job,sal from empbak where sal>=2000 and sal<=3000;Select ename

12、,job,sal from empbak where sal between 2000 and 3000;-查詢工資小于2000，或者大于3000的員工姓名，崗位和工資Select ename,job,sal from empbak where sal<2000 or sal>3000;-查詢工作崗位是CLERK 和SALESMAN的所有員工姓名和工資Select ename,sal from empbak where job=CLERK or job=SALESMAN;Select ename,sal from empbak where job in(CLERK ,SALESMA

13、N);-查詢工作崗位除了CLERK 和SALESMAN之外的所有員工姓名和工資Select ename,sal from empbak where job!=CLERK and job!=SALESMAN;Select ename,sal from empbak where job<>CLERK and job<>SALESMAN;Select ename,sal from empbak where job not in('CLERK', 'SALESMAN');-job大于任意一個值Select ename ,sal from empb

14、ak where job > any(2000,3000,4000);-job大于所有值Select ename ,sal from empbak where job > all(2000,3000,4000);-查詢所有有獎金的人的姓名和獎金Select ename,comm from empbak where comm is not null;-模糊查詢% 0個或者多個任意字符_ 1個任意字符-查詢所有名字中有S的人的姓名和工資Select ename,sal from empbak where ename like %S%;-查詢名字以S開頭的人的姓名和工資 Select e

15、name,sal from empbak where ename like 'S%'-查詢名字中第二個字符是A的人的姓名和工資Select ename,sal from empbak where ename like '_A%'-查詢名字只有4個字母的人的姓名和工資Select ename,sal from empbak where ename like '_'排序語句 order by查詢所有人的姓名和工資，按照工資從少到多排序Select ename,sal from empbak order by sal asc;查詢所有人的姓名和工資，按

16、照工資從多到少排序Select ename,sal from empbak order by sal desc;-對部門10的員工按照工資從多到少排序Select ename,sal from empbak where deptno=10 order by sal desc; 根據(jù)多個字段排序優(yōu)先按照前面的字段排序，在前面字段值一樣的情況下，再按照后面的字段排序Select ename,sal,deptno from empbak order by deptno asc,sal desc;*1224*Oracle中的運算符算術(shù)運算符 + - * / 比較運算符 =等于 != <>不

17、等于 > >=大于 大于等于 < <= 小雨 小于等于邏輯運算符And 類似于Java中的&& ,并且Or類似于Java中的 |, 或者Between and 在某一個范圍內(nèi)In在多個值之間取任意一個值Not in 不能取in中的值A(chǔ)ny 任意一個值A(chǔ)ll 所有的值Like 模糊匹配 % 通配0個以上字符_ 通配1個字符連接運算符：用來連接兩個SQL語句的union 用來合并兩個結(jié)果集，如果有重復的數(shù)據(jù)，就去掉查詢員工部門編號大于10的所有員工信息Select * from empbak where deptno>10;查詢員工部門編號為20的所有

18、員工信息Select * from empbak where deptno=20;Select * from empbak where deptno>20 union Select * from empbak where deptno=20;union all 用來合并兩個結(jié)果集，并保留重復的數(shù)據(jù)Select * from empbak where deptno>10 union all Select * from empbak where deptno=20;intersect 取兩個結(jié)果集的交集保留兩個結(jié)果集中相同的數(shù)據(jù)Select * from empbak where de

19、ptno>10 intersect Select * from empbak where deptno=20;Minus 取兩個結(jié)果集的差集去掉兩個結(jié)果集相同的數(shù)據(jù)Select * from empbak where deptno>10 minus Select * from empbak where deptno=20;Oracle函數(shù)單行函數(shù)：對每一條數(shù)據(jù)單獨進行處理的函數(shù) 不會改變結(jié)果集中的數(shù)據(jù)條數(shù)對null值進行處理的函數(shù)NVL函數(shù) nvl(字段，初始值) 如果字段的值為空，則用指定值表示計算員工的月收入 =月工資+獎金Select ename,sal,nvl(comm,0

20、),sal+nvl(comm,0) as 收入 from empbak;NVL2 函數(shù) nvl2(字段,值1，值2) 如果字段值不為空，就執(zhí)行值1，如果為空，就執(zhí)行值2如果有獎金，就在原獎金的基礎(chǔ)上+200，如果沒有獎金，就發(fā)100的獎金Select ename,comm,nvl2(comm,comm+200,100) as 應(yīng)發(fā)獎金 from empbak;NULLIF 函數(shù) nullif(字段,值) 如果 字段=值，則將字段值改為null如果獎金是300，就變?yōu)閚ullSelect ename,comm,nullif(comm,300) from empbak;日期函數(shù)Select tru

21、nc(Sysdate) from dual;-獲得當前日期Select add_months(sysdate,3) from dual;-在當前日期上增加相應(yīng)的月份數(shù)months_between(date1,date2) -判斷兩個時間之間的月份差獲得在這個公司工作時間超過30年的員工姓名和工作年限Select ename,months_between(sysdate,hiredate) from empbak where months_between(sysdate,hiredate) >30*12;字符函數(shù)-查詢empbak表中員工的姓名，全部用小寫表示Select Initcap(

22、ename) from empbak;upper('') 將小寫轉(zhuǎn)成大寫Lower() 將大寫轉(zhuǎn)成小寫Initcap() 將首字母大寫Ltrim('abc123','abc') 如果參數(shù)二中任意一個字符出現(xiàn)在參數(shù)一的左邊，就去掉Rtrim('abc123','123')如果參數(shù)二中任意一個字符出現(xiàn)在參數(shù)一的右邊，就去掉Translate('abca','a','b');如果參數(shù)二在參數(shù)一中出現(xiàn)，就用參數(shù)三代替replace('','',

23、'')如果參數(shù)二在參數(shù)一中出現(xiàn)，就用參數(shù)三代替instr('abcd','c') 獲得參數(shù)二在參數(shù)一種出現(xiàn)的位置substr('abcdef',3,2);從第3個字符開始，截取2個字符Concat('abc','def') 連接兩個字符串CHR(97) 獲得數(shù)字對應(yīng)的字符ASCII('A') 獲得字符對應(yīng)ASCII碼Lpad('abc',10,'*')如果abc不夠10位，就在abc的左邊加上*Rpad('abc',10,'*&

24、#39;)如果abc不夠10位，就在abc的右邊加上*length('') 獲得字符串的長度trim(' abcabc ') 去掉參數(shù)一左右兩邊的空格數(shù)字函數(shù)abs() -取絕對值ceil(10.4) -向后取整 結(jié)果為11floor(10.6) -向前取整 結(jié)果為10round(10.55,1)-保留指定的小數(shù)位，四舍五入trunc(10.54,1)-保留指定的小數(shù)位，去掉尾數(shù)Power(a,b) -計算a的b次方Mod(a,b) -計算 a%bSqrt(a) -計算 a的平方根轉(zhuǎn)換函數(shù)To_char() 將日期類型轉(zhuǎn)成字符串類型Select ename,to

25、_char(hiredate,yyyy-MM-dd) from empbak;To_date() 將字符串類型轉(zhuǎn)成日期類型Insert into empbak values(1122,ZHANGSAN,CLERK,7902,to_date(1980-10-20,yyyy-mm-dd),2000,null,30);To_number() -將純數(shù)字字符串轉(zhuǎn)成數(shù)字類型的Select to_num(1234) from dual;* 1225 *分組函數(shù)Count函數(shù) 統(tǒng)計函數(shù)，統(tǒng)計查詢到的數(shù)據(jù)的條數(shù)的統(tǒng)計部門10有多少員工Select count(*) from empbak where dept

26、no=10;Select count(empno) from empbak where deptno=10;SUM函數(shù) 求和函數(shù) 統(tǒng)計部門10的員工每個月的總工資Select sum(sal) from empbak where deptno=10;AVG 函數(shù) 計算平均值計算部門10的員工每個月的平均工資Select avg(sal) from empbak where deptno=10;MAX函數(shù) 求最大值計算部門10的最高工資Select max(sal) from empbak where deptno=10;MIN函數(shù) 求最小值計算部門10的最工低資Select min(sal)

27、from empbak where deptno=10;分組函數(shù)通常和分組子句一起使用分組子句：group by統(tǒng)計每個部門每個月發(fā)出的工資總和Select deptno,sum(sal) from empbak group by deptno;根據(jù)職位求各工種的平均工資Select job,avg(sal) from empbak group by job;按照部門統(tǒng)計每個部門的總?cè)藬?shù)Select deptno,count(empno) from empbak group by deptno;Having 子句 作用類似于where，having是用在分組之后 where用在分組之前按照部門

28、統(tǒng)計每個部門的總?cè)藬?shù)超過3人的部門Select deptno,count(empno) from empbak group by deptno having count(empno)>3;根據(jù)職位求各職位的平均工資,需要取出平均工資超過2000的職位Select job,avg(sal) from empbak group by job having avg(sal)>2000;求出每個經(jīng)理手下員工的平均工資在2500以上的經(jīng)理編號及平均工資,并按照平均工資倒敘排序Select mgr,avg(sal) from empbak where mgr is not null group

29、 by mgr having avg(sal)>2000 order by avg(sal) desc;子查詢：將查詢的結(jié)果作為另外一個查詢的條件或結(jié)果集查找工資最高的員工姓名和工資Select max(sal) from empbak; /查找最高工資Select ename,sal from empbak where sal=(Select max(sal) from empbak);查找各個部門工資最高的人的姓名和工資Select max(sal) from empbak group by deptno;/按照部門查出最高工資Select deptno,ename,sal from

30、 empbak a where sal in (Select max(sal) from empbak b group by deptno having a.deptno=b.deptno) ;Select ename,job,sal from empbak;Select * from (Select ename,job,sal from empbak);分析函數(shù)：對數(shù)據(jù)進行排位的Row_number() 連續(xù)的排位Select ename,sal,Row_number() over (order by sal desc) as 排名 from empbak ;Rank() 如果被排序的值一樣

31、，則排位一樣，后面會跳過相應(yīng)的位數(shù)Select ename,sal,Rank() over (order by sal desc) as 排名from empbak ;Dense_Rank()Select ename,sal,Dense_Rank() over (order by sal desc) as 排名 from empbak ;按照工資排序，取出第1名到第5名Select * from (Select ename,sal,Row_number() over (order by sal desc) as 排名 from empbak) where 排名<=5;按照工資排序，取出第

32、6名到第10名Select * from (Select ename,sal,Row_number() over (order by sal desc) as 排名 from empbak) where 排名>5 and 排名<=10;分頁查詢當前頁碼：page=1每頁條數(shù)：num=5Select * from (Select ename,sal,Row_number() over (order by sal desc) as 排名 from empbak) where 排名>num*(page-1) and 排名<=page*num;*多表查詢*查詢員工的姓名和所屬部

33、門名稱Select ename,deptno from empbak;Select deptno,dname from deptbak;Select ename,dname from empbak,deptbak where empbak.deptno=deptbak.deptno;得到部門名->人數(shù)Select deptno,count(empno) from empbak group by deptno;Select d.dname ,a.* from deptbak d,(Select deptno,count(empno) from empbak group by deptno)

34、 a where d.deptno=a.deptno;/得到人數(shù)最多的部門的人數(shù) select count(empno) from empbak group by deptno;Select max(c) from (select deptno d,count(empno) c from empbak group by deptno);/得到人數(shù)最多的部門的編號和人數(shù)Select * from (select deptno ,count(empno) c1 from empbak group by deptno) where c1=(Select max(c) from (select cou

35、nt(empno) c from empbak group by deptno);/得到人數(shù)最多的部門的名字和人數(shù)Select d.dname,a.c from dept d,(Select deptno,count(empno) c from empbak group by deptno) a where d.deptno=a.deptno;Select * from (Select d.dname,a.c1 from dept d,(Select deptno,count(empno) c1 from empbak group by deptno) a where d.deptno=a.deptno) where C1=(Select max(c) from (select count(empno) c from empbak group by deptno);求SMITH所在部門的位置：Select deptno from empbak where ename=SMITH;Select loc from deptbak where deptno =(Select deptno fro