创建数据库
create database databaseName;
Oracle:无法使用create database,oracle没有数据库概念但有表空间;
一般使用:
drop database if exists databaseName;
create database databaseName default character set utf8mb4 collate utf8mb4_general_ci;
删除数据库
drop database if exists databaseName;
修改数据库字符集
alter database databaseNam charset utf8mb4 collate utf8mb4_general_ci;
创建表
create table tableName(
id int(32) primary key,
name varchar(128) default null
);
一般使用:
drop table if exists tableName;
create table tableName(
id bigint primary key not null auto_increment comment 'id,主键',
`code` varchar(128) unique not null comment '唯一码',
`status` enum('-1','0','1') not null default '0' comment '状态,-1未连接,0关,1开',
recorded_date datetime not null comment '录入时间'
)engine=innodb default charset=utf8mb4 collate utf8mb4_general_ci comment='xx表';
//使用CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci也行
Oracle:同mysql,但无法在建表语句内使用comment,需要在建表之后再添加。
create table ww_offic_doc_upload
(
id int not null primary key,
file_xml clob,
file_name varchar2(300),
file_title varchar2(300) not null,
file_number varchar2(300),
receive_number varchar2(300),
requestid varchar2(100) not null,
operate_name varchar2(100),
upload_user_id int not null,
upload_user_name varchar2(50),
upload_time varchar2(20) not null
);
comment on table ww_offic_doc_upload is '公文上传(导入)信息记录表。';
comment on column ww_offic_doc_upload.file_xml is 'xml文件内容';
comment on column ww_offic_doc_upload.file_name is '文件名';
comment on column ww_offic_doc_upload.file_title is '文件标题';
comment on column ww_offic_doc_upload.file_number is '文件编号';
comment on column ww_offic_doc_upload.receive_number is '回文编号';
comment on column ww_offic_doc_upload.requestid is '流程requestid';
comment on column ww_offic_doc_upload.operate_name is '操作名称';
comment on column ww_offic_doc_upload.upload_user_id is '上传人id';
comment on column ww_offic_doc_upload.upload_user_name is '上传人名称';
comment on column ww_offic_doc_upload.upload_time is '上传时间';
oracle exists函数
exists表示()内子查询语句返回结果不为空,效果等同于count()>0,可以配合not一起使用。
当父查询数据量小于子查询数据量时,使用exists的效率更高,反之使用in效率更高。
示例:
select * from t1 where exists(select 1 from t2 where t1.id=t2.id);
oracle判断表是否存在
declare tb_count number;
begin
select count(1) into tb_count from user_tables where table_name = upper('tableName') ;
if num > 0 then execute immediate 'drop table tableName'; end if;
end;
oracle immediate
immediate是oracle用于动态执行字符串形式的sql,常用于sql编程。
传参实例:
--参数:v_table ,v_id
execute immediate 'select * from '||v_table||' where id=:id' using v_id;
--参数v_id,v_name
str varchar2(200);
str:='select * from user_t where id=:1 and name=:2';
execute immediate str using v_id,v_name;
删除表
drop table tableName;
Oracle:同mysql;
修改表名
alter table oldTableName rename newTableName;
Oracle:同mysql;
修改表字符集
alter table tableName charset=utf8mb4 collate=utf8mb4_general_ci;
添加列
alter table tableName add column columnName varchar(10);
Oracle:
alter table tableName add (columnName varchar2(10) );
添加列到指定列之后(仅限mysql,oracle不支持)
alter table tableName add column columnName2 varchar(10) after columnName1;
删除列
alter table tableName drop column columnName;
Oracle:
alter table tableName drop (columnName);
修改列名
alter table tableName change column oldColumnName newColumnName varchar(10);
修改列类型(或修改列长度)
alter table tableName modify column columnName varchar(10);
或
alter table tableName modify columnName varchar(10);
Oracle:
alter table tableName modify (column varchar2(10));
添加主键
alter table tableName add primary key(id);
删除主键
alter table tableName drop primary key;
添加外键
alter table table1 add constraint foreignKeyName foreign key( table1_ColumnName) references table2(table2_PrimaryKeyColumnName);
删除外键
alter table table1 drop foreign key foreignKeyName;
添加自增
auto_increment必须要求该列是主键(或别的键,详细请看文章:[MySQL添加/删除主键、外键、唯一键、索引、自增](https://blog.csdn.net/u012643122/article/details/52890772)),所以
如果该列不是主键:
alter table tableName change columnName columnName int(16) not null primary key auto_increment;
如果该列是主键:
alter table tableName change columnName columnName int(16) not null auto_increment;
删除自增
alter table tableName change columnName columnName int(16);//删除自增长
oracle自增
oracle不支持自增,但可以使用触发器+序列实现自增效果,并且在使用时需特别注意,为每个表单独创建一个序列,不要多表共用一个序列,不然自增的id不是连续的。
create or replace trigger triggerName before insert on tableName
for each row begin select sequenceName.nextval into :new.id from dual; end;
示例:
create table ww_offic_doc_operlog
(
id int not null primary key,
operate_name varchar2(100),
upload_user_id int not null,
upload_user_name varchar2(50),
upload_time varchar2(20) not null
);
comment on table ww_offic_doc_operlog is '公文上传(导入)历史记录表。';
create sequence ww_offic_doc_operlog_seq start with 1 increment by 1 cache 10;
create or replace trigger ww_offic_doc_operlog_tri before insert on ww_offic_doc_operlog
for each row
begin
select ww_offic_doc_operlog_seq.nextval into :new.id from dual;
end;