Oracle基本语句

建表

CREATE TABLE users (
  id int  NOT NULL,
  name varchar(20),
  age int , 
  sex varchar(10),
  birthday date ,
  time date
);

CREATE TABLE works (
  id int NOT NULL,
  bumen varchar(20),
  zhiwei varchar(20)
);

CREATE TABLE admin(
  id int NOT NULL,
  name varchar(20),
  pwd varchar(20)
);

增加

-- 增加一条，这需要一条一条执行
insert into user values
('1','白起','37','男',to_date('2021@05@01','yyyy@mm@dd'),to_date('2021-1-30 17:20:33','YYYY-MM-DD HH24:MI:SS'));
insert into user (name,sex) values ('lisi','woman');

-- 一次性增加多条
insert all 
into users values ('2','王翦','32','男',to_date('2021/05/01','yyyy/mm/dd'),to_date('2021-1-30 16:20:33','YYYY-MM-DD HH24:MI:SS'))
into users values ('3','李牧','30','男',to_date('2021?05?01','yyyy?mm?dd'),to_date('2021-1-30 15:20:33','YYYY-MM-DD HH24:MI:SS'))
into users values ('4','廉颇','54','男',to_date('2021\05\01','yyyy\mm\dd'),to_date('2021-1-30 14:20:33','YYYY-MM-DD HH24:MI:SS'))
into users values ('1001','田雯','31','女',to_date('2021-05-01','yyyy-mm-dd'),to_date('2021-1-30 22:20:33','YYYY-MM-DD HH24:MI:SS'))
into users values ('1002','赵胜','42','女',to_date('2021%05%01','yyyy%mm%dd'),to_date('2021-1-30 22:20:33','YYYY-MM-DD HH24:MI:SS'))
into users values ('1003','黄歇','39','男',to_date('2021*05*01','yyyy*mm*dd'),to_date('2021-1-30 18:20:33','YYYY-MM-DD HH24:MI:SS'))
select * from dual;

-- 一次性增加多条，并且可以增加不同表
insert all 
into works values('1','后勤','保安')
into works values('2','后勤','食堂')
into works values('3','人事','HR')
into works values('1001','开发','Java工程师')
into works values('1002','研发','算法工程师')
into works values('1003','开发','前端工程师')
into users values ('1004','魏无忌','33','女',to_date('2021&05&01','yyyy&mm&dd'),to_date('2021-1-30 16:20:33','YYYY-MM-DD HH24:MI:SS'))
into users values ('101','卫玠','22','女',to_date('2021^05^01','yyyy^mm^dd'),to_date('2021-1-30 15:20:33','YYYY-MM-DD HH24:MI:SS'))
into users values ('102','潘安','31','男',to_date('2021$05$01','yyyy$mm$dd'),to_date('2021-1-30 14:20:33','YYYY-MM-DD HH24:MI:SS'))
select * from dual;

删除

-- 删除表的数据和结构
drop admin;

-- 删除多个表
DROP TABLE [IF EXISTS] users [ ,works ];

-- 删除表里的数据
delete from user where id=2;
delete from user where age>30 and name like "王%";

修改

update user set name='lisi' where id=2;
update user set sex='女',age=31 where id=1002；

-- 另外两种方式是查询全部数据之后，解锁修改
select * from users for update;
select u.*, u.rowid from users u;

查询

select * from user
select name,sex,age from user
select name, sex, age from user  where age>18

-- 查询时间 select now()这句是MySQL的，Oracle执行不了
SELECT SYSDATE FROM DUAL

示例

create table test_insert(
       data_id number(10) primary key,
       user_name varchar2(30),
       address varchar2(50)
)

-- 自增
create sequence seq_test_insert 
minvalue 1
maxvalue 999999999999
start with 1
increment by 2
cache 20;

create or replace trigger tr_test_insert
before insert on test_insert
for each row
begin
  select seq_test_insert.nextval into :new.data_id from dual;
end;  

insert all 
into test_insert(user_name,address) values('aaa','henan')
into test_insert(user_name,address) values('bbb','shanghai')
into test_insert(user_name,address) values('ccc','beijing')
select * from dual;

select * from test_insert;

--创建用户表
create table t_user(
       id number primary key,
       username varchar2(100) not null unique,
       password varchar2(32) not null,
       email varchar2(200)
);

--表及表字段注释
comment on table t_user is '用户表';
comment on column t_user.id is '用户表ID';
comment on column t_user.username is '用户名';
comment on column t_user.password is '密码';
comment on column t_user.email is '邮箱';

--创建自增序列
create sequence t_user_seq
increment by 1
START WITH 1
NOMAXVALUE
NOCYCLE
NOCACHE

--创建表的触发器(用来触发id自增)
create or replace trigger t_user_trigger 
before insert or update on t_user
for each row
  when (new.id is null)
begin
  select t_user_seq.nextval into :new.id from dual;
end;


--插入数据测试
insert into t_user (username,password,email) values ('admin','admin','admin@aaa.com');
insert into t_user (username,password,email) values ('admin2','admin2','admin2@aaa.com');
  
--查询结果
select * from t_user;

/*
Oracle使用计算机操作系统的当前日期和时间。
SYSDATE：可将Sysdate视为一个其结果为当前日期和时间的函数，在任何可以使用Oracle函数的地方都可以使用Sysdate。也可以将它视为每个表的一个隐藏的列或伪列。*/
select sysdate 时间 from dual;
/*
CURRENT_DATE：报告会话的时区中的系统日期。注：可以设置自己的时区，以区别于数据库的时区。
*/
select current_date from dual;
/*
SYSTIMESTAMP：报告TIMESTAMP数据类型格式的系统日期。
*/
select SYSTIMESTAMP  from dual;