Oracle常规操作

合集 - Oracle(6)

1. Oracle用户和权限2023-08-082.00.Oracle 11g安装2023-08-083.Oracle表结构&数据类型&约束2023-08-08

4.Oracle常规操作2023-08-09

5.Oracle索引&约束2023-08-096.Spring Boot对接Oracle数据库2023-08-11

收起

Oracle常规操作

创建表

CREATE TABLE t_user(
    id NUMBER(4),
    username VARCHAR2(20) NOT NULL,
    gender CHAR(1) DEFAULT 'M',
    birth DATE
);

插入数据

-- 插入指定的列
INSERT INTO t_user (id,username,gender)
VALUES(1,'pp0419','M');
-- 使用默认日期格式插入记录
INSERT INTO t_user (id,username,gender,birth)
VALUES(2,'pp0419','M','01-SEP-89');
-- 自定义日期格式插入数据
INSERT INTO t_user (id,username,gender,birth)
VALUES(1001,'盼盼','M',TO_DATE('1999-04-19','YYYY-MM-DD'));

更新数据

-- 更改id为2的username为UPDATE_panpan
UPDATE t_user SET username = 'UPDATE_panpan' WHERE id = 2;
-- 查看更新结果
SELECT * FROM T_USER;

删除数据

-- 删除id为1的用户
DELETE FROM T_USER WHERE id = 1;
-- 删除用户表中birth为空的记录
DELETE T_USER WHERE birth IS NULL;
-- 查看更新结果
SELECT * FROM T_USER;

创建Account表

表结构如下：

sql如下：

CREATE TABLE Account (
  id NUMBER, -- 账户ID
  recommender_id NUMBER, -- 推荐人ID
  login_name VARCHAR2(100), -- 登录名
  login_password VARCHAR2(100), -- 登录密码
  status VARCHAR2(20), -- 账户状态
  create_date DATE, -- 创建日期
  pause_date DATE, -- 暂停日期
  close_date DATE, -- 关闭日期
  real_name VARCHAR2(100), -- 真实姓名
  idcard_no VARCHAR2(20), -- 身份证号码
  birthdate DATE, -- 出生日期
  gender VARCHAR2(10), -- 性别
  occupation VARCHAR2(100), -- 职业
  telephone VARCHAR2(20), -- 电话号码
  email VARCHAR2(100), -- 电子邮件
  mailaddress VARCHAR2(200), -- 邮寄地址
  zipcode VARCHAR2(10), -- 邮政编码
  qq VARCHAR2(20), -- QQ号码
  last_login_time DATE, -- 最后登录时间
  last_login_ip VARCHAR2(50) -- 最后登录IP
);

修改账务账户表：

1、修改account表的表名为t_account;

RENAME ACCOUNT TO t_account;

2、向t_account表增加一列bak，其数据类型为varchar2，长度为50;

ALTER TABLE t_account ADD bak VARCHAR2(50);

3、修改t_account表中bak列的长度为40，并增加默认值的设置，默认为“login”

4、删除t_account表中的bak列

ALTER TABLE t_account DROP COLUMN bak ;

常用日期转换

TO_CAHR()

-- 按格式显示系统时间
SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss') FROM DUAL;
-- 按指定格式显示时间
SELECT TO_CHAR(hiredate,'fmDD "of" MONTH YYYY') "Date Hired" FROM emp;
-- 按指定格式显示时间
SELECT ename,TO_CHAR(hiredate,''yyyy-mm-dd') as "HIREDATE",
    TO_CHAR(hiredate,'yyyy"年"mm"月"dd') as "REVIRE" FROM emp;

常用日期函数

案例：查询一个订单从下单开始到启运需要多长时间，以月为单位

以下是本案例sql：

-- 查询一个订单从下单开始到启运需要多长时间，以月为单位
CREATE TABLE ord(
	custid NUMBER(4),
	orderdate DATE,
	shipdate DATE
);
RENAME ord to t_ord;
INSERT INTO t_ord(custid,orderdate,shipdate) VALUES(1001,'12-APR-98','10-JUN-98');
INSERT INTO t_ord(custid,orderdate,shipdate) VALUES(1001,'1-APR-98','2-JUN-98');
INSERT INTO t_ord(custid,orderdate,shipdate) VALUES(1001,'3-APR-98','4-JUN-98');
-- 查询一个订单，从开始下单到启运需要多长时间
SELECT custid,orderdate,shipdate,ROUND(MONTHS_BETWEEN(shipdate, orderdate)) as "TIME TAKEN" FROM T_ORD;

常用空值函数

-- 创建cutomer
CREATE TABLE t_cutomer(
	cust_id NUMBER(4), -- 客户编码
	cname VARCHAR2(25), -- 客户姓名
	birthdate DATE,-- 客户生日
	account NUMBER-- 客户账单余额
);
-- 列出客户信息，当birthdate为null时，将birthdate列的数据显示为"not available",to_chat()转换的时候出现null也进行转换
SELECT 
cust_id,cname,
NVL(TO_CHAR(birthdate,'yyyy-mm-dd'), 'not available'),
NVL(TO_CHAR(account), 'no account') 
FROM t_cutomer;
-- 列出生日在指定年份的客户
SELECT * FROM t_cutomer WHERE TO_CHAR(birthdate,'yyyy') = '1999';