Oracle (2) Basic Primer for Oracle

1.sys and System

System Admin user, SYS superuser, sys account has more permissions than the SYSTEM account, no Scott user after Oracle11

2. Import and export of tables

IMP username/password @ service name file= database table. DMP Storage Address Ignore=y full=y//import

EXP username/Password @ service name tables= table name file= Deposit local DMP Destination Address log= Local log Destination address owner= username//Export to export

PL/SQL Developer tool Export: Menu bar---->tools---->export tables//Export

PL/SQL Developer tool Export: Menu bar---->tools---->import Tables//import

3.oracle is case-sensitive and greater than or equal to

SQL language case insensitive (such as keywords, field names, table names (MySQL this default is sensitive)), SQL can be written in one line or more lines, the keyword can not be abbreviated or branch, SQL only <= no >=, only; But sensitive to such things as field content (MySQL is not sensitive by default)

Meaning of the 4.null representation (as with MySQL)

Null value is not valid, null value is not a space or 0, any one of the values and air calculation results are null

5. "and" "

Characters, dates can only be single quotes, and aliases need to use double quotes for special (field names, keywords, etc.).

6.| |

|| Similar to concat or Java +

7.escape

Escape escape character, similar to Java and MySQL \;

8. Time function

Next_day (Date,week)//date of the next week of the specified date, the default format of the date is dd-mm-yyyy

Months_between (DATE1,DATE2)//Two months of difference between dates

Last_day (date)//Last day of the month

Add_months (Date,month)//Add a number of months to the specified date

To_char (date, ' Format_model ')//Conversion time: YYYY year, MM month, DD, day week, HH Hour, MI min, ss sec, converted to Digital: 9 number//presence is replaced, no there is no, 00//does not exist with 0 substitution, $ dollar symbol, L local currency symbol,. decimal point, thousand character

Trunc (Sysdate, ' year ')//Date truncated,

To_date (char, ' format_model ')//format needs to meet consistent yyyy, MM, DD, day, HH hours, MI minutes, ss sec

Note: the date on which a number is added or subtracted is still a date, and a two date subtraction returns the number of days between dates. No addition can be done between two dates.

9. Some other functions

Lpad (numeric, 10, character)//less than 10 left complement character, 10 custom

Rpad (numeric, 10, character)//less than 10-bit right-complement character, 10-Custom

Trim (' h ' from ' HelloWorld ')//Remove the head and tail characters H

Replace (' ABCD ', ' B ', ' m ')//change all B to M

Trunc (45.926, 2)//reserved Two decimal places, rounding

Round (45.926, 2)//reserved Two decimal places, rounded

To_number (char, ' format_model ')//format needs to be consistent, $,l can do addition operations

NVL (EXPR1, EXPR2)//If it is not empty then it is its own expr1, empty then expr2, note that Oracle requires EXPR1 and EXPR2 in the same field type, or need to transform, like MySQL ifnull

NVL2 (Expr1, EXPR2, EXPR3)//Meet the conditions Expr1 expr2, otherwise expr3, mysql-like if

Nullif (EXPR1, EXPR2)//EXPR1 equals EXPR2 return null, unequal return EXPR1

COALESCE (EXPR1, expr2, ..., exprn)//coalesce means expr1, Buul expr12 then go down

10. Conditional loops

conditional expressions with MySQL;

Case is then with MySQL;

While condition Loop loop body end;//loop

11. The table query is basically the same as the Mysql,cross join cross join with the WHERE e,t;using clause to specify the required columns in the equivalent connection (equivalent to where e.deptno=d.deptno);

12. Creating tables and modifying tables

1) Table name and column name: Naming rules

Must start with a letter

Must be between 1–30 characters

Must only contain a–z, A–z, 0–9, _, $, and #

Must not duplicate user-defined other objects

Must not be a reserved word for Oracle

Data type number is a replacement for MySQL int,double, etc.

VARCHAR2 is similar to MySQL's varchar, also has char

BLOB binary Max 4g

CLOB character data Max 4g

Long variable long character data, Max 2g

Date Dates

2) Rename the columns of the data table

Modify,add with MySQL

ALTER TABLE table name drop column name

ALTER TABLE name rename column name to new column name

TRUNCATE TABLE name//empty data, cannot rollback, delete can roll back

Rename table name to new table name;

13. Business

Commit start or rollback ROLLBACK statement, rollback Point is the most recent commit

SavePoint Roll Roll name, rollback to rollback point, can go back to the corresponding rollback position

Note:

DDL statements (AUTOCOMMIT), are not rolled back, rollback is for DML statements, the rows involved in DML after adding a transaction are locked, similar to MySQL's InnoDB, no other user can see their own changes before the commit, and see what they have written, like MySQL, Oracle does not automatically commit DML statements by default and requires the use of commit (MySQL default auto-commit)

