MySQL入门， 基本查询语法

先准备一组数据

-- 准备数据
create table dept
(
    id   int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部门名称'
) comment '部门表';

create table emp
(
    id          int auto_increment comment 'ID' primary key,
    name        varchar(50) not null comment '姓名',
    age         int comment '年龄',
    job         varchar(20) comment '职位',
    salary      int comment '薪资',
    entrydate   date comment '入职时间',
    managerid   int comment '直属领导ID',
    dept_id     int comment '部门ID',
    workaddress int         not null
) comment '员工表';


-- 添加外键
alter table emp
    add constraint fk_emp_dept_id foreign key (dept_id) references dept (id);

INSERT INTO dept (id, name)
VALUES (1, '研发部'),
       (2, '市场部'),
       (3, '财务部'),
       (4, '销售部'),
       (5, '总经办'),
       (6, '人事部');
INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id)
VALUES (1, '金庸', 66, '总裁', 20000, '2000-01-01', null, 5),

       (2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1),
       (3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1),
       (4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),
       (5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1),
       (6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1),

       (7, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3),
       (8, '周芷若', 19, '会计', 48000, '2006-06-02', 7, 3),
       (9, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3),

       (10, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2),
       (11, '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2),
       (12, '鹤笔翁', 19, '职员', 3750, '2007-05-09', 10, 2),
       (13, '方东白', 19, '职员', 5500, '2009-02-12', 10, 2),

       (14, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4),
       (15, '俞莲舟', 38, '销售', 4600, '2004-10-12', 14, 4),
       (16, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4),
       (17, '陈友谅', 42, null, 2000, '2011-10-12', 1, null);
update emp
set workno = 01,
    gender ='男'
where id = 1;
update emp
set workno = 02,
    gender ='男'
where id = 2;
update emp
set workno = 03,
    gender ='男'
where id = 3;
update emp
set workno = 04,
    gender ='男'
where id = 4;
update emp
set workno = 05,
    gender ='男'
where id = 5;
update emp
set workno = 06,
    gender ='男'
where id = 6;
update emp
set workno = 07,
    gender ='女'
where id = 7;
update emp
set workno = 08,
    gender ='女'
where id = 8;
update emp
set workno = 09,
    gender ='女'
where id = 9;
update emp
set workno = 10,
    gender ='女'
where id = 10;
update emp
set workno = 11,
    gender ='男'
where id = 11;
update emp
set workno = 12,
    gender ='男'
where id = 12;
update emp
set workno = 13,
    gender ='男'
where id = 13;
update emp
set workno = 14,
    gender ='男'
where id = 14;
update emp
set workno = 15,
    gender ='男'
where id = 15;
update emp
set workno = 16,
    gender ='男'
where id = 16;
update emp
set workno = 17,
    gender ='男'
where id = 17;

update emp
set workaddress='北京'
where id = 1;
update emp
set workaddress='上海'
where id = 2;
update emp
set workaddress='北京'
where id = 3;
update emp
set workaddress='哈尔滨'
where id = 4;
update emp
set workaddress='河南'
where id = 5;
update emp
set workaddress='广州'
where id = 6;
update emp
set workaddress='南京'
where id = 7;
update emp
set workaddress='江苏'
where id = 8;
update emp
set workaddress='深圳'
where id = 9;
update emp
set workaddress='四川'
where id = 10;
update emp
set workaddress='成都'
where id = 11;
update emp
set workaddress='香港'
where id = 12;
update emp
set workaddress='天津'
where id = 13;
update emp
set workaddress='北京'
where id = 14;
update emp
set workaddress='山东'
where id = 15;
update emp
set workaddress='湖南'
where id = 16;
update emp
set workaddress='贵州'
where id = 17;

条件查询

-- 1. 查询年龄等于 88 的员工
select *
from emp
where age = 88;

-- 2. 查询年龄小于20 的员工
select *
from emp
where age < 20;

-- 3. 查询年龄小于等于20的员工信息
select *
from emp
where age <= 20;

-- 4. 查询没有身份证的员工信息
select *
from emp
where emp.managerid is null;

-- 5. 查询有身份证的员工信息
select *
from emp
where emp.managerid is not null;

-- 6. 查询年龄不等于 88 的员工信息
select *
from emp
where age != 88;
select *
from emp
where age <> 88;

-- 7.查询年龄在15岁（包含） 到 20岁（包含）之间的员工信息
select *
from emp
where age >= 15 && age <= 20;
select *
from emp
where age >= 15
  and age <= 20;
select *
from emp
where age between 15 and 20;

-- 8.查询性别 为女 且年龄小于 25岁的员工和信息
select * from emp where gender = '女' and age<=25;

-- 9.查询年龄等于18 或 20 或 40 的员工信息
select *
from emp
where age = 18
   or age = 20
   or age = 40;
select *
from emp
where age in (18, 20, 40);

-- 10.查询姓名为两个字的员工
select *
from emp
where name like '__';

-- 11.查询身份证好最后一位是X的员工信息
select *
from emp
where managerid like '%X';

聚合函数

-- 1. 统计该企业员工数量
select count(*)
from emp;
select count(id)
from emp;


-- 2.统计该企业员工的平均年龄
select avg(age)
from emp;

-- 3.统计该企业员工的最大年龄
select max(age)
from emp;

-- 4.统计该企业员工的最小年龄
select min(age)
from emp;

-- 5.统计先地区员工的年龄之和
select sum(age)
from emp
where job = '职员';

分组查询

-- 1. 根据性别分组，统计男性员工 和 女性员工的数量
select job, count(*)
from emp
group by job;


-- 2.根据性别小组 , 统计男性员工 和 女性员工的平均年龄
select job, avg(age)
from emp
group by job;


-- 3.查询年龄小于45的员工，并根据工作地址分组 ， 获取员工数量大于等于3的工作地址
select job, count(*) address_count
from emp
where age < 45
group by job
having address_count >= 3;

排序查询

-- 1.根据年龄对公司的员工进行升序排序
select *
from emp
order by age asc;
select *
from emp
order by age desc;

select *
from emp
order by age;


-- 2.根据入职时间，对员工进行降序排序
select *
from emp
order by entrydate desc;


-- 3.根据年龄对公司的员工进行升序排序 ， 年龄相同，再按照入职时间进行降序排序
select *
from emp
order by age asc, entrydate desc;

select *
from emp
order by age asc, entrydate asc;

分页查询

-- 1. 查询第一页员工数据， 每页展示10条记录
select *
from emp
limit 0,10;


select *
from emp
limit 10;


-- 2.查询第二页员工数据，每页展示10条记录------->(页码-1）*页展示记录数
select *
from emp
limit 10,10;