mysql 基础注意点

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

mysql 基础注意点,古老的榕树,5-wow.com