-----------文章出现的"MM"."AA"和MM.AA是一样的,因为有些版本兼容不一致-------------
--------------Orcal终端需要开启自动提交事务----------- SET autocommit
-- 建库
-- CREATE USER
ALTER SESSION SET "_ORACLE_SCRIPT" = TRUE;
CREATE USER MXY IDENTIFIED BY 123456;
GRANT CONNECT,RESOURCE TO MM;
alter user mxy quota unlimited on users;-- 为用户“MXY”在users表空间上设置配额(分配无限制的空间)
---------删除AB表----------
DROP table IF EXISTS MM.AB
----------创建AB表-----------
CREATE TABLE
"MM"."AB" (
"a" CHAR(33),
"b" DATE,
"c" FLOAT(33),
"d" FLOAT(33),
"e" VARCHAR2(33)
);
---------给字段和表添加注释-------------
COMMENT ON COLUMN "MM"."AB"."a" IS '哈哈1';
COMMENT ON COLUMN "MM"."AB"."b" IS '哈哈2';
COMMENT ON COLUMN "MM"."AB"."c" IS '哈哈1';
COMMENT ON COLUMN "MM"."AB"."d" IS '哈哈1';
COMMENT ON COLUMN "MM"."AB"."e" IS '哈哈1';
COMMENT ON TABLE "MM"."AB" IS ' 注释123';
---------------------------------插入数据----------有的版本可能需要对schema和表名加""--------
例如:------INSERT INTO "MM"."AB"(..............) VALUES (........)-----------
INSERT INTO MM.AB("a","b","c","d","e") VALUES ('我的祖国123456',TO_DATE('2022-09-07','YYYY-MM-DD HH24:MI:SS'),'1.23','4.21',TO_DATE('2021-07-09','YYYY-MM-DD HH24:MI:SS'));
-- 插入数据(不带schema)
INSERT INTO AB("a","b","c","d","e") VALUES ('我的祖国123456789',TO_DATE('2022-09-07','YYYY-MM-DD HH24:MI:SS'),'1.23','4.21',TO_DATE('2021-07-09','YYYY-MM-DD HH24:MI:SS'));
SELECT * from MM.AB;
-------------修改数据的时候注意,where条件后面尽量使用主键判断,用时间、小数这些类型有些版本是容易出错的,结果可能会出错-----------
UPDATE "MM"."AB" SET "a"='我的祖国1234454',"b"=TO_DATE('2022-02-07 00:00:00','YYYY-MM-DD HH24:MI:SS'),"c"='1.234',"d"='4.214',"e"=TO_DATE('2021-04-09 00:00:00','YYYY-MM-DD HH24:MI:SS') WHERE "a" = '我的祖国123445';
-- 更新数据(带schema)
UPDATE "MM"."AB" SET "a"='我的祖国1234454',"b"=TO_DATE('2022-02-07 00:00:00','YYYY-MM-DD HH24:MI:SS'),"c"='1.234',"d"='4.214',"e"=TO_DATE('2021-04-09 00:00:00','YYYY-MM-DD HH24:MI:SS') WHERE "a" = '我的祖国123445' AND "b" = TO_DATE('2022-09-07 00:00:00','YYYY-MM-DD HH24:MI:SS') AND "c" = '1.23' AND "d" = '4.21' AND "e" = TO_DATE('2021-07-09 00:00:00','YYYY-MM-DD HH24:MI:SS') AND ROWNUM = 1;
-- 更新数据(不带schema)
UPDATE "AB" SET "a"='我的祖国1234454',"b"=TO_DATE('2022-02-07 00:00:00','YYYY-MM-DD HH24:MI:SS'),"c"='1.234',"d"='4.214',"e"=TO_DATE('2021-04-09 00:00:00','YYYY-MM-DD HH24:MI:SS') WHERE "a" = '我的祖国123445' AND "b" = TO_DATE('2022-09-07 00:00:00','YYYY-MM-DD HH24:MI:SS') AND "c" = '1.23' AND "d" = '4.21' AND "e" = TO_DATE('2021-07-09 00:00:00','YYYY-MM-DD HH24:MI:SS') AND ROWNUM = 1;
--使用内置函数
SELECT count(*) FROM "MM"."AB";
--调试update 是否和精确度有关
DROP TABLE MM.BB;
SELECT * from MM.BB;
CREATE TABLE MM.BB
(
id NUMBER PRIMARY KEY,
id_data DATE,
id_name char(15),
id_phone varchar2(33)
);
INSERT into MM.BB(id,id_data,id_name,id_phone) VALUES(1,TO_DATE('2022-09-07','YYYY-MM-DD HH24:MI:SS'),'小明','11111111');
INSERT into MM.BB(id,id_data,id_name,id_phone) VALUES(2,TO_DATE('2022-09-07','YYYY-MM-DD HH24:MI:SS'),'小王','22222222');
UPDATE "MM"."BB" SET id_phone = '22000' where id=1;
-- Alter 删除某一列
ALTER TABLE MM.AA DROP COLUMN "d";
--Alter 增加某一列
ALTER TABLE "MM" "AA" ADD "c" FLOAT;
-------创建索引
CREATE
UNIQUE
INDEX
index_name1
ON
MM.AB(
"a"
);
-- 查看某个表的索引,表名需大写
SELECT
*
FROM
ALL_INDEXES
WHERE
TABLE_NAME = "MM"."AA";
-- 查看某个表哪些列有索引
SELECT
*
FROM
ALL_IND_COLUMNS
WHERE
TABLE_NAME = "MM"."AA";
-- 删除索引
DROP
INDEX
"INDEX_NAME1"
;
-- CREATE VIEW
CREATE
VIEW
gg
as
SELECT
*
FROM
"MM"."AA";
DROP
VIEW
GG;
-- CREATE SEQUENCE 创建序列
CREATE
SEQUENCE
S223
INCREMENT
BY
2
--步进 2
START
WITH
1
--起始值 1
MAXVALUE 20
--最大值 20
MINVALUE -20
--最小值 -20
-- 查询用户下有哪些序列
SELECT
*
FROM
USER_SEQUENCES;
alter
sequence
S223 increment
by
-2;
alter
sequence
S223 maxvalue 100;
--修改最大值为100
alter
sequence
S223 cache 30;
--修改为缓存,缓存块大小为30,意为一次缓存最多存储30个序列值到内存中
-- truncate命令,删除表名
TRUNCATE
TABLE
MM.AA;
-- DELETE
DELETE
MM.AA;
-- 删除表,清理脏数据
DROP
TABLE
MM.AA;
-- 删除库,清理脏数据
ALTER
SESSION
SET
"_ORACLE_SCRIPT"
=
TRUE
;
DROP
USER
MM
CASCADE
;