Oracle基本SQL操作-SQL语句

一、sql分类：

DDL：数据定义语句，create、alter、drop

DML：数据操作语句，insert、update、delete、select。。。for update；

DQL：数据查询语句，select，order by   group by；

TCL：事务操作语句，begin，commit、rollback，savepoint；

DCL：数据控制语句，权限控制，grant、revoke；

二、数据类型：

char（）：定长

varchar2（）：不定长

number(p,s):p表示数字最大位数，s指小数位数

date：日期

timestamp：时间戳，及秒后6位

clob：文本，非结构化的xml文档

blob：二进制，比如图形，视频，声音

函数：sysdate、systimestamp

注意：oracle与mysql的区别，在oracle里面，from  dual是固定结构，必须要写完整的，dual相当于一个临时表：

-- 查询当前系统日期及时间
SELECT  sysdate,systimestamp  from dual;

三、建表语句：

数据存储在哪？

建表语法：在oracle中，关键字不区分大小写；

create  table  table_name(

字段名称  字段类型   约束（唯一、主键）,

);

注意：oracle不支持在建表的时候，在字段后加comment  ‘字段注释’  添加注释

-- 建表语句
create table t_user(
id  number(5),
name varchar2(255),
age number(3),
address varchar2(255)
);

注意：关键字不能用``,也可以使用图形化界面快速创建；

给表添加注释，给表的字段添加注释：

-- 给t_user表添加注释,注意是给表，不是表字段
comment on table t_user is '用户表';

--给表的字段添加注释
comment on column t_user.id is '主键';
comment on column  t_user.name is '名字';
comment on column t_user.age is '年龄';
comment on column t_user.address is '住址';

删除表

-- 删除表
drop TABLE t_user;

修改表结构：

-- 修改表结构，新增字段
alter  table t_user add  gender char(1);
-- 修改表结构，修改字段类型和长度
alter table t_user modify  gender varchar2(1);
-- 修改表结构，修改字段名称
alter table t_user rename column gender to sex;
-- 删除字段
alter table t_user drop column sex;

四、表结构约束：

非空约束：建表时指定字段：not  null，给默认值：default  0；

-- 建表语句
drop table t_user;
create table t_user(
id  number(5) not null,
name varchar2(255) not null,
age number(3) default 0,
address varchar2(255) default ''
);

主键约束：能够唯一标识该字段的唯一约束+不能为空，一张表只能出现一个主键，但可以出现联合主键（一个主键包含多个字段）；使用primary  key标识：

-- 建表语句
drop table t_user;
create table t_user(
id  number(5) primary key,            -- 主键
name varchar2(255) not null unique,   -- 非空 + 唯一约束
age number(3) default 0,
address varchar2(255) default ''
);

-- 建表后维护主键
alter table t_user add CONSTRAINTS pk_user_id primary key(id);

外键约束：将primary key改为 foreign  key；

唯一约束：有值的情况下，不能重复，不包括null，字段后加unique；

-- 建表语句
drop table t_user;
create table t_user(
id  number(5) not null,
name varchar2(255) not null unique,   -- 非空 + 唯一约束
age number(3) default 0,
address varchar2(255) default ''
);

check约束（在类型基础上再约束范围之类的，和postgreSQL一样）

-- 建表语句
drop table t_user;
create table t_user(
id  number(5) primary key,            -- 主键约束
name varchar2(255) not null unique,   -- 非空 + 唯一约束
age number(3) default 0 check(age>0  and age<150),  --检查约束
address varchar2(255) default '',
gender varchar2(10) default '男' check(gender in ('男','女'))  -- 检查约束
);

五、DML语言（insert、update、delete）

insert：

insert into t_user(id,name,age,address,gender)values(1,'zhangsan',18,'四川成都双流','男');

多行插入，表复制:

-- 多行插入
insert into t_user_copy select * from t_user;

update：

update t_user set name='lisi' where id=1;

delete：

-- 删除数据，还保留了缓存，有可能被恢复
DELETE FROM t_user  WHERE id=4;
-- 删除全部数据
DELETE FROM t_user;
-- truncate,清空数据，不做缓存，效率高
truncate table t_user;

