Oracle的SQL操作

最新推荐文章于 2022-03-10 15:12:58 发布
最新推荐文章于 2022-03-10 15:12:58 发布
阅读量1.1k 收藏 4
点赞数
文章标签： Oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhaoxuyang1997/article/details/81612680
版权

目录

文章目录

第一部分 - Oracle基本操作

1 创建数据表

1.1创建员工表

create table tb_emp1(
       id number(11),
       name  varchar2(25),
       deptId number(11),
       salary number(9,2)
);

1.2 使用主键约束

1.2.1 在定义列的同时创建主键约束

create table tb_emp2(
       id number(11) primary key,
       name  varchar2(25),
       deptId number(11),
       salary number(9,2)
);

1.2.2 在定义列后创建主键约束

create table tb_emp3(
       id number(11),
       name  varchar2(25),
       deptId number(11),
       salary number(9,2),
       primary key(id)
);

1.2.3 创建多字段联合主键约束

create table tb_emp4(
       name  varchar2(25),
       deptId number(11),
       salary number(9,2),
       primary key(name,deptId)
);

1.2.4 用修改表来添加主键约束

create table tb_emp5(
       id number(11),
       name  varchar2(25),
       deptId number(11),
       salary number(9,2)
);
alter table tb_emp5
add constraints pk_id primary key(id);

1.2.5 移除主键约束

alter table tb_emp5
drop constraints pk_id;

1.3 使用外键约束

1.3.1 创建外键约束

create table tb_dept1(
       id number(11) primary key,
       name varchar2(22) not null,
       location varchar2(50)
);

create table tb_emp6(
       id number(11) primary key,
       name varchar2(22),
       deptId number(11),
       salary number(9,2),
       
       constraints fk_emp1_dept1
       foreign key(deptId)
       references tb_dept1(id)
);

1.3.2 用修改表来添加外键约束

create table tb_dept2(
       id number(11) primary key,
       name varchar2(22) not null,
       location varchar2(50)
);
create table tb_emp7(
       id number(11) primary key,
       name varchar2(22),
       deptId number(11),
       salary number(9,2)
);
alter table tb_emp7
add constraints fk_emp7_dept2
foreign key(deptId) references tb_dept2(id)
on delete cascade;

1.3.3 移除外键约束

alter table tb_emp7
drop constraints fk_emp7_dept2;

1.4 使用非空约束

1.4.1 创建非空约束

create table tb_emp8(
       id number(11) primary key,
       name varchar2(25) not null
);

1.4.2 用修改表来添加非空约束

create table tb_emp9(
       id number(11) primary key,
       name varchar2(25) 
);
alter table tb_emp9
modify name not null;

1.4.3 移除删除非空约束

alter table tb_emp9
modify name null;

1.5 使用唯一性约束

1.5.1 创建唯一性约束

create table tb_emp10(
       id number(11) primary key,
       name varchar2(22) unique
);

1.5.2 在定义列后创建唯一性约束

create table tb_emp11(
       id number(11) primary key,
       name varchar2(22),
       constraints sth unique(name)
);

1.5.3 用修改表来添加唯一性约束

create table tb_emp12(
       id number(11) primary key,
       name varchar2(22)
);
alter table tb_emp12
add constraints unq_name unique(name);

1.5.4 移除唯一性约束

alter table tb_emp12
drop constraints unq_name;

1.6 使用默认约束

create table tb_emp13(
       id number(11) primary key,
       name varchar2(22) not null,
       major varchar2(20) default '电子商务'
);

1.7 使用检查约束

1.7.1 创建检查约束

create table tb_emp14(
       id number(11) primary key,
       name varchar2(22) not null,
       sex varchar2(2),
       constraints ck_emp14_sex check (sex='男' or sex='女')
);

1.7.2 用修改表来添加检查约束

create table tb_emp15(
       id number(11) primary key,
       name varchar2(22) not null,
       sex varchar2(2)
);
alter table tb_emp15
add constraints ck_emp15_sex
check(sex='男' or sex='女');

1.7.3 移除检查约束

alter table tb_emp15
drop constraints ck_emp15_sex;

2 修改数据表

2.1 修改表名

