Oracle Common noun Parsing

Create user
Overview: In Oracle, to create a new user using the Create USER statement, you typically have DBA (database administrator) permissions to use.
Create user username identified by password; (Oracle has a problem, the password must start with a letter, if it does not start with a letter, it will not create the user)
Change password for user
Overview: If you change your password you can use it directly
Password User name
If you change the password for someone else, you need to have DBA authority, or have alter user's system permissions
Sql> alter user username identified by new password
Delete User
Overview: Generally as a DBA to delete a user, if you use other users to remove users will need to have drop user permissions.
For example, DROP user username "cascade"
When deleting a user, note:
If you want to delete the user, has created a table, then you need to delete the time with a parameter cascade;

Data type
character class
Char is set to a maximum of 2000 characters long.
Example: char (10) ' Xiao Han ' first four characters descriptors ' Xiao Han ', after adding 6 spaces to complement the whole like ' Xiao Han '
varchar2 () The maximum length of 4,000 characters.
Example: VARCHAR2 (10) ' Korean ' Oracle assigns four characters. This will save space.
CLOB (character large object) maximum 4G for character type large objects
Char queries the speed of a very fast waste of space, query more data.
varchar saves space
Digital type
Number Range-10 of 38 parties to 10 of 38 times
Can represent an integer, or it can represent a decimal
Number (5,2)
Indicates that a decimal number has 5 valid digits, 2 decimal places
Range: 999.99 to 999.99
Number (5)
Represents a 5-bit integer
Range 99999 to-99999

Date type
Date contains date and time of year Oracle default format January-January-1999
Timestamp this is an extension of the oracle9i to the date data type. Can be accurate to milliseconds.
Image
Blob binary data can be stored picture/sound 4G Generally speaking, in the real project is not to put the picture and sound really into the database, generally store pictures, video path, if security needs relatively high, then put into the database.

Modify Table
Add a field

Sql>alter TABLE Student Add (classId number (2));
Modify the length of a field
Sql>alter TABLE Student MODIFY (xmVARCHAR2 (30));
Modify the type of field/or name (cannot have data) do not recommend to do
Sql>alter TABLE Student Modify (XM CHAR (30));
Deleting a field is not recommended (after deletion, the order is changed.) Add to the problem, should be added in the back)
Sql>alter TABLE student DROP COLUMN Sal;
Add data
All fields are inserted into the data n
INSERT into student VALUES (' A001 ', ' Zhang San ', ' Male ', ' January-May-05 ', 10);
Default date format in Oracle ' DD-MON-YY ' DD days (days) Mon month yy 2-bit year ' September-June-99 ' June 9, 1999
Modify the default format of the date (temporary modification, the database is still the default after the restart, if you want to modify the registry)
ALTER SESSION SET nls_date_format = ' yyyy-mm-dd ';

DELETE from student;
Delete all records, the table structure is still in, write log, can be restored, the speed is slow.
The data for the Delete can be restored.
SavePoint A; --Create a save point
DELETE from student;
Rollback to A; --Revert to the Save point
An experienced DBA that creates a restore point periodically when it is ensured that it is complete without error.
DROP TABLE student; --delete the structure and data of the table;
Delete from student WHERE xh = ' A001 '; --delete a record;
Truncate TABLE student; --Delete all the records in the table, the table structure is still in, do not write the log, can not retrieve deleted records, fast.

If there is a grouping function in the column, the other must be a grouping function,

GROUP BY and HAVING clause n
Group BY is used to group statistics on the results of a query,
The HAVING clause is used to restrict the display of grouping results.
1 The grouping function can only appear in the select list, having, ORDER BY clause (cannot appear in the where)
2 If the SELECT statement contains group BY, have, order by then their order is group by, have, order by
3 in the Select column, if there are columns, expressions, and grouping functions, then the columns and expressions must have one in the GROUP BY clause, or an error will occur.
(if there are n tables in a federated query, you have to have N-1 conditions to avoid the Cartesian collection)
Self-connect

It needs to be explained here. When a subquery is used in the FROM clause, the subquery is treated as a view and is therefore called an inline view, and when a subquery is used in the FROM clause, the subquery must be given an alias. &NBSP
merge query
Sometimes in real-world applications, in order to merge the results of multiple SELECT statements, you can use the set operation symbol Union,union All,intersect,minus
, which is used for data banks with large data volumes and runs faster.
1). Union
This operator is used to obtain a union of two result sets. When the operator is used, duplicate rows in the result set are automatically removed.
2). UNION ALL
The operator is similar to union, but it does not cancel duplicate rows and is not sorted.
3). Intersect
  Use this operator to get the intersection of two result sets.
4). Minus
Using the change operator to get the difference set of two result sets, he only shows the presence of the first collection, not the data in the second collection.
There are two ways to create a database:
1). Through the wizard tools provided by Oracle. √
   metabase configuration assistant  "database Config Assistant"
