likes
comments
collection
share

Oracle SQL 语法基础

作者站长头像
站长
· 阅读数 36

基本概念

  1. SQL：Structured Query Language

    结构化查询语言，是用于访问和处理数据库的标准的计算机语言，不同数据库间语言存在差异

  2. SQL 分为 DML、DCL、DQL、DDL 等

    • DQL，Data Query Language，数据库查询语言，即查询语句
    • DML，Data Manipulation Language，数据库操纵语言，即增加、删除、更新
    • DDL，Data Definition Language，数据库定义语言，即建库、建表等
    • DCL，Data Control Language，数据库控制语言，即角色、权限控制相关等

  3. Oracle Database，甲骨文公司提供的通用数据库，是当前最流行的数据库之一

  4. 数据文件（dbf）

    数据存储在数据库中，终究是存储在物理磁盘之上，数据文件就是数据库的物理存储单位

  5. 表空间（tablespace）

    表空间是数据库对物理存储上相关数据文件的映射

    一个数据库被划分为一到若干个表空间，至少有一个表空间

    每个表空间由磁盘上一个或多个数据文件组成

    一个数据文件只能属于一个表空间

  6. 用户（user）和模式（schema）

    用户是用来连接数据库和访问数据库对象的，持有系统的权限及资源

    schema 是数据对象的集合，包含了表、函数、包等等对象

    Oracle 中的 schema 就是指一个用户下所有对象的集合

    一个用户一般对应一个 schema，该用户的 schema 名等于用户名，并作为该用户缺省 schema

建库建表

  1. 创建用户

    create user 用户名 identified by 密码;

  2. 查看表空间信息

    SELECT
	*
FROM
	Dba_Tablespaces;

  3. 查看表空间配置文件信息

    SELECT
	*
FROM
	Dba_data_files;

  4. 创建表空间

    CREATE TABLESPACE 表空间名 DATAFILE 配置文件存放位置 SIZE 配置文件大小

  5. 指定默认表空间

    # 设置指定用户的默认表空间
ALTER USER 用户名 DEFAULT TABLESPACE 表空间名;

# 设置数据库的默认临时表空间
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_tbs_name;

  6. 查看默认表空间

    SELECT
	default_tablespace
FROM
	user_users;

  7. 给用户授权

    GRANT DBA TO 用户名

  8. 创建数据表

    CREATE TABLE USER(
	ID NUMBER,
    NAME VARCHAR2(32),
    AGE NUMBER,
    BIRTH DATE
)

    几种字符串类型：

    • CHAR(n)

      长度固定，不足自动补空格，汉字占 2 字节，字母 1 字节，n 为字节数

    • VARCHAR(n)

      可变长度，汉字占 2 字节，字母 1 字节，n 为字节数

    • VARCHAR2(n)

      非工业标准，保证版本兼容，占用与字符集相关，空串处理为 null，n 为字节数，最大 4000

    • NVARCHAR(n)

      可变长度，Unicode 编码，n 为字符数

    • NVARCHAR2

      汉字和字母统一占用两个字节

  9. 设置自增 ID

    用来获取 ID 的自增序列

    CREATE SEQUENCE SQ_USER_ID
MINVALUE 1 MAXVALUE 99999999
INCREMENT BY 1 START WITH 1

    自动填充 ID 的触发器

    CREATE OR REPLACE TRIGGER USER
    BEFORE INSERT
    ON USER
    FOR EACH ROW
BEGIN
    SELECT SQ_USER_ID.NEXTVAL INTO :NEW.ID FROM DUAL;
END;

增删改查

  1. insert

    INSERT INTO table_name VALUES(value_1, value_2, value_3);
INSERT INTO table_name(column_1, column_3) VALUES(value_1, value_3);

  2. delete

    DELETE FROM 
	table_name 
WHERE 
	column_1='xxx';

  3. update

    UPDATE 
	table_name 
SET 
	column_1="xxx", 
	column_2="xxx" 
WHERE column_3="xxx"

  4. select

    SELECT
  column_1, 
  column_2
FROM
  table_name;

高级查询

  1. where

    表示查询条件，与 and/or 配合使用

    SELECT 
	*
FROM
	table_name
WHERE 
	(column_1='xxx'
	AND column_2='xxx')
	OR column_3='xxx';

  2. distinct

    用于获得唯一性记录，可以限制多个列

    SELECT 
	DISTINCT column_1
