SQL自学笔记

最新推荐文章于 2024-09-11 10:34:24 发布

小千nothing

最新推荐文章于 2024-09-11 10:34:24 发布

阅读量877

点赞数

文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/weixin_42262796/article/details/129407631

版权

文章介绍了数据库的基本概念，如数据、数据库、数据库管理系统，强调了使用数据库的原因，如数据独立性和多应用共享。详细讨论了关系模型，包括主键和外键的概念，并解释了SQL语言在创建表、插入、更新和删除数据，以及执行查询等方面的作用。此外，还提到了ER图在数据库设计中的重要性。

摘要由CSDN通过智能技术生成

参考资料

作者：littleqian
（用rmarkdown写的笔记，转化为markdown图片不能显示，格式也有点问题。懒得改了，如果有需要可以问我要html的笔记）

有灵感的叶子博客
youtube
sql初学视频
youtube视频官方配套笔记
数据库系统概论王珊
database system concepts
所用工具：mySQL+popSQL

基础概念

data: 数据自身可能有结构，eg 经度和纬度共同与地点绑定。
database:按照一定数据模型长期存储在计算机内的大量数据。
database management system:
和操作系统类似，是软件。通过它来定义database中的数据、组织和存储数据库中的数据、操纵数据etc。总之，它是操作database的软件。用户使用应用时，需要链接数据。这时，应用通过数据库管理系统操作数据库中的数据供用户使用。eg.
mySQL etc.
SQL(structured query language) is the language that we can use to
interact with those relational database management systems. SQL
implementations vary between systems.
database queries: queries are requests made to the database
management system for specific information. eg. a google search is a
query.

为什么要使用数据库？

以往是一组数据对应于一个应用程序，数据共享性差，冗余性大。eg
ios系统微信要用到onedrive里的文件，要复制一份到微信里头。
数据独立性差。数据依赖于应用程序，不同的应用程序需重设数据的存储结构。我们想要做到应用程序只需要处理数据的逻辑结构，当数据的物理存储改变时应用程序不变。这样将数据的定义从程序中分离，可以简化应用程序的编制，减少应用程序的维护和修改。
多用户、多应用共享数据的要求。数据库共享涉及许多问题：数据的不一致性，ie同一数据的不同副本的值不一样；数据的安全性；数据的完整性；数据库恢复。

数据库系统的建立，从现实到计算机的抽象过程：

概念模型，按照用户的观点对数据和信息建模，用来设计数据库。
逻辑模型，按照计算机的观点对数据建模。eg
网状模型，关系模型，面向对象模型etc。需要定义下面三个方面：
- 数据结构（def），eg树，网，关系模型（表格）。
- 数据操作，主要包含查询和更新
- 完整性约束条件，确保数据合理性
物理模型，在磁盘上的存储方法。

关系模型

A relational database consists of a collection of tables, each of which
is assigned a unique name.

A row in a table represents a relationship among a set of values. Table
is a collection of such relationships. 有如下对应关系：

a column of a table —– attributes，属性的取值有范围，called the
domain of that attribute.
a row of a table—— tuple(与顺序无关), a sequence of values
a specific set of rows —— relation instance
table ——– relation, since a relation is a set of tuples, relations
cannot contain duplicate tuples.

In practice, tables in database systems are permitted to contain
duplicates unless a specific constraint prohibits it.

We require that, the domains of all attributes of relations be
atomic(不可分割的).

The null value is a special value that signifies that the value is
unknown of does not exist.处理null value是麻烦的。

primary key & foreign key

主键(primary
key)是指表中某个能唯一标识一条记录的属性，不能有重复的，也不能为空。在一个表中，一定存在主键。一条记录中有若干个属性，若其中某一个属性组能唯一标识一条记录，该属性组就可以成为一个主键，若两个属性组各自都不能唯一标识一条记录，但合起来能够唯一标识一条记录，这也可以成为主键。

外键(foreign
key)指的是当前表连接到别的表的键，将别的表中的主键存储在当前表上(这样，当前表的一条记录，可以唯一对应到其他表的一条记录)。外键的作用是维护用于表和表之间的关联。

