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;

原文: 链接

上一篇:Oracle(一)介绍:结构、实例、表空间、用户              下一篇:Oracle(三)Oracle运算符

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值