oracle小计

 

一、简介

1.1常用命令

·oracle并没有像其他数据库一样的命令来创建数据库，或者是很复杂的命令，一般我们使用：Databsse Configuration Assistant来创建数据库

·用Databsse Configuration Assistant维护监听器

·dba账户默认是不需要密码的

 

Oracle9i ：i是网络

Oracle10g ：g网格计算

服务：必须启动

1)OracleOraDb10g_home1TNSListener

2)OracleDBConsoleIDOC

两个cmd命令：sqlplus/sqlplusw（常用，但是命令无法修改）

常用用户名sys/system/scott

·select * from emp;

·set linesize 长度；

·set pagesize 长度；

因为sqlplusw无法修改，所以要用外部文件，文件名为a

·ed a;

编辑完成之后直接用@文件名，直接运行该文件中的sql语句，默认的文件名是.sql。

·@ a；

执行其他类型的文件名

·@d:\demo.txt

连接其他用户进行连接

·conn 用户名/密码 [AS SYSDBA]

·conn system/passw0rd

·conn scott/passw0rd

·conn sys/passw0rd as sysdba (否则无法登录）

在不同用户下，访问某一个用户的表的时候，需要加上用户名

·select * from scott.emp;

查询当前连接的用户是哪一个。

·show user;

查询当前数据库中所有表的名称

·select * from tab；

查询表结构

·desc 表明

·desc emp；

重复执行上一条命令

·/

1.2系统表分析

1.2.1雇员表

描述一个雇员的信息

 

雇员表（EMP)
NO	字段	类型	描述
1	EMPNO	NUMBER(4)	雇员编号
2	ENAME	VARCHAR2(10)	雇员姓名
3	JOB	VARCHAR2(9)	工作职位
4	MGR	NUMBER(4)	雇员领导编号
5	HIREDATE	DATE	雇佣日期
6	SAL	NUMBER(7,2)	工资
7	COMM	NUMBER(7,2)	佣金
8	DEPTNO	NUMBER(2)	部门编号

1.2.2部门表（dept)

描述一个部门的信息。

部门表（dept)
NO	字段	类型	描述
1	DEPTNO	NUMBER(2)	部门编号，唯一
2	DNAME	VARCHAR2(14)	部门名称
3	LOG	VARCJAR2(13)	部门位置

1.2.3工资等级表（SQLGRADE)

描述工资等级的表。

公司等级表（SQLGRADE)
NO	字段	类型	描述
1	GRADE	NUMBER	等级名称
2	LOSAL	NUMBER	此等级的最低工资
3	HISAL	NUMBER	此等级的最高工资

 

1.2.4奖金表（BONUS)

描述一个雇员的工资和奖金。

奖金表（BONUS)
NO	字段	类型	描述
1	ENAME	VARCHAR2(10)	雇员姓名
2	JOB	VARCHAR2(9)	雇员工作
3	SAL	NUMBER	雇员工资
4	COMM	NUMBER	雇员奖金

 

1.3常用sql语句

查询语句的格式

·SELECT {DISTINCT} */具体的列/别名 

from 表名称

{WHERE 条件(s)}

 

·select empno 编号,ename 姓名,job 工作 from emp;

 

去除重复列的查询

·SELECT DISTINCT job FROM emp;

查询 编号是7369的雇员，姓名是：SMITH,工作是：CLERK

注：需要实现这种功能需要使用“||”加入一些显示信息，将所有的固定的信息（不变的信息）用“’”括起来

·SELECT '编号是' || empno ||'的雇员，姓名是：'||ename||'，工作是'|| job From emp;

 

 

在查询中也可以进行四则运算

查询每个雇员的年薪

·SELECT ename ,sal * 12 FROM emp;

可以为sal*12起一个别名，但是要回避中文。

·SELECT ename,sal * 12 income FROM EMP;

 

查询工资大于1500的雇员信息

·SELECT * FROM emp where sal > 1500;

查询能得到奖金的雇员信息 IS NOT NULL

·SELECT * FROM emp where comm IS NOT NULL;

查询奖金为空的 IS NULL

·SELECT * FROM emp WHERE comm IS NULL;

查询基本工资大于1500，并且可以领取奖金的雇员。AND操作符

·select * from emp where sal > 1500 and comm is not null;

查询基本工资大于1500，或者可以领取奖金的雇员。OR操作符

·select * from emp where sal > 1500 or comm is not null;

NOT可以把真的结果变成假的，假的变成真的

查询基本工资小于1500，并且没有奖金的雇员。NOT操作符

·select * from emp where not（sal > 1500 and comm is not null）;

BETWEEB....AND.....表示查找范围（包含最大值和最小值）

·select * from emp where sal between 1500 and 3000;

查询日期的范围，日期表示的时候要加‘

·select * from emp where hiredate between to_date('1981-2-20','yyyy-MM-dd') and to_date('1983-10-05 ','yyyy-MM-dd');  

使用IN/NOT IN表示查询范围（可以用在数字，字符串）

·select * from emp where empno in (7521,7369);

·select * from emp where empno not in (7521,7369);

like操作符可以在任何类型上使用

查询雇员名字中第二个字符是m的所有人的姓名

