数据库中description

转载

mob64ca14010a69 2025-01-08 15:18:37

文章标签 数据库中description oracle 12c SQL 主键 文章分类 架构后端开发

SQL SELECT的能力

SQL是声明式（declarative ）编程语言，因此简单，而且灵活。另一类是命令式（imperative ），如C语言。
SQL除了简单外，另一个好处是行业标准，因此比较成熟。

DESCRIBE table命令

To get the answers you seek, you must ask the correct questions.

希望问题得到解答，就必须正确的问问题。查询表之前就必须了解表结构。
表的结构存在数据字典中，称为元数据。实际存在行中的称为数据。
元数据可以通过describe命令获得。

DESC[RIBE] [schema.]tablename

例如：

SQL> desc sys.dual;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DUMMY                                              VARCHAR2(1)

必须有值的列定义为NOT NULL， NULL表示值空缺。

SELECT语句的能力

专业术语tuple带属性，翻译成Oracle的语言就是表带行，行带列。
SELECT 语句的能力包括

projection - 可以选择列
selection - 可以选择行
joining - 表之间的交互

数据规范化

在数据访问和数据存储之间取得平衡。

数据建模

UML，SQL Developer Data Modeler
逻辑模型：实体与关系
实体以圆角矩形表示，实体有属性，即列
实体的实例称为tuple或行
实体的集合称为表
实体的行称为cardinality（基数） of the tuples
唯一的属性称为唯一键，唯一键通常作为主键
实体间的关系称为外键
逻辑模型在关系型数据库中被实现为物理模型

冗余的数据不仅浪费空间，而且维护困难。反之则维护方便。
第一范式(1NF)：消除重复数据
第二范式(2NF)：消除不依赖于主键的属性（另外定义实体）
第三范式(3NF)：在第二范式基础上，消除相互依赖的属性
在数仓或决策支持系统中，可能通过冗余提高查询性能，但这是例外。在OLTP系统通常遵守。主键唯一标识一行，用#*表示。关系可以是1:N, N:1,1:1，M:N。后两者，一对一和多对多比较少见。 1对多表示一个可以关联多个，例如一种颜色可以关联多个车，或多种车可以有一个颜色。这种关系用crow’s foot notation表示，参考。参照一致性与外键：主键端是唯一，外键端的值必须在主键端存在。这对表之间的Join较关键表是二维的，包括行和列。每一行的列数必须相同这里提到了在关系型之前的层级模型（hierarchical model），层级模型将数据存于一个文件中，数据查询效率高，但数据移动较麻烦。层级模型的一个例子是将部门和员工存于一行，这样每一行并不等宽。总的来说，关系型模型是最优的模型。关于建模，请参考这篇文章Get Your Information in Order和这篇文章：Modeling and Accessing Relational Data 老的示例schema是SCOTT，目前都是用HR和OE等：

SQL> select table_name from all_tables where owner='OE';

TABLE_NAME
--------------------------------------------------------------------------------
CUSTOMERS
INVENTORIES
ORDERS
ORDER_ITEMS
PRODUCT_DESCRIPTIONS
PRODUCT_INFORMATION
PRODUCT_REF_LIST_NESTEDTAB
PROMOTIONS
SUBCATEGORY_REF_LIST_NESTEDTAB
WAREHOUSES

10 rows selected.

SQL> select table_name from all_tables where owner='HR';

TABLE_NAME
--------------------------------------------------------------------------------
DEPARTMENTS
EMPLOYEES
JOBS
JOB_HISTORY
LOCATIONS
REGIONS
COUNTRIES

7 rows selected.

