Oracle makes additions and deletions to the table, as well as some simple actions for the user

1. Verify that the database has been successfully installed

The Select status from V$instance status displays the open

2. How to return the Oracle database to the operating system

Exit

3, login Oracle database A total of two authentication methods

Operating system authentication: By default, if you log in to the Oracle database on a native server, you can use the operating system authentication without entering the database user name and password

User Password Authentication: If it is a remote link database, you need to authenticate with a database user name and password

4. How to access Oracle database Enterprise level Manager (OEM)

You can open the Oracle Database Enterprise Manager page by entering the OEM address that you recorded when you installed the database in Internet Explorer

5. Dynamic table

Oracle's dynamic tables are also part of a dictionary table, a table that belongs to Oracle, and cannot be changed manually, because the data in the table changes based on the data of other tables, and in Oracle dynamic tables, often beginning with "v$", V$instance is one of the Oracle dynamic tables, This table records the various information for an Oracle DB instance

6. How to close the Oracle database

Shutdown immidiate

7. How to start Oracle

Startup

8. How to know the current database name

View the name of the database: Show parameter db_name

Show parameter: Represents a view of various parameters in an Oracle database

Db_name: Represents the name parameter of the Oracle database, which can be used to see the specific parameter values.

9, querying the database of a user in what state can query the database dictionary table Dba_users to understand the user state, Dba_users store all the user information in the Oracle database

10. Dictionary table: An internal table of the Oracle database, in other words, a table that does not need to be created in the Oracle database and cannot change its information

11. Query database Default User Scott user status

Select Username,account_status from dba_users where username= ' Scott '; results no data found

Analysis reason: When querying data in an Oracle database using SQL statements in Sqlplus, Oracle defaults to case-insensitive inEnglish, but the values represented by each field following the where condition are case-sensitive

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/57/1A/wKioL1SReQWhNvd-AAC5XnBQZHc391.jpg "title=" Qq20141217203514.png "alt=" Wkiol1sreqwhnvd-aac5xnbqzhc391.jpg "/>

EXPIRED: Indicates that the user has expired

LOCKED: Indicates that the user is in a locked state, cannot use the database user, if need to reuse, open indicates that the user is in a normal state, can access the data in the database

12, Query dba_users table column: Desc dba_tables;

Explanation: DESC is the abbreviation of describe, followed by the table name can be queried out of the structure of the table, that is, the columns are composed, but also can be used describe user_tables

13, how to unlock a database user.

Syntax: ALTER USER database username account unlock

Eg: Unlock scott:alter user Scott account unlock

View the status of the SCOTT User: Select Username,account_status from dba_users where username= ' SCOTT ';

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/57/1B/wKioL1SRhNSjXBjyAACVH7MhC1g373.jpg "title=" Qq20141217212601.png "alt=" Wkiol1srhnsjxbjyaacvh7mhc1g373.jpg "/>

Scoot the user has been unlocked to be in an expired state, log back in with the user's original password, and set a new password to release the expiration status

14. View current database User: Show user

15. Switch users in the database

Syntax: Conn username/password

Eg:conn scott/tiger Change Password for cat

Other users switch back to SYS User: Conn/as sysdba, view Scott user status

Select Username,account_status from dba_users where username= ' SCOTT ';

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/57/1E/wKiom1SRlXDTL_fiAACdDLP-H74954.jpg "title=" QQ Pinyin is not named. png "alt=" wkiom1srlxdtl_fiaacddlp-h74954.jpg "/>

Open indicates that the user is in an opened state

16. Log in to the database directly in the operating system with the Scott user

Sqlplus User name/password

Eg:sqlplus Scott/cat

View all users: Select User#,name from user$

Modify user password: Alter users Scott identified by Scott

17, see the user below are those tables

Under current user, enter select table_name from User_tables;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/57/1E/wKiom1SRnyDRQabbAAB1QkqPl0Q797.jpg "title=" 111111.png "alt=" Wkiom1srnydrqabbaab1qkqpl0q797.jpg "/>

Detailed

Select: Represents the query, followed by the column in the table to query, if you query multiple column values, you can use commas to separate

TABLE_NAME: is the field indicated in the User_tables, and if "*" is used, all fields in the table are represented

The keyword in the From:sql statement, which indicates the meaning, followed by the table name

User_tables: Refers to the table name, that is, the table column data all query out, the practical Oracle database internal tables (also data dictionary) dedicated to query the user own those tables

Semicolon (;): Is the end symbol of a query statement

18. Increase, delete, check, change

There are those columns in the Query Table Dept DESC Dept

Query section (check all columns in dept): SELECT * FROM Dept

Add a department (Insert raw in dept): INSERT INTO Dept (Deptno,dname, LOC) VALUES (' development ', ' Beijing ') (column name is not uppercase, table name brackets are blank, values brackets are empty) Not required by the grid)

Commit a commit;

Update Department table data (change table data): Update dept set loc= ' Shanghai ' where deptno=50

Commit

SELECT * FROM dept;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/57/1F/wKiom1SSKfiw9whLAACzfJjWPdU595.jpg "title=" Qq20141218091315.png "alt=" Wkiom1sskfiw9whlaaczfjjwpdu595.jpg "/>

Delete a piece of data: delete the development department

Delete from dept where deptno=50;

Commit

