add:

指的是最小2个以及2个以上的条件才满足要求

select * from employees where first_name="Parto"and gender="M" limit 5;

 or:

2个或者2个以上满足其中一个条件即可

select * from employees where first_name="Chirstian"or last_name="Speek" limit 5;

 大于

select * from salaries where salary>60117 limit 5;

 大于等于

select * from salaries where salary>=60117 limit 5;

 小于

select * from salaries where salary<60117 limit 5;

 小于等于

 select * from salaries where salary<=60117 limit 5;

 不等于

select * from salaries where salary!=60117 limit 5;

 等于

select * from salaries where salary=60117 limit 5;

in:包含

任意一种即可

 select * from employees where first_name in ("Georgi","Parto") limit 5;

 between...and

查询范围，包含开始也包含结尾

select * from salaries where salary between 60117 and 60118 limit 5;

 not

not in

select * from salaries where salary not in (60117,60118) limit 5;

 not between...and 

select * from salaries where salary not between 60117 and 60118 limit 5;

正则

%:匹配任意字符

可以放在任意位置

select * from employees where first_name like "%gi"limit 5;

 select * from employees where first_name like "Ch%an" limit 5;

 select * from employees where first_name like "Ch%" limit 5;

 ^:以......为开头

rlike:正则模式

 select * from employees where first_name rlike "^Ch" limit 5;

 $:以......为结尾

select * from employees where first_name rlike "an$" limit 5;

 as:别名

select first_name as 首名 from employees limit 5;

 函数：实现某一个特定的功能

max():最大

select max(score) as 最高分 from student;

 min():最小

 select min(score)as 最低分 from student;

 avg():平均分

select avg(score)as 平均分 from student;

 sum():总数

 select sum(score)as 总分 from student;

 order by：排序

默认排序：由小到大

select * from student order by score;

 asc:顺序排序

由小到大

select * from student order by score asc;

 desc:倒序排序

由大到小

 distinct:去重

 select distinct name from student;

select distinct * from student;

聚合函数

group by

例题1：对employees表中的gender进行分组，然后对每组进行统计

select gender,count(gender)from employees group by gender limit 10;

 例题2：查询出以年龄取出平均分 过滤出平均分大于50 并且，以平均分进行倒序排序

select age,avg(score) as 平均分 from student group by age having 平均分>50 order by 平均分 desc;

理解

1、取出不同年龄段的平均分

2、在这个不同年龄段平均分的基础上过滤出>50的

3、再对这些大于50的平均分做一个倒序排序

 内连接：

inner join

查询2个或者2个以上 查询他们交集（共同拥有）的数据

 例题1：

查询出店铺名称 商品名称 商品类型 商品价格

select shop_name,good_name,good_type,price from shop inner join goods on shop.id=goods.shop_id;

例题2：查询出店铺名称，商品名称，商品类型，价格，还有快递名称，电话

select shop_name,good_name,good_type,price,name,phone from shop inner join goods on shop.id=goods.shop_id inner join logistic on goods.id=logistic.good_id;

 例题3：查询商品目前有多少个

 例题4：

1、查询shop,goods表的所有字段

2、以商品的价格作为倒序排序

select * from shop inner join goods on shop.id=goods.shop_id order by goods.price desc;

having与where的区别：

1. having只能⽤于group by（分组统计语句中）
2. where 是⽤于在初始表中筛选查询，having⽤于在where和group by 结果分组中查询
3. having ⼦句中的每⼀个元素也必须出现在select列表中
4. having语句可以使⽤聚合函数，⽽where不使⽤。