SELECT * FROM table name for update;//query the updated table, if the query results are locked, then it will be in a blocked state

14. View

View creation with MySQL, modifying columns that do not have errors that are different from MySQL (MySQL is invalid but does not error)

Group functions, groups by clauses, distinct keywords, rownum pseudo-columns, columns defined as expressions cannot use update with insert. Adding with Read only indicates that the view is read-only.

15. Sequence

Similar to auto_increment, but different, auto_increment must be on the index, and each table is unique, for the union index after sorting the same as the left 1 field, sequence, unique value, can act as a primary key value, shared objects, loading memory can improve access efficiency, The first default does not exist, need to use nextval before using Currval, can not modify sequence values, need to delete after reconstruction, you can modify the sequence of increment, maximum, minimum, looping options, or whether to load memory, the sequence of cracks may appear in multiple tables using the same sequence, Rollback (the rollback is not valid for the sequence), the system crashes.

Grammar:

Create sequence sequence;

[Iincrement by N]--the value of each increment

[Start with N]-from which value begins

[{maxvalue n | nomaxvalue e}]

[{minvalue n | nominvalu e}]

[{cycle| nocycle}]--whether a loop is required

[{cache | nocache}]; --whether to cache login

16. Index

When you delete a table, all indexes based on that table are automatically deleted, the query speed of the Oracle server can be accelerated by pointers, the disk I/O is reduced, and the index is created to delete the same MySQL

17. Synonyms

Create synonym alias for table name;//created, similar to alias for table

drop synonym alias;//delete synonym

18. Rights Management

1) Create, modify, and change your password

Create user username identified by password;//Add User

Alter user username identified by new password;//Change Password

Grant permission to user name;//grant permission

Generally includes the following permissions

Create session (creating sessions)

CREATE table (creating tables)

Create sequence (creation sequence)

Create view (Creating views)

CREATE PROCEDURE (creating a stored procedure)

Note: You need to create a table permission, in addition to creating table permissions, you need to use the ALTER user username quota Unlimited on users

2) Role creation, modification

Create role role name;//Creating Roles

Grant permission to role name;//grant permission

Grant role to User name ...;//give role to user

Grant object permissions on object to user/role;//creator has all permissions, System has permissions, owner assigns permissions to other people, role user is public and has permissions for everyone

Grant object permissions on object to user/role with Grant option;//users also have the right to assign permissions

3) Remove Permissions

Revoke permissions on object from user/role;//with grant OPTION's permission is also retracted

Revoke permissions on the object from the user/role Cascade constraints;//Remove the Cascade permission, a gives b,b c,a to use cascade constraints Remove the permissions of B, then B gives Geic the same permission to clear

Note: The rights assignment can be queried by the table, you can delete the corresponding user and role

Role_sys_privs role-owned system permissions

Role_tab_privs role-owned object permissions

User_role_privs User-owned roles

User_tab_privs_made user-assigned Permissions on table objects

USER_TAB_PRIVS_RECD user-owned Permissions on table objects

User_col_privs_made user-assigned object permissions on columns

USER_COL_PRIVS_RECD user-owned object permissions on columns

User_sys_privs user-owned system permissions

19.set operator

Union/union all//union can be de-weighed, union all does not go heavy, intersect//Take intersection, minus//minus, usage is the same as union

20.pl/sql syntax

1) General usage

Declare

Declares a variable//a int (10) or a table name. field name%type; The field name%type indicates the same type as the field name of the table name, and changes the type of the fields.

Type variable class is record (//similar to class and property in Java

b table name. field name%type;

）

Variable class instance variable class

The cursor cursor name is select: Defines a cursor that places the query results of a select row into a cursor,

Begin

Select ... into variable class name instances from ... the order and quantity of the field names after//select must be consistent with the variable class name, and subsequent use can use the class name. Variable Read

Fetch cursor name into variable;//Extract cursor value, cursor name%found similar to iterate hasnext, you can use loop to extract the value that each cursor points to

Close cursor name//close cursor

Exception//No write exception, then this is not written here, when the type of exception then output printing;

End

2) function

Create or Replace function name (parameter) return value type

is declares a variable similar to declare;

Begin

Return

exception

End Function name;

3) Stored procedure//Remove return on the basis of function

Create or Replace procedure stored procedure name (parameter)

AS//Declaration

Begin//execution

exception//Stored Procedure exception

End

Note: The parameter type has 3 in can be a value, out must be a variable, inout must be a variable//create procedure procedure name (in Parameter name data type, out parameter name data type, inout parameter name data type), the steamer process is lagging , the assignment is passed to the external global variable only after the internal completion.

4) Trigger

Create or replace trigger trigger: Behind the same MySQL

Note: To use the output need to turn on set serveroutput;

Oracle (2) Basic Primer for Oracle