oracleデータベースに関する知識のまとめ
15013 ワード
TNS
C:\Documents and Settings
ew>lsnrctl start
system
sqlplus
conn system as sysdba
scott ， tiger
SQL> alter user scott identified by tiger account unlock;
SQL> conn scott/tiger
create table tableName
(
colName TYPE,
colName TYPE,
.....
);
drop table tableName;
alter table tableName modify
purge recyclebin;
：1. 。primary key （ ）
unique: ，
not null:
1 create table S2
2 (
3 id number(4),
4 name varchar2(20),
5 constraint pk_S2 primary key(id),
6 constraint uni_S2 unique(name)
7* )
，
alter table S2 drop constraint pk_S2;
> create table stuScore
(
id number(4),
score number(4,1),
constraint fk_ss foreign key(id) references stuName(id)
);
StuScore.id stuName.id
， ， ， ， ，
， ，
， ，
，
create table S2
(
id number,
age number check ( age> 0 and age<100)
);
check ， insert
//
alter table S2 add name varchar2(20);
//
alter table S2 drop column name;
//
alter table S2 modify name varchar2(10);
:
char/varchar2/number/date/systimestamp( )
select *** from *** where ****;
// ：from -> where -> select
null+ =null
( )，
1. select count(*) from emp;
2. select sum(sal) from emp;
3. select max(sal) from emp;
4. select min(sal) from emp;
5. select avg(sal) from emp;
distinct
1.mod()
2.round()
select round(10.987654,2) from dual; 2
3.trunc: ，
select trunc(123.456,2) from dual;
4.bitand:
5.lower:
6.upper:
7.initcap:
8.trim:
9.to_number()
to_char()
to_date()
10. nvl( ,0)
select ename,(sal+nvl(comm,0)) from emp;
select ... from .... where ..... groupby .... having .....
emp deptno 20 ， deptno ， 2000
： select ， ，
id name address job sex height weight
1 zhangsan jilin student m 178 160
2 hanmeimei liaoning teacher f 165 118
3 lilei jizhou javapg m 180 172
4 huhansan jilin student f 177 180
5 liudehua jizhou singer m 170 120
1. jizhou
select max(weight) from myclass where address='jizhou'
2. liaoning ， 。
select sex,avg(height) from myclass where address<>'liaoning' group by sex;
3.
select * from myclass where weight>height;
4. j
select job from myclass where address not like 'j%';
5.
select (select count(*) from myclass where sex='m') / (select count(*) from myclass where sex='f')
from dual
order by asc desc
select .... from ..... where . .... group by ..... having ..... order by
1.from -> where -> group by -> having -> select -> order by
（ ）
select
SQL> select (select count(*) from emp where deptno=10) / (select count(*) from emp ) from dual;
from
select * from (select * from emp);
select * from (select * from emp where deptno=20) where sal>2000;
where
SQL> select * from emp where sal > (select avg(sal) from emp);
select * from emp where sal in (select avg(sal) from emp);
：
SQL> select ename,deptno from emp
where (deptno,sal) in (select deptno,max(sal) from emp group by deptno);
having
：
SQL> select deptno,avg(sal) from emp group by deptno having avg(sal) > (select avg(sal) from emp);
exists
not exists
：
： ， ，
： ， ，
， ， ( ， )
， ， （ 、）
， （ ）
SQL> create table xi
2 ( xi_name varchar2(10),
3 xi_zr varchar2(10),
4 constraint pk_xi primary key(xi_name)
5 );
SQL> create table stu
2 ( stu_id number,
3 stu_name varchar2(10),
4 xi_name varchar2(10),
5 constraint pk_stu primary key(stu_id),
6 constraint fk_stu foreign key(xi_name) references xi(xi_name)
7 );
SQL> create table score
2 (
3 stu_id number,
4 subj varchar2(10),
5 scor number(4,1),
6 constraint fk_score foreign key(stu_id) references stu(stu_id)
7 );
insert into xi values('waiyu','zhangsan');
insert into xi values('jisuanji','lisi');
insert into xi values('zhengfa','wangwu');
insert into stu values(1,'haner','waiyu');
insert into stu values(2,'tom','zhengfa');
insert into stu values(3,'jerry','jisuanji');
insert into stu values(4,'moto','waiyu');
insert into stu values(5,'nokia','waiyu');
insert into stu values(6,'iphone','jisuanji');
insert into stu values(7,'alibaba','jisuanji');
insert into stu values(8,'taobao','zhengfa');
insert into stu values(9,'dongdong','zhengfa');
insert into stu values(10,'dingding','waiyu');
insert into score values(1,'english',98);
insert into score values(1,'computer',76);
insert into score values(1,'math',56);
insert into score values(2,'math',88);
insert into score values(2,'computer',98);
insert into score values(3,'english',44);
insert into score values(3,'math',77);
insert into score values(3,'computer',39);
insert into score values(4,'computer',49);
insert into score values(4,'math',49);
insert into score values(5,'math',79);
insert into score values(5,'english',69);
commit;（ ）
( )
( )select * from A cross join B on;
( ) select * from A , B where;
select * from A join B on
select stu.stu_id,stu_name,scor from stu join score on
stu.stu_id=score.stu_id and subj='math' and scor>60;
SQL> select * from A left join B on A.id=B.id;
ID NAME ID SCORE
---------- ---------- ---------- ----------
1 a 1 88
2 b 2 99
SQL> select * from A right join B on A.id=B.id;
ID NAME ID SCORE
---------- ---------- ---------- ----------
1 a 1 88
2 b 2 99
3 100
rownum : rownum 1
，
select * from (select rownum r,s.scor from (select stu.stu_id,stu.stu_name,scor from stu join score
on stu.stu_id=score.stu_id and subj='math' order by scor desc) s ) ss where ss.r>=2 and ss.r<=4;
delete from where
， where
update set =' ' where
1. wangwu
2.
3.
4.
5.
6.
7.
8.
9. ， （ ）
10. ， （ ）
oracle:
create table / drop table /alter table
insert into
insert into..
update ....
delete ....
commit;
rollback;// TNS
C:\Documents and Settings
ew>lsnrctl start
system
sqlplus
conn system as sysdba
scott ， tiger
SQL> alter user scott identified by tiger account unlock;
SQL> conn scott/tiger
create table tableName
(
colName TYPE,
colName TYPE,
.....
);
drop table tableName;
alter table tableName modify
purge recyclebin;
：1. 。primary key （ ）
unique: ，
not null:
1 create table S2
2 (
3 id number(4),
4 name varchar2(20),
5 constraint pk_S2 primary key(id),
6 constraint uni_S2 unique(name)
7* )
，
alter table S2 drop constraint pk_S2;
> create table stuScore
(
id number(4),
score number(4,1),
constraint fk_ss foreign key(id) references stuName(id)
);
StuScore.id stuName.id
， ， ， ， ，
， ，
， ，
，
create table S2
(
id number,
age number check ( age> 0 and age<100)
);
check ， insert
//
alter table S2 add name varchar2(20);
//
alter table S2 drop column name;
//
alter table S2 modify name varchar2(10);
:
char/varchar2/number/date/systimestamp( )
select *** from *** where ****;
// ：from -> where -> select
null+ =null
( )，
1. select count(*) from emp;
2. select sum(sal) from emp;
3. select max(sal) from emp;
4. select min(sal) from emp;
5. select avg(sal) from emp;
distinct
1.mod()
2.round()
select round(10.987654,2) from dual; 2
3.trunc: ，
select trunc(123.456,2) from dual;
4.bitand:
5.lower:
6.upper:
7.initcap:
8.trim:
9.to_number()
to_char()
to_date()
10. nvl( ,0)
select ename,(sal+nvl(comm,0)) from emp;
select ... from .... where ..... groupby .... having .....
emp deptno 20 ， deptno ， 2000
： select ， ，
id name address job sex height weight
1 zhangsan jilin student m 178 160
2 hanmeimei liaoning teacher f 165 118
3 lilei jizhou javapg m 180 172
4 huhansan jilin student f 177 180
5 liudehua jizhou singer m 170 120
1. jizhou
select max(weight) from myclass where address='jizhou'
2. liaoning ， 。
select sex,avg(height) from myclass where address<>'liaoning' group by sex;
3.
select * from myclass where weight>height;
4. j
select job from myclass where address not like 'j%';
5.
select (select count(*) from myclass where sex='m') / (select count(*) from myclass where sex='f')
from dual
order by asc desc
select .... from ..... where . .... group by ..... having ..... order by
1.from -> where -> group by -> having -> select -> order by
（ ）
select
SQL> select (select count(*) from emp where deptno=10) / (select count(*) from emp ) from dual;
from
select * from (select * from emp);
select * from (select * from emp where deptno=20) where sal>2000;
where
SQL> select * from emp where sal > (select avg(sal) from emp);
select * from emp where sal in (select avg(sal) from emp);
：
SQL> select ename,deptno from emp
where (deptno,sal) in (select deptno,max(sal) from emp group by deptno);
having
：
SQL> select deptno,avg(sal) from emp group by deptno having avg(sal) > (select avg(sal) from emp);
exists
not exists
：
： ， ，
： ， ，
， ， ( ， )
， ， （ 、）
， （ ）
SQL> create table xi
2 ( xi_name varchar2(10),
3 xi_zr varchar2(10),
4 constraint pk_xi primary key(xi_name)
5 );
SQL> create table stu
2 ( stu_id number,
3 stu_name varchar2(10),
4 xi_name varchar2(10),
5 constraint pk_stu primary key(stu_id),
6 constraint fk_stu foreign key(xi_name) references xi(xi_name)
7 );
SQL> create table score
2 (
3 stu_id number,
4 subj varchar2(10),
5 scor number(4,1),
6 constraint fk_score foreign key(stu_id) references stu(stu_id)
7 );
insert into xi values('waiyu','zhangsan');
insert into xi values('jisuanji','lisi');
insert into xi values('zhengfa','wangwu');
insert into stu values(1,'haner','waiyu');
insert into stu values(2,'tom','zhengfa');
insert into stu values(3,'jerry','jisuanji');
insert into stu values(4,'moto','waiyu');
insert into stu values(5,'nokia','waiyu');
insert into stu values(6,'iphone','jisuanji');
insert into stu values(7,'alibaba','jisuanji');
insert into stu values(8,'taobao','zhengfa');
insert into stu values(9,'dongdong','zhengfa');
insert into stu values(10,'dingding','waiyu');
insert into score values(1,'english',98);
insert into score values(1,'computer',76);
insert into score values(1,'math',56);
insert into score values(2,'math',88);
insert into score values(2,'computer',98);
insert into score values(3,'english',44);
insert into score values(3,'math',77);
insert into score values(3,'computer',39);
insert into score values(4,'computer',49);
insert into score values(4,'math',49);
insert into score values(5,'math',79);
insert into score values(5,'english',69);
commit;（ ）
( )
( )select * from A cross join B on;
( ) select * from A , B where;
select * from A join B on
select stu.stu_id,stu_name,scor from stu join score on
stu.stu_id=score.stu_id and subj='math' and scor>60;
SQL> select * from A left join B on A.id=B.id;
ID NAME ID SCORE
---------- ---------- ---------- ----------
1 a 1 88
2 b 2 99
SQL> select * from A right join B on A.id=B.id;
ID NAME ID SCORE
---------- ---------- ---------- ----------
1 a 1 88
2 b 2 99
3 100
rownum : rownum 1
，
select * from (select rownum r,s.scor from (select stu.stu_id,stu.stu_name,scor from stu join score
on stu.stu_id=score.stu_id and subj='math' order by scor desc) s ) ss where ss.r>=2 and ss.r<=4;
delete from where
， where
update set =' ' where
1. wangwu
2.
3.
4.
5.
6.
7.
8.
9. ， （ ）
10. ， （ ）
oracle:
create table / drop table /alter table
insert into
insert into..
update ....
delete ....
commit;
rollback;// JAvaプログラム接続oracleデータベースコード:
public class Test {
public static void main(String[] args) throws ClassNotFoundException {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:myoracle","scott","tiger");
if(conn!=null)
System.out.println(" ");
else
System.out.println(" ");
} catch (SQLException e) {
e.printStackTrace();
}
}
}