oracle-sql语句（建表，增删改查）

Oracle自学笔记整理（二）
二、sql语句

2.1 建表语句

-- Create table
create` `table` `表名
(
 ``字段名1  字段类型  【字段约束】,

   字段名2  字段类型  【字段约束】,
  ......
   字段名n  字段类型  【字段约束】
   )
tablespace USERS ``--表空间为users
 ``storage--存储信息
 ``(
  ``initial 64K  --区段一次扩展64k
  ``minextents 1  --最小区段为1
  ``maxextents unlimited  --最大区段无限制
 ``);

-- Add comments to the table (增加表注释)
comment ``on` `table` `STUDENT.stuinfo ``
 ``is` `'中文表名'``;
 
-- Add comments to the columns （增加字段注释）
comment ``on` `column` `库名.表名.字段名1 ``
 ``is` `'中文注释1'``;
comment ``on` `column` `库名.表名.字段名2
 ``is` `'中文注释2'``;
 ......
comment ``on` `column` `库名.表名.字段名n
 ``is` `'中文注释n'``;

--增加字段约束（主键约束，唯一约束，外键约束）
-- Create/Recreate primary, unique and foreign key constraints 
alter` `table` `表名
 ``add` `constraint` `字段约束名称 ``约束类型` `key` `(字段);
 ``
eg:
alter table STUDENT.STUINFO
  add constraint pk_stuinfo_stuid primary key (STUID);
  --把stuid当做主键，主键字段的数据必须是唯一性的（学号是唯一的）

--增加字段检查约束
-- Create/Recreate check constraints 
alter table 表名
  add constraint 字段约束名称
  check (字段约束条件);
  
eg:
-- Create/Recreate check constraints 
alter table STUDENT.STUINFO
  add constraint ch_stuinfo_age
  check (age>0 and age<=50);--给字段年龄age添加约束，学生的年龄只能0-50岁之内的

2.2 查询语句（select）

--完整查询语句及执行顺序
select` `*|列名|表达式 ``		⑦
from` `表名1 `` 别名1 			 ①
【join type】join 表名2 别名2	    ②
on 连接条件						 ③
where` `条件 ``				  ④
group by 分组字段				 ⑤
having 分组后筛选条件			   ⑥
order` `by` `列名 --排序		 ⑧
limit 要显示的条目起始索引（(page-1)*size）,要显示的条目个数（size）--起始索引从0开始  ⑨

--备份查询数据命令结构
create` `table` `表名 ``as` `select` `语句

2.3 插入语句（insert）

--向表中插入数据
insert` `into` `表名（列名1,列名2,列名3.....）``values``(值1,值2,值3.....);
--把一个select结果集一次性插入到一张表中
INSERT INTO 表 SELECT 子句

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

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

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

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

2.4 更新语句（update）

--更新表中数据
update` `表名 ``set` `列名1=值1,列名2=值2,列名3=值3..... ``where` `条件

eg:
update student.stuinfo t
   set t.age = '24', t.idnumber = '3503021994XXXXXXXX'
 where t.stuname = '张三';
 
--利用另外一张表关联更新本表数据

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

eg:
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 = '张三');

2.5 删除语句（delete|truncate）

--删除表中数据（delete）
delete` `from` `表名 ``where` `条件  --当delete from不加where条件时，表示是把表中的数据全部删除

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

--表数据一次删除(truncate,是一个DDL命令，不同于delete是DML命令。)
truncate` `table` `表名；

eg:
truncate table stuinfo_2018;

truncate和delete都能删除表中的数据，他们的区别：

1、TRUNCATE 是 DDL 命令，命令执行完就提交，删除的数据不能恢复； DELETE 命令是 DML 命令，命令执行完需提交后才能生效，删除后的数据可以通过日志文件恢复。

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

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

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

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

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