Oracle（二）Oracle sql操作

SQL：结构化查询语言（Structured Query Language）。

常用sql语句分为五类：

数据定义语言（DDL），包括 CREATE（创建）命令、 ALTER（修改）命令、 DROP（删除）命令等。

数据操纵语言（DML），包括 INSERT（插入）命令、 UPDATE（更新）命令、 DELETE（删除）命令、 SELECT … FOR UPDATE（查询）等。

数据查询语言（DQL），包括基本查询语句、 Order By 子句、 Group By 子句等。

事务控制语言（TCL），包括 COMMIT（提交）命令、 SAVEPOINT（保存点）命令、ROLLBACK（回滚）命令。

数据控制语言（DCL）， GRANT（授权）命令、 REVOKE（撤销）命令。

Oracle建表（create table）

Oracle 字段类型：

VARCHAR2(length)	字符串类型：存储可变的长度的字符串，length:是字符串的最大长度，默认不填的时候是1，最大长度不超过4000。
CHAR(length)	字符串类型：存储固定长度的字符串，length:字符串的固定长度大小，默认是1，最大长度不超过2000。
NUMBER(a, b)	数值类型：存储数值类型，可以存整数，也可以存浮点型。a代表数值的最大位数：包含小数位和小数点，b代表小数的位数。例子：number(6,2)，输入123.12345，实际存入：123.12 。number(4,2)，输入12312.345，实际：提示不能存入，超过存储的指定的精度。
DATA	时间类型：存储的是日期和时间，包括年、月、日、时、分、秒。例子：内置函数sysdate获取的就是DATA类型
TIMESTAMP	时间类型：存储的不仅是日期和时间，还包含了时区。例子：内置函数systimestamp获取的就是timestamp类型
CLOB	大字段类型：存储的是大的文本，比如：非结构化的txt文本，字段大于4000长度的字符串。
BLOB	二进制类型：存储的是二进制对象，比如图片、视频、声音等转换过来的二进制对象

create table语句

示例：

-- Create table
create table STUDENT.stuinfo
(
  stuid      varchar2(11) not null,--学号：'S'+班号（7位数）+学生序号（3位数）(1)
  stuname    varchar2(50) not null,--学生姓名
  sex        char(1) not null,--性别
  age        number(2) not null,--年龄
  classno    varchar2(7) not null,--班号：'C'+年级（4位数）+班级序号（2位数）
  stuaddress varchar2(100) default '地址未录入',--地址 (2)
  grade      char(4) not null,--年级
  enroldate  date,--入学时间
  idnumber   varchar2(18) default '身份证未采集' not null--身份证
)

tablespace USERS --(3)
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

-- Add comments to the table
comment on table STUDENT.stuinfo --(4)
  is '学生信息表';
-- Add comments to the columns 
comment on column STUDENT.stuinfo.stuid -- (5)
  is '学号';

（1）处： not null 表示学号字段（stuid）不能为空。

（2）处：default 表示字段stuaddress不填时候会默认填入‘地址未录入’值。

（3）处：表示表stuinfo存储的表空间是users，storage表示存储参数：区段(extent)一次扩展64k，最小区段数为1，最大的区段数不限制。

（4）处：comment on table 是给表名进行注释。

（5）处：comment on column 是给表字段进行注释。

添加约束：

-- Create/Recreate primary, unique and foreign key constraints 
alter table STUDENT.STUINFO
  add constraint pk_stuinfo_stuid primary key (STUID);
  --把stuid当做主键，主键字段的数据必须是唯一性的（学号是唯一的）
  
-- Create/Recreate check constraints 
alter table STUDENT.STUINFO
  add constraint ch_stuinfo_age
  check (age>0 and age<=50);--给字段年龄age添加约束，学生的年龄只能0-50岁之内的
  
alter table STUDENT.STUINFO
  add constraint ch_stuinfo_sex
  check (sex='1' or sex='2'); --给sex添加约束，只能为1或2
  
alter table STUDENT.STUINFO
  add constraint ch_stuinfo_GRADE
  check (grade>='1900' and grade<='2999'); --给grade添加约束，只能在1900和2999之间

Oracle 查询（select）

格式：

select *|列名|表达式 from 表名 where 条件 order by 列名

示例：

查询学生信息表（stuinfo）中“李四”同学的基本信息,按年龄进行升序排列：
select t.* from STUDENT.STUINFO t where t.stuname = '李四' ORDER BY T.AGE ASC;

备份查询数据：

create table 表名 as select 语句

示例：

备份学生信息表（stuinfo）的数据：

create table student.stuinfo_2018 as select * from student.stuinfo ;

--从备份中查询数据
select * from student.stuinfo_2018;

Oracle 插入（insert into）

insert 命令结构：

insert into 表名（列名1,列名2,列名3.....）values(值1,值2,值3.....);

语法解析：

1、列名可以省略，当列名不填时，默认的是表中的所有列，列的顺序是按照建表的顺序进行排列的。

2、列名的数量和值的数量要一致，并且值的类型要和列的类型一一对应。

3、当表当中某些字段设置了某些约束的情况下，必须按照字段的约束来进行该值的插入，例如：学生信息表（STUINFO）当中设置有主键（主键字段是STUID）,因此该字段必须具有唯一性，不能和原有的数据重复。age、stuname、calassno等字段是必填字段，因此是必须有值的。

