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