《SQLPLUS,基本SQL.ppt

SQL select sname, coname,score from student a,courses b,grade c where a.sid=c.sid and b.coid=c.coid and sname like 黄% order by score; 选择行8行 SQL select sname, coname,score from student a,courses b,grade c 2 where a.sid=c.sid and b.coid=c.coid and sname like 黄_ and score 60; 选择行0行 SQL select tname from teacher a,courses b 2 where a.tid=b.coteacher 3 group by (a.tid,tname) 4 having count(*)=2; TNAME 李明 * * January February March April May June July August September October November December * January February March April May June July August September October November December * January February March April May June July August September October November December NLS_DATE_LANGUAGE：设定字符集为中文，还是英文等， 可以在语句中临时设置，也可以修改数据库相关的参数设置。 select tid,tname,rowid from teachers * select tid,tname,rowid from teachers * insert into student values(S200309,小寒,sysdate,C0010602,null); select sname,to_char(Sbirth,yyyy-month-dd hh24:mi:ss) from student; * * alter table student add email varchar2 (100); update student set email= concat(sid,@); * alter table grade add test varchar2 (100); update grade set test=(select coname from courses where courses.coid= grade.coid); * create table results( sid varchar2(30) not null, sname varchar2(30) not null, coname varchar2(50) not null, score number); insert into results select a.sid,sname,coname,score from student a,COURSES b,grade c where a.sid=c.sid and b.coid=c.coid and score=90; * authentication: [ ?:.θentikei??n ] PCTINCREASE percent increase * PCTINCREASE percent increase ALTER USER VENDITION QUOTA UNLIMITED ON USERS; * PCTINCREASE percent increase * create table test( a number) tablespace users storage(initial 50k next 100k minextents 2 maxextents 100 pctincrease 0) pctfree 20 pctused 40; * * Initrans maxtrans 允许的初始和最大访问事务数 pctfree pctused 若定义PCTFREE=20% PCTUSED=40% 当数据在一个BLOCK中少于80%时，可以插入数据，直到大于80% 当数据被删除到少于80%，但大于40%，仍不能插入数据，