create table tb_emp17(
       id number primary key,
       name varchar2(25) not null,
       sex varchar2(2),
       birth date,
       college varchar2(30),
       major varchar2(30),
       email varchar2(50),
       constraints ck_emp17_sex check(sex='男' or sex='女')    
);
alter table tb_emp17
rename to tb_alter;

2.2 修改字段

2.2.1 修改字段的数据类型

alter table tb_alter
modify name varchar2(40);

2.2.2 修改字段名

alter table tb_alter
rename column id to userId;

2.2.3 添加字段

alter table tb_alter
add address varchar2(100);
alter table tb_alter
add QQ varchar2(11);

2.2.4删除字段

alter table tb_alter
drop column email;

3 删除数据表

3.1 删除没有被关联的表

create table tb_emp18(
       id number primary key,
       name varchar2(25) not null
);
drop table tb_emp18;

3.2 删除被其他表关联的表

create table tb_emp19(
       id number primary key,
       name varchar2(22) not null,
       address varchar2(50)
);
create table tb_emp20(
       id number primary key,
       name varchar2(22) not null,
       deptId number,
       constraints fk_emp20_deptId
       foreign key(deptId) references tb_emp19(id)
);
--错误语句
drop table tb_emp19;
--删除失败，提示“表中的唯一/主键被外键引用”
--如果是drop table tb_emp20; 则能成功地删除
--正确语句
alter table tb_emp20
drop constraints fk_emp20_deptId;
drop table tb_emp19;

4 综合

4.1 表结构

4.1.1 offices表结构

字段名数据类型主键外键非空唯一
officeCodenumber(10)是否是是
cityvarchar2(50)否否是否
addressvarchar2(50)否否否否
countyvarchar2(50)否否是否
postalCodevarchar2(15)否否否是

4.1.2 employees表结构

字段名数据类型主键外键非空唯一
employeeNumbernumber(11)否否是是
lastNamevarhcar2(50)否否是否
firstNamevarhcar2(50)否否是否
mobilevarhcar2(25)否否是是
officeCodevarhcar2(10)否是是否
jobTitlevarhcar2(50)否否是否
birthdate否否是否
notevarhcar2(255)否否否否
sexvarhcar2(5)否否否否

4.2 题目

  1. 创建表offices
  2. 创建表employees
  3. 将表employees的officeCode字段改名为employess_ officeCode
  4. 将表employees的sex字段的数据类型改为varchar2(2)，添加非空约束，检查约束
  5. 将表employees的note字段删除
  6. 在表employees中添加favorite_activty，数据类型为varchar2(100)
  7. 将表offices删除
  8. 将表employees改名为employeesInfo

4.3 参考答案

4.3.1 创建表offices

create table offices(
       officeCode number(10) primary key,
       city varchar2(50) not null,
       address varchar2(50),
       county varchar2(50) not null,
       postalCode varchar2(15) unique
);

4.3.2 创建表employees

create table employees(
       employeeNumber number(11) not null unique,
       lastName varchar2(50) not null,
       firstName varchar2(50) not null,
       mobile varchar2(25) unique,
       officeCode number(10) not null,
       jobTitle varchar2(50) not null,
       birth date not null,
       note varchar2(255),
       sex varchar2(5),
       constraints fk_employees_officeCode
       foreign key(officeCode)
       references offices(officeCode)
);

4.3.3 将表employees的officeCode字段改名为eCode

alter table employees
rename column officeCode to eCode;

4.3.4 将表employees的sex字段的数据类型改为varchar2(2)，添加非空约束，检查约束

alter table employees
modify sex varchar2(2);

alter table employees
add constraints unique_sex unique(sex);

alter table employees
add constraints ck_sex
check(sex='男' or sex='女');

4.3.5 将表employees的note字段删除

alter table employees
drop column note;

4.3.6 在表employees中添加favorite_activty，数据类型为varchar2(100)

alter table employees
add favorite_activty varchar2(100);

4.3.7 将表offices删除

alter table employees
drop constraints fk_employees_officeCode;
drop table offices;

4.3.8 将表employees改名为employeesInfo

alter table employees
rename to employeesInfo;

第二部分 - Orale 视图操作

1 创建视图

1.1 在单表上创建视图

