Create and manage an Oracle note (9) Table

Oracle notes (9) Table creation and management links: Oracle notes (1) Oracle introduction and installation tips (2) SQLPlus command http://www.bkjia.com/database/201209/154051.html#oraclenotes (3) Scott user's table structure workshop (4) simple query, limited query, data sort (5) single-row function http://www.bkjia.com/database/201209/154056.html#oraclenotes (6) Multi-Table query http://www. B Kjia.com/database/201209/154060.html?oracle=note (7) data update, transaction processing, data pseudo column partition (8) complex query and summary http://www.bkjia.com/database/201209/154063.html for the database, in fact, each table represents a database object, database objects refer to all DDL-defined operations, such as tables, views, indexes, sequences, and constraints, which all belong to object operations. Therefore, table creation means object creation, object operations are divided into the following three types of syntax: CREATE object name ...; Delete object: DROP object name ...; Modify Object: ALTER object name ...;
1. Common data fields www.2cto.com each data table is actually composed of several fields, and each field has its corresponding data type. In Oracle, common data types include the following types: bytes data type keyword description 1 string VARCHAR2 (n) Where n represents the maximum length that a string can save, basically, the content of about 200 pieces is saved. 2 integer NUMBER (n) indicates an integer of up to n digits. Sometimes, you can use INT instead of 3.
Decimal NUMBER (n, m) where m is decimal, n-m is the integer, sometimes you can use FLOAT instead of 4 www.2cto.com DATE to store the DATE-time 5 big text CLOB can store massive text (4 GB ), for example, six objects, BLOB, stored for Three Kingdoms performing arts and A Dream of Red Mansions, are stored for binary data, such as movies, MP3, pictures, and texts.
Generally, VARCHAR2 (), NUMBER, DATE, and CLOB are the most widely used in development, while BLOB fields are generally used less. First, BLOB can store 4 GB of binary data, however, the database is too large, and the reading is inconvenient. 2. If you want to create a table, you can use the following Operation Syntax: create table Name (Field 1 Data Type [DEFAULT value], Field 2 Data Type [DEFAULT value],… ..., Field n data type [DEFAULT value]); www.2cto.com
Create a member table (member) with the following information: name, age, birthday, and personal profile. Create table member (name VARCHAR2 (50) DEFAULT 'Anonymous ', age NUMBER (3), birthday date default sysdate, content CLOB); after the TABLE is created successfully, insert into member (name, age, birthday, content) VALUES ('zhang san', 20, TO_DATE ('2017-08-12 ', 'yyyy-mm-dd'), 'Nice loan'); insert into member (age, content) VALUES (20, 'Nice loan'); Be sure to remember again, table creation is the creation of database objects, so the CREATE syntax is used.
3. The copy operation of a TABLE has been learned before. The complete operation syntax is AS follows: create table copy TABLE name AS subquery. Example: copy a TABLE named create table emp20 as select * FROM emp WHERE deptno = 20. Example: copy the TABLE structure of the emp TABLE, no data-simply write a condition that will never be met. Create table empnull as select * FROM emp WHERE 1 = 2; however, the above syntax is only supported by the Oracle database. There are some differences in the syntax of other databases.
4. rename a table in the Oracle database. All data is saved in the data dictionary. For example, you have used the following query before: SELECT * FROM tab; the above is a data dictionary. in Oracle, three types of data dictionaries are provided. The most common types are dba _ and user _. Therefore, we can query a user_tables data dictionary as follows: SELECT * FROM user_tables; that is to say, all data in Oracle is saved by file, so all the content will be registered in the data dictionary. In this case, the so-called modification of the table name is actually equivalent TO modifying a piece of data for Oracle. The method for modifying the table name is as follows: RENAME the old table name TO the new table name; example: RENAME the member table TO the person table rename member TO person. However, this operation is unique TO Oracle databases, so you can understand it without further understanding.
5. Cut-off table www.2cto.com previously explained an operation to DELETE table data, which uses the DELETE operation. However, this operation has its own characteristic: transactions can be rolled back. That is to say, the deleted data resources will not be released immediately. If you want to completely release all the resources occupied by a table (such as tablespaces and indexes) you can use the truncation TABLE syntax. The syntax is as follows: truncate table name; example: truncate table person in the person TABLE. However, this syntax is only applicable to Oracle, so you only need to understand it. 6. Deleting a TABLE refers to deleting a database object. to delete a TABLE, use the DROP statement. The syntax for deleting a TABLE is as follows: drop table name. Example: delete the person table drop table person;
VII. New Features of Oracle 10g: Flash back technology after Oracle 10g, in order to prevent users from accidentally deleting tables, it provides the recycle bin function, the deleted table is saved in a recycle bin by default, and the table can be restored through the recycle bin. Therefore, this technology is called FLASHBACK. Example: view show recyclebin in the recycle bin. You can find that all the deleted tables are saved in the recycle bin. You can use the following syntax TO restore the TABLE: flashback table name to before drop; example: Restore the myemp table flashback table myemp to before drop. Of course, you can also directly delete some data tables in the recycle bin. The syntax is as follows: purge table name. Example: delete the "person" TABLE in the recycle bin. Example: Clear the "purge recyclebin" TABLE in the recycle bin. If you want to delete a TABLE To the recycle bin, you can add the PURGE when deleting it.
Drop table myemp PURGE; www.2cto.com is only available 10 Gb after Oracle, and 11g also exists in Oracle. Q: Now there is a tab table in the recycle bin, and a tab table is created. Can it be recovered from the recycle bin? A: it cannot be recovered. 8. Modify the table structure. If a created data table does not meet the requirements of later use, you can modify the table, the TABLE modification operation is actually a database object modification operation, which is completed using the ALTER command. For example, there is now a TABLE: create table member (mid NUMBER, name VARCHAR2 (50 ));
Now you want to ADD fields to the TABLE, so you can use the following syntax: alter table name ADD (column Name Data Type [DEFAULT value], column Name Data Type [DEFAULT value],...); Example: ADD the field alter table member ADD (age NUMBER (3), birthday date default sysdate) to the member TABLE. If the added data column has no DEFAULT value, the content of all columns with existing data is null. If the DEFAULT value is specified for the added column, all the existing data columns are set by DEFAULT.
You can also MODIFY the existing TABLE structure. The syntax is as follows: alter table name MODIFY (column Name Data Type [DEFAULT value], column Name Data Type [DEFAULT value],…); Example: Define the DEFAULT value of the name field as anonymous alter table member MODIFY (name VARCHAR2 (100) DEFAULT 'Anonymous '). Although in the SQL syntax and in the Oracle database, the table structure modification operation is provided, but this operation is not used. For large databases, the database with the highest performance in the world is IBM DB2, however, IBM DB2 has a platform limitation problem. Therefore, if it is a cross-platform database, it is the highest performance of the Oracle database.
In the IBM DB2 database, the table structure cannot be modified after the table is created. Therefore, do not modify the table structure as much as possible during development. 9. Question www.2cto.com now requires an nation table with the name field, which stores four records: China, the United States, Brazil, and the Netherlands. The following operations must be performed through queries: china United States China Brazil China Netherlands United States Brazil United States China United States Netherlands
The rest are the same. Now we need to create a new table and complete the query operation. The main purpose of this question is not to compile the query, but to standardize the format of the database creation script. If you encounter similar requirements in the future, you must first compile a database creation script, the requirements for this script are as follows: 1. The file name suffix of this file must be "*. SQL "; 2. Delete the corresponding data TABLE first; 3. Write the statement for creating the TABLE; 4. Add test data; 5. Submit the transaction; -- 1. Delete the TABLE DROP TABLE nation PURGE; -- 2. create table nation (name VARCHAR2 (50); www.2cto.com -- 3. Test Data insert into nation (name) VALUES ('China '); insert into nation (name) VALUES ('u.s. '); insert into nation (name) VALUES ('brazilian'); insert into nation (name) VALUES ('Dutch '); -- 4 , Transaction COMMIT; if the program is to be completed by Cartesian product, it belongs to the table's own Association. SELECT n1.name, n2.nameFROM nation n1, nation n2WHERE n1.name <> n2.name; if there are some complicated queries in the interview, we recommend that you write all the scripts.