No two tuples in a relation are allowed to have exactly the same value
for all attributes. A superkey is a set of one or more attributes
that, taken collectively, allow us to identify uniquely a tuple in the
relation. We are often interested in superkeys for which no proper
subset is a superkey. Such minimal superkeys are called candidate
keys. We shall use the term primary key(primary key constraints)
to denote a candidate key that is chosen by the database designer as the
principal means of identifying tuples within a relation. The primary key
should be chosen such that its attribute values are never, or are very
rarely, changed. 下图展示了一个数据库，primary-key attributes are
shown underlined. Foreign-key constraints appear as arrows from the
foreign-key attributes of the referencing relation to the primary key of
the referenced relation.(a foreign key is a primary key of another
table. A foreign key allows us to link up or define relationships
between tables.)

Schema diagram for the university
database

The relational algebra

The relational algebra consists of a set of operations that take one or
two relations as input and produce a new relation as their
result.(i.e.输入一个或者两个图表，输出一个图表的运算)

unary operations: select, project, and rename operations. They
operate on one relation.
- select:
  按照给定的条件，选出某些行。σ_{deptname = “Physics” ∧ salary > 90000} (instructor )
- project:
  选出某些列。Π_{I**D, name,salary/ 12}( instructor )
- rename：重命名relation和attribute.
binary operations: union, Cartesian product, and set difference,
operate on pairs of relations.
- cartesian-product: A Cartesian product of database relations
  differs in its definition slightly from the mathematical
  definition of a Cartesian product of sets. Instead of
  r₁ × r₂ producing pairs
  (t₁,t₂) of tuples from r₁
  and r₂, the relational algebra concatenates
  t₁ and t₂ into a single tuple.
  输出的关系的属性怎么命名呢？一个想法是在属性前加上原来的relation
  name作为前缀。这可能会产生一些问题，需要用到rename操作来解决。
- join: The join operation allows us to combine a selection and a
  Cartesian product into a single operation.
  可以理解为两个列表按照相同的某列进行融合。
- union:
  两个relation要能做并集，需要满足两个条件：1，属性的数目相同；2，ith的属性的type相同。满足这两个条件的relations称为compatible
  relations。同样的，可以在compatible
  relations上定义intersection，set-difference。

除了上述这些relational algebra operations外，还有其他的运算。

注意到，relation对relational-algebra运算封闭，因此，我们可以组合使用这些运算。有的时候，我们可以用不同的方式写出等价的queries，the
algebraic structure of relational algebra makes it easy to find
efficient but equivalent alternative expressions.

Introduction to SQL

Structured Query Language or SQL (pronounced
“sequel”)是一种声明式编程语言( declarative programming language).
Python就是 procedural programming language.

SQL can do much more than just query a database. It can define the
structure of the data(define database schemas), modify data in the
database, specify security constraints, and control access to the data
in the database.

SQL 是一个混合语言，包括以下四种语言

DQL (Data Query Language): 数据查询语言，查询数据库中的信息
DDL (Data Definition Language): 数据定义语言，用于定义数据库中的表
DCL (Data Control Language):
数据控制语言，控制数据库中的数据的访问权限
DML (Data Manipulation Language):
数据操作语言，在数据库中插入、更新、删除数据。

create tables(database schemas)

在创建表之前，我们要首先了解 MySQL 中的数据类型，MySQL
中主要包括以下六种数据类型

INT: 所有的整数
DECIMAL(M, N): 实数，M 表示有效位数，N 表示小数位数
VARCHAR(l): 长度为 l 的字符串
BLOB: 二进制的大对象，存储大数据
DATE: 日期格式，YYYY-MM-DD
TIMESTAMP: 时间戳，YYYY-MM-DD HH:MM:SS

CREATE TABLE student (
    student_id INT,
    name VARCHAR(20),
    major VARCHAR(20),
    PRIMARY KEY(student_id)
);

查看以及修改表格属性。