用户是登录数据库用的，schema是用户名下所有的对象。schema是用来存对象的，包括数据对象和PL/SQL对象等。用户初始创建时，schema为空。用户和schema是一一对应的，因此经常同时使用。
用户是用来访问对象的，用户默认拥有自身schema对象的权限，而没有访问其它schema的权限。
常用的用户是某些用户只有登录权限，本身的schema中并没有对象，只是访问其它schema中的对象；而某些用户不允许登录，只是拥有对象供其他用户访问。
数据对象必须存在于schema中，例如HR.EMPLOYEES表示表EMPLOYEES存在于HR schema中。
访问时必须写全，除非时HR用户本身或使用同义词。
HR和OE用户默认是锁定的，即不允许登录，以下命令可以解锁：

alter user hr account unlock identiified by password;

示例schema可以在创建数据库时建立，或者之后下载安装，例如：

@?/demo/schema/human_resources/hr_main.sql

其中@表示执行，？表示ORACLE_HOME.
在生产系统中，不建议建立示例schema。

执行基础SELECT命令

SELECT * ：所有
 SELECT DISTINCT：消除重复
 SELECT email e FROM hr.employees：e是别名
 select ‘GOOD’ || ’ MORNING’ from dual： 字符串拼接

DUAL表：

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
15-SEP-19 11.55.34.847737 AM +00:00

虽然不是必须，但SQL中的保留着建议大写，另，对象的元数据在数据字典中是大写存放的。

字符串常数（literal）是以单引号包围的。而不是双引号：

SQL> select "GOOD" from dual;
select "GOOD" from dual
       *
ERROR at line 1:
ORA-00904: "GOOD": invalid identifier

转义：

SQL> select 'I''m OK!' from dual;

'I''MOK
-------
I'm OK!

或者用q转义，注意单引号前后的X，可以是任意字符，表示分隔符：

SQL> select q'XI'm OK!X' from dual;

Q'XI'MO
-------
I'm OK!

NULL表示值未知或空缺。所有NULL参与的操作结果均为NULL。

SQL> select NULL + 5 from dual;

    NULL+5
----------

主键不允许NULL，但外键允许。
关于基本的SELECT用户，请参见文章Getting Answers with SELECT

限制查询返回的行

where子句。where是可选的。
a=1: 数值条件
a=‘OK’：字符串条件
a=‘01-JAN-99’：日期条件
比较符号：=, >=, <=, >, <, BETWEEN…AND…，!=或<>, IN, LIKE，IS NULL
LIKE中的通配符包括%（0或多次匹配）和_（单次匹配）
LIKE中的转义：

SQL> select job_id from hr.jobs where job_id like 'SA\_%' escape '\';

JOB_ID
----------
SA_MAN
SA_REP

IS NULL示例：

SQL> select * from dual where 5+null is null;

D
-
X

SQL> select * from dual where 5+null is not null;

no rows selected

Bool操作：AND， OR，NOT where的示例请参见文章Why WHERE Matters

排序查询返回的行

order by 语法：

数据库中description_oracle

关于示例，请参见文章An Order of Sorts，非常好。

SQL Row-Limiting 子句

数据库中description_12c_02

参考这篇文章On ROWNUM and Limiting Results和这篇文章，以及这篇文章。

示例：

SQL> select * from hr.countries order by country_name;

CO COUNTRY_NAME                              REGION_ID
-- ---------------------------------------- ----------
AR Argentina                                         2
AU Australia                                         3
BE Belgium                                           1
BR Brazil                                            2
CA Canada                                            2
CN China                                             3
DK Denmark                                           1
EG Egypt                                             4
FR France                                            1
DE Germany                                           1
IN India                                             3
IL Israel                                            4
IT Italy                                             1
JP Japan                                             3
KW Kuwait                                            4
ML Malaysia                                          3
MX Mexico                                            2
NL Netherlands                                       1
NG Nigeria                                           4
SG Singapore                                         3
CH Switzerland                                       1
UK United Kingdom                                    1
US United States of America                          2
ZM Zambia                                            4
ZW Zimbabwe                                          4

25 rows selected.

SQL> select * from hr.countries order by country_name fetch first 5 rows only;