示例：

向学生信息表（stuinfo）插入一条数据：

insert into STUDENT.STUINFO (STUID, STUNAME, SEX, AGE, CLASSNO, STUADDRESS, GRADE, ENROLDATE, IDNUMBER)
values ('SC201801005', '龙七', '1', 26, 'C201801', '福建省厦门市XXX号', '2018', to_date('01-09-2018', 'dd-mm-yyyy'),
 '3503021992XXXXXXXX');
select * from student.stuinfo t where t.stuid='SC201801005';

oracle插入多条语句

注： oracle 不支持像mysql一样的插入多条语句的写法，插入多条语句可以按如下格式写法：

CREATE TABLE test(
	id INTEGER,
	name VARCHAR2(32)
)


insert all
into test(id, name) values(1, '张三')
into test(id, name) values(2, '李四')
into test(id, name) values(3, '王五')
select 1 from dual;

dual表说明

说明：dual是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录。

我们可以用它做很多事情，如下：

1、查看当前用户，可以在 SQL Plus中执行下面语句

select user from dual;

2、用来调用系统函数

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;–获得当前系统时间

select SYS_CONTEXT('USERENV','TERMINAL') from dual;–获得主机名

select SYS_CONTEXT('USERENV','language') from dual; --获得当前locale

select dbms_random.random from dual; --获得一个随机数

3、得到序列的下一个值或当前值，用下面语句

select your_sequence.nextval from dual; --获得序列your_sequence的下一个值

select your_sequence.currval from dual; --获得序列your_sequence的当前值

4、可以用做计算器

select 7*9 from dual

向学生信息表（stuinfo）插入重复数据：

insert into STUDENT.STUINFO (STUID, STUNAME, SEX, AGE, CLASSNO, STUADDRESS, GRADE, ENROLDATE, IDNUMBER)
values ('SC201801005', '龙七', '1', 26, 'C201801', '福建省厦门市XXX号', '2018', to_date('01-09-2018', 'dd-mm-yyyy'),
 '3503021992XXXXXXXX');

会提示如下错误：

insert 插入一个select的结果集

在 Oracle 中，一个 INSERT 命令可以把一个select结果集一次性插入到一张表中。

INSERT INTO 表 SELECT 子句。

示例：把上一章节利用Oracle查询（select）语句备份的表stuinfo_2018的数据一次插入表stuinfo当中：

delete  from student.stuinfo t where t.stuid in (select b.stuid from student.stuinfo_2018 b );

insert into student.stuinfo select * from student.stuinfo_2018;

select * from student.stuinfo;

Oracle 更新（update）

update 表名 set 列名1=值1,列名2=值2,列名3=值3..... where 条件

示例：更新学生“张三”的年龄和身份证信息：

update student.stuinfo t
   set t.age = '24', t.idnumber = '3503021994XXXXXXXX'
 where t.stuname = '张三';
commit;
select * from student.stuinfo t where t.stuname='张三';

利用另外一张表关联更新本表数据的命令结构如下：

update 表1 set 列名=（select 列名 from 表2 where 表1.列名=表2.列名） 
       where exists (select 1 from 表2 where 表1.列名=表2.列名)

利用备份表stuinfo_2018更新回学生“张三”的年龄和身份证：

update student.stuinfo t
   set (age, idnumber) =
       (select age, idnumber from student.stuinfo_2018 b where b.stuid = t.stuid)
 where exists (select 1
          from student.stuinfo_2018 b
         where b.stuid = t.stuid
           and b.stuname = '张三');
           
select *from student.stuinfo t where t.stuname='张三';

Oracle 删除（delete）

delete

当delete from不加where条件时，表示是把表中的数据全部删除。

delete from 表名 where 条件

案例1、删除学生信息表（stuinfo）中学生“张三”的数据：

delete  from stuinfo t where t.stuname='张三';

truncate

截断、截短、删节。

truncate也是数据删除命令，它是直接把表数据删除（truncate删除原来的表然后重新创建一个新的表，而不是逐行删除表中数据！）truncate是一个DDL命令（delete是DML命令）。

truncate table 表名；

案例2、删除学生信息备份表（stuinfo_2018）:

truncate table stuinfo_2018;

drop 删除整个表，包括表结构

drop table xx;
从数据库中删除表。

truncate和delete区别：

1、truncate是DDL命令，命令执行完就提交，删除的数据不能恢复；

delete是DML命令，命令执行完需提交后才能生效，删除后的数据可通过日志文件恢复。

2、如果表中的数据量较大，TRUNCATE的速度比DELETE速度快很多。

3、truncate删除将重新设置表索引的初始大小，而delete不能。

4、delete能够触发表上相关的delete触发器，而truncate则不会触发。

5、delete删除的原理是一次一条从表中删除数据，并将删除操作当做 事务 记录在数据库的日志当中，以便进行数据回滚。而truncate是一次性进行数据页的删除，因此执行速度快，但是不能回滚。

总结： truncate命令是属于DDL命令，一次性删除表中所有数据，并且数据不能恢复，在实际开发过程当中truncate命令慎用。

其它：

连接字符操作

连接两个字符（concat）：

select concat('aa','bb') from dual;

连接多个字符（||）

select 'aa'||'bb'||'cc' from dual;