2). We can create them directly using manual steps.

transaction
transactions are used to ensure data consistency. It consists of a set of related DML statements whose DML (Data manipulation language, additions and deletions, no query) statements either succeed or fail altogether.
For example: Online transfer is typically handled using transactions to ensure consistency of data.
DML Data Manipulation Language
Bank transfer, QQ application, ticket purchase
  Transaction and lock N
when performing transactional operations (DML statements), Oracle locks on the table being applied, preventing other users from modifying the structure of the table. This is very important for our users to come to terms with.
..... Other process sequencing, know process 1th completed, lock Open, process 2nd entered. In turn, if there is a higher process level, you can queue up.
  COMMIT TRANSACTION
A transaction can be committed when execution is executed with a commit statement. When a commit statement is executed, the transaction is confirmed to be changed and the transaction is ended. Delete the savepoint, release the lock, and when the commit statement is used to end the transaction, other sessions will be able to view the new data after the transaction has changed. The
save point is for fallback. There is no limit to the number of Save points
  fallback transactions
before we introduce a fallback transaction, let's introduce the concept and role of the SavePoint (savepoint). A savepoint is a point in a transaction. Used to cancel a partial transaction, and when the transaction is ended, all the savepoint-defined save points are automatically deleted. When executing rollback, you can fall back to the specified point by specifying a savepoint,
  Several important operations of the transaction N
1. Set SavePoint savepoint a
2. Cancel partial transaction rollback to a
3. Cancel All transactions Rollback
Note: This fallback transaction must not be used before commit; If the transaction is committed, then no matter how many save points you have just made, none.
If you do not perform a commit manually, but exit, it is automatically committed

Read-only transaction n
Read-only transactions are operations that allow only queries to be performed, not transactions that perform any other DML operations, and use read-only transactions to ensure that users can only get data at a point in time. Assume that the ticket sales start at 18 o'clock every day to count today's sale, you can use a read-only transaction. After a read-only transaction has been set, the read-only transaction will not have the latest data changes, although the other sessions may commit new transactions, thus guaranteeing the acquisition of data information at a specific point in time.
Set read-only transaction n
Set transaction Read only;

Table Space
Table spaces are used to logically organize the data for a database. A database is logically composed of one or more table spaces. The following functions can be achieved through Tablespace:
1. Controlling disk space consumed by the database
2. DBAs can deploy different data types to different locations, which helps improve I/O performance while facilitating management operations such as backup and recovery.

Constraints

Maintain the integrity of your data
Data integrity is used to ensure that database data complies with certain commercial and logical rules, and in Oracle, data integrity can be implemented using three methods of constraints, triggers, applications (procedures, functions), in three ways, because constraints are easy to maintain and have the best performance. So as the first choice to maintain data integrity.
Constraints are used to ensure that database data meets specific business rules. In Oracle, constraints include: NOT NULL, unique, primary key, ForeignKey, and check five.

Index
An index is a data object used to speed up data access. Proper use of indexes can significantly reduce I/O times and thus improve data access performance. There are many kinds of indexes we mainly introduce several commonly used:
Why does the query speed up when the index is added?
Create an index
Single-column index
A single-column index is an index that is based on individual columns, such as:
Create index index name on table name (column name);
Composite Index
A composite index is a two-column or multiple-column index. You can have multiple indexes on the same table, but the combination of required columns must be different, such as:
CREATE INDEX emp_idx1 on EMP (ename, job);
CREATE INDEX emp_idx1 on EMP (Job, ename);
Principles of Use
1. Building indexes on large tables makes sense
2. Indexing on columns that are frequently referenced on a WHERE clause or join condition
3. No more than 4 levels of index
Can you show this effect to the students here?
How to build a big table?
Disadvantages of the Index
The index has some congenital weaknesses:
1. Indexing, the system will occupy about 1.2 times times the disk and memory space to save the index.
2. When updating data, the system must have additional time to update the index simultaneously to maintain data and index consistency.
The practice shows that inappropriate indexes not only help but degrade system performance. Because a large number of indexes spend more system time than no indexes when inserting, modifying, and deleting operations.
For example, it should be inappropriate to create an index in the following fields:
1. Fields that are seldom or never referenced;
2. Logical type fields, such as male or female (yes or no), etc.
To sum up, improve query efficiency is to consume a certain system resources at the expense of the index can not be blindly established, this is the test of a DBA is an important indicator of excellence.
Permissions
Permissions are the rights to execute a specific type of SQL command or to access other schema objects, including both system and object permissions.

Role
A role is a collection of commands related to permissions, and the primary purpose of using roles is to simplify the management of permissions.

