Introduction to Oracle basic statement instance code

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

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

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 );