MySQL、Oracle 常用SQL：建表、建视图、数据增删改查、常用condition

2019一路前行

已于 2024-01-14 16:28:44 修改

阅读量1.6k

点赞数 18

于 2024-01-14 15:01:03 首次发布

本文链接：https://blog.csdn.net/weixin_42307069/article/details/135576426

版权

数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

1 MySQL、Oracle 建表语句整理

删除表：DROP TABLE TABLE_NAME;
建表：CREATE TABLE TABLE_NAME();
主键：PRIMARY KEY
不为空： NOT NULL
默认值：DEFAULT DEFAULT_VALUE

MySQL、Oracle 通用样例：

-- 删除表：表必须存在，才能删除表
DROP TABLE TABLE_NAME;
-- 建表：表存在，则不允许重复创建
CREATE TABLE TABLE_NAME (  
    COLUMN1 DATATYPE PRIMARY KEY,  
    COLUMN2 DATATYPE NOT NULL,  
    COLUMN3 DATATYPE DEFAULT '默认值'
);
-- MySQL、Oracle 修改表名
ALTER TABLE TABLE_NAME RENAME TO NEW_TABLE_NAME;
-- MySQL、Oracle 复制表
CREATE TABLE NEW_TABLE_NAME1 AS
SELECT * FROM TABLE_NAME;

注意：DATATYPE 是指数据库的数据类型，需要修改成具体数据类型。

1.1 MySQL 建表

INT类型自增：INT AUTO_INCREMENT
注释：COMMENT

参考案例：

-- 删除表：表必须存在，才能删除表
DROP TABLE TABLE_NAME;
-- 建表：表存在，则不允许重复创建
CREATE TABLE TABLE_NAME (
    COLUMN1 INT AUTO_INCREMENT PRIMARY KEY, 
    COLUMN2 VARCHAR(200) NOT NULL DEFAULT '默认值' COMMENT '字段注释',
    COLUMN3 VARCHAR(200)
) COMMENT '表的注释';
-- 修改表注释
ALTER TABLE TABLE_NAME COMMENT 'XXXX表';
-- 字段添加注释
ALTER TABLE TABLE_NAME MODIFY COLUMN3 VARCHAR(200) COMMENT '注释内容';

1.2 Oracle 建表

INT类型：不支持自增，Oracle 没有自增的数据类型。

注释：不能直接在建表语句里面写。
（1）表注释：
COMMENT ON TABLE TABLE_NAME IS ‘表的注释’;
（2）字段注释：
COMMENT ON COLUMN TABLE_NAME.COLUMN1 IS ‘字段注释’;

参考案例：

DROP TABLE TABLE_NAME; -- 表必须存在，才能删除表
CREATE TABLE TABLE_NAME (  
    COLUMN1 NUMBER PRIMARY KEY,
    COLUMN2 VARCHAR2(200) DEFAULT '默认值' NOT NULL,
    COLUMN3 VARCHAR2(200)
);
COMMENT ON TABLE TABLE_NAME IS '表的注释';  
COMMENT ON COLUMN TABLE_NAME.COLUMN1 IS '字段注释';

注意事项：
建表语句的字段同时设置：不为空和默认值，MySQL 和 Oracle 顺序不一样。
MySQL：不为空 + 默认值，如：NOT NULL DEFAULT ‘默认值’
Oracle：默认值 + 不为空，如：DEFAULT ‘默认值’ NOT NULL

1.3 补充

1.3.1 主键：新增、删除

MySQL、Oracle 的添加主键、删除主键，参考如下：

-- 添加主键
ALTER TABLE TABLE_NAME ADD PRIMARY KEY (COLUMN1);
-- 删除主键
ALTER TABLE TABLE_NAME DROP PRIMARY KEY;

1.3.2 字段：新增、修改、删除

MySQL、Oracle 的新增字段、修改字段数据类型、删除字段，参考如下：

-- 添加字段
ALTER TABLE TABLE_NAME ADD COLUMN1 DATATYPE;
-- 修改字段数据类型
ALTER TABLE TABLE_NAME MODIFY COLUMN1 NEW_DATATYPE;
-- 删除字段
ALTER TABLE TABLE_NAME DROP COLUMN COLUMN1;

MySQL 字段重命名

ALTER TABLE TABLE_NAME CHANGE COLUMN1 NEW_COLUMN1 DATATYPE;

Oracle 字段重命名

ALTER TABLE TABLE_NAME RENAME COLUMN COLUMN1 TO NEW_COLUMN1;

