Oracle Foundation SQL

Second, SQL structur Query Language Structured Query language, is the object in the Operation relational database.
DDL (data definition Language), used to create or delete table operations, and to modify table constraints CREATE TABLE, ALTER TABLE, DROP table additions and deletions to tables structure. DML (Data manipulation language), inserting records into tables, modifying records INSERT, UPDATE, delete, merge transaction, transaction control language, composed of DML statements, commit; , rollback; Select query Statement DCL Authorization statement grant
Oracle DBMS Database management system is provided by Oracle, as, Application Server DBA database administrator

Iv. related Operations 1, sqlplus Access database command (local access/remote access), and database to establish a connection to the command, is the environment of database Operation Sqlplus username/password 2, show user display user name change identity can directly connect username/password- -This is the Sqlplus command that can be used in Sqlplus! You can switch between the shell and Sqlplus, and the!shell command can use shell commands in Sqlplus. The Sqlplus actually opened the child process to execute the shell command. 3. The tables in Oracle database are divided into two categories: User tables (tables used by users), system tables (tables maintained by the database system, also called data dictionaries) to the user table of the DDL operation on the system table DML operation!

V. Basic grammar

1. Select query statement

Select table_name from User_tables; (Query system table) The query statement above is the name of the table that queries all tables owned by this user.

A projection operation that only views information for the selected field. Select an action to view specific information in the field. Join operation, multi-table query, through inter-table connection, search out the information in multiple tables

(1) Select table_name from User_tables; (Query system table) The query statement above is the name of the table that queries all tables owned by this user.
(2) The last SQL statement is cached in buffer sqlplus, you can use "/" to execute the last SQL statement, or you can use the Edit command to edit the last SQL statement. The l command (list) (Sqlplus command) can display the last command in buffer.
The Sqlplus command can be abbreviated
(3) DESC [table name] This is a sqlplus command, note that he is not an SQL statement, this command is used to view the structure of the table. descript abbreviation [field name] [Type of field], which is the table structure displayed after using the DESC command.
(4) Select [table field name 1],[table field Name 2], ... from table name; SELECT * from table name; Information for all fields in the lookup table
(5) Keywords are not equal to split, SQL statements, and table names, field names are case insensitive. The SQL statement is to be ";" End, to indicate the end of the SQL statement, if not added ";" The system does not execute this SQL statement and prompts. In Oracle, the character display is left-aligned and the value is right-aligned.
(6) A mathematical expression can be used in a SELECT statement. Select [Expression (must contain this table field name)],[],.... the From table name, the priority of the operation is multiplication after the first, the sibling is left-to-right, and the parentheses change precedence.

(7) Alias select [Field name or expression] ["Alias"],[...] ["..."],.... from table name, either by adding a space "Alias" after the field name or expression, or by giving the expression an alias for the result. Expression aliases must be enclosed in double quotation marks.
(8) string concatenation using | | Symbol Select Target Field name | | " "|| Target Field name from table name;

NOTE: Strings in Oracle use ' ... ' Include the use of spaces in aliases, or when case sensitivity is required ".." Contains.
Exercise: Write an SQL statement yourself, and the result is a select * from ...; where ... is the table name of each system table in front of the table name of each system table with "SELECT * from", plus ";" SELECT ' select * from ' | | table_name| | '; ' from User_tables;
2, processing error (1)!oerr ora [error number], the system can show the cause of the error and how to modify it. If the command error input can use Edit or ed to modify the input error. is actually editing the last SQL statement in the cache file. You can also use the (change) C/Error field/correct field to modify the replacement operation. Only available on Linux platforms! Equivalent to host, there is no disconnection, just a switch, execute the shell command (2) Edit command for editing the last SQL statement.

3, Sqlplus set pause on return response, split screen display, only in this session effective set pause off the split screen display. Set pause "..." Sets the display of the split-screen prompt information. Set pause on first output prompt message, enter response, split screen display set head off header output off set feed off end output off set echo off write off spool file name. SQL write to specified file spool off Write.

4, SQL script is the file written in the SQL statement files, you can run in Sqlplus. Introduction of SQL Script Sqlplus username/password @sql script (note: After the user name password entry end must add a space and then write the @sql script) in the last line of the script to write "Exit", then run out of script, back to the shell
5, NULL value in Oracle will be infinite processing, in fact, the null value is not stored at all, just as infinity.