SELECT * FROM dept;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/57/1F/wKiom1SSKuuhJ4RdAABldeIIGGo139.jpg "title=" Qqq.png "alt=" Wkiom1sskuuhj4rdaabldeiiggo139.jpg "/>

19. In Oracle's Sqlplus, the English characters are left-aligned by default, while numbers are the default right-aligned

20. Change the View

Alter session set nls_date_format= ' YYYY-MM-DD ';

Detailed

Alter: This is the keyword of the SQL statement that changes the parameters of the Oracle database, Wei the beginning of the SQL statement, representing the change, generally in changing the parameters of the database for the use of the vector coupling;

Session: Represents a conversation in the computer, which means that only the time format of the current session is changed. Other people linked to the database will not see the changed format;

Set: Indicates the meaning of the ' set ' parameter;

Nls_date_format: This is the date parameter of the Oracle database, although the date is the same, but it can have a different time date format;

21, Query Database current time: select Sysdate from dual;

Description: Sysdate is the current date of the database, dual is a virtual table of the Oracle database, that is, a table that is not a real existence, in the query to calculate, constant expression, etc. can be used when the dual table.

22. View employee's current working years

Select Ename, round ((sysdate-hiredate)/365.0) from EMP;

Explanation: Sysdate-hiredate, which indicates the current time minus the entry time, calculated how many days

(sysdate-hiredate)/365.0: Calculation work for several years

Round (x, y): is a rounded function of the Oracle database, X is the number that needs to be rounded, and Y indicates which one needs to be rounded, such as y=0, that is, in the case of you, the y=2 table is the two digits after the decimal point

23, according to the number of working years to find out the name and age of each employee

Select ename, HireDate feom emp ORDER by HireDate;

Select ename, HireDate feom emp order by 2; Where 2 represents the second column, which is HireDate

24, to find out the number of years of work per employee, the number of years in reverse order data

Select Ename, Rount ((sysdate-hiredate)/365,0) from emp Order by Rount ((sysdate-hiredate)/365,0) desc;

25. Change to Chinese column name

Select Ename as "name", Rount ((sysdate-hiredate)/365,0) as "work (years)" from EMP Order BY "work (years)" DESC;

Explanation: As is a keyword for column aliases, you can also use the alias of a column to wake the sort in Oracle

26, temporary more to each employee to send 1500 yuan bonus

Select Ename, asl+1500 from emp order by Sal;

It is easier to understand the text of the employee's salary as it is queried.

Select Ename| | ' Employee salary for this month: ￥ ' | | (sal+1500) as "Company employee salary statement for the month" Order by Sal;

Explanation: | | : is an Oracle connector that links the queried data with other strings, and can be connected to the column values of multiple characters and tables

': Which is a string that can be put inside the string output

27, the results of the query only show a unique value, filter out the duplicate data

SELECT DISTINCT deptno from EMP;

28. View employees with a salary of less than 2000 and sort by salary

Select Ename, sal from EMP where Sal <=2000 order by Sal;

Explain:

Where: keywords that restrict conditions

ORDER BY: Sort

29. View employee information for salaries between 1500 and $2500

Select Ename, sal from EMP where Sal between and 2500 order by Sal;

Explain

Between...and: Used in a conditional where to represent a number between two values

30. View sales staff with guaranteed wages

Select Empno, ename, Job, Sal from EMP where job= "salesman" and sal=1250;

View employees with no bonus or less than $1500 in salary

Select Empno, ename, Job,sal, comm from EMP where comm= null or SAL < 1500;

Employee salary plus Commission, which employee is least paid

Select Empno, ename, Job, Sal, Comm, SAL+NVL (comm,0) where comm = NULL or sal<1500 ORDER by SAL+NVL (comm,0);

NVL (x, y) is an internal function of the database, and if x has a value, the value of x is returned, and if x is null, the Y value is returned

31, Fuzzy query: See the name of "M" beginning of the employees

Select Empno, ename, Job, Sal from EMP where ename like "M%";

32, find those employees belong to sales staff, analysts, managers

Select Empno, ename, job from EMP where job in (' Salesman ', ' ANALYST ', ' MANAGER ');

Same as: select Empno, ename, job from EMP where job= ' salesman ' or job= ' ANALYST ' or job = ' MANAGER ';

Explanation: In, means that there is more than one reference in a column, or you can use or instead

33, statistics company each post how many people, and sort

Select Job, COUNT (*) from the EMP group by Job ORDER by COUNT (*);

COUNT (*) is a statistical number of functions

34. Total salary paid to employees

Select sum (SAL) +sum (NVL (comm,0)) as "total payroll expenditure" from EMP;

Statistic the average salary of the company's employees

Select round (AVG (SAL), 2) from EMP;

Explanation: Avg: a function to average an Oracle database

34. Query the maximum, minimum, and gap of employee's salary

Select Max (sal), Min (Sal), Max (SAL)-min (SAL) from EMP;

Max: is an internal function of the Oracle database that is dedicated to the largest number

Min: is an internal function of the Oracle database that specifically counts the smallest number

35. Search for jobs with average salary greater than 2500

Select Job, AVG (SAL) from EMP has avg (SAL) >2500 group by job;

Explanation: Having: When using a group by group, if a conditional restriction is required to use having, rather than where, the condition in group by IS limited to having

Oracle makes additions and deletions to the table, as well as some simple actions for the user