2 MySQL、Oracle 建视图

MySQL、Oracle 建视图

CREATE VIEW VIEW_NAME AS  
SELECT COLUMN1, COLUMN2 
FROM TABLE_NAME  
WHERE CONDITION;

MySQL、Oracle 创建视图、修改视图：

CREATE OR REPLACE VIEW VIEW_NAME AS  
SELECT COLUMN1, COLUMN2 
FROM TABLE_NAME  
WHERE CONDITION;

3 数据：增删改查

3.1 插入数据

3.1.1 MySQL、Oracle 插入一条数据

基本形式如下：

INSERT INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3, ...) 
VALUES (VALUE1, VALUE2, VALUE3, ...);

TABLE_NAME：插入数据的表名称
(COLUMN1, COLUMN2, …) ：表的列名称，指定要插入的列
(value1, value2, …) ：与列对应的值，它们的顺序和类型应该与列的定义相匹配

参考案例：

INSERT INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3) 
VALUES ('1','11','111');

注意：如果表中的某些列设置了默认值或者允许空值（NULL），那么在插入数据时可以省略这些列，会自动使用默认值或者空值填充这些列。

3.1.2 MySQL、Oracle 插入多条数据（语法不一样）

（1）MySQL 的插入多条数据，基本形式如下：

INSERT INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3, ...)  
VALUES  
(VALUE1A, VALUE2A, VALUE3A, ...),  
(VALUE1B, VALUE2B, VALUE3B, ...),  
...  
(VALUE1Z, VALUE2Z, VALUE3Z, ...);

参考案例：

INSERT INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3) 
VALUES 
('1','11','111'),
('2','22','222'),
('3','33','333');

（2）Oracle 的插入多条数据，基本形式如下：

INSERT ALL  
INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3, ...) VALUES (VALUE1A, VALUE2A, VALUE3A, ...)  
INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3, ...) VALUES (VALUE1B, VALUE2B, VALUE3B, ...)  
...  
INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3, ...) VALUES (VALUE1Z, VALUE2Z, VALUE3Z, ...)  
SELECT * FROM DUAL;

参考案例：

INSERT ALL  
INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3) VALUES ('1', '11', '111')  
INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3) VALUES ('2', '22', '222')  
INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3) VALUES ('3', '33', '333')  
SELECT * FROM DUAL;

3.1.3 MySQL、Oracle 将查询出来的数据插入到表中

基本形式如下：

INSERT INTO TABLE2 (COLUMN1, COLUMN2, COLUMN3)  
SELECT COLUMN1, COLUMN2, COLUMN3  
FROM TABLE1  
WHERE CONDITION;

参考案例：

INSERT INTO TABLE2 (COLUMN1, COLUMN2, COLUMN3)  
SELECT '4' AS COLUMN1, COLUMN2, COLUMN3  
FROM TABLE_NAME
WHERE COLUMN1='1';

注意：AS COLUMN1 可以不写，但是为了阅读性，最好还是加上。

当目标表TABLE2 和来源表TABLE1 的结构一致 (相同的列、列的顺序和数据类型一致)，插入语句可以不写列，基本形式如下：

INSERT INTO TABLE2
SELECT * 
FROM TABLE1  
WHERE CONDITION;

3.2 删除数据

3.2.1 删除所有数据：TRUNCATE、DELETE

1、MySQL、Oracle 删除所有数据，可以使用 TRUNCATE

TRUNCATE 不允许带条件，直接删除表所有数据并释放空间。比 DELETE 操作更快。

TRUNCATE TABLE TABLE_NAME;

2、MySQL、Oracle 删除所有数据，使用 DELETE 操作

DELETE 可以带条件删除，是逐行删除的，比 TRUNCATE 操作慢。

DELETE FROM TABLE_NAME;

注意：数据量特别大时，全部删除，建议使用 TRUNCATE。

3.2.2 删除符合条件的数据：DELETE

MySQL、Oracle 的语法一致，如下：

DELETE FROM TABLE_NAME WHERE CONDITION;
DELETE FROM TABLE_NAME WHERE CONDITION1 AND CONDITION2;
DELETE FROM TABLE_NAME WHERE CONDITION1 OR CONDITION2;
-- 参考案例：
DELETE FROM TABLE_NAME WHERE COLUMN1='1';

3.3 修改数据：UPDATE

MySQL、Oracle 的语法一致，如下：

