Oracle Database statements

Oracle Database Statements

Oracle supports five types of integrity constraints

Not NULL (non-NULL)-prevents null values from entering the specified column, defined on a single column basis, and by default, Oracle allows null values in any column.

Check (check)-Checks whether the condition specified in the constraint has been met.

Unique (unique)--guarantees that there are no duplicate values in the specified column. Each value in the table, or each set of values, will be unique.

PRIMARY key (primary key)--used to uniquely identify each row of a table, and to prevent null values from appearing, a table can have only one primary KEY constraint.

Poreign key (foreign key)--A parent-child (Parent-child) relationship is established between the tables by using a public column, and the foreign keys defined on the table can point to the primary key or to the unique key of the other table.

The linesize sets how many characters are displayed per line set of 300;

2 set how many records are displayed per page set pagesize 30;

3 User name switching: such as Conn System/tiger Conn Sys/change_on_install as SYSDBA (note that Superuser is added as SYSDBA at the back)

4 looking for a normal user's table under Superuser is not found. You must look for such as SELECT * from Scott.emp (EMP table under normal user)

5 View current is that user login: show user;

6 See how many tables: SELECT * from tab; (note that tables under different users are different)

7 View the structure of the table: DESC EMP (EMP is the table name)

8 Remove duplicate columns (DISTINCT): such as SELECT DISTINCT job EMP (remove duplicate value of job)

9 link operation of strings by: | |

10 Query employees with bonus: select* from EMP where comm are NOT null;

11 Query Employee Information without bonus: SELECT * from EMP where comm is null;

122 conditions above will have to use and such as query wages heavy rain 1500 and have the bonus staff select * from EMP where sal>1500 and comm are NOT null;

13 means that two conditions have one satisfaction to use: or if you are querying employee information with a salary greater than 1500 or no bonus Select * from EMP where sal>1500 or comm are NOT null;

14 negate can use not such as query staff wages not greater than 1500 and have the bonus employee information such as: Select * from EMP where not (sal>1500 or comm is not NULL);

15 in what between what with between----and----such as querying employee information between 1500 and 3000 wages: Select * from EMP where Sal between and 3000;

16 Query Employee number is 2323, 4555, 2222 employee specific information: such as Select * from EMP where empno in (2323,4555,2222);

17.L Fuzzy query like General union "%" and "_" use where%: means that can match any length of content, "_" means matching a length into content such as:

Query Employee Name Second letter is M employee information: Select * from EMP where ename like ' _m% ';

And if the name contains M's employee Select * from EMP where ename like '%M% ';

18oracle does not equal two representations of "<>" and "! ="

19 sort with ORDER by {ASC DESC} where ASC is sorted in ascending order if you do not write it by default in ascending order desc is sorted in descending order by the end of the SAL statement as:

Sort by employee's salary

Select * from emp ORDER by Sal ASC (Ascending)

SELECCT * from emp ORDER BY sal Desc (Descending)

Select * from EMP where deptno= ' ORDER by Sal desc,hiredate ASC; (Query department 10 employee salary in ascending order if wages are equal, sort by employee's entry time)

20.group by is used to group the results of a query: Displays the average wage and maximum wage for each department, such as: Select avg (SAL), Max (SAL) from EMP Group by DEPTNO;

The 21.Having clause is used to limit the grouping display result: Displays the department number and his average salary of the average salary greater than 2000, such as: Select AVG (SAL), Deptno from EMP Group BY DEPTNO have avg (SAL) >20 00;

2. Single-line function:

1 lowercase to uppercase: Upper such as SELECT * from emp where Ename=upper (' Smith ');

Converts a string into lowercase letters such as: Select Lower (' HELLO world ') from DUAL;

Capitalize the first letter of the word with initcap such as: SELECT Initcap (' HELLO World ') from DUAL;

2. Manipulation of strings

SUBSTR () intercepts the length of the string () replace () replacement string

3 Numeric functions

Rounded: round ();

truncated decimal digits: trunc ();

A Introductory section

1. Create a table space
Create tablespace schooltbs datafile ' D:\oracle\datasource\schooltbs.dbf ' size 10M autoextend on;

2. Delete Table spaces
Drop tablespace schooltbs[including contents and datafiles];

3. Querying table Space Basic information
Select *| | Tablespace_name from Dba_tablespaces;

4. Create a user
Create User Lihua
Identified by Lihua
Default Tablespace Schooltbs
Temporary tablespace temp;

5. Change the user
Alter user Lihua
Identified by 123
Default tablespace users;

6. Lock the user
Alter user Lihua account Lock|unlock;

7. Delete a user
Drop User Lihua cascade;--delete users mode

8. Roles in the Oracle database
Connect,dba,select_catalog_role,delete_catalog_role,execute_catalog_role,exp_full_database,imp_full_database, Resource

9. Grant the role of the connection server
Grant connect to Lihua;

10. Granting a role using tablespace
Grant resource to Lihua with Grant option;--the user also has authorized permissions

11. Permissions granted to the action table
Grant Select,insert on USER_TBL to scott;--current user
Grant Delete,update on LIHUA.USER_TBL to scott;--system administrator

12. Modify the structure of the table (ALTER)

Alter table name Add (Name of column, type of column);

Oracle Database statements