In Oracle, the control handler function NVL (field name, value), the null value in this field is replaced with the specified value, and if not NULL, the original value is returned. Example: SELECT (salary*12) * (NVL (commission_pct,0)/100+1) salary,first_name from S_emp;

Distinct keyword, remove duplicate rows (this keyword triggers a sort operation) Example: SELECT distinct dept_id,title from S_emp; The union of DEPT_ID and title is not unique note: DISTINCT, after the keyword, the field before the from is drained.

6, Column command---sqlplus command column format definition

Column name to see if the class defines a format

Column target columns name format a. Sets the column width. Column last_name heading ' employee| Name ' Format A15 set the header of the ' | ' is a newline character.

Column salary justify left format $99,990.00 defines the digital display format Note: If the display format is not met, the data will be displayed as "#"
Column salary justify left format $00,000.00 will appear $00,928.00, with 0 padded

Column name Clear (Clear column type definition)

Note: Only the Sqlplus command has shorthand, and you cannot add a semicolon to the end when you use the Sqlplus command.

Vi. Select Action 1, ORDER by sort clause ASC (default, Ascending) DESC (descending) Order by Target column name (alias) sort order (does not write sort order, will default to ascending sort)

Example: Select First_Name from S_emp order by first_name; Select First_Name from S_emp order by first_name Desc;

Note: An ascending null value is at the end of the result, and a descending null value precedes the result.
2. WHERE clause

The WHERE clause uses the Select ... from ... Later, used to select the required (eligible) records

Where followed by the expression is xxx=xxx, xxx between x and x, xxx in (x,x,x) like ' ... ' Pass-Match Query

Between. And ..., indicating the result between this, between and is a closed interval, which is equivalent to ... <= ... and ... >=.!=,<>,^=, these three are not equal, <=,> ; =,=, these operators are available. ... in (Va1,val2,...) determine if the result exists in this enumeration like ' ... ' String wildcard query, '% ' denotes multiple characters, ' _ ', representing a character. Note: Escaped usage: like ' s\_% ' escape ' \ ' ... Indicates that only two conditions are met ... or ... Represents a condition as long as it satisfies only one of them can all ... are required to meet the conditions. Not ..., it can be reversed with the above conditions. A null value has an effect on not, which is not equal to any value, but an empty value exception. ... is null used to determine whether the value is empty.
NOTE: Strings in Oracle are strictly case-sensitive.

(1) Note data type, numeric type direct write, characters with ' ... ', the default format of date can be used ' ... ', only aliases are used "" contains. (2) Select the appropriate operator

Seven, one-line function 1. Character functions

The character is case sensitive to lowercase lower (field name)---where the argument can be a string constant or a field name to uppercase upper (field name) Capitalize initcap (field name) string concatenation concat (field 1, field 2) Intercept substring substr (word Segment name, starting position, number of characters) dual table, is dedicated to function testing and operation, he only a record string splicing concat (...,....) to specify the substring substr (..., starting position, number of characters) can use "-" to indicate right-to-left fetching, You can take it from the left to your friends. Example: Select SUBSTR (first_name,-2,2) sub from S_emp; (the second bit starts fetching two) select SUBSTR (first_name,2,2) sub from S_emp; (starting from the second to last fetch two) Select substr (ename,2,2), substr (ename,-3,2), ename from Scott.emp Select Concat (Initcap (ename), deptno) from Scott.emp; 2, numeric function

Rounding round (data, the number of reserved decimal points) can be expressed in negative numbers before the decimal point, 0, indicating the first digit after the decimal point, that is, reserved bits,-1 for the digit (reserved to 10 bits). Example: Select Round (15.36,1) from dual,---15.4 select round (15.36,0) from dual,---Select round (15.36,-1) from dual,---20 intercept Word function trunc (data, number of digits reserved (number of digits after the decimal point)) interception of single-digit after 0 cases: Select Trunc (123.456,1) from dual; ---123.4
3, Date function
Date format, full date format century information, month day, hours seconds. Default date format, day-month-year DD-MON-RR modifies the date format of the current session and outputs the date in the specified format alter session set nls_date_format= ' yyyy mm DD hh24:mi:ss ';

