oracle（第一天）
浏览数：24 /
时间：2015年06月12日
======数值类型定义======
create table test(
c1 number,
c2 number(6),
c3 number(4,3),
c4 number(3,-3),
c5 number(2,4)
);
insert into test values(10,999999,9.999,999000,0.0099);
==显示方式与隐式方式
隐式方式：指定需要都要插值的列
显示方式：不插值的列指定为null
===定义缺省值default===
1、缺省值的数据类型必须匹配列的数据类型
2、有效的缺省值为文字值、表达式、sql函数、sysdate、user等。
3、无效的缺省值为另一个列的列名或伪列
4、default可以用于insert语句、update语句
drop table test purge;
create table test(
c1 number default 1,
c2 number
);
insert into test(c2)values(2);
insert into test values(default,3);
insert into test values(4,4);
update test set c1=default where c1=4;
select * from test;
===字符串类型===
insert into test values(‘abc‘,‘abc‘);
select length(c1),length(c2) from test;
===varchar2和char===
1、varchar2必须定义长度，最大长度4000字节；char可以不定义长度。缺省为1，最大长度2000字节
2、varchar2按字符串的实际长度存，char按定义长度存
3、列的取值是定长，定义成char。列的取值不固定。定义为varchar2
===日期类型===
sysdate是一个系统函数，返回当前系统时间
create table c2(
c1 date
);
insert into c2 values(‘01-1月-08‘);
===列别名===
--给列起别名（列别名）能够改变一个列、表达式的标识
--在原名和别名之间可以使用as关键字
--若列别名中包含空格或希望大小写敏感，用双引号括起来。
==拼接运算符||==
==消除重复行：distinct==
注意：distinct必须( 只能 )跟在select后边
/*
*SQL命令可以分成以下几种类型：
--DDL命令(Data Definition Language)
称为数据定义语言，是用于创建，修改，删除表结构的
DDL对数据结构起作用。
create 数据库对象的创建
alter 修改数据库对象
drop 删除数据库对象
truncate 清空表数据
--DML命令(Data Manipulation Language)
被称为数据操作语言，是用于向表中增删改数据的
insert 插入操作
update 更新操作
delete 删除操作
--DQL命令（Data Query Language）
被称为数据查询语言，是用于从表查询数据的
select 查询操作
--TCL命令（Transaction Control Language）
被称为事务控制语言，是用于确认或撤销DML操作的
commit 提交数据
rollback 数据回滚
savepoint 保存点
--DCL命令（Data Control Language）
被称为数据控制语言，是用于给用户授权或取消授权的
（DBA使用比较多）
*/
select * from dual;
借助函数nvl(p1,p2)实现，nvl函数的功能：
/*
if p1 is null then
return p2;
else
return p1;
end if;nvl函数(p1,p2),要求两个参数的数据类型一致，
任何数据类型的值都可以是null，所以参数的类型可以是number，字符型，date。
*/
例如：若一个月使用了250小时,每种资费标准对应的费用是多少。
方案一：
月超出费用计算方法如下:一月里超出包在线时长所需的费用。
(250 - base_duration)*unit_cost
总费用是月固定费用和月超出费用之和：
base_cost + (250 - base_duration)*unit_cost
代码实现如下：
select base_cost + (250 - base_duration)*unit_cost fee
from cost;
方案二：（fee是列别名）
select id,
nvl(base_cost,0) + (250 - nvl(base_duration,0)) * nvl(unit_cost,0) fee
from cost;
============================================
drop table user;
create table userr(
id number(4) ,
password char(4) ,
name char(20) ,
phone char(20) ,
email varchar2(50)
) ;
insert into userr values(1001 ,‘1234‘ ,‘liucs‘,‘13600000000‘,‘[email protected]‘);
select * from userr;
======================================
设置分页显示
SQL> set pagesize 100 --每100行数据分页显示
SQL> set pages 0 --pagesize可以简写为pages,设置为0表示丌分页
=======================================
create table dept(
deptno number(2) ,
dname char(20) ,
location char(20)
) ;
insert into dept values(10 , ‘developer‘ , ‘beijing‘) ;
insert into dept values(20 , ‘account‘ , ‘shanghai‘) ;
insert into dept values(30 , ‘sales‘ , ‘guangzhou‘) ;
insert into dept values(40 , ‘operations‘ , ‘tianjin‘) ;
commit;
select * from dept;
create table emp(
empno number(4) ,
ename varchar2(20) ,
job varchar2(15) ,
salary number(7 , 2) ,
bonus number(7 , 2) ,
hiredate date,
mgr number(4) ,
deptno number(10)
) ;
insert into emp values( 1001 , ‘张无忌‘ , ‘Manager‘ , 10000 , 2000 , ‘12-3月-10‘ , 1005 , 10) ;
insert into emp values( 1002 , ‘王五‘ , ‘Analyst‘ , 8000 , 1000 , ‘01-4月-11‘ , 1001, 10) ;
insert into emp values( 1003 , ‘李四‘ , ‘Analyst‘ , 9000 , 1000 , ‘11-4月-10‘ , 1001, 10) ;
insert into emp values( 1004 , ‘郭芙蓉‘ , ‘Programmer‘ , 5000 , null , ‘01-1月-11‘ , 1001 , 10) ;
insert into emp values( 1005 , ‘张三丰‘ , ‘President‘ , 15000 , null , ‘15-10月-08‘ , null , 20) ;
insert into emp values( 1006 , ‘燕小六‘ , ‘Manager‘ , 5000 , 400 , ‘01-2月-09‘ , 1005 , 20) ;
insert into emp values( 1007 , ‘陆无双‘ , ‘clerk‘ , 3000 , 500 , ‘01-2月-09‘ , 1006 , 20) ;
insert into emp values( 1008 , ‘黄蓉‘ , ‘Manager‘ , 5000 , 500 , ‘1-10月-09‘ , 1005 , 30) ;
insert into emp values( 1009 , ‘韦小宝‘ , ‘salesman‘ , 4000 , null , ‘20-2月-09‘ , 1008 , 30) ;
insert into emp values( 1010 , ‘郭靖‘ , ‘salesman‘ , 4500 , 500 , ‘10-10月-09‘ , 1008 , 30) ;
set linesize 150 -- 设置行长度为150
col empno for 9999 -- 设置empno显示4位
col mgr for 9999 -- 设置mgr显示4位
col deptno for 99 -- 设置deptno显示2位
col salary for 99999.99 -- 设置salary显示格式
select * from emp;
【案例4】计算员工的名字、月薪和年薪？
select ename,salary,salary*12 year_sal from emp;
======null空值=======
1) 任何数据类型都可以取值null
2) 空值和任何数据做算数运算 , 结果都是null。
3) 空值和字符串类型做连接操作 , 结果相当亍空值丌存在。
4) 处理空值的函数nvl
=========================
【案例5】计算员工的月收入
select ename,salary,bonus,salary+nvl(bonus,0) month_sal from emp;--正确的写法
select ename,salary,bonus,nvl(salary+bonus,0) month_sall from emp;--错误的写法
【案例6】揑入一条ID为1011 , 姓名为 ‘余泽成‘ , 其余字段为null的数据
insert into emp(empno,ename)values(1011,‘余泽成‘);
【案例7】查询emp表 , 如果没有职位 , 显示‘no position‘ , 如果有职位 , 显示员工的职位
select ename,nvl(job,‘no position‘)from emp;
【案例8】查询emp表 , 如果没有入职时间 , 显示为2011年10月10日 , 否则原样显示
select ename,nvl(hiredate,‘10-10月-11‘)from emp;
=======扩展=========
-- "||" 符号表示两个数据串接起来 , 类似亍Java中的两个字符串之间的+号
-- 上例是把ename和job两个字符串连接起来
-- 如果job是null , 相当亍丌串接任何字符
select empno, ename || job detail from emp ;
-- 串接的两个字段之间有常量字符 , 每行显示一次
select empno , ename || ‘ work as ‘ || job detail from emp ;
【案例9】复制表emp为emp_x
create table emp_x as select * from emp;
【案例10】机构中有多少种职位？
select distinct job from emp;
【案例11】员工分布在哪些部门？
select distinct deptno from emp;
【案例12】查询每个部门不重复的职位
select distinct deptno,job from emp;
【案例13】薪水高亍10000元的员工数据？
select ename from emp where salary>10000;
--或者
select * from emp where salary>10000;
【案例14】职位是Analyst的员工数据？
select * from emp where job=‘Analyst‘;
==lower()函数==
--将字符数据转换为小写
【案例15】查找职位为“analyst”的员工
select * from emp where lower(job)=‘analyst‘;
==upper()函数==
--将数据转换为大写
【案例16】忽略大小写 , 查找职位为“analyst”的员工
select * from emp where upper(job)=‘ANALYST‘;
======between …and… 关键字======
1) 在区间中：between 低值 and 高值
2) 闭区间：[低值 , 高值]
【案例17】薪水大亍5000并且小亍10000的员工数据？
select * from emp where salary>=5000 and salary<=10000;
--或者(等同于)
select * from emp where salary between 5000 and 10000;
【案例18】入职时间在2011年的员工？
select * from emp where hiredate between ‘01-01月-11‘ and ‘31-12月-11‘;
==in列表==
【案例19】列出职位是Manager或者Analyst的员工
select * from emp where job in(‘Manager‘,‘Analyst‘);
--或者（等同于）
select * from emp where job = ‘Manager‘ or job = ‘Analyst‘ ;
==模糊匹配 like %==
1) “% ”表示0到多个字符 , 跟like配合使用
2) “_”下划线表示一个字符
【案例20】列出职位中包含有sales字符的员工数据？
select * from emp where lower(job) like ‘%sales%‘;
【案例21】列出职位中第二个字符是a的员工数据？
select * from emp where job like ‘_a‘;
==is null==
--数据库语言判断null值的方法。
【案例22】查询哪些员工没有奖金？
select * from emp where bonus is null;
==is not null==
--数据库语言判断不是null值的方法
【案例23】查询哪些员工没有奖金？。
select * from emp where bonus is not null;
==not between 低值 and 高值==
【案例24】薪水丌在5000至8000的员工？
select * from emp where salary not between 5000 and 8000;
==not in (list):丌在列表中==
【案例25】不是部门20和部门30的员工？
select *from emp where deptno not in(20,30);
======加强训练======
1、查询职员表中工资大于5600 的员工姓名和工资
select ename,salary from emp where salary>5600;
2. 查询职员表中员工号为1008 的员工的姓名和部门号码
select ename,deptno from emp where empno=1008;
3. 选择职员表中工资不在6000 到8000 的员工的姓名和工资
select ename,salary from emp where salary not between 6000 and 8000;
4. 选择职员表中在20 和30 号部门工作的员工姓名和部门号
select ename,deptno from emp where deptno in(20,30);
5. 选择职员表中没有管理者的员工姓名及职位, 按职位排序
select ename,job from emp where mgr is null order by job;
6. 选择职员表中有奖金的员工姓名，工资和奖金，按工资倒序排列
select ename,salary,bonus from emp where bonus is not null order by salary desc;
7. 选择职员表中员工职位的第二个字母是a 的员工姓名
select * from emp where job like‘_a%‘;
8. 列出部门表中的部门名字和所在城市；
select dname,location from dept;
9. 显示出职员表中的不重复的职位；
select distinct job from emp;
10. 连接职员表中的职员名字、职位、薪水，列之间用逗号连接，列头显示成OUT_PUT
提示1: 字符串连接符||
提示2：用别名控制列头显示
select ename||‘,‘||job||‘,‘||salary out_put from emp;
11. 查询职员表emp 中员工号、姓名、工资，以及工资提高百分之20%后的结果
select empno,ename,salary,salary*1.2 sal_d from emp;
12. 查询员工的姓名和工资数,条件限定为工资数必须大于7200，并对查询结果按入职时间进行排
列，早入职排在前面，晚入职排在后面。
select ename salary from emp where salary>7200 order by hiredate;
13. 列出除了ACCOUNT 部门还有哪些部门
select deptno, dname, location from dept where dname <> ‘ACCOUNT‘;
create table test(
c1 number,
c2 number(6),
c3 number(4,3),
c4 number(3,-3),
c5 number(2,4)
);
insert into test values(10,999999,9.999,999000,0.0099);
==显示方式与隐式方式
隐式方式：指定需要都要插值的列
显示方式：不插值的列指定为null
===定义缺省值default===
1、缺省值的数据类型必须匹配列的数据类型
2、有效的缺省值为文字值、表达式、sql函数、sysdate、user等。
3、无效的缺省值为另一个列的列名或伪列
4、default可以用于insert语句、update语句
drop table test purge;
create table test(
c1 number default 1,
c2 number
);
insert into test(c2)values(2);
insert into test values(default,3);
insert into test values(4,4);
update test set c1=default where c1=4;
select * from test;
===字符串类型===
insert into test values(‘abc‘,‘abc‘);
select length(c1),length(c2) from test;
===varchar2和char===
1、varchar2必须定义长度，最大长度4000字节；char可以不定义长度。缺省为1，最大长度2000字节
2、varchar2按字符串的实际长度存，char按定义长度存
3、列的取值是定长，定义成char。列的取值不固定。定义为varchar2
===日期类型===
sysdate是一个系统函数，返回当前系统时间
create table c2(
c1 date
);
insert into c2 values(‘01-1月-08‘);
===列别名===
--给列起别名（列别名）能够改变一个列、表达式的标识
--在原名和别名之间可以使用as关键字
--若列别名中包含空格或希望大小写敏感，用双引号括起来。
==拼接运算符||==
==消除重复行：distinct==
注意：distinct必须( 只能 )跟在select后边
/*
*SQL命令可以分成以下几种类型：
--DDL命令(Data Definition Language)
称为数据定义语言，是用于创建，修改，删除表结构的
DDL对数据结构起作用。
create 数据库对象的创建
alter 修改数据库对象
drop 删除数据库对象
truncate 清空表数据
--DML命令(Data Manipulation Language)
被称为数据操作语言，是用于向表中增删改数据的
insert 插入操作
update 更新操作
delete 删除操作
--DQL命令（Data Query Language）
被称为数据查询语言，是用于从表查询数据的
select 查询操作
--TCL命令（Transaction Control Language）
被称为事务控制语言，是用于确认或撤销DML操作的
commit 提交数据
rollback 数据回滚
savepoint 保存点
--DCL命令（Data Control Language）
被称为数据控制语言，是用于给用户授权或取消授权的
（DBA使用比较多）
*/
select * from dual;
借助函数nvl(p1,p2)实现，nvl函数的功能：
/*
if p1 is null then
return p2;
else
return p1;
end if;nvl函数(p1,p2),要求两个参数的数据类型一致，
任何数据类型的值都可以是null，所以参数的类型可以是number，字符型，date。
*/
例如：若一个月使用了250小时,每种资费标准对应的费用是多少。
方案一：
月超出费用计算方法如下:一月里超出包在线时长所需的费用。
(250 - base_duration)*unit_cost
总费用是月固定费用和月超出费用之和：
base_cost + (250 - base_duration)*unit_cost
代码实现如下：
select base_cost + (250 - base_duration)*unit_cost fee
from cost;
方案二：（fee是列别名）
select id,
nvl(base_cost,0) + (250 - nvl(base_duration,0)) * nvl(unit_cost,0) fee
from cost;
============================================
drop table user;
create table userr(
id number(4) ,
password char(4) ,
name char(20) ,
phone char(20) ,
email varchar2(50)
) ;
insert into userr values(1001 ,‘1234‘ ,‘liucs‘,‘13600000000‘,‘[email protected]‘);
select * from userr;
======================================
设置分页显示
SQL> set pagesize 100 --每100行数据分页显示
SQL> set pages 0 --pagesize可以简写为pages,设置为0表示丌分页
=======================================
create table dept(
deptno number(2) ,
dname char(20) ,
location char(20)
) ;
insert into dept values(10 , ‘developer‘ , ‘beijing‘) ;
insert into dept values(20 , ‘account‘ , ‘shanghai‘) ;
insert into dept values(30 , ‘sales‘ , ‘guangzhou‘) ;
insert into dept values(40 , ‘operations‘ , ‘tianjin‘) ;
commit;
select * from dept;
create table emp(
empno number(4) ,
ename varchar2(20) ,
job varchar2(15) ,
salary number(7 , 2) ,
bonus number(7 , 2) ,
hiredate date,
mgr number(4) ,
deptno number(10)
) ;
insert into emp values( 1001 , ‘张无忌‘ , ‘Manager‘ , 10000 , 2000 , ‘12-3月-10‘ , 1005 , 10) ;
insert into emp values( 1002 , ‘王五‘ , ‘Analyst‘ , 8000 , 1000 , ‘01-4月-11‘ , 1001, 10) ;
insert into emp values( 1003 , ‘李四‘ , ‘Analyst‘ , 9000 , 1000 , ‘11-4月-10‘ , 1001, 10) ;
insert into emp values( 1004 , ‘郭芙蓉‘ , ‘Programmer‘ , 5000 , null , ‘01-1月-11‘ , 1001 , 10) ;
insert into emp values( 1005 , ‘张三丰‘ , ‘President‘ , 15000 , null , ‘15-10月-08‘ , null , 20) ;
insert into emp values( 1006 , ‘燕小六‘ , ‘Manager‘ , 5000 , 400 , ‘01-2月-09‘ , 1005 , 20) ;
insert into emp values( 1007 , ‘陆无双‘ , ‘clerk‘ , 3000 , 500 , ‘01-2月-09‘ , 1006 , 20) ;
insert into emp values( 1008 , ‘黄蓉‘ , ‘Manager‘ , 5000 , 500 , ‘1-10月-09‘ , 1005 , 30) ;
insert into emp values( 1009 , ‘韦小宝‘ , ‘salesman‘ , 4000 , null , ‘20-2月-09‘ , 1008 , 30) ;
insert into emp values( 1010 , ‘郭靖‘ , ‘salesman‘ , 4500 , 500 , ‘10-10月-09‘ , 1008 , 30) ;
set linesize 150 -- 设置行长度为150
col empno for 9999 -- 设置empno显示4位
col mgr for 9999 -- 设置mgr显示4位
col deptno for 99 -- 设置deptno显示2位
col salary for 99999.99 -- 设置salary显示格式
select * from emp;
【案例4】计算员工的名字、月薪和年薪？
select ename,salary,salary*12 year_sal from emp;
======null空值=======
1) 任何数据类型都可以取值null
2) 空值和任何数据做算数运算 , 结果都是null。
3) 空值和字符串类型做连接操作 , 结果相当亍空值丌存在。
4) 处理空值的函数nvl
=========================
【案例5】计算员工的月收入
select ename,salary,bonus,salary+nvl(bonus,0) month_sal from emp;--正确的写法
select ename,salary,bonus,nvl(salary+bonus,0) month_sall from emp;--错误的写法
【案例6】揑入一条ID为1011 , 姓名为 ‘余泽成‘ , 其余字段为null的数据
insert into emp(empno,ename)values(1011,‘余泽成‘);
【案例7】查询emp表 , 如果没有职位 , 显示‘no position‘ , 如果有职位 , 显示员工的职位
select ename,nvl(job,‘no position‘)from emp;
【案例8】查询emp表 , 如果没有入职时间 , 显示为2011年10月10日 , 否则原样显示
select ename,nvl(hiredate,‘10-10月-11‘)from emp;
=======扩展=========
-- "||" 符号表示两个数据串接起来 , 类似亍Java中的两个字符串之间的+号
-- 上例是把ename和job两个字符串连接起来
-- 如果job是null , 相当亍丌串接任何字符
select empno, ename || job detail from emp ;
-- 串接的两个字段之间有常量字符 , 每行显示一次
select empno , ename || ‘ work as ‘ || job detail from emp ;
【案例9】复制表emp为emp_x
create table emp_x as select * from emp;
【案例10】机构中有多少种职位？
select distinct job from emp;
【案例11】员工分布在哪些部门？
select distinct deptno from emp;
【案例12】查询每个部门不重复的职位
select distinct deptno,job from emp;
【案例13】薪水高亍10000元的员工数据？
select ename from emp where salary>10000;
--或者
select * from emp where salary>10000;
【案例14】职位是Analyst的员工数据？
select * from emp where job=‘Analyst‘;
==lower()函数==
--将字符数据转换为小写
【案例15】查找职位为“analyst”的员工
select * from emp where lower(job)=‘analyst‘;
==upper()函数==
--将数据转换为大写
【案例16】忽略大小写 , 查找职位为“analyst”的员工
select * from emp where upper(job)=‘ANALYST‘;
======between …and… 关键字======
1) 在区间中：between 低值 and 高值
2) 闭区间：[低值 , 高值]
【案例17】薪水大亍5000并且小亍10000的员工数据？
select * from emp where salary>=5000 and salary<=10000;
--或者(等同于)
select * from emp where salary between 5000 and 10000;
【案例18】入职时间在2011年的员工？
select * from emp where hiredate between ‘01-01月-11‘ and ‘31-12月-11‘;
==in列表==
【案例19】列出职位是Manager或者Analyst的员工
select * from emp where job in(‘Manager‘,‘Analyst‘);
--或者（等同于）
select * from emp where job = ‘Manager‘ or job = ‘Analyst‘ ;
==模糊匹配 like %==
1) “% ”表示0到多个字符 , 跟like配合使用
2) “_”下划线表示一个字符
【案例20】列出职位中包含有sales字符的员工数据？
select * from emp where lower(job) like ‘%sales%‘;
【案例21】列出职位中第二个字符是a的员工数据？
select * from emp where job like ‘_a‘;
==is null==
--数据库语言判断null值的方法。
【案例22】查询哪些员工没有奖金？
select * from emp where bonus is null;
==is not null==
--数据库语言判断不是null值的方法
【案例23】查询哪些员工没有奖金？。
select * from emp where bonus is not null;
==not between 低值 and 高值==
【案例24】薪水丌在5000至8000的员工？
select * from emp where salary not between 5000 and 8000;
==not in (list):丌在列表中==
【案例25】不是部门20和部门30的员工？
select *from emp where deptno not in(20,30);
======加强训练======
1、查询职员表中工资大于5600 的员工姓名和工资
select ename,salary from emp where salary>5600;
2. 查询职员表中员工号为1008 的员工的姓名和部门号码
select ename,deptno from emp where empno=1008;
3. 选择职员表中工资不在6000 到8000 的员工的姓名和工资
select ename,salary from emp where salary not between 6000 and 8000;
4. 选择职员表中在20 和30 号部门工作的员工姓名和部门号
select ename,deptno from emp where deptno in(20,30);
5. 选择职员表中没有管理者的员工姓名及职位, 按职位排序
select ename,job from emp where mgr is null order by job;
6. 选择职员表中有奖金的员工姓名，工资和奖金，按工资倒序排列
select ename,salary,bonus from emp where bonus is not null order by salary desc;
7. 选择职员表中员工职位的第二个字母是a 的员工姓名
select * from emp where job like‘_a%‘;
8. 列出部门表中的部门名字和所在城市；
select dname,location from dept;
9. 显示出职员表中的不重复的职位；
select distinct job from emp;
10. 连接职员表中的职员名字、职位、薪水，列之间用逗号连接，列头显示成OUT_PUT
提示1: 字符串连接符||
提示2：用别名控制列头显示
select ename||‘,‘||job||‘,‘||salary out_put from emp;
11. 查询职员表emp 中员工号、姓名、工资，以及工资提高百分之20%后的结果
select empno,ename,salary,salary*1.2 sal_d from emp;
12. 查询员工的姓名和工资数,条件限定为工资数必须大于7200，并对查询结果按入职时间进行排
列，早入职排在前面，晚入职排在后面。
select ename salary from emp where salary>7200 order by hiredate;
13. 列出除了ACCOUNT 部门还有哪些部门
select deptno, dname, location from dept where dname <> ‘ACCOUNT‘;
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
