1.单表查询
前面做了大量工作进行表格数据创建,但是数据越多,该怎么使用呢???如何从表中关联的表找到自己需要的数据,这个操作很重要
1.1单表查询
语法
一、单表查询的语法 SELECT 字段1,字段2... FROM 表名 WHERE 条件 GROUP BY field HAVING 筛选 ORDER BY field LIMIT 限制条数 二、关键字的执行优先级(重点) 重点中的重点:关键字的执行优先级 from where group by having select distinct order by limit 1.找到表:from 2.拿着where指定的约束条件,去文件/表中取出一条条记录 3.将取出的一条条记录进行分组group by,如果没有group by,则整体作为一组 4.将分组的结果进行having过滤 5.执行select 6.去重 7.将结果按条件排序:order by 8.限制结果的显示条数
老师代码演示
company.employee
员工id id int
姓名 name varchar
性别 sex enum
年龄 age int
入职日期 hire_date date
岗位 post varchar
职位描述 post_comment varchar
薪水 salary double
办公室 office int
部门编号 depart_id int
创建表格的代码
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
#创建表,设置字段的约束条件 create table employee( id int primary key 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 employee(name ,sex,age,hire_date,post,salary,office,depart_id) values ('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部 ('alex','male',78,'20150302','teacher',1000000.31,401,1), ('wupeiqi','male',81,'20130305','teacher',8300,401,1), ('yuanhao','male',73,'20140701','teacher',3500,401,1), ('liwenzhou','male',28,'20121101','teacher',2100,401,1), ('jingliyang','female',18,'20110211','teacher',9000,401,1), ('jinxin','male',18,'19000301','teacher',30000,401,1), ('xiaomage','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) ;
1.2where的使用
where子句中可以使用 1.比较运算符:>、<、>=、<=、<>、!= 2.between 80 and 100 :值在80到100之间 3.in(80,90,100)值是10或20或30 4.like '%': 可以是%或者_。%表示任意多字符,_表示一个字符,like'e_' 5.逻辑运算符:在多个条件直接可以使用逻辑运算符 and or not
案例
# 查出id大于5的员工姓名 select name from employee where id>5; # 查出员工身份是老师且薪资大于10000的员工姓名 select name from employee where post='teacher' and salary>10000; # 查出员工薪资介于10000到20000的员工姓名及薪资 select name,salary from employee where salary between 10000 and 20000; # 给id=2的员工职位描述更改为空 update employee set post_comment='' where id=2; # 查出职位描述为空的员工姓名 select name,post_comment from employee where post_comment=''; # 查询薪资是3000或3500或4000或9000的员工姓名以及薪资 select name,salary from employee where salary=3000 or salary=4000 or salary=9000; select name,salary from employee where salary in(3000,4000,9000); # 关键字模糊查询 # 查询以eg开头的员工姓名 select * from employee where name like 'eg__'; # 你大爷居然是两条杠(轻易不要用这个,一条杠代表一个字符) select * from employee where name like 'eg%';
1.3 常用聚合函数
max() # 求最大值 min() # 求最小值 avg() # 求平均值 sum() # 求和 count() # 求总个数 group_concat() # 查详细信息
1.4 分组(group)查询
分组是基于where之后得到的记录而进行的
group常与聚合和函数结合使用,例:
# 给岗位进行分组 select post from employee group by post; # group by关键字和count()函数结合使用 # 岗位分组,并查看每个岗位人数 select post,count(id) as count from employee group by post; # group by关键字和group_count()函数结合使用 # 岗位分组,并查看每个岗位人员 select post,group_concat(name) from employee group by post; # 给他改个名,改为members select post,group_concat(name) as numbers from employee group by post; # 岗位分组,查看每个岗位人员,并统计人数 select post,group_concat(name) as members,count(id) from employee group by post; # 查询公司内男员工和女员工的个数 select sex,count(id) from employee group by sex; # 查询岗位名以及各岗位最高工资 select post,max(salary) from employee group by post; # 查询男员工与男员工平均薪资,女员工与女员工平均薪资 select sex,avg(salary) from employee group by sex;
1.5 having过滤
having和where语法上一样,但是他两的执行优先级不同:
优先级从高到低:where > group by > having
# 查询薪资大于100000的员工信息(下面两个结果一样) select * from employee where salary>100000; select * from employee having salary>100000; # 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数 select post,group_concat(name),count(id) from employee group by post having count(id)<2; # 查询各岗位平均薪资大于10000的岗位名、平均工资 select post,avg(salary) from employee group by post having avg(salary)>1000; # 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资 select post post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000;
1.6查询排序(order by)
升序 asc
降序desc
单列排序 select * from employee order by salary; # 默认升序 select * from employee order by salary asc; # 升序 select * from employee order by salary desc; # 降序 多列排序 # 先按age默认升序,如果年龄相同,再按薪资升序,如果薪资相同,再按id降序 select * from employee order by age,salary asc,id desc;
1.7 限制查询记录数(limit)
# 查询前三条数据 select * from employee limit 3; # 查询后三条数据 select * from employee order by id desc limit 3; # 从第六条数据开始查询后三条(这种操作常用来分页) select * from employee limit 6,3; # 分页显示,每页5条 select * from employee limit 0,5; select * from employee limit 5,5; select * from employee limit 10,5;
1.8 其它神操作
# 查出所有岗位(去重) select distinct depart_id from employee; # 查出所有员工名字以及他们年薪 select name,salary*12 年薪 from employee;