Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Example Analysis of DDL Operation in SQL

2025-01-16 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)05/31 Report--

This article is about sample analysis of DDL operations in SQL. Xiaobian thinks it is quite practical, so share it with everyone for reference. Let's follow Xiaobian and have a look.

1. Database objects

table

View: A select statement stored in a data dictionary

sequence

synonyms

index

2, reserved words

select * from v$reserved_words where reserved='Y';

3. Create a table

Premise:

CREATE TABLE permission, space, a table with up to 1000 columns

Temporary Table Level GLOBAL TEMPORARY:

Transaction Level ON COMMIT DELETE ROWS

Session Level ON COMMIT PRESERVE ROWS

syntax

CREATE GLOBAL TEMPORARY TABLE ....

(...

)

ON COMMIT PRESERVE ROWS;

Grammar:

create table dept01

( deptno number(2),

dname varchar2(14)

);

create table test (col1 number,col2 date default sysdate);

Data types: characters, numbers, dates, large objects, etc.

data dictionary

user_tables

user_objects: Name, ID and type of object

user_catalog: View user-owned tables, views, synonyms, sequences

Create a new table using CAST

The not null constraint on the list in the source table is also applied to the new table, but the primary key, unique, foreignkey constraints, and implicit not null constraints are not inherited;

create table test as select * from emp where 1=2;

4. Amendment table

add add an alternate table dept30 add (job varchar2(9));

modify alter table dept30 modify (NAME varchar2(15));

drop drop a column

alter table dept30 drop column job;

Add cascade constraint alter table... drop column ... cascade constraint

rename rename column alter table dept30 rename column empno to id;

set unused flag unavailable column

alter table xxx set unused (col1,col2);

Used in conjunction with drop columns, marking unavailable at peak times and dropping columns at low times alter table xxx drop unused columns;

cannot restore

select * from user_unused_col_tabs;

5. Renaming tables

rename dept30 to department30;

6. Truncation table

truncate table department;

Delete only data, retain table structure

Difference between truncate and delete:

a) delete is a DML statement that generates a lot of undo data for rollback, which is slow.

Delete does not lower the high water mark. delete can delete part of the data in a table.

b) truncate is a DDL statement that produces almost no undo data and cannot be rolled back. Fast.

Truncate lowers the high water mark. truncate deletes all data from the table.

7. Delete table

drop table dept30;

unable to rollback

drop table purge //deleted tables will not go to the recycle bin

drop table dept02 cascade constraint;

8. Add comments

Table-level comment on table emp is 'Employee Information'; user_tab_comments Data dictionary

column level comment on column emp.deptno is 'Department Number'; user_col_comments Data dictionary

Thank you for reading! About "SQL DDL operation sample analysis" This article is shared here, I hope the above content can have some help for everyone, so that we can learn more knowledge, if you think the article is good, you can share it to let more people see it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report