Returns the current date sysdate example: select Sysdate from dual; select sysdate+1 from dual; Get tomorrow's date, plus 1, day
Date is format sensitive how many months are separated by two dates months _between (date1,date2) Select Months_between (sysdate,sysdate+61) from dual; Add and subtract the specified number of months add_months (date, number of months), the number of months can be negative, and the negative value is minus the corresponding number of months. The first Friday next_day (date,friday)
, beginning with the date day, returns the date of the month Last_day (date) Intercept date trunc (date, ' year or month or day or seconds ') Example: Select Next_day ( sysdate,1) from dual;---1 Sunday 2 monday ... 7 weeks Six cases: Select Trunc (Add_months (sysdate,1), ' month ') from dual; ROUND (' 25-may-95 ', ' month ') 01-jun-95 ROUND (' 25-may-95 ', ' year ') 01-jan-95 TRUNC (' 25-may-95 ', ' month ') 01-may-95 TRUNC ( ' 25-may-95 ', ' Year ') 01-jan-95 select Trunc (sysdate, "Day") from dual? Select TRUNC (' 25-may-95 ', ' year ') from dual exercise: Returns the date of the first day of the next month select round (Last_day (sysdate), ' MONTH ') from dual; Select Add_months (trunc (sysdate, ' MONTH '), 1) from dual;
4, conversion functions between different data types

Convert date to character ToChar (date, ' date format ') date format to be in valid format, format case sensitive ' yyyy mm DD hh24:mi:ss ', ' Year ' (full spell years), ' mm ' (number expressed in months) ' Month ' (full spell month), ' Day ' ( Full spell of the week), ' ddspth ' (full spell of the date) ' yy mm dd ' Example: SELECT To_char (sysdate, ' ddspth ') from dual;

Converts a character into a digital to_number (' ... ')
Convert numbers to characters to_char (number, ' FMT ') FMT is a digital format

