Oracle database about creating use of databases, tablespaces, multiple tables, views, stored procedures, sequences

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as [email protected] as SYSDBA

Sql>
Sql>--Create user
Sql>create user LPA identified by liPeng1;
User created
Sql>--Grant DBA authority to the user
Sql>Grant DBA to LPA;
Grant succeeded
Sql>--Create use table space
Sql>Create tablespace liPeng11
2datafile ' E:\oracle11\liPeng1.dbf '
3size 1500M
4autoextend on next 5M maxsize 3000M;
Tablespace created
Sql>--Grant users permission to use the tablespace
Sql>ALTER user LPA quota unlimited on liPeng11;
User Altered
Sql>--Switch to LPA user
Sql>Conn lpa/lipeng1 @liPeng as SYSDBA;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as [email protected] as SYSDBA
Sql>--Create student tables, including number, name, age, gender, address, telephone
Sql>CREATE TABLE student111111 (
2MPO char (4) primary key, --Set the primary key for the MPO, which is unique and not empty
3name VARCHAR2 (+),
4Age number (2),
5sex char (2) Default ' man ' Check (sex in (' Male ', ' female ')), --set gender in male, female selection, and default to male
6address varchar2 (+),
7Telephone Number (one) unique--Set the phone number is not unique
8);
Table created
Sql>--Creation of student-selected timetable, including number, course name
Sql>CREATE TABLE chooseclass11111 (
2MPO char (4) References student111111 (MPO),--Set MPO as foreign key
3classname VARCHAR2 () primary key
4);
Table created
Sql>--Create a curriculum, which includes the course name, week
Sql>CREATE TABLE myclass1111 (
2classname varchar2 () references chooseclass11111 (classname),
3dat char (6)
4);
Table created
Sql>--Insert elements into each table
Sql>INSERT into student111111 values (' 0001 ', ' liPeng1 ', 23, ' Male ', ' kroea ', ' 13558755474 ');
1 row inserted
Sql>INSERT into student111111 values (' 0002 ', ' Chengjunyun ', 23, ' Male ', ' China ', ' 13532755474 ');
1 row inserted
Sql>INSERT into student111111 values (' 0003 ', ' Liujixiang ', 23, ' Male ', ' Died ', ' 13558732474 ');
1 row inserted
Sql>INSERT into student111111 values (' 0004 ', ' lijing ', 23, ' female ', ' japane ', ' 13558752474 ');
1 row inserted
Sql>INSERT into student111111 values (' 0005 ', ' Lijingyong ', 23, ' Male ', ' China ', ' 13553755474 ');
1 row inserted
Sql>INSERT into student111111 values (' 0006 ', ' Liyan ', 23, ' female ', ' China ', ' 13558115474 ');
1 row inserted
Sql>INSERT into chooseclass11111 values (' 0001 ', ' math ');
1 row inserted
Sql>INSERT into chooseclass11111 values (' 0002 ', ' English ');
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 ', ' data mining ');
1 row inserted
Sql>INSERT into chooseclass11111 values (' 0006 ', ' ORACLE ');
1 row inserted
Sql>INSERT into myclass1111 values (' Math ', ' Tuesday ');
1 row inserted
Sql>INSERT into myclass1111 values (' English ', ' Thursday ');
1 row inserted
Sql>INSERT into myclass1111 values (' JAVA ', ' Week Seven ');
1 row inserted
Sql>INSERT into myclass1111 values (' C # ', ' Wednesday ');
1 row inserted
Sql>INSERT into myclass1111 values (' Data mining ', ' Tuesday ');
1 row inserted
Sql>INSERT into myclass1111 values (' ORACLE ', ' Friday ');
1 row inserted
Sql>--Print out the tables
Sql>select * from student111111;
MPO NAME Age SEX ADDRESS Telephone
---- ------------------------------ --- --- ------------------------------ ------------
0001 liPeng1 23 Male Kroea 13558755474
0002 Chengjunyun 23 Male China 13532755474
0003 Liujixiang 23 Male Died 13558732474
0004 lijing 23 Female Japane 13558752474
0005 Lijingyong 23 Male China 13553755474
0006 Liyan 23 Women China 13558115474
6 Rows selected
Sql>select * from chooseclass11111;
MPO CLASSNAME
---- --------------------
0001 Mathematics
0002 English
0003 JAVA
0004 C #
0005 Data Mining
0006 ORACLE
6 Rows selected
Sql>select * from myclass1111;
CLASSNAME DAT
-------------------- ------
Mathematics Tuesday
English Thursday
JAVA Week Seven
C # Wednesday
Data Mining Tuesday
ORACLE Friday
6 Rows selected
Sql>--Add a row to the student1111111 table and then delete
Sql>INSERT into student111111 values (' 0007 ', ' Chenyun ', 23, ' Male ', ' China ', ' 13132755474 ');
1 row inserted
Sql>Delete from student111111 where mpo= ' 0007 ';
1 row deleted
Sql>--Change the age of Chengjunyun students in the student1111111 table to 22 and then show
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 Male China 13532755474
Sql>--Find the name, age, gender, address, telephone number, and selected course of Student No. 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 male China 13532755474 English
Sql>--Sequence
Sql>--Create sequence
Sql>Create sequence My_seq11
2Increment by 1
3start with 1
4Nomaxvalue
5nocycle
6Cache;
Sequence created
Sql>--use sequences and view
Sql>INSERT into student111111 values (my_seq11.nextval, ' Chenyun ', 23, ' Male ', ' China ', ' 13132755474 ');
1 row inserted
Sql>select * from student111111;
MPO NAME Age SEX ADDRESS Telephone
---- ------------------------------ --- --- ------------------------------ ------------
0001 liPeng1 23 Male Kroea 13558755474
0002 Chengjunyun 22 Male China 13532755474
0003 Liujixiang 23 Male Died 13558732474
0004 lijing 23 Female Japane 13558752474
0005 Lijingyong 23 Male China 13553755474
0006 Liyan 23 Women China 13558115474
1 Chenyun 23 Male China 13132755474
7 Rows selected
Sql>--Stored procedures
Sql>--Create process
Sql>CREATE PROCEDURE My_p is
2--Define variables
3--Execute section, modify the number of the last line in the student1111111 table
4begin
5Update student111111 set mpo=0010 where mpo= ' 1 ';
6--End
7end;
8/
Procedure created

Sql>--Invokes the procedure and displays
Sql>exec my_p;
PL/SQL procedure successfully completed

Sql>--View
Sql>--Create a view
Sql>CREATE VIEW MY_VIEW11
2 as
3select * from student111111;
View created

Sql> --use of views
sql> INSERT INTO MY_VIEW11 (Mpo,name,age,sex,address,telephone) VALUES (' 2100 ', ' CHEN ', 24, ' Male ', ' China ', ' 13123755474 ');
1 row inserted

Sql> SELECT * from student111111;
/
MPO NAME Age SEX ADDRESS Telephone
---- ------------------------------ --- --- ------------------------------ ------------
0001 liPeng1 23 Male Kroea 13558755474
0002 Chengjunyun 22 Male China 13532755474
0003 Liujixiang 23 Male Died 13558732474
0004 lijing 23 Female Japane 13558752474
0005 Lijingyong 23 Male China 13553755474
0006 Liyan 23 Women China 13558115474
2100 CHEN 24 Male China 13123755474
Chenyun 23 Male China 13132755474
8 Rows selected

Sql>

Oracle database about creating use of databases, tablespaces, multiple tables, views, stored procedures, sequences