先创建表
- # 创建表
- 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,teacher,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)
- ;
- # 一点小知识复习
1. 注意:
select * from t1 where 条件 group by 分组字段
1. 分组只能查询分组字段, 要想查看其余的利用聚合函数
2. 聚合函数的分类: count,min,max,avg,group_concat,sum 等
3. 模糊匹配: 用 like 关键字
- select * from t1 where name like %eg%; #% 表示任意字符
- select * from t1 where name like d__l; #一个下划线表示一个字符, 两个下划线就表示两个字符
4. 拷贝表 :create table t2 select * from t1;
create table t2 select * from t1 where 1=2 ;
一查询语法
SELECT 字段 1, 字段 2... FROM 表名
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数
二简单查询
- # 简单查询 SELECT id,
- name,
- sex,
- age,
- hire_date,
- post,
- post_comment,
- salary,
- office,
- depart_id FROM employee;
- SELECT * FROM employee;
- SELECT name,
- salary FROM employee;# 避免重复 DISTINCT SELECT 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>
select concat(<名字:,name,> ,<薪资:,salary,> ) from employee;
2 查出所有的岗位(去掉重复)
select distinct depart_id from employee;
3 查出所有员工名字, 以及他们的年薪, 年薪的字段名为年薪
select name,salary*12 年薪 from employee;
三 where 约束
where 字句中可以使用:
1. 比较运算符:> < >= <= <> !=
2. between 80 and 100 值在 10 到 20 之间
3. in(80,90,100) 值是 80 或 90 或 100
4. like eg%
可以是 % 或_,
% 表示任意多字符
_表示一个字符
like e__n :
5. 逻辑运算符: 在多个条件直接可以使用逻辑运算符 and or not
- #1: 单条件查询
- SELECT name FROM employee
- WHERE post=sale;
- #2: 多条件查询
- SELECT name,salary FROM employee
- WHERE post=teacher AND salary>10000;
- #3: 关键字 BETWEEN AND
- SELECT 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=; 注意是空字符串, 不是 null
ps:
执行
update employee set post_comment= where id=2;
再用上条查看, 就会有结果了
- #5: 关键字 IN 集合查询
- SELECT name,salary FROM employee
- WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;
- 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__;
四 having 过滤
having 和 where 语法上是一样的
- select * from employee where id>15;
- select * from employee having id>15;
但是 having 和 where 不一样的地方在于以下几点!!!
#!!! 执行优先级从高到低: where > group by > 聚合函数 > having >order by
1.where 和 having 的区别
1. Where 是一个约束声明, 使用 Where 约束来自数据库的数据, Where 是在结果返回之前起作用的
(先找到表, 按照 where 的约束条件, 从表 (文件) 中取出数据),Where 中不能使用聚合函数
2.Having 是一个过滤声明, 是在查询返回结果集以后对查询结果进行的过滤操作
(先找到表, 按照 where 的约束条件, 从表 (文件) 中取出数据, 然后 group by 分组,
如果没有 group by 则所有记录整体为一组, 然后执行聚合函数, 然后使用 having 对聚合的结果进行过滤),
在 Having 中可以使用聚合函数
3.where 的优先级比 having 的优先级高
4.having 可以放到 group by 之后, 而 where 只能放到 group by 之前
验证不同之处:
1. 查看员工的 id>15 的有多少个
select count(id) from employee where id>15;# 正确, 分析: where 先执行, 后执行聚合 count(id),
然后 select 出结果
- select count(id) from employee having id>15; #报错, 分析: 先执行聚合 count(id), 后执行 having 过滤,
- #无法对 id 进行 id>15 的过滤
- # 以上两条 sql 的顺序是
1: 找到表 employee--->用 where 过滤 ---->没有分组则默认一组执行聚合 count(id)--->select 执行查看组内 id 数目
2: 找到表 employee--->没有分组则默认一组执行聚合 count(id)---->having 基于上一步聚合的结果 (此时只有 count(id) 字段了)
进行 id>15 的过滤, 很明显, 根本无法获取到 id 字段
- #having 举例
- 1 ------having-----------
- 2 select depart_id,count(id) from employee group by depart_id;
- 3 select depart_id,count(id) from employee group by depart_id having depart_id = 3;
- 4 select depart_id,count(id) from employee group by depart_id having count(id)>7;
5 select max(salary) 最大工资 from employee where id>2 group by depart_id having count(id)>3;
6 select * from employee where id>7; #查看所有 id>7 的员工信息
小练习:
1. 查询各岗位内包含的员工个数小于 2 的岗位名岗位内包含员工名字个数
select post,group_concat(name) 员工姓名, count(id) 个数 from employee group by post having count(id)<2;
2. 查询各岗位平均薪资大于 10000 的岗位名平均工资
select post,avg(salary) from employee group by post having avg(salary)>10000;
3. 查询各岗位平均薪资大于 10000 且小于 20000 的岗位名平均工资
select post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000;
五分组查询 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;# 按照岗位分组, 并查看每个组有多少人
强调:
分组: 一般相同的多的话就可以分成一组(一定是有重复的字段)
小练习:
1. 查询岗位名以及岗位包含的所有员工名字
select post,group_concat(name) from employee group by post;
2. 查询岗位名以及各岗位内包含的员工个数
select post,count(id) from employee group by post;
3. 查询公司内男员工和女员工的个数
select sex,count(id) from employee group by sex;
4. 查询岗位名以及各岗位的平均薪资
select post,max(salary) from employee group by post;
5. 查询岗位名以及各岗位的最高薪资
select post,max(salary) from employee group by post;
6. 查询岗位名以及各岗位的最低薪资
select post,min(salary) from employee group by post;
7. 查询男员工与男员工的平均薪资, 女员工与女员工的平均薪资
select sex,avg(salary) from employee group by sex;
六关键字的执行优先级(重点)
重点中的重点: 关键字的执行优先级
- from
- where
- group by
- having
- select
- distinct
- order by
- limit
1. 找到表: from
2. 拿着 where 指定的约束条件, 去文件 / 表中取出一条条记录
3. 将取出的一条条记录进行分组 group by, 如果没有 group by, 则整体作为一组
4. 如果有聚合函数, 则将组进行聚合
5. 将 4 的结果过滤: having
6. 查出结果: select
7. 去重
8. 将 6 的结果按条件排序: order by
9. 将 7 的结果限制显示条数
七查询排序 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 employee
- ORDER BY age,
- salary DESC;
- ===========order by==========
- 1.select * from employee order by salary;# 如果不指定, 默认就是升序
- 2.select * from employee order by salary asc;
- 3.select * from employee order by salary desc;
- # 先按照年龄升序, 当年龄相同的太多, 分不清大小时, 在按照工资降序
- 4.select * from employee order by age asc, salary desc;
小例子:
1. 查询所有员工信息, 先按照 age 升序排序, 如果 age 相同则按照 hire_date 降序排序
select * form employee order by age,hire_date desc;
2. 查询各岗位平均薪资大于 10000 的岗位名平均工资, 结果按平均薪资升序排列
select post ,avg(salary) from employee group by post having avg(salary)>10000;
3. 查询各岗位平均薪资大于 10000 的岗位名平均工资, 结果按平均薪资降序排列
select post ,avg(salary) from employee group by post having avg(salary)>10000 desc;
八使用聚合函数查询
先 from 找到表
再用 where 的条件约束去表中取出记录
然后进行分组 group by, 没有分组则默认一组
然后进行聚合
最后 select 出结果
示例:
- select count(*) from employee;
- select count(*) from employee where depart_id=1;
- select max(salary) from employee;
- select min(salary) from employee;
- select avg(salary) from employee;
- select sum(salary) from employee;
- select sum(salary) form employee WHERE depart_id=3;
九 where 的补充(使用正则表达式查询)
- select * from employee where name regexp ^ale; #匹配以 ale 开头的员工信息
- select * from employee where name regexp on$; #匹配以 on 结尾的员工信息
- select * from employee where name regexp n{1,2}; #匹配 name 里面包含 1 到 2 个 n 的员工信息
小结: 对字符串匹配的方式
- where name = egon;
- where name like yua%;
- where name regexp on$;
小练习:
查看所有员工中名字是 jin 开头, n 或者 g 结果的员工信息
select * from employee where name regexp ^jin.*[ng]$;
十限制查询的记录数: LIMIT
=========limit: 限制打印几条 =========
1.select * from employee limit 3;# 打印前三条
2. 像这样表示的: 指的是从哪开始, 往后取几条 (这样的操作一般用来分页)
- select * from employee limit 0,3;
- select * from employee limit 3,4;
- select * from employee limit 6,3;
- select * from employee limit 9,3;
- 3.select * from employee order by id desc limit 3; #查看后三条
小练习
1. 分页显示, 每页 5 条
- select * from employee limit 0,5;
- select * from employee limit 5,5;
- select * from employee limit 10,5;
来源: http://www.bubuko.com/infodetail-2501206.html