2015-10-12
目录
一、连接命令
1.$ sqlplus
2.SQL> conn
二、SELECT语句
1.简单查询
2.WEHRE子句
3.别名
4.算术运算符+ - * /
5.DISTINCT关键字
6.连接运算符||
7.单行函数
7.1字符型
LOWER
UPPER
INITCAP
CONCAT
SUBSTR
INSTR
LENGTH
LPAD|RPAD
7.2数值型
TRIM
REPLACE
ROUND
TRUNC
MOD
7.3日期型
SYSDATE
MONTHS_BETWEEN
ADD_MONTHS
NEXT_DAY
LAST_DAY
8.空值函数
IS NULL
IS NOT NULL
NVL,NVL2
NULLIF
COALESCE
9.条件表达式
CASE..WHEN..END
DECODE
10.分组函数
AVG
SUM
MAX
MIN
COUNT
GROUP BY子句
HAVING 子句
三、INSERT语句
四、UPDATE语句
五、DELETE语句
一、连接命令
sqlplus 用户名/密码 [as 角色]
sqlplus /nolog 空用户，密码nolog登录
sqlplus sys/oracle as sysdba 用户sys，密码oracle，角色sysdba
sqlplus system/oracle 用户system，密码oracle
sqlplus scott/oracle 用户scott，密码oracle
SQL> conn 用户名/密码 [as 角色];
以sytem用户登录，解锁scott账户，并重置scott密码
SQL> conn system/oracle;
Connected.
SQL> alter user scott identified by oracle account unlock;
User altered.
再以scott用户登录
SQL> conn scott/oracle;
Connected.
二、SELECT语句
SELECT *|｛[DISTINCI] column | expression [alias],.....｝
FROM table;
1.简单查询
SQL> select * from dept;
切换到sytem用户登录，并查看scott用户的表dept
SQL> conn system/oracle;
Connected.
查看失败
SQL> select * from dept;
select * from dept
*
ERROR at line 1:
ORA-00942: table or view does not exist
查看成功
SQL> select * from scott.dept;
再切换回scott用户，查看dept表的某几列
SQL> conn scott/oracle
Connected.
SQL> select dname,loc from dept;
SQL> select dname,loc,deptno from dept;
2.where子句
SQL> select * from dept where loc = 'CHICAGO';
SQL> select * from dept where deptno < 30;
3.别名
双引号只作为列别名或表名，单引号是字符串。
SQL> select empno,ename,job,mgr,sal from emp;
SQL> select empno,ename employee_name,sal as salary,deptno "deptmentnumber" from emp;
4.算术运算符
SQL> select ename,sal*12 from emp where job = 'MANAGER';
SQL> select ename,sal*12+1000 from emp where job = 'MANAGER';
5.DISTINCI关键字
SQL> select job from emp;
SQL> select distinct job from emp;
6.连接运算符||
SQL> select ename,job,sal from emp;
SQL> select ename || ' is a '||job||' and 1month salary is :'|| sal as "the imployees's information" from emp;
7.单行函数
小写函数
LOWER(column|expression)
SQL> select lower('Structured Query Language') from dual;
大写函数
UPPER(column|expression)
SQL> select upper('Structured Query Language') from dual;
首字母大写函数
INITCAP(column|expression)
SQL> select initcap('structured query language') from dual;
字符串连接函数
CONCAT(column1|expression1,column2|expression2)
SQL> select concat ('Structured Query Language', ' is easy to learn!') from dual;
SQL> select concat(ename,hiredate) "emp_name,hiredate" from emp;
子串函数
SUBSTR(column|expression,m[,n])
SQL> select substr('structured query language',12) from dual;
子串位置函数
INSTR(column|expression,'string',[,m],[n])
SQL> select instr('structured query language','query') from dual;
字符串长度函数
LENGTH(column|expression)
SQL> select length('structured query language') from dual;
左|右填充函数
LPAD|RPAD(column|expression,n,'string')
SQL> select LPAD(sal,10,'*') from emp where sal > 1500;
SQL> select RPAD(sal,10,'*') from emp where sal > 1500;
字符截断函数
TRIM(leading|trailing|both,trim_character FROM 'source_string')
SQL> select trim('S' from 'SQL is an easy DatabaseS languageS') from dual;
替换函数
REPLACE(text,search_string,replacement_string)
SQL> select replace('sql is an easy Database language','sql','Structured Query Language') from dual;
四舍五入函数
ROUND(column|expression,n)
SQL> select round(32.1515,3) from dual;
SQL> select round(32.1515,-1) from dual;
数字截断函数
TRUNC(column|expression,n)
SQL> select trunc(32.1515,3) from dual;
SQL> select trunc(32.1515,-1) from dual;
求余函数
MOD(m,n)
SQL> select mod(1000,500) from dual;
SQL> select mod(100,500) from dual;
当前日期函数
SYSDATE
SQL> select sysdate from dual;
SQL> select sysdate + 7 ,sysdate - 7 from dual;
SQL> select sysdate + 20/25 from dual;
SQL> select sysdate +1/25 from dual;
SQL> select to_date('06-JUN-16') - sysdate from dual;
月差函数
MONTHS_BETWEEN(date,date)
SQL> select months_between('06-JUN-10','06-JUN-09') from dual;
SQL> select months_between('06-JUN-09','06-JUN-10') from dual;
加月函数
ADD_MONTHS(date,n)
SQL> select sysdate from dual;
SQL> select add_months(sysdate,5) from dual;
下周几日期函数
NEXT_DAY(date,'string')
SQL> select next_day(sysdate,'Saturday') from dual;
月末日期函数
LAST_DAY(date)
SQL> select last_day(sysdate) from dual;
空值处理函数IS NULL,IS NOT NULL,NVL,NVL2,NULLIF,COALESCE
SQL> col empno for 9999;
SQL> col sal for 9999;
SQL> col comm for 9999;
SQL> col mgr for 9999;
只有salsman的comm非空
SQL> select empno,ename,job,mgr,hiredate,sal,comm from emp order by job;
查询comm = null 匹配不到结果
SQL> select empno,ename,job,mgr,hiredate,sal,comm from emp where comm = null;
no rows selected
查询comm is null 的记录
SQL> select empno,ename,job,mgr,hiredate,sal,comm from emp where comm is null;
查询comm is not null 的记录
SQL> select empno,ename,job,mgr,hiredate,sal,comm from emp where comm is not null;
NVL(expr1,expr2)空值转换函数
如果expr1的值为NULL，则返回expr2的值，否则返回expr1的值。
使用NVL函数计算sal的查询
SQL> select ename,sal,comm,sal+nvl(comm,0) from emp;
不使用NVL函数计算sal+comm的查询
SQL> select ename,sal,comm,sal+comm from emp;
NVL2(expr1,expr2,expr3) 空值转换函数2
如果expr1的值为NULL，则返回expr3的值，否则返回expr2的值。
使用NVL2函数计算sal+comm的查询
SQL> select ename,sal,comm,nvl2(comm,sal+comm,sal) from emp;
NULLIF(expr1,expr2) 表达式比较函数
如果expr1=expr2，则返回NULL，否则返回expr1的值
SQL> select ename,length(ename) "expr1",job,length(job) "expr2",nullif(length(ename),length(job)) "comparision_result" from emp;
COALESCE(expr1,expr2,...,exprn)空值转换函数3
返回从expr1到exprn中第一个不为NULL的表达式exprx的值
SQL> select ename "Employ_name",job,coalesce(comm,1) "Comm" from emp order by job;
CASE表达式
双引号只作为列别名或表名，单引号是字符串。
SQL> select ename,job,sal,
2 case job when 'salesman' then 1.2*sal
3 when 'manager' then 1.3*sal
4 when 'analyst' then 1.5*sal
5 else sal end "Last Salary"
6 from emp
7 order by job;
select ename,job,sal,case job when 'salesman' then 1.2*sal when 'manager' then 1.3*sal when 'analyst' then 1.5*sal else sal end "Last Salary" from emp order by job;
DECODE函数
SQL> select ename,job,sal,
2 decode(job,'salesman',1.2*sal,
3 'manger',1.3*sal,
4 'analyst',1.5*sal,
5 sal)
6 Last_Salary
7 from emp
8 order by job;
select ename,job,sal,decode(job,'salesman',1.2*sal,'manger',1.3*sal,'analyst',1.5*sal,sal) Last_Salary from emp order by job;
分组函数
AVG、SUM、MAX、MIN、COUNT
SQL> select avg(sal) "avg_sal" ,sum(sal) "total_sal" from emp;
SQL> select avg(sal),sum(sal),max(sal),min(sal),count(sal) from emp;
SQL> select hiredate from emp;
SQL> select max(hiredate),min(hiredate) from emp;
max、min既可用于数值型数据，也可用于字符型数据。
SQL> select count(*) from emp;
SQL> select count(distinct job) from emp;
GROUP BY 子句
SQL> select job,avg(sal),sum(sal) from emp group by job;
ORDER BY 子句
SQL> select job,avg(sal),sum(sal) from emp group by job order by sum(sal);
分组函数嵌套
SQL> select max(avg(sal)),min(avg(sal)) from emp group by job;
HAVING子句
SQL> select job,avg(sal) from emp having avg(sal)>2000 group by job;
SQL> select job,avg(sal) from emp having avg(sal)>2000 group by job order by avg(sal);
二、INSERT语句
INSERT INTO tablename [(column [,column ...])]
VALUES (value [,value ...])
INSERT INTO tablename [(column [,column ...])]
SELECT column [,column ...]
FROM another_tablename
WHERE clause
SQL> select * from dept;
SQL> desc dept;
SQL> insert into dept (deptno,dname,loc) values (60,'manager','new york');
1 row created.
SQL> select * from dept;
SQL> insert into dept(deptno,dname) values(70,'audit');
1 row created.
SQL> select * from dept;
三、UPDATE语句
UPDATE tablename
SET column = value [, column = value, ...]
[WHERE condition];
SQL> select * from dept;
SQL> update dept set loc = 'new york' where deptno = 70;
1 row updated.
SQL> select * from dept;
四、DELETE语句
DELETE [FROM] tablename
[WHERE condition];
SQL> delete from dept where deptno = 60;
1 row deleted.
参考资料
[1] 林树泽.Oracle 11g R2 DBA操作指南[M].北京：清华大学出版社，2013
[2] Oracle数据库语句大全
[3] Oracle 复习知识点
[5] oracle SQL命令大全
[6] sql语句百例之Oracle
[7] oracle基本操作sql语句
[8] 浅谈从Oracle数据库中取出10条数据的Select语句与SQL Server、MySQL的区别