单标查询
单表查询语句
SELECT DISTINCT 字段1,字段2... FROM 表名
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数
关键字执行的优先级
fromwhere
group by
select
distinct
having
order by
limit
1.找到表 :from
2.拿着where指定的约束条件,去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by,如果没有group by,则整体作为一组
4.这行select(去重)
5.将分组的结果进行having过滤
6.将结果按条件排序:order by
7.限制结果的显示条数
简单查询
#简单查询
select 字段名字,字段名字 ,字段名字 from 表名 ----名字查询
select* from 表名 -----查询所有#避免重复DISTINCT
select distinct 字段名字 from表名;#定义显示格式
concat() 函数用于连接字符串
select concat('姓名:',字段名) from表名;
concat_ws() 第一个参数为分隔线
select concat_ws(':',字段名) from表名;#通过四则运算查询
select 字段名, salary*12(月薪*12) as annual_salary(年薪) from 表名#运算加重新定义名字
where 约束
where字句中可以使用:
1.比较运算符:>< >= <= <> !=
2.between 80 and 100 #值在80到100之间
3.in(80,10) #值是80或10
4.like 'e%'
通配符可以是%或_,
%表示任意多字符
_表示一个字符
5.逻辑运算符 and or not
#单条件查询
select 字段名 from表名 where 条件;
例:select emp_namefrom employee where post='sale';#多条件查询
select 字段名from employee where 条件 and条件
例:SELECT emp_name,salary FROM employee WHERE post='teacher' AND salary>10000;#关键字between and
例: SELECT emp_name,salary FROM employee
WHERE salary BETWEEN10000 AND 20000;
SELECT emp_name,salary FROM employee
WHERE salary NOT BETWEEN10000 AND 20000;#关键字 IS NULL(判断某个字段是否为null不能用等号,需要用IS)
例:SELECT emp_name,post_comment FROM employee
WHERE post_comment IS NULL;
SELECT emp_name,post_comment FROM employee
WHERE post_comment IS NOT NULL;
注意是空字符串,不是null#关键字IN集合查询
例:SELECT emp_name,salary FROM employee
WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000;
SELECT emp_name,salary FROM employee
WHERE salary IN (3000,3500,4000,9000) ;
SELECT emp_name,salary FROM employee
WHERE salary NOT IN (3000,3500,4000,9000) ;#关键字like模糊查询
例: 通配符’%’
SELECT*FROM employee
WHERE emp_name LIKE'eg%';
通配符’_’
SELECT*FROM employee
WHERE emp_name LIKE'al__';
group by
#单独使用group by 关键字分组
select post fromemployee group by post;#group by关键字和group_concat()函数一起使用
select post,group_concat(emp_name)fromemployee group by post;#按照岗位分组,并查询组内成员名字
#group by 与聚合函数一起使用
select post,count(id) fromemployee 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 @@sql_mode;+--------------------+
| @@sql_mode |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+rowin set (0.00sec)
mysql> select * from emp where salary > 100000;+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+rowin set (0.00sec)
mysql> select post,group_concat(emp_name) from emp group by post having salary > 10000;#错误,分组后无法直接取到salary字段
ERROR 1054 (42S22): Unknown column 'salary' in 'having clause'mysql> select post,group_concat(emp_name) from emp group by post having avg(salary) > 10000;+-----------+-------------------------------------------------------+
| post | group_concat(emp_name) |
+-----------+-------------------------------------------------------+
| operation | 程咬铁,程咬铜,程咬银,程咬金,张野 |
| teacher | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex |
+-----------+-------------------------------------------------------+rowsin set (0.00sec)
验证
验证
order by 查询排序
按单列排序
SELECT*FROM employee ORDER BY salary;
SELECT* FROM employee ORDER BY salary ASC; #升序
SELECT * FROM employee ORDER BY salary DESC;#降序
按多列排序:先按照age排序,如果年纪相同,则按照薪资排序
SELECT* fromemployee
ORDER BY age,
salary DESC;
LIMLT 限制查询的记录数
示例:
SELECT*FROM employee ORDER BY salary DESC
LIMIT3; #默认初始位置为0
SELECT*FROM employee ORDER BY salary DESC
LIMIT 0,5; #从第0开始,即先查询出第一条,然后包含这一条在内往后查5条
SELECT*FROM employee ORDER BY salary DESC
LIMIT5,5; #从第5开始,即先查询出第6条,然后包含这一条在内往后查5条
使用正则表达式查询
SELECT * FROM employee WHERE emp_name REGEXP '^ale'; #以ale开头
SELECT* FROM employee WHERE emp_name REGEXP 'on$'; #以on结尾
SELECT* FROM employee WHERE emp_name REGEXP 'm{2}'; #连续的两个m