DESCRIBE student; --看表 student 的基本信息
DROP TABLE student; --删除表 student
ALTER TABLE student add gpa DECIMAL(3, 2);  --在表中再加上新属性gpa
ALTER TABLE student DROP COLUMN gpa;  --在表中删除某一个属性gpa.(为什么不能删除一行？现在是在创造schema呀，没有行的概念)

在创建表时，对部分属性需要添加一些约束，例如非空、唯一等等。在 MySQL
中，通过 NOT NULL 和 UNIQUE
来指定非空和唯一的约束，这样在插入数据时这些数据值就需要满足相应的约束。如果一个属性可空，那在插入数据时，如果不指定这个属性的值，这个属性的值默认为
NULL，我们也可以通过 DEFAULT 来指定默认值。

对于主键，可以使用 AUTO_INCREMENT(只能在主键处使用)
设置主键的值是自动增加的，这样在插入数据时就不需要显式设置主键。

CREATE TABLE student(
    student_id INT AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    major VARCHAR(20) DEFAULT 'undecided',
    PRIMARY KEY(student_id)
);

-- 插入数据
INSERT INTO student VALUES (1, 'Quicy', 'Mathematic');
INSERT INTO student(name, major) VALUES ('quicy', 'Mathematica');
INSERT INTO student(name) VALUES ('Jack');

Update & delete rows

-- 更新数据，下述代码表示将 student 表中major是biology或者chemistry的学生的专业改为 Biochemistry, 姓名改为Tom
UPDATE student 
SET major = 'Biochemistry', name = 'Tom'
WHERE major = 'Biology' OR major = 'Chemistry';


-- 删除数据
DELETE FROM student 
WHERE name = 'Jack';

在上述两个语句中，WHERE
后面代表的都是对所有数据的一个筛选条件，我们可以不加 WHERE
语句，那更新和删除数据就是对所有的数据进行操作。在 WHERE
语句后，我们可以添加多种筛选条件，并利用 AND 或者 OR 连接。在 MySQL
中，除了等于符号，还有 <、>、<=、>= 等等，不等于的符号是
<>，还有 IN 和 NOT IN 等等。

Queris

通过 SELECT * FROM table; 可以获取名为 table
的表的所有数据。但我们通常并不是很想获取全部的数据，如果我们只想获取某一些列的数据，那将查询语句中的
* 更改为对应的属性即可

SELECT name, major FROM student;

我们还可以对查询出来的数据进行排序，只需在后面加上 ORDER
BY即可指定按某一个或多个属性进行排序，如果需要逆序，使用 DESC。

SELECT student.name, student.major FROM student ORDER BY student_id DESC; -- 默认ASC 升序

在数据很多的情况下，我们可以通过 LIMIT 命令来限制输出多少个记录。

SELECT student.name, student.major FROM student ORDER BY student_id DESC LIMIT 1;

我们也可以通过 WHERE 语句来对数据进行筛选。

SELECT name,major   --select: 要获取信息
FROM student
ORDER BY name DESC --ASC 升序; ORDER BY major, student_id; 
LIMIT 2
WHERE major = 'Biology'; -- <, >, <=, >=, <> not, AND, OR; WHERE name IN ('Clarie', 'Kate', 'Mike');

Create more complex database

公司数据库: 红色primary key; 绿色: foreign key; 蓝色: normal
attributes

在分析了公司的数据库之后，我们就可以开始使用 SQL
建立公司数据库。对于每一个表，在用 SQL
建立时，要显示指出每个属性的数据类型及主键与外键等。外键要指出哪一个属性指向了哪个表的哪一个属性，并设置删除时的动作。

通常，链接到的表中内容删除时，当前表也需要做一些变化，有两种常见变化：1.
将外键设置为空，2. 当前表删除这些记录。在 SQL 代码上，对应为
ON DELETE SET NULL 和 ON DELETE CASCADE。 (Delete entries in the
database when they have foreign keys associated to them.
当我们删除的条目里，有属性是其他table指向的foreign key的情况)

