cmd--mysql mysql/bin
zerofill not null default xxxx
unsigned not null default XXXX
auto_increment primary key
show create table goods;查看见表语句
\c 终止命令
create table test5 (
-> ts
timestamp default CURRENT_TIMESTAMP --当前时间 XXXX-XX-XX XX:XX:XX格式
->
,
-> id int
-> )engine myisam charset utf8;
alter table XXX modify / change 的区别
max,min,sum,avg,count 注意null列 null count(*) count(id)注意null影响
select goods_id,sum(goods_number) from goods; 错误的 goods_id是第一次粗线的值
select name,count(score<60) as k,avg(score) from stu group by name; 错误
select name,sum(score < 60) as gk ,avg(score) as pj from stu group by name having gk >=2
取出点击量前三名到前5名的商品select goods_id,goods_name,click_count from ecs_goods order by click_count desc limit 2,3;
group by having 顺序注意
注意聚合函数max min sum 之类如果连带查name 的附属列 ，注意name不一定是要的值
例子 每个栏目下id最大的XXX
select goods_id,goods_name from
(select
goods_id,cat_id,goods_name,shop_price,add_time from goods
order by
cat_id,goods_id desc) as tmp
GROUP BY cat_id order by goods_id
select goods_id,goods_name from goods where goods_id in (
select
max(goods_id) from goods GROUP BY cat_id
)
exists的用法 下面有内容的项目
select cat_id,cat_name from category
where exists (select * from goods
where goods.cat_id=category.cat_id);
null的判断方式注意！count(*) count(id) 对于 null值那一行的统计有什么不同
select from table1,table2 并集 t1Xt2
create table minigoods like goods; 创建某表像goods表的结构
select ‘A‘ = ‘a‘;
A left join B 以A为基准 A不会有null B则可能有null
A right join B 以B为基准，A可能有null B不一定有null
inner join 不允许有 null 包含行
union 会去除相同的重复行，每个列
union all 则是并集
列名不同，列类型不同，可以union / union all