oralce+基础知识.docx
- 文档编号:19346011
- 上传时间:2023-04-24
- 格式:DOCX
- 页数:43
- 大小:36.95KB
oralce+基础知识.docx
《oralce+基础知识.docx》由会员分享,可在线阅读,更多相关《oralce+基础知识.docx(43页珍藏版)》请在冰豆网上搜索。
oralce+基础知识
一、选择行
1.简单的SELECT语句
SELECT字段名1[AS]'字段名1解释'FROMtable;
2.处理NULL
NVL函数可把NULL转换成其它类型的符号
编程技巧:
NVL函数在多条件模糊查询的时候比较有用
NVL函数可返回多种数据类型:
返回日期NVL(start_date,'2002-02-01')
返回字符串NVL(title,'notitle')
返回数字NVL(salary,1000)
3.使用SQL*PLUS
(1)
SQL>desctable;显示表结构
SQL>select*fromtab;查看用户下所有的表
SQL>setpauseon;可以使大量结果集在用户按“Enter”(回车)后翻页
SQL>setpagesize100;设定SQL语句返回结果集一页的行数100,默认值是14
SQL>setlinesize100;设定SQL语句返回结果集一行的宽度100,默认值是80
4.SQL*PLUS里规定字段的显示格式
规定数字的显示格式
SQL>column字段名format99999999999;
SQL>column字段名format999,999,999,999;
SQL>column字段名formata数字[word_wrapped];
规定long字符的显示宽度
SQL>setlong200;
规定字段名的显示内容
SQL>column字段名heading'字段名显示内容';
SQL>setheadingoff;查询时不显示字段名称
规定字段的对齐方向
SQL>column字段名justify[left|right|center];
清除字段的格式
SQL>column字段名clear;
5.SQL*PLUS里规定字段的显示格式例子
SQL>columnlast_nameheading'Employee|Name'formata15;
SQL>columnsalaryjustifyrightformat$99,999.99;
SQL>columnstart_dateformata10null'NotHired';
说明:
如果start_date为null,显示字符串'NotHired'
6.判断题(T/F)
(1).SQLcommandarealwaysheldinsqlbuffer.[T]
(2).SQL*PLUScommandassitwithquerydata.[T]
5
J2EE@zxw
SQL*PLUS命令只控制SELECT结果集的显示格式及控制文件.只有SQL命令能访问数据库.
二、限制选择行
1.按指定的规则排序
SELECTexprFROMtable[ORDERBY{column,expr}[ASC|DESC]];
默认的排序是ASC升序(由小到大)
还可以ORDERBY字段名的位置[1]|[2]ASC|DESC;
2.用WHERE限制选择行
(1)
比较操作符=><>=<=!
=<>^=与NULL比较不能用上面的比较操作符ANYSOME
ALL
SQL操作符BETWEEN…AND…INLIKEISNULL
NOTBETWEEN…AND…NOTINNOTLIKEISNOTNULL
逻辑操作符ANDORNOT
3.用WHERE限制选择行
(2)
比较顺序(可以用括号改变它们的顺序)
(1).=<>>=<=inlikeisnullbetween
(2).and
(3).Or
4.LIKE操作
%零到任意多个字符_一个字符
例如:
字段名like'M%'字段名like'%m%'字段名like'job_'
如果要找含下划线的字符,要加反斜线例如:
字段名like'%X/_Y%'escape'/'
5.日期字段的比较
举例:
日期字段betweento_date('2001-12-12','YYYY-MM-DD')andto_date('2002-02-01','YYYY-MM-DD')
日期字段>to_date('2001-12-12','YYYY-MM-DD')and日期字段<=
to_date('2002-02-01','YYYY-MM-DD');
6.不能用到索引的比较操作符
ISNULL
ISNOTNULL
LIKE'%m%'
三、单行函数
1.数字函数
ABS取绝对值POWER乘方LN10为底数取0
SQRT平方根EXPe的n次乘方LOG(m,n)m为底数n取0
数学运算函数:
ACOSATANATAN2COSCOSHSIGNSINSINHTANTANH
CEIL大于或等于取整数
FLOOR小于或等于取整数
MOD取余数
ROUND(n,m)按m的位数取四舍五入值如果round(日期):
中午12以后将是明天的日期.
round(sysdate,'Y')是年的第一天
TRUNC(n,m)按m的位数取小数点后的数值如果trunc(日期),确省的是去掉时间
6
J2EE@zxw
2.字符函数
CHR按数据库的字符集由数字返回字符
CONCAT(c1,c2)把两个字符c1,c2组合成一个字符,和||相同
REPLACE(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字符串替换左边的空位
RPAD(c1,n,c2)
3.日期函数
ADD_MONTHS(d,n)日期值加n月
LAST_DAY返回当月的最后一天的日期
MONTHS_BETWEEN(d1,d2)两个日期值间的月份,d1 NEXT_DAY返回日期值下一天的日期 SYSDATE当前的系统时间 DUAL是SYS用户下一个空表,它只有一个字段dummy 4.转换函数 (1) TO_CHAR(date,'日期显示格式') TO_CHAR(number)用于显示或报表的格式对齐 TO_DATE(char,'日期显示格式') TO_LOB把long字段转换成lob字段 TO_NUMBER(char)用于计算或者比较大小 4.转换函数 (2) to_date里日期显示格式 YYYY年YEARYYYYYY Q季度 MM月MONTHMON W星期(weekofmonth)WW,IW(weekofyear) (说明: 周计是按ISO标准,从1月1日的星期数到后面七天为一周,不一定是从周一到周日) DD日DAYDY HH24小时HH12HH MI分钟 SS秒 如果想固定日期的显示格式可以在数据库的参数文件initorasid.ora里新写一行参数 NLS_DATE_FORMAT=yyyy-mm-dd hh24: mi: ss可以在UNIX环境变量或者NT的注册表里的设置NLS_DATE_FORMAT=yyyy-mm-dd 7 J2EE@zxw hh24: mi: ss 4.转换函数(3) 如果想固定日期的显示格式可以用altersession命令改变 SQL>altersessionsetnls_date_format='yyyy-mm-ddhh24: mi: ss'; 它的作用顺序如下: initializationparameter Environmentvariable ALTERSESSIONcommand 4.转换函数(4) to_char(number)里数字显示格式 9数字位 0数字前面补0to_char(-1200,'00000.00') .小数点的位置 标记位置的逗号用在数字显示格式的左边 L根据数据库字符集加货币符号to_char(-1200,'L9999.99') B把数字0显示为空格,用在数字显示格式的右边 MI右边的负数标记to_char(-1200,'9999.99MI') PR括起来的负数to_char(-1200,'9999.99PR') EEEE用指数方式表示to_char(-1200,'9999.99EEEE') 5.输入字符,返回数字的函数 instr(c1,c2)字符c2出现在c1的位置,不出现,返回0,常用于模糊查询 length按数据库的字符集,算出字符c的长度,跟数据库的字符集有关,一个汉字长度为1 6.有逻辑比较的函数NVL(EXPR1,EXPR2)函数 解释: IFEXPR1=NULLRETURNEXPR2 ELSERETURNEXPR1 DECODE(AA0V10R10V20R2....)函数 解释: IFAA=V1THENRETURNR1 IFAA=V2THENRETURNR2 ..… ELSE RETURNNULL 举例: decode(id,1,'deptsale',2,'depttech') 四、从多个表里选取数据记录 1.数据表间的连接 简单的连接语法: SELECT字段名1,字段名2,……FROM表名1,[表名2,……] WHERE表名1.字段名=表名2.字段名[AND……]; SELECT字段名1,字段名2,……FROM表名1,[表名2,……] WHERE表名1.字段名=表名2.字段名(+)[AND……]; 有(+)号的字段位置自动补空值 连接的分类: 等于的连接= 不等于的连接! =BETWEEN…AND…IN注意IN和OR不能一起用 8 J2EE@zxw 外连接有一个字段名(+),没有满足的条件补空值 自连接同一个表自己跟自己连接例如找重复记录 2.数据表间的连接例子 删除table_name表里字段名email重复的记录: SQL>deletefromtable_namet1 wheret1.rowid> (selectmin(rowid)fromtable_namet2 wheret1.email=t2.email groupbyemail havingcount(email)>1); 找到手机用户的服务区域: SQL>selecta.handphoneno,nvl(c.name,'null'),a.totalscore fromtopscorea,chargeoperatorcc,chargeoperatorinfoc wheresubstr(a.handphoneno,1,7)=cc.hpnohead(+) andcc.chargetype=c.chargetype(+) orderbya.totalscoredesc; 3.数据表间的连接技巧 连接N个表,需要N-1个连接操作 被连接的表最好建一个单字符的别名,字段名前加上这个单字符的别名 BETWEEN..AND..比用>=AND<=要好 连接操作的字段名上最好要有索引 连接操作的字段最好用整数数字类型 有外连接时,不能用OR或IN的比较操作 4.如何分析和执行SQL语句 写多表连接SQL语句时要知道它的分析执行计划的情况. Sys用户下运行@/ORACLE_HOME/sqlplus/admin/plustrce.sql产生plustrace角色 Sys用户下把此角色赋予一般用户SQL>grantplustraceto&username; 一般用户下运行@/ORACLE_HOME/rdbms/admin/utlxplan.sql 产生plan_table SQL>settimeon;说明: 打开时间显示 SQL>setautotraceon;说明: 打开自动分析统计,并显示SQL语句的运行结果 SQL>setautotracetraceonly;说明: 打开自动分析统计,不显示SQL语句的运行结果 接下来你就运行测试SQL语句,看到其分析统计结果了。 一般来讲,我们的SQL语句应该避免大表的全表扫描。 SQL>setautotraceoff;说明: 关闭自动分析统计 五、集合函数 经常和groupby一起使用 1.集合函数列表 AVG(DISTINCT|ALL|N)取平均值 COUNT(DISTINCT|ALL|N|expr|*)统计数量 MAX(DISTINCT|ALL|N)取最大值 MIN(DISTINCT|ALL|N)取最小值 SUM(DISTINCT|ALL|N)取合计值 9 J2EE@zxw STDDEV(DISTINCT|ALL|N)取偏差值,如果组里选择的内容都相同,结果为0 VARIANCE(DISTINCT|ALL|N)取平方偏差值 2.使用集合函数的语法 SELECTcolumn,group_functionFROMtable WHEREconditionGROUPBYgroup_by_expression HAVINGgroup_conditionORDERBYcolumn; 3.使用count时的注意事项 SELECTCOUNT(*)FROMtable; SELECTCOUNT(常量)FROMtable; 都是统计表中记录数量,如果没有PK后者要好一些 SELECTCOUNT(all字段名)FROMtable; SELECTCOUNT(字段名)FROMtable; 不会统计为NULL的字段的数量 SUM,AVG时都会忽略为NULL的字段 4.用groupby时的限制条件 SELECT字段名不能随意,要包含在GROUPBY的字段里 GROUPBY后ORDERBY时不能用位置符号和别名 限制GROUPBY的显示结果,用HAVING条件 5.例子 SQL>selecttitle,sum(salary)payrollfroms_emp wheretitlelike'VP%'groupbytitle havingsum(salary)>5000orderbysum(salary)desc; 找出某表里字段重复的记录数,并显示 SQL>select(duplicatefieldnames)fromtable_name groupby(listoutfields)havingcount(*)>1; 6.判断题(T/F) (1)Groupfunctionsincludenullsincalculations[F] (2)Usingthehavingclausetoexcluderowsfromagroupcalculation[F] 解释: Groupfunction都是忽略NULL值的如果您要计算NULL值,用NVL函数 Where语句在GroupBy前把结果集排除在外Having语句在GroupBy后把结果集排除在外 六、子查询 1.查询语句可以嵌套 例如: SELECT……FROM(SELECT……FROM表名1,[表名2,……]WHERE条件)WHERE条件2; 2.何处可用子查询? 当查询条件是不确定的条件时 DML(insert,update,delete)语句里也可用子查询 HAVING里也可用子查询 3.两个查询语句的结果可以做集合操作 例如: 并集UNION(去掉重复记录) 并集UNIONALL(不去掉重复记录) 10 J2EE@zxw 差集MINUS, 交集INTERSECT 4.子查询的注意事项 先执行括号里面的SQL语句,一层层到外面 内部查询只执行一次 如果里层的结果集返回多个,不能用=><>=<=等比较符要用IN. 5.子查询的例子 (1) SQL>selecttitle,avg(salary)froms_emp groupbytitleHavingavg(salary)= (selectmin(avg(salary))froms_emp groupbytitle); 找到最低平均工资的职位名称和工资 5.子查询的例子 (2) 子查询可以用父查询里的表名 这条SQL语句是对的: SQL>selectcty_namefromcitywherest_codein (selectst_codefromstatewherest_name='TENNESSEE'and t_code=t_code); 说明: 父查询调用子查询只执行一次. 6.取出结果集的80到100的SQL语句 ORACLE处理每个结果集只有一个ROWNUM字段标明它的逻辑位置, 并且只能用ROWNUM<100,不能用ROWNUM>80。 以下是经过分析后较好的两种ORACLE取得结果集80到100间的SQL语句 (ID是唯一关键字的字段名) 语句写法: SQL>select*from( (selectrownumasnumrow,c.*from( select[field_name,...]fromtable_namewhere条件1orderby条件2)c) wherenumrow>80andnumrow<=100) orderby条件3; 七、在执行SQL语句时绑定变量 1.接收和定义变量的SQL*PLUS命令 ACCEPT DEFINEUNDEFINE & 2.绑定变量SQL语句的例子 (1) SQL>selectid,last_name,salaryfroms_empwheredept_id=&department_number; Entervaluefordepartment_number: 10 old1: selectid,last_name,salaryfroms_empwheredept_id=&department_number; new1: selectid,last_name,salaryfroms_empwheredept_id=10 SQL>SETVERIFYOFF|ON;可以关闭和打开提示确认信息old1和new1的显示. 3.绑定变量SQL语句的例子 (2) SQL>selectid,last_name,salary froms_emp wheretitle='&job_title'; Entervalueforjob_title: StockClerk 11 J2EE@zxw SQL>selectid,last_name,salary froms_emp wherehiredate>to_date('&start_hire_date','YYYY-MM-DD'); Entervalueforstart_hire_date: 2001-01-01 把绑定字符串和日期类型变量时,变量外面要加单引号 也可绑定变量来查询不同的字段名 输入变量值的时候不要加;等其它符号 4.ACCEPT的语法和例子 SQL>ACCEPTvariable[datatype][FORMAT][PROMPTtext][HIDE] 说明: variable指变量名datatype指变量类型,如number,char等format指变量显示格 式prompttext可自定义弹出提示符的内容texthide隐藏用户的输入符号 使用ACCEPT的例子: ACCEPTp_dnamePROMPT'Providethedepartmentname: ' ACCEPTp_salaryNUMBERPROMPT'Salaryamount: ' ACCEPTpswdCHARPROMPT'Password: 'HIDE ACCEPTlow_datedateformat'YYYY-MM-DD'PROMPT“Enterthelowdaterange('YYYY-MM-DD'): ” 4.DEFINE的语法和例子 SQL>DEFINEvariable=value 说明: variable指变量名value指变量值 定义好了变良值后,执行绑定变量的SQL语句时不再提示输入变量 使用DEFINE的例子: SQL>DEFINEdname=sales SQL>DEFINEdname DEFINEdname=“sales”(CHAR) SQL>selectnamefromdeptwherelower(name)='&dname'; NAME ------------------------- sales sales SQL>UNDEFINEdname SQL>DEFINEdname SymboldnameisU
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oralce 基础知识