-- 注意创建table和添加foreign key的顺序！！
CREATE TABLE employee (
    emp_id INT PRIMARY KEY,
    first_name VARCHAR(40),
    last_name VARCHAR(40),
    birth_day DATE,
    sex VARCHAR(1),
    salary INT,
    super_id INT,
    branch_id INT
);

CREATE TABLE branch (
    branch_id INT PRIMARY KEY,
    branch_name VARCHAR(40),
    mgr_id INT,
    mgr_start_data DATE,
    FOREIGN KEY(mgr_id) REFERENCES employee(emp_id) ON DELETE SET NULL
);

ALTER TABLE employee ADD FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE SET NULL;

ALTER TABLE employee ADD FOREIGN KEY(super_id) REFERENCES employee(emp_id) ON DELETE SET NULL;

CREATE TABLE client (
    client_id INT PRIMARY KEY,
    client_name VARCHAR(40),
    branch_id INT,
    FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE SET NULL
);

CREATE TABLE works_with (
    emp_id INT,
    client_id INT,
    total_sales INT,
    PRIMARY KEY(emp_id, client_id),
    FOREIGN KEY(emp_id) REFERENCES employee(emp_id) ON DELETE CASCADE,
    FOREIGN KEY(client_id) REFERENCES client(client_id) ON DELETE CASCADE
);

CREATE TABLE branch_supplier (
    branch_id INT,
    supplier_name VARCHAR(40),
    supply_type VARCHAR(40),
    PRIMARY KEY(branch_id, supplier_name),
    FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE CASCADE
);

通过 INSERT INTO
语句可以在数据库中插入数据，要注意的是，当外键中链接的表的内容不存在时，则无法直接新建这条记录，需要先设置为
NULL，并在外键中链接的表的内容存在时，再更新这条记录的值。

INSERT INTO employee VALUES (100, 'David', 'Wallace', '1967-11-17', 'M', 250000, NULL, NULL);
INSERT INTO employee VALUES (101, 'Jan', 'Levinson', '1961-05-11', 'F', 110000, 100, NULL);
INSERT INTO employee VALUES (102, 'Michael', 'Scott', '1964-03-15', 'M', 75000, 100, NULL);
INSERT INTO employee VALUES (103, 'Angela', 'Martin', '1971-06-25', 'F', 63000, 102, NULL);
INSERT INTO employee VALUES (104, 'Kelly', 'Kapoor', '1980-02-05', 'F', 55000, 102, NULL);
INSERT INTO employee VALUES (105, 'Stanley', 'Hudson', '1958-02-05', 'M', 69000, 102, NULL);
INSERT INTO employee VALUES (106, 'Josh', 'Porter', '1969-09-05', 'M', 78000, 100, NULL);
INSERT INTO employee VALUES (107, 'Andy', 'Bemard', '1973-07-22', 'M', 65000, 106, NULL);
INSERT INTO employee VALUES (108, 'Jim', 'Halpert', '1978-10-01', 'M', 71000, 106, NULL);

INSERT INTO branch VALUES (1, 'Corporate', 100, '2006-02-09');
INSERT INTO branch VALUES (2, 'Scranton', 102, '1992-04-06');
INSERT INTO branch VALUES (3, 'Stamford', 106, '1998-02-13');

UPDATE employee SET branch_id = 1 WHERE emp_id In (100, 101);
UPDATE employee SET branch_id = 2 WHERE emp_id In (102, 103, 104, 105);
UPDATE employee SET branch_id = 3 WHERE emp_id In (106, 107, 108);

INSERT INTO client VALUES (400, 'Dunmore Highschool', 2);
INSERT INTO client VALUES (401, 'Lackawana Country', 2);
INSERT INTO client VALUES (402, 'FedEx', 3);
INSERT INTO client VALUES (403, 'John Daly Law, LLC', 3);
INSERT INTO client VALUES (404, 'Scranton Whitepages', 2);
INSERT INTO client VALUES (405, 'Times Newspaper', 3);
INSERT INTO client VALUES (406, 'FedEx', 2);

