零基础学习SQL -- 实战练习

为了能让大家对于SQL有更好的理解，在这里作者通过分析一个练习对于之前的内容进行一个整体的复习。

1、ER（Entity Relationship）图

ER图也就是实例关系图是创建数据库的第一步，工程师通过分析将实际的需求进行加工构建一个ER图然后通过ER图开始一步一步创建各个表。在这里，我直接提供一个ER图作为模板进行分析。

注意

• ER图中的元素分为三种：

1. 灰色矩形：实例，实例都会被创建为表，有双边框的实例称为弱实例，该类实例必须依附在普通实例上，普通实例不存在，弱实例也就没有了意义
2. 橘色菱形：关系，关系主要反映了实例之间的相关性，主键或外键会通过关系设定
3. 蓝（红）色椭圆：属性，属性反映了每个实例的对象所要拥有的值，键的设定参考属性。边框为虚线的属性被称作被必须属性，可以不被考虑

那么如何将ER图转换为表呢，这就需要按顺序进行以下几个步骤：

1. 映射普通实例，将属性映射为键，同时选定主键，一般来说，选择值唯一且不可为空的属性作为主键
2. 映射弱实例，将属性映射为键同时选定主键，另外，还要根据关系将关联的实例的主键作为主键和外键
3. 映射1:1关系，分别将对方的主键设置为外键
4. 映射1:N关系，N的一边将1一边的主键设置为外键
5. 映射N:M关系，为了避免数据的重复，创建一个新表，映射关系并且将两端实例的主键作为主键和外键

完成这5个步骤后，我们会得到一个简单的表的框架

那么接下来，我们就要使用SQL创建这些表啦。

2、创建表

1. employee

CREATE TABLE employee (
	emp_id INT PRIMARY KEY,
	first_name VARCHAR(20),
	last_name VARCHAR(20),
	birth_date DATE,
	sex VARCHAR(1),
	salary INT,
	super_id INT,
	branch_id INT
);

2. branch

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

注意：

• 定义外键一定要在关联的主键被定义之后，使用 FOREIGN KEY(外键名称) REFERENCES 关联的表(关联主键名称)
• ON DELETE SET NULL 在外键不是该表的主键是使用，当关联表的数据被删除时，该外键自动被赋值为 NULL

3. 修改emplyee（添加外键）

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;

4. client

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
);

5. works_with

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
);

注意：

• ON DELETE CASCADE 在外键是该表的主键是使用，当关联表的数据被删除时，该表的有关行全部被删除***

6. branch_supplier

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
);

完成后不要忘记检查哦

DESCRIBE employee;
DESCRIBE branch;
DESCRIBE client;
DESCRIBE works_with;`在这里插入代码片`
DESCRIBE branch_supplier;

3、插入数据

在这里我提供给大家一些数据

插入employee数据

INSERT INTO employee VALUES(100, 'David', 'Wallace', '1967-11-17', 'M', 250000, NULL, NULL);

注意：
在插入外键的数据时，如果关联的数据不存在，就要先插入 NULL 值，等到关联数据插入到相应位置后，在通过 ***UPDATE***更新数据

插入branch数据

INSERT INTO branch VALUES(1, 'Corporate', 100, '2006-02-09');

更新数据

UPDATE employee
SET branch_id = 1
WHERE emp_id = 100;

继续插入employee数据

INSERT INTO employee VALUES(101, 'Jan', 'Levinson', '1961-05-11', 'F', 110000, 100, 1);
INSERT INTO employee VALUES(102, 'Michael', 'Scott', '1964-03-15', 'M', 75000, 100, NULL);

遇到主键数据不存在

INSERT INTO branch VALUES(2, 'Scranton', 102, '1992-04-06');

UPDATE employee
SET branch_id = 2
WHERE first_name = 'Michael';

继续插入employee数据

INSERT INTO employee VALUES(103, 'Angela', 'Martin', '1971-06-25', 'F', 63000, 102, 2);
INSERT INTO employee VALUES(104, 'Kelly', 'Kapoor', '1980-02-05', 'F', 55000, 102, 2);
INSERT INTO employee VALUES(105, 'Stanley', 'Hudson', '1958-02-19', 'M', 69000, 102, 2);
INSERT INTO employee VALUES(106, 'Josh', 'Porter', '1969-09-05', 'M', 78000, 100, NULL);

遇到主键数据不存在

INSERT INTO branch VALUES(3, 'Stamford', 106, '1998-02-13');

UPDATE employee
SET branch_id = 3
WHERE emp_id = 106;

继续插入employee数据

INSERT INTO employee VALUES(107, 'Andy', 'Bernard', '1973-07-22', 'M', 65000, 106, 3);
INSERT INTO employee VALUES(108, 'Jim', 'Halpert', '1978-10-01', 'M', 71000, 106, 3);

插入branch_supplier数据

INSERT INTO branch_supplier VALUES(2, 'Hammer Mill', 'Paper');
INSERT INTO branch_supplier VALUES(2, 'Uni_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(2, 'Uni_ball', 'Writing Utensils');
INSERT INTO branch_supplier VALUES(3, 'Hammer Mill', 'Paper');
INSERT INTO branch_supplier VALUES(3, 'Stamford Lables', 'Custom Forms');

插入client数据

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);

插入works_with数据

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);

完成后进行检查

SELECT * FROM employee;
SELECT * FROM branch;
SELECT * FROM branch_supplier ORDER BY supplier_name;
SELECT * FROM client;
SELECT * FROM works_with ORDER BY client_id;

4、查询

