基本操作
create table T_STUDENT
(
name varchar2(10) not null,
id char(11) not null,
age number(3) constraint CKC_age_STUDENT check (age >=10) not null,
gender char(1) constraint CKC_gender_STUDENT check (gender in (1,2)),
class varchar2(10) not null,
height number(5,2),
weight number(5,2),
crttime date default sysdate not null,
primary key (name,id)
)tablespace USERS;
select * from T_STUDENT;
select name,id,age,gender from T_STUDENT;
select * from user_cons_columns where table_name = 'T_STUDENT';
alter table T_STUDENT drop constraint SYS_C0011106;
alter table T_STUDENT add constraint PK_STUDENT primary key(name,id);
insert into T_STUDENT(name,Id,age,gender,class,HEIGHT,WEIGHT,CRTTIME)
values ('张三','2020100000','12',1,'六年级一班',170.00,60.00,sysdate);
insert into T_STUDENT(name,Id,age,gender,class,HEIGHT,WEIGHT)
values ('李四','2020100001','13',1,'六年级一班',170.00,60.00);
update T_STUDENT set id = '2020100003' where name='李四';
update T_STUDENT set id = '2020100003',gender='2' where name='李四';
drop table T_STUDENT;
alter table T_STUDENT rename to STUDENT;
alter table STUDENT rename to T_STUDENT;
alter table T_STUDENT drop column remarks;
alter table T_STUDENT rename column id to card;
alter table T_STUDENT rename column card to id;
alter table T_STUDENT add remarks varchar2(100);
alter table T_STUDENT modify name varchar2(20);
序列生成器
create sequence SEQ_STUDENT;
create sequence SEQ_STUDENT_1
minvalue 10
maxvalue 9999999999999999999999999999
increment by 20
start with 30
cache 20
noorder
nocycle ;
select SEQ_STUDENT.nextval from dual;
select SEQ_STUDENT.currval from dual;
alter sequence SEQ_STUDENT_1 cache 40 order cycle;
drop sequence SEQ_STUDENT_1;
序列的nextval方法不会产生事务,事务回滚命令rollback不会恢复序列的值
日期函数
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
select to_char(sysdate,'yyyymmddhh24miss') from dual;
select to_char(sysdate,'yyyymmddhh24:mi:ss') from dual;
select to_char(sysdate,'yyyymmdd-hh24:mi:ss') from dual;
select to_char(sysdate,'yyyy-mm-dd') from dual;
select to_char(sysdate,'hh24:mi:ss') from dual;
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
create table ttime
(
datetime date
);
select * from ttime;
insert into ttime(datetime) values (to_date('2021-01-22','yyyy-mm-dd'));
select sysdate,sysdate+1 from dual;
select sysdate,sysdate+1/24 from dual;
select sysdate,sysdate+1/24/60 from dual;
select sysdate,sysdate+1/24/60/60 from dual;
select sysdate,add_months(sysdate,1) from dual;
select add_months(to_date('2021-12-1','yyyy-mm-dd'),1) from dual;
select add_months(to_date('2021-1-31','yyyy-mm-dd'),1) from dual;