select 。。。for update

主键唯一且自动递增，使用序列：

创建序列：

-- 创建序列
create sequence sq_t_user_id 
increment by 1   -- 每次自增的数量
start with 1    -- 从1开始
nomaxvalue      -- 没有最大值
nocycle         -- 不循环，不重复
cache 100       -- 缓存100，不需要每次增加数量时再生成，可以一次生成100个；

查看序列：

注意，在第一次生成序列时，得先执行nextval，才能查看currval

使用序列：

-- 插入数据时使用序列
insert into t_user(id,name,age,address,gender)values(sq_t_user_id.nextval,'zhangsan3',28,'四川成都武侯','男');

六、DQL

简单查询：

查询like匹配固定长度的字段：

-- 使用一个下划线_表示匹配后面一位字符
SELECT id,name,age,address,gender FROM t_user where name like 'zhangsan_';

函数：

add_months(字段)

last_day（字段）

substr(字段，长度)

length(字段)

not like  ‘%R%’；

replace（字段，‘’，‘’）

extract(year   from  startdate)  : 提取年份、月份

nvl(30,0):不为空则取30，否则取0

range（）：取小数，四舍五入

聚合函数：

count(*)：统计所有列的函数

count(1) ：在列后面再加一列，值用1填充，然后进行统计，也会包含空的结果

count(字段)：统计该列数据不为空的

min、max、sum、range、agv

尽量使用exists代替in，因为in会每次获取结果后判断一次；

分组函数：

group by-+聚合函数

多表查询：

交叉连接：t1，t2，全笛卡尔积

内联：inner join  。。。on。。。

左连接：left join

右连接：right  join

全连接：full join ，保留左右两侧不满足条件的数据

关联查询优化：

-- 关联查询优化,将过滤条件放在from 后面，使用子查询
SELECT
    * FROM t_user tu 
    LEFT JOIN t_user_copy  tup ON tu.id=tup.id
    WHERE tu.name='zhangsan';
-- 思路：优化前，将两张表的数据做笛卡尔积关联，再过滤条件，这样会生成一张数据量比较大的临时表，实际上有用的信息在where后，数据量很少  
-- 优化后，在left前进行单表数据过滤，再进行关联，这样生成的关联表的数据会减少很多；
    SELECT
    * FROM 
    (select * from t_user where name='zhangsan')
    tu 
    LEFT JOIN t_user_copy  tup ON tu.id=tup.id
    WHERE tu.name='zhangsan';

分页查询的三种方式：

1、在oralce里面，表有个rownum字段，使用rownum进行分页，rownum类似于mysql中的隐藏rowid列，从1开始，通过截取rownum的范围实现分页：

-- 通过rownum实现分页,但单个select只能实现<, 当执行>2时，由于rownum是从1开始，
--所以不能跳过1直接从2开始，因此只能使用子查询，将结果集当成另外一个表进行分页
SELECT id,name,age,address,gender,ROWNUM FROM t_user where rownum <=4; 

-- 查出来的数据为空
SELECT id,name,age,address,gender,ROWNUM FROM t_user 
where rownum <=4  and rownum >=2; 
-- 使用子查询,相当于出现了两个rownum，然后再分页
SELECT t.*,rownum from 
(
SELECT id,name,age,address,gender,ROWNUM num FROM t_user where rownum <=4
)  t WHERE t.num > 2;

缺点：简单、效率低

2、使用offset   rows  fetch next ，仅从12c开始支持，11g不支持；

-- 从下标 > 2开始，取3条记录，
SELECT id,name,age,address FROM t_user order BY id OFFSET 2 ROWS FETCH NEXT 3 ROWS ONLY;

3、手动生成行号，使用between  and进行分页：

-- 使用row_number()函数生成行号，再指定排序方式，然后使用子查询和between  and
-- 注意不能直接使用rownum进行between  and，原因与第一种方式一样
-- 数据库自带的rownum不能使用between and，可以手动生成行号，使用between and
SELECT
    * FROM
(SELECT id,name,age,address,gender,row_number() over(order by t_user.id) r FROM t_user)
WHERE r BETWEEN 2 AND 4;

聚合函数

分组查询