Oracle的sql基本语法总结（1） -- 创建、插入、查询备份、更新、删除

目录

一、SQL语句介绍

二、Oracle字段数据类型

三、创建表

四、添加约束

五、插入数据

（1）、单条数据插入

（2）多条数据插入

（3）查询结果的备份(存储)

（4）查询并插入他表

六、简单查询

七、更新数据

八、删除（清空）表

---

一、SQL语句介绍

在 Oracle 开发中，客户端把 SQL 语句发送给服务器，服务器对 SQL 语句进行编译、执行，把执行的结果返回给客户端。常用的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字段数据类型

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

三、创建表

-- Create table
create table jalen.stuinfo     --用户名.表名
(
  stuid      varchar2(11) not null,--学号：①
  stuname    varchar2(50) not null,--学生姓名
  sex        char(1) not null,--性别
  age        number(2) not null,--年龄
  classno    varchar2(7) not null,--班号：
  stuaddress varchar2(100) default '地址未录入',--地址 ②
  grade      char(4) not null,--年级
  enroldate  date,--入学时间
  idnumber   varchar2(18) default '身份证未采集' not null--身份证
)
tablespace USERS --③
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table jalen.stuinfo --④
  is '学生信息表';
-- Add comments to the columns 
comment on column jalen.stuinfo.stuid -- ⑤
  is '学号';
comment on column jalen.stuinfo.stuname
  is '学生姓名';

create table jalen.teacher (
 teacherid   varchar2(11) primary key, -- 老师编号
 terchername varchar2(50) not null, --老师姓名
 sex         char(1) not null,--性别
 age         number(2) not null ,--年龄
 post        varchar2(40) default '讲师', --职称默认讲师
 subject     varchar2(20), -- 教学科目
 idnumber    varchar2(18) default '未填写' --身份证
 ) tablespace users storage (initial 64k minextents 1 maxextents unlimited);

代码说明：

• ①处： not null 表示学号字段（stuid）不能为空。
• ②处：default 表示字段stuaddress不填时候会默认填入‘地址未录入’值。
• ③处：表示表stuinfo存储的表空间是users，storage表示存储参数：区段(extent)一次扩展64k，最小区段数为1，最大的区段数不限制。
• ④处：comment on table 用户名.表名 is "注释内容";  是给表名进行注释。
• ⑤处：comment on column 用户名.表名.字段名 is "注释内容" ;  是给表字段进行注释

四、添加约束

约束条件	NOT NULL	UNIQUE	PRIMARY KEY	FOREIGN KEY	CHECK	default
说明	非空约束	唯一约束	主键约束	外键外键	条件约束	默认值

 -- 非空（NOT NULL）约束
 ALTER TABLE jalen.teacher MODIFY subject NOT NULL; 
 -- 主键(PRIMARY KEY)约束
 ALTER TABLE jalen.stuinfo ADD CONSTRAINT pk_stuinfo_stuid PRIMARY KEY(stuid);
 -- 唯一（UNIQUE）约束
 ALTER TABLE jalen.teacher ADD CONSTRAINT uk_teacher_idnumber UNIQUE (idnumber);
 -- 外键（FOREIGN KEY）约束  
     -- dept为主表，emp为从表（外键表），emp中的外键列deptno引用dept中的主键
 ALTER TABLE scott.emp ADD CONSTRAINT fk_scott_emp_teptno FOREIGN KEY(deptno) REFERENCES scott.dept(deptno);  
 -- 条件（CHECK）约束     
     --给字段年龄age添加约束，学生的年龄只能0-50岁之内的
 alter table jalen.STUINFO add constraint ch_stuinfo_age check(age>0 and age<=50);
     -- 限定sex的值
 alter table jalen.STUINFO add constraint ch_stuinfo_sex check(sex='1' or sex='0');
     -- 限定年级的范围
 alter table jalen.STUINFO add constraint ch_stuinfo_grade check (grade>='1900' and grade<='2999');

五、插入数据

注意：

