mysql

一、创建表格

1.创建员工表

CREATE TABLE `employee`(
	
	
    `emp_id` INT PRIMARY KEY,
    `name` VARCHAR(20),
    `birth_date`DATE,
    `sex`VARCHAR(1),
    `branch_id`INT,
    `sup_id`INT
);

2.创建部门表格

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

3.添加employee 的外键

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;

4.创建客户的资料

CREATE TABLE `client`(
	`client_id` INT PRIMARY KEY,
    `client_name` VARCHAR(20),
    `phone`VARCHAR(20)
);

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

二、添加资料

1.新增部门资料，先让外键设为空

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

2.新增员工资料

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

3.新增部门id（自动修改填入）

UPDATE `branch`
SET `manger_id` = 207
WHERE `branch_id` = 2;

4.新增销售资料

INSERT INTO `client` VALUES(400, '阿猫', '253454');
INSERT INTO `client` VALUES(401, '阿狗', '5435454');
INSERT INTO `client` VALUES(402, '旺来', '56546676');
INSERT INTO `client` VALUES(403, '露西', '5433454656');
INSERT INTO `client` VALUES(404, '艾瑞克', '35465335');

5.新增work_with资料

INSERT INTO `works_with` VALUES(206, 400, '70000');
INSERT INTO `works_with` VALUES(207, 401, '240000');
INSERT INTO `works_with` VALUES(208, 402, '9800');
INSERT INTO `works_with` VALUES(208, 403, '24000');
INSERT INTO `works_with` VALUES(210, 404, '87940');

三、取资料

1.取得所有员工的资料

SELECT * FROM `employee`;

2.取得所有客户的资料

SELECT * FROM `client`;

3.按照年龄大小排序

SELECT * FROM `employee` ORDER BY `birth_date` DESC;

4.取得年龄前三的员工

SELECT * 
FROM `employee` 
ORDER BY `birth_date` DESC
LIMIT 3;

5.取的所有员工的名字

SELECT `name` FROM `employee`;

6.取不重复的性别

SELECT DISTINCT `sex` FROM `employee`;

四、常用函数

1、聚合函数

1.取得员工人数

SELECT count(*) FROM `employee`;

2.取得电话号码尾数是335的客户

SELECT *
FROM `client`
WHERE `phone` LIKE '%335';

3.取得客户姓艾的客户

SELECT *
FROM `client`
WHERE `client_name` LIKE '艾%';

4.取得生日在12月的员工

SELECT *
FROM `employee`
WHERE `birth_date` LIKE '_____09%';
#1998-12-03:一个_是一个字元,%是多个字元

2、union连集

SELECT `name`
FROM `employee`
UNION
SELECT `client_name`
FROM `client`
UNION
SELECT `branch_name`
FROM `branch`;

2.员工id+员工名字union 客户id +客户名字

SELECT `emp_id` AS `total_id`, `name`AS `total_name`
FROM `employee`
UNION
SELECT `client_id`,`client_name`
FROM `client`;

SELECT `salary`
FROM `employee`
UNION
SELECT `tota_sales`
FROM `works_with`;

3、连接join

INSERT INTO `branch` VALUES(4,'偷懒',NULL);
SELECT * FROM `branch`

取得所有部门经理的名字

SELECT *
FROM `employee`
JOIN `branch`
ON `emp_id` = `manager_id`;

取得所有部门经历的名字

SELECT  `employee`.`emp_id`,`employee`.`name`, `branch`.`branch_name`
FROM `employee` RIGHT JOIN `branch`
ON `employee`.`emp_id` =  `branch`.`manager_id`;

4、subquery 子查询

1.找出研发部门的经理名字

SELECT `name`
FROM `employee`
WHERE `emp_id` = (
	SELECT `manager_id`
	FROM `branch`
	WHERE `branch_name` = '研发'
);