mysql_查询语句_where_逻辑运算_is判断_模糊查询_group by

作者: 鲁智深 分类: PHP 发布时间: 2015-09-24 17:30

概述

mysql中的完整的查询语句

select 字段列表 from 表名 [where条件] [group by 字段] [having] [order by 字段 asc|desc] [limit [开始位置],长度]

where:条件查询

group by:分组

having:筛选

order by:排序

limit:截取记录

一、where常用运算符

比较运算符:

>

>=

<

<=

=

!=、<>//不等于

in(v1,v2,v3)//等于某个值 select * from text where id in(1,2,3);

between v1 and v2 区间 select * from text where id between 1 and 2;

二、逻辑运算符:

and(逻辑与)

1
select * from text where id=1 and id=2;

or(逻辑或)

1
select * from text where id=1 or id=2;

not(逻辑非)

1
select * from text where not id=1;

三、is判断:

is null:查询字段值为null的记录

1
select * from text where id is null;

is not nul //查询字段值不为null

1
select * from text where id not null;

四、模糊查询:

like 像

%:表示任意字符,包括空字符

1
select * from text where name like "李%";

_:表示任意一个字符,不包括空

1
select * from text where name like "李_";

五、group by:分组

1、一般情况下,group by需要与统计函数(聚合函数)一起使用有意义,聚合函数:类似于php中的系统函数概念

2、用 group by 字段 分组后,这个字段有集中类型,就得到几条记录,每个分组默认取第一条

mysql的五大统计函数:

1、max:求最大值

1
2
3
select max(shop_price) from goods;

select max(shop_price) from goods group by cat_id;//求每个分类中价格最高的手机

2、min:求最小值

1
select min(shop_price) from goods;

3、sum:求总和(as totalNum取个别名)

1
2
3
select sum(shop_price) as totalNum from goods;

select sum(shop_price) as typeTotalNum from goods group by cat_id;//求每个分类的总和

4、avg:求平均值

1
2
3
select avg(shop_price) as avgPrice from goods;

select avg(shop_price) as avgPrice from goods group by cat_id;//求每个分类平均值

5、count:求总行数

1
2
3
select count(*) from goods;

select cat_id,count(*) from goods group by cat_id;//求每个类型下的商品种类数量

//找出商品种类>=4的分类,主要类型ID(cat_id)如下2种

1
2
3
select cat_id,count(*) as t from goods group by cat_id having t &gt;= 4;

select cat_id from goods group by cat_id having count(*) &gt;= 4;

//查询每个分类下积压的货款

1
select cat_id,sum(goods_price*goods_num) as hk from goods group by cat_id;

六、mysql中的其他函数

ceil()//向上取整

floor()//向下取整

round()//四舍五入

upper()//转大写

lower()//转小写

curdate()//当前日期

curtime()//当前时间

now()//当前日期时间

year(“2015-09-21”)//求年

month(“2015-09-21”)//求月

day(“2015-09-21”)//求天

hour(“22:22:09”)//求时

minute(“22:22:09”)//求分

second(“22:22:09”)//求秒

concat(“str”,”str1″)//拼接字符串或者字段

distinct//对记录去重复,每个字段相同

1
select disinct goods_name from goods;

案例:

创建商品表

编号

商品名称

商品进货价

商品售价

库存

商品类型

1
2
3
4
5
6
7
8
create table goods(
id int auto_increment primary key,
goods_name varchar(30) not null default "",
goods_price decimal(6,2) not null default "0.00",
shop_price decimal(6,2) not null default "0.00",
goods_num smallint not null default 0,
cat_id tinyint not null default 0
)
1
2
3
4
5
6
7
8
9
insert into goods(goods_name,goods_price,goods_price,shop_price,goods_num,cat_id)values
("诺基亚",1500.00,1600.00,5,1),
("小米",2500.00,2600.00,6,1),
("坚果",800.00,900.00,7,1),
("锤子",1500.00,2600.00,8,1),
("魅族",1500.00,1700.00,9,1),
("苹果4",3500.00,4600.00,15,3),
("苹果5",4500.00,5600.00,15,3),
("苹果6",5500.00,6600.00,15,3);

如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!

发表评论

电子邮件地址不会被公开。 必填项已用*标注