??????????????????DDL
????1.????????CREAT TABLESPACE lyy DATAFILE 'C:/app/lyy.dbf' SIZE 10M;(???????10M??????????C??app???????)
???????????DROP TABLESPACE lyy;
????2.?????????????CREAT USER lyy PROFILE DEFAULT IDENTIFIED BY 123456 DEFAULT TABLESPACE lyy TEMPORARY TABLESPACE temp ACCOUNT UNLOCK; ?????????lyy??????????123456?????????lyy??????????temp????????????
????GRANT CONNECT TO lyy;
????GRANT RESOURCE TO lyy;(???????lyy connect??resource???)
????3.??????
???????????CREAT TABLE ????????????1 ??????????????????????2 ????????......??????n ???????????
???????????????????????
????--????? student????? sid?????? sname????? ssex?????? sage???綽 sphone
????--?γ?? course: ?γ??? cid???γ????? cname????????? tname????? chour
????--????? score: ?????? scid????? sid???γ??? cid?????? grade
??????Oracle?У????????????????.?????????????????????????????д???????????????????????
????——?????????
????CREAT TABLE student(
????sid CHAR(10)??
????sname VARCHAR2(50) NOT NULL??
????ssex CHAR(1)??
????sage NUMBER??
????sphone INTEGER
????);
????——?????γ??
????CREAT TABLE course(
????cid CHAR(10)??
????cname VARCHAR2(50)??
????tname VARCHAR2(50)??
????chour NUMBER
????);
????——?????????
????CREAT TABLE score(
????scid CHAR(10)??
????sid CHAR(10)??
????cid CHAR(10)??
????grade NUMBER
????);
?????????? DROP TABLE student;
????4.???????
??????????????????????????洢?????????????????????????????????
?????????????????????
????--?????????Primary key??????????????????ε??????????????
????constraint p1_sid primary key(sid)               --????sid???????
????--????????Foreign key?????????????????ε???????????????????ε???? ?????κ??????????Σ????????????????????????????????????????￡?
????constraint f1_sid foreign key(sid) references student(sid)   --????sid????????????student???sid
????--?????Unique  ???????????
????unique(sid??cid)                           --????sid??cid??????????
????--????????not null?????????????
????sname varchar2(50) not null              --????sname??η??
????--????????check?????????????????????????ε????????????????
????constraint c1_ssex check(ssex in ('M'??'F'))  --??????????????ssex??ε????????M????F
????5.????ALTER
????ALTER TABLE SCOTT.TEST RENAME TO TEST1--??????
????ALTER TABLE SCOTT.TEST RENAME COLUMN NAME TO NAME1 --????????
????ALTER TABLE SCOTT.TEST MODIFY NAME1 NUMBER(20)  --??????????
????ALTER TABLE SCOTT.TEST ADD ADDRESS VARCHAR2(40) --??????
????ALTER TABLE SCOTT.TEST DROP NAME CASCADECONSTRAINTS --???????
???????????????????DML
????DML??????????????????????????????????insert????update?????delete?????DML???????????????????????commit????rollback???????????????????????????????????????????????????????????????DDL?????????????
????1.insert????????
?????????1?? insert into ???? values(?1???2......???n????
?????????????SQL????У???????????????ò????????????????????????????Сд????????????????????????????????????????????????????￡??????????????????????????????????α???????
?????????SELECT * FROM student;
????INSERT INTO student VALUES('S001'??'????'??‘M’??30??13089247856) ??
?????????2?? insert into ???????????1???????2??......???????n?? values(?1???2??......???n);
???????????????????ε????????????????????????????????????????ε?????????????????в????????
?????????  INSERT INTO student(sname??ssex??sid??sage) VALUES('????'??'F'??'s0002'??20);
????2.update????????
???????????update ???? set ??????? [where ????]
???????????update??????????в??????????????????????????????е????????????????????????????????????У???????????????е???????????С?
????????? SELECT * FROM student;
????UPDATE student SET sage=40;
????UPDATE student SET sage=(sage+sphone)-sage??sphone=(sage+sphone)-sphone;
????UPDATE student SET sage=sage+1;
????UPDATE student SET sage=sage+1 where ssec='F';
????UPDATE student SET sname=replace(sname??'??'??'??');
????3.delete???????
???????????delete from ?? [where ????]
???????????delete??????в?????С??????λ???????????м?delete???*??????????where????????????????????е????м????
?????????DELETE FROM student WHERE sid='s0002';
????INSERT INTO course VALUES('c0001'??'oracle'??'teacher 1'??32);
????INSERT INTO score VALUES('sc0001'??'s0001'??'c0001'??100);
????SELECT * FROM student;
????SELECT * FROM course;
????SELECT * FROM score;
????4.truncate????DDL??????????????????????????Ч??
???????????truncate table  ????
?????????  truncate table score??
??????????????????DQL
????1. select ????????????в????????????????????
???????????
????select ?? ?????????????????????????????????????????????????????????????????????????Σ???
????--from?? ?????????????????????????????????????????????????????
????--where?????????????????????????????????????????????????????
????--group by????Σ????????????????????????
????--having?????????????????????????????????????й????
????--order by????Σ??????????????????????????
????????????????????????????в??????????????????е??????Ρ??????Ρ??????????????
?????????
????--?????????????????
????SELECT * FROM student;
????--??????????????????????
????SELECT sname??sage  FROM student;
????--???????
????SELECT sname??sage??'teacher 1'  FROM student;
????--????????????
????SELECT sname??sage??sage+1 FROM student;
????2. ||  ????????????????????????????
?????????SELECT sname??sage??sage||'??' FROM student;
????SELECT sname??ssex FROM student;
????SELECT sname??ssex??case WHEN ssex='M' THEN '??' ELSE '?' END FROM student;