Process
Procedures are used to perform a specific operation, when the process is established, you can specify either an input parameter (in) or an output parameter (out), you can pass the data to the execution part by using the input parameters in the procedure, and you can pass the execution part of the data to the application environment by using the output parameters. You can use the CREATE PROCEDURE command in Sqlplus to establish the process.
CREATE PROCEDURE Sp_pro3 (spname varchar2, newsal number) is
--Do not write number (3,2), indicating that the type is available and does not require size. Just like the parameters of Java when writing methods

SQL code

1. Begin
2. --Execute part, revise salary according to user name
3. Update emp set sal=newsal where Ename=spname;
4. End
5. /Begin--Execute section, modify salary according to user name

Update emp set sal=newsal whereename=spname;

End

/

public static void Main (string[] args) {

try{

1. Load Driver

Class.forName ("Oracle.jdbc.driver.OracleDriver");

2. Get Connected

Connection CT =drivermanager.getconnection ("Jdbc:oracle:[email protected]:1521:myora1", "Scott", "m123");

3. Create CallableStatement

CallableStatement CS =ct.preparecall ("{Call Sp_pro3 (?,?)}");

4. Assigning Values to

Cs.setstring (1, "SMITH");

Cs.setint (2,10);

5. Implementation

Cs.execute ();

Shut down

Cs.close ();

Ct.close ();

}catch (Exception e) {

E.printstacktrace ();

}

}

}

function
function is used to return specific data, and when a function is established, the function header must contain a return clause. The body of the function must contain the data returned by the return statement. We can use CREATE function to create functions, the actual case:

SQL code

1. --Enter the employee's name and return the employee's annual salary
2. Create function Annual_incomec (name VARCHAR2)
3. Return number is
4. Annual_salazy number (7,2);
5. Begin
6. --Executive Section
7. Select SAL*12+NVL (comm, 0) into the Annual_salazy from EMP where ename=name;
8. return annual_salazy;
9. End
10. /

--Enter the employee's name and return the employee's annual salary

Create FUNCTIONANNUAL_INCOMEC (name Varchar2)

Return number is

Annual_salazy number (7,2);

Begin

--Executive Section

Select SAL*12+NVL (comm, 0) into Annual_salazyfrom emp where ename=name;

return annual_salazy;

End

/

If the function creation process has compile errors, you can use show error;
Calling a function in Sqlplus

SQL code

1. sql> var income number
2. Sql> Call Annual_incomec (' Scott ') Into:income;
3. sql> Print Income

sql> var income number

Sql> Callannual_incomec (' Scott ') Into:income;

sql> Print Income

Again, we can call the function in a Java program
Select Annual_income (' SCOTT ') from dual;
This allows the returned result to be obtained by Rs.getint (l).
Trigger
A trigger is an implicit execution of a stored procedure. When you define a trigger, you must specify the event that is triggered and the action that is triggered, and the commonly used trigger event includes the Insert,update,delete statement, and the triggering action is actually a PL/SQL block. You can use create trigger to create a trigger.
Special Note:
We'll show you the use of triggers in detail later, because triggers are very useful to maintain the security and consistency of your databases.

Declare

C_tax_rate number (3,2): = 0.03;

--User name

V_ename VARCHAR2 (5);

V_sal number (7,2);

V_tax_sal number (7,2);

Begin

-Execution

Select Ename,sal into V_ename,v_sal fromemp where empno=&no;

--Calculation of income tax

V_tax_sal: = v_sal*c_tax_rate;

--Output

Dbms_output.put_line (' name is: ' | | v_ename| | ' Salary: ' | | v_sal| | ' tax: ' | | V_tax_sal);

End

/

View
A view is a virtual table whose contents are defined by a query, which, like a real table, contains a series of column and row data with names. However, the view does not exist in the database as a stored set of data values. Row and column data is derived from the table referenced by the query that defines the view, and is generated dynamically when the view is referenced. (The view is not on a real disk)
The difference between a view and a table
Difference between a view and a table n
1. The table requires disk space, and the view does not require
2. Views cannot be indexed (so queries are slightly slower)
3. Use views to simplify, complex queries
For example: Student elective system
4. Use of views for improved security
For example: Different users view different views
Create/Modify views
CREATE VIEW N
CREATE VIEW name as SELECT statement [with Read Only]
Create or modify a view n
Create or Replace view view name as SELECT statement [with Read Only]
Delete View n
Drop View Name
When the table structure is too complex, please use the View bar!
--Create a view that maps the sal<1000 employees of the EMP table to the view

SQL code

1. CREATE VIEW MyView as SELECT * from EMP where sal<1000; CREATE VIEW MyView as SELECT * from EMP where sal<1000;
   --To simplify the operation, use a view to resolve the display employee number, name and department name

SQL code

1. CREATE View myview2 as select Emp.empno,emp.ename,dept.dname from emp,dept where Emp.deptno=dept.deptno;

Create View Myview2 as Selectemp.empno,emp.ename,dept.dname from Emp,dept where Emp.deptno=dept.deptno;

Federated queries can also be made between views

Oracle Common noun Parsing