Oracle SQL调用

普通用户连接
sqlplus scott/scott@orcl;
超级管理员身份连接
sqlplus sys/orcl as sysdba;
用户断开与当前数据库的连接
disc[onnect]
连接数据库
conn[ect] scott/scott@orcl
修改密码
password

创建用户
create user xiaoming indentified by xiaoming;
系统授权
grant connect,resource to xiaoming;
对象授权
grant select on scott.ursers to xiaoming;
收回对象授权
revoke select on scott.users from xiaoming;
删除用户和用户的表
drop user xiaoming cascade

清屏
host cls
显示当前用户
show user
当前用户下的表
select * from tab
查看表结构
desc emp
设置显示的行宽
–先显示行宽
show linesize
–设置行宽
set line size 150
设置列宽,表示ename这个字段 a代表字符串，固定写法，表示8个字符长度
column ename format a8
–可以简写，9代表一位数字
col sal for 9999
/表示执行上条SQL语句
注释
–单行注释
/* */多行注释

一、oracle表名和列的命名规则
 必须以字母开头
 长度不能超过30个字符
 不能使用oracle的保留字
 只能使用如下字符A-Z,a-z,0-9,$,#等
1、 oracle支持的数据类型
（1）、 字符型
 char 定长，最大2000字符，查询速度快。
char(12) ‘a’ 也占12个字符
 varchar2 变长，最大4000字符，查询速度比char慢，
varchar2(12) ‘a’ 只占1个
 clob(character large object)字符型大对象，最大4G
（2）、 数字型
 number范围 -10的38次方到10的38次方
可以表示整数，也可以表示小数。
number(5,2)
表示一个小数有5位有效数，2位小数，范围-999.99至999.99
number(5)表示一个五位整数
范围-99999至99999
（3）、 日期类型
 date 包含年月日和时分秒
 timestamp这是oracle对date数据类型的扩展
（4）、 图片
 blob 二进制数据 可以存放图片/声音

二、表的操作
1、 建表
create table student1(–表名
stuNo number(4),–学号
stuName varchar2(20),–姓名
sex char(2),–性别
birthday date,–出生日期
sal number(7,2) --奖金
);
2、修改表结构
添加一个字段
alter table student add(class_id number(2));
修改字段长度
alter table student modify(sex char(3));
修改字段类型或名字（不能有数据）
alter table student modify(stuName char(30));
删除一个字段
alter table student drop column sal;
修改表名
rename student to stu;
删除表
drop table student;

3、oracle中的约束
(1)、约束包括：
not null、
unique、
primary key、
foreign key、
check
五种

商店售货系统表案例，由三个表构成：
商品表：
表名： goods
字段 类型 描述 约束
goodsId char(8) 商品编号 主键
goodsName varchar2(40) 商品名
unitprice number(8,2) 单价 大于0
category varchar2(20) 商品类别
provider varchar2(40) 供应商

客户表
表名： customer
字段 类型 描述 约束
customerId char(8) 客户编号 主键
name varchar2(50) 姓名 非空
address varchar2(50) 住址
email varchar2(50) 邮箱 唯一
sex char(2) 性别 只能有男和女，默认男
cardId char(18) 身份证

销售表
表名： purchase
字段 类型 描述 约束
p_customerId char(8) 客户编号 外键
p_goodsId char(8) 商品编号 外键
nums number(2) 销售数量 1-30之间

(2)、创建表时列级定义约束
create table goods1(
goodsId char(8) primary key,
goodsName varchar2(30),
unitprice number(10,2),check(unitprice>0),
category varchar2(20),
provide varchar2(30)
);

create table customer(
customerId char(8) primary key,
name varchar2(50) not null,
address varchar2(50),
email varchar2(50) unique,
sex char(3) default ‘男’ check(sex in(‘男’,‘女’)),
cardId char(18)
);

create table purchase(
p_customerId char(8) references customer(customerId),
p_goodsId char(8) references goods(goodsId),
nums number(2) check(nums between 1 and 30)
);

(3)、创建表后再添加约束：
注意如果是非空约束需要加modify
alter table goods1 modify goodsName not null;
默认约束
alter table class modify cname default ‘张三’;
给customer表的cardId增加唯一约束
alter table customer add constraint card_unique unique(cardId);
给customer表的address地址添加检查约束
alter table cutomer add constraint card_unique unique(cardId);
其余和SQLServer的一样。

(4)、删除约束
alter table 表名 drop constraint 约束名;
在删除主键约束的时候，可能有错误，比如:
alter table 表名 drop primary key;
这是因为如果两张表存在主从关系，那么，在删除主表的主键约束时，必须带上cascade选项
alter table 表名 drop primary key cascade;

4、插入数据：
insert into student1 values(2,'李四’,'男’,‘1-1月-09’,1100);
注意：oracle中默认的日期格式为 DD-MM-YYYY
该日期的默认格式：
to_date(‘2017-1-1’,‘YYYY-MM-DD’)
alter session set nls_date_format = ‘yyyy-mm-dd’;
修改后就可以按照熟悉的格式插入数据了
insert into student values(3,’李四’,’男’,’2000-10-19’,1100);
插入空值，字段值为null
insert into student values(2,’李四’,’男’,’2000-10-19’,1,null);

5、删除数据
 delete from student;
删除所有记录，表结构还在，写日志，可以恢复的，速度慢

 truncate table student;
删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快

 drop table student;
删除表的结构和数据

6、查询
set timing on;//打开操作时间开关
（1）、 查看表结构
desc 表名;
（2）、 取消重复行
select distinct deptno,job from emp;
（3）、 查询处理空值
nvl(列名,0) 当该列的值为空时，就补一个值 0
select sal*13+nvl(comm,0) total from emp;

nvl2(列名,‘不为空显示的’，‘为空的时候显示的’)
select empno,ename,nvl2(comm,‘有奖金’,‘没有奖金’) from emp;

nullif(列名,值) 当该列的值为多少时显示为null;

（4）、 合并查询
为了合并多个select语句的结果，可以使用集合操作符号union，union all,intersect,minus
 union该操作符用于取得两个结果集的并集，当使用该操作符时，会自动去掉结果集中重复行
查询工资大于2500与职位为MANAGER的并集
select ename,sal,job,from emp where sal>2500 union select ename,sal,job from emp where job=’MANAGER’;
 union all与union相似，但是它不会取消重复行
select ename,sal,job,from emp where sal>2500 union all select ename,sal,job from emp where job=’MANAGER’;

三、序列

   可以为表中的列自动产生值
   由用户创建数据库对象,并可由多个用户共享
   一般用于主键或唯一列.

   例子:
   create sequence my_seq              ---创建序列名

   start with 1                        ---从1开始

   increment by 1                      ---每次增长1

   maxvalue 999999999                  ---最大值

   minvalue 1                          ---最小值

   cycle                               ---循环

   cache                               ---缓存

   order

   从1开始,每次增长1,最大值为999999999,之后又循环从1开始．

    SQL语句:
    insert into mytable values(my_seq.nextval,'aaa')

    insert into mytable values(my_seq.nextval,'bbb')

   结果为:
   1     aaa
   2    bbb

   调用:
   select my_seq.nextval from dual             ---新值

   select my_seq.currval from dual             ---当前值