您的位置：360文档中心› sql基本命令语句

sql基本命令语句

2. SQL Plus和SQL基本操作

-- 创建用户
create user identified by ;

-- 用户授权
grant [, ...] to ;
-- 权限有：connect（可登录）resource（可以创建、操作各种资源）

-- 用户撤权
revoke [, ...] from ;

-- 登录
connect /

-- 退出登录
exit;

-- 创建表
create table ( [, ...]);

-- 查看表结构
describe ;
desc ;des

-- 删除表
drop table ;

-- 插入数据
insert into values ([, ...]);
insert into ([, ...]) values ([, ...]);

-- 查询数据
select [, ...] from ;
select * from ;

-- 删除数据
delete from ; -- 全删！切记，一定要检查where
delete form where ;

-- 更新数据
update set =[, ...] where ;
update set =[, ...]; -- 切记，一定要检查where


在sql文件里使用&1，&2，&3这样的方式来引用参数，如：
select * from customers where first_name like '&1%' and last_name like '&2%';
然后在sql plus里执行时可指定参数的值，格式为：
start [.sql] [, ...]
如：
start d:\select.sql D B
start d:\select D B


1. PL/SQL的结构

PL/SQL（Procedure Language & Structural Query Language）

PL/SQL过程的结构：
declare
-- 变量、常量定义
begin
-- 过程体
end;

注：
在SQL Plus里需要使用set serveroutput on来打开系统输出，然后可以在PL SQL里用dbms_output.put_line()进行输出。


2. 定义（declaration）

变量、常量定义：
<变量名> <类型>; -- 默认为null
<变量名> <类型> := <值>;
<常量名> constant <类型> := <值>;

注意：
常量必须赋初值。
类型可以用Oracle里所有支持的类型。


3. 过程体（begin）

在过程体里写整真正的过程代码，可以包括数值计算、函数调用。
注意，赋值操作使用“:=”，于Java不同。

DBMS（DataBase Management System）
dbms_output.put_line(); -- 输出信息

if语句
第一种格式：
if then
...
end if;
第二种格式：
if then
...
elsif then
...
elsif then
...
else
...
end if;

case语句
第一种语法（赋值型）
<变量> := case <变量>
when <值> then <值>
...
when <值> then <值>
else <值>
end;
第二种语法（命令型）
case
when <条件> then <命令>;
...
when <条件> then <命令>;
else <命令>;
end case;

loop语句
第一种语法
loop
...
if <条件> then
exit;
end if;
...
end loop;
第二种语法
loop
...
exit when <条件>;
...
en

d loop;
第三种语法
while <条件>
loop
...
end loop;
第四种语法（foreach）
for <循环变量> in <初值>..<终值>
loop
...
end loop;


4. 异常处理（exception）

PL/SQL过程的完整格式为
declare
...
begin
...
exception
...
end;

在exception中可以捕获到begin里出现的异常，并对其进行处理：
begin
...
exception
when <异常名> then
... -- 处理异常
when <异常名> then
... -- 处理异常
when others then -- 捕获任意异常
... -- 处理异常
end;


5. select into语句

declare
a char(20) := 'hello';
begin
select last_name into a from customers where first_name='Steve';
dbms_output.put_line(a);
end;

select into可以将查询结果放进一个变量里。要求查询结果有且只有一行。
如果没有结果或结果有多行时，会出现异常，需要对异常进行处理。

declare
a char(20) := 'hello';
begin
select last_name into a from customers where customer_id=100;
dbms_output.put_line(a);
exception
when too_many_rows then
dbms_output.put_line('too many rows');
when no_data_found then
dbms_output.put_line('sorry, I can''t find anything...');
end;


6. 游标（cursor）

6.1 显式游标（需要显式声明）

游标用于在PL/SQL里取得查询结果，并一行一行地将结果取出放进变量里。
之后，可以从变量中获取行里的每一列。

使用游标的过程：
声明、打开、取数据、关闭

例子：
declare
cursor getCustomerName is
select * from customers where customer_id>2;
r getCustomerName%rowtype;
begin
open getCustomerName;
fetch getCustomerName into r;
close getCustomerName;
dbms_output.put_line(r.first_name);
end;

通过以下方式可以获取游标的行类型：
<游标名>%rowtype

%表示取游标的属性。属性有：
rowtype
found，表示fetch到了一行
notfound
rowcount，表示当前的行数
isopen，表示游标是否已开启

使用的关键：fetch应该放入循环中用以取出所有的行。

6.2 隐式游标（foreach游标）

declare
begin
for r in (select * from customers) loop
dbms_output.put_line(r.first_name);
end loop;
end;

1 基本信息管理

创建用户：
create user identified by ;
改密码：
alter user identified by ;
SQL Plus里用password命令
查看系统里的用户：
select username from dba_users;
删除用户：
drop user ;


2 系统授权

授权：
grant {|}[, ...] to [, ...] [with admin option];
撤权：
revoke {|}[, ...] from [, ...];

角色（role）：
connect角色：可以登录、创建视图等
resource角色：可以创建表、过程、触发器、序列等各种资源

查看用户权限：
select username, privilege, admin_option from user_sys_privs;



