（总结篇 二 ）SQL语句

SQL语句使用

数据库查询语句

select

数据库操纵语句

insert

update

delete 删除表中的数据

数据定义语句

create

alter 改变系统参数

drop 删除对象 表、索引、序列号

rename 重命名对象

truncate 截断一个表

事物控制语句

commit 提交

rollback 回滚

数据控制语句

grant 授予用户访问某对象的权限

revoke 回收用户访问某对象的权限

一、数据库查询

(1)查询表中的所有数据

select * from TABLENAME;

(2)通过别名更改列标题查询

select  name, age employee_age, sal AS salary,deptno "Deptmentnumber" from TABLENAME;

(3)where 以特定条件查询

select * from TABLENAME where name='kczou';

(4)运算符

select sal * 12 from TABLENAME where job='MANAGER';

(5)去重

select distinct name from TABLENAME;

select distinct name,age from TABLEAME; --每一行数据不完全相同

(6)连接

select name || 'is a' || job from TABLENAME; 

——字符型

(7)LOWER  将字符串转换成小写

select LOWER('Hello World') from TABLENAME;

(8)UPPER 转换成大写

select UPPER('Hello World') from TABLENAME;

(9)INITCAP  将首字母大写

select INITCAP('hello world') from TABLENAME;

(10)CONCAT 连接两列的数据

select CONCAT('hello','world') from TABLENAME;

(11)SUBSTR 从字符串中获取一个子串

select SUBSTR('hello world',12) from TABLENAME;

(12)LENGTH 计算字符串长度

select LENGTH('hello world') from TABLENAME;

(13)INSTR 在字符串中搜索字符串，输出起始位置。也可指定开始和结束的位置进行搜索

select instr('hello world', 'world') from TABLENAME;

(14)LPAD|RPAD  左补/右补字符串 n代表输出结果需要的字符个数

select LPAD('hello',10,'*') from TABLENAME;

(15)TRIM 剪切字符串

select TRIM('l' from 'hello world') from TABLENAME;

(16)REPLACE 把源字符串替换成某个字符串

select REPLACE('hello world','world','hello') from TABLENAME;

——数字型

(17)ROUND 输出小数点后的指定位数(遵循四舍五入)

select ROUND(25.1990,2) from TABLENAME; 

(18)TRUNC 截断一个数字，保留小数点后的一定的位置，不遵循四舍五入

select TRUNC(25.1990,2) from TABLENAME;

select TRUNC(25.1990,-1) from TABLENAME;

(19)MOD 求余数

select MOD(1000,400) from TABLENAME;

——日期型

如果报错ORA-01843:无效的月份，可尝试执行alter session set nls_date_language='american' ;  可能因为含有MON这样的月份字样

(20)SYSDATE  系统当前日期

select SYSDATE from TABLENAME;

select SYSDATE + 7, SYSDATE - 7 from TABLENAME;

select TO_DATE('2007-06-12 10:00:00', 'yyyy-mm-dd hh24:mi:ss') from TABLENAME;

select SYSDATE + 20 / 24 from TABLENAME;

(21)MONTHS_BETWEEN 得到两个日期之间的月数

select MONTHS_BETWEEN('06-JUN-10', '06-JUN-09') from TABLENAME;

(22)ADD_MONTHS(date, n) 加上n个月

select ADD_MONTHS(SYSDATE, 3) from TABLENAME;

(23)NEXT_DAY(date, string) 输出该日期的下一个指定的日期

select NEXT_DAY(sysdate, 'Saturday') from TABLENAME;

(24)LAST_DAY 返回日期的最后一天的日期

select LAST_DAY(sysdate) from TABLENAME;

——空值处理

--空值是一类没有定义的、具有不确定性的值。在数据表中，这类值无法表示，更无法显示。

select * from TABLENAME where columnName = NULL;

select * from TABLENAME where columnName is NULL;

select * from TABLENAME where columnName is not NULL;

(25) NVL \ NVL2 使得空值可以运算

NVL(expr1, expr2) 如果expr1的值为空值NULL,则返回expr2的值，否则返回expr1的值。其中expr1 expr2的数据类型必须相同。

NVL2(expr1, expr2, expr3) 如果expr1为空，则返回表达式expr3的值，如果expr1不为空，则返回expr2的值。其中expr1为任何数据类型，而expr2、expr3为LONG数据类型以外的任何数据类型。

select NVL(sal, 0) from TABLENAME; 

(26)NULLIF 比较两个表达式，如果二者相等，则返回空值NULL，如果不相等则返回第一个表达式的值。

NULLIF(expr1, expr2)

select NULLIF(length(A), length(B)) from TABLENAME;

(27)COALESCE 返回该函数中第一个不为NULL的表达式

COALESCE(expr1, expr2,........,exprn)

select coalesce(sal, 1) from TABLENAME;

——逻辑判断

(28)case 

case expr WHEN comparison_expr1 THEN return_expr1

WHEN comparison_expr2 THEN return_expr2

WHEN comparison_exprn THEN return_expr3

ELSE else_expr

END

select seniority,sal,

<span style="white-space:pre"></span>case seniority when 'oneYear' THEN 1.20 * sal

<span style="white-space:pre"></span>when 'twoYears' THEN 1.30 * sal

<span style="white-space:pre"></span>when 'threeYears' THEN 1.40 * sal

<span style="white-space:pre"></span>else sal end "Last Salary"

order by seniority;

(29)decode

decode(col | expression,search1,result1

[,search2, result2,...,]

[,default])

判断search1与col或expression的值是否相等，若相等则返回result1

select seniority,sal,

<span style="white-space:pre"></span>decode(seniority,'oneYear',1.20 * sal,

<span style="white-space:pre"></span>'TwoYears', 1.30 * sal,

<span style="white-space:pre"></span>'ThreeYears', 1.40 * sal,

<span style="white-space:pre"></span>sal)

Last_Salary

from TABLENAME

order by seniority;

——分组函数

常用分组函数：AVG、SUM、MAX、MIN、COUNT

(30)AVG和SUM 

select AVG(sal) "平均工资", SUM(sal) "总工资" from TABLENAME;

(31)MAX和MIN

select MAX(sal) "MAX SAL",min(sal) "MIN SAL" from TABLENAME;

(32)COUNT

select count(*) from TABLENAME;

select count(distinct sal) from TABLENAME;

(33)GROUP BY 按条件分组

</pre><pre name="code" class="sql">select seniority,AVG(sal) "AVG SAL",SUM(sal) "SUM SAL" from TABLLENAME group by seniority;

select MAX(AVG(sal)),MIN(AVG(sal)) from TABLENAME group by <span style="font-family: Arial, Helvetica, sans-serif;">seniority;</span>

(34)order by 按条件排序

select seniority,AVG(sal) "AVG SAL",SUM(sal) "SUM SAL" from TABLLENAME group by seniority order by "SUM SAL" ;

(35) HAVING 分组函数不能使用where，而使用having

select seniority,AVG(sal) from TABLENAME HAVING AVG(sal) > 1990 group by seniority;

<pre name="code" class="sql">select seniority,AVG(sal) from TABLENAME HAVING AVG(sal) > 1990 group by seniority order by 2; 与order by AVG(sal)一样

二、数据库插入

insert into tablename(column1,column2,...,column)

values (value1,value2,...,value);

insert into tablename(column1,column2,...,column)

select column1,column2,...,column from another_tablename

where condition;

三、更新数据库

update tablename set column = value,... where condition;

四、删除语句

delete from tablename where condition;