常用SQL语句

关键字: sql语句 常用
--显示当前用户
show user

--显示表结构
desc sun1

--显示ORACLE数据库中所有的表
select * from tabs

--显示表的所有列的属性
select *
from cols
where table_name = 'SUN1'

--显示表的列总数
SELECT COUNT(*)
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME=UPPER('sun1')

--显示表的索引信息
select * from user_indexes
where table_name = upper( 'sun2' )

--找数据库表的主键字段的名称
SELECT *
FROM user_constraints
WHERE CONSTRAINT_TYPE='P'
and table_name='TABLE_NAME';

--显示用户的权限
select *
from dba_sys_privs
where grantee = upper( username );

--显示表注释
select *
from user_tab_comments
where comments is not null
and table_name = upper( 'sun2' );

--测试查询时间
set timing on ;
--再查询操作

-----------------------------------函数使用------------------------------------------
--取随机数
dbms_random.random

--10进制转换成16进制
select to_char(125,'XXXXX') from dual
--或者
select to_char(125, 'xxxxx' ) from dual

--16进制转换成10进制
select to_number( '7D', 'XX') from dual

--取本月最后一天的日期
SELECT LAST_DAY(SYSDATE) FROM DUAL

---------------------------------建表-----------------------------------------------

--简单建表
create table sun1( col1 varchar2(10), col2 varchar2(10 ) );

--非空，带CHECK，唯一值的建表
create table student
(
sno int not null unique,
sname varchar(20) unique check( sname <> '' ),
ssex bit,
sage int check( sage<150 and sage >=0 ),
sdept varchar(20)
)

--带主键，带默认值的建表
create table sun2(
col1 number(10) primary key,
col2 varchar(2) not null,
col3 varchar(3) default '111'
)

--主键有多列的建表
create table sun2(
col1 number(10) not null ,
col2 varchar(2) not null,
col3 varchar(3) default '111',
constraint pk_sun2 primary key( col1, col2 )
)

--通过一现有的表，建一个与之结构完全一样的表
create table sun2 as
select * from sun1 where rownum < 1

--建一与现有的表完全一样的表（数据也一样）
create table sun2 as
select * from sun1

--创建会话临时表
create global temporary table tmp_sun
(
col1 number(10,1),
col2 number(1)
)
on commit preserve rows

--创建事务临时表
create global temporary table tmp_sun
(
col1 number(10,1),
col2 number(1)
)
on commit delete rows

--创建同义词
create synonym sunsun for sun1;

--为表加上注释
comment on table sun2 is '测试用的表'

--为表中的某列加上注释
comment on column sun2.col1 is '第一列'

--删除表的主键
alter table sun2 drop constraint pk_sun2;

--增加表的主键
alter table sun2 add constraint pk_sun2 primary key( col1 );

--给表增加外键
ALTER TABLE 表名
ADD CONSTRAINT 约束名
FOREIGN KEY(列名) REFERENCES 被引用表名(被引用列名);

--建索引
CREATE INDEX 索引名　ON 表名(列名1, 列名2);

--增加表的列
alter table sun2 add( col1 number(10), col4 char )

--修改表的列
alter table sun2 modify col1 char

--删除表的列
alter table sun2 drop column col1;


--修改列名
alter table sun2 rename column col1 to colNew

--修改表名
alter table sun2 rename to sun8

---------------------------------查询数据---------------------------------------------
--给查询到的数据加锁
select * from sun1 for update

--取上个月的今天
select nvl( add_months( sysdate, -1), null) from dual

---------------------------------插入数据---------------------------------------------
--简单的插入数据
insert into sun1( col1, col2 ) values( 'ab', 'ba' )
--或者
insert into sun1 values( 'ab', 'ba' )

--通过子查询向表中插入数据
insert into sun1
(
select 'aa', 'bb' from dual
union all
select 'aa', 'bb' from dual
)

--或者
insert into sun1(col1, col2 )
(
select 'aa', 'bb' from dual
union all
select 'aa', 'bb' from dual
)


---------------------------------删除数据---------------------------------------------
--删除表中的所有数据
delete from sun1
--或者
truncate table sun1


--删除表中重复的行
delete from sun1
where rowid not in
(
select max( s.rowid )
from sun1 s
group by col1,col2
)

---------------------------------更新数据---------------------------------------------
--从一表中取多列数据来更新另一表中的多列数据
update table1 a
set (a.col3, a.col4) = (　select b.col3, b.col4
　　　from table2 b
　　　where b.col1 = a.col1
　　　and b.col2 = a.col2
)

---------------------------------删除表-------------------------------------------------

--删除一表
drop table sun2


---------------------------------权限管理-------------------------------------------------
--系统权限
--给用户授权CONNECT(基本的连接)
grant connect to username

--给用户授权RESOURCE(程序开发)
grant resource to username

--给用户授权DBA(数据库管理)
grant dba to username

--给用户授权
grant connect, resource to username

--数据对象权限
GRANT SELECT ON sun1 TO username;

GRANT SELECT, INSERT, DELETE ON sun2 TO user1, user2


--REVOKE 回收权限

REVOKE CONNECT, RESOURCE FROM 用户名;

REVOKE SELECT ON 表名 FROM 用户名;

REVOKE SELECT, INSERT, DELETE ON 表名 FROM 用户名1, 用户名2;


---------------------------表空间等管理-------------------------
--创建一表空间

CREATE TABLESPACE 自定义的模块名a
DATAFILE 'd:\database\ora\projet_info\d01.ora' SIZE 2M REUSE
DEFAULT STORAGE ( INITIAL 50K NEXT 50K
PCTINCREASE 0
MAXEXTENTS UNLIMITED
)
ONLINE;