[Reprinted] basic Oracle knowledge and basic oracle knowledge

[Reprinted] basic Oracle knowledge and basic oracle knowledge

I. oracle Installation Process

Ii. sys and system users

(1) The sys user is a super user with the highest permissions, The sysdba role, and the create database permission.

The default password is change_oninstall.

(2) The system user is a management operator with a high level of permissions and has the sysoper role, but does not have the create database permission. The default password is manager.

(3) Generally, database maintenance is sufficient to log on using the system user.

Iii. oracle startup

The so-called Start refers to the oracle instance, that is, OracleServiceORCL. When using java to connect to the database, the listener must be started; otherwise, JDBC cannot be used.

4. oracle Data Objects

In oracle, tables, views, stored procedures, and triggers are called Data Objects in oracle.

V. oracle management tools

Sqlplus is a tool software provided by oracle. It is mainly used to execute SQL statements.

 

 

 

1. The following are common sqlplus commands

(1) conn [ect]

Usage: conn username/password @ network service name [as sysbda/sysoper]

When a user is a privileged user, it must include as sysbda/sysoper

Example: conn system/manager

(2) disc [onnect]

Disconnect

(3) passw [ord]

Change the password. If you want to change the password of another user, use sys or system to log on.

(4) show user

Show current user

(5) exit

Disconnect and exit sqlplus

Ii. File Operation commands

(1) start ,@

Run the SQL script

For example, if a. SQL file exists in d:, run the following command to execute the content in a. SQL.

SQL> @ d: a. SQL or

SQL> start d: a. SQL

(2) edit

Edit the specified SQL script

SQL> edit d: a. SQL

(3) spool

This command can output content on the sqlplus screen to the specified file.

SQL> spool d: B. SQL

SQL> select * from emp;

Spl> spool off

Note: The spool command outputs the query result of select * from emp; to the file at the specified position, and then spool off is similar to enabling/disabling IO.

3. Interactive commands

(1 )&

Can replace the variable, and the variable needs to be input during execution

SQL> select * from emp where job = '& job ';

Oracle will prompt the user to enter the value

Iv. Display and set Environment Variables

It can be used to control various output formats. If you want to permanently save relevant settings, you can modify the glogin. SQL script.

(1) linesize

Set the display row width. The default value is 80 characters.

SQL> show linesize

SQL> set linesize 120

(2) pagesize

Set the number of lines displayed on each page to 14 by default. The usage is the same as linesize.

The usage of other environment parameters is similar.

 

 

1. Oracle user management

Create userThe DBA permission is required.

Command:Create user [user name] identified by [Password]

Change Password

Command:Password [user name] (when the user is connected)

Note: When changing passwords for other users, you must have DBA or alter user system permissions.

Command:Alter user [user name] identified by [New Password]

Delete a user

Generally, users are deleted as DBAs.

If the user you want to delete has already created a table, add the cascade parameter to the deletion.

Command:Drop user [user name] [cascade]

Ii. permissions and Roles

The newly created user does not have any permissions, even the database logon permission. When you use conn [user name]/[Password], you will be prompted that you do not have the permission.

After creating a user, you must authorize the user. Of course, you must use authorized users, such as sys and system.

Permissions include system permissions and object permissions.

System permission: the user's permissions on the database

Object permission: the user's permission to operate on Data Objects of other users

Role

A role is a set of system permissions. Generally, when a user is granted permissions, if no role exists, one operation is required, and the role exists.

It makes authorization very convenient. Generally, a role consists of multiple system permissions. Common roles include connect (7 permissions), dba, and resource (create tables in any tablespace ).

Here is just a simple introduction, and will be studied as a topic in the future.

Use the grant commandAssign Permissions:

Grant [permission name] to [user name]

Assign a role:

Grant [role name] to [user name]

Revoke permissions:

Revoke [permission name] from [user name]

For example:

1. Create a user

Create user stu identified by stu;

2. Enable stu to be connected

Grant create session to stu;

3. Allows stu to create tables in any tablespace

Grant resource to stu

3. Create a simple table

Create table users (name varchar2 (10), age number (2 ));

4. Insert several data entries

Insert into users values ('houjinxin', 22 );

5. log on to scott and authorize stu so that stu can view the emp table under scott.

Grant select on emp to stu;

6. log on to stu to view the emp table.

Select * from scott. emp;

If you want to update data in scott. emp

Update scott. emp set ename = 'ok2' where ename = 'OK ';

The ORA-01031 is prompted: the permission is insufficient. Scott only gives stu the permission to view data. If you still want to update the data, you must authorize it under scott.

7. log on to the system and revoke the resource role.

Revoke resource from stu;

8. log on to scott and revoke the select permission.

Revoke select on emp from stu;

This is stu, so you can no longer query scott. emp's data.

Transfer Permissions

When you want stu users to query scott's emp table, you also want stu to pass this permission to other users.

If you want to passObject permissionAdd with grant option

