一、简介
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;
执行其他类型的文件名
连接其他用户进行连接
·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