INSERT INTO works_with VALUES (105, 400, 55000);
INSERT INTO works_with VALUES (102, 401, 267000);
INSERT INTO works_with VALUES (108, 402, 22500);
INSERT INTO works_with VALUES (107, 403, 5000);
INSERT INTO works_with VALUES (108, 403, 12000);
INSERT INTO works_with VALUES (105, 404, 33000);
INSERT INTO works_with VALUES (107, 405, 26000);
INSERT INTO works_with VALUES (102, 406, 15000);
INSERT INTO works_with VALUES (105, 406, 130000);

INSERT INTO branch_supplier VALUES (2, 'Hammer Mill', 'Paper');
INSERT INTO branch_supplier VALUES (2, 'Unit ball', 'Writing Utensils');
INSERT INTO branch_supplier VALUES (3, 'Patriot Paper', 'Paper');
INSERT INTO branch_supplier VALUES (2, 'J.T. Forms & Labels', 'Custom Forms');
INSERT INTO branch_supplier VALUES (3, 'Unit ball', 'Writing Utensils');
INSERT INTO branch_supplier VALUES (3, 'Hammer Mill', 'Paper');
INSERT INTO branch_supplier VALUES (3, 'Stamford Labels', 'Custom Forms');

以这个公司数据库为例子，练习一些查询语句。

-- 查找所有的员工
SELECT * FROM employee;

-- 查找所有客户
SELECT * FROM client;

-- 查找所有的员工，并按照工资排序
SELECT * FROM employee ORDER BY salary;

-- 查找所有的员工，并按照工资逆序排序
SELECT * FROM employee ORDER BY salary DESC;

-- 查找所有的员工，并先按照性别排序，再按照姓名排序
SELECT * FROM employee ORDER BY sex, first_name, last_name;

-- 查找表格中前五个员工
SELECT * FROM employee LIMIT 5;

-- 查找所有员工的姓名
SELECT employee.first_name, employee.last_name FROM employee;

-- 查找所有员工的姓名，并将列名记为 forename 和 surname
SELECT employee.first_name AS forename, employee.last_name AS surname FROM employee;

-- 查找所有员工的不同的性别
SELECT DISTINCT employee.sex FROM employee;

-- 查找所有员工所属的不同分公司的 id
SELECT DISTINCT employee.branch_id FROM employee;

function

MySQL 中包括很多函数，例如统计个数 (COUNT)、计算平均值 (AVG)、求和 (SUM)
等等，这里给出几个例子

-- 统计员工的个数
SELECT COUNT(employee.emp_id) FROM employee;

-- 统计在 1970 年后出生的女员工的个数
SELECT COUNT(employee.emp_id) FROM employee WHERE sex = 'F' AND birth_day >= '1971-01-01';

-- 计算所有员工的平均工资
SELECT AVG(employee.salary) FROM employee;

-- 计算所有员工的工资之和
SELECT SUM(employee.salary) FROM employee;

-- 统计有多少个男员工和多少个女员工
SELECT COUNT(employee.sex), sex FROM employee GROUP BY sex;
SELECT COUNT(employee.sex) FROM employee GROUP BY sex; -- 比较区别


-- 计算销售员工的所有销售额
SELECT SUM(works_with.total_sales), works_with.emp_id FROM works_with GROUP BY emp_id;

通配符

在查询时，我们有时不知道要查询的具体内容，只知道一部分，这时候就需要使用到通配符。用
% 表示匹配一个或多个任意字符，用 _ 表示匹配单个字符。

-- 查找所有是 LLC 的客户
SELECT * FROM client WHERE client_name LIKE '%LLC';

-- 查找所有包含 label 的供应商
SELECT * FROM branch_supplier WHERE supplier_name LIKE '%Label%';

-- 查找所有在 2 月出生的员工
SELECT * FROM employee WHERE birth_day LIKE '____-02%';

Union & Join (binary operations)