CO COUNTRY_NAME                              REGION_ID
-- ---------------------------------------- ----------
AR Argentina                                         2
AU Australia                                         3
BE Belgium                                           1
BR Brazil                                            2
CA Canada                                            2

5 rows selected.
SQL> select * from hr.countries where rownum <= 5 order by country_name;

CO COUNTRY_NAME                              REGION_ID
-- ---------------------------------------- ----------
AR Argentina                                         2
AU Australia                                         3
BE Belgium                                           1
BR Brazil                                            2
CA Canada                                            2

5 rows selected.

SQL> select * from hr.countries order by country_name offset 2 rows fetch first 5 rows only;

CO COUNTRY_NAME                              REGION_ID
-- ---------------------------------------- ----------
BE Belgium                                           1
BR Brazil                                            2
CA Canada                                            2
CN China                                             3
DK Denmark                                           1

5 rows selected.
SQL> select * from hr.countries order by region_id fetch first 5 rows with ties;

CO COUNTRY_NAME                              REGION_ID
-- ---------------------------------------- ----------
BE Belgium                                           1
UK United Kingdom                                    1
NL Netherlands                                       1
IT Italy                                             1
FR France                                            1
DK Denmark                                           1
DE Germany                                           1
CH Switzerland                                       1

8 rows selected.

符号替换

&符号，每次出现都必须输入。 &&符号，相同的只需输入一次。因为存作了会话变量。可以替代literal或列名或表达式。对于SQL*Plus和SQL Developer均适用。对于字符串替代无需考虑转义。

SQL> select * from hr.employees where employee_id =&empid;
Enter value for empid: 101
old   1: select * from hr.employees where employee_id =&empid
new   1: select * from hr.employees where employee_id =101

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID         SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
        101 Neena                Kochhar
NKOCHHAR                  515.123.4568         21-SEP-05 AD_VP           17000
                      100            90


1 row selected.
SQL> select * from hr.employees where hire_date = '&d';
Enter value for d: 21-SEP-05
old   1: select * from hr.employees where hire_date = '&d'
new   1: select * from hr.employees where hire_date = '21-SEP-05'

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID         SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
        101 Neena                Kochhar
NKOCHHAR                  515.123.4568         21-SEP-05 AD_VP           17000
                      100            90


1 row selected.

注意以上的with ties，是指会根据order by列出并列排名的行。以下是set verify和表达式替换的示例：

SQL> select &a;
Enter value for a: * from dual
old   1: select &a
new   1: select * from dual

D
-
X

1 row selected.

SQL> set verify off
SQL> select &a;
Enter value for a: * from dual

D
-
X

1 row selected.

&&替代避免重复，是因为存为了会话变量。可以用undefine取消定义。特别是如果第一次输错时有用：

SQL> select &&a;
Enter value for a: * from dual;
old   1: select &&a
new   1: select * from dual;
select * from dual;
                  *
ERROR at line 1:
ORA-00933: SQL command not properly ended


SQL> undefine a;
SQL> select &&a;
Enter value for a: * from dual
old   1: select &&a
new   1: select * from dual

D
-
X

查看会话变量，蛮多有用信息：

SQL> define
DEFINE _DATE           = "15-SEP-19" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1903000000" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0" (CHAR)
DEFINE _O_RELEASE      = "1903000000" (CHAR)
DEFINE A               = "* from dual" (CHAR)

可直接定义或更改会话变量：

SQL> define b=1

set define on|off决定了&和&&是否生效：

SQL> select 'Coda & Sid' from dual;
Enter value for sid: a
old   1: select 'Coda & Sid' from dual
new   1: select 'Coda a' from dual

'CODAA
------
Coda a

SQL> set define off
SQL> select 'Coda & Sid' from dual;

'CODA&SID'
----------
Coda & Sid

SQL> show define
define "&" (hex 26)
SQL> set define off
SQL> show define
define OFF

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。