UPDATE TABLE_NAME SET COLUMN1 = VALUE1, COLUMN2 = VALUE2 WHERE CONDITION;
-- 参考案例：
UPDATE TABLE_NAME SET COLUMN2 = 'AAA' WHERE COLUMN1='2';

column1 和 column2 是要更新列的名称，value1 和 value2 是要设置的新值。

3.4 数据查询：SELECT

MySQL、Oracle 的语法一致，如下：

-- 查询表所有列的数据
SELECT * FROM TABLE_NAME WHERE CONDITION;
-- 参考案例：
SELECT * FROM TABLE_NAME WHERE COLUMN1='2';

-- 查询具体列的数据
SELECT COLUMN1, COLUMN2, ... FROM TABLE_NAME WHERE CONDITION;
-- 参考案例：
SELECT COLUMN1,COLUMN2 FROM TABLE_NAME WHERE COLUMN1='2';

查询所有列，可以使用星号（*）作为通配符。

4 MySQL、Oracle 常用 condition 条件整理

1、等于（=）：用于匹配等于指定值的记录。
2、不等于（<> 或 !=）：用于匹配不等于指定值的记录。
3、大于（>）、小于（<）、大于等于（>=）、小于等于（<=）：用于匹配指定范围内的值。

SELECT * FROM TABLE_NAME WHERE COLUMN1 = VALUE;
SELECT * FROM TABLE_NAME WHERE COLUMN1 != VALUE;
SELECT * FROM table_name WHERE COLUMN1 <> VALUE;
SELECT * FROM TABLE_NAME WHERE COLUMN1 > VALUE1 AND COLUMN2 < VALUE2;
-- 案例：
SELECT * FROM TABLE_NAME WHERE COLUMN1 = '1';
SELECT * FROM TABLE_NAME WHERE COLUMN1 != '1';
SELECT * FROM TABLE_NAME WHERE COLUMN1 <> '1';
SELECT * FROM TABLE_NAME WHERE COLUMN1 > '1' AND COLUMN2 < '999';

4、LIKE：用于模糊匹配字符串值。可以使用通配符来指定模式。

SELECT * FROM TABLE_NAME WHERE COLUMN1 LIKE 'PATTERN%'; -- 查询：以 PATTERN 开头的数据 
SELECT * FROM TABLE_NAME WHERE COLUMN1 LIKE '%PATTERN%'; -- 查询：包含 PATTERN 的数据
-- 案例：
SELECT * FROM TABLE_NAME WHERE COLUMN1 LIKE '2015%';  -- 以 2015开头的所有数据，如：20150101
SELECT * FROM TABLE_NAME WHERE COLUMN1 LIKE '%01-2%'; -- 包含 01-2 的数据，如：2015-01-20

5、IN：用于匹配指定的多个值之一。

SELECT * FROM TABLE_NAME WHERE COLUMN1 IN (VALUE1, VALUE2, ...);
-- 案例-返回：COLUMN1 等于 1、2、3 的数据 
SELECT * FROM TABLE_NAME WHERE COLUMN1 IN ('1','2','3');

6、BETWEEN：用于匹配指定范围内的值。

SELECT * FROM TABLE_NAME WHERE COLUMN1 BETWEEN VALUE1 AND VALUE2;
-- 案例-返回：COLUMN1 在 1 和 99 之间的数据(包含1、99)
SELECT * FROM TABLE_NAME WHERE COLUMN1 BETWEEN '1' AND '999';

7、IS NULL：用于匹配空值的数据。
8、IS NOT NULL：用于匹配不为空的数据。

SELECT * FROM TABLE_NAME WHERE COLUMN1 IS NULL;
SELECT * FROM TABLE_NAME WHERE COLUMN1 IS NOT NULL;

9、AND、OR：用于组合多个条件，使用AND运算符表示同时满足多个条件，使用OR运算符表示满足任一条件。

SELECT * FROM TABLE_NAME WHERE COLUMN1 = VALUE1 AND COLUMN2 = VALUE2;
SELECT * FROM TABLE_NAME WHERE COLUMN1 = VALUE1 OR COLUMN2 = VALUE2;

5 其他补充

5.1 MySQL、Oracle 的数据排序：ORDER BY … ASC|DESC

MySQL、Oracle 的 ORDER BY 子句可以指定一个或多个列进行排序，
并且可以指定升序（ASC）或降序（DESC）排序。