（1）字符串引用用的是英文状态的单引号'xx'；
（2）与mysql不同的是insert into tablename [(field1,field2,...)]values(v1,v2,...)一次只能插入一条记录；
（3）多条记录的插入（借助表dual）：
    insert into tablename (field1,field2,...)
　　      select v1,v2,... from dual
    union all select v1,v2,... from dual
    union all select v1,v2,... from dual
    ...

（1）、单条数据插入

-- 单条数据插入
-- insert into tablename [(field1,field2,...)]values(v1,v2,...)
insert into jalen.stuinfo (stuid,stuname,sex,age,classno,stuaddress,grade,enroldate,idnumber) values 
('SC201901001', 'jalen', '1', 27, 'C201901', '广东省广州市XXX号', '2019',to_date('01-09-2019', 'dd-mm-yyyy'),'5224221993XXXXXXXX');
insert into jalen.stuinfo (stuid,stuname,sex,age,classno,stuaddress,grade,enroldate,idnumber) values 
('SC201901002', 'momo', '1', 26, 'C201901', '贵州省毕节市XXX号', '2019',to_date('01-09-2019', 'dd-mm-yyyy'),'5224201994XXXXXXXX');
insert into jalen.stuinfo (stuid,stuname,sex,age,classno,stuaddress,grade,enroldate,idnumber) values 
('SC201901003', 'jingjing', '1', 27, 'C201901', '广东省广州市XXX号', '2019',to_date('01-09-2019', 'dd-mm-yyyy'),'5224221993XXXXXXXX');
insert into jalen.stuinfo (stuid,stuname,sex,age,classno,stuaddress,grade,enroldate,idnumber) values 
('SC201901004', 'liangliang', '1', 26, 'C201901', '贵州省毕节市XXX号', '2019',to_date('01-09-2019', 'dd-mm-yyyy'),'5224201994XXXXXXXX');

-- insert into tablename [(field1,field2,...)]values(v1,v2,...)
insert into jalen.teacher (teacherid,terchername,sex,age,post,subject,idnumber) values 
('1025001','liwei','1',50,'教授','数学','523429xxxxxxxx2356');
insert into jalen.teacher (teacherid,terchername,sex,age,post,subject,idnumber) values 
('1025002','wangli','1',48,'教授','语文','552429xxxxxxxx2356');

（2）多条数据插入

-- 多条数据插入
insert into jalen.teacher (teacherid,terchername,sex,age,post,subject,idnumber)
          select '1025003','liucha','1',48,'讲师','英语','520427xxxxxxxx3009' from dual            
union all select '1025004','huanghuang','0',46,'讲师','英语','520250xxxxxxxx5201' from dual
union all select '1025005','jiya','0',46,'教授','语文','250250xxxxxxxx2501' from dual

（3）查询结果的备份(存储)

create table jalen.teacher01 as 
select * from jalen.teacher;

（4）查询并插入他表

-- 清空表
truncate table jalen.teacher;

-- 查询并插入新标
insert into jalen.teacher (teacherid,terchername,sex,age,post,subject,idnumber)
select teacherid,terchername,sex,age,post,subject,idnumber from  jalen.teacher01;

六、简单查询

select * from jalen.stuinfo;
select * from  jalen.teacher;
select * from jalen.teacher01;

七、更新数据

-- update 表名 set 列名1=值1,列名2=值2,列名3=值3..... where 条件
update jalen.teacher set post = '副教授',subject ='历史' where teacherid = '1025002';

-- update 利用另外一张表关联更新本表数据的命令结构如下：
update 表1 set 列名=（select 列名 from 表2 where 表1.列名=表2.列名） 
       where exists (select 1 from 表2 where 表1.列名=表2.列名)

八、删除（清空）表

 -- delete from 表名 where 条件 
 delete from jalen.teacher01 where teacherid = '1025005'; 
 
 -- truncate table 表名 
 truncate table jalen.teacher01; 
 
 -- drop table 表名
 drop table jalen.teacher01;