5.30-MySQL（四）

查询大于等于，小于等于，不等于：

比如 select * from salaries where salary>=76885 limit 5;

 

 模糊查询：select * from 表名 where 字段 like “xxx%”

比如：select * from employees where first_name like "Kyoic%hi" limit 10;

 

 使用别名：两种方式

select count(1) as from

select count(*) as from

select count(1) as 总数 from employees;

select count(*) as 总数 from employees;

 

 查询以 开始：select * from 表名 where 被查询的 rlike "^以XX开始"limit 5;

比如：select * from titles wherer title rlike "^eng"limit 5;

 

 查询以 结束：select * from 表名 where 被查询的 rlike "以XX结尾$"limit 5;

比如：select * from titles where title rlike "er$"limit 5;

 

 查询最高max最低min平均值avg：select max(score) as from；

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

 

 查询排序方式：select * from order by ；

比如：select * from student order by score;

 

 查询正序倒序方式：select * from order by asc（desc）；

比如：

select * from student order by age asc;

select * from student order by age desc;

 

 查询总数：select 要查询数据的数据 from 要查询的数据

比如：select sum(score) from student;

 

 去重复：select distinct

比如：select distinct name from student;

 

 实战：以年龄查询学生的平均分大于50并且以平均分倒序

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

面试：having和where的区别

having：根据聚合函数gruop by 结合使用

where：聚合函数不能在where子句中使用。（不能用在where后面，可以用在where前面）

 

 实战 查询员工里有多少名男女

select gender as 性别,count(gender) as 人数 from employees group by gender;

 

 

 

 

比如：查询shop里id和goods里shop_id是否一致

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

 

 比如：查询shop和goods共同的id并且goods的name为maitai53

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

 

 查询结果：select+字段+from+表名+inner join+要加入的表名+on

比如查询店铺名称，快递名称，快递电话

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

 

 作业

1、查询shop,goods表的所有字段 2、以商品的价格作为倒序排序

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