create database test;

drop table emp;

use test;

-- 数据准备
CREATE TABLE emp (
    id              INT                 COMMENT '编号',
    workno          VARCHAR(10)         COMMENT '工号',
    name            VARCHAR(10)         COMMENT '姓名',
    gender          CHAR(1)             COMMENT '性别',
    age             TINYINT UNSIGNED    COMMENT '年龄',
    idcard          CHAR(18)            COMMENT '身份证号',
    workaddress     VARCHAR(50)         COMMENT '工作地址',
    entrydate       DATE                COMMENT '入职时间'
) COMMENT '员工表';

INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES  (1, '1', '柳岩', '女', 20, '123456789012345678', '北京', '2000-01-01'),
        (2, '2', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01'),
        (3, '3', '韦一笑', '男', 38, '123456789712345670', '上海', '2005-08-01'),
        (4, '4', '赵敏', '女', 18, '123456757123845670', '北京', '2009-12-01'),
        (5, '5', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01'),
        (6, '6', '杨逍', '男', 28, '12345678931234567X', '北京', '2006-01-01'),
        (7, '7', '范瑶', '男', 40, '123456789212345670', '北京', '2005-05-01'),
        (8, '8', '黛绮丝', '女', 38, '123456157123645670', '天津', '2015-05-01'),
        (9, '9', '范凉凉', '女', 45, '123156789012345678', '北京', '2010-04-01'),
        (10, '10', '陈友谅', '男', 53, '123456789012345670', '上海', '2011-01-01'),
        (11, '11', '张士诚', '男', 55, '123567897123465670', '江苏', '2015-05-01'),
        (12, '12', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01'),
        (13, '13', '张三丰', '男', 88, '123656789012345678', '江苏', '2020-11-01'),
        (14, '14', '灭绝', '女', 65, '123456719012345670', '西安', '2019-05-01'),
        (15, '15', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-01'),
        (16, '16', '周芷若', '女', 18, NULL, '北京', '2012-06-01');

-- 基本查询

SELECT name, gender, workaddress
FROM emp;

SELECT name AS 姓名, gender AS 性别, entrydate AS 入职日期
FROM emp AS 员工表;

SELECT DISTINCT workaddress
FROM emp;


-- 条件查询

SELECT *
FROM emp
WHERE age >= 30 AND gender = '男';

SELECT name, gender, workaddress
FROM emp
WHERE workaddress IN ('北京', '上海');

SELECT name, entrydate
FROM emp
WHERE gender = '女' AND idcard IS NULL;

SELECT *
FROM emp
WHERE name LIKE '%张%';

SELECT *
FROM emp
WHERE age != 18 AND workaddress != '北京';

SELECT *
FROM emp
WHERE age BETWEEN 25 AND 40
  AND idcard IS NOT NULL
  AND gender = '男';

SELECT *
FROM emp
WHERE name = '张无忌' AND workaddress != '上海';

SELECT *
FROM emp
WHERE workaddress != '西安'
  AND (age <= 35 OR age >= 60);

-- 聚合函数
SELECT COUNT(*) AS 员工总数
FROM emp;

SELECT SUM(age) AS 年龄总和
FROM emp;

SELECT AVG(age) AS 女员工平均年龄
FROM emp
WHERE gender = '女';

SELECT MAX(age) AS 最大年龄
FROM emp;

SELECT MIN(age) AS 北京最小年龄
FROM emp
WHERE workaddress = '北京';

-- 分组查询

SELECT gender, COUNT(*) AS 员工数量
FROM emp
GROUP BY gender;

SELECT gender, AVG(age) AS 平均年龄
FROM emp
GROUP BY gender;

SELECT workaddress, COUNT(*) AS 员工数量
FROM emp
WHERE age < 45
GROUP BY workaddress
HAVING COUNT(*) >= 3;


-- 排序查询

SELECT name, age
FROM emp
ORDER BY age ASC;

SELECT name, gender, age
FROM emp
ORDER BY gender ASC, age DESC;

SELECT name, entrydate
FROM emp
ORDER BY entrydate DESC;


-- 分页查询

SELECT name, age, workno
FROM emp
LIMIT 5;

SELECT id, name, entrydate
FROM emp
LIMIT 5, 5;

SELECT name, entrydate
FROM emp
ORDER BY entrydate DESC
LIMIT 8, 4;