select* |字段表名from 表名where 布尔表达式【条件】externalcandidate 职员相关信息contractrecruiter 猎头公司查看表里所有字段 describe[描述] contractrecruiterselect cname,nperecentagecharge from contractrecruiter不显示原来名字，显示现在名字【用AS】select cname as "Recruiter Name",nperecentagecharge as "Hire Fees" from contractrecruiter不显示原来名字，显示现在名字【可不用AS】select cname "Recruiter Name",nperecentagecharge "Hire Fees" from contractrecruiter【双引号是否写】双引号是为了区分大小写职位表 positionselect *from position缺额运算:[想减]selectVDESCRIPTION ,nbudgetedstrength,NBUDGETEDSTRENGTH -NCURRENTSTRENGTH ,NYEARfrom positionselectVDESCRIPTION "Potion",nbudgetedstrength "Budgeted Strength" , NBUDGETEDSTRENGTH -NCURRENTSTRENGTH "Vacancies", NYEAR "Year"from position显示非重复运行查询来源地的人数 describe externalcandidate查看职员城市名字 select ccity from externalcandidate 查看职员城市名字【名字不重复，插入一个关键字：distinct】select distinct ccity,cstate from externalcandidate运算符：两列select vfirstname,vlastname from externalcandidate字段拼接select vfirstname||vlastname from externalcandidate select vfirstname||' '||vlastname from externalcandidate学校describe college查看所有学校select *from college查看只是加利福尼亚的学校select * from college where cstate='California'一个条件select vfirstname,vlastname,dbirthdate,ntestscorefrom externalcandidatewhere dbirthdate>='01-1月-70'加上第二个条件[符合条件，逻辑与]：select vfirstname,vlastname,dbirthdate,ntestscorefrom externalcandidatewhere dbirthdate>='01-1月-70' and ntestscore>=80求反，在where后面加上个【not】select vfirstname,vlastname,dbirthdate,ntestscorefrom externalcandidatewhere not dbirthdate>='01-1月-70' and ntestscore>=80describe newspaperselect *from newspaper匹配模式的运算符 :like通配符： % 代表1~n个字符； _ 代表一个字符select *from newspaperwhere cnewspapername like ' %Texas%'and vcontactperson like 'Jackson %''Jackson %'以Jackson大头的。

'%Jackson'以Jackson结尾的。

select vfirstname,vlastname from externalcandidate名字第二个字母是“a”select vfirstname,vlastname from externalcandidatewhere vfirstname '_a% '想查名字的最后一个字母是%的人：必须利用转义字符【\】select vfirstname,vlastname from externalcandidatewhere vfirstname '%\ % '[前面%代表通配符，而后面%代表就是%本身！] escape '\ '[注明那个字符时作为转义字符用的]即【空值】或者【null值】面试时间select vfirstname,vlastname,dinterviewdatefrom externalcandidatewhere dinterviewdate is null //判断字段上是为空的！select vfirstname,vlastname,dinterviewdatefrom externalcandidatewhere dinterviewdate is not null //判断字段上是不为空的！select vfirstname,vlastname,vemailid,cphonefrom externalcandidatewhere vemailid is nullselect vfirstname,vlastname,dbirthdate, ntestscorefrom externalcandidateorder by ntestscore [按ntestscore排序，默认是升序；aesc升序 desc 降序]select cname ,vaddressfrom contractrecruiterorder by cname desc先按照日期降序，然后再按照年龄降序select vfirstname,vlastname,dbirthdate, ntestscorefrom externalcandidateorder by ntestscore desc ,dbirthdate desc作业：toy数据库第三章：1、字符函数initcap[以大写方式显示字符首字母],lower[转换成小写],upper[转换成大写],ltrim,rtrimcontractrecruiter 猎头公司代码：selectupper(CNAME) Name,lower(VADDRESS) Addressfrom contractrecruiter2、ABS[绝对值]、CEIL[返回大于这个数的最小整数]、FLOOR[截取数值的整数部分]、POWER[求几次方]、MOD[返回第一个参数除以第二个参数的余数]、ROUND[四舍五入]、TRUNC[]、SQRT[]VFIRSTNAMECPHONENTESTSCORE代码：SELECTvfirsTname vFirstname,cphone cPhone,ROUND(ntestscore) MarksFROM externalcandidatewhere ntestscore>703 、数据类型转换varchar2(40) 表示可变长度，最长为40char(40) 表示固定长度，固定为40第四章：查询多表中数据1.用等值联接显示两个表中数据A．【内链接】：只有满足联接条件的才显示，不联接的不显示个人表RecruitmentAgencies和公司表ExternalCandidate Join on 句式格式：select vfirstname,cnamefrom externalcandidate join recruitmentagenciesonrecruitmentagencies.cagencycode=externalcandidate.cagencycode [因为表明太长，所以要给表取一个别名]select vfirstname,cnamefrom externalcandidate e join recruitmentagencies r on r.cagencycode=e.cagencycodewhere ntestscore>=80 [添加一个where]Where 句式格式:select vfirstname,cnamefrom externalcandidate e,recruitmentagencies rwhere e. cagencycode = r.cagencycodeand ntestscore>=80 [逻辑与 and]cpositioncode ――――表externalcandidate（应聘者CPOSITIONCODE ――――表position（资料VDESCRIPTION）selectvfirstname ,vlastnamefrom externalcandidate e join position pon e.CPOSITIONCODE =p.CPOSITIONCODE或者：selectvfirstname ,vlastnamefrom externalcandidate e , position pwhere e.CPOSITIONCODE =p.CPOSITIONCODE2、自然联接【两个表中有且仅有同名的条件】有且仅有:selectvfirstname ,vlastnamefrom externalcandidate natural join position非有且仅有：（不能完成）selectvfirstname ,cnamefrom externalcandidate natural join recruitmentagencies3、交叉连接4、外联接{只有join on 句式}selectollegecode,ollegename,ampusrecruitmentcodefrom college co join campusrecruitment caon ollegecode=ollegecode我们一般都用outer join onleft|right|full outer joinselectollegecode,ollegename,ampusrecruitmentcodefrom college co left outer join campusrecruitment ca on ollegecode=ollegecode这种句式是在Oracle在身上用的。