SELECT COLUMN1, COLUMN2, ...
FROM TABLE_NAME
ORDER BY COLUMN1, COLUMN2, ... ASC|DESC;
-- 默认升序
SELECT COLUMN1, COLUMN2, ...
FROM TABLE_NAME
ORDER BY COLUMN1;

注意：如果不指定排序方向（ASC或DESC），则默认情况下都是升序排序（ASC）

5.2 MySQL、Oracle 查询数量的控制(分页): LIMIT、ROWNUM

1、MySQL 的 LIMIT 子句用于限制查询结果返回的数据数量。

LIMIT 通常与 SELECT 语句一起使用，以指定从数据库表中检索的记录的数量。
（1）LIMIT count：只返回最多count条记录。返回结果集的前 count 条数据。
（2）LIMIT offset, count：从指定的offset位置开始返回最多count条记录。
例如，LIMIT 5, 10 将返回结果集中的第6条到第15条记录（索引从0开始）。

参考案例：

-- 返回：1条数据
SELECT * FROM TABLE_NAME LIMIT 1; 
-- 返回：前5条数据
SELECT * FROM TABLE_NAME LIMIT 5; 
-- 返回：从第2条开始的2条数据，即 (第2条、第3条)
SELECT * FROM TABLE_NAME LIMIT 1,2;
-- 想每页显示10条记录，并查询第3页的内容，可以使用以下查询：
SELECT * FROM TABLE_NAME LIMIT 20, 10;

2、Oracle 的 ROWNUM 用于限制返回行数的伪列。

ROWNUM 是在数据行被检索出来后才分配的，这意味着当行被检索出来时，ROWNUM 的值已经确定了。

通常使用 ROWNUM <= n 来限制返回的行数，而不是使用 ROWNUM > n 来筛选行。

因此，当你尝试使用类似 ROWNUM > 1 这样的条件时，由于在行被检索出来时 ROWNUM 的值已经确定，
所以不会有任何行可以满足 ROWNUM > 1 的条件。

-- 返回：第 1 条数据
SELECT * FROM TABLE_NAME WHERE ROWNUM = 1;
-- 返回：前 10 条数据
SELECT * FROM TABLE_NAME WHERE ROWNUM <=10;
-- 没有返回数据
SELECT * FROM TABLE_NAME WHERE ROWNUM > 1;
SELECT * FROM TABLE_NAME WHERE ROWNUM > 1 AND ROWNUM <4;

注意：使用类型 ROWNUM > 1 AND ROWNUM < 4 的查询，结果将不会返回任何行，
因为对于任何一行，ROWNUM的值要么大于1，要么小于5，但不能同时满足这两个条件。

将 ROWNUM 伪列放到子查询的列中，达到分页效果
参考案例：

SELECT * FROM (
  SELECT T.*, ROWNUM ROW_ID  
  FROM TABLE_NAME T  
)
WHERE ROW_ID > START_OFFSET AND ROW_ID <= END_OFFSET;

-- 返回：第2条、第3条 数据
SELECT * FROM (  
  SELECT T.*, ROWNUM ROW_ID  
  FROM TABLE_NAME T  
)  
WHERE ROW_ID > 1 AND ROW_ID <= 3;
-- 想每页显示10条记录，并查询第3页的内容，可以使用以下查询：
SELECT * FROM (  
  SELECT T.*, ROWNUM ROW_ID  
  FROM TABLE_NAME T  
)  
WHERE ROW_ID > 20 AND ROW_ID <= 30;

5.3 CASE WHEN：用于条件逻辑的判断

MySQL、Oracle 的 CASE WHEN 语句基本结构，如下：

CASE  
    WHEN condition1 THEN result1  
    WHEN condition2 THEN result2  
    ...  
    ELSE result  
END;

当符合条件(condition) 则返回：结果(result)。

参考案例:

-- 查询列的逻辑判断处理
SELECT COLUMN1, COLUMN2,   
CASE   
    WHEN COLUMN1='1' THEN 'RESULT 1'  
    WHEN COLUMN1='2' THEN 'RESULT 2'  
    ELSE 'RESULT 3'  
END AS RESULT_COLUMN   
FROM TABLE_NAME;

-- 条件的逻辑判断处理
SELECT COLUMN1, COLUMN2, COLUMN3
FROM TABLE_NAME  
WHERE COLUMN1 = '1'  
AND CASE  
	WHEN COLUMN3 > 10 THEN 1  
	WHEN COLUMN3 < 5 THEN 2  
	ELSE 3 
END = 1;