MySQL_03

增删改查
1，insert into 表名 (字段1，字段2) values (v1,...),(v2....)
2，update 表名
set 列1=值1 , 列2=值2
where expression
3，delete from 表名 where expression;
-------------------------------------------------------------
select * from 表名 #取出所有行所有列
select 字段名1,字段名2 from 表名 #取出表中数据的某列
select * from table where expression #取出满足条件行
select 列名1,列名 from table where expression #取出部分行中的部分列
where后跟表达式，使得表达式为真的数据项被取出（表达式不要求是逻辑表达式）

>, < ,>=,<=,=,!=,<>, in ( , ),between value1 and value2, expre1 and expre2,
expre1 or expre2, column not in (value1,value2 ,value3)

例：select goods_id,goods_name from goods;
select * from goods;
select goods_id,goods_name from goods where goods_id>20;
select goods_id,goods_name,shop_price,market_price from goods;
select goods_id,goods_name from goods where shop_price>3000;
select goods_id,goods_name,market_price from goods where market_price - shop_price>200;
select goods_id,cat_id,goods_name from goods where cat_id != 3; #<>
select goods_id,cat_id,goods_name from goods where cat_id in(4,5,6);
select goods_id,goods_name,shop_price from goods where shop_price between 2000 and 3000; #允许等于边界值
select goods_id,goods_name,shop_price from goods where shop_price>=3000 and shop_price<=5000;
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 !=4 and cat_id !=5;
select goods_id,cat_id,goods_name from goods where cat_id not in(4,5);
# %通配任意字符，_通配单个字符
select goods_id,goods_name from goods where goods_name like '诺基亚%';
select goods_id,goods_name from goods where goods_name like '诺基亚N__'

MySQL常用函数：
max 最大值
min 最小值
avg 平均值
sum 总和
count 总条数

group子句：

as可以给列或表取别名 column as newName
列名有时候可以当做变量名看待，从而进行各种计算

having子句：对查询结果进行再次筛选
select goods_id,goods_name,market_price-shop_price as jiesheng from goods;
select goods_id,goods_name,market_price-shop_price as jiesheng from goods having jiesheng >200;
select goods_id,cat_id,market_price-shop_price as jiesheng from goods where cat_id = 3 having jisheng >200;
select cat_id,sum(shop_price * goods_num) as hk from goods group by cat_id having hk>20000;

order by排序
asc:升序，默认方式
desc:降序
select * from tab where expression order by column asc/desc, column2 asc/desc;

limit (处理分页)
语法：limit offset,num 其中offset代表偏移量、位置，范围从0开始，num代表取出条目
select * from tab where expression order by name limit 0,10; 从符合条件的数据项中取出从0行开始的10行数据。
例：查出每个栏目下最贵的商品