常用sql语句

最新推荐文章于 2025-07-19 15:12:22 发布

原创最新推荐文章于 2025-07-19 15:12:22 发布 · 271 阅读

0 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

2 篇文章

订阅专栏

本文档提供了一系列Oracle SQL语句实例，包括连接数据库、创建表、修改表结构、权限管理等操作。通过这些实例，读者可以了解到如何进行基本的数据表管理和用户管理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQLPLUS /nolog
conn pan/592595

spool c:/day2.txt app
.
.
.
spool off

set wrap off
select *from user_tables;
desc Student
desc user_tables;
create table Student
(
…..
);
alter table Student ADD s_enterance date;
alter table Student modify Sage int;
drop table Student cascade constraints;
edit

create user pan identified by “592595”;
grant dba to pan;
conn pan/592595
create table dept as select * from scott.dept;

conn system/59295
alter user scott account unlock;
alter user scott identified by “592595”;
grant dba to scott;
conn scott/592595;
select *from cat;

select *from emp;
select ename,12*sal+100 from emp;
select ename,12*(sal+100) from emp;
select empno,ename name,sal*12 “新名字” from emp;
select ename||’–’||sal||’–’||deptno from emp;
select ename,sal,comm,sal+NVL(comm,100)from emp;
select deptno from emp;
select DISTINCT deptno from emp;

desc dept;
(下面这条语句查询原用户HR 里面表的约束)
select all_constraints.constraint_name,constraint_type,search_condition,column_name
from all_constraints,all_cons_columns
where all_constraints.constraint_name=all_cons_columns.constraint_name and all_constraints.owner=’HR’ and all_constraints.table_name=’JOBS’;
（下面选定的是用户和表名）
all_constraints.owner=’HR’ and all_constraints.table_name=’EMPLOYEES’
（这样可以看外键依赖哪个表，依赖了哪个主键约束）
select all_constraints.constraint_name,r_constraint_name,constraint_type,search_condition,column_name
from all_constraints,all_cons_columns
where all_constraints.constraint_name=all_cons_columns.constraint_name and all_constraints.owner=’HR’ and all_constraints.table_name=’EMPLOYEES’
/
(加主键约束)
alter table regions add constraint <名字> primary key(列名);