数据库复习

数据模型与关系模型

数据模型

即要面向现实世界，又要面向机器世界，因此需满足三个要求：

1、能够真实地模拟现实世界；

2、容易被人们理解；

3、能够方便地在计算机上实现。


概念模型

也叫信息模型，为了正确直观地反映

客观事物及其联系，对所研究的信息世界建立一个抽象的模型，是以用户的观点对数据和信息建

模。

概念模型独立于计算机系统

概念模型也独立于具体的DBVS支持的数据模型

概念模型也是用产和数据库设计人员交流的工具


描述概念模型

最常用方法是实体-联系方法，即
E-R方法（也称E一R模型）。使用的工具称E一R图。

• E-R模型的三个基本图素

实体（长方体）属性（椭圆形）联系（菱形）



画ER图（‼️）

用E-R图表示某个工厂物资管理的概念模型

用矩形表示实体，用圆润矩形表示实体属性，用棱形表示两者关系，并且在该棱形上面，需要加上圆润矩形表示关系产生的属性，最后还要求加上多对多或者多对一的关系

根据E-R图写关系模式

首先把主要实体和其属性加上，还要加上多出来的属性，以及区分主键出来（一般就是编号），最后还要记得写联系的实体和属性

实体

＞仓库：仓库号、面积、电话号码

＞零件：零件号、名称、规格、单价、描述

＞供应商：供应商号、姓名、地址、电话号码、帐号

＞项目：项目号、预算、开工日期

＞职工：职工号、姓名、年龄、职称



数据模型

• 信息世界的信息在机器世界用数据的形式表示和存储，为正确在机器世界中表示信息而建立的模型称为数据模型，它按计算机系统的观点对
  数据建模
• 用于提供数据库系统中信息表示和操作手段的形式
  框架，主要用于DBWS的实现
• 数据模型是数据库系统的核心和基础。


数据模型三要素

数据结构—-描述数据集合的结构

数据操作—-查询、插入，修改，删除

完整性约束—-数据及其联系的制约和依存规则，保证数据的正确，有效和相容性

关系模型的相关概念

• 关系：一个关系对应着一个二维表，二维表名就是关系名

• 关系模式：对关系的描述，即二维表中列标题的定义称为
  关系模式

• 元组：关系中的每一行，称为一个元组。
  元组的集合为关系

• 属性：在二维表中的列名，称为属性。属性的个数称为关系的元或度。

• 域：某属性的值的取值范围为域，又称值域

• 分量：元组中一个属性的值

  

  

  

设计该系统的ER图，并转换成关系模式，说明主键及外键



表操作与约束（‼️）

操作

创建表：CREATE table

CREATE table student (
    stuNo char(8) PRIMARY KEY,#主见约束
    stuName varchar(20) NOT NULL,
    stuSclass char(10)
);

自增：AUTO_INCREMENT

-- 创建表并定义自增主键
CREATE TABLE ExampleTable (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(255),
    -- 其他列
);

数字补零：ZEROFILL

-- 创建表并使用 ZEROFILL 选项
CREATE TABLE ExampleTable (
    ID INT ZEROFILL PRIMARY KEY,
    Amount INT ZEROFILL
);

约束

• 主键/码约束：唯一标识表中的一条记录，一个表只有一个主键——PRIMARY KEY

#复合列主键约束
CREATE TABLE students (
  id int,
  semester int,
  name varchar(50),
  PRIMARY KEY (id, semester)
);

• 外键/码约束：用来建立两个表之间的关联，约束数据一致性——FOREIGN KEY

-- 创建客户表
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(255),
    -- 其他客户相关字段
);

-- 创建订单表，并定义外键约束
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderNumber VARCHAR(255),
    CustomerID INT,
    -- 其他订单相关字段
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

• 唯一约束（候选键码约束）：数据不能重复，也能标识表中的一条记录，与主键不同的是候选键可以有多个——UNIQUE

#复合列唯一约束
CREATE TABLE table_name(
	column name1 datatype, 
  column name2 datatype,
  unique(column_name1, column_name2);
) 

• 默认值约束：不给表中的列输入值时，自动賦值——DEFAULT 0

