oracle数据库_sql学习笔记.docVIP

oracle-9i sql(structured query language , 结构化查询语言) SQL语言按照功能可分为4大类 *DQL(数据查询语言):查询数据 *DDL(数据定义语言):建立/删除和修改数据对象 *DML(数据操纵语言):完成数据操作的命令,包括查询 *DCL(数据控制语言):控制对数据库的访问,服务器的关闭/启动等 在Oracle 9i 中为使用SQL语言提供了2个主要工具 *[SQL Plus] *[SQL Plus Worksheet] select * from scott.emp --用户名.数据表的形式 select distinct job from scott.emp --distinct 保留字指在显示时去除相同的记录, select empno,ename,job from scott.emp where job=MANAGER select empno,ename,sal from scott.emp where sal=2500 --等于 select * from scott.emp where job=MANAGER select * from scott.emp where sal=1100 --不等于 select * from scott.emp where job!=MANAGER select * from scott.emp where sal!=1100 select * from scott.emp where job^=MANAGER select * from scott.emp where sal^=1100 select * from scott.emp where jobMANAGER select * from scott.emp where sal1100 --小于 select * from scott.emp where jobMANAGER select * from scott.emp where sal2000 --大于 select * from scott.emp where jobMANAGER select * from scott.emp where sal2000 --int列表 select * from scott.emp where sal in(2000,1000,3000) select * from scott.emp where job in(MANAGER,CLERK) select * from scott.emp where sal not in(2000,1000,3000) select * from scott.emp where job not in(MANAGER,CLERK) --between select * from scott.emp where sal not between 2000 and 3000 select * from scott.emp where job not between MANAGER and CLERK --like select * from scott.emp where job like %M% --代表包含M的字符串 select * from scott.emp where job like %M --代表以M字符结尾的字符串 select * from scott.emp where job like M% --代表以M字符开头的字符串 select * from scott.emp where job like M_ --代表M开头的长度为2的字符串 select empno,ename,job from scott.emp where job=CLERK or sal=2000 not job=CLERK等价于jobCLERK --逻辑比较符 and(与) select * from scott.emp where job=MANAGER and sal2000 or(???) select * from scott.emp where job！=MANAGER or sal2000 not(菲) select * from scott.emp where not job=MANAGER --排序查询 select empno,ename,job,sal from scott.emp where job=CLERK order by job asc,sal desc --分组查询 select empno,ename,job,sal from scott.emp group b