oracle数据库学习笔记心得

select*|字段表名from表名where布尔表达式【条件】externalcandidate职员相关信息contractrecruiter猎头公司查看表里所有字段describe[描述]contractrecruiterselectcname,nperecentagechargefromcontractrecruiter不显示原来名字，显示现在名字【用AS】selectcnameasRecruiterName,nperecentagechargeasHireFeesfromcontractrecruiter不显示原来名字，显示现在名字【可不用AS】selectcnameRecruiterName,nperecentagechargeHireFeesfromcontractrecruiter【双引号是否写】双引号是为了区分大小写职位表positionselect*fromposition缺额运算:[想减]selectVDESCRIPTION,nbudgetedstrength,NBUDGETEDSTRENGTH-NCURRENTSTRENGTH,NYEARfrompositionselectVDESCRIPTIONPotion,nbudgetedstrengthBudgetedStrength,NBUDGETEDSTRENGTH-NCURRENTSTRENGTHVacancies,NYEARYearfromposition显示非重复运行查询来源地的人数describeexternalcandidate查看职员城市名字selectccityfromexternalcandidate查看职员城市名字【名字不重复，插入一个关键字：distinct】selectdistinctccity,cstatefromexternalcandidate运算符：两列selectvfirstname,vlastnamefromexternalcandidate字段拼接selectvfirstname||vlastnamefromexternalcandidateselectvfirstname||''||vlastnamefromexternalcandidate学校describecollege查看所有学校select*fromcollege查看只是加利福尼亚的学校select*fromcollegewherecstate='California'一个条件selectvfirstname,vlastname,dbirthdate,ntestscorefromexternalcandidatewheredbirthdate='01-1月-70'加上第二个条件[符合条件，逻辑与]：selectvfirstname,vlastname,dbirthdate,ntestscorefromexternalcandidatewheredbirthdate='01-1月-70'andntestscore=80求反，在where后面加上个【not】selectvfirstname,vlastname,dbirthdate,ntestscorefromexternalcandidatewherenotdbirthdate='01-1月-70'andntestscore=80describenewspaperselect*fromnewspaper匹配模式的运算符:like通配符：%代表1~n个字符；_代表一个字符select*fromnewspaperwherecnewspapernamelike'%Texas%'andvcontactpersonlike'Jackson%''Jackson%'以Jackson大头的。'%Jackson'以Jackson结尾的。selectvfirstname,vlastnamefromexternalcandidate名字第二个字母是“a”selectvfirstname,vlastnamefromexternalcandidatewherevfirstname'_a%'想查名字的最后一个字母是%的人：必须利用转义字符【\】selectvfirstname,vlastnamefromexternalcandidatewherevfirstname'%\%'[前面%代表通配符，而后面%代表就是%本身！]escape'\'[注明那个字符时作为转义字符用的]即【空值】或者【null值】面试时间selectvfirstname,vlastname,dinterviewdatefromexternalcandidatewheredinterviewdateisnull//判断字段上是为空的！selectvfirstname,vlastname,dinterviewdatefromexternalcandidatewheredinterviewdateisnotnull//判断字段上是不为空的！selectvfirstname,vlastname,vemailid,cphonefromexternalcandidatewherevemailidisnullselectvfirstname,vlastname,dbirthdate,ntestscorefromexternalcandidateorderbyntestscore[按ntestscore排序，默认是升序；aesc升序desc降序]selectcname,vaddressfromcontractrecruiterorderbycnamedesc先按照日期降序，然后再按照年龄降序selectvfirstname,vlastname,dbirthdate,ntestscorefromexternalcandidateorderbyntestscoredesc,dbirthdatedesc作业：toy数据库第三章：1、字符函数initcap[以大写方式显示字符首字母],lower[转换成小写],upper[转换成大写],ltrim,rtrimcontractrecruiter猎头公司代码：selectupper(CNAME)Name,lower(VADDRESS)Addressfromcontractrecruiter2、ABS[绝对值]、CEIL[返回大于这个数的最小整数]、FLOOR[截取数值的整数部分]、POWER[求几次方]、MOD[返回第一个参数除以第二个参数的余数]、ROUND[四舍五入]、TRUNC[]、SQRT[]VFIRSTNAMECPHONENTESTSCORE代码：SELECTvfirsTnamevFirstname,cphonecPhone,ROUND(ntestscore)MarksFROMexternalcandidatewherentestscore703、数据类型转换varchar2(40)表示可变长度，最长为40char(40)表示固定长度，固定为40第四章：查询多表中数据1.用等值联接显示两个表中数据A．【内链接】：只有满足联接条件的才显示，不联接的不显示个人表RecruitmentAgencies和公司表ExternalCandidateJoinon句式格式：selectvfirstname,cnamefromexternalcandidatejoinrecruitmentagenciesonrecruitmentagencies.cagencycode=externalcandidate.cagencycode[因为表明太长，所以要给表取一个别名]selectvfirstname,cnamefromexternalcandidateejoinrecruitmentagenciesronr.cagencycode=e.cagencycodewherentestscore=80[添加一个where]Where句式格式:selectvfirstname,cnamefromexternalcandidatee,recruitmentagenciesrwheree.cagencycode=r.cagencycodeandntestscore=80[逻辑与and]cpositioncode――――表externalcandidate（应聘者CPOSITIONCODE――――表position（资料VDESCRIPTION）selectvfirstname,vlastnamefromexternalcandidateejoinpositionpone.CPOSITIONCODE=p.CPOSITIONCODE或者：selectvfirstname,vlastnamefromexternalcandidatee,positionpwheree.CPOSITIONCODE=p.CPOSITIONCODE2、自然联接【两个表中有且仅有同名的条件】有且仅有:selectvfirstname,vlastnamefromexternalcandidatenaturaljoinposition非有且仅有：（不能完成）selectvfirstname,cnamefromexternalcandidatenaturaljoinrecruitmentagencies3、交叉连接4、外联接{只有joinon句式}selectco.ccollegecode,co.ccollegename,ca.ccampusrecruitmentcodefromcollegecojoincampusrecruitmentcaonco.ccollegecode=ca.ccollegecode我们一般都用outerjoinonleft|right|fullouterjoinselectco.ccollegecode,co.ccollegename,ca.ccampusrecruitmentcodefromcollegecoleftouterjoincampusrecruitmentcaonco.ccollegecode=ca.ccollegecode这种句式是在Oracle在身上用的。selectco.ccollegecode,co.ccollegename,ca.ccampusrecruitmentcodefromcollegeco,campusrecruitmentcawhereco.ccollegecode=ca.ccollegecode(+)作业：第5课Externalcandidate表Monthlysalary表Newspaper表Newsad表【投广告】Recruitmentagencies推荐公司一、多行函数：Avg:返回n个数据的平均值selectavg(列名)from(表名)where(条件)selectavg(ntestscore)fromexternalcandidateSun:返回n个值的总和selectsun(列名)from(表名)where(条件)selectsum(NMONTHLYSALARY)fromMonthlysalaryMax:返回一列中的最大值selectmax(列名)from(表名)selectmax(ntestscore)fromexternalcandidateMin:返回这一列的最小值selectmin(列名)from(表名)selectmin(ntestscore)fromexternalcandidateCount:显示查询的行数---------[包括null值]selectcount(列名)from(表名)where(条件)selectcount(*)fromexternalcandidateStddev:返回一组值得标准偏差selectstddev(列名)from(表名)Variance:返回一组值的方差selectVariance(列名)from(表名)例题：selectmax(NPERCENTAGECHA