Oracle Study Notes (2)

As I mentioned earlier, permissions are divided into system permissions and object permissions. Here we will explain object permissions. Object permission is the user's permission to operate on Data Objects of other users. Data Objects mainly include tables, views, and triggers. Object permissions include select, insert, update, delete, exec, all, index, refereces, and execute. All indicates select, insert, update, and delete permissions. For example, if I grant the search permission of Scott's table work to Xiaoming, the syntax is as follows:

 
SQL> Grant Select OnScott.Work ToXiaoming;
 

Then, we can view Scott's work table data after logging on to Xiaoming. The command is as follows:

 
SQL> Select * FromScott.Work;
 

If you want a user to grant the object permission to another user, you can add the with grant option;

 
Grant Select OnScott.Work ToXiaoming with grant option;
 

If we do not want Xiaoming to have this permission, we can revoke it. The command is as follows:

 
Revoke Select On Work FromXiaoming;
 

Here is a question: if user a grants the permissions of a table to user B, if user A does not have the permissions of this table, does user B still have the permissions of this table ?? Try it by yourself. Haha!

If you want a user to be locked for one day after the wrong password is entered three times, the command line is as follows:

 
CreateProfile loginlimt (Lock name) limit failed_login_attempts3(Times) password_lock_time1(Days)
 

 
Set User restrictionsAlter UserXiaoming profile loginlimt
Unlock a userAlter UserXiaoming account unlock
 

Next I will introduce some common functions.

Lower to lowercase select lower (ename) from EMP; upper to uppercase select upper (ename) from EMP; length to get the character length select ename from EMP where length (ename) = 5; the length of the substr truncation character contains three parameters. The first parameter indicates the truncated character, the second parameter indicates the truncation starting from the nth parameter, and the third parameter indicates the truncation of several select substr (ename, 1, 3) from EMP; the replace function has three parameters. The first parameter indicates the replacement string, and the second parameter indicates the character to be replaced, the second parameter indicates the select Replace (ename, 'A', 'ilove') from EMP;
 

 
The column to be searched by nvl (column name, default value) has a null value, which is replaced by the default value.
 

In addition, there are round (n, [m]) Rounding, Ceil (n) rounded up and rounded up, floor (n) rounded down and mode (N. M) modulo functions;

Here I will introduce the very important function, the conversion function to_char ();

Run the select sysdate from dual command to obtain the system time. In Oracle, the date format is yyyy-dd-mm by default, so some do not meet the Chinese's yyyy-mm-dd habits. to modify the format, use to_char, that is, select to_char (sysdate, 'yyyy-mm-dd hh24: MI: ss') from dual, get

10:35:15, hh24 indicates the 24-hour system, and hh12 indicates the 12-hour system. Add_month (,DN), last_day (N) and several date functions, which are particularly useful for reporting.

To_char () not only converts the date, but also converts the salary, to_char (columnname, 'l99999. 99 '), L in l99999.99 represents the local currency. The first five 9 represents five digits before the decimal point. If not, 2 9 after the decimal point represents two decimal places. Where l can be C (International Currency symbol), $ (USD ). For specific usage, you can view other information, too many.

Similar functions are available in Microsoft SQL Server. In order to facilitate your own learning, I also summarized the advantages of comparative learning.

Select upper ('abc') Select power (3, 3) --- Select round (3.513, 2) Select substring ('abc', 1, 2) to the power of 27) --- extract the string length select Len ('avbc') -- Obtain the string length select getdate () Select dateadd (D, 3, getdate () -- add a few days to the time, where D can be changed to select year (getdate () such as YY and M -- get select Day (getdate () -- get day select month (getdate () -- get month
 

 
Select convert (nvarchar (100), getdate (), 111); --- 2012/02/28 Conversion Function, where 111 can be changed. For details, referArticle 
 

The following are some common field types in Oracle, which are very basic!

CHAR: character type. Although it occupies space, it is very efficient and extremely fast to query. Because it is a full match, it is often used to store data of a certain length, such as ID card and student ID.

Varchar2: variable-length character type. It saves space, but the query speed is slow. It can store articles, news, and so on.

Number: One of the digit types. The maximum value is the 38 power of 10. If it is number (5, 2), it indicates the 5-digit effective number of The number, 2 decimal places, and number (5) an integer that represents a five-digit valid number. The event type date, starting with timestamp is an extension of date. Blob is used to store binary data, slices, and sounds, but we usually use the address to store the data. If the sound of these images is particularly important, we can directly store them in the database.