Union
可以将两个表格合并为一个大表格，将第二个表格的内容接在第一个表格的末尾，这就需要这两个表格有相同的列数，否则无法进行
Union 操作。

-- 查找所有的员工和分公司的名字
SELECT employee.first_name FROM employee
UNION
SELECT branch.branch_name FROM branch;

-- 查找所有的客户和供应商的名字和对应的分公司 ID
SELECT client.client_name, client.branch_id FROM client
UNION
SELECT branch_supplier.supplier_name, branch_supplier.branch_id FROM branch_supplier;

-- 查找公司所有花的钱和赚的钱
SELECT employee.salary FROM employee
UNION
SELECT works_with.total_sales FROM works_with;

Join
可以将两个表格合并为一个大表格，将第二个表格的数据以新的列的形式加入到第一个表格中。我们需要在这两个表格中都选择一个基准的属性，如果第一个表格的某条记录的基准属性和第二个表格的某条记录的基准属性满足条件
(这个条件在 ON
语句之后定义)，则将这两条记录连接起来。包括以下几种连接方式

默认连接: 即这两个记录的基准属性都不能为空
左连接:
第一个表格的所有记录都必须被连接，如果在第二个表格中无法匹配到记录，则设置为
NULL
右连接:
第二个表格的所有记录都必须被连接，如果在第一个表格中无法匹配到记录，则设置为
NULL
全连接: (MySQL 不支持)
两个表格的所有记录都必须被连接，如果在另一个表格中无法匹配到记录，则设置为
NULL

在开始测试这几种连接方式之前，我们在分公司的表中插入一条数据，它的管理者和创建日期为空。

INSERT INTO branch VALUES(4, 'Buffalo', NULL, NULL);

我们查看一下员工表和分公司表中的数据。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RW0JJPDz-1678266855282)(pic/2.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JlhjayN3-1678266855285)(pic/3.png)]

然后我们比较一下这三种连接方式的区别。

-- 查找所有的分公司及他们的管理者的名字，默认连接
SELECT employee.emp_id, employee.first_name, branch.branch_name FROM employee
JOIN branch
ON employee.emp_id = branch.mgr_id;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dfoLH2G1-1678266855288)(pic/4.png)]

-- 查找所有的分公司及他们的管理者的名字，左连接
SELECT employee.emp_id, employee.first_name, branch.branch_name FROM employee
LEFT JOIN branch
ON employee.emp_id = branch.mgr_id;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zX52UOIy-1678266855289)(pic/5.png)]

-- 查找所有的分公司及他们的管理者的名字，右连接
SELECT employee.emp_id, employee.first_name, branch.branch_name FROM employee
RIGHT JOIN branch
ON employee.emp_id = branch.mgr_id;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-T59g1cg9-1678266855290)(pic/6.png)]

嵌套查询

在查询过程中，我们通常需要查询两个表的数据。例如在 branch
表中，只给出了分公司的 id、名字、管理者
id、创立日期这四个属性，但我们其实更关心的并不是管理者 id
是什么，而是管理者叫什么名字。这样，我们就需要在 branch 表中查询管理者
id，然后再在 employee 表中查询姓名。

通过 SELECT
语句返回的查询结果是一个表，我们可以在这个查询结果的基础上再进行查询，这里给出两个例子。

-- 查找所有给单个客户销售三万以上的员工的名字
SELECT employee.first_name, employee.last_name FROM employee WHERE employee.emp_id IN (
    SELECT works_with.emp_id FROM works_with WHERE works_with.total_sales > 30000
);

-- 查找 Michael Scott 管理的分公司的所有客户的名字
SELECT client_name FROM client WHERE branch_id IN (
    SELECT branch.branch_id FROM branch WHERE mgr_id = (
        SELECT employee.emp_id FROM employee WHERE employee.first_name = 'Michael' AND employee.last_name = 'Scott'
    )
);

triggers

A trigger is a statement that the system executes automatically as a
side effect of a modification to the database. To define a trigger, we
must: - Specify when a trigger is to be executed. This is broken up into
an event that causes the trigger to be checked and a condition that must
be satisfied for trigger execution to proceed. - Specify the actions to
be taken when the trigger executes.

