2009达内SQL学习笔记
5星
- 浏览量: 0
- 大小:None
- 文件类型：RAR
简介：
请首先登录到服务器 IP 地址 192.168.0.23，使用公共账号 openlab-open123 和子账号 tarena-tarena。随后切换至 SQL 界面，在完成上述操作后，请切换至 SQL 界面，使用用户名 sd0807 和子账号 sd0807，并提供相同的密码。同时，公共账号 openlab-open123 也已准备好。
请确保您已经安装了 JDK 环境，否则无法正常运行。如果尚未安装，请访问官方网站下载相应的软件包。
设置本地环境变量：
ORACLE_SID=oral10g --作为本地环境变量进行配置
export ORACLE_SID --将其标记为全局环境变量
unset ORACLE SID --从系统中删除该本地环境变量ORACLE SID，释放资源占用空间
首先设置安装路径... --建议先确定数据库的安装位置
然后将ORACLE_HOME参数赋值给系统环境变量ORACLE_HOME --可直接在一条命令行指令中完成操作
最后进行测试验证以确保配置无误
大小写对系统无影响，无需在意其大小写字母。
感叹号用于在SQL环境中执行Unix操作系统中的命令。
由一系列专有术语组成；这些专有术语被称为关键字保留字，仅作特定用途。作为一组标准术语使用。
SQL语句由子句构成，某些子句必不可少，而其他子句则可选择性地添加。
所有空格均被系统忽略（仅用于分隔单词，连续多个空格被视为一个）。
SQL语句可以一次性编写，建议分多行书写以便阅读和调试。
每个完整的SQL语句需以分号结束。
选择列及聚合函数，
从表中选数据
[过滤条件]
[Groub by group_by_expression]
[Having group_condition]
……
[Order by column]; --最后
三、常用简单语句：
- The screen is cleared.
Edit: Modify the preceding entry.
DESCRIBE command lists all column names along with their descriptions, data types, and remarks for a specified table or view. This command provides an overview of existing database objects.
Dual creates temporary tables (dual tables) that act as workspaces during operations such as joins or updates. For example:
- desc dual; -- Displays metadata about the current dual table
- from dual; -- Retrieves data from the associated base table
The ROLLBACK statement undoes the most recent transaction and reverts changes to their original state. Once executed, this operation cannot be undone.
The SET PAUSE ON/OFF command toggles screen partitioning (split-screen mode). When enabled, it divides the terminal window into two panels for simultaneous viewing of two different outputs.
- set pause onoff: Turn screen partitioning off if you want to return to normal output viewing.
- set pause please put an enter key and then press enter to continue. Once executed, this command prevents further processing.
The OERR OORA 904 command retrieves detailed error information for a specified SQL statement or expression. This helps in troubleshooting issues related to Oracle databases.
The SET HEAD OFF command removes the header from screen output when running Oracle client tools like SQL*Plus.
- set feed off: Removes the foot line (trailer) at the end of each screen output line.
All database operations are logged by default, and these logs can be captured using spool commands:
- spool oracleday01.txt on; -- Begin logging to Oracle daily operation log file
- spool off; -- Stop saving logs
These commands provide a comprehensive set of tools for managing and monitoring Oracle database operations.
四、SELECT 语句：执行选择操作或投影操作。
选择语句用于从一张或多张表格中提取一列或多列数据。该过程包含以下要素：选中哪些表，从何处进行查询。此功能由 FROM 子句指定。(最常用)
当从多张表查询时，默认会生成笛卡尔积；但通过附加条件可避免这种问题。
如果两张表格具有相同的字段名称，则需在列前面加上表别名来区分。（单引号引起字符串，双引号用于别名。若使用符号或区分大小写的情况，必须用双引号）
SELECT 语句的基本语法格式如下：
select [字段列表]([表别名].[字段]) FROM[表别名].[表格名称];（以分号结束）
示例说明：
1. 简单查询
Select id from s_emp;
结果：返回员工表中的员工ID。
2. 复杂查询
Select last_name,name from s_emp,s_dept where s_emp.dept_id=s_dept.id; -- 每人所属部门信息
3. 全选查询
SELECT * FROM Products; -- 返回所有字段值
在某些数据库系统中，默认无法直接连接字段。但可以通过使用双竖线字符来实现字段拼接。
示例：
Select first_name || last_name|| , title Employees From s_emp;
其中，||只能用于执行选择操作的语句部分。
不同的数据库系统可能采用其他连接符；在某些情况下，可以对字段进行别名处理。例如：
Select first_name || last_name || as fullName From s_emp;
或
Select first_name last_name|| , title Employees From s_emp;
其中，“||”只能用于执行选择操作的语句部分。
互斥锁：执行以下SQL语句以实现并发控制功能：
SELECT id, salary FROM s_emp WHERE id = 1 FOR lock;
在数据库设计中，为了防止数据不一致，可以使用FOR lock关键字来阻止其他人进行并行修改，直至 you unlock. 如果已有锁则自动退出：执行以下SQL语句以实现精确字段锁定：
SELECT id, salary FROM s_emp WHERE id = 1 FOR lock NO Wait;
在实现上可进一步指定精确到某个字段。例如，在语句末尾添加 For lock Of salary 将会实现这一功能。
注意要使用 unlocked 来释放锁，避免数据不一致问题。
五、ORDER BY 子句，排序
Order by：根据特定排序规则对查询结果进行排列，默认情况下是按从小到大的顺序（asc），可选使用desc指定从大到小的降序排列。
在SQL中，ORDER BY子句用于对查询返回的结果集按照指定字段进行排序。该子句允许指定一个或多个字段作为排序依据，并且这些字段可以来自被SELECT语句中的列，也可以是其他表中的字段（即非结果列）。
对于null值的处理方式为：在升序排列时，默认情况下排到最后，在降序排列时则排到最前面。这确保了排序逻辑的一致性和可预期性。
基本用法如下：
Select prod_id,prod_price,prod_name From Products Order By prod_price;
（从左到右执行排序，先按价格进行排序）
支持按照字段的相对位置进行排序。例如：
SELECT prod_id,prod_price,prod_name
FROM Products
ORDER BY 2，3;
（等同于：Order by price and name columns）
其中，数字表示字段的位置索引。
默认情况下，默认为升序排列（asc），若需要降序则使用desc关键字：
SELECT prod_id,prod_price,prod_name FROM Products ORDER BY prod_price DESC;
注意：desc只能应用于直接与其相连的字段列。如果要按照多个字段进行排序，则必须对每个字段单独指定排序方向，例如：
SELECT ... ORDER BY col1 ASC, col2 DESC;
升序是默认选项，无需显式声明；而降序则需要特别指定。这为用户提供灵活的控制方式以满足不同的数据排列需求。六、WHERE子句，在SQL查询中用于筛选数据
它必须遵循逻辑条件，结果仅返回布尔值true或false。例如，在SQL查询中，您可以编写如下语句：select last_name, salary from s_emp where salary = 1000；这将检索所有薪资等于1000的员工信息。WHERE子句中的运算符设置：
逻辑比较运算符部分如下描述：
等于符号“＝”表示相等关系；
非等值运算则通过以下几种形式进行表达：包括不等号“≠”、<>和^=，这些符号均可用于判断数值间的非等性关系。
大于关系由“>”来体现，
而>= 则用于说明某个范围的上限；
小于关系则以“<”表示，
<= 则适用于设定下限值。SQL比较运算符介于两个数值之间：位于两个指定范围之内。(BETWEEN 小值 AND 大值)例如：查询薪资介于1000至1500者。in(列表)：属于列表中的成员。(如：select last_name,dept_id from s_emp where dept_id in(41,42);第41、42部门的人)like ：包含某内容的。模糊查询可使用通配符制定更精确的数据匹配标准，但此方法仅限于文本字段，其他非文本数据类型无法应用。通过通配符可指定特定模式的搜索条件，例如：select table_name from user_tables where table_name like S_% escape;其中%表示任意字符序列或空值，默认情况下使用双引号更为直观和便于阅读。单引号中的内容保持大小写敏感性，并用于限定字符串范围。在比较数值类型数据时无需附加引号，而与文本字段进行匹配查询则需要指定引号。is null：指代无值状态。(NULL表示不包含具体值。此情况与空格或零不同)例如：SELECT prod_name,prod_price FROM Products WHERE prod_price IS NULL;在实际应用中，我们常常需要依据多种条件进行数据检索。除了传统的逻辑运算符之外，还可以灵活运用AND、OR和NOT等组合方式来实现更为精确的搜索效果。为了保证计算次序的明确性，在涉及复杂表达式时应合理运用圆括号(...)，这样不仅能够避免混淆并确保计算顺序的正确性，还能够在必要时优先处理复杂的子表达式。建议尽量避免过度依赖默认运算规则，并且在必要时优先使用括号来明确计算顺序。
条件与
例如：在SQL查询中，使用$prod\_price<4$和$vend\_id=‘DELL’结合在一起可以实现筛选特定价格范围内的产品信息。
条件或 （注：and 的优先级比 or 更高，可以通过括号改变运算顺序）
如：通过编写SELECT prod_id, prod_price, prod_name FROM Products WHERE $prod\_price<4$ OR vend\_id=‘DELL’可以检索符合条件的记录。
否定条件（not）
例如：NOT BETWEEN； NOT IN； NOT LIKE等否定性SQL运算符被用来反向查询指定范围或模式的数据。需要注意的是，按照英语习惯应使用“is not”而非“not is”。
当同时使用NOT与IN时，NOT的作用是查找不包含在条件列表中的行。特别地，在IN操作中存在NULL值时，执行正常操作；而若出现NOT IN且含NULL值的情况，则必须先排除无效记录再进行运算。
选择范围（in）
例如：通过编写SELECT prod_id,prod\_price,prod\_name FROM Products WHERE vend\_id IN(‘DELL’,’RBER’,’TTSR’)可以实现根据指定供应商筛选产品信息的功能。
此方法的优势在于：在处理长列表时语法直观；操作效率高；并具有动态构建WHERE子句的能力。
八、单行函数：
函数通常作用于数据集合，并为实现这些操作提供便利。
不同数据库管理系统提供的功能存在差异，这种差异可能导致系统间的不兼容。
遵循编写代码的规范是使用函数的良好习惯。
大多数SQL实现支持以下几类功能模块： 文本处理相关功能、算术运算支持、日期和时间管理工具以及数值计算能力等。Null：The Null value represents empty values. When performing operations involving the Null value, all results will be empty. The comparison between two Null values is considered unequal because they cannot undergo arithmetic operations directly.
For instance:
prod_price = ... (This approach may lead to errors)
prod_price = NULL (This approach also leads to an error due to potential issues with corejava)
The correct approach is: prod_price IS NULL
NVL：It stands for Not Valid Until, a function used to handle Null values by converting them into specified default values. These defaults can be of type date, character string, or numeric. Its important to note that the two parameters within an NVL expression must be of identical data types.
For example:
NVL(date_field, 1900-01-01) NVL(title_field, No Title Yet) NVL(salary_field, 0)
Incorrect usage:
SELECT last_name, title, salary * commission_pct FROM s_emp; (This will fail to display salaries for employees without a commission.)
Correct usage:
SELECT last_name, title, salary * NVL(commission_pct, 0) AS total_salary FROM s_emp; (Null values in the commission_pct are converted to 0.)
[D ISTINCT] 是一种去重功能。它会去除重复的数据行；当涉及多列时，仅去除数据块上的重复条目。[D ISTINCT]只能应用于单个字段的名称，不允许用于复杂的计算或表达式。所有聚合函数均可调用；当指定字段名时，[D ISTINCT] 仅支持计数单个字段的数据（如 COUNT(列)），而不适用于计数所有数据项（如 COUNT(*)）。例如：SELECT Distinct name FROM s_dept; SELECT Distinct dept_id,title FROM s_emp;
TRIM()函数用于去除字符串前后空格。它仅对字符串的两端执行去空格操作，并不影响中间内容。
使用实例：select trim( heo Are fdou ) from dual; 会返回 heo Are fdou。
将字符串转为小写：
lower(SQL Course) --> sql course
将字符串转为大写：
upper( SQL Course) --> SQL COURSE
对字符串进行首字母变大、其余变小的操作：
initcap(SQL Course) --> Sql Course
CONCAT为合成操作，在select语句中使用时仅限于select语句，适用于所有其他语句。示例：Concat(Good, String)返回GoodString。SUBSTR用于截取字符串的一部分，其定义如下：第一个参数表示从第几个字符开始截取（若要从倒数第n个字符开始，则用负值如-2），第二个参数为截取的长度。例如，Substr(String, 1, 3)将返回Str。LENGTH函数用于计算字符串的总长度，示例：Length(String)返回6。NVL函数的作用是处理空值（null）情况。Oracle中的日期表示法为：$DD-MMM-YYYY$，其中d表示日期（date）、m表示月份（month）和y表示年份（year）。例如，在SELECT语句中，prod_name字段通过DAY函数获取完整的星期几信息，并由DY函数提取前三字母来标识周首几个字符。两个日期相减将返回相差的天数（number of days），而两个日期直接相加则没有意义。此外，系统提供的SYSDATE用于获取当前操作日的日期值。计算两个日期之间的月份数量可以使用MONTHS_BETWEEN函数，在此参数中也可以嵌入其他时间函数来进一步细化计算结果。增加指定数量的月份到原始日期可使用ADD_MONTHS函数，并根据需要选择特定星期几的下一个工作日，这可以通过NEXT_DAY函数实现。最后一天的处理则由LAST_DAY函数负责，该函数可以根据给定日期返回其所属月份数值的最大天数。数值处理：适用于代数、三角学与几何学领域。Round函数将数字四舍五入到指定的小数位数。具体来说：
- Round(45.925, 2) 将返回45.93
- Round(45.925, 0) 将返回46
- 当第二个参数为负值时，Round函数会截断小数点前的相应位数。例如：
- Round(45.925,-1) 返回结果为50
Truncation函数则直接舍去末尾数字而不进行四舍五入处理。
- Trunc(45.925, 2) 将返回45.92
- 当第二个参数为正时，Trunc函数会截断小数点后的多余位数。例如：
- Trunc(45.925, 2) 返回结果为45.92
对于日期的处理方式：
以下是一些常见的数值处理函数：
ABS()用于计算绝对值，如 ABS(-5741.5854)返回 5741.5854。
圆周率常被表示为PI()。然而，在Oracle数据库中，并不支持该函数；而在MySQL数据库中，则可正常调用。
SIN()执行正弦运算作为基础数值运算之一，同时Oracle还提供了COS()、ASIN()和ACOS()等其他三角函数。
SQRT()则用于计算平方根。
该段代码用于数值转换和显示控制。其中：
- 当数值为9时会完整地显示出来；
- 数值小于1时，系统会在前面自动补上一个零以确保格式正确；
- 采用美元符号表示货币形式；
- 根据环境设置可选择带有货币符号的特别格式；
- 在固定位置显示小数点和逗号分隔符；当数值位数不足时，系统会进行四舍五入处理。例如：
SQL> select Order||TO_CHAR(id)||
2 总计为
3 TO_CHAR(total,fm$9,999,999)
4 来自 s_ord
5 其中ship_date =21-SEP-92;
TO_NUMBER(char)函数将输入的字符映射到数值型数据中。内链接：采用精确匹配策略实现两表数据的严格对应关系。外链接中的左连接操作会利用一方表的全部数据与另一方进行对比计算，即使某些字段为空也会纳入匹配范围，并确保所有记录都能完整显示。右链接则采取了完全逆向的操作策略，即通过另一方的数据反推当前表的相关信息。左侧连接结果会生成一个新的临时表，并确保所有匹配字段的数据都能完整展示。该查询使用INNER连接内部分表进行关联操作。结果集返回distinct的employee IDs以及对应的last name。从s_emp表中选择a和b两个副本执行LEFT join连接，确保左半边表中的每一行都能找到匹配键记录，并在没有匹配键时返回null值。分组允许将数据划分为若干个逻辑单元，便于对每个单元执行聚合计算。Group by：按字段进行分组。(默认按升序排列各组；若需降序则需使用order by指令)可包含任意字段组合。若嵌入了分组结构，系统将在最终指定的分组上执行聚合计算。GROUP BY子句中定义的所有字段必须是查询基础数据表中的字段或有效的复杂表达式，并不得包含任何聚合函数。在GROUP BY子句中定义的所有字段必须是查询基础数据表中的字段或有效的复杂表达式，并不得包含任何聚合函数。若需要对分组结果执行聚合计算，则应确保所有SELECT列表项均与GROUP BY子句中的相应字段对应。若在SELECT语句中使用了复杂的表达式，须同时在GROUP BY子句中定义这些表达式，并避免引入新表 alias。若需要进行分组汇总，则应确保所有SELECT列表项均包含对应的GROUP BY字段。在MySQL数据库中进行数据筛选时，可以采用GROUP BY结合HAVING的方式。按条件排序之后再进行筛选。GROUP BY后必须紧跟HAVING关键字才能使用该功能。同时，在执行完GROUP BY操作后才会有机会应用HAVING来进一步限制结果集。
聚合函数：
AVG表示计算所有数值的平均数（不支持包含NULL值的情况）。
COUNT用于统计特定列中的非空记录数量，其中Count(列)会忽略为空的数据行；而COUNT(*)则返回整个表中该列的所有记录数目，其中包括空值。
MAX函数返回指定字段中的最大值，忽略为空的数据行。需要注意的是，在某些数据库系统中，允许对文本型数据进行计算，默认情况下将返回最后一个非空的文本值。
MIN函数用于获取某个字段中的最小数值或日期；不能直接作用于字符串类型的数据。但在一些数据库系统中可以对文本列进行处理，默认会返回最前的一个文本值。
SUM函数用来累加数值型数据，忽略为空的情况；但无法应用于字符型或其他非数字数据类型的字段。
子查询即为嵌套于主查询之内的子查询语句，在任意的SELECT语句中使用时，需注意子查询不应包含带有排序功能的ORDER BY子句。由于其结构特性决定了只能返回单列数据，因此在处理多字段查询任务时会产生矛盾与无效结果。例如，试图从子查询中获取多个字段将导致运行错误，具体表现形式包括：直接指定多列如SELECT min(last_name),min(salary)；或采用嵌套方式选择如SELECT last_name,salary FROM s_emp WHERE salary=(SELECT min(salary) FROM s_emp)等无效语句结构。在E-R图中，属性包括：实体（Entity）与关系（Relationship）。其中：
[必填字段] [必填且不能为空]
[可选字段] [可有可无]
[唯一必填字段]表示此属性唯一且非空。约束：针对表中的字段进行定义的。
PK: primary key（主键约束，PK=UK+NN）具有唯一性和非空属性，在每个表中只能存在一个主键，两个字段可以联合构成主键（Composite Primary Key）。
FK: foreign key（外键约束），它确保引用关系的有效性，外键的取值范围必须受限于另一张表中的主键或唯一索引。被引用的表称为父表（parent table），而进行引用的对象表则为子表（child table）。在创建和操作子表时，应先建立父表，并遵循以下原则：记录插入时按照父后子的顺序执行；删除操作需先清除子表中的相关记录，随后才能处理父表；数据修改前必须确保没有其他子表引用该记录；若要删除表格，则需要首先删去所有关联的子表记录，最后再进行父表的删除操作。
U: unique key（唯一键 UK），其值具有唯一性且不允许重复。在设置了唯一约束的字段中可以存在null值，因为null被视为与其它值不等。
NN: NOT NULL（不能为空属性）。
index（索引）是数据库特有的对象类目，在实际应用过程中必须优先考虑其使用；view（视图）是一种特殊的表结构，用于实现数据的一致性视图和用户界面的独立性。数量关系: 1:1、n:1、m:1、m:n关系
该资源介绍了一种优化数据库设计的方法以减少冗余、维护完整性并明确实体间的关系。
为了实现目标，我们遵循以下原则：
1. 确保每个字段仅记录单一信息；
2. 避免存在重复或共享的字段值；
3. 保证主键唯一性，并通过合理关联关系减少数据冗余；
4. 使用外键确保数据的一致性和完整性。
具体实现如下：
- 第一范式（1NF）：每个属性独立且不重复；
- 第二范式（2NF）：建立明确唯一的标识符并保持一致性；
- 第三范式（3NF）：避免因依赖关系产生的额外数据存储。
通常情况下，我们追求并实现这一级别。
创建表：使用CREATE TABLE语句定义表结构，指定字段名、数据类型和约束条件；建表名称需满足以下命名规范：必须以字母开头且长度不超过30个字符；只能包含A-Z/a-z/0-9以及符号 _ $ #等标识符；禁止与同一目录内的其他相同名称的表共享空间，同时不能使用关键字或具有特殊含义的字段名。MySQL数据库中，“number”型别分为小类如“SMALLINT、INTEGER、BIGINT”等，而Oracle仅支持单一的“number”类型；在MySQL环境中，创建带有约束条件的表格时需注意：不允许有空字段（即每个字段必须指定默认值）；Oracle则要求所有非主键列都必须定义NOT NULL约束。MySQL实例中的建表语句示例为：CREATE TABLE table_name (field1 data_type [default value] primary_key, field2 data_type not null unique)；Oracle的建表语句格式为：CREATE TABLE table_name (field1 data_type primary_key, field2 data_type NOT NULL UNIQUE)；特别需要注意的是，MySQL中的数值型别分为小类而 Oracle仅支持单一类型，因此在设计数据库结构时需根据具体需求选择合适的字段类型。
Insert操作用于将完整记录添加至数据库表中。主要有三种不同的方法：第一种是直接输入完整信息；第二种仅提供部分数据；第三种基于查询结果进行插入。
通常需要数据库管理系统的相应权限模块来执行此操作。对于第一种方式，即INSERT INTO products VALUES(2008,TV,222.22,US)，这种方法按照字段预设的位置进行处理，不太推荐使用。当某些字段为空时，必须手动填充以确保完整性。
第二种方式则采用INSERT INTO products(id,name,price,vend_name) VALUES(2008,TV,222.22,US)；这种操作方法是按照数据的自然排列顺序进行处理，并且系统会自动生成缺失的部分。这种方法是更优的选择。
插入获取的记录直接添加至数据库中的指定表格中。该SQL语句采用SELECT子句提取所需数据后通过INSERT语句将其直接注入到目标表中。若该表当前为空，则此操作不会插入任何数据也不会引发错误，这是由于操作程序本身的合法性所决定的。可以使用WHERE子句来进一步筛选需要处理的数据记录，确保仅针对符合条件的数据执行此插入操作。建表指令：将新表定义为从原表复制所有列的结果集。与CREATE TABLE AS SELECT不同的是INSERT INTO语句，它负责将外部查询结果导入数据库，而后者则是直接创建新的表结构。任何SELECT选项和子句都可以使用，包括WHERE和GROUP BY。通过使用JOIN操作符，可以从多个源表中获取数据输入到目标表。无论从多少个源表中提取信息，最终的所有数据都会被写入同一个目标数据库表。该更新操作用于修改表中的记录信息。具体来说，需要指定要更新的数据所在的表；设定具体的字段名称及其新的值；以及使用过滤条件来确定哪些行会被更新。如果需要根据查询结果来更新数据，则可以利用子查询的功能。或者将某个字段设置为空。
该更新操作用于修改表中的记录信息。具体来说，需要指定要更新的数据所在的表；设定具体的字段名称及其新的值；以及使用过滤条件来确定哪些行会被更新。如果需要根据查询结果来更新数据，则可以利用子查询的功能。或者将某个字段设置为空。
删除数据 DELETE 语句用于完全删除整行数据，需特别注意操作的安全性。与之不同的是，该语句不会仅删除某列内容。重要提示：DELETE命令是用于从数据库中删除所有记录的指令，它会将整个表的内容清空而不是仅仅针对特定字段进行操作。
如果目标是从数据库表格中删除全部记录，则推荐使用TRUNCATE TABLE语句（清除表），该方法操作速度更快。
数值型索引表：序列:作为排序依据。存储物理内存地址Index：作为依赖表的数据结构，其主要目标是以加快搜索效率为主。
该系统提供一个视图（View），用于显示部分表格数据。通过限制对数据的访问来简化查询操作，并提高表的数据独立性。本质上，这是一项SQL查询语句的功能实现。
创建或重命名视图：生成或更新视图。
别名定义为：将视图名称指定为特定别名。
子查询功能带有约束条件：该视图作为子查询时可包含必要的约束条件设置。
可读只模式：仅允许读取数据的访问权限，禁止修改操作。
删除该视图的功能：移除或删除该预设的视图内容。
Rownum: 行号。内存里排序的前N个记录。
在WHERE子句中，可以使用Rownum=1、Rownum<=N或Rownum
全部评论 (0)