·select * from emp where ename like '_M%';

查询雇员名字中有m的雇员信息

·select * from emp where ename like '%M%';

表示不等于信息“<>”或“!=”

·select * from emp where empno != 7369;

按照雇员工资进行排序

·select * from emp order by sal；

·select * from emp order by sal asc；

·select * from emp order by sal desc；

查询出10部门的所有雇员信息，查询的信息按照工资从高到低排序，如果工资相等，则按照雇佣日期由早到晚排序。

·此时存在两个排序条件，一个是降序，一个是升序。

·select * from emp where deptno = 10 order by sal desc ,hiredate asc;

 

1.4单行函数

每个数据库最大的不同是各自对函数的支持情况的不同。

语法：

function_name(colunm|expression,[arg1,arg2,....])

·function_name:函数名称

·colunm：数据库列明

·expression：字符串或计算表达式

·arg1，arg2：在函数中使用参数

单行函数的分类：

·字符函数：接受字符输入并且返回字符或数值。

·数值函数：接受数值输入并返回数值。

·日期函数：对日期型数据进行操作。

·转换函数：从一种数据类型转换为另一种数据类型

·通用函数：NVL函数，DECODE函数。

 

1.4.1字符函数

upper（）:将字符串转换为大写字符串

select * from emp where ename = upper('king');

lower（）：将字符串转换为小写字符串

initcap()：将单词开头字母大写

select initcap(ename) from emp;

 

字符串除了可以使用“||”连接之外，还可以使用concat（）函数进行连接操作。不如双竖线好用。

select concat('hello','world') from dual;

 

在字符函数中可以进行字符串截取、求出字符串的长度、进行指定内容的替换

·substr（）字符串截取

·length（）字符串长度

·replace（）内容替换

·select substr('hello',1,3) 截取字符串,length('hello') 字符串长度,replace('hello','l','x') from dual;

 

substr从0或者1开始效果是一样的。

查询雇员的姓名的后三个字符。

select substr(ename,length(ename)-3,3) from emp;

 

但是这种方式比较复杂，substr提供了一种倒着截取的机制，只要输入的是负数即可

select substr(ename,-1,3) from emp;

 

1.4.2数值函数

·四舍五入：round（）

·截断小数点：trunc（）

·取余（取摸）：mod（）

·select round(111.511) from dual;

结果为112

保留两位小数

·select round(111.511,2) from dual;

结果为111.51

可以对整数进行四舍五入

·select round(161.511,-2) from dual;

结果为200

trunc（）和round（）的不同的地方是不会保存任何小数位数，也不是四舍五入，小数全部抛弃

select trunc(161.511) from dual;

结果为161

select trunc(161.511,2) from dual;

结果为161.51

select trunc(161.511,-2) from dual;

结果为100

mod（）取余函数

1.4.3日期函数

oracle提供了很多与日期相关的函数，包括日期的加减，但是在日期加减的时候有一些规律。

·日期-数字=日期

·日期+数字=日期

·日期-日期=数字（天数）

范例：显示10部门雇员进入公司的星期数。

求出当前日期

·select sysdate from dual;

·select ename,round((sysdate-hiredate)/7) from emp;

 

·months_between():求出给定日期范围内的月数

·add_months()：在指定的日期上加上指定的月数，求出之后的日期

·next_day()：下一个的今天是哪个日期

例如下一个星期一是哪天

·last_day()：求出给定日期的最后一天日期

本月的最后一天是哪天

select ename,months_between(sysdate,hiredate) from emp;

 

1.4.4转化函数

·to_char：转换为字符串

·to_number:转换为数字

·to_data:转换为日期

1）to_char

要求将雇佣日期的年月日分开，所以使用to_char()函数进行拆分。

·年：用yyyy表示

·月：用mm表示

·日：用dd表示

·select empno,ename,to_char(hiredate,'yyyy') from emp;

 

使用to_char()将日期转换为想要的格式

select empno,ename,to_char(hiredate,'yyyy/mm/dd') from emp;

 

去掉月份中那些自动生成的0

select empno,ename,to_char(hiredate,'fmyyyy/mm/dd') from emp;

 

将工资数字加入符号，分割太长的数字。

·9表示一位数字

select empno,ename,to_char(sal,'9,999,999') from emp;

 

为工资加上符号

·$表示美元

·L表示本地符号

select empno,ename,to_char(sal,'L9,999,999') from emp;

 

2）to_number将字符串变为数字

将两个字符串相加

·select to_number('123')+to_number('111') from dual

 

3）to_date

将字符串变为date类型的数据

将一个字符串变为数字日期类型

·select to_date('2009/02/16','yyyy-mm-dd') from dual

 

4）nul函数 

将null值转换为指定的函数

·select empno,ename,nvl(comm,0) from emp;

 

5）decode（）函数

类似if。。。elseif。。。else。。。

将工作名称换为中文

·select empno,ename,decode(job,'CLERK','业务员','MANAGER','经理','工人') from emp;

*其中工人为默认值

 

1.5其他sql语句

MLDN魔乐科技_Oracle课堂07

转载于:https://www.cnblogs.com/alexlee73/archive/2011/11/29/2268012.html