目录
一、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;