MySQL学习笔记day2

您所在的位置：网站首页 › sql给查询最小年龄前6个 › MySQL学习笔记day2

MySQL学习笔记day2

2024-07-03 18:25| 来源: 网络整理| 查看: 265

创建一个员工表

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 = 15 and age 聚合函数 > 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

【本文地址】

MySQL学习笔记day2

MySQL学习笔记day2

今日新闻

推荐新闻