【数据库知识小结】
在IT领域,数据库是一个至关重要的组成部分,尤其在数据管理和分析中扮演着核心角色。这里我们将深入探讨Oracle数据库以及一些常见的数据库理论和优化策略。
让我们来了解一下数据库三范式。数据库三范式是设计关系型数据库时遵循的规则,旨在减少数据冗余和提高数据的一致性。第一范式(1NF)要求数据库中的每一列都是不可分割的基本数据项,避免多值依赖;第二范式(2NF)在满足1NF的基础上,要求消除非主属性对候选键的部分函数依赖;第三范式(3NF)是在满足2NF的基础上,进一步消除非主属性之间的传递函数依赖。
数据库优化是确保高效查询性能的关键。一些数据库优化的经验包括:合理设计索引,避免全表扫描,使用合适的JOIN类型,优化SQL语句,控制事务粒度,合理分配内存资源,以及定期进行数据库维护任务如重建索引和分析统计信息。
在SQL查询中,UNION和UNION ALL都是用于合并多个SELECT语句的结果集。UNION会去除重复行,而UNION ALL则保留所有行,包括重复行。在需要排除重复行时选择UNION,而在不关心重复行时选择UNION ALL可以提高查询速度。
针对给定的员工薪资比较问题,我们可以使用如下的SQL语句:
```sql
CREATE TABLE IF NOT EXISTS employees (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
salary INT,
managerid INT REFERENCES employees(id)
);
INSERT INTO employees VALUES (NULL, 'zxx', 10000, NULL),
(NULL, 'lhm', 15000, 1),
(NULL, 'flx', 9000, 1),
(NULL, 'tg', 10000, 2),
(NULL, 'wzg', 10000, 3);
-- wzg 大于 flx, lhm 大于 zxx
SELECT e.* FROM employees e, employees m
WHERE e.managerid = m.id AND e.salary > m.salary;
```
接下来,我们看一个Oracle关联查询的例子。这里涉及到三个表:teacher(教师表)、student(学生表)和tea_stu(师生关系表)。我们需要查询教师名字、年龄以及他们所带的12岁以上学生的人数,且只列出40岁以下教师的信息。我们可以这样处理:
```sql
-- 预备知识:
-- 1. SQL语句按行处理,满足条件则执行操作(SELECT, INSERT, DELETE, UPDATE)
-- 2. 迪卡尔积可能导致“垃圾”信息,需注意消除
-- 清除现有表
DROP TABLE IF EXISTS tea_stu;
DROP TABLE IF EXISTS teacher;
DROP TABLE IF EXISTS student;
-- 创建表
CREATE TABLE teacher (teaID INT PRIMARY KEY, name VARCHAR(50), age INT);
CREATE TABLE student (stuID INT PRIMARY KEY, name VARCHAR(50), age INT);
CREATE TABLE tea_stu (teaID INT REFERENCES teacher(teaID), stuID INT REFERENCES student(stuID));
-- 插入数据
INSERT INTO teacher VALUES (1, 'zxx', 45), (2, 'lhm', 25), (3, 'wzg', 26), (4, 'tg', 27);
INSERT INTO student VALUES (1, 'wy', 11), (2, 'dh', 25), (3, 'ysq', 26), (4, 'mxc', 27);
INSERT INTO tea_stu VALUES (1, 1), (1, 2), (1, 3), (2, 2), (2, 3), (2, 4), (3, 3), (3, 4), (3, 1), (4, 4), (4, 1), (4, 2), (4, 3);
-- 查询结果
-- 1. 统计每个老师所带的学生人数
SELECT t.teaID, t.name, COUNT(s.stuID) AS student_count
FROM teacher t
JOIN tea_stu ts ON t.teaID = ts.teaID
JOIN student s ON ts.stuID = s.stuID
GROUP BY t.teaID, t.name;
-- 2. 只列出40岁以下,且带12岁以上学生的老师
SELECT t.name, t.age, COUNT(s.stuID) AS student_count
FROM teacher t
JOIN tea_stu ts ON t.teaID = ts.teaID
JOIN student s ON ts.stuID = s.stuID
WHERE t.age < 40 AND s.age > 12
GROUP BY t.name, t.age;
```
在这个例子中,我们首先通过JOIN操作将三个表连接起来,然后使用GROUP BY对教师进行分组,并使用COUNT统计每个教师的学生数量。通过WHERE子句筛选出年龄在40岁以下的教师,并考虑学生年龄大于12的情况。
以上就是关于数据库,特别是Oracle数据库的一些基础知识,包括三范式、数据库优化经验、UNION与UNION ALL的区别,以及涉及多表关联的复杂查询实例。这些知识对于数据库设计和管理来说是非常基础且实用的。