1、增
插入单行或者多行
insert into msg
(id,title,name,content)
values
(1,‘又来了’,‘李四’,‘你只能千年老二’),
(3,‘3标题’,‘刘备’,‘雌雄双剑’);
特殊:insert语句 允不允许不写列名?
答:允许!如果没有声明列名,则默认插入所有列,因此,值应该与全部列,按顺序一一对应
2、删
delete from msg where id=2;
3、改
update msg
set
id=2,
content=‘偏要当老大’
where
name=‘李四’;
4、查
select * from msg; //查询整张表内容
select id,title from msg; //查询id,title这两列
select id,title from msg where id>2; // 查id>2的内容
select 5种子句:
Where 条件查询
group by 分组
having 筛选
order by 排序
- limit 限制结果条数
(1)、where:各种条件查询场合
常用运算符: <小于 >大于 !=或<>不等于 =等于 <=小于等于 >=大于等于 in在某集合内 between在某范围内
in (值1,值2…值n),等于值1—n任意一个都行(in在某集合内)
Between 值1 and 值2,表示在值1和值2之间(between在某范围内),取等号
例:select goods_id,cat_id,goods_name from goods where cat_id in (4,5);
例:select goods_id,goods_nmae,shop_price from goods where shop_price between 2000 and 3000;
逻辑运算符: not or and
select goods_id,goods_name,shop_price from goods where shop_price >=3000 and shop_price <=5000 or shop_price >=500 and shop_price <=1000;
select goods_id,cat_id,goods_name from goods where cat_id not in (4,5);
(2)、group:常用语统计场合
max : 求最大
min : 求最小
sum : 求总和
avg : 求平均
count:求总行数
查询每个栏目下面最贵的商品价格
Select cat_id,max(shop_price) from goods group by cat_id;
查询所有行
Select count(*) from goods;
列名当变量看
查询出本店每个商品比市场价格低多少钱?
Select goods_id,goods_name,market_price-shop_price from goods;
查询每个栏目下面积压的货款
Select cat_id,sum(shop_price * goods_number) from goods group by cat_id;
可以给列或计算结果取别名,用as
Select cat_id,sum(shop_price * goods_number) as hk from goods group by cat_id;
(3)、having:
having与where异同点:
having与where类似,可筛选数据 where后的表达式怎么写,having就怎么写
where针对表中的列发挥作用,查询数据
having针对查询结果中的列发挥作用,筛选数据
where 对表起作用 having是对结果进行筛选
查询出本店价格比市场低多少钱,并且把低200元以上的商品选出来。
Select goods_id,goods_name,market_price – shop_price as sheng from goods having sheng > 200;
同上题,只不过查第3个栏目下比市场价格低200元以上的商品
Select goods_id,cat_id,market_price – shop_price as sheng from goods where cat_id=3 having sheng > 200;
查询积压货款超过2W元的栏目,以及该栏目积压的货款
Select cat_id,sum(shop_price * goods_number) as hk from goods group by cat_id having hk > 20000;
(4)、order by:各种排序场合
可以根据字段来排序
根据字段可以升序排asc,也可以降序排列desc
默认是升序排列
可以按多字段排序, order by 列1 [desc/asc] , 列2 [desc/asc]……
栏目3下商品价格降序排列
Select goods_id,cat_id,goods_name,shop_price from goods where cat_id=3 order by shop_price desc(降序)/asc(升序);
(5)、limit :在语句的最后,起到限制条目的作用
Limit [offset,] [N]
Offset:偏移量,从第n-1个开始 ,第四个,offset=3
N: 取出几条
Offset,如果不写,则相当于 limit 0,N
例:取出价格第4到第6高的商品
Select goods_id,goods_name,shop_price from goods order by shop_price desc limit 3,3;
注意:5个子句是有顺序要求的:where, group, having, order by, limit
5、模糊查询:Like
‘%’ : 通配任意个字符
‘_’ : 通配单个字符
案例:想查找’诺基亚’开头的所有商品
select goods_id,goods_name from goods where goods_name like ‘诺基亚%’;
select goods_id,goods_name from goods where goods_name like ‘诺基亚N_ _’;