Oracle|DM 常用|不常用 SQL大口袋

在Oracle和达梦数据库中，以下是一些常用和不常用的SQL语句示例。

常用SQL语句:

1. 创建表:

-- Oracle
CREATE TABLE users (
  id NUMBER PRIMARY KEY,
  username VARCHAR2(50) NOT NULL,
  password VARCHAR2(50) NOT NULL
);
 
-- 达梦
CREATE TABLE "USERS" (
  "ID" INT PRIMARY KEY,
  "USERNAME" NVARCHAR(50) NOT NULL,
  "PASSWORD" NVARCHAR(50) NOT NULL
);

2. 插入数据:

-- Oracle
INSERT INTO users (id, username, password) VALUES (1, 'user1', 'pass1');
 
-- 达梦
INSERT INTO "USERS" ("ID", "USERNAME", "PASSWORD") VALUES (1, 'user1', 'pass1');

3. 查询数据:

-- Oracle
SELECT * FROM users;
 
-- 达梦
SELECT * FROM "USERS";

4. 更新数据:

-- Oracle
UPDATE users SET password = 'new_pass' WHERE id = 1;
 
-- 达梦
UPDATE "USERS" SET "PASSWORD" = 'new_pass' WHERE "ID" = 1;

5. 删除数据:

-- Oracle
DELETE FROM users WHERE id = 1;
 
-- 达梦
DELETE FROM "USERS" WHERE "ID" = 1;

不常用SQL语句:

1. 创建索引:

-- Oracle
CREATE INDEX idx_username ON users(username);
 
-- 达梦
CREATE INDEX "IDX_USERNAME" ON "USERS" ("USERNAME");

2. 创建视图:

-- Oracle
CREATE VIEW user_view AS SELECT id, username FROM users;
 
-- 达梦
CREATE VIEW "USER_VIEW" AS SELECT "ID", "USERNAME" FROM "USERS";

3. 创建存储过程:

-- Oracle
CREATE PROCEDURE add_user (p_id IN NUMBER, p_username IN VARCHAR2, p_password IN VARCHAR2) AS
BEGIN
  INSERT INTO users (id, username, password) VALUES (p_id, p_username, p_password);
END;
 
-- 达梦
CREATE PROCEDURE "ADD_USER" (p_id IN INT, p_username IN NVARCHAR, p_password IN NVARCHAR) AS
BEGIN
  INSERT INTO "USERS" ("ID", "USERNAME", "PASSWORD") VALUES (p_id, p_username, p_password);
END;

这些示例展示了如何在Oracle和达梦数据库中执行基本的SQL操作。实际使用时，需要根据具体的数据库版本和需求进行相应的调整。