一、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;
聚合函数
分组查询