Grant select on emp to stu with grant option

If yesSystem Permissions: Add the with admin option.

Grant connect to stu with admin option

When the system authorizes stu, it will authorize other users

Make an experiment to verify

1. log on to the system user and create two new users.

Create user hou identified by hou;

Create user jin identified by jin;

Assign the connect role to hou.

Grant connect to hou with admin option;

2. log on to scott and authorize the next hou.

Grant select on emp to hou with grant option;

3. log on to hou and start jin authorization.

Grant select on scott. emp to jin;

Grant connect to jin;

4. log on to jin to query scott. emp.

Select * from scott. emp;

The current location is normal. The problem is coming!

If the system revokes the permissions assigned to hou, Will jin's permissions be revoked together and the experiment continues.

5. log on to scott and revoke the hou permission.

Revoke select on emp from hou;

Revoke connect from hou;

6. log on to jin.

It is found that you can still log on to jin. This indicates that the connect role has not been withdrawn.

When scott. emp is queried, The ORA-00942 is prompted: The table or view does not exist.

This indicates that the system permission is different from the object permission. For system permissions, hou is not revoked after being assigned to jin, but object permissions are also revoked as hou permissions are revoked.

 

 

Use profile to manage user passwords

Profile is a set of commands for password and resource restrictions. When a database is created, oracle automatically creates a profile named default. If the profile option is not set for the created user, oracle assigns the default option to the user.

(1) account locking

Specify the maximum number of times a password can be entered during logon, or the user lock time, in days. Generally, you can use the dba identity to execute commands. For example, you can specify a maximum of three logon attempts for stu, and the lock time is 2 days.

SQL> create profile lock_account limit failed_login_attempts 3 password_lock_time 2;

SQL> alter user stu profile lock_account;

(2) Unlock an account

SQL> alter user stu account unlock;

(3) Termination Password

To allow users to change their passwords on a regular basis, you can run the command to terminate the password. Similarly, this command also requires dba identity to create a profile file for stu, the user is required to change the login password every 10 days. The grace period is 2 days.

 SQL> create profile stu limit password_life_time 10 password_grace_time 2;

SQL> alter user stu profile stu;

The unlock method is the same as above.

(4) Password History

If you do not want to use a previously used password when changing the password, you can use the password history. In this way, oracle stores the password modification information in the data dictionary, in this way, oracle will compare the new password with the new one when the user changes the password. If the new password is the same, the user will be prompted to re-enter it.

For example:

 SQL> create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 10

SQL> alter user stu profile password_history;

Password_reuse_time 10 indicates that the password can be reused after 10 days

(5) delete a profile

 Drop profile password_history [cascade]

Cascade indicates that if you still want to delete the profile when you have allocated the profile to a user, you must add cascade

 

 

Table Management

I. Table Name and column naming rules

1. It must start with a letter

2. The length cannot exceed 30 characters

3. oracle reserved words cannot be used

4. Only the following strings can be used: A --> Z, a --> z, 0 --> 9, $, #, etc.

Ii. Data Types supported by oracle

1. Balanced

Char fixed length, up to 2000 characters

For example, when char (10) is stored as "Xiaohan", the first four characters are placed as "Xiaohan", and the last six characters are filled with spaces.

Advantage: High Efficiency and fast query speed. For example, the ID card field can be set to char (18 ).

Varchar2 (recommended for oracle)

When varchar2 (10) is stored as "Korean", oracle allocates 4 characters

Clob (character large object) large object

Maximum 4 GB

2. digit type

Number range:-power 38 of 10 to power 38 of 10

It can be an integer or a decimal number.

Number (5, 2) indicates that a decimal place has 5 Valid digits, and 2 digits are decimal places.

For example, you can use number (5, 2) to define a number in the range of-999.99-999.99)

Defines a range of-99999-99999 which can use number (5)

3. date type

Date includes year, month, day, hour, minute, and second

Timestamp oracle's extension to the date type

4. Image Type

Blob binary data, which can store pictures, audios, and videos up to 4 GB

This type allows us to store large files in the database, but generally in the database, it should be the path of these files. If you have security requirements, you can put the files into the database

Iii. Table creation statements

1) create a table

SQL> create table student (-- table name

Idnumber (4), -- Student ID

Namevarchar (20), -- name

Sex char (2), -- Gender

Birthday date); -- date of birth

The preceding statement is sufficient to create a simple student table.

2) add fields to the created table

SQL> alter table student add (ClassId number (2 ));

3) modify the length of a field

SQL> alter table studentmodify (Name varchar2 (50 ));

4) modify the field type/or name (data is not allowed)

SQL> alter table student modify (Name char (20 ));

SQL> alter table student rename Name to Sname;

5) delete a field (use it with caution)

Alter table student drop column Sex;

6) modify the table name

SQL> rename student to stu;

7) delete a table

Drop table student;

8) view the table structure

Desc student;

Iv. operation table

1. Add data. All fields must be inserted.