CREATE TABLE trigger_test(
  message VARCHAR(100)
);

-- 需要在mySQL command line中才能使用DELIMITER(分隔符)

-- trigger 1
DELIMITER $$

CREATE
  TRIGGER my_trigger1 BEFORE INSERT -- BEFORE could be AFTER; INSERT的操作也可以改变
  ON employee
  FOR EACH ROW BEGIN
    INSERT INTO trigger_test VALUES('added new employee');
  END$$
  
DELIMITER ; --复原分隔符
    

-- trigger 2
DELIMITER $$

CREATE
  TRIGGER my_trigger2 BEFORE INSERT
  ON employee
  FOR EACH ROW BEGIN
    INSERT INTO trigger_test VALUES(NEW.first_name); -- NEW指代新插入的行
  END$$
  
DELIMITER ; --复原分隔符


-- trigger 3
DELIMITER $$

CREATE
  TRIGGER my_trigger3 BEFORE INSERT
  ON employee
  FOR EACH ROW BEGIN
    IF NEW.sex = 'M' THEN
      INSERT INTO trigger_test VALUES('add male employee');
    ELSEIF NEW.sex = 'F' THEN
        INSERT INTO trigger_test VALUES('add female employee');
    ELSE
        INSERT INTO trigger_test VALUES('add other employee');
    END IF;
  END$$
  
DELIMITER ; --复原分隔符

ER diagram

ER模型是用ER图来描述现实世界的概念模型，ER图可以帮助我们构建database
schemas.首先，由自然语言描述的数据库转化为ER图，再由ER图得到database
schema. ER图的定义如下：

Entity(矩形): an object we want to model and store information
about. eg. student, class
Attributes(椭圆形，用无向边与其实体连接): specific pieces of
information about an entity. eg. name, grade, gpa etc.
primary key(下划线的属性)
composite attribute(sub-sttributes与其composite attribute连接): an
attribute that can be broken up into sub-attributes. eg. name ->
family name & last name
multi-valued attribute(双层椭圆,用无向边与其实体连接): an attributes
that can have more than one value. eg. clubs
derived attribute(虚线椭圆,用无向边与其实体连接): an attribute that
can be derived from the other attributes. eg. has_honors(derived
from gpa)
relationships: defines a relationship between two entities.
- partial participation(single line): 该实体部分的成员都参与该关系
- total participation(double line): 该实体所有的成员都参与该关系
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BnfWeVlw-1678266855291)(pic/7.png)]
relationship attribute(椭圆形，用无向边与其relationship连接): an
attribute about the relationship. eg. grades, only when student
takes class, can grades exist.
relationship cardinality: the number of instances of an entity from
a relation that can be associated with the relation.(1:1; 1:N; N:M)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AuVo3se8-1678266855293)(pic/8.png)]

weak entity(双层矩形): an entity that cannot be uniquely identified
by its attributes alone. eg. exam. exam rely on class. note: weak
entity has to have total participation in the identifying
relationship.(注意exam与has的double line)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lQxeRmqO-1678266855295)(pic/9.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6YoJwXVE-1678266855296)(pic/10.png)]

indentifying relationship(双层菱形): a relationship that serves to
uniquly identify the weak entity.

以上E-R图的画法最早由Peter
Chen提出，称为Chen方法。在Chen方法的基础上，后人有做一些增补和改进，后来的一种比较有名的改进方法叫做Crow’s
foot方法。Crow’s foot方法中，用了一种更自然的方式来表示relationship
cardinality和participation。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-E72YAHx9-1678266855298)(pic/11.png)]

怎么理解上述的crow’foot呢？举个栗子。

下面的图，指的是一个老板对应1-n个工厂；一个工厂对应1-1也就是存在且唯一的一个老板。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HWyX4n0x-1678266855300)(pic/12.jpg)]

下面的图，指的是一个老板对应0-n个工厂；一个工厂对应0-1个老板。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-06vYxRO4-1678266855302)(pic/13.jpg)]

