oracle数据库建表

services.msc
cmd-->sqlplus
-->(输入用户名)sys as sysdba
-->(创建表空间)create tablespace demo datafile 'c:\demo.dbf' size 20m autoextend on next 30m maxsize unlimited;
-->(创建用户)create user lgd identified by lgd default tablespace demo;
-->(授权用户)grant connect,resource to lgd;
-->(用户进入空间)conn lgd/lgd
-->(创建表)create table userinfos(
2 userid number(10) primary key not null,
3 username varchar(20) not null,
4 birthday date not null
5 );
-->(创建表)create table bankcards(
2 cardid number(10) primary key not null,
3 userid number(10) references userinfos(userid) not null,
4 money number(10,2) not null
5 );
-->(创建数据)insert into userinfos values (1,'abc',sysdate);
-->(创建数据)insert into userinfos values (2,'李广东',to_date('2000-1-1','yyyy-mm-dd'));
-->(查看数据)select * from userinfos;
-->(更新数据)update userinfos set username='aaa' where userid=1;
-->(删除数据)delete from userinfos where userid=1;
-->(保存数据)commit work;(增加，删除，修改需要保存数据！)
-->(删除表)drop table bankcards;
-->(切换到管理员身份)conn sys as sysdba
-->(删除数据以及用户,空间还在)drop user lgd cascade;
-->(删除空间)drop tablespace demo;
-->(删除空间以及内容)drop tablespace demo including contents;
-->(进入表内conn lgd/lgd)
-->(创建数据)create table userinfos (userid number(10) primary key not null,username varchar(20) not null,birthday date not null);
-->(增加数据)alter table userinfos add(age number(3,0));
-->(更改数据)alter table userinfos modify(age varchar(20));
-->(删除数据)alter table userinfos drop column birthday;
-->(删除用户)drop user ccc cascade;
-->(输出权限)set serveroutput on;
-->(删除表格)drop table 表格名;
-->(关联表格)select e.*,d.dname,d.loc from emp e inner join dept d on d.deptno=e.deptno;
-->(关键代码)select ss.*,t.dname from(select * from (select ename,sal,deptno,job from emp where job='办事员')
order by sal desc)ss inner join dept t on ss.deptno=t.deptno;
循环
begin
for rn in 1..20 loop
insert into dept values(rn,trunc(dbms_random.value(1,4)),dbms_random.string('a',6),trunc(dbms_random.value(3000,15000)),sysdate);
end loop;
commit;
end;
/
insert into class values (1,'潘承涛','男',25,to_date('1991-10-12','yyyy-mm-dd'));
insert into class values (2,'李广东','男',26,to_date('1990-08-23','yyyy-mm-dd'));
insert into class values (3,'符梁柱','男',22,to_date('1994-05-19','yyyy-mm-dd'));

-->(分组查询 group by)select housetype from houseinfos group by housetupe;
-->(分组查询 rowid)select housetype from houseinfos hi where rowid=(select max(rowid) from houseinfos h2 where h1.housetype = h2.housetype);
-->(内部链接)select * from students st inner join scores sc on st.scode=sc.stuid;
-->(左外连接)select * from students st left join scores sc on st.sco

de=sc.stuid;
-->(伪列代码)select * from (select rownum r,scode, sname from from students where rownum<=4) k were k.r>2;
-->(树状查询)select * from areas ars start with areaid=20 connect by prior ars.subarea=ars.areaid;
-->(去除重复)select * from sc e where e.rowid=(select max(k. rowid) from sc k where https://www.360docs.net/doc/2d15234388.html,=https://www.360docs.net/doc/2d15234388.html,);
-->(截断删除)delete from sc e where e.rowid>(select min(k.rowid) from sc k where https://www.360docs.net/doc/2d15234388.html,=https://www.360docs.net/doc/2d15234388.html,);
-->(行转换列)select name,'china' project, chinese score from sc union select name,'math'project, maths score from sc;
-->(转换输出)select autotrace traceonly;
-->(创建索引)create index 索引名 on 表名 (列名，列名)
-->(强制索引)/*+index(表别名,索引名) */ 规则优化器
-->(列转换行)select msx(decode(areasay,'street',areaname,null)) street
,max(decode(areasay,'area',areaname,null)) area
,max(decode(areasay,'city',areaname,null)) city
from areas ars start with areaid=20 connect by prior srs. subarea = ars.areaid;



select name 名称,sal 分数 from(select (ee.sal-xx.sal) from (select sal from(
select e.ename,sal,rank()over(partition by deptno order by sal desc)rank from emp e where deptno=10)k where k.rank=1)ee,
(select sal from(
select e.ename,sal,row_number()over(partition by deptno order by sal)row_number from emp e where deptno=10)k where k.row_number=1)xx)差值;


-->(分列排序)select substr(housetype,1,2) from houseinfos group by substr(housetype,1,2);
-->(分列排序)select substr(housetype,1,2)ht from houseinfos group by substr(housetype,1,2) order by ht;


select max(housearea) from houseinfos;
- -
(.)(.)
-->(触发器)
create or replace trigger trig_test
before
delete
on a
for each row
begin
delete from b where b.aaid=:old.aaid;
end;