[大数据]MySQL-练习1

MySQL-练习1

-- 部门表
CREATE TABLE department(
    id INT PRIMARY KEY AUTO_INCREMENT, -- 部门ID
    dname VARCHAR(30) NOT NULL UNIQUE, -- 部门名称
    address VARCHAR(40),               -- 部门地址
    leeder VARCHAR(20) NOT NULL        -- 部门领导
);

INSERT INTO department VALUE
(NULL, "财务部", "北京市", "李老板"),
(NULL, "销售部", "上海市", "王老板"),
(NULL, "开发部", "广州市", "胡老板"),
(NULL, "客服部", "深圳市", "曾老板");

-- 员工表
CREATE TABLE employees(
    id INT PRIMARY KEY AUTO_INCREMENT, -- 员工ID
    ename VARCHAR(20) NOT NULL,        -- 姓名
    sex VARCHAR(10),                   -- 性别
    phone INT UNIQUE,                  -- 电话号码
    hiredate DATE,                     -- 入职时间
    salary INT,                        -- 薪资
    depa_id INT,                       -- 部门ID
    CONSTRAINT emp_dep_fk FOREIGN KEY (depa_id) REFERENCES department(id)
);

INSERT INTO employees VALUE
(NULL, "小虎", "男", 10086, "2016-04-27", 6000, 1),
(NULL, "小林", "男", 10000, "2017-08-11", 9000, 2),
(NULL, "小花", "女", 10010, "2015-11-02", 3000, 2),
(NULL, "小李", "男", 10001, "2011-08-19", 2000, 2),
(NULL, "小丽", "女", 10085, "2018-12-01", 6000, NULL),
(NULL, "小红", "女", 10011, "2017-09-07", NULL, 3),
(NULL, "小胡", "男", 12315, "2008-06-15", 8000, 4),
(NULL, "小廖", "男", 12306, "2021-10-30", 7000, 4);

-- 隐式内连接，查询所有员工信息和对应的部门名称、部门领导
SELECT t1.*, t2.`dname`, t2.`leeder`
FROM employees t1, department t2 WHERE t1.`depa_id` = t2.`id`;

-- 显式内连接，查询所有员工信息和对应的部门名称、部门领导
SELECT t1.*, t2.`dname`, t2.`leeder`
FROM employees t1 INNER JOIN department t2 ON t1.`depa_id` = t2.`id`;

-- 左外连接，查询所有员工信息，如果员工有部门则查询部门名称，没有部门则不显示部门名称
SELECT t1.*, t2.`dname`
FROM employees t1 LEFT OUTER JOIN department t2 ON t1.`depa_id` = t2.id;

-- 右外连接，查询所有员工信息，如果员工有部门则查询部门名称，没有部门则不显示部门名称
SELECT t1.*,t2.`dname`
FROM department t2 RIGHT OUTER JOIN employees t1 ON t1.`depa_id` = t2.id;

-- 子查询，查询工资最高的员工信息
SELECT * FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);

-- 子查询结果是单行单列的，查询工资小于平均工资的所有员工信息
SELECT * FROM employees WHERE salary < (SELECT AVG(salary) FROM employees);

-- 子查询结果是多行单列的，查询"财务部"和“开发部”的所有员工信息
SELECT * FROM employees WHERE depa_id IN(SELECT id FROM department WHERE dname IN ("财务部", "开发部"));

-- 子查询结果是多行多列的，查询员工的入职日期是2016年后的员工信息和部门信息
SELECT * FROM (SELECT * FROM employees WHERE employees.`hiredate` > "2016-00-00") t1, department t2
WHERE t1.`depa_id` = t2.`id`;

-- 普通内连接查询，查询员工的入职日期是2016年后的员工信息和部门信息
SELECT * FROM employees t1, department t2 WHERE t1.`depa_id` = t2.`id` AND t1.`hiredate` > "2016-00-00";