自然语言到ER图

Company Data Storage Requirements

The company is organized into branches. Each branch has a unique number,
a name, and a particular employee who manages it.

The company makes it’s money by selling to clients. Each client has a
name and a unique number to identify it.

The foundation of the company is it’s employees. Each employee has a
name, birthday, sex, salary and a unique number.

An employee can work for one branch at a time, and each branch will be
managed by one of the employees that work there. We’ll also want to keep
track of when the current manager started as manager.

An employee can act as a supervisor for other employees at the branch,
an employee may also act as the supervisor for employees at other
branches. An employee can have at most one supervisor.

A branch may handle a number of clients, with each client having a name
and a unique number to identify it. A single client may only be handled
by one branch at a time.

Employees can work with clients controlled by their branch to sell them
stuff. If nescessary multiple employees can work with the same client.
We’ll want to keep track of how many dollars worth of stuff each
employee sells to each client they work with.

Many branches will need to work with suppliers to buy inventory. For
each supplier we’ll keep track of their name and the type of product
they’re selling the branch. A single supplier may supply products to
multiple branches.

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gEhNmVbR-1678266855305)(pic/14.png)]

我们看branch通过works for与employee联系的部分。含义是：

a employee可以为a~b个branch工作。a = 1,因为work
for与employee之间的participation是双横线，也就是说employee是total
participation的，i.e.每个employee都对应着至少一个branch; b = 1,
因为work for与branch之间的relationship cardinality是1. 总而言之，a
employee对应着唯一一个(且存在)branch.
同样的分析，a branch可以被1-N个employee work for.

用crow’foot 表示如下图:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BfoGCJHO-1678266855308)(pic/15.png)]

ER图到database schema(没弄明白原理)

database schema实际上是一组table(or relation)的集合，ER图是由entity,
attribute and relationship between
entities组成的。因此，将ER图转换为relational database management systems
schema实际就是将entity, attribute and relationship between
entities转换为table.

step 1: Mapping of regular entity types. For each regular entity
type create a relation table that includes all the simple attributes
of that entity.

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9zc8y3rx-1678266855310)(pic/16.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Zz6ioDe3-1678266855311)(pic/17.png)]

step 2: mapping of weak entity types. For each weak entity type
create a relation table that includes all simple attributes of the
weak entity. The primary key of the new relation should be the
partial key of the weak entity plus the primary key of its
owner.(多对多关系)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-utpxEvFb-1678266855312)(pic/18.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VVyOFlfl-1678266855314)(pic/19.png)]

略 [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ciyn48V4-1678266855315)(pic/20.png)]

关系模型的逻辑结构是一组关系模式的集合。 E-R图则是由实体型、实体的属性和
实体型之间的联系三个要素组成的,
所以将E − R图转换为关系模型实际上就是要将实体型、实体的属性和实体型之间的联系转换为关系模式。下面介绍转换的一般原则。
实体型转换为一个关系模式,关系的属性就是实体的属性,关系的码就是实体的码。
对于实体型间的联系有以下不同的情况:

一个 1 : 1 联系可以转换为一个独立的关系模式,
也可以与任意一端对应的关系模式合并。如果转换为一个独立的关系模式,
则与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性,
每个实体的码均是该关系的候选码。如果与某一端实体对应的关系模式合并,
则需要在该关系模式的属性中加入另一个关系模式的码和联系本身的属性。
一个 1 : n 联系可以转换为一个独立的关系模式, 也可以与 n
端对应的关系模式合并。如果转换为一个独立的关系模式,
则与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性,
而关系的码为 n 端实体的码。
一个 m : n 联系转换为一个关系模式,
与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性,
各实体的码组成关系的码或关系码的一部分。
三个或三个以上实体间的一个多元联系可以转换为一个关系模式。与该多元联系
相连的各实体的码以及联系本身的属性均转换为关系的属性,
各实体的码组成关系的码或关系码的一部分。
具有相同码的关系模式可合并。