刘道成 mysql 学习笔记2

仿ecshop建库create database mugua charset utf8;use mugua;create table goods(goods_id int primary key auto_increment,cat_id smallint not null default 0,goods_sn char(15) not null default '',goods_name varchar(30) not null default '',click_count mediumint unsigned not null default 0,brand_id smallint not null default 0,goods_number smallint not null default 0,marcket_price decimal(7,2) not null default 0.00,shop_price decimal(7,2) not null default 0.00,add_time int unsigned not null default 0 )charset utf8;从一个数据库导数据到另一个数据库的方法会碰到中文出现乱码   先运行这个语句 set names gbkinsert into mugua.goodsselectgoods_id,cat_id,goods_sn,goods_name,click_count,brand_id,goods_number,market_price,shop_price,add_timefrom shop.goods;create table category(cat_id smallint primary key auto_increment,cat_name varchar(30) not null default '',parent_id smallint not null default 0)charset utf8;insert into mugua.categoryselectcat_id,cat_name,parent_idfrom shop.category;create table brand(brand_id smallint primary key auto_increment,brand_name varchar(30) not null default'')charset utf8;insert into mugua.brandselectbrand_id,brand_namefrom shop.brand;（一）where  表达式在哪一（几）行成立  就把那一（几）行取出来  select * from goods where cat_id=3;  select * from goods where cat_id !=3;等价于 select * from goods where cat_id <>3; select * from goods where cat_id>3; select * from goods where cat_id<3; select * from goods where cat_id>=3; select * from goods where cat_id<=3; select * from goods where cat_id  in(3,4); select * from goods where cat_id between 3 and 5; select * from goods where cat_id >3 or cat_id <=2; select * from goods where cat_id>=3 and cat_id<=6;（二）group by  分组查询   group by 与max  min  sum  avg  count连用1 查出最贵商品价格select max(shop_price) from goods;2 查出最便宜商品价格select min(shop_price) from goods;3 查出商品总量select sum(goods_number) from goods;4 查出所有商品平均价格select avg(shop_price) from goods;5,查出所有商品总类select count(goods_id) from goods;#查询每个栏目下  要用到group by1 查出最贵商品价格select cat_id,max(shop_price) from goods group by cat_id;2 查出最便宜商品价格select cat_id,min(shop_price) from goods group by cat_id;3 查出商品总量select cat_id,sum(goods_number) from goods group by cat_id;4 查出所有商品平均价格select cat_id,avg(shop_price) from goods group by cat_id;5,查出所有商品总类select cat_id,count(goods_id) from goods group by cat_id;#把列名当变量就可以进行运算了  可以写出更加高效的sql语句#查询每个栏目下积压的货款select cat_id,sum(shop_price*goods_number) from goods group by cat_id;#查询出每个商品比市场价低多少select goods_id,goods_name,marcket_price-shop_price as cha from goods;（三）having查询where 发挥作用的时间在查表结果之前  having 是筛选  是对查询结果的进一步选择1，查询比市场价省200元以上得物品价格以及所省的钱select goods_name,goods_id,marcket_price-shop_price as sheng from goods having sheng>200;如果写成 select goods_name,goods_id,marcket_price-shop_price as sheng from goods where sheng>200;是错误的当然也可以写成select goods_name,goods_id,marcket_price-shop_price as sheng from goods where marcket_price-shop_price>200;只不错计算了两次  不如having 好2，查询积压货款超过 20000元货款的栏目 以及该货款select cat_id,sum(shop_price*goods_number) as jiya from goods group by cat_id having jiya>20000;更多资源  http://www.taobtbw.com/（四）order by 和limit查询1，按价格由高到低排序select goods_name,goods_id,shop_price from goods order by shop_price desc; 2，按发布时间由早到晚排序select goods_id,goods_name,add_time from goods order by add_time;3，按栏目由低到高排序，栏目内部按价格由高到低排序select goods_id,goods_name,cat_id,shop_price from goods order by cat_id asc,shop_price desc;limit 的用法  limit在语句的最后  起到限制的作用limit[offset]Noffset :偏移量N;去处的条目数量offset不写则相当于 limit 0，N是从0 开始算的  而不是14,取出价格最高的前三名商品select goods_id,goods_name,shop_price from goods order by shop_price desc limit 0,3;5,取出点击量前三名到前5名的商品select goods_id,goods_name,click_count from goods order by click_count desc limit 2,3;思考题  ：取出每个栏目下最贵的商品错误1：select goods_id,goods_name,cat_id,max(shop_price) from goods group by cat_id  错误原因是用了聚合函数  而其他栏目以第一次遇到的为准错误2： select goods_id,goods_name,cat_id,shop_price from goods group by cat_id order by shop_price desc;这只是对分组后的排序 而不是对每一个栏目的排序错误3：select goods_id,goods_name,cat_id,shop_price from goods order by shop_price desc group by cat_id ;   语法错误   因为查询字句是有顺序性的顺序如下  where ， group by， having ，  order  by ，limit正确：select * from (select goods_id,goods_name,cat_id,shop_price from goods order by cat_id asc,shop_price desc ) as temp group by  cat_id;(四) （1）where from exists 子查询1查询出最新商品（以最大编号为准）select goods_name,goods_id from goods where goods_id=(select max(goods_id) from goods);2,查询出编号为19的商品的栏目名称(待定)3，用where子查询出每个栏目下的最新产品（以id号最大为准）select goods_name,goods_id,cat_id from goods where goods_id in (select max(goods_id) from goods group by cat_id );  这里后面不能再加其他字段  否则会出错4,用from子查询查出每个栏目下最新的产品（以id号最大为准）select * from (select goods_name,goods_id,cat_id from goods order by cat_id asc,goods_id desc) as temp group by cat_id;  5,exists 子查询    在category表中查询有商品的栏目  （先从category表中找出cat_id 然后与goods逐行比较）select cat_id,cat_name from category where exists (select * from goods where category.cat_id=goods.cat_id);注意几个问题  1：from 子查询是把from后的查询结果当成一张临时表  一定要为临时表创建一个名字  用as  + 名字  表示2；from子查询中的* 指的是from表中的字段  而不是原表的字段。3.where 子查询是把内层查询的结果当外层查询的比较条件4.exists 查询是把外层的查询结果拿到内层，看内层是否成立

更多资源请访问  http://www.17xuexiba.com/