Introduction to Oracle basic statement instance code

For more information about Oracle basic statement instance code, see.

Basic Oracle statements

 

1. Enter the interface

 

Go to the sqlplus interface of oracle in cmd: sqlplus scott/orcl @ orcl

 

2. Connection Management

 

Connection command conn [ect] sys/orcl @ orcl as sysdba

 

Disconnect disc [onnect]

 

Change Password psssw [ord]

 

Show user

 

Exit

 

 

3. Execute the edit SQL statement

 

Run the SQL statement start D: 1. SQL or @ D: 1. SQL.

 

Edit SQL statement edit D: 1. SQL

 

Capture content on the screen spool D: 1. SQL (start to intercept) spool off (stop intercept)

 

4. User Management

 

Create user ssy identified by ssy

 

Alter user ssy identified by orcl

 

Drop user ssy (cascade) cascade to delete all objects corresponding to this user

 

Grant permissions to grant create session to ssy

 

Grant all on emp to ssy

 

Grant all permissions on emp to ssy with grant option (Object permission) permissions related to the root table

 

Grant create session to ssy with admin option (system permission) Other Permissions

 

Revoke permissions revoke all on emp to ssy)

 

 

5. User Password Management profile

 

6 Table operations

 

Create table student (SNo number (4), Name nvarchar2 (50), Sex char (2), Birthday date, Salary number (1000) default not null)

 

Alter table student add (Address nvarchar2 (100) [default value] [null/not null]);

 

Alter table student modify (Name nvarchar2 (10), Address nvarchar2 (10 ));

 

Alter table student modify (Name varchar2 (10 ));

 

Modify the Name of a field alter table student rename column Name to Name2;

 

Delete a field alter table student drop column Salary;

 

Rename student to stu;

 

Drop table student;

 

Delete from student; Delete all records. The table structure is still in progress, logs can be written, data can be restored, the speed is slow, and deleted data can be recovered.

 

View the table field structure desc student;

 

 

7 add, delete, modify, and query

 

Add insert into student (SNo, Name, Sex, Birthday, Salary) values (1002, 'shi shouyang ', 'mal', '21-August-12', 2000 );

 

Modify the default format of the date (temporary modification is still the default format after the database is restarted; modify the registry if necessary)

 

Alter session set nls_date_format = 'yyyy-mm-dd ';

 

Insert into student (SNo, Name, Sex, Birthday, Salary) values (1002, 'shi shouyang ', 'mal', '2017-08-21', 2012 );

 

Insert some fields and null values

 

Insert into student (SNo, Name, Sex, Birthday, Salary) select * from student;

 

Change update student set Name = 'chen Huilin 'and Sex = 'female' where SNo = 1002;

 

Delete savepoint;

 

Delete from student where SNo = 1003;

 

Rollback to;

 

Conclusion: delete from student in three ways to delete a table. delete All records. The table structure is still in progress. You can write logs to restore the table.

 

Truncate table student; delete all the records in the table. The table structure is still in progress. If no logs are written, the deleted records cannot be retrieved, which is fast.

 

Drop table student; Delete the table structure and data.

 

Query (the query is relatively complex and is being improved ...)

 

Enable timing set timing on;

 

Cancel repeated rows select distinct * from emp;

 

Null Value Calculation select sal * 13 + nvl (comm, 0) * 13 as annual salary from emp;

 

Subquery (nested query)

 

Single Row subquery

 

Multi-row subquery

 

Multi-column subquery

 

Paging Query

 

Merge Query

 

 

8. data backup and recovery

 

Backup (multiple tables and multiple files with 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 scheme exp userid = system/orcl @ orcl owner = scott file = d: scott2.dmp log = d: scott2.log

 

Export your table exp userid = scott/orcl @ orcl tables = emp file = d: emp. dmp log = d: emp. log

 

Export the table exp userid = system/orcl @ orcl tables = scott. emp file = d: emp. dmp log = d: emp. log for other solutions.

 

Export the table structure exp userid = scott/orcl @ orcl tables = emp file = d: emp. dmp rows = n log = d: emp. log

 

Use the direct export method exp userid = scott/orcl @ orcl tables = emp file = d: emp. dmp direct = y log = d: emp. log

 

This method is faster than the conventional method by default. When the data volume is large, you can consider using this method.

 

 

In this case, the character set of the database must be exactly the same as that of the client. Otherwise, an error is reported.

 

Restore (multiple tables and multiple files with braces)

 

Import the whole 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 schemes imp userid = system/orcl @ orcl file = d: emp. dmp fromuser = system touser = scott log = d: empimp. log

 

Import your table imp userid = scott/orcl @ orcl tables = emp file = d: emp. dmp

 

Import the table to another user 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, you can only import table data.

 

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

 

Note that formuser is the user to which the table belongs.

 

 

9. tablespace

 

Create tablespace ssy datafile 'C: oracleproduct10.2.0oradatassy. dbf'

 

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

 

Create user ssy identified by ssy default tablespace ssy;

 

Create table student (SNo number (4), Name nvarchar2 (50), Sex char (2), Birthday date, Salary number (1000) default not null) tablespace ssy;

 

Know the tablespace name and display all tables included in the tablespace

 

Select * from all_tables where tablespace_name = 'ssy ';

 

Know the table name and check which tablespace the table belongs.

 

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

 

Drop tablespace ssy including contents and datafiles cascade constraints;

 

 

10 Constraints

 

Not null unique primary key foreign key check

 

Alter table class add constraint class_key primary key (classid );

 

11 primary key

 

Automatic Growth

 

Create a table first

 

Create table student (SNo number (4) primary key, Name nvarchar2 (50), Sex char (2), Birthday date, Salary number (1000) default not null)

 

Customize 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 ;/

 

Insert a row of data recently

 

Insert into student (Name, Sex, Birthday, Salary) values ('shi shouyang ', 'male', '21-August 12-12', 2000 );

 

GUID

 

Create a table first

 

Create table student2 (SNo char (32) primary key, Name nvarchar2 (50), Sex char (2), Birthday date, Salary number (1000) default not null)

 

Insert a row of data

 

Insert into student2 (SNo, Name, Sex, Birthday, Salary) values (sys_guid (), 'shi shouyang ', 'male', '21-August 12-12 ', 2000 );