FROM
	table_name

  3. order by

    将查询的结果，按照一定的顺序进行排序，ASC 升序（默认），DESC 降序

    如果是字符串，则按字母表顺序排序

    order by 一般用在 SQL 语句的最后

    SELECT 
	column_1, column_2
FROM
	table_name
ORDER BY
    column_1 DESC

  4. group by

    对记录集合进行分组

    group by 后的字段表示按这些字段分组，按照这些字段组成一条记录，重复的属于同一组，最后返回这些分组

    SELECT
	name, age, sex
WHERE
	sex='男'
GROUP BY
	name, age, sex

    分组之后 select 语句的真实操作目标为各个分组数据，每次循环处理的也是各个分组，而不是单条记录

  5. having

    having 多用于 group by 后对分组的筛选，功能类似 where，但是只能筛选分组后的查询结果中的字段

    例如，查看 person 表的男生中每个名字的同名次数，并且展示次数大于 2 的分组

    SELECT
	name, COUNT(*) num
FROM
	person
WHERE
	sex='男'
GROUP BY
	name
having 
	num > 2

  6. 嵌套查询

    子查询是嵌套在查询语句中的查询语句，是完整的查询语句

    子查询的结果集除了可以作为父查询 from 的数据表，还可以作为 where 子句查询条件

    结果集只有一行，通常使用=判断

    结果集为单行单列，即一个值

    SELECT
	*
FROM
	table_1 t1
WHERE
	t1.t1_colum_1 = (
    	SELECT
			t2_colum_1
		FROM
			table_2
    )

    结果集为单行多列，即一行记录

    SELECT
	*
FROM
	table_1 t1
WHERE
	(t1.t1_colum_1, t1.t1_colum_2) = (
    	SELECT
			t2_colum_1, t2_colum_2
		FROM
			table_2
    )

    结果集有多行，主要使用三种操作符：IN、ANY、ALL

    结果集为多行单列，即多个值

    SELECT
	*
FROM
	table_1 t1
WHERE
	t1.t1_colum_1 IN (
    	SELECT
			t2_colum_1
		FROM
			table_2
    )

    结果集为多行多列，即多行记录

    SELECT
	*
FROM
	table_1 t1
WHERE
	(t1.t1_colum_1, t1.t1_colum_2) IN (
    	SELECT
			t2_colum_1, t2_colum_1
		FROM
			table_2
    )

    IN 表示在多个值中进行匹配

    = ANY 等价于 IN

    < ANY 表示比子查询结果集中的最大的小

    > ANY 表示比子查询中结果集中最小的大

    <> ALL 等价于 NOT IN

    < ALL 表示比子查询结果集中的最小的小

    > ALL 表示比子查询中结果集中最大的大

    需要注意：

    <> ANY 表示与结果集中任意一条记录不等就返回真

    <> ALL 表示与每一条记录都不等才返回真

    此外，EXSITS 构用于判断子查询是否有数据返回，如果有则返回 true

    SELECT
	*
FROM 
	table_1
WHERE
	EXSITS (
        SELECT
            column_1, 
            column_2
        FROM
        	table_name
	)

  7. 联合查询

    UNION：将两个结果集进行并集操作，并剔除重复记录

    UNION ALL：并集操作但不剔除重复记录，比 UNION 要快

    SELECT column_1 FROM table_name1
UNION ALL
SELECT column_1 FROM table_name2

    UNION 需要两个结果集拥有同样的列数，不要求列名相同

    UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名

    同样的，INTERSECT 用于交集操作，MINUS 用于差集操作

  8. 连接查询

    左连接：LEFT JOIN，返回左表所有行，取右表数据进行匹配，匹配不到的为空

    右连接：RIGHT JOIN，返回右表所有行，取左表数据进行匹配，匹配不到的为空

    内连接：INNER JOIN，返回两个表中匹配的行

    SELECT 
	t1.cloumn_1,
	t2.cloumn_1,
	t2.cloumn_2
FROM 
	table_1 t1
LEFT JOIN 
	table_2 t2
ON 
	table_name1.cloumn_1 = table_name2.cloumn_1

    自身连接：

    SELECT 
	t1.id,
	t2.id
FROM 
	table1 t1, table_1 t2
WHERE 
	t1.id = t2.parentId

工具函数

  1. 聚合函数

    AVG：求平均值

    SUM：求和

    COUNT：统计数量

    MIN、MAX：求最大、最小记录

    聚合函数对一组数据进行操作，返回一行结果

    当需要返回其他字段时，需要按该字段分组

    SELECT
	SUM(cloumn_1)
FROM
	table_1;
	