嵌套查询

• 找到营业额超过30000的员工的名字

SELECT first_name, last_name
FROM employee
WHERE emp_id IN(
	SELECT emp_id 
	FROM works_with
	WHERE total_sales > 30000
);

这里就可以通过使用嵌套查询将两个表的信息进行整合，找到正确的结果。下面再看一个例子

• 找到Michael Scott 管理的公司的客户,（Michael 的ID已知）

SELECT client.client_name
FROM client
WHERE client.branch_id = (
	SELECT branch.branch_id 
	FROM branch
	WHERE mgr_id = 102
);

在这里可以发现使用了 “=” ，这就说明 “=” 和 IN 的作用是相同的

JOIN查询

• 查找所有品牌和他们负责人的名字

SELECT branch.branch_name, employee.emp_id, employee.first_name
FROM employee
JOIN branch
ON branch.mgr_id = employee.emp_id;

在这里，JOIN的作用只是简单将两个表进行关联，找到ID相同的行

在介绍 LEFT JOIN 和RIGHT JOIN 之前，我们先来添加一组数据作为对照

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

在数据结果之前，大家可以思考一下结果会有什么不同

SELECT branch.branch_name, employee.emp_id, employee.first_name
FROM employee
LEFT JOIN branch
ON branch.mgr_id = employee.emp_id;

SELECT branch.branch_name, employee.emp_id, employee.first_name
FROM employee
RIGHT JOIN branch
ON branch.mgr_id = employee.emp_id;

这是LEFT JOIN的结果

这是RIGHT JOIN的结果

• 大家可以发现，在使用LEFT JOIN时，位于左边的表中的所有的信息都被输出，而对应右边的表只有匹配的数据被输出。而在使用RIGHT JOIN时情况刚好相反。
• 你这样做有什么好处呢，最明显的好处就是当你侧重想查看某个表的信息和与其相关联的信息时，这样做会更加简洁明了

UNION查询

UNION查询最直接的作用就是将多条查询语句的结果整合在一个输出域中。

SELECT first_name AS all_names
FROM employee
UNION
SELECT branch_name
FROM branch
UNION
SELECT client_name
FROM client;

结果如下

在这里，为了美观和明了，使用了AS对输出表格进行了重命名，要注意，重命名一定要在第一条语句中使用，否则无效

当然，也可以输出多列，但要注意查询的键值数量相等

SELECT client_name, branch_id
FROM client
UNION
SELECT supplier_name, supply_type
FROM branch_supplier;

5、函数

SQL包含了大量的内置函数，这些函数可以被直接使用

• Count函数，统计数量

SELECT COUNT(emp_id)
FROM employee
WHERE sex = 'F' AND birth_date > '1971-01-01';

• AVG函数，计算平均值

SELECT AVG(salary)
FROM employee;

• SUM函数，计算总数

SELECT SUM(salary)
FROM employee;

除此之外，还可以使用 GROUP BY 让查询更加灵活

SELECT COUNT(sex), sex
FROM employee
GROUP BY sex;

SELECT SUM(total_sales), emp_id
FROM works_with
GROUP BY emp_id;

注意
还可以自定义函数，语法为

Create function 函数名（参数）
Returns 返回值数据类型
as
begin
SQL语句(必须有return 变量或值)
End

用法与内置函数相同，在这里我就不做过多的介绍了

6、触发器（Trigger）

与之前所有的内容不同，Tigger不能被运行在IDE（Navicat Premium）中，只能运行在MYSQL自带的Command Line中。它可以被当作日志记录器使用，例如：

DELIMITER $$
CREATE
	TRIGGER my_trigger BEFORE 
	INSERT ON employee
	FOR EACH ROW BEGIN
		IF NEW.sex = 'M' THEN
			INSERT INTO trigger_test VALUES('add a new male');
			INSERT INTO trigger_test VALUES(NEW.first_name);
		ELSEIF NEW.sex = 'F' THEN
			INSERT INTO trigger_test VALUES('add a new female');
			INSERT INTO trigger_test VALUES(NEW.first_name);
		ELSE
			INSERT INTO trigger_test VALUES('add a new employee');
			INSERT INTO trigger_test VALUES(NEW.first_name);
		END IF;
	END $$
DELIMITER ;

当然，我们需要创建一个新表用来存放这些数据

CREATE TABLE trigger_test(
	massage VARCHAR(100)
);

那么这个Trigger是怎样工作的呢。

首先我们可以看到，它是在EACH ROW BEGIN是触发，也就是每当有数据存入employee表时被触发。在触发之后，它根据新数据的sex值来判断执行哪些语句，执行的语句就是简单的SQL语句。

结果如下

当然，可以存在多个trigger，但一定要保证每个trigger有自己的名字，不然会报错

DELIMITER $$
CREATE
	TRIGGER my_trigger1 AFTER DELETE 
	ON employee
	FOR EACH ROW BEGIN
		INSERT INTO trigger_test VALUES('delete a employee');
	END $$
DELIMITER ;

那么问题来了，如果说创建Trigger时发生了小错误或者重复创建该怎么办呢？

答案就是，删除就好啦~

DROP TRIGGER my_trigger;

总结

到目前为止，零基础学习SQL专栏已经有了6篇文章，虽然都是很基础的内容，但对于初学者来说，这些内容却是进入SQL世界的敲门砖，如果想成为一名出色的数据库工程师，那么还需要大量的学习，加油！

当然，如果大家有什么意见和建议，或者还对其他内容感兴趣，欢迎大家进行评论，谢谢！