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