oracle数据库学习笔记

注意：学习的朋友要先在数据库中创建这2个表，后边是示例都按照这2个表演示的；--样例表1:部门表CREATETABLEdept(deptnoNUMBER(2)PRIMARYKEY,dnameVARCHAR2(14),locVARCHAR2(13));--部门表中的数据INSERTINTOdeptVALUES(10,'ACCOUNTING','beijing');INSERTINTOdeptVALUES(20,'RESEARCH','tianjin');INSERTINTOdeptVALUES(30,'SALES','shanghai');INSERTINTOdeptVALUES(40,'OPERATIONS','guangzhou');--样例表2:职员表empCREATETABLEemp(empnoNUMBER(4)PRIMARYKEY,enameVARCHAR2(10),jobVARCHAR2(9),mgrNUMBER(4),hiredateDATE,salNUMBER(7,2),commNUMBER(7,2),deptnoNUMBER(2)CONSTRAINTFK_DEPTNOREFERENCESDEPT);--给数据表emp增加记录INSERTINTOempVALUES(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);INSERTINTOempVALUES(7499,'ALLEN','SALESMAN',7698,to_date('20-02-1981','dd-mm-yyyy'),1600,300,30);INSERTINTOempVALUES(7521,'WARD','SALESMAN',7698,to_date('22-02-1981','dd-mm-yyyy'),1250,500,30);INSERTINTOempVALUES(7566,'JONES','MANAGER',7839,to_date('02-04-1981','dd-mm-yyyy'),2975,NULL,20);INSERTINTOempVALUES(7654,'MARTIN','SALESMAN',7698,to_date('28-09-1981','dd-mm-yyyy'),1250,1400,30);INSERTINTOempVALUES(7698,'BLAKE','MANAGER',7839,to_date('01-05-1981','dd-mm-yyyy'),2850,NULL,30);INSERTINTOempVALUES(7782,'CLARK','MANAGER',7839,to_date('09-06-1981','dd-mm-yyyy'),2450,NULL,10);INSERTINTOempVALUES(7788,'SCOTT','ANALYST',7566,to_date('09-12-1982','dd-mm-yyyy'),3000,NULL,20);INSERTINTOempVALUES(7839,'KING','PRESIDENT',UNLL,to_date('17-11-1980','dd-mm-yyyy'),5000,NULL,10);INSERTINTOempVALUES(7844,'TURNER','SALESMAN',7698,to_date('08-09-1981','dd-mm-yyyy'),1500,0,30);INSERTINTOempVALUES(7876,'ADAMS','CLERK',7788,to_date('12-01-1983','dd-mm-yyyy'),1100,NULL,20);INSERTINTOempVALUES(7900,'JAMES','CLERK',7698,to_date('03-12-1981','dd-mm-yyyy'),950,NULL,30);INSERTINTOempVALUES(7902,'FORD','ANALYST',7566,to_date('03-12-1981','dd-mm-yyyy'),3000,NULL,20);INSERTINTOempVALUES(7934,'MILLER','CLERK',7782,to_date('23-01-1982','dd-mm-yyyy'),1300,NULL,10);insertintoemp(empno,ename,job,hiredate,sal,deptno)values(7839,'KING','PRESIDENT',to_date('17-11-1981','dd-mm-yy'),5000,10);如何创建表：createtableusers_chang(idnumber(4),namevarchar2(20),passwordchar(6),phonechar(11),emailvarchar2(50));如何删除表：droptableusers_chang;如何向表中增加数据insertintousers_chang(id,name,password,phone,email)values(1002,'liucs','1234','13600000000','[e-mail]');如何查询：select*fromusers_chang;------*表示查询全部列selectid,pasword,namefromusers_chang;------查询id,password,name这个三个；selectnamefromusers_changwhereid=1001andpassword='1234';columnnameformata9;descemp;显示表结构创建表：ID不能重复，也不能为空：主键PASSWORD：不能为空非空EMAIL不能重复：唯一具体操作：createtableusers_chang(idnumber(4)PRIMARYKEY,namevarchar2(20),passwordchar(6)NOTNULL,phonechar(11),emailvarchar2(50)UNIQUE);PRIMARYKEY主键createtableemp(empnonumber(4)primarykey,enamevarchar2(10),jobvarchar2(9),mgrnumber(4),hiredatedate,salnumber(7,2),commnumber(7,2),deptnonumber(2)constraintfk_deptnonreferencesdept);insertintoemp(empno,ename,job,mgr,hiredate,sal,deptno)values(7369,'smith','clerk',7902,'17-DEC-80',800,20);insertintoemp(empno,ename,job,mgr,sal,deptno)values(7369,'smith','clerk',7902,800,20);selectename,sal,comm,sal+nvl(comm,0)month_salfromemp;//nvl(comm,0)如果comm是0输出salselectename,sal,comm,sal+commmonth_salfromemp;//空值和任何数据做算数运算，都是null解决方案：nvl函数nvl(comm,0);---字符串连接||相当于JAVA中的+selectename||'workas'||jobemployeefromemp;//'||'****'||'字符串连接smithworkasclerk----小写：函数lowerselectlower(ename||'workas'||job)employeefromemp;----全大写upper----首写字母大写initcap---selectdistinctjobfromemp;不重复的职位---selectdistinctdeptnofromemp;---selectdistinctdeptno,jobfromemp;---条件查询whereselectename,salfromempwheresal=300;---名字是scott的员工(oracle中的数据大小写敏感。)selectenamefromempwhereename='SCOTT';---不确定大小写select*fromempwherelower(ename)='scott';---查询工资在2000--3000之间的人员selectename,salfromempwheresal=2000andsal=3000;---闭区间【2000,3000】selectename,salfromempwheresalbetween2000and3000;selectename,salfromempwheresalnotbetween2000and3000;不在这个范围---在部门10和部门20工作的员工selectename,deptnofromemp_changwheredeptno=10ordeptno=20;---在deptno列表中：inselectename,deptnofromemp_changwheredeptnoin(10,20);selectename,deptnofromemp_changwheredeptnonotin(10,20);不在这个范围中---模糊查询like---通配符：%(任意字符）_（一个字段）wherenamelike'%zhangsan%'只要包含zhangsan都查出来；---有多少个表名字包含EMPselectcount(*)fromuser_tableswheretable_namelike'%EMP%';---员工名字中有字符’A‘的数据selectenamefromemp_changwhereenamelike'%A%';selectenamefromemp_changwhereenamenotlike'%A%';不包含---ename:S_Scott只查找_S的数据selectenamefromempwhereenamelike'%\_S%'escape'\';---数据排序默认由小到大selectename,salfromemp_changorderbysal;由大到小排列selectename,salfromemp_changorderbysaldesc;oracle把空值看为最大去掉空值然后排序selectename,salfromemp_changwheresalisnotnullorderbysaldesc;---select空值的处理空值的比较：isnullisnotnull不要用=或比较空值---SQL条件中的否定不等于：或者！=---user_tables:当前用户的表selectcount(*)fromuser_tables;//当前用户表的总数---descuser_tables;显示表字段的---表的复制createtableemp_changasselect*fromemp;Day02二、函数---函数小结(单行函数)字符函数：upper(lower)/trim/lpad***length/replace/substr/rpad数字函数：round/trunc/mod日期函数：months_between/last_day/add_months/next_day转换函数：to_date***/to_char***/to_number通用函数：nvl/coalesce/decode---空值处理函数nvl---用法：nvl(p1,p2)---p1/p2类型务必保持一致；ifp1sin