Preface: Relationship Database 1970.dr.efCodded Creates a model of a relational database (similar to the currently commonly used two-dimensional table) relational database consists of many data objects, and the relationship is integrity between the SQL commands. Constraint conditions. RDBMS (Object Relational Database Management System) Oracle Rdbms Always called Oracle Server It is written in C language.
Oracle's brief introduction Oracle is a comprehensive technical product, program, service database company Oracle database design to store data, quick query data, guarantee data security and consistency, cross-network distributed management and customer-server Configuration, etc. Oracle SQL syntax complies with an ANSI 1986 Standard SQL and Database Command Language SQL * Plus Oracle's tool to run SQL and PL / SQL statement PL / SQL Oracle process programming language
First, choose a line 1. Simple SELECT statement select [Distinct] {*, column [alias], ...} from table_name; field's non-unique result set Distinct field name 1 [, field name 2] Digital Type Field name Arithmetic operation - * /, etc., for example: (Field Name 1 * Field Name 2) / 3 Connection Field Contents || Field Name 1 || Field Name 2 Field You can have an alias SELECT field name 1 [as] 'Field Name 1 Explain' from Table;
2. Handling nullnull undefined, unconfirmed, nothing is null! = 0 null! = 'Spacer' and NULL any operation returns NULLORCLE to convert NULL functions into other types of symbols for nullnvl functions NVL (EXPR1, EXPR2) function explanation: if expr1 = null return expr2 else Return EXPR1 programming skill: NVL function is more useful in multi-condition fuzzy query, more useful NVL (' INPUT_VALUE ', '0') = '0' or filed_name Like '% input_value %' NVL function returns a variety of data types: Return date NVL (start_date, '2002-02-01') Returns a string NVL (Title, 'NO Title') Returns NVL (Salary, 1000)
3. Use SQL * Plus (1) to log in to SQL * Plus method username, password, database connection string database connection string can be used to establish a local network service name configuration, or manually edit $ Oracle_home / network / admin /Tnsnames.ora file. Do not use $ sqlplus username / password @ DBNAME to see the user's password SQL> DESC TABLE; display table structure SQL> Select * from tab; view users all table sql> Set Pause On; Make a large number of result sets After the user presses "Enter", turn the SQL> SET PAGESIZE 100; set the SQL statement to return the result set 1 page number 100, the default value is 14 SQL> Set Linesize 100 ; Set the SQL statement to return the result of the width 100, the default value is 80
3. Using SQL * Plus (2) SQL * Plus The editing command in the Plus has the latest SQL command statement in Oracle Memory, but the SQL * Plus command will not store. Editor command in SQL * Plus: a [ppend] text Adds Text to the back of the current line C [hange] / old / new / replace the current row old symbol to NEW symbol, New is empty, remove the OLD symbol CL [EAR] BUFF [ER] Removes all line DEL N from SQL Buffer Del N Delete SQL Buffer NPT I [NPUT] Text Insert Text L [IST] N Remove the N-line N of SQL Buffer to Currently Row n TEXT NT RESULTS TEXT 0 Text Insert a row in front of the first line 3. Using SQL * Plus (3) SQL * Plus file command: SAV [e] filename [rep [LACE] | App [End] ] Save the SQL command in the SQL Buffer to the next file filename, the default file name suffix is. SQL. REP [LACE] Replace the SQL command app [end] in the filename in the filename, add the SQL command in the SQL buffer to FileName GET FileName reads the SQL command from the filename to SQL Buffer Sta [RT] filename runs the last SQL command @FileName runs the last SQL command Edit to open the editing window, edit the SQL Buffer in the file filename. SQL Command Edit FileName Opens Edit Window, Editing the SQL Command SPO [OL] filename in FileName SPO [OL] filename [off] outputs the SQL command result to the next file filename OFF End Output to the file, OUT ends output to the file and print Document 4. SQL * Plus specifies the display format of the field specified in the display format SQL> Column field name format 9999999999; SQL> Column field name Format 9999, 999, 999, 999; Specify string display width SQL> Column field name format A number [word_wrapped] Description: Only the length of the digital bit is displayed, more than the length of the fold, add word_wrapped, the word will not fold the display width SQL> SET Long 200 The specified field name display content SQL> Column field name Heading 'field name display content'; sql> set heading OFF; Query When the field name is not displayed in the alignment direction SQL> Column field name Justify [Left | Right | Center] ; Clear Format SQL> Column field name clear;
5. SQL * PLUS in a predetermined display format example SQL field> column last_name heading 'Employee | Name' format a15; SQL> column salary justify right format $ 99,999.99; SQL> column start_date format a10 null 'Not Hired'; Note: If start_date For NULL, display string 'not hired'
6. Decision (T / F) (1). SQL Command Are Arew Sql Buffer. [T] (2). SQL * Plus Command Assit with Query Data. [T] SQL * Plus command only controls the select result set Display format and control file. Only SQL commands can access databases. Second, restriction selection line 1. Sort the SELECT Expr from table in the specified rule [ORDER BY {Column, EXPR} [ASC | DESC]]; the default sort is ASC Ascending (by small to large) can also be the location of the ORDER BY field [1] | [2] ASC | DESC; when the field name is complicated or the location of the field name is sorted by the field name when the arithmetic expression is displayed.
2. Use the WHERE Limit Select Line (1) Comparison Operator => <> = <=! = <> = = Null Compare Cannot Use the above comparison operator Any Some Allsql operator Between ... and ... in Like is Null Not Between ... and ... NOT IN NOT LIKE IS NOT NULL Logic Operator And OR Not NOT
3. Use the WHERE Limit Selection Line (2) to compare the order (you can use parentheses to change their order) (1). = <>> = <= In Like is Null Between (2). And (3). OR Note: char The comparisons of VARCHAR2 have different: Char comparison, the space behind the string is ignored. VARCHAR2 will calculate the space behind the string
4. LIKE operation% zero to any multiple characters _ a character such as: field name Like 'm%' field name Like '% M%' field name LIKE 'JOB_' If you want to find the underlined character, you want to add a reverse slash For example: Field name Like '% X / _Y%' Escape '/'
5. Comparison example of the date field: Date field betWeen to_date ('2001-12-12', 'YYYY-mm-dd') and to_date ('2002-02-01', 'YYYY-MM-DD') date field > to_date ('2001-12-12', 'yyyy-mm-dd') and date field <= to_date ('2002-02-01', 'YYYY-MM-DD');
6. Cannot use the indexed comparison operator is Nullis Not Nullike '% M%'
Third, the single line function 1. Digital function ABS Take the absolute value POWER Multime LN 10 for the base Power SQRT square root EXP E N-multiplier log (m, n) m is the base N Power Mathematical Operation function: ACOS Atan Atan2 CoS COSH SIGN SIN SINH TAN TANH CEIL is greater than or equal to taking a number of Floor less than or equal to taking a total number of Round (n, m) Round (n, m) Press M to take a four-family value if the Round (date) is later. Date will be tomorrow. Round (sysdate, 'y') is the first day of the year Trunc (n, m) Press M's bit to take the previous value if trunc (date), and it is omitted to remove the time
2. Character Function CHR Press the character set of the database by the number returns the characters CONCAT (C1, C2) to combine the two characters C1, C2 into a character, and || The same Replace (C, S, R) appears in the character c The characters are replaced with R, return new characters SubStr (C, M, N) m greater than 0, the character C starts to take N-bit characters from the front M, M is equal to 0 and 1, M small and 0, character c from behind M, " At the beginning, the N-bit Translate (C, F1, T1) character c is started to convert the rules of F1 to T1 to the new string INITCAP character, the first letter uppercase, other characters lowercase LOWER characters All lowercase Upper characters All uppercase LTRIM (C1, C2 The character c2rtrim (C1, C2) TRIM (C1, C2) that appears on the left side of the character C1 removes the character C2LPAD (C1, N, C2) character C1 on the left and right sides of the character C1 to display the number of bits in the number of bits N to display the number of bits. C2 The string replaces the left space RPAD (C1, N, C2) 3. Date function add_months (d, n) Date value plus N-month Last_Day (d) Returns the date of the last day of the month Months_between (D1, D2) two dates values Month, D1 4. Conversion function (2) TO_DATE Date Display Format YYYY YEAR YYY YY Y Q Quarter MM Month Mon W Week (Week Of Year) (Description: Week is based on ISO standard, from On January 1st, the number of days to last seven days is one week. It is not necessarily from Monday to Sunday) DD Day DY HH24 HH12 HH MI Minute SS Second If you want to fix the date display format can be in the database of parameter file inTorasid.ora Xinwen a line of parameters NLS_DATE_FORMAT = YYY-MM-DD HH24: MI: SS can set up NLS_DATE_FORMAT = YYY-MM-MM-DD HH24: MI: SS in UNIX environment variable or NT registry 4. Conversion function (3) If you want a fixed date display format to change SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DDTD HH24: MI: SS'; its function order is as follows: Initialization Parametenvironment VariableAlter Session Command 4. Conversion function (4) TO_CHAR (NUMBER) Number display format 9 Digital Bit 0 Digital front Support 0 TO_CHAR (-1200, '00000.00'). The position of the decimal point, the comma of the tag position is based on the left L according to the digital display format Database character set currency symbol to_CHAR (-1200, 'L9999.99) B Displays the number 0 as space, use the negative mark TO_CHAR (-1200,' 999999mi ') in the right side of the number display format (-1200,' 9999.99mi ') PR Negative TO_CHAR (-1200, '9999.99PR') EEEE uses an index method to represent to_CHAR (-1200, '9999.99eeee') 5. Enter the character, return the number of functions INSTR (C1, C2) character C2 appears in the position of C1, does not appear, returns 0, often used in the fuzzy query length (c) to calculate the length of the character C, follow The character set of the database is related to the length of a Chinese character is 16. Function NVL (Expr1, Expr2) function explained by logic comparison: if expr1 = null return expr2 else return expr1decode (AA, V1, R1, V2, R2 ....) Function Explanation: if aa = v1 Then R1 IF AA = V2 Then R2 .. ... Else Return NULL Example: Decode (ID, 1, 'DEPT SALE', 2, 'DEPT TECH') Fourth, select data record from multiple tables 1. Simple connection syntax between datasheets: SELECT field name 1, field name 2, ... from table name 1, [Table name 2, ...] Where table name 1 . Field name = Table name 2. Field name [and ...]; select field name 1, field name 2, ... from table name 1, [Table name 2, ......] Where table name 1. Field name = Tame name 2. Field name ( ) [and ...]; "There is a ( ) field location automatic null value Classification: equal to connection = not equal to connection! = BetWeen ... and ... in note in and OR can not be used with a field name ( ), no satisfied condition supplies air value self connected to the same table you follow If you connect yourself, for example, find a replication record 2. Connection example of the data table Delete the table_name table Field name Email Repeat record: SQL> Delete from table_name T1 where t1.rowid> (SELECT MIN (Rowid) from table_name t2 where t1.email = t2.email group by email Having count (email)> 1); Find a mobile phone user's service area: SQL> SELECT A. Handphoneno, NVL (C.Name, 'Null'), a.totalscore from Topscore A, ChargeOperator CC, ChargeOperatorInfo C Where Substr (A. Handphoneno, 1, 7) = CC .hpnohead ( ) and cc.chargetype = c.chargetype ( ) Order by a.totalscore desc; 3. The connection skills between the data table connects N tables. You need N-1 connection operation to be connected. It is best to build a single-character alias, and the field name plus this single character alias Between .. and. > = And <= Field name to be a good connection operation is best to have an index connection operation with an integer digital type, which cannot be used to operate with OR or IN. 4. How to analyze and execute SQL statements Write multiple tables When connecting SQL statements, you must know how its analysis is executed. SYS users run @ / oracle_home / sqlplus / admin / plustrce.sql Generated Plustrace role SYS users give this role to give this role General User SQL> GRANT PLUSTRACE TO & UserName; General User Running @ / Oracle_Home / RDBMS / Admin / UTLXPLAN.SQL Generate Plan_Tablesql> Set Time ON; Description: Open Time Display SQL> Set Autotrace ON; Description: Open Automatic Analysis Statistics, and Displaying the running result of the SQL statement SQL> SET Autotrace Traceonly; Generally speaking, our SQL statement should avoid a full mete scan of a big table. SQL> SET Autotrace Off; Description: Turn off Auto Analysis Statistics 5. Collective Functions are often used with Group By 1. Collective List AVG (Distinct | All | N) Take average COUNT (DistINCT | All | N | EXPR | *) Statistics Max (DistINCT | All | N) Take the Maximum Min (DistINCT | All | N) Take the Minimum Sum (DistINCT | All | N) Getting Value STDDEV (DistINCT | All | N) Removal Value, if in the group The selected content is the same, and the result is 0Variance (DistINCT | All | N) to take a square difference value 2. Using aggregate functions Syntax SELECT column, group_function FROM table WHERE condition GROUP BY group_by_expression HAVING group_condition ORDER BY column; Precautions 3. count SELECT COUNT (*) FROM table; SELECT COUNT (constant) FROM table; They are all recorded in the statistics table. If there is no PK, there are some Select Count (all field names) from table; select count from table; The number of fields that do not count the fields of NULL, and the fields of NULL are ignored when AVG. 4. The restrictions of the restrictions in Group By cannot be casual, and if you want to include the group and alias when you group BY, you cannot limit the display result of Group By, with a Having Condition 5. Example SQL> Select Title, SUM (SALARY) Payroll from S_Emp Where Title Like 'VP%' Group By Title Having Sum (SALARY)> 5000 ORDER BY SUM (SALARY) DESC; find the number of records repeated in a table, and display SQL> SELECT (DUPLICATE FIELD NAMES) from table_name group by (list outfields) HAVING Count (*)> 1; 6. Judgment questions (T / F) (1) Group functions include nulls in calculation [f] (2) Using the hat Calculation [f] Explanation: Group function is ignored if a null value To calculate the null value, use the NVL function where statement in GROUP BY, eliminates the result set outside the HAVING statement after group BY, excludes the result set out 7. Other commands that can be used in SQL * Plus: Ctrl ^ c Terminate Run SQL Statement Remark /*...*/ - Note Some UNIX can be used in the command of the symbol Host executable operating system! Break on column_name skip n [On Column_name Skip N] Separates the display, clearer, Skip N is the number of spaces in the separation of N Break on row Skip N, each line interval Compute Collection Operator Of Field 1 ON Field 2 Press Field 2 to Field 1 Make Collection Operations COMPUT OR: SUM Minimum Maximum Avg Std Variance Count Number 8. Examples of other commands available in SQL * Plus: (Scott User) Break on Report Compute Sum Label Total of Sal on Report At all results sets SELECT ENAME, SAL from Emp where job = 'salesman'; Compute Avg Label Avg Of Sal On Report In full Results, the average value / again performs the last SQL statement Break on Deptno Skip 2 on Job Skip 1 In the Break Field Results Set Back Almediate Compute Sum of Sal On Deptno Select Deptno, Job , Ename, Sal from Emp ORDER BY DEPTNO, JOB; SQL> Clear Breaks; Clear Settings Break Conditions SQL> Clear Computes; Clear Settings COMPUTE Conditions Six, child query 1. Query statement can be nested, for example: select ... from (select ...... from table name 1, [table name 2, ...] Where Conditions) Where condition 2; 2. Where can I query? When the query condition is uncertain, the DML (INSERT, UPDATE, DELETE) statement can also be queried in having, also available 3. The results of the two query statements can do a collection operation, for example: set Union (deduplication) and set union all (do not deduct repetitive records) Differences MINUS, intersection intersect 4. Precautions for subqueries First perform the SQL statement in parentheses, one layer to the outside inside the outside, if only one, the result set returns multiple, can not use => <> = <=, etc. . 5. Example of child query (1) SQL> SELECT TITLE, AVG (Salary) from s_emp group by title haVing avg (SALARY) = (SALARY) "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" Job name and salary 5. Example of subqueries (2) Subquerry You can use the table name in the parent query This SQL statement is correct: SQL> select cty_name from city where st_code in (select st_code from state where st_name = 'Tennessee' and city. CNT_CODE = state.cnt_code); Description: The parent query call sub-query is only once. 6. SQL statement of 80 to 100 to take out the result set Oracle handle only one ROWNUM field indicating its logical position and can only use rownum> 80. The following is a SQL statement that has obtained the result set 80 to 100 after analysis (ID is the field name of the unique keyword): Statement Writing: SQL> SELECT * FROM ((SELECT ROWNUM AS NUMROW, C. * From (SELECT [Field_Name, ...] from table_name where condition 1 Order BY Condition 2) c) Where Numrow> 80 and Numrow <= 100) Order BY Condition 3; 7. Binding variables when executing SQL statements 1. SQL * Plus command accepting and defining variables ACCEPT Define undefine & 2. An example of binding variable SQL statement (1) SQL> SELECT ID, LAST_NAME, SALARY from S_EMP where dept_id = & department_number; Enter value for department_number: 10old 1: select id, last_name, salary from s_emp where dept_id = & department_number; new 1: select id, last_name, salary from s_emp where dept_id = 10SQL> SET VERIFY oFF | ON; can be closed and opened tips Confirm the display of OLD 1 and New 1. 3. Example of binding variable SQL statement (2) SQL> SELECT ID, LAST_NAME, SALARY from S_Emp Where title = '& job_title'; Enter Value for Job_Title: Stock Clerk SQL> SELECT ID, LAST_NAME, SALARY from S_Emp Where Hiredate> To_Date ('& start_hire_date', 'YYYY-MM-DD'); Enter Value for Start_Hire_Date: 2001-01-01 When the binding string and date type variable, the variable is added to add single quotes to the variables to query different field names to enter variables, do not add; etc. 4. Suplactical and examples of Accept SQL> Accept variable [DataType] [format] [prompt text] [hide] Description: Variable refers to variable name DataType refers to variable types, such as number, char, etc. Format refer to variable display format Prompt text can be customizable examples prompt text contents of the pop hide the hidden user input symbol using the ACCEPT: ACCEPT p_dname pROMPT 'Provide the department name:' ACCEPT p_salary NUMBER pROMPT 'Salary amount:' ACCEPT pswd CHAR pROMPT 'Password:' HIDEACCEPT low_date date format 'YYYY -MM-DD 'Prompt "Enter the Low Date Range (' YYYY-MM-DD '):" 4. Define Syntax and Example SQL> Define Variable = VALUE Description: Variable refers to variable name value means variable value definitions After the good value, the SQL statement of the binding variable is not prompted to use Define example: SQL> Define DName = Sales SQL> Define DName Define DName = "Sales" (char) sql> SELECT NAME from Dept Where Lower Name) = '& DNAME'; Name ------------------------- Sales Sales SQL> Undefine DName SQL> Define DName Symbol DName Is undefined 5. SQL * Plus Transfer parameters to saving * .sql file SQL> @ / path name / file name parameter name 1 [, parameter name 2, ....] SQL> START / path name / file name parameter name 1 [, Parameter name 2, ....]. Note: You can only get 9 & variables at a time, the variable name can only be from & 1, & 2 to & 9 variable name, do not add special end symbols if you want to save the & symbol in the Oracle database in the SQL * Plus, Modify SQL * Plus Environment Variable Definesql> Set Define OFF; 8. Overview Data Model and Database Design 1. System Development Stage: Strategy and AnalysisDesignBuild and DocumentTransitionProduction 2. Data Model Model of System In Client's Mindentity Model of Client's ModelTable Model of Entity ModelTables on Disk 3. Entity Relationship Model (ERM) Concept ERM (Entity Relationship Modeling) entity There is a target and event of specific information, for example: customer, order, etc. Attribute description entity, for example: name, phone number, etc. related to relationships between two entities, for example : Order and Products such as the agreement in the chart in the chart DASHED LINE Optional Parameter "May Be" Solid Line "Must Be" CROT's Foot "One or More" SINGLE LINE level parameter "One and only one" 4. Entity relationship model Example Each order must have one or several customers per customer may be an applicant for one or several orders 5. Type of physical relationship 1: 1 One-on-one, for example: taxi and driver M: 1 pairs, for example: passengers and aircraft 1: M, for example: employee and skills 6. The principle of correction entity is a single Value, there is no repetitive attributes must be contained in the entity. If there is a unique tag, there is no non-unique property that relies on another non-unique attribute 7. Precautions when defining the structure reduces data redundancy reduction of integrity constraints. Entity, relationship and attribute 8. Integrity Constraints Primary Key Main Keyword Unique Non NullFore_ Key Foreign Key Depending on another primary key, you may be a NullColumn field name that meets the defined type and length constraint constraints Conditions User-defined constraints, you must match workflow For example, a sales person's commission cannot exceed its basic salary Candidate Key candidate keyword multiple field names that can form candidate keywords, which are unique and non-NULL 9. Put the entity diagram to the relational database object to map the simple entity to the table in the database to map the field to the table in the database, indicate the type and comment on the unique keyword to the single mark to the database. The relationship is mapped to the foreign key in the database Other considerations: Design index, make the query faster, so that the information has different presentation faces, reducing the allocation of complex SQL statement program storage space redefined integrity constraints 10. Entity relationship The meaning of the symbol of the symbol PK Unique keyword FK Foreign Fk1, FK2 The two different foreign keys FK1, FK1, together with the FK1, together into a foreign key NN non-null field u unique Field U1, U1 two fields together form a unique field The second part Oracle SQL and SQL * Plus Yu Feng write 2002.10 Nine, Create Table 1. Oracle Common Field Type Oracle Common Field Types with Varchar2 (Size) Variable Length strings, must specify the length of the length of the fixed length, not specifying the length default value of 1 Number ( p, s) digital P is the total length of the number, S is the length of the decimal, the maximum number of cans, the maximum number of 38 bits. Date date and time type LOB long characters, the maximum 4G Clob is out of length Text string BLOB ultra-long binary string BFILE ultra-long binary string, saved in a file outside the database is read-only. Digital field type bits and their four result Original numerical value 1234567.89 Digital field type bits stored value Number 1234567.89number (8) 12345678Number (6) misfriend Number (9, 1) 1234567.9Number (9, 3) wrong Number (7, 2) Wrong Number (5, -2) 1234600Number (5, -4) 1230000number (*, 1) 1234567.9 2. When creating a table, you can add the default value to the field when you create a table, such as: Date Field DEFAULT SYSDATE, does not need to operate this field, you can get the action time when you insert and modify this field. When you create a table, you can add constraints to the field, for example: non-empty NOT NULL does not allow repeated UNIQUE Keyword Primary Key Press Condition Check Check (Condition) Foreign Key References Table Name (Field Name) 3. Example of the table Create Table DEPT EPTNO Number (2) Constraint PK_DEPT Primary Key, DName Varchar2 (14), Loc Varchar2 (13)); Create Table Region (ID Number (2) Not Null Primary Key, Postcode Number (6) Default '0' NOT NULL, Areaname Varchar2 (30) Default '' NOT NULL) Download Adobe Reader Download Adobe Reader DEFAULT 'NOT NULL 4. Naming rules and precautions when creating tables, named rules for the table name and field name: must be started with a letter, you can include symbol AZ, AZ, 0-9, _, $, # case, no distinguishing the retention word without SQL Be sure to use double quotes to enclose the character. English symbols related to entities or attributes have certain limitations: You can use the Chinese field name when you build a table, but it is best to create a small field when you create a table with the field name of English. In front, if an empty field is placed behind the table, if there is a unique keyword or the only constraint condition, the number of automatic builds automatically built a table is also limited, 254. 5. Naming Rules and Syntax Name Name Names Name Rules Name Rule Name If there is not specified when the table is built, the system naming rule is named rule of SYS_CN (N is a number) constrained name string with the name of the table and field name. rule 6. Precautions when using constraints cannot be used in system functions, such as the fields of sysdate and other tables can be compared to the fields within this table. If you want to handle the transaction, you will make a constraint check SQL> ALTER SESSION SET CONSTRAINTS DeferRed. 7. Examples of examples of entity to create tables S_DEPT Prerequisites: IdSQL> Create Table S_DEPT (ID Number (ID Number) containing the unique keyword (ID Number (7) constraint s_dept_id_pk primary key, name varchar2 (25) constraint s_dept_name_nn not NULL, region_id NUMBER (7) CONSTRAINT s_dept_region_id_fk REFERENCES region (id), CONSTRAINT s_dept_name_region_id_uk UNIQUE (name, region_id)); 8. table create more complex example of SQL> CREATE tABLE s_emp (id NUMBER (7) CONSTRAINT s_emp_id_pk PRIMARY KEY, last_name VARCHAR2 (25) CONSTRAINT s_emp_last_name_nn NOT NULL, first_name VARCHAR2 (25), userid VARCHAR2 (8) CONSTRAINT s_emp_userid_nn NOT NULL CONSTRAINT s_emp_userid_uk UNIQUE, start_date DATE DEFAULT SYSDATE, comments VARCHAR2 (25), manager_id NUMBER (7), title VARCHAR2 (25) , dept_id NUMBER (7) CONSTRAINT s_emp_dept_id_fk REFERENCES s_dept (id), salary NUMBER (11,2), commission_pct NUMBER (4,2) CONSTRAINT s_emp_commission_pct_ck CHECK (commission_pct IN (10,12.5,15,17.5,20))); 8. Example of SQL> Create Table Emp_41 AS SELECT ID, LAST_NAME, UserId, Start_Date from S_EMP Where DEPT_ID = 41; SQL> CREATE TABLE A AS SELECT * from b WHERE 1 = 2; As long as the structure is structured. 10. Precautions for using subquery measures can be connected to multiple tables and generate new tables with a collection function. Note that the selected fields must have legal field names and cannot be repeated. The table established by subquery, only non-empty NOT NULL constraints can inherit, and other constraints and default values are not inherited. According to need, you can use ALTER TABLE ADD CONSTRAINT ... to establish additional constraints, Such as Primary Key, etc. 10. Foreign Key Optional Parameters ON Delete Cascade can add optional parameters when creating a Foreign Key: On Delete Cascade It meaning if it deletes the content in the foreign key, the content related to the sub-table Will be deleted together. If there is no ON delete cascade parameter, there is a content in the subtray, the primary keyword record in the parent table cannot be deleted. 12. If there is an unsatisfactory record in the database table, the establishment constraint will not succeed. 13. An example of creating and deleting synonyms for the table SQL> CREATE SYNONYM D_SUM 2 for DEPT_SUM_VU; SQL> CREATE PUBLIC SYNONYM S_DEPT 2 for alice.s_dept; SQL> Drop Synonym S_DEPT; Ten, Oracle's Data Dictionary 1. What is the data dictionary of Oracle is one of the important components of the database, which is generated as the database is generated, and the database changes, it is reflected as all of the SYS users. Some tables and views. 2. The data dictionary exists: User Information Users' Permissions Information All Data Object Information Table Constraint Condition Statistics Analysis The views of the database cannot manually modify the information in the data dictionary. 3. Commonly used file data dictionary Dictionary to store all data tables, views, synonyms Dict_columns name and interpret the data dictionary field name and explain Dba_users Dba_tablespaces user database table space Dba_data_files Dba_free_space free table space Dba_rollback_segs rollback User_objects data objects User_constraints constraints system privileges User_tab_privs user role permissions User_sys_privs user's role User_role_privs user User_sequences serial number User_views view User_indexes index User_synonyms synonym Session_roles user table level permissions V $ session in real-time user case V $ sysstat real-time system statistics V $ sesstat real-time user statistics V $ sgastat Real-time SGA uses V $ locked_object real-time lock V $ controlfile control file v $ logfile log file v $ parameter parameter file 4. Data Dictionary Data Dictionary four categories User_User User All Database Objects All Database Objects All Database Objects DBA_ All Database Objects V $ Statistical Analysis Database View Tagua Opter_Monitor Permissions Non-DBA Users can also query V $ *view 5. Query the data dictionary SQL> select * from dictionary where instr (comments, 'index')> 0; SQL> select constraint_name, constraint_type, 2 search_condition, r_constraint_name 3 from user_constraints 4 where table_name = '& table_name'; XI. Control Data 1, INSERT (inserted into the data) SQL> INSERT INTO table name (field name 1, field name 2, ...) VALUES (value 1, value 2, ...); sql> INSERT INTO Name (Field Name 1, Field Name 2, ...) SELECT (Field Name 1, Field Name 2, ...) from other table name Where condition; can be used multiple input records multiple times with & tag variables Data method, generally used for data transfer SQL> INSERT / * APPEND * / INTO table name SQL> INSERT / * APPEND * / INTO table name. Additional table name where conditions; sql> commit; Note: Use INSERT / * APPEND * / method to generate an exclusive lock for Target_Tablename generates 6, if you run this command, there is a DML operation to target_tablename, which will line up behind it, and operate on the OLTP system It is not suitable. 2. Precautions for inserting a string type field: The field value of the string type must be enclosed in single quotes, for example: 'good day' If the field value contains single quotes' need to make a string conversion, we replace it The field value of the two single quotes' string types exceeds the length of the defined, it is best to perform the length check '' mark before insertion is NULL, the user indicates the current user date field of the field value to use the current database system time Sysdate, accurate to second string to convert to date-type functions to_date ('2001-08-01', 'YYYY-mm-DD') to_date () also has many date formats, you can see Oracle Doc. Year - Month - Day: Minute: Second Format YYYY-MM-DD HH24: MI: SS INSERT, the maximum operable string length is less than or equal to 4000 single-bytes, if you want to insert a longer string, consider the field with a CLOB type The method borrows the DBMS_LOB package from Oracle. 3, Update (the statement recorded in the data sheet) SET field name 1 = value 1, field name 2 = value 2, ... where condition; if the modified value n does not assign or define, The original record content is NULL, it is best to perform non-empty verification before modification; the value N exceeds the length of the defined, it is best to perform a length verification before insertion. New function, you can modify the result set example after the child query : SQL> Update (Select * from s_dept) set id = 50 where id = 60; 4, Delete (delete the statement recorded in the data table) SQL> Delete from table name where conditions; Note: Deleting records do not release the data block table space occupied in Oracle. It only uses the deleted data blocks into unused If you really want to delete all the records in a big table, you can use the TRUNCATE command, which can release the occupied data block table space SQL> TRUNCATE TABLE table name; this operation is not rollback. 5, SQL statement classification data definition language ( DDL): CREATE, ALTER, DROP (Create, Modifying Structure, Delete) (Other: Rename) Data Manipulation Language (DML): Insert, Delete, SELECT, UPDATE (Add, Delete, Check, Change) (Other: TRUNCATE) Data Control Language (DCL): Grant, Revoke (Authorized, Recycling), Set Role Transaction Control: Commit, Rollback, SavePoint (Other: Lock Table, SET Constraint (SET Transaction) Audit Control: Audit, NoAudit System Control: ALter SYSTEM session control: Alter session Other statement: Comment (Add Note), Explain Plan, Analyze, Validate, Call6, Oracle Transaction Control Commits Submit Transaction Rollback Retreat SavePoint Setup breakpoint, labeled the location in the transaction, the transaction ends, broken Point release transaction ends encountering commit or rollback encounters DDL and DCL statements to find errors, such as deadlock users exit SQL * Plus system restart or crash 6, things control and SavePoint command 7. Precautions for DML operations The above SQL statement pairs have a row-level lock. After confirmation, the commands that must be completed in the end of the event can be formally effective, otherwise it is not necessarily written to the database. Row level lock Failed to get released. If you want to withdraw these operations, you can use the command rollback. It is best to estimate the possible operation record range before running Insert, DELETE, and UPDATE statements. It should be limited to smaller (10,000 records) Inside,. Otherwise, Oracle handles this thing to use a big return. The program responds slowly and even lost the response. If the number of records excellent, these SQL statements can be done twice, and there is COMMIT confirmation Treatment. Too often frequent commit Twelve, change the table and constraints 1. Several cases of changing the table (1) When running, the name of the lock changing table will change the name SQL> RENAME table name 1 to table name 2; SQL> ALTER TABLE Name 1 Rename To Table name 2; Add a field SQL> ALTER TABLE table name after the table adds [default expr] [not null] [NOT NULL] [, Field Name 2 ...]; Modify the definition of the field Description SQL> ALter Table Table Name Modify Field Name 1 Field Name 1 Description [Default Expr] [NOT NULL] [, Field Name 2 ...]; When the record is empty, the field length can be reduced, and the change field type Modify the default value only on the modification. INSERT and UPDATE Record Modify NOT NULL constraints only work on the currently non-empty record 1. Several cases of changing the table (2) When running, you will add a field SQL> ALTER TABLE TA name DROP field name; add / disable / enable constraints for the fields in the table; ALTER TABLE | ENABLE CONSTRAINT Constraints Primary Key (Field Name 1 [, Field Name 2 ...]); SQL> ALTER TABLE Name Add | Disable | ENABLE CONSTRAINT Retal Unique (Field Name 1 [, " Name 2 ...]); Automatically establish an index when adding a single keyword or unique constraint: Prohibition of unique keywords and unique constraints still exist, can be used .1. Several cases of changing tables (3) Run Will add the constraints of the table in the table, SQL> ALTER TABLE Name DROP Constraints Constraints [Cascade]; will delete the constraint-related index. Cascade can delete the foreign key constraints. Put the table or Remove the memory area of the database SQL> ALTER TABLE table name cache; SQL> ALTER TABLE Name Nocache; change the table SQL> ALTER TABLE Table Name Move TableSpace Table Space Name; Note: If the table space is transferred with an index The index will be unavailable after the form transfer. We have to delete old indexes and build new indexes. 2. Delete the data delete table SQL> DROP TABLE table name; Cascade constraints]; empty table records SQL> TRUNCATE TABLE table name; press the record in the log list, use the rename method (application The program may have a short error, you can choose to execute in a busy time) Create a new table A1 as the original table A, rename the table A as a2 (if there is a more frequent DML operation on Table A, will table Plus the row-level lock, rename the process to cycle with a recursive manner until the DML operation ends, naming successfully). Rename the creation new table A1 to a history table A2 backup or delete 3. After deleting the problem, you should pay attention to the problem to delete the table, delete the index in the table. After the table is deleted, it will end after deleting the table based on its unresolved things, the views, Synynym, Stored Procedure created according to the table, still exists , But views, Synynym becomes illegal. You need to manually find them and delete. If you use Cascade Constraints, you will delete this constraint with it. 4. Syntax to the table address plus annotation SGM SQL> Comment on Table Table Name | Column Name. Field Name Is 'Text' Adjustment Example SQL> Comment On Table S_Emp IS 'Enployee Information'; SQL> Comment On Column S_EMP .last_name is ''; Database Dictionary About_Col_Comments User_col_Col_Comments User_col_COMMENTS All_TAB_COL_COMMENTS All_TAB_COMMENTS USER_TAB_COMMENTS Thirteen, create a serial number 1. Explanation of each parameter in the serial number SQL> Create sequence name [increment by n] [start with n] [{maxValue n | nomaxvalue}] [{minvalue n | nominvalue}] [{cycle | Nocycle}] [{cache n | nocache}] increment by n One growth N digits NOMaxValue Default value 10e 27NominValue Default 1nOcycle is not recycled, often used for unique keyword cache n in memory N-sequence, error The default N is 20 when the rollback is retired. The name of the serial number can generally adopt the name rules of "table name _ field name" 2. When inserting the automatic growth sequence number field, if you want to use the number of automatic growth from 1 to start the number of numbers from 1 to do a unique keyword, you should first create a serial number .create sequence serial number (preferably a table name serial number) Mark) Increment by 1 Start With 1 maxValue 99999 Nocycle Nocache; where the largest value is set by the length, such as the defined automatic growth, Number (6), the maximum value is 99999 INSERT statement Insert this field value: serial number Name .Nextval example: SQL> INSERT INTO S_DEPT (ID, Name, Region_ID) VALUES (S_DEPT_ID.NEXTVAL, 'FINANCE', 2); 1 Row Created. Only the name of the serial number is running. NEXTVAL sequence number name. Currval is only valid. 3. SQL> Select sequence_name, min_value, max_value, increment_by, las_number from user_sequences; where Last_Number refers to the next value of the serial number. 4. Change the serial number SQL> ALTER sequence sequence [increment by n] [{maxValue n | nomaxvalue}] [{minvalue n | nominvalue}] [{cycle | nocycle}] [{cache n | nocache}]; Note: Can't Changing its starting value If you want to change the start value of the sequence, first remove the serial number, then create a new one. 5. Delete serial number SQL> Drop sequence sequence; 6. NEXTVAL and CURRVAL from the serial number View of the view with a Distinct query with group by, haven, order by querying the default value in the query table of a child query 14. Creating a view 1. The concept and advantage view of the view is based on some query statements based on one or more tables and views, which is like displaying the window of the data, which is not stored in the data. The view can limit the database access, more Good control permissions make different expressions of non-dependent the same data from simple query statement data 2. Creating a view Syntax SQL> Create [or report] [force | NOFORCE] View View [(alias [, alias] ...)] As SubQuery [with check option] [WITH READ online "parameter explanation : The FORCE table does not exist, the view can still create a success with check option only if the view definition can be inserted or modified with read only does not allow DML operation. Oracle8i creates views available in Order By 3. Create a modified example of the view SQL> CREATE OR REPLACE VIEW salvu41 AS SELECT id, first_name FIRST, last_name LAST, salary MONTHLY_SALARY FROM s_emp WHERE dept_id = 41; SQL> CREATE VIEW dept_sum_vu (name, minsal, maxsal, avgsal) AS SELECT d .name, min (e.Salary), Max (E.SAlary), AVG (E.SAlary) from s_emp e, s_dept d where e.dept_id = d.id group by D.Name; Note: If you use SELECT * FROM The structure of the table_name created by Table_name After the structure of Table_Name changes the View to rebuild or compile to display the new field content. 4. Query the data dictionary SQL> SET long 1600; SQL> Select View_name, Text from User_views; Description: You can set the set long number according to the view text_length; the user_updatable_columns view can query the field that can be modified in the view 5. Simple and complex View comparative characteristics simple view complex views of the quantity of a plurality of functions? Is there a packet operation? No view-based DML operation? Have you? 6. Can you use a DML command on the view? However, there is no case where there is no situation below, you can delete the record in the view. Group function, group by, distinct does not have the above and below, you can modify the view Record. Field expression, for example: salary * 12 contains ROWNUM's VIEW does not have both cases, and in the view containing all non-empty fields in the base table, you can insert records in the view. 7. Use with Check in the view OPTION constraint SQL> create or replace view empvu41 as select * from s_emp where dept_id = 41 with check option constraint empvu41_ck; if you run command error ora-01402SQL> update empvu41 set dept_id = 42 where id = 16; reason: view empvu41 The records can only be configured with its constraints outside the view EMPVU41 after the record modification of the department number is 41. 8. Delete View SQL> DROP View View_name; Fifteen, create an index 1. The concept index index is a data object in the database. It uses B * tree, hash, bitmap structure directly quickly accesses the data. It is a separately stored two entity index creation, automatically calls and management 2. When do you create an index? Automatically create index: unique keyword, unique constraints manually need to create an index: When the SQL statement where WHERE is used, the field or field combination field is very large. NULL value is very large, return to the number of records 3. B * Structure of the tree index Each index consists of a field value and a pointer or RowID 4. Create an index syntax Create INDEX Index Name ON Name (Field 1, [Field 2, ...]) TableSpace Table Space Name; 5. Precautions for Creating Indexes Creating an index Create an index of a table with a single list is preferably not more than three (except special big sheets) It is best to use a single field index index. Combined with the analysis of the SQL statement, it is also possible to establish a combined index of multi-field and the index of the function-based index large tables will occupy a large storage space. Do not build a unique index, but should add a unique constraint. 6. Query the index method query data dictionary user_indexes and user_ind_columns Examples: SQL> SELECT ic.index_name, ic.column_name, 2 ic.column_position col_pos, ix.uniqueness 3 FROM user_indexes ix, user_ind_columns ic 4 WHERE ic.index_name = ix.index_name 5 AND ic.table_name = 'S_EMP'; Note: The characters stored in the data dictionary are capitalized. 7. Comparison fields that do not need to be indexed Where Where's comparison field tables are frequently modified Return records a lot of WHER later Contains IS NULL / IOT NULL / LIKE '% Input%' and other conditions 8. Reconstruction of the syntax of the index Alter Index Index name Rebuild TableSpace original table space name NOLOGGING; regular reconstruction index can reduce the indexed fragments, and use table space more effectively. 9. Delete Index SQL> DROP INDEX index name; SQL> ALTER TABLE Name DROP Constraint constraint name; 16 Object level permissions such as table space: For a specific Object operation, such as: SELECT, UPDATE, DELETE permission for a table, view, table, View the user information of the current database SQL> SELECT username, default_tablespace, temporary_tablespace from dba_users; to view online user information SQL> select count (*) "number", username "current username" from v $ session group by username; users view their default table space SQL> select username, default_tablespace From User_Users; 3. Create a new user SQL> create user username identified by password default tablespace tablespace_name temporary tablespace temp quota unlimited on tablespace_name quota 1k on system [quota 1k on other_tablespace_name ......]; to assign user permissions SQL> grant connect, resource to username; View Current user's permissions role SQL> Select * from user_role_privs; see current users' system permissions and table-level privileges SQL> SELECT * from user_sys_privs; sql> select * from user_tab_privs; 4. Common role and its authority Connect 8 PRIVS Connect Oracle Backup Enter Permissions View Role Detail System Permissions SQL> SELECT * from Role_Sys_Privs; 5, changing the old user can change the old password for the user, the default table space, temporary table space quota .SQL> alter user username identified by password default tablespace tablespace_name temporary tablespace temp quota unlimited on tablespace_name quota 1k on system [quota 1k on Other_tablespace_name .....]; Undo user's role or permissions SQL> Revoke role_name or priv_name from username; Precautions When the user's role DBA is revoked, the user Unlimited TableSpace system permission is revoked. Remember to give the resource role again to this user SQL> Grant resource to usrname; 6. Delete users If there is no data object SQL> DROP user username under the user; if the user has data object SQL> DROP USER Username cascade; Precautions If the user has a table with clob, blob field, you should first delete these tables Can be fully deleted with the Cascade option. 7, the concept and management role of the role is a combination of multiple related permissions. You can create a role in other users or roles, making the permissions management easier. 8, the privilege grammar and examples of the system: SQL> GRANT SYS_PRIV TO {user | role | public} [with admin option]; example: SQL> Grant Create session to Sue, Rich; SQL> Grant Create Table To Scott, MANAGER; Note: If you delete the system permission to use the WITH ADMIN OPTION, system privileges still exists. 9. Data Object Level Permissions Syntax and Example Syntax: SQL> Grant Object_Priv [(Column)] On Object To {User | Role | Public} [with grant option]; example: SQL> GRANT SELECT ON S_EMP to SUE, Rich; SQL> Grant Update (name, region_id) on s_dept to scott, manager; Note: If you are deleted with the use of Grant Option, the object privilege does not exist after the user is deleted through the intermediate user.
