--1.创建表空间
CREATE TABLESPACEf ts_demo
DATAFILE ‘D:\app\Administrator\oradata\orcl\ts_demo.dbf‘
SIZE 10M;
--2.创建用户
CREATE USER testemp
IDENTIFIED BY 123456
DEFAULT TABLESPACE ts_demo;
--3.给新建的用户授权
GRANT connect,resource to testemp;
----快速创建用户并授权
GRANT connect,resource to java43 IDENTIFIED BY 123456;
--4.修改用户密码
ALTER USER testemp IDENTIFIED BY 123;
--5.锁定/解锁用户
ALTER USER testemp ACCOUNT LOCK;
ALTER USER testemp ACCOUNT UNLOCK;
--6.显示当前系统时间
SELECT sysdate FROM dual;
SELECT systimestamp FROM dual;
Large OBject
--7.伪列
rowid,rownum
--8.伪表：使用函数时，必须满足SELECT语句的语法，虚构一个表
dual
--9.查看当前用户下有哪些表
SELECT * FROM tab;
--10.创建表：员工表
CREATE TABLE employee
(
id number(2) PRIMARY KEY,
name char(6) NOT NULL,
address varchar2(10),
birthday date,
remark nvarchar2(10)
);
--11.查看表的结构
DESCRIBE employee
--12.插入记录
INSERT INTO employee VALUES(10,‘张三‘,‘深圳南山区‘,
to_date(‘1990-01-01‘,‘YYYY-mm-DD‘),‘是一个好人‘);
--13.查看表的数据
SELECT * FROM employee
--14.修改表记录
UPDATE employee SET birthday=to_date(‘2000-01-01‘,‘YYYY-mm-DD‘),name=‘张小三‘
WHERE id=10;
--15.检查代码
edit
--16.结束提交
commit;
--17.建表的同时添加约束
建议在建表的同时添加各种需要的约束
不建议先建再添加约束
先建主表，再建从表
--18
禁用约束
alter table tb_name disable constraint constraint_name [cascade];
alter table test disable constraint pk_test_id;
启用约束
alter table tb_name enable constraint constraint_name;
alter table test enable constraint pk_test_id;
--19添加约束的语法：
ALTER TABLE 表名
ADD CONSTRAINT 约束名 约束类型 具体的约束说明
alter table myemp add constraint pk_empno primary key(empno)
约束名的取名规则推荐采用：约束类型_表名_约束字段
主键（Primary Key）约束：如 PK_student_tno
唯一（Unique ）约束：如 UQ_student_name
检查（Check ）约束：如 CK_student_gendar
外键(Foreign Key)约束：如 FK_student_deptno
--20删除约束
ALTER TABLE 表名
DROP CONSTRAINT 约束名
ALTER TABLE teacher
DROP CONSTRAINT ck_gendar;
--21创建和使用序列
CREATE SEQUENCE seq_name
[START WITH start]
[INCREMENT BY increment]
[MINVALUE minvalue|NOMINVALUE]
[MAXVALUE maxvalue|NOMAXVALUE]
[CYCLE|NOCYCLE]
[CACHE cache|NOCACHE]
[ORDER|NOORDER]
#创建序列
CREATE SEQUENCE master_seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE
CACHE 10;
#使用序列
INSERT INTO master VALUES(master_seq.nextval,‘lkl‘,‘lkl‘,1);
INSERT INTO master VALUES(master_seq.nextval,‘lyg‘,‘801‘,1);
SELECT master_seq.currval FROM dual; //查看序列的当前值
SELECT master_seq.nextval FROM dual; //查看序列的下一个值
--22排序
SELECT * FROM student where sex=1 order by studentno;--DESC倒序
--23别名
列取别名：2种方法
列名 AS 别名
别名 别名
表取别名：1种方法
表名 别名
如果列的别名中间有空格，使用双引号引起来
列名 AS "别 名"
--24使用常量列
SELECT STUDENTNO,STUDENTNAME,PHONE,‘大学城校区‘,school,99,score FROM STUDENT;
--25限制行数
select * from student where rownum<=2;
26--查询入职日期在 1981-5-1到1981-12-31之间的所有员工信息
select * from emp where hiredate between to_date(‘1981-5-1‘,‘YYYY-MM-DD‘) and to_date(‘1981-12-31‘,‘YYYY-MM-DD‘);
--27查询一月份过生日的学生信息
select * from Student where to_number(to_char(BornDate,‘MM‘)=1);
--28随机数
SELECT DBMS_RANDOM.RANDOM FROM 表名
取一百以内的随机数（ABS取绝对值）
SELECT ABS(DBMS_RANDOM.RANDOM,100) FROM 表名
SELECT SUBSTR(ABS(DBMS_RANDOM.RANDOM,100)1,4) FROM 表名
--29日期函数包括：
ADD_MONTHS 添加月份：add_months(sysdate,3) from dual;
MONTHS_BETWEEN 取月份的范围：months_between(sysdate,日期) from dual;
LAST_DAY 计算当月最后一天
ROUND
NEXT_DAY
TRUNC
EXTRACT
--30条件函数(*****)
select decode(sex,1,‘男‘,0,‘女‘,‘不男不女‘) as 性别,Sex,Studentno from student;
--数字函数接受数字输入并返回数值结果(****)
Ceil(n) Select ceil(44.778) from dual;
取比此数大的最小数
Floor(n) Select floor(100.2) from dual;
取比此数小的最大数
--31转换函数
TO_CHAR
TO_DATE
TO_NUMBER
--32取时间
SELECT TO_CHAR(sysdate,‘YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS‘)
FROM dual;
(*************************************************)
--33其他函数
NVL（表达式1,表达式2）
SELECT sal,comm, NVL(re_level,0) FROM emp;
--如果表达式为空（null），函数结果返回值为表达式2；
--如果表达式不为空，函数结果返回值表达式1；
NVL2（p1,p2,p3）
SELECT sal,comm,nvl2(comm,comm,0),sal+nv12(comm,comm,0) totalsal from emp;
--如果p1不为null，函数结果返回值为p2
--如果p1为空，函数结果返回值为p3
NULLIF
SELECT sex,nullif(sex,0) from student;
--如果p1=p2，函数结果返回值为null;
--如果p1!=p2,函数结果返回值为p1;
(***************************************************)
--34五个聚合函数
--带聚合函数的查询，查询列中只能包含聚合函数，不能包含其他类
--如果要显示其他非统计列，须按该非统计列进行分组统计
select deptno,
SUM(SAL), 总工资
AVG(SAL), 平均工资
MAX(SAL), 最高工资
MIN(SAL),最低工资
COUNT(*),总人数
COUNT(COMM),获得奖金的人数
COUNT(DISTINCT DEPTHNO) ，部门数 --(筛选相同的数)
FROM EMP
where sal>=1000
group by deptho --(group by分组统计)
having avg(sal)>2000 --(having对分组统计的记录结果进行筛选)
order by deptho(order 排序)
;
查询北京的男同学
SELECT *
FROM Student
WHERE Address LIKE ‘%北京%‘ AND Sex=1
首字母大写（initcap）
select initcap(ename)
from emp
显示字符为5的（length）
select ename
from emp
where length(ename)=5
2、查询姓名第二个字为“丽”的女同学
SELECT *
FROM Student
WHERE StudentName LIKE ‘_丽%‘ AND Sex=0
substr（‘helloworld‘,1,5）从第一个字符取到第五个结束
select substr(ename,1,3)
from emp;
instr（‘helloworld‘,‘w‘）查询w在输入的值的位置
lpad （salary，10，*）在salary列中给十个位置，填不满左边用*补齐
rpad 则相反
trim （‘H‘ from ‘hhheeasdh‘） from dual//区首尾的字
replace（‘asdada‘,‘a‘）去除所有的、(ename,‘A‘,‘a‘)
替换
select replace(ename,‘A‘,‘a‘)
from emp;
数值函数
round（435.45,2）取两位小数
mod(1100,100) 取余
trunk(100.567) from dual 数字截取
round(100.567) from dual 四舍五入
其他函数
Select nvl(null,0)from dual;判断值为空就取0
decode 条件判断
select decode(400,1,2,3,400,500)from dual;如果有相同的值就取后一个
case when then
select name,(case ownertypeid when 1 then ‘居民’)意思同上
分析函数
值相同，排名相同，序号跳跃
Select rank() over(order by usenum desc), t.* from dual t;
值相同，排名相同，序号连续
Select dense_rank() over(order by usenum desc), t.* from dual t;
序号连续，不管值是否相同
值相同，排名相同，序号跳跃
Select row_number() over(order by usenum desc), t.* from dual t;
集合运算
并集
Select * from dual where id>5
Union all
Select * from dual where id<8
并集（去掉重复记录）
Select * from dual where id>5
Union all
Select * from dual where id<8
交集（两个结果集的重复部分）
Select * from dual where id>5
intersect
Select * from dual where id<8
差集
Select * from dual where id>5
Minus
Select * from dual where id<8
数据库对象
--将数据库对象
--以管理员身份，将创建同义词的权限授予给某个用户，方可创建同义词
grant create synonym to java43
--以管理员身份，将创建视图的权限授予给某个用户，方可创建视图
grant create view to java43
--1.创建简单的视图
create view view_emp as
select * from emp where emp_id=1;
--2.查询简单的视图
select * from view_emp where emp_id=1;
--3.修改视图数据
update viem_emp set name = ‘小晓‘ where id=1;
公共同义词只能有管理员创建
system
其他用户访问公共同义词的前提条件是，用户必须获得对同义词源对象访问的权限
私有同义词
CREATE SYNONYM emp FOR SCOTT.emp;
公有同义词
CREATE PUBLIC SYNONYM emp_syn FOR SCOTT.emp;
--4.带检查约束的视图
create view view_empid as
select * from emp_id where id=2;
with check option;//约束：无法修改id为2的语句，
--5.只读视图
creat or replace view view_empid as
select * from emp where id=1
with read only;//设置只读
--6.创建带错误的视图(force)
create force view view_test as
select * from t_test;
--7创建自动刷新的物化视图 –基表发生commit操作，自动刷新物化视图
create materialized view mv_addss1
refresh
on commit
as
select ad.id,ad.name,ar.name arname from t_add ad,t_area ar
where ad.areaid=ar.id
--创建时不生成数据的物化视图
create materialized view mv_addss1
build deferred
refresh
on commit
as
select ad.id,ad.name,ar.name arname from t_add ad,t_area ar
where ad.areaid=ar.id
--查询物化视图
Select * from mv_addss1
Insert into T_ADD values (12.55,33)
--第一次必须手动执行刷新2020-05-25
begin
DBMS_MVIEW.refresh（’ mv_addss1’,’C’）;
end
--创建索引
create index student_sex_index
on student(sex);
--创建唯一索引
Create unique index student_phone_index
ON student(phone);
--序列
序列是Oracle提供的用于产生一系列唯一数字的数据库对象。
--创建简单的序列
Create sequence 序列名;
//案例
create sequence 序列名
increment by 10//10开始
start with 10//每次增长是
minvalue 5
maxvalue 100
create sequence students2
start with 10
increment by -1
maxvalue 10
minvalue 1
cycle
cache 5
创建索引
create index student_sex_index
on student(sex);
唯一索引
create unique index student_index
ON student(name);
反向键索引
create index student_sex_index
ON student(sex)reverse
create index student_sex_index
ON student(sex)rebuild noreverse
位图索引
create bitmap index student_sex_index
ON student(sex);
索引组织表
create table student_index(
vencode number(4) primary key,
vencode varchar(20)
)
organization index;
基于函数的索引
create index student_id
ON student(LOWER(id));
获取索引的信息
与索引有关的数据字典视图有：
USER_INDEXES － 用户创建的索引的信息
USER_IND_PARTITIONS － 用户创建的分区索引的信息
USER_IND_COLUMNS － 与索引相关的表列的信息
数据导出与导入
整库导出
exp system/orcl fully=y file=导出的文件名
整库导入命令
Imp system/orcl full=y
按用户导出
Exp system/orcl owner=wateruser file=wateruser.dmp
按用户导入
Imp system/orcl file=wateruser.dmp fromuser=wateruser
按表导出（有多个表用逗号分隔开）
Exp system/orcl file=a.dmp table=t_account,b_account
按表导入
Imp system/orcl file=a.dmp table=t_account,b_account
高级查询
内联接语法
SELECT
FROM 表1
INNER JOIN 表2
ON
设置字符长度
内联接注意事项：
- 取出两个表比较关键字都有的记录
- 内联接原理：一个表中的每一条记录分别于另外一个表的所有记录从头到尾进行比较，如果与比较关键字相同，就列出来，否则就忽略。