One, Data update operationDML syntax includes two main content: Query and update, update mainly include: Add data, modify data, delete data. These operations are inseparable from the query.
1. Add DataSyntax: INSERT into table name [(Field name 1, field Name 2, ...)] VALUES (data 1, data 2 ...) ；
Note:
1, for the string need to use "" enclosed
2, for the time can use To_date () to convert
3, the number is directly writtenExample: Inserting a data into the Myemp table insert into Myemp (empno, Job, Sal, HireDate, ename, Deptno, Mgr,comm) VALUES (6666, ' cleaners ', ' a ', ' to_date ' 1988-10-10 ', ' yyyy-mm-dd '), ' King II ', 40,7369,null);
2. Modify the DataSyntax: UPDATE table name SET Field 1 = content 1, Field 2 = Content 2, ... ... [Where update condition (s)] Example: An employee who is required to code an employee to 7369 is changed to 810, the Commission is changed to update myemp SET sal=810,comm=100 WHERE empno=7369; Example: Change the hire date of all employees employed in 81 to today, wage growth 20% UPDATE myemp SET hiredate=sysdate, sal=sal*1.2 WHERE hiredate between ' January-January -1081 ' and ' 3 January-December-1981 ';
3. Delete DataSyntax: delete from table name [WHERE Delete condition (s)]; Example: Remove employee information for employee number 7369 Delete from myemp WHERE empno=7369; Example: Delete information for several employees (in) Delete from Myemp WHERE empno in (7566,7788,7899); Second, the treatment of things
1, the concept of businessThings: a means of ensuring data integrity. Things have the acid principle that when one person updates the data, the other person cannot update it.
2, the concept of transaction lockTwo core commands for transactions: Commit things: Commit, rollback things: rollback; When a commit is actually used, it means that the update will be done normally. All update operations are required to be protected by the office. Transaction Lock: Each session has its own transaction processing. If two different sessions update the same data, the other session cannot be updated until one session is not updated. This process is called a transaction lock: Only one session can manipulate the data before committing or rolling back the update, enabling the isolation of things
3, the idea of transaction processingCase: If you suddenly need to update the contents of a field for all users, and if the amount of user data is large enough, then there is a problem: if you update directly, then the amount of data is large enough, the time required is very long, and in the updated time period, All users cannot log on to the system to do other operations, which is equivalent to a system crash. This approach is to take the time to change space if a change: the use of space to change the time: The customer is divided into active users and inactive users, priority to update the information of active users, or in the user's time to log on the user operation, this is a cycle of the completion of things, not all of a sudden completed. The so-called solution is based on time complexity or space complexity, or take time to change space, or take space for time, the specific practice also need to combine the specific situation of analysis, but this is a most basic idea. Third, data pseudo-column
1, line numberThe line number is generated automatically, but he is dynamic and does not specify certain rows, which are generated automatically based on the data you query. Example: SELECT ROWNUM, empno, ename, job from Emp;rownum role: 1, get the first row of data 2, obtain the data of the previous N rows Note: ROWNUM cannot directly obtain the following lines or the middle part of the data 1, The first row of data is generally used to obtain the data structure of the table, generally in the actual operation, we do not use the query all the statements to operate: SELECT * from the table name. SELECT * from EMP WHERE rownum=1;
2, the implementation of the page up and down displayCurrentPage, which represents the current page; Linesize, which represents the rows of data displayed per page; syntax: Select *from (select ROWNUM rn, column ...). From table name WHERE ROWNUM <= currentpage*linesize) Temp
WHERE temp.rn> (currentPage-1) *linesize;
3, Row IDThe so-called rowID refers to the physical address provided for each line of data. Take the data "aaar3qaaeaaaachaaa" for example, rowID composition: Data Object number: aaar3q data File Number: AAE data saved block number: Aaaach Data saved line number: AAA
Face Test: There are a number of fully duplicated data in the table that require the deletion of duplicate data (only the last one left); Analysis: The data column information in the table is almost identical, so if you delete it by the existing field, the final result will be erased. Now even if the data is duplicated, there is a rowid in Oracle, and its physical save address is not the same. In the program will be designed to accumulate the operation, so theoretically, the first to save the data rowid content should be minimal. If you want to confirm the minimum, use the Min () function to complete. Now that the data in the Mydept table is duplicated, the smallest rowid (the earliest rowid) can be grouped and grouped by repeating content. First step: Find the smallest ROWID data select Deptno, Dname, loc, MIN (ROWID) from Mydeptgroup by Deptno, Dname, loc; Step Two: Delete the data that ROWID does not have in this delete from the mydept where ROWID not in (SELECT MIN (ROWID) from Mydept GROUP by Deptno, Dname, LOC);
Iv. creation and management of tables
1. Common data Types1. String: Use VARCHAR2 description (other database uses varchar), use this type in 200 words, for example: Name, address, postal code, telephone, gender; 2, Number: Use number in Oracle to describe numbers, if you describe decimals using " Number (M,n) ", where n bits are decimal places and m-n are integer digits, but the database also takes into account the habits of the program staff;-Integers, using int;-decimals, using float. 3, Date: Using date is the most common practice, but in Oracle date contains a date time, but the other database inside date may be just a date, DateTime is to represent a datetime; 4, big This article data: Use CLOB description, Up to 4G of text information can be maintained; 5. Large Object data: Use BLOBs to keep pictures, music, movies, text, up to 4G.
VARCHAR2, number, DATE, CLOB are used in the actual development.
2, the creation of the tableSyntax: CREATE table table name (column name type [default defaults], column name type [default defaults], column name type [default defaults], .... Column name type [default defaults]); Example: Create a table named member that has four descriptions: Mid, name, birthday, Notecreate table member (mid number, name VARCHAR2 () DEFAULT ' anonymous ', Birethday DATE DEFAULT sysdate, note CLOB); Insert data into table: INSERT into member (Mid,name,birethday,note) VALUES (1, ' Zhang San ', To_date (' 1890-10-10 ', ' yyyy-mm-dd '), ' network data line ');
3, the basic operation of the table: Table copy, table deletion, flash back technology
Copy table: Create TABLE table name as subquery; You can copy the queried child table as a table to a new table Save example: Save all 30-door employee information in the EMP30 table CREATE table Emp30 as SELECT * from EM P WHERE deptno=30; Delete table: Under normal circumstances, once the data table has been deleted, it cannot be restored, please use caution: drop table name; example: Delete data table drop tables dept; Flashback technology: Flashback's functionality gives the user a chance to regret, but now if the user wants to operate the Recycle Bin, then the user has the ability to view, recover, condition, and completely delete several operations. 1. After deleting the data sheet, check the Recycle Bin: show Recycblebin; 2. Recover deleted tables Flashback table name to before drop; Completely delete the table operation: 1, delete the person table completely drop table person purge;2, empty Recycle Bin PURGE recyclebin;
4. Restore the table scriptThis script can be quickly restored to the database. The script contains a few things like: 1, delete the original data table, 2, re-create a new data table, 3, create test data, 4, commit. Code implementation:--delete Datasheet drop table member purge;--case Recycle Bin PURGE recyclebin;--CREATE TABLE member (mid number, name VARCHAR2 (20 );--test data insert into member (Mid, name) values (1, ' Zhang San '); INSERT into member (Mid, name) values (2, ' John Doe ');---Commit things commit; V. Creation and management of constraints
1. Conditions of the dataIn order to ensure the integrity of data in the data table, the data needs to meet a number of conditions before it can operate, the constraint is a double-edged sword, the constraint does guarantee the legitimacy of the data before saving, if there are too many constraints in a table, then the speed of the update will be slow, in the development process, Some validation actions (constraints) can be done in the program: Six constraints: Data type non-null constraint UNIQUE constraint PRIMARY KEY constraint CHECK constraint foreign KEY constraints
2, non-null constraintsNon-null constraint: The contents of a field in a table are not allowed to be empty, and if a non-null constraint is used, only after a column, use the not NULL declaration. When creating the table, add a NOT NULL declaration after the column create table member (ID number, name VARCHAR2 (), default ' anonymous ' NOT NULL, Sex VARCHAR2 (5), Bithday date default sysdate); ---The field that represents name is not allowed to be empty
3. Unique constraint UNIQUE constraint: In a column where the content can not be duplicated, such as identity cards, mailboxes, etc., setting a unique constraint only needs to add the unique field after the column to create table member ( id number, name varchar2 () Default ' anonymous ' Not null, email VARCHAR2 (5) unique, & Nbsp; bithday date default sysdate); ---indicates that the contents of the email field must be unique and no duplicate information can appear Note: In the event of a Uniqueness constraint error, there is no direct question as to which column of data is present as a non-null constraint, and in practical development, you can use shorthand to prompt for error messages for easy modification: When creating constraints, use the shorthand constraint name _ field Display in the form: CONSTRAINT uk_email UNIQUE (email) create Table member ( id Number, name varchar2 () Default ' anonymous ' not null, email& nbsp VARCHAR2 (5) , bithday Date default sysdate, constraint uk_email UNIQUE (email)); &NBsp ---Use uk_email shorthand, you can make simple hints at the wrong time, you can quickly catch the problem column Note 2:null is not within the scope of the unique constraint
4. Primary KEY constraint (primary key, PK)PRIMARY KEY constraint = non-null constraint + UNIQUE constraint; The column that is set as the primary key, neither empty nor duplicate data definition primary KEY constraints: CREATE TABLE member (ID number, name VARCHAR2 () default ' anonymous ' Not NULL, CONSTRAINT pk_id primary key (ID)),----implement PRIMARY KEY constraints, use shorthand
5, check the constraints: checking, CKIn the data row livings set some filter conditions, when the filter conditions can be done to operate such as age settings: Age range 0-120 set CHECK constraints: CREATE TABLE member (ID number, name VARCHAR2 () default ' nameless n ' not NULL, age number (3), CONSTRAINT ck_age check (age between 0 and));---Implementation of conditional constraints on columns
6. Foreign KEY constraint (foreign, FK)Role: When two tables are associated, such as when the parent table is associated with a Word table, you must set the relationship of the associated field, otherwise it will result in data that does not exist in the parent table, and can also be inserted in the child table by the CREATE table member (mid number, name Varcha R2, constraint Pk_mid primary key (mid)----set the primary key) CREATE TABLE book (bid number, title VARCHAR2 (20), Mid number constraint Fk_mid foreign key (mid) References member (mid) on DELETE CASCADE----Set the mid in book as the foreign key, and the data From mid in member table)----ON delete Cascad is to set cascade Delete
Note: Restrictions on foreign keys
1, before deleting the parent table, you must delete her corresponding all the child table before you can delete
forces the deletion of the parent table and does not care if the child table exists: DROP table name CASCADE constraint---Try not to use forced deletion, try to delete the child table first, and then delete the parent table
2, if you want to be the parent table column of the foreign key of the child table, then this column must be set to a unique constraint or a PRIMARY KEY constraint
3, if the main table in a row of data has the corresponding child table data, you need to delete the data in the child table before you can delete the data in the parent table, after the parent table is deleted, the data of the child table is also deleted
4, set as a foreign key field, in the main table must be a primary KEY or a unique constraint. Cascade Delete: On delete cascad: When you delete data from the parent table, the data cascade update associated with the child table is automatically deleted: ON delete set NULL the foreign key column of the corresponding child table's data is now set to NULL when the parent table data is deleted
vii. Modification of Constraints 100% prohibit modificationConstraints must be created at the time the table is created, and cannot be constrained for modification. Vi. Design of the database
1. The first paradigm:The contents of each column in the data table are no longer available; Note: 1, for the date description can not be divided into: year, month, Day 2, the name of the field can not be divided into Fistname, LastName
1. Second paradigm (many-to-many relationships)There is no non-critical field in the data table that has a partial function dependency on any of the candidate key fields.
2. The third paradigm: (one-to-many relationships) primary considerationThere is no non-critical field in the data table to pass function dependency on any of the candidate key fields. Vii. other Parts
I. Backup of the database1. Export and import of data, export and import of data in user-based data table: you need to prepare a data backup directory, such as: Enter the MD folder name in CMD to create a folder to enter the directory, as a command line operation: Enter the EXP command, export the data Enter the directory and operate as a command line: Enter the IMP command, import data 2, cold backup of the database, archive backup, refer to the database needs to shut down the service, all the transactions need to commit. Backup files: 1, Control files: Control the entire Oracle instance information, you can use the ' v$controlfile ' data dictionary to find 2, redo the log file: 3, data file through the ' V$logfile ' data dictionary: found by ' v$datefile ' data dictionary 4 , core profile (Pfile): Using ' Showparamete Pflie ' to find a database backup operation requires an administrator: 1. Database administrator Login: CONN sys/change_on_install as Sysdba;2, Find the path to several files: SELECT * from V$controlfile;select * from V$logfile;select * from V$datefile; SHOW PARAMETER Pfile; Record the path of the above file 3, close the Oracle service: Shutdown immediate;4, copy all backup files 5, restart the service startup;
2. Other objects of the databaseSynonym (exclusive function of Oracle) synonym definition: If the SYS user, go to use SELECT * from EMP, the syntax to query the EMP table, he will report the table or the view does not exist, because the EMP table belongs to the Scott user, Therefore, if you need to access the table under other users, you should add scott.emp, which is called synonyms. Syntax: Create "public" synonym synonym name for mode. Table Name Example: Map scott.emp to semp;create synonym semp for scott.emp; Note: When creating synonyms, if they are not created as public synonyms, the synonym can only be used under the created user and not under other users. Create a common synonym: Create public synonym semp for scott.emp; Login with account password: CONN sys/change_on_install as SYSDBA; Delete synonym: drop synonym semp;
Ii. Index Source and functionWhen querying data in a database, we can open the tracker to analyze the analysis process of the database open tracker: Set autotrace on; Need to be under the SYS user to view the results according to the view, analysis to: During the execution of data, such as simple select * from emp where sal>1500; The execution of the statement is to perform a full scan of the table, if the data volume is large enough, if the second half of the data is not satisfied with the condition, but the statement will be the subsequent data query, which will cause the waste of resources will also pull down performance, so we take the first sort of way to sort the data, This prevents subsequent resources from being wasted, but cannot use order by, because the order by is executed at the end by sorting the data that has already been filtered; the binary number is sorted by selecting a data as the root node, with data that is greater than or equal to the number of nodes on the right , the data that is smaller than the node is placed on the left sub-number. (right big left small) uses the index to realize the classification search to the data, improves the performance to save the resources.
Third, create the index1. To create a lasso, you must set a specified field: Example: Create an index for the Sal field of the Scott.emp table creation index emp_sal_ind on scott.emp (SAL); 2, then the SELECT * from scott.emp where sal>1500; query, the way to query is to take an indexed way to query and based on the Rowid:table ACCESS by index Rowi D/index RANGE SCAN
3. DefectsIndex limitations: Indexes can improve the speed and performance of queries, but there are also many limitations, such as: if the data is constantly changing, then the index is not working. The maintenance of the tree takes a lot of time, if you do not want to repeat the maintenance of the tree, then you need to ensure that the data can not be changed and unique, so by default on the primary key constraints on the automatic addition of an index in the real process: we want to ensure that the user's response speed, no delay, and can withstand the user A lot of updates, then we can deal with: Sacrifice real-time: divided into two databases, a database to save the user needs to query the data, a database to save the user's updated operations, in the early morning 2-3, and then update the database data to the query library.
Iv. User Management (user's Maintenance and authority Division) User login: CONN sys/change_on_install as sysdba; Create a new user Dog/wangwang (username/password): Note The password begins with the English alphabet create user dog identified by wangwang; & nbsp Grant permissions: grant permissions 1, permissions 2 ... to username example:grant create session to Dog; problem came, but a brand-new user is not any permissions, need this user can be normal use, it is necessary to each permission to use a separate given statement, this is too troublesome, so introduced the concept of roles, different roles have a lot of different permissions , you do not need to give permissions alone, just give the user role. Connect: Connection role, connect role contains RESOURCE permissions RESOURCE: Access operation (Access table and tablespace) roles give: GRANT CONNECT, resource to dog; users have to sign in again after they get new permissions to get new permissions. The login operation is not simply to verify the user name and password, but to obtain and verify the permissions. Modify user's password: alter user user name identified by password Let user's password expire: & nbsp alter user user name password EXPIRE;---using this instruction, the user will be prompted to enter a new password to change the password. Lock User:alter user username account lock; unlock user:alter user user name account UNLOCK; In addition to system permissions, you need permission to use objects: You can access data tables under one object: Four permissions:insert/update/delete/select Example: SELECT of the table to be scott.emp , insert permissions give the dog user: grant SELECT, insert on scott.emp to dog; permission recycle revoke select, insert on Scott.emp from dog; Delete user action:
DROP User Username CASCADE
3. SequenceI. Auto-grow column sequence syntax: create sequence sequence name "MAXVALUE Max | Nomaxvalue "Default maximum value:1.0000e+28 " MINVALUE min value | Nominvalue "Default minimum value is 1 incrementby step" default step is 1 "Start with start value" "cycle| Nocycle "Default is N, loop Cache Count | NOCACHE "Default is 20? This seems to be a change, the follow-up sequence belongs to the creation process of database object, belongs to the classification category of DDL, for the sequence, it will be saved in the data dictionary after creation example:create sequence myseq; Nextval: Gets the next content of the sequence, each time the value of the call sequence grows Currval: Represents the current contents of the acquisition sequence, and each call sequence does not grow. is equivalent to displaying the current sequence If the nextval is not used for the first time, then using Currval cannot display the current data, as if there is no value two, Defining special sequences Creating a table: Create TABLE Mytab ( id number, name & NBSP;VARCHAR2 (, constraint pk_id primary key (ID)); After creating the table, you can automatically grow the ID based on the sequence: INSERT INTO mytab ( ID, name) VALUES (myseq.nextval, ' hello '); caching: The role of caching is to improve performance, first prepare the data, then you can directly use, but in the process of use, there will be a loss of the number of cases, the problem can not be resolved. select Myseq.nextval from dual; ---Find the sequence from the virtual table select Sequence_name, Cache_size, last_number from user_sequences;---Find related information in the data dictionary drop sequence myseq;----Delete the sequence create sequence MYSEQ;---Create the sequence IncrementBy 2;---Set the step to 2START with;---Change the start value of the sequence cycle;---set cycle Note: The value of Last_number is equal to the number of steps * Cache Cache value must be less than cycle value: The number of caches must be less than the value of the loop
4, the definition and use of viewsFirst, the concept of view creation: Using the view can implement complex SQL statement encapsulation Operation view is the definition category of DDL, the syntax is as follows: Create "or replace" view name as subquery example:---Permissions to create views for Scott users: CONN sys/ Change_on_install as SYSDBA; GRANT CREATE VIEW to Scott; CONN Scott/tiger; ---CREATE VIEW: encapsulates the personnel information for 10 in the EMP table into a view CREATE view MyView as SELECT * from EMP where deptno=10; Views can be queried directly like a normal data table: SELECT * from MyView; the result of a---query is the same as the result of a previous subquery, and the view is rarely deleted with the drop statement because there is a time interval between deletion and creation, which can cause problems. So if there's a problem with the view, we'll update it in a replacement way: Create or Replace View MyView as SELECT * from EMP where deptno=10;---if the view does not exist, creates, if present, Change update
Second, the update operation of the viewThe view is just temporary data that contains the query statement, not real, but by default the view is a create or replace view MyView as SELECT * from EMP where deptno=10; Update View's department number : Update MyView set deptno=30 where empno=7369; Note: 1. The protection creation condition is not changed: the data updated in the view is also updated at the same time as the original EMP data: This is not a reasonable operation, Therefore, in order to prevent changes to the view resulting in the update operation of the data, use the clause with CHECK option when creating the view to prevent the view creation condition from being changed. Create or Replace view MyView as SELECT * from EMP where deptno=10 with CHECK option; 2, the view can not modify the other than the creation of the conditions of the field, when creating the view is a mapping of the data, then you should essentially create a read-only view to protect the data is not modified
Create or Replace view MyView as SELECT * from EMP where deptno=10 with Read only
Data collection operations in Oracle