create table t(
       q number(9),
       p number(9)
);
insert into t
values(3,4);

create view view1 as
select * from t;

1.2 在多表上创建视图

create table t2(
       a number(10) ,
       d number(10) 
);
insert into t2
values(0,12);

create view view2 as
select t.q,t.p,t2.a from t,t2;

1.3 创建视图的视图

create view view3 as
select view1.q,view2. a from view1,view2

2 修改视图

create or replace view view3 as
select * from view1;

3 更新视图

3.1 直接更新

update view3
set  q=9;

3.2 插入记录

insert into view3
values(2,4);

3.3 删除记录

delete from view3
where q=2;

4 删除视图

drop view view2;

5 限制视图

5.1 设置只读

create or replace view view4 as
select * from view3
with read only;

5.2 设置检查

create or replace view view5 as
select * from view3
where p>0
with check option;

第三部分 - Oracle数据操作

1 插入数据

1.1 为表的所有字段插入数据

create table data1(
       id number primary key,
       name varchar2(22) not null,
       age number not null,
       innfo varchar2(100)
);
insert into data1 values(1,'zhangsan',19,'student');

1.2 为表的指定字段插入数据

insert into data1(id,name,age) values(2,'lisi',20);

1.3 同时插入多条记录

insert into data1 values(3,'wangwu',20,'a good boy');
insert into data1 values(4,'chuliu',22,'a good boy');
insert into data1 values(5,'wangwu',18,'a good boy');

1.4 将查询结果插入到表中

create table data2(
       id number primary key,
       name varchar2(22) not null,
       age number not null,
       info varchar2(100)
);
insert into data2
select * from data1
where age>20;

2 更新数据

update data2
set info='his/her age>20'
where age>20;

3 删除数据

delete from data2;

delete from data1
where age<20;

第四部分 - Oracle函数

#1 数学函数
##1.1 绝对值ABS(x)
##1.2 平方根SQRT(x)
##1.3 求余MOD(x,y)
##1.4 向上取整CEIL(x)
##1.5 向下取整FLOOR(x)
##1.6 获取随机数
###1.6.1 返回[0,1]之间的随机数DBMS_RANDOM.RANDOM
###1.6.2 返回[x,y]之间的随机数DBMS_RANDOM.VALUE(x,y)
##1.7 四舍五入
###1.7.1 最接近x的整数ROUND(x)
###1.7.2 保留y位小数位数ROUND(x,y)
###1.7.3 舍到小数点后y位TRUNC(x,y)
##1.8 符号SIGN(x)
##1.9 幂运算
###1.9.1 x的y次方PRWER(x,y)
###1.9.2 e的x次方EXP(x)
##1.10 对数运算
###1.10.1 以x为底y的对数LOG(x,y)
###1.10.2 以e为底x的对数ln(x)
##1.11 三角函数
###1.11.1 正弦函数SIN(x)
###1.11.2 反正弦函数ASIN(x)
###1.11.3 余弦函数COS(x)
###1.11.4 反余弦函数ACOS(x)
###1.11.5 正切函数TAN(x)
###1.11.6 反正切函数ATAN(x)
#2 字符串函数
##2.1 字符串长度LENGTH(str)
##2.2 合并字符串CONCAT(s1,s2)
##2.3 字符串搜索INSTR(s,x)
##2.4字母大小写转换
###2.4.1 全部转换成大写LOWER(str)
###2.4.2 全部转换成小写UPPER(str)
###2.4.3 首字母大写INITCAP(str)
##2.5 获取指定长度的字符串SUBSTR(原字符串,截取的位置,截取的长度)
##2.6 替换字符串REPLACE(原字符串,将要替换的字符串,被替换成的字符串)
##2.7删除字符串首尾字符
###2.7.1 删除字符串s左侧为str的字符串LTRIM(s,str)
###2.7.2 删除字符串s右侧为str的字符串RTRIM(s,str)
###2.7.3 删除字符串s两侧为str的字符串TRIM(str)
##2.8 字符串名称和ID互换
###2.8.1 获取字符串集对应的ID 	NLS_CHARSET_ID(str)
###2.8.2 获取字符串集对应的名称NLS_CHARSET_NAME(str)
#3 日期和时间函数
##3.1 获取系统当前日期 SYSDATE
##3.2 获取系统当前时间 SYSTIMESTAMP
#4 转换函数
##4.1 字符转ASCII码 ASCII(s)