-- 创建表并添加默认值约束
CREATE TABLE ExampleTable (
    ID INT PRIMARY KEY,
    Name VARCHAR(255) DEFAULT 'Unknown', -- Name 列的默认值为 'Unknown'
    Age INT DEFAULT 18, -- Age 列的默认值为 18
);

• 非空约束：用来限制表中的某列必须输入值——NOT NULL

-- 创建表并添加非空约束
CREATE TABLE ExampleTable (
    ID INT PRIMARY KEY,
    Name VARCHAR(255) NOT NULL, -- Name 列不允许包含 NULL
    Age INT NOT NULL -- Age 列不允许包含 NULL
);

• 检查约束：用来限制标准的某列输入值的范围——CHECK

-- 创建表并添加检查约束
CREATE TABLE ExampleTable (
    ID INT PRIMARY KEY,
    Age INT CHECK (Age >= 18), -- Age 列的值必须大于等于 18
    Status VARCHAR(50) CHECK (Status IN ('Active', 'Inactive')) -- Status 列的值必须是 'Active' 或 'Inactive'
);

修改表：ALTER table

基本语法：

ALTER table table_name add column index|constraint

alter column {SET DEFAULT literal | DROP DEFAULT}

change column

modify column

drop column|primary key|foreign key

添加列

ALTER TABLE your_table_name
ADD COLUMN new_column_name data_type;

编辑列

ALTER TABLE your_table_name
MODIFY COLUMN column_name new_data_type;

修改列名

ALTER TABLE your_table_name
CHANGE COLUMN old_column_name new_column_name data_type;

删除列

ALTER TABLE your_table_name
DROP COLUMN column_name;

添加主键

ALTER TABLE your_table_name
ADD PRIMARY KEY (column_name);

删除主键

ALTER TABLE your_table_name
DROP PRIMARY KEY;

添加外键

ALTER TABLE your_table_name
ADD FOREIGN KEY (column_name) REFERENCES another_table_name(referenced_column);

删除外键

ALTER TABLE your_table_name
DROP FOREIGN KEY foreign_key_name;

添加默认值约束

ALTER TABLE your_table_name
ALTER COLUMN column_name SET DEFAULT default_value;

删除默认值约束

ALTER TABLE your_table_name
ALTER COLUMN column_name DROP DEFAULT;

添加非空约束

ALTER TABLE your_table_name
MODIFY COLUMN column_name data_type NOT NULL;

删除非空约束

ALTER TABLE your_table_name
MODIFY COLUMN column_name data_type NULL;

添加CHECK约束

ALTER TABLE your_table_name
ADD CONSTRAINT check_constraint_name CHECK (condition);

添加/删除唯一约束

-- 添加唯一约束
ALTER TABLE your_table_name
ADD CONSTRAINT unique_constraint_name UNIQUE (column_name);

-- 删除唯一约束
ALTER TABLE your_table_name
DROP INDEX unique_constraint_name;

添加自增

ALTER TABLE your_table_name
MODIFY COLUMN column_name data_type AUTO_INCREMENT;

取消自增

ALTER TABLE your_table_name
MODIFY COLUMN column_name data_type;

删除表：DROP table

DROP TABLE your_table_name;

DML(数据操纵语言)操作数据表（‼️）

数据插入操作：INSERT/REPLACE

REPLACE在冲突时会更新新的数据

插入一条记录：

INSERT INTO your_table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

例如：

INSERT INTO employees (employee_id, full_name, salary)
VALUES (1, 'John Doe', 50000);

数据更新操作：UPDATE

更新记录：

UPDATE your_table_name
SET column1 = value1, column2 = value2, ...
WHERE condition
ORDER BY column_to_order_by
LIMIT number_of_rows;

例如：

UPDATE employees
SET salary = 55000
WHERE department_id = 2
ORDER BY hire_date
LIMIT 10;

数据删除操作：DELETE

删除记录：

DELETE FROM your_table_name
WHERE condition
ORDER BY column_to_order_by
LIMIT number_of_rows;

例如：

DELETE FROM employees
WHERE department_id = 2
ORDER BY hire_date ASC #默认就是ASC升序排列，使用DESC可以实现降序排列
LIMIT 10;

