一 , 单表查询的语法
基本语法格式:
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
姓名 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
- ('duoduo','male',18,'20170301','天王盖地虎办事处外交大使',7300.33,401,1), #以下是教学部
- ('黑魔导','male',78,'20150302','teacher',1000000.31,401,1),
- ('青眼白龙','male',81,'20130305','teacher',8300,401,1),
- ('真红眼黑龙','male',73,'20140701','teacher',3500,401,1),
- ('黑暗大法师','male',28,'20121101','teacher',2100,401,1),
- ('黑暗骑士','female',18,'20110211','teacher',9000,401,1),
- ('圣天使','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)
- ;
- #ps: 如果在 windows 系统中, 插入中文字符, select 的结果为空白, 可以将所有字符编码统一设置成 gbk
- # 简单查询
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;
结合 CASE 语句:
- SELECT
- (
- CASE
- WHEN NAME = 'duoduo' THEN
- NAME
- WHEN NAME = '黑魔导' THEN
- CONCAT(name,'_BIGSB')
- ELSE
- concat(NAME, 'SB')
- END
- ) as new_name
- FROM
- emp;
四 ,WHERE 约束
where 字句中可以使用:
1. 比较运算符:> <>= <= <> !=
2. between 80 and 100 值在 10 到 20 之间
3. in(80,90,100) 值是 10 或 20 或 30
4. like 'egon%'
pattern 可以是 % 或_,
% 表示任意多字符
_表示一个字符
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 '黑 %';
通配符'_'
- SELECT * FROM employee
- WHERE name LIKE '黑__';
五 , 分组查询: GROUP BY
一 , 什么是分组? 为什么要分组?
- #1, 首先明确一点: 分组发生在 where 之后, 即分组是基于 where 之后得到的记录而进行的
- #2, 分组指的是: 将所有记录按照某个相同字段进行归类, 比如针对员工信息表的职位分组, 或者按照性别进行分组等
- #3, 为何要分组呢? 取每个部门的最高工资 取每个部门的员工数 取男人数和女人数 小窍门:'每'这个字后面的字段, 就是我们分组的依据
- #4, 大前提: 可以按照任意字段分组, 但是分完毕后, 比如 group by post, 只能查看 post 字段, 如果想查看组内信息, 需要借助于聚合函数
二 ,ONLY_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 的语义就是确定 select target list 中的所有列的值都是明确语义, 简单的说来, 在 ONLY_FULL_GROUP_BY 模式下, target list 中的值要么是来自于聚集函数的结果, 要么是来自于 group by list 中的表达式的值.
- # 设置 sql_mole 如下操作 (我们可以去掉 ONLY_FULL_GROUP_BY 模式):
- mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
三 ,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 的字段作为分组的依据, 则每一条记录自成一组, 这种分组没有意义 多条记录之间的某个字段值相同, 该字段通常用来作为分组的依据
四 , 聚合函数
# 强调: 聚合函数聚合的是组的内容, 若是没有分组, 则默认一组
示例:
- 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) FROM employee WHERE depart_id=3;
五, HAVING 过滤
HAVING 与 WHERE 不一样的地方在于!!!!!!
- #!!! 执行优先级从高到低: where> group by> having
- #1. Where 发生在分组 group by 之前, 因而 Where 中可以有任意字段, 但是绝对不能使用聚合函数.
- #2. Having 发生在分组 group by 之后, 因而 Having 中可以使用分组的字段, 无法直接取到其他字段, 可以使用聚合函数
- mysql> select * from emp where salary> 100000;
- mysql> select * from emp having salary> 100000;
- ERROR 1463 (42000): Non-grouping field 'salary' is used in HAVING clause
- mysql> select post,group_concat(name) from emp group by post having salary> 10000;# 错误, 分组后无法直接取到 salary 字段
- ERROR 1054 (42S22): Unknown column 'salary' in 'having clause'
- mysql> select post,group_concat(name) from emp group by post having avg(salary)> 10000;
六, 查询排序: 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;
七, 限制查询的记录数: LIMIT
示例:
- SELECT * FROM employee ORDER BY salary DESC LIMIT 3; #默认初始位置为 0
- SELECT * FROM employee ORDER BY salary DESC LIMIT 0,5; #从第 0 开始, 即先查询出第一条, 然后包含这一条在内往后查 5 条
- SELECT * FROM employee ORDER BY salary DESC LIMIT 5,5; #从第 5 开始, 即先查询出第 6 条, 然后包含这一条在内往后查 5 条
八 , 使用正则表达式查询
- SELECT * FROM employee WHERE name REGEXP '^d';
- SELECT * FROM employee WHERE name REGEXP '导 $';
小结: 对字符串匹配的方式
- WHERE name = 'duoduo';
- WHERE name REGEXP '黑魔导';
来源: http://www.linuxidc.com/Linux/2018-05/152333.htm