查询语法：
select 字段1，字段2….from 表名
? where 条件
? group by 字段1
? having 筛选
? order by 默认升序
? limit 限制条数
关键字的执行优先级
重点中的重点：关键字的执行优先级
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的结果限制显示条数简单查询
select * from 表名
distinct 去重，必须放在要去重的字段前面
四则运算 字段的四则运算
concat 定义显示格式
#简单查询
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;where 约束
比较运算符：大于小于不等于
between 10 and 20 值在10 到20之间
in（10,20,30）值是或
like ‘da%’，%表示任意多字符
like ‘da_’，表示一个字符，要几个字符就加几个_
逻辑运算符：在多个条件直接可以使用逻辑运算符 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__';group up:分组查询
可以按照任意字段分组，但分完组后，只能查看分组的那个字段，要想获取其他字段信息，需要借助函数
单独使用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;#按照岗位分组，并查看每个组有多少人having：筛选
having和where语法上是一样的
select * from employee where id>15;
select * from employee having id>15;不同点：
? order by：查询排序
单列排序
SELECT * FROM employee ORDER BY salary;
SELECT * FROM employee ORDER BY salary ASC; #升序
SELECT * FROM employee ORDER BY salary DESC; #降序多列排序
SELECT * from employee
ORDER BY age,
salary DESC;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;聚合函数
示例：
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;#建表
create table department(
id int,
name varchar(20)
);
create table employee1(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
#插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');
insert into employee1(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;交叉连接：不适用任何匹配条件，生成笛卡尔积
select * from employee1,department where employee1.dep_id=department.id;内连接：找到两张表中共有的部分，只连接匹配的行
#上面用where表示的可以用下面的内连接表示，建议使用下面的那种方法
select * from employee1 inner join department on employee1.dep_id=department.id;左连接：优先显示左表全部记录
#左链接：在按照on的条件取到两张表共同部分的基础上，保留左表的记录
select * from employee1 left join department on department.id=employee1.dep_id;
select * from department left join employee1 on department.id=employee1.dep_id;右连接：优先显示右表全部记录
#右链接：在按照on的条件取到两张表共同部分的基础上，保留右表的记录
select * from employee1 right join department on department.id=employee1.dep_id;
select * from department right join employee1 on department.id=employee1.dep_id;全外连接：显示两个表的全部记录
mysql不支持full join，可以使用union间接实现全外连接
select * from employee1 left join department on department.id=employee1.dep_id
union
select * from employee1 right join department on department.id=employee1.dep_id;左连接，右连接，没有匹配的用null填充
符合条件连接查询
示例1：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出公司所有部门中年龄大于25岁的员工
select * from employee1 inner join department on employee1.dep_id=department.id and age>25;示例2：以内连接的方式查询employee和department表，并且以age字段的升序方式显示
select * from employee1 inner join department on employee1.dep_id=department.id =and age>25 and age>25 order by age asc;子查询
#1：子查询是将一个查询语句嵌套在另一个查询语句中。
#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
#3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
#4：还可以包含比较运算符：= 、 !=、> 、<等select语句关键字的定义顺序
select
distinct
from
join
on
where
group by
having
order by
limit
SELECT DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>
SELECT语句关键字的定义顺序原文：https://www.cnblogs.com/daviddd/p/12047421.html