数据库搜索

一.where条件查询
1.like模糊查询，%是通配符
select*from item where name like '%联想%';
2.and,or前者优先级高
select*from item where name like '%联想%' and detail like '%4%';
select*from item where name like '%联想%' or name like '%宏碁%';

3.>,<,>=,<=,!=,<>
select*from item where name like '%显示器%' and price<=1000;
select*from item where name like '%显示器%' and price<=1000 and price>=900;
select*from item where name like '%显示器%' and price!=1299;
select*from item where name like '%显示器%' and price<>1299;
select*from item where name like '%显示器%' and (price<=1000 or price>5000);

4.between m and n（包含m和n）
select*from item where price between 500 and 1000;
5.is,in,not
select*from item where category_id not in (6,7,8);
select*from item where price not between 500 and 1000;

二.搜索结果排序order by语句——在where子句之后
asc——升序,desc——降序
select*from item where name like '%得力%' order by price asc;
select*from item where name like '%得力%' order by price desc;
select*from item where name like '%得力%' order by price;——默认升序
select*from item where name like '%得力%' order by category_id asc,price desc;

三.搜索结果分页limit子句——在order by语句之后
1.begin（起始行）,size（本页总行数）
select*from item order by price asc limit 0,5;——第一页，每页五条
select*from item order by price asc limit 5,5;——第二页，每页五条

四.搜索结果的显示
1.concat()函数——拼接字符串
select concat(name,' ',price,'元') as title,detail,stock from item;
2.数值计算
+ - * / % mod()
select name,detail,price*stock as total from item;

3.date_format()函数——日期格式化
select name,price,date_format(upload_time,'%Y年%c/m月%d日 %h时%i分%s秒') from item;
4.ifnull()函数——空值处理
select name,price,ifnull(image,'无') from item;
5.搜索结果的显示
汇总搜索结果——聚合函数
1.sum();——库存合计
2.avg();——平均单价
select sum(stock),avg(price) from item where name like '%笔记本%';

3.count();——查询条目数
select count(*) from item where name like '%笔记本%';
select count(stock) from item where name like '%笔记本%';
4.max();——最高单价
select max(price) from item where name like '%笔记本%';
5.min();——最小库存
select min(stock) from item where name like '%笔记本%';
select name as 最贵的电脑 from item where price in (select max(price) from item);