Creation and management of tables one (study note)

Common data types

1. char (n) n=1 to2000 byte, saving a fixed-length string
2. VARCHAR2 (n) n=1 to 4000 bytes, variable length can be into numbers, letters, ASCII character set, 12C start maximum support 32767 byte length
3. Long is used to hold a length file up to 2G, and only one column in a table is long
4. Number (M,n) m= 1 to $, n=-84 to 127, total length m, decimal divided into N, integer part M-n
5. Date type, used to hold date data with no milliseconds
6. Timestamp, date type, used to hold date data containing milliseconds
7. CLOB 4G capacity, storing large amounts of text
8. BLOB 4G capacity, store pictures, movies, music, etc.
9. Bfile the ability to store binaries in operating system files outside the database
10. Integer number (m) can also be substituted with int
11. Decimal number (M,n) can also be substituted with float

Syntax for creating tables:

CREATE TABLE  [default defaults ][ default defaults ]....);

The operation to create the table belongs to the DDL (database definition language) naming requirement

1. Must start with a letter
2. Length is 1-30 characters
3. Table name by Word line (a-z,a-z), Number (0-9), _, underscore, $ USD, #组成, and name to make sense
4. Cannot use the same table name for the same user

Cannot be a keyword in Oracle such as Create, select,update

Example One

Create a member table, consisting of a member number (mid), name (name), age, Birthday (birthday), Introduction (Note) 5 fields

CREATE TABLEMember (Mid Number(5), nameVARCHAR2( -)DEFAULT 'Anonymous', Age Number(3), Birthday DATEDEFAULTsysdate, note CLOB);--View all tables under the current userSELECT *  fromtab

See if the structure of the member table is correct

DESC MEMBER;

Add several records to a table

INSERT  intoMEMBER (mid,name,age,birthday,note)VALUES(1,'test1', -, To_date ('1984-9-23','YYYY-MM-DD'),'bdqn1');INSERT  intoMEMBER (mid,name,age,birthday,note)VALUES(2,'test2', -, To_date ('1990-3-22','YYYY-MM-DD'),'bdqn2');INSERT  intoMEMBER (mid,name,age,birthday,note)VALUES(3,'test2', to, To_date ('1983-6-21','YYYY-MM-DD'),'Bdqn3');

To see if a success was added Select *  from member;

Example Two

Copying tables

CREATE table table name as subquery

Copy the EMP table to the Myemp table CREATE TABLE  as SELECT *  from  Select* from Myemp;

Example Three

Requires an employee table based on the structure of the EMP table, but does not require the contents of the EMP table and only duplicates the table structure, the best way is to write a condition in the query that will never be successful where 1=2

CREATE TABLE  as SELECT*fromWHERE1=2; -- Query Employee Table SELECT *  from employee;

querying table structure by DESC employee DESC employee;

Example Four

Renaming a table

--Grammar

RENAME old table name to new table name

 to Mldnuser;   -- querying all tables under the current user SELECT *  from tab

Example Five

Truncating the table is also called emptying the table

DELETE from table name takes a long time

TRUNCATE table name Efficiency high Oracle proprietary

--Truncate Mldnuser table is empty, first query

-- Clear Table TRUNCATE TABLE Mldnuser;

Example Six

Deletion of tables

--Grammar

DROP Table Name

--Delete myemp table

DROP TABLE myemp;

Creation and management of tables one (study note)