Oracle----SQL
一、SQL语句书写标准
不区分大小写
可以换行书写
关键词不能简写或分割开跨行写
通常一个子句一行
排版增加可读性
开发工具里，结束语；是可选的。执行多个语句用；分割开来
SQL*PLUS里必须使用；表示一个语句结束
1.算数表达式
＋ － ＊ ／
2.空值
null是一个不确定、未赋值、未知的值
null不等于零和空值
null可以看成无穷大
注意：空值算数操作符为空
3.列别名
重命名列标题
有益运算
直接跟在列后，可在列和别名之间用as 增强可读性
包含空格、特殊字符，区分大小写必须用双引号引起来
示例：
1 SQL> select ename||'''s salary is '||sal from emp; 2 3 ENAME||'''SSALARYIS'||SAL 4 --------------------------------------------------------------- 5 SMITH's salary is 800 6 ALLEN's salary is 1600 7 WARD's salary is 1250 8 JONES's salary is 2975 9 MARTIN's salary is 1250 10 BLAKE's salary is 2850 11 CLARK's salary is 2450 12 SCOTT's salary is 3000 13 KING's salary is 5000 14 TURNER's salary is 1500 15 ADAMS's salary is 1100 16 17 ENAME||'''SSALARYIS'||SAL 18 --------------------------------------------------------------- 19 JAMES's salary is 950 20 FORD's salary is 3000 21 MILLER's salary is 1300 22 23 14 rows selected. 24
以上为字面字符串实例
字面字符串是包含在select 中的字符，数字，日期值
字符，日期类型必须使用单引号
每行每次返回一行字符串
单引号转义符
指定引号界定符
选择任何界定符
增加可读性和可用性
1 select department_name||q'[ Department's Manager Id: ]'||manager_id 2 as "Department and Manager" 3 from departments;
查看表结构
1 desc table_name;
4.限制语句
限制行的查询使用where子句
where子句放在from子句后
示例
5.字符和日期
设置显示格式
注意：只在当前会话生效
字符，日期使用单引号括起来
字符区分大小写，日期格式敏感
默认日期格式为 DD-MON-RR
6比较操作符
使用like匹配时
1 “_”表示单个字符 2 “%”表示任意字符
亦可自定义转义符
7.逻辑条件
![1[4]](https://images2018.cnblogs.com/blog/1339881/201803/1339881-20180330170336309-68462353.png "1[4]")
8.规则优先级
![1[6]](https://images2018.cnblogs.com/blog/1339881/201803/1339881-20180330170338624-1946237530.png "1[6]")
注意：OR的优先级最低，使用时要加括号
9.order by 子句
排序检索行的order by 子句
order by 子句放在select语句末尾
asc 升序
desc降序
示例
10.变量替换
• Use substitution variables to
使用替代变量:
– Temporarily store values with single-ampersand ( &) and
double-ampersand ( &&) substitution
临时变量用& ，调用替代变量用&&
• Use substitution variables to supplement the following
替代变量可用在:
– WHERE conditions ---- where子句
– ORDER BY clauses ---- order by子句
– Column expressions ----列表达式
– Table names ---- 表名
– Entire SELECT statements ----整个语句
示例
1 2 SQL> select ename,sal from emp where deptno=# 3 Enter value for num: 20 4 old 1: select ename,sal from emp where deptno=&num 5 new 1: select ename,sal from emp where deptno=20 6 7 ENAME SAL 8 ---------- ---------- 9 SMITH 800 10 JONES 2975 11 SCOTT 3000 12 ADAMS 1100 13 FORD 3000 14
注意：遇到字符和日期时，使用变量要用单引号引起来
1 SQL> select ename,sal from emp where ename=&name; 2 Enter value for name: 'SMITH' 3 old 1: select ename,sal from emp where ename=&name 4 new 1: select ename,sal from emp where ename='SMITH' 5 6 ENAME SAL 7 ---------- ---------- 8 SMITH 800 9
重用替代变量使用&&
define命令
• Use the DEFINE command to create and assign a value to
a variable
DEFINE命令创建和分配一个值到一个变量.
• Use the UNDEFINE command to remove a variable.
UNDEFINE 取消已定义的变量
1 define employee_num = 200; 2 select employee_id,last_name,salary,department_id 3 from employees 4 where employee_id=&employee_num; 5 undefine employee_num;
verify 命令
Use the VERIFY command to toggle the display of the
substitution variable, both before and after SQL Developer
replaces substitution variables with values:
变量替换的过程新旧值是否显示用:set verify on|off
1 HR@prod>select employee_id,last_name,salary 2 2 from employees 3 3 where employee_id = &employee_num; 4 Enter value for employee_num: 200 5 old 3: where employee_id = &employee_num 6 new 3: where employee_id = 200 7 EMPLOYEE_ID LAST_NAME SALARY 8 ------------------ --------------------- ---------- 9 200 Whalen 4400
11.单行函数
使用函数为了操作数据
将输入的变量处理，每行返回一个结果
处理返回的每一行
Return one result per row 一行返回一个结果
•May modify the data type 可以转化数据类型
•Can be nested 能嵌套使用
Accept arguments that can be a column or an expression
传入的变量可以是列的值，也可以是表达式
![1[8]](https://images2018.cnblogs.com/blog/1339881/201803/1339881-20180330170339995-991855167.png "1[8]")
![1[10]](https://images2018.cnblogs.com/blog/1339881/201803/1339881-20180330170343895-884717373.png "1[10]")
类型
![1[12]](https://images2018.cnblogs.com/blog/1339881/201803/1339881-20180330170346385-647141102.png "1[12]")
![1[14]](https://images2018.cnblogs.com/blog/1339881/201803/1339881-20180330170348429-1852522419.png "1[14]")
11.1 大小写函数
![1[16]](https://images2018.cnblogs.com/blog/1339881/201803/1339881-20180330170351213-2105010890.png "1[16]")
11.2字符处理函数
![1[18]](https://images2018.cnblogs.com/blog/1339881/201803/1339881-20180330170354093-2093766405.png "1[18]")
11.3数字函数
• ROUND: Rounds value to a specified decimal
按照指定的小数位四舍五入
• TRUNC: Truncates value to a specified decimal
按照指定的小数位截断数据
• MOD : Returns remainder of division
两数相除，返回余数
![1[20]](https://images2018.cnblogs.com/blog/1339881/201803/1339881-20180330170356442-966588399.png "1[20]")
1 select round(45.976,2),trunc(45.976,0),mod(1800,400) from dual; 2 ROUND(45.976,2) TRUNC(45.976,0) MOD(1800,400) 3 --------------- --------------- ------------- 4 45.98 45 200
12.日期
oracle中默认的日期格式为“DD-MON-RR”
可用以下命令修改
sysdate函数
日期运算
现有日期加上或减去一个数值，可以得到一个日期.
两个日期相减，可以得到两个日期间的天数.
将值除以24，可以得到天数.
![1[2]](https://images2018.cnblogs.com/blog/1339881/201803/1339881-20180330170358115-263071773.png "1[2]")
![1[4]](https://images2018.cnblogs.com/blog/1339881/201803/1339881-20180330170409312-48047491.png "1[4]")
next_day 下星期几是几月几号
日期的舍入与截断
SYSDATE = '25-JUL-03' :
![1[6]](https://images2018.cnblogs.com/blog/1339881/201803/1339881-20180330170413368-1429368286.png "1[6]")
13.转换函数
![1[8]](https://images2018.cnblogs.com/blog/1339881/201803/1339881-20180330170415578-790070610.png "1[8]")
13.1隐式转换
在表达式中oracle能自动转换的
![1[10]](https://images2018.cnblogs.com/blog/1339881/201803/1339881-20180330170417097-6396762.png "1[10]")
对表达式求值Oracle服务器能自动转换的
![1[12]](https://images2018.cnblogs.com/blog/1339881/201803/1339881-20180330170419153-971659473.png "1[12]")
13.2显示转换
![1[14]](https://images2018.cnblogs.com/blog/1339881/201803/1339881-20180330170421824-774849552.png "1[14]")
日期处理函数
格式
to_char(date,'format_model')
注意：
1.必须用单引号括起来
2.区分大小写
3.可用有效日期元素
4.能除去填充的空格或前置的零
5.用逗号与日期隔开
![1[16]](https://images2018.cnblogs.com/blog/1339881/201803/1339881-20180330170424908-1816789084.png "1[16]")
![1[18]](https://images2018.cnblogs.com/blog/1339881/201803/1339881-20180330170427901-1878677799.png "1[18]")
日期元素
时间元素
![1[20]](https://images2018.cnblogs.com/blog/1339881/201803/1339881-20180330170454435-679163773.png "1[20]"){kind=small}
加入的字符串用双引号括起来
![1[22]](https://images2018.cnblogs.com/blog/1339881/201803/1339881-20180330170459425-1234396325.png "1[22]"){kind=small}
序数
![1[24]](https://images2018.cnblogs.com/blog/1339881/201803/1339881-20180330170501304-2111139741.png "1[24]"){kind=small}
14.to_char处理数字
![1[26]](https://images2018.cnblogs.com/blog/1339881/201803/1339881-20180330170502140-1508050749.png "1[26]"){kind=small}
![1[28]](https://images2018.cnblogs.com/blog/1339881/201803/1339881-20180330170503028-217930620.png "1[28]")
![1[30]](https://images2018.cnblogs.com/blog/1339881/201803/1339881-20180330170504762-1321272621.png "1[30]")
14.1to_number与to_date
• Convert a character string to a number format using the
TO_NUMBER function:
转字符为数字用to_number函数![1[36]](https://images2018.cnblogs.com/blog/1339881/201803/1339881-20180330170506028-639124653.png "1[36]"){kind=small}
• Convert a character string to a date format using the
TO_DATE function:
转字符为日期用to_date函数
确认表信息
• 查询用户的所有表名信息
1 select * from tab; 2 select table_name from user_tables; 3 select object_name from user_objects 4 where object_type='TABLE';
• 查看列名信息
查看主外键
1 SQL> select table_name,constraint_name,constraint_type,r_constraint_name from user_constraints;
TABLE_NAME CONSTRAINT_NAME C R_CONSTRAINT_NAME
------------------------------ ------------------------------ - ------------------------------
DEPT PK_DEPT P
EMP PK_EMP P
EMP FK_DEPTNO R PK_DEPT
查看约束表
1 SQL> desc user_constraints 2 Name Null? Type 3 ----------------------------------------- -------- ---------------------------- 4 OWNER VARCHAR2(120) 5 CONSTRAINT_NAME NOT NULL VARCHAR2(30) 6 CONSTRAINT_TYPE VARCHAR2(1) 7 TABLE_NAME NOT NULL VARCHAR2(30) 8 SEARCH_CONDITION LONG 9 R_OWNER VARCHAR2(120) 10 R_CONSTRAINT_NAME VARCHAR2(30) 11 DELETE_RULE VARCHAR2(9) 12 STATUS VARCHAR2(8) 13 DEFERRABLE VARCHAR2(14) 14 DEFERRED VARCHAR2(9) 15 VALIDATED VARCHAR2(13) 16 GENERATED VARCHAR2(14) 17 BAD VARCHAR2(3) 18 RELY VARCHAR2(4) 19 LAST_CHANGE DATE 20 INDEX_OWNER VARCHAR2(30) 21 INDEX_NAME VARCHAR2(30) 22 INVALID VARCHAR2(7) 23 VIEW_RELATED VARCHAR2(14) 24
purge recyclebin
