第2章 SQL 基础（MySQL 对照速成） ​

-- 达梦：创建业务用户（会自动创建同名模式 SHOP）
CREATE USER SHOP IDENTIFIED BY "Shop_Pass123";
GRANT RESOURCE TO SHOP;            -- 拥有在本模式下建表等对象权限
GRANT CONNECT TO SHOP;             -- 拥有登录会话的基础权限

-- 对照 MySQL
CREATE DATABASE shop;
CREATE USER 'shop'@'%' IDENTIFIED BY 'xxx';
GRANT ALL PRIVILEGES ON shop.* TO 'shop'@'%';
FLUSH PRIVILEGES;

-- MySQL 原写法（回忆用）
CREATE TABLE t_user (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL COMMENT '用户名',
  status TINYINT NOT NULL DEFAULT 0 COMMENT '状态 0禁用 1启用',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 达梦等价写法
CREATE TABLE T_USER (
  ID BIGINT IDENTITY(1, 1) PRIMARY KEY,          -- 自增：IDENTITY(种子, 增量)
  USERNAME VARCHAR(50) NOT NULL,
  STATUS SMALLINT DEFAULT 0 NOT NULL,
  CREATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  CONSTRAINT UK_USERNAME UNIQUE (USERNAME)
);
COMMENT ON TABLE T_USER IS '用户表';               -- 注释是独立语句
COMMENT ON COLUMN T_USER.USERNAME IS '用户名';
COMMENT ON COLUMN T_USER.STATUS IS '状态 0禁用 1启用';

ALTER TABLE T_USER ADD COLUMN PHONE VARCHAR(20);      -- MySQL: ADD COLUMN 一样可写
ALTER TABLE T_USER MODIFY COLUMN USERNAME VARCHAR(100) NOT NULL;  -- ✅ 达梦支持 MODIFY（也支持 Oracle 的 ALTER COLUMN）
ALTER TABLE T_USER DROP COLUMN PHONE;
RENAME T_USER TO T_ACCOUNT;                           -- MySQL: RENAME TABLE a TO b
DROP TABLE T_ACCOUNT PURGE;                           -- 达梦默认删除进「回收站」，PURGE 彻底删
TRUNCATE TABLE T_USER;                                -- 与 MySQL 同

CREATE SEQUENCE SEQ_T_USER
  START WITH 1
  INCREMENT BY 1
  CACHE 20;                       -- 高速缓存，MySQL 无此概念，性能高

INSERT INTO T_USER (ID, USERNAME) VALUES (SEQ_T_USER.NEXTVAL, 'tom');
SELECT SEQ_T_USER.CURRVAL FROM DUAL;    -- 当前值（本会话内）
SELECT SEQ_T_USER.NEXTVAL FROM DUAL;    -- 取下一个

-- 单行插入：完全同 MySQL
INSERT INTO T_USER (USERNAME, STATUS) VALUES ('amy', 1);
INSERT INTO T_USER VALUES ('bo', 0, SYSDATE);   -- 全列可省列名（不推荐）

-- 多行插入：MySQL 的 VALUES,(...) 语法达梦同样支持
INSERT INTO T_USER (USERNAME) VALUES ('c'), ('d');
-- 达梦/Oracle 味的批量写法
INSERT ALL
  INTO T_USER (USERNAME) VALUES ('e')
  INTO T_USER (USERNAME) VALUES ('f')
SELECT 1 FROM DUAL;

-- 插 NULL：达梦严格区分 '' 与 NULL。MySQL 里 NOT NULL 列插 '' 能混过去，
-- 达梦默认 CASE_SENSITIVE 库中 '' 就是 NULL（Oracle 语义），VARCHAR 空串判断要写 IS NULL
UPDATE T_USER SET STATUS = 1, CREATE_TIME = SYSDATE WHERE USERNAME = 'amy';
DELETE FROM T_USER WHERE ID = 3;

-- MySQL 特色更新语法达梦不支持：
-- UPDATE t SET a=a+1 ORDER BY id LIMIT 1;   ❌ 达梦无 UPDATE ... ORDER BY ... LIMIT
-- 改写成子查询定位：
UPDATE T_USER SET STATUS = STATUS + 1
 WHERE ID IN (SELECT ID FROM T_USER WHERE STATUS = 0 AND ROWNUM <= 1);

-- ① MySQL 味：LIMIT（达梦原生就支持，迁移成本最低）
SELECT * FROM T_USER ORDER BY ID LIMIT 10 OFFSET 20;   -- 第 3 页，每页 10 条
SELECT * FROM T_USER ORDER BY ID LIMIT 20, 10;          -- 同样效果：LIMIT 偏移, 行数

-- ② Oracle 味：ROWNUM 伪列（老项目/老手册常见）
SELECT * FROM (
  SELECT A.*, ROWNUM RN FROM (SELECT * FROM T_USER ORDER BY ID) A WHERE ROWNUM <= 30
) WHERE RN > 20;

-- ③ SQL 标准：OFFSET-FETCH（DM8 支持，跨库可移植性最好）
SELECT * FROM T_USER ORDER BY ID OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- JOIN / 子查询 / GROUP BY 与 MySQL 完全同语法
SELECT U.STATUS, COUNT(*) CNT
  FROM T_USER U JOIN T_ORDER O ON O.USER_ID = U.ID
 WHERE U.CREATE_TIME >= DATE'2026-01-01'      -- ANSI 日期字面量，达梦支持
 GROUP BY U.STATUS
HAVING COUNT(*) > 10
 ORDER BY CNT DESC;

SELECT 'A' X FROM DUAL UNION ALL SELECT 'B' X FROM DUAL;   -- DUAL 照用
SELECT 1;                                                  -- 不写 FROM 也支持（MySQL 习惯可保留）

-- MySQL 原版
CREATE TABLE `t_product` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` VARCHAR(100) NOT NULL COMMENT '商品名',
  `price` DECIMAL(10,2) DEFAULT 0.00 COMMENT '售价',
  `status` TINYINT DEFAULT 1 COMMENT '1上架 0下架',
  `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';

-- 达梦版
CREATE TABLE T_PRODUCT (
  ID BIGINT IDENTITY(1,1) PRIMARY KEY,
  NAME VARCHAR(100) NOT NULL,
  PRICE DECIMAL(10,2) DEFAULT 0.00,
  STATUS SMALLINT DEFAULT 1,
  CREATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT UK_NAME UNIQUE (NAME)          -- ②③④⑥⑩ 全在里面
);
COMMENT ON TABLE T_PRODUCT IS '商品表';
COMMENT ON COLUMN T_PRODUCT.NAME IS '商品名';

INSERT INTO T_PRODUCT (NAME, PRICE) VALUES ('Java实战', 99.00);   -- 不写 ID，IDENTITY 自动给 1
SELECT @@IDENTITY FROM DUAL;                                      -- 1，本会话最后一次的自增值（兼容 SQLServer 写法）；严谨场合直接查 SCOPE_IDENTITY()
SELECT COUNT(*) FROM T_PRODUCT WHERE NAME='Java实战';             -- 1，唯一键可命中

CREATE TABLE T_ORDER2 (ID BIGINT PRIMARY KEY, AMOUNT DECIMAL(10,2));
CREATE SEQUENCE SEQ_T_ORDER2 START WITH 1 INCREMENT BY 1;

INSERT INTO T_ORDER2 VALUES (SEQ_T_ORDER2.NEXTVAL, 10.00);
INSERT INTO T_ORDER2 VALUES (SEQ_T_ORDER2.NEXTVAL, 20.00);
INSERT INTO T_ORDER2 VALUES (SEQ_T_ORDER2.NEXTVAL, 30.00);
INSERT INTO T_ORDER2 VALUES (SEQ_T_ORDER2.NEXTVAL, 40.00);
INSERT INTO T_ORDER2 VALUES (SEQ_T_ORDER2.NEXTVAL, 50.00);
INSERT INTO T_ORDER2 VALUES (SEQ_T_ORDER2.NEXTVAL, 60.00);
INSERT INTO T_ORDER2 VALUES (SEQ_T_ORDER2.NEXTVAL, 70.00);
COMMIT;
SELECT * FROM T_ORDER2 ORDER BY ID;      -- 输入对账底稿：ID 1~7，金额 10~70

-- 写法① LIMIT（MySQL 手感，达梦直接支持）
SELECT ID, AMOUNT FROM T_ORDER2 ORDER BY ID LIMIT 3 OFFSET 3;

-- 写法② ROWNUM 经典两层（Oracle 老项目里最常见的形态）
SELECT * FROM (
  SELECT A.*, ROWNUM RN FROM (
    SELECT ID, AMOUNT FROM T_ORDER2 ORDER BY ID
  ) A WHERE ROWNUM <= 6          -- 6 = offset+limit = 3+3
) WHERE RN > 3;                  -- 跳过前 3 行

-- 写法③ OFFSET..FETCH（SQL 标准写法，未来移植性最好）
SELECT ID, AMOUNT FROM T_ORDER2 ORDER BY ID
OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;

CREATE TABLE T_REG (ID INT PRIMARY KEY, REG_TIME TIMESTAMP);
INSERT INTO T_REG VALUES (1, TIMESTAMP'2026-02-28 23:59:59');   -- 边界：2月最后一秒
INSERT INTO T_REG VALUES (2, TIMESTAMP'2026-03-01 00:00:00');   -- 边界：3月第一秒
INSERT INTO T_REG VALUES (3, TIMESTAMP'2026-03-15 12:00:00');
INSERT INTO T_REG VALUES (4, TIMESTAMP'2026-04-01 00:00:00');   -- 边界：4月第一秒
COMMIT;

-- MySQL 习惯：DATE_FORMAT(t,'%Y-%m')='2026-03'
SELECT DATE_FORMAT(REG_TIME, '%Y-%m') FROM T_REG;   -- 达梦不报错（部分版本），但 '%Y-%m' 不会按 MySQL 解释！

-- 达梦写法①（推荐，索引可用）：区间而非函数
SELECT ID FROM T_REG
 WHERE REG_TIME >= DATE'2026-03-01' AND REG_TIME < DATE'2026-04-01'
 ORDER BY ID;                                       -- → 2, 3

-- 达梦写法②（TO_CHAR 风格匹配，全表扫描，只当备选）
SELECT ID FROM T_REG WHERE TO_CHAR(REG_TIME, 'YYYY-MM') = '2026-03' ORDER BY ID;  -- → 2, 3

SELECT USERNAME, NVL2(USERNAME, '非空', '是NULL') FLAG FROM T_USER;   -- 刚插的那行 FLAG = 是NULL
SELECT COUNT(*) FROM T_USER WHERE USERNAME IS NULL;                    -- → 1，这才是正确的判空姿势