Create and manage tables 2 (learning notes), create and manage learning notes

Create and manage tables 2 (learning notes), create and manage learning notes

Modify the table structure

In DDL definition, there are three main syntaxes for database operations

CREATE a TABLE and add data -- delete the member table drop table member purge; -- CREATE a member table create table member (mid NUMBER (5), name VARCHAR2 (50) DEFAULT 'Anonymous ');

-- View all the tables under the current user SELECT * FROM tab; -- check whether the structure of the member table is correct desc member; -- add several records to the table insert into member (mid, NAME) VALUES (1, 'zhang san'); insert into member (mid, NAME) VALUES (2, 'Li si'); insert into member (mid, NAME) VALUES (3, 'wang 5'); -- query the member table SELECT * from member;

Example 1,

Add three fields to the member table

Alter table member add (age NUMBER (3); -- ADD the age field alter table member add (sex VARCHAR2 (10) DEFAULT 'male '); -- ADD the gender field alter table member add (phote VARCHAR2 (100) DEFAULT 'nophote.jpg '); -- ADD the image -- view the TABLE structure desc member;
-- Added successfully

Modify Table Fields

ALTER TABLETable Name MODIFY (Field nameField Type DEFAULTDefault Value );

Example 2,

Change the length of the name field to 30, and the default value of the sex field to female.

Alter table member modify (NAME VARCHAR2 (30); -- change the field length from 50 to 30 alter table member modify (sex VARCHAR2 (3) DEFAULT 'femal '); -- set the gender field to 3 with the default value female -- view the table structure desc member;

--Delete fields in a table

Alter table Name drop column name;

Example 3,

-- Delete the phote and age fields in the member table

Alter table member drop column phote; alter table member drop column sex; -- view the TABLE structure desc member;

Set some fields in the table to useless state

Alter table name set unused (column name)

Alter table name set unused column name

Example 4,

Set age in member to useless state

Alter table member set unused column age; -- view the structure of the table desc member; -- query the member table select * from member; -- if it is SET to useless, it is not displayed.

Delete useless columns in a table

ALTER TABLETable Name drop unused columns;

--Delete useless columns in the member Column

ALTER TABLE MEMBER DROP UNUSED COLUMNS;

Add Comment

Syntax:

Comm on table name | column table name. COLUMN name IS 'comment content ';

View the user_tab_comments data dictionary SELECT * FROM User_Tab_Comments WHERE table_name = 'member'; -- the default comments content is NULL.

Add comments to the member table

Comment on table member is 'this IS the member TABLE to learn '; --- view the COMMENT of the member table select * FROM User_Tab_Comments WHERE table_name = 'Member ';

View comments of a column

SELECT * FROM user_col_comments WHERE table_name='MEMBER';

Add comments for mid, name, age birthday

Comment on column member. mid IS 'Member ID of the attendee '; comment on column member. name IS 'Member name'; comment on column member. age IS 'Membership age'; comment on column member. birthday IS 'Member's birthday'; -- view the comment in the column SELECT * FROM user_col_comments WHERE table_name = 'member ';