二进制转十进制 BIN_TO_NUM()

--daul：系统表，常用来测试函数
select BIN_TO_NUM(1,1,0) from dual
--输出：6
select BIN_TO_NUM(110) from dual
--输出：错误，参数'110''超出范围

数据类型转换 CAST

select cast(10 as  number(3,1)) from dual
--输出：10.0

数值转换成字符串TO_CHAR

select to_char(098) from dual
--输出：98

转换成格式化日期 TO_DATE

select to_char(sysdate,'yyyy/mm/dd hh:mi:ss') from dual
select to_date('2017-09-08','yyyy-mm-dd') from dual

第五部分 - PL/SQL编程

1 PL/SQL 基本结构

1.1 只包含执行部分

begin
	dbms_output.put_line('PL/SQL的基本单位是块（block）');
end;

--输出：PL/SQL的基本单位是块（block）

1.2 包含声明和执行部分

declare
	number_Avg number;

begin
	number_Avg:=1997;
	dbms_output.put_line(number_Avg);
end;

--输出：1997

1.2 包含声明、执行和异常处理部分

declare
	aNum number;

begin
	aNum:=0;
	aNum:=1/aNum;
	dbms_output.put_line(aNum);

exception
	when ZERO_DIVIDE then
		dbms_output.put_line('0不能作为除数！');
		
end;

/*
	输出：0不能作为除数！
*/

更多参见：
https://user.qzone.qq.com/1395359719/blog/1496132813

第六部分 - 存储过程

https://user.qzone.qq.com/1395359719/blog/1496132891

