Oracle数据库关于创建使用数据库、表空间呢、多表、视图、存储过程、序列的使用...

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as sys@ORCL AS SYSDBA

SQL>
SQL> --创建用户
SQL> create user LPA identified by liPeng1;
User created
SQL> --给用户授予dba权限
SQL> grant dba to LPA;
Grant succeeded
SQL> --创建使用表空间
SQL> create tablespace liPeng11
2 datafile 'E:\oracle11\liPeng1.dbf'
3 size 1500M
4 autoextend on next 5M maxsize 3000M;
Tablespace created
SQL> --授予用户使用表空间的权限
SQL> alter user LPA quota unlimited on liPeng11;
User altered
SQL> --切换到LPA用户
SQL> conn LPA/liPeng1 @liPeng as sysdba;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as LPA@liPeng AS SYSDBA
SQL> --创建学生表，其中包括学号、姓名、年龄、性别、地址、电话
SQL> create table student111111 (
2 mpo char(4) primary key, --给mpo设置为主键，其唯一且不为空
3 name varchar2(30),
4 age number(2),
5 sex char(2) default '男' check (sex in ('男','女')), --设置性别在男，女选择，且默认为男
6 address varchar2(30),
7 telephone number(11) unique --设置手机号码不唯一
8 );
Table created
SQL> --创建学生选课表，其中包括学号、课程名称
SQL> create table chooseclass11111 (
2 mpo char(4) references student111111(mpo), --设置mpo为外键
3 classname varchar2(20) primary key
4 );
Table created
SQL> --创建课程表，其中包括课程名称、星期
SQL> create table myclass1111 (
2 classname varchar2(20) references chooseclass11111(classname),
3 dat char(6)
4 );
Table created
SQL> --给各表插入元素
SQL> insert into student111111 values('0001','liPeng1',23,'男','Kroea','13558755474');
1 row inserted
SQL> insert into student111111 values('0002','CHENGJUNYUN',23,'男','China','13532755474');
1 row inserted
SQL> insert into student111111 values('0003','LIUJIXIANG',23,'男','Died','13558732474');
1 row inserted
SQL> insert into student111111 values('0004','LIJING',23,'女','Japane','13558752474');
1 row inserted
SQL> insert into student111111 values('0005','LIJINGYONG',23,'男','China','13553755474');
1 row inserted
SQL> insert into student111111 values('0006','LIYAN',23,'女','China','13558115474');
1 row inserted
SQL> insert into chooseclass11111 values('0001','数学');
1 row inserted
SQL> insert into chooseclass11111 values('0002','英语');
1 row inserted
SQL> insert into chooseclass11111 values('0003','JAVA');
1 row inserted
SQL> insert into chooseclass11111 values('0004','C#');
1 row inserted
SQL> insert into chooseclass11111 values('0005','数据挖掘');
1 row inserted
SQL> insert into chooseclass11111 values('0006','ORACLE');
1 row inserted
SQL> insert into myclass1111 values('数学','星期二');
1 row inserted
SQL> insert into myclass1111 values('英语','星期四');
1 row inserted
SQL> insert into myclass1111 values('JAVA','星期七');
1 row inserted
SQL> insert into myclass1111 values('C#','星期三');
1 row inserted
SQL> insert into myclass1111 values('数据挖掘','星期二');
1 row inserted
SQL> insert into myclass1111 values('ORACLE','星期五');
1 row inserted
SQL> --打印出各表的情况
SQL> select * from student111111;
MPO NAME AGE SEX ADDRESS TELEPHONE
---- ------------------------------ --- --- ------------------------------ ------------
0001 liPeng1 23 男 Kroea 13558755474
0002 CHENGJUNYUN 23 男 China 13532755474
0003 LIUJIXIANG 23 男 Died 13558732474
0004 LIJING 23 女 Japane 13558752474
0005 LIJINGYONG 23 男 China 13553755474
0006 LIYAN 23 女 China 13558115474
6 rows selected
SQL> select * from chooseclass11111;
MPO CLASSNAME
---- --------------------
0001 数学
0002 英语
0003 JAVA
0004 C#
0005 数据挖掘
0006 ORACLE
6 rows selected
SQL> select * from myclass1111;
CLASSNAME DAT
-------------------- ------
数学 星期二
英语 星期四
JAVA 星期七
C# 星期三
数据挖掘 星期二
ORACLE 星期五
6 rows selected
SQL> --给student1111111表加入一行然后再删除
SQL> insert into student111111 values('0007','CHENYUN',23,'男','China','13132755474');
1 row inserted
SQL> delete from student111111 where mpo='0007';
1 row deleted
SQL> --将student1111111表中的CHENGJUNYUN同学的年龄改为22，然后显示
SQL> update student111111 set age=22 where name='CHENGJUNYUN';
1 row updated
SQL> select * from student111111 where name='CHENGJUNYUN';
MPO NAME AGE SEX ADDRESS TELEPHONE
---- ------------------------------ --- --- ------------------------------ ------------
0002 CHENGJUNYUN 22 男 China 13532755474
SQL> --查找出学号为002学生的姓名，年龄，性别，地址，电话，所选课程
SQL> select table3.name,table3.age,table3.sex,table3.address,table3.telephone,table3.classname from (select * from student111111 table1,chooseclass11111 table2 where table1.mpo=table2.mpo and table1.mpo='0002') table3 ;
NAME AGE SEX ADDRESS TELEPHONE CLASSNAME
------------------------------ --- --- ------------------------------ ------------ --------------------
CHENGJUNYUN 22 男 China 13532755474 英语
SQL> --序列
SQL> --创建序列
SQL> create sequence my_seq11
2 increment by 1
3 start with 1
4 nomaxvalue
5 nocycle
6 cache 100;
Sequence created
SQL> --使用序列并查看
SQL> insert into student111111 values(my_seq11.nextval,'CHENYUN',23,'男','China','13132755474');
1 row inserted
SQL> select * from student111111;
MPO NAME AGE SEX ADDRESS TELEPHONE
---- ------------------------------ --- --- ------------------------------ ------------
0001 liPeng1 23 男 Kroea 13558755474
0002 CHENGJUNYUN 22 男 China 13532755474
0003 LIUJIXIANG 23 男 Died 13558732474
0004 LIJING 23 女 Japane 13558752474
0005 LIJINGYONG 23 男 China 13553755474
0006 LIYAN 23 女 China 13558115474
1 CHENYUN 23 男 China 13132755474
7 rows selected
SQL> --存储过程
SQL> --创建过程
SQL> create procedure my_p is
2 --定义变量
3 --执行部分，修改student1111111表中最后一行的学号
4 begin
5 update student111111 set mpo=0010 where mpo='1 ';
6 --结束
7 end;
8 /
Procedure created

SQL> --调用过程并显示
SQL> exec my_p;
PL/SQL procedure successfully completed

SQL> --视图
SQL> --创建视图
SQL> create view my_view11
2 as
3 select * from student111111;
View created

SQL> --视图的使用
SQL> insert into my_view11(mpo,name,age,sex,address,telephone) values('2100','CHEN',24,'男','China','13123755474');
1 row inserted

SQL> select * from student111111;
/
MPO NAME AGE SEX ADDRESS TELEPHONE
---- ------------------------------ --- --- ------------------------------ ------------
0001 liPeng1 23 男 Kroea 13558755474
0002 CHENGJUNYUN 22 男 China 13532755474
0003 LIUJIXIANG 23 男 Died 13558732474
0004 LIJING 23 女 Japane 13558752474
0005 LIJINGYONG 23 男 China 13553755474
0006 LIYAN 23 女 China 13558115474
2100 CHEN 24 男 China 13123755474
10 CHENYUN 23 男 China 13132755474
8 rows selected

SQL>

转载于:https://www.cnblogs.com/xiaoxuehuzhikiss/p/4638762.html