前言
首先，Oracle的安装在这里就不多介绍了，本文本质上算是Oracle学习过程中的一个记录，基本都是基础，也是工作中涉及Oracle的常用知识，可以说学会了以下知识就完全够资格当一个SQL boy了。当然，本人算是比较讨厌文字的，所以本文不会用过多的文字作介绍。
关于用户
Oracle安装好之后有两个默认的用户，但是一般是DBA（数据库管理员）进行管理，一般只会创建其他用户给普通员工使用，不过也还是需要了解一下用户创建等内容。
Oracle的两个默认用户：
sys 超级管理员：可以管理所有用户的所有内容
system 一般管理员：管理常用的日常操作
创建一个自己的用户 （例如 用户：zx 密码：123456）
create user zx identified by 123456;
修改某个用户的密码
alter user zx identified by 111222;
删除某个用户
drop user zx;
给用户赋予不同的权限
grant connect,resource,dba to zx;
SQL 语句：结构化的查询语言
DCL：数据控制语言（grant）
DDL：数据定义语言（create alter drop truncate）
DQL：数据查询语言（select）
DML：数据操纵语言（insert update delete）
一、表格
1 建表
create table 表格名字 (
列的名字 数据类型 约束条件 ,
列的名字 数据类型
);
例：创建一个学生表
create table student(
stuid integer, --整数
sname varchar2(12), --字符串（长度）--不定长
ssex char(3), --字符串（长度）--定长
shigh number(4,2), --小数（总长度，小数的长度）
mobile char(11),
birth date --日期
);
大文件可用数据类型（一般大于4GB） CLOB 文本的大文件（html 小说...） BLOB 二进制文件的大文件（图片 视频 音乐...）
约束条件：
primary key 主键约束，不能重复也不能为空，一表一主键，可联合主键
not null 非空约束，可以重复但是不能为空
Unique 唯一约束，可以为空但是不能重复
check(限制条件) 检查约束，限制用户的输入范围控制在规定范围内
create table student_2(
stuid integer primary key,
sname varchar2(12) not null,
ssex char(3) check(ssex='男' or ssex='女'),
shigh number(4,2) check(shigh>=1 and shigh<=3),
mobile char(11) unique,
birth date
);
2 修改表结构
新增列
alter table 表名 add 列名 数据类型 约束条件;
alter table student add parent_name varchar2(12) not null;
删除列
alter table 表名 drop column 列名;
alter table student drop column shigh;
修改列 修改的是列的数据类型
alter table 表名 modify 列名 新的数据类型 约束条件;
alter table student modify mobile number(11);
重命名列
alter table 表名 rename column 旧列名 to 新列名;
alter table student rename column mobile to phone;
表的重命名
alter table 旧表名 rename to 新表名;
alter table student rename to stu;
3 修改表中的约束条件
建表的时候，约束条件的名字是系统自动分配的，单独的操作约束条件，名字需要自己来取
新增主键约束
alter table 表名 add constraint pk_表名_列名 primary key (列名);
alter table stu add constraint pk_stu_stuid primary key(stuid);
新增唯一约束
alter table stu add constraint uni_stu_phone unique(phone);
新增非空约束
alter table stu add constraint ck_stu_sname check(sname is not null);
新增检查约束
alter table stu add constraint ck_stu_ssex check(ssex='男' or ssex='女');
删除约束
alter table 表名 drop constraint 约束名;
alter table stu drop constraint sys_c0010405;
4 外键约束的添加
子表的数据必须来自于父表，父表的列，必须是主键
1.先需要一个父表
create table class_info(
cid integer primary key,
cname varchar2(12),
tname varchar2(12)
);
2.然后创建有外键约束的子表
create table student_3(
stuid integer primary key,
sname varchar2(12),
classid integer,
foreign key (classid) references class_info(cid)
);
在一个已经存在的表上，去添加外键约束：
alter table stu
add constraint fk_student3_classid
foreign key(classid) references class_info(cid);
删除外键的约束条件：
alter table stu drop constraint fk_student3_classid;
注意，少用外键：
1.有外键的表格，操作效率会慢；
2.外键会限制父表的操作；
DML的操作：表格内容的修改，必须要使用commit或者是rollback去提交或者回滚表格的数据。
5 插入数据
第一种句式：往表格的每一列都添加一个数据
insert into 表名 values(数据1,数据2…);
如：insert into class_info values(1001,'一年一班','李老师');
第二种句式：往表格的部分列添加数据
insert into 表名(列名1,列名2…) values(数据1,数据2…);
如：insert into class_info(class_id,cname) values(1004,'一年四班');
6 更新数据
update 表格名 set 列=新值 where 列=值;
如：update student_2 set birth=date'2012-9-19' where stuid=2;
7 删除数据
删除整个表格的所有数据：
方法1：delete from 表名;
方法2：Truncate table 表名;
删除选中的部分数据：+ where条件
delete from 表名 where 列=值;
如：delete from class_info where class_id=1002;
8 删除表
drop table 表名;
9 drop、delete、truncate三者的区别
1、drop是删除整个表的结构，包括了其中的数据。
2、delete是一个DML语句，可以回滚和提交，truncate是一个DDL语句，不能回滚和提交的。
3、delete删除数据可以添加where进行数据的筛选，truncate不能添加筛选条件。
4、delete是以行为单位进行数据的删除，truncate是以表为单位清空。
10 表格的复制
注意：不会复制原表中的约束条件
create table 新表的名字 as select * from 另一张表名;
如：create table class_2 as select *from class_info; --结构+数据
create table class_3 as select * from class_infowhere 1=2;–只复制结构
11 DQL语句：数据查询语句
11.1 数据的精确筛选
select 列，列 from emp where条件; --用条件限定行 例：查询工资为5000的员工信息
select * from emp where sal=5000;
11.2 数据的范围查询
例：查询工资小于1500的员工信息
select * from emp where sal<1500;
例：部门不等于20的员工信息
select * from emp where deptno!=20;
11.3 数据的逻辑查询
取反的时候用not,需要同时满足的用and，两者满足一个即可的用or。
例：部门不等于20的员工信息
select * from emp where not deptno=20;
select * from emp where deptno<>20;
例：查询20号部门里面，工资小于1500的员工信息
select * from emp where deptno=20 and sal<1500;
例：查询10号部门或者20号部门员工的信息
select * from emp where deptno=10 or deptno=20;
11.4 数据的模糊查询
例：查询名字中是A字母开头的所有员工信息
select * from emp where ename like 'A%';
例：查询名字中包含I字母的所有员工信息
select * from emp where ename like '%I%';
例：查询第三个字母为I的员工信息
select * from emp where ename like '__I%';
11.5 搜索NULL空值的内容
例：搜索mgr为空的信息
select * from emp where mgr is null;
例：搜索mgr不为空的信息
select * from emp where mgr is not null;
11.6 范围搜索
in：简化当前的sql语句，也可以用来做嵌套的查询
例：搜索7369 7499 7788三个员工的信息
1、select * from emp
where empno=7369 or empno=7499 or empno=7788;
2、select * from emp
where empno in (7369,7499,7788);
between ... and ... ：
例：查询工资在1000到3000之间的员工信息（包含1000和3000）
1、select * from emp where sal>=1000 and sal<=3000;
2、select * from emp where sal between 1000 and 3000;
12 通配符和转义符
在Oracle中 % 表示通配符，\ 为转义符，\% 表示一个普通的字符百分号，但字符串中使用% 则需要escape来标注\为转义符：
例：查询出名字中包含%的员工信息
select * from emp where ename like '%\%%' escape '\';
% 通配符，表示在这个位置有0-N个任意的字符内容
_ 通配符，表示在这个位置有1个任意的字符内容
例：查询出名字第二个字符是 _ 的员工信息
select * from emp where ename like '_\_%' escape '\';
二、函数
1 数字类型
abs(x) 计算绝对值
select abs(-1) from dual; --输出：1
round(x, 小数精度) 四舍五入
select round(1.23456,4) from dual; --输出：1.2346
trunc(x, 小数精度) 截取数字
select trunc(1.23456,4) from dual; --输出：1.2345
ceil(x) 向上取整
select ceil(1.00000001) from dual; --输出：2
floor(x) 向下取整
select floor(1.9999999) from dual; --输出：1
power(x, 次方) 幂运算
select power(3,4) from dual; --输出：81
mod(被除数, 除数) 模运算，取余数运算
select mod(10,3) from dual; --输出：1
2 字符串类型
substr() 字符串的截取 substr(字符串, 开始位置, 连续取值长度) 字符串的截取 开始位置为负数的话，表示倒着数数 如果取值连续长度为空不写的话，就是一直取到最后
select substr('abcdefg',2,4) from dual;--输出：bcd
concat(字符串1, 字符串2) 字符串的拼接，只能拼两个字符串 可用 || 替代concat()
1、select concat(‘abc’,’123’) from emp;--输出：abc123
2、select concat(concat(‘abc’,':'), ‘123’) from emp;--输出：abc:123
3、select ‘abc’||':'|| ‘123’from emp;--输出：abc:123
length(x) 计算字符串的长度
select length(‘abcd’) from emp; --输出：4
replace(x, '旧内容', '新内容') 替换字符串的内容
select replace(‘ABCD’,'A','.') from emp; --输出：.BCD
trim() ltrim() rtrim() 去除字符串左右两边的空格
select trim(' lilei ') from dual;--输出：lilei
lpad(字符串, 总长度, 填充符号) rpad() 进行左右两边字符的填充
select lpad(ename,6,'-') from emp;
select rpad(ename,6,'#') from emp;
3 日期类型
sysdate 获取当前系统的时间日期
select sysdate from dual;
add_months(日期, 月的数量) 月份的偏移
select add_months(sysdate,-3) from dual;
months_between(日期1, 日期2) 日期的间隔
select months_between(date'2020-09-01',date'2019-08-16') from dual;
last_day(日期) 获取最后一天
select last_day(sysdate) from dual;
获取当前时间所在月的第一天
select trunc(sysdate,'mm') from dual;
获取当前时间所在年的第一天
select trunc(sysdate,'yyyy') from dual;
4 类型转换
将时间转换成字符串 使用to_char()提取日期里面的年月日时分秒周
select to_char(sysdate,'yyyy') from dual;
select to_char(sysdate,'mm') from dual;
select to_char(sysdate,'dd') from dual;
select to_char(sysdate,'hh') from dual;
select to_char(sysdate,'mi') from dual;
select to_char(sysdate,'ss') from dual;
select to_char(sysdate,'day') from dual;
拼接出一个明天早上10点的时间出来，显示成2020/10/21 10:00:00
select concat(to_char(sysdate+1,'yyyy/mm/dd'),' 10:00:00')
from dual;
将字符串转换成时间 to_date(字符串, 时间格式)
select to_date('08-31-1986','mm-dd-yyyy') from dual;
select to_date('08-31-1986 23:59:59','mm-dd-yyyy hh24:mi:ss') from dual;
select to_date(concat(to_char(sysdate+1,'yyyy/mm/dd'),' 10:00:00') , 'yyyy/mm/dd hh24:mi:ss') from dual;
将字符串转换成数字 to_number(x)
select to_number('123') from dual;
评论区