单表查询
单表查询
语法
1select distinct 字段1，字段2 from 表名
2 where 条件
3 group by 条件
4 having 聚合函数或条件
5 order by 条件
6 limit 数字
语法执行优先级
1from
2 where
3 group by
4 having
5 distinct
6 order by
7 limit
1.找到表:from
2.拿着where指定的约束条件，去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4.将分组的结果进行having过滤
5.执行select
6.去重
7.将结果按条件排序：order by
8.限制结果的显示条数
示例表
| 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 |
简单查询
1select id,name from emp;
2select * from emp;
去重查询
1selece distinct post from emp;
四则运算查询
1select name,salary*12 from emp;
定义显示格式查询
1#concat()函数连接字符串
2select concat('姓名: ',name,' 年薪: ',salary*12) as annual_salary from emp;
3#concat_ws()第一个参数为分割符
4select concat(':',name,salaary*12) as annual_salsry from emp;
5#结合CASE语句：
6 SELECT
7 (
8 CASE
9 WHEN NAME = 'egon' THEN
10 NAME
11 WHEN NAME = 'alex' THEN
12 CONCAT(name,'_BIGSB')
13 ELSE
14 concat(NAME, 'SB')
15 END
16 ) as new_name
17 FROM
18 emp;
19注意：as annual_salary 是给select 的表字段重新命名。可以省略as
20补充as语法
21 mysql> select emp.id,emp.name from emp as t1; # 报错
22 mysql> select t1.id,t1.name from emp as t1;
where 约束
1#1. 比较运算符：> < >= <= <> !=
2#2. between x and y
3#3. 关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)
4#4. in(x,y,z)
5#5. 关键字like
6 %表示任意多字符
7 _表示一个字符
8#6. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
1**代码示例**
2#1:单条件查询
3 SELECT name FROM employee
4 WHERE post='sale';
5#2:多条件查询
6 SELECT name,salary FROM employee
7 WHERE post='teacher' AND salary>10000;
8#3:关键字BETWEEN AND
9 SELECT name,salary FROM employee
10 WHERE salary NOT BETWEEN 10000 AND 20000;
11#4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)
12 SELECT name,post_comment FROM employee
13 WHERE post_comment IS NOT NULL;
14#5:关键字IN集合查询
15 SELECT name,salary FROM employee
16 WHERE salary NOT IN (3000,3500,4000,9000) ;
17#6:关键字LIKE模糊查询
18 通配符’%’
19 SELECT * FROM employee
20 WHERE name LIKE 'eg%';
21 通配符’_’
22 SELECT * FROM employee
23 WHERE name LIKE 'al__';
group by
分组发生在where之后，即分组是基于where之后得到的记录而进行的
设置sql_mode 为ONLY_FULL_GROUP_BY
没有设置ONLY_FULL_GROUP_BY,也有结果，默认都是组内的第一条记录，但其实这是没有意义的
1单独使用GROUP BY关键字分组
2 SELECT post FROM employee GROUP BY post;
3 注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数
4GROUP BY关键字和GROUP_CONCAT()函数一起使用
5 SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#按照岗位分组，并查看组内成员名
6 SELECT post,GROUP_CONCAT(name) as emp_members FROM empGROUP BY post;
7GROUP BY与聚合函数一起使用
8 select post,count(id) as count from emp group by post;#按照岗位分组，并查看每个组有多少人
可以借助聚合函数查看分组后的具体信息
聚合函数
强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组
1示例：
2 SELECT COUNT(*) FROM employee;
3 SELECT COUNT(*) FROM employee WHERE depart_id=1;
4 SELECT MAX(salary) FROM employee;
5 SELECT MIN(salary) FROM employee;
6 SELECT AVG(salary) FROM employee;
7 SELECT SUM(salary) FROM employee;
8 SELECT SUM(salary) FROM employee WHERE depart_id=3;
having关键字过滤
！！！执行优先级从高到低：where > group by > having
- Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
- Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
order by 排序
1按单列排序
2 SELECT * FROM employee ORDER BY salary;
3 SELECT * FROM employee ORDER BY salary ASC;(asc 升序)
4 SELECT * FROM employee ORDER BY salary DESC;(desc 降序)
5按多列排序:先按照age排序，如果年纪相同，则按照薪资排序
6 SELECT * from employee
7 ORDER BY age,
8 salary DESC;
9select * from emp order by age desc,salary asc; #先按照age降序排，再按照薪资升序排
limit 限制查询记录数可以实现分页查询
1示例：
2 SELECT * FROM employee ORDER BY salary DESC
3 LIMIT 3; #默认初始位置为0
4 SELECT * FROM employee ORDER BY salary DESC
5 LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条
6 SELECT * FROM employee ORDER BY salary DESC
7 LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条
正则表达式查询
1#示例
2SELECT * FROM employee WHERE name REGEXP '^ale';
3SELECT * FROM employee WHERE name REGEXP 'on$';
4SELECT * FROM employee WHERE name REGEXP 'm{2}';
