oracleÁ·Ï°£¨mldnÊÓÆµ¿Î³Ì£©Èý
×ÛºÏÁ·Ï°
ѧÉúÔ˶¯»á±ÈÈüÐÅÏ¢Êý¾Ý¿â
1£¬Ô˶¯Ô±sporter£¨Ô˶¯Ô±±àºÅsporterid£¬Ô˶¯Ô±ÐÕÃûname£¬Ô˶¯Ô±ÐÔ±ðsex£¬ËùÊôϵºÅdepartment£©
2£¬ÏîÄ¿item£¨ÏîÄ¿±àºÅitemid£¬ÏîÄ¿Ãû³Æitemname£¬ÏîÄ¿±ÈÈüµØµãlocation£©
3£¬³É¼¨grade£¨Ô˶¯Ô±±àºÅsporterid£¬ÏîÄ¿±àºÅitemid£¬»ý·Ömark£©
½¨±í
1£¬¶¨Òå¸÷¸ö±íµÄÖ÷ÂëÍâÂëÔ¼Êø
2£¬Ô˶¯Ô±µÄÐÕÃûºÍËùÊôϧ±ð²»ÄÜΪ¿ÕÖµ
3£¬»ý·ÖҪôΪ¿ÕÖµ£¬ÒªÃ´Îª6£¬4£¬2£¬0£¬·Ö±ð´ú±íµÚÒ»¶þÈýÃûµÄºÍÆäËûÃû´ÎµÄ»ý·Ö
Ô˶¯Ô±±í
1001£¬ÀîÃ÷£¬ÄУ¬¼ÆËã»úϵ
1002£¬ÕÅÈý£¬ÄУ¬Êýѧϵ
1003£¬ÀîËÄ£¬ÄУ¬¼ÆËã»úϵ
1004£¬Íõ¶þ£¬ÄУ¬ÎïÀíϵ
1005£¬ÀîÄÈ£¬Å®£¬ÐÄÀíϵ
1006£¬ËïÀö£¬Å®£¬Êýѧϵ
ÏîÄ¿±í
X001£¬ÄÐ×ÓÎåǧÃ×£¬Ò»²Ù³¡
X002£¬ÄÐ×Ó±êǹ£¬Ò»²Ù³¡
X003£¬ÄÐ×ÓÌøÔ¶£¬¶þ²Ù³¡
X004£¬Å®×ÓÌø¸ß£¬¶þ²Ù³¡
X005£¬Å®×ÓÈýǧÃ×£¬Èý²Ù³¡
»ý·Ö±í
1001£¬X001£¬6
1002£¬X001£¬4
1003£¬X001£¬2
1004£¬X001£¬0
1001£¬X003£¬4
1002£¬X003£¬6
1004£¬X003£¬2
1005£¬X004£¬6
1006£¬X004£¬4
Ô˶¯Ô±±í£º
create table sporter(sporterid number(4) primary key not null,name varchar2(50) not null,
sex varchar2(2) check (sex in('ÄÐ','Å®')),department varchar2(30) not null);
ÏîÄ¿±í£º
create table item(itemid varchar2(4) primary key not null,
itemname varchar2(50) not null,location varchar2(50) not null);
³É¼¨±í£º
create table grade(sporterid number(4),itemid varchar2(4),
constraint sporter_grade_sporterid_fk foreign key(sporterid)
references sporter(sporterid) on delete cascade,
constraint sporter_item_itemid_fk foreign key(itemid)
references item(itemid) on delete cascade,
mark number(2) check(mark in('6','4','2','0')));
²åÈëÊý¾Ý£º
Ô˶¯Ô±±í
insert into sporter values('1001','ÀîÃ÷','ÄÐ','¼ÆËã»úϵ');
insert into sporter values('1002','ÕÅÈý','ÄÐ','Êýѧϵ');
insert into sporter values('1003','ÀîËÄ','ÄÐ','¼ÆËã»úϵ');
insert into sporter values('1004','Íõ¶þ','ÄÐ','ÎïÀíϵ');
insert into sporter values('1005','ÀîÄÈ','Å®','ÐÄÀíϵ');
insert into sporter values('1006','ËïÀö','Å®','Êýѧϵ');
ÏîÄ¿±í
insert into item values('X001','ÄÐ×ÓÎåǧÃ×','Ò»²Ù³¡');
insert into item values('X002','ÄÐ×Ó±êǹ','Ò»²Ù³¡');
insert into item
Ïà¹ØÎĵµ£º
ÔÚÇ°ÃæÑ§Ï°OracleÊý¾Ý¿â»ù´¡¼Ü¹¹Ê±£¬ÒѾÁ˽âÁËOracleµÄ´æ´¢½á¹¹£¬Âß¼ÉÏ£¬OracleµÄÊý¾Ý´æ·ÅÔÚtablespacesÖУ¬ÎïÀíÉÏ´æ·ÅÔÚdatafilesÖС£Ò»¸ötablespaceÖ»ÄÜÊôÓÚÒ»¸öÊý¾Ý¿â(Ò»¸öÊý¾Ý¿â¿É°üÀ¨¶à¸ötablespace)£¬°üÀ¨ÁË1¸ö»ò¶à¸öÊý¾ÝÎļþ¡£Tablespace¿É½øÒ»²½·ÖΪsegments¡¢extentsºÍblocks¡£Ò»¸ödatafileÖ»ÊôÓÚÒ»¸öÊý¾Ý¿âµÄÒ» ......
Æäʵ´´½¨dgÍøÂçÉϵÄÎĵµºÜ¶à£¬ÎÒÊÇ¿´Ð¡²¼ÀÏʦÊÇÊÓÆµÑ§Ï°µÄ
ÓÃÐéÄâ»ú×ö²âÊÔ£¬ÅªÁ˺ü¸Ì죬ÏÂÃæ¼Ç¼һÏÂÔø¾×ö¹ýÓ¡Ïó±È½ÏÉîµÄµØ·½
1.½«Primary·þÎñÆ÷´¦Óڹ鵵ģʽºÍForce Loggingģʽ
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
S ......
²é¿´ËùÓбí¿Õ¼äʹÓÃÇé¿ö
SELECT UPPER(F.TABLESPACE_NAME) "±í¿Õ¼äÃû",
D.TOT_GROOTTE_MB "±í¿Õ¼ä´óС(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "ÒÑʹÓÿռä(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE ......
1,Áгö×îµÍн½ð´óÓÚ1500µÄ¸÷ÖÖ¹¤×÷¼°´ÓÊ´˹¤×÷µÄÈ«²¿¹ÍÔ±ÈËÊý¡£
select job,count(empno) from emp
where job in(select job from emp group by job having min(sal)>1500)
group by job;
2£¬Áгöн½ð¸ßÓÚ¹«Ë¾Æ½¾ùн½ðµÄËùÓÐÔ±¹¤ËùÔÚ²¿ÃÅ£¬Éϼ¶Áìµ¼£¬¹«Ë¾µÄ¹¤×ʵȼ¶¡£
select e.ename,d.dname,m.ename,e.sal,sa. ......
£¨1 £© ×¢ÒâÔö¼ÓÔ¼ÊøÊ±µÄд·¨£¬ºÍORACLE ÂÔÓв»Í¬
Oracle £º
ALTER TABLE SCHEMA.PREFIX_INFO ADD (
CONSTRAINT PK_PREFIX_INFO PRIMARY KEY (INFO_ID));
PostgresQL £º
alter table schema.prefix_info add constraint prefix_info_pkey primary key(info_id);
£¨2 £©ÏµÍ³Ä¬ÈϵÄ×î´óÖµÓëORACLE ²»Í¬
Oracle £ ......