In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
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.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.