第一课：客户端1.SqlPlus(客户端），命令行直接输入：sqlplus，然后按提示输入用户名，密码。2.从开始程序运行:sqlplus，是图形版的sqlplus.3.http://localhost:5560/isqlplusToad：管理，PlSqlDeveloper:第二课：更改用户1.sqlplussys/bjsxtassysdba2.alteruserscottaccountunlock;(解锁)第三课：tablestructure1.描述某一张表：desc表名2.select*from表名第四课：select语句：1.计算数据可以用空表：比如：.select2*3fromdual2.selectename,sal*12annual_salfromemp;与selectename,sal*12"annualsal"fromemp;区别，加双引号保持原大小写。不加全变大写。3.selectename||"abcd"如果连接字符串中含有单引号，用两个单引号代替一个单引号。第五课：distinctselectdeptnofromemp;selectdistinctdeptnofromemp;selectdistinctdeptnofromemp;selectdistinctdeptno,jobfromemp去掉deptno,job两者组合的重复。更多的项，就是这么多项的组合的不重复组合。第六课：Whereselect*fromempwheredeptno=10;select*fromempwheredeptno<>10;不等于10select*fromempwhereename='bike';selectename,salfromempwheresalbetween800and1500(>=800and<=1500)空值处理:selectename,sal,commfromempwherecommis(not)null;selectename,sal,commfromempwhereename(not)in('smith','king','abc');selectenamefromempwhereenamelike'_A%';_代表一个字母,%代表0个或多个字母.如果查询%可用转义字符.\%.还可以用escape'$'比如:selectenamefromempwhereenamelike'%$a%'escape'$';第七课:orderbyselect*fromdept;select*fromdeptorderbydeptdesc;(默认:asc)selectename,sal,deptnofromemporderbydeptnoasc,enamedesc;第八课:sqlfunction1:selectename,sal*12annual_salfromempwhereenamenotlike'_A%'andsal>800orderbysaldesc;selectlower(ename)fromemp;selectenamefromempwherelower(ename)like'_a%';等同于selectenamefromempwhereenamelike'_a%'orenamelike'_A%';selectsubstr(ename,2,3)fromemp;从第二字符截,一共截三个字符.selectchr(65)fromdual结果为:Aselectascii('a')fromdual结果为:65selectround(23.652,1)fromdual;结果为:23.7selectround(23.652,-1)fromdual;20selectto_char(sal,'$99,999,999')fromemp;selectto_char(sal,'L99,999,999')fromemp;人民币符号,L:代表本地符号这个需要掌握牢:selectbirthdatefromemp;显示为:BIRTHDATE----------------17-12月-80----------------改为:selectto_char(birthdate,'YYYY-MM-DDHH:MI:SS')fromemp;显示:BIRTHDATE-------------------1980-12-1712:00:00-------------------selectto_char(sysdate,'YYYY-MM-DDHH24:MI:SS')fromdual;//也可以改为:HH12TO_CHAR(SYSDATE,'YY-------------------2007-02-2514:46:14to_date函数:selectename,birthdatefromempwherebirthdate>to_date('1981-2-2012:34:56','YYYY-MM-DDHH24:MI:SS');如果直接写birthdate>'1981-2-2012:34:56'会出现格式不匹配,因为表中的格式为:DD-MM月-YY.selectsalfromempwheresal>888.88无错.但selectsalfromempwheresal>$1,250,00;会出现无效字符错误.改为:selectsalfromempwheresal>to_number('$1.250.00','$9,999,99');把空值改为0selectename,sal*12+nvl(comm,0)fromemp;这样可以防止comm为空时,sal*12相加也为空的情况.第九课:Groupfunction组函数max,min,avg,count,sum函数selectto_char(avg(sal),'99999999,99')fromemp;selectround(avg(sal),2)fromemp;结果:2073.21selectcount(*)fromempwheredeptno=10;selectcount(ename)fromempwheredeptno=10;count某个字段,如果这个字段不为空就算一个.selectcount(distinctdeptno)fromemp;selectsum(sal)fromemp;第十课:Groupby语句需求:现在想求,求每个部门的平均薪水.selectavg(sal)fromempgroupbydeptno;selectdeptnoavg(sal)fromempgroupbydeptno;selectdeptno,job,max(sal)fromempgroupbydeptno,job;求薪水值最高的人的名字.selectename,max(sal)fromemp;出错,因为max只有一个值,但等于max值的人可能好几个,不能匹配.应如下求:selectenamefr...
