Oracle数据库基础(二)--数据类型,约束,oracle函数

oracle 数据库的常见数据类型
oracle 全部数据类型有26种


char 
定长字符串类型 长度是固定不变的 no char(10) 如果存入值 不足十个字符,其他位也被占用
char(10)   等价于  char(10 byte)
           也可以  char(10 char)
默认长度1 即1个字符 最大长度:2000个字节  GBK编码---1000个汉字
占用的空间较多,但是查询效率较高


varchar2
变长字符串类型 长度可变 例如:定义的长度是100个字节,存入的值是80个字节,那么实际占用的就是80个字节
varchar2(100)
         等价于 varchar2(100 byte)
也可以 varchar2(100 char)  
必须要为其指定长度 1-4000个字节 所以要考虑编码问题
oracle使用GBK编码---2000个汉字     一个中文占2个字节 英文\数字占一个字节
占用的空间较少,但是查询的效率较低


varchar在oracle中不推荐使用,强烈推荐使用varchar2


number
数字类型 number(m,n)---对应真实的数据
m:整个数字的位数,从左边第一个不为0的数算起,小数点和负号不计入有效位数

n:小数位的位数

例:123.89   有效位:5;  小数位:2

number(p,s)--对应要截取的数字

1<= p<=38 ;   -84<= s <=127

当s>0时; 精确到小数点右边s位,并四舍五入,然后比较有效位数m是否<=p;

当s<0时; 精确到小数点左边s位,并四舍五入,然后比较有效位数m是否<=p+|s|

当s=0时; number表示整数,s默认为0

123.89      number           123.89
123.89      number(3)        124-------------------------------------------------------------s=0; 取整数
123.89      number(6.2)      123.89--------------------------------------------------------s=2,精确到小数点右边2位, 并且有效位数5<6
123.89      number(6.1)      123.9
123.89      number(4.2)      exceeds precision(有效位为5,5>4) 超出范围-----------------------------------------------s=2,取小数点右边2位, 并且有效位数5>4,错误
123.89      number(6,-2)     100--------------------------------------------------------s=-2,精确到小数点左边2位,即十位,并且有效位数5<6+2
.01234      number(4.5)      .01234(有效位为4)
.00012      number(4.5)      .00012
.000127     number(4.5)      .00013
.0000012    number(2.7)      .0000012
.00000123   number(2.7)      .0000012


date
将数据存入数据库中,需要进行格式化存储的函数(方法)
默认格式:DD-MM-YY
select sysdate from dual;
30-9月 -15


to_date('存入的值','存入的格式');  
to_date('2015-12-12 12:12:12','YYYY-MM-DD HH24:MI:SS'); 
create table test_date(name char(3),test_time date default sysdate);
insert into test_date(name,test_time) values('xyz',to_date('2015-12-12 22:12:12','YYYY-MM-DD HH24:MI:SS'));


to_char();
select to_char(test_time,'YYYY-MM-DD HH24:MI:SS')tochar_time from test_date where name='xyz';


timestamp


clob 用来存储单字节字符型数据  适用于存储超长文本 例如小说\博客


blob 用来存储二进制数据  存储视频\图像\音频


-------------------------------------------------------------------------------------------------------------------------


约束
创建表时使用约束
是强加在表上的规则和条件.确保数据库满足业务规则.可以保证数据的完整性
当对表进行DML DDL操作时,如果此操作会造成表中的数据违反约束条件或规则时
系统就会拒绝执行这个操作




定义约束时如果没有给定一个明确的约束名称,系统将会自动为该约束生成一个名字.
oracle强烈建议创建约束时要给约束起名称


约束的分类
非空约束(NOT NULL)----约束字段(列)不能为NULL值


唯一约束(UNIQUE KEY)----在表中每一行定义的这列或这些列的值都不能相同,即唯一性


主键约束(PRIMARY KEY)----唯一的标示表中的每一条数据(每一行记录) 不能为NULL 不能重复


外键约束(FOREIGN KEY)----用来维护从表和主表之间引用的完整性(不推荐使用外键约束)


条件或检查约束(CHECK)----表中每一行该列都要满足该约束条件


命名规则:
约束名称简称_表名_列名
非空约束  NN_tablename_coll
唯一约束  UK_tablename_coll
主键约束  PK_tablename_coll
外键约束  FK_tablename_coll
条件或检查约束 CK__tablename_coll


create table c_test(
    id number(6),
name varchar2(30),
gender char,
age number(3),
birthday date,
constraint UK_C_TEST_NAME not null(name)
);

----------------------------------------------------------------------------------------------
表结构操作
修改表名称--rename tt to new_tt;
添加表字段--alter table new_tt add gender char default 'M';
表字段重命名--alter table tt rename column name to username;
修改表字段--alter table modify (name varchar2(50) default'甘俊来了');


查询某用户下某表的约束名称（在dba角色下查询）
select constraint_name from dba_constraints where owner = 'SCOTT' AND table_name= 'EMP' ;


查询当前用户下的表约束
select   constraint_name,   constraint_type , search_condition,   r_constraint_name 
from   user_constraints   where   table_name   =   upper('emp'); 