3 对象授权

指在对象上（表）的insert，select，update，delete的权限。

授权：
grant [, ...] on to [, ...] [with grant option];
其中：
可以用.来指定其它用户的对象，比如：test1.customers指test1的customers表。
update可以指定某几列：update([, ...])

撤权：
revoke [, ...] on from [, ...];

注意：
对象权限的撤权是“级联撤权”。系统撤权没有级联效果哦~

查看用户授予他人的表上的权限：
select ... from user_tab_privs_made;
查看用户接受到的表上的权限：
select ... from user_tab_privs_recd;
查看用户授予他人的列上的权限：
select ... from user_col_privs_made;
查看用户接受到的列上的权限：
select ... from user_col_privs_recd;


4 表空间

Oracle里的表存储于表空间中，可以在建表时指定：
create table (...) [tablespace ];
如果没有显示指定表空间，那么就存储于用户的默认表空间中。

表空间的配额（每个用户可以使用的大小）：
alter user quota {unlimited|[k|m]} on ;
如果用户是resource角色，默认为unlimited，否则是0

用户的默认表空间通过在创建用户时指定：
create user identified by
[default tablespace ]
[temporary tablespace ];
如果创建用户时没有显示指定表空间，那么默认的表空间是users

创建表空间：
create tablespace datafile
[size [k|m]] [autoextend {on|off}];
删除表空间：
drop tablespace ; -- 不会删除文件和数据
drop tablespace [including contents [and datafiles]];

修改表空间的状态
alter tablespace {read only|read write|offline|online}
read only：只读
read write：读写
offline：脱机
online：在线
查询表空间的信息：
select tablespace_name, status from dba_tablespace;


1. 创建表

crate table (
[, ...]
);


2. null约束

我们可以在不期望出现null的列上添加not null约束。
[not null]
如果某一列为not null，那么该列的值就不能为null。


3. default约束

在某一列上使用default约束可以为该列设置默认值。
在插入数据时，有默认值的列可以不指定数据。
[default ]

所有列都有默认值，如果不显式指定默认值，其默认值就是null。


4. unique约束（唯一性约束）

如果某一列有unique约束，那么这一列的值不能出现重复。如果有null值，null只能出现一次。
[unique]


5. check约束

check约束是针对于表的约

束，用于用户自定义的约束检查条件，一个表里可以写多个约束，一个约束里可以使用多列。

crate table (
[, ...],
[check <约束条件>[, ...]]
);

如：
create table student (
num varchar(20),
name varchar(10),
gender varchar(5),
age int,
check (length(num)),
check (gender in ('男','女','未知')),
check (age >= 18)
);


6. 查看表的信息

查看表结构：
desc[ibe] ;

查看用户的表：
select table_name[, <其它字段>] from user_tables;


7. 修改表

重命名表：
rename to ;

添加列：
alter table add <列定义>;
<列定义>： [各种约束]

删除列：
alter table drop column ;

改变列：
alter table modify <列定义>;
如：
alter table student modify num varchar2(20) not null;
alter table student modify num unique;
alter table student modify num default '12345';

重命名列：
alter table rename column to ;


8. 临时表

创将临时表：
create [globle temporary] table (...) [on commit {delete|preserve} rows]

会话（连接）：当我们连接到数据库时（无论是通过sql plus还是sql developer还是……），只要一连上，就会有一个会话（session）。
临时表只在会话中存在，当会话（连接）断开时，临时表的内容会被销毁。

因为还没有讲到事务，[on commit {delete|preserve} rows]可忽略。


9. 虚拟列（oracle11里新增功能）

虚拟列是实质上不存在的列，它代表某几列的计算结果，在查询时数据库会自动计算。

create table person (
height int,
weight int,
fat_degree as (height/weight)
);

不允许对虚拟列进行插入或赋值操作。


10. 主键约束

数据库设计理论（主码）：在数据库的设计中，每一个表都应该能有若干列，可以从这若干列里唯一地确定一行，而且这若干列是最小的集合。

实践建议：在数据库的设计中，每一个表都应该能有一列，可以从这列里唯一地确定一行。
那么，这一列通常叫做id（主键）。

create table (
[...] [primary key]
);

主键自动为not null，unique--(非空 无重)

11. 外键约束

当一个表里某一列的数据想要限定为其它表里的某一列中的数据（比如学生成绩表中的学号一定要在学生表中存在）,这时需要使用外键约束。

create table student_grade (
student_num varchar(20) foreign key references student(num),
);

被引用的表叫做父表或主表，引用的表叫子表或从表，子表中的数据一定要在父表中存在。
子表要依赖于父表，子表中的数据一定要在父表中有存在。

被外键所引用的

列必须要unique，比如上面的student（num），num这一列必须要是unique的。
或者可以在foreign key的定义中加上on delete cascade,可以对子表的数据进行级联删除。
如果在foreign key的定义中加上on delete set null,则在删除父表的数据时，子表的相关引用会被设置成null。

人
身份证号
姓名
性别
配偶

人
身份证号
姓名
性别
配偶---->人

class Person (
string id;
...
Person sate;

)






















相关主题