z8g
关注
  • 0
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
oracle常用操作SQL语句
05-06
汇总oracle一些常用SQL语句，比如：用户解锁、修改用户密码、锁表、视图恢复、定时任务、物化视图、触发器、带参游标、常用函数、执行计划、字符串/时间各种运算、HTTP请求、SQL优化等
PL/SQL DEVELOPER 使用小技巧
weixin_30340819的博客
04-01 808
1，登录后默认自动选中My Objects 默认情况下，PLSQL Developer登录后，Brower里会选择All objects，如果你登录的用户是dba，要展开tables目录，正常情况都需要Wait几秒钟，而选择My Objects后响应速率则是以毫秒计算的。 Tools菜单 --> Object Brower Filters，会打开Brower Folder...
char、varchar、varchar2区别
WEBCODE
01-03 2902
char varchar varchar2 的区别 区别: 1．CHAR的长度是固定的，而VARCHAR2的长度是可以变化的， 比如，存储字符串“abc"，对于CHAR (20)，表示你存储的字符将占20个字节(包括17个空字符)，而同样的VARCHAR2 (20)则只占用3个字节的长度，20只是最大值，当你存储的字符小于20时，按实际长度存储。 2．CHAR的效率比VARCHAR2的效率稍...
oracle中char与varchar2的区别
haiross的专栏
03-09 2万+
oracle中char与varchar2的区别      1. CHAR的长度是固定的，而VARCHAR2的长度是可以变化的，比如，存储字符串“abc"，对于CHAR(20)，表示你存储的字符将占20个字节(包括17个空字符),在数据库中它是以空格占位的，而同样的VARCHAR2(20)则只占用3个字节的长度，20只是最大值，当你存储的字符小于20时，按实际长度存储。     
Oracle数据库的编码问题
jesse_lin的博客
12-30 267
一直没有意识到数据库的编码，这一次，碰到了一个很奇特的问题，varhcar2的字段存中文都是乱码，nvarhcar2用sql插入的中文也是乱码，几经折腾，才知道 Oracle数据库在安装的时候，是可以指定编码，其中有两种编码要指定 NLS_CHARACTERSET     US7ASCII NLS_NCHAR_CHARACTERSET     AL16UTF16   而碰到的数据库设...
VarChar和VarChar2的区别
热门推荐
sunny110的专栏
05-10 2万+
varchar       存放固定长度的字符数据，最长2000个字符。varchar2    存放可变长字符数据，最大长度为4000字符。，最大長度為4000字符。varchar     是标准sql提供的数据类型。varchar2  是oracle提供的独特的数据类型。oracle保证在任何版本中该数据类型向上和向下兼容，但不保证varchar。总之，如果想新版本的数据库兼
SQL ORACLE数据库操作
06-20
方便快速操作数据库，一个语句就搞定
ORACLE SQL DEVELOPER
03-13
ORACLE SQL DEVELOPER, oracle IDE连接 oracle , SQL SERVER, mysql 工具 1. 支持3种数据库连接 2. 操作方便 3. 免安装，直接使用
oracle sql 跟踪
12-20
通过监听应用程序,实时获取操作执行后的sql语句和sql语句绑定的参数
Oracle基本语法知识点
ych0204的博客
03-14 1343
解锁scott用户：alter user  scott account unlock 重新给scott用户修改密码：alter user scott identified by 123456   DML：增加   删除   修改   查询  （CRUD） 单行注释：   --   多行注释:  /*   */   查询 ：
Oracle数据库SQL基本操作
程序员小王的博客
03-10 1259
Oracle数据库SQL基本操作
mysql练习题
喀喀喀的博客
11-05 2902
（1）create创建练习 offices表结构 字段名 数据类型 主键 外键 非空 唯一 自增 officeCode INT(10) 是 否 是 是 否 city INT(11) 否 否 是 否 否 address VARCHAR(50) 否 否 否 否 否 country VARCHAR(50) 否 否 否 否 否 postalCode VARCHAR(25) 否 否 否 是 否 employees表结构
练习1：数据表的基本操作
匿名攻城狮
08-29 3346
已经学习了MySQL的各种操作，如创建表、添加各种约束、产看表结构、以及修改和删除表。给出一个实战演练，全面复习一下数据表的基本操作基础。 案例:创建数据库company，按照下面两个表给出的表结构在company数据库中创建两个数据表offices和employees，按照操作过程完成数据表的基本操作。 操作过程如下： (1)：登录MySQL。 打开windows命令行，输入登录用户名和密...
oracle的sql操作
奔代
11-23 171
导出oracle数据sql: exp 用户名/密码@ip地址:1521/数据库服务 file=导出路径 owner=用户名 导入oracle数据sql: imp 用户名/密码@ip地址:1521/数据库服务 file=导入路径 full=y
mysql数据库基本操作
鲲潜九渊，凤栖梧桐
05-11 347
mysql> create database company; //创建数据库company Query OK, 1 row affected (0.01 sec) mysql> use company; //使用数据库company Database changed mysql> create table offices(officescode int(10) not null uniq
Oracle基本SQL操作
哈哈哈哈的博客
11-26 174
1、创建用户并赋予权限（sqlplus / as sysdba） 创建用户 create user Scaffold identified by Scaffold; 创建一个表空间 create tablespace Scaffold datafile '/app/oracle/oradata/OFSAA/Scaffold.dbf' size 1000M; 表空间分配给用户 alter u...
Oracle 数据库操作
梦和远方的博客
01-05 1597
Oracle数据库操作的一些具体的赋权和语法应用. 赋查询权限： 说明：grant select on 表名 to 用户名; 例：grant select on T_REPORT_LOSS to aasi; 创建同义词： 说明：create or replace synonym 表名 for 用户名.表名; 例：create or replace synonym tb_wx_to_us...
Oracle（二）Oracle sql操作
crazywsp的博客
01-23 864
文章目录常用sql语句分为五类：Oracle建表（create table）Oracle 字段类型：create table语句添加约束：Oracle 查询（select）备份查询数据：Oracle 插入（insert into）oracle插入多条语句dual表说明insert 插入一个select的结果集Oracle 更新（update）Oracle 删除（delete）deletetruncatedrop 删除整个表，包括表结构truncate和delete区别：连接字符操作连接两个字符（concat
java操作oracle分页sql
最新发布
05-23
在Java中操作Oracle数据库进行分页查询，可以使用ROWNUM和子查询的方式实现。以下是一个简单的实现示例： ```java public List<User> getUsers(int pageNum, int pageSize) { List<User> userList = new ArrayList...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值