删除emp表中的外键约束
alter table emp
drop constraint FK_DEPTNO ;


INSERT INTO "SCOTT"."EMP" (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO) VALUES ('8888', 'lg', 'BOSS', '7839', TO_DATE('1981-11-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '4500', '50')




oracle函数
运算符:
算数运算符:+ - * /
连接运算符: ||
比较运算符: >  =  >=  <  <=  !=  like  between--and   is--null  in


select * from tt where name like '%k';  (k结尾)
select * from tt where name like 'j%';  (j开头)
select * from tt where name like '%a%'; (中间含a)


select * from tt where age between 23 and 24;


select * from tt where name is null;
任何值与null进行运算,都是null


select * from tt where age in (23,33,43);(找到相匹配的)


ascii('a');               根据字符返回编码值
chr(编码值);              返回对应的字符
concat(字段名1,字段名2);  连接两个字符串                              select concat(name||'--',age+100) from tt;
initcap(字段名);          返回字符串,并将字符串的第一个字母变大写     select initcap(name) from tt;
length(字段名)            返回字段值的长度                            select length(name) name_length from tt;
lower(字段名)             返回字符串的全部小写                        select low(initcap(name)) low,upper(name) up from tt;
upper(字段名)             返回字符串的全部大写 
ltrim(字段名)             清除字段值左边的空字符串                    select ltrim(rtrim(name)) uname from tt;  
rtrim(字段名)             清除字段值右边的空字符串 
substr(原字符串,从第几个字符开始,截取度少个)                          select substr('abcdefghijklmn',1,3) from dual;
replace(原字符串,被替换的字符串,要替换的字符串)                       select replace('He love rose','He','I') from dual;
trim(要去掉的#字符#,from,目标字符串)    剪掉两边所有的字符            select trim ('s' from 'selects')  from dual;
leading                                 剪掉前面所有的s字符           select trim (leading 's' from 'selects')  from dual;             
trailing                                剪掉后面所有的s字符           select trim (trailing 's' from 'selects')  from dual;   
abs(数字字段名)            返回绝对值                                 
floor(字段名)              将小数转成为整数(不四舍五入)               select floor(1234.123) from dual;
ceil(字段名)               将小数转成整数(有小数就+1)                 select ceil(1234.123) from dual;
mod(被除数,除数)                                                      select mod(10,3) m1,mod(3,3)m2,mod(1,3)m3 from dual;
power(原数字,幂)                                                      select power(2,3) from dual;
round(字段名)              对小数进行四舍五入                         select round(55.5) from dual;
trunc(字段名)              直接截取整数部分(不四舍五入)               select trunc(55.5) from dual;
avg(age)                   平均值                                     select avg(age) from tt;
count(字段名)   获取记录数(如果使用字段名,该字段一定不能有null值,否则忽略) select count(*) from tt; 
sum(字段名)                求和                                       select sum(age) from tt;
min(字段名)                最小值                                     select min(age) from tt;
max(字段名)                最大值                                     select max(age) from tt;
to_number('1234')                                                     select to_number('1234') from dual;


show autocommit            自动提交
commit                     手动提交
set autocommit off/on      自动提交关/开
rollback                   回滚--撤销之前一步操作(自动提交关闭状态下--autocommit off)








last_day(date)返回日期date所在月的最后一天
  select last_day(sysdate)from dual;
add_months(date,i)返回日期date加上i个月后的日期值
         i取正整数值      小数:截取整数后在运算       负数:减去i个月的日期值
months_between(date,date_)返回两个日期间隔多少个月
         结果可能是负数和小数(可以用ceil函数直接进位)
next_day(date,周几)返回date日期数据的下一个周几的日期,例如4 即下一周第4天的日期
                   january  february  march april may june july 
least(1,3,55,100,2,24)
greatest(1,88,2,33,55)
       也被称作比较函数,参数类型必须一致
  第二个参数类型会被转向第一参数类型 不能转换就报错
  
extract(year/month/day from date)从参数datetime中提取参数date指定的数据类型,例如提取年,月,日


select * from tt where name = null;
select * from tt where name is null;
select * from tt where name is not null;
空值函数
nvl(col_null,col_not_noll)将null值转换成非null值处理 请保持两个参数的类型一致
例如:select sal,comm,nvl(sal,0)as'salary'from emp;


nvl2(col_null,col_is_noll,col_is_not_noll)将null值转换成实际值   作用有点像三目表达式
如果是null  转成col_is_null,  如果不是null  转成col_is_not_null
select sal,comm,nvl2(sal,0,1000)as'salary'from emp;






------------------------------------------------------------------------------------------------
基本查询语句
any和all
不能单独使用 需要与比较符号配合使用
>any   大于最小的     selec * from emp where sal>any(1000,2000,3000);
<any   小于最大的
>all   大于最大的
<all   小于最小的




distinct     过滤重复关键字


聚合函数 即--数据统计
max(col)          最大值
min(col)          最小值
avg(col)          平均值
sum(col)          总值
count(col/*)      总个数    以字段名查询,会忽略null值 ,造成查询误差  
                            可以使用nvl和nvl2函数处理--select count(nvl(sal,0)) from emp;



查询语句执行顺序
from子句--执行顺序从后往前,从右往左,故数据量较少的表尽量放在后面
          select t1.col,t2.col from t1,t2 where t1.id=t2.id;
 
where子句--执行顺序自上往下,从右往左,故将能过滤掉最大数量记录的条件写在where子句的最右


group子句--执行顺序从左往右分组,故最好在group by前使用where 将不需要的记录在group by 之前过滤掉


having子句--消耗资源 尽量避免使用,having会在检索出所有记录之后才对结果进行过滤,需要排序等操作


group by 子句 分组
当希望得到每个部门的平均薪水,而不是整个机构的平均薪水
把整个数据表按部门分成一个个小组,每个小组中包含一行或多行数据,在每个小组中在使用分组函数进行计算,每组返回一个结果
划分的小组有多少,最终的结果集行数就有多少


having子句
having子句用来对分组后的结果进一步限制,比如按部门分组后,得到每个部门的最高薪水,可以继续限制输出结果
必须跟在group by后面,不能单独存在


select子句
少用*号,尽量取字段名称,oracle在解析的过程中,通过查询数据字典将*号依次转换成所有的列名,消耗时间
  
order by 子句--执行顺序从左往右,消耗资源 
order by 子句 对数据按一定规则进行排序  必须是查询语句的最后一个子句
ASC(ascending)升序(默认)     DESC(decending)降序
null值视作最大
多列排序:
首先按照第一列进行排序,如果第一列数据相同,再以第二列排序,以此类推
例如:select ename,deptno,sal from emp order by deptno asc,sal desc;




1--创建一个表空间 scott_space
2--创建一个用户scott/a
3--授权
4--使用该用户创建表并插入数据




简单查询


1.       选择部门30中的所有员工
select * from emp where deptno = 30;
2.       列出所有办事员的姓名、编号和部门编号
select ename,empno,deptno from emp WHERE job = 'CLERK';
3.       找出佣金高于薪金的员工
select * from emp  where comm > sal;
4.       找出佣金高于薪金60%的员工
select * from emp  where comm > (0.6)*sal;
5.       找出部门10中所有经理和部门20中所有办事员的详细资料
select * from emp where (deptno=10and job='MANAGER') or (deptno=20and job='CLERK');
6.       找出部门10中所有经理，部门20中所有办事员，既不是经理又不是办事员但薪金大于或等于2000的所有员工的详细资料 
select * from emp where (deptno=10and job='MANAGER') or (deptno=20and job='CLERK')or job not in ('MANAGER','CLERK') and sal >=2000;
7.       找出收取佣金的员工的不同工作
selectdistinct job from emp where comm isnotnull;
8.       找出不收取佣金或收取的佣金低于100的员工
select * from emp where comm is null or comm < 100;
9.       找出各月倒数第三天受雇的所有员工。
select * from emp where last_day(hiredate)-hiredate=2;
10.   找出早于12年前受雇的员工
select * from emp where months_between(sysdate,hiredate)/12 >=12;
11.   以首字母大写的方式显示所有员工的姓名
select INITCAP(ENAME) from emp;
12.   显示正好为5个字符的员工姓名
select ENAME from emp where length(ename)=5; 
13.   显示不带有R的员工姓名  www.2cto.com  
select ENAME from emp where ename notlike'%R%';
14.   显示所有员工的前三个字符
select substr(ename,1,3) from emp ;
15.   显示所有员工的姓名，用a替换所有A
selectreplace(ename,'A','a') from emp ;
16.   显示满10年服务年限的员工的姓名和受雇日期
select ename ,hiredate from emp where months_between(sysdate,hiredate)/12>=10;
17.   显示员工的详细资料，按姓名排序
select * from emp orderby ename ;
18.   显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面
select ename ,hiredate from emp orderby hiredate asc;
19.   显示所有员工的姓名、工作和薪金，按工作的降序排序，若工作相同按薪金排序
select ename,job,sal from emp orderby job desc ,sal;
20.显示所有员工姓名、加入公司的年份和月份，按受雇日期所在月排序，若月份相同则将最早年份的员工排在最前面。
select ename,to_char(hiredate,'YYYY/MM') from emp orderby to_char(hiredate,'MM'),to_char(hiredate,'yyyy') asc ;
21.   显示在一个月为30天的情况所有员工的日薪金，忽略余数
select  round(sal/30) from emp;  
22.   找出在任何年份的2月受聘的所有员工
select * from emp where to_char(hiredate ,'MM')=2 ;
23.   对于每个员工，显示其加入公司的天数
select ename,round(sysdate-hiredate) emp_date from emp;
24.   以年月日的方式显示所有员工的服务年限（表述意图不明）
select ename,to_char(hiredate,'YYYY')||'年'||to_char(hiredate,'MM')||'月'||to_char(hiredate,'DD')||'日'from emp;




按照薪水倒序排列,取出结果集中第8-10条记录
(select * from emp order by sal desc)


select * 
from(select rownum rm,e.* from(select * from emp order by sal desc)e)
where rm between 8 and 10;