资源描述:
《oracle数据库_sql学习笔记.docx》由会员上传分享,免费在线阅读,更多相关内容在行业资料-天天文库。
1、oracle-9isql(structuredquerylanguage,结构化查询语言)SQL语言按照功能可分为4大类*DQL(数据查询语言):查询数据*DDL(数据定义语言):建立/删除和修改数据对象*DML(数据操纵语言):完成数据操作的命令,包括查询*DCL(数据控制语言):控制对数据库的访问,服务器的关闭/启动等在Oracle9i中为使用SQL语言提供了2个主要工具*[SQLPlus]*[SQLPlusWorksheet]select*fromscott.emp--"用户名.数据表"的形式selectdistinctjobfromscott.em
2、p--distinct17/17保留字指在显示时去除相同的记录,selectempno,ename,jobfromscott.empwherejob='MANAGER'selectempno,ename,salfromscott.empwheresal<=2500--等于select*fromscott.empwherejob='MANAGER'select*fromscott.empwheresal=1100--不等于select*fromscott.empwherejob!='MANAGER'select*fromscott.empwheresal!=
3、1100select*fromscott.empwherejob^='MANAGER'select*fromscott.empwheresal^=1100select*fromscott.empwherejob<>'MANAGER'17/17select*fromscott.empwheresal<>1100--小于select*fromscott.empwherejob<'MANAGER'select*fromscott.empwheresal<2000--大于select*fromscott.empwherejob>'MANAGER'select*fr
4、omscott.empwheresal>2000--int列表select*fromscott.empwheresalin(2000,1000,3000)select*fromscott.empwherejobin('MANAGER','CLERK')select*fromscott.empwheresalnotin(2000,1000,3000)select*fromscott.empwherejobnot17/17in('MANAGER','CLERK')--betweenselect*fromscott.empwheresalnotbetween20
5、00and3000select*fromscott.empwherejobnotbetween'MANAGER'and'CLERK'--likeselect*fromscott.empwherejoblike'%M%'--代表包含M的字符串select*fromscott.empwherejoblike'%M'--代表以M字符结尾的字符串select*fromscott.empwherejoblike'M%'--代表以M字符开头的字符串select*fromscott.empwherejoblike'M_'--代表M开头的长度为2的字符串selectemp
6、no,ename,jobfromscott.empwhere17/17job>='CLERK'orsal<=2000"notjob='CLERK'"等价于"job<>'CLERK'"--逻辑比较符and(与)select*fromscott.empwherejob='MANAGER'andsal<>2000or(或)select*fromscott.empwherejob!='MANAGER'orsal<>2000not(菲)select*fromscott.empwherenotjob>='MANAGER'--排序查询selectempno,ename,
7、job,salfromscott.empwherejob<='CLERK'orderbyjobasc,saldesc--分组查询17/17selectempno,ename,job,salfromscott.empgroupbyjob,empno,ename,salhavingsal<=2000selectempno,ename,job,salfromscott.empwheresal<=2000groupbyjob,empno,ename,sal/*where检查每条记录是否符合条件,having是检查分组后的各组是否满足条件.having语句只能配合g
8、roupby语句使用,没有groupby时不能使用having,但