oracle语法大全.doc

SQLdesc 表名SQLdesc s_emp脚本 sql.txt sql.sql 1.上传脚本2.运行脚本 绝对路径/文件名 文件名（关注语言环境）select userenv(lang) from dual; USERENV(LANG)-USUSERENV(LANG)-ZHSdesc 一张表Name Null? Type- - -ID 员工id NOT NULL NUMBER(7)LAST_NAME 姓 NOT NULL VARCHAR2(25)FIRST_NAME 名 VARCHAR2(25)USERID 员工描述 VARCHAR2(8)START_DATE 入职日期 DATECOM MENTS 备注 VARCHAR2(255)MANAGER_ID 领导的员工id NUMBER(7)TITLE 职位 VARCHAR2(25)DEPT_ID 部门id NUMBER(7)SALARY 月薪 NUMBER(11,2)COMMISSION_PCT 提成 NUMBER(4,2)sql语句的分类：数据检索 select数据操作 insert delete update数据定义 create drop alter事务控制 commit rollback savepoint数据控制 grant revoke选择 投影 连接查询语句：A from 子句 1.查询出任意一个字段 select 字段名 from 表名; select salary from s_emp; 2.查询多个字段 (用逗号分割) select first_name,salary from s_emp; 3.* 号可以替代所有的字段 select * from s_emp; 4.表头的原样显示 -使用双引号 select first_name,salary sal from s_emp; select first_name,salary sal from s_emp; 可以让别名中包含空格 select first_name,salary emP sal from s_emp; 5.字段的数学运算 求每个人的年薪 select salary*12 yearsal from s_emp; 换一种年薪计算方式 考虑提成 select salary*12*(1+commission_pct/100) yearsal from s_emp; select salary*12*(1+commission_pct/100) yearsal from s_emp; nvl 空值处理函数 nvl(值/字段,如果是空要得到的值) 第一个参数是NULL 就返回第二个参数 如果第一个参数不是NULL 返回第一个参数 select nvl(salary*12*(1+commission_pct/100),0) yearsal from s_emp;/logic error 空值要尽早处理 空值和任何值做运算 都是NULL select salary*12*(1+nvl(commission_pct,0)/100) yearsal from s_emp; 6.想把姓名显示出来 select first_name,last_name from s_emp; 字符串连接 | select first_name|last_name from s_emp; 字符串的表达 hello _ select first_name|_|last_name from s_emp; thiss select first_name|last_name from s_emp; 7.排重显示 -distinct select salary from s_emp; select distinct salary from s_emp; 补充：联合排重 select distinct salary,id from s_emp; clear screen !clear B 条件子句 where where 字段表达式 表达式 找出工资大于1400的 first_name,salary select first_name,salary from s_emp where 1=1;/全部显示 select first_name,salary from s_emp where 1=2;/无显示 select first_name,salary from s_emp where salary1400; where 条件 限制行的返回 符合条件返回 不符合过滤掉 字段表达式中可以使用的比较运算符 = 一个等号判断相等 找出first_name ,manager_id 是Carmen 的工资 select first_name,salary,manager_id from s_emp where first_name=Carmen; 数字类型可以直接用等号判断相等 字符串不要忘记单引号 oracle sql 大小写不敏感 单字符串的值大小写敏感 = != = between and not between and in not in like not like is null is not null oracle(sql) 也提供了一些运算符 字段 between a and b 表达一个闭区间a,b 工资 salary 在800,1400 select first_name,salary from s_emp where salary between 800 and 1400; 字段 in (list) list用逗号隔开的一组值 查询一下 部门号 在 41 42 50 人first_name,salary select first_name,salary,dept_id from s_emp where dept_id in (41,42,50); 顺序对最终结果没有影响 但对效率可能产生影响 字段 is null 判断一个字段是不是NULL id 是1的人manager_id 是不是NULL select id,first_name from s_emp where manager_id is null; like -模糊查询 成龙配套 成龙 李小龙 龙飞凤舞 first_name 中带a字符的 统配符： % 代表n个任意字符 _ 一个任意字符 %a% %a a% _a% %a_ select first_name from s_emp where first_name like %a%; 找出第二个字符是a select first_name from s_emp where first_name like _a%; user_tables-数据字典表，存储了数据库中所有表的信息 desc user_tables TABLE_NAME 表名 select table_name from user_tables; s_emp s_dept s 找出所有s_开头的表名 select table_name from user_tables where table_name like s_%; user_tables 数据字典表-默认处理成大写 select table_name from user_tables where table_name like S_%; select table_name from user_tables where table_name like S_%;/logic error 转义 select table_name from user_tables where table_name like S_% escape ; select table_name from user_tables where table_name like S|_|%;/logic 逻辑连接符号 and or ! 工资 salary 在800,1400 select first_name,salary from s_emp where salary between 800 and 1400 ; select first_name,salary from s_emp where salary=800 and salary800 and salary = = = != = between and not between and in not in like not like is null is not null manager_id 不是NULL的first_name ,salary,manager_id select first_name ,salary,manager_id from s_emp where manager_id is not null; xml -dom sax ui 条件可以用小括号改变优先级别 select first_name from s_emp where dept_id=41 or dept_id=42 and salary1000; select first_name from s_emp where (dept_id=41 or dept_id=42) and salary1000; 如果不确定你的逻辑 可以通过小括号改变逻辑优先 select salary*12+100 from s_emp; select (salary+100)*12 from s_emp; C 排序 排序的种类： 升序 字典顺序 自然顺序 asc 默认顺序 降序 desc 按照工资排序 显示first_name,salary order by 排序字段 一定出现在sql语句最后 select first_name,salary from s_emp order by salary; select first_name,salary from s_emp order by salary asc; 降序 select first_name,salary from s_emp order by salary desc; 补充：第一排序字段 第二排序排序 按照工资排序 显示first_name,salary 工资相同 按first_name 降序 select first_name,salary from s_emp where 1=1 order by salary desc,first_name desc;D 单行函数 单行函数：对一行操作之后得到一个结果 组函数：对一组数据数据处理之后得到一个结果 upper select first_name,upper(first_name) from s_emp; count select count(id) from s_emp; s_emp 表中数据特别多 对单行函数的测试不是很方便 测试表 dual 单行单列 desc dual select * from dual; 测试 字符串的函数 upper lower select lower(ONE DREAM ONE WORLD) from dual; initcap select initcap(ONE DREAM ONE WORLD) from dual; length select length(ONE) from dual; edit -进入vi 编辑界面 x 删除字符 dd 删除一行 a i o 命令下 ZZ 保存退出 nvl() select nvl(NULL,is null) from dual; select nvl(,is null) from dual; select nvl( ,is null) from dual; nvl 要求可以处理任何类型 但两个参数 的类型必须一致 substr(字段/值,开始的位置,截取多长) 注意开始的位置是从1开始 也可以负数 负数从后往前编号从-1开始编号 select substr(hello,1,3) from dual; select substr(hello,0,3) from dual; select substr(hello,2,4) from dual; 从后编号 select substr(hello,-2,2) from dual; 把s_emp中first_name 的后三个字符截取下来 select first_name,substr(first_name,-3,3) from s_emp; 要求从前往后编号 hello 12345 select first_name, substr(first_name,length(first_name)-2,3) from s_emp; 数字 select first_name from s_emp where id=1; select first_name from s_emp where id=1;/隐式类型转换 select first_name from s_emp where id=to_number(1); to_number(abc) /logic error select initcap(one world one world) from s_emp; round(字段/值) 四舍五入取整 select round(99.99) from dual;/100 select round(99.94,1) from dual;/99.9 select round(99.96,1) from dual;/100 select round(94.96,-1) from dual;/90 trunc(字段/值) 截取取整 select trunc(99.99) from dual;/99 select trunc(99.94,1) from dual;/99.9 select trunc(99.96,1) from dual;/99.9 select trunc(94.96,-1) from dual;/90 select round(99.96,-2) from dual;/100 to_char() 字符转换函数 to_char(字段) 把这个字段变成字符串 按照格式显示数字 to_char(字段/值,格式) fm 格式说明 $ 美元符号 L 本地货币符号 ￥ RMB 9 任意数字 0 强制显示0 100 000,100.00 , 国际货币分割符 . 小数点 select salary ,to_char(salary,fmL099,999.00) from s_emp; select salary ,to_char(salary,fmL099,999.99) from s_emp; select userenv(lang) from dual; select to_char(9999.97,fmL099,999.00) from dual; select to_char(9999.97,fmL099,999.99) from dual; 切换语言环境 对显示的本地货币符号的影响 对日期格式产生了影响 select sysdate from dual; SYSDATE - 26-APR-12 现在切换成中文 NLS_LANG bash 修改.bash_profile export NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK source .bash_profile 重新进入sqlplus select userenv(lang) from dual; ZHS select sysdate from dual; select salary ,to_char(salary,fmL099,999.00) from s_emp; 练习：select first_name, salary,nvl(to_char(manager_id),boss)from s_emp where salary1000 order by salary desc; 把first_name,salary,manager_id 显示出来 要求工资 大于1000 并且按照工资降序 manager_id 如果是NULL 则显示manager_id 为 boss select first_name,salary,nvl(manager_id,boss) from s_emp where salary1000 order by salary desc; select first_name,salary,nvl(to_char(manager_id),boss) from s_emp where salary1000 order by salary desc; select first_name,salary, nvl(to_char(manager_id),boss) from s_emp where salary1000 order by salary desc; E 多表查询 我们需要的数据 不存储在一张表中 把每个员工的first_name,dept_id 查询出来 select first_name,dept_id from s_emp; 部门表 s_dept 名称 是否为空? 类型- - -ID 部门id NOT NULL NUMBER(7)NAME 部门名称 NOT NULL VARCHAR2(25)REGION_ID 地区id NUMBER(7) 地区表 s_region名称 是否为空? 类型- - -ID 地区id NOT NULL NUMBER(7)NAME 地区名称 NOT NULL VARCHAR2(50) select * from s_region;select first_name,dept_id,name from s_emp,s_dept;笛卡尔集select first_name,dept_id,name from s_emp,s_dept where dept_id=s_dept.id;解决两个表直接的关系表的连接： 内连接：符合连接条件 就放入结果集 不符合就过滤掉 等值连接:用等号做两个表的连接 select first_name,dept_id,name from s_emp,s_dept where dept_id=s_dept.id; 把每个部门的名字 和所在的地区名称列出来 select s_ ,s_ from s_dept,s_region where region_id=s_region.id; 表的别名 select , from s_dept d,s_region r where d.region_id=r.id; 非等值连接:不用等号做表的连接desc salgrade 工资级别表 名称 - GRADE 工资级别 LOSAL 低工资 HISAL 高工资 把s_emp 表中first_name,salary,grade 列一个清单 select first_name,salary,grade from s_emp e,salgrade g where salary between losal and hisal; 自连接:需要把一张表看成两张表谁是领导？把s_emp 逻辑上看成 两张表 然后找出这两张表的关系 select distinct m.first_name,m.id from s_emp e,s_emp m where e.manager_id=m.id; select first_name,id from s_emp where manager_id=id; select id,manager_id from s_emp; 谁不是领导？ select distinct m.first_name,m.id, from s_emp e,s_emp m where e.manager_id!=m.id; 外连接： 外连接的结果集等于内连接的结果集 +匹配不上的记录（和直接选择出是不同的）. 一个也不能少 自连接 select distinct m.first_name,m.id from s_emp e,s_emp m where e.manager_id(+)=m.idand e.manager_id is null;/哪些不是领导 select distinct m.first_name,m.id from s_emp e,s_emp m where e.manager_id(+)=m.idand e.manager_id is not null; scott tiger select * from s_dept; insert into s_dept values(100,mytest1,1); commit; s_emp2 中没人在我新增加的部门中 把每个员工所在部门名列出来（内连接） select first_name,name from s_emp,s_dept where dept_id(+)=s_dept.id; 根据上面的提示 找出没有员工的部门(外连接) select first_name,name from s_emp,s_dept where dept_id(+)=s_dept.id and dept_id is null; (+) 对面的表的数据全部匹配出来 等值连接非等值连接 找出每个员工 的工资级别first_name,salary,gradeselect first_name,salary,grade from s_emp,salgrade where salary between losal and hisal; update s_emp set salary=12500 where id=1;commit;更新之后老板不符合内连接的连接条件把老板的工资级别显示成10 select first_name,salary,nvl(grade,10) from s_emp,salgrade where salary between losal(+) and hisal(+); 外连接的标准实现：sql99 left outer join on right outer join on full outer join on 找出那些部门中还没有员工 select first_name,name from s_emp,s_dept where dept_id(+)=s_dept.id and dept_id is null; 正确显示mytest1行 那张表发起连接 那张表中的数据就被全部匹配出来 select first_name,name from s_dept left outer join s_emp 表示s_dept发起连接，其数据全部显示出来 on s_dept.id=s_emp.dept_id where dept_id is null;正确显示mytest1行 select first_name,name from s_emp left outer join s_dept on s_dept.id=s_emp.dept_id where dept_id is null;1; 按照部门号 分组 统计每个部门的平均工资 select dept_id,avg(salary) from s_emp group by dept_id; 按照部门号 分组 统计每个部门的平均工资 找出平均工资大于1200的组 select dept_id,avg(salary) from s_emp group by dept_id having avg(salary)1200; select dept_id,avg(salary) asal from s_emp group by dept_id having avg(salary)1200 order by asal; 按照部门号 分组 统计每个部门的平均工资 找出平均工资大于1200的组 显示部门名称 select dept_id,name,avg(salary) asal from s_emp,s_dept where dept_id=s_dept.id group by dept_id having avg(salary)1200 order by asal; 因为部门号不会重复 所有在这个分组的标准 再追加 额外的标准 没有影响 select dept_id,name,avg(salary) asal from s_emp,s_dept where dept_id=s_dept.id group by dept_id,name -第一行有name,分组标准里必须有name having avg(salary)1200 order by asal; select dept_id,max(name),avg(salary) asal from s_emp,s_dept where dept_id=s_dept.id group by dept_id having avg(salary)1200 order by asal;- 第一行不max(name) 换成 name 会出错 max()作用有时和distinct(去重）类似。有分组的情况下在select 后的字段要么是分组标准 要么是组函数处理之后的字段 重点 补充：组函数 处理数据时会忽略NULL值select avg(commission_pct) from s_emp;G 子查询 一个sql 语句可以出现在另一个sql 语句中 where 一个sql查询结果 可以作为另一个sql的条件 谁是领导？ select distinct manager_id from s_emp; select first_name,id from s_emp where id in(6,7,8,1,2,3,9,10); select first_name,id from s_emp where id in(select distinct manager_id from s_emp); # 用子查询 找出谁是普通员工？ having 按照部门号分组 求每个部门的平均工资 找出平均工资大于42部门的部门 select dept_id,avg(salary) from s_emp group by dept_id; 求42部门的平均工资 select avg(salary) from s_emp where dept_id=42; select dept_id,avg(salary) from s_emp group by dept_id having avg(salary)(select avg(salary) from s_emp where dept_id=42); from :你可以把sql 语句的查询结果看成一张内存表 select id,salary sal from s_emp where salary