Introduction to Oracle Basic statement Instance Code

Oracle BASIC Statements

1 Enter the interface

Enter Oracle's Sqlplus interface in cmd: Sqlplus SCOTT/ORCL@ORCL

2 Connection Management

Connection command Conn[ect] SYS/ORCL@ORCL as Sysdba

Disconnect Disc[onnect]

Modify Password Psssw[ord]

Display user Show Users

Exit from interface exit

3 Execute Edit SQL statement

Execute SQL statement start D:1.sql or @ d:1.sql

Edit the SQL statement edit D:1.sql

Intercept content on screen spool d:1.sql (start intercept) spool off (stop intercepting)

4 User Management

Create user Ssy identified by Ssy

Modify password alter user Ssy identified by ORCL

Deletes user drop user Ssy (CASCADE) cascade represents deleting all objects corresponding to this user

Give permission grant create session to Ssy

Grant all on EMP to Ssy

Permission pass grant all on EMP-Ssy with GRANT option (object permissions) root table permissions

Grant create session to Ssy with ADMIN option (System permissions) Other permissions

Retract permissions revoke all in EMP to Ssy (implicated system)

5 User Password Management profile

6 Table Operations

CREATE TABLE Student (SNo number (4), Name nvarchar2 (x), Sex char (2), Birthday date,salary number (7,2) default 1000 not Nu ll

Modify table Add a field ALTER TABLE student Add (address nvarchar2 () [default value][null/not null]);

Modifies the length of a field ALTER TABLE student Modify (Name nvarchar2 (), Address nvarchar2 (10));

Modifies the type of a field ALTER TABLE student Modify (Name VARCHAR2 (10));

Modifies the name of a field ALTER TABLE student Rename column name to Name2;

Deletes a field ALTER TABLE student drop column Salary;

Modify the name of the table rename student to Stu;

Deletes table drop table student;

Delete from student; Deletes all records, the table structure is still in, writes the log, can recover, the speed is slow, the delete data can recover.

View the table field structure DESC student;

7 Additions and deletions to check

INSERT into student (sno,name,sex,birthday,salary) VALUES (1002, ' Shuyang ', ' Male ', ' 2 January-August-12 ', 2000);

The default format for modifying dates (temporarily modified, still default after the database is restarted; If you need to modify the registry to modify it)

Alter session set nls_date_format= ' YYYY-MM-DD ';

INSERT into student (sno,name,sex,birthday,salary) VALUES (1002, ' Shuyang ', ' Male ', ' 2012-08-21 ', 2000);

Insert partial fields and Null values

The data insert into the student (sno,name,sex,birthday,salary) SELECT * FROM student in the quick enlargement table;

Change update student set Name= ' Kelly ', sex= ' where sno=1002;

by deleting savepoint A;

Delete from student where sno=1003;

Rollback to A;

Summary: Three ways to delete a table delete from student; Delete all records, table structure is still in, write log, can recover, slow.

TRUNCATE TABLE student; Delete all the records in the table, the table structure is still, do not write log, can not retrieve deleted records, fast.

drop table student; Deletes the structure and data of the table.

Check (query relatively complex, perfect in ...) )

Turn on timing set timing on;

Cancel duplicate Row SELECT DISTINCT * from EMP;

Null value calculates select SAL*13+NVL (comm,0) *13 as yearly salary from EMP;

subqueries (nested queries)

Single line subquery

Multi-line subquery

Dolez Query

Page-Search

Merging queries

8 Data backup and recovery

Backup (multiple-table multiple files plus braces)

Export the entire database exp USERID=SYSTEM/ORCL@ORCL file=d:all.dmp full=y log=d:all.log

Export your own scheme exp USERID=SCOTT/ORCL@ORCL Owner=scott file=d:scott.dmp log=d:scott.log

Export other schemes exp USERID=SYSTEM/ORCL@ORCL Owner=scott file=d:scott2.dmp log=d:scott2.log

Export your own table exp USERID=SCOTT/ORCL@ORCL tables=emp file=d:emp.dmp log=d:emp.log

Export tables for other scenarios exp USERID=SYSTEM/ORCL@ORCL tables=scott.emp file=d:emp.dmp log=d:emp.log

Export table structure exp USERID=SCOTT/ORCL@ORCL tables=emp file=d:emp.dmp rows=n log=d:emp.log

Using the direct Export method exp USERID=SCOTT/ORCL@ORCL tables=emp file=d:emp.dmp direct=y log=d:emp.log

This approach is faster than the default conventional way, and you can consider using this method when the volume of data is large.

The character set of the database needs to be exactly the same as the client character set, otherwise the error will be

Recovery (multiple-table multiple files plus braces)

Import the entire database imp USERID=SYSTEM/ORCL@ORCL file=d:all.dmp full=y log=d:allimp.log ignore=y

Import your own solution imp USERID=SCOTT/ORCL@ORCL file=d:emp.dmp log=d:empimp.log

Import other Scenarios imp USERID=SYSTEM/ORCL@ORCL file=d:emp.dmp fromuser=system Touser=scott log=d:empimp.log

Import your own table imp USERID=SCOTT/ORCL@ORCL tables=emp file=d:emp.dmp

Import tables to other users imp USERID=SYSTEM/ORCL@ORCL tables=emp file=d:emp.dmp fromuser=system Touser=scott log=d:empimp.log

Import Table Structure imp USERID=SCOTT/ORCL@ORCL tables=emp file=d:emp.dmp rows=n log=d:empimp.log

Import data If an object, such as a table, already exists, can import only the table's data

Imp userid=scott/orcl@orcl tables=emp file=d:emp.dmp ignore=y log=d:empimp.log

Note that Formuser is the user touser the table belongs to and which user is now passed to

9 Table Space

Creating a tablespace Create tablespace ssy datafile ' c:oracleproduct10.2.0oradatassy.dbf '

Size 50m autoextend on next 50m maxsize unlimited extent management local;

Create user Ssy identified by ssy default Tablespace ssy;

CREATE TABLE Student (SNo number (4), Name nvarchar2 (M), Sex char (2), Birthday date,salary number (7,2) default 1000 NOT NULL) Tablespace Ssy;

Know the table space name and show all the tables included in the table space

SELECT * from All_tables where tablespace_name= ' Ssy ';

Know the name of the table, and see if the table belongs to that table space

Select Tablespace_name, table_name from user_tables where table_name= ' emp ';

Deletes the tablespace drop tablespace ssy including contents and datafiles cascade;

10 constraints

Not NULL unique PRIMARY key foreign key check

ALTER TABLE class ADD constraint Class_key primary key (ClassID);

11 Primary keys

Automatic growth

Create a table first

CREATE TABLE Student (SNo number (4) Primary key,name NVARCHAR2 (m), Sex char (2), Birthday date,salary number (7,2) default 10 NOT null)

Customizing a sequence

Create sequence student_sequence increment by 1-start with 1 nomaxvalue nocycle NoCache;

Create a Trigger

Create trigger Student_trigger before insert on student for each row when (new. SNo is null) BEGIN select Student_sequence.nextval into:new. SNo from dual;end;/

Recently inserted a row of data

INSERT into student (name,sex,birthday,salary) VALUES (' Shuyang ', ' Men ', ' 2 January-August-12 ', 2000);

Guid

Create a table first

CREATE TABLE Student2 (SNo Char primary key,name nvarchar2 (), Sex char (2), Birthday date,salary number (7,2) default 10 NOT null)

Then insert a row of data

Insert into Student2 (sno,name,sex,birthday,salary) VALUES (Sys_guid (), ' Shuyang ', ' Male ', ' 2 January-August-12 ', 2000);