oracle_day01

oracle简介:

优点:
	oracle是收费的,1.安全性高;2.处理速度快;3.可移植性
体系结构:
	数据库-->实例-->表空间(tablespace)-->用户-->表(table)    --从左往右,都是一对多

DDL

1.创建表空间
	create tablespace baidu  --表空间的名称
	datafile 'c:\baidu1.dbf'  --数据文件名称,会在指定位置自动创建一个dbf文件
	size 100m  --初始文件大小
	autoextend on --开启自动扩容
	next 10m;   --每次扩容的大小
2.删除表空间
	drop tablespace baidu;  --只有先删除表空间,才能删除datafile
3.创建用户
	create user baidu    --用户名
	identified by baidu    --密码,密码不能以数字开头
	default tablespace baidu;  --指定表空间
4.授权
	grant dba to baidu;  --超级管理员角色
	grant connect to baidu;  --连接角色
	grant resource to baidu;   --开发者角色	
5.创建表
     create table person(
     	id number(10),
     	username varchar2(10)
     );
	数据类型:
    数字:number(n,m)  只有一个n表示整数,长度为n;有n,m表示小数,总长度n,小数m
    字符串:varchar2(长度) 可变长度  char(长度) 固定长度
    日期: date
6.删除表
	drop table person;
7.修改表中的字段
	-- 添加字段
	alter table 表名 add 字段名 数据类型;
		alter table person add age number(10);
	-- 删除字段
	alter table 表名 drop column 字段名;
		alter table person drop column age;
	-- 修改字段的数据类型或者长度
	alter table 表名 modify 字段名 数据类型;
		alter table person modify age varchar2(15);
	-- 修改字段名称
	alter table 表名 rename column 旧字段 to 新字段;
		alter table person rename column age to address;
8.解锁内置用户scott
	--解锁scott用户
	alter user scott account unlock;
	--解锁soctt用户的密码,也可以用来重置密码
	alter user scott identified by tiger;

DML

oracle默认设置的手动提交事务,所以执行DML需要手动commit;oracle事务隔离级别:read committed
1.增
insert into 表名(字段1,字段2) values(值1,值2);
commit;
2.删
delelte from 表名 where 条件;
commit;
delete from 表名 的数据可以被回滚,逐条删除;truncate table 表名 的数据不能被回滚,直接摧毁表
3.改
update 表名 set 字段1=值1,字段2=值2 where 条件

序列(sequence):

  概念:默认是连续增长的数据库对象,序列不真的属于任何一张表，但是可以逻辑和表做绑定。
  作用:主要用于生成表的主键,默认自增1,初始值1,没有最大值
  注意:序列创建后,第一次使用必须nextval
  dual: 虚表/伪表,补全语法
  语法:
  	create sequence 序列名称 --序列名称 create sequence s_abc
  	start with 1  --开始值
  	increment by 1;  --每次增长值
  select s_abc.currval from dual; -- 查询当前的序列值,current value
  select s_abc.nextval from dual; -- 查询下一次的序列值,第一次使用是就是start with指定的值
  用法: insert into person values(s_abc.nextval,'张三');

单行函数与多行函数

单行函数:作用于一行，返回一个值。
1.字符函数
	upper() --变大写  select upper('hello') from dual; 
	lower() --变小写  select lower('HELLO') from dual;
	concat('aaa','bbb') --字符串拼接 select concat('aaa','bbb') from dual;
	('aaa'||'bbb')  --字符串拼接 select ('aaa'||'bbb') from dual;
	length() -- 长度   --select length('aaa') from dual; 
2.数值函数
	round() --四舍五入 正数保留小数点后几位,负数保留小数点前几位
		select round(555.555,2) from dual ; --555.56
		select round(555.555,-2) from dual ; --600
	trunc() --截取 正数从小数点后几位开始截取,负数从小数点前几位开始截取,truncate
		select trunc(555.555,2) from dual ; --555.55
		select trunc(555.555,-2) from dual ; --500
	mod()   --取余
		select mod(5,2) from dual; --1
3.日期函数
	sysdate 当前系统的日期
		select sysdate+1 from dual; --计算明天此时此刻
	months_beyween(sysdate,e.hiredate) 两个日期差过去多少星期
		select months_between(sysdate,e.hiredate) from emp e; 查询emp表中所有员工入职离现在几月。
4.转换函数
	to_char() date转char
		select to_char(sysdate,'fm yyyy-mm-dd hh24:mi:ss');
fm:去掉0n,比如2018-06-06,去掉0n后,2018-6-6; hh24表示24小时制,hh表示12小时制,mi代表分钟
	to_date() char转date
		select to_date('2018-8-8','fm yyyy-mm-dd') from dual;
5.通用函数
	nvl(); 	相当于ifnull()
		select nvl(math,0)+english from emp;
		select nvl(5,2)+1 from dual; --6
		select nvl(null,2)+1 from dual; --3

多行函数:聚合函数,将一列看成一个整体来计算
 	sum()求和,avg()平均数,max()最大值,min()最小值,count()统计次数
oracle中统计次数通常用count(1),1代表主键,select count(1) from emp;

条件表达式

   1.等值判断,有end,关键字空格隔开
select 
e.ename,(case e.ename
 when 'SMITH' then '史密斯'
  when 'ALLEN' then '爱丽丝'
   when 'JAMES' then '詹姆斯'
    else '无名'   --else可以省略,表示空白null
     end) as "中文名"  --此处起别名必须用双引号
from emp e;

	2.范围判断,有end
select
e.sal,(case
 when e.sal>3000 then '高工资'
  when e.sal>1500 then '中工资'
   else '低工资'
    end) as "员工工资"  
from emp e;

	3.oracle专用的等值判断,没有end,关键字用","隔开
select e.ename,decode(e.ename,
	'SMITH','史密斯',
	  'ALLEN','爱丽丝',
	   'JAMES','詹姆斯',
	    '无名'
	)
from emp e;

复杂条件查询

1.连接查询
内连接查询:
	隐式内连接,显示内连接
外连接查询:
	左外连接,右外连接
oracle专属外连接:
	没有(+)的一方显示所有数据
	select * from emp e,dept d where e.deptno=d.deptno(+) ; --显示emp表的所有数据以及交集数据
2.分组查询
	分组查询中,出现在group by后面的原始列,才能出现在select后面;没有出现在group by后面的列,想在select后面,必须加上聚合函数。
	sql的执行顺序: from-->where-->group by-->select-->having-->order by;
	所有的条件都不能使用别名来判断
3.自连接查询
	自连接其实就是站在不同的角度把一张表看成多张表。
select e1.empno,e1.ename,e2.empno,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;
	当需要查询员工姓名,员工部门,领导姓名,领导部门时,如果只有一张dept表,那么连接条件就是e1.deptno=d.deptno and e2.deptno=d.deptno,也就是说限定了员工必须跟领导一个部门,然而实际上却会有误差,所以需要两张dept表
4.子查询
	select子查询的结果必须为一行一列
5.分页查询
	rownum 伪列,行号,行号必须从1开始,依次递增,不能跳着走。rownum不能使用> = >=,rownum不属于任何一张表
--表工资倒叙排列后，每页五条记录，查询第二页。
	第一步:对表工资进行倒序
	第二步:获取伪类别名
	第三步:条件限定
select * from (
  select rownum rn,e1.* from 
	(select * from emp order by sal desc) e1 
) e2 where e2.rn between 5 and 9;