单表查询
表准备
- create table emp(
- id int not null unique auto_increment,
- name varchar(20) not null,
- sex enum('male','female') not null default 'male', #大部分是男的
- age int(3) unsigned not null default 28,
- hire_date date not null,
- post varchar(50),
- post_comment varchar(100),
- salary double(15,2),
- Office int, #一个部门一个屋子
- depart_id int
- );
- # 插入记录
- # 三个部门: 教学, 销售, 运营
- insert into emp(name,sex,age,hire_date,post,salary,Office,depart_id) values
- ('jason','male',18,'20170301','张江第一帅形象代言',7300.33,401,1), #以下是教学部
- ('egon','male',78,'20150302','teacher',1000000.31,401,1),
- ('kevin','male',81,'20130305','teacher',8300,401,1),
- ('tank','male',73,'20140701','teacher',3500,401,1),
- ('owen','male',28,'20121101','teacher',2100,401,1),
- ('jerry','female',18,'20110211','teacher',9000,401,1),
- ('nick','male',18,'19000301','teacher',30000,401,1),
- ('sean','male',48,'20101111','teacher',10000,401,1),
- ('歪歪','female',48,'20150311','sale',3000.13,402,2),# 以下是销售部门
- ('丫丫','female',38,'20101101','sale',2000.35,402,2),
- ('丁丁','female',18,'20110312','sale',1000.37,402,2),
- ('星星','female',18,'20160513','sale',3000.29,402,2),
- ('格格','female',28,'20170127','sale',4000.33,402,2),
- ('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
- ('程咬金','male',18,'19970312','operation',20000,403,3),
- ('程咬银','female',18,'20130311','operation',19000,403,3),
- ('程咬铜','male',18,'20150411','operation',18000,403,3),
- ('程咬铁','female',18,'20140512','operation',17000,403,3)
- ;
- #ps: 如果在 Windows 系统中, 插入中文字符, select 的结果为空白, 可以将所有字符编码统一设置成 gbk
where 约束条件
- # 1. 查询 id 大于等于 3 小于等于 6 的数据
- select id,name from emp where id>= 3 and id <= 6;
- select * from emp where id between 3 and 6;
- # 2. 查询薪资是 20000 或者 18000 或者 17000 的数据
- select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
- select * from emp where salary in (20000,18000,17000); # 简写
- # 3. 查询员工姓名中包含 o 字母的员工姓名和薪资
- # 在你刚开始接触 MySQL 查询的时候, 建议你按照查询的优先级顺序拼写出你的 sql 语句
- """
- 先是查哪张表 from emp
- 再是根据什么条件去查 where name like '%o%'
- 再是对查询出来的数据筛选展示部分 select name,salary
- """select name,salary from emp where name like'%o%';
- # 4. 查询员工姓名是由四个字符组成的员工姓名与其薪资
- select name,salary from emp where name like '____';
- select name,salary from emp where char_length(name) = 4;
- # 5. 查询 id 小于 3 或者大于 6 的数据
- select * from emp where id not between 3 and 6;
- # 6. 查询薪资不在 20000,18000,17000 范围的数据
- select * from emp where salary not in (20000,18000,17000);
- # 7. 查询岗位描述为空的员工名与岗位名 针对 null 不能用等号, 只能用 is
- select name,post from emp where post_comment = NULL; # 查询为空!
- select name,post from emp where post_comment is NULL;
- select name,post from emp where post_comment is not NULL;
- group by(分组)
- # 数据分组应用场景: 每个部门的平均薪资, 男女比例等
- # 1. 按部门分组
- select
来源: http://www.bubuko.com/infodetail-3164753.html