MySQL学习笔记day2--DQL案例练习

创建一个员工表

create table emp(
id                int
workno            varchar(10)          comment'编号',
name              varchar(10)          comment'工号',
gender            char(1)              comment'姓名',
age               tinyint unsigned     comment'年龄',
idcard            varchar(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,'123456719012345678','北京','2005-09-01'),
      (3,'3','海绵宝宝','男',38,'123456729012345678','上海','2005-08-01'),
      (4,'4','珍珍','女',18,'123456739012345678','北京','2009-12-01'),
      (5,'5','玛卡巴卡','女',16,'123456749012345678','上海','2007-07-01'),
      (6,'6','痞老板','男',28,'12345675901234567x','北京','2006-01-01'),
      (7,'7','小窝','男',48,'123456769012345678','北京','2005-05-01'),
      (8,'8','唔西迪西','女',38,'123456779012345678','天津','2015-05-01'),
      (9,'9','依古比古','女',45,'123456789012345678','北京','2018-04-01'),
      (10,'10','汤姆布利柏','男',53,'123456799012345678','上海','2011-01-01'),
      (11,'11','拉文克劳','男',55,'123456711012345678','江苏','2015-05-01'),
      (12,'12','斯莱特林','男',32,'123456722012345678','北京','2004-02-01'),
      (13,'13','格兰芬多','男',88,'123456733012345678','江苏','2020-11-01'),
      (14,'14','小点点','女',65,'123456744012345678','西安','2019-05-01'),
      (15,'15','赫奇帕奇','男',70,'12345675501234567x','西安','2018-04-01'),
      (16,'16','小豆豆','女',18,'null','北京','2012-06-01');

根据以上创建的表,完成以下要求

基础查询

1.查询指定字段name,workno,age并返回

select name,workno,age from emp;

2.查询所有字段返回

select id,workno,name,gender,age,idcard,workaddress,entrydate from emp;
-----或者
select * from emp;

3.查询所有的员工地址,起别名

select workaddress as'工作地址' from emp;

4.查询员工的工作地址,不要重复

select distinct workaddress as'工作地址' from emp;

条件查询

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 idcard is null;

5.查询有身份证的员工

select * from emp where idcard 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 and age <=20;
-----或者
select * from where age between 15 and 20 emp;

8.查询性别为女,且年龄20的员工

select * from emp where age = 20 and gender = '女';

9.查询年龄为18或20或40的员工信息

select * from emp where age in(18,20,40);
-----或者
select * from emp where age = 18 or age =20 or age = 40;

10.查询姓名为两个字的员工信息

select * from emp where name like '_ _';

11.查询身份证最后一位是x的员工信息

select * from emp where idcard like '%x';

聚合函数

1.统计该企业员工数量

select count(*) from emp;

2.统计企业员工平均年龄

select avg(age) from emp;

3.统计企业员工最大年龄

select max(age) from emp;

4.统计员工的最小年龄

select min(age) from emp;

5.统计西安地区员工年龄之和

select sum(age) where workaddress = '西安' from emp;

分组查询

1.根据性别分组,统计男性员工和女性员工的数量

select gender, count(*) from emp group by gender;

2.根据性别分组,统计男性员工和女性员工的平均年龄

select gender, avg(age) from emp group by gender;

3.查询年龄小于45的员工,并根据工作地址分组获取员工数量大于等于3的工作地址

select workaddress, count(*) from emp where age <45 group by workaddress having count(*) >= 3;

笔者认为了解掌握DQL各级语法间的执行顺序将会对你理解较长的查询语句有很大的帮助

DQL语法顺序

编写顺序	执行顺序
select 字段列表	from
from 表名列表	where
where 条件列表	group by
group by 分组字段列表	having
having 分组后条件列表	select
order by 排序字段列表	order by
limit 分页参数	limit

注意: 执行顺序 where > 聚合函数 > having

排序查询

1.根据年龄对公司员工进行升序排序

select * from emp order by age asc;

2.根据入职时间对员工进行降序排序

select * from emp order by entrydate desc;

3.根据年龄对公司员工进行升序排序,年龄相同,再按照入职时间进行降序排序

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

分页查询

1.查询第一页员工数据,每页显示10条记录

select * from emp limit 0,10;

2.查询第二页员工数据,每页显示10条记录

select * from emp limit 10,10;

DQL语法综合练习

1.查询年龄为20,21,22,23的女性员工信息

select * from emp where age in (20,21,22,23) and gender = '女';

2.查询性别为男,且年龄在20-40(含)的姓名为三个字的员工

select * from emp where gender = '男' and age between 20 and 40 and name like '_ _ _';

3.统计员工表中,年龄小于60岁的男性员工和女性员工的数量

select gender , count (*) from emp where age < 60 group by gender;

4.查询所有年龄小于等于35岁员工的姓名和年龄,并对查询结果按年龄升序排序,如果年龄相同按入职时间降序排序

select name,age from emp where age <= 35 Oder by age asc,entry desc;

5.查询性别为男,且年龄在20-40岁(含)以内的前五个员工信息,对查询结果按年龄升序排序,如果年龄相同按入职时间降序排序

select * from emp where gender = '男' and age between 20 and 40 order by age asc,entrydate desc limit 0,5;

以上就是笔者在黑马程序员课上整理的DQL语法练习,如有不足请大家不吝指教,万分感激

蓝瘦,卷又卷不过,躺又不敢躺,希望这半年早早过去,又希望慢点,有亿点烦躁