Insert into student values (1, 'zhang san', 'mal', '01-August-05 ');

Do not think it is wrong here, the default date format in oracle 'dd-MON-YY '(day-month-year)

You can modify the default format of a date as follows:

SQL> alter session set nls_date_format = 'yyyy-MM-DD ';

After modification, you can use the format we are familiar with to add types.

Insert into student values (1, 'zhang san', 'mal', '2017-08-31 ');

However, the modification here is only temporary. To change the date input format permanently, you need to change the Registry. Another method is to use the function, which is skipped for the moment.

2. Insert some fields, provided that the field not inserted can be null.

Insert into student (Id, Name) values (1, 'zhang san ');

3. Insert null values

Insert into student (Id, Name, Sex) values ('1', null, null );

4. query a record with an empty Name

According to the normal logic, many people will do this.

Select * from studentwhere Name = null;

However, none of these results can be found. The correct method is as follows:

Select * from student where Name is null;

To query all non-empty objects, add not after is.

5. modify a field

Update student set sex = 'female 'where Id = '1 ';

6. Modify Multiple Fields

Update student set sex = 'male', Name = 'zhao si' whereId = '1 ';

7. modify a field containing null values

Update student set Name = 'zhang san' where Name is null;

8. delete data (method 3)

1) delete a record

Delete from student where Id = '1 ';

2) deleting all records, the table structure is still in progress, and logs are recorded. Such deletion can be restored and the speed will be slower.

Delete from student;

3) Delete the table structure and data

Drop table student;

4) delete all records, the table structure is still in progress, and no diary is recorded. All such deletions cannot retrieve data, but the speed is very high.

Truncate table student;

9. Restore data

Data can be recovered when delete from student is used

1) first set a save point

Savepoint sp; -- sp is the name of the storage point. You can name it at will to save the data in the log.

2) delete data

Delete from student;

3) check whether the data has been deleted

Select * from student;

The result is yes, no data

4) Roll Back data

Rollback to sp;

5) Check whether data rollback is successful in the query.

Select * from student;

The data is certainly returned!

Of course, multiple save points can be set, but if not processed, the new save point will overwrite the previous one by default.

10. Cancel duplicate rows

Select distinct deptno, job from emp;

Duplicate data can be omitted by adding distinct after select during query.

 

 

Basic Oracle Query

First, we will introduce two commands in PL/SQL software.

1. Clear screen command

Clear

2. Close/open the show Operation Time Command

Set timing off/on

Second, there are two SQL skills

1. Quickly insert large amounts of data into the database

Insert into users (userid, username, userpass)

Select * from user;

The premise of using this statement is that the table must have at least one piece of data.

2. query the number of all records

Select count (*) from user;

Note that when writing SQL statements, pay attention to the Case sensitivity issue.

The Orace field is case-insensitive, but the entity is case-sensitive.

I. Use arithmetic expressions

? Displays the annual salary of each employee

The column alias can be used.

Select ename "name", sal * 12 as "annual income" from emp;

It is best to enclose Chinese Characters in quotation marks and try not to use Chinese characters.

? If there is a null value in the calculation expression, the calculation result is null. How to Deal with the null value?

Using nvl Functions

Select sal * 13 + nvl (comm, 0) "annual salary", ename from emp;

Nvl (comm, 0) means that if comm is null, it is calculated by 0 instead of by itself.

? How to connect strings

Use "|"

Select ename | 'is a' | job from emp;

Ii. Use the where clause

? How to show employees with salaries higher than 3000

Select ename, sal from emp where sal> 3000;

? How to find new employees after 1982.1.1

Select ename from emp where hiredate> '1-January 1, January-1982 ';

? Search for employees with salaries between and

Select ename from emp where sal> = 2000 and sal <= 2500;

3. How to Use the like Operator

%: 0 to multiple characters

_: Represents any single character

? How to display employees whose first letter is s

Select ename from emp where ename like's % ';

? How to display the name and salary of all employees whose third letter is uppercase O

Select ename, sal from emp where ename like '_ O % ';

4. Use the in clause in the where clause

? How to display the employees with empno 123,456,234

Select * from emp where empno in (123,234,456 );

This query is highly efficient.

5. Use the is null operator

? How to display employees without superiors

Select * from emp where mgr is null;

6. Use logical operation symbols

? Query employees whose salaries are higher than 500 or whose positions are MANAGER and whose names are written in uppercase T

Select * from emp where (sal> 500 or job = 'manager') and ename like't % ';

The brackets here cannot be forgotten. Otherwise, the conditions change because the priority of and is higher than or.

7. Use the order by clause

? How to display employees in order of salary from high to low

Select ename from emp order by sal desc;

Desc is in reverse order. asc is in order (default)

? Sort employees in descending order by Department number

Select * from emp order by deptno asc, sal desc;

8. sort by column aliases

Select ename, sal * 12 "annual salary" from emp order by "annual salary" asc;