SELECT
	cloumn_1, SUM(cloumn_2)
FROM
	table_1
GROUP BY
	cloumn_1;

  2. 字符串

    LENGTH(str)：返回字符串长度

    SUBSTR(str, start[, length])：从 start 处开始，截取 length 个字符，缺省 length 默认到结尾

    SELECT
	*
FROM
	table_1
WHERE
	SUBSTR(id, 2) = 'xxx'

    SUBSTR 的第二个参数从 1 开始，表示第一个字符。若是 -1 则表示倒数第一个，以此类推

    INSTR：查询字符串在另一字符串中的位置

    SELECT 
	INSTR('hello', 'l', 1, 2)
FROM 
	DUAL;

    INSTR(orign, target[, start, nth])：查询在 origin 中 target 的位置，从 start 开始，查找第 nth 个 target

    若 start 为负数，则从后往前查找，但返回值还是从前往后数，返回 0 表示没有查到

  3. 格式化

    TO_DATE(str[, fmt])：将字符串以 fmt 格式转为日期类型

    TO_CHAR(d[, fmt])：将日期或数字转为 fmt 格式的字符串

    SELECT 
	TO_CHAR(TO_DATE('20210314', 'yyyymmdd'))
FROM 
	DUAL;

    TO_CHAR 的 fmt 参数：

    • iw：获取是第几周
    • yyyy：获取年
    • mm：获取月
    • dd：获取日
    • day：获取是周几
    • hh24：获取 24 制小时
    • mi：获取分种
    • ss：获取秒

  4. 日期

    SYSDATE：获取当前时间

    ADD_MONTHS(d,n)：d 指定日期，n 表示要加的月数量，可为负数

    LAST_DAY(d)：获取指定时间当月的最后一天

    NEXT_DAY(d, '星期一')：获取下一周的周一

    TRUNC(d[, fmt])：截取时间，常用来获取时间范围的第一天

    TRUNC(SYSDATE, 'yy') 本年第一天

    TRUNC(SYSDATE, 'q') 本季度第一天

    TRUNC(SYSDATE, 'mm') 本月第一天

    TRUNC(SYSDATE, 'd') 本周第一天，周日开始

    举例：

    • 获取最近 7 天（包括当天）

      SELECT 
	TO_CHAR(SYSDATE - 6, 'yyyy-mm-dd hh24:mi:ss') 
FROM 
	dual;

    • 获取最近 4 周（包括当前周）

      SELECT 
	TO_CHAR(TRUNC(SYSDATE - 21, 'd') + 1, 'yyyy-mm-dd hh24:mi:ss') 
FROM 
	dual;

    • 获取最近 3 个月（包括当前月）

      SELECT 
	TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE, -2), 'mm'), 'yyyy-mm-dd hh24:mi:ss') 
FROM 
	dual;

    • 获取当前季度第一天

      SELECT 
	TO_CHAR(TRUNC(SYSDATE, 'q'), 'yyyy-mm-dd hh24:mi:ss') 
FROM 
	dual;

    • 获取上个季度第一天

      SELECT 
	TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'q'), -3), 'yyyy-mm-dd hh24:mi:ss') 
FROM 
	dual;

  5. 数字

    ABS：求绝对值

    CEIL：向上取整

    FLOOR：向下取整

    ROUND(x, y)：x 在第 y 位四舍五入

  6. 判断

    NVL(x, v)：x 为空，返回 v，否则返回 x

    NVL2(x, v1, v2)：x 为空，返回 v2，否则返回 v1

    DECODE(x, v1, r1, v2, r2, ...vn, rn, default)：类似于 if-else，x 等于 v1 则返回 r1，以此类推，直到缺省值

    示例，查询学生，要求年龄在 20 以上的显示 20 以上，20 以下的显示 20 以下，20 的显示正好 20：

    select t.id,
       t.name,
       t.age,
       DECODE(SIGN(t.age - 20),
              1,
              '20 以上',
              -1,
              '20 以下',
              0,
              '正好 20',
              '未知') sex
  from student t

    CASE WHEN：类似于 switch-case，比 DECODE 复杂且灵活，在其他数据库中也有该函数

    上面示例用 case when 实现：

    select t.id,
       t.name,
       t.age,
       (CASE 
       		WHEN t.age = 20 THEN '正好 20'
       		WHEN t.age < 20 THEN '20 以下'
       	ELSE '20 以上'
       END) sex
  from student t
转载自:https://juejin.cn/post/6994593808595812388
评论
请登录