oracle DML DDL operation

µ±Êý¾Ý¿â×Ö¶ÎÉè¼ÆΪ¶¨³¤×Ö·û´®Ê±£¬Ð§Âʸߣ¬Õ¼¿Õ¼ä¡£¿Õ¼ä»»Ê±¼ä
varchar2×î¶à4096×Ö½Ú£¬ÔÙÍù´óÁË£¬ÓÃlongÐÍ´æ
ͼƬµÄ´æ´¢Ò²¿ÉÒÔÊÇÊý¾Ý¿âÖдæÃû×Ö£¬Êµ¼Ê´æÔÚÓ²ÅÌÉÏ
create table stu
(
  id number(6) primary key,
  name varchar2(20) constraint stu_name_nn not null,
  sex number(1),
  age number(3),
  sdate date,
  grade number(2) default 1,
  class number(4) references class(id),
  email varchar2(50),
  constraint stu_name_email_nui unique(email, name)
)
create table class
(
  id number(4) primary key,
  name varchar2(20) not null
);
--Îå¸öÔ¼ÊøÌõ¼þ
-- Ϊ¿Õ
name varchar2(20) constraint stu_name_nn not null, --stu_name_nn Ò²¿ÉÒÔ²»ÆðÃû×Ö
-- Ψһ
email varchar2(50) unique  --×ֶμ¶Ô¼Êø
constraint stu_name_email_nui unique(email, name)  --±í¼¶Ô¼Êø
-- Ö÷¼üÖ÷¼ü×îºÃÊÇÊý×Ö£¬Ë÷Òý ¿ì
constraint stu_id_pk primary key (id)
-- Íâ¼ü , ±»²Î¿¼µÄ×ֶΣ¬±ØÐëÊÇÖ÷¼ü
constraint stu_class_fk foreign key(class) references class(id)
-- checkÔ¼Êø
-- ÐÞ¸ÄÏÖÓбíµÄ½á¹¹
alter table stu add(addr varchar2(100));
alter table stu drop (addr);
alter table stu modify(addr varchar2(150));
alter table stu drop constraint stu_class_fk
-- ɾ³ýÒ»Õűí
drop table table_name;
-- Êý¾Ý×Öµä±í
desc user_tables;
select table_name from user_tables;
select view_name from user_views;
select constraint_name from user_constraints;
select dictionary;
select constraint_name, table_name from user_constraints;
select * from dictionary where table_name like 'USER_TABLE%';
-- Ë÷Òý
-- ¼ÓÖ÷¼üºÍΨһԼÊøʱ£¬×Ô¶¯½¨Á¢Ë÷Òý
-- ²éѯËٶȿìÁË£¬²åÈëËÙ¶ÈÂýÁË
create index idx_stu_email on stu(email);
drop index idx_stu_email;
-- ÐòÁÐ
create sequence seq;
select seq.nextval from dual;
drop seq;
--²åÈëÊý¾ÝÇ°Ïȱ£´æ
create table emp2 as select * from emp;
sqlplus sys/password as sysdba
alter user scott account unlock;
grand create table, create view to scott;
--½¨Á¢É¾³ýÓû§ µ¼³ö±í
SQL> conn sys/orcl as sysdba;
drop user liuchao