Oracle基础知识Word格式文档下载.docx

1、规定字段的对齐方向 column 字段名 justify left | right | center;清除字段的格式 column 字段名 clear;5. SQL*PLUS里规定字段的显示格式例子 column last_name heading Employee|Name format a15; column salary justify right format $99,999.99; column start_date format a10 null Not Hired说明：如果start_date为null, 显示字符串6. 判断题（T/F） （1）. SQL command are

2、 always held in sql buffer. T （2）. SQL*PLUS command assit with query data. T 5 J2EE zxw SQL*PLUS命令只控制SELECT结果集的显示格式及控制文件.只有SQL命令能访问数据库. 二、限制选择行 1. 按指定的规则排序 SELECT expr FROM table ORDER BY column, expr ASC | DESC ;默认的排序是ASC升序（由小到大） 还可以ORDER BY 字段名的位置1| 2 ASC| DESC;2. 用WHERE限制选择行（1） 比较操作符 = = = 与NULL比

3、较不能用上面的比较操作符 ANY SOME ALL SQL操作符 BETWEEN AND IN LIKE IS NULL NOT BETWEEN AND NOT IN NOT LIKE IS NOT NULL 逻辑操作符 AND OR NOT 3. 用WHERE限制选择行（2） 比较顺序（可以用括号改变它们的顺序） （1）. = to_date（） and日期字段= to_date（）;6. 不能用到索引的比较操作符 IS NULL IS NOT NULL LIKE 三、单行函数 1. 数字函数 ABS 取绝对值 POWER 乘方 LN 10为底数取0 SQRT 平方根 EXP e的n次乘方

4、LOG（m,n） m为底数n取0 数学运算函数:ACOS ATAN ATAN2 COS COSH SIGN SIN SINH TAN TANH CEIL 大于或等于取整数 FLOOR 小于或等于取整数 MOD 取余数 ROUND（n,m） 按m的位数取四舍五入值如果round（日期）: 中午12以后将是明天的日期.round（sysdate,Y）是年的第一天 TRUNC（n,m） 按m的位数取小数点后的数值如果trunc（日期）, 确省的是去掉时间 6 2. 字符函数 CHR 按数据库的字符集由数字返回字符 CONCAT（c1,c2） 把两个字符c1,c2组合成一个字符, 和 | 相同 REP

5、LACE（c,s,r） 把字符c里出现s的字符替换成r, 返回新字符 SUBSTR（c,m,n） m大于0,字符c从前面m处开始取n位字符,m等于0和1一样, m小与0,字符c从后面m处开始取n位字符 TRANSLATE（c,f1,t1） 字符c按f1到t1的规则转换成新的字符串 INITCAP 字符首字母大写,其它字符小写 LOWER 字符全部小写 UPPER 字符全部大写 LTRIM（c1,c2） 去掉字符c1左边出现的字符c2 RTRIM（c1,c2） TRIM（c1,c2） 去掉字符c1左右两边的字符c2 LPAD（c1,n,c2） 字符c1按制定的位数n显示不足的位数用c2字符串替换

6、左边的空位 RPAD（c1,n,c2） 3. 日期函数 ADD_MONTHS（d,n） 日期值加n月 LAST_DAY 返回当月的最后一天的日期 MONTHS_BETWEEN（d1,d2） 两个日期值间的月份,d1（select min（rowid） from table_name t2 where t1.email = t2.email group by email having count（email） 1）;找到手机用户的服务区域: select a.handphoneno,nvl（c.name,null）,a.totalscore from topscore a,chargeopera

7、tor cc,chargeoperatorinfo c where substr（a.handphoneno,1,7）=cc.hpnohead（+） and cc.chargetype=c.chargetype（+） order by a.totalscore desc;3. 数据表间的连接技巧 连接N个表, 需要N-1个连接操作 被连接的表最好建一个单字符的别名, 字段名前加上这个单字符的别名 BETWEEN . AND. 比用 = AND grant plustrace to &username;一般用户下运行/ORACLE_HOME/rdbms/admin/utlxplan.sql 产生

8、plan_table set time on; 说明：打开时间显示 set autotrace on;打开自动分析统计，并显示SQL语句的运行结果 set autotrace traceonly;打开自动分析统计，不显示SQL语句的运行结果 接下来你就运行测试SQL语句，看到其分析统计结果了。一般来讲，我们的SQL语句应该避免大表的全表扫描。 set autotrace off;关闭自动分析统计 五、集合函数 经常和group by一起使用 1. 集合函数列表 AVG （DISTINCT | ALL | N） 取平均值 COUNT （DISTINCT | ALL | N | expr | *

