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;