网站透明效果,欧美在线网站设计教程,浙江金圣建设有限公司网站,自己做网站难么一 介绍 本节内容: 查询语法 关键字的执行优先级 简单查询 单条件查询:WHERE 分组查询:GROUP BY HAVING 查询排序:ORDER BY 限制查询的记录数:LIMIT 使用聚合函数查询 使用正则表达式查询 company.employee员工id id int 姓名 emp_na…一 介绍 本节内容: 查询语法 关键字的执行优先级 简单查询 单条件查询:WHERE 分组查询:GROUP BY HAVING 查询排序:ORDER BY 限制查询的记录数:LIMIT 使用聚合函数查询 使用正则表达式查询 company.employee员工id id int 姓名 emp_name varchar性别 sex enum年龄 age int入职日期 hire_date date岗位 post varchar职位描述 post_comment varchar薪水 salary double办公室 office int部门编号 depart_id int#创建表
create table employee(
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
);#查看表结构
mysql desc employee;
-------------------------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-------------------------------------------------------------------------
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| sex | enum(male,female) | NO | | male | |
| age | int(3) unsigned | NO | | 28 | |
| hire_date | date | NO | | NULL | |
| post | varchar(50) | YES | | NULL | |
| post_comment | varchar(100) | YES | | NULL | |
| salary | double(15,2) | YES | | NULL | |
| office | int(11) | YES | | NULL | |
| depart_id | int(11) | YES | | NULL | |
-------------------------------------------------------------------------#插入记录
#三个部门教学销售运营
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),
(成龙,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)
; 二 查询语法 SELECT 字段1,字段2... FROM 表名WHERE 条件GROUP BY fieldHAVING 筛选ORDER BY fieldLIMIT 限制条数 三 关键字的执行优先级(重点) 重点中的重点关键字的执行优先级
from
where
group by
having
select
distinct
order by
limit 1.找到表:from 2.拿着where指定的约束条件去文件/表中取出一条条记录 3.将取出的一条条记录进行分组group by如果没有group by则整体作为一组 4.按照select后的字段得到一张新的虚拟表如果有聚合函数则将组内数据进行聚合 5.将4的结果过滤having,如果有聚合函数也是先执行聚合再having过滤 6.查出结果select 7.去重 8.将结果按条件排序order by 9.限制结果的显示条数 详细见http://www.cnblogs.com/linhaifeng/articles/7372774.html 四 简单查询 #简单查询SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id FROM employee;SELECT * FROM employee;SELECT name,salary FROM employee;#避免重复DISTINCTSELECT DISTINCT post FROM employee; #通过四则运算查询SELECT name, salary*12 FROM employee;SELECT name, salary*12 AS Annual_salary FROM employee;SELECT name, salary*12 Annual_salary FROM employee;#定义显示格式CONCAT() 函数用于连接字符串SELECT CONCAT(姓名: ,name, 年薪: , salary*12) AS Annual_salary FROM employee;CONCAT_WS() 第一个参数为分隔符SELECT CONCAT_WS(:,name,salary*12) AS Annual_salary FROM employee; 小练习 1 查出所有员工的名字薪资,格式为名字:egon 薪资:3000
2 查出所有的岗位去掉重复
3 查出所有员工名字以及他们的年薪,年薪的字段名为annual_year select concat(名字:,name, ,薪资:,salary,) from employee;
select distinct depart_id from employee;
select name,salary*12 annual_salary from employee; View Code 五 WHERE约束 强调where是一种约束条件mysql会拿着where指定的条件去表中取数据而having则是在取出数据后进行过滤 where字句中可以使用 1. 比较运算符 !2. between 80 and 100 值在10到20之间3. in(80,90,100) 值是10或20或304. like egon% pattern可以是%或_ %表示任意多字符 _表示一个字符 5. 逻辑运算符在多个条件直接可以使用逻辑运算符 and or not #1:单条件查询SELECT name FROM employeeWHERE postsale;#2:多条件查询SELECT name,salary FROM employeeWHERE postteacher AND salary10000;#3:关键字BETWEEN ANDSELECT name,salary FROM employee WHERE salary BETWEEN 10000 AND 20000;SELECT name,salary FROM employee WHERE salary NOT BETWEEN 10000 AND 20000;#4:关键字IS NULL(判断某个字段是否为NULL不能用等号需要用IS)SELECT name,post_comment FROM employee WHERE post_comment IS NULL;SELECT name,post_comment FROM employee WHERE post_comment IS NOT NULL;SELECT name,post_comment FROM employee WHERE post_comment; 注意是空字符串不是nullps执行update employee set post_comment where id2;再用上条查看就会有结果了#5:关键字IN集合查询SELECT name,salary FROM employee WHERE salary3000 OR salary3500 OR salary4000 OR salary9000 ;SELECT name,salary FROM employee WHERE salary IN (3000,3500,4000,9000) ;SELECT name,salary FROM employee WHERE salary NOT IN (3000,3500,4000,9000) ;#6:关键字LIKE模糊查询通配符’%’SELECT * FROM employee WHERE name LIKE eg%;通配符’_’SELECT * FROM employee WHERE name LIKE al__; 1. 查看岗位是teacher的员工姓名、年龄
2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资
4. 查看岗位描述不为NULL的员工信息
5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪 select name,age from employee where post teacher;
select name,age from employee where postteacher and age 30;
select name,age,salary from employee where postteacher and salary between 9000 and 10000;
select * from employee where post_comment is not null;
select name,age,salary from employee where postteacher and salary in (10000,9000,30000);
select name,age,salary from employee where postteacher and salary not in (10000,9000,30000);
select name,salary*12 from employee where postteacher and name like jin%; View Code 六 分组查询:GROUP BY 大前提可以按照任意字段分组,但分完组后只能查看分组的那个字段要想取的组内的其他字段信息需要借助函数 单独使用GROUP BY关键字分组SELECT post FROM employee GROUP BY post;注意我们按照post字段分组那么select查询的字段只能是post想要获取组内的其他相关信息需要借助函数GROUP BY关键字和GROUP_CONCAT()函数一起使用SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#按照岗位分组并查看组内成员名SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;GROUP BY与聚合函数一起使用select post,count(id) as count from employee group by post;#按照岗位分组并查看每个组有多少人 强调 如果我们用unique的字段作为分组的依据则每一条记录自成一组这种分组没有意义
多条记录之间的某个字段值相同该字段通常用来作为分组的依据 #!!!MySQL 5.7默认ONLY_FULL_GROUP_BY语义介绍#参考链接http://www.ywnds.com/?p8184
#分组查询的常见问题
mysql select id,count from tt group by id;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column test.tt.count which is
not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_modeonly_full_group_by#查看MySQL 5.7默认的sql_mode如下
mysql select global.sql_mode;
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION#去掉ONLY_FULL_GROUP_BY模式如下操作
mysql set global sql_modeSTRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION;#注意
ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义简单的说来在ONLY_FULL_GROUP_BY模式下target list中的值要么是来自于聚集函数的结果要么是来自于group by list中的表达式的值。 1. 查询岗位名以及岗位包含的所有员工名字
2. 查询岗位名以及各岗位内包含的员工个数
3. 查询公司内男员工和女员工的个数
4. 查询岗位名以及各岗位的平均薪资
5. 查询岗位名以及各岗位的最高薪资
6. 查询岗位名以及各岗位的最低薪资
7. 查询男员工与男员工的平均薪资女员工与女员工的平均薪资 #题1分组
mysql select post,group_concat(name) from employee group by post;
--------------------------------------------------------------------------------------------------
| post | group_concat(name) |
--------------------------------------------------------------------------------------------------
| operation | 张野,程咬金,程咬银,程咬铜,程咬铁 |
| sale | 歪歪,丫丫,丁丁,星星,格格 |
| teacher | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙 |
| 老男孩驻沙河办事处外交大使 | egon |
--------------------------------------------------------------------------------------------------#题目2
mysql select post,count(id) from employee group by post;
----------------------------------------------------
| post | count(id) |
----------------------------------------------------
| operation | 5 |
| sale | 5 |
| teacher | 7 |
| 老男孩驻沙河办事处外交大使 | 1 |
----------------------------------------------------#题目3
mysql select sex,count(id) from employee group by sex;
-------------------
| sex | count(id) |
-------------------
| male | 10 |
| female | 8 |
-------------------#题目4
mysql select post,avg(salary) from employee group by post;
--------------------------------------------------------
| post | avg(salary) |
--------------------------------------------------------
| operation | 16800.026000 |
| sale | 2600.294000 |
| teacher | 151842.901429 |
| 老男孩驻沙河办事处外交大使 | 7300.330000 |
--------------------------------------------------------#题目5
mysql select post,max(salary) from employee group by post;
------------------------------------------------------
| post | max(salary) |
------------------------------------------------------
| operation | 20000.00 |
| sale | 4000.33 |
| teacher | 1000000.31 |
| 老男孩驻沙河办事处外交大使 | 7300.33 |
------------------------------------------------------#题目6
mysql select post,min(salary) from employee group by post;
------------------------------------------------------
| post | min(salary) |
------------------------------------------------------
| operation | 10000.13 |
| sale | 1000.37 |
| teacher | 2100.00 |
| 老男孩驻沙河办事处外交大使 | 7300.33 |
------------------------------------------------------#题目七
mysql select sex,avg(salary) from employee group by sex;
-----------------------
| sex | avg(salary) |
-----------------------
| male | 110920.077000 |
| female | 7250.183750 |
----------------------- View Code 七 使用聚合函数查询 先from找到表 再用where的条件约束去表中取出记录 然后进行分组group by没有分组则默认一组 然后进行聚合 最后select出结果 示例SELECT COUNT(*) FROM employee;SELECT COUNT(*) FROM employee WHERE depart_id1;SELECT MAX(salary) FROM employee;SELECT MIN(salary) FROM employee;SELECT AVG(salary) FROM employee;SELECT SUM(salary) FROM employee;SELECT SUM(salary) FROM employee WHERE depart_id3; 八 HAVING过滤 HAVING与WHERE在语法上是一样的 select * from employee where salary 10000;
select * from employee having salary 10000; HAVING与WHERE不一样的地方在于!!!!!! #执行优先级从高到低where group by 聚合函数 having
#1. Where 是一个约束声明使用Where约束来自数据库的数据Where是在结果返回之前起作用的先找到表按照where的约束条件从表(文件)中取出数据Where中不能使用聚合函数。#2. Having是一个过滤声明是在查询返回结果集以后对查询结果进行的过滤操作先找到表按照where的约束条件从表(文件)中取出数据然后group by分组如果没有group by则所有记录整体为一组然后执行聚合函数然后使用having对聚合的结果进行过滤在Having中可以使用聚合函数。#3. having可以放到group by之后而where只能放到group by之前#4. 在查询过程中聚合语句(sum,min,max,avg,count)要比having子句优先执行。而where子句在查询过程中执行优先级高于聚合语句。 验证不同之处 #验证之前再次强调执行优先级从高到低where group by 聚合函数 having
select count(id) from employee where salary 10000; #正确分析where先执行后执行聚合count(id),然后select出结果
select count(id) from employee having salary 10000;#错误分析先执行聚合count(id)后执行having过滤无法对id进行salary10000的过滤#以上两条sql的顺序是
1:找到表employee---用where过滤----没有分组则默认一组执行聚合count(id)---select执行查看组内id数目
2:找到表employee---没有分组则默认一组执行聚合count(id)----having 基于上一步聚合的结果此时只有count(id)字段了进行salary10000的过滤很明显根本无法获取到salary字段 其他需要注意的问题 select post,group_concat(name) from employee group by post having salary 10000;#错误分组后无法直接取到salary字段
select post,group_concat(name) from employee group by post having avg(salary) 10000; 小练习 1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
3. 查询各岗位平均薪资大于10000的岗位名、平均工资
4. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资 #题1
mysql select post,group_concat(name),count(id) from employee group by post having count(id) 2;
------------------------------------------------------------------------
| post | group_concat(name) | count(id) |
------------------------------------------------------------------------
| 老男孩驻沙河办事处外交大使 | egon | 1 |
------------------------------------------------------------------------#题目2
mysql select post,avg(salary) from employee group by post having avg(salary) 10000;
--------------------------
| post | avg(salary) |
--------------------------
| operation | 16800.026000 |
| teacher | 151842.901429 |
--------------------------#题目3
mysql select post,avg(salary) from employee group by post having avg(salary) 10000 and avg(salary) 20000;
-------------------------
| post | avg(salary) |
-------------------------
| operation | 16800.026000 |
------------------------- 九 查询排序:ORDER BY 按单列排序SELECT * FROM employee ORDER BY salary;SELECT * FROM employee ORDER BY salary ASC;SELECT * FROM employee ORDER BY salary DESC;按多列排序:先按照age排序如果年纪相同则按照薪资排序SELECT * from employeeORDER BY age,salary DESC; 小练习 1. 查询所有员工信息先按照age升序排序如果age相同则按照hire_date降序排序
2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列 #题目1
mysql select * from employee ORDER BY age asc,hire_date desc;#题目2
mysql select post,avg(salary) from employee group by post having avg(salary) 10000 order by avg(salary) asc;
--------------------------
| post | avg(salary) |
--------------------------
| operation | 16800.026000 |
| teacher | 151842.901429 |
--------------------------#题目3
mysql select post,avg(salary) from employee group by post having avg(salary) 10000 order by avg(salary) desc;
--------------------------
| post | avg(salary) |
--------------------------
| teacher | 151842.901429 |
| operation | 16800.026000 |
-------------------------- View Code 十 限制查询的记录数:LIMIT 示例SELECT * FROM employee ORDER BY salary DESC LIMIT 3; #默认初始位置为0 SELECT * FROM employee ORDER BY salary DESCLIMIT 0,5; #从第0开始即先查询出第一条然后包含这一条在内往后查5条
SELECT * FROM employee ORDER BY salary DESCLIMIT 5,5; #从第5开始即先查询出第6条然后包含这一条在内往后查5条 小练习 1. 分页显示每页5条 mysql select * from employee limit 0,5;
----------------------------------------------------------------------------------------------------------------------------
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
----------------------------------------------------------------------------------------------------------------------------
| 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 |
| 2 | alex | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 |
| 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
----------------------------------------------------------------------------------------------------------------------------
rows in set (0.00 sec)mysql select * from employee limit 5,5;
---------------------------------------------------------------------------------------------
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
---------------------------------------------------------------------------------------------
| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
---------------------------------------------------------------------------------------------
rows in set (0.00 sec)mysql select * from employee limit 10,5;
----------------------------------------------------------------------------------------------
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
----------------------------------------------------------------------------------------------
| 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
----------------------------------------------------------------------------------------------
rows in set (0.00 sec) View Code 十一 使用正则表达式查询 SELECT * FROM employee WHERE name REGEXP ^ale;SELECT * FROM employee WHERE name REGEXP on$;SELECT * FROM employee WHERE name REGEXP m{2};小结对字符串匹配的方式
WHERE name egon;
WHERE name LIKE yua%;
WHERE name REGEXP on$; 小练习 查看所有员工中名字是jin开头n或者g结果的员工信息 select * from employee where name regexp ^jin.*[gn]$; View Code 转载于:https://www.cnblogs.com/ctztake/p/7512084.html
