编辑代码

CREATE DATABASE test;
USE test;
CREATE TABLE employee (
	emp_id int primary key,
	name varchar(20),
	birth_date date,
	sex varchar(1),
	salary int,
	branch_id int,
	sup_id int
);

CREATE TABLE branch (
	branch_id int primary key,
	branch_name varchar(20),
	manager_id int,
    foreign key (manager_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(sup_id) references employee(emp_id) ON DELETE SET NULL;

CREATE TABLE client (
	client_id int primary key,
	client_name varchar(20),
	phone varchar(20)
);

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

INSERT INTO branch VALUES (1, '研发', NULL);
INSERT INTO branch VALUES (2, '行政',  NULL);
INSERT INTO branch VALUES (3, '咨询',  NULL);

INSERT INTO employee VALUES(206, '小黄', '1998-10-08', 'F', 50000, 1, NULL);
INSERT INTO employee VALUES(207, '小绿', '1985-09-16', 'M', 29000, 2, 206);
INSERT INTO employee VALUES(208, '小黑', '2000-12-19', 'M', 35000, 3, 206);
INSERT INTO employee VALUES(209, '小白', '1997-01-22', 'F', 39000, 3, 207);
INSERT INTO employee VALUES(210, '小蓝', '1925-11-10', 'F', 84000, 1, 207);

UPDATE branch SET manager_id = 206 WHERE branch_id = 1;
UPDATE branch SET manager_id = 207 WHERE branch_id = 2;
UPDATE branch SET manager_id = 208 WHERE branch_id = 3;

INSERT INTO client VALUES(400, '阿a', '1255');
INSERT INTO client VALUES(401, '阿b', '2256');
INSERT INTO client VALUES(402, '阿c', '5257');
INSERT INTO client VALUES(403, '吴d', '4258');
INSERT INTO client VALUES(404, '阿e', '2259');

INSERT INTO works_with VALUES(206, 400, '10000');
INSERT INTO works_with VALUES(207, 401, '20000');
INSERT INTO works_with VALUES(208, 402, '30000');
INSERT INTO works_with VALUES(208, 403, '40000');
INSERT INTO works_with VALUES(210, 404, '50000');

-- -- 取得所有员工资料
-- SELECT * FROM employee;
-- -- 取得所有客户资料
-- SELECT * FROM client;
-- -- 按薪水低到高取得员工资料
-- SELECT * FROM employee ORDER BY salary;
-- -- 取得薪水前3高的员工
-- SELECT * FROM employee ORDER BY salary DESC LIMIT 3;
-- -- 取得部门ID（不重复）
-- SELECT DISTINCT branch_id FROM employee;

-- 聚合函数
-- 取得员工人数
-- SELECT COUNT(*) FROM employee;
-- -- 取得出生于1970-01-01后的女员工人数
-- SELECT COUNT(*) FROM employee WHERE birth_date > '1970-01-01' AND sex = 'F';
-- -- 所有员工的平均薪水
-- SELECT AVG(salary) FROM employee;
-- -- 所有员工的薪水总和
-- SELECT SUM(salary) FROM employee;
-- -- 最高薪水
-- SELECT MAX(salary) FROM employee;
-- -- 最低薪水
-- SELECT MIN(salary) FROM employee;


-- wildcard 通配符 ： % 代表多字符，_ 代表一个字符
-- 电话号码尾数是56的客户
-- SELECT * FROM client WHERE phone LIKE '%56';
-- -- 姓吴的客户
-- SELECT * FROM client WHERE client_name LIKE '吴%';
-- -- 生日在12月的员工
-- SELECT * FROM employee WHERE birth_date LIKE '_____12%';


-- union 
-- -- 员工名字 union 客户名字
-- SELECT name FROM employee union SELECT client_name FROM client;
-- -- 员工ID + 员工名字 union 客户ID + 客户名字
-- SELECT emp_id AS total_id, name AS total_name FROM employee 
-- union SELECT client_id,client_name FROM client;
-- -- 员工薪水 union 销售金额
-- SELECT salary FROM employee 
-- union SELECT total_sales FROM works_with;

-- join 将2个表格连接
-- INSERT INTO branch VALUES(4, '偷懒',NULL);
-- -- 取得所有部门经理的名字 （需连接branch与employee2张表）
-- SELECT emp_id,name,branch_name FROM employee RIGHT JOIN branch ON employee.emp_id = branch.manager_id;


-- subquery 子查询：在一个查询语句里使用另一个查询语句的结果
-- 找出研发部分的经理名字（找出branch表中研发部门对于的manager_id，再在employee中找manager_id对应的name）
-- SELECT name FROM employee WHERE emp_id = (
--     SELECT manager_id FROM branch WHERE branch_name='研发'
-- );

-- 找出研发部分的经理名字
-- SELECT name FROM employee WHERE emp_id IN (
--     SELECT emp_id FROM works_with WHERE total_sales > 20000
-- );

-- ON DELETE NULL: 对应不上（原来对于的被删掉），设为Null, primary key 不能被设为null
-- ON DELETE CASCADE: 对应不上（原来对于的被删掉），也删掉
SELECT * FROM branch;
SELECT * FROM works_with;
DELETE FROM employee WHERE emp_id = 207;
SELECT * FROM branch;
SELECT * FROM works_with;