9、） 统计数量 MAX （DISTINCT | ALL | N） 取最大值 MIN （DISTINCT | ALL | N） 取最小值 SUM （DISTINCT | ALL | N） 取合计值 9 STDDEV （DISTINCT | ALL | N） 取偏差值,如果组里选择的内容都相同,结果为0 VARIANCE （DISTINCT | ALL | N） 取平方偏差值 2. 使用集合函数的语法 SELECT column, group_function FROM table WHERE condition GROUP BY group_by_expression HAVING group_c

10、ondition ORDER BY column;3. 使用count时的注意事项 SELECT COUNT（*）SELECT COUNT（常量） FROM table;都是统计表中记录数量,如果没有PK后者要好一些 SELECT COUNT（all 字段名） FROM table;SELECT COUNT（字段名） FROM table;不会统计为NULL的字段的数量 SUM,AVG时都会忽略为NULL的字段 4. 用group by时的限制条件 SELECT字段名不能随意, 要包含在GROUP BY的字段里 GROUP BY后ORDER BY时不能用位置符号和别名 限制GROUP BY的显

11、示结果, 用HAVING条件 5. 例子 select title,sum（salary） payroll from s_emp where title like VP% group by title having sum（salary）5000 order by sum（salary） desc;找出某表里字段重复的记录数, 并显示 select （duplicate field names） from table_name group by （list out fields） having count（*）1;（1） Group functions include nulls in cal

12、culations F （2） Using the having clause to exclude rows from a group calculation F Group function 都是忽略NULL值的 如果您要计算NULL值, 用NVL函数 Where语句在Group By前把结果集排除在外Having语句在Group By后把结果集排除在外 六、子查询 1. 查询语句可以嵌套 SELECT FROM （SELECT FROM表名1, 表名2, WHERE 条件） WHERE 条件2;2. 何处可用子查询?当查询条件是不确定的条件时 DML（insert, update,del

13、ete）语句里也可用子查询 HAVING里也可用子查询 3. 两个查询语句的结果可以做集合操作 并集UNION（去掉重复记录） 并集UNION ALL（不去掉重复记录） 10 差集MINUS, 交集INTERSECT 4. 子查询的注意事项 先执行括号里面的SQL语句，一层层到外面 内部查询只执行一次 如果里层的结果集返回多个，不能用= =等比较符要用IN. 5. 子查询的例子（1） select title,avg（salary） from s_emp group by title Having avg（salary） = （select min（avg（salary） from s_emp

14、 group by title）;找到最低平均工资的职位名称和工资 5. 子查询的例子（2） 子查询可以用父查询里的表名 这条SQL语句是对的:select cty_name from city where st_code in （select st_code from state where st_name=TENNESSEE and t_code=t_code）;父查询调用子查询只执行一次 6.取出结果集的80 到100的SQL语句 ORACLE处理每个结果集只有一个ROWNUM字段标明它的逻辑位置, 并且只能 用ROWNUM80。以下是经过分析后较好的两种ORACLE取得结果集80到10

15、0间的SQL语句 （ ID是唯一关键字的字段名 ） 语句写法：select * from （ （ select rownum as numrow, c.* from （ select field_name,. from table_name where 条件1 order by 条件2） c） where numrow 80 and numrow to_date（ start_hire_dateEnter value for start_hire_date : 2001-01-01 把绑定字符串和日期类型变量时，变量外面要加单引号 也可绑定变量来查询不同的字段名 输入变量值的时候不要加;等其它

16、符号 4. ACCEPT的语法和例子 ACCEPT variable datatype FORMAT PROMPT text HIDE variable 指变量名 datatype 指变量类型，如number,char等 format 指变量显示格 式 prompt text 可自定义弹出提示符的内容text hide 隐藏用户的输入符号 使用ACCEPT的例子：ACCEPT p_dname PROMPT Provide the department name: ACCEPT p_salary NUMBER PROMPT Salary amount:ACCEPT pswd CHAR PROMP

17、T Password: HIDE ACCEPT low_date date format PROMPT“Enter the low date range（）:” 4. DEFINE的语法和例子 DEFINE variable = value variable 指变量名 value 指变量值 定义好了变良值后, 执行绑定变量的SQL语句时不再提示输入变量 使用DEFINE的例子： DEFINE dname = sales DEFINE dname DEFINE dname = “sales” （CHAR） select name from dept where lower（name）=dnameNAME - sales UNDEFINE dname Symbol dname is UNDEFINED 5.