Convert String to date to_date (' ... ', ' date format ') Example: Select To_char (To_date (' 2006 ", ' yyyy mm DD '), ' Dd-month-yy ') from dual;

1. Equivalent connection
Select [Table alias 1. Field name 1],[table alias 2. Field Name 2],... from table 1 table alias 1, table 2 table alias 2 where table alias 1. Field name 3= table alias 2. Field name 4; When a table is connected, when there is a same name field between the table and the table, it can be distinguished by a table name or table alias, using the table name. field name or table alias. Field name (column name). When a table's field name is unique, you can not add a table name or table alias.

Note: When you alias a table, you can no longer use the table name. Field name.

Example: Select A.first_name,a.last_name,b.name from s_emp a,s_dept b where a.dept_id=b.id;

2, non-equivalent connection

Select [Table alias 1. Field name 1],[table alias 2. Field Name 2],... from table 1 table alias 1, table 2 table alias 2 where table alias 1. Field Name 3 ... Table Aliases 2. Field name 4

.... You can make the comparison operator, or you can make other operators other than ' = '

Example: Select E.ename, d.grade,e.sal from emp E,salgrade D where e.sal between D.losal and D.hisal;

3. Self-connected

Use aliases to divide the data in a table into two parts, and then filter by using conditions. Select [Table alias 1. Field name 1],[table alias 2. Field Name 2],... from table 1 table alias 1, table 1 table alias 2 where table alias 1. Field name 3= table alias 2. Field name 4;

Example: Select A.first_name ename,b.first_name cname from s_emp a,s_emp b where a.manager_id=b.id;

The table connections mentioned above are called Inner joins and strictly match the records of the two tables.
4. External connection

All the records in one table are used to match the records in the other table, the null values match, and all the records in the table are displayed, and the database simulates the records to match those that do not match.

Example: Select A.first_name enamei,a.id,b.first_name cname,b.id from s_emp a,s_emp b where a.manager_id=b.id (+); That is, using the data in table A to match the B table, if there is null in table B, the system simulation record matches

Note: To show all of the records of that party, there is also a notice condition (+) followed by the peer to be selected.
Applications for outer joins: List which department does not have an employee select E.deptno,d.deptno from emp e,dept D where E.deptno (+) =d.deptno and e.deptno is null;
Three, group function

Group GROUP BY grouping clauses, grouped by the specified grouping rules, this GROUP BY clause can be followed by a SELECT statement or after having a. The GROUP BY clause also starts the sort operation, sorted by grouping field.

Select [Group function or Group field name], ... from table name GROUP by [Field name 1],[field Name 2],..... ；

Example: Select AVG (Salary) from S_emp Group by dept_id;

Note: Group functions can process a set of data and return a value. The group function ignores null values.

Avg (..), averaging, sum (..), sum the parameters of these two functions can only be number type.

The functions mentioned below can use any type to make arguments. Count (..), which is used to count the number of records, can be used with the row weight command. Count (...) By default, all is used. Max (..), Min (..) Find the maximum and minimum values, COUNT (*), and the number of records in the statistics table.

Example: select Max (b.name), avg (a.salary), Max (c.name) from S_emp a,s_dept b,s_region C where a.dept_id=b.id and B.REGION_ID=C.I D GROUP by b.dept_id;

Note: As long as the GROUP BY clause is written, the * * Select can only be used after a group by field or a set of functions. The WHERE clause is only capable of filtering records and placing single-line functions.

The HAVING clause can filter the result of a group function or a grouped message, and it is written after a GROUP BY clause.

Example: select Max (b.name), avg (a.salary), Max (c.name) from S_emp a,s_dept b,s_region C where a.dept_id=b.id and B.REGION_ID=C.I D GROUP by b.ID have sum (a.salary) >4000;

Column can also define the format of the columns that have aliases. Column "Alias" format definition

Note: To filter out unwanted records first, then group operations, improve efficiency.

Four, sub-query

A subquery is a SELECT statement that can be embedded in any SQL statement.

When you nest a subquery in a SELECT statement, the subquery is executed first. Typically, the subquery is placed to the right of the operator.

Note: When using a subquery, be aware that the operator is a single line (that is, it can only be single-valued) or a multiline operator (range, multi-value, in). The results returned with a subquery must conform to the use of the operator.

Example: Select First_name,title from S_emp where Title=any (select title from S_emp where Last_name= ' Smith ') and upper (last_name )! = ' SMITH ';

Select First_name,title from S_emp where title in (select title from S_emp where Last_name= ' Smith ') and upper (last_name)! = ' SMITH ';

V. Translating business requirements into actionable tables

One: Demand analysis two: painting e-r figure three: Conversion into table relationship four: cutting (new and old system handover) Five:

E-r Chart Properties: * is a mandatory and non-empty property o Optional attribute (can have a value or not) #* indicates that this property is unique and not empty

Entity Relationship: Mastbean Maybean

Quantity Relationship: Many-to-one relationship-a-to-many relationship-to-many relationships

In the first paradigm, all attributes must be single-valued, that is, the attribute represents only a single meaning. (Records can be duplicated, without any restrictions) the second paradigm, where attributes are required to be unique and non-null (records are not repeatable, but data can be redundant). In the third paradigm, a non-primary attribute can depend only on the primary attribute and not on other non-primary properties. (Resolve data redundancy issues)

Vi. constraints

Constraints are defined for the fields in the table.

Primary KEY (PRIMARY KEY constraint PK) guarantees the integrity of the entity, guarantees unique primary key constraints for the record, is unique and non-null, and can only have one primary key in each table, two fields are federated as primary key, and only two fields are placed together to uniquely identify the record, called the Federated primary Key.

FOREIGN key (external constraint FK) guarantees referential integrity, foreign KEY constraints, the value of the foreign key is subject to another table in the primary key or the only one worthy of constraint, not be able to take other values, can only reference the primary key will be unique key value, the referenced table, called the parent table, The table of the referrer is called the child table, to create the child table, you create the parent table, then create the child table, the record is inserted, the child table after the table, delete the record, to delete the child table records, delete the parent table record, to modify the record, if you want to modify the parent table records to ensure that no quilt table reference To delete a table, remove the child table first, then the parent table.

Unuque key (Unique key) with a value of unique

Index is a database-specific class of objects, and the view (illustration) is a typical one-to-many class that corresponds to multiple students. Student Table Class Table ______________________________ _________________________ | ID | name | address| class_id| | ID |class_desc|class_num| | (PK) |______|________|___ (FK) __| | (PK) |__________|_________| | | | | | | | | | |

One

Student tabel Shenfenzheng table ____________________ _________________________________ | ID | name | address| | s_id |shenfen_desc|shenfen_num| | (PK) |______|________| | (PK,FK) |____________|___________| | | | | | | | |

Many-to-many

Student Tabel Zhongjian table Kecheng table ____________________ _________________________________ __________________ | ID | name | address| | s_id |shenfen_desc|shenfen_num| | Kid | kechengname| | (PK) |______|________| | (FK,FK) |____________|___________| | (PK) |____________| | | | | | Joint PRIMARY KEY | | | | | |

The primary key of the table is referred to as its primary key, so the primary key of the table is both the primary key and the external

Build tables and other related operations

DDL statements

Creating Tables: Create table table name (field name 1 type (data length) (default ...) constraint, field name 2 type (data length) constraint);

Data types in an Oracle database

varchar (length), variable-length string, char (length) fixed length number (.,..), number represents a floating-point number, or is an integer long large object, a large object of clog characters, equivalent to a text file in a table that only holds one equivalent to a value-only blog Binary large objects are also stored in the form of pointers. PRIMARY KEY constraint: the definition of a PRIMARY KEY constraint: The first definition form: CREATE TABLE Test (c number primary key); Column-level constraints SELECT * The second definition form of the FROM test: CREATE TABLE Test (c number, primary key (c)); Table-level Constraint CREATE TABLE test (C1 number constraints PKC1 primary key); This constraint has the name: PKC1 CREATE TABLE Test (c number, C2 number, primary key (C, C1)); Using table-level constraints to implement federated primary keys

Foregin Key (FK) FOREIGN KEY constraint: (define parent table first, then define child table) CREATE table parent (C1 number primary key); CREATE Table child (c number primary key , C2 number references parent (c1)); or table-level constraint definition: CREATE TABLE child (c number primary key, C2 number, foreign key (C2) references parent (C1));

If two fields are unique and non-null, you can define the Uk+not NULL

(PK or UK) one-to-many (FK) (Pk+uk) Single-to (FK) or (PK) A-to-many-to-many relationship, usually through an intermediate table to decompose into two one-to-many tables

CREATE TABLE Create Table[schema]table schema: one user corresponding to a schema different users under the table cannot view each other

Select COUNT (*) from s_dept; <===> Select COUNT (*) from sd0611.s_dept;

A table can only store a long type CLOB storing large text object blobs storing large binary objects

CREATE TABLE test (C1 number primary key); Set the primary key CREATE TABLE test (C1 number constraints TEST_C1 primary key); Define the constraint name, the default constraint named Sys_, after the column defines a constraint called a column-level constraint, CREATE TABLE test (C1 number primary key (C1)); After all columns have been defined, the definition constraint is called a table-level constraint (can define a federated primary key) Cretae table test (C1 number,c2 number,priary key (C1,C2)); Define the Federated primary key create TABLE child (C1 number primary key); First, you define the parent table CREATE TABLE child (C1 number primary key, C2 number references parent (c1)); Then define the child table references parent to define the foreign key create TABLE child (C1 number primary key, C2 number references parent (c1) on delete cascate); On delete cascate for cascade Delete CREATE TABLE child (C1 number primary key, C2 number references parent (c1) on delete set null); On delete Set NULL delete the foreign key is empty CREATE TABLE child (C1 number primary key, C2 Number,foreignkey (C2) References parent (C1));

Ii. constraints

1, non-empty constraint (NOT null) This is a column-level constraint when you build a table, you add not NULL after the data type, that is, the insertion is not allowed to insert null values. Example: CREATE table student (ID number primary key,name varchar2 (+) not null,address varchar2 (32));
2. Unique UNIQUE constraint is a unique constraint that ignores null values and requires that the value in the inserted record be one. Example: CREATE TABLE student (ID number,name varchar2 (+), address varchar2 (+), primary key (ID), unique (address)); If you create a UK, the system automatically builds a unique index
3, PK, UK oralce support cascade Delete, do not support cascading updates

4, check constraint checking constraints, you can follow the specified conditions, check the insertion of records. You cannot use a tail column in a check, you cannot use a function, and you cannot reference another field. Example: Create TABLE sal (A1 number, check (a1>1000));

Original source: http://www.360doc.com/content/12/0927/22/1007797_238535838.shtml