清空表记录：truncate

快速删除表中的所有行，但保留表的结构

TRUNCATE TABLE your_table_name;

关系代数（‼️）

,>，≥，<，≤，=，≠∧（与）,∨（或）,┐（非）∪(并)，∩(交) ， —(差) ， ×(笛卡尔积σ (选择) ，∏ (投影)，∞ (连接)，÷(除)

1. 选择 (σ)：

   • 语法：$(\sigma_{条件}(关系)$)
   • 解释：从给定的关系中选择满足特定条件的元组。
   • 例子：$(\sigma_{Age > 25}(Employees))$ 选择年龄大于25的员工。

2. 投影 (∏)：

   • 语法：$(\pi_{属性列表}(关系)$)
   • 解释：从给定的关系中选择特定的属性列，去除重复的元组。
   • 例子：$(\pi_{Name, Salary}(Employees))$ 从员工关系中选择姓名和工资。

3. 连接 (∞)：

   • 语法：$(关系1 \bowtie_{条件} 关系2$)
   • 解释：将两个关系的元组按照指定条件连接在一起。
   • 例子：$(Departments \bowtie_{DeptID} Employees)$ 连接部门和员工关系，条件为部门ID相等。

4. 除 (÷)：

   • 语法：$(关系1 ÷ 关系2$)
   • 解释：返回属于关系1但不属于关系2的元组。
   • 例子：$(Projects ÷ (Projects \bowtie_{ProjID} AssignedProjects)$) 返回没有被分配的项目。

5. 笛卡尔积 (×)：

   • 语法：$(关系1 \times 关系2$)
   • 解释：返回所有可能的关系1和关系2元组的组合。
   • 例子：$(Employees \times Departments$) 返回所有可能的员工和部门的组合。

6. 差 (—)：

   • 语法：$(关系1 - 关系2$)
   • 解释：返回属于关系1但不属于关系2的元组。
   • 例子：$(Students - (Students \cap Graduates)$) 返回没有毕业的学生。

7. 并 (∪)：

   • 语法：$(关系1 \cup 关系2$)
   • 解释：返回属于关系1或关系2的所有元组，去除重复的元组。
   • 例子：$(MaleEmployees \cup FemaleEmployees$) 返回所有男性或女性员工的元组。

8. 交 (∩)：

   • 语法：$(关系1 \cap 关系2$)
   • 解释：返回同时属于关系1和关系2的元组。
   • 例子：$(Managers \cap Employees$) 返回既是经理又是员工的元组。

9. 非 (┐)：

   • 语法：$(\bar{关系}$) 或者 $(\neg 关系$)
   • 解释：返回不属于给定关系的所有元组。
   • 例子：$(\bar{HighlyPaidEmployees}$) 返回不是高薪员工的所有员工。

10. 与 (∧)：

    • 语法：$(条件1 \land 条件2$)
    • 解释：返回同时满足条件1和条件2的元组。
    • 例子：$(Age > 30 \land Salary > 50000$) 返回年龄大于30且工资大于50000的员工。

11. 或 (∨)：

    • 语法：$(条件1 \lor 条件2$)
    • 解释：返回满足条件1或条件2的元组。
    • 例子：$(Department = ‘HR’ \lor Department = ‘Finance’$) 返回属于人力资源部或财务部的员工。

12. 等于 (=)：

    • 语法：$(关系1 = 关系2$)
    • 解释：判断两个关系是否相等，即包含相同的元组。
    • 例子：$(Students = EnrolledStudents$) 判断学生关系和已注册学生关系是否相等。

13. 不等于 (≠)：

    • 语法：$(关系1 \neq 关系2$)
    • 解释：判断两个关系是否不相等。
    • 例子：$(Employees \neq FormerEmployees$) 判断员工关系和离职员工关系是否不相等。

14. 大于 (>)：

    • 语法：$(属性 > 值$)
    • 解释：选择属性值大于给定值的元组。
    • 例子：$(Salary > 60000$) 选择工资大于60000的员工。

15. 大于等于 (≥)：

    • 语法：$(属性 \geq 值$)
    • 解释：选择属性值大于或等于给定值的元组。
    • 例子：$(Experience \geq 5$) 选择经验大于等于5年的员工。

16. 小于 (<)：

    • 语法：$(属性 < 值$)
    • 解释：选择属性值小于给定值的元组。
    • 例子：$(Age < 25$) 选择年龄小于25的员工。

17. 小于等于 (≤)：

    • 语法：$(属性 \leq 值$)
    • 解释：选择属性值小于或等于给定值的元组。
    • 例子：$(Rating \leq 3$) 选择评分小于等于3的项目。

简单查询与子查询

运算符

算术运算符：+，-，*，1，％

比较运算符：＞，＜，>=，<=，<>，l去答ike, in,between…and.., is null, greatest, least

逻辑运算符：not, ！I, and, &&, or,||,xor

位运算符：|，＆，^<<, >>,~

数据查询数据

SELECT [DISTINCT] 
    column1 AS output_name1, 
    column2 AS output_name2, 
    ...
FROM 
    table1, table2, ...
WHERE 
    condition
GROUP BY 
    group_column1, group_column2, ...
HAVING 
    group_filter_condition
ORDER BY 
    sort_column1 DESC, sort_column2 ASC
LIMIT 
    offset, row_count;
    
-- 选择不重复的州字段，并按州名称升序排序，只选择前5行
SELECT DISTINCT 
    state AS state_name, 
    COUNT(*) AS total_cities
FROM 
    cities
WHERE 
    population > 100000
GROUP BY 
    state
HAVING 
    total_cities > 1
ORDER BY 
    state_name ASC
LIMIT 
    5;

选择所有列和所有行

SELECT * FROM your_table_name;

例如，从名为 employees 的表中选择所有列和所有行：

SELECT * FROM employees;

选择特定列和所有行

选择特定列的所有行：

SELECT column1, column2, ... FROM your_table_name;

例如，从名为 employees 的表中选择雇员的姓名和薪水：

SELECT full_name, salary FROM employees;

使用 WHERE 子句筛选行

使用 WHERE 子句筛选特定的行：

SELECT * FROM your_table_name
WHERE condition;

例如，从名为 employees 的表中选择薪水大于 50000 的雇员：

SELECT * FROM employees
WHERE salary > 50000;

Like 用于模糊查询

使用通配符：

% 表示任意多个字符

_ 表示一个字符

查询姓黄的学生的信息（完整记录）

SELECT * FROM student WHERE sname LIKE '黄％';

查询姓黄的单名的学生的记录

SELECT * FROM student WHERE sname LIKE '黄_';

注意事项：

(1、where ssex=1 2、where Sname like "%燕%"(包含燕)3、where后面如果有多个条件，用AND连接4、where tname like "刘__",5、 WHERE teachno = 2 OR teachno = 8( IN(2,8) )6、between "1993-8-15" AND "1995-12-13"(where sbirth>='1993-8-15' and sbirth <='1995-12-13')7、WHERE ssex=1 and sbirth NOT BETWEEN '1994-3-12' AND '1995-2-15'(where ssex=1 and（sbirth<''1994-3-12'' or sbirth >''1995-2-15''）))

使用 ORDER BY 子句排序结果

使用 ORDER BY 子句对结果进行排序：

SELECT * FROM your_table_name
ORDER BY column_to_order_by;

例如，从名为 employees 的表中按照薪水降序排序：

SELECT * FROM employees
ORDER BY salary DESC;

使用 LIMIT 子句限制结果行数

使用 LIMIT 子句限制结果集的行数：

SELECT * FROM your_table_name
LIMIT number_of_rows;

例如，从名为 employees 的表中选择前 10 行：

SELECT * FROM employees
LIMIT 10;

聚合函数

最大值：MAX

SELECT MAX(salary) AS max_salary
FROM employees;

这将返回 employees 表中 salary 列的最大值，并将其命名为 max_salary。

最小值：MIN

SELECT MIN(salary) AS min_salary
FROM employees;

这将返回 employees 表中 salary 列的最小值，并将其命名为 min_salary。

平均值：AVG

SELECT AVG(salary) AS average_salary
FROM employees;

这将返回 employees 表中 salary 列的平均值，并将其命名为 average_salary。

求和：SUM

SELECT SUM(salary) AS total_salary
FROM employees;

这将返回 employees 表中 salary 列的总和，并将其命名为 total_salary。

计数：COUNT

SELECT COUNT(employee_id) AS total_employees
FROM employees;

这将返回 employees 表中的行数（即总员工数量），并将其命名为 total_employees。

例子

查询选课号为1的最高分的学号和成绩

SELECT g.sNo, g.score
FROM grade g
WHERE g.teachNo = 1
AND g.score = (SELECT MAX(score) FROM grade WHERE teachNo = 1);

简单查询小结(‼️)

Select 需要的字段列表（*表示全部，字段之间使用,连接，注意别用、）

from 查询的表名

where 查询条件

order by 排序字段列表

limit 限定行数(注意比如说limit2,2 就是从第二个数组取两个，也就是显示二、三两条数据)

学习通作业

院系信息表（院系编号，院系名，办公室，电话，负责人）
department（dNo，dName，dOffice，dPhone，leader
班级信息表（班级编号，班级名，院系编号，当前人数，额定人数）
classinfo（cNo，cName，dNo，cNum，cMaxNum）
学生信息表（学号，密码，姓名，性别，出生日期，联系电话，班级编号）
student（sNo，sPassword，sName，sSex，sBirth，sPhone，cNo）
教师信息表（教师编号，姓名，性别，学历，职称，出生年月，院系编号）
teacher（tNo，tName，tSex，tDegree，tTitle，tBirth，dNo）
课程信息表（课程号，课程名，学分，类型）
course（coNo，coName， coCredit，coType）
授课信息表(授课编号，教师编号，课程号，学期，备注)
teach(teachNo, tNO, coNo, teachTerm, teachRemark)
成绩表（学号，成绩，授课编号，备注）
grade（sNo，score，teachNo ，gradeRemark）
管理员信息表（用户名，姓名，密码，联系电话，邮箱）
admin（adAccount，adName，adPassword，adPhone，adEMail）

最简单的查询：


1、select * from department

2、select teachNo As 选课编号, tNo As 教师编号, coNo As 课程编号,teachTerm As 开课学期 from teach

3、select * from teach order by teachNo limit 2

4、select * from teach order by teachNo desc limit 2

5、select tNo,tName,tDegree,tBirth from teacher order by tDegree ask,tBirth desc;

增加where：


学生信息表（学号，密码，姓名，性别，出生日期，联系电话，班级编号）
student（sNo，sPassword，sName，sSex，sBirth，sPhone，cNo）

教师信息表（教师编号，姓名，性别，学历，职称，出生年月，院系编号）
teacher（tNo，tName，tSex，tDegree，tTitle，tBirth，dNo）
课程信息表（课程号，课程名，学分，类型）
course（coNo，coName， coCredit，coType）

成绩表（学号，成绩，授课编号，备注）
grade（sNo，score，teachNo ，gradeRemark）

1、select * from student where ssex=1

2、select sName,sNo,sBirth from student where Sname like “%燕%”

3、select sName,sNo,cNo from student where Sname like “%飞%” AND ssex=1

4、select * from teacher where tname like “刘__”

5、select sNo,sName,sPhone from student order by

6、select coName,coNo,coCredit from course from course where coName like “%语%” AND coType=1order by coCredit DESC

7、SELECT * FROM grade WHERE graderemark=’重修’;

8、SELECT * FROM grade WHERE teachno = 2 OR teachno = 8( IN(2,8) ) ORDER BY teachno, score DESC;

9、select sno,sname,ssex,sbirth FROM student where sbirth between “1993-8-15” AND “1995-12-13”(where sbirth>=’1993-8-15’ and sbirth <=’1995-12-13’)

10、SELECT sno,sname,sbirth FROM student WHERE ssex=1 and sbirth NOT BETWEEN ‘1994-3-12’ AND ‘1995-2-15’;

用where ssex=1 and（sbirth<’’1994-3-12’’ or sbirth >’’1995-2-15’’） 也可以

增加聚合函数：


1、

2、

3、

4、

5、

6、

7、

8、

9、

复杂查询（‼️）

分组查询

GROUP BY

语法

GROUP BY 字段1：根据字段1分组

GROUP BY 字段1，字段2：先根据字段1分组，再根据字段2分组

注意：seleet 后面只能是聚合西数或者是group by之后出现过的列名。 (但是mysql不会报错语法）

例子

查询成绩表grade，每个选课号参加学习的学生数

SELECT teachNo, COUNT(sNo)
FROM grade
GROUP BY teachNo;

HAVING 子句

语法：

HAVING 组筛选条件

用法：

放在group by 子句后面，选出满足条件的分组

注意：having和where都用于给出查询条件，但 having只能放在group by子句后面，作为组的筛选条件，不能替代where子句

例子

查询选课人数超过2位学生的选课号与选课人数

SELECT teachno,COUNT(sno)

FROM grade

GROUP BY teachno

HAVING COUNT(SnO)>2

查询平均分大于80分的选课号和平均分，并按照平均分降序排序，另外平均分统计时不包括重修和补考的成绩记录（即（”正常”的成绩记录）

SELECT teachno,ROUND(AVG(score) ,1) FROM grade

WHERE graderemark='正常'

GROUP BY teachno

HAVING AVG(SCore)>80

ORDER BY AVG(SCOre) DESC;

多表查询

等值连接

两个表连接

语法

select 表1.宇段1，⋯，表2.宇段1，…

from 表1，表2

where 表1.连接字段-表2.连接字段

例子：

查询班级表classinfo和学院表department，了解每个班级所属的学院情况（班级名称，学院名称）

SELECT cname,dname 
FROM classinfo, department 
WHERE classinfo.dno=department.dno;

内连接与外连接

在MySQL中，连接（Join）是一种用于联合两个或多个表中数据的操作。连接分为内连接（Inner Join）和外连接（Outer Join），它们之间的主要区别在于连接条件不满足时对结果的处理方式。

内连接（Inner Join）：

内连接是最常见的连接类型，它返回两个表中满足连接条件的行。语法如下：

SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

这个查询将返回两个表中在连接条件上匹配的行。如果某一行在其中一个表中没有匹配行，则不会包含在结果中。

外连接（Outer Join）：

外连接分为左外连接（Left Outer Join）、右外连接（Right Outer Join）和全外连接（Full Outer Join）。它们的区别在于连接条件不满足时对左表或右表的处理方式。

左外连接（Left Outer Join）：

左外连接返回左表中所有行，以及与左表中的行匹配的右表中的行。如果右表中没有匹配的行，那么结果集中右表的列将包含 NULL 值。

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

右外连接（Right Outer Join）：

右外连接与左外连接相反，返回右表中所有行，以及与右表中的行匹配的左表中的行。如果左表中没有匹配的行，那么结果集中左表的列将包含 NULL 值。

SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

合并查询结果

合并查询结果使用的是 UNION 操作符，它用于组合两个或多个 SELECT 语句的结果集，并去除重复的行。合并的 SELECT 语句必须具有相同数量的列，且相应位置的列的数据类型必须兼容。

语法如下：

SELECT columns FROM table1
UNION
SELECT columns FROM table2;

要注意以下几点：

1. 列的数量和数据类型必须匹配： 在 UNION 操作中，参与合并的 SELECT 语句必须返回相同数量和类型的列。列的顺序也要一致。

2. 去重： UNION 默认会去除重复的行，如果你希望保留重复行，可以使用 UNION ALL。

SELECT columns FROM table1
UNION ALL
SELECT columns FROM table2;

3. 列名可以指定别名： 如果你想要给 UNION 合并结果的列指定别名，可以在每个 SELECT 子句中使用别名。

SELECT column1 AS alias_name FROM table1
UNION
SELECT column2 AS alias_name FROM table2;

4. 排序： UNION 不会自动对结果进行排序，如果需要按特定的顺序返回结果，可以在最外层的 SELECT 语句中使用 ORDER BY 子句。

SELECT columns FROM (
    SELECT columns FROM table1
    UNION
    SELECT columns FROM table2
) AS merged_result
ORDER BY column1;

通过使用 UNION 操作符，你可以将两个查询的结果集合并成一个结果集，并且自动去除重复的行。

例子：
查询全校师生，按姓名排序后保留前10行记录

(SELECT tno AS 编号, tname AS 姓名 FROM teacher)
UNION
(SELECT sno AS 编号, sname AS 姓名 FROM student) ORDER BY 姓名 LIMIT 10;

当使用order by子句或limit子句时，UNION前后的 (select查询语句必须用小括号括起来

关系规范化理论

关系模式设计的问题

1）数据冗余量大

学学生姓名，课程名，院系和院长重复出现多次，浪费存储空间

2）插入异常一一应该存储的信息无法存储

如果某学生刚入学，没有选课，则由于课程号没有值，但主属性是不能为空的，因此也无法存储学生的学号，姓名， 所在院系这些信息

再如一个院系刚刚成立，没有学生，则院长的信息也无法存储

3）删除异常一一不该删除的数据不得不删

如果某个院系的学生全部毕业了，我们在删除该院系学生信息的同时把课程，院系及院系领导的信息也丢掉了。


4）更新异常一一修改困难，容易造成数据不一致

比如院系领导换人了，那么如果有1万条相关记录，必须一一修改，漏政一处，就会导致数据出问题

再比如，课程名称输入错误，“数据库”写成了 “数据结构” ，则修改量也很大，且容易漏政，导致数据不一致。

再者，赵六换了学院，则需要修改院系和院系领导，遗忘修改院系领导，则也会造成数据混乱


事实上，异常现象产生的根源，就是由于关系模式中属性间内部存在着复杂的依赖关系。

如学生学号和学生姓名、学生学号和院系名称、院系名称和院系系领导之问都存在着依赖关系，这种依赖都称为数据依赖

函数依赖

函数依赖极普遍地存在现实生活中，如学生关系：

学生（学号、姓名、院系、院长）

语义：一个学号只对应一个学生，

一个学生只属于一个学院，

一个院系只有一个院长；

那么

学号一>姓名；学号一>院系；院系一>院长

函数依赖分类：

这些函数依赖的概念对于关系数据库的设计和规范化过程非常重要。通过了解和分析函数依赖，数据库设计者可以更好地规划表结构，减少数据冗余，提高数据完整性和一致性。

平凡函数依赖

这是一种非常简单的函数依赖，指的是A属性的任何一个值都可以确定A属性的其他值。换句话说，A属性完全决定于自身，没有其他属性的参与。

非平凡函数依赖

相对于平凡函数依赖，非平凡函数依赖是指属性之间存在一些关系，其中A属性的值不仅仅由自身决定，还受到其他属性的影响。这是关系数据库中更为常见和有用的一种函数依赖。

完全函数依赖

完全函数依赖是指在一个组合键（Composite Key）中的任何一个属性都不能单独决定另外一个属性。只有整个组合键能够唯一地确定其他属性的值。这主要涉及到复合主键的情况。


部分函数依赖

与完全函数依赖相对，部分函数依赖指的是组合键中的一部分属性能够唯一地决定其他属性的值，而不需要整个组合键。这在设计数据库时可能会引起冗余数据的问题。


传递函数依赖

传递函数依赖发生在三个或更多属性之间，其中A决定B，B决定C，从而A间接地决定C。传递函数依赖可能导致冗余数据，因此在数据库设计中应当尽量避免。


范式。

1NF ：

• 数据表中的每个字段都包含不可再分的原子数据。
• 每一列都是单一值，不包含集合、数组或其他非原子数据类型。

2NF：

• 数据表必须符合第一范式。
• 所有非主键字段完全依赖于主键，而不是部分依赖。
• 也就是说，表中的每个非主键字段都要直接依赖于整个主键，而不是主键的一部分。


3NF：

• 数据表必须符合第二范式。
• 所有非主键字段之间不存在传递依赖。
• 换句话说，如果有一个字段依赖于另一个非主键字段，那么这两个字段应该共同成为一个表的主键。


BCNF ：

• 数据表必须符合第三范式。
• 针对主键的任何非平凡依赖关系，主键的所有属性都必须是这个关系的超键。

通俗地讲，当且仅当关系中的每个函数依赖的决定因子都是候选码时， 该范式即为BCNF。

BC范式不存在非主属性和主属性对码的部分函数依赖，和传递函数依赖

关系模式的分解准则

模式分解的目的：

减少数据冗余，消除关系模式的操作异常消除关系模式中存在异常的步骤：

1、消除非主属性对码的部分函数依赖

2、消除非主属性对码的传递函数依赖

3、消除主属性对码的部分和传递函数

依赖模式分解准则：

模式分解具有无损连接性；

模式分解能够保持函数依赖



数据库设计概述

基本步骤

需求分析：了解和分析用户需求（信息需求和处理需求）

概念结构设计：根据需求分析，进行综合、归纳和抽象， 形成独立于DBMS的概念模型 （E-R模型）。

逻辑结构设计：将E-R模型转化为数据模型（关系模型）， 并进行规范化处理，形成模式。设计视图，形成外模式。

物理设计：针对应用，选择最适合的物理存储结构和存取方法。设计索引，形成内模式

数据库实施：数据库的建立和针对应用的操作行为的实现。

数据库运行与维护：数据库正式运行后的备份，转储，恢复，安全性和完整性控制，性能简单与改进，甚至是数据

库的重构等


需求分析

重点是两个“流”

“数据流” 、“处理流”

数据流图的符号

矩形表示外部实体，反映数据的源和去向；

带箭头的连线表示数据流，反映数据的流动方向；

椭圆或圆表示加工，反映对数据的加工处理动作；

用两条平行线表示存储一一信息的静态存储，可以代表文件、文件的一部分、数据库的元素等，反映数据的存档情況。/


概念模型设计



设计局部E-R模型

一般有三种抽象方法：

分类、概括、聚集

分类：将客观世界中具有某些共同特征和行为的对象抽象为一个实体（类），比如学生底下有小明、小红、小李

概括：概括定义对象类型间的超类与子类的关系，将具有共同属性和行为的实体集类抽象出来，形成一般，更抽象的实体集类，比如学生底下有本科生和研究生

聚集：聚集定义将某类型的组成成分，将对象的组成成分抽象为实体的属性，比如学生底下有学号、姓名、性别




全局概念设计

解决冲突，合并局部E-R图并生成初步E-R图

•属性冲突：同一属性在不同局部E-R图其类型或取值范围不同

•命名冲突：同名异义，异名同义

•结构冲突：不同局部E-R因，同一实体属性不同；联系类型不同；或同一事物有时是实体，有时是属性




逻辑结构设计

逻辑结构设计阶段任务：

E-R图向关系模型的转换

并对数据模型进行优化

设计外模式



物理结构设计

数据库的物理设计通常分为两步：

确定数据库的物理结构；

对物理结构进行时问和空间效率的评价。

内容和方法：

首先，对经常用到的查询和对数据进行更新的事务进行分析，获取物理结构设计所需的参数

对于数据查询，需要得到如下信息：

＞查询所涉及的表；

＞查询条件所涉及的属性；

＞连接条件所涉及的属性；

＞查询结果列表中涉及的属性。

对于更新数据的事务，需得到如下信息：

＞ 更新所涉及的表；

＞ 每个表上的更新条件所涉及的属性；

＞ 更新操作所涉及的属性。

然后：

＞确定数据的存取方法；

＞确定数据的存储结构。


确定存取方法

一般用户可以通过建立索引的方法来加快数据的查询效率。

一般原则:

在经常作为查询条件的属性上建立索引

在经常作为连接条件的属性上建立索引

在经常作为分组依据列的属性上建立索引

对经常进行连接操作的表可以建立索引


确定存储结构

一般的存储方式有：

＞顺序存储

＞散列存储

＞聚集存储

物理结构设计的评价

具体包括：

＞查询和响应时间

＞更新事务开销

＞生成报告开销

＞主存储空间的开销

＞辅助存储空间的开销

数据运行与维护

具体维护工作包括：

＞数据库的备份和恢复

＞数据库的安全性和完整性控制

＞监视、分析、调整数据库性能

＞数据库的重组