ORACLE数据库表和字段常用操作

表和字段常用

新增表

CREATE TABLE TABLE_NAME
(
    --数据主键
    ID VARCHAR2(32) NOT NULL,
    --姓名
    USER_NAME VARCHAR2(32),
    --年龄
    AGE NUMBER(10,0),
    --生日
    BIRTHDAY DATE,
    --创建时间
    CREATE_TIME TIMESTAMP,
		--指定主键
  	PRIMARY KEY (ID)
);

添加表注释

COMMENT ON TABLE TABLE_NAME IS '用户信息表';

新增字段

ALTER TABLE TABLE_NAME ADD NEW_FIELD_NAME VARCHAR2(32);

添加字段注释

COMMENT ON COLUMN TABLE_NAME.FIELD_NAME IS '字段注释';

修改字段数据类型

--修改字段数据类型
ALTER TABLE TABLE_NAME MODIFY FIELD_NAME VARCHAR2(64);

--修改字段数据类型+默认值+非空
ALTER TABLE TABLE_NAME MODIFY FIELD_NAME VARCHAR2(64) DEFAULT '默认值' NOT NULL;

重命名表名

ALTER TABLE TABLE_NAME RENAME TO NEW_TABLE_NAME;

重命名字段名

ALTER TABLE TABLE_NAME RENAME COLUMN FIELD_NAME TO NEW_FIELD_NAME;

删除字段

ALTER TABLE TABLE_NAME DROP COLUMN FIELD_NAME;

删除表

DROP TABLE TABLE_NAME;

插入表数据

--给表中所有列插入数据
INSERT INTO TABLE_NAME VALUES('字段值1','字段值2','字段值3');

所有列插入数据时，数据值要按列的顺序插入。

--给表中指定列插入数据
INSERT INTO TABLE_NAME (FIELD_NAME1,FIELD_NAME3) VALUES('字段值1','字段值3');

修改表数据

UPDATE TABLE_NAME SET FIELD_NAME1='字段值1',FIELD_NAME3='字段值3' WHERE ID = '';

删除表数据

DELETE FROM TABLE_NAME WHERE ID = '';

DELETE，意思为删除，占用资源。

优点：能够快速恢复，可以选择性删除。

缺点：删除慢，大批量的删除不建议使用。

清空表数据

TRUNCATE TABLE TABLE_NAME;

TRUNCATE，意思为截断表，能够不占用资源的全部删除表。

优点：速度快。

缺点：删除后不能恢复，也不能按条件删除。

备份表数据

CREATE TABLE TABLE_NAME_BACKUP AS SELECT * FROM TABLE_NAME;

相当于把查询的结果单独存一份。

常用SQL场景

查询是否有重复的数据

--根据身份证号查询是否有重复的数据（2条及以上）
SELECT ID_NO FROM TABLE_NAME GROUP BY ID_NO HAVING COUNT(ID_NO) > 1;

时间格式化

查询条件时间格式化

SELECT * FROM TABLE_NAME WHERE TO_CHAR(CREATE_TIME,'yyyy-mm-dd') = '2023-03-02';

查询结果时间格式化

SELECT TO_CHAR(C.CREATE_TIME,'yyyy-mm-dd hh24:mi:ss') FROM TABLE_NAME C;

修改时间格式化

UPDATE TABLE_NAME C SET C.CREATE_TIME = TO_DATE('2023-03-02','yyyy-mm-dd') WHERE C.ID = '';

高级操作

循环执行SQL语句

--有时候需要大量的数据，就可以使用循环执行的方式，快速插入多条数据。

BEGIN
	FOR i IN 1..1000
  	LOOP
    	INSERT INTO TABLE_NAME(ID,NAME,AGE,REGISTER_DATE) VALUES (SYS_GUID(),'张三',18,SYSDATE);
  	END LOOP;
END;

--在FOR中控制执行的次数，1000表示执行1000次。

ORACLE内置函数

产生唯一标识符SYS_GUID()

SELECT SYS_GUID() FROM DUAL();

返回长度为32位的字符串，包括0-9和大写A-F。

执行效果：

应用场景：SQL插入数据时，自动生成32位的数据主键
 

INSERT INTO TABLE_NAME(ID,NAME,AGE) VALUES (SYS_GUID(),'张三',18);

问题解决：如果出现乱码，则使用RAWTOHEX()函数

SELECT RAWTOHEX(SYS_GUID()) FROM DUAL();

执行效果：

获取当前日期时间SYSDATE

SELECT SYSDATE FROM DUAL();

执行效果：

INSERT INTO TABLE_NAME(ID,NAME,AGE,REGISTER_DATE) VALUES (SYS_GUID(),'张三',18,SYSDATE);