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:求最大值 select max(shop_price) from goods; select max(shop_price) from goods group by cat_id;//求每个分类中价格最高的手机
2、min:求最小值 select min(shop_price) from goods;
3、sum:求总和(as totalNum取个别名) select sum(shop_price) as totalNum from goods; select sum(shop_price) as typeTotalNum from goods group by cat_id;//求每个分类的总和
4、avg:求平均值 select avg(shop_price) as avgPrice from goods; select avg(shop_price) as avgPrice from goods group by cat_id;//求每个分类平均值
5、count:求总行数 select count(*) from goods;
select cat_id,count(*) from goods group by cat_id;//求每个类型下的商品种类数量
//找出商品种类>=4的分类,主要类型ID(cat_id)如下2种
select cat_id,count(*) as t from goods group by cat_id having t >= 4;
select cat_id from goods group by cat_id having count(*) >= 4;
//查询每个分类下积压的货款
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;
案例: 创建商品表 编号 商品名称 商品进货价 商品售价 库存 商品类型
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 )
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);