oracle DDL/DML/约束总结
目录
2.DDL语句
2.1 create语句
2.1.1 创建含有约束的表
见 约束-约束的定义 部分
2.1.2 复制其他表的结构及数据
复制全部字段：
Create table tb_clazz2 as select * from tb_clazz1;
复制部分字段：
Create table tb_clazz3 as select name from tb_clazz1;
2.2 alter语句
2.2.1 修改表名
alter table tb_clazz rename to tb_class;
或者
rename tb_clazz to tb_class;
2.2.2 修改列
增加一列：
alter table tb_stu add gender varchar2(6);
修改列的属性：
alter table tb_stu modify gender varchar2(10);
修改列名：
alter table tb_stu rename column gender to sex;
删除一列:
alter table tb_stu drop column gender;
2.3 truncate 语句
truncate语句用来截断表（其他ddl语句针对的是表中的数据，但truncate针对的是表），截断（删除）后表中的数据全部删除，且不可回滚，但表结构依然存在
truncate table tb_clazz;
2.4 drop语句
删除表中数据，同时删除表结构,可以通过flashback回滚
drop table tb_clazz;
2.5 flashback 语句
可用于恢复drop删除的表
flashback table tb_stu to before drop;
2.6 comment 语句
2.6.1 为表添加注释
comment on table tb_stu is 'student table';
2.6.2 为列添加注释
comment on column tb_stu.gender is 'student's gender';
3. DML语句：
3.1 Insert语句
3.1.1 普通插入
Insert into tb_clazz2(id,name) values(1,'A');
3.1.2 复杂插入
Insert into tb_clazz2 select * from tb_clazz1;
插入其他表中的部分字段
Insert into tb_clazz2(id,name) select id,name from tb_clazz1;
3.2 update语句
修改指定列的指定行
update tb_clazz set name='B' where id=1;
如果没有where条件，则所有行都会改变
update tb_clazz set name='B';
3.3 delete语句
delete from tb_clazz where id=1;
3.4 select语句
3.4.1 简单的查询语句
-
+-*/运算
select empno,ename,comm,comm*12 from emp;
-
||字符串连接符
select empno||'_'||ename from emp;
-
别名
select deptno (as) 编号,ename (as) 姓名from emp; --as可以省略
-
去重
select distinct deptno from emp;
3.4.2 where 条件查询
-
特殊比较运算符
< > ：不等于
between ... and ： 包含两侧的值
in(a,b):a或b
eg：select * from dept where deptno in(20,30);
<=> select * from dept where deptno = 20 or deptno = 30;
like : 其中%代表任意字符，_代表任意一个字符
eg: _A% 代表第一个字符任意，第二个字符为A，其他部分任意
is null : select * from dept where comm is null;
-
逻辑运算符
and
or
not：select * from dept where comm is not null;
select * from dept where deptno not in(20,30); --不包括20和30
select * from dept where not deptno>20 and dept <30;
select * from dept where deptno not between 20 and 30;
3.4.3 order by 排序
select * from dept order by deptno (asc); --默认为升序asc
select * from dept order by deptno desc;
4 约束
4.1 约束的定义
4.1 .1列级约束
create table tb_student(
id number primary key, --主键约束
name varchar2(16) not null, --非空约束
gender varchar2(2) check(gender = '男' or gender = '女'), --检查约束
age number not null check(age > 19 and age <30),
email varchar2(20) unique, --唯一键约束
address varchar2(30) default '南京', --默认值
clazz_id number not null reference tb_clazz(id) --外键约束
);
create table tb_clazz(
id number primary key,
name varchar2(20) not null
);
4.1.2 表级约束
三键（主键，外键，唯一键）、检查（check）可以使用，not null及default只可用于列级约束
create table tb_clazz(
id number,
name varchar2(20) not null,
constraints clazz_id primary key (id) 其中clazz_id为此约束的名字
);
create table tb_student(
id number,
name varchar2(10) not null,
gender varchar2(1),
age number,
email varchar2(20),
address varchar2(20) default '南京',
clazz_id number,
constraints stu_id primary key (id),
constraints stu_gender check (gender = '男' or gender = '女'),
constraints stu_age check(age > 19 and age <30),
constraints stu_email unique (email),
constraints class_id foreign key (clazz_id) references tb_clazz(id)
);
ps：联合主键
create table tb_person(
lastname varchar2(18),
firstname varchar2(18),
constraints person_pk primary key(lastname,firstname)
);
4.2 约束的维护
4.2.1增加约束
alter table tb_stu add constraints student_id primary key(id); --student_id为约束的名字
alter table tb_stu add constraints student_email unique(email);
alter table tb_stu add constraints student_gender check(gender='男' or gender='女');
alter table tb_stu add constraints stu_class foreign key(class_id) references tb_clazz(id);
4.2.2删除约束
alter table tb_stu drop constraints student_id; --student_id为约束的名字
4.2.3修改约束名称
没有专门的语句，oracle的实现原理是先删除，再添加
4.2.4禁止约束
alter table tb_stu disable constraints student_email;
4.2.5激活约束
alter table tb_stu enable constraints student_email;
4.2.6 查询约束
查询一张表的约束：
select constraint_name,constraint_type,search_condition from user_constraints where table_name = 'TB_STU'; --此处表名一定要大写
查询与列相关的约束：
select constraint_name,column_name from user_cons_columns where table_name='TB_STU' --此处表名一定要大写
5.函数
5.1 单行函数
5.1.1 字符函数
-
大小写转换
lower();
upper();
initcap(); -把每个单词的首字母变为大写
eg：initcap("hello world")=> Hello World
-
截取字符串
substr（str，i，[j]） --从i（起始值是1）开始，截取j个,没有j则去到末尾
-
长度
length（str）
-
检索字符串
instr（str，char） 返回char字符在str中的第一个位置，没有则返回0
-
两侧增加内容
rpad（str，6，'*'） str右侧不满6位，则用*补满至6位
lpad （str，6，'*'） str左侧不满6位，则用*补满至6位
-
两侧删除内容
trim（str） 去除收尾的空格
trim([both]'H' from 'HeadH') 去除首位的H (both可写可不写)
trim(leading 'H' from 'HeadH') 去除首位的H
trim(trailing 'H' from 'HeadH') 去除尾部的H
-
替换
replace('hello','ll','ff') 把hello中的ll替换为ff
5.1.2 数值函数
-
四舍五入 round
select round (45.965,2) from dual; ==> 45.97
-
截断 trunc
select trunc(45.965,2) from dual; ==>45.96
-
取模 mod
select mod(1600,300) from dual; ==>100
5.1.3 日期函数
-
select sysdate from dual; --系统时间
-
trunc
-
select trunc(sysdate+1/24,'hh') from dual; --当前时间的下一个小时（理解:sysdate加上1/24天，截断到小时，同理，sysdate+1/12就表示当前时间的后两个小时）
ps：trunc默认截断到天，即trunc(sysdate)==trunc(sysdate,'dd')
-
round
-
select round(sysdate) from dual; --按天四舍五入， 12之前则是今天，否则则是明天，默认还是天
-
select round(sysdate,'mm') from dual; --按月四舍五入，月份过半为下月第一天，否则为本月第一天
